svn-gvsig-desktop / trunk / org.gvsig.desktop / org.gvsig.desktop.plugin / org.gvsig.h2spatial / org.gvsig.h2spatial.h2gis132 / org.gvsig.h2spatial.h2gis132.provider / src / test / java / org / gvsig / fmap / dal / store / h2 / H2SpatialSQLBuilderTest.java @ 45649
History | View | Annotate | Download (23.1 KB)
1 | 45472 | jjdelcerro | package org.gvsig.fmap.dal.store.h2; |
---|---|---|---|
2 | |||
3 | import junit.framework.TestCase; |
||
4 | import org.apache.commons.lang3.ArrayUtils; |
||
5 | import org.cresques.cts.IProjection; |
||
6 | import org.gvsig.expressionevaluator.ExpressionBuilder; |
||
7 | import org.gvsig.expressionevaluator.GeometryExpressionBuilder; |
||
8 | import org.gvsig.fmap.crs.CRSFactory; |
||
9 | import org.gvsig.fmap.dal.SQLBuilder; |
||
10 | import org.gvsig.fmap.dal.SQLBuilder.Privilege; |
||
11 | import org.gvsig.fmap.dal.feature.spi.SQLBuilderBase; |
||
12 | import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference; |
||
13 | import org.gvsig.fmap.dal.store.jdbc2.spi.operations.OperationsFactoryBase; |
||
14 | import org.gvsig.fmap.geom.DataTypes; |
||
15 | import org.gvsig.fmap.geom.Geometry; |
||
16 | import org.gvsig.fmap.geom.GeometryLocator; |
||
17 | import org.gvsig.fmap.geom.GeometryManager; |
||
18 | import org.gvsig.fmap.geom.primitive.Polygon; |
||
19 | import org.gvsig.tools.library.impl.DefaultLibrariesInitializer; |
||
20 | |||
21 | public class H2SpatialSQLBuilderTest extends TestCase { |
||
22 | |||
23 | public H2SpatialSQLBuilderTest(String testName) { |
||
24 | super(testName);
|
||
25 | } |
||
26 | |||
27 | @Override
|
||
28 | protected void setUp() throws Exception { |
||
29 | super.setUp();
|
||
30 | new DefaultLibrariesInitializer().fullInitialize();
|
||
31 | } |
||
32 | |||
33 | @Override
|
||
34 | protected void tearDown() throws Exception { |
||
35 | super.tearDown();
|
||
36 | } |
||
37 | |||
38 | 45649 | jjdelcerro | private SQLBuilder createSQLBuilder() throws Exception { |
39 | H2SpatialHelper helper = TestUtils.createJDBCHelper(); |
||
40 | 45472 | jjdelcerro | return new H2SpatialSQLBuilder(helper); |
41 | } |
||
42 | |||
43 | public void testCalulateEnvelopeOfColumn() throws Exception { |
||
44 | |||
45 | TableReference table = new OperationsFactoryBase.DefaultTableReference("master","dbo","test1", null); |
||
46 | String columnName = "the_geom"; |
||
47 | |||
48 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
49 | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
50 | |||
51 | sqlbuilder.select().column().value( |
||
52 | expbuilder.as_geometry( |
||
53 | expbuilder.ST_ExtentAggregate( |
||
54 | expbuilder.column(columnName) |
||
55 | ) |
||
56 | ) |
||
57 | ); |
||
58 | //sqlbuilder.select().group_by(expbuilder.column(columnName));
|
||
59 | sqlbuilder.select().from().table() |
||
60 | .database(table.getDatabase()) |
||
61 | .schema(table.getSchema()) |
||
62 | .name(table.getTable()); |
||
63 | sqlbuilder.select().from().subquery(table.getSubquery()); |
||
64 | |||
65 | sqlbuilder.select().where().and( |
||
66 | expbuilder.not_is_null(expbuilder.column(columnName)) |
||
67 | ); |
||
68 | |||
69 | System.out.println("# Test:: testCalulateEnvelopeOfColumn"); |
||
70 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
71 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
72 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
73 | assertEquals( |
||
74 | "SELECT NVL2(ST_Extent(\"the_geom\"),ST_AsBinary(ST_Extent(\"the_geom\")),NULL) FROM \"dbo\".\"test1\" WHERE ( (\"the_geom\") IS NOT NULL )",
|
||
75 | sqlbuilder.toString() |
||
76 | ); |
||
77 | assertEquals( |
||
78 | "[the_geom]",
|
||
79 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
80 | ); |
||
81 | assertEquals( |
||
82 | "[]",
|
||
83 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
84 | ); |
||
85 | } |
||
86 | |||
87 | public void testCalulateEnvelope() throws Exception { |
||
88 | GeometryManager geometryManager = GeometryLocator.getGeometryManager(); |
||
89 | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
||
90 | |||
91 | Polygon limit = geometryManager.createPolygon(Geometry.SUBTYPES.GEOM2D);
|
||
92 | limit.addVertex(0, 0); |
||
93 | limit.addVertex(0, 100); |
||
94 | limit.addVertex(100, 100); |
||
95 | limit.addVertex(100, 0); |
||
96 | limit.addVertex(0, 0); |
||
97 | |||
98 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
99 | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
100 | |||
101 | sqlbuilder.select().column().value( |
||
102 | expbuilder.as_geometry( |
||
103 | expbuilder.ST_ExtentAggregate( |
||
104 | expbuilder.column("the_geom")
|
||
105 | ) |
||
106 | ) |
||
107 | ).as("envelope");
|
||
108 | sqlbuilder.select().from().table().database("master").schema("dbo").name("test1"); |
||
109 | sqlbuilder.select().where().set( |
||
110 | expbuilder.ST_Intersects( |
||
111 | expbuilder.ST_Envelope( |
||
112 | expbuilder.column("the_geom")
|
||
113 | ), |
||
114 | expbuilder.geometry(limit, proj) |
||
115 | ) |
||
116 | ); |
||
117 | sqlbuilder.select().where().and( |
||
118 | expbuilder.custom("x = 27")
|
||
119 | ); |
||
120 | |||
121 | System.out.println("# Test:: testCalulateEnvelope"); |
||
122 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
123 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
124 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
125 | assertEquals( |
||
126 | "SELECT NVL2(ST_Extent(\"the_geom\"),ST_AsBinary(ST_Extent(\"the_geom\")),NULL) AS \"envelope\" FROM \"dbo\".\"test1\" WHERE (( ((ST_Envelope(\"the_geom\")) && (ST_GeomFromWKB((x'000000000300000001000000050000000000000000000000000000000000000000000000004059000000000000405900000000000040590000000000004059000000000000000000000000000000000000000000000000000000000000'), (4326)))) AND ST_Intersects((ST_Envelope(\"the_geom\")),(ST_GeomFromWKB((x'000000000300000001000000050000000000000000000000000000000000000000000000004059000000000000405900000000000040590000000000004059000000000000000000000000000000000000000000000000000000000000'), (4326))) )) AND x = 27)",
|
||
127 | sqlbuilder.toString() |
||
128 | ); |
||
129 | assertEquals( |
||
130 | "[the_geom]",
|
||
131 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
132 | ); |
||
133 | assertEquals( |
||
134 | "[]",
|
||
135 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
136 | ); |
||
137 | } |
||
138 | |||
139 | public void testCount() throws Exception { |
||
140 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
141 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
142 | |||
143 | sqlbuilder.select().column().value(sqlbuilder.count().all()); |
||
144 | sqlbuilder.select().from().table().database("master").schema("dbo").name("test1"); |
||
145 | sqlbuilder.select().from().subquery(null);
|
||
146 | sqlbuilder.select().where().set( expbuilder.custom("pp = 200"));
|
||
147 | |||
148 | System.out.println("# Test:: testCount"); |
||
149 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
150 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
151 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
152 | |||
153 | //# Test:: testCount
|
||
154 | //# SQL:: SELECT COUNT(*) FROM "test1" WHERE pp = 200
|
||
155 | //# Variables:: []
|
||
156 | //# Parametros:: []
|
||
157 | |||
158 | assertEquals( |
||
159 | "SELECT COUNT(*) FROM \"dbo\".\"test1\" WHERE pp = 200",
|
||
160 | sqlbuilder.toString() |
||
161 | ); |
||
162 | assertEquals( |
||
163 | "[]",
|
||
164 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
165 | ); |
||
166 | assertEquals( |
||
167 | "[]",
|
||
168 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
169 | ); |
||
170 | } |
||
171 | |||
172 | public void testCreateTable() throws Exception { |
||
173 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
174 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
175 | |||
176 | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
||
177 | |||
178 | sqlbuilder.create_table().table().database("master").schema("dbo").name("test1"); |
||
179 | sqlbuilder.create_table().add_column( |
||
180 | "name",
|
||
181 | DataTypes.STRING, |
||
182 | 45,
|
||
183 | 0,
|
||
184 | 0,
|
||
185 | false,
|
||
186 | false,
|
||
187 | true,
|
||
188 | false,
|
||
189 | null
|
||
190 | ); |
||
191 | sqlbuilder.create_table().add_column( |
||
192 | "id",
|
||
193 | DataTypes.INT, |
||
194 | 0,
|
||
195 | 0,
|
||
196 | 0,
|
||
197 | true,
|
||
198 | false,
|
||
199 | false,
|
||
200 | true,
|
||
201 | 0
|
||
202 | ); |
||
203 | |||
204 | sqlbuilder.create_table().add_geometry_column("geom", Geometry.TYPES.MULTIPOLYGON, Geometry.SUBTYPES.GEOM2D, proj, true, true); |
||
205 | |||
206 | |||
207 | // 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
|
||
208 | System.out.println("# Test:: testCreateTable"); |
||
209 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
210 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
211 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
212 | assertEquals( |
||
213 | "CREATE TABLE \"dbo\".\"test1\" (\"name\" VARCHAR(45) DEFAULT NULL, \"id\" INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL, \"geom\" GEOMETRY(6) CHECK NVL2(\"geom\", ST_GeometryTypeCode(\"geom\") = 6 AND ST_CoordDim(\"geom\") = 2 AND ST_SRID(\"geom\") = 4326, TRUE) )",
|
||
214 | sqlbuilder.toString() |
||
215 | ); |
||
216 | assertEquals( |
||
217 | "[]",
|
||
218 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
219 | ); |
||
220 | assertEquals( |
||
221 | "[]",
|
||
222 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
223 | ); |
||
224 | } |
||
225 | |||
226 | public void testDropTable() throws Exception { |
||
227 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
228 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
229 | |||
230 | sqlbuilder.drop_table().table().database("master").schema("dbo").name("test1"); |
||
231 | |||
232 | // DROP TABLE "test1"
|
||
233 | |||
234 | System.out.println("# Test:: testDropTable"); |
||
235 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
236 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
237 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
238 | assertEquals( |
||
239 | "DROP TABLE \"dbo\".\"test1\"",
|
||
240 | sqlbuilder.toString() |
||
241 | ); |
||
242 | assertEquals( |
||
243 | "[]",
|
||
244 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
245 | ); |
||
246 | assertEquals( |
||
247 | "[]",
|
||
248 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
249 | ); |
||
250 | } |
||
251 | |||
252 | public void testFetchFeatureProviderByReference() throws Exception { |
||
253 | SQLBuilder sqlbuilder = new SQLBuilderBase();
|
||
254 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
255 | |||
256 | String value = "yoyo"; |
||
257 | sqlbuilder.select().column().name("name");
|
||
258 | sqlbuilder.select().column().name("id");
|
||
259 | sqlbuilder.select().column().name("geom").as_geometry();
|
||
260 | sqlbuilder.select().from().table().database("master").schema("dbo").name("test1"); |
||
261 | sqlbuilder.select().where().set( |
||
262 | expbuilder.eq( |
||
263 | expbuilder.column("name"),
|
||
264 | expbuilder.parameter(value).as_constant() |
||
265 | ) |
||
266 | ); |
||
267 | sqlbuilder.select().limit(1);
|
||
268 | |||
269 | // SELECT "name", "id", ST_AsBinary("geom") FROM "master"."dbo"."test1" WHERE ( ("name") = (?) ) LIMIT 1
|
||
270 | |||
271 | System.out.println("# Test:: testFetchFeatureProviderByReference"); |
||
272 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
273 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
274 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
275 | assertEquals( |
||
276 | "SELECT \"name\", \"id\", ST_AsBinary(\"geom\") FROM \"master\".\"dbo\".\"test1\" WHERE ( (\"name\") = (?) ) LIMIT 1",
|
||
277 | sqlbuilder.toString() |
||
278 | ); |
||
279 | assertEquals( |
||
280 | "[geom, id, name]",
|
||
281 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
282 | ); |
||
283 | assertEquals( |
||
284 | "['yoyo']",
|
||
285 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
286 | ); |
||
287 | } |
||
288 | |||
289 | public void testFetchFeatureType() throws Exception { |
||
290 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
291 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
292 | |||
293 | sqlbuilder.select().column().all(); |
||
294 | sqlbuilder.select().from().table().database("master").schema("dbo").name("test1"); |
||
295 | sqlbuilder.select().limit(1);
|
||
296 | |||
297 | System.out.println("# Test:: testFetchFeatureType"); |
||
298 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
299 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
300 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
301 | |||
302 | //# Test:: testFetchFeatureType
|
||
303 | //# SQL:: SELECT * FROM "test1" LIMIT 1
|
||
304 | //# Variables:: []
|
||
305 | //# Parametros:: []
|
||
306 | |||
307 | assertEquals( |
||
308 | "SELECT * FROM \"dbo\".\"test1\" LIMIT 1",
|
||
309 | sqlbuilder.toString() |
||
310 | ); |
||
311 | assertEquals( |
||
312 | "[]",
|
||
313 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
314 | ); |
||
315 | assertEquals( |
||
316 | "[]",
|
||
317 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
318 | ); |
||
319 | } |
||
320 | |||
321 | public void testPerformDeletes() throws Exception { |
||
322 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
323 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
324 | |||
325 | sqlbuilder.delete().table().database("master").schema("dbo").name("test1"); |
||
326 | sqlbuilder.delete().where().and( |
||
327 | expbuilder.eq( |
||
328 | expbuilder.column("id1"),
|
||
329 | expbuilder.parameter("id1").as_variable()
|
||
330 | ) |
||
331 | ); |
||
332 | sqlbuilder.delete().where().and( |
||
333 | expbuilder.eq( |
||
334 | expbuilder.column("id2"),
|
||
335 | expbuilder.parameter("id2").as_variable()
|
||
336 | ) |
||
337 | ); |
||
338 | |||
339 | // DELETE FROM "test1" WHERE ( ("id1") = (?) ) AND ( ("id2") = (?) )
|
||
340 | |||
341 | System.out.println("# Test:: testPerformDeletes"); |
||
342 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
343 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
344 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
345 | assertEquals( |
||
346 | "DELETE FROM \"dbo\".\"test1\" WHERE (( (\"id1\") = (?) ) AND ( (\"id2\") = (?) ))",
|
||
347 | sqlbuilder.toString() |
||
348 | ); |
||
349 | assertEquals( |
||
350 | "[id1, id2]",
|
||
351 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
352 | ); |
||
353 | assertEquals( |
||
354 | "[\"id1\", \"id2\"]",
|
||
355 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
356 | ); |
||
357 | } |
||
358 | |||
359 | public void testPerformInserts1() throws Exception { |
||
360 | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
||
361 | |||
362 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
363 | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
364 | |||
365 | sqlbuilder.insert().table().database("master").schema("dbo").name("test1"); |
||
366 | sqlbuilder.insert().column().name("id").with_value(expbuilder.parameter("id")); |
||
367 | sqlbuilder.insert().column().name("name").with_value(expbuilder.parameter("name")); |
||
368 | sqlbuilder.insert().column().name("geom").with_value(expbuilder.parameter("geom").as_geometry_variable().srs(proj)); |
||
369 | |||
370 | System.out.println("# Test:: testPerformInserts1"); |
||
371 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
372 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
373 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
374 | assertEquals( |
||
375 | "INSERT INTO \"dbo\".\"test1\" ( \"id\", \"name\", \"geom\" ) VALUES ( ?, ?, ST_GeomFromWKB((?), (4326)) )",
|
||
376 | sqlbuilder.toString() |
||
377 | ); |
||
378 | assertEquals( |
||
379 | "[geom, id, name]",
|
||
380 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
381 | ); |
||
382 | assertEquals( |
||
383 | "[\"id\", \"name\", \"geom\"]",
|
||
384 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
385 | ); |
||
386 | } |
||
387 | |||
388 | public void testPerformInserts2() throws Exception { |
||
389 | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
||
390 | |||
391 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
392 | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
393 | |||
394 | sqlbuilder.insert().table().database("master").schema("dbo").name("test1"); |
||
395 | sqlbuilder.insert().column().name("id").with_value(expbuilder.parameter("id")); |
||
396 | sqlbuilder.insert().column().name("name").with_value(expbuilder.parameter("name")); |
||
397 | sqlbuilder.insert().column().name("geom").with_value(expbuilder.parameter("geom").as_geometry_variable().srs(proj)); |
||
398 | |||
399 | System.out.println("# Test:: testPerformInserts2"); |
||
400 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
401 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
402 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
403 | assertEquals( |
||
404 | "INSERT INTO \"dbo\".\"test1\" ( \"id\", \"name\", \"geom\" ) VALUES ( ?, ?, ST_GeomFromWKB((?), (4326)) )",
|
||
405 | sqlbuilder.toString() |
||
406 | ); |
||
407 | assertEquals( |
||
408 | "[geom, id, name]",
|
||
409 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
410 | ); |
||
411 | assertEquals( |
||
412 | "[\"id\", \"name\", \"geom\"]",
|
||
413 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
414 | ); |
||
415 | } |
||
416 | |||
417 | public void testPerformUpdates1() throws Exception { |
||
418 | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
||
419 | |||
420 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
421 | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
422 | |||
423 | sqlbuilder.update().table().database("master").schema("dbo").name("test1"); |
||
424 | sqlbuilder.update().where().and( |
||
425 | expbuilder.eq( |
||
426 | expbuilder.column("id"),
|
||
427 | expbuilder.parameter("id").as_variable()
|
||
428 | ) |
||
429 | ); |
||
430 | sqlbuilder.update().column().name("name").with_value(expbuilder.parameter("name")); |
||
431 | sqlbuilder.update().column().name("geom").with_value(
|
||
432 | expbuilder.parameter("geom").as_geometry_variable().srs(proj)
|
||
433 | ); |
||
434 | |||
435 | System.out.println("# Test:: testPerformUpdates"); |
||
436 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
437 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
438 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
439 | assertEquals( |
||
440 | "UPDATE \"dbo\".\"test1\" SET \"name\" = ?, \"geom\" = ST_GeomFromWKB((?), (4326)) WHERE ( (\"id\") = (?) )",
|
||
441 | sqlbuilder.toString() |
||
442 | ); |
||
443 | assertEquals( |
||
444 | "[geom, id, name]",
|
||
445 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
446 | ); |
||
447 | assertEquals( |
||
448 | "[\"name\", \"geom\", \"id\"]",
|
||
449 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
450 | ); |
||
451 | } |
||
452 | |||
453 | public void testPerformUpdates2() throws Exception { |
||
454 | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
||
455 | |||
456 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
457 | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
458 | |||
459 | sqlbuilder.update().table().database("master").schema("dbo").name("test1"); |
||
460 | sqlbuilder.update().where().and( |
||
461 | expbuilder.eq( |
||
462 | expbuilder.column("id"),
|
||
463 | expbuilder.parameter("id").as_variable()
|
||
464 | ) |
||
465 | ); |
||
466 | sqlbuilder.update().column().name("name").with_value(expbuilder.parameter("name")); |
||
467 | sqlbuilder.update().column().name("geom").with_value(
|
||
468 | expbuilder.parameter("geom").as_geometry_variable()
|
||
469 | .srs(expbuilder.parameter().value(proj)) |
||
470 | ); |
||
471 | |||
472 | System.out.println("# Test:: testPerformUpdates"); |
||
473 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
474 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
475 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
476 | assertEquals( |
||
477 | "UPDATE \"dbo\".\"test1\" SET \"name\" = ?, \"geom\" = ST_GeomFromWKB((?), (?)) WHERE ( (\"id\") = (?) )",
|
||
478 | sqlbuilder.toString() |
||
479 | ); |
||
480 | assertEquals( |
||
481 | "[geom, id, name]",
|
||
482 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
483 | ); |
||
484 | assertEquals( |
||
485 | "[\"name\", \"geom\", 4326, \"id\"]",
|
||
486 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
487 | ); |
||
488 | } |
||
489 | |||
490 | public void testGrant1() throws Exception { |
||
491 | |||
492 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
493 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
494 | |||
495 | sqlbuilder.grant().table().database("master").schema("dbo").name("test1"); |
||
496 | sqlbuilder.grant().role("prueba").select().insert().update();
|
||
497 | sqlbuilder.grant().role("gis").all();
|
||
498 | |||
499 | |||
500 | System.out.println("# Test:: testGrant1"); |
||
501 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
502 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
503 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
504 | assertEquals( |
||
505 | "",
|
||
506 | sqlbuilder.toString() |
||
507 | ); |
||
508 | assertEquals( |
||
509 | "[]",
|
||
510 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
511 | ); |
||
512 | assertEquals( |
||
513 | "[]",
|
||
514 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
515 | ); |
||
516 | } |
||
517 | |||
518 | public void testGrant2() throws Exception { |
||
519 | |||
520 | SQLBuilder sqlbuilder = new SQLBuilderBase();
|
||
521 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
522 | |||
523 | sqlbuilder.grant().table().database("master").schema("dbo").name("test1"); |
||
524 | sqlbuilder.grant().role("prueba").privilege(Privilege.SELECT)
|
||
525 | .privilege(Privilege.INSERT) |
||
526 | .privilege(Privilege.UPDATE); |
||
527 | sqlbuilder.grant().role("gis").privilege(Privilege.ALL);
|
||
528 | |||
529 | |||
530 | System.out.println("# Test:: testGrant2"); |
||
531 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
532 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
533 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
534 | assertEquals( |
||
535 | "",
|
||
536 | sqlbuilder.toString() |
||
537 | ); |
||
538 | assertEquals( |
||
539 | "[]",
|
||
540 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
541 | ); |
||
542 | assertEquals( |
||
543 | "[]",
|
||
544 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
545 | ); |
||
546 | } |
||
547 | |||
548 | |||
549 | } |