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