Revision 43687 trunk/org.gvsig.desktop/org.gvsig.desktop.compat.cdc/org.gvsig.fmap.dal/org.gvsig.fmap.dal.spi/src/main/java/org/gvsig/fmap/dal/feature/spi/SQLBuilderBase.java
SQLBuilderBase.java | ||
---|---|---|
49 | 49 |
protected GrantBuilder grant; |
50 | 50 |
protected DropTableBuilder drop_table; |
51 | 51 |
protected UpdateTableStatisticsBuilder update_table_statistics; |
52 |
protected CreateIndexBuilder create_index; |
|
52 | 53 |
protected List<Parameter> parameters; |
53 | 54 |
|
54 | 55 |
protected class ColumnDescriptorBuilderBase implements ColumnDescriptorBuilder { |
... | ... | |
63 | 64 |
private Object defaultValue; |
64 | 65 |
private int geom_type; |
65 | 66 |
private int geom_subtype; |
66 |
private int geom_srsid;
|
|
67 |
private Object geom_srsdbcode;
|
|
67 | 68 |
private boolean _isIndexed; |
68 | 69 |
|
69 | 70 |
public ColumnDescriptorBuilderBase(String name, int type, Object defaultValue) { |
... | ... | |
77 | 78 |
this.defaultValue = defaultValue; |
78 | 79 |
this.geom_type = Geometry.TYPES.GEOMETRY; |
79 | 80 |
this.geom_subtype = Geometry.SUBTYPES.GEOM2D; |
80 |
this.geom_srsid = -1;
|
|
81 |
this.geom_srsdbcode = null;
|
|
81 | 82 |
this._isIndexed = false; |
82 | 83 |
} |
83 | 84 |
|
... | ... | |
92 | 93 |
this.defaultValue = defaultValue; |
93 | 94 |
this.geom_type = Geometry.TYPES.GEOMETRY; |
94 | 95 |
this.geom_subtype = Geometry.SUBTYPES.GEOM2D; |
95 |
this.geom_srsid = -1;
|
|
96 |
this.geom_srsdbcode = null;
|
|
96 | 97 |
this._isIndexed = isIndexed; |
97 | 98 |
} |
98 | 99 |
|
... | ... | |
107 | 108 |
this.defaultValue = null; |
108 | 109 |
this.geom_type = geom_type; |
109 | 110 |
this.geom_subtype = geom_subtype; |
110 |
this.geom_srsid = getSRSId(proj);
|
|
111 |
this.geom_srsdbcode = getSRSId(proj);
|
|
111 | 112 |
this._isIndexed = isIndexed; |
112 | 113 |
} |
113 | 114 |
|
115 |
public ColumnDescriptorBuilderBase(String name, int geom_type, int geom_subtype, Object srsdbcode, boolean isIndexed, boolean allowNulls) { |
|
116 |
this.name = name; |
|
117 |
this.type = DataTypes.GEOMETRY; |
|
118 |
this.type_p = 0; |
|
119 |
this.type_s = 0; |
|
120 |
this.isPk = false; |
|
121 |
this._allowNulls = allowNulls; |
|
122 |
this._isAutomatic = false; |
|
123 |
this.defaultValue = null; |
|
124 |
this.geom_type = geom_type; |
|
125 |
this.geom_subtype = geom_subtype; |
|
126 |
this.geom_srsdbcode = srsdbcode; |
|
127 |
this._isIndexed = isIndexed; |
|
128 |
} |
|
129 |
|
|
114 | 130 |
@Override |
115 | 131 |
public String getName() { |
116 | 132 |
return this.name; |
... | ... | |
217 | 233 |
} |
218 | 234 |
|
219 | 235 |
@Override |
220 |
public int getGeometrySRSId() {
|
|
221 |
return geom_srsid;
|
|
236 |
public Object getGeometrySRSId() {
|
|
237 |
return geom_srsdbcode;
|
|
222 | 238 |
} |
223 | 239 |
|
224 | 240 |
@Override |
225 |
public void setGeometrySRSId(int geom_srsid) {
|
|
226 |
this.geom_srsid = geom_srsid;
|
|
241 |
public void setGeometrySRSId(Object geom_srsid) {
|
|
242 |
this.geom_srsdbcode = geom_srsid;
|
|
227 | 243 |
} |
228 | 244 |
|
229 | 245 |
@Override |
... | ... | |
1192 | 1208 |
} |
1193 | 1209 |
} |
1194 | 1210 |
|
1211 |
public class CreateIndexBuilderBase implements CreateIndexBuilder { |
|
1212 |
|
|
1213 |
protected boolean ifNotExist = false; |
|
1214 |
protected boolean isUnique = false; |
|
1215 |
protected String indexName; |
|
1216 |
protected boolean isSpatial = false; |
|
1217 |
protected TableNameBuilder table; |
|
1218 |
protected final List<String> columns; |
|
1219 |
|
|
1220 |
public CreateIndexBuilderBase() { |
|
1221 |
this.columns = new ArrayList<>(); |
|
1222 |
} |
|
1223 |
|
|
1224 |
@Override |
|
1225 |
public CreateIndexBuilder unique() { |
|
1226 |
this.isUnique = true; |
|
1227 |
return this; |
|
1228 |
} |
|
1229 |
|
|
1230 |
@Override |
|
1231 |
public CreateIndexBuilder if_not_exist() { |
|
1232 |
this.ifNotExist = true; |
|
1233 |
return this; |
|
1234 |
} |
|
1235 |
|
|
1236 |
@Override |
|
1237 |
public CreateIndexBuilder name(String name) { |
|
1238 |
this.indexName = name; |
|
1239 |
return this; |
|
1240 |
} |
|
1241 |
|
|
1242 |
@Override |
|
1243 |
public CreateIndexBuilder spatial() { |
|
1244 |
this.isSpatial = true; |
|
1245 |
return this; |
|
1246 |
} |
|
1247 |
|
|
1248 |
@Override |
|
1249 |
public CreateIndexBuilder column(String name) { |
|
1250 |
this.columns.add(name); |
|
1251 |
return this; |
|
1252 |
} |
|
1253 |
|
|
1254 |
@Override |
|
1255 |
public TableNameBuilder table() { |
|
1256 |
if( table == null ) { |
|
1257 |
table = createTableNameBuilder(); |
|
1258 |
} |
|
1259 |
return table; |
|
1260 |
} |
|
1261 |
|
|
1262 |
@Override |
|
1263 |
public void accept(Visitor visitor, VisitorFilter filter) { |
|
1264 |
if( filter.accept(this) ) { |
|
1265 |
visitor.visit(this); |
|
1266 |
} |
|
1267 |
if( this.table != null ) { |
|
1268 |
this.table.accept(visitor, filter); |
|
1269 |
} |
|
1270 |
} |
|
1271 |
|
|
1272 |
@Override |
|
1273 |
public List<String> toStrings() { |
|
1274 |
StringBuilder builder = new StringBuilder(); |
|
1275 |
builder.append("CREATE "); |
|
1276 |
if( this.isUnique ) { |
|
1277 |
builder.append("UNIQUE "); |
|
1278 |
} |
|
1279 |
builder.append("INDEX "); |
|
1280 |
if( this.ifNotExist ) { |
|
1281 |
builder.append("IF NOT EXISTS "); |
|
1282 |
} |
|
1283 |
builder.append(identifier(this.indexName)); |
|
1284 |
builder.append(" ON "); |
|
1285 |
builder.append(this.table.toString()); |
|
1286 |
if( this.isSpatial ) { |
|
1287 |
builder.append(" USING GIST "); |
|
1288 |
} |
|
1289 |
builder.append(" ( "); |
|
1290 |
boolean is_first_column = true; |
|
1291 |
for( String column : this.columns) { |
|
1292 |
if( is_first_column ) { |
|
1293 |
is_first_column = false; |
|
1294 |
} else { |
|
1295 |
builder.append(", "); |
|
1296 |
} |
|
1297 |
builder.append(column); |
|
1298 |
} |
|
1299 |
builder.append(" )"); |
|
1300 |
|
|
1301 |
List<String> sqls = new ArrayList<>(); |
|
1302 |
sqls.add(builder.toString()); |
|
1303 |
return sqls; |
|
1304 |
} |
|
1305 |
|
|
1306 |
} |
|
1307 |
|
|
1195 | 1308 |
public class AlterTableBuilderBase implements AlterTableBuilder { |
1196 | 1309 |
|
1197 | 1310 |
protected TableNameBuilder table; |
... | ... | |
1208 | 1321 |
} |
1209 | 1322 |
|
1210 | 1323 |
@Override |
1324 |
public boolean isEmpty() { |
|
1325 |
return this.drops.isEmpty() && |
|
1326 |
this.adds.isEmpty() && |
|
1327 |
this.alters.isEmpty() && |
|
1328 |
this.renames.isEmpty(); |
|
1329 |
} |
|
1330 |
|
|
1331 |
@Override |
|
1211 | 1332 |
public void accept(Visitor visitor, VisitorFilter filter) { |
1212 | 1333 |
if( filter.accept(this) ) { |
1213 | 1334 |
visitor.visit(this); |
... | ... | |
1250 | 1371 |
} |
1251 | 1372 |
|
1252 | 1373 |
@Override |
1374 |
public AlterTableBuilder add_geometry_column(String columnName, int type, int subtype, Object srsdbcode, boolean isIndexed, boolean allowNulls) { |
|
1375 |
if( StringUtils.isEmpty(columnName) ) { |
|
1376 |
throw new IllegalArgumentException("Argument 'columnName' can't be empty."); |
|
1377 |
} |
|
1378 |
this.adds.add(new ColumnDescriptorBuilderBase(columnName, type, subtype, srsdbcode, isIndexed, allowNulls)); |
|
1379 |
return this; |
|
1380 |
} |
|
1381 |
|
|
1382 |
@Override |
|
1253 | 1383 |
public AlterTableBuilder alter_column(String columnName, int type, int type_p, int type_s, boolean isPk, boolean isIndexed, boolean allowNulls, boolean isAutomatic, Object defaultValue) { |
1254 | 1384 |
if (isPk || isAutomatic) { |
1255 | 1385 |
allowNulls = false; |
... | ... | |
1268 | 1398 |
} |
1269 | 1399 |
|
1270 | 1400 |
@Override |
1401 |
public AlterTableBuilder alter_geometry_column(String columnName, int type, int subtype, Object srsdbcode, boolean isIndexed, boolean allowNulls) { |
|
1402 |
if( StringUtils.isEmpty(columnName) ) { |
|
1403 |
throw new IllegalArgumentException("Argument 'columnName' can't be empty."); |
|
1404 |
} |
|
1405 |
this.alters.add(new ColumnDescriptorBuilderBase(columnName, type, subtype, srsdbcode, isIndexed, allowNulls)); |
|
1406 |
return this; |
|
1407 |
} |
|
1408 |
|
|
1409 |
@Override |
|
1271 | 1410 |
public AlterTableBuilder rename_column(String source, String target) { |
1272 | 1411 |
this.renames.add(new ImmutablePair(source, target)); |
1273 | 1412 |
return this; |
... | ... | |
1294 | 1433 |
@Override |
1295 | 1434 |
public List<String> toStrings() { |
1296 | 1435 |
List<String> sqls = new ArrayList<>(); |
1297 |
/* |
|
1298 |
ALTER TABLE [ ONLY ] name [ * ] |
|
1299 |
action [, ... ] |
|
1300 |
ALTER TABLE [ ONLY ] name [ * ] |
|
1301 |
RENAME [ COLUMN ] column TO new_column |
|
1302 |
ALTER TABLE name |
|
1303 |
RENAME TO new_name |
|
1304 |
ALTER TABLE name |
|
1305 |
SET SCHEMA new_schema |
|
1306 |
|
|
1307 |
where action is one of: |
|
1308 |
|
|
1309 |
ADD [ COLUMN ] column data_type [ COLLATE collation ] [ column_constraint [ ... ] ] |
|
1310 |
DROP [ COLUMN ] [ IF EXISTS ] column [ RESTRICT | CASCADE ] |
|
1311 |
ALTER [ COLUMN ] column [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ] |
|
1312 |
ALTER [ COLUMN ] column SET DEFAULT expression |
|
1313 |
ALTER [ COLUMN ] column DROP DEFAULT |
|
1314 |
ALTER [ COLUMN ] column { SET | DROP } NOT NULL |
|
1315 |
ALTER [ COLUMN ] column SET STATISTICS integer |
|
1316 |
ALTER [ COLUMN ] column SET ( attribute_option = value [, ... ] ) |
|
1317 |
ALTER [ COLUMN ] column RESET ( attribute_option [, ... ] ) |
|
1318 |
ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } |
|
1319 |
ADD table_constraint [ NOT VALID ] |
|
1320 |
ADD table_constraint_using_index |
|
1321 |
VALIDATE CONSTRAINT constraint_name |
|
1322 |
DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ] |
|
1323 |
DISABLE TRIGGER [ trigger_name | ALL | USER ] |
|
1324 |
ENABLE TRIGGER [ trigger_name | ALL | USER ] |
|
1325 |
ENABLE REPLICA TRIGGER trigger_name |
|
1326 |
ENABLE ALWAYS TRIGGER trigger_name |
|
1327 |
DISABLE RULE rewrite_rule_name |
|
1328 |
ENABLE RULE rewrite_rule_name |
|
1329 |
ENABLE REPLICA RULE rewrite_rule_name |
|
1330 |
ENABLE ALWAYS RULE rewrite_rule_name |
|
1331 |
CLUSTER ON index_name |
|
1332 |
SET WITHOUT CLUSTER |
|
1333 |
SET WITH OIDS |
|
1334 |
SET WITHOUT OIDS |
|
1335 |
SET ( storage_parameter = value [, ... ] ) |
|
1336 |
RESET ( storage_parameter [, ... ] ) |
|
1337 |
INHERIT parent_table |
|
1338 |
NO INHERIT parent_table |
|
1339 |
OF type_name |
|
1340 |
NOT OF |
|
1341 |
OWNER TO new_owner |
|
1342 |
SET TABLESPACE new_tablespace |
|
1343 |
|
|
1344 |
and table_constraint_using_index is: |
|
1345 |
|
|
1346 |
[ CONSTRAINT constraint_name ] |
|
1347 |
{ UNIQUE | PRIMARY KEY } USING INDEX index_name |
|
1348 |
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] |
|
1349 |
|
|
1350 |
*/ |
|
1351 |
|
|
1436 |
if( this.isEmpty() ) { |
|
1437 |
return sqls; |
|
1438 |
} |
|
1352 | 1439 |
for (String column : drops) { |
1353 | 1440 |
StringBuilder builder = new StringBuilder(); |
1354 | 1441 |
builder.append("ALTER TABLE "); |
... | ... | |
1367 | 1454 |
if( column.getType() == DataTypes.INT && column.isAutomatic() ) { |
1368 | 1455 |
builder.append(" SERIAL"); |
1369 | 1456 |
} else { |
1370 |
builder.append(sqltype(column.getType(), column.getPrecision(), column.getSize())); |
|
1457 |
builder.append( |
|
1458 |
sqltype( |
|
1459 |
column.getType(), |
|
1460 |
column.getPrecision(), |
|
1461 |
column.getSize(), |
|
1462 |
column.getGeometryType(), |
|
1463 |
column.getGeometrySubtype() |
|
1464 |
) |
|
1465 |
); |
|
1371 | 1466 |
} |
1372 | 1467 |
if (column.getDefaultValue() == null) { |
1373 | 1468 |
if (column.allowNulls()) { |
... | ... | |
1387 | 1482 |
builder.append(" PRIMARY KEY"); |
1388 | 1483 |
} |
1389 | 1484 |
sqls.add(builder.toString()); |
1390 |
if( column.isIndexed() ) { |
|
1391 |
String sql; |
|
1392 |
String name = "idx_" + this.table().getName() + "_" + column.getName(); |
|
1393 |
if( column.isGeometry() ) { |
|
1394 |
sql = MessageFormat.format( |
|
1395 |
config.getString(SQLConfig.CREATE_INDEX_name_ON_table_USING_GIST_column), |
|
1396 |
name, |
|
1397 |
this.table().toString(), |
|
1398 |
column.getName() |
|
1399 |
); |
|
1400 |
} else { |
|
1401 |
sql = MessageFormat.format( |
|
1402 |
config.getString(SQLConfig.CREATE_INDEX_name_ON_table_column), |
|
1403 |
name, |
|
1404 |
this.table().toString(), |
|
1405 |
column.getName() |
|
1406 |
); |
|
1407 |
} |
|
1408 |
sqls.add(sql); |
|
1409 |
} |
|
1410 | 1485 |
} |
1411 | 1486 |
for (ColumnDescriptorBuilderBase column : alters) { |
1412 | 1487 |
StringBuilder builder = new StringBuilder(); |
... | ... | |
1418 | 1493 |
if( column.getType() == DataTypes.INT && column.isAutomatic() ) { |
1419 | 1494 |
builder.append(" SERIAL"); |
1420 | 1495 |
} else { |
1421 |
builder.append(sqltype(column.getType(), column.getPrecision(), column.getSize())); |
|
1496 |
builder.append( |
|
1497 |
sqltype( |
|
1498 |
column.getType(), |
|
1499 |
column.getPrecision(), |
|
1500 |
column.getSize(), |
|
1501 |
column.getGeometryType(), |
|
1502 |
column.getGeometrySubtype() |
|
1503 |
) |
|
1504 |
); |
|
1422 | 1505 |
} |
1423 | 1506 |
if (column.getDefaultValue() == null) { |
1424 | 1507 |
if (column.allowNulls()) { |
... | ... | |
1432 | 1515 |
builder.append("'"); |
1433 | 1516 |
} |
1434 | 1517 |
sqls.add(builder.toString()); |
1435 |
if( column.isIndexed() ) { |
|
1436 |
String sql; |
|
1437 |
String name = "idx_" + this.table().getName() + "_" + column.getName(); |
|
1438 |
if( column.isGeometry() ) { |
|
1439 |
sql = MessageFormat.format( |
|
1440 |
config.getString(SQLConfig.CREATE_INDEX_name_ON_table_USING_GIST_column), |
|
1441 |
name, |
|
1442 |
this.table().toString(), |
|
1443 |
column.getName() |
|
1444 |
); |
|
1445 |
} else { |
|
1446 |
sql = MessageFormat.format( |
|
1447 |
config.getString(SQLConfig.CREATE_INDEX_name_ON_table_column), |
|
1448 |
name, |
|
1449 |
this.table().toString(), |
|
1450 |
column.getName() |
|
1451 |
); |
|
1452 |
} |
|
1453 |
sqls.add(sql); |
|
1454 |
} |
|
1455 | 1518 |
} |
1456 | 1519 |
for (Pair<String,String> pair : renames) { |
1457 | 1520 |
StringBuilder builder = new StringBuilder(); |
... | ... | |
1517 | 1580 |
} |
1518 | 1581 |
|
1519 | 1582 |
@Override |
1583 |
public CreateTableBuilder add_geometry_column(String columnName, int type, int subtype, Object srsdbcode, boolean isIndexed, boolean allowNulls) { |
|
1584 |
if( StringUtils.isEmpty(columnName) ) { |
|
1585 |
throw new IllegalArgumentException("Argument 'columnName' can't be empty."); |
|
1586 |
} |
|
1587 |
this.columns.add(new ColumnDescriptorBuilderBase(columnName, type, subtype, srsdbcode, isIndexed, allowNulls)); |
|
1588 |
return this; |
|
1589 |
} |
|
1590 |
|
|
1591 |
@Override |
|
1520 | 1592 |
public ColumnDescriptorBuilder getColumnDescriptor(String columnName) { |
1521 | 1593 |
if( StringUtils.isEmpty(columnName) ) { |
1522 | 1594 |
return null; |
... | ... | |
1616 | 1688 |
} |
1617 | 1689 |
builder.append(" )"); |
1618 | 1690 |
sqls.add(builder.toString()); |
1619 |
for (ColumnDescriptorBuilderBase column : columns) { |
|
1620 |
if( column.isIndexed() ) { |
|
1621 |
String sql; |
|
1622 |
String name = "idx_" + this.table().getName() + "_" + column.getName(); |
|
1623 |
if( column.isGeometry() ) { |
|
1624 |
sql = MessageFormat.format( |
|
1625 |
config.getString(SQLConfig.CREATE_INDEX_name_ON_table_USING_GIST_column), |
|
1626 |
name, |
|
1627 |
this.table().toString(), |
|
1628 |
column.getName() |
|
1629 |
); |
|
1630 |
} else { |
|
1631 |
sql = MessageFormat.format( |
|
1632 |
config.getString(SQLConfig.CREATE_INDEX_name_ON_table_column), |
|
1633 |
name, |
|
1634 |
this.table().toString(), |
|
1635 |
column.getName() |
|
1636 |
); |
|
1637 |
} |
|
1638 |
sqls.add(sql); |
|
1639 |
} |
|
1640 |
} |
|
1641 | 1691 |
return sqls; |
1642 | 1692 |
} |
1643 | 1693 |
} |
... | ... | |
1867 | 1917 |
config.set(SQLConfig.UPDATE_table_SET_columnsAndValues_WHERE_expresion, "UPDATE {0} SET {1} WHERE {2}"); |
1868 | 1918 |
config.set(SQLConfig.UPDATE_table_SET_columnsAndValues, "UPDATE {0} SET {1}"); |
1869 | 1919 |
config.set(SQLConfig.GRANT_privileges_ON_table_TO_role, "GRANT {0} ON {1} TO {2}"); |
1870 |
config.set(SQLConfig.CREATE_INDEX_name_ON_table_column, "CREATE INDEX {0} ON {1} ({2})"); |
|
1871 |
config.set(SQLConfig.CREATE_INDEX_name_ON_table_USING_GIST_column, "CREATE INDEX {0} ON {1} USING GIST ({2})"); |
|
1872 | 1920 |
} |
1873 | 1921 |
|
1874 | 1922 |
@Override |
... | ... | |
2096 | 2144 |
return new UpdateTableStatisticsBuilderBase(); |
2097 | 2145 |
} |
2098 | 2146 |
|
2147 |
protected CreateIndexBuilder createCreateIndexBuilder() { |
|
2148 |
return new CreateIndexBuilderBase(); |
|
2149 |
} |
|
2150 |
|
|
2099 | 2151 |
@Override |
2100 | 2152 |
public SelectBuilder select() { |
2101 | 2153 |
if (this.select == null) { |
... | ... | |
2129 | 2181 |
} |
2130 | 2182 |
|
2131 | 2183 |
@Override |
2184 |
public CreateIndexBuilder create_index() { |
|
2185 |
if (this.create_index == null) { |
|
2186 |
this.create_index = this.createCreateIndexBuilder(); |
|
2187 |
} |
|
2188 |
return this.create_index; |
|
2189 |
} |
|
2190 |
|
|
2191 |
|
|
2192 |
@Override |
|
2132 | 2193 |
public DeleteBuilder delete() { |
2133 | 2194 |
if (this.delete == null) { |
2134 | 2195 |
this.delete = this.createDeleteBuilder(); |
Also available in: Unified diff