Re: [Geotools-devel] JDBC stores hitting multiple schemas
Yeah, i am thinking just names that the SDE approach is the best way to go. I know the names are ugly but at least in the geoserver case the user can set a different name if they want to. I guess the only issue is code (mostly in the dialect) that takes schema as a parameter. I guess the datastore will have to do quite a few checks for which mode is in play, and split the qualified table names accordingly. On Thu, Jan 12, 2012 at 8:10 AM, Andrea Aime wrote: > Hi, > I'm looking into some options to have a JDBCDataStore work against multiple > schemas at the same time without troubles. > > Right now the code works two ways: > - if you specify a schema during the store setup, we work against tables > of that >schema and we're fine > - if you don't the list of possible feature types contains a list of > tables from > multiple schemas, and the table names are never schema qualified when we > do queries (which means, the table we actually pick is a matter of luck) > > The second case is trouble. At first I was thinking about the possiblity > of specifying > the schema as a "sidecar" information with feature types, but there is a > fundamental > problem: if two schemas have the same table there must be a way for the > user to > identify which table is coming from which schema, and point at the right > one. > > Name is not an option, since that is for namespace qualification, not > schema qualification, > besides, most of the client code is not really ready ot use Name instead > of a plain string. > > I guess a better option is to return names that do contain the schema, > pretty much > like the SDE store does (afaik), that is, schema.table. > > The way I'm picturing it is that the code would return plain table names > if you specify > the schema, and schema qualified names otherwise. > And maybe be a bit tolerant if an unqualified name comes in, if there is > just a single > table with that matches that name accept the short name as if it was > schema qualified > (think of someone changing the configuration of the store from schema > specified > to schema-less, all the table names would suddenly change). > > What other approaches could we follow? What problems do you foresee? > > Cheers > Andrea > > -- > --- > Ing. Andrea Aime > GeoSolutions S.A.S. > Tech lead > > Via Poggio alle Viti 1187 > 55054 Massarosa (LU) > Italy > > phone: +39 0584 962313 > fax: +39 0584 962313 > mob:+39 339 8844549 > > http://www.geo-solutions.it > http://geo-solutions.blogspot.com/ > http://www.youtube.com/user/GeoSolutionsIT > http://www.linkedin.com/in/andreaaime > http://twitter.com/geowolf > > --- > > > -- > RSA(R) Conference 2012 > Mar 27 - Feb 2 > Save $400 by Jan. 27 > Register now! > http://p.sf.net/sfu/rsa-sfdev2dev2 > ___ > Geotools-devel mailing list > Geotools-devel@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/geotools-devel > > -- Justin Deoliveira OpenGeo - http://opengeo.org Enterprise support for open source geospatial. -- RSA(R) Conference 2012 Mar 27 - Feb 2 Save $400 by Jan. 27 Register now! http://p.sf.net/sfu/rsa-sfdev2dev2___ Geotools-devel mailing list Geotools-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geotools-devel
Re: [Geotools-devel] JDBC stores hitting multiple schemas
Zitat von Andrea Aime : > On Thu, Jan 12, 2012 at 4:44 PM, wrote: > >> Oh yes, I know the problem. Creating a db2 data store without a schema >> lists a ton of tables including the whole system catalog. >> > > You know you can setup table name filters in the sql dialect? > That's how we hide most of the system tables in Oracle for example. > Uuphs, I should know, shame on me :-) > >> >> I would prefer a more precise solution, but I am not sure if it is >> possible. >> >> 1) We always use schema.table, because this it the unique name, comparable >> to a java class name including the package name. >> > > This may backwards compatibility issues, what happens when someone using > uDig, GeoServer or another > application that mantains a configuration of its own referencing the old > name? > But I otherwise like the direction. See answer of 3) > > >> >> 2) If a data store has a schema specified, we use the schema name only as >> a filter for presenting tables/views to the user. >> > > Ok > > >> >> 3) For already existing features using a table name without schema, we >> should complete the name when reading the configuration file. Future >> features should always use schema.table. >> > > How do we complete the name? All applications should have to roll new code > to handle the switch? This would be really annoying towards users. > That's why I was suggesting to be lenient, if we get a non qualified name > try to match it > internally to a qualified one > I am asking myself the following. If you connect to db and create a table without specifying a schema, in which schema is the table created. DB2, as an example, uses the userid as schema. I think there are similar mechanisms for other databases. There must be something like a default schema. As a consequence, if we have no explicit schema, we could use the default schema. How does Oracle and Postgres ? Cheers Christian > Another possiblity would be to have a configuration flag specifying whether > to use qualified > or unqualified names > > Cheers > Andrea > > -- > --- > Ing. Andrea Aime > GeoSolutions S.A.S. > Tech lead > > Via Poggio alle Viti 1187 > 55054 Massarosa (LU) > Italy > > phone: +39 0584 962313 > fax: +39 0584 962313 > mob:+39 339 8844549 > > http://www.geo-solutions.it > http://geo-solutions.blogspot.com/ > http://www.youtube.com/user/GeoSolutionsIT > http://www.linkedin.com/in/andreaaime > http://twitter.com/geowolf > > --- > This message was sent using IMP, the Internet Messaging Program. -- RSA(R) Conference 2012 Mar 27 - Feb 2 Save $400 by Jan. 27 Register now! http://p.sf.net/sfu/rsa-sfdev2dev2 ___ Geotools-devel mailing list Geotools-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geotools-devel
Re: [Geotools-devel] JDBC stores hitting multiple schemas
On Thu, Jan 12, 2012 at 4:44 PM, wrote: > Oh yes, I know the problem. Creating a db2 data store without a schema > lists a ton of tables including the whole system catalog. > You know you can setup table name filters in the sql dialect? That's how we hide most of the system tables in Oracle for example. > > I would prefer a more precise solution, but I am not sure if it is > possible. > > 1) We always use schema.table, because this it the unique name, comparable > to a java class name including the package name. > This may backwards compatibility issues, what happens when someone using uDig, GeoServer or another application that mantains a configuration of its own referencing the old name? But I otherwise like the direction. > > 2) If a data store has a schema specified, we use the schema name only as > a filter for presenting tables/views to the user. > Ok > > 3) For already existing features using a table name without schema, we > should complete the name when reading the configuration file. Future > features should always use schema.table. > How do we complete the name? All applications should have to roll new code to handle the switch? This would be really annoying towards users. That's why I was suggesting to be lenient, if we get a non qualified name try to match it internally to a qualified one Another possiblity would be to have a configuration flag specifying whether to use qualified or unqualified names Cheers Andrea -- --- Ing. Andrea Aime GeoSolutions S.A.S. Tech lead Via Poggio alle Viti 1187 55054 Massarosa (LU) Italy phone: +39 0584 962313 fax: +39 0584 962313 mob:+39 339 8844549 http://www.geo-solutions.it http://geo-solutions.blogspot.com/ http://www.youtube.com/user/GeoSolutionsIT http://www.linkedin.com/in/andreaaime http://twitter.com/geowolf --- -- RSA(R) Conference 2012 Mar 27 - Feb 2 Save $400 by Jan. 27 Register now! http://p.sf.net/sfu/rsa-sfdev2dev2___ Geotools-devel mailing list Geotools-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geotools-devel
Re: [Geotools-devel] JDBC stores hitting multiple schemas
Oh yes, I know the problem. Creating a db2 data store without a schema lists a ton of tables including the whole system catalog. I would prefer a more precise solution, but I am not sure if it is possible. 1) We always use schema.table, because this it the unique name, comparable to a java class name including the package name. 2) If a data store has a schema specified, we use the schema name only as a filter for presenting tables/views to the user. 3) For already existing features using a table name without schema, we should complete the name when reading the configuration file. Future features should always use schema.table. As already mentioned, I am not sure if it is possible, but it would be straight forward. Christian Zitat von Andrea Aime : > Hi, > I'm looking into some options to have a JDBCDataStore work against multiple > schemas at the same time without troubles. > > Right now the code works two ways: > - if you specify a schema during the store setup, we work against tables of > that >schema and we're fine > - if you don't the list of possible feature types contains a list of tables > from > multiple schemas, and the table names are never schema qualified when we > do queries (which means, the table we actually pick is a matter of luck) > > The second case is trouble. At first I was thinking about the possiblity of > specifying > the schema as a "sidecar" information with feature types, but there is a > fundamental > problem: if two schemas have the same table there must be a way for the > user to > identify which table is coming from which schema, and point at the right > one. > > Name is not an option, since that is for namespace qualification, not > schema qualification, > besides, most of the client code is not really ready ot use Name instead of > a plain string. > > I guess a better option is to return names that do contain the schema, > pretty much > like the SDE store does (afaik), that is, schema.table. > > The way I'm picturing it is that the code would return plain table names if > you specify > the schema, and schema qualified names otherwise. > And maybe be a bit tolerant if an unqualified name comes in, if there is > just a single > table with that matches that name accept the short name as if it was schema > qualified > (think of someone changing the configuration of the store from schema > specified > to schema-less, all the table names would suddenly change). > > What other approaches could we follow? What problems do you foresee? > > Cheers > Andrea > > -- > --- > Ing. Andrea Aime > GeoSolutions S.A.S. > Tech lead > > Via Poggio alle Viti 1187 > 55054 Massarosa (LU) > Italy > > phone: +39 0584 962313 > fax: +39 0584 962313 > mob:+39 339 8844549 > > http://www.geo-solutions.it > http://geo-solutions.blogspot.com/ > http://www.youtube.com/user/GeoSolutionsIT > http://www.linkedin.com/in/andreaaime > http://twitter.com/geowolf > > --- > This message was sent using IMP, the Internet Messaging Program. -- RSA(R) Conference 2012 Mar 27 - Feb 2 Save $400 by Jan. 27 Register now! http://p.sf.net/sfu/rsa-sfdev2dev2 ___ Geotools-devel mailing list Geotools-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geotools-devel
