Revision 47580 trunk/org.gvsig.desktop/org.gvsig.desktop.plugin/org.gvsig.sqlite/org.gvsig.sqlite.provider/src/test/resources/org/gvsig/sqlite/dal/createTable.sql

View differences:

createTable.sql
1 
-- normalize-spaces false
2 
-- strip-start false
3 
-- remove-nl false
1 4 

  		




  2
  
  
    
-- Se usa en TestCreateTable.


  		




  3
  
  
    
-- Se corresponde con el fichero CSV "testCreateSource1.csv".


  		




  
  5
  
    
-- begin createTable


  		




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


  		




  
  7
  
    
-- end createTable


  		




  4
  8
  
    

  		




  5
  
  
    
--CREATE TABLE "test" (


  		




  6
  
  
    
--  "ID" INTEGER PRIMARY KEY NOT NULL, 


  		




  7
  
  
    
--  "Byte" TINYINT DEFAULT NULL, 


  		




  8
  
  
    
--  "Bool1" BOOLEAN DEFAULT NULL, 


  		




  9
  
  
    
--  "Long" BIGINT DEFAULT NULL, 


  		




  10
  
  
    
--  "Timestamp" TIMESTAMP DEFAULT NULL, 


  		




  11
  
  
    
--  "Date" DATE DEFAULT NULL, 


  		




  12
  
  
    
--  "Time" TIME DEFAULT NULL, 


  		




  13
  
  
    
--  "Bool2" BOOLEAN DEFAULT NULL, 


  		




  14
  
  
    
--  "String" VARCHAR(30) DEFAULT NULL, 


  		




  15
  
  
    
--  "Bool3" BOOLEAN DEFAULT NULL, 


  		




  16
  
  
    
--  "Double" DOUBLE DEFAULT NULL, 


  		




  17
  
  
    
--  "Bool4" BOOLEAN DEFAULT NULL, 


  		




  18
  
  
    
--  "Float" REAL DEFAULT NULL, 


  		




  19
  
  
    
--  "Bool5" BOOLEAN DEFAULT NULL, 


  		




  20
  
  
    
--  "Decimal" DECIMAL(6,3) DEFAULT NULL, 


  		




  21
  
  
    
-- H2 Spatial usa la constraint para averiguar el tipo de geometria, 


  		




  22
  
  
    
-- la dimension de esta (2D,3D,...) y el SRID.


  		




  23
  
  
    
--  "Geometry" GEOMETRY(1) CHECK NVL2("Geometry", ST_GeometryTypeCode("Geometry") = 1 AND ST_CoordDim("Geometry") = 2 AND ST_SRID("Geometry") = 4326, TRUE) 


  		




  24
  
  
    
--);


  		




  25
  
  
    

  		




  26
  
  
    

  		




  27
  
  
    

  		




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


  		




  29
  
  
    

  		




  
  9
  
    
-- begin createTable


  		




  30
  10
  
    
CREATE TABLE IF NOT EXISTS gpkg_extensions (


  		




  31
  11
  
    
    table_name TEXT, 


  		




  32
  12
  
    
    column_name TEXT, 


  		




  33
  13
  
    
    extension_name TEXT NOT NULL, 


  		




  34
  14
  
    
    definition TEXT NOT NULL, 


  		




  35
  15
  
    
    scope TEXT NOT NULL, 


  		




  36
  
  
    
    CONSTRAINT ge_tce UNIQUE (table_name, column_name, extension_name) );


  		




  
  16
  
    
    CONSTRAINT ge_tce UNIQUE (table_name, column_name, extension_name) )


  		




  
  17
  
    
-- end createTable


  		




  37
  18
  
    

  		




  
  19
  
    
-- begin createTable


  		




  38
  20
  
    
CREATE TABLE IF NOT EXISTS "gpkg_geometry_columns" (


  		




  39
  21
  
    
	"table_name"	TEXT NOT NULL,


  		




  40
  22
  
    
	"column_name"	TEXT NOT NULL,


  		




  ......




  46
  28
  
    
	CONSTRAINT "fk_gc_tn" FOREIGN KEY("table_name") REFERENCES "gpkg_contents"("table_name"),


  		




  47
  29
  
    
	CONSTRAINT "uk_gc_table_name" UNIQUE("table_name"),


  		




  48
  30
  
    
	CONSTRAINT "fk_gc_srs" FOREIGN KEY("srs_id") REFERENCES "gpkg_spatial_ref_sys"("srs_id")


  		




  49
  
  
    
);


  		




  
  31
  
    
)


  		




  
  32
  
    
-- end createTable


  		




  50
  33
  
    

  		




  51
  
  
    
