gvsig-oracle / org.gvsig.oracle / trunk / org.gvsig.oracle / org.gvsig.oracle.provider / src / main / java / org / gvsig / oracle / dal / OracleSQLBuilder.java @ 916
History | View | Annotate | Download (52.2 KB)
1 |
/**
|
---|---|
2 |
* gvSIG. Desktop Geographic Information System.
|
3 |
*
|
4 |
* Copyright (C) 2007-2013 gvSIG Association.
|
5 |
*
|
6 |
* This program is free software; you can redistribute it and/or
|
7 |
* modify it under the terms of the GNU General Public License
|
8 |
* as published by the Free Software Foundation; either version 3
|
9 |
* of the License, or (at your option) any later version.
|
10 |
*
|
11 |
* This program is distributed in the hope that it will be useful,
|
12 |
* but WITHOUT ANY WARRANTY; without even the implied warranty of
|
13 |
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
14 |
* GNU General Public License for more details.
|
15 |
*
|
16 |
* You should have received a copy of the GNU General Public License
|
17 |
* along with this program; if not, write to the Free Software
|
18 |
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
|
19 |
* MA 02110-1301, USA.
|
20 |
*
|
21 |
* For any additional information, do not hesitate to contact us
|
22 |
* at info AT gvsig.com, or visit our website www.gvsig.com.
|
23 |
*/
|
24 |
package org.gvsig.oracle.dal; |
25 |
|
26 |
import java.io.ByteArrayInputStream; |
27 |
import java.io.IOException; |
28 |
import java.io.InputStream; |
29 |
import java.io.OutputStream; |
30 |
import java.sql.Connection; |
31 |
import java.sql.PreparedStatement; |
32 |
import java.sql.SQLException; |
33 |
import java.sql.Time; |
34 |
import java.sql.Timestamp; |
35 |
import java.text.MessageFormat; |
36 |
import java.util.ArrayList; |
37 |
import java.util.List; |
38 |
import java.util.Objects; |
39 |
import oracle.sql.BLOB; |
40 |
import org.apache.commons.dbcp.DelegatingConnection; |
41 |
import org.apache.commons.lang3.BooleanUtils; |
42 |
import org.apache.commons.lang3.StringUtils; |
43 |
import org.apache.commons.lang3.tuple.Pair; |
44 |
import org.gvsig.expressionevaluator.DelegatedGeometryExpressionBuilder; |
45 |
import org.gvsig.expressionevaluator.ExpressionBuilder; |
46 |
import static org.gvsig.expressionevaluator.ExpressionBuilder.PARAMETER_TYPE_CONSTANT; |
47 |
import static org.gvsig.expressionevaluator.ExpressionBuilder.PARAMETER_TYPE_VARIABLE; |
48 |
import static org.gvsig.expressionevaluator.ExpressionBuilder.VALUE_NULL; |
49 |
import org.gvsig.expressionevaluator.ExpressionUtils; |
50 |
import org.gvsig.expressionevaluator.Formatter; |
51 |
import org.gvsig.expressionevaluator.GeometryExpressionBuilder; |
52 |
import org.gvsig.expressionevaluator.GeometryExpressionBuilderHelper.GeometrySupportType; |
53 |
import static org.gvsig.expressionevaluator.GeometryExpressionBuilderHelper.GeometrySupportType.WKT; |
54 |
import org.gvsig.fmap.dal.DataTypes; |
55 |
import org.gvsig.fmap.dal.SQLBuilder; |
56 |
import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor; |
57 |
import org.gvsig.fmap.dal.feature.FeatureType; |
58 |
import org.gvsig.fmap.dal.feature.spi.FeatureProvider; |
59 |
import org.gvsig.fmap.dal.feature.spi.SQLBuilderBase; |
60 |
import org.gvsig.fmap.dal.store.jdbc.JDBCConnectionParameters; |
61 |
import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper; |
62 |
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
63 |
import org.gvsig.fmap.geom.Geometry; |
64 |
import org.gvsig.fmap.geom.GeometryUtils; |
65 |
import org.gvsig.fmap.geom.primitive.Envelope; |
66 |
import org.gvsig.oracle.dal.expressionbuilderformatter.OracleFormatter; |
67 |
import static org.gvsig.oracle.dal.expressionbuilderformatter.OracleGeometryParameter.ORACLE_GEOMETRY_PARAMETER; |
68 |
import org.gvsig.tools.ToolsLocator; |
69 |
import org.gvsig.tools.dataTypes.Coercion; |
70 |
import org.gvsig.tools.dataTypes.DataType; |
71 |
import org.gvsig.tools.dataTypes.DataTypeUtils; |
72 |
import org.gvsig.tools.dataTypes.DataTypesManager; |
73 |
import org.gvsig.tools.dispose.Disposable; |
74 |
import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference; |
75 |
|
76 |
public class OracleSQLBuilder extends JDBCSQLBuilderBase { |
77 |
|
78 |
protected static final String ADD_SERIAL_COLUMN_SEQUENCE_QUERY = "CREATE SEQUENCE \"{0}\""; |
79 |
protected static final String ADD_SERIAL_COLUMN_TRIGGER_QUERY = "CREATE OR REPLACE TRIGGER \"{0}\" BEFORE INSERT ON {1} FOR EACH ROW " |
80 |
+ "BEGIN SELECT \"{3}\".NEXTVAL INTO :new.\"{2}\" FROM dual; END;";
|
81 |
protected static final String DELETE_FROM_OGIS_GEOMETRY_COLUMNS_QUERY = "DELETE FROM MDSYS.OGIS_GEOMETRY_COLUMNS " |
82 |
+ "WHERE F_TABLE_SCHEMA = ''{0}'' AND F_TABLE_NAME = ''{1}'' AND F_GEOMETRY_COLUMN = ''{2}''";
|
83 |
protected static final String INSERT_OGIS_GEOMETRY_COLUMNS_QUERY = "INSERT INTO MDSYS.OGIS_GEOMETRY_COLUMNS " |
84 |
+ "(F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN, GEOMETRY_TYPE) VALUES "
|
85 |
+ "(''{0}'', ''{1}'', ''{2}'', {3})";
|
86 |
private final String quote_for_identifiers; |
87 |
private final String quote_for_strings; |
88 |
public static final String ST_GEOMFROMTEXT = "SDO_GEOMETRY(({0}), ({1}))"; |
89 |
public static final String ST_GEOMFROMWKB = "SDO_GEOMETRY(({0}), ({1}))"; |
90 |
public static final String ST_GEOMFROMEWKB = "SDO_GEOMETRY(({0}), ({1}))"; |
91 |
public static final String FORMAT_OPERATOR_NOTISNULL = "( (({0}) IS NOT NULL) )"; |
92 |
public static final String FORMAT_OPERATOR_AND = "( ({0}) AND ({1}) )"; |
93 |
public static final String FORMAT_OPERATOR_OR = "( ({0}) OR ({1}) )"; |
94 |
public static final String FORMAT_OPERATOR_NE = "( ({0}) != ({1}) )"; |
95 |
public static final String FORMAT_OPERATOR_ILIKE = "( LOWER({0}) LIKE LOWER({1}) )"; |
96 |
public static final String FORMAT_OPERATOR_CONCAT = "{0} + {1}"; |
97 |
|
98 |
public static final int BOOLEAN_STRING_DEFAULT_LENGTH = 1; |
99 |
|
100 |
private static final Geometry EMPTY_POINT = GeometryUtils.createPoint(0, 0); |
101 |
|
102 |
protected Formatter formatter = null; |
103 |
|
104 |
public OracleSQLBuilder(JDBCHelper helper) {
|
105 |
super(helper);
|
106 |
|
107 |
this.defaultSchema = ""; |
108 |
this.allowAutomaticValues = true; |
109 |
this.geometrySupportType = this.helper.getGeometrySupportType(); |
110 |
this.hasSpatialFunctions = this.helper.hasSpatialFunctions(); |
111 |
this.constant_true = "TRUE"; |
112 |
this.constant_false = "FALSE"; |
113 |
this.quote_for_identifiers = "\""; |
114 |
this.quote_for_strings = "\'"; |
115 |
|
116 |
this.type_boolean = "CHAR(1)"; |
117 |
this.type_byte = "NUMBER(" + (DataType.BYTE_MAX_PRECISION) + ",0)"; |
118 |
this.type_bytearray = "BLOB"; |
119 |
this.type_geometry = "SDO_GEOMETRY"; |
120 |
this.type_char = "CHAR(1)"; |
121 |
this.type_date = "DATE"; |
122 |
this.type_double = "BINARY_DOUBLE"; //float con 53 bits de mantisa = float(54) |
123 |
this.type_decimal_p = "NUMBER({0})"; |
124 |
this.type_decimal_ps = "NUMBER({0},{1})"; |
125 |
// this.type_bigdecimal = "NUMBER({0},{1})";
|
126 |
this.type_float = "BINARY_FLOAT"; //float con 24 bits de mantisa = float(24) |
127 |
this.type_int = "NUMBER(" + (DataType.INT_MAX_PRECISION - 1) + ",0)"; |
128 |
this.type_long = "NUMBER(" + (DataType.LONG_MAX_PRECISION - 1) + ",0)"; |
129 |
this.type_string = "NCLOB"; |
130 |
this.type_string_p = "NVARCHAR2({0,Number,#######})"; |
131 |
this.type_time = "TIMESTAMP"; |
132 |
this.type_timestamp = "TIMESTAMP"; |
133 |
this.type_version = "VARCHAR2(30)"; |
134 |
this.type_URI = "NVARCHAR2(2048)"; |
135 |
this.type_URL = "NVARCHAR2(2048)"; |
136 |
this.type_FILE = "NVARCHAR2(2048)"; |
137 |
this.type_FOLDER = "NVARCHAR2(2048)"; |
138 |
|
139 |
STMT_DELETE_FROM_table_WHERE_expresion = "DELETE FROM {0} WHERE {1}";
|
140 |
STMT_DELETE_FROM_table = "DELETE FROM {0}";
|
141 |
STMT_INSERT_INTO_table_columns_VALUES_values = "INSERT INTO {0} ( {1} ) VALUES ( {2} )";
|
142 |
STMT_UPDATE_TABLE_STATISTICS_table = "VACUUM ANALYZE {0}"; // FIXME: ALTER INDEX [schema.]index REBUILD INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS (''LAYER_GTYPE={3}''; |
143 |
// config.remove_functionality(SQLConfig.UPDATE_TABLE_STATISTICS_table; // FIXME
|
144 |
STMT_DROP_TABLE_table = "DROP TABLE {0}";
|
145 |
STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_table = null;
|
146 |
STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table = null;
|
147 |
STMT_UPDATE_table_SET_columnsAndValues_WHERE_expresion = "UPDATE {0} SET {1} WHERE {2}";
|
148 |
STMT_UPDATE_table_SET_columnsAndValues = "UPDATE {0} SET {1}";
|
149 |
STMT_GRANT_privileges_ON_table_TO_role = "GRANT {0} ON {1} TO {2}";
|
150 |
|
151 |
} |
152 |
|
153 |
public OracleHelper getHelper() {
|
154 |
return (OracleHelper) super.getHelper(); |
155 |
} |
156 |
|
157 |
@Override
|
158 |
public Formatter formatter() { |
159 |
if (this.formatter == null) { |
160 |
this.formatter = new OracleFormatter(this); |
161 |
} |
162 |
return this.formatter; |
163 |
} |
164 |
|
165 |
private class OracleGeometryExpressionBuilder extends DelegatedGeometryExpressionBuilder { |
166 |
|
167 |
public OracleGeometryExpressionBuilder(GeometryExpressionBuilder expressionBuilder) {
|
168 |
super(expressionBuilder);
|
169 |
} |
170 |
|
171 |
@Override
|
172 |
public Formatter<Value> formatter() { |
173 |
return OracleSQLBuilder.this.formatter();
|
174 |
} |
175 |
|
176 |
} |
177 |
|
178 |
private OracleGeometryExpressionBuilder oracleExpressionBuilder = null; |
179 |
|
180 |
@Override
|
181 |
public GeometryExpressionBuilder expression() {
|
182 |
if (this.oracleExpressionBuilder == null) { |
183 |
this.oracleExpressionBuilder = new OracleGeometryExpressionBuilder(super.expression()); |
184 |
} |
185 |
return this.oracleExpressionBuilder; |
186 |
} |
187 |
|
188 |
@Override
|
189 |
public String default_schema() { |
190 |
JDBCConnectionParameters params = this.helper.getConnectionParameters();
|
191 |
if (params != null) { |
192 |
if (StringUtils.isBlank(params.getSchema())) {
|
193 |
if ((StringUtils.isNotBlank(params.getUser()))) {
|
194 |
return params.getUser();
|
195 |
} |
196 |
} else {
|
197 |
return params.getSchema();
|
198 |
} |
199 |
} |
200 |
return this.defaultSchema; |
201 |
} |
202 |
|
203 |
public class OracleUpdateTableStatisticsBuilderBase extends UpdateTableStatisticsBuilderBase { |
204 |
|
205 |
@Override
|
206 |
public List<String> toStrings() { |
207 |
List<String> sqls = new ArrayList<>(); |
208 |
|
209 |
// String name = as_identifier(this.table.getName());
|
210 |
// if (table.has_schema()) {
|
211 |
// name = as_identifier(this.table.getSchema()) + "." + name;
|
212 |
// }
|
213 |
String sql = MessageFormat.format( |
214 |
"ANALYZE TABLE {0} COMPUTE STATISTICS",
|
215 |
this.table.toString()
|
216 |
); |
217 |
if (!StringUtils.isEmpty(sql)) {
|
218 |
sqls.add(sql); |
219 |
} |
220 |
|
221 |
return sqls;
|
222 |
} |
223 |
} |
224 |
|
225 |
public String getSerialSequenceName(String tableName, String columnName) { |
226 |
return ("GVSEQ_" + tableName + "_" + columnName).toUpperCase(); |
227 |
} |
228 |
|
229 |
public String getSerialTriggerName(String tableName, String columnName) { |
230 |
return ("GVSER_" + tableName + "_" + columnName).toUpperCase(); |
231 |
} |
232 |
|
233 |
protected class OracleCreateTableBuilder extends CreateTableBuilderBase { |
234 |
|
235 |
@Override
|
236 |
public List<String> toStrings(Formatter formatter) { |
237 |
|
238 |
List<String> sqls = new ArrayList<>(); |
239 |
StringBuilder builder = new StringBuilder(); |
240 |
|
241 |
builder.append("CREATE TABLE ");
|
242 |
builder.append(this.table().toString());
|
243 |
builder.append(" (");
|
244 |
boolean first = true; |
245 |
|
246 |
ArrayList<String> pks = new ArrayList<>(); |
247 |
boolean automaticPrimaryKey = false; |
248 |
|
249 |
for (ColumnDescriptor column : columns) {
|
250 |
if (first) {
|
251 |
first = false;
|
252 |
} else {
|
253 |
builder.append(", ");
|
254 |
} |
255 |
if (column.isGeometry()) {
|
256 |
builder.append(as_identifier(column.getName())); //.toUpperCase()));
|
257 |
builder.append(" ");
|
258 |
builder.append(" SDO_GEOMETRY");
|
259 |
if (!column.allowNulls()) {
|
260 |
builder.append(" NOT NULL");
|
261 |
} |
262 |
} else {
|
263 |
builder.append(as_identifier(column.getName())); |
264 |
builder.append(" ");
|
265 |
builder.append(sqltype( |
266 |
column.getType(), |
267 |
column.getSize(), |
268 |
column.getPrecision(), |
269 |
column.getScale(), |
270 |
column.getGeometryType(), |
271 |
column.getGeometrySubtype())); |
272 |
|
273 |
// if (column.getDefaultValue() == null) {
|
274 |
// if (column.allowNulls()) {
|
275 |
// builder.append(" DEFAULT NULL");
|
276 |
// }
|
277 |
// } else {
|
278 |
// builder.append(" DEFAULT '");
|
279 |
// builder.append(column.getDefaultValue().toString());
|
280 |
// builder.append("'");
|
281 |
// }
|
282 |
// if (column.allowNulls()) {
|
283 |
// builder.append(" NULL");
|
284 |
// } else {
|
285 |
// builder.append(" NOT NULL");
|
286 |
// }
|
287 |
} |
288 |
if (column.isPrimaryKey()) {
|
289 |
if (column.isAutomatic()) {
|
290 |
// if the pk is automatic, ignore any other PK
|
291 |
automaticPrimaryKey = true;
|
292 |
pks.clear(); |
293 |
pks.add(column.getName()); |
294 |
} else if (!automaticPrimaryKey) { |
295 |
pks.add(column.getName()); |
296 |
} |
297 |
} else {
|
298 |
if (column.isAutomatic()) {
|
299 |
// builder.append(" AUTO_INCREMENT");
|
300 |
builder.append(" GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1)");
|
301 |
} |
302 |
if (column.getDefaultValue() == null |
303 |
|| ExpressionUtils.isDynamicText(Objects.toString(column.getDefaultValue(), null))) {
|
304 |
if (column.allowNulls()) {
|
305 |
builder.append(" DEFAULT NULL");
|
306 |
} |
307 |
} else {
|
308 |
switch (column.getType()) {
|
309 |
case DataTypes.TIMESTAMP:
|
310 |
builder.append(" DEFAULT ( TIMESTAMP '");
|
311 |
Timestamp dtimestamp = (Timestamp) DataTypeUtils.toTimestamp(column.getDefaultValue()); |
312 |
builder.append(MessageFormat.format("{0,date,yyyy-MM-dd HH:mm:ss.S}", dtimestamp)); |
313 |
builder.append("' )");
|
314 |
break;
|
315 |
case DataTypes.TIME:
|
316 |
builder.append(" DEFAULT ( TIME '");
|
317 |
Time dtime = (Time) DataTypeUtils.toTime(column.getDefaultValue()); |
318 |
builder.append(MessageFormat.format("{0,date,HH:mm:ss}", dtime)); |
319 |
builder.append("' )");
|
320 |
break;
|
321 |
case DataTypes.DATE:
|
322 |
builder.append(" DEFAULT ( DATE '");
|
323 |
java.sql.Date ddate = (java.sql.Date) DataTypeUtils.toDate(column.getDefaultValue()); |
324 |
builder.append(MessageFormat.format("{0,date,yyyy-MM-dd}", ddate)); |
325 |
builder.append("' )");
|
326 |
break;
|
327 |
default:
|
328 |
builder.append(" DEFAULT '");
|
329 |
builder.append(Objects.toString(column.getDefaultValue(), ""));
|
330 |
builder.append("'");
|
331 |
} |
332 |
} |
333 |
} |
334 |
} |
335 |
builder.append(" )");
|
336 |
sqls.add(builder.toString()); |
337 |
|
338 |
if (pks.size() > 0) { |
339 |
builder = new StringBuilder("ALTER TABLE "); |
340 |
builder.append(this.table().toString());
|
341 |
builder.append(" ADD PRIMARY KEY (");
|
342 |
for (int i = 0; i < pks.size(); i++) { |
343 |
if (i != 0) { |
344 |
builder.append(", ");
|
345 |
} |
346 |
builder.append(as_identifier(pks.get(i))); |
347 |
} |
348 |
builder.append(")");
|
349 |
sqls.add(builder.toString()); |
350 |
} |
351 |
|
352 |
for (ColumnDescriptor column : columns) {
|
353 |
if (column.isAutomatic()) {
|
354 |
String sequenceName = getSerialSequenceName(this.table.getName(), column.getName()); |
355 |
String sql = MessageFormat.format( |
356 |
ADD_SERIAL_COLUMN_SEQUENCE_QUERY, |
357 |
sequenceName |
358 |
); |
359 |
sqls.add(sql); |
360 |
String autoTriggerName = getSerialTriggerName(this.table.getName(), column.getName()); |
361 |
sql = MessageFormat.format(
|
362 |
ADD_SERIAL_COLUMN_TRIGGER_QUERY, |
363 |
autoTriggerName, |
364 |
this.table().toString(),
|
365 |
column.getName(), |
366 |
sequenceName |
367 |
); |
368 |
sqls.add(sql); |
369 |
} |
370 |
} |
371 |
|
372 |
for (ColumnDescriptor column : columns) {
|
373 |
if (column.isGeometry()) {
|
374 |
/**
|
375 |
* Inserting the geomtype and column on
|
376 |
* OGIS_GEOMETRY_COLUMNS.
|
377 |
*
|
378 |
* It is useful to retrieve the geometry type when the
|
379 |
* spatial index can't be defined (for instance users
|
380 |
* creating tables on a different schema).
|
381 |
*
|
382 |
* Note: We don't insert the SRID because it is unusable as
|
383 |
* defined on OGIS_GEOMETRY_COLUMNS, as it must reference an
|
384 |
* existing SRID on OGIS_SPATIAL_REFERENCE_SYSTEM.
|
385 |
* OGIS_SPATIAL_REFERENCE_SYSTEM is empty and it has a
|
386 |
* trigger that duplicates on SDO_COORD_REF_SYS any SRID
|
387 |
* inserted on OGIS_SPATIAL_REFERENCE_SYSTEM.
|
388 |
* SDO_COORD_REF_SYS is not empty and we don't want to
|
389 |
* duplicate the SRID definitions!!
|
390 |
*/
|
391 |
int gvsigType = column.getGeometryType();
|
392 |
int gvsigSubtype = column.getGeometrySubtype();
|
393 |
String sql = MessageFormat.format( // delete before inserting to avoid creating duplicates |
394 |
DELETE_FROM_OGIS_GEOMETRY_COLUMNS_QUERY, |
395 |
this.table().getSchema(),
|
396 |
this.table().getName().toUpperCase(),
|
397 |
column.getName() |
398 |
); |
399 |
sqls.add(sql); |
400 |
sql = MessageFormat.format(
|
401 |
INSERT_OGIS_GEOMETRY_COLUMNS_QUERY, |
402 |
this.table().getSchema(),
|
403 |
this.table().getName().toUpperCase(),
|
404 |
column.getName(), |
405 |
Integer.toString(GeometryTypeUtils.toSFSGeometryTypeCode(gvsigType, gvsigSubtype))
|
406 |
); |
407 |
sqls.add(sql); |
408 |
Envelope tablebbox = column.getTableBBox(); |
409 |
if (tablebbox != null) { |
410 |
sql = SpatialIndexUtils.getSQLDeleteUserMetadata( |
411 |
this.table().getSchema(),
|
412 |
this.table().getName(),
|
413 |
column.getName() |
414 |
); |
415 |
sqls.add(sql); |
416 |
sql = SpatialIndexUtils.getSQLInsertUserMetadata( |
417 |
this.table().getName(),
|
418 |
tablebbox, |
419 |
column.getName(), |
420 |
(int) column.getGeometrySRSId()
|
421 |
); |
422 |
sqls.add(sql); |
423 |
sql = SpatialIndexUtils.getSQLCreateSpatialIndex( |
424 |
this.table().getName(),
|
425 |
column.getName(), |
426 |
gvsigType, |
427 |
gvsigSubtype |
428 |
); |
429 |
sqls.add(sql); |
430 |
} |
431 |
} |
432 |
} |
433 |
return sqls;
|
434 |
} |
435 |
} |
436 |
|
437 |
public class OracleSelectBuilder extends SelectBuilderBase { |
438 |
|
439 |
@Override
|
440 |
public String toString(Formatter<ExpressionBuilder.Value> formatter) { |
441 |
StringBuilder builder = new StringBuilder(); |
442 |
if (!isValid(builder)) {
|
443 |
throw new IllegalStateException(builder.toString()); |
444 |
} |
445 |
boolean involveWithLimitOrOffset = ((this.has_offset() || this.has_limit()) |
446 |
&& (this.has_aggregate_functions() || this.has_group_by() || this.has_order_by())); |
447 |
|
448 |
if (involveWithLimitOrOffset) {
|
449 |
/**
|
450 |
* Using ROWNUM for limit & offset, compatible with any Oracle
|
451 |
* version This requires wrapping the original query as shown in
|
452 |
* the example: select * from ( select a.*, ROWNUM rnum from (
|
453 |
* <select statement with order by clause>
|
454 |
* ) a where ROWNUM <= MAX_ROW
|
455 |
* ) where rnum >= MIN_ROW because ROWNUM is considered before
|
456 |
* applying order by and group by
|
457 |
*/
|
458 |
builder.append("SELECT * FROM ( ");
|
459 |
if (this.has_offset()) { |
460 |
builder.append("SELECT a.*, ROWNUM rnum FROM ( ");
|
461 |
} |
462 |
} |
463 |
|
464 |
builder.append("SELECT ");
|
465 |
if (this.distinct) { |
466 |
builder.append("DISTINCT ");
|
467 |
} |
468 |
boolean first = true; |
469 |
for (SelectColumnBuilder column : columns) {
|
470 |
if (first) {
|
471 |
first = false;
|
472 |
} else {
|
473 |
builder.append(", ");
|
474 |
} |
475 |
builder.append(column.toString(formatter)); |
476 |
} |
477 |
|
478 |
if (this.has_from()) { |
479 |
builder.append(" FROM ");
|
480 |
builder.append(this.from.toString(formatter));
|
481 |
} |
482 |
if (this.has_where()) { |
483 |
builder.append(" WHERE ");
|
484 |
builder.append(this.where.toString(formatter));
|
485 |
} |
486 |
|
487 |
if (!involveWithLimitOrOffset) {
|
488 |
if (this.has_limit() || this.has_offset()) { |
489 |
if (this.has_where()) { |
490 |
builder.append(" AND ");
|
491 |
} else {
|
492 |
builder.append(" WHERE ");
|
493 |
} |
494 |
} |
495 |
|
496 |
if (this.has_limit()) { |
497 |
builder.append(" ROWNUM <= ");
|
498 |
if (this.has_offset()) { |
499 |
builder.append(this.offset + this.limit); |
500 |
} else {
|
501 |
builder.append(this.limit);
|
502 |
} |
503 |
} |
504 |
if (this.has_offset()) { |
505 |
builder.append(" AND rnum > ");
|
506 |
builder.append(this.offset);
|
507 |
} |
508 |
|
509 |
if (this.has_group_by()) { |
510 |
builder.append(" GROUP BY ");
|
511 |
builder.append(this.groupColumn.get(0).toString(formatter)); |
512 |
for (int i = 1; i < groupColumn.size(); i++) { |
513 |
builder.append(", ");
|
514 |
builder.append(this.groupColumn.get(i).toString(formatter));
|
515 |
} |
516 |
} |
517 |
|
518 |
if (this.has_order_by()) { |
519 |
builder.append(" ORDER BY ");
|
520 |
first = true;
|
521 |
for (OrderByBuilder item : this.order_by) { |
522 |
if (first) {
|
523 |
first = false;
|
524 |
} else {
|
525 |
builder.append(", ");
|
526 |
} |
527 |
builder.append(item.toString(formatter)); |
528 |
} |
529 |
} |
530 |
|
531 |
} |
532 |
|
533 |
if (involveWithLimitOrOffset) {
|
534 |
if (this.has_order_by()) { |
535 |
builder.append(" ORDER BY ");
|
536 |
first = true;
|
537 |
for (OrderByBuilder item : this.order_by) { |
538 |
if (first) {
|
539 |
first = false;
|
540 |
} else {
|
541 |
builder.append(", ");
|
542 |
} |
543 |
builder.append(item.toString(formatter)); |
544 |
} |
545 |
} |
546 |
builder.append(") a");
|
547 |
if (this.has_limit()) { |
548 |
builder.append(" WHERE ROWNUM <= ");
|
549 |
if (this.has_offset()) { |
550 |
builder.append(this.offset + this.limit); |
551 |
} else {
|
552 |
builder.append(this.limit);
|
553 |
} |
554 |
} |
555 |
if (this.has_offset()) { |
556 |
builder.append(" )");
|
557 |
builder.append(" WHERE rnum > ");
|
558 |
builder.append(this.offset);
|
559 |
} |
560 |
|
561 |
} |
562 |
return builder.toString();
|
563 |
} |
564 |
|
565 |
} |
566 |
|
567 |
@Override
|
568 |
public TableNameBuilder createTableNameBuilder() {
|
569 |
return new OracleTableNameBuilder(); |
570 |
} |
571 |
|
572 |
@Override
|
573 |
protected CreateTableBuilder createCreateTableBuilder() {
|
574 |
return new OracleCreateTableBuilder(); |
575 |
} |
576 |
|
577 |
@Override
|
578 |
public SelectBuilder createSelectBuilder() {
|
579 |
return new OracleSelectBuilder(); |
580 |
} |
581 |
|
582 |
@Override
|
583 |
protected FromBuilder createFromBuilder() {
|
584 |
return new OracleFromBuilder(); |
585 |
} |
586 |
|
587 |
@Override
|
588 |
protected UpdateTableStatisticsBuilder createUpdateTableStatisticsBuilder() {
|
589 |
return new OracleUpdateTableStatisticsBuilderBase(); |
590 |
} |
591 |
|
592 |
public String getProviderTableName(TableReference table) { |
593 |
return this.getProviderTableName(table.getTable()); |
594 |
} |
595 |
|
596 |
private String getProviderTableName(String tableName) { |
597 |
boolean forceUpperCase = true; |
598 |
OracleConnectionParameters params = getHelper().getConnectionParameters(); |
599 |
if (params != null) { |
600 |
forceUpperCase = params.getForceUppercaseInTableName(); |
601 |
} |
602 |
if( forceUpperCase ) {
|
603 |
return StringUtils.upperCase(tableName);
|
604 |
} |
605 |
return tableName;
|
606 |
} |
607 |
|
608 |
protected class OracleTableNameBuilder extends TableNameBuilderBase { |
609 |
|
610 |
@Override
|
611 |
public boolean has_database() { |
612 |
return false; |
613 |
} |
614 |
|
615 |
@Override
|
616 |
public String toString() { |
617 |
return this.toString(formatter()); |
618 |
} |
619 |
|
620 |
protected String databaseName2provider() { |
621 |
return null; |
622 |
} |
623 |
|
624 |
protected String schemaName2provider() { |
625 |
return this.schemaName; |
626 |
} |
627 |
|
628 |
protected String tableName2provider() { |
629 |
return getProviderTableName(this.tableName); |
630 |
} |
631 |
|
632 |
|
633 |
@Override
|
634 |
public String toString(Formatter<ExpressionBuilder.Value> formatter) { |
635 |
if (formatter != null && formatter.canApply(this)) { |
636 |
return formatter.format(this); |
637 |
} |
638 |
if (this.has_schema()) { |
639 |
return as_identifier(this.schemaName2provider()) + "." |
640 |
+ as_identifier(this.tableName2provider());
|
641 |
} |
642 |
|
643 |
OracleConnectionParameters params = getHelper().getConnectionParameters(); |
644 |
if (params != null) { |
645 |
if ((StringUtils.isNotBlank(params.getUser()))) {
|
646 |
return as_identifier(params.getUser()) + "." |
647 |
+ as_identifier(this.tableName2provider());
|
648 |
} |
649 |
} |
650 |
return as_identifier(this.tableName2provider()); |
651 |
} |
652 |
} |
653 |
|
654 |
public class OracleFromBuilder extends FromBuilderBase { |
655 |
|
656 |
@Override
|
657 |
public String toString(Formatter<ExpressionBuilder.Value> formatter) { |
658 |
if (formatter != null && formatter.canApply(this)) { |
659 |
return formatter.format(this); |
660 |
} |
661 |
if (!StringUtils.isEmpty(passthrough)) {
|
662 |
return passthrough;
|
663 |
} |
664 |
if (!StringUtils.isEmpty(subquery)) {
|
665 |
return "( " + this.subquery + ") " + quote_for_identifiers() + "_subquery_alias_" + quote_for_identifiers() + " "; |
666 |
} |
667 |
if (this.joins == null || this.joins.isEmpty()) { |
668 |
return this.tableName.toString(formatter); |
669 |
} |
670 |
StringBuilder builder = new StringBuilder(); |
671 |
builder.append(this.tableName.toString(formatter));
|
672 |
for (JoinBuilder join : this.joins) { |
673 |
builder.append(" ");
|
674 |
builder.append(join.toString(formatter)); |
675 |
} |
676 |
return builder.toString();
|
677 |
} |
678 |
} |
679 |
|
680 |
public class DisposableBlobs implements Disposable { |
681 |
|
682 |
private final ArrayList<BLOB> blobList = new ArrayList<>(); |
683 |
|
684 |
public void add(BLOB blob) { |
685 |
blobList.add(blob); |
686 |
} |
687 |
|
688 |
@Override
|
689 |
public void dispose() { |
690 |
blobList.forEach((blob) -> { |
691 |
try {
|
692 |
blob.freeTemporary(); |
693 |
} catch (SQLException ex) { |
694 |
LOGGER.warn("Can't dispose blob " + blob.toString(), ex);
|
695 |
} |
696 |
}); |
697 |
} |
698 |
|
699 |
} |
700 |
|
701 |
protected void setBlob(PreparedStatement st, int columnIndex, |
702 |
byte[] bytes, DisposableBlobs blobList) throws SQLException, IOException { |
703 |
Connection conn = st.getConnection();
|
704 |
if (conn instanceof DelegatingConnection) { |
705 |
conn = ((DelegatingConnection) conn).getInnermostDelegate(); |
706 |
} |
707 |
BLOB blob = BLOB.createTemporary(conn, false, BLOB.DURATION_SESSION);
|
708 |
blobList.add(blob); |
709 |
InputStream inputStream;
|
710 |
try (OutputStream outputStream = blob.setBinaryStream(0L)) { |
711 |
inputStream = new ByteArrayInputStream(bytes); |
712 |
byte[] buffer = new byte[blob.getBufferSize()]; |
713 |
int byteread;
|
714 |
while ((byteread = inputStream.read(buffer)) != -1) { |
715 |
outputStream.write(buffer, 0, byteread);
|
716 |
} |
717 |
} |
718 |
inputStream.close(); |
719 |
st.setBlob(columnIndex, blob); |
720 |
} |
721 |
|
722 |
@Override
|
723 |
public Disposable setStatementParameters(
|
724 |
PreparedStatement st,
|
725 |
List values,
|
726 |
List<Integer> types, //Can be null |
727 |
GeometrySupportType geometrySupportType) throws SQLException { |
728 |
|
729 |
DisposableBlobs blobList = new DisposableBlobs();
|
730 |
if (values == null) { |
731 |
return blobList;
|
732 |
} |
733 |
try {
|
734 |
byte[] bytes; |
735 |
int columnIndex = 1; |
736 |
for (Object value : values) { |
737 |
if (value instanceof Geometry) { |
738 |
switch (geometrySupportType) {
|
739 |
case WKT:
|
740 |
value = ((Geometry) value).convertToWKT(); |
741 |
st.setObject(columnIndex, value); |
742 |
break;
|
743 |
case NATIVE:
|
744 |
case WKB:
|
745 |
bytes = ((Geometry) value).convertToWKB(); |
746 |
setBlob(st, columnIndex, bytes, blobList); |
747 |
break;
|
748 |
case EWKB:
|
749 |
bytes = ((Geometry) value).convertToEWKB(); |
750 |
setBlob(st, columnIndex, bytes, blobList); |
751 |
break;
|
752 |
} |
753 |
} else {
|
754 |
if (types == null) { |
755 |
st.setObject(columnIndex, value); |
756 |
} else {
|
757 |
this.setStatementValue(st, columnIndex, types.get(columnIndex - 1), value); |
758 |
} |
759 |
} |
760 |
columnIndex++; |
761 |
} |
762 |
} catch (Exception ex) { |
763 |
throw new SQLException("Can't set values for the prepared statement.", ex); |
764 |
} |
765 |
return blobList;
|
766 |
} |
767 |
|
768 |
@Override
|
769 |
public List<Object> getParameters(FeatureProvider feature) { |
770 |
return getParameters(feature, null); |
771 |
} |
772 |
|
773 |
@Override
|
774 |
public List<Object> getParameters(FeatureProvider feature, List<Integer> types) { |
775 |
try {
|
776 |
FeatureType type = feature.getType(); |
777 |
FeatureType providerType = this.getHelper().getProviderFeatureType();
|
778 |
List<Object> values = new ArrayList<>(); |
779 |
Object value;
|
780 |
DataTypesManager dataTypesManager = ToolsLocator.getDataTypesManager(); |
781 |
for (ExpressionBuilder.Parameter parameter : this.parameters()) { |
782 |
if (parameter.is_constant()) {
|
783 |
value = parameter.value(); |
784 |
values.add(value); |
785 |
if (types != null) { |
786 |
if (value == null) { |
787 |
if (values.get(values.size() - 2) == EMPTY_POINT) { |
788 |
types.add(DataTypes.INTEGER); |
789 |
} else {
|
790 |
types.add(DataTypes.OBJECT); |
791 |
} |
792 |
} else {
|
793 |
DataType dataType = dataTypesManager.getDataType(value.getClass()); |
794 |
types.add(dataType.getType()); |
795 |
} |
796 |
} |
797 |
} else {
|
798 |
String name = parameter.name();
|
799 |
value = feature.get(name); |
800 |
FeatureAttributeDescriptor attrDesc = type.getAttributeDescriptor(name); |
801 |
Coercion convert = null;
|
802 |
if (providerType != null) { |
803 |
FeatureAttributeDescriptor attrDescProvider = providerType.getAttributeDescriptor(name); |
804 |
if (attrDescProvider != null && attrDescProvider.getType() != attrDesc.getType()) { |
805 |
convert = attrDescProvider.getDataType().getCoercion(); |
806 |
} |
807 |
} |
808 |
switch (attrDesc.getType()) {
|
809 |
case org.gvsig.fmap.dal.DataTypes.GEOMETRY:
|
810 |
Geometry geom = this.forceGeometryType(
|
811 |
attrDesc.getGeomType(), |
812 |
(Geometry) value |
813 |
); |
814 |
if (BooleanUtils.isTrue((Boolean) parameter.getProperty(ORACLE_GEOMETRY_PARAMETER))) { |
815 |
//Es muy guarro pero es para evitar un error de NullPointerException en Oracle
|
816 |
if (geom == null) { |
817 |
//NVL2((NULL),SDO_GEOMETRY((EMPTY_POINT), (?)),NULL)
|
818 |
values.add(null);
|
819 |
values.add(EMPTY_POINT); |
820 |
} else {
|
821 |
//NVL2((1),SDO_GEOMETRY((WKB), (?)),NULL)
|
822 |
values.add(1);
|
823 |
values.add(geom); |
824 |
} |
825 |
if (types != null) { |
826 |
types.add(DataTypes.INTEGER); |
827 |
types.add(DataTypes.GEOMETRY); |
828 |
} |
829 |
} |
830 |
break;
|
831 |
case org.gvsig.fmap.dal.DataTypes.BOOLEAN:
|
832 |
if (value == null) { |
833 |
values.add(null);
|
834 |
} else {
|
835 |
if (convert == null) { |
836 |
values.add(value); |
837 |
} else {
|
838 |
String s = (String) convert.coerce(value); |
839 |
values.add(s == null ? null : s.substring(0, 1)); |
840 |
} |
841 |
} |
842 |
if (types != null) { |
843 |
types.add(DataTypes.STRING); |
844 |
} |
845 |
break;
|
846 |
default:
|
847 |
if (convert == null) { |
848 |
values.add(value); |
849 |
} else {
|
850 |
values.add(convert.coerce(value)); |
851 |
} |
852 |
if (types != null) { |
853 |
int t = attrDesc.getDataType().getType();
|
854 |
types.add(t); |
855 |
if (t == DataTypes.INTEGER) {
|
856 |
Object x = values.get(values.size() - 1); |
857 |
if (x != null && !(x instanceof Integer)) { |
858 |
LOGGER.debug("Esto est? mal");
|
859 |
} |
860 |
} |
861 |
|
862 |
} |
863 |
break;
|
864 |
} |
865 |
} |
866 |
} |
867 |
|
868 |
return values;
|
869 |
} catch (Exception ex) { |
870 |
String f = "unknow"; |
871 |
try {
|
872 |
f = feature.toString(); |
873 |
} catch (Exception ex2) { |
874 |
// Do nothing
|
875 |
} |
876 |
throw new RuntimeException("Can't get parameters to prepared statement from the feature (" + f + ")", ex); |
877 |
} |
878 |
} |
879 |
|
880 |
@Override
|
881 |
public List<String> parameters_names() { |
882 |
List<String> params = new ArrayList<>(); |
883 |
for (ExpressionBuilder.Parameter param : parameters()) {
|
884 |
String s;
|
885 |
switch (param.type()) {
|
886 |
case PARAMETER_TYPE_CONSTANT:
|
887 |
Object theValue = param.value();
|
888 |
if (theValue == null) { |
889 |
s = "null";
|
890 |
} else if (theValue instanceof String) { |
891 |
s = "'" + (String) theValue + "'"; |
892 |
} else {
|
893 |
s = theValue.toString(); |
894 |
} |
895 |
break;
|
896 |
case PARAMETER_TYPE_VARIABLE:
|
897 |
default:
|
898 |
s = "\"" + param.name() + "\""; |
899 |
} |
900 |
params.add(s); |
901 |
if (BooleanUtils.isTrue((Boolean) param.getProperty(ORACLE_GEOMETRY_PARAMETER))) { |
902 |
params.add(s); |
903 |
} |
904 |
|
905 |
} |
906 |
return params;
|
907 |
} |
908 |
|
909 |
public String blob(byte[] data) { |
910 |
StringBuilder builder = new StringBuilder(); |
911 |
builder.append("TO_BLOB(HEXTORAW('");
|
912 |
for (byte abyte : data) { |
913 |
int v = abyte & 0xff; |
914 |
builder.append(String.format("%02x", v)); |
915 |
} |
916 |
builder.append("'))");
|
917 |
return builder.toString();
|
918 |
} |
919 |
|
920 |
@Override
|
921 |
public String as_clob(String s) { |
922 |
int chunkSize = 1024; |
923 |
StringBuilder builder = new StringBuilder(); |
924 |
builder.append("(TO_CLOB('");
|
925 |
for (int i = 0; i < s.length(); i += chunkSize) { |
926 |
String chunk = s.substring(i, Math.min(s.length(), i + chunkSize)); |
927 |
if( i>0 ) { |
928 |
builder.append("') || TO_CLOB('");
|
929 |
} |
930 |
builder.append(StringUtils.replace(chunk, "'", "''")); |
931 |
} |
932 |
builder.append("'))");
|
933 |
return builder.toString();
|
934 |
} |
935 |
|
936 |
public class OracleCreateIndexBuilder extends CreateIndexBuilderBase { |
937 |
|
938 |
@Override
|
939 |
public List<String> toStrings() { |
940 |
List<String> sqls = new ArrayList<>(); |
941 |
if (!this.isSpatial) { |
942 |
// Spatial index is created in a different way because it requires some queries based on the results of other queries
|
943 |
// See OracleUpdateSpatialIndexAndMetadata class
|
944 |
StringBuilder builder = new StringBuilder(); |
945 |
builder.append("CREATE ");
|
946 |
if (this.isUnique) { |
947 |
builder.append("UNIQUE ");
|
948 |
} |
949 |
builder.append("INDEX ");
|
950 |
// if (this.ifNotExist) {
|
951 |
// builder.append("IF NOT EXISTS ");
|
952 |
// }
|
953 |
builder.append(as_identifier(this.indexName));
|
954 |
builder.append(" ON ");
|
955 |
builder.append(this.table.toString());
|
956 |
builder.append(" ( ");
|
957 |
boolean is_first_column = true; |
958 |
for (String column : this.columns) { |
959 |
if (is_first_column) {
|
960 |
is_first_column = false;
|
961 |
} else {
|
962 |
builder.append(", ");
|
963 |
} |
964 |
builder.append(as_identifier(column)); |
965 |
} |
966 |
builder.append(" )");
|
967 |
sqls.add(builder.toString()); |
968 |
} |
969 |
return sqls;
|
970 |
} |
971 |
} |
972 |
|
973 |
public class OracleAlterTableBuilder extends AlterTableBuilderBase { |
974 |
|
975 |
public OracleAlterTableBuilder(SQLBuilderBase sqlbuilder) {
|
976 |
super(sqlbuilder);
|
977 |
} |
978 |
|
979 |
@Override
|
980 |
public List<String> toStrings() { |
981 |
List<String> sqls = new ArrayList<>(); |
982 |
List<String> pks = new ArrayList<>(); |
983 |
|
984 |
for (String column : drops) { |
985 |
StringBuilder builder = new StringBuilder(); |
986 |
builder.append("ALTER TABLE ");
|
987 |
builder.append(this.table.toString());
|
988 |
builder.append(" DROP COLUMN ");
|
989 |
builder.append(as_identifier(column)); |
990 |
sqls.add(builder.toString()); |
991 |
} |
992 |
for (ColumnDescriptor column : adds) {
|
993 |
StringBuilder builder = new StringBuilder(); |
994 |
builder.append("ALTER TABLE ");
|
995 |
builder.append(this.table.toString());
|
996 |
builder.append(" ADD "); //COLUMN "); |
997 |
builder.append(as_identifier(column.getName())); |
998 |
builder.append(" ");
|
999 |
builder.append(sqltype( |
1000 |
column.getType(), |
1001 |
column.getSize(), |
1002 |
column.getPrecision(), |
1003 |
column.getScale(), |
1004 |
column.getGeometryType(), |
1005 |
column.getGeometrySubtype() |
1006 |
) |
1007 |
); |
1008 |
if (column.getDefaultValue() == null) { |
1009 |
if (column.allowNulls()) {
|
1010 |
builder.append(" DEFAULT NULL");
|
1011 |
} |
1012 |
} else {
|
1013 |
builder.append(" DEFAULT '");
|
1014 |
builder.append(column.getDefaultValue().toString()); |
1015 |
builder.append("'");
|
1016 |
} |
1017 |
if (column.allowNulls()) {
|
1018 |
builder.append(" NULL");
|
1019 |
} else {
|
1020 |
builder.append(" NOT NULL");
|
1021 |
} |
1022 |
if (column.isPrimaryKey()) {
|
1023 |
pks.add(column.getName()); |
1024 |
} |
1025 |
sqls.add(builder.toString()); |
1026 |
if (pks.size() > 0) { |
1027 |
builder = new StringBuilder("ALTER TABLE "); |
1028 |
builder.append(this.table.toString());
|
1029 |
builder.append(" ADD PRIMARY KEY (");
|
1030 |
for (int i = 0; i < pks.size(); i++) { |
1031 |
if (i != 0) { |
1032 |
builder.append(", ");
|
1033 |
} |
1034 |
builder.append(as_identifier(pks.get(i))); |
1035 |
} |
1036 |
builder.append(")");
|
1037 |
sqls.add(builder.toString()); |
1038 |
} |
1039 |
if (column.isAutomatic()) {
|
1040 |
String sequenceName = getSerialSequenceName(this.table.getName(), column.getName()); |
1041 |
String sql = MessageFormat.format( |
1042 |
ADD_SERIAL_COLUMN_SEQUENCE_QUERY, |
1043 |
sequenceName |
1044 |
); |
1045 |
sqls.add(sql); |
1046 |
String autoTriggerName = getSerialTriggerName(this.table.getName(), column.getName()); |
1047 |
sql = MessageFormat.format(
|
1048 |
ADD_SERIAL_COLUMN_TRIGGER_QUERY, |
1049 |
autoTriggerName, |
1050 |
this.table.toString(formatter),
|
1051 |
column.getName(), |
1052 |
sequenceName |
1053 |
); |
1054 |
sqls.add(sql); |
1055 |
} |
1056 |
|
1057 |
if (column.isGeometry()) {
|
1058 |
/**
|
1059 |
* Inserting the geomtype and column on
|
1060 |
* OGIS_GEOMETRY_COLUMNS.
|
1061 |
*
|
1062 |
* It is useful to retrieve the geometry type when the
|
1063 |
* spatial index can't be defined (for instance users
|
1064 |
* creating tables on a different schema).
|
1065 |
*
|
1066 |
* Note: We don't insert the SRID because it is unusable as
|
1067 |
* defined on OGIS_GEOMETRY_COLUMNS, as it must reference an
|
1068 |
* existing SRID on OGIS_SPATIAL_REFERENCE_SYSTEM.
|
1069 |
* OGIS_SPATIAL_REFERENCE_SYSTEM is empty and it has a
|
1070 |
* trigger that duplicates on SDO_COORD_REF_SYS any SRID
|
1071 |
* inserted on OGIS_SPATIAL_REFERENCE_SYSTEM.
|
1072 |
* SDO_COORD_REF_SYS is not empty and we don't want to
|
1073 |
* duplicate the SRID definitions!!
|
1074 |
*/
|
1075 |
int gvsigType = column.getGeometryType();
|
1076 |
int gvsigSubtype = column.getGeometryType();
|
1077 |
String sql = MessageFormat.format( // delete before inserting to avoid creating duplicates |
1078 |
DELETE_FROM_OGIS_GEOMETRY_COLUMNS_QUERY, |
1079 |
this.table().getSchema(),
|
1080 |
this.table().getName(),
|
1081 |
column.getName()); |
1082 |
sqls.add(sql); |
1083 |
sql = MessageFormat.format(
|
1084 |
INSERT_OGIS_GEOMETRY_COLUMNS_QUERY, |
1085 |
this.table().getSchema(),
|
1086 |
this.table().getName(),
|
1087 |
column.getName(), |
1088 |
Integer.toString(GeometryTypeUtils.toSFSGeometryTypeCode(gvsigType, gvsigSubtype)));
|
1089 |
sqls.add(sql); |
1090 |
// Note: The spatial index will be created after inserting/updating layer metadata.
|
1091 |
// See OracleUpdateSpatialIndexAndMetadata class
|
1092 |
} |
1093 |
} |
1094 |
for (ColumnDescriptor column : alters) {
|
1095 |
if (!column.isGeometry()) { // FIXME: can't modify geom column. It could be done by adding a temporary one, copying values and then droping and renaming |
1096 |
StringBuilder builder = new StringBuilder(); |
1097 |
builder.append("ALTER TABLE ");
|
1098 |
builder.append(this.table.toString());
|
1099 |
builder.append(" MODIFY (");
|
1100 |
builder.append(as_identifier(column.getName())); |
1101 |
builder.append(" ");
|
1102 |
builder.append(sqltype( |
1103 |
column.getType(), |
1104 |
column.getSize(), |
1105 |
column.getPrecision(), |
1106 |
column.getScale(), |
1107 |
column.getGeometryType(), |
1108 |
column.getGeometrySubtype() |
1109 |
) |
1110 |
); |
1111 |
if (column.getDefaultValue() == null) { |
1112 |
builder.append(" DEFAULT NULL");
|
1113 |
} else {
|
1114 |
builder.append(" DEFAULT '");
|
1115 |
builder.append(column.getDefaultValue().toString()); |
1116 |
builder.append("'");
|
1117 |
} |
1118 |
builder.append(")");
|
1119 |
sqls.add(builder.toString()); |
1120 |
} |
1121 |
} |
1122 |
for (Pair<String, String> pair : renames) { |
1123 |
StringBuilder builder = new StringBuilder(); |
1124 |
builder.append("ALTER TABLE ");
|
1125 |
builder.append(this.table.toString());
|
1126 |
builder.append(" RENAME COLUMN ");
|
1127 |
builder.append(as_identifier(pair.getLeft())); |
1128 |
builder.append(" TO ");
|
1129 |
builder.append(as_identifier(pair.getRight())); |
1130 |
sqls.add(builder.toString()); |
1131 |
} |
1132 |
return sqls;
|
1133 |
} |
1134 |
} |
1135 |
|
1136 |
@Override
|
1137 |
protected AlterTableBuilder createAlterTableBuilder() {
|
1138 |
return new OracleAlterTableBuilder(this); |
1139 |
} |
1140 |
|
1141 |
@Override
|
1142 |
public CreateIndexBuilder createCreateIndexBuilder() {
|
1143 |
return new OracleCreateIndexBuilder(); |
1144 |
} |
1145 |
|
1146 |
@Override
|
1147 |
public String quote_for_identifiers() { |
1148 |
return this.quote_for_identifiers; |
1149 |
} |
1150 |
|
1151 |
@Override
|
1152 |
public String quote_for_strings() { |
1153 |
return this.quote_for_strings; |
1154 |
} |
1155 |
|
1156 |
@Override
|
1157 |
protected SelectColumnBuilder createSelectColumnBuilder() {
|
1158 |
return new OracleSelectColumnBuilder(this); |
1159 |
} |
1160 |
|
1161 |
public class OracleSelectColumnBuilder extends SelectColumnBuilderBase { |
1162 |
|
1163 |
public OracleSelectColumnBuilder(SQLBuilder sqlbuilder) {
|
1164 |
super(sqlbuilder);
|
1165 |
} |
1166 |
|
1167 |
@Override
|
1168 |
public String toString(Formatter<ExpressionBuilder.Value> formatter) { |
1169 |
if (formatter != null && formatter.canApply(this)) { |
1170 |
return formatter.format(this); |
1171 |
} |
1172 |
StringBuilder builder = new StringBuilder(); |
1173 |
if (this.asGeometry) { |
1174 |
if(this.value == VALUE_NULL){ |
1175 |
builder.append(this.value.toString(formatter));
|
1176 |
} else {
|
1177 |
builder.append(expression().ST_AsBinary(this.name).toString(formatter));
|
1178 |
} |
1179 |
} else {
|
1180 |
if (this.value == null) { |
1181 |
builder.append(this.name.toString(formatter));
|
1182 |
} else {
|
1183 |
builder.append(this.value.toString(formatter));
|
1184 |
} |
1185 |
} |
1186 |
if (this.alias != null) { |
1187 |
builder.append(" ");
|
1188 |
builder.append(as_identifier(this.alias));
|
1189 |
} |
1190 |
return builder.toString();
|
1191 |
} |
1192 |
} |
1193 |
|
1194 |
@Override
|
1195 |
public SelectBuilder select() {
|
1196 |
if (this.select == null) { |
1197 |
this.select = this.createSelectBuilder(); |
1198 |
} |
1199 |
return this.select; |
1200 |
} |
1201 |
|
1202 |
@Override
|
1203 |
public UpdateBuilder update() {
|
1204 |
if (this.update == null) { |
1205 |
this.update = this.createUpdateBuilder(); |
1206 |
} |
1207 |
return this.update; |
1208 |
} |
1209 |
|
1210 |
@Override
|
1211 |
public UpdateTableStatisticsBuilder update_table_statistics() {
|
1212 |
if (this.update_table_statistics == null) { |
1213 |
this.update_table_statistics = this.createUpdateTableStatisticsBuilder(); |
1214 |
} |
1215 |
return this.update_table_statistics; |
1216 |
} |
1217 |
|
1218 |
@Override
|
1219 |
public DropTableBuilder drop_table() {
|
1220 |
if (this.drop_table == null) { |
1221 |
this.drop_table = this.createDropTableBuilder(); |
1222 |
} |
1223 |
return this.drop_table; |
1224 |
} |
1225 |
|
1226 |
@Override
|
1227 |
public CreateIndexBuilder create_index() {
|
1228 |
if (this.create_index == null) { |
1229 |
this.create_index = this.createCreateIndexBuilder(); |
1230 |
} |
1231 |
return this.create_index; |
1232 |
} |
1233 |
|
1234 |
@Override
|
1235 |
public DeleteBuilder delete() {
|
1236 |
if (this.delete == null) { |
1237 |
this.delete = this.createDeleteBuilder(); |
1238 |
} |
1239 |
return this.delete; |
1240 |
} |
1241 |
|
1242 |
@Override
|
1243 |
public InsertBuilder insert() {
|
1244 |
if (this.insert == null) { |
1245 |
this.insert = this.createInsertBuilder(); |
1246 |
} |
1247 |
return this.insert; |
1248 |
} |
1249 |
|
1250 |
@Override
|
1251 |
public TableNameBuilder table_name() {
|
1252 |
if (this.table_name == null) { |
1253 |
this.table_name = this.createTableNameBuilder(); |
1254 |
} |
1255 |
return this.table_name; |
1256 |
} |
1257 |
|
1258 |
@Override
|
1259 |
public AlterTableBuilder alter_table() {
|
1260 |
if (this.alter_table == null) { |
1261 |
this.alter_table = this.createAlterTableBuilder(); |
1262 |
} |
1263 |
return this.alter_table; |
1264 |
} |
1265 |
|
1266 |
@Override
|
1267 |
public CreateTableBuilder create_table() {
|
1268 |
if (this.create_table == null) { |
1269 |
this.create_table = this.createCreateTableBuilder(); |
1270 |
} |
1271 |
return this.create_table; |
1272 |
} |
1273 |
|
1274 |
@Override
|
1275 |
public GrantBuilder grant() {
|
1276 |
if (this.grant == null) { |
1277 |
this.grant = this.createGrantBuilder(); |
1278 |
} |
1279 |
return this.grant; |
1280 |
} |
1281 |
|
1282 |
@Override
|
1283 |
public String sqltype(int type, int size, int precision, int scale, int geomType, int geomSubtype) { |
1284 |
switch (type) {
|
1285 |
case DataTypes.DECIMAL:
|
1286 |
if (precision > 38 && scale < 0) { |
1287 |
if (precision > 126) { |
1288 |
precision = 126;
|
1289 |
} |
1290 |
return String.format("FLOAT(%d)", precision); |
1291 |
} |
1292 |
if (precision < 1) { |
1293 |
precision = DataType.DECIMAL_DEFAULT_PRECISION; |
1294 |
} |
1295 |
if (scale < 1) { |
1296 |
return MessageFormat.format(type_decimal_p, precision); |
1297 |
} |
1298 |
return MessageFormat.format(type_decimal_ps, precision, scale); |
1299 |
default:
|
1300 |
return super.sqltype(type, size, precision, scale, geomType, geomSubtype); |
1301 |
} |
1302 |
} |
1303 |
|
1304 |
@Override
|
1305 |
public int getMaxRecomendedSQLLength() { |
1306 |
return 2048; |
1307 |
} |
1308 |
|
1309 |
} |