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