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
[Geotools-devel] JDBC stores hitting multiple schemas
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
