Revision 165 trunk/org.gvsig.mssqlserver/org.gvsig.mssqlserver.provider/src/main/java/org/gvsig/mssqlserver/dal/MSSQLServerSQLBuilder.java
| MSSQLServerSQLBuilder.java | ||
|---|---|---|
| 7 | 7 |
import org.gvsig.fmap.dal.DataTypes; |
| 8 | 8 |
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
| 9 | 9 |
import org.gvsig.fmap.geom.Geometry; |
| 10 |
import org.gvsig.fmap.geom.primitive.Envelope; |
|
| 10 | 11 |
|
| 11 | 12 |
public class MSSQLServerSQLBuilder extends JDBCSQLBuilderBase {
|
| 12 | 13 |
|
| ... | ... | |
| 31 | 32 |
|
| 32 | 33 |
config.set(SQLConfig.UPDATE_TABLE_STATISTICS_table,"UPDATE STATISTICS {0}");
|
| 33 | 34 |
|
| 34 |
config.set(SQLConfig.type_bytearray, "VARARRAY"); |
|
| 35 | 35 |
config.set(SQLConfig.type_geometry, "GEOMETRY"); |
| 36 |
|
|
| 37 |
config.set(SQLConfig.type_boolean, "BIT"); |
|
| 38 |
config.set(SQLConfig.type_double, "FLOAT"); //float con 53 bits de mantisa, float(54) |
|
| 39 |
config.set(SQLConfig.type_numeric_p, "NUMERIC({0})");
|
|
| 40 |
config.set(SQLConfig.type_numeric_ps, "NUMERIC({0},{1})");
|
|
| 41 |
config.set(SQLConfig.type_bigdecimal, "NUMERIC({0},{1})");
|
|
| 42 |
config.set(SQLConfig.type_float, "REAL"); //float con 24 bits de mantisa, float(24) |
|
| 43 |
config.set(SQLConfig.type_int, "INT"); |
|
| 44 |
config.set(SQLConfig.type_long, "BIGINT"); |
|
| 45 |
config.set(SQLConfig.type_byte, "TINYINT"); |
|
| 46 |
|
|
| 47 |
config.set(SQLConfig.type_date, "DATE"); |
|
| 48 |
config.set(SQLConfig.type_time, "TIME"); |
|
| 36 | 49 |
|
| 50 |
config.set(SQLConfig.type_char, "CHAR(1)"); |
|
| 51 |
config.set(SQLConfig.type_string, "TEXT"); |
|
| 52 |
config.set(SQLConfig.type_string_p, "VARCHAR({0})");
|
|
| 53 |
|
|
| 54 |
config.set(SQLConfig.type_version, "VARCHAR(30)"); |
|
| 55 |
config.set(SQLConfig.type_URI, "TEXT"); |
|
| 56 |
config.set(SQLConfig.type_URL, "TEXT"); |
|
| 57 |
config.set(SQLConfig.type_FILE, "TEXT"); |
|
| 58 |
config.set(SQLConfig.type_FOLDER, "TEXT"); |
|
| 59 |
|
|
| 60 |
config.set(SQLConfig.type_bytearray, "VARBINARY"); |
|
| 61 |
|
|
| 62 |
config.set(SQLConfig.type_timestamp, "TIMESTAMP"); |
|
| 63 |
|
|
| 64 |
config.set(SQLConfig.ST_SRID, "(({0}).STSrid)");
|
|
| 37 | 65 |
config.set(SQLConfig.ST_AsText, "({0}).STAsText()");
|
| 38 | 66 |
config.set(SQLConfig.ST_AsBinary, "({0}).STAsBinary()");
|
| 39 | 67 |
config.set(SQLConfig.ST_AsEWKB, "({0}).STAsBinary()");
|
| 40 | 68 |
config.set(SQLConfig.ST_ExtentAggregate, "geometry::EnvelopeAggregate({0})");
|
| 41 | 69 |
config.set(SQLConfig.ST_UnionAggregate, "geometry::UnionAggregate({0})");
|
| 42 |
config.set(SQLConfig.ST_Contains, "({0}).STContains({1})");
|
|
| 43 |
config.set(SQLConfig.ST_Crosses, "({0}).STCrosses({1})");
|
|
| 44 |
config.set(SQLConfig.ST_Disjoint, "({0}).STDisjoint({1})");
|
|
| 45 |
config.set(SQLConfig.ST_Equals, "({0}).STEquals({1})");
|
|
| 46 |
config.set(SQLConfig.ST_IsClosed, "({0}).STIsClosed()");
|
|
| 47 |
config.set(SQLConfig.ST_Overlaps, "({0}).STOverlaps({1})");
|
|
| 48 |
config.set(SQLConfig.ST_Touches, "({0}).STTouches({1})");
|
|
| 49 |
config.set(SQLConfig.ST_Within, "({0}).STWithin ({1})");
|
|
| 70 |
config.set(SQLConfig.ST_Contains, "(({0}).STContains({1})=1)");
|
|
| 71 |
config.set(SQLConfig.ST_Crosses, "(({0}).STCrosses({1})=1)");
|
|
| 72 |
config.set(SQLConfig.ST_Disjoint, "(({0}).STDisjoint({1})=1)");
|
|
| 73 |
config.set(SQLConfig.ST_Equals, "(({0}).STEquals({1})=1)");
|
|
| 74 |
config.set(SQLConfig.ST_IsClosed, "(({0}).STIsClosed()=1)");
|
|
| 75 |
config.set(SQLConfig.ST_Overlaps, "(({0}).STOverlaps({1})=1)");
|
|
| 76 |
config.set(SQLConfig.ST_Touches, "(({0}).STTouches({1})=1)");
|
|
| 77 |
config.set(SQLConfig.ST_Within, "(({0}).STWithin ({1})=1)");
|
|
| 50 | 78 |
config.set(SQLConfig.ST_Envelope, "({0}).STEnvelope()");
|
| 51 |
config.set(SQLConfig.ST_Intersects, "({0}).STIntersects({1})");
|
|
| 79 |
config.set(SQLConfig.ST_Intersects, "(({0}).STIntersects({1})=1)");
|
|
| 52 | 80 |
config.set(SQLConfig.ST_GeomFromText, "geometry::STGeomFromText({0}, {1})");
|
| 53 | 81 |
config.set(SQLConfig.ST_GeomFromWKB, "geometry::STGeomFromWKB({0}, {1})");
|
| 54 | 82 |
config.set(SQLConfig.ST_GeomFromEWKB, "geometry::STGeomFromWKB({0}, {1})");
|
| ... | ... | |
| 56 | 84 |
config.set(SQLConfig.lcase, "LOWER({0})");
|
| 57 | 85 |
config.set(SQLConfig.ucase, "UPPER({0})");
|
| 58 | 86 |
config.set(SQLConfig.operator_ILIKE, "LOWER({0}) LIKE LOWER({1})");
|
| 87 |
config.set(SQLConfig.notIsNull,"( ({0}) IS NOT NULL )" );
|
|
| 59 | 88 |
|
| 60 | 89 |
config.set(MSSQLServerSQLConfig.ST_ExtentAggregateEx, "{1}::EnvelopeAggregate({0})");
|
| 61 | 90 |
config.set(MSSQLServerSQLConfig.ST_UnionAggregateEx, "{1}::UnionAggregate({0})");
|
| ... | ... | |
| 75 | 104 |
return (MSSQLServerHelper) helper; |
| 76 | 105 |
} |
| 77 | 106 |
|
| 107 |
public class MSSQLServerCreateIndexBuilder extends CreateIndexBuilderBase {
|
|
| 108 |
|
|
| 109 |
private Envelope boundingBox; |
|
| 110 |
|
|
| 111 |
public MSSQLServerCreateIndexBuilder() {
|
|
| 112 |
super(); |
|
| 113 |
this.boundingBox = null; |
|
| 114 |
} |
|
| 115 |
|
|
| 116 |
public void setBoundingBox(Envelope boundingBox) {
|
|
| 117 |
this.boundingBox = boundingBox; |
|
| 118 |
} |
|
| 119 |
|
|
| 120 |
private double getXMin() {
|
|
| 121 |
// https://alastaira.wordpress.com/2011/07/26/determining-the-geographic-extent-of-spatial-features-in-a-sql-server-table/ |
|
| 122 |
return Math.min(boundingBox.getUpperCorner().getX(), boundingBox.getLowerCorner().getX()); |
|
| 123 |
} |
|
| 124 |
|
|
| 125 |
private double getYMin() {
|
|
| 126 |
return Math.min(boundingBox.getUpperCorner().getY(), boundingBox.getLowerCorner().getY()); |
|
| 127 |
} |
|
| 128 |
|
|
| 129 |
private double getXMax() {
|
|
| 130 |
return Math.max(boundingBox.getUpperCorner().getX(), boundingBox.getLowerCorner().getX()); |
|
| 131 |
} |
|
| 132 |
|
|
| 133 |
private double getYMax() {
|
|
| 134 |
return Math.max(boundingBox.getUpperCorner().getY(), boundingBox.getLowerCorner().getY()); |
|
| 135 |
} |
|
| 136 |
|
|
| 137 |
@Override |
|
| 138 |
public List<String> toStrings() {
|
|
| 139 |
StringBuilder builder = new StringBuilder(); |
|
| 140 |
if( this.isSpatial ) {
|
|
| 141 |
builder.append("CREATE SPATIAL INDEX ");
|
|
| 142 |
builder.append(identifier(this.indexName)); |
|
| 143 |
builder.append(" ON ");
|
|
| 144 |
builder.append(this.table.toString()); |
|
| 145 |
builder.append(" ( ");
|
|
| 146 |
boolean is_first_column = true; |
|
| 147 |
for( String column : this.columns) {
|
|
| 148 |
if( is_first_column ) {
|
|
| 149 |
is_first_column = false; |
|
| 150 |
} else {
|
|
| 151 |
builder.append(", ");
|
|
| 152 |
} |
|
| 153 |
builder.append(column); |
|
| 154 |
} |
|
| 155 |
builder.append(" ) ");
|
|
| 156 |
builder.append("USING GEOMETRY_GRID ");
|
|
| 157 |
builder.append("WITH( ");
|
|
| 158 |
builder.append("BOUNDING_BOX = ( ");
|
|
| 159 |
builder.append("xmin = ").append(this.getXMin()).append(", ");
|
|
| 160 |
builder.append("ymin = ").append(this.getYMin()).append(", ");
|
|
| 161 |
builder.append("xmax = ").append(this.getXMax()).append(", ");
|
|
| 162 |
builder.append("ymax = ").append(this.getYMax());
|
|
| 163 |
builder.append(" )");
|
|
| 164 |
// builder.append(", DROP_EXISTING = ON");
|
|
| 165 |
// builder.append(", GRIDS = ( LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM, LEVEL_3 = MEDIUM, LEVEL_4 = MEDIUM)");
|
|
| 166 |
// builder.append(", CELLS_PER_OBJECT = 16");
|
|
| 167 |
// builder.append(", STATISTICS_NORECOMPUTE = OFF");
|
|
| 168 |
// builder.append(", ALLOW_ROW_LOCKS = ON");
|
|
| 169 |
// builder.append(", ALLOW_PAGE_LOCKS = ON");
|
|
| 170 |
builder.append(" ) ");
|
|
| 171 |
} else {
|
|
| 172 |
builder.append("CREATE ");
|
|
| 173 |
if( this.isUnique ) {
|
|
| 174 |
builder.append("UNIQUE ");
|
|
| 175 |
} |
|
| 176 |
builder.append("INDEX ");
|
|
| 177 |
// if( this.ifNotExist ) {
|
|
| 178 |
// builder.append("IF NOT EXISTS ");
|
|
| 179 |
// } |
|
| 180 |
builder.append(identifier(this.indexName)); |
|
| 181 |
builder.append(" ON ");
|
|
| 182 |
builder.append(this.table.toString()); |
|
| 183 |
builder.append(" ( ");
|
|
| 184 |
boolean is_first_column = true; |
|
| 185 |
for( String column : this.columns) {
|
|
| 186 |
if( is_first_column ) {
|
|
| 187 |
is_first_column = false; |
|
| 188 |
} else {
|
|
| 189 |
builder.append(", ");
|
|
| 190 |
} |
|
| 191 |
builder.append(column); |
|
| 192 |
} |
|
| 193 |
builder.append(" )");
|
|
| 194 |
} |
|
| 195 |
List<String> sqls = new ArrayList<>(); |
|
| 196 |
sqls.add(builder.toString()); |
|
| 197 |
return sqls; |
|
| 198 |
} |
|
| 199 |
|
|
| 200 |
} |
|
| 201 |
|
|
| 78 | 202 |
public class MSSQLServerParameter extends ParameterBase {
|
| 79 | 203 |
|
| 80 | 204 |
public MSSQLServerParameter() {
|
| ... | ... | |
| 288 | 412 |
} |
| 289 | 413 |
|
| 290 | 414 |
@Override |
| 415 |
protected CreateIndexBuilder createCreateIndexBuilder() {
|
|
| 416 |
return new MSSQLServerCreateIndexBuilder(); |
|
| 417 |
} |
|
| 418 |
|
|
| 419 |
@Override |
|
| 291 | 420 |
public String identifier(String id) {
|
| 292 | 421 |
// En SQLServer se aceptan las comillas dobles pero se prefiere |
| 293 | 422 |
// corchetes [xx]. Asi que si hay comillas dobles las quitamos |
Also available in: Unified diff