Possible bug in h2gis-geotools

classic Classic list List threaded Threaded
4 messages Options
rsn
Reply | Threaded
Open this post in threaded view
|

Possible bug in h2gis-geotools

rsn
hi there,

after the change of licensing to H2GIS --thanks BTW. for doing that-- i'm trying to use it as well as h2gis-geotools and h2gis-geoserver in my (commercial) application.  however i'm getting an exception when, through the gt-jdbc, version 14.5, JDBCDataStore#createSchema method this statement (line #755) is executed:

  dialect.postCreateTable(databaseSchema, featureType, cx);

i tracked it down to line #530 in H2GISDialect#postCreateTable method

  st.execute(sql);

the generated SQL statement being executed and causing the exception looks like so:

  ALTER TABLE "PUBLIC"."P1508632L2" ALTER COLUMN "the_geom" TYPE geometry (MULTIPOLYGON, 4326);

the exception message (and stack trace) follows:

INFO   | jvm 1    | 2017/08/24 11:09:31 | Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "ALTER TABLE ""PUBLIC"".""P1508632L2"" ALTER COLUMN ""the_geom"" TYPE GEOMETRY (MULTIPOLYGON[*], 4326); "; expected "long"; SQL statement:
INFO   | jvm 1    | 2017/08/24 11:09:31 | ALTER TABLE "PUBLIC"."P1508632L2" ALTER COLUMN "the_geom" TYPE geometry (MULTIPOLYGON, 4326); [42001-193]
INFO   | jvm 1    | 2017/08/24 11:09:31 | at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
INFO   | jvm 1    | 2017/08/24 11:09:31 | at org.h2.message.DbException.getSyntaxError(DbException.java:205)
INFO   | jvm 1    | 2017/08/24 11:09:31 | at org.h2.command.Parser.readLong(Parser.java:3094)
INFO   | jvm 1    | 2017/08/24 11:09:31 | at org.h2.command.Parser.parseColumnWithType(Parser.java:4099)
INFO   | jvm 1    | 2017/08/24 11:09:31 | at org.h2.command.Parser.parseColumnForTable(Parser.java:3938)
INFO   | jvm 1    | 2017/08/24 11:09:31 | at org.h2.command.Parser.parseAlterTableAlterColumnType(Parser.java:5694)
INFO   | jvm 1    | 2017/08/24 11:09:31 | at org.h2.command.Parser.parseAlterTable(Parser.java:5619)
INFO   | jvm 1    | 2017/08/24 11:09:31 | at org.h2.command.Parser.parseAlter(Parser.java:4844)
INFO   | jvm 1    | 2017/08/24 11:09:31 | at org.h2.command.Parser.parsePrepared(Parser.java:344)
INFO   | jvm 1    | 2017/08/24 11:09:31 | at org.h2.command.Parser.parse(Parser.java:317)
INFO   | jvm 1    | 2017/08/24 11:09:31 | at org.h2.command.Parser.parse(Parser.java:289)
INFO   | jvm 1    | 2017/08/24 11:09:31 | at org.h2.command.Parser.prepareCommand(Parser.java:254)
INFO   | jvm 1    | 2017/08/24 11:09:31 | at org.h2.engine.Session.prepareLocal(Session.java:561)
INFO   | jvm 1    | 2017/08/24 11:09:31 | at org.h2.server.TcpServerThread.process(TcpServerThread.java:263)
INFO   | jvm 1    | 2017/08/24 11:09:31 | at org.h2.server.TcpServerThread.run(TcpServerThread.java:158)
INFO   | jvm 1    | 2017/08/24 11:09:31 | at java.lang.Thread.run(Thread.java:748)

trying to run that same SQL in the H2 Console throws the same error which strongly suggests it's an H2 specific issue unrelated to any other JARs --but i could be wrong.

looking at the SQL reference of H2 [1] for the ALTER TABLE ALTER COLUMN shows that:

a/ the keyword TYPE is not part of the allowed syntax, and
b/ the type is expected to be GEOMETRY and not geomtry(...)

is this a known error or am i doing something wrong?

changing the SQL statement on line #510 to

                    sql = "ALTER TABLE \"" + schemaName + "\".\"" + tableName + "\" "
                            + "ALTER COLUMN \"" + gd.getLocalName() + "\" "
                            + "GEOMETRY;";

seems to fix the issue but i'm doubtful it is the right fix.

any feedback is much appreciated.

TIA + cheers;
rsn



[1] http://h2database.com/html/grammar.html#alter_table_alter_column
Reply | Threaded
Open this post in threaded view
|

