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