[mapserver-users] Hacking Mappostgis.c to allow Views as Mapserver Data Source

[Giorgio Volpe]

> Sean, 
> 
>         Thanks a lot for your comments and work on the mappostgis.c
> connector!   
> Its always great to see someone contributing!
> 
> Sean Gillies wrote:
> > 1. Querying a unique row ID called "URID" instead of the Postgres
> >     OID.
> 
> The reason why I used OID was because it is always available in every
> table, and is always unique.  Relying on a column called "URID" to
> exist,
> be an integer, and be unique is a pretty hefty assumption. Plus it 
> requires people to manually add this column to every table.

I'm a new mapserver user, older user of postgres.

I think it's very usefull the possibility of using a key field different
from oid, normally a relational db programmer defines a "primary key"
for quite every table, and a primary key is for definition unique (not
integer ... ok). The most usefull think is that postgres automatically
creates an index over a primary key field so access is faster when
filtering over that field ( if you want index over oid you must create
it by yourself).

You create primary key simply writing

CREATE TABLE table_name (key integer PRIMARY KEY, other_field
text,other_field2 text);

So i would like to suggest the possibilty of using a primary key field
if it exists in the db (may be controlling wheter it is integer or not).
(From postgres 7.x oid column can be disabled so it's no more sure that
oid column exists!)

The simplest way is the one you suggested, that is let user specify
which column to use.
It's also possible to query system table to discover if our table has a
primary key but this is quite complicated expecially for views!

For normal table the following query return all integer attributes
(column name) that are primary keys (always returns 0 record for views):

select attname from pg_type,pg_index,pg_attribute,pg_class where
pg_type.oid = atttypid and indrelid=attrelid and indkey[0]=attnum and
indkey[1]=0 and indisunique and pg_class.oid = attrelid and attnum > 0
and typname in ('int2','int4','int8','integer') and
relname='table_name';

( indkey[1]=0 to discard multicolumn keys )

Obviously a column can be a "unique integer identifier" also if it's not
defined as a primary key in the db .. but only users nows it!

Finally the strategy for defining SRID coud be:
1) if user suggest the column use it
2) Otherwise look for an integer primary key, if found use it
3) Use oid (may be control if oid exist or raise an error!)

(select attname from pg_attribute,pg_class where pg_class.oid =
attrelid  and attname='oid' and relname='p'; return a record only if oid
exist)

An other suggestion: to get all field's names (and their types) of a
table you can use the following query:

select attname,typname from pg_class,pg_type,pg_attribute where
pg_class.oid = attrelid and  pg_type.oid = atttypid  and attnum > 0 and
relname='table_name';

more simple of "verbouse explain" and valid also for views ...

PS: can someone send me the path for using "USING UNIQUE ID urid"?
Thanks a lot!
-- 

    Giorgio

-----------------------------------------