svn-gvsig-desktop / trunk / org.gvsig.desktop / org.gvsig.desktop.plugin / org.gvsig.sqlite / org.gvsig.sqlite.provider / src / test / resources / org / gvsig / sqlite / dal / performChanges.sql @ 47787
History | View | Annotate | Download (5.04 KB)
1 | 47606 | fdiaz | -- normalize-spaces false
|
---|---|---|---|
2 | -- strip-start false
|
||
3 | -- remove-nl false
|
||
4 | 47539 | jjdelcerro | |
5 | 47606 | fdiaz | -- begin performChanges
|
6 | INSERT INTO "test" ( "ID", "Byte", "Bool1", "Long", "Timestamp", "Date", "Time", "Bool2", "String", "Bool3", "Double", "Bool4", "Float", "Bool5", "Decimal", "Geometry" ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) |
||
7 | -- end performChanges
|
||
8 | |||
9 | -- begin performChanges
|
||
10 | DELETE FROM "test" WHERE ( ("ID") = (?) ) |
||
11 | -- end performChanges
|
||
12 | |||
13 | -- begin performChanges
|
||
14 | UPDATE "test" SET "Byte" = ?, "Bool1" = ?, "Long" = ?, "Timestamp" = ?, "Date" = ?, "Time" = ?, "Bool2" = ?, "String" = ?, "Bool3" = ?, "Double" = ?, "Bool4" = ?, "Float" = ?, "Bool5" = ?, "Decimal" = ?, "Geometry" = ? WHERE ( ("ID") = (?) ) |
||
15 | -- end performChanges
|
||
16 | |||
17 | -- begin performChanges
|
||
18 | ALTER TABLE "test" ADD COLUMN "Byte" TINYINT DEFAULT NULL NULL |
||
19 | -- end performChanges
|
||
20 | |||
21 | -- begin performChanges
|
||
22 | ALTER TABLE "test" ADD COLUMN "Bool1" BOOLEAN DEFAULT NULL NULL |
||
23 | -- end performChanges
|
||
24 | |||
25 | -- begin performChanges
|
||
26 | ALTER TABLE "test" ADD COLUMN "Long" BIGINT DEFAULT NULL NULL |
||
27 | -- end performChanges
|
||
28 | |||
29 | -- begin performChanges
|
||
30 | ALTER TABLE "test" ADD COLUMN "Timestamp" DATETIME DEFAULT NULL NULL |
||
31 | -- end performChanges
|
||
32 | |||
33 | -- begin performChanges
|
||
34 | ALTER TABLE "test" ADD COLUMN "Date" DATE DEFAULT NULL NULL |
||
35 | -- end performChanges
|
||
36 | |||
37 | -- begin performChanges
|
||
38 | ALTER TABLE "test" ADD COLUMN "Time" TIME DEFAULT NULL NULL |
||
39 | -- end performChanges
|
||
40 | |||
41 | -- begin performChanges
|
||
42 | ALTER TABLE "test" ADD COLUMN "Bool2" BOOLEAN DEFAULT NULL NULL |
||
43 | -- end performChanges
|
||
44 | |||
45 | -- begin performChanges
|
||
46 | ALTER TABLE "test" ADD COLUMN "String" VARCHAR(30) DEFAULT NULL NULL |
||
47 | -- end performChanges
|
||
48 | |||
49 | -- begin performChanges
|
||
50 | ALTER TABLE "test" ADD COLUMN "Bool3" BOOLEAN DEFAULT NULL NULL |
||
51 | -- end performChanges
|
||
52 | |||
53 | -- begin performChanges
|
||
54 | ALTER TABLE "test" ADD COLUMN "Double" DOUBLE PRECISION DEFAULT NULL NULL |
||
55 | -- end performChanges
|
||
56 | |||
57 | -- begin performChanges
|
||
58 | ALTER TABLE "test" ADD COLUMN "Bool4" BOOLEAN DEFAULT NULL NULL |
||
59 | -- end performChanges
|
||
60 | |||
61 | -- begin performChanges
|
||
62 | ALTER TABLE "test" ADD COLUMN "Float" FLOAT DEFAULT NULL NULL |
||
63 | -- end performChanges
|
||
64 | |||
65 | -- begin performChanges
|
||
66 | ALTER TABLE "test" ADD COLUMN "Bool5" BOOLEAN DEFAULT NULL NULL |
||
67 | -- end performChanges
|
||
68 | |||
69 | -- begin performChanges
|
||
70 | ALTER TABLE "test" ADD COLUMN "Decimal" DECIMAL(6,3) DEFAULT NULL NULL |
||
71 | -- end performChanges
|
||
72 | |||
73 | -- begin performChanges
|
||
74 | ALTER TABLE "test" ADD COLUMN "Geometry" BLOB DEFAULT NULL NULL |
||
75 | -- end performChanges
|
||
76 | |||
77 | -- begin performChanges
|
||
78 | 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) ) |
||
79 | -- end performChanges
|
||
80 | |||
81 | -- begin performChanges
|
||
82 | CREATE VIRTUAL TABLE "rtree_test_Geometry" USING rtree(id, minx, maxx, miny, maxy) |
||
83 | -- end performChanges
|
||
84 | |||
85 | -- begin performChanges
|
||
86 | CREATE TRIGGER "rtree_test_Geometry_delete" AFTER DELETE ON "test" |
||
87 | WHEN old."Geometry" NOT NULL |
||
88 | BEGIN
|
||
89 | DELETE FROM "rtree_test_Geometry" WHERE ID = OLD."ID"; |
||
90 | END
|
||
91 | -- end performChanges
|
||
92 | |||
93 | -- begin performChanges
|
||
94 | CREATE TRIGGER "rtree_test_Geometry_insert" AFTER INSERT ON "test" |
||
95 | WHEN (new."Geometry" NOT NULL AND NOT ST_IsEmpty(NEW."Geometry")) |
||
96 | BEGIN
|
||
97 | INSERT OR REPLACE INTO "rtree_test_Geometry" VALUES ( |
||
98 | NEW."ID",
|
||
99 | ST_MinX(NEW."Geometry"), ST_MaxX(NEW."Geometry"), |
||
100 | ST_MinY(NEW."Geometry"), ST_MaxY(NEW."Geometry") |
||
101 | 47539 | jjdelcerro | ); |
102 | 47606 | fdiaz | END
|
103 | -- end performChanges
|
||
104 | 47539 | jjdelcerro | |
105 | 47606 | fdiaz | -- begin performChanges
|
106 | CREATE TRIGGER "rtree_test_Geometry_update1" AFTER UPDATE OF "Geometry" ON "test" |
||
107 | WHEN OLD."ID" = NEW."ID" AND |
||
108 | (NEW."Geometry" NOTNULL AND NOT ST_IsEmpty(NEW."Geometry")) |
||
109 | BEGIN
|
||
110 | INSERT OR REPLACE INTO "rtree_test_Geometry" VALUES ( |
||
111 | NEW."ID",
|
||
112 | ST_MinX(NEW."Geometry"), ST_MaxX(NEW."Geometry"), |
||
113 | ST_MinY(NEW."Geometry"), ST_MaxY(NEW."Geometry") |
||
114 | ); |
||
115 | END
|
||
116 | -- end performChanges
|
||
117 | 47539 | jjdelcerro | |
118 | 47606 | fdiaz | -- begin performChanges
|
119 | CREATE TRIGGER "rtree_test_Geometry_update2" AFTER UPDATE OF "Geometry" ON "test" |
||
120 | WHEN OLD."ID" = NEW."ID" AND |
||
121 | (NEW."Geometry" ISNULL OR ST_IsEmpty(NEW."Geometry")) |
||
122 | BEGIN
|
||
123 | DELETE FROM "rtree_test_Geometry" WHERE ID = OLD."ID"; |
||
124 | END
|
||
125 | -- end performChanges
|
||
126 | 47539 | jjdelcerro | |
127 | 47606 | fdiaz | -- begin performChanges
|
128 | CREATE TRIGGER "rtree_test_Geometry_update3" AFTER UPDATE ON "test" |
||
129 | WHEN OLD."ID" != NEW."ID" AND |
||
130 | (NEW."Geometry" NOTNULL AND NOT ST_IsEmpty(NEW."Geometry")) |
||
131 | BEGIN
|
||
132 | DELETE FROM "rtree_test_Geometry" WHERE ID = OLD."ID"; |
||
133 | INSERT OR REPLACE INTO "rtree_test_Geometry" VALUES ( |
||
134 | NEW."ID",
|
||
135 | ST_MinX(NEW."Geometry"), ST_MaxX(NEW."Geometry"), |
||
136 | ST_MinY(NEW."Geometry"), ST_MaxY(NEW."Geometry") |
||
137 | ); |
||
138 | END
|
||
139 | -- end performChanges
|
||
140 | 47539 | jjdelcerro | |
141 | 47606 | fdiaz | -- begin performChanges
|
142 | CREATE TRIGGER "rtree_test_Geometry_update4" AFTER UPDATE ON "test" |
||
143 | WHEN OLD."ID" != NEW."ID" AND |
||
144 | (NEW."Geometry" ISNULL OR ST_IsEmpty(NEW."Geometry")) |
||
145 | BEGIN
|
||
146 | DELETE FROM "rtree_test_Geometry" WHERE ID IN (OLD."ID", NEW."ID"); |
||
147 | END
|
||
148 | -- end performChanges
|
||
149 | 47539 | jjdelcerro | |
150 | 47606 | fdiaz | -- begin performChanges
|
151 | INSERT INTO "gpkg_extensions" ( |
||
152 | "table_name",
|
||
153 | "column_name",
|
||
154 | "extension_name",
|
||
155 | "definition",
|
||
156 | "scope"
|
||
157 | ) VALUES (
|
||
158 | 'test',
|
||
159 | 'Geometry',
|
||
160 | 'gpkg_rtree_index',
|
||
161 | 'http://www.geopackage.org/spec/#extension_rtree',
|
||
162 | 'read-write'
|
||
163 | ) |
||
164 | -- end performChanges |