This is an automated email from the ASF dual-hosted git repository.
jsorel pushed a commit to branch geoapi-4.0
in repository https://gitbox.apache.org/repos/asf/sis.git
The following commit(s) were added to refs/heads/geoapi-4.0 by this push:
new 5db8c56f56 Dispatch query instances in each class, remove queries
bundle
5db8c56f56 is described below
commit 5db8c56f56dac59cef3e967c697c81aecbe1d01a
Author: jsorel <johann.so...@geomatys.com>
AuthorDate: Tue Aug 13 10:34:58 2024 +0200
Dispatch query instances in each class, remove queries bundle
---
.../apache/sis/storage/geopackage/GpkgStore.java | 22 +++-
.../apache/sis/storage/geopackage/privy/Query.java | 47 +------
.../sis/storage/geopackage/privy/Record.java | 40 ++++--
.../sis/storage/geopackage/queries.properties | 141 ---------------------
4 files changed, 47 insertions(+), 203 deletions(-)
diff --git
a/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/GpkgStore.java
b/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/GpkgStore.java
index 6a8b1db6d9..41ee2beb4e 100644
---
a/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/GpkgStore.java
+++
b/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/GpkgStore.java
@@ -122,6 +122,14 @@ public class GpkgStore extends DataStore implements
WritableAggregate, ResourceO
}
};
+ private static final Query CONTENTS_EXIST = new Query("SELECT count(name)
FROM sqlite_master WHERE type='table' AND name='gpkg_contents'");
+ private static final Query CONTENTS_ALL = new Query("SELECT table_name,
data_type, identifier, description, last_change, min_x, min_y, max_x, max_y,
srs_id FROM gpkg_contents ORDER BY identifier");
+ private static final Query SPATIAL_REF_BY_SRID = new Query("SELECT * FROM
gpkg_spatial_ref_sys WHERE srs_id = [INTEGER]?");
+ private static final Query SPATIAL_REF_BY_ORGANIZATION = new Query("SELECT
* FROM gpkg_spatial_ref_sys WHERE lower(organization) = lower([VARCHAR]?) AND
organization_coordsys_id = [INTEGER]?");
+ private static final Query SPATIAL_REF_NEXT_SRID = new Query("SELECT
max(srs_id) FROM gpkg_spatial_ref_sys WHERE srs_id >= 32768");
+ private static final Query SPATIAL_REF_CREATE = new Query("INSERT INTO
gpkg_spatial_ref_sys(srs_name, srs_id, organization, organization_coordsys_id,
definition, description) VALUES ([VARCHAR]?, [INTEGER]?, [VARCHAR]?,
[INTEGER]?, [VARCHAR]?, [VARCHAR]?)");
+ private static final Query SPATIAL_REF_CREATE_EXT = new Query("INSERT INTO
gpkg_spatial_ref_sys(srs_name, srs_id, organization, organization_coordsys_id,
definition, description, definition_12_063) VALUES ([VARCHAR]?, [INTEGER]?,
[VARCHAR]?, [INTEGER]?, [VARCHAR]?, [VARCHAR]?, [VARCHAR]?)");
+
/**
* Synchronization object used for accessing datasource.
* we do not use 'synchronized' on the method since writing may use
thread-pools.
@@ -411,7 +419,7 @@ public class GpkgStore extends DataStore implements
WritableAggregate, ResourceO
final boolean contentsExist;
try (Connection cnx = getConnection(false);
Statement stmt = cnx.createStatement();
- ResultSet rs =
stmt.executeQuery(Query.CONTENTS_EXIST.query())) {
+ ResultSet rs = stmt.executeQuery(CONTENTS_EXIST.query()))
{
contentsExist = rs.getInt(1) != 0;
}
@@ -422,7 +430,7 @@ public class GpkgStore extends DataStore implements
WritableAggregate, ResourceO
final List<Record.Content> contents = new ArrayList<>();
try (Connection cnx = getConnection(false);
Statement stmt = cnx.createStatement();
- ResultSet rs =
stmt.executeQuery(Query.CONTENTS_ALL.query())) {
+ ResultSet rs =
stmt.executeQuery(CONTENTS_ALL.query())) {
while (rs.next()) {
final Record.Content row = new Record.Content();
row.read(rs);
@@ -586,7 +594,7 @@ public class GpkgStore extends DataStore implements
WritableAggregate, ResourceO
ioLock.readLock().lock();
search:
- try (PreparedStatement stmt =
Query.SPATIAL_REF_BY_SRID.createPreparedStatement(cnx, srid);
+ try (PreparedStatement stmt =
SPATIAL_REF_BY_SRID.createPreparedStatement(cnx, srid);
ResultSet rs = stmt.executeQuery()) {
if (rs.next()) {
//check new WKT definition
@@ -688,7 +696,7 @@ public class GpkgStore extends DataStore implements
WritableAggregate, ResourceO
if (srsId == null) {
//next available srsid in user reserved space from 32768 to
60000000
try (Connection cnx = getConnection(false);
- PreparedStatement stmt =
Query.SPATIAL_REF_NEXT_SRID.createPreparedStatement(cnx);
+ PreparedStatement stmt =
SPATIAL_REF_NEXT_SRID.createPreparedStatement(cnx);
ResultSet rs = stmt.executeQuery()) {
if (rs.next()) {
srsId = rs.getInt(1);
@@ -710,11 +718,11 @@ public class GpkgStore extends DataStore implements
WritableAggregate, ResourceO
final String wkt2 = wktFormat.format(crs);
try (Connection cnx = getConnection(true)) {
- try (PreparedStatement stmt =
Query.SPATIAL_REF_CREATE_EXT.createPreparedStatement(cnx, srsName, srsId,
organisation, organisationCode, wkt1, description, wkt2)) {
+ try (PreparedStatement stmt =
SPATIAL_REF_CREATE_EXT.createPreparedStatement(cnx, srsName, srsId,
organisation, organisationCode, wkt1, description, wkt2)) {
stmt.executeUpdate();
} catch (SQLException ex) {
cnx.rollback();
- try (PreparedStatement stmt =
Query.SPATIAL_REF_CREATE.createPreparedStatement(cnx, srsName, srsId,
organisation, organisationCode, wkt1, description)) {
+ try (PreparedStatement stmt =
SPATIAL_REF_CREATE.createPreparedStatement(cnx, srsName, srsId, organisation,
organisationCode, wkt1, description)) {
stmt.executeUpdate();
} catch (SQLException ex2) {
cnx.rollback();
@@ -729,7 +737,7 @@ public class GpkgStore extends DataStore implements
WritableAggregate, ResourceO
private Integer getSRID(String organisation, Integer organisationCode)
throws DataStoreException, SQLException {
try (Connection cnx = getConnection(false);
- PreparedStatement stmt =
Query.SPATIAL_REF_BY_ORGANIZATION.createPreparedStatement(cnx, organisation,
organisationCode);
+ PreparedStatement stmt =
SPATIAL_REF_BY_ORGANIZATION.createPreparedStatement(cnx, organisation,
organisationCode);
ResultSet rs = stmt.executeQuery()) {
if (rs.next()) {
return rs.getInt("srs_id");
diff --git
a/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/privy/Query.java
b/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/privy/Query.java
index 241cb6d72e..426a7588e1 100644
---
a/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/privy/Query.java
+++
b/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/privy/Query.java
@@ -24,60 +24,15 @@ import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
-import java.util.ResourceBundle;
import org.apache.sis.util.ArraysExt;
/**
- * Contain all queries, backed by a property file.
+ * SQL Query object.
*
* @author Johann Sorel (Geomatys)
*/
public final class Query {
- static final ResourceBundle BUNDLE =
ResourceBundle.getBundle("org/apache/sis/storage/geopackage/queries");
-
- public static final Query DROP_TABLE = new
Query(BUNDLE.getString("DROP_TABLE"));
-
- public static final Query CONTENTS_EXIST = new
Query(BUNDLE.getString("CONTENTS_EXIST"));
- public static final Query CONTENTS_ALL = new
Query(BUNDLE.getString("CONTENTS_ALL"));
- public static final Query CONTENTS_BY_TABLE_NAME = new
Query(BUNDLE.getString("CONTENTS_BY_TABLE_NAME"));
- public static final Query CONTENTS_CREATE = new
Query(BUNDLE.getString("CONTENTS_CREATE"));
- public static final Query CONTENTS_UPDATE = new
Query(BUNDLE.getString("CONTENTS_UPDATE"));
- public static final Query CONTENTS_DELETE = new
Query(BUNDLE.getString("CONTENTS_DELETE"));
-
- public static final Query SPATIAL_REF_BY_SRID = new
Query(BUNDLE.getString("SPATIAL_REF_BY_SRID"));
- public static final Query SPATIAL_REF_BY_ORGANIZATION = new
Query(BUNDLE.getString("SPATIAL_REF_BY_ORGANIZATION"));
- public static final Query SPATIAL_REF_NEXT_SRID = new
Query(BUNDLE.getString("SPATIAL_REF_NEXT_SRID"));
- public static final Query SPATIAL_REF_CREATE = new
Query(BUNDLE.getString("SPATIAL_REF_CREATE"));
- public static final Query SPATIAL_REF_CREATE_EXT = new
Query(BUNDLE.getString("SPATIAL_REF_CREATE_EXT"));
- public static final Query SPATIAL_REF_DELETE = new
Query(BUNDLE.getString("SPATIAL_REF_DELETE"));
-
- public static final Query GEOMETRY_COLUMN_CREATE = new
Query(BUNDLE.getString("GEOMETRY_COLUMN_CREATE"));
-
- public static final Query EXTENSION_BY_TABLE_AND_COLUMN = new
Query(BUNDLE.getString("EXTENSION_BY_TABLE_AND_COLUMN"));
- public static final Query EXTENSION_CREATE = new
Query(BUNDLE.getString("EXTENSION_CREATE"));
- public static final Query EXTENSION_DELETE_BY_TABLE = new
Query(BUNDLE.getString("EXTENSION_DELETE_BY_TABLE"));
-
- public static final Query TILE_MATRIX_SET_BY_TABLE = new
Query(BUNDLE.getString("TILE_MATRIX_SET_BY_TABLE"));
- public static final Query TILE_MATRIX_SET_CREATE = new
Query(BUNDLE.getString("TILE_MATRIX_SET_CREATE"));
- public static final Query TILE_MATRIX_SET_DELETE = new
Query(BUNDLE.getString("TILE_MATRIX_SET_DELETE"));
-
- public static final Query TILE_MATRIX_BY_TABLE = new
Query(BUNDLE.getString("TILE_MATRIX_BY_TABLE"));
- public static final Query TILE_MATRIX_CREATE = new
Query(BUNDLE.getString("TILE_MATRIX_CREATE"));
- public static final Query TILE_MATRIX_DELETE = new
Query(BUNDLE.getString("TILE_MATRIX_DELETE"));
- public static final Query TILE_MATRIX_UPDATE_ZOOMLEVEL = new
Query(BUNDLE.getString("TILE_MATRIX_UPDATE_ZOOMLEVEL"));
-
- public static final Query TILE_TABLE_CREATE = new
Query(BUNDLE.getString("TILE_TABLE_CREATE"));
- public static final Query TILE_CREATE = new
Query(BUNDLE.getString("TILE_CREATE"));
- public static final Query TILE_ANY = new
Query(BUNDLE.getString("TILE_ANY"));
- public static final Query TILE_GET = new
Query(BUNDLE.getString("TILE_GET"));
- public static final Query TILE_EXIST = new
Query(BUNDLE.getString("TILE_EXIST"));
- public static final Query TILE_DELETE = new
Query(BUNDLE.getString("TILE_DELETE"));
- public static final Query TILE_IN_RANGE= new
Query(BUNDLE.getString("TILE_IN_RANGE"));
- public static final Query TILE_IN_RANGE_SHORT = new
Query(BUNDLE.getString("TILE_IN_RANGE_SHORT"));
- public static final Query TILE_DELETE_BY_MATRIX = new
Query(BUNDLE.getString("TILE_DELETE_BY_MATRIX"));
- public static final Query TILE_DELETE_BY_RANGE = new
Query(BUNDLE.getString("TILE_DELETE_BY_RANGE"));
-
private final String query;
private final Integer[] parameters;
diff --git
a/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/privy/Record.java
b/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/privy/Record.java
index 91dc231725..4b15a03616 100644
---
a/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/privy/Record.java
+++
b/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/privy/Record.java
@@ -49,6 +49,9 @@ public final class Record {
*/
public static final class Content {
+ private static final Query CONTENTS_CREATE = new Query("INSERT INTO
gpkg_contents(table_name, data_type, identifier, description, last_change,
min_x, min_y, max_x, max_y, srs_id) VALUES ([VARCHAR]?, [VARCHAR]?, [VARCHAR]?,
[VARCHAR]?, [TIMESTAMP]?, [DOUBLE]?, [DOUBLE]?, [DOUBLE]?, [DOUBLE]?,
[INTEGER]?)");
+ private static final Query CONTENTS_UPDATE = new Query("UPDATE
gpkg_contents SET table_name = [VARCHAR]?, data_type = [VARCHAR]?, identifier =
[VARCHAR]?, description = [VARCHAR]?, last_change = [TIMESTAMP]?, min_x =
[DOUBLE]?, min_y = [DOUBLE]?, max_x = [DOUBLE]?, max_y = [DOUBLE]?, srs_id =
[INTEGER]? WHERE table_name = [VARCHAR]?");
+
public String tableName;
public String dataType;
public String identifier;
@@ -123,7 +126,7 @@ public final class Record {
lastChange = format.format(this.lastChange.getTime());
}
- try (PreparedStatement stmt =
Query.CONTENTS_CREATE.createPreparedStatement(cnx,
+ try (PreparedStatement stmt =
CONTENTS_CREATE.createPreparedStatement(cnx,
tableName,
dataType,
identifier,
@@ -139,7 +142,7 @@ public final class Record {
}
public void update(Connection cnx) throws SQLException {
- try (PreparedStatement stmt =
Query.CONTENTS_UPDATE.createPreparedStatement(cnx,
+ try (PreparedStatement stmt =
CONTENTS_UPDATE.createPreparedStatement(cnx,
tableName,
dataType,
identifier,
@@ -170,6 +173,9 @@ public final class Record {
* );
*/
public static final class TileMatrixSet {
+
+ private static final Query TILE_MATRIX_SET_CREATE = new Query("INSERT
INTO gpkg_tile_matrix_set(table_name, srs_id, min_x, min_y, max_x, max_y)
VALUES ([VARCHAR]?, [INTEGER]?, [DOUBLE]?, [DOUBLE]?, [DOUBLE]?, [DOUBLE]?)");
+
public String tableName;
public int srsId;
public double minX;
@@ -187,7 +193,7 @@ public final class Record {
}
public void create(Connection cnx) throws SQLException {
- try (PreparedStatement stmt =
Query.TILE_MATRIX_SET_CREATE.createPreparedStatement(
+ try (PreparedStatement stmt =
TILE_MATRIX_SET_CREATE.createPreparedStatement(
cnx,
tableName,
srsId,
@@ -215,6 +221,9 @@ public final class Record {
* );
*/
public static final class TileMatrix {
+
+ private static final Query TILE_MATRIX_CREATE = new Query("INSERT INTO
gpkg_tile_matrix(table_name, zoom_level, matrix_width, matrix_height,
tile_width, tile_height, pixel_x_size, pixel_y_size) VALUES ([VARCHAR]?,
[INTEGER]?, [INTEGER]?, [INTEGER]?, [INTEGER]?, [INTEGER]?, [DOUBLE]?,
[DOUBLE]?)");
+
public String tableName;
public int zoomLevel;
public int matrixWidth;
@@ -236,7 +245,7 @@ public final class Record {
}
public void create(Connection cnx) throws SQLException {
- try (PreparedStatement stmt =
Query.TILE_MATRIX_CREATE.createPreparedStatement(
+ try (PreparedStatement stmt =
TILE_MATRIX_CREATE.createPreparedStatement(
cnx,
tableName,
zoomLevel,
@@ -262,6 +271,9 @@ public final class Record {
* )
*/
public static final class Tile {
+
+ private static final Query TILE_CREATE = new Query("INSERT OR REPLACE
INTO [VARCHAR]? (zoom_level, tile_column, tile_row, tile_data) VALUES
([INTEGER]?, [INTEGER]?, [INTEGER]?, [BLOB]? )");
+
public int id;
public int zoomLevel;
public int tileColumn;
@@ -277,7 +289,7 @@ public final class Record {
}
public void create(Connection cnx) throws SQLException {
- try (PreparedStatement stmt =
Query.TILE_CREATE.createPreparedStatement(
+ try (PreparedStatement stmt = TILE_CREATE.createPreparedStatement(
cnx,
zoomLevel,
tileColumn,
@@ -303,6 +315,9 @@ public final class Record {
* );
*/
public static final class GeometryColumn {
+
+ private static final Query GEOMETRY_COLUMN_CREATE = new Query("INSERT
INTO gpkg_geometry_columns(table_name, column_name, geometry_type_name, srs_id,
z, m) VALUES ([VARCHAR]?, [VARCHAR]?, [VARCHAR]?, [INTEGER]?, [TINYINT]?
[TINYINT]?)");
+
public String tableName;
public String columnName;
public String geometryType;
@@ -320,7 +335,7 @@ public final class Record {
}
public void write(Connection cnx) throws SQLException {
- try (PreparedStatement stmt =
Query.GEOMETRY_COLUMN_CREATE.createPreparedStatement(
+ try (PreparedStatement stmt =
GEOMETRY_COLUMN_CREATE.createPreparedStatement(
cnx,
tableName,
columnName,
@@ -345,6 +360,10 @@ public final class Record {
* );
*/
public static final class SpatialRefSys {
+
+ private static final Query SPATIAL_REF_CREATE = new Query("INSERT INTO
gpkg_spatial_ref_sys(srs_name, srs_id, organization, organization_coordsys_id,
definition, description) VALUES ([VARCHAR]?, [INTEGER]?, [VARCHAR]?,
[INTEGER]?, [VARCHAR]?, [VARCHAR]?)");
+ private static final Query SPATIAL_REF_CREATE_EXT = new Query("INSERT
INTO gpkg_spatial_ref_sys(srs_name, srs_id, organization,
organization_coordsys_id, definition, description, definition_12_063) VALUES
([VARCHAR]?, [INTEGER]?, [VARCHAR]?, [INTEGER]?, [VARCHAR]?, [VARCHAR]?,
[VARCHAR]?)");
+
public String srsName;
public int srsId;
public String organization;
@@ -368,7 +387,7 @@ public final class Record {
}
public void create(Connection cnx) throws SQLException {
- try (PreparedStatement stmt =
Query.SPATIAL_REF_CREATE.createPreparedStatement(
+ try (PreparedStatement stmt =
SPATIAL_REF_CREATE.createPreparedStatement(
cnx,
srsName,
srsId,
@@ -380,7 +399,7 @@ public final class Record {
}
public void createExt(Connection cnx) throws SQLException {
- try (PreparedStatement stmt =
Query.SPATIAL_REF_CREATE.createPreparedStatement(
+ try (PreparedStatement stmt =
SPATIAL_REF_CREATE_EXT.createPreparedStatement(
cnx,
srsName,
srsId,
@@ -404,6 +423,9 @@ public final class Record {
* );
*/
public static final class Extension {
+
+ private static final Query EXTENSION_CREATE = new Query("INSERT INTO
gpkg_extensions(table_name, column_name, extension_name, definition, scope)
VALUES ([VARCHAR]?, [VARCHAR]?, [VARCHAR]?, [VARCHAR]?, [VARCHAR]?)");
+
public String tableName;
public String columnName;
public String extensionName;
@@ -419,7 +441,7 @@ public final class Record {
}
public void create(Connection cnx) throws SQLException {
- try (PreparedStatement stmt =
Query.EXTENSION_CREATE.createPreparedStatement(
+ try (PreparedStatement stmt =
EXTENSION_CREATE.createPreparedStatement(
cnx,
tableName,
columnName,
diff --git
a/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/queries.properties
b/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/queries.properties
deleted file mode 100644
index fa92f0ab32..0000000000
---
a/incubator/src/org.apache.sis.storage.geopackage/main/org/apache/sis/storage/geopackage/queries.properties
+++ /dev/null
@@ -1,141 +0,0 @@
-
-################################################################################
-#
-# NOTICE ABOUT AUTOINCREMENT :
-#
-# We de not declare INTEGER primary key as AUTOINCREMENT to benefit from
-# SQLite optimised rowId which is a unique id autogenerated already.
-# See :
-# - https://sqlite.org/autoinc.html
-# - https://sqlite.org/lang_createtable.html#rowid
-#
-#
-################################################################################
-
-
-DROP_TABLE = DROP TABLE [VARCHAR]?
-
-# gpkg_contents
################################################################
-# CREATE TABLE IF NOT EXISTS gpkg_contents (
-# table_name TEXT NOT NULL PRIMARY KEY,
-# data_type TEXT NOT NULL,
-# identifier TEXT UNIQUE,
-# description TEXT DEFAULT '',
-# last_change DATETIME NOT NULL DEFAULT
(strftime('%Y-%m-%dT%H:%M:%fZ','now')),
-# min_x DOUBLE,
-# min_y DOUBLE,
-# max_x DOUBLE,
-# max_y DOUBLE,
-# srs_id INTEGER,
-# CONSTRAINT fk_gc_r_srs_id FOREIGN KEY (srs_id) REFERENCES
gpkg_spatial_ref_sys(srs_id)
-# );
-CONTENTS_EXIST = SELECT count(name) FROM sqlite_master WHERE type='table' AND
name='gpkg_contents'
-CONTENTS_ALL = SELECT table_name, data_type, identifier, description,
last_change, min_x, min_y, max_x, max_y, srs_id FROM gpkg_contents ORDER BY
identifier
-# we add an order by to preserve some consistancy
-CONTENTS_BY_TABLE_NAME = SELECT table_name, data_type, identifier,
description, last_change, min_x, min_y, max_x, max_y, srs_id FROM gpkg_contents
WHERE table_name = [VARCHAR]?
-CONTENTS_CREATE = INSERT INTO gpkg_contents(table_name, data_type, identifier,
description, last_change, min_x, min_y, max_x, max_y, srs_id) VALUES
([VARCHAR]?, [VARCHAR]?, [VARCHAR]?, [VARCHAR]?, [TIMESTAMP]?, [DOUBLE]?,
[DOUBLE]?, [DOUBLE]?, [DOUBLE]?, [INTEGER]?)
-CONTENTS_UPDATE = UPDATE gpkg_contents SET table_name = [VARCHAR]?, data_type
= [VARCHAR]?, identifier = [VARCHAR]?, description = [VARCHAR]?, last_change =
[TIMESTAMP]?, min_x = [DOUBLE]?, min_y = [DOUBLE]?, max_x = [DOUBLE]?, max_y =
[DOUBLE]?, srs_id = [INTEGER]? WHERE table_name = [VARCHAR]?
-CONTENTS_DELETE = DELETE FROM gpkg_contents WHERE table_name = [VARCHAR]?
-
-# gpkg_spatial_ref_sys
#########################################################
-# CREATE TABLE IF NOT EXISTS gpkg_spatial_ref_sys (
-# srs_name TEXT NOT NULL,
-# srs_id INTEGER NOT NULL PRIMARY KEY,
-# organization TEXT NOT NULL,
-# organization_coordsys_id INTEGER NOT NULL,
-# definition TEXT NOT NULL, -- WKT-1
-# description TEXT
-# definition_12_063 TEXT -- WKT-2
-# );
-SPATIAL_REF_BY_SRID = SELECT * FROM gpkg_spatial_ref_sys WHERE srs_id =
[INTEGER]?
-SPATIAL_REF_BY_ORGANIZATION = SELECT * FROM gpkg_spatial_ref_sys WHERE
lower(organization) = lower([VARCHAR]?) AND organization_coordsys_id =
[INTEGER]?
-SPATIAL_REF_NEXT_SRID = SELECT max(srs_id) FROM gpkg_spatial_ref_sys WHERE
srs_id >= 32768
-SPATIAL_REF_CREATE = INSERT INTO gpkg_spatial_ref_sys(srs_name, srs_id,
organization, organization_coordsys_id, definition, description) VALUES
([VARCHAR]?, [INTEGER]?, [VARCHAR]?, [INTEGER]?, [VARCHAR]?, [VARCHAR]?)
-SPATIAL_REF_CREATE_EXT = INSERT INTO gpkg_spatial_ref_sys(srs_name, srs_id,
organization, organization_coordsys_id, definition, description,
definition_12_063) VALUES ([VARCHAR]?, [INTEGER]?, [VARCHAR]?, [INTEGER]?,
[VARCHAR]?, [VARCHAR]?, [VARCHAR]?)
-SPATIAL_REF_DELETE = DELETE FROM gpkg_spatial_ref_sys WHERE srs_id = [INTEGER]?
-
-# gpkg_extensions
##############################################################
-# CREATE TABLE IF NOT EXISTS gpkg_extensions (
-# table_name TEXT,
-# column_name TEXT,
-# extension_name TEXT NOT NULL,
-# definition TEXT NOT NULL,
-# scope TEXT NOT NULL,
-# CONSTRAINT ge_tce UNIQUE (table_name, column_name, extension_name)
-# );
-EXTENSION_BY_TABLE_AND_COLUMN = SELECT table_name, column_name,
extension_name, definition, scope FROM gpkg_extensions WHERE table_name =
[VARCHAR]? AND column_name = [VARCHAR]?
-EXTENSION_CREATE = INSERT INTO gpkg_extensions(table_name, column_name,
extension_name, definition, scope) VALUES ([VARCHAR]?, [VARCHAR]?, [VARCHAR]?,
[VARCHAR]?, [VARCHAR]?)
-EXTENSION_DELETE_BY_TABLE = DELETE FROM gpkg_extensions WHERE table_name =
[VARCHAR]?
-
-# gpkg_geometry_columns
########################################################
-# CREATE TABLE IF NOT EXISTS gpkg_geometry_columns (
-# table_name TEXT NOT NULL,
-# column_name TEXT NOT NULL,
-# geometry_type_name TEXT NOT NULL,
-# srs_id INTEGER NOT NULL,
-# z TINYINT NOT NULL,
-# m TINYINT NOT NULL,
-# CONSTRAINT pk_geom_cols PRIMARY KEY (table_name, column_name),
-# CONSTRAINT uk_gc_table_name UNIQUE (table_name),
-# CONSTRAINT fk_gc_tn FOREIGN KEY (table_name) REFERENCES
gpkg_contents(table_name),
-# CONSTRAINT fk_gc_srs FOREIGN KEY (srs_id) REFERENCES gpkg_spatial_ref_sys
(srs_id)
-# );
-GEOMETRY_COLUMN_CREATE = INSERT INTO gpkg_geometry_columns(table_name,
column_name, geometry_type_name, srs_id, z, m) VALUES ([VARCHAR]?, [VARCHAR]?,
[VARCHAR]?, [INTEGER]?, [TINYINT]? [TINYINT]?)
-
-# gpkg_tile_matrix_set
#########################################################
-# CREATE TABLE IF NOT EXISTS gpkg_tile_matrix_set (
-# table_name TEXT NOT NULL PRIMARY KEY,
-# srs_id INTEGER NOT NULL,
-# min_x DOUBLE NOT NULL,
-# min_y DOUBLE NOT NULL,
-# max_x DOUBLE NOT NULL,
-# max_y DOUBLE NOT NULL,
-# CONSTRAINT fk_gtms_table_name FOREIGN KEY (table_name) REFERENCES
gpkg_contents(table_name),
-# CONSTRAINT fk_gtms_srs FOREIGN KEY (srs_id) REFERENCES
gpkg_spatial_ref_sys (srs_id)
-# );
-TILE_MATRIX_SET_BY_TABLE = SELECT table_name, srs_id, min_x, min_y, max_x,
max_y FROM gpkg_tile_matrix_set WHERE table_name = [VARCHAR]?
-TILE_MATRIX_SET_CREATE = INSERT INTO gpkg_tile_matrix_set(table_name, srs_id,
min_x, min_y, max_x, max_y) VALUES ([VARCHAR]?, [INTEGER]?, [DOUBLE]?,
[DOUBLE]?, [DOUBLE]?, [DOUBLE]?)
-TILE_MATRIX_SET_DELETE = DELETE FROM gpkg_tile_matrix_set WHERE table_name =
[VARCHAR]?
-
-# gpkg_tile_matrix
#############################################################
-# CREATE TABLE IF NOT EXISTS gpkg_tile_matrix (
-# table_name TEXT NOT NULL,
-# zoom_level INTEGER NOT NULL,
-# matrix_width INTEGER NOT NULL,
-# matrix_height INTEGER NOT NULL,
-# tile_width INTEGER NOT NULL,
-# tile_height INTEGER NOT NULL,
-# pixel_x_size DOUBLE NOT NULL,
-# pixel_y_size DOUBLE NOT NULL,
-# CONSTRAINT pk_ttm PRIMARY KEY (table_name, zoom_level),
-# CONSTRAINT fk_tmm_table_name FOREIGN KEY (table_name) REFERENCES
gpkg_contents(table_name)
-# );
-TILE_MATRIX_BY_TABLE = SELECT table_name, zoom_level, matrix_width,
matrix_height, tile_width, tile_height, pixel_x_size, pixel_y_size FROM
gpkg_tile_matrix WHERE table_name = [VARCHAR]?
-TILE_MATRIX_CREATE = INSERT INTO gpkg_tile_matrix(table_name, zoom_level,
matrix_width, matrix_height, tile_width, tile_height, pixel_x_size,
pixel_y_size) VALUES ([VARCHAR]?, [INTEGER]?, [INTEGER]?, [INTEGER]?,
[INTEGER]?, [INTEGER]?, [DOUBLE]?, [DOUBLE]?)
-TILE_MATRIX_DELETE = DELETE FROM gpkg_tile_matrix WHERE table_name =
[VARCHAR]? AND zoom_level = [INTEGER]?
-TILE_MATRIX_UPDATE_ZOOMLEVEL = UPDATE gpkg_tile_matrix SET zoom_level =
[INTEGER]? WHERE table_name = [VARCHAR]? AND zoom_level = [INTEGER]?
-
-
-################################################################################
-# NOTE : AUTOINCREMENT REMOVED from id field, see header.
-TILE_TABLE_CREATE = \
- CREATE TABLE [VARCHAR]? ( \
- id INTEGER PRIMARY KEY NOT NULL, \
- zoom_level INTEGER NOT NULL, \
- tile_column INTEGER NOT NULL, \
- tile_row INTEGER NOT NULL, \
- tile_data BLOB NOT NULL, \
- UNIQUE (zoom_level, tile_column, tile_row) \
- )
-TILE_TABLE_DELETE = DROP TABLE [VARCHAR]?
-
-TILE_CREATE = INSERT OR REPLACE INTO [VARCHAR]? (zoom_level, tile_column,
tile_row, tile_data) VALUES ([INTEGER]?, [INTEGER]?, [INTEGER]?, [BLOB]? )
-TILE_DELETE_BY_MATRIX = DELETE FROM [VARCHAR]? WHERE zoom_level=[INTEGER]?
-TILE_DELETE = DELETE FROM [VARCHAR]? WHERE zoom_level=[INTEGER]? AND
tile_column=[INTEGER]? AND tile_row=[INTEGER]?
-TILE_DELETE_BY_RANGE = DELETE FROM [VARCHAR]? WHERE zoom_level=[INTEGER]? AND
tile_column >= [INTEGER]? AND tile_column < [INTEGER]? AND tile_row >=
[INTEGER]? AND tile_row < [INTEGER]?
-TILE_ANY = SELECT id, zoom_level, tile_column, tile_row, tile_data FROM
[VARCHAR]? WHERE zoom_level=[INTEGER]? LIMIT 1
-TILE_GET = SELECT id, zoom_level, tile_column, tile_row, tile_data FROM
[VARCHAR]? WHERE zoom_level=[INTEGER]? AND tile_column=[INTEGER]? AND
tile_row=[INTEGER]?
-TILE_EXIST = SELECT count(id) FROM [VARCHAR]? WHERE zoom_level=[INTEGER]? AND
tile_column=[INTEGER]? AND tile_row=[INTEGER]?
-TILE_UPDATE_ZOOMLEVEL = UPDATE [VARCHAR]? SET zoom_level = [INTEGER]? WHERE
zoom_level = [INTEGER]?
-TILE_IN_RANGE_SHORT = SELECT tile_column, tile_row FROM [VARCHAR]? WHERE
zoom_level=[INTEGER]? AND tile_column >= [INTEGER]? AND tile_column <
[INTEGER]? AND tile_row >= [INTEGER]? AND tile_row < [INTEGER]?
-TILE_IN_RANGE = SELECT id, zoom_level, tile_column, tile_row, tile_data FROM
[VARCHAR]? WHERE zoom_level=[INTEGER]? AND tile_column >= [INTEGER]? AND
tile_column < [INTEGER]? AND tile_row >= [INTEGER]? AND tile_row < [INTEGER]?
