Revision 134 trunk/org.gvsig.spatialite/org.gvsig.spatialite.provider/src/main/java/org/gvsig/spatialite/dal/SpatiaLiteSQLBuilder.java
| SpatiaLiteSQLBuilder.java | ||
|---|---|---|
| 12 | 12 |
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
| 13 | 13 |
import org.gvsig.fmap.geom.Geometry; |
| 14 | 14 |
import org.gvsig.tools.dispose.Disposable; |
| 15 |
import org.apache.commons.lang3.tuple.Pair; |
|
| 15 | 16 |
|
| 16 | 17 |
public class SpatiaLiteSQLBuilder extends JDBCSQLBuilderBase {
|
| 17 | 18 |
|
| ... | ... | |
| 91 | 92 |
@Override |
| 92 | 93 |
public List<String> toStrings() {
|
| 93 | 94 |
// |
| 94 |
// Respecto al base cambia la declaracion de campo automaticos |
|
| 95 |
// SpatiaLite usa AUTOINCREMENT en lugar de SERIAL y que hay que usar |
|
| 96 |
// la funcion AddGeometryColumn para a?adir las columnas de tipo |
|
| 97 |
// geometria. |
|
| 98 |
// El orden en el que hay que declarar las constrains tambien cambia |
|
| 95 |
// Respecto al base cambia la declaracion de campo automaticos: |
|
| 96 |
// - Los campos se crean autom?ticamente como SERIAL si son INTEGER PRIMARY KEY. |
|
| 97 |
// - Existe la palabra clave AUTOINCREMENT que se puede usar |
|
| 98 |
// en los campos INTEGER PRIMARY KEY, pero no se recomienda su uso (penaliza el rendimiento). |
|
| 99 |
// - Spatialite no permite definir SERIALs para el resto de campos. |
|
| 100 |
// - Hay que usar la funcion AddGeometryColumn para a?adir las columnas de tipo geometria |
|
| 101 |
// - El orden en el que hay que declarar las constrains tambien cambia |
|
| 99 | 102 |
// respecto al que hay por defecto. |
| 100 | 103 |
// |
| 101 | 104 |
List<String> sqls = new ArrayList<>(); |
| ... | ... | |
| 116 | 119 |
} |
| 117 | 120 |
builder.append(identifier(column.getName())); |
| 118 | 121 |
builder.append(" ");
|
| 119 |
builder.append(sqltype(column.getType(), column.getPrecision(), column.getSize())); |
|
| 122 |
builder.append(sqltype(column.getType(), column.getPrecision(), column.getSize(), column.getGeometryType(), column.getGeometrySubtype()));
|
|
| 120 | 123 |
if (column.isPrimaryKey()) {
|
| 121 | 124 |
builder.append(" PRIMARY KEY");
|
| 122 | 125 |
} |
| 123 |
if( column.isAutomatic() ) {
|
|
| 124 |
builder.append(" AUTOINCREMENT");
|
|
| 125 |
} |
|
| 126 | 126 |
if (column.getDefaultValue() == null) {
|
| 127 | 127 |
if (column.allowNulls()) {
|
| 128 | 128 |
builder.append(" DEFAULT NULL");
|
| ... | ... | |
| 145 | 145 |
builder.append(" )");
|
| 146 | 146 |
sqls.add(builder.toString()); |
| 147 | 147 |
|
| 148 |
String AddGeometryColumn = "SELECT AddGeometryColumn({0} , {1} , {2,number,#######} , {3}, {4}, {5})";
|
|
| 148 |
String AddGeometryColumn = "SELECT AddGeometryColumn({0} , {1} , {2} , {3}, {4}, {5})";
|
|
| 149 | 149 |
for (ColumnDescriptorBuilderBase column : columns) {
|
| 150 | 150 |
if( column.isGeometry() ) {
|
| 151 | 151 |
String sql = MessageFormat.format( |
| 152 | 152 |
AddGeometryColumn, |
| 153 | 153 |
this.table.toString(), |
| 154 | 154 |
identifier(column.getName()), |
| 155 |
column.getGeometrySRSId(), |
|
| 155 |
column.getGeometrySRSId().toString(),
|
|
| 156 | 156 |
constant(sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype())), |
| 157 | 157 |
constant(sqlgeometrydimension(column.getGeometryType(), column.getGeometrySubtype())), |
| 158 |
column.allowNulls()? 1:0
|
|
| 158 |
column.allowNulls()? 0:1
|
|
| 159 | 159 |
); |
| 160 | 160 |
sqls.add(sql); |
| 161 | 161 |
} |
| ... | ... | |
| 333 | 333 |
@Override |
| 334 | 334 |
public List<String> toStrings() {
|
| 335 | 335 |
List<String> sqls = new ArrayList<>(); |
| 336 |
if( this.isSpatial ) {
|
|
| 336 |
if( this.isSpatial && this.columns.size()==1) {
|
|
| 337 | 337 |
StringBuilder builder = new StringBuilder(); |
| 338 | 338 |
builder.append("SELECT CreateSpatialIndex('");
|
| 339 | 339 |
builder.append(this.indexName); |
| 340 | 340 |
builder.append("', '");
|
| 341 |
builder.append(this.table().getName());
|
|
| 341 |
builder.append(this.columns.get(0));
|
|
| 342 | 342 |
builder.append("')");
|
| 343 | 343 |
sqls.add(builder.toString()); |
| 344 | 344 |
} else {
|
| ... | ... | |
| 369 | 369 |
} |
| 370 | 370 |
return sqls; |
| 371 | 371 |
} |
| 372 |
|
|
| 373 | 372 |
} |
| 374 | 373 |
|
| 375 | 374 |
@Override |
| ... | ... | |
| 391 | 390 |
protected CreateIndexBuilder createCreateIndexBuilder() {
|
| 392 | 391 |
return new SpatiaLiteCreateIndexBuilder(); |
| 393 | 392 |
} |
| 393 |
|
|
| 394 |
@Override |
|
| 395 |
protected AlterTableBuilder createAlterTableBuilder() {
|
|
| 396 |
return new SpatialiteAlterTableBuilder(); |
|
| 397 |
} |
|
| 394 | 398 |
|
| 399 |
public class SpatialiteAlterTableBuilder extends AlterTableBuilderBase {
|
|
| 400 |
|
|
| 401 |
@Override |
|
| 402 |
public List<String> toStrings() {
|
|
| 403 |
// Notes: |
|
| 404 |
// - Alter column is not supported in SQLite |
|
| 405 |
// - Fields are automatically created as serial if they are INTEGER PRIMARY KEY |
|
| 406 |
// - It is not possible to define serial fields for other columns |
|
| 407 |
List<String> sqls = new ArrayList<>(); |
|
| 408 |
if( this.isEmpty() ) {
|
|
| 409 |
return sqls; |
|
| 410 |
} |
|
| 411 |
for (String column : drops) {
|
|
| 412 |
StringBuilder builder = new StringBuilder(); |
|
| 413 |
builder.append("ALTER TABLE ");
|
|
| 414 |
builder.append(this.table.toString()); |
|
| 415 |
builder.append(" DROP COLUMN IF EXISTS ");
|
|
| 416 |
builder.append(identifier(column)); |
|
| 417 |
sqls.add(builder.toString()); |
|
| 418 |
} |
|
| 419 |
|
|
| 420 |
for (ColumnDescriptorBuilderBase column : adds) {
|
|
| 421 |
if (column.isGeometry()) {
|
|
| 422 |
String addGeometryColumn = "SELECT AddGeometryColumn({0} , {1} , {2} , {3}, {4}, {5})";
|
|
| 423 |
String sql = MessageFormat.format( |
|
| 424 |
addGeometryColumn, |
|
| 425 |
this.table.toString(), |
|
| 426 |
identifier(column.getName()), |
|
| 427 |
column.getGeometrySRSId().toString(), |
|
| 428 |
constant(sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype())), |
|
| 429 |
constant(sqlgeometrydimension(column.getGeometryType(), column.getGeometrySubtype())), |
|
| 430 |
column.allowNulls()? 0:1 |
|
| 431 |
); |
|
| 432 |
sqls.add(sql); |
|
| 433 |
} |
|
| 434 |
else {
|
|
| 435 |
StringBuilder builder = new StringBuilder(); |
|
| 436 |
builder.append("ALTER TABLE ");
|
|
| 437 |
builder.append(this.table.toString()); |
|
| 438 |
builder.append(" ADD COLUMN ");
|
|
| 439 |
builder.append(identifier(column.getName())); |
|
| 440 |
builder.append(" ");
|
|
| 441 |
builder.append( |
|
| 442 |
sqltype( |
|
| 443 |
column.getType(), |
|
| 444 |
column.getPrecision(), |
|
| 445 |
column.getSize(), |
|
| 446 |
column.getGeometryType(), |
|
| 447 |
column.getGeometrySubtype() |
|
| 448 |
) |
|
| 449 |
); |
|
| 450 |
if (column.getDefaultValue() == null) {
|
|
| 451 |
if (column.allowNulls()) {
|
|
| 452 |
builder.append(" DEFAULT NULL");
|
|
| 453 |
} |
|
| 454 |
} else {
|
|
| 455 |
builder.append(" DEFAULT '");
|
|
| 456 |
builder.append(column.getDefaultValue().toString()); |
|
| 457 |
builder.append("'");
|
|
| 458 |
} |
|
| 459 |
if (column.allowNulls()) {
|
|
| 460 |
builder.append(" NULL");
|
|
| 461 |
} else {
|
|
| 462 |
builder.append(" NOT NULL");
|
|
| 463 |
} |
|
| 464 |
if (column.isPrimaryKey()) {
|
|
| 465 |
builder.append(" PRIMARY KEY");
|
|
| 466 |
} |
|
| 467 |
sqls.add(builder.toString()); |
|
| 468 |
} |
|
| 469 |
} |
|
| 470 |
for (Pair<String,String> pair : renames) {
|
|
| 471 |
StringBuilder builder = new StringBuilder(); |
|
| 472 |
builder.append("ALTER TABLE ");
|
|
| 473 |
builder.append(this.table.toString()); |
|
| 474 |
builder.append(" RENAME COLUMN ");
|
|
| 475 |
builder.append(identifier(pair.getLeft())); |
|
| 476 |
builder.append(" TO ");
|
|
| 477 |
builder.append(identifier(pair.getRight())); |
|
| 478 |
sqls.add(builder.toString()); |
|
| 479 |
} |
|
| 480 |
return sqls; |
|
| 481 |
} |
|
| 482 |
} |
|
| 483 |
|
|
| 395 | 484 |
} |
Also available in: Unified diff