MARMOTTA-584: kept compatible with h2 geosparql new storage needs in postgresql
Project: http://git-wip-us.apache.org/repos/asf/marmotta/repo Commit: http://git-wip-us.apache.org/repos/asf/marmotta/commit/0fcaf95f Tree: http://git-wip-us.apache.org/repos/asf/marmotta/tree/0fcaf95f Diff: http://git-wip-us.apache.org/repos/asf/marmotta/diff/0fcaf95f Branch: refs/heads/MARMOTTA-584 Commit: 0fcaf95f78c4b3f26099cff8e15a22364939bf14 Parents: df2c80f Author: Sergio Fernández <[email protected]> Authored: Fri Jul 1 16:26:02 2016 +0200 Committer: Sergio Fernández <[email protected]> Committed: Fri Jul 1 16:26:15 2016 +0200 ---------------------------------------------------------------------- .../kiwi/model/rdf/KiWiGeometryLiteral.java | 2 +- .../kiwi/persistence/KiWiConnection.java | 4 ++-- .../kiwi/persistence/h2/create_base_tables.sql | 2 ++ .../kiwi/persistence/h2/statements.properties | 23 ++++++++++---------- .../persistence/h2/upgrade_base_004_005.sql | 5 ++++- .../persistence/pgsql/statements.properties | 2 -- 6 files changed, 21 insertions(+), 17 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/marmotta/blob/0fcaf95f/libraries/kiwi/kiwi-triplestore/src/main/java/org/apache/marmotta/kiwi/model/rdf/KiWiGeometryLiteral.java ---------------------------------------------------------------------- diff --git a/libraries/kiwi/kiwi-triplestore/src/main/java/org/apache/marmotta/kiwi/model/rdf/KiWiGeometryLiteral.java b/libraries/kiwi/kiwi-triplestore/src/main/java/org/apache/marmotta/kiwi/model/rdf/KiWiGeometryLiteral.java index 4d63b48..5d059fc 100644 --- a/libraries/kiwi/kiwi-triplestore/src/main/java/org/apache/marmotta/kiwi/model/rdf/KiWiGeometryLiteral.java +++ b/libraries/kiwi/kiwi-triplestore/src/main/java/org/apache/marmotta/kiwi/model/rdf/KiWiGeometryLiteral.java @@ -84,7 +84,7 @@ public class KiWiGeometryLiteral extends KiWiLiteral { } /** - * Return the content of the literal, using the parametrized Java type + * Return the SRID of the literal, using the parametrized Java type * * @return */ http://git-wip-us.apache.org/repos/asf/marmotta/blob/0fcaf95f/libraries/kiwi/kiwi-triplestore/src/main/java/org/apache/marmotta/kiwi/persistence/KiWiConnection.java ---------------------------------------------------------------------- diff --git a/libraries/kiwi/kiwi-triplestore/src/main/java/org/apache/marmotta/kiwi/persistence/KiWiConnection.java b/libraries/kiwi/kiwi-triplestore/src/main/java/org/apache/marmotta/kiwi/persistence/KiWiConnection.java index bfef466..96d2bcd 100644 --- a/libraries/kiwi/kiwi-triplestore/src/main/java/org/apache/marmotta/kiwi/persistence/KiWiConnection.java +++ b/libraries/kiwi/kiwi-triplestore/src/main/java/org/apache/marmotta/kiwi/persistence/KiWiConnection.java @@ -1161,8 +1161,9 @@ public class KiWiConnection implements AutoCloseable { } PreparedStatement insertNode = getPreparedStatement("store.gliteral"); + insertNode.setLong(1, node.getId()); - insertNode.setString(2, "Geometry Resource"); + insertNode.setString(2, "Geometry Resource"); //FIXME insertNode.setObject(3, null); @@ -1178,7 +1179,6 @@ public class KiWiConnection implements AutoCloseable { insertNode.setTimestamp(7, new Timestamp(geoLiteral.getCreated().getTime()), calendarUTC); insertNode.setString(8, gvalue); - insertNode.setInt(9, geoLiteral.getSRID()); insertNode.executeUpdate(); http://git-wip-us.apache.org/repos/asf/marmotta/blob/0fcaf95f/libraries/kiwi/kiwi-triplestore/src/main/resources/org/apache/marmotta/kiwi/persistence/h2/create_base_tables.sql ---------------------------------------------------------------------- diff --git a/libraries/kiwi/kiwi-triplestore/src/main/resources/org/apache/marmotta/kiwi/persistence/h2/create_base_tables.sql b/libraries/kiwi/kiwi-triplestore/src/main/resources/org/apache/marmotta/kiwi/persistence/h2/create_base_tables.sql index f969ead..a491af7 100644 --- a/libraries/kiwi/kiwi-triplestore/src/main/resources/org/apache/marmotta/kiwi/persistence/h2/create_base_tables.sql +++ b/libraries/kiwi/kiwi-triplestore/src/main/resources/org/apache/marmotta/kiwi/persistence/h2/create_base_tables.sql @@ -25,6 +25,8 @@ CREATE TABLE nodes ( ltype bigint REFERENCES nodes(id), lang varchar(5), createdAt timestamp NOT NULL DEFAULT now(), + gvalue varchar(2147483647), + srid INT, PRIMARY KEY(id) ); http://git-wip-us.apache.org/repos/asf/marmotta/blob/0fcaf95f/libraries/kiwi/kiwi-triplestore/src/main/resources/org/apache/marmotta/kiwi/persistence/h2/statements.properties ---------------------------------------------------------------------- diff --git a/libraries/kiwi/kiwi-triplestore/src/main/resources/org/apache/marmotta/kiwi/persistence/h2/statements.properties b/libraries/kiwi/kiwi-triplestore/src/main/resources/org/apache/marmotta/kiwi/persistence/h2/statements.properties index 753c574..0017cc3 100644 --- a/libraries/kiwi/kiwi-triplestore/src/main/resources/org/apache/marmotta/kiwi/persistence/h2/statements.properties +++ b/libraries/kiwi/kiwi-triplestore/src/main/resources/org/apache/marmotta/kiwi/persistence/h2/statements.properties @@ -26,21 +26,21 @@ meta.get = SELECT mvalue FROM metadata WHERE mkey = ?; # load entities load.node_by_id = SELECT id,ntype,svalue,ivalue,dvalue,tvalue,tzoffset,bvalue,lang,ltype,createdAt FROM nodes WHERE id = ? -load.nodes_by_ids = SELECT id,ntype,svalue,ivalue,dvalue,tvalue,tzoffset,bvalue,lang,ltype,createdAt FROM nodes WHERE id IN(%s) LIMIT %d +load.nodes_by_ids = SELECT id,ntype,svalue,ivalue,dvalue,tvalue,tzoffset,bvalue,lang,ltype,createdAt FROM nodes WHERE id IN(%s) LIMIT %d load.uri_by_uri = SELECT id,ntype,svalue,ivalue,dvalue,tvalue,tzoffset,bvalue,lang,ltype,createdAt FROM nodes WHERE ntype = 'uri' AND svalue = ? load.bnode_by_anonid = SELECT id,ntype,svalue,ivalue,dvalue,tvalue,tzoffset,bvalue,lang,ltype,createdAt FROM nodes WHERE ntype = 'bnode' AND svalue = ? -load.literal_by_v = SELECT id,ntype,svalue,ivalue,dvalue,tvalue,tzoffset,bvalue,lang,ltype,createdAt FROM nodes WHERE ntype = 'string' AND svalue = ? AND (lang IS NULL OR lang = '') AND ltype IS NULL -load.literal_by_vl = SELECT id,ntype,svalue,ivalue,dvalue,tvalue,tzoffset,bvalue,lang,ltype,createdAt FROM nodes WHERE svalue = ? AND lang = ? -load.literal_by_vt = SELECT id,ntype,svalue,ivalue,dvalue,tvalue,tzoffset,bvalue,lang,ltype,createdAt FROM nodes WHERE svalue = ? AND ltype = ? +load.literal_by_v = SELECT id,ntype,svalue,ivalue,dvalue,tvalue,tzoffset,bvalue,lang,ltype,createdAt FROM nodes WHERE ntype = 'string' AND svalue = ? AND (lang IS NULL OR lang = '') AND ltype IS NULL +load.literal_by_vl = SELECT id,ntype,svalue,ivalue,dvalue,tvalue,tzoffset,bvalue,lang,ltype,createdAt FROM nodes WHERE svalue = ? AND lang = ? +load.literal_by_vt = SELECT id,ntype,svalue,ivalue,dvalue,tvalue,tzoffset,bvalue,lang,ltype,createdAt FROM nodes WHERE svalue = ? AND ltype = ? load.literal_by_iv = SELECT id,ntype,svalue,ivalue,dvalue,tvalue,tzoffset,bvalue,lang,ltype,createdAt FROM nodes WHERE ivalue = ? AND (lang IS NULL OR lang = '') AND ltype = ? load.literal_by_dv = SELECT id,ntype,svalue,ivalue,dvalue,tvalue,tzoffset,bvalue,lang,ltype,createdAt FROM nodes WHERE dvalue = ? AND (lang IS NULL OR lang = '') AND ltype = ? load.literal_by_tv = SELECT id,ntype,svalue,ivalue,dvalue,tvalue,tzoffset,bvalue,lang,ltype,createdAt FROM nodes WHERE tvalue = ? AND tzoffset = ? AND (lang IS NULL OR lang = '') AND ltype = ? load.literal_by_bv = SELECT id,ntype,svalue,ivalue,dvalue,tvalue,tzoffset,bvalue,lang,ltype,createdAt FROM nodes WHERE bvalue = ? AND (lang IS NULL OR lang = '') AND ltype = ? - +load.literal_by_gv = SELECT id,ntype,svalue,ivalue,dvalue,tvalue,tzoffset,bvalue,lang,ltype,createdAt,gvalue FROM nodes WHERE gvalue = ? AND ltype = ? load.namespace_prefix = SELECT id,prefix,uri,createdAt FROM namespaces WHERE prefix = ?; load.namespace_uri = SELECT id,prefix,uri,createdAt FROM namespaces WHERE uri = ?; @@ -51,14 +51,15 @@ load.triple_by_id = SELECT id,subject,predicate,object,context,deleted,infe # store entities store.uri = INSERT INTO nodes (id,ntype,svalue,createdAt) VALUES (?,'uri',?,?) store.bnode = INSERT INTO nodes (id,ntype,svalue,createdAt) VALUES (?,'bnode',?,?) -store.sliteral = INSERT INTO nodes (id,ntype,svalue,dvalue,ivalue,lang,ltype,createdAt) VALUES (?,'string',?,?,?,?,?,?) +store.sliteral = INSERT INTO nodes (id,ntype,svalue,dvalue,ivalue,lang,ltype,createdAt) VALUES (?,'string',?,?,?,?,?,?) -store.iliteral = INSERT INTO nodes (id,ntype,svalue,dvalue,ivalue,ltype,createdAt) VALUES (?,'int',?,?,?,?,?) -store.dliteral = INSERT INTO nodes (id,ntype,svalue,dvalue,ltype,createdAt) VALUES (?,'double',?,?,?,?) -store.bliteral = INSERT INTO nodes (id,ntype,svalue,bvalue,ltype,createdAt) VALUES (?,'boolean',?,?,?,?) -store.tliteral = INSERT INTO nodes (id,ntype,svalue,tvalue,tzoffset,ltype,createdAt) VALUES (?,'date',?,?,?,?,?) +store.iliteral = INSERT INTO nodes (id,ntype,svalue,dvalue,ivalue,ltype,createdAt) VALUES (?,'int',?,?,?,?,?) +store.dliteral = INSERT INTO nodes (id,ntype,svalue,dvalue,ltype,createdAt) VALUES (?,'double',?,?,?,?) +store.bliteral = INSERT INTO nodes (id,ntype,svalue,bvalue,ltype,createdAt) VALUES (?,'boolean',?,?,?,?) +store.tliteral = INSERT INTO nodes (id,ntype,svalue,tvalue,tzoffset,ltype,createdAt) VALUES (?,'date',?,?,?,?,?) +store.gliteral = INSERT INTO nodes (id,ntype,svalue,dvalue,ivalue,lang,ltype,createdAt,gvalue,srid) VALUES (?,'geom',?,?,?,?,?,?,?,?) -store.namespace = INSERT INTO namespaces (id,prefix,uri,createdAt) VALUES (?,?,?,?) +store.namespace = INSERT INTO namespaces (id,prefix,uri,createdAt) VALUES (?,?,?,?) #store.triple = INSERT INTO triples (id,subject,predicate,object,context,inferred,createdAt) VALUES (?,?,?,?,?,?,?) store.triple = MERGE INTO triples (id,subject,predicate,object,context,inferred,createdAt) KEY(id) VALUES (?,?,?,?,?,?,?) http://git-wip-us.apache.org/repos/asf/marmotta/blob/0fcaf95f/libraries/kiwi/kiwi-triplestore/src/main/resources/org/apache/marmotta/kiwi/persistence/h2/upgrade_base_004_005.sql ---------------------------------------------------------------------- diff --git a/libraries/kiwi/kiwi-triplestore/src/main/resources/org/apache/marmotta/kiwi/persistence/h2/upgrade_base_004_005.sql b/libraries/kiwi/kiwi-triplestore/src/main/resources/org/apache/marmotta/kiwi/persistence/h2/upgrade_base_004_005.sql index f177498..452a238 100644 --- a/libraries/kiwi/kiwi-triplestore/src/main/resources/org/apache/marmotta/kiwi/persistence/h2/upgrade_base_004_005.sql +++ b/libraries/kiwi/kiwi-triplestore/src/main/resources/org/apache/marmotta/kiwi/persistence/h2/upgrade_base_004_005.sql @@ -15,4 +15,7 @@ ALTER TABLE nodes ADD tzoffset int default 0 AFTER tvalue; -UPDATE METADATA SET mvalue = '3' WHERE mkey = 'version'; \ No newline at end of file +-- TODO: new two columns in nodes + +UPDATE METADATA SET mvalue = '5' WHERE mkey = 'version'; +INSERT INTO metadata(mkey,mvalue) VALUES ('updated',to_char(now(),'yyyy-MM-DD HH:mm:ss TZ') ); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/marmotta/blob/0fcaf95f/libraries/kiwi/kiwi-triplestore/src/main/resources/org/apache/marmotta/kiwi/persistence/pgsql/statements.properties ---------------------------------------------------------------------- diff --git a/libraries/kiwi/kiwi-triplestore/src/main/resources/org/apache/marmotta/kiwi/persistence/pgsql/statements.properties b/libraries/kiwi/kiwi-triplestore/src/main/resources/org/apache/marmotta/kiwi/persistence/pgsql/statements.properties index a738d90..5593985 100644 --- a/libraries/kiwi/kiwi-triplestore/src/main/resources/org/apache/marmotta/kiwi/persistence/pgsql/statements.properties +++ b/libraries/kiwi/kiwi-triplestore/src/main/resources/org/apache/marmotta/kiwi/persistence/pgsql/statements.properties @@ -39,7 +39,6 @@ load.literal_by_iv = SELECT id,ntype,svalue,ivalue,dvalue,tvalue,tzoffset,bv load.literal_by_dv = SELECT id,ntype,svalue,ivalue,dvalue,tvalue,tzoffset,bvalue,lang,ltype,createdAt,ST_AsText(gvalue) FROM nodes WHERE dvalue = ? AND (lang IS NULL OR lang = '') AND ltype = ? load.literal_by_tv = SELECT id,ntype,svalue,ivalue,dvalue,tvalue,tzoffset,bvalue,lang,ltype,createdAt,ST_AsText(gvalue) FROM nodes WHERE tvalue = ? AND tzoffset = ? AND (lang IS NULL OR lang = '') AND ltype = ? load.literal_by_bv = SELECT id,ntype,svalue,ivalue,dvalue,tvalue,tzoffset,bvalue,lang,ltype,createdAt,ST_AsText(gvalue) FROM nodes WHERE bvalue = ? AND (lang IS NULL OR lang = '') AND ltype = ? - load.literal_by_gv = SELECT id,ntype,svalue,ivalue,dvalue,tvalue,tzoffset,bvalue,lang,ltype,createdAt,ST_AsText(gvalue) FROM nodes WHERE gvalue = ? AND ltype = ? load.namespace_prefix = SELECT id,prefix,uri,createdAt FROM namespaces WHERE prefix = ?; @@ -56,7 +55,6 @@ store.iliteral = INSERT INTO nodes (id,ntype,svalue,dvalue,ivalue,ltype,cr store.dliteral = INSERT INTO nodes (id,ntype,svalue,dvalue,ltype,createdAt) VALUES (?,'double',?,?,?,?) store.bliteral = INSERT INTO nodes (id,ntype,svalue,bvalue,ltype,createdAt) VALUES (?,'boolean',?,?,?,?) store.tliteral = INSERT INTO nodes (id,ntype,svalue,tvalue,tzoffset,ltype,createdAt) VALUES (?,'date',?,?,?,?,?) - store.gliteral = INSERT INTO nodes (id,ntype,svalue,dvalue,ivalue,lang,ltype,createdAt,gvalue) VALUES (?,'geom',?,?,?,?,?,?,ST_GeomFromText(?,?)) store.namespace = INSERT INTO namespaces (id,prefix,uri,createdAt) VALUES (?,?,?,?)
