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.jdbc / src / test / java / org / gvsig / fmap / dal / store / jdbc2 / SQLBuilderTest.java @ 46517
History | View | Annotate | Download (27.3 KB)
1 | 43114 | jjdelcerro | package org.gvsig.fmap.dal.store.jdbc2; |
---|---|---|---|
2 | |||
3 | 44682 | jjdelcerro | import java.util.ArrayList; |
4 | import java.util.List; |
||
5 | 44198 | jjdelcerro | import junit.framework.TestCase; |
6 | 44682 | jjdelcerro | import org.apache.commons.collections.CollectionUtils; |
7 | import org.apache.commons.collections.ListUtils; |
||
8 | 43114 | jjdelcerro | import org.apache.commons.lang3.ArrayUtils; |
9 | 44682 | jjdelcerro | import org.apache.commons.lang3.StringUtils; |
10 | 43114 | jjdelcerro | import org.cresques.cts.IProjection; |
11 | 44198 | jjdelcerro | import org.gvsig.expressionevaluator.ExpressionBuilder; |
12 | 44644 | jjdelcerro | import org.gvsig.expressionevaluator.GeometryExpressionBuilder; |
13 | 43114 | jjdelcerro | import org.gvsig.fmap.crs.CRSFactory; |
14 | 44376 | jjdelcerro | import org.gvsig.fmap.dal.DALLocator; |
15 | import org.gvsig.fmap.dal.DataManager; |
||
16 | 43114 | jjdelcerro | import org.gvsig.fmap.dal.SQLBuilder; |
17 | import org.gvsig.fmap.dal.SQLBuilder.Privilege; |
||
18 | 44376 | jjdelcerro | import org.gvsig.fmap.dal.feature.EditableFeatureAttributeDescriptor; |
19 | import org.gvsig.fmap.dal.feature.EditableFeatureType; |
||
20 | import org.gvsig.fmap.dal.feature.EditableForeingKey; |
||
21 | 43114 | jjdelcerro | import org.gvsig.fmap.dal.feature.spi.SQLBuilderBase; |
22 | 45647 | fdiaz | import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters; |
23 | 44376 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCHelperBase; |
24 | 43114 | jjdelcerro | import org.gvsig.fmap.geom.DataTypes; |
25 | import org.gvsig.fmap.geom.Geometry; |
||
26 | import org.gvsig.fmap.geom.GeometryLocator; |
||
27 | import org.gvsig.fmap.geom.GeometryManager; |
||
28 | import org.gvsig.fmap.geom.primitive.Polygon; |
||
29 | 44198 | jjdelcerro | import org.gvsig.tools.library.impl.DefaultLibrariesInitializer; |
30 | 43114 | jjdelcerro | |
31 | 44198 | jjdelcerro | public class SQLBuilderTest extends TestCase { |
32 | 43114 | jjdelcerro | |
33 | 44198 | jjdelcerro | public SQLBuilderTest(String testName) { |
34 | super(testName);
|
||
35 | } |
||
36 | |||
37 | 43114 | jjdelcerro | @Override
|
38 | 44198 | jjdelcerro | protected void setUp() throws Exception { |
39 | super.setUp();
|
||
40 | new DefaultLibrariesInitializer().fullInitialize();
|
||
41 | } |
||
42 | 43114 | jjdelcerro | |
43 | 44198 | jjdelcerro | @Override
|
44 | protected void tearDown() throws Exception { |
||
45 | super.tearDown();
|
||
46 | 43114 | jjdelcerro | } |
47 | 44198 | jjdelcerro | |
48 | private SQLBuilder createSQLBuilder() {
|
||
49 | return new SQLBuilderBase(); |
||
50 | 43114 | jjdelcerro | } |
51 | |||
52 | |||
53 | public void testCalulateEnvelope() throws Exception { |
||
54 | GeometryManager geometryManager = GeometryLocator.getGeometryManager(); |
||
55 | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
||
56 | |||
57 | Polygon limit = geometryManager.createPolygon(Geometry.SUBTYPES.GEOM2D);
|
||
58 | limit.addVertex(0, 0); |
||
59 | limit.addVertex(0, 100); |
||
60 | limit.addVertex(100, 100); |
||
61 | limit.addVertex(100, 0); |
||
62 | limit.addVertex(0, 0); |
||
63 | |||
64 | 44198 | jjdelcerro | SQLBuilder sqlbuilder = createSQLBuilder(); |
65 | 44644 | jjdelcerro | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
66 | 43114 | jjdelcerro | |
67 | 44198 | jjdelcerro | sqlbuilder.select().column().value( |
68 | expbuilder.as_geometry( |
||
69 | expbuilder.ST_ExtentAggregate( |
||
70 | expbuilder.column("the_geom")
|
||
71 | 43114 | jjdelcerro | ) |
72 | ) |
||
73 | ).as("envelope");
|
||
74 | 44198 | jjdelcerro | sqlbuilder.select().from().table().database("master").schema("dbo").name("test1"); |
75 | sqlbuilder.select().where().set( |
||
76 | expbuilder.ST_Intersects( |
||
77 | expbuilder.ST_Envelope( |
||
78 | expbuilder.column("the_geom")
|
||
79 | 43114 | jjdelcerro | ), |
80 | 44198 | jjdelcerro | expbuilder.geometry(limit, proj) |
81 | 43114 | jjdelcerro | ) |
82 | ); |
||
83 | 44198 | jjdelcerro | sqlbuilder.select().where().and( |
84 | expbuilder.custom("x = 27")
|
||
85 | 43114 | jjdelcerro | ); |
86 | |||
87 | System.out.println("# Test:: testCalulateEnvelope"); |
||
88 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
89 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
90 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
91 | 43114 | jjdelcerro | assertEquals( |
92 | 44376 | jjdelcerro | "SELECT ST_AsBinary(ST_ExtentAggregate(\"the_geom\")) AS \"envelope\" FROM \"master\".\"dbo\".\"test1\" WHERE (ST_Intersects((ST_Envelope(\"the_geom\")), (ST_GeomFromWKB((DECODE('000000000300000001000000050000000000000000000000000000000000000000000000004059000000000000405900000000000040590000000000004059000000000000000000000000000000000000000000000000000000000000','hex')), (4326)))) AND x = 27)",
|
93 | 44198 | jjdelcerro | sqlbuilder.toString() |
94 | 43114 | jjdelcerro | ); |
95 | assertEquals( |
||
96 | "[the_geom]",
|
||
97 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
98 | 43114 | jjdelcerro | ); |
99 | assertEquals( |
||
100 | "[]",
|
||
101 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.parameters_names()) |
102 | 43114 | jjdelcerro | ); |
103 | } |
||
104 | |||
105 | public void testCount() throws Exception { |
||
106 | 44198 | jjdelcerro | SQLBuilder sqlbuilder = createSQLBuilder(); |
107 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
108 | 43114 | jjdelcerro | |
109 | 44198 | jjdelcerro | sqlbuilder.select().column().value(sqlbuilder.count().all()); |
110 | sqlbuilder.select().from().table().database("master").schema("dbo").name("test1"); |
||
111 | sqlbuilder.select().from().subquery(null);
|
||
112 | sqlbuilder.select().where().set( expbuilder.custom("pp = 200"));
|
||
113 | 43114 | jjdelcerro | |
114 | System.out.println("# Test:: testCount"); |
||
115 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
116 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
117 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
118 | 43114 | jjdelcerro | |
119 | //# Test:: testCount
|
||
120 | //# SQL:: SELECT COUNT(*) FROM "master"."dbo"."test1" WHERE pp = 200
|
||
121 | //# Variables:: []
|
||
122 | //# Parametros:: []
|
||
123 | |||
124 | assertEquals( |
||
125 | "SELECT COUNT(*) FROM \"master\".\"dbo\".\"test1\" WHERE pp = 200",
|
||
126 | 44198 | jjdelcerro | sqlbuilder.toString() |
127 | 43114 | jjdelcerro | ); |
128 | assertEquals( |
||
129 | "[]",
|
||
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 testCreateTable() throws Exception { |
||
139 | 44198 | jjdelcerro | SQLBuilder sqlbuilder = createSQLBuilder(); |
140 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
141 | 43114 | jjdelcerro | |
142 | 44198 | jjdelcerro | sqlbuilder.create_table().table().database("master").schema("dbo").name("test1"); |
143 | sqlbuilder.create_table().add_column( |
||
144 | 43114 | jjdelcerro | "name",
|
145 | DataTypes.STRING, |
||
146 | 45,
|
||
147 | 0,
|
||
148 | 44669 | jjdelcerro | 0,
|
149 | 43114 | jjdelcerro | false,
|
150 | 43355 | jjdelcerro | false,
|
151 | 43114 | jjdelcerro | true,
|
152 | false,
|
||
153 | null
|
||
154 | ); |
||
155 | 44198 | jjdelcerro | sqlbuilder.create_table().add_column( |
156 | 43114 | jjdelcerro | "id",
|
157 | DataTypes.INT, |
||
158 | 0,
|
||
159 | 0,
|
||
160 | 44669 | jjdelcerro | 0,
|
161 | 43114 | jjdelcerro | true,
|
162 | false,
|
||
163 | 43355 | jjdelcerro | false,
|
164 | 43114 | jjdelcerro | true,
|
165 | 0
|
||
166 | ); |
||
167 | 44198 | jjdelcerro | sqlbuilder.create_table().add_column( |
168 | 43114 | jjdelcerro | "geom",
|
169 | DataTypes.GEOMETRY, |
||
170 | 0,
|
||
171 | 0,
|
||
172 | 44669 | jjdelcerro | 0,
|
173 | 43114 | jjdelcerro | false,
|
174 | 43355 | jjdelcerro | false,
|
175 | 43114 | jjdelcerro | true,
|
176 | false,
|
||
177 | null
|
||
178 | ); |
||
179 | |||
180 | |||
181 | // CREATE TABLE "master"."dbo"."test1" ("name" VARCHAR(45) DEFAULT NULL NULL, "id" SERIAL DEFAULT '0' NOT NULL PRIMARY KEY, "geom" TEXT DEFAULT NULL NULL )
|
||
182 | System.out.println("# Test:: testCreateTable"); |
||
183 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
184 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
185 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
186 | 43114 | jjdelcerro | assertEquals( |
187 | "CREATE TABLE \"master\".\"dbo\".\"test1\" (\"name\" VARCHAR(45) DEFAULT NULL NULL, \"id\" SERIAL DEFAULT '0' NOT NULL PRIMARY KEY, \"geom\" TEXT DEFAULT NULL NULL )",
|
||
188 | 44198 | jjdelcerro | sqlbuilder.toString() |
189 | 43114 | jjdelcerro | ); |
190 | assertEquals( |
||
191 | "[]",
|
||
192 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
193 | 43114 | jjdelcerro | ); |
194 | assertEquals( |
||
195 | "[]",
|
||
196 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.parameters_names()) |
197 | 43114 | jjdelcerro | ); |
198 | } |
||
199 | |||
200 | public void testDropTable() throws Exception { |
||
201 | 44198 | jjdelcerro | SQLBuilder sqlbuilder = createSQLBuilder(); |
202 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
203 | 43114 | jjdelcerro | |
204 | 44198 | jjdelcerro | sqlbuilder.drop_table().table().database("master").schema("dbo").name("test1"); |
205 | 43114 | jjdelcerro | |
206 | // DROP TABLE "master"."dbo"."test1"; DELETE FROM GEOMETRY_COLUMNS WHERE f_table_schema = 'dbo' AND f_table_name = 'test1'
|
||
207 | |||
208 | System.out.println("# Test:: testDropTable"); |
||
209 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
210 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
211 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
212 | 43114 | jjdelcerro | assertEquals( |
213 | "DROP TABLE \"master\".\"dbo\".\"test1\"; DELETE FROM GEOMETRY_COLUMNS WHERE f_table_schema = 'dbo' AND f_table_name = 'test1'",
|
||
214 | 44198 | jjdelcerro | sqlbuilder.toString() |
215 | 43114 | jjdelcerro | ); |
216 | assertEquals( |
||
217 | "[]",
|
||
218 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
219 | 43114 | jjdelcerro | ); |
220 | assertEquals( |
||
221 | "[]",
|
||
222 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.parameters_names()) |
223 | 43114 | jjdelcerro | ); |
224 | } |
||
225 | |||
226 | public void testFetchFeatureProviderByReference() throws Exception { |
||
227 | 44198 | jjdelcerro | SQLBuilder sqlbuilder = createSQLBuilder(); |
228 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
229 | 43114 | jjdelcerro | |
230 | String value = "yoyo"; |
||
231 | 44198 | jjdelcerro | sqlbuilder.select().column().name("name");
|
232 | sqlbuilder.select().column().name("id");
|
||
233 | sqlbuilder.select().column().name("geom").as_geometry();
|
||
234 | sqlbuilder.select().from().table().database("master").schema("dbo").name("test1"); |
||
235 | sqlbuilder.select().where().set( |
||
236 | expbuilder.eq( |
||
237 | expbuilder.column("name"),
|
||
238 | expbuilder.parameter(value).as_constant() |
||
239 | 43114 | jjdelcerro | ) |
240 | ); |
||
241 | 44198 | jjdelcerro | sqlbuilder.select().limit(1);
|
242 | 43114 | jjdelcerro | |
243 | // SELECT "name", "id", ST_AsBinary("geom") FROM "master"."dbo"."test1" WHERE ( ("name") = (?) ) LIMIT 1
|
||
244 | |||
245 | System.out.println("# Test:: testFetchFeatureProviderByReference"); |
||
246 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
247 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
248 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
249 | 43114 | jjdelcerro | assertEquals( |
250 | "SELECT \"name\", \"id\", ST_AsBinary(\"geom\") FROM \"master\".\"dbo\".\"test1\" WHERE ( (\"name\") = (?) ) LIMIT 1",
|
||
251 | 44198 | jjdelcerro | sqlbuilder.toString() |
252 | 43114 | jjdelcerro | ); |
253 | assertEquals( |
||
254 | "[geom, id, name]",
|
||
255 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
256 | 43114 | jjdelcerro | ); |
257 | assertEquals( |
||
258 | "['yoyo']",
|
||
259 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.parameters_names()) |
260 | 43114 | jjdelcerro | ); |
261 | } |
||
262 | |||
263 | public void testFetchFeatureType() throws Exception { |
||
264 | 44198 | jjdelcerro | SQLBuilder sqlbuilder = createSQLBuilder(); |
265 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
266 | 43114 | jjdelcerro | |
267 | 44198 | jjdelcerro | sqlbuilder.select().column().all(); |
268 | sqlbuilder.select().from().table().database("master").schema("dbo").name("test1"); |
||
269 | sqlbuilder.select().limit(1);
|
||
270 | 43114 | jjdelcerro | |
271 | System.out.println("# Test:: testFetchFeatureType"); |
||
272 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
273 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
274 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
275 | 43114 | jjdelcerro | |
276 | //# Test:: testFetchFeatureType
|
||
277 | //# SQL:: SELECT * FROM [master].[dbo].[test1] LIMMIT 1
|
||
278 | //# Variables:: []
|
||
279 | //# Parametros:: []
|
||
280 | |||
281 | assertEquals( |
||
282 | "SELECT * FROM \"master\".\"dbo\".\"test1\" LIMIT 1",
|
||
283 | 44198 | jjdelcerro | sqlbuilder.toString() |
284 | 43114 | jjdelcerro | ); |
285 | assertEquals( |
||
286 | "[]",
|
||
287 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
288 | 43114 | jjdelcerro | ); |
289 | assertEquals( |
||
290 | "[]",
|
||
291 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.parameters_names()) |
292 | 43114 | jjdelcerro | ); |
293 | } |
||
294 | |||
295 | public void testPerformDeletes() throws Exception { |
||
296 | 44198 | jjdelcerro | SQLBuilder sqlbuilder = createSQLBuilder(); |
297 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
298 | 43114 | jjdelcerro | |
299 | 44198 | jjdelcerro | sqlbuilder.delete().table().database("master").schema("dbo").name("test1"); |
300 | sqlbuilder.delete().where().and( |
||
301 | expbuilder.eq( |
||
302 | expbuilder.column("id1"),
|
||
303 | expbuilder.parameter("id1").as_variable()
|
||
304 | 43114 | jjdelcerro | ) |
305 | ); |
||
306 | 44198 | jjdelcerro | sqlbuilder.delete().where().and( |
307 | expbuilder.eq( |
||
308 | expbuilder.column("id2"),
|
||
309 | expbuilder.parameter("id2").as_variable()
|
||
310 | 43114 | jjdelcerro | ) |
311 | ); |
||
312 | |||
313 | 44376 | jjdelcerro | // # Test:: testPerformDeletes
|
314 | // # SQL:: DELETE FROM "master"."dbo"."test1" WHERE (( ("id1") = (?) ) AND ( ("id2") = (?) ))
|
||
315 | // # Variables:: [id1, id2]
|
||
316 | // # Parametros:: ["id1", "id2"]
|
||
317 | 43114 | jjdelcerro | |
318 | System.out.println("# Test:: testPerformDeletes"); |
||
319 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
320 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
321 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
322 | 43114 | jjdelcerro | assertEquals( |
323 | 44376 | jjdelcerro | "DELETE FROM \"master\".\"dbo\".\"test1\" WHERE (( (\"id1\") = (?) ) AND ( (\"id2\") = (?) ))",
|
324 | 44198 | jjdelcerro | sqlbuilder.toString() |
325 | 43114 | jjdelcerro | ); |
326 | assertEquals( |
||
327 | "[id1, id2]",
|
||
328 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
329 | 43114 | jjdelcerro | ); |
330 | assertEquals( |
||
331 | "[\"id1\", \"id2\"]",
|
||
332 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.parameters_names()) |
333 | 43114 | jjdelcerro | ); |
334 | } |
||
335 | |||
336 | public void testPerformInserts1() throws Exception { |
||
337 | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
||
338 | |||
339 | 44198 | jjdelcerro | SQLBuilder sqlbuilder = createSQLBuilder(); |
340 | 44644 | jjdelcerro | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
341 | 43114 | jjdelcerro | |
342 | 44198 | jjdelcerro | sqlbuilder.insert().table().database("master").schema("dbo").name("test1"); |
343 | sqlbuilder.insert().column().name("id").with_value(expbuilder.parameter("id")); |
||
344 | sqlbuilder.insert().column().name("name").with_value(expbuilder.parameter("name")); |
||
345 | 44644 | jjdelcerro | sqlbuilder.insert().column().name("geom").with_value(expbuilder.parameter("geom").as_variable().srs(proj)); |
346 | 43114 | jjdelcerro | |
347 | System.out.println("# Test:: testPerformInserts1"); |
||
348 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
349 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
350 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
351 | 43114 | jjdelcerro | assertEquals( |
352 | 44198 | jjdelcerro | "INSERT INTO \"master\".\"dbo\".\"test1\" ( \"id\", \"name\", \"geom\" ) VALUES ( ?, ?, ST_GeomFromWKB((?), (4326)) )",
|
353 | sqlbuilder.toString() |
||
354 | 43114 | jjdelcerro | ); |
355 | assertEquals( |
||
356 | "[geom, id, name]",
|
||
357 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
358 | 43114 | jjdelcerro | ); |
359 | assertEquals( |
||
360 | "[\"id\", \"name\", \"geom\"]",
|
||
361 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.parameters_names()) |
362 | 43114 | jjdelcerro | ); |
363 | } |
||
364 | |||
365 | public void testPerformInserts2() throws Exception { |
||
366 | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
||
367 | |||
368 | 44198 | jjdelcerro | SQLBuilder sqlbuilder = createSQLBuilder(); |
369 | 44644 | jjdelcerro | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
370 | 43114 | jjdelcerro | |
371 | 44198 | jjdelcerro | sqlbuilder.insert().table().database("master").schema("dbo").name("test1"); |
372 | sqlbuilder.insert().column().name("id").with_value(expbuilder.parameter("id")); |
||
373 | sqlbuilder.insert().column().name("name").with_value(expbuilder.parameter("name")); |
||
374 | 44644 | jjdelcerro | sqlbuilder.insert().column().name("geom").with_value(expbuilder.parameter("geom").as_variable().srs(proj)); |
375 | 43114 | jjdelcerro | |
376 | System.out.println("# Test:: testPerformInserts2"); |
||
377 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
378 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
379 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
380 | 43114 | jjdelcerro | assertEquals( |
381 | 44198 | jjdelcerro | "INSERT INTO \"master\".\"dbo\".\"test1\" ( \"id\", \"name\", \"geom\" ) VALUES ( ?, ?, ST_GeomFromWKB((?), (4326)) )",
|
382 | sqlbuilder.toString() |
||
383 | 43114 | jjdelcerro | ); |
384 | assertEquals( |
||
385 | "[geom, id, name]",
|
||
386 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
387 | 43114 | jjdelcerro | ); |
388 | assertEquals( |
||
389 | 44198 | jjdelcerro | "[\"id\", \"name\", \"geom\"]",
|
390 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
391 | 43114 | jjdelcerro | ); |
392 | } |
||
393 | |||
394 | 44198 | jjdelcerro | public void testPerformUpdates1() throws Exception { |
395 | 43114 | jjdelcerro | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
396 | |||
397 | 44198 | jjdelcerro | SQLBuilder sqlbuilder = createSQLBuilder(); |
398 | 44644 | jjdelcerro | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
399 | 43114 | jjdelcerro | |
400 | 44198 | jjdelcerro | sqlbuilder.update().table().database("master").schema("dbo").name("test1"); |
401 | sqlbuilder.update().where().and( |
||
402 | expbuilder.eq( |
||
403 | expbuilder.column("id"),
|
||
404 | expbuilder.parameter("id").as_variable()
|
||
405 | 43114 | jjdelcerro | ) |
406 | ); |
||
407 | 44198 | jjdelcerro | sqlbuilder.update().column().name("name").with_value(expbuilder.parameter("name")); |
408 | sqlbuilder.update().column().name("geom").with_value(
|
||
409 | 44644 | jjdelcerro | expbuilder.parameter("geom").as_variable().srs(proj)
|
410 | 43114 | jjdelcerro | ); |
411 | |||
412 | 44198 | jjdelcerro | System.out.println("# Test:: testPerformUpdates"); |
413 | System.out.println("# SQL:: " + sqlbuilder.toString()); |
||
414 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
415 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
416 | assertEquals( |
||
417 | "UPDATE \"master\".\"dbo\".\"test1\" SET \"name\" = ?, \"geom\" = ST_GeomFromWKB((?), (4326)) WHERE ( (\"id\") = (?) )",
|
||
418 | sqlbuilder.toString() |
||
419 | ); |
||
420 | assertEquals( |
||
421 | "[geom, id, name]",
|
||
422 | ArrayUtils.toString(sqlbuilder.variables_names()) |
||
423 | ); |
||
424 | assertEquals( |
||
425 | "[\"name\", \"geom\", \"id\"]",
|
||
426 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
427 | ); |
||
428 | } |
||
429 | 43114 | jjdelcerro | |
430 | 44198 | jjdelcerro | public void testPerformUpdates2() throws Exception { |
431 | IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
||
432 | |||
433 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
434 | 44644 | jjdelcerro | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
435 | 44198 | jjdelcerro | |
436 | sqlbuilder.update().table().database("master").schema("dbo").name("test1"); |
||
437 | sqlbuilder.update().where().and( |
||
438 | expbuilder.eq( |
||
439 | expbuilder.column("id"),
|
||
440 | expbuilder.parameter("id").as_variable()
|
||
441 | ) |
||
442 | ); |
||
443 | sqlbuilder.update().column().name("name").with_value(expbuilder.parameter("name")); |
||
444 | sqlbuilder.update().column().name("geom").with_value(
|
||
445 | 44644 | jjdelcerro | expbuilder.parameter("geom").as_variable()
|
446 | 44198 | jjdelcerro | .srs(expbuilder.parameter().value(proj)) |
447 | ); |
||
448 | |||
449 | 43114 | jjdelcerro | System.out.println("# Test:: testPerformUpdates"); |
450 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
451 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
452 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
453 | 43114 | jjdelcerro | assertEquals( |
454 | 44198 | jjdelcerro | "UPDATE \"master\".\"dbo\".\"test1\" SET \"name\" = ?, \"geom\" = ST_GeomFromWKB((?), (?)) WHERE ( (\"id\") = (?) )",
|
455 | sqlbuilder.toString() |
||
456 | 43114 | jjdelcerro | ); |
457 | assertEquals( |
||
458 | "[geom, id, name]",
|
||
459 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
460 | 43114 | jjdelcerro | ); |
461 | assertEquals( |
||
462 | "[\"name\", \"geom\", 4326, \"id\"]",
|
||
463 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.parameters_names()) |
464 | 43114 | jjdelcerro | ); |
465 | } |
||
466 | |||
467 | public void testGrant1() throws Exception { |
||
468 | |||
469 | 44198 | jjdelcerro | SQLBuilder sqlbuilder = createSQLBuilder(); |
470 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
471 | 43114 | jjdelcerro | |
472 | 44198 | jjdelcerro | sqlbuilder.grant().table().database("master").schema("dbo").name("test1"); |
473 | sqlbuilder.grant().role("prueba").select().insert().update();
|
||
474 | sqlbuilder.grant().role("gis").all();
|
||
475 | 43114 | jjdelcerro | |
476 | |||
477 | System.out.println("# Test:: testGrant1"); |
||
478 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
479 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
480 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
481 | 43114 | jjdelcerro | assertEquals( |
482 | "",
|
||
483 | 44198 | jjdelcerro | sqlbuilder.toString() |
484 | 43114 | jjdelcerro | ); |
485 | assertEquals( |
||
486 | "[]",
|
||
487 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
488 | 43114 | jjdelcerro | ); |
489 | assertEquals( |
||
490 | "[]",
|
||
491 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.parameters_names()) |
492 | 43114 | jjdelcerro | ); |
493 | } |
||
494 | |||
495 | public void testGrant2() throws Exception { |
||
496 | |||
497 | 44198 | jjdelcerro | SQLBuilder sqlbuilder = createSQLBuilder(); |
498 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
499 | 43114 | jjdelcerro | |
500 | 44198 | jjdelcerro | sqlbuilder.grant().table().database("master").schema("dbo").name("test1"); |
501 | sqlbuilder.grant().role("prueba").privilege(Privilege.SELECT)
|
||
502 | 43114 | jjdelcerro | .privilege(Privilege.INSERT) |
503 | .privilege(Privilege.UPDATE); |
||
504 | 44198 | jjdelcerro | sqlbuilder.grant().role("gis").privilege(Privilege.ALL);
|
505 | 43114 | jjdelcerro | |
506 | |||
507 | System.out.println("# Test:: testGrant2"); |
||
508 | 44198 | jjdelcerro | System.out.println("# SQL:: " + sqlbuilder.toString()); |
509 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
510 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
511 | 43114 | jjdelcerro | assertEquals( |
512 | "",
|
||
513 | 44198 | jjdelcerro | sqlbuilder.toString() |
514 | 43114 | jjdelcerro | ); |
515 | assertEquals( |
||
516 | "[]",
|
||
517 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
518 | 43114 | jjdelcerro | ); |
519 | assertEquals( |
||
520 | "[]",
|
||
521 | 44198 | jjdelcerro | ArrayUtils.toString(sqlbuilder.parameters_names()) |
522 | 43114 | jjdelcerro | ); |
523 | } |
||
524 | |||
525 | 44376 | jjdelcerro | public void testForeingValue() throws Exception { |
526 | DataManager dataManager = DALLocator.getDataManager(); |
||
527 | 45647 | fdiaz | JDBCHelper helper = createJDBCHelper(); |
528 | 44376 | jjdelcerro | |
529 | SQLBuilder sqlbuilder = createSQLBuilder(); |
||
530 | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
531 | |||
532 | EditableFeatureAttributeDescriptor attr; |
||
533 | EditableForeingKey foreingKey; |
||
534 | EditableFeatureType ft = dataManager.createFeatureType(); |
||
535 | ft.add("ID", DataTypes.INT);
|
||
536 | ft.add("NAME", DataTypes.STRING, 80); |
||
537 | attr = ft.add("TYPE", DataTypes.INT);
|
||
538 | foreingKey = attr.getForeingKey(); |
||
539 | foreingKey.setForeingKey(true);
|
||
540 | foreingKey.setClosedList(true);
|
||
541 | foreingKey.setCodeName("ID");
|
||
542 | foreingKey.setTableName("TYPES");
|
||
543 | attr = ft.add("PHONE_TYPE", DataTypes.INT);
|
||
544 | foreingKey = attr.getForeingKey(); |
||
545 | foreingKey.setForeingKey(true);
|
||
546 | foreingKey.setClosedList(true);
|
||
547 | foreingKey.setCodeName("ID");
|
||
548 | foreingKey.setTableName("PHONE_TYPES");
|
||
549 | |||
550 | 46507 | jjdelcerro | sqlbuilder.select().from().table().schema("dbo").name("test1"); |
551 | 44376 | jjdelcerro | |
552 | sqlbuilder.select().column().name("ID");
|
||
553 | sqlbuilder.select().column().name("NAME");
|
||
554 | 46505 | fdiaz | // sqlbuilder.select().column().name("DESCRIPTION");
|
555 | sqlbuilder.select().column().value( |
||
556 | expbuilder.function( |
||
557 | "FOREING_VALUE",
|
||
558 | expbuilder.constant("TYPE.DESCRIPTION")
|
||
559 | ) |
||
560 | ); |
||
561 | 44376 | jjdelcerro | sqlbuilder.select().column().name("TYPE");
|
562 | 46505 | fdiaz | sqlbuilder.select().column().name("TYPE").as("TYPE1"); |
563 | 44376 | jjdelcerro | sqlbuilder.select().where().set( |
564 | expbuilder.and( |
||
565 | expbuilder.like( |
||
566 | expbuilder.function( |
||
567 | "FOREING_VALUE",
|
||
568 | expbuilder.constant("TYPE.DESCRIPTION")
|
||
569 | ), |
||
570 | expbuilder.constant("A%")
|
||
571 | ), |
||
572 | expbuilder.eq( |
||
573 | expbuilder.function( |
||
574 | "FOREING_VALUE",
|
||
575 | expbuilder.constant("PHONE_TYPE.DESCRIPTION")
|
||
576 | ), |
||
577 | expbuilder.constant("mobile")
|
||
578 | ) |
||
579 | ) |
||
580 | ); |
||
581 | 46505 | fdiaz | sqlbuilder.select().order_by().column("TYPE");
|
582 | 44376 | jjdelcerro | System.out.println("# Test:: testForeingValue"); |
583 | System.out.println("# SQL1:: " + sqlbuilder.toString()); |
||
584 | 43114 | jjdelcerro | |
585 | 44682 | jjdelcerro | List<String> attrNames = new ArrayList<>(); |
586 | 44748 | jjdelcerro | helper.processSpecialFunctions(sqlbuilder, ft, attrNames); |
587 | 44376 | jjdelcerro | |
588 | System.out.println("# SQL2:: " + sqlbuilder.toString()); |
||
589 | System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names())); |
||
590 | System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names())); |
||
591 | 44682 | jjdelcerro | System.out.println("# attrNames:: " + StringUtils.join(attrNames,",")); |
592 | 44376 | jjdelcerro | |
593 | //# Test:: testForeingValue
|
||
594 | //# SQL1:: SELECT "ID", "NAME", "DESCRIPTION", "TYPE" FROM "dbo"."test1" WHERE (( (FOREING_VALUE('TYPE.DESCRIPTION')) LIKE ('A%') ) AND ( (FOREING_VALUE('PHONE_TYPE.DESCRIPTION')) = ('mobile') ))
|
||
595 | 44644 | jjdelcerro | //# SQL2:: SELECT "ID", "NAME", "dbo"."test1"."DESCRIPTION", "dbo"."test1"."TYPE", "dbo"."TYPES"."DESCRIPTION", "dbo"."PHONE_TYPES"."DESCRIPTION" FROM "dbo"."test1" LEFT JOIN "dbo"."TYPES" ON ( ("dbo"."test1"."TYPE") = ("dbo"."TYPES"."ID") ) LEFT JOIN "dbo"."PHONE_TYPES" ON ( ("dbo"."test1"."PHONE_TYPE") = ("dbo"."PHONE_TYPES"."ID") ) WHERE (( ("dbo"."TYPES"."DESCRIPTION") LIKE ('A%') ) AND ( ("dbo"."PHONE_TYPES"."DESCRIPTION") = ('mobile') ))
|
596 | 44376 | jjdelcerro | //# Variables:: [DESCRIPTION, DESCRIPTION, DESCRIPTION, ID, NAME, TYPE]
|
597 | //# Parametros:: []
|
||
598 | //# attrNames:: [TYPE.DESCRIPTION, PHONE_TYPE.DESCRIPTION]
|
||
599 | |||
600 | assertEquals( |
||
601 | 46517 | fdiaz | // "SELECT \"dbo\".\"test1\".\"ID\", \"dbo\".\"test1\".\"NAME\", \"dbo\".\"TYPES\".\"DESCRIPTION\", \"dbo\".\"test1\".\"TYPE\", \"dbo\".\"test1\".\"TYPE\" AS \"TYPE1\" FROM \"dbo\".\"test1\" LEFT JOIN \"dbo\".\"TYPES\" ON ( (\"dbo\".\"test1\".\"TYPE\") = (\"dbo\".\"TYPES\".\"ID\") ) LEFT JOIN \"dbo\".\"PHONE_TYPES\" ON ( (\"dbo\".\"test1\".\"PHONE_TYPE\") = (\"dbo\".\"PHONE_TYPES\".\"ID\") ) WHERE (( (\"dbo\".\"TYPES\".\"DESCRIPTION\") LIKE ('A%') ) AND ( (\"dbo\".\"PHONE_TYPES\".\"DESCRIPTION\") = ('mobile') )) ORDER BY \"TYPE\" ASC NULLS LAST",
|
602 | "SELECT \"dbo\".\"test1\".\"ID\", \"dbo\".\"test1\".\"NAME\", \"dbo\".\"TYPES\".\"DESCRIPTION\", \"dbo\".\"test1\".\"TYPE\", \"dbo\".\"test1\".\"TYPE\" AS \"TYPE1\", \"dbo\".\"TYPES\".\"DESCRIPTION\", \"dbo\".\"PHONE_TYPES\".\"DESCRIPTION\" FROM \"dbo\".\"test1\" LEFT JOIN \"dbo\".\"TYPES\" ON ( (\"dbo\".\"test1\".\"TYPE\") = (\"dbo\".\"TYPES\".\"ID\") ) LEFT JOIN \"dbo\".\"PHONE_TYPES\" ON ( (\"dbo\".\"test1\".\"PHONE_TYPE\") = (\"dbo\".\"PHONE_TYPES\".\"ID\") ) WHERE (( (\"dbo\".\"TYPES\".\"DESCRIPTION\") LIKE ('A%') ) AND ( (\"dbo\".\"PHONE_TYPES\".\"DESCRIPTION\") = ('mobile') )) ORDER BY \"TYPE\" ASC NULLS LAST",
|
||
603 | 44376 | jjdelcerro | sqlbuilder.toString() |
604 | ); |
||
605 | assertEquals( |
||
606 | 46505 | fdiaz | "[DESCRIPTION, DESCRIPTION, ID, NAME, TYPE]",
|
607 | 44376 | jjdelcerro | ArrayUtils.toString(sqlbuilder.variables_names()) |
608 | ); |
||
609 | assertEquals( |
||
610 | "[]",
|
||
611 | ArrayUtils.toString(sqlbuilder.parameters_names()) |
||
612 | ); |
||
613 | assertEquals( |
||
614 | 44682 | jjdelcerro | "TYPE.DESCRIPTION,PHONE_TYPE.DESCRIPTION",
|
615 | StringUtils.join(attrNames,",")
|
||
616 | 44376 | jjdelcerro | ); |
617 | } |
||
618 | |||
619 | 45647 | fdiaz | private JDBCHelper createJDBCHelper(){
|
620 | |||
621 | JDBCStoreParameters params = new JDBCStoreParameters();
|
||
622 | JDBCHelperBase helper = new JDBCHelperBase(params);
|
||
623 | return helper;
|
||
624 | |||
625 | } |
||
626 | 44376 | jjdelcerro | |
627 | 43114 | jjdelcerro | } |