Hi,
These were the instructions for the first version but I suppose they work
still. We have not tried the system with PostGIS but it works with Oracle for
pointing the sequence to be used.
"To use the lookup functionality you'll have to create a lookup
table that GeoServer/GeoTools will use to determine the
primary key structure and the sequence.
You can create the table with:
CREATE TABLE gt_pk_metadata_table (
table_schema VARCHAR(32) NOT NULL,
table_name VARCHAR(32) NOT NULL,
pk_column VARCHAR(32) NOT NULL,
pk_column_idx INTEGER,
pk_policy VARCHAR(32),
pk_sequence VARCHAR(64),
unique (table_schema, table_name, pk_column),
check (pk_policy in ('sequence', 'assigned', 'autoincrement'))
)
where:
* table_schema (varchar): schema name
* table_name (varchar): table name
* pk_column (varchar): column name
* pk_column_idx (integer): column index if pk is multicolumn (nullable)
* pk_policy (varchar): pk assignment policy: "assigned", "sequence",
"autogenerated"
* pk_sequence (varchar): full name of the sequence to be used to
generate the next value, if any
The table by default will be looked up in the current schema.
If you want to name the table in a different way there is a datastore
parameter called "Primary key metadata table" where you can type a
different name. Also, if you need to put that table in a different
schema, you can specify a qualified name in the form or
schema.tableName
in that same field.
If the metadata table above does not exist or does not contain
information about a specific table/view the usual set of heuristics
will be applied."
-Jukka-
> -----Alkuperäinen viesti-----
> Lähettäjä: babsip [mailto:babsi...@hotmail.com]
> Lähetetty: 3. syyskuuta 2010 15:47
> Vastaanottaja: geoserver-users@lists.sourceforge.net
> Aihe: Re: [Geoserver-users] Error duplicate mappingName in
> 2.0.2 (but works fine in 2.0.1)
>
>
> Hi,
>
> thanks for your tip. I checked the user-dox and googled a
> lot, but the only
> thread that got me a bit further is this one:
> http://old.nabble.com/postgresql-postgis-views-and-primary-key
> s-td28616028.html
> http://old.nabble.com/postgresql-postgis-views-and-primary-key
> s-td28616028.html
> . However, I'm still having the same problem - hopefully you
> can give me
> more information about PostGIS/view/primary key...
>
> Here is what I did:
>
> I created a table "gt_pk_metadata_table" exactly as described
> in this post:
>
> CREATE TABLE gt_pk_metadata_table (table_schema varchar(255),
> table_name
> varchar(255),
> pk_column varchar(255), pk_column_idx integer,
> pk_policy varchar(255),
> pk_sequence varchar(255));
>
> Then I inserted a line - to be honest, I wasn't sure, how to fill it
> exactly:
>
> INSERT INTO gt_pk_metadata_table
> (table_schema, table_name, pk_column, pk_column_idx,
> pk_policy)
> VALUES ('public', 'species_in_ps',
> 'map_species_ps.map_species_ps_pkey', null, 'assigned');
>
> * "species_in_ps" is the name of my view
> * "'map_species_ps.map_species_ps_pkey" is a primary key on one of the
> tables used in the view (I tried "map_species_ps_pkey" as well)
> * I tried all possible values for "pk_policy": "assigned",
> "sequence",
> "autogenerated"
>
> Then I entered the following lines into my mapping-file
> ("SpeciesInclude.xml"):
> <Parameter>
> <name>Primary key metadata table</name>
> <value>public.gt_pk_metadata_table</value>
> </Parameter>
>
> Upon start-up of GeoServer, I get the following 2 errors:
>
> WARN [geotools.jdbc] - Unknown column
> map_species_ps.map_species_ps_pkey in
> table species_in_ps
> WARN [geotools.jdbc] - No primary key or unique index found for
> species_in_ps.
>
> And, of course, retrieving the data via WFS doesn't work. I
> then tried to
> add the PK "map_species_ps_pkey" to "species_in_ps"-view, but
> PostGIS won't
> have it.
>
> A short description of the tables/views I'm using:
>
> view "species_in_ps"
> --------------------
> CREATE OR REPLACE VIEW species_in_ps AS
> SELECT map_species_ps.id_species, map_species_ps.id_ps,
> species.title,
> species.href
> FROM species
> JOIN map_species_ps ON species.idstr::text =
> map_species_ps.id_species::text;
>
> It comprises the following tables:
>
> table "species"
> --------------
> CREATE TABLE species
> (
> idstr character varying(20) NOT NULL,
> title character varying(50),
> href character varying(50),
> CONSTRAINT species_pkey PRIMARY KEY (idstr)
> )
> WITH (
> OIDS=FALSE
> );
>
> table "map_species_ps"
> ----------------------
> CREATE TABLE map_species_ps
> (
> id_species character varying(20) NOT NULL,
> id_ps character varying(20) NOT NULL,
> CONSTRAINT map_species_ps_pkey PRIMARY KEY (id_species, id_ps)
> )
> WITH (
> OIDS=FALSE
> );
>
> GeoServer features:
> * a complex feature named "protected_sites_1_xml" chains to the
> non-feature-type "speciesIncluded" (with Feature Chaining).
> * "speciesIncluded" is based upon the view "species_in_ps".
> It does not
> contain any geographical information. I have added "Primary
> key metadata
> table" in its mapping file (as described above).
>
> Could you pls. help me as to how to fill the entry in
> "gt_pk_metadata_table"? Also: is it necessary to add the
> "species_in_ps"-view in PostGIS-"geometry_columns"? I found a
> post that
> suggested this. But since the feature "speciesIncluded" does
> not contain any
> geometrical information, I thought that this would not be necessary?
> Actually, I did add it once, but it didn't make any difference...
>
> Thank you again in advance!
>
> Barbara
>
>
> Rahkonen Jukka wrote:
> >
> > Hi,
> >
> > It is possible to use a metadatatable for that. The
> structure of the table
> > is like this
> >
> > SQL> desc geoserver_metadata
> > Nimi Tyhjä arvo? Tyyppi
> > ----------------------------------------- --------
> ------------------
> > TABLE_SCHEMA NOT NULL VARCHAR2(32)
> > TABLE_NAME NOT NULL VARCHAR2(32)
> > PK_COLUMN NOT NULL VARCHAR2(32)
> > PK_COLUMN_IDX NUMBER(38)
> > PK_POLICY VARCHAR2(32)
> > PK_SEQUENCE VARCHAR2(64)
> >
> > The usage must be documented somewhere, in addition to my emails.
> >
> > -Jukka Rahkonen-
> >
>
> --
> View this message in context:
> http://old.nabble.com/Error-duplicate-mappingName-in-2.0.2-%28
> but-works-fine-in-2.0.1%29-tp29570368p29614022.html
> Sent from the GeoServer - User mailing list archive at Nabble.com.
>
>
> --------------------------------------------------------------
> ----------------
> This SF.net Dev2Dev email is sponsored by:
>
> Show off your parallel programming skills.
> Enter the Intel(R) Threading Challenge 2010.
> http://p.sf.net/sfu/intel-thread-sfd
> _______________________________________________
> Geoserver-users mailing list
> Geoserver-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>
------------------------------------------------------------------------------
This SF.net Dev2Dev email is sponsored by:
Show off your parallel programming skills.
Enter the Intel(R) Threading Challenge 2010.
http://p.sf.net/sfu/intel-thread-sfd
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
