Revision 511
| trunk/org.gvsig.postgresql/pom.xml | ||
|---|---|---|
| 11 | 11 |
<parent> |
| 12 | 12 |
<groupId>org.gvsig</groupId> |
| 13 | 13 |
<artifactId>org.gvsig.desktop</artifactId> |
| 14 |
<version>2.0.241</version>
|
|
| 14 |
<version>2.0.242</version>
|
|
| 15 | 15 |
</parent> |
| 16 | 16 |
|
| 17 | 17 |
<url>https://devel.gvsig.org/redmine/projects/gvsig-postgresql</url> |
| trunk/org.gvsig.postgresql/org.gvsig.postgresql.provider/src/main/java/org/gvsig/postgresql/dal/PostgreSQLHelper.java | ||
|---|---|---|
| 6 | 6 |
import org.apache.commons.dbcp.BasicDataSource; |
| 7 | 7 |
import org.apache.commons.lang3.BooleanUtils; |
| 8 | 8 |
import org.apache.commons.lang3.StringUtils; |
| 9 |
import org.gvsig.expressionevaluator.ExpressionBuilder.GeometrySupportType; |
|
| 9 | 10 |
import org.gvsig.fmap.dal.SQLBuilder; |
| 10 | 11 |
import org.gvsig.fmap.dal.resource.exception.AccessResourceException; |
| 11 | 12 |
import org.gvsig.fmap.dal.store.jdbc.JDBCConnectionParameters; |
| ... | ... | |
| 238 | 239 |
} |
| 239 | 240 |
|
| 240 | 241 |
@Override |
| 241 |
public SQLBuilder.GeometrySupportType getGeometrySupportType() {
|
|
| 242 |
return SQLBuilder.GeometrySupportType.WKB;
|
|
| 242 |
public GeometrySupportType getGeometrySupportType() {
|
|
| 243 |
return GeometrySupportType.WKB; |
|
| 243 | 244 |
} |
| 244 | 245 |
|
| 245 | 246 |
@Override |
| trunk/org.gvsig.postgresql/org.gvsig.postgresql.provider/src/main/java/org/gvsig/postgresql/dal/operations/PostgreSQLFetchFeatureTypeOperation.java | ||
|---|---|---|
| 11 | 11 |
import java.util.Map; |
| 12 | 12 |
import org.apache.commons.lang3.StringUtils; |
| 13 | 13 |
import org.cresques.cts.IProjection; |
| 14 |
import org.gvsig.expressionevaluator.ExpressionBuilder; |
|
| 14 | 15 |
import org.gvsig.fmap.dal.DataTypes; |
| 15 | 16 |
import org.gvsig.fmap.dal.exception.DataException; |
| 16 | 17 |
import org.gvsig.fmap.dal.feature.EditableFeatureAttributeDescriptor; |
| ... | ... | |
| 74 | 75 |
|
| 75 | 76 |
return super.getDataTypeFromMetadata(rsMetadata, colIndex); |
| 76 | 77 |
} |
| 78 |
|
|
| 79 |
@Override |
|
| 77 | 80 |
protected String getSQLToRetrievePrimaryKeysFromInformationSchema( |
| 78 | 81 |
String catalog, |
| 79 | 82 |
String schema, |
| 80 | 83 |
String table |
| 81 | 84 |
) throws SQLException {
|
| 82 | 85 |
PostgreSQLBuilder sqlbuilder = (PostgreSQLBuilder) this.createSQLBuilder(); |
| 83 |
|
|
| 86 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
|
| 87 |
|
|
| 84 | 88 |
String column_COLUMN_NAME = "column_name"; |
| 85 | 89 |
String column_CONSTRAINT_TYPE = "constraint_type"; |
| 86 | 90 |
|
| ... | ... | |
| 99 | 103 |
+ "c.constraint_name = t_cons.constraint_name " |
| 100 | 104 |
); |
| 101 | 105 |
sqlbuilder.select().where().set( |
| 102 |
sqlbuilder.like(
|
|
| 103 |
sqlbuilder.custom("c.TABLE_NAME"),
|
|
| 104 |
sqlbuilder.constant(table)
|
|
| 106 |
expbuilder.like(
|
|
| 107 |
expbuilder.custom("c.TABLE_NAME"),
|
|
| 108 |
expbuilder.constant(table)
|
|
| 105 | 109 |
) |
| 106 | 110 |
); |
| 107 | 111 |
if (schema != null) {
|
| 108 | 112 |
sqlbuilder.select().where().and( |
| 109 |
sqlbuilder.like(
|
|
| 110 |
sqlbuilder.custom("c.TABLE_SCHEMA"),
|
|
| 111 |
sqlbuilder.constant(schema)
|
|
| 113 |
expbuilder.like(
|
|
| 114 |
expbuilder.custom("c.TABLE_SCHEMA"),
|
|
| 115 |
expbuilder.constant(schema)
|
|
| 112 | 116 |
) |
| 113 | 117 |
); |
| 114 | 118 |
} |
| 115 | 119 |
if (catalog != null) {
|
| 116 | 120 |
sqlbuilder.select().where().and( |
| 117 |
sqlbuilder.like(
|
|
| 118 |
sqlbuilder.custom("c.CONSTRAINT_CATALOG"),
|
|
| 119 |
sqlbuilder.constant(catalog)
|
|
| 121 |
expbuilder.like(
|
|
| 122 |
expbuilder.custom("c.CONSTRAINT_CATALOG"),
|
|
| 123 |
expbuilder.constant(catalog)
|
|
| 120 | 124 |
) |
| 121 | 125 |
); |
| 122 | 126 |
} |
| 123 | 127 |
sqlbuilder.select().where().and( |
| 124 |
sqlbuilder.eq(
|
|
| 125 |
sqlbuilder.column(column_CONSTRAINT_TYPE),
|
|
| 126 |
sqlbuilder.constant("PRIMARY KEY")
|
|
| 128 |
expbuilder.eq(
|
|
| 129 |
expbuilder.column(column_CONSTRAINT_TYPE),
|
|
| 130 |
expbuilder.constant("PRIMARY KEY")
|
|
| 127 | 131 |
) |
| 128 | 132 |
); |
| 129 | 133 |
return sqlbuilder.toString(); |
| ... | ... | |
| 140 | 144 |
} |
| 141 | 145 |
try {
|
| 142 | 146 |
JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder(); |
| 147 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
|
| 148 |
|
|
| 143 | 149 |
sqlbuilder.select().column().name("f_table_catalog");
|
| 144 | 150 |
sqlbuilder.select().column().name("f_table_schema");
|
| 145 | 151 |
sqlbuilder.select().column().name("f_table_name");
|
| ... | ... | |
| 148 | 154 |
sqlbuilder.select().column().name("srid");
|
| 149 | 155 |
sqlbuilder.select().column().name("type");
|
| 150 | 156 |
sqlbuilder.select().where().set( |
| 151 |
sqlbuilder.eq(
|
|
| 152 |
sqlbuilder.column("f_table_name"),
|
|
| 153 |
sqlbuilder.constant(this.getTable().getTable())
|
|
| 157 |
expbuilder.eq(
|
|
| 158 |
expbuilder.column("f_table_name"),
|
|
| 159 |
expbuilder.constant(this.getTable().getTable())
|
|
| 154 | 160 |
) |
| 155 | 161 |
); |
| 156 | 162 |
sqlbuilder.select().where().and( |
| 157 |
sqlbuilder.eq(
|
|
| 158 |
sqlbuilder.column("f_geometry_column"),
|
|
| 159 |
sqlbuilder.constant(attr.getName())
|
|
| 163 |
expbuilder.eq(
|
|
| 164 |
expbuilder.column("f_geometry_column"),
|
|
| 165 |
expbuilder.constant(attr.getName())
|
|
| 160 | 166 |
) |
| 161 | 167 |
); |
| 162 | 168 |
sqlbuilder.select().from().table().name("geometry_columns");
|
| trunk/org.gvsig.postgresql/org.gvsig.postgresql.provider/src/main/java/org/gvsig/postgresql/dal/PostgreSQLBuilder.java | ||
|---|---|---|
| 32 | 32 |
import org.apache.commons.lang3.StringUtils; |
| 33 | 33 |
import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper; |
| 34 | 34 |
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
| 35 |
import org.gvsig.tools.packageutils.Version; |
|
| 36 |
import org.gvsig.tools.packageutils.impl.DefaultVersion; |
|
| 37 | 35 |
|
| 38 | 36 |
public class PostgreSQLBuilder extends JDBCSQLBuilderBase {
|
| 39 | 37 |
|
| ... | ... | |
| 79 | 77 |
|
| 80 | 78 |
public PostgreSQLBuilder(JDBCHelper helper) {
|
| 81 | 79 |
super(helper); |
| 82 |
|
|
| 83 |
config.set(SQLConfig.default_schema, "public"); |
|
| 84 |
config.set(SQLConfig.allowAutomaticValues, true); |
|
| 85 |
config.set(SQLConfig.geometry_type_support, this.helper.getGeometrySupportType()); |
|
| 86 |
config.set(SQLConfig.has_spatial_functions, this.helper.hasSpatialFunctions()); |
|
| 87 |
config.set(SQLConfig.constant_true, "true"); |
|
| 88 |
config.set(SQLConfig.constant_false, "false"); |
|
| 89 |
|
|
| 90 |
config.remove_functionality(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table); |
|
| 91 |
config.remove_functionality(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_table); |
|
| 92 |
|
|
| 93 |
config.set(SQLConfig.UPDATE_TABLE_STATISTICS_table,"ANALYZE {0}");
|
|
| 80 |
this.defaultSchema = "public"; |
|
| 81 |
this.supportSchemas = true; |
|
| 82 |
this.allowAutomaticValues = true; |
|
| 83 |
this.geometrySupportType = this.helper.getGeometrySupportType(); |
|
| 84 |
this.hasSpatialFunctions = this.helper.hasSpatialFunctions(); |
|
| 94 | 85 |
|
| 95 |
config.set(SQLConfig.ST_GeomFromEWKB, "ST_GeomFromEWKB({0}, {1})");
|
|
| 96 |
config.set(SQLConfig.ST_AsEWKB, "ST_AsEWKB(({0}))");
|
|
| 97 |
config.set(SQLConfig.ST_ExtentAggregate, "ST_Extent({0})");
|
|
| 98 |
config.set(SQLConfig.ST_UnionAggregate, "ST_Union({0})");
|
|
| 86 |
this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table = null; |
|
| 87 |
this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_table = null; |
|
| 88 |
|
|
| 89 |
this.STMT_UPDATE_TABLE_STATISTICS_table = "ANALYZE {0}";
|
|
| 90 |
|
|
| 91 |
// config.set(SQLConfig.ST_GeomFromEWKB, "ST_GeomFromEWKB({0}, {1})");
|
|
| 92 |
// config.set(SQLConfig.ST_AsEWKB, "ST_AsEWKB(({0}))");
|
|
| 93 |
// config.set(SQLConfig.ST_ExtentAggregate, "ST_Extent({0})");
|
|
| 94 |
// config.set(SQLConfig.ST_UnionAggregate, "ST_Union({0})");
|
|
| 99 | 95 |
|
| 100 |
config.set(SQLConfig.lcase, "lower({0})");
|
|
| 101 |
config.set(SQLConfig.ucase, "upper({0})");
|
|
| 102 |
config.set(SQLConfig.operator_ILIKE, "({0}) ILIKE ({1})");
|
|
| 103 |
config.set(SQLConfig.isNull, "( ({0}) ISNULL )");
|
|
| 104 |
config.set(SQLConfig.notIsNull, "( ({0}) NOT NULL )");
|
|
| 96 |
// config.set(SQLConfig.isNull, "( ({0}) ISNULL )");
|
|
| 97 |
// config.set(SQLConfig.notIsNull, "( ({0}) NOT NULL )");
|
|
| 105 | 98 |
|
| 106 | 99 |
} |
| 107 | 100 |
|
| ... | ... | |
| 110 | 103 |
public List<String> toStrings() {
|
| 111 | 104 |
List<String> sqls = new ArrayList<>(); |
| 112 | 105 |
|
| 113 |
if( config.has_functionality(SQLConfig.UPDATE_TABLE_STATISTICS_table) ) {
|
|
| 106 |
if( !StringUtils.isBlank(STMT_UPDATE_TABLE_STATISTICS_table) ) {
|
|
| 114 | 107 |
// In postGIS, UpdateLayerStatistics function, don't allow to |
| 115 | 108 |
// use the database name in the table name. |
| 116 |
String name = identifier(this.table.getName()); |
|
| 109 |
String name = as_identifier(this.table.getName());
|
|
| 117 | 110 |
if( table.has_schema()) {
|
| 118 |
name = identifier(this.table.getSchema()) + "." + name; |
|
| 111 |
name = as_identifier(this.table.getSchema()) + "." + name;
|
|
| 119 | 112 |
} |
| 120 | 113 |
String sql = MessageFormat.format( |
| 121 |
config.getString(SQLConfig.UPDATE_TABLE_STATISTICS_table),
|
|
| 114 |
STMT_UPDATE_TABLE_STATISTICS_table,
|
|
| 122 | 115 |
name |
| 123 | 116 |
); |
| 124 | 117 |
if( !StringUtils.isEmpty(sql) ) {
|
| ... | ... | |
| 144 | 137 |
builder.append("IF NOT EXISTS ");
|
| 145 | 138 |
} |
| 146 | 139 |
} |
| 147 |
builder.append(identifier(this.indexName)); |
|
| 140 |
builder.append(as_identifier(this.indexName));
|
|
| 148 | 141 |
builder.append(" ON ");
|
| 149 | 142 |
builder.append(this.table.toString()); |
| 150 | 143 |
if( this.isSpatial ) {
|
| ... | ... | |
| 192 | 185 |
} else {
|
| 193 | 186 |
builder.append(", ");
|
| 194 | 187 |
} |
| 195 |
builder.append(identifier(column.getName())); |
|
| 188 |
builder.append(as_identifier(column.getName()));
|
|
| 196 | 189 |
builder.append(" ");
|
| 197 | 190 |
if( column.isAutomatic() ) {
|
| 198 | 191 |
builder.append(" SERIAL");
|
| 199 | 192 |
} else {
|
| 200 |
builder.append(sqltype(column.getType(), column.getPrecision(), column.getSize())); |
|
| 193 |
builder.append(sqltype( |
|
| 194 |
column.getType(), |
|
| 195 |
column.getPrecision(), |
|
| 196 |
column.getSize(), |
|
| 197 |
column.getGeometryType(), |
|
| 198 |
column.getGeometrySubtype() |
|
| 199 |
)); |
|
| 201 | 200 |
if (column.getDefaultValue() == null) {
|
| 202 | 201 |
if (column.allowNulls()) {
|
| 203 | 202 |
builder.append(" DEFAULT NULL");
|
| ... | ... | |
| 225 | 224 |
if( column.isGeometry() ) {
|
| 226 | 225 |
String sql = MessageFormat.format( |
| 227 | 226 |
AddGeometryColumn, |
| 228 |
constant(this.table.getSchema()),
|
|
| 229 |
constant(this.table.getName()),
|
|
| 230 |
constant(column.getName()),
|
|
| 227 |
as_string(this.table.getSchema()),
|
|
| 228 |
as_string(this.table.getName()),
|
|
| 229 |
as_string(column.getName()),
|
|
| 231 | 230 |
column.getGeometrySRSId(), |
| 232 |
constant(sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype())),
|
|
| 233 |
constant(sqlgeometrydimension(column.getGeometryType(), column.getGeometrySubtype())),
|
|
| 234 |
constant(column.allowNulls())
|
|
| 231 |
as_string(sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype())),
|
|
| 232 |
as_string(sqlgeometrydimension(column.getGeometryType(), column.getGeometrySubtype())),
|
|
| 233 |
as_string(column.allowNulls())
|
|
| 235 | 234 |
); |
| 236 | 235 |
sqls.add(sql); |
| 237 | 236 |
} |
| ... | ... | |
| 323 | 322 |
} |
| 324 | 323 |
} |
| 325 | 324 |
|
| 326 |
@Override |
|
| 327 |
public String bytearray(byte[] data) {
|
|
| 328 |
return "decode('"+bytearray_hex(data)+"','hex')";
|
|
| 329 |
} |
|
| 325 |
// @Override
|
|
| 326 |
// public String bytearray(byte[] data) {
|
|
| 327 |
// return "decode('"+as_string(data)+"','hex')";
|
|
| 328 |
// }
|
|
| 330 | 329 |
|
| 331 | 330 |
public PostgreSQLHelper getHelper() {
|
| 332 | 331 |
return (PostgreSQLHelper) helper; |
Also available in: Unified diff