[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
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 andrea.a...@geo-solutions.it: 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
Re: [Geotools-devel] JDBC stores hitting multiple schemas
On Thu, Jan 12, 2012 at 4:44 PM, christian.muel...@nvoe.at 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
