It looks like GeoPkgDialect should override encodeTableName
& encodeColumnName to handle this case and to force lower case names. Can
you raise a GeoTools ticket preferably with a test case or test data and
assign it to me.
Cheers
Ian
On 24 October 2017 at 09:46, Rahkonen Jukka (MML) <
jukka.rahko...@maanmittauslaitos.fi> wrote:
> Hi,
>
>
>
> Sorry, I just did not read your mail long enough for seeing that you
> already used –nln for getting rid of the dot in the name of the table.
>
>
>
> In principle using delimited identifiers everywhere would be the right
> solution. That would make a difference between
>
> "mosselenoesterhabitats.mosselenoesterhabitats" and
>
> "mosselenoesterhabitats”.”mosselenoesterhabitats"
>
>
>
> However, as you can see from https://en.wikibooks.org/wiki/
> SQL_Dialects_Reference/Data_structure_definition/Delimited_identifiers
> there are quite a lot differences in how delimited identifiers are handled
> by the database engines and SQLite is the most special of all. For example
> you can create a table "Mosselenoesterhabitats.Mosselenoesterhabitats" or
> "mosselenoesterhabitats.mosselenoesterhabitats" but not both because the
> names are the same for SQLite. This issue is recognized in the GeoPackage
> standard which states “All specified table and column name values SHALL be
> lowercase” but there are not similar rules about how to use characters with
> special meaning for SQL.
>
>
>
> As far as I can see all the SQL examples in the GeoPackage standard are
> using the simple, non-delimited syntax. I did not test and it may be that
> there will be no problems if for example gpkg_geometry_columns contain
> identifiers with dots in the name in columns table_name and column_name in
> queries like
>
> SELECT table_name, column_name FROM gpkg_geometry_columns WHERE table_name
> IN (SELECT table_name FROM gpkg_contents WHERE data_type == ‘features'));
>
>
>
> In any case such examples lead developers to use the similar SQL syntax in
> other places instead of the delimited version
>
> SELECT “table_name”, “column_name” FROM “gpkg_geometry_columns” WHERE
> “table_name” IN (SELECT “table_name” FROM “gpkg_contents” WHERE “data_type”
> == ‘features'));
>
>
>
> I am pretty sure that creating GeoPackages with dots and perhaps some
> other special characters in table and columns names means pegging for
> troubles when it comes to interoperability. Even if Geoserver would be
> patched to handle them right I would say that it would be better not to
> deliver such GeoPackages for other users outside your organization. That
> affects also Geoserver which in my mind should launder names like
> my.dot.column into my_dot_columns by default.
>
>
>
> -Jukka Rahkonen-
>
>
>
>
>
> *Lähettäjä:* Steggink, Frank [mailto:frank.stegg...@kadaster.nl]
> *Lähetetty:* 24. lokakuuta 2017 11:00
> *Vastaanottaja:* Rahkonen Jukka (MML) <jukka.rahko...@maanmittauslaitos.fi>;
> Andrea Aime <andrea.a...@geo-solutions.it>
> *Kopio:* geoserver-users@lists.sourceforge.net
> *Aihe:* RE: [Geoserver-users] Issues with Geopackage support?
>
>
>
> Hi Jukka,
>
>
>
> When attaching another database with the table mosselenoesterhabitats,
> then my query without the quotes succeeds. However, that is not a viable
> option for usage in geopackage, since a geopackage is supposed to be
> self-contained. If I were to add a connection to another database, I would
> need to add a second connection as well, because of the spatial index
> (rtree_mosselenoestehabitats.mosselenoesterhabitats_geom).
>
>
>
> I’ve already mentioned that I used -nln as a workaround. This is working
> fine. I’ll keep on using it for now. Wouldn’t it be better to support table
> names which contains dots? Apparently they are valid in SQLite. Querying a
> table name without dots surrounded by double quotes also works, for
> example: sqlite> select count(*) from "mosselenoesterhabitats";
>
>
>
> Regards,
>
>
> Frank Steggink
>
>
>
> *Van:* Rahkonen Jukka (MML) [mailto:jukka.rahko...@maanmittauslaitos.fi
> <jukka.rahko...@maanmittauslaitos.fi>]
> *Verzonden:* zondag 22 oktober 2017 22:29
> *Aan:* Andrea Aime; Steggink, Frank
> *CC:* geoserver-users@lists.sourceforge.net
> *Onderwerp:* Re: [Geoserver-users] Issues with Geopackage support?
>
>
>
> Hi Frank,
>
>
>
> I would test if table name without the dot would work better. Give some
> other name with -nln
>
>
>
> ogr2ogr -f GPKG mossel.gpkg "PG:dbname='#DB-name#' host='#Host-IP#'
> port=#Port# user='#User-name#' password='#Password#'"
> "mosselenoesterhabitats.mosselenoesterhabitats" -nln name_without_a_dot.
>
>
>
> The table name that you have requires double quotes in SQL. This tries to
> find table
>
> mosselenoesterhabitats from database mosselenoesterhabitats
>
> select * from mosselenoesterhabitats.mosselenoesterhabitats;
>
>
>
> SQL that works with your table would look like
>
> select * from "mosselenoesterhabitats.mosselenoesterhabitats";
>
>
>
>
>
> -Jukka Rahkonen-
>
>
> ------------------------------
>
> *Lähettäjä:* Andrea Aime <andrea.a...@geo-solutions.it>
> *Lähetetty:* 21. lokakuuta 2017 12:14
> *Vastaanottaja:* Steggink, Frank
> *Kopio:* geoserver-users@lists.sourceforge.net
> *Aihe:* Re: [Geoserver-users] Issues with Geopackage support?
>
>
>
> Hi Frank,
>
> at least part of the issues you're seeing seem to be related to running
> inside docker.
>
> Can you do tests on the native OS instead, to check if they are
> reproducable?
>
>
>
> As for a test file, yep, if you could make it available it would be nice,
> someone else could run tests too.
>
>
>
> Cheers
>
> Andrea
>
>
>
>
>
> On Fri, Oct 20, 2017 at 4:33 PM, Steggink, Frank <
> frank.stegg...@kadaster.nl> wrote:
>
> Hi,
>
>
>
> I’m investigating to serve Geopackages through Geoserver 2.12. We are
> using Docker, with Geoserver 2.12.0 and Tomcat 8.5.20. I just started
> experimenting with Geopackage, using Geoserver 2.11 and the Geopackage
> plugin, when Geoserver 2.12 was released with core support for Geopackage.
>
>
>
> With the old setup I was able to serve Geopackages, although the
> performance was disappointing, because Geotools version 17 didn’t support
> the spatial index from Geopackage. So I’m glad to notice that spatial index
> support was added to 2.12 as well (https://osgeo-org.atlassian.
> net/browse/GEOT-5826). Your release notes (http://blog.geoserver.org/
> 2017/10/17/geoserver-2-12-0-released/) seem to be outdated: “Currently,
> GeoPackage vector does not support spatial indexes but stay tuned, it’s
> cooking!”
>
>
>
> Anyways, it is possible that some of the issues I’m mentioning are due to
> our setup and because we’re just dipping our toes in the water when it
> comes to Geopackage. My apologies if that turns out to be the case,
> although I did make sure that this was not the case. I also decided to
> write this e-mail first, before setting out for Jira straight ahead.
>
>
>
> First of all, I’m having difficulties adding a new geopackage store. I’m
> adding a new datastore by just specifying the data source name and the
> database file (SQLite file), using the file browser. In the
> GEOSERVER_DATA_DIR (which is at /data in my container) I’ve created a
> subdirectory, also called “data”, which is mounted as a volume from the
> host into my Docker container. Because it is a subdirectory of the Geserver
> data dir, I’m getting the following relative path: file:data/mossel.gpkg.
> Since there are no other mandatory parameters (the namespace comes from my
> workspace), I just click “Save”, but the following error message appears:
> Error creating data store, check the parameters. Error message: Unable
> to obtain connection: Cannot create PoolableConnectionFactory (path to
> 'data/mossel.gpkg': '/usr/local/tomcat/data' does not exist). The directory
> /usr/local/tomcat is the Tomcat (Catalina) base dir. When creating a
> symbolic link from /usr/local/tomcat to /data/data (by executing the
> command ln -s /data/data from within /usr/local/tomcat), I’m not getting
> this error message anymore. So I’m fairly certain that this issue is not
> caused by my particular setup. My guess is that when the connection to the
> SQLite database is being set up, the relative path is not prefixed by the
> Geoserver data dir.
>
>
>
> My second issue occurs when I’m publishing the only layer which is
> contained in my geopackage. I’ve just calculated the bbox and WGS84 bbox,
> and press Save. When using WMS or WFS, my layer shows up in the
> Capabilities document. However, when executing a GetMap request, I’m
> getting a service exception:
>
>
>
> <?xml version="1.0" encoding="UTF-8"?><ServiceExceptionReport
> version="1.3.0" xmlns="http://www.opengis.net/ogc"; xmlns:xsi="
> http://www.w3.org/2001/XMLSchema-instance"; xsi:schemaLocation="http://
> www.opengis.net/ogc http://localhost:8080/geoserver/schemas/wms/1.3.0/
> exceptions_1_3_0.xsd"> <ServiceException code="internalError">
>
> Rendering process failed
>
> java.io.IOExceptionBorrow prepareStatement from pool failed
>
> [SQLITE_ERROR] SQL error or missing database (no such table:
> rtree_mosselenoesterhabitats.mosselenoesterhabitats_geom)
>
> </ServiceException></ServiceExceptionReport>
>
>
>
> My apologies for the unwieldy layer name. I’m getting a similar service
> exception when doing a WFS GetFeature request. When accessing my geopackage
> with the sqlite3 CLI I’m seeing this table (and also the main table) with
> the .tables command. But when doing a count on this table (or the main
> table), I’m also getting a “no such table” exception. I’m getting a count
> when enclosing the table name within double quotes, like: select count(*)
> from "rtree_mosselenoesterhabitats.mosselenoesterhabitats_geom". (The
> result is 577.)
>
>
>
> I have created this geopackage with OGR 2.2 (http://www.gdal.org/drv_
> geopackage.html) as an export of a PostGIS table using the following
> command: ogr2ogr -f GPKG mossel.gpkg "PG:dbname='#DB-name#'
> host='#Host-IP#' port=#Port# user='#User-name#' password='#Password#'"
> "mosselenoesterhabitats.mosselenoesterhabitats". In case anyone is
> interested, I can share this geopackage. As a workaround I recreated the
> geopackage by appending -nln mosselenoesterhabitats to my OGR command. I
> don’t care about the schema name.
>
>
>
> And lastly, I’ve also found a weird issue when putting my geopackage in
> the Geoserver data dir itself (/data, not mounted as a Docker volume), and
> adding it as a store. The database setting at the connection parameters is
> just “file:mossel.gpkg”. When clicking Save, I’m getting an overview of the
> layers I wish to publish, but it is showing no layers. I’m also not seeing
> any errors. When adding a new layer and selecting the geopackage store, I’m
> also getting an empty list of layers. I could provide a screenshot if
> necessary. I’m able to reproduce this issue on a Windows 7 machine with the
> standard Geoserver 2.12.0 installation, and putting the geopackage in the
> data dir. (Because the data dir is still in c:\program files (standard), I
> had to start Geoserver as administrator, because the JDBC driver needs
> write access to this dir.)
>
>
>
> Is anyone able to confirm these issues, and, if this is the case, what
> would be the best way to go forward? Next week I might be able to create
> some patches. Please let me know if more information is required (data,
> screenshots). With regard to my own work, there is no blocking issue
> because of the workarounds. They might be useful to someone else who is
> looking into serving Geopackage files with Geoserver.
>
>
>
> Thank you in advance,
>
>
>
> Frank Steggink
>
> Kadaster PDOK
>
>
>
> Disclaimer:
> De inhoud van dit bericht is uitsluitend bestemd voor geadresseerde.
> Gebruik van de inhoud van dit bericht door anderen zonder toestemming van
> het Kadaster
> is onrechtmatig. Mocht dit bericht ten onrechte bij u terecht komen, dan
> verzoeken wij u
> dit direct te melden aan de verzender en het bericht te vernietigen.
> Aan de inhoud van dit bericht kunnen geen rechten worden ontleend.
>
> Disclaimer:
> The content of this message is meant to be received by the addressee only.
> Use of the content of this message by anyone other than the addressee
> without the consent
> of the Kadaster is unlawful. If you have received this message, but are
> not the addressee,
> please contact the sender immediately and destroy the message.
> No rights can be derived from the content of this message.
>
>
> ------------------------------------------------------------
> ------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> _______________________________________________
> Geoserver-users mailing list
>
> Please make sure you read the following two resources before posting to
> this list:
> - Earning your support instead of buying it, but Ian Turton:
> http://www.ianturton.com/talks/foss4g.html#/
> - The GeoServer user list posting guidelines: http://geoserver.org/comm/
> userlist-guidelines.html
>
> Geoserver-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>
>
>
>
>
> --
>
> Regards,
>
> Andrea Aime
>
> ==
> GeoServer Professional Services from the experts! Visit
> http://goo.gl/it488V for more information.
> ==
>
> Ing. Andrea Aime
> @geowolf
> Technical Lead
>
> GeoSolutions S.A.S.
> Via di Montramito 3/A
> 55054 Massarosa (LU)
> phone: +39 0584 962313 <+39%200584%20962313>
> fax: +39 0584 1660272 <+39%200584%20166%200272>
> mob: +39 339 8844549 <+39%20339%20884%204549>
>
> http://www.geo-solutions.it
> http://twitter.com/geosolutions_it
>
> AVVERTENZE AI SENSI DEL D.Lgs. 196/2003
>
> Le informazioni contenute in questo messaggio di posta elettronica e/o
> nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
> loro utilizzo è consentito esclusivamente al destinatario del messaggio,
> per le finalità indicate nel messaggio stesso. Qualora riceviate questo
> messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
> darcene notizia via e-mail e di procedere alla distruzione del messaggio
> stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
> divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
> utilizzarlo per finalità diverse, costituisce comportamento contrario ai
> principi dettati dal D.Lgs. 196/2003.
>
> The information in this message and/or attachments, is intended solely for
> the attention and use of the named addressee(s) and may be confidential or
> proprietary in nature or covered by the provisions of privacy act
> (Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
> Code).Any use not in accord with its purpose, any disclosure, reproduction,
> copying, distribution, or either dissemination, either whole or partial, is
> strictly forbidden except previous formal approval of the named
> addressee(s). If you are not the intended recipient, please contact
> immediately the sender by telephone, fax or e-mail and delete the
> information in this message that has been received in error. The sender
> does not give any warranty or accept liability as the content, accuracy or
> completeness of sent messages and accepts no responsibility for changes
> made after they were sent or for other risks which arise as a result of
> e-mail transmission, viruses, etc.
>
>
>
>
>
> Disclaimer:
> De inhoud van dit bericht is uitsluitend bestemd voor geadresseerde.
> Gebruik van de inhoud van dit bericht door anderen zonder toestemming van
> het Kadaster
> is onrechtmatig. Mocht dit bericht ten onrechte bij u terecht komen, dan
> verzoeken wij u
> dit direct te melden aan de verzender en het bericht te vernietigen.
> Aan de inhoud van dit bericht kunnen geen rechten worden ontleend.
>
> Disclaimer:
> The content of this message is meant to be received by the addressee only.
> Use of the content of this message by anyone other than the addressee
> without the consent
> of the Kadaster is unlawful. If you have received this message, but are
> not the addressee,
> please contact the sender immediately and destroy the message.
> No rights can be derived from the content of this message.
>
> ------------------------------------------------------------
> ------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> _______________________________________________
> Geoserver-users mailing list
>
> Please make sure you read the following two resources before posting to
> this list:
> - Earning your support instead of buying it, but Ian Turton:
> http://www.ianturton.com/talks/foss4g.html#/
> - The GeoServer user list posting guidelines: http://geoserver.org/comm/
> userlist-guidelines.html
>
> Geoserver-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>
>
--
Ian Turton
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Geoserver-users mailing list
Please make sure you read the following two resources before posting to this
list:
- Earning your support instead of buying it, but Ian Turton:
http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines:
http://geoserver.org/comm/userlist-guidelines.html
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
