Hi Pierre, having proper support for multiple schemas is something that has been on the TODO list for a while. Now, a simple, although incomplete, solution would be to track for each feature type the origin schema, and use it to build queries, when the schema has not been provided to the store factory.
That however has a downside, that one cannot handle two same named tables in different namespaces. And no, we cannot use namespace as a way to track the schema, because that's a parameter provided from the outside, again to the store factory. Once provided, it must be honored, failing to do so would break backwards compatibility (and generally speaking, break a lot of existing GeoServer installations, where the namespace URI is always provided, and comes from the workspace configuration in which the store is found). Given that a Name is made of namespace URI and simple name only, there is no place where the schema can be put in a clean way. In a less clean way, the schema may become a prefix in the name only, with all the limits of a simple name built as "prefix:tableName" (potential conflicts if one starts using your separator of choice in the schema or table names). If not clean, this should at least be livable. If you go this way, the prefixing should be enabled with a new optional store parameter (w.g., schemaPrefix=true/false), again for backwards compatibility. A separate store is also an option, but everything in JDBCDataStore is final, to avoid the temptation to write custom database classes, so you'd be stuck having to replicate all the code... Anyone have any better ideas? Regards, Andrea Aime == GeoServer Professional Services from the experts! Visit http://bit.ly/gs-services-us for more information. == Ing. Andrea Aime @geowolf Technical Lead GeoSolutions Group phone: +39 0584 962313 fax: +39 0584 1660272 mob: +39 339 8844549 https://www.geosolutionsgroup.com/ http://twitter.com/geosolutions_it ------------------------------------------------------- Con riferimento alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 - Regolamento generale sulla protezione dei dati “GDPR”), si precisa che ogni circostanza inerente alla presente email (il suo contenuto, gli eventuali allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le sarei comunque grato se potesse darmene notizia. This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. We remind that - as provided by European Regulation 2016/679 “GDPR” - copying, dissemination or use of this e-mail or the information herein by anyone other than the intended recipient is prohibited. If you have received this email by mistake, please notify us immediately by telephone or e-mail On Wed, Mar 6, 2024 at 10:34 AM Pierre Mauduit < pierre.maud...@camptocamp.com> wrote: > Hello, > > I was willing to use the gt-postgis module to query a postgresql database > where the data are stored inside different schema, and was surprised by the > behaviour of the library in its current state. > > First, the module is able to find every relevant tables no matter the > (database) schema it is stored into, but then trying to access the returned > typenames lead to SQL query errors as it generally does not explicitely > target the expected database schema. > > Considering a database with the following structure (schema.table): > > * nongeo.flup > * fo.armoires > * "savoie"."73-Savoie" > > And the following pseudocode (it is actually valid groovy code): > > import org.geotools.api.data.DataStoreFinder > import org.geotools.api.data.Query > import org.geotools.feature.NameImpl > > def ds = DataStoreFinder.getDataStore([ > "dbtype": "postgis", > "host": "localhost", > "port": 5432, > "database": "mel", > "user": "pmauduit", > "passwd": "secret", > ]) > > > println ds.getTypeNames() > > ds.getTypeNames().each { > def fs = ds.getFeatureSource(it) > println "${fs.getCount(Query.ALL)} features in ${it}" > } > > > The call to getTypeNames() will return [armoires, flup, 73-savoie] which I > was expecting, meaning that the module was able to discover all the tables > of interest from my database, no matter the schema they are nested in. > > But then the call to getFeatureSource() in the loop will fail, because the > table is not in the search_path of my user, leading to the following > warnings/errors: > > WARNING: Failure occurred while looking up the primary key with finder: > org.geotools.jdbc.HeuristicPrimaryKeyFinder@4996c99 > org.postgresql.util.PSQLException: ERROR: relation "armoires" does not > exist > WARNING: Error occured determing srid for armoires.geom > [...] > org.postgresql.util.PSQLException: ERROR: relation "public.armoires" does > not exist > WARNING: Failed to retrieve information about public.armoires.geom by > examining the first sample geometry > org.postgresql.util.PSQLException: ERROR: relation "public.armoires" does > not exist > [...] > > I then skimmed into the codebase, looking for a way to have versions of > the classes that would be "schema-agnostic". I stumbled upon the > PostGisDialect class which looked as a good start, but was not sufficient > to circumvent my issue. So I began to relax some code enforcement (removing > final modifier on the JDBCDataStore class, copy/pasting code to get more > familiar with the codebase and how objects interact each other, ...), and > created a version of the objects (e.g. SchemaAwareJDBCDataStore, > SchemaAwareJDBCFeatureSource). I also stumbled upon the class being used to > represent typenames (NameImpl), and figured out it was mainly composed of a > namespaceURI, a separator and a localName, each being a String. So I > wondered why not considering the namespaceURI could be in our case the name > of the postgresql schema the table belongs to, even if it is not a "URI" > per se ? > > At this point, my current modifications are more of a PoC, but using a > custom parameter (schemaAware set to true in the params list), I could > switch to my custom version of the classes. As a result, using the > following pseudocode: > > def ds = DataStoreFinder.getDataStore([ > "dbtype": "postgis", > "host": "localhost", > "port": 5432, > "database": "mel", > "user": "pmauduit", > "passwd": "secret", > "schemaAware": true > ]) > > ds.getTypeNames().each { > def fs = ds.getFeatureSource(it) > println "${fs.getCount(Query.ALL)} features in ${it}" > > } > > will produce the following output: > > 56 features in fo.armoires > 4 features in nongeo.flup > 305 features in savoie.73-savoie > > But the getCount() is almost the only call working for now. > > To summarize, before getting further, here are some questions I am asking > myself and would have liked to share with you / have your inputs on it: > > * Is there any reason that I may have missed for the existing > implementation to react this way ? > * Am I on the right track with my approach and/or do you think I missed > some understandings on how the library is designed and works ? > * Would a custom JDBC datastore being able to be schema-agnostic of > interest for the project ? > > Thanks in advance for your inputs, > > Regards, > > -- > > Pierre Mauduit > > Ingénieur développement > > > Camptocamp France SAS 18 rue du Lac Saint André Savoie Technolac - > Bâtiment Le Dauphin F-73370 Le Bourget du Lac Tel. +33 (0)4 58 48 20 24 Fax > +33(0)4 58 48 20 10 www.camptocamp.com > _______________________________________________ > GeoTools-Devel mailing list > GeoTools-Devel@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/geotools-devel >
_______________________________________________ GeoTools-Devel mailing list GeoTools-Devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geotools-devel
