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