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