Revision 213 trunk/org.gvsig.mssqlserver/org.gvsig.mssqlserver.provider/src/test/java/org/gvsig/mssqlserver/dal/MSSQLServerSQLBuilderTest.java
| MSSQLServerSQLBuilderTest.java | ||
|---|---|---|
| 47 | 47 |
helper = new MSSQLServerHelper(params); |
| 48 | 48 |
} |
| 49 | 49 |
|
| 50 |
List<String> getVariableNames(ExpressionBuilder builder) {
|
|
| 51 |
List<String> vars = new ArrayList<>(); |
|
| 52 |
for (Variable var : builder.getVariables()) {
|
|
| 53 |
vars.add(var.getName()); |
|
| 54 |
} |
|
| 55 |
return vars; |
|
| 56 |
} |
|
| 57 | 50 |
|
| 58 |
List<String> getParameterNames(ExpressionBuilder builder) {
|
|
| 59 |
List<String> params = new ArrayList<>(); |
|
| 60 |
for (Parameter param : builder.getParameters()) {
|
|
| 61 |
String s; |
|
| 62 |
switch(param.getType()) {
|
|
| 63 |
case Constant: |
|
| 64 |
Object value = param.getValue(); |
|
| 65 |
if( value==null ) {
|
|
| 66 |
s = "null"; |
|
| 67 |
} else if( value instanceof String ) {
|
|
| 68 |
s = "'" + (String)value + "'"; |
|
| 69 |
} else {
|
|
| 70 |
s = value.toString(); |
|
| 71 |
} |
|
| 72 |
break; |
|
| 73 |
case Geometry: |
|
| 74 |
case Variable: |
|
| 75 |
default: |
|
| 76 |
s = "\"" + param.getName() + "\""; |
|
| 77 |
} |
|
| 78 |
params.add(s); |
|
| 79 |
} |
|
| 80 |
return params; |
|
| 81 |
} |
|
| 82 |
|
|
| 83 | 51 |
public SQLBuilder createSQLBuilder() {
|
| 84 | 52 |
return new MSSQLServerSQLBuilder(helper); |
| 85 | 53 |
} |
| ... | ... | |
| 95 | 63 |
limit.addVertex(100, 0); |
| 96 | 64 |
limit.addVertex(0, 0); |
| 97 | 65 |
|
| 98 |
SQLBuilder builder = createSQLBuilder(); |
|
| 66 |
SQLBuilder sqlbuilder = createSQLBuilder(); |
|
| 67 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
|
| 99 | 68 |
|
| 100 |
builder.select().column().value( |
|
| 101 |
builder.getAsGeometry(
|
|
| 102 |
builder.ST_ExtentAggregate( |
|
| 103 |
builder.column("the_geom")
|
|
| 69 |
sqlbuilder.select().column().value(
|
|
| 70 |
expbuilder.as_geometry(
|
|
| 71 |
expbuilder.ST_ExtentAggregate(
|
|
| 72 |
expbuilder.column("the_geom")
|
|
| 104 | 73 |
) |
| 105 | 74 |
) |
| 106 | 75 |
).as("envelope");
|
| 107 |
builder.select().from().table().database("master").schema("dbo").name("test1");
|
|
| 108 |
builder.select().where().set( |
|
| 109 |
builder.ST_Intersects( |
|
| 110 |
builder.ST_Envelope( |
|
| 111 |
builder.column("the_geom")
|
|
| 76 |
sqlbuilder.select().from().table().database("master").schema("dbo").name("test1");
|
|
| 77 |
sqlbuilder.select().where().set(
|
|
| 78 |
expbuilder.ST_Intersects(
|
|
| 79 |
expbuilder.ST_Envelope(
|
|
| 80 |
expbuilder.column("the_geom")
|
|
| 112 | 81 |
), |
| 113 |
builder.geometry(limit, proj) |
|
| 82 |
expbuilder.geometry(limit, proj)
|
|
| 114 | 83 |
) |
| 115 | 84 |
); |
| 116 |
builder.select().where().and( |
|
| 117 |
builder.custom("x = 27").add( builder.variable("x") )
|
|
| 85 |
sqlbuilder.select().where().and(
|
|
| 86 |
expbuilder.custom("x = 27").add( expbuilder.variable("x") )
|
|
| 118 | 87 |
); |
| 119 | 88 |
|
| 120 | 89 |
System.out.println("# Test:: testCalulateEnvelope");
|
| 121 |
System.out.println("# SQL:: " + builder.toString());
|
|
| 122 |
System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));
|
|
| 123 |
System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));
|
|
| 90 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
|
| 91 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
|
| 92 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
|
| 124 | 93 |
|
| 125 | 94 |
//# Test:: testCalulateEnvelope |
| 126 | 95 |
//# SQL:: SELECT (geometry::EnvelopeAggregate([the_geom])).STAsBinary() AS [envelope] FROM [master].[dbo].[test1] WHERE (([the_geom]).STEnvelope()).STIntersects(geometry::STGeomFromWKB(0x000000000300000001000000050000000000000000000000000000000000000000000000004059000000000000405900000000000040590000000000004059000000000000000000000000000000000000000000000000000000000000, 4326)) AND x = 27 |
| ... | ... | |
| 128 | 97 |
//# Parametros:: [] |
| 129 | 98 |
assertEquals( |
| 130 | 99 |
"SELECT (geometry::EnvelopeAggregate([the_geom])).STAsBinary() AS [envelope] FROM [master].[dbo].[test1] WHERE (([the_geom]).STEnvelope()).STIntersects(geometry::STGeomFromWKB(0x000000000300000001000000050000000000000000000000000000000000000000000000004059000000000000405900000000000040590000000000004059000000000000000000000000000000000000000000000000000000000000, 4326)) AND x = 27", |
| 131 |
builder.toString() |
|
| 100 |
sqlbuilder.toString()
|
|
| 132 | 101 |
); |
| 133 | 102 |
assertEquals( |
| 134 | 103 |
"[the_geom, x]", |
| 135 |
ArrayUtils.toString(getVariableNames(builder))
|
|
| 104 |
ArrayUtils.toString(sqlbuilder.variables_names())
|
|
| 136 | 105 |
); |
| 137 | 106 |
assertEquals( |
| 138 | 107 |
"[]", |
| 139 |
ArrayUtils.toString(getParameterNames(builder))
|
|
| 108 |
ArrayUtils.toString(sqlbuilder.parameters_names())
|
|
| 140 | 109 |
); |
| 141 | 110 |
} |
| 142 | 111 |
|
| 143 | 112 |
public void testCount() throws Exception {
|
| 144 |
SQLBuilder builder = createSQLBuilder(); |
|
| 113 |
SQLBuilder sqlbuilder = createSQLBuilder(); |
|
| 114 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
|
| 145 | 115 |
|
| 146 |
builder.select().column().value(builder.count().all());
|
|
| 147 |
builder.select().from().table().database("master").schema("dbo").name("test1");
|
|
| 148 |
builder.select().from().subquery(null); |
|
| 149 |
builder.select().where().set( builder.custom("pp = 200").add(builder.variable("pp")));
|
|
| 116 |
sqlbuilder.select().column().value(sqlbuilder.count().all());
|
|
| 117 |
sqlbuilder.select().from().table().database("master").schema("dbo").name("test1");
|
|
| 118 |
sqlbuilder.select().from().subquery(null);
|
|
| 119 |
sqlbuilder.select().where().set( expbuilder.custom("pp = 200").add(expbuilder.variable("pp")));
|
|
| 150 | 120 |
|
| 151 | 121 |
System.out.println("# Test:: testCount");
|
| 152 |
System.out.println("# SQL:: " + builder.toString());
|
|
| 153 |
System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));
|
|
| 154 |
System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));
|
|
| 122 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
|
| 123 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
|
| 124 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
|
| 155 | 125 |
|
| 156 | 126 |
//# Test:: testCount |
| 157 | 127 |
//# SQL:: SELECT COUNT(*) FROM [master].[dbo].[test1] WHERE pp = 200 |
| ... | ... | |
| 160 | 130 |
|
| 161 | 131 |
assertEquals( |
| 162 | 132 |
"SELECT COUNT(*) FROM [master].[dbo].[test1] WHERE pp = 200", |
| 163 |
builder.toString() |
|
| 133 |
sqlbuilder.toString()
|
|
| 164 | 134 |
); |
| 165 | 135 |
assertEquals( |
| 166 | 136 |
"[pp]", |
| 167 |
ArrayUtils.toString(getVariableNames(builder))
|
|
| 137 |
ArrayUtils.toString(sqlbuilder.variables_names())
|
|
| 168 | 138 |
); |
| 169 | 139 |
assertEquals( |
| 170 | 140 |
"[]", |
| 171 |
ArrayUtils.toString(getParameterNames(builder))
|
|
| 141 |
ArrayUtils.toString(sqlbuilder.parameters_names())
|
|
| 172 | 142 |
); |
| 173 | 143 |
} |
| 174 | 144 |
|
| 175 | 145 |
public void testUpdateStatistics() throws Exception {
|
| 176 |
SQLBuilder builder = createSQLBuilder(); |
|
| 146 |
SQLBuilder sqlbuilder = createSQLBuilder(); |
|
| 147 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
|
| 177 | 148 |
|
| 178 |
builder.update_table_statistics().table().database("master").schema("dbo").name("test1");
|
|
| 149 |
sqlbuilder.update_table_statistics().table().database("master").schema("dbo").name("test1");
|
|
| 179 | 150 |
|
| 180 | 151 |
System.out.println("# Test:: testUpdateStatistics");
|
| 181 |
System.out.println("# SQL:: " + builder.toString());
|
|
| 182 |
System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));
|
|
| 183 |
System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));
|
|
| 152 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
|
| 153 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
|
| 154 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
|
| 184 | 155 |
|
| 185 | 156 |
//# Test:: testUpdateStatistics |
| 186 | 157 |
//# SQL:: UPDATE STATISTICS [master].[dbo].[test1] |
| ... | ... | |
| 189 | 160 |
|
| 190 | 161 |
assertEquals( |
| 191 | 162 |
"UPDATE STATISTICS [master].[dbo].[test1]", |
| 192 |
builder.toString() |
|
| 163 |
sqlbuilder.toString()
|
|
| 193 | 164 |
); |
| 194 | 165 |
assertEquals( |
| 195 | 166 |
"[]", |
| 196 |
ArrayUtils.toString(getVariableNames(builder))
|
|
| 167 |
ArrayUtils.toString(sqlbuilder.variables_names())
|
|
| 197 | 168 |
); |
| 198 | 169 |
assertEquals( |
| 199 | 170 |
"[]", |
| 200 |
ArrayUtils.toString(getParameterNames(builder))
|
|
| 171 |
ArrayUtils.toString(sqlbuilder.parameters_names())
|
|
| 201 | 172 |
); |
| 202 | 173 |
} |
| 203 | 174 |
|
| 204 | 175 |
public void testCreateTable() throws Exception {
|
| 205 |
SQLBuilder builder = createSQLBuilder(); |
|
| 206 |
|
|
| 207 |
builder.create_table().table().database("master").schema("dbo").name("test1");
|
|
| 208 |
builder.create_table().add_column( |
|
| 176 |
SQLBuilder sqlbuilder = createSQLBuilder(); |
|
| 177 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
|
| 178 |
|
|
| 179 |
sqlbuilder.create_table().table().database("master").schema("dbo").name("test1");
|
|
| 180 |
sqlbuilder.create_table().add_column( |
|
| 209 | 181 |
"name", |
| 210 | 182 |
DataTypes.STRING, |
| 211 | 183 |
45, |
| ... | ... | |
| 216 | 188 |
false, |
| 217 | 189 |
null |
| 218 | 190 |
); |
| 219 |
builder.create_table().add_column( |
|
| 191 |
sqlbuilder.create_table().add_column(
|
|
| 220 | 192 |
"id", |
| 221 | 193 |
DataTypes.INT, |
| 222 | 194 |
0, |
| ... | ... | |
| 227 | 199 |
true, |
| 228 | 200 |
0 |
| 229 | 201 |
); |
| 230 |
builder.create_table().add_column( |
|
| 202 |
sqlbuilder.create_table().add_column(
|
|
| 231 | 203 |
"geom", |
| 232 | 204 |
DataTypes.GEOMETRY, |
| 233 | 205 |
0, |
| ... | ... | |
| 240 | 212 |
); |
| 241 | 213 |
|
| 242 | 214 |
System.out.println("# Test:: testCreateTable");
|
| 243 |
System.out.println("# SQL:: " + builder.toString());
|
|
| 244 |
System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));
|
|
| 245 |
System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));
|
|
| 215 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
|
| 216 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
|
| 217 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
|
| 246 | 218 |
|
| 247 | 219 |
//# Test:: testCreateTable |
| 248 | 220 |
//# SQL:: CREATE TABLE [master].[dbo].[test1] ([name] VARCHAR(45) DEFAULT NULL NULL, [id] INT IDENTITY(1,1) DEFAULT '0' NOT NULL PRIMARY KEY, [geom] GEOMETRY DEFAULT NULL NULL ) |
| ... | ... | |
| 251 | 223 |
|
| 252 | 224 |
assertEquals( |
| 253 | 225 |
"CREATE TABLE [master].[dbo].[test1] ([name] VARCHAR(45) DEFAULT NULL NULL, [id] INT IDENTITY(1,1) DEFAULT '0' NOT NULL PRIMARY KEY, [geom] GEOMETRY DEFAULT NULL NULL )", |
| 254 |
builder.toString() |
|
| 226 |
sqlbuilder.toString()
|
|
| 255 | 227 |
); |
| 256 | 228 |
assertEquals( |
| 257 | 229 |
"[]", |
| 258 |
ArrayUtils.toString(getVariableNames(builder))
|
|
| 230 |
ArrayUtils.toString(sqlbuilder.variables_names())
|
|
| 259 | 231 |
); |
| 260 | 232 |
assertEquals( |
| 261 | 233 |
"[]", |
| 262 |
ArrayUtils.toString(getParameterNames(builder))
|
|
| 234 |
ArrayUtils.toString(sqlbuilder.parameters_names())
|
|
| 263 | 235 |
); |
| 264 | 236 |
} |
| 265 | 237 |
|
| 266 | 238 |
public void testDropTable() throws Exception {
|
| 267 |
SQLBuilder builder = createSQLBuilder(); |
|
| 239 |
SQLBuilder sqlbuilder = createSQLBuilder(); |
|
| 240 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
|
| 268 | 241 |
|
| 269 |
builder.drop_table().table().database("master").schema("dbo").name("test1");
|
|
| 242 |
sqlbuilder.drop_table().table().database("master").schema("dbo").name("test1");
|
|
| 270 | 243 |
|
| 271 | 244 |
System.out.println("# Test:: testDropTable");
|
| 272 |
System.out.println("# SQL:: " + builder.toString());
|
|
| 273 |
System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));
|
|
| 274 |
System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));
|
|
| 245 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
|
| 246 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
|
| 247 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
|
| 275 | 248 |
|
| 276 | 249 |
//# Test:: testDropTable |
| 277 | 250 |
//# SQL:: DROP TABLE [master].[dbo].[test1] |
| ... | ... | |
| 280 | 253 |
|
| 281 | 254 |
assertEquals( |
| 282 | 255 |
"DROP TABLE [master].[dbo].[test1]", |
| 283 |
builder.toString() |
|
| 256 |
sqlbuilder.toString()
|
|
| 284 | 257 |
); |
| 285 | 258 |
assertEquals( |
| 286 | 259 |
"[]", |
| 287 |
ArrayUtils.toString(getVariableNames(builder))
|
|
| 260 |
ArrayUtils.toString(sqlbuilder.variables_names())
|
|
| 288 | 261 |
); |
| 289 | 262 |
assertEquals( |
| 290 | 263 |
"[]", |
| 291 |
ArrayUtils.toString(getParameterNames(builder))
|
|
| 264 |
ArrayUtils.toString(sqlbuilder.parameters_names())
|
|
| 292 | 265 |
); |
| 293 | 266 |
} |
| 294 | 267 |
|
| 295 | 268 |
public void testFetchFeatureProviderByReference() throws Exception {
|
| 296 |
SQLBuilder builder = createSQLBuilder(); |
|
| 269 |
SQLBuilder sqlbuilder = createSQLBuilder(); |
|
| 270 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
|
| 297 | 271 |
|
| 298 | 272 |
String value = "yoyo"; |
| 299 |
builder.select().column().name("name");
|
|
| 300 |
builder.select().column().name("id");
|
|
| 301 |
builder.select().column().name("geom").as_geometry();
|
|
| 302 |
builder.select().from().table().database("master").schema("dbo").name("test1");
|
|
| 303 |
builder.select().where().set( |
|
| 304 |
builder.eq( |
|
| 305 |
builder.column("name"),
|
|
| 306 |
builder.parameter(value).as_constant() |
|
| 273 |
sqlbuilder.select().column().name("name");
|
|
| 274 |
sqlbuilder.select().column().name("id");
|
|
| 275 |
sqlbuilder.select().column().name("geom").as_geometry();
|
|
| 276 |
sqlbuilder.select().from().table().database("master").schema("dbo").name("test1");
|
|
| 277 |
sqlbuilder.select().where().set(
|
|
| 278 |
expbuilder.eq(
|
|
| 279 |
expbuilder.column("name"),
|
|
| 280 |
expbuilder.parameter(value).as_constant()
|
|
| 307 | 281 |
) |
| 308 | 282 |
); |
| 309 |
builder.select().limit(1); |
|
| 283 |
sqlbuilder.select().limit(1);
|
|
| 310 | 284 |
|
| 311 | 285 |
System.out.println("# Test:: testFetchFeatureProviderByReference");
|
| 312 |
System.out.println("# SQL:: " + builder.toString());
|
|
| 313 |
System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));
|
|
| 314 |
System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));
|
|
| 286 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
|
| 287 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
|
| 288 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
|
| 315 | 289 |
|
| 316 | 290 |
//# Test:: testFetchFeatureProviderByReference |
| 317 | 291 |
//# SQL:: SELECT TOP 1 [name], [id], ([geom]).STAsBinary() FROM [master].[dbo].[test1] WHERE ( ([name]) = (?) ) |
| ... | ... | |
| 320 | 294 |
|
| 321 | 295 |
assertEquals( |
| 322 | 296 |
"SELECT TOP 1 [name], [id], ([geom]).STAsBinary() FROM [master].[dbo].[test1] WHERE ( ([name]) = (?) )", |
| 323 |
builder.toString() |
|
| 297 |
sqlbuilder.toString()
|
|
| 324 | 298 |
); |
| 325 | 299 |
assertEquals( |
| 326 | 300 |
"[geom, id, name]", |
| 327 |
ArrayUtils.toString(getVariableNames(builder))
|
|
| 301 |
ArrayUtils.toString(sqlbuilder.variables_names())
|
|
| 328 | 302 |
); |
| 329 | 303 |
assertEquals( |
| 330 | 304 |
"['yoyo']", |
| 331 |
ArrayUtils.toString(getParameterNames(builder))
|
|
| 305 |
ArrayUtils.toString(sqlbuilder.parameters_names())
|
|
| 332 | 306 |
); |
| 333 | 307 |
} |
| 334 | 308 |
|
| 335 | 309 |
public void testIsNull() throws Exception {
|
| 336 |
SQLBuilder builder = createSQLBuilder(); |
|
| 310 |
SQLBuilder sqlbuilder = createSQLBuilder(); |
|
| 311 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
|
| 337 | 312 |
|
| 338 |
builder.select().column().name("name");
|
|
| 339 |
builder.select().column().name("id");
|
|
| 340 |
builder.select().column().name("geom").as_geometry();
|
|
| 341 |
builder.select().from().table().database("master").schema("dbo").name("test1");
|
|
| 342 |
builder.select().where().set( |
|
| 343 |
builder.and( |
|
| 344 |
builder.isNull(
|
|
| 345 |
builder.column("name")
|
|
| 313 |
sqlbuilder.select().column().name("name");
|
|
| 314 |
sqlbuilder.select().column().name("id");
|
|
| 315 |
sqlbuilder.select().column().name("geom").as_geometry();
|
|
| 316 |
sqlbuilder.select().from().table().database("master").schema("dbo").name("test1");
|
|
| 317 |
sqlbuilder.select().where().set(
|
|
| 318 |
expbuilder.and(
|
|
| 319 |
expbuilder.is_null(
|
|
| 320 |
expbuilder.column("name")
|
|
| 346 | 321 |
), |
| 347 |
builder.notIsNull(
|
|
| 348 |
builder.column("id")
|
|
| 322 |
expbuilder.not_is_null(
|
|
| 323 |
expbuilder.column("id")
|
|
| 349 | 324 |
) |
| 350 | 325 |
) |
| 351 | 326 |
); |
| 352 |
builder.select().limit(2); |
|
| 327 |
sqlbuilder.select().limit(2);
|
|
| 353 | 328 |
|
| 354 | 329 |
System.out.println("# Test:: testIsNull");
|
| 355 |
System.out.println("# SQL:: " + builder.toString());
|
|
| 356 |
System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));
|
|
| 357 |
System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));
|
|
| 330 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
|
| 331 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
|
| 332 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
|
| 358 | 333 |
|
| 359 | 334 |
//# Test:: testIsNull |
| 360 | 335 |
//# SQL:: SELECT TOP 2 [name], [id], ([geom]).STAsBinary() FROM [master].[dbo].[test1] WHERE ( ([name]) IS NULL ) AND ( ([id]) NOT IS NULL ) |
| ... | ... | |
| 363 | 338 |
|
| 364 | 339 |
assertEquals( |
| 365 | 340 |
"SELECT TOP 2 [name], [id], ([geom]).STAsBinary() FROM [master].[dbo].[test1] WHERE ( ([name]) IS NULL ) AND ( ([id]) NOT IS NULL )", |
| 366 |
builder.toString() |
|
| 341 |
sqlbuilder.toString()
|
|
| 367 | 342 |
); |
| 368 | 343 |
assertEquals( |
| 369 | 344 |
"[geom, id, name]", |
| 370 |
ArrayUtils.toString(getVariableNames(builder))
|
|
| 345 |
ArrayUtils.toString(sqlbuilder.variables_names())
|
|
| 371 | 346 |
); |
| 372 | 347 |
assertEquals( |
| 373 | 348 |
"[]", |
| 374 |
ArrayUtils.toString(getParameterNames(builder))
|
|
| 349 |
ArrayUtils.toString(sqlbuilder.parameters_names())
|
|
| 375 | 350 |
); |
| 376 | 351 |
} |
| 377 | 352 |
|
| 378 | 353 |
public void testFetchFeatureType() throws Exception {
|
| 379 |
SQLBuilder builder = createSQLBuilder(); |
|
| 354 |
SQLBuilder sqlbuilder = createSQLBuilder(); |
|
| 355 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
|
| 356 |
|
|
| 357 |
sqlbuilder.select().column().all(); |
|
| 358 |
sqlbuilder.select().from().table().database("master").schema("dbo").name("test1");
|
|
| 359 |
sqlbuilder.select().limit(1); |
|
| 380 | 360 |
|
| 381 |
builder.select().column().all(); |
|
| 382 |
builder.select().from().table().database("master").schema("dbo").name("test1");
|
|
| 383 |
builder.select().limit(1); |
|
| 384 |
|
|
| 385 | 361 |
System.out.println("# Test:: testFetchFeatureType");
|
| 386 |
System.out.println("# SQL:: " + builder.toString());
|
|
| 387 |
System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));
|
|
| 388 |
System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));
|
|
| 362 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
|
| 363 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
|
| 364 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
|
| 389 | 365 |
|
| 390 | 366 |
//# Test:: testFetchFeatureType |
| 391 | 367 |
//# SQL:: SELECT TOP 1 * FROM [master].[dbo].[test1] |
| ... | ... | |
| 394 | 370 |
|
| 395 | 371 |
assertEquals( |
| 396 | 372 |
"SELECT TOP 1 * FROM [master].[dbo].[test1]", |
| 397 |
builder.toString() |
|
| 373 |
sqlbuilder.toString()
|
|
| 398 | 374 |
); |
| 399 | 375 |
assertEquals( |
| 400 | 376 |
"[]", |
| 401 |
ArrayUtils.toString(getVariableNames(builder))
|
|
| 377 |
ArrayUtils.toString(sqlbuilder.variables_names())
|
|
| 402 | 378 |
); |
| 403 | 379 |
assertEquals( |
| 404 | 380 |
"[]", |
| 405 |
ArrayUtils.toString(getParameterNames(builder))
|
|
| 381 |
ArrayUtils.toString(sqlbuilder.parameters_names())
|
|
| 406 | 382 |
); |
| 407 | 383 |
} |
| 408 | 384 |
|
| 409 | 385 |
public void testPerformDeletes() throws Exception {
|
| 410 |
SQLBuilder builder = createSQLBuilder(); |
|
| 411 |
|
|
| 412 |
builder.delete().table().database("master").schema("dbo").name("test1");
|
|
| 413 |
builder.delete().where().and( |
|
| 414 |
builder.eq( |
|
| 415 |
builder.column("id1"),
|
|
| 416 |
builder.parameter("id1").as_variable()
|
|
| 386 |
SQLBuilder sqlbuilder = createSQLBuilder(); |
|
| 387 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
|
| 388 |
|
|
| 389 |
sqlbuilder.delete().table().database("master").schema("dbo").name("test1");
|
|
| 390 |
sqlbuilder.delete().where().and( |
|
| 391 |
expbuilder.eq( |
|
| 392 |
expbuilder.column("id1"),
|
|
| 393 |
expbuilder.parameter("id1").as_variable()
|
|
| 417 | 394 |
) |
| 418 | 395 |
); |
| 419 |
builder.delete().where().and( |
|
| 420 |
builder.eq( |
|
| 421 |
builder.column("id2"),
|
|
| 422 |
builder.parameter("id2").as_variable()
|
|
| 396 |
sqlbuilder.delete().where().and(
|
|
| 397 |
expbuilder.eq(
|
|
| 398 |
expbuilder.column("id2"),
|
|
| 399 |
expbuilder.parameter("id2").as_variable()
|
|
| 423 | 400 |
) |
| 424 | 401 |
); |
| 425 | 402 |
|
| 426 | 403 |
System.out.println("# Test:: testPerformDeletes");
|
| 427 |
System.out.println("# SQL:: " + builder.toString());
|
|
| 428 |
System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));
|
|
| 429 |
System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));
|
|
| 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()));
|
|
| 430 | 407 |
|
| 431 | 408 |
//# Test:: testPerformDeletes |
| 432 | 409 |
//# SQL:: DELETE FROM [master].[dbo].[test1] WHERE ( ([id1]) = (?) ) AND ( ([id2]) = (?) ) |
| ... | ... | |
| 435 | 412 |
|
| 436 | 413 |
assertEquals( |
| 437 | 414 |
"DELETE FROM [master].[dbo].[test1] WHERE ( ([id1]) = (?) ) AND ( ([id2]) = (?) )", |
| 438 |
builder.toString() |
|
| 415 |
sqlbuilder.toString()
|
|
| 439 | 416 |
); |
| 440 | 417 |
assertEquals( |
| 441 | 418 |
"[id1, id2]", |
| 442 |
ArrayUtils.toString(getVariableNames(builder))
|
|
| 419 |
ArrayUtils.toString(sqlbuilder.variables_names())
|
|
| 443 | 420 |
); |
| 444 | 421 |
assertEquals( |
| 445 | 422 |
"[\"id1\", \"id2\"]", |
| 446 |
ArrayUtils.toString(getParameterNames(builder))
|
|
| 423 |
ArrayUtils.toString(sqlbuilder.parameters_names())
|
|
| 447 | 424 |
); |
| 448 | 425 |
} |
| 449 | 426 |
|
| 450 | 427 |
public void testPerformInserts1() throws Exception {
|
| 451 | 428 |
IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
| 452 | 429 |
|
| 453 |
SQLBuilder builder = createSQLBuilder(); |
|
| 454 |
|
|
| 455 |
builder.insert().table().database("master").schema("dbo").name("test1");
|
|
| 456 |
builder.insert().column().name("id").with_value(builder.parameter("id"));
|
|
| 457 |
builder.insert().column().name("name").with_value(builder.parameter("name"));
|
|
| 458 |
builder.insert().column().name("geom").with_value(builder.parameter("geom").as_geometry_variable().srs(proj));
|
|
| 430 |
SQLBuilder sqlbuilder = createSQLBuilder(); |
|
| 431 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
|
| 459 | 432 |
|
| 433 |
sqlbuilder.insert().table().database("master").schema("dbo").name("test1");
|
|
| 434 |
sqlbuilder.insert().column().name("id").with_value(expbuilder.parameter("id"));
|
|
| 435 |
sqlbuilder.insert().column().name("name").with_value(expbuilder.parameter("name"));
|
|
| 436 |
sqlbuilder.insert().column().name("geom").with_value(expbuilder.parameter("geom").as_geometry_variable().srs(proj));
|
|
| 437 |
|
|
| 460 | 438 |
System.out.println("# Test:: testPerformInserts1");
|
| 461 |
System.out.println("# SQL:: " + builder.toString());
|
|
| 462 |
System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));
|
|
| 463 |
System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));
|
|
| 439 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
|
| 440 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
|
| 441 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
|
| 464 | 442 |
|
| 465 | 443 |
//# Test:: testPerformInserts1 |
| 466 | 444 |
//# SQL:: INSERT INTO [master].[dbo].[test1] ( [id], [name], [geom] ) VALUES ( ?, ?, geometry::STGeomFromWKB(?, 4326) ) |
| ... | ... | |
| 469 | 447 |
|
| 470 | 448 |
assertEquals( |
| 471 | 449 |
"INSERT INTO [master].[dbo].[test1] ( [id], [name], [geom] ) VALUES ( ?, ?, geometry::STGeomFromWKB(?, 4326) )", |
| 472 |
builder.toString() |
|
| 450 |
sqlbuilder.toString()
|
|
| 473 | 451 |
); |
| 474 | 452 |
assertEquals( |
| 475 | 453 |
"[geom, id, name]", |
| 476 |
ArrayUtils.toString(getVariableNames(builder))
|
|
| 454 |
ArrayUtils.toString(sqlbuilder.variables_names())
|
|
| 477 | 455 |
); |
| 478 | 456 |
assertEquals( |
| 479 | 457 |
"[\"id\", \"name\", \"geom\"]", |
| 480 |
ArrayUtils.toString(getParameterNames(builder))
|
|
| 458 |
ArrayUtils.toString(sqlbuilder.parameters_names())
|
|
| 481 | 459 |
); |
| 482 | 460 |
} |
| 483 | 461 |
|
| 484 | 462 |
public void testPerformInserts2() throws Exception {
|
| 485 | 463 |
IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
| 486 | 464 |
|
| 487 |
SQLBuilder builder = createSQLBuilder(); |
|
| 488 |
|
|
| 489 |
builder.insert().table().database("master").schema("dbo").name("test1");
|
|
| 490 |
builder.insert().column().name("id").with_value(builder.parameter("id"));
|
|
| 491 |
builder.insert().column().name("name").with_value(builder.parameter("name"));
|
|
| 492 |
builder.insert().column().name("geom").with_value(builder.parameter("geom").as_geometry_variable().srs(proj));
|
|
| 465 |
SQLBuilder sqlbuilder = createSQLBuilder(); |
|
| 466 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
|
| 493 | 467 |
|
| 468 |
sqlbuilder.insert().table().database("master").schema("dbo").name("test1");
|
|
| 469 |
sqlbuilder.insert().column().name("id").with_value(expbuilder.parameter("id"));
|
|
| 470 |
sqlbuilder.insert().column().name("name").with_value(expbuilder.parameter("name"));
|
|
| 471 |
sqlbuilder.insert().column().name("geom").with_value(expbuilder.parameter("geom").as_geometry_variable().srs(proj));
|
|
| 472 |
|
|
| 494 | 473 |
System.out.println("# Test:: testPerformInserts2");
|
| 495 |
System.out.println("# SQL:: " + builder.toString());
|
|
| 496 |
System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));
|
|
| 497 |
System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));
|
|
| 474 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
|
| 475 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
|
| 476 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
|
| 498 | 477 |
|
| 499 | 478 |
//# Test:: testPerformInserts2 |
| 500 | 479 |
//# SQL:: INSERT INTO [master].[dbo].[test1] ( [id], [name], [geom] ) VALUES ( ?, ?, geometry::STGeomFromWKB(?, ?) ) |
| ... | ... | |
| 503 | 482 |
|
| 504 | 483 |
assertEquals( |
| 505 | 484 |
"INSERT INTO [master].[dbo].[test1] ( [id], [name], [geom] ) VALUES ( ?, ?, geometry::STGeomFromWKB(?, ?) )", |
| 506 |
builder.toString() |
|
| 485 |
sqlbuilder.toString()
|
|
| 507 | 486 |
); |
| 508 | 487 |
assertEquals( |
| 509 | 488 |
"[geom, id, name]", |
| 510 |
ArrayUtils.toString(getVariableNames(builder))
|
|
| 489 |
ArrayUtils.toString(sqlbuilder.variables_names())
|
|
| 511 | 490 |
); |
| 512 | 491 |
assertEquals( |
| 513 | 492 |
"[\"id\", \"name\", \"geom\", 4326]", |
| 514 |
ArrayUtils.toString(getParameterNames(builder))
|
|
| 493 |
ArrayUtils.toString(sqlbuilder.parameters_names())
|
|
| 515 | 494 |
); |
| 516 | 495 |
} |
| 517 | 496 |
|
| 518 | 497 |
public void testPerformUpdates() throws Exception {
|
| 519 | 498 |
IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
| 520 | 499 |
|
| 521 |
SQLBuilder builder = createSQLBuilder(); |
|
| 522 |
|
|
| 523 |
builder.update().table().database("master").schema("dbo").name("test1");
|
|
| 524 |
builder.update().where().and( |
|
| 525 |
builder.eq( |
|
| 526 |
builder.column("id"),
|
|
| 527 |
builder.parameter("id").as_variable()
|
|
| 500 |
SQLBuilder sqlbuilder = createSQLBuilder(); |
|
| 501 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
|
| 502 |
|
|
| 503 |
sqlbuilder.update().table().database("master").schema("dbo").name("test1");
|
|
| 504 |
sqlbuilder.update().where().and( |
|
| 505 |
expbuilder.eq( |
|
| 506 |
expbuilder.column("id"),
|
|
| 507 |
expbuilder.parameter("id").as_variable()
|
|
| 528 | 508 |
) |
| 529 | 509 |
); |
| 530 |
builder.update().column().name("name").with_value(builder.parameter("name"));
|
|
| 531 |
builder.update().column().name("geom").with_value(
|
|
| 532 |
builder.parameter("geom").as_geometry_variable().srs(proj)
|
|
| 510 |
sqlbuilder.update().column().name("name").with_value(expbuilder.parameter("name"));
|
|
| 511 |
sqlbuilder.update().column().name("geom").with_value(
|
|
| 512 |
expbuilder.parameter("geom").as_geometry_variable().srs(
|
|
| 513 |
expbuilder.parameter().value(proj)) |
|
| 533 | 514 |
); |
| 534 | 515 |
|
| 535 | 516 |
System.out.println("# Test:: testPerformUpdates");
|
| 536 |
System.out.println("# SQL:: " + builder.toString());
|
|
| 537 |
System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));
|
|
| 538 |
System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));
|
|
| 517 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
|
| 518 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
|
| 519 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
|
| 539 | 520 |
|
| 540 | 521 |
//# Test:: testPerformUpdates |
| 541 | 522 |
//# SQL:: UPDATE [master].[dbo].[test1] SET [name] = ?, [geom] = geometry::STGeomFromWKB(?, ?) WHERE ( ([id]) = (?) ) |
| ... | ... | |
| 544 | 525 |
|
| 545 | 526 |
assertEquals( |
| 546 | 527 |
"UPDATE [master].[dbo].[test1] SET [name] = ?, [geom] = geometry::STGeomFromWKB(?, ?) WHERE ( ([id]) = (?) )", |
| 547 |
builder.toString() |
|
| 528 |
sqlbuilder.toString()
|
|
| 548 | 529 |
); |
| 549 | 530 |
assertEquals( |
| 550 | 531 |
"[geom, id, name]", |
| 551 |
ArrayUtils.toString(getVariableNames(builder))
|
|
| 532 |
ArrayUtils.toString(sqlbuilder.variables_names())
|
|
| 552 | 533 |
); |
| 553 | 534 |
assertEquals( |
| 554 | 535 |
"[\"name\", \"geom\", 4326, \"id\"]", |
| 555 |
ArrayUtils.toString(getParameterNames(builder))
|
|
| 536 |
ArrayUtils.toString(sqlbuilder.parameters_names())
|
|
| 556 | 537 |
); |
| 557 | 538 |
} |
| 558 | 539 |
|
Also available in: Unified diff