svn-gvsig-desktop / trunk / org.gvsig.desktop / org.gvsig.desktop.plugin / org.gvsig.sqlite / org.gvsig.sqlite.provider / src / test / java / org / gvsig / sqlite / dal / SQLiteSQLBuilderTest.java @ 47606
History | View | Annotate | Download (29.3 KB)
1 | 47539 | jjdelcerro | package org.gvsig.sqlite.dal; |
---|---|---|---|
2 | |||
3 | import junit.framework.TestCase; |
||
4 | 47606 | fdiaz | import org.apache.commons.io.FilenameUtils; |
5 | 47539 | jjdelcerro | import org.apache.commons.lang3.ArrayUtils; |
6 | import org.cresques.cts.IProjection; |
||
7 | import org.gvsig.expressionevaluator.ExpressionBuilder; |
||
8 | import org.gvsig.expressionevaluator.GeometryExpressionBuilder; |
||
9 | import org.gvsig.fmap.crs.CRSFactory; |
||
10 | 47606 | fdiaz | import org.gvsig.fmap.dal.DALLocator; |
11 | 47539 | jjdelcerro | import org.gvsig.fmap.dal.SQLBuilder; |
12 | 47606 | fdiaz | import static org.gvsig.fmap.dal.SQLBuilder.PROP_FEATURE_TYPE; |
13 | import static org.gvsig.fmap.dal.SQLBuilder.PROP_TABLE; |
||
14 | import static org.gvsig.fmap.dal.SQLBuilder.PROP_TABLENAME; |
||
15 | 47539 | jjdelcerro | import org.gvsig.fmap.dal.SQLBuilder.Privilege; |
16 | 47606 | fdiaz | import org.gvsig.fmap.dal.feature.EditableFeatureType; |
17 | 47539 | jjdelcerro | import org.gvsig.fmap.dal.feature.spi.SQLBuilderBase; |
18 | import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference; |
||
19 | import org.gvsig.fmap.dal.store.jdbc2.spi.operations.OperationsFactoryBase; |
||
20 | import org.gvsig.fmap.geom.DataTypes; |
||
21 | import org.gvsig.fmap.geom.Geometry; |
||
22 | import org.gvsig.fmap.geom.GeometryLocator; |
||
23 | import org.gvsig.fmap.geom.GeometryManager; |
||
24 | import org.gvsig.fmap.geom.primitive.Polygon; |
||
25 | 47606 | fdiaz | import org.gvsig.sqlite.dal.geopackage.GeopackageUtils; |
26 | import org.gvsig.sqlite.dal.geopackage.index.GeopackageIndexRTree; |
||
27 | 47539 | jjdelcerro | import org.gvsig.tools.library.impl.DefaultLibrariesInitializer; |
28 | |||
29 | 47540 | jjdelcerro | public class SQLiteSQLBuilderTest extends TestCase { |
30 | 47539 | jjdelcerro | |
31 | 47540 | jjdelcerro | public SQLiteSQLBuilderTest(String testName) { |
32 | 47539 | jjdelcerro | super(testName);
|
33 | } |
||
34 | |||
35 | @Override
|
||
36 | protected void setUp() throws Exception { |
||
37 | super.setUp();
|
||
38 | new DefaultLibrariesInitializer().fullInitialize();
|
||
39 | } |
||
40 | |||
41 | @Override
|
||
42 | protected void tearDown() throws Exception { |
||
43 | super.tearDown();
|
||
44 | } |
||
45 | |||
46 | private SQLBuilder createSQLBuilder() throws Exception { |
||
47 | SQLiteHelper helper = TestUtils.createJDBCHelper(); |
||
48 | return new SQLiteSQLBuilder(helper); |
||
49 | } |
||
50 | |||
51 | 47606 | fdiaz | public void testCalculateEnvelopeOfColumn() throws Exception { |
52 | 47539 | jjdelcerro | |
53 | TableReference table = new OperationsFactoryBase.DefaultTableReference("master","dbo","test1", null); |
||
54 | String columnName = "the_geom"; |
||
55 | |||
56 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
57 | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
58 | |||
59 | sqlbuilder.select().column().value( |
||
60 | expbuilder.as_geometry( |
||
61 | expbuilder.ST_ExtentAggregate( |
||
62 | expbuilder.column(columnName) |
||
63 | ) |
||
64 | ) |
||
65 | ); |
||
66 | //sqlbuilder.select().group_by(expbuilder.column(columnName));
|
||
67 | sqlbuilder.select().from().table() |
||
68 | .database(table.getDatabase()) |
||
69 | .schema(table.getSchema()) |
||
70 | .name(table.getTable()); |
||
71 | sqlbuilder.select().from().subquery(table.getSubquery()); |
||
72 | |||
73 | sqlbuilder.select().where().and( |
||
74 | expbuilder.not_is_null(expbuilder.column(columnName)) |
||
75 | ); |
||
76 | 47606 | fdiaz | String exp = "SELECT ( ST_Extent(\"the_geom\") ) FROM \"test1\" WHERE ( (\"the_geom\") IS NOT NULL )"; |
77 | |||
78 | System.out.println("# Test:: testCalculateEnvelopeOfColumn"); |
||
79 | 47539 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
80 | 47606 | fdiaz | System.out.println("# EXP:: " + exp); |
81 | 47539 | jjdelcerro | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
82 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
83 | 47606 | fdiaz | assertEquals( |
84 | exp, |
||
85 | 47539 | jjdelcerro | sqlbuilder.toString() |
86 | ); |
||
87 | assertEquals( |
||
88 | "[the_geom]",
|
||
89 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
90 | ); |
||
91 | assertEquals( |
||
92 | "[]",
|
||
93 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
94 | ); |
||
95 | } |
||
96 | |||
97 | 47606 | fdiaz | public void testCalculateEnvelope() throws Exception { |
98 | try {
|
||
99 | 47539 | jjdelcerro | GeometryManager geometryManager = GeometryLocator.getGeometryManager(); |
100 | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
||
101 | |||
102 | Polygon limit = geometryManager.createPolygon(Geometry.SUBTYPES.GEOM2D);
|
||
103 | limit.addVertex(0, 0); |
||
104 | limit.addVertex(0, 100); |
||
105 | limit.addVertex(100, 100); |
||
106 | limit.addVertex(100, 0); |
||
107 | limit.addVertex(0, 0); |
||
108 | |||
109 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
110 | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
111 | |||
112 | sqlbuilder.select().column().value( |
||
113 | expbuilder.as_geometry( |
||
114 | expbuilder.ST_ExtentAggregate( |
||
115 | 47606 | fdiaz | expbuilder.column("Geometry")
|
116 | 47539 | jjdelcerro | ) |
117 | ) |
||
118 | ).as("envelope");
|
||
119 | 47606 | fdiaz | sqlbuilder.select().from().table().database("master").schema("dbo").name("test"); |
120 | 47539 | jjdelcerro | sqlbuilder.select().where().set( |
121 | expbuilder.ST_Intersects( |
||
122 | expbuilder.ST_Envelope( |
||
123 | 47606 | fdiaz | expbuilder.column("Geometry")
|
124 | 47539 | jjdelcerro | ), |
125 | expbuilder.geometry(limit, proj) |
||
126 | ) |
||
127 | ); |
||
128 | sqlbuilder.select().where().and( |
||
129 | 47606 | fdiaz | expbuilder.custom("Long = 27")
|
130 | 47539 | jjdelcerro | ); |
131 | 47606 | fdiaz | String exp = "SELECT ( ST_Extent(\"Geometry\") ) AS \"envelope\" FROM \"test\" WHERE (ST_Intersects((ST_Envelope(\"Geometry\")),((x'010300000001000000050000000000000000000000000000000000000000000000000000000000000000005940000000000000594000000000000059400000000000005940000000000000000000000000000000000000000000000000'))) AND Long = 27)"; |
132 | 47539 | jjdelcerro | System.out.println("# Test:: testCalulateEnvelope"); |
133 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
134 | 47606 | fdiaz | System.out.println("# EXP:: " + exp); |
135 | 47539 | jjdelcerro | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
136 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
137 | assertEquals( |
||
138 | 47606 | fdiaz | exp, |
139 | 47539 | jjdelcerro | sqlbuilder.toString() |
140 | ); |
||
141 | assertEquals( |
||
142 | 47606 | fdiaz | "[Geometry]",
|
143 | 47539 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
144 | ); |
||
145 | assertEquals( |
||
146 | "[]",
|
||
147 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
148 | ); |
||
149 | 47606 | fdiaz | } catch (Throwable th) { |
150 | th.printStackTrace(); |
||
151 | throw th;
|
||
152 | } |
||
153 | 47539 | jjdelcerro | } |
154 | |||
155 | 47606 | fdiaz | public void testCalculateEnvelopeWithSpatialIndex() throws Exception { |
156 | try {
|
||
157 | GeometryManager geometryManager = GeometryLocator.getGeometryManager(); |
||
158 | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
||
159 | |||
160 | Polygon limit = geometryManager.createPolygon(Geometry.SUBTYPES.GEOM2D);
|
||
161 | limit.addVertex(0, 0); |
||
162 | limit.addVertex(0, 100); |
||
163 | limit.addVertex(100, 100); |
||
164 | limit.addVertex(100, 0); |
||
165 | limit.addVertex(0, 0); |
||
166 | |||
167 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
168 | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
169 | |||
170 | sqlbuilder.select().column().value( |
||
171 | expbuilder.as_geometry( |
||
172 | expbuilder.ST_ExtentAggregate( |
||
173 | expbuilder.column("Geometry")
|
||
174 | ) |
||
175 | ) |
||
176 | ).as("envelope");
|
||
177 | sqlbuilder.select().from().table().database("master").schema("dbo").name("test"); |
||
178 | sqlbuilder.select().where().set( |
||
179 | expbuilder.ST_Intersects( |
||
180 | expbuilder.column("Geometry"),
|
||
181 | expbuilder.geometry(limit, proj) |
||
182 | ) |
||
183 | ); |
||
184 | sqlbuilder.select().where().and( |
||
185 | expbuilder.custom("Long = 27")
|
||
186 | ); |
||
187 | String exp = "SELECT ( ST_Extent(\"Geometry\") ) AS \"envelope\" FROM \"test\" WHERE (( ((\"Long\" IN (SELECT \"rtree_test_Geometry\".\"id\" FROM \"rtree_test_Geometry\" WHERE \"rtree_test_Geometry\".\"minx\" <= (100.0) AND \"rtree_test_Geometry\".\"miny\" <= (100.0) AND \"rtree_test_Geometry\".\"maxx\" >= (0.0) AND \"rtree_test_Geometry\".\"maxy\" >= (0.0)))) AND ST_Intersects((\"Geometry\"),((x'010300000001000000050000000000000000000000000000000000000000000000000000000000000000005940000000000000594000000000000059400000000000005940000000000000000000000000000000000000000000000000')) )) AND Long = 27)"; |
||
188 | EditableFeatureType ft = DALLocator.getDataManager().createFeatureType(); |
||
189 | ft.add("Geometry", DataTypes.GEOMETRY)
|
||
190 | .setGeometryType(Geometry.TYPES.POLYGON, Geometry.SUBTYPES.GEOM2D) |
||
191 | .setSRS(proj) |
||
192 | .setIsIndexed(true);
|
||
193 | ft.add("Long",DataTypes.INTEGER).setIsPrimaryKey(true); |
||
194 | GeopackageIndexRTree index = new GeopackageIndexRTree();
|
||
195 | index.setHasIndex(ft.getDefaultGeometryAttribute(), true);
|
||
196 | |||
197 | sqlbuilder.setProperties( |
||
198 | sqlbuilder.select(), |
||
199 | null,
|
||
200 | PROP_FEATURE_TYPE, ft.getNotEditableCopy(), |
||
201 | PROP_TABLENAME, "test"
|
||
202 | ); |
||
203 | |||
204 | System.out.println("# Test:: testCalculateEnvelopeWithSpatialIndex"); |
||
205 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
206 | System.out.println("# EXP:: " + exp); |
||
207 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
208 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
209 | assertEquals( |
||
210 | exp, |
||
211 | sqlbuilder.toString() |
||
212 | ); |
||
213 | assertEquals( |
||
214 | "[Geometry]",
|
||
215 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
216 | ); |
||
217 | assertEquals( |
||
218 | "[]",
|
||
219 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
220 | ); |
||
221 | } catch (Throwable th){ |
||
222 | th.printStackTrace(); |
||
223 | throw th;
|
||
224 | } |
||
225 | } |
||
226 | |||
227 | 47539 | jjdelcerro | public void testCount() throws Exception { |
228 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
229 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
230 | |||
231 | sqlbuilder.select().column().value(sqlbuilder.count().all()); |
||
232 | sqlbuilder.select().from().table().database("master").schema("dbo").name("test1"); |
||
233 | sqlbuilder.select().from().subquery(null);
|
||
234 | sqlbuilder.select().where().set( expbuilder.custom("pp = 200"));
|
||
235 | |||
236 | 47606 | fdiaz | String exp = "SELECT COUNT(*) FROM \"test1\" WHERE pp = 200"; |
237 | 47539 | jjdelcerro | System.out.println("# Test:: testCount"); |
238 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
239 | 47606 | fdiaz | System.out.println("# EXP:: " + exp); |
240 | 47539 | jjdelcerro | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
241 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
242 | |||
243 | //# Test:: testCount
|
||
244 | //# SQL:: SELECT COUNT(*) FROM "test1" WHERE pp = 200
|
||
245 | //# Variables:: []
|
||
246 | //# Parametros:: []
|
||
247 | |||
248 | assertEquals( |
||
249 | 47606 | fdiaz | exp, |
250 | 47539 | jjdelcerro | sqlbuilder.toString() |
251 | ); |
||
252 | assertEquals( |
||
253 | "[]",
|
||
254 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
255 | ); |
||
256 | assertEquals( |
||
257 | "[]",
|
||
258 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
259 | ); |
||
260 | } |
||
261 | |||
262 | public void testCreateTable() throws Exception { |
||
263 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
264 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
265 | |||
266 | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
||
267 | |||
268 | sqlbuilder.create_table().table().database("master").schema("dbo").name("test1"); |
||
269 | sqlbuilder.create_table().add_column( |
||
270 | "name",
|
||
271 | DataTypes.STRING, |
||
272 | 45,
|
||
273 | 0,
|
||
274 | 0,
|
||
275 | false,
|
||
276 | false,
|
||
277 | true,
|
||
278 | false,
|
||
279 | null
|
||
280 | ); |
||
281 | sqlbuilder.create_table().add_column( |
||
282 | "id",
|
||
283 | DataTypes.INT, |
||
284 | 0,
|
||
285 | 0,
|
||
286 | 0,
|
||
287 | true,
|
||
288 | false,
|
||
289 | false,
|
||
290 | true,
|
||
291 | 0
|
||
292 | ); |
||
293 | |||
294 | sqlbuilder.create_table().add_geometry_column("geom", Geometry.TYPES.MULTIPOLYGON, Geometry.SUBTYPES.GEOM2D, proj, true, true); |
||
295 | |||
296 | 47606 | fdiaz | String exp = "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)); CREATE TABLE IF NOT EXISTS gpkg_extensions ( " |
297 | + "table_name TEXT, "
|
||
298 | + "column_name TEXT, "
|
||
299 | + "extension_name TEXT NOT NULL, "
|
||
300 | + "definition TEXT NOT NULL, "
|
||
301 | + "scope TEXT NOT NULL, "
|
||
302 | + "CONSTRAINT ge_tce UNIQUE (table_name, column_name, extension_name) ); CREATE TABLE IF NOT EXISTS \"gpkg_geometry_columns\" ( "
|
||
303 | + "\"table_name\" TEXT NOT NULL, "
|
||
304 | + "\"column_name\" TEXT NOT NULL, "
|
||
305 | + "\"geometry_type_name\" TEXT NOT NULL, "
|
||
306 | + "\"srs_id\" INTEGER NOT NULL, "
|
||
307 | + "\"z\" TINYINT NOT NULL, "
|
||
308 | + "\"m\" TINYINT NOT NULL, "
|
||
309 | + "CONSTRAINT \"pk_geom_cols\" PRIMARY KEY(\"table_name\",\"column_name\"), "
|
||
310 | + "CONSTRAINT \"fk_gc_tn\" FOREIGN KEY(\"table_name\") REFERENCES \"gpkg_contents\"(\"table_name\"), "
|
||
311 | + "CONSTRAINT \"uk_gc_table_name\" UNIQUE(\"table_name\"), "
|
||
312 | + "CONSTRAINT \"fk_gc_srs\" FOREIGN KEY(\"srs_id\") REFERENCES \"gpkg_spatial_ref_sys\"(\"srs_id\") "
|
||
313 | + "); INSERT INTO \"gpkg_contents\" (\"table_name\", \"data_type\", \"identifier\", \"description\", \"last_change\", \"min_x\", \"min_y\", \"max_x\", \"max_y\", \"srs_id\") VALUES ('test1', 'features', 'test1', '', '*', NULL, NULL, NULL, NULL, NULL); CREATE TABLE \"test1\" (\"name\" VARCHAR(45) DEFAULT NULL, \"id\" MEDIUMINT PRIMARY KEY AUTOINCREMENT DEFAULT '0' NOT NULL, \"geom\" BLOB DEFAULT NULL ); INSERT INTO \"gpkg_geometry_columns\" (\"table_name\", \"column_name\", \"geometry_type_name\", \"srs_id\", \"z\", \"m\") VALUES ('test1', 'geom', 'MULTIPOLYGON', '4326', '0', '0')";
|
||
314 | |||
315 | 47539 | jjdelcerro | // CREATE TABLE "test1" ("name" VARCHAR(45) DEFAULT NULL, "id" INTEGER PRIMARY KEY AUTO_INCREMENT DEFAULT '0' NOT NULL, "geom" GEOMETRY ); ALTER TABLE "test1" ADD CONSTRAINT IF NOT EXISTS "constraint_test1_geom_dim" CHECK ST_CoordDim("geom") = 2
|
316 | System.out.println("# Test:: testCreateTable"); |
||
317 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
318 | 47606 | fdiaz | System.out.println("# EXP:: " + exp); |
319 | 47539 | jjdelcerro | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
320 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
321 | 47606 | fdiaz | assertTrue(FilenameUtils.wildcardMatch(sqlbuilder.toString(), exp)); |
322 | 47539 | jjdelcerro | assertEquals( |
323 | "[]",
|
||
324 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
325 | ); |
||
326 | assertEquals( |
||
327 | "[]",
|
||
328 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
329 | ); |
||
330 | } |
||
331 | |||
332 | public void testDropTable() throws Exception { |
||
333 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
334 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
335 | |||
336 | sqlbuilder.drop_table().table().database("master").schema("dbo").name("test1"); |
||
337 | |||
338 | // DROP TABLE "test1"
|
||
339 | |||
340 | 47606 | fdiaz | String exp = "DELETE FROM \"gpkg_geometry_columns\" WHERE \"table_name\" = 'test1'; DELETE FROM \"gpkg_contents\" WHERE \"identifier\" = 'test1'; DELETE FROM \"gpkg_extensions\" WHERE \"table_name\" = 'test1'; DROP TABLE \"test1\""; |
341 | 47539 | jjdelcerro | System.out.println("# Test:: testDropTable"); |
342 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
343 | 47606 | fdiaz | System.out.println("# EXP:: " + exp); |
344 | 47539 | jjdelcerro | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
345 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
346 | assertEquals( |
||
347 | 47606 | fdiaz | exp, |
348 | 47539 | jjdelcerro | sqlbuilder.toString() |
349 | ); |
||
350 | assertEquals( |
||
351 | "[]",
|
||
352 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
353 | ); |
||
354 | assertEquals( |
||
355 | "[]",
|
||
356 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
357 | ); |
||
358 | } |
||
359 | |||
360 | public void testFetchFeatureProviderByReference() throws Exception { |
||
361 | SQLBuilder sqlbuilder = new SQLBuilderBase();
|
||
362 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
363 | |||
364 | String value = "yoyo"; |
||
365 | sqlbuilder.select().column().name("name");
|
||
366 | sqlbuilder.select().column().name("id");
|
||
367 | sqlbuilder.select().column().name("geom").as_geometry();
|
||
368 | sqlbuilder.select().from().table().database("master").schema("dbo").name("test1"); |
||
369 | sqlbuilder.select().where().set( |
||
370 | expbuilder.eq( |
||
371 | expbuilder.column("name"),
|
||
372 | expbuilder.parameter(value).as_constant() |
||
373 | ) |
||
374 | ); |
||
375 | sqlbuilder.select().limit(1);
|
||
376 | |||
377 | // SELECT "name", "id", ST_AsBinary("geom") FROM "master"."dbo"."test1" WHERE ( ("name") = (?) ) LIMIT 1
|
||
378 | |||
379 | 47606 | fdiaz | String exp = "SELECT \"name\", \"id\", ST_AsBinary(\"geom\") FROM \"master\".\"dbo\".\"test1\" WHERE ( (\"name\") = (?) ) LIMIT 1"; |
380 | 47539 | jjdelcerro | System.out.println("# Test:: testFetchFeatureProviderByReference"); |
381 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
382 | 47606 | fdiaz | System.out.println("# EXP:: " + exp); |
383 | 47539 | jjdelcerro | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
384 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
385 | assertEquals( |
||
386 | 47606 | fdiaz | exp, |
387 | 47539 | jjdelcerro | sqlbuilder.toString() |
388 | ); |
||
389 | assertEquals( |
||
390 | "[geom, id, name]",
|
||
391 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
392 | ); |
||
393 | assertEquals( |
||
394 | "['yoyo']",
|
||
395 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
396 | ); |
||
397 | } |
||
398 | |||
399 | public void testFetchFeatureType() throws Exception { |
||
400 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
401 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
402 | |||
403 | sqlbuilder.select().column().all(); |
||
404 | sqlbuilder.select().from().table().database("master").schema("dbo").name("test1"); |
||
405 | sqlbuilder.select().limit(1);
|
||
406 | |||
407 | 47606 | fdiaz | String exp = "SELECT * FROM \"test1\" LIMIT 1"; |
408 | |||
409 | 47539 | jjdelcerro | System.out.println("# Test:: testFetchFeatureType"); |
410 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
411 | 47606 | fdiaz | System.out.println("# EXP:: " + exp); |
412 | 47539 | jjdelcerro | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
413 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
414 | |||
415 | //# Test:: testFetchFeatureType
|
||
416 | //# SQL:: SELECT * FROM "test1" LIMIT 1
|
||
417 | //# Variables:: []
|
||
418 | //# Parametros:: []
|
||
419 | |||
420 | assertEquals( |
||
421 | 47606 | fdiaz | exp, |
422 | 47539 | jjdelcerro | sqlbuilder.toString() |
423 | ); |
||
424 | assertEquals( |
||
425 | "[]",
|
||
426 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
427 | ); |
||
428 | assertEquals( |
||
429 | "[]",
|
||
430 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
431 | ); |
||
432 | } |
||
433 | |||
434 | public void testPerformDeletes() throws Exception { |
||
435 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
436 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
437 | |||
438 | sqlbuilder.delete().table().database("master").schema("dbo").name("test1"); |
||
439 | sqlbuilder.delete().where().and( |
||
440 | expbuilder.eq( |
||
441 | expbuilder.column("id1"),
|
||
442 | expbuilder.parameter("id1").as_variable()
|
||
443 | ) |
||
444 | ); |
||
445 | sqlbuilder.delete().where().and( |
||
446 | expbuilder.eq( |
||
447 | expbuilder.column("id2"),
|
||
448 | expbuilder.parameter("id2").as_variable()
|
||
449 | ) |
||
450 | ); |
||
451 | |||
452 | 47606 | fdiaz | String exp = "DELETE FROM \"test1\" WHERE (( (\"id1\") = (?) ) AND ( (\"id2\") = (?) ))"; |
453 | 47539 | jjdelcerro | |
454 | System.out.println("# Test:: testPerformDeletes"); |
||
455 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
456 | 47606 | fdiaz | System.out.println("# EXP:: " + exp); |
457 | 47539 | jjdelcerro | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
458 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
459 | assertEquals( |
||
460 | 47606 | fdiaz | exp, |
461 | 47539 | jjdelcerro | sqlbuilder.toString() |
462 | ); |
||
463 | assertEquals( |
||
464 | "[id1, id2]",
|
||
465 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
466 | ); |
||
467 | assertEquals( |
||
468 | "[\"id1\", \"id2\"]",
|
||
469 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
470 | ); |
||
471 | } |
||
472 | |||
473 | public void testPerformInserts1() throws Exception { |
||
474 | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
||
475 | |||
476 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
477 | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
478 | |||
479 | sqlbuilder.insert().table().database("master").schema("dbo").name("test1"); |
||
480 | sqlbuilder.insert().column().name("id").with_value(expbuilder.parameter("id")); |
||
481 | sqlbuilder.insert().column().name("name").with_value(expbuilder.parameter("name")); |
||
482 | sqlbuilder.insert().column().name("geom").with_value(expbuilder.parameter("geom").as_geometry_variable().srs(proj)); |
||
483 | |||
484 | 47606 | fdiaz | String exp ="INSERT INTO \"test1\" ( \"id\", \"name\", \"geom\" ) VALUES ( ?, ?, ? )"; |
485 | 47539 | jjdelcerro | System.out.println("# Test:: testPerformInserts1"); |
486 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
487 | 47606 | fdiaz | System.out.println("# EXP:: " + exp); |
488 | 47539 | jjdelcerro | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
489 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
490 | assertEquals( |
||
491 | 47606 | fdiaz | exp, |
492 | 47539 | jjdelcerro | sqlbuilder.toString() |
493 | ); |
||
494 | assertEquals( |
||
495 | "[geom, id, name]",
|
||
496 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
497 | ); |
||
498 | assertEquals( |
||
499 | "[\"id\", \"name\", \"geom\"]",
|
||
500 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
501 | ); |
||
502 | } |
||
503 | |||
504 | public void testPerformInserts2() throws Exception { |
||
505 | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
||
506 | |||
507 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
508 | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
509 | |||
510 | sqlbuilder.insert().table().database("master").schema("dbo").name("test1"); |
||
511 | sqlbuilder.insert().column().name("id").with_value(expbuilder.parameter("id")); |
||
512 | sqlbuilder.insert().column().name("name").with_value(expbuilder.parameter("name")); |
||
513 | sqlbuilder.insert().column().name("geom").with_value(expbuilder.parameter("geom").as_geometry_variable().srs(proj)); |
||
514 | 47606 | fdiaz | |
515 | String exp = "INSERT INTO \"test1\" ( \"id\", \"name\", \"geom\" ) VALUES ( ?, ?, ? )"; |
||
516 | 47539 | jjdelcerro | System.out.println("# Test:: testPerformInserts2"); |
517 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
518 | 47606 | fdiaz | System.out.println("# EXP:: " + exp); |
519 | 47539 | jjdelcerro | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
520 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
521 | assertEquals( |
||
522 | 47606 | fdiaz | exp, |
523 | 47539 | jjdelcerro | sqlbuilder.toString() |
524 | ); |
||
525 | assertEquals( |
||
526 | "[geom, id, name]",
|
||
527 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
528 | ); |
||
529 | assertEquals( |
||
530 | "[\"id\", \"name\", \"geom\"]",
|
||
531 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
532 | ); |
||
533 | } |
||
534 | |||
535 | public void testPerformUpdates1() throws Exception { |
||
536 | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
||
537 | |||
538 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
539 | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
540 | |||
541 | sqlbuilder.update().table().database("master").schema("dbo").name("test1"); |
||
542 | sqlbuilder.update().where().and( |
||
543 | expbuilder.eq( |
||
544 | expbuilder.column("id"),
|
||
545 | expbuilder.parameter("id").as_variable()
|
||
546 | ) |
||
547 | ); |
||
548 | sqlbuilder.update().column().name("name").with_value(expbuilder.parameter("name")); |
||
549 | sqlbuilder.update().column().name("geom").with_value(
|
||
550 | expbuilder.parameter("geom").as_geometry_variable().srs(proj)
|
||
551 | ); |
||
552 | 47606 | fdiaz | String exp = "UPDATE \"test1\" SET \"name\" = ?, \"geom\" = ? WHERE ( (\"id\") = (?) )"; |
553 | 47539 | jjdelcerro | |
554 | System.out.println("# Test:: testPerformUpdates"); |
||
555 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
556 | 47606 | fdiaz | System.out.println("# EXP:: " + exp); |
557 | 47539 | jjdelcerro | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
558 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
559 | assertEquals( |
||
560 | 47606 | fdiaz | exp, |
561 | 47539 | jjdelcerro | sqlbuilder.toString() |
562 | ); |
||
563 | assertEquals( |
||
564 | "[geom, id, name]",
|
||
565 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
566 | ); |
||
567 | assertEquals( |
||
568 | "[\"name\", \"geom\", \"id\"]",
|
||
569 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
570 | ); |
||
571 | } |
||
572 | |||
573 | public void testPerformUpdates2() throws Exception { |
||
574 | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
||
575 | |||
576 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
577 | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
578 | |||
579 | sqlbuilder.update().table().database("master").schema("dbo").name("test1"); |
||
580 | sqlbuilder.update().where().and( |
||
581 | expbuilder.eq( |
||
582 | expbuilder.column("id"),
|
||
583 | expbuilder.parameter("id").as_variable()
|
||
584 | ) |
||
585 | ); |
||
586 | sqlbuilder.update().column().name("name").with_value(expbuilder.parameter("name")); |
||
587 | sqlbuilder.update().column().name("geom").with_value(
|
||
588 | expbuilder.parameter("geom").as_geometry_variable()
|
||
589 | .srs(expbuilder.parameter().value(proj)) |
||
590 | ); |
||
591 | |||
592 | 47606 | fdiaz | String exp = "UPDATE \"test1\" SET \"name\" = ?, \"geom\" = ? WHERE ( (\"id\") = (?) )"; |
593 | |||
594 | 47539 | jjdelcerro | System.out.println("# Test:: testPerformUpdates"); |
595 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
596 | 47606 | fdiaz | System.out.println("# EXP:: " + exp); |
597 | 47539 | jjdelcerro | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
598 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
599 | assertEquals( |
||
600 | 47606 | fdiaz | exp, |
601 | 47539 | jjdelcerro | sqlbuilder.toString() |
602 | ); |
||
603 | assertEquals( |
||
604 | "[geom, id, name]",
|
||
605 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
606 | ); |
||
607 | assertEquals( |
||
608 | 47606 | fdiaz | "[\"name\", \"geom\", \"id\"]",
|
609 | 47539 | jjdelcerro | ArrayUtils.toString(sqlbuilder.parameters_names()) |
610 | ); |
||
611 | } |
||
612 | |||
613 | public void testGrant1() throws Exception { |
||
614 | |||
615 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
616 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
617 | |||
618 | sqlbuilder.grant().table().database("master").schema("dbo").name("test1"); |
||
619 | sqlbuilder.grant().role("prueba").select().insert().update();
|
||
620 | sqlbuilder.grant().role("gis").all();
|
||
621 | |||
622 | 47606 | fdiaz | String exp = ""; |
623 | 47539 | jjdelcerro | |
624 | System.out.println("# Test:: testGrant1"); |
||
625 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
626 | 47606 | fdiaz | System.out.println("# EXP:: " + exp); |
627 | 47539 | jjdelcerro | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
628 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
629 | assertEquals( |
||
630 | 47606 | fdiaz | exp, |
631 | 47539 | jjdelcerro | sqlbuilder.toString() |
632 | ); |
||
633 | assertEquals( |
||
634 | "[]",
|
||
635 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
636 | ); |
||
637 | assertEquals( |
||
638 | "[]",
|
||
639 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
640 | ); |
||
641 | } |
||
642 | |||
643 | public void testGrant2() throws Exception { |
||
644 | |||
645 | SQLBuilder sqlbuilder = new SQLBuilderBase();
|
||
646 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
647 | |||
648 | sqlbuilder.grant().table().database("master").schema("dbo").name("test1"); |
||
649 | sqlbuilder.grant().role("prueba").privilege(Privilege.SELECT)
|
||
650 | .privilege(Privilege.INSERT) |
||
651 | .privilege(Privilege.UPDATE); |
||
652 | sqlbuilder.grant().role("gis").privilege(Privilege.ALL);
|
||
653 | |||
654 | |||
655 | System.out.println("# Test:: testGrant2"); |
||
656 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
657 | 47606 | fdiaz | System.out.println("# EXP:: " + ""); |
658 | 47539 | jjdelcerro | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
659 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
660 | assertEquals( |
||
661 | "",
|
||
662 | sqlbuilder.toString() |
||
663 | ); |
||
664 | assertEquals( |
||
665 | "[]",
|
||
666 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
667 | ); |
||
668 | assertEquals( |
||
669 | "[]",
|
||
670 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
671 | ); |
||
672 | } |
||
673 | |||
674 | |||
675 | } |