Revision 44361 trunk/org.gvsig.desktop/org.gvsig.desktop.compat.cdc/org.gvsig.fmap.dal/org.gvsig.fmap.dal.db/org.gvsig.fmap.dal.db.h2/src/test/java/org/gvsig/fmap/dal/store/h2/H2SpatialSQLBuilderTest.java
| H2SpatialSQLBuilderTest.java | ||
|---|---|---|
| 2 | 2 |
|
| 3 | 3 |
import junit.framework.TestCase; |
| 4 | 4 |
import org.apache.commons.lang3.ArrayUtils; |
| 5 |
import org.apache.commons.lang3.StringUtils; |
|
| 5 | 6 |
import org.cresques.cts.IProjection; |
| 6 | 7 |
import org.gvsig.expressionevaluator.ExpressionBuilder; |
| 7 | 8 |
import org.gvsig.fmap.crs.CRSFactory; |
| 8 | 9 |
import org.gvsig.fmap.dal.SQLBuilder; |
| 9 | 10 |
import org.gvsig.fmap.dal.SQLBuilder.Privilege; |
| 10 | 11 |
import org.gvsig.fmap.dal.feature.spi.SQLBuilderBase; |
| 12 |
import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference; |
|
| 13 |
import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_TABLE; |
|
| 14 |
import org.gvsig.fmap.dal.store.jdbc2.spi.operations.OperationsFactoryBase; |
|
| 11 | 15 |
import org.gvsig.fmap.geom.DataTypes; |
| 12 | 16 |
import org.gvsig.fmap.geom.Geometry; |
| 13 | 17 |
import org.gvsig.fmap.geom.GeometryLocator; |
| ... | ... | |
| 36 | 40 |
return new H2SpatialSQLBuilder(new H2SpatialHelper(null)); |
| 37 | 41 |
} |
| 38 | 42 |
|
| 43 |
public void testCalulateEnvelopeOfColumn() throws Exception {
|
|
| 44 |
|
|
| 45 |
TableReference table = new OperationsFactoryBase.DefaultTableReference("master","dbo","test1", null);
|
|
| 46 |
String columnName = "the_geom"; |
|
| 47 |
|
|
| 48 |
SQLBuilder sqlbuilder = createSQLBuilder(); |
|
| 49 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
|
| 50 |
|
|
| 51 |
sqlbuilder.select().column().value( |
|
| 52 |
expbuilder.as_geometry( |
|
| 53 |
expbuilder.ST_ExtentAggregate( |
|
| 54 |
expbuilder.column(columnName) |
|
| 55 |
) |
|
| 56 |
) |
|
| 57 |
); |
|
| 58 |
//sqlbuilder.select().group_by(expbuilder.column(columnName)); |
|
| 59 |
sqlbuilder.select().from().table() |
|
| 60 |
.database(table.getDatabase()) |
|
| 61 |
.schema(table.getSchema()) |
|
| 62 |
.name(table.getTable()); |
|
| 63 |
sqlbuilder.select().from().subquery(table.getSubquery()); |
|
| 64 |
|
|
| 65 |
sqlbuilder.select().where().and( |
|
| 66 |
expbuilder.not_is_null(expbuilder.column(columnName)) |
|
| 67 |
); |
|
| 68 |
|
|
| 69 |
System.out.println("# Test:: testCalulateEnvelopeOfColumn");
|
|
| 70 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
|
| 71 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
|
| 72 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
|
| 73 |
assertEquals( |
|
| 74 |
"SELECT ST_AsBinary(ST_Extent(\"the_geom\")) FROM \"dbo\".\"test1\" WHERE ( (\"the_geom\") NOT IS NULL )", |
|
| 75 |
sqlbuilder.toString() |
|
| 76 |
); |
|
| 77 |
assertEquals( |
|
| 78 |
"[the_geom]", |
|
| 79 |
ArrayUtils.toString(sqlbuilder.variables_names()) |
|
| 80 |
); |
|
| 81 |
assertEquals( |
|
| 82 |
"[]", |
|
| 83 |
ArrayUtils.toString(sqlbuilder.parameters_names()) |
|
| 84 |
); |
|
| 85 |
} |
|
| 39 | 86 |
|
| 40 | 87 |
public void testCalulateEnvelope() throws Exception {
|
| 41 | 88 |
GeometryManager geometryManager = GeometryLocator.getGeometryManager(); |
| ... | ... | |
| 76 | 123 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 77 | 124 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 78 | 125 |
assertEquals( |
| 79 |
"SELECT ST_AsBinary(ST_Extent(\"the_geom\")) AS \"envelope\" FROM \"test1\" WHERE (( ((ST_Envelope(\"the_geom\")) && (ST_GeomFromWKB((x'000000000300000001000000050000000000000000000000000000000000000000000000004059000000000000405900000000000040590000000000004059000000000000000000000000000000000000000000000000000000000000'), (4326)))) AND ST_Intersects((ST_Envelope(\"the_geom\")),(ST_GeomFromWKB((x'000000000300000001000000050000000000000000000000000000000000000000000000004059000000000000405900000000000040590000000000004059000000000000000000000000000000000000000000000000000000000000'), (4326))) )) AND x = 27)", |
|
| 126 |
"SELECT ST_AsBinary(ST_Extent(\"the_geom\")) AS \"envelope\" FROM \"dbo\".\"test1\" WHERE (( ((ST_Envelope(\"the_geom\")) && (ST_GeomFromWKB((x'000000000300000001000000050000000000000000000000000000000000000000000000004059000000000000405900000000000040590000000000004059000000000000000000000000000000000000000000000000000000000000'), (4326)))) AND ST_Intersects((ST_Envelope(\"the_geom\")),(ST_GeomFromWKB((x'000000000300000001000000050000000000000000000000000000000000000000000000004059000000000000405900000000000040590000000000004059000000000000000000000000000000000000000000000000000000000000'), (4326))) )) AND x = 27)",
|
|
| 80 | 127 |
sqlbuilder.toString() |
| 81 | 128 |
); |
| 82 | 129 |
assertEquals( |
| ... | ... | |
| 109 | 156 |
//# Parametros:: [] |
| 110 | 157 |
|
| 111 | 158 |
assertEquals( |
| 112 |
"SELECT COUNT(*) FROM \"test1\" WHERE pp = 200", |
|
| 159 |
"SELECT COUNT(*) FROM \"dbo\".\"test1\" WHERE pp = 200",
|
|
| 113 | 160 |
sqlbuilder.toString() |
| 114 | 161 |
); |
| 115 | 162 |
assertEquals( |
| ... | ... | |
| 168 | 215 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 169 | 216 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 170 | 217 |
assertEquals( |
| 171 |
"CREATE TABLE \"test1\" (\"name\" VARCHAR(45) DEFAULT NULL, \"id\" INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL, \"geom\" GEOMETRY ); ALTER TABLE \"test1\" ADD CONSTRAINT IF NOT EXISTS \"constraint_test1_geom_dim\" CHECK ST_CoordDim(\"geom\") = 2",
|
|
| 218 |
"CREATE TABLE \"dbo\".\"test1\" (\"name\" VARCHAR(45) DEFAULT NULL, \"id\" INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL, \"geom\" GEOMETRY ); ALTER TABLE \"dbo\".\"test1\" ADD CONSTRAINT IF NOT EXISTS \"constraint_test1_geom_dim\" CHECK NVL2(\"geom\", ST_CoordDim(\"geom\") = 2, TRUE)",
|
|
| 172 | 219 |
sqlbuilder.toString() |
| 173 | 220 |
); |
| 174 | 221 |
assertEquals( |
| ... | ... | |
| 194 | 241 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 195 | 242 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 196 | 243 |
assertEquals( |
| 197 |
"DROP TABLE \"test1\"", |
|
| 244 |
"DROP TABLE \"dbo\".\"test1\"",
|
|
| 198 | 245 |
sqlbuilder.toString() |
| 199 | 246 |
); |
| 200 | 247 |
assertEquals( |
| ... | ... | |
| 263 | 310 |
//# Parametros:: [] |
| 264 | 311 |
|
| 265 | 312 |
assertEquals( |
| 266 |
"SELECT * FROM \"test1\" LIMIT 1", |
|
| 313 |
"SELECT * FROM \"dbo\".\"test1\" LIMIT 1",
|
|
| 267 | 314 |
sqlbuilder.toString() |
| 268 | 315 |
); |
| 269 | 316 |
assertEquals( |
| ... | ... | |
| 301 | 348 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 302 | 349 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 303 | 350 |
assertEquals( |
| 304 |
"DELETE FROM \"test1\" WHERE (( (\"id1\") = (?) ) AND ( (\"id2\") = (?) ))", |
|
| 351 |
"DELETE FROM \"dbo\".\"test1\" WHERE (( (\"id1\") = (?) ) AND ( (\"id2\") = (?) ))",
|
|
| 305 | 352 |
sqlbuilder.toString() |
| 306 | 353 |
); |
| 307 | 354 |
assertEquals( |
| ... | ... | |
| 330 | 377 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 331 | 378 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 332 | 379 |
assertEquals( |
| 333 |
"INSERT INTO \"test1\" ( \"id\", \"name\", \"geom\" ) VALUES ( ?, ?, ST_GeomFromWKB((?), (4326)) )", |
|
| 380 |
"INSERT INTO \"dbo\".\"test1\" ( \"id\", \"name\", \"geom\" ) VALUES ( ?, ?, ST_GeomFromWKB((?), (4326)) )",
|
|
| 334 | 381 |
sqlbuilder.toString() |
| 335 | 382 |
); |
| 336 | 383 |
assertEquals( |
| ... | ... | |
| 359 | 406 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 360 | 407 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 361 | 408 |
assertEquals( |
| 362 |
"INSERT INTO \"test1\" ( \"id\", \"name\", \"geom\" ) VALUES ( ?, ?, ST_GeomFromWKB((?), (4326)) )", |
|
| 409 |
"INSERT INTO \"dbo\".\"test1\" ( \"id\", \"name\", \"geom\" ) VALUES ( ?, ?, ST_GeomFromWKB((?), (4326)) )",
|
|
| 363 | 410 |
sqlbuilder.toString() |
| 364 | 411 |
); |
| 365 | 412 |
assertEquals( |
| ... | ... | |
| 395 | 442 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 396 | 443 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 397 | 444 |
assertEquals( |
| 398 |
"UPDATE \"test1\" SET \"name\" = ?, \"geom\" = ST_GeomFromWKB((?), (4326)) WHERE ( (\"id\") = (?) )", |
|
| 445 |
"UPDATE \"dbo\".\"test1\" SET \"name\" = ?, \"geom\" = ST_GeomFromWKB((?), (4326)) WHERE ( (\"id\") = (?) )",
|
|
| 399 | 446 |
sqlbuilder.toString() |
| 400 | 447 |
); |
| 401 | 448 |
assertEquals( |
| ... | ... | |
| 432 | 479 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 433 | 480 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 434 | 481 |
assertEquals( |
| 435 |
"UPDATE \"test1\" SET \"name\" = ?, \"geom\" = ST_GeomFromWKB((?), (?)) WHERE ( (\"id\") = (?) )", |
|
| 482 |
"UPDATE \"dbo\".\"test1\" SET \"name\" = ?, \"geom\" = ST_GeomFromWKB((?), (?)) WHERE ( (\"id\") = (?) )",
|
|
| 436 | 483 |
sqlbuilder.toString() |
| 437 | 484 |
); |
| 438 | 485 |
assertEquals( |
Also available in: Unified diff