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

View differences:

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