FW: PostGIS provider connection question
|
|
Orest Halustchak
|
Some javascript/style in this post has been disabled (why?)
Expanding the audience … From:
[hidden email]
[mailto:[hidden email]] On Behalf Of Jason Birch I don't have any real
objections to this, though I don't have a vested interest in the PostGIS
provider at this point. It would probably be best to go to the mapguide
and fdo users lists for feedback from real PostGIS users. The original design
decision was made because we had limited resources after moving away from the
initial decision to implement using the generic RDBMS framework. It was
easier not to support multiple databases (something about ListDataStores, a
lack of cross-database query capabilities in PostgreSQL, getting a bit fuzzy
now) and at the time I didn't have a requirement for multiple databases on the
same server, or cross-schema data access from a single connection. As a note, I believe
that best practice is not to store the PostGIS support in the public schema,
but in an alternate schema using a search path. I'm not entirely up to
date on this though. Jason From:
[hidden email]
[mailto:[hidden email]] On Behalf Of Orest
Halustchak Hi, Does anyone have any thoughts on
the topic below? Thanks, Orest. From:
[hidden email]
[mailto:[hidden email]] On Behalf Of Orest
Halustchak 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@server:port And the datastore to
The physical postgis schema So a typical connection would be Service :
MyPGDatabase@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. _______________________________________________ fdo-internals mailing list [hidden email] http://lists.osgeo.org/mailman/listinfo/fdo-internals _______________________________________________ fdo-users mailing list [hidden email] http://lists.osgeo.org/mailman/listinfo/fdo-users |
||||||||||||||||
Bruno Scott
|
We would have to take care of a couple of things.
First : backward compatibility with existing postGIS connection. I think it would be fairly easy to support both type of connection. Second : Actually, the provider caches the full schema, caching a full database could be a long process. Third : Naming convention for tables. PostGIS provider actually return only the table name whitout the schema. Supporting multi schema would meen to return a schema.table type of name. This may be another backard cimpatibility problems. Of course all these concerns have solutions:) Bruno |
||||||||||||||||
Mike Toews
|
In reply to this post
by Orest Halustchak
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:* [hidden email] > [mailto:[hidden email]] *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@server:port > > And the datastore to > > The physical postgis schema > > So a typical connection would be > > Service : MyPGDatabase@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. > _______________________________________________ fdo-users mailing list [hidden email] http://lists.osgeo.org/mailman/listinfo/fdo-users |
||||||||||||||||
|
Orest Halustchak
|
Hi Mike,
Thanks very much for your comments. As a summary, you're saying that we should fix major bugs first before this enhancement since there are ways to work around the connection to access other schemas. The bugs are the more important problem right now. Makes sense! Thanks, Orest. -----Original Message----- From: [hidden email] [mailto:[hidden email]] On Behalf Of Mike Toews Sent: Thursday, July 16, 2009 6:22 PM To: FDO Users Mail List Cc: [hidden email] Subject: Re: [fdo-users] FW: PostGIS provider connection question 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:* [hidden email] > [mailto:[hidden email]] *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@server:port > > And the datastore to > > The physical postgis schema > > So a typical connection would be > > Service : MyPGDatabase@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. > _______________________________________________ fdo-users mailing list [hidden email] http://lists.osgeo.org/mailman/listinfo/fdo-users _______________________________________________ fdo-users mailing list [hidden email] http://lists.osgeo.org/mailman/listinfo/fdo-users |
||||||||||||||||
| Free Embeddable Forum Powered by Nabble | Help |
