Revision 46806 trunk/org.gvsig.desktop/org.gvsig.desktop.plugin/org.gvsig.h2spatial/org.gvsig.h2spatial.h2gis132/org.gvsig.h2spatial.h2gis132.provider/src/main/java/org/gvsig/fmap/dal/store/h2/H2SpatialSQLBuilder.java
H2SpatialSQLBuilder.java | ||
---|---|---|
6 | 6 |
import java.sql.Timestamp; |
7 | 7 |
import java.text.MessageFormat; |
8 | 8 |
import java.util.ArrayList; |
9 |
import java.util.Collections; |
|
9 | 10 |
import java.util.Date; |
10 | 11 |
import java.util.List; |
11 | 12 |
import java.util.Objects; |
12 | 13 |
import org.apache.commons.lang3.StringUtils; |
13 | 14 |
import org.apache.commons.lang3.tuple.Pair; |
14 | 15 |
import org.cresques.cts.IProjection; |
16 |
import org.gvsig.expressionevaluator.ExpressionBuilder; |
|
15 | 17 |
import org.gvsig.expressionevaluator.ExpressionBuilder.Parameter; |
16 | 18 |
import org.gvsig.expressionevaluator.ExpressionUtils; |
17 | 19 |
import org.gvsig.expressionevaluator.Formatter; |
... | ... | |
138 | 140 |
} |
139 | 141 |
|
140 | 142 |
} |
141 |
|
|
143 |
|
|
142 | 144 |
protected class H2SpatialAlterTableBuilderBase extends AlterTableBuilderBase { |
143 | 145 |
|
144 |
@Override |
|
145 |
public List<String> toStrings(Formatter formatter) { |
|
146 |
List<String> sqls = new ArrayList<>(); |
|
147 |
if( this.isEmpty() ) { |
|
148 |
return sqls; |
|
149 |
} |
|
150 |
|
|
151 |
String local_drop_primary_key_column = this.drop_primary_key_column; |
|
152 |
for (String column : drops) { |
|
153 |
StringBuilder builder = new StringBuilder(); |
|
154 |
builder.append("ALTER TABLE "); |
|
155 |
builder.append(this.table.toString(formatter)); |
|
156 |
builder.append(" DROP COLUMN IF EXISTS "); |
|
157 |
builder.append(as_identifier(column)); |
|
158 |
sqls.add(builder.toString()); |
|
159 |
if( StringUtils.equals(local_drop_primary_key_column, column) ) { |
|
160 |
// Si hemos eliminado la columna sobre la que estaba la pk, ya no hay que |
|
161 |
// eliminar la pk |
|
162 |
local_drop_primary_key_column = null; |
|
163 |
} |
|
164 |
} |
|
165 |
for (ColumnDescriptor column : adds) { |
|
166 |
StringBuilder builder = new StringBuilder(); |
|
167 |
builder.append("ALTER TABLE "); |
|
168 |
builder.append(this.table.toString(formatter)); |
|
169 |
builder.append(" ADD COLUMN "); |
|
170 |
builder.append(as_identifier(column.getName())); |
|
171 |
builder.append(" "); |
|
172 |
if( column.getType() == DataTypes.INT && column.isAutomatic() ) { |
|
173 |
builder.append(" SERIAL"); |
|
174 |
} else { |
|
175 |
builder.append( |
|
176 |
sqltype( |
|
177 |
column.getType(), |
|
178 |
column.getSize(), |
|
179 |
column.getPrecision(), |
|
180 |
column.getScale(), |
|
181 |
column.getGeometryType(), |
|
182 |
column.getGeometrySubtype() |
|
183 |
) |
|
146 |
protected List<String> alter_column_add_geometry_constraint_sqls(Formatter<ExpressionBuilder.Value> formatter, ColumnDescriptor column) { |
|
147 |
String constraint_name = this.getConstrainName("GEOM", column.getName()); |
|
148 |
String sql; |
|
149 |
if (column.getGeometrySRSId() == null) { |
|
150 |
if ((int) sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype()) == 0) { |
|
151 |
sql = MessageFormat.format( |
|
152 |
"ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT IF NOT EXISTS \"{2}\" CHECK NVL2(\"{3}\", ST_CoordDim(\"{3}\") = {4,number,###}, TRUE)", |
|
153 |
this.table().getSchema(), |
|
154 |
this.table().getName(), |
|
155 |
constraint_name, |
|
156 |
column.getName(), |
|
157 |
(int) sqlgeometrynumdimension(column.getGeometryType(), column.getGeometrySubtype()) |
|
184 | 158 |
); |
185 |
} |
|
186 |
if (column.getDefaultValue() == null) { |
|
187 |
if (column.allowNulls()) { |
|
188 |
builder.append(" DEFAULT NULL"); |
|
189 |
} |
|
190 | 159 |
} else { |
191 |
builder.append(" DEFAULT '"); |
|
192 |
builder.append(Objects.toString(column.getDefaultValue(),"")); |
|
193 |
builder.append("'"); |
|
194 |
} |
|
195 |
if (column.allowNulls()) { |
|
196 |
builder.append(" NULL"); |
|
197 |
} else { |
|
198 |
builder.append(" NOT NULL"); |
|
199 |
} |
|
200 |
sqls.add(builder.toString()); |
|
201 |
|
|
202 |
if (column.isPrimaryKey()) { |
|
203 |
String sql; |
|
204 | 160 |
sql = MessageFormat.format( |
205 |
"ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT \"{2}\" PRIMARY KEY ( \"{3}\" ) INDEX \"{4}\"", |
|
206 |
this.table().getSchema(), |
|
207 |
this.table().getName(), |
|
208 |
this.getConstrainName("PK", column.getName()), |
|
209 |
column.getName(), |
|
210 |
this.getConstrainName("IDX", column.getName()) |
|
161 |
"ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT IF NOT EXISTS \"{2}\" CHECK NVL2(\"{3}\", ST_GeometryTypeCode(\"{3}\") = {4,number,###} AND ST_CoordDim(\"{3}\") = {5,number,###}, TRUE)", |
|
162 |
this.table().getSchema(), |
|
163 |
this.table().getName(), |
|
164 |
constraint_name, |
|
165 |
column.getName(), |
|
166 |
(int) sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype()), |
|
167 |
(int) sqlgeometrynumdimension(column.getGeometryType(), column.getGeometrySubtype()) |
|
211 | 168 |
); |
212 |
sqls.add(sql);
|
|
213 |
} else if( column.isIndexed() ) {
|
|
214 |
String sql;
|
|
169 |
}
|
|
170 |
} else {
|
|
171 |
if ((int) sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype()) == 0) {
|
|
215 | 172 |
sql = MessageFormat.format( |
216 |
"CREATE INDEX IF NOT EXISTS \"{0}\" ON \"{1}\".\"{2}\" ( \"{3}\" )", |
|
217 |
this.getConstrainName("IDX", column.getName()), |
|
218 |
this.table().getSchema(), |
|
219 |
this.table().getName(), |
|
220 |
column.getName() |
|
173 |
"ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT IF NOT EXISTS \"{2}\" CHECK NVL2(\"{3}\", ST_CoordDim(\"{3}\") = {4,number,###} AND ST_SRID(\"{3}\") = {5,number,#####}, TRUE)", |
|
174 |
this.table().getSchema(), |
|
175 |
this.table().getName(), |
|
176 |
constraint_name, |
|
177 |
column.getName(), |
|
178 |
(int) sqlgeometrynumdimension(column.getGeometryType(), column.getGeometrySubtype()), |
|
179 |
(int) column.getGeometrySRSId() |
|
221 | 180 |
); |
222 |
sqls.add(sql); |
|
223 |
} |
|
224 |
|
|
225 |
if( column.isGeometry() ) { |
|
226 |
String constraint_name = this.getConstrainName("GEOM", column.getName()); |
|
227 |
String sql; |
|
228 |
if (column.getGeometrySRSId() == null) { |
|
229 |
if ((int) sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype()) == 0) { |
|
230 |
sql = MessageFormat.format( |
|
231 |
"ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT IF NOT EXISTS \"{2}\" CHECK NVL2(\"{3}\", ST_CoordDim(\"{3}\") = {4,number,###}, TRUE)", |
|
232 |
this.table().getSchema(), |
|
233 |
this.table().getName(), |
|
234 |
constraint_name, |
|
235 |
column.getName(), |
|
236 |
(int)sqlgeometrynumdimension(column.getGeometryType(), column.getGeometrySubtype()) |
|
237 |
); |
|
238 |
} else { |
|
239 |
sql = MessageFormat.format( |
|
240 |
"ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT IF NOT EXISTS \"{2}\" CHECK NVL2(\"{3}\", ST_GeometryTypeCode(\"{3}\") = {4,number,###} AND ST_CoordDim(\"{3}\") = {5,number,###}, TRUE)", |
|
241 |
this.table().getSchema(), |
|
242 |
this.table().getName(), |
|
243 |
constraint_name, |
|
244 |
column.getName(), |
|
245 |
(int)sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype()), |
|
246 |
(int)sqlgeometrynumdimension(column.getGeometryType(), column.getGeometrySubtype()) |
|
247 |
); |
|
248 |
} |
|
249 |
} else { |
|
250 |
if ((int) sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype()) == 0) { |
|
251 |
sql = MessageFormat.format( |
|
252 |
"ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT IF NOT EXISTS \"{2}\" CHECK NVL2(\"{3}\", ST_CoordDim(\"{3}\") = {4,number,###} AND ST_SRID(\"{3}\") = {5,number,#####}, TRUE)", |
|
253 |
this.table().getSchema(), |
|
254 |
this.table().getName(), |
|
255 |
constraint_name, |
|
256 |
column.getName(), |
|
257 |
(int)sqlgeometrynumdimension(column.getGeometryType(), column.getGeometrySubtype()), |
|
258 |
(int)column.getGeometrySRSId() |
|
259 |
); |
|
260 |
} else { |
|
261 |
sql = MessageFormat.format( |
|
262 |
"ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT IF NOT EXISTS \"{2}\" CHECK NVL2(\"{3}\", ST_GeometryTypeCode(\"{3}\") = {4,number,###} AND ST_CoordDim(\"{3}\") = {5,number,###} AND ST_SRID(\"{3}\") = {6,number,#####}, TRUE)", |
|
263 |
this.table().getSchema(), |
|
264 |
this.table().getName(), |
|
265 |
constraint_name, |
|
266 |
column.getName(), |
|
267 |
(int)sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype()), |
|
268 |
(int)sqlgeometrynumdimension(column.getGeometryType(), column.getGeometrySubtype()), |
|
269 |
(int)column.getGeometrySRSId() |
|
270 |
); |
|
271 |
} |
|
272 |
} |
|
273 |
sqls.add(sql); |
|
274 |
} |
|
275 |
if( StringUtils.equals(local_drop_primary_key_column, column.getName()) ) { |
|
276 |
// Si la columna de la que tenemos que quitar la pk la acabamos de a?adir |
|
277 |
// ya se habra a?adido como toca, y no tendremos que quitar luego la pk. |
|
278 |
local_drop_primary_key_column = null; |
|
279 |
} |
|
280 |
} |
|
281 |
|
|
282 |
if( StringUtils.isNotBlank(local_drop_primary_key_column) ) { |
|
283 |
String sql; |
|
284 |
sql = MessageFormat.format( |
|
285 |
"ALTER TABLE \"{0}\".\"{1}\" DROP CONSTRAINT IF EXISTS \"{2}\"", |
|
286 |
this.table().getSchema(), |
|
287 |
this.table().getName(), |
|
288 |
this.getConstrainName("PK", local_drop_primary_key_column) |
|
289 |
); |
|
290 |
sqls.add(sql); |
|
291 |
sql = MessageFormat.format( |
|
292 |
"DROP INDEX IF EXISTS \"{0}\"", |
|
293 |
this.getConstrainName("IDX", local_drop_primary_key_column) |
|
294 |
); |
|
295 |
sqls.add(sql); |
|
296 |
} |
|
297 |
|
|
298 |
for (ColumnDescriptor column : alters) { |
|
299 |
StringBuilder builder = new StringBuilder(); |
|
300 |
builder.append("ALTER TABLE "); |
|
301 |
builder.append(this.table.toString(formatter)); |
|
302 |
builder.append(" ALTER COLUMN "); |
|
303 |
builder.append(as_identifier(column.getName())); |
|
304 |
builder.append(" "); |
|
305 |
builder.append( |
|
306 |
sqltype( |
|
307 |
column.getType(), |
|
308 |
column.getSize(), |
|
309 |
column.getPrecision(), |
|
310 |
column.getScale(), |
|
311 |
column.getGeometryType(), |
|
312 |
column.getGeometrySubtype() |
|
313 |
) |
|
314 |
); |
|
315 |
if (column.getDefaultValue() == null) { |
|
316 |
if (column.allowNulls()) { |
|
317 |
builder.append(" DEFAULT NULL"); |
|
318 |
} |
|
319 | 181 |
} else { |
320 |
builder.append(" DEFAULT '"); |
|
321 |
builder.append(column.getDefaultValue().toString()); |
|
322 |
builder.append("'"); |
|
323 |
} |
|
324 |
if( column.isAutomatic() ) { |
|
325 |
builder.append(" AUTO_INCREMENT"); |
|
326 |
} |
|
327 |
if (column.allowNulls()) { |
|
328 |
builder.append(" NULL"); |
|
329 |
} else { |
|
330 |
builder.append(" NOT NULL"); |
|
331 |
} |
|
332 |
sqls.add(builder.toString()); |
|
333 |
if (column.isPrimaryKey()) { |
|
334 |
String sql; |
|
335 | 182 |
sql = MessageFormat.format( |
336 |
"ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT \"{2}\" PRIMARY KEY ( \"{3}\" ) INDEX \"{4}\"", |
|
337 |
this.table().getSchema(), |
|
338 |
this.table().getName(), |
|
339 |
this.getConstrainName("PK", column.getName()), |
|
340 |
column.getName(), |
|
341 |
this.getConstrainName("IDX", column.getName()) |
|
183 |
"ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT IF NOT EXISTS \"{2}\" CHECK NVL2(\"{3}\", ST_GeometryTypeCode(\"{3}\") = {4,number,###} AND ST_CoordDim(\"{3}\") = {5,number,###} AND ST_SRID(\"{3}\") = {6,number,#####}, TRUE)", |
|
184 |
this.table().getSchema(), |
|
185 |
this.table().getName(), |
|
186 |
constraint_name, |
|
187 |
column.getName(), |
|
188 |
(int) sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype()), |
|
189 |
(int) sqlgeometrynumdimension(column.getGeometryType(), column.getGeometrySubtype()), |
|
190 |
(int) column.getGeometrySRSId() |
|
342 | 191 |
); |
343 |
sqls.add(sql); |
|
344 | 192 |
} |
345 |
if( column.isGeometry() ) { |
|
346 |
String sql; |
|
347 |
sql = MessageFormat.format( |
|
348 |
"ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT IF NOT EXISTS \"{2}\" CHECK ST_CoordDim(\"{3}\") = {4}", |
|
349 |
this.table().getSchema(), |
|
350 |
this.table().getName(), |
|
351 |
this.getConstrainName("DIM", column.getName()), |
|
352 |
column.getName(), |
|
353 |
sqlgeometrynumdimension(column.getGeometryType(),column.getGeometrySubtype()) |
|
354 |
); |
|
355 |
sqls.add(sql); |
|
356 |
} |
|
357 | 193 |
} |
358 |
for (Pair<String,String> pair : renames) { |
|
359 |
StringBuilder builder = new StringBuilder(); |
|
360 |
builder.append("ALTER TABLE "); |
|
361 |
builder.append(this.table.toString(formatter)); |
|
362 |
builder.append(" RENAME COLUMN "); |
|
363 |
builder.append(as_identifier(pair.getLeft())); |
|
364 |
builder.append(" TO "); |
|
365 |
builder.append(as_identifier(pair.getRight())); |
|
366 |
sqls.add(builder.toString()); |
|
367 |
} |
|
368 |
return sqls; |
|
194 |
return Collections.singletonList(sql); |
|
369 | 195 |
} |
370 | 196 |
|
371 | 197 |
} |
372 |
|
|
198 |
|
|
373 | 199 |
protected class H2SpatialCreateTableBuilder extends CreateTableBuilderBase { |
374 | 200 |
|
375 | 201 |
@Override |
Also available in: Unified diff