INSERT INTO "main"."gpkg_contents" ("table_name", "data_type", "identifier", "description", "last_change", "min_x", "min_y", "max_x", "max_y", "srs_id") VALUES ('test', 'features', 'test', '', '2023-10-31 10:41:12.628', NULL, NULL, NULL, NULL, NULL);


  		




  
  34
  
    
-- begin createTable


  		




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


  		




  
  36
  
    
-- end createTable


  		




  52
  37
  
    

  		




  53
  
  
    
CREATE TABLE "test" ("ID" MEDIUMINT 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 );


  		




  
  38
  
    
-- begin createTable


  		




  
  39
  
    
CREATE TABLE "test" ("ID" MEDIUMINT 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 )


  		




  
  40
  
    
-- end createTable


  		




  54
  41
  
    

  		




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


  		




  
  42
  
    
-- begin createTable


  		




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


  		




  
  44
  
    
-- end createTable


  		




  56
  45
  
    

  		




  
  46
  
    
-- begin createTable


  		




  57
  47
  
    
CREATE TABLE IF NOT EXISTS gpkg_extensions (


  		




  58
  48
  
    
    table_name TEXT, 


  		




  59
  49
  
    
    column_name TEXT, 


  		




  60
  50
  
    
    extension_name TEXT NOT NULL, 


  		




  61
  51
  
    
    definition TEXT NOT NULL, 


  		




  62
  52
  
    
    scope TEXT NOT NULL, 


  		




  63
  
  
    
    CONSTRAINT ge_tce UNIQUE (table_name, column_name, extension_name) );


  		




  
  53
  
    
    CONSTRAINT ge_tce UNIQUE (table_name, column_name, extension_name) )


  		




  
  54
  
    
-- end createTable


  		




  64
  55
  
    

  		




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


  		




  
  56
  
    
-- begin createTable


  		




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


  		




  
  58
  
    
-- end createTable


  		




  66
  59
  
    

  		




  
  60
  
    
-- begin createTable


  		




  67
  61
  
    
CREATE TRIGGER "rtree_test_Geometry_delete" AFTER DELETE ON "test"


  		




  68
  62
  
    
  WHEN old."Geometry" NOT NULL


  		




  69
  63
  
    
BEGIN


  		




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


  		




  71
  
  
    
END;


  		




  
  65
  
    
END


  		




  
  66
  
    
-- end createTable


  		




  72
  67
  
    

  		




  
  68
  
    
-- begin createTable


  		




  73
  69
  
    
CREATE TRIGGER "rtree_test_Geometry_insert" AFTER INSERT ON "test"


  		




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


  		




  75
  71
  
    
BEGIN


  		




  ......




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


  		




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


  		




  80
  76
  
    
  );


  		




  81
  
  
    
END;


  		




  
  77
  
    
END


  		




  
  78
  
    
-- end createTable


  		




  82
  79
  
    

  		




  
  80
  
    
-- begin createTable


  		




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


  		




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


  		




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


  		




  ......




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


  		




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


  		




  91
  89
  
    
  );


  		




  92
  
  
    
END;


  		




  
  90
  
    
END


  		




  
  91
  
    
-- end createTable


  		




  93
  92
  
    

  		




  
  93
  
    
-- begin createTable


  		




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


  		




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


  		




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


  		




  97
  97
  
    
BEGIN


  		




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


  		




  99
  
  
    
END;


  		




  
  99
  
    
END


  		




  
  100
  
    
-- end createTable


  		




  100
  101
  
    

  		




  
  102
  
    
-- begin createTable


  		




  101
  103
  
    
CREATE TRIGGER "rtree_test_Geometry_update3" AFTER UPDATE ON "test"


  		




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


  		




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


  		




  ......




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


  		




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


  		




  110
  112
  
    
  );


  		




  111
  
  
    
END;


  		




  
  113
  
    
END


  		




  
  114
  
    
-- end createTable


  		




  112
  115
  
    

  		




  
  116
  
    
-- begin createTable


  		




  113
  117
  
    
CREATE TRIGGER "rtree_test_Geometry_update4" AFTER UPDATE ON "test"


  		




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


  		




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


  		




  116
  120
  
    
BEGIN


  		




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


  		




  118
  
  
    
END;


  		




  
  122
  
    
END


  		




  
  123
  
    
-- end createTable


  		




  119
  124
  
    

  		




  
  125
  
    
-- begin createTable


  		




  120
  126
  
    
INSERT INTO "gpkg_extensions" (


  		




  121
  127
  
    
    "table_name", 


  		




  122
  128
  
    
    "column_name", 


  		




  ......




  129
  135
  
    
    'gpkg_rtree_index', 


  		




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


  		




  131
  137
  
    
    'read-write'


  		




  132
  
  
    
);


  		




  
  138
  
    
)


  		




  
  139
  
    
-- end createTable


  		












Also available in:  Unified diff