[fdo-users] FW: PostGIS provider connection question

[Mike Toews]

Hi Orest,

I've been working with the provider for about a year now using Map3D 
2008 (soon 2010) and PosgreSQL 8.3/PostGIS 1.3.4 on an Ubuntu server. 
Over the course of this period I've experienced many incompatibilities 
with my schema and what we can implement in a production environment. 
I've submitted many bug reports for these, including the use of multiple 
geometry columns and using array data types. Several of these are fixed 
(Thanks!).

To overcome the incompatibilities with the FDO provider, I have found 
that I needed to isolate all data for FDO into it's own schema, and keep 
it separate from the main data. So I have a main schema for SQL work, 
QGIS, ODBC/MS Access, etc. and an fdo schema for the FDO provider 
(only), which is a subset of the main schema. I've even developed local 
replication triggers (set up using a PL/Python script) which replicates 
tables in both schemas (I can't use views with rules, since this is also 
a bug). This way, edits to data in either schemas are replicated.

This setup was a lot of work, is fragile to schema changes, and I'd 
rather use a single schema, just like the other client apps. Much of the 
recent work in the FDO provider will hopefully allow me to forget about 
the second replicated schema altogether, and connect/use the main schema.

Bruno's second comment about caching the schema is a bit of a concern 
for me. I have 10 schemas (three which are replications for fdo), with 
about 250 tables total (some of which are locally replicated between two 
schemas) and about 30 views. Also, Bruno's third comment about naming 
conventions is also a concern, since I have similarly named tables in 
different schemas, regardless if they are locally replicated between two 
schemas. I will have many many conflicts.

I don't see any limitations using the datastore to define the schema, as 
is presently done in the provider. This limits the scope of caching, 
which is good. I can always create additional FDO connections to other 
datastores if I need to get data from other schemas. Or I can create a 
cross-schema view if it is read-only.

I think I'd rather have development go toward fixing the current issues 
and bugs with the provider so that it is up to par with the capabilities 
of other PostgreSQL client software, such as ODBC and QGIS.

-Mike

Orest Halustchak wrote:
>
> *From:* fdo-internals-bounces at lists.osgeo.org 
> [mailto:fdo-internals-bounces at lists.osgeo.org] *On Behalf Of *Orest 
> Halustchak
> *Sent:* Friday, June 26, 2009 11:06 AM
> *To:* FDO Internals Mail List
> *Subject:* [fdo-internals] PostGIS provider connection question
>
> Hi,
>
> I have a concern about the current connection parameters that the FDO 
> PostGIS provider is using.
>
> The current situation is this.
>
> To make a connection to PostGIS we need to specify a service and a 
> datastore.
>
> The service is mapped to
>
> database at server:port
>
> And the datastore to
>
> The physical postgis schema
>
> So a typical connection would be
>
> Service : MyPGDatabase at MyServer:5432
>
> Datastore : public
>
> But a PostGIS server can contain multiple databases with each database 
> containing multiple schemas, which contain the tables. My question is 
> why wouldn’t we set up a connection mapping that exposes the PostGIS 
> databases as FDO datastores and the PostGIS schemas as FDO schema, 
> similar to how we do it with SQL Server, which has similar concepts.
>
> So, connection information would be:
>
> Service: server:port, e.g. MyServer:5432
>
> Datastore: PG database name, e.g. MyPGDatabase
>
> Then, within that datastore, we have schemas called ‘public’ and any 
> other schemas that the user has created there, such as “landbase”, 
> “transportation”, “utilities”, etc.
>
> I realize that this has already been implemented and changing this 
> could have some backwards compatibility issues with applications and 
> MG resource definitions. But, maybe the benefits of having a better 
> datastore / schema mapping to PG database / schema would be worth 
> figuring out how to deal with the compatibility issues?
>
> What do you think?
>
> Thanks,
>
> Orest.
>