[fdo-internals] RE: [mapguide-internals] FDO RFC 43 - Standard
FDO Class Naming Conventions
Brent Robinson
brent.robinson at autodesk.com
Mon Nov 2 15:32:08 EST 2009
See inline, ([BR] heading).
From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Zac Spitzer
Sent: Thursday, October 29, 2009 8:31 PM
To: FDO Internals Mail List
Subject: Re: [fdo-internals] RE: [mapguide-internals] FDO RFC 43 - Standard FDO Class Naming Conventions
On Fri, Oct 30, 2009 at 1:10 AM, Brent Robinson <brent.robinson at autodesk.com<mailto:brent.robinson at autodesk.com>> wrote:
Hi Zac,
I had a look at RFC 43 and had a few questions regarding the MySQL provider:
1. Do the naming conventions apply only to MySQL databases that do not have FDO MetaData?
FDO meta data describes a secondary set, I would assume the underlying tables would be always
exposed in the same fashion..
[BR] - The metadata also describes the mappings between tables and FDO Schemas and classes, so it would affect how the tables are exposed. These mappings should continue to be supported if any users are taking advantage of them.
2. For the statement:
"MySql? returns schema:tablename or if connected as "schema" then just tablename"
Is "schema" equivalent to a MySQL database?
That was my intention, I rarely use MySQL myself.. similiar to the oracle n sql server discussions
3. For the case where the tables are exposed as in an empty schema, would that mean that the DescribeSchema command would return a Feature Schema with blank name? Or would there be a special name to represent the empty schema (e.g. "default" or "none").
thats a really good question! We would want it describable if it's accessible, blank doesn't seem quite right,
but it does imply the default or none or current...
[BR] - Agreed. Having a special name for the empty schema would be preferable to blank.
the unqualified name is a synonym for current_user.table_name
4. What sort of problems are you currently encountering without these naming conventions being in place?
As jackie mentioned at the top to the thread, the same data loaded under a different provider appears different
and thus cannot be just accessed without provider specific knowledge and coding workarounds
a. Problems copying schemas and features between providers?
yes
b. Problems generating statements for the ISQLCommand?
yes
and a general question: are the conventions intended as standards for all DBMS or file base providers or just the ones mentioned in the RFC?
It is intended as a standard for all providers, I have updated the RFC to reflect that
[BR] - These are good for the more general providers but applying as a standard for all providers might be too restrictive in some cases. At one site I worked at in the past, all of the production data was in a single Oracle user. Tables were grouped into schemas where table name was essentially:
<schema_name>_<class_name>
In this case, they would probably prefer to expose the water_pipe and sewer_pipe tables as water:pipe and sewer:pipe, rather than default:water_pipe and default:sewer_pipe. On the downside, this may expose them to the problems that you and Jack encountered, but I still think they should have the latitude to make these design trade-offs.
If the answer to question 2 is yes then the MySQL provider is currently close. The feature schema name is:
FDO<database_name>
FDO is prepended to the database name to handle a particular use case which, in hindsight, is not likely very common (I'll describe the use case details later). This prefix could therefore be dropped from the feature schema name.
Exposing tables in the current database in an empty schema sounds reasonable conceptually. Some users might not necessarily separate each logical schema into a different MySQL database; a database might simply have a collection of tables not tied to a particular schema. I have a few implementation concerns but I'll wait for the answer to question 3.
Switching to the SQLServerSpatial provider, when the database has no FDO Metadata, each SQL Server schema is presented as an FDO Feature Schema. No special handling is done for the dbo schema. I was curious as to why the dbo schema would be non-portable. It would be an unusual name for a logical schema but could be copied to a provider, such as SDF, without having to rename it.
dbo implies the unqualified schema naming and is meaningless out side sql server..
[BR] - At the SQL Server level, the default schema can be set per-user. If connected as a user with "land" as the default schema, then
select * from parcel;
would go against the land.parcel table, rather than dbo.parcel.
If the unqualified schema naming at the FDO level is suppose to mirror what happens at the user level, I'd be hesitant to introduce it into the SQLServerSpatial provider since the corresponding FDO schema name for a table could differ depending on which user connects.
ie a database called dbo on mysql or an oracle user called dbo
[BR] -it is possible to create both. If a dbo schema is copied to mysql or oracle, changing the name would be problematic . Since dbo is not always the default schema, there would not be an obvious name to change it to.
challenging problem
Getting back to the user case mentioned above, it would be the case where the schema is copied from a MySQL database without FDO MetaData to a database with Metadata, where the two databases have the same name but are in different servers. The FDO-enabled databases have a special feature schema (with same name as the database) which stores the datastore descriptions. This schema is not directly modifiable so the copy would fail if the feature schema being copied is named after the source database. To handle this, the FDO prefix was added to the schema name. However, as mentioned above, this use case is not likely very common.
If you are using meta data, maybe turning it around and exposing metadata under FDO, also not very pretty
Brent.
From: fdo-internals-bounces at lists.osgeo.org<mailto:fdo-internals-bounces at lists.osgeo.org> [mailto:fdo-internals-bounces at lists.osgeo.org<mailto:fdo-internals-bounces at lists.osgeo.org>] On Behalf Of Zac Spitzer
Sent: Wednesday, October 28, 2009 10:28 PM
To: FDO Internals Mail List
Subject: Re: [fdo-internals] RE: [mapguide-internals] FDO RFC 43 - Standard FDO Class Naming Conventions
would introducing the concept of a default schema resolve the issue of multiple
tables having the same name in different schemas? ie GetDefaultSchema()
Sorry about my confusion about the datastore stuff, my FDO experience
is predominately from working with MapGuide which doesn't expose them.
Datastores (databases/instances) also are potentially named differently,
where as a schema tends to be more consistant.
If we introduce datastores into FDO Class names I fear things will become
even more inconsistent. It's easy enough to change the name of a schema,
but renaming a database is a much bigger change.
As I described in the Proposed Test case:
"Expose the Sheboygan dataset via FDO matching the standard conventions"
is all about achieving the goal of making FDO provider agnostic
SQL server does present a challenge here with it's three levels, but it if it
has schema shouldn't we present them as schema's? well no because then
we will be oft returning dbo which is completely non portable.
z
On Thu, Oct 29, 2009 at 12:21 PM, Zac Spitzer <zac.spitzer at gmail.com<mailto:zac.spitzer at gmail.com>> wrote:
sounds like another RFC ?
On Thu, Oct 29, 2009 at 1:04 AM, Orest Halustchak <orest.halustchak at autodesk.com<mailto:orest.halustchak at autodesk.com>> wrote:
Hi,
I'd also like to add a comment about something that Haris said in a separate reply.
> Perhaps, providers could return additional info about names of newly created classes.
I think that's a good idea, maybe a way for ApplySchema to return information about anything that was changed from the user's input.
Thanks,
Orest.
From: fdo-internals-bounces at lists.osgeo.org<mailto:fdo-internals-bounces at lists.osgeo.org> [mailto:fdo-internals-bounces at lists.osgeo.org<mailto:fdo-internals-bounces at lists.osgeo.org>] On Behalf Of Orest Halustchak
Sent: Wednesday, October 28, 2009 9:39 AM
To: FDO Internals Mail List
Subject: RE: [fdo-internals] RE: [mapguide-internals] FDO RFC 43 - Standard FDO Class Naming Conventions
Hi Zac,
FDO does have a concept of datastore. There are FDO commands to create, destroy, and list datastores. A datastore is also what you open with the connection command. It plays a role. My example is meant to show a case of two separate FDO datastores that for an rdbms are managed by one rdbms server.
Thanks,
Orest.
From: fdo-internals-bounces at lists.osgeo.org<mailto:fdo-internals-bounces at lists.osgeo.org> [mailto:fdo-internals-bounces at lists.osgeo.org<mailto:fdo-internals-bounces at lists.osgeo.org>] On Behalf Of Zac Spitzer
Sent: Wednesday, October 28, 2009 9:15 AM
To: FDO Internals Mail List
Subject: Re: [fdo-internals] RE: [mapguide-internals] FDO RFC 43 - Standard FDO Class Naming Conventions
inline
On Wed, Oct 28, 2009 at 11:56 PM, Orest Halustchak <orest.halustchak at autodesk.com<mailto:orest.halustchak at autodesk.com>> wrote:
Hi,
Note that there is another qualifier that should be taken into account in these discussions and that is the datastore name. There are really three levels of qualification: datastore name, schema name, class name. So, cases where it is suggested to use the file name as the schema name, if the file name is already used for the datastore name, it doesn't have to be used again for the schema name.
My understanding is that FDO has no concept of datastore, it starts with schema's
datastore is defined at the FDO connection level, why squeeze three levels into
a two tier structure?
An issue with rdbms is that some such as SQL Server include the three levels of database, schema, and table. Others such as Oracle do not. So in the Oracle case if we map the datastore name to the Oracle user/schema, then we should not also use Oracle user/schema for FDO schema.
Consider this example.
FDO Datastore = CityOfNewYork
FDO Schema = LandBase
Class = Parcel
Class = Road
FDO Schema = Water
Class = Pipe
Class = Valve
FDO Schema = Gas
Class = Pipe
FDO Datastore = CityOfAlbany
Same schemas and classes
We don't want to mix up the parcels from CityOfNewYork with the parcles from CityOfAlbany, those should be in separate tables. Having the Oracle schema name as LandBase, Water, and Gas doesn't handle this.
but that is a problem already solved when you follow the other way other
database access layers work????
based on the connection details, either by specifying a datastore, or not
Pipe would only be accessible if it was the default for the user or the for
specified datastore. There is a implied concept of the default schema?
if there's no default and two schemas means there are no schema less tables
to enumerate
This is how databases normally work in my experience which is all I'm trying
to suggest with this RFC.
Also, about the ~ColumnName convention being used for geometry columns, note that FDO does allow more than one geometry column per class with one flagged as the main geometry. I wouldn't want to require that these have to be exposed as separate feature classes.
Thanks,
Orest.
-----Original Message-----
From: fdo-internals-bounces at lists.osgeo.org<mailto:fdo-internals-bounces at lists.osgeo.org> [mailto:fdo-internals-bounces at lists.osgeo.org<mailto:fdo-internals-bounces at lists.osgeo.org>] On Behalf Of Haris Kurtagic
Sent: Wednesday, October 28, 2009 6:07 AM
To: 'FDO Internals Mail List'
Subject: [fdo-internals] RE: [mapguide-internals] FDO RFC 43 - Standard FDO Class Naming Conventions
there is another important case which is not covered in RFC.
Same table name in multiple schemas. Also, how to not mess up when users
logs using different credentials.
One way to solve that could be to always use rdbms schema name as fdo schema
name.
I believe it is absolutely necessary that application using FDO to access
rdbms can be sure exactly which table and column it is accessing.
Because what FDO provider sees when connects to rdbms can change regarding
which credentials are used to connect to rdbms. That menas that on same
database it can see only one table, or two tables (same name) in two schemas
or even one or more geometry columns in table.
I am not keen on current naming in King.Oracle with this ~ sign, but I am
very pleased that when using King.Oracle and FDO class trough it, I know
every time exactly which Oracle schema, table and column I am hitting.
Just when wanted to finish something else from RFC:
"Class names for databases are normally tablename, unless qualified which
then has ~COLUMN_NAME appended. (if the are multiple geometries, FDO
requires to know which one is the identifier)"
In King.Oracle ~COLUMN_NAME is appened to make sure resulting name would be
unique. Which actuall oracle column is used is not parsed from name of class
but it is kept inside provider. ~COLUMN_NAME could be anything.
Haris
-----Original Message-----
From: mapguide-internals-bounces at lists.osgeo.org<mailto:mapguide-internals-bounces at lists.osgeo.org>
[mailto:mapguide-internals-bounces at lists.osgeo.org<mailto:mapguide-internals-bounces at lists.osgeo.org>] On Behalf Of Zac Spitzer
Sent: Wednesday, October 28, 2009 9:45 AM
To: FDO Internals Mail List; MapGuide Internals Mail List
Subject: [mapguide-internals] FDO RFC 43 - Standard FDO Class Naming
Conventions
I have posted RFC 43 for review
http://trac.osgeo.org/fdo/wiki/FDORfc43
--
Zac Spitzer -
http://zacster.blogspot.com
+61 405 847 168
_______________________________________________
mapguide-internals mailing list
mapguide-internals at lists.osgeo.org<mailto:mapguide-internals at lists.osgeo.org>
http://lists.osgeo.org/mailman/listinfo/mapguide-internals
_______________________________________________
fdo-internals mailing list
fdo-internals at lists.osgeo.org<mailto:fdo-internals at lists.osgeo.org>
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
fdo-internals at lists.osgeo.org<mailto:fdo-internals at lists.osgeo.org>
http://lists.osgeo.org/mailman/listinfo/fdo-internals
--
Zac Spitzer -
http://zacster.blogspot.com
+61 405 847 168
_______________________________________________
fdo-internals mailing list
fdo-internals at lists.osgeo.org<mailto:fdo-internals at lists.osgeo.org>
http://lists.osgeo.org/mailman/listinfo/fdo-internals
--
Zac Spitzer -
http://zacster.blogspot.com
+61 405 847 168
--
Zac Spitzer -
http://zacster.blogspot.com
+61 405 847 168
_______________________________________________
fdo-internals mailing list
fdo-internals at lists.osgeo.org<mailto:fdo-internals at lists.osgeo.org>
http://lists.osgeo.org/mailman/listinfo/fdo-internals
--
Zac Spitzer -
http://zacster.blogspot.com
+61 405 847 168
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/fdo-internals/attachments/20091102/c4dce62c/attachment-0001.html
More information about the fdo-internals
mailing list