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 @ 44644
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 | false,
|
||
182 | 43355 | jjdelcerro | false,
|
183 | 43114 | jjdelcerro | true,
|
184 | false,
|
||
185 | null
|
||
186 | ); |
||
187 | 44198 | jjdelcerro | sqlbuilder.create_table().add_column( |
188 | 43114 | jjdelcerro | "id",
|
189 | DataTypes.INT, |
||
190 | 0,
|
||
191 | 0,
|
||
192 | true,
|
||
193 | false,
|
||
194 | 43355 | jjdelcerro | false,
|
195 | 43114 | jjdelcerro | true,
|
196 | 0
|
||
197 | ); |
||
198 | 44198 | jjdelcerro | sqlbuilder.create_table().add_column( |
199 | 43114 | jjdelcerro | "geom",
|
200 | DataTypes.GEOMETRY, |
||
201 | 0,
|
||
202 | 0,
|
||
203 | false,
|
||
204 | 43355 | jjdelcerro | false,
|
205 | 43114 | jjdelcerro | true,
|
206 | false,
|
||
207 | null
|
||
208 | ); |
||
209 | |||
210 | |||
211 | 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
|
212 | 43114 | jjdelcerro | System.out.println("# Test:: testCreateTable"); |
213 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
214 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
215 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
216 | 43114 | jjdelcerro | assertEquals( |
217 | 44361 | jjdelcerro | "CREATE TABLE \"dbo\".\"test1\" (\"name\" VARCHAR(45) DEFAULT NULL, \"id\" INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL, \"geom\" GEOMETRY ); ALTER TABLE \"dbo\".\"test1\" ADD CONSTRAINT IF NOT EXISTS \"constraint_test1_geom_dim\" CHECK NVL2(\"geom\", ST_CoordDim(\"geom\") = 2, TRUE)",
|
218 | 44198 | jjdelcerro | sqlbuilder.toString() |
219 | 43114 | jjdelcerro | ); |
220 | assertEquals( |
||
221 | "[]",
|
||
222 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
223 | 43114 | jjdelcerro | ); |
224 | assertEquals( |
||
225 | "[]",
|
||
226 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.parameters_names()) |
227 | 43114 | jjdelcerro | ); |
228 | } |
||
229 | |||
230 | public void testDropTable() throws Exception { |
||
231 | 44198 | jjdelcerro | SQLBuilder sqlbuilder = createSQLBuilder(); |
232 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
233 | 43114 | jjdelcerro | |
234 | 44198 | jjdelcerro | sqlbuilder.drop_table().table().database("master").schema("dbo").name("test1"); |
235 | 43114 | jjdelcerro | |
236 | 44198 | jjdelcerro | // DROP TABLE "test1"
|
237 | 43114 | jjdelcerro | |
238 | System.out.println("# Test:: testDropTable"); |
||
239 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
240 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
241 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
242 | 43114 | jjdelcerro | assertEquals( |
243 | 44361 | jjdelcerro | "DROP TABLE \"dbo\".\"test1\"",
|
244 | 44198 | jjdelcerro | sqlbuilder.toString() |
245 | 43114 | jjdelcerro | ); |
246 | assertEquals( |
||
247 | "[]",
|
||
248 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
249 | 43114 | jjdelcerro | ); |
250 | assertEquals( |
||
251 | "[]",
|
||
252 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.parameters_names()) |
253 | 43114 | jjdelcerro | ); |
254 | } |
||
255 | |||
256 | public void testFetchFeatureProviderByReference() throws Exception { |
||
257 | 44198 | jjdelcerro | SQLBuilder sqlbuilder = new SQLBuilderBase();
|
258 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
259 | 43114 | jjdelcerro | |
260 | String value = "yoyo"; |
||
261 | 44198 | jjdelcerro | sqlbuilder.select().column().name("name");
|
262 | sqlbuilder.select().column().name("id");
|
||
263 | sqlbuilder.select().column().name("geom").as_geometry();
|
||
264 | sqlbuilder.select().from().table().database("master").schema("dbo").name("test1"); |
||
265 | sqlbuilder.select().where().set( |
||
266 | expbuilder.eq( |
||
267 | expbuilder.column("name"),
|
||
268 | expbuilder.parameter(value).as_constant() |
||
269 | 43114 | jjdelcerro | ) |
270 | ); |
||
271 | 44198 | jjdelcerro | sqlbuilder.select().limit(1);
|
272 | 43114 | jjdelcerro | |
273 | // SELECT "name", "id", ST_AsBinary("geom") FROM "master"."dbo"."test1" WHERE ( ("name") = (?) ) LIMIT 1
|
||
274 | |||
275 | System.out.println("# Test:: testFetchFeatureProviderByReference"); |
||
276 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
277 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
278 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
279 | 43114 | jjdelcerro | assertEquals( |
280 | "SELECT \"name\", \"id\", ST_AsBinary(\"geom\") FROM \"master\".\"dbo\".\"test1\" WHERE ( (\"name\") = (?) ) LIMIT 1",
|
||
281 | 44198 | jjdelcerro | sqlbuilder.toString() |
282 | 43114 | jjdelcerro | ); |
283 | assertEquals( |
||
284 | "[geom, id, name]",
|
||
285 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
286 | 43114 | jjdelcerro | ); |
287 | assertEquals( |
||
288 | "['yoyo']",
|
||
289 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.parameters_names()) |
290 | 43114 | jjdelcerro | ); |
291 | } |
||
292 | |||
293 | public void testFetchFeatureType() throws Exception { |
||
294 | 44198 | jjdelcerro | SQLBuilder sqlbuilder = createSQLBuilder(); |
295 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
296 | 43114 | jjdelcerro | |
297 | 44198 | jjdelcerro | sqlbuilder.select().column().all(); |
298 | sqlbuilder.select().from().table().database("master").schema("dbo").name("test1"); |
||
299 | sqlbuilder.select().limit(1);
|
||
300 | 43114 | jjdelcerro | |
301 | System.out.println("# Test:: testFetchFeatureType"); |
||
302 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
303 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
304 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
305 | 43114 | jjdelcerro | |
306 | //# Test:: testFetchFeatureType
|
||
307 | 44198 | jjdelcerro | //# SQL:: SELECT * FROM "test1" LIMIT 1
|
308 | 43114 | jjdelcerro | //# Variables:: []
|
309 | //# Parametros:: []
|
||
310 | |||
311 | assertEquals( |
||
312 | 44361 | jjdelcerro | "SELECT * FROM \"dbo\".\"test1\" LIMIT 1",
|
313 | 44198 | jjdelcerro | sqlbuilder.toString() |
314 | 43114 | jjdelcerro | ); |
315 | assertEquals( |
||
316 | "[]",
|
||
317 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
318 | 43114 | jjdelcerro | ); |
319 | assertEquals( |
||
320 | "[]",
|
||
321 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.parameters_names()) |
322 | 43114 | jjdelcerro | ); |
323 | } |
||
324 | |||
325 | public void testPerformDeletes() throws Exception { |
||
326 | 44198 | jjdelcerro | SQLBuilder sqlbuilder = createSQLBuilder(); |
327 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
328 | 43114 | jjdelcerro | |
329 | 44198 | jjdelcerro | sqlbuilder.delete().table().database("master").schema("dbo").name("test1"); |
330 | sqlbuilder.delete().where().and( |
||
331 | expbuilder.eq( |
||
332 | expbuilder.column("id1"),
|
||
333 | expbuilder.parameter("id1").as_variable()
|
||
334 | 43114 | jjdelcerro | ) |
335 | ); |
||
336 | 44198 | jjdelcerro | sqlbuilder.delete().where().and( |
337 | expbuilder.eq( |
||
338 | expbuilder.column("id2"),
|
||
339 | expbuilder.parameter("id2").as_variable()
|
||
340 | 43114 | jjdelcerro | ) |
341 | ); |
||
342 | |||
343 | 44198 | jjdelcerro | // DELETE FROM "test1" WHERE ( ("id1") = (?) ) AND ( ("id2") = (?) )
|
344 | 43114 | jjdelcerro | |
345 | System.out.println("# Test:: testPerformDeletes"); |
||
346 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
347 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
348 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
349 | 43114 | jjdelcerro | assertEquals( |
350 | 44361 | jjdelcerro | "DELETE FROM \"dbo\".\"test1\" WHERE (( (\"id1\") = (?) ) AND ( (\"id2\") = (?) ))",
|
351 | 44198 | jjdelcerro | sqlbuilder.toString() |
352 | 43114 | jjdelcerro | ); |
353 | assertEquals( |
||
354 | "[id1, id2]",
|
||
355 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
356 | 43114 | jjdelcerro | ); |
357 | assertEquals( |
||
358 | "[\"id1\", \"id2\"]",
|
||
359 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.parameters_names()) |
360 | 43114 | jjdelcerro | ); |
361 | } |
||
362 | |||
363 | public void testPerformInserts1() throws Exception { |
||
364 | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
||
365 | |||
366 | 44198 | jjdelcerro | SQLBuilder sqlbuilder = createSQLBuilder(); |
367 | 44644 | jjdelcerro | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
368 | 43114 | jjdelcerro | |
369 | 44198 | jjdelcerro | sqlbuilder.insert().table().database("master").schema("dbo").name("test1"); |
370 | sqlbuilder.insert().column().name("id").with_value(expbuilder.parameter("id")); |
||
371 | sqlbuilder.insert().column().name("name").with_value(expbuilder.parameter("name")); |
||
372 | sqlbuilder.insert().column().name("geom").with_value(expbuilder.parameter("geom").as_geometry_variable().srs(proj)); |
||
373 | 43114 | jjdelcerro | |
374 | System.out.println("# Test:: testPerformInserts1"); |
||
375 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
376 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
377 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
378 | 43114 | jjdelcerro | assertEquals( |
379 | 44361 | jjdelcerro | "INSERT INTO \"dbo\".\"test1\" ( \"id\", \"name\", \"geom\" ) VALUES ( ?, ?, ST_GeomFromWKB((?), (4326)) )",
|
380 | 44198 | jjdelcerro | sqlbuilder.toString() |
381 | 43114 | jjdelcerro | ); |
382 | assertEquals( |
||
383 | "[geom, id, name]",
|
||
384 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
385 | 43114 | jjdelcerro | ); |
386 | assertEquals( |
||
387 | "[\"id\", \"name\", \"geom\"]",
|
||
388 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.parameters_names()) |
389 | 43114 | jjdelcerro | ); |
390 | } |
||
391 | |||
392 | public void testPerformInserts2() throws Exception { |
||
393 | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
||
394 | |||
395 | 44198 | jjdelcerro | SQLBuilder sqlbuilder = createSQLBuilder(); |
396 | 44644 | jjdelcerro | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
397 | 43114 | jjdelcerro | |
398 | 44198 | jjdelcerro | sqlbuilder.insert().table().database("master").schema("dbo").name("test1"); |
399 | sqlbuilder.insert().column().name("id").with_value(expbuilder.parameter("id")); |
||
400 | sqlbuilder.insert().column().name("name").with_value(expbuilder.parameter("name")); |
||
401 | sqlbuilder.insert().column().name("geom").with_value(expbuilder.parameter("geom").as_geometry_variable().srs(proj)); |
||
402 | 43114 | jjdelcerro | |
403 | System.out.println("# Test:: testPerformInserts2"); |
||
404 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
405 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
406 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
407 | 43114 | jjdelcerro | assertEquals( |
408 | 44361 | jjdelcerro | "INSERT INTO \"dbo\".\"test1\" ( \"id\", \"name\", \"geom\" ) VALUES ( ?, ?, ST_GeomFromWKB((?), (4326)) )",
|
409 | 44198 | jjdelcerro | sqlbuilder.toString() |
410 | 43114 | jjdelcerro | ); |
411 | assertEquals( |
||
412 | "[geom, id, name]",
|
||
413 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
414 | 43114 | jjdelcerro | ); |
415 | assertEquals( |
||
416 | 44198 | jjdelcerro | "[\"id\", \"name\", \"geom\"]",
|
417 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
418 | 43114 | jjdelcerro | ); |
419 | } |
||
420 | |||
421 | 44198 | jjdelcerro | public void testPerformUpdates1() throws Exception { |
422 | 43114 | jjdelcerro | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
423 | |||
424 | 44198 | jjdelcerro | SQLBuilder sqlbuilder = createSQLBuilder(); |
425 | 44644 | jjdelcerro | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
426 | 43114 | jjdelcerro | |
427 | 44198 | jjdelcerro | sqlbuilder.update().table().database("master").schema("dbo").name("test1"); |
428 | sqlbuilder.update().where().and( |
||
429 | expbuilder.eq( |
||
430 | expbuilder.column("id"),
|
||
431 | expbuilder.parameter("id").as_variable()
|
||
432 | 43114 | jjdelcerro | ) |
433 | ); |
||
434 | 44198 | jjdelcerro | sqlbuilder.update().column().name("name").with_value(expbuilder.parameter("name")); |
435 | sqlbuilder.update().column().name("geom").with_value(
|
||
436 | expbuilder.parameter("geom").as_geometry_variable().srs(proj)
|
||
437 | 43114 | jjdelcerro | ); |
438 | |||
439 | 44198 | jjdelcerro | System.out.println("# Test:: testPerformUpdates"); |
440 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
441 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
442 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
443 | assertEquals( |
||
444 | 44361 | jjdelcerro | "UPDATE \"dbo\".\"test1\" SET \"name\" = ?, \"geom\" = ST_GeomFromWKB((?), (4326)) WHERE ( (\"id\") = (?) )",
|
445 | 44198 | jjdelcerro | sqlbuilder.toString() |
446 | ); |
||
447 | assertEquals( |
||
448 | "[geom, id, name]",
|
||
449 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
450 | ); |
||
451 | assertEquals( |
||
452 | "[\"name\", \"geom\", \"id\"]",
|
||
453 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
454 | ); |
||
455 | } |
||
456 | 43114 | jjdelcerro | |
457 | 44198 | jjdelcerro | public void testPerformUpdates2() throws Exception { |
458 | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
||
459 | |||
460 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
461 | 44644 | jjdelcerro | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
462 | 44198 | jjdelcerro | |
463 | sqlbuilder.update().table().database("master").schema("dbo").name("test1"); |
||
464 | sqlbuilder.update().where().and( |
||
465 | expbuilder.eq( |
||
466 | expbuilder.column("id"),
|
||
467 | expbuilder.parameter("id").as_variable()
|
||
468 | ) |
||
469 | ); |
||
470 | sqlbuilder.update().column().name("name").with_value(expbuilder.parameter("name")); |
||
471 | sqlbuilder.update().column().name("geom").with_value(
|
||
472 | expbuilder.parameter("geom").as_geometry_variable()
|
||
473 | .srs(expbuilder.parameter().value(proj)) |
||
474 | ); |
||
475 | |||
476 | 43114 | jjdelcerro | System.out.println("# Test:: testPerformUpdates"); |
477 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
478 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
479 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
480 | 43114 | jjdelcerro | assertEquals( |
481 | 44361 | jjdelcerro | "UPDATE \"dbo\".\"test1\" SET \"name\" = ?, \"geom\" = ST_GeomFromWKB((?), (?)) WHERE ( (\"id\") = (?) )",
|
482 | 44198 | jjdelcerro | sqlbuilder.toString() |
483 | 43114 | jjdelcerro | ); |
484 | assertEquals( |
||
485 | "[geom, id, name]",
|
||
486 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
487 | 43114 | jjdelcerro | ); |
488 | assertEquals( |
||
489 | "[\"name\", \"geom\", 4326, \"id\"]",
|
||
490 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.parameters_names()) |
491 | 43114 | jjdelcerro | ); |
492 | } |
||
493 | |||
494 | public void testGrant1() throws Exception { |
||
495 | |||
496 | 44198 | jjdelcerro | SQLBuilder sqlbuilder = createSQLBuilder(); |
497 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
498 | 43114 | jjdelcerro | |
499 | 44198 | jjdelcerro | sqlbuilder.grant().table().database("master").schema("dbo").name("test1"); |
500 | sqlbuilder.grant().role("prueba").select().insert().update();
|
||
501 | sqlbuilder.grant().role("gis").all();
|
||
502 | 43114 | jjdelcerro | |
503 | |||
504 | System.out.println("# Test:: testGrant1"); |
||
505 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
506 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
507 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
508 | 43114 | jjdelcerro | assertEquals( |
509 | "",
|
||
510 | 44198 | jjdelcerro | sqlbuilder.toString() |
511 | 43114 | jjdelcerro | ); |
512 | assertEquals( |
||
513 | "[]",
|
||
514 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
515 | 43114 | jjdelcerro | ); |
516 | assertEquals( |
||
517 | "[]",
|
||
518 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.parameters_names()) |
519 | 43114 | jjdelcerro | ); |
520 | } |
||
521 | |||
522 | public void testGrant2() throws Exception { |
||
523 | |||
524 | 44198 | jjdelcerro | SQLBuilder sqlbuilder = new SQLBuilderBase();
|
525 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
526 | 43114 | jjdelcerro | |
527 | 44198 | jjdelcerro | sqlbuilder.grant().table().database("master").schema("dbo").name("test1"); |
528 | sqlbuilder.grant().role("prueba").privilege(Privilege.SELECT)
|
||
529 | 43114 | jjdelcerro | .privilege(Privilege.INSERT) |
530 | .privilege(Privilege.UPDATE); |
||
531 | 44198 | jjdelcerro | sqlbuilder.grant().role("gis").privilege(Privilege.ALL);
|
532 | 43114 | jjdelcerro | |
533 | |||
534 | System.out.println("# Test:: testGrant2"); |
||
535 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
536 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
537 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
538 | 43114 | jjdelcerro | assertEquals( |
539 | "",
|
||
540 | 44198 | jjdelcerro | sqlbuilder.toString() |
541 | 43114 | jjdelcerro | ); |
542 | assertEquals( |
||
543 | "[]",
|
||
544 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
545 | 43114 | jjdelcerro | ); |
546 | assertEquals( |
||
547 | "[]",
|
||
548 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.parameters_names()) |
549 | 43114 | jjdelcerro | ); |
550 | } |
||
551 | |||
552 | |||
553 | } |