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