Application: gvSIG desktop

-- normalize-spaces false

-- strip-start false

-- remove-nl false

-- begin createTable

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))

-- end createTable

-- begin createTable

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) )

-- end createTable

-- begin createTable

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 "fk_gc_tn" FOREIGN KEY("table_name") REFERENCES "gpkg_contents"("table_name"), CONSTRAINT "uk_gc_table_name" UNIQUE("table_name"), CONSTRAINT "fk_gc_srs" FOREIGN KEY("srs_id") REFERENCES "gpkg_spatial_ref_sys"("srs_id") )

-- end createTable

-- begin createTable

INSERT INTO "gpkg_contents" ("table_name", "data_type", "identifier", "description", "last_change", "min_x", "min_y", "max_x", "max_y", "srs_id") VALUES ('test', 'features', 'test', '', '*', NULL, NULL, NULL, NULL, NULL)

-- end createTable

-- begin createTable

CREATE TABLE "test" ("ID" INTEGER PRIMARY KEY NOT NULL, "Byte" TINYINT DEFAULT NULL, "Bool1" BOOLEAN DEFAULT NULL, "Long" BIGINT DEFAULT NULL, "Timestamp" DATETIME DEFAULT NULL, "Date" DATE DEFAULT NULL, "Time" TIME DEFAULT NULL, "Bool2" BOOLEAN DEFAULT NULL, "String" VARCHAR(30) DEFAULT NULL, "Bool3" BOOLEAN DEFAULT NULL, "Double" DOUBLE PRECISION DEFAULT NULL, "Bool4" BOOLEAN DEFAULT NULL, "Float" FLOAT DEFAULT NULL, "Bool5" BOOLEAN DEFAULT NULL, "Decimal" DECIMAL(6,3) DEFAULT NULL, "Geometry" BLOB DEFAULT NULL )

-- end createTable

-- begin createTable

INSERT INTO "gpkg_geometry_columns" ("table_name", "column_name", "geometry_type_name", "srs_id", "z", "m") VALUES ('test', 'Geometry', 'POINT', '4326', '0', '0')

-- end createTable

-- begin createTable

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) )

-- end createTable

-- begin createTable

CREATE VIRTUAL TABLE "rtree_test_Geometry" USING rtree(id, minx, maxx, miny, maxy)

-- end createTable

-- begin createTable

CREATE TRIGGER "rtree_test_Geometry_delete" AFTER DELETE ON "test"

  WHEN old."Geometry" NOT NULL

BEGIN

  DELETE FROM "rtree_test_Geometry" WHERE ID = OLD."ID";

END

-- end createTable

-- begin createTable

CREATE TRIGGER "rtree_test_Geometry_insert" AFTER INSERT ON "test"

  WHEN (new."Geometry" NOT NULL AND NOT ST_IsEmpty(NEW."Geometry"))

BEGIN

  INSERT OR REPLACE INTO "rtree_test_Geometry" VALUES (

    NEW."ID",

    ST_MinX(NEW."Geometry"), ST_MaxX(NEW."Geometry"),

    ST_MinY(NEW."Geometry"), ST_MaxY(NEW."Geometry")

  );

END

-- end createTable

-- begin createTable

CREATE TRIGGER "rtree_test_Geometry_update1" AFTER UPDATE OF "Geometry" ON "test"

  WHEN OLD."ID" = NEW."ID" AND

       (NEW."Geometry" NOTNULL AND NOT ST_IsEmpty(NEW."Geometry"))

BEGIN

  INSERT OR REPLACE INTO "rtree_test_Geometry" VALUES (

    NEW."ID",

    ST_MinX(NEW."Geometry"), ST_MaxX(NEW."Geometry"),

    ST_MinY(NEW."Geometry"), ST_MaxY(NEW."Geometry")

  );

END

-- end createTable

-- begin createTable

CREATE TRIGGER "rtree_test_Geometry_update2" AFTER UPDATE OF "Geometry" ON "test"

  WHEN OLD."ID" = NEW."ID" AND

       (NEW."Geometry" ISNULL OR ST_IsEmpty(NEW."Geometry"))

BEGIN

  DELETE FROM "rtree_test_Geometry" WHERE ID = OLD."ID";

END

-- end createTable

-- begin createTable

CREATE TRIGGER "rtree_test_Geometry_update3" AFTER UPDATE ON "test"

  WHEN OLD."ID" != NEW."ID" AND

       (NEW."Geometry" NOTNULL AND NOT ST_IsEmpty(NEW."Geometry"))

BEGIN

  DELETE FROM "rtree_test_Geometry" WHERE ID = OLD."ID";

  INSERT OR REPLACE INTO "rtree_test_Geometry" VALUES (

    NEW."ID",

    ST_MinX(NEW."Geometry"), ST_MaxX(NEW."Geometry"),

    ST_MinY(NEW."Geometry"), ST_MaxY(NEW."Geometry")

  );

END

-- end createTable

-- begin createTable

CREATE TRIGGER "rtree_test_Geometry_update4" AFTER UPDATE ON "test"

  WHEN OLD."ID" != NEW."ID" AND

       (NEW."Geometry" ISNULL OR ST_IsEmpty(NEW."Geometry"))

BEGIN

  DELETE FROM "rtree_test_Geometry" WHERE ID IN (OLD."ID", NEW."ID");

END

-- end createTable

-- begin createTable

INSERT INTO "gpkg_extensions" (

    "table_name", 

    "column_name", 

    "extension_name", 

    "definition", 

    "scope"

) VALUES (

    'test', 

    'Geometry', 

    'gpkg_rtree_index', 

    'http://www.geopackage.org/spec/#extension_rtree', 

    'read-write'

)

-- end createTable