Re: Possible bug in h2gis-geotools

nicolas-f
Administrator
Hi,

Yes you are right there is an issue exactly where you see it.

https://github.com/orbisgis/h2gis-geotools/blob/master/src/main/java/org/h2gis/geotools/H2GISDialect.java#L525

This is the postgis syntax here not the H2GIS one.

The right syntax is this

ALTER TABLE "PUBLIC"."P1508632L2" ALTER COLUMN "the_geom" MULTIPOLYGON;
ALTER TABLE "PUBLIC"."P1508632L2" ADD CONSTRAINT ST_SRID("the_geom") = 4326;

This method seem not covered by unit test..

I will add an issue on github

Thanks
rsn
Reply | Threaded
Open this post in threaded view
|

Re: Possible bug in h2gis-geotools

rsn
thanks Nicolas for the prompt reply!

i'll try those changes tomorrow and will report back.


cheers;
rsn
rsn
Reply | Threaded
Open this post in threaded view
|

Re: Possible bug in h2gis-geotools

rsn
In reply to this post by nicolas-f
hi Nicolas,

the first ALTER TABLE statement works as you suggested, the second one doesn't:

INFO   | jvm 1    | 2017/08/25 10:16:54 | Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement " ALTER TABLE ""PUBLIC"".""P1508632L2"" ADD CONSTRAINT ST_SRID([*] ""the_geom"") = 4326;"; expected "., COMMENT, PRIMARY, INDEX, KEY, CHECK, UNIQUE, FOREIGN"; SQL statement:
INFO   | jvm 1    | 2017/08/25 10:16:54 |  ALTER TABLE "PUBLIC"."P1508632L2" ADD CONSTRAINT ST_SRID("the_geom") = 4326; [42001-193]
INFO   | jvm 1    | 2017/08/25 10:16:54 | at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
INFO   | jvm 1    | 2017/08/25 10:16:54 | at org.h2.message.DbException.getSyntaxError(DbException.java:205)
INFO   | jvm 1    | 2017/08/25 10:16:54 | at org.h2.command.Parser.getSyntaxError(Parser.java:537)
INFO   | jvm 1    | 2017/08/25 10:16:54 | at org.h2.command.Parser.parseAlterTableAddConstraintIf(Parser.java:5865)
INFO   | jvm 1    | 2017/08/25 10:16:54 | at org.h2.command.Parser.parseAlterTable(Parser.java:5447)
INFO   | jvm 1    | 2017/08/25 10:16:54 | at org.h2.command.Parser.parseAlter(Parser.java:4844)
INFO   | jvm 1    | 2017/08/25 10:16:54 | at org.h2.command.Parser.parsePrepared(Parser.java:344)
INFO   | jvm 1    | 2017/08/25 10:16:54 | at org.h2.command.Parser.parse(Parser.java:317)
INFO   | jvm 1    | 2017/08/25 10:16:54 | at org.h2.command.Parser.parse(Parser.java:293)
INFO   | jvm 1    | 2017/08/25 10:16:54 | at org.h2.command.Parser.prepareCommand(Parser.java:254)
INFO   | jvm 1    | 2017/08/25 10:16:54 | at org.h2.engine.Session.prepareLocal(Session.java:561)
INFO   | jvm 1    | 2017/08/25 10:16:54 | at org.h2.command.CommandList.executeRemaining(CommandList.java:32)
INFO   | jvm 1    | 2017/08/25 10:16:54 | at org.h2.command.CommandList.update(CommandList.java:43)
INFO   | jvm 1    | 2017/08/25 10:16:54 | at org.h2.command.Command.executeUpdate(Command.java:258)
INFO   | jvm 1    | 2017/08/25 10:16:54 | at org.h2.server.TcpServerThread.process(TcpServerThread.java:344)
INFO   | jvm 1    | 2017/08/25 10:16:54 | at org.h2.server.TcpServerThread.run(TcpServerThread.java:158)
INFO   | jvm 1    | 2017/08/25 10:16:54 | at java.lang.Thread.run(Thread.java:748)

checking the H2 SQL reference [1] manual for adding constraint suggests that the keyword CONSTRAINT in incorrect.  re-writing the statement as

  "ALTER TABLE \"" + schemaName + "\".\"" + tableName + "\" ADD CHECK ST_SRID(\"" + gd.getLocalName() + "\") = " + srid + ";"

works.

cheers;
rsn


[1] http://h2database.com/html/grammar.html#constraint