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