Revision 47606 trunk/org.gvsig.desktop/org.gvsig.desktop.plugin/org.gvsig.sqlite/org.gvsig.sqlite.provider/src/test/java/org/gvsig/sqlite/dal/SQLiteSQLBuilderTest.java
| SQLiteSQLBuilderTest.java | ||
|---|---|---|
| 1 | 1 |
package org.gvsig.sqlite.dal; |
| 2 | 2 |
|
| 3 | 3 |
import junit.framework.TestCase; |
| 4 |
import org.apache.commons.io.FilenameUtils; |
|
| 4 | 5 |
import org.apache.commons.lang3.ArrayUtils; |
| 5 | 6 |
import org.cresques.cts.IProjection; |
| 6 | 7 |
import org.gvsig.expressionevaluator.ExpressionBuilder; |
| 7 | 8 |
import org.gvsig.expressionevaluator.GeometryExpressionBuilder; |
| 8 | 9 |
import org.gvsig.fmap.crs.CRSFactory; |
| 10 |
import org.gvsig.fmap.dal.DALLocator; |
|
| 9 | 11 |
import org.gvsig.fmap.dal.SQLBuilder; |
| 12 |
import static org.gvsig.fmap.dal.SQLBuilder.PROP_FEATURE_TYPE; |
|
| 13 |
import static org.gvsig.fmap.dal.SQLBuilder.PROP_TABLE; |
|
| 14 |
import static org.gvsig.fmap.dal.SQLBuilder.PROP_TABLENAME; |
|
| 10 | 15 |
import org.gvsig.fmap.dal.SQLBuilder.Privilege; |
| 16 |
import org.gvsig.fmap.dal.feature.EditableFeatureType; |
|
| 11 | 17 |
import org.gvsig.fmap.dal.feature.spi.SQLBuilderBase; |
| 12 | 18 |
import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference; |
| 13 | 19 |
import org.gvsig.fmap.dal.store.jdbc2.spi.operations.OperationsFactoryBase; |
| ... | ... | |
| 16 | 22 |
import org.gvsig.fmap.geom.GeometryLocator; |
| 17 | 23 |
import org.gvsig.fmap.geom.GeometryManager; |
| 18 | 24 |
import org.gvsig.fmap.geom.primitive.Polygon; |
| 25 |
import org.gvsig.sqlite.dal.geopackage.GeopackageUtils; |
|
| 26 |
import org.gvsig.sqlite.dal.geopackage.index.GeopackageIndexRTree; |
|
| 19 | 27 |
import org.gvsig.tools.library.impl.DefaultLibrariesInitializer; |
| 20 | 28 |
|
| 21 | 29 |
public class SQLiteSQLBuilderTest extends TestCase {
|
| ... | ... | |
| 40 | 48 |
return new SQLiteSQLBuilder(helper); |
| 41 | 49 |
} |
| 42 | 50 |
|
| 43 |
public void testCalulateEnvelopeOfColumn() throws Exception {
|
|
| 51 |
public void testCalculateEnvelopeOfColumn() throws Exception {
|
|
| 44 | 52 |
|
| 45 | 53 |
TableReference table = new OperationsFactoryBase.DefaultTableReference("master","dbo","test1", null);
|
| 46 | 54 |
String columnName = "the_geom"; |
| ... | ... | |
| 65 | 73 |
sqlbuilder.select().where().and( |
| 66 | 74 |
expbuilder.not_is_null(expbuilder.column(columnName)) |
| 67 | 75 |
); |
| 68 |
|
|
| 69 |
System.out.println("# Test:: testCalulateEnvelopeOfColumn");
|
|
| 76 |
String exp = "SELECT ( ST_Extent(\"the_geom\") ) FROM \"test1\" WHERE ( (\"the_geom\") IS NOT NULL )"; |
|
| 77 |
|
|
| 78 |
System.out.println("# Test:: testCalculateEnvelopeOfColumn");
|
|
| 70 | 79 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
| 80 |
System.out.println("# EXP:: " + exp);
|
|
| 71 | 81 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 72 | 82 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 73 |
assertEquals( |
|
| 74 |
"SELECT NVL2(ST_Extent(\"the_geom\"),ST_AsBinary(ST_Extent(\"the_geom\")),NULL) FROM \"dbo\".\"test1\" WHERE ( (\"the_geom\") IS NOT NULL )",
|
|
| 83 |
assertEquals(
|
|
| 84 |
exp,
|
|
| 75 | 85 |
sqlbuilder.toString() |
| 76 | 86 |
); |
| 77 | 87 |
assertEquals( |
| ... | ... | |
| 84 | 94 |
); |
| 85 | 95 |
} |
| 86 | 96 |
|
| 87 |
public void testCalulateEnvelope() throws Exception {
|
|
| 97 |
public void testCalculateEnvelope() throws Exception {
|
|
| 98 |
try {
|
|
| 88 | 99 |
GeometryManager geometryManager = GeometryLocator.getGeometryManager(); |
| 89 | 100 |
IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
| 90 | 101 |
|
| ... | ... | |
| 101 | 112 |
sqlbuilder.select().column().value( |
| 102 | 113 |
expbuilder.as_geometry( |
| 103 | 114 |
expbuilder.ST_ExtentAggregate( |
| 104 |
expbuilder.column("the_geom")
|
|
| 115 |
expbuilder.column("Geometry")
|
|
| 105 | 116 |
) |
| 106 | 117 |
) |
| 107 | 118 |
).as("envelope");
|
| 108 |
sqlbuilder.select().from().table().database("master").schema("dbo").name("test1");
|
|
| 119 |
sqlbuilder.select().from().table().database("master").schema("dbo").name("test");
|
|
| 109 | 120 |
sqlbuilder.select().where().set( |
| 110 | 121 |
expbuilder.ST_Intersects( |
| 111 | 122 |
expbuilder.ST_Envelope( |
| 112 |
expbuilder.column("the_geom")
|
|
| 123 |
expbuilder.column("Geometry")
|
|
| 113 | 124 |
), |
| 114 | 125 |
expbuilder.geometry(limit, proj) |
| 115 | 126 |
) |
| 116 | 127 |
); |
| 117 | 128 |
sqlbuilder.select().where().and( |
| 118 |
expbuilder.custom("x = 27")
|
|
| 129 |
expbuilder.custom("Long = 27")
|
|
| 119 | 130 |
); |
| 120 |
|
|
| 131 |
String exp = "SELECT ( ST_Extent(\"Geometry\") ) AS \"envelope\" FROM \"test\" WHERE (ST_Intersects((ST_Envelope(\"Geometry\")),((x'010300000001000000050000000000000000000000000000000000000000000000000000000000000000005940000000000000594000000000000059400000000000005940000000000000000000000000000000000000000000000000'))) AND Long = 27)"; |
|
| 121 | 132 |
System.out.println("# Test:: testCalulateEnvelope");
|
| 122 | 133 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
| 134 |
System.out.println("# EXP:: " + exp);
|
|
| 123 | 135 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 124 | 136 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 125 | 137 |
assertEquals( |
| 126 |
"SELECT NVL2(ST_Extent(\"the_geom\"),ST_AsBinary(ST_Extent(\"the_geom\")),NULL) 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)",
|
|
| 138 |
exp,
|
|
| 127 | 139 |
sqlbuilder.toString() |
| 128 | 140 |
); |
| 129 | 141 |
assertEquals( |
| 130 |
"[the_geom]",
|
|
| 142 |
"[Geometry]",
|
|
| 131 | 143 |
ArrayUtils.toString(sqlbuilder.variables_names()) |
| 132 | 144 |
); |
| 133 | 145 |
assertEquals( |
| 134 | 146 |
"[]", |
| 135 | 147 |
ArrayUtils.toString(sqlbuilder.parameters_names()) |
| 136 | 148 |
); |
| 149 |
} catch (Throwable th) {
|
|
| 150 |
th.printStackTrace(); |
|
| 151 |
throw th; |
|
| 152 |
} |
|
| 137 | 153 |
} |
| 138 | 154 |
|
| 155 |
public void testCalculateEnvelopeWithSpatialIndex() throws Exception {
|
|
| 156 |
try {
|
|
| 157 |
GeometryManager geometryManager = GeometryLocator.getGeometryManager(); |
|
| 158 |
IProjection proj = CRSFactory.getCRS("EPSG:4326");
|
|
| 159 |
|
|
| 160 |
Polygon limit = geometryManager.createPolygon(Geometry.SUBTYPES.GEOM2D); |
|
| 161 |
limit.addVertex(0, 0); |
|
| 162 |
limit.addVertex(0, 100); |
|
| 163 |
limit.addVertex(100, 100); |
|
| 164 |
limit.addVertex(100, 0); |
|
| 165 |
limit.addVertex(0, 0); |
|
| 166 |
|
|
| 167 |
SQLBuilder sqlbuilder = createSQLBuilder(); |
|
| 168 |
GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
|
| 169 |
|
|
| 170 |
sqlbuilder.select().column().value( |
|
| 171 |
expbuilder.as_geometry( |
|
| 172 |
expbuilder.ST_ExtentAggregate( |
|
| 173 |
expbuilder.column("Geometry")
|
|
| 174 |
) |
|
| 175 |
) |
|
| 176 |
).as("envelope");
|
|
| 177 |
sqlbuilder.select().from().table().database("master").schema("dbo").name("test");
|
|
| 178 |
sqlbuilder.select().where().set( |
|
| 179 |
expbuilder.ST_Intersects( |
|
| 180 |
expbuilder.column("Geometry"),
|
|
| 181 |
expbuilder.geometry(limit, proj) |
|
| 182 |
) |
|
| 183 |
); |
|
| 184 |
sqlbuilder.select().where().and( |
|
| 185 |
expbuilder.custom("Long = 27")
|
|
| 186 |
); |
|
| 187 |
String exp = "SELECT ( ST_Extent(\"Geometry\") ) AS \"envelope\" FROM \"test\" WHERE (( ((\"Long\" IN (SELECT \"rtree_test_Geometry\".\"id\" FROM \"rtree_test_Geometry\" WHERE \"rtree_test_Geometry\".\"minx\" <= (100.0) AND \"rtree_test_Geometry\".\"miny\" <= (100.0) AND \"rtree_test_Geometry\".\"maxx\" >= (0.0) AND \"rtree_test_Geometry\".\"maxy\" >= (0.0)))) AND ST_Intersects((\"Geometry\"),((x'010300000001000000050000000000000000000000000000000000000000000000000000000000000000005940000000000000594000000000000059400000000000005940000000000000000000000000000000000000000000000000')) )) AND Long = 27)"; |
|
| 188 |
EditableFeatureType ft = DALLocator.getDataManager().createFeatureType(); |
|
| 189 |
ft.add("Geometry", DataTypes.GEOMETRY)
|
|
| 190 |
.setGeometryType(Geometry.TYPES.POLYGON, Geometry.SUBTYPES.GEOM2D) |
|
| 191 |
.setSRS(proj) |
|
| 192 |
.setIsIndexed(true); |
|
| 193 |
ft.add("Long",DataTypes.INTEGER).setIsPrimaryKey(true);
|
|
| 194 |
GeopackageIndexRTree index = new GeopackageIndexRTree(); |
|
| 195 |
index.setHasIndex(ft.getDefaultGeometryAttribute(), true); |
|
| 196 |
|
|
| 197 |
sqlbuilder.setProperties( |
|
| 198 |
sqlbuilder.select(), |
|
| 199 |
null, |
|
| 200 |
PROP_FEATURE_TYPE, ft.getNotEditableCopy(), |
|
| 201 |
PROP_TABLENAME, "test" |
|
| 202 |
); |
|
| 203 |
|
|
| 204 |
System.out.println("# Test:: testCalculateEnvelopeWithSpatialIndex");
|
|
| 205 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
|
| 206 |
System.out.println("# EXP:: " + exp);
|
|
| 207 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
|
| 208 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
|
| 209 |
assertEquals( |
|
| 210 |
exp, |
|
| 211 |
sqlbuilder.toString() |
|
| 212 |
); |
|
| 213 |
assertEquals( |
|
| 214 |
"[Geometry]", |
|
| 215 |
ArrayUtils.toString(sqlbuilder.variables_names()) |
|
| 216 |
); |
|
| 217 |
assertEquals( |
|
| 218 |
"[]", |
|
| 219 |
ArrayUtils.toString(sqlbuilder.parameters_names()) |
|
| 220 |
); |
|
| 221 |
} catch (Throwable th){
|
|
| 222 |
th.printStackTrace(); |
|
| 223 |
throw th; |
|
| 224 |
} |
|
| 225 |
} |
|
| 226 |
|
|
| 139 | 227 |
public void testCount() throws Exception {
|
| 140 | 228 |
SQLBuilder sqlbuilder = createSQLBuilder(); |
| 141 | 229 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
| ... | ... | |
| 145 | 233 |
sqlbuilder.select().from().subquery(null); |
| 146 | 234 |
sqlbuilder.select().where().set( expbuilder.custom("pp = 200"));
|
| 147 | 235 |
|
| 236 |
String exp = "SELECT COUNT(*) FROM \"test1\" WHERE pp = 200"; |
|
| 148 | 237 |
System.out.println("# Test:: testCount");
|
| 149 | 238 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
| 239 |
System.out.println("# EXP:: " + exp);
|
|
| 150 | 240 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 151 | 241 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 152 | 242 |
|
| ... | ... | |
| 156 | 246 |
//# Parametros:: [] |
| 157 | 247 |
|
| 158 | 248 |
assertEquals( |
| 159 |
"SELECT COUNT(*) FROM \"dbo\".\"test1\" WHERE pp = 200",
|
|
| 249 |
exp,
|
|
| 160 | 250 |
sqlbuilder.toString() |
| 161 | 251 |
); |
| 162 | 252 |
assertEquals( |
| ... | ... | |
| 203 | 293 |
|
| 204 | 294 |
sqlbuilder.create_table().add_geometry_column("geom", Geometry.TYPES.MULTIPOLYGON, Geometry.SUBTYPES.GEOM2D, proj, true, true);
|
| 205 | 295 |
|
| 206 |
|
|
| 296 |
String exp = "CREATE TABLE IF NOT EXISTS gpkg_contents (table_name TEXT NOT NULL PRIMARY KEY,data_type TEXT NOT NULL,identifier TEXT UNIQUE,description TEXT DEFAULT '',last_change DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),min_x DOUBLE, min_y DOUBLE,max_x DOUBLE, max_y DOUBLE,srs_id INTEGER,CONSTRAINT fk_gc_r_srs_id FOREIGN KEY (srs_id) REFERENCES gpkg_spatial_ref_sys(srs_id)); CREATE TABLE IF NOT EXISTS gpkg_extensions ( "
|
|
| 297 |
+ "table_name TEXT, " |
|
| 298 |
+ "column_name TEXT, " |
|
| 299 |
+ "extension_name TEXT NOT NULL, " |
|
| 300 |
+ "definition TEXT NOT NULL, " |
|
| 301 |
+ "scope TEXT NOT NULL, " |
|
| 302 |
+ "CONSTRAINT ge_tce UNIQUE (table_name, column_name, extension_name) ); CREATE TABLE IF NOT EXISTS \"gpkg_geometry_columns\" ( " |
|
| 303 |
+ "\"table_name\" TEXT NOT NULL, " |
|
| 304 |
+ "\"column_name\" TEXT NOT NULL, " |
|
| 305 |
+ "\"geometry_type_name\" TEXT NOT NULL, " |
|
| 306 |
+ "\"srs_id\" INTEGER NOT NULL, " |
|
| 307 |
+ "\"z\" TINYINT NOT NULL, " |
|
| 308 |
+ "\"m\" TINYINT NOT NULL, " |
|
| 309 |
+ "CONSTRAINT \"pk_geom_cols\" PRIMARY KEY(\"table_name\",\"column_name\"), " |
|
| 310 |
+ "CONSTRAINT \"fk_gc_tn\" FOREIGN KEY(\"table_name\") REFERENCES \"gpkg_contents\"(\"table_name\"), " |
|
| 311 |
+ "CONSTRAINT \"uk_gc_table_name\" UNIQUE(\"table_name\"), " |
|
| 312 |
+ "CONSTRAINT \"fk_gc_srs\" FOREIGN KEY(\"srs_id\") REFERENCES \"gpkg_spatial_ref_sys\"(\"srs_id\") " |
|
| 313 |
+ "); INSERT INTO \"gpkg_contents\" (\"table_name\", \"data_type\", \"identifier\", \"description\", \"last_change\", \"min_x\", \"min_y\", \"max_x\", \"max_y\", \"srs_id\") VALUES ('test1', 'features', 'test1', '', '*', NULL, NULL, NULL, NULL, NULL); CREATE TABLE \"test1\" (\"name\" VARCHAR(45) DEFAULT NULL, \"id\" MEDIUMINT PRIMARY KEY AUTOINCREMENT DEFAULT '0' NOT NULL, \"geom\" BLOB DEFAULT NULL ); INSERT INTO \"gpkg_geometry_columns\" (\"table_name\", \"column_name\", \"geometry_type_name\", \"srs_id\", \"z\", \"m\") VALUES ('test1', 'geom', 'MULTIPOLYGON', '4326', '0', '0')";
|
|
| 314 |
|
|
| 207 | 315 |
// CREATE TABLE "test1" ("name" VARCHAR(45) DEFAULT NULL, "id" INTEGER PRIMARY KEY AUTO_INCREMENT DEFAULT '0' NOT NULL, "geom" GEOMETRY ); ALTER TABLE "test1" ADD CONSTRAINT IF NOT EXISTS "constraint_test1_geom_dim" CHECK ST_CoordDim("geom") = 2
|
| 208 | 316 |
System.out.println("# Test:: testCreateTable");
|
| 209 | 317 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
| 318 |
System.out.println("# EXP:: " + exp);
|
|
| 210 | 319 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 211 | 320 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 321 |
assertTrue(FilenameUtils.wildcardMatch(sqlbuilder.toString(), exp)); |
|
| 212 | 322 |
assertEquals( |
| 213 |
"CREATE TABLE \"dbo\".\"test1\" (\"name\" VARCHAR(45) DEFAULT NULL, \"id\" INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL, \"geom\" GEOMETRY(6) CHECK NVL2(\"geom\", ST_GeometryTypeCode(\"geom\") = 6 AND ST_CoordDim(\"geom\") = 2 AND ST_SRID(\"geom\") = 4326, TRUE) )", |
|
| 214 |
sqlbuilder.toString() |
|
| 215 |
); |
|
| 216 |
assertEquals( |
|
| 217 | 323 |
"[]", |
| 218 | 324 |
ArrayUtils.toString(sqlbuilder.variables_names()) |
| 219 | 325 |
); |
| ... | ... | |
| 231 | 337 |
|
| 232 | 338 |
// DROP TABLE "test1" |
| 233 | 339 |
|
| 340 |
String exp = "DELETE FROM \"gpkg_geometry_columns\" WHERE \"table_name\" = 'test1'; DELETE FROM \"gpkg_contents\" WHERE \"identifier\" = 'test1'; DELETE FROM \"gpkg_extensions\" WHERE \"table_name\" = 'test1'; DROP TABLE \"test1\""; |
|
| 234 | 341 |
System.out.println("# Test:: testDropTable");
|
| 235 | 342 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
| 343 |
System.out.println("# EXP:: " + exp);
|
|
| 236 | 344 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 237 | 345 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 238 | 346 |
assertEquals( |
| 239 |
"DROP TABLE \"dbo\".\"test1\"",
|
|
| 347 |
exp,
|
|
| 240 | 348 |
sqlbuilder.toString() |
| 241 | 349 |
); |
| 242 | 350 |
assertEquals( |
| ... | ... | |
| 268 | 376 |
|
| 269 | 377 |
// SELECT "name", "id", ST_AsBinary("geom") FROM "master"."dbo"."test1" WHERE ( ("name") = (?) ) LIMIT 1
|
| 270 | 378 |
|
| 379 |
String exp = "SELECT \"name\", \"id\", ST_AsBinary(\"geom\") FROM \"master\".\"dbo\".\"test1\" WHERE ( (\"name\") = (?) ) LIMIT 1"; |
|
| 271 | 380 |
System.out.println("# Test:: testFetchFeatureProviderByReference");
|
| 272 | 381 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
| 382 |
System.out.println("# EXP:: " + exp);
|
|
| 273 | 383 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 274 | 384 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 275 | 385 |
assertEquals( |
| 276 |
"SELECT \"name\", \"id\", ST_AsBinary(\"geom\") FROM \"master\".\"dbo\".\"test1\" WHERE ( (\"name\") = (?) ) LIMIT 1",
|
|
| 386 |
exp,
|
|
| 277 | 387 |
sqlbuilder.toString() |
| 278 | 388 |
); |
| 279 | 389 |
assertEquals( |
| ... | ... | |
| 294 | 404 |
sqlbuilder.select().from().table().database("master").schema("dbo").name("test1");
|
| 295 | 405 |
sqlbuilder.select().limit(1); |
| 296 | 406 |
|
| 407 |
String exp = "SELECT * FROM \"test1\" LIMIT 1"; |
|
| 408 |
|
|
| 297 | 409 |
System.out.println("# Test:: testFetchFeatureType");
|
| 298 | 410 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
| 411 |
System.out.println("# EXP:: " + exp);
|
|
| 299 | 412 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 300 | 413 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 301 | 414 |
|
| ... | ... | |
| 305 | 418 |
//# Parametros:: [] |
| 306 | 419 |
|
| 307 | 420 |
assertEquals( |
| 308 |
"SELECT * FROM \"dbo\".\"test1\" LIMIT 1",
|
|
| 421 |
exp,
|
|
| 309 | 422 |
sqlbuilder.toString() |
| 310 | 423 |
); |
| 311 | 424 |
assertEquals( |
| ... | ... | |
| 336 | 449 |
) |
| 337 | 450 |
); |
| 338 | 451 |
|
| 339 |
// DELETE FROM "test1" WHERE ( ("id1") = (?) ) AND ( ("id2") = (?) )
|
|
| 452 |
String exp = "DELETE FROM \"test1\" WHERE (( (\"id1\") = (?) ) AND ( (\"id2\") = (?) ))";
|
|
| 340 | 453 |
|
| 341 | 454 |
System.out.println("# Test:: testPerformDeletes");
|
| 342 | 455 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
| 456 |
System.out.println("# EXP:: " + exp);
|
|
| 343 | 457 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 344 | 458 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 345 | 459 |
assertEquals( |
| 346 |
"DELETE FROM \"dbo\".\"test1\" WHERE (( (\"id1\") = (?) ) AND ( (\"id2\") = (?) ))",
|
|
| 460 |
exp,
|
|
| 347 | 461 |
sqlbuilder.toString() |
| 348 | 462 |
); |
| 349 | 463 |
assertEquals( |
| ... | ... | |
| 367 | 481 |
sqlbuilder.insert().column().name("name").with_value(expbuilder.parameter("name"));
|
| 368 | 482 |
sqlbuilder.insert().column().name("geom").with_value(expbuilder.parameter("geom").as_geometry_variable().srs(proj));
|
| 369 | 483 |
|
| 484 |
String exp ="INSERT INTO \"test1\" ( \"id\", \"name\", \"geom\" ) VALUES ( ?, ?, ? )"; |
|
| 370 | 485 |
System.out.println("# Test:: testPerformInserts1");
|
| 371 | 486 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
| 487 |
System.out.println("# EXP:: " + exp);
|
|
| 372 | 488 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 373 | 489 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 374 | 490 |
assertEquals( |
| 375 |
"INSERT INTO \"dbo\".\"test1\" ( \"id\", \"name\", \"geom\" ) VALUES ( ?, ?, ST_GeomFromWKB((?), (4326)) )",
|
|
| 491 |
exp,
|
|
| 376 | 492 |
sqlbuilder.toString() |
| 377 | 493 |
); |
| 378 | 494 |
assertEquals( |
| ... | ... | |
| 395 | 511 |
sqlbuilder.insert().column().name("id").with_value(expbuilder.parameter("id"));
|
| 396 | 512 |
sqlbuilder.insert().column().name("name").with_value(expbuilder.parameter("name"));
|
| 397 | 513 |
sqlbuilder.insert().column().name("geom").with_value(expbuilder.parameter("geom").as_geometry_variable().srs(proj));
|
| 398 |
|
|
| 514 |
|
|
| 515 |
String exp = "INSERT INTO \"test1\" ( \"id\", \"name\", \"geom\" ) VALUES ( ?, ?, ? )"; |
|
| 399 | 516 |
System.out.println("# Test:: testPerformInserts2");
|
| 400 | 517 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
| 518 |
System.out.println("# EXP:: " + exp);
|
|
| 401 | 519 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 402 | 520 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 403 | 521 |
assertEquals( |
| 404 |
"INSERT INTO \"dbo\".\"test1\" ( \"id\", \"name\", \"geom\" ) VALUES ( ?, ?, ST_GeomFromWKB((?), (4326)) )",
|
|
| 522 |
exp,
|
|
| 405 | 523 |
sqlbuilder.toString() |
| 406 | 524 |
); |
| 407 | 525 |
assertEquals( |
| ... | ... | |
| 431 | 549 |
sqlbuilder.update().column().name("geom").with_value(
|
| 432 | 550 |
expbuilder.parameter("geom").as_geometry_variable().srs(proj)
|
| 433 | 551 |
); |
| 552 |
String exp = "UPDATE \"test1\" SET \"name\" = ?, \"geom\" = ? WHERE ( (\"id\") = (?) )"; |
|
| 434 | 553 |
|
| 435 | 554 |
System.out.println("# Test:: testPerformUpdates");
|
| 436 | 555 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
| 556 |
System.out.println("# EXP:: " + exp);
|
|
| 437 | 557 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 438 | 558 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 439 | 559 |
assertEquals( |
| 440 |
"UPDATE \"dbo\".\"test1\" SET \"name\" = ?, \"geom\" = ST_GeomFromWKB((?), (4326)) WHERE ( (\"id\") = (?) )",
|
|
| 560 |
exp,
|
|
| 441 | 561 |
sqlbuilder.toString() |
| 442 | 562 |
); |
| 443 | 563 |
assertEquals( |
| ... | ... | |
| 469 | 589 |
.srs(expbuilder.parameter().value(proj)) |
| 470 | 590 |
); |
| 471 | 591 |
|
| 592 |
String exp = "UPDATE \"test1\" SET \"name\" = ?, \"geom\" = ? WHERE ( (\"id\") = (?) )"; |
|
| 593 |
|
|
| 472 | 594 |
System.out.println("# Test:: testPerformUpdates");
|
| 473 | 595 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
| 596 |
System.out.println("# EXP:: " + exp);
|
|
| 474 | 597 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 475 | 598 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 476 | 599 |
assertEquals( |
| 477 |
"UPDATE \"dbo\".\"test1\" SET \"name\" = ?, \"geom\" = ST_GeomFromWKB((?), (?)) WHERE ( (\"id\") = (?) )",
|
|
| 600 |
exp,
|
|
| 478 | 601 |
sqlbuilder.toString() |
| 479 | 602 |
); |
| 480 | 603 |
assertEquals( |
| ... | ... | |
| 482 | 605 |
ArrayUtils.toString(sqlbuilder.variables_names()) |
| 483 | 606 |
); |
| 484 | 607 |
assertEquals( |
| 485 |
"[\"name\", \"geom\", 4326, \"id\"]",
|
|
| 608 |
"[\"name\", \"geom\", \"id\"]", |
|
| 486 | 609 |
ArrayUtils.toString(sqlbuilder.parameters_names()) |
| 487 | 610 |
); |
| 488 | 611 |
} |
| ... | ... | |
| 496 | 619 |
sqlbuilder.grant().role("prueba").select().insert().update();
|
| 497 | 620 |
sqlbuilder.grant().role("gis").all();
|
| 498 | 621 |
|
| 622 |
String exp = ""; |
|
| 499 | 623 |
|
| 500 | 624 |
System.out.println("# Test:: testGrant1");
|
| 501 | 625 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
| 626 |
System.out.println("# EXP:: " + exp);
|
|
| 502 | 627 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 503 | 628 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 504 | 629 |
assertEquals( |
| 505 |
"",
|
|
| 630 |
exp,
|
|
| 506 | 631 |
sqlbuilder.toString() |
| 507 | 632 |
); |
| 508 | 633 |
assertEquals( |
| ... | ... | |
| 529 | 654 |
|
| 530 | 655 |
System.out.println("# Test:: testGrant2");
|
| 531 | 656 |
System.out.println("# SQL:: " + sqlbuilder.toString());
|
| 657 |
System.out.println("# EXP:: " + "");
|
|
| 532 | 658 |
System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));
|
| 533 | 659 |
System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));
|
| 534 | 660 |
assertEquals( |
Also available in: Unified diff