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