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