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 | 56 | jjdelcerro | /**
|
---|---|---|---|
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 | 68 | cmartinez | 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 | 248 | fdiaz | import java.sql.Time; |
34 | import java.sql.Timestamp; |
||
35 | 56 | jjdelcerro | import java.text.MessageFormat; |
36 | import java.util.ArrayList; |
||
37 | import java.util.List; |
||
38 | 248 | fdiaz | import java.util.Objects; |
39 | 609 | fdiaz | import oracle.sql.BLOB; |
40 | 68 | cmartinez | import org.apache.commons.dbcp.DelegatingConnection; |
41 | 609 | fdiaz | import org.apache.commons.lang3.BooleanUtils; |
42 | 56 | jjdelcerro | import org.apache.commons.lang3.StringUtils; |
43 | 182 | fdiaz | import org.apache.commons.lang3.tuple.Pair; |
44 | 599 | jjdelcerro | import org.gvsig.expressionevaluator.DelegatedGeometryExpressionBuilder; |
45 | 178 | fdiaz | 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 | 609 | fdiaz | import static org.gvsig.expressionevaluator.ExpressionBuilder.VALUE_NULL; |
49 | 248 | fdiaz | import org.gvsig.expressionevaluator.ExpressionUtils; |
50 | 178 | fdiaz | import org.gvsig.expressionevaluator.Formatter; |
51 | 599 | jjdelcerro | import org.gvsig.expressionevaluator.GeometryExpressionBuilder; |
52 | 178 | fdiaz | import org.gvsig.expressionevaluator.GeometryExpressionBuilderHelper.GeometrySupportType; |
53 | import static org.gvsig.expressionevaluator.GeometryExpressionBuilderHelper.GeometrySupportType.WKT; |
||
54 | 248 | fdiaz | import org.gvsig.fmap.dal.DataTypes; |
55 | 495 | jjdelcerro | import org.gvsig.fmap.dal.SQLBuilder; |
56 | 178 | fdiaz | 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 | 778 | jjdelcerro | import org.gvsig.fmap.dal.feature.spi.SQLBuilderBase; |
60 | 178 | fdiaz | import org.gvsig.fmap.dal.store.jdbc.JDBCConnectionParameters; |
61 | 609 | fdiaz | 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 | 178 | fdiaz | import org.gvsig.fmap.geom.GeometryUtils; |
65 | 599 | jjdelcerro | import org.gvsig.fmap.geom.primitive.Envelope; |
66 | 178 | fdiaz | import org.gvsig.oracle.dal.expressionbuilderformatter.OracleFormatter; |
67 | import static org.gvsig.oracle.dal.expressionbuilderformatter.OracleGeometryParameter.ORACLE_GEOMETRY_PARAMETER; |
||
68 | 320 | fdiaz | import org.gvsig.tools.ToolsLocator; |
69 | 181 | fdiaz | import org.gvsig.tools.dataTypes.Coercion; |
70 | 178 | fdiaz | import org.gvsig.tools.dataTypes.DataType; |
71 | 248 | fdiaz | import org.gvsig.tools.dataTypes.DataTypeUtils; |
72 | 320 | fdiaz | import org.gvsig.tools.dataTypes.DataTypesManager; |
73 | 609 | fdiaz | import org.gvsig.tools.dispose.Disposable; |
74 | 916 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference; |
75 | 68 | cmartinez | |
76 | 56 | jjdelcerro | public class OracleSQLBuilder extends JDBCSQLBuilderBase { |
77 | 161 | omartinez | |
78 | protected static final String ADD_SERIAL_COLUMN_SEQUENCE_QUERY = "CREATE SEQUENCE \"{0}\""; |
||
79 | 95 | jjdelcerro | protected static final String ADD_SERIAL_COLUMN_TRIGGER_QUERY = "CREATE OR REPLACE TRIGGER \"{0}\" BEFORE INSERT ON {1} FOR EACH ROW " |
80 | 181 | fdiaz | + "BEGIN SELECT \"{3}\".NEXTVAL INTO :new.\"{2}\" FROM dual; END;";
|
81 | 95 | jjdelcerro | protected static final String DELETE_FROM_OGIS_GEOMETRY_COLUMNS_QUERY = "DELETE FROM MDSYS.OGIS_GEOMETRY_COLUMNS " |
82 | 81 | cmartinez | + "WHERE F_TABLE_SCHEMA = ''{0}'' AND F_TABLE_NAME = ''{1}'' AND F_GEOMETRY_COLUMN = ''{2}''";
|
83 | 95 | jjdelcerro | protected static final String INSERT_OGIS_GEOMETRY_COLUMNS_QUERY = "INSERT INTO MDSYS.OGIS_GEOMETRY_COLUMNS " |
84 | 81 | cmartinez | + "(F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN, GEOMETRY_TYPE) VALUES "
|
85 | + "(''{0}'', ''{1}'', ''{2}'', {3})";
|
||
86 | 161 | omartinez | private final String quote_for_identifiers; |
87 | private final String quote_for_strings; |
||
88 | 178 | fdiaz | 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 | 166 | omartinez | 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 | 180 | fdiaz | |
98 | 178 | fdiaz | public static final int BOOLEAN_STRING_DEFAULT_LENGTH = 1; |
99 | 180 | fdiaz | |
100 | 178 | fdiaz | private static final Geometry EMPTY_POINT = GeometryUtils.createPoint(0, 0); |
101 | 166 | omartinez | |
102 | 178 | fdiaz | protected Formatter formatter = null; |
103 | |||
104 | 56 | jjdelcerro | public OracleSQLBuilder(JDBCHelper helper) {
|
105 | 95 | jjdelcerro | super(helper);
|
106 | 56 | jjdelcerro | |
107 | 161 | omartinez | 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 | 56 | jjdelcerro | |
116 | 181 | fdiaz | this.type_boolean = "CHAR(1)"; |
117 | 180 | fdiaz | this.type_byte = "NUMBER(" + (DataType.BYTE_MAX_PRECISION) + ",0)"; |
118 | 178 | fdiaz | this.type_bytearray = "BLOB"; |
119 | 161 | omartinez | this.type_geometry = "SDO_GEOMETRY"; |
120 | 178 | fdiaz | this.type_char = "CHAR(1)"; |
121 | this.type_date = "DATE"; |
||
122 | 161 | omartinez | this.type_double = "BINARY_DOUBLE"; //float con 53 bits de mantisa = float(54) |
123 | 178 | fdiaz | this.type_decimal_p = "NUMBER({0})"; |
124 | this.type_decimal_ps = "NUMBER({0},{1})"; |
||
125 | // this.type_bigdecimal = "NUMBER({0},{1})";
|
||
126 | 161 | omartinez | this.type_float = "BINARY_FLOAT"; //float con 24 bits de mantisa = float(24) |
127 | 180 | fdiaz | this.type_int = "NUMBER(" + (DataType.INT_MAX_PRECISION - 1) + ",0)"; |
128 | this.type_long = "NUMBER(" + (DataType.LONG_MAX_PRECISION - 1) + ",0)"; |
||
129 | 357 | jjdelcerro | this.type_string = "NCLOB"; |
130 | 912 | jjdelcerro | this.type_string_p = "NVARCHAR2({0,Number,#######})"; |
131 | 178 | fdiaz | this.type_time = "TIMESTAMP"; |
132 | 161 | omartinez | this.type_timestamp = "TIMESTAMP"; |
133 | 357 | jjdelcerro | 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 | 56 | jjdelcerro | |
139 | 166 | omartinez | 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 | 161 | omartinez | // config.remove_functionality(SQLConfig.UPDATE_TABLE_STATISTICS_table; // FIXME
|
144 | 166 | omartinez | 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 | 161 | omartinez | } |
152 | 915 | jjdelcerro | |
153 | public OracleHelper getHelper() {
|
||
154 | return (OracleHelper) super.getHelper(); |
||
155 | } |
||
156 | |||
157 | 180 | fdiaz | @Override
|
158 | 416 | jjdelcerro | public Formatter formatter() { |
159 | 180 | fdiaz | if (this.formatter == null) { |
160 | 178 | fdiaz | this.formatter = new OracleFormatter(this); |
161 | } |
||
162 | return this.formatter; |
||
163 | } |
||
164 | 56 | jjdelcerro | |
165 | 599 | jjdelcerro | private class OracleGeometryExpressionBuilder extends DelegatedGeometryExpressionBuilder { |
166 | 609 | fdiaz | |
167 | 599 | jjdelcerro | public OracleGeometryExpressionBuilder(GeometryExpressionBuilder expressionBuilder) {
|
168 | super(expressionBuilder);
|
||
169 | } |
||
170 | |||
171 | @Override
|
||
172 | public Formatter<Value> formatter() { |
||
173 | return OracleSQLBuilder.this.formatter();
|
||
174 | } |
||
175 | 609 | fdiaz | |
176 | 599 | jjdelcerro | } |
177 | 609 | fdiaz | |
178 | 599 | jjdelcerro | private OracleGeometryExpressionBuilder oracleExpressionBuilder = null; |
179 | 609 | fdiaz | |
180 | 56 | jjdelcerro | @Override
|
181 | 599 | jjdelcerro | public GeometryExpressionBuilder expression() {
|
182 | 609 | fdiaz | if (this.oracleExpressionBuilder == null) { |
183 | 599 | jjdelcerro | this.oracleExpressionBuilder = new OracleGeometryExpressionBuilder(super.expression()); |
184 | } |
||
185 | return this.oracleExpressionBuilder; |
||
186 | } |
||
187 | 609 | fdiaz | |
188 | 599 | jjdelcerro | @Override
|
189 | 56 | jjdelcerro | public String default_schema() { |
190 | 178 | fdiaz | JDBCConnectionParameters params = this.helper.getConnectionParameters();
|
191 | if (params != null) { |
||
192 | 180 | fdiaz | if (StringUtils.isBlank(params.getSchema())) {
|
193 | if ((StringUtils.isNotBlank(params.getUser()))) {
|
||
194 | 182 | fdiaz | return params.getUser();
|
195 | 178 | fdiaz | } |
196 | } else {
|
||
197 | return params.getSchema();
|
||
198 | } |
||
199 | 56 | jjdelcerro | } |
200 | 161 | omartinez | return this.defaultSchema; |
201 | 56 | jjdelcerro | } |
202 | 161 | omartinez | |
203 | 56 | jjdelcerro | public class OracleUpdateTableStatisticsBuilderBase extends UpdateTableStatisticsBuilderBase { |
204 | |||
205 | @Override
|
||
206 | public List<String> toStrings() { |
||
207 | List<String> sqls = new ArrayList<>(); |
||
208 | |||
209 | 916 | jjdelcerro | // String name = as_identifier(this.table.getName());
|
210 | // if (table.has_schema()) {
|
||
211 | // name = as_identifier(this.table.getSchema()) + "." + name;
|
||
212 | // }
|
||
213 | 161 | omartinez | String sql = MessageFormat.format( |
214 | 178 | fdiaz | "ANALYZE TABLE {0} COMPUTE STATISTICS",
|
215 | 916 | jjdelcerro | this.table.toString()
|
216 | 161 | omartinez | ); |
217 | 166 | omartinez | if (!StringUtils.isEmpty(sql)) {
|
218 | 161 | omartinez | sqls.add(sql); |
219 | 56 | jjdelcerro | } |
220 | 161 | omartinez | |
221 | 56 | jjdelcerro | return sqls;
|
222 | } |
||
223 | } |
||
224 | 161 | omartinez | |
225 | 95 | jjdelcerro | public String getSerialSequenceName(String tableName, String columnName) { |
226 | 181 | fdiaz | return ("GVSEQ_" + tableName + "_" + columnName).toUpperCase(); |
227 | 81 | cmartinez | } |
228 | 161 | omartinez | |
229 | 95 | jjdelcerro | public String getSerialTriggerName(String tableName, String columnName) { |
230 | 181 | fdiaz | return ("GVSER_" + tableName + "_" + columnName).toUpperCase(); |
231 | 82 | cmartinez | } |
232 | 161 | omartinez | |
233 | 56 | jjdelcerro | protected class OracleCreateTableBuilder extends CreateTableBuilderBase { |
234 | |||
235 | @Override
|
||
236 | 178 | fdiaz | public List<String> toStrings(Formatter formatter) { |
237 | 56 | jjdelcerro | |
238 | List<String> sqls = new ArrayList<>(); |
||
239 | StringBuilder builder = new StringBuilder(); |
||
240 | |||
241 | builder.append("CREATE TABLE ");
|
||
242 | 915 | jjdelcerro | builder.append(this.table().toString());
|
243 | 56 | jjdelcerro | builder.append(" (");
|
244 | boolean first = true; |
||
245 | 161 | omartinez | |
246 | ArrayList<String> pks = new ArrayList<>(); |
||
247 | boolean automaticPrimaryKey = false; |
||
248 | |||
249 | for (ColumnDescriptor column : columns) {
|
||
250 | if (first) {
|
||
251 | 56 | jjdelcerro | first = false;
|
252 | } else {
|
||
253 | builder.append(", ");
|
||
254 | } |
||
255 | 161 | omartinez | if (column.isGeometry()) {
|
256 | 182 | fdiaz | builder.append(as_identifier(column.getName())); //.toUpperCase()));
|
257 | 73 | cmartinez | builder.append(" ");
|
258 | 69 | cmartinez | builder.append(" SDO_GEOMETRY");
|
259 | 161 | omartinez | if (!column.allowNulls()) {
|
260 | 69 | cmartinez | builder.append(" NOT NULL");
|
261 | 68 | cmartinez | } |
262 | 56 | jjdelcerro | } else {
|
263 | 161 | omartinez | builder.append(as_identifier(column.getName())); |
264 | 73 | cmartinez | builder.append(" ");
|
265 | 178 | fdiaz | builder.append(sqltype( |
266 | 180 | fdiaz | column.getType(), |
267 | column.getSize(), |
||
268 | column.getPrecision(), |
||
269 | 178 | fdiaz | column.getScale(), |
270 | 180 | fdiaz | column.getGeometryType(), |
271 | 178 | fdiaz | column.getGeometrySubtype())); |
272 | 180 | fdiaz | |
273 | 248 | fdiaz | // 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 | 56 | jjdelcerro | } |
288 | 161 | omartinez | 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 | 248 | fdiaz | } 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 | 95 | jjdelcerro | } |
334 | 56 | jjdelcerro | } |
335 | builder.append(" )");
|
||
336 | sqls.add(builder.toString()); |
||
337 | 161 | omartinez | |
338 | if (pks.size() > 0) { |
||
339 | builder = new StringBuilder("ALTER TABLE "); |
||
340 | 915 | jjdelcerro | builder.append(this.table().toString());
|
341 | 161 | omartinez | 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 | 71 | cmartinez | } |
351 | 81 | cmartinez | |
352 | 599 | jjdelcerro | for (ColumnDescriptor column : columns) {
|
353 | 609 | fdiaz | if (column.isAutomatic()) {
|
354 | 599 | jjdelcerro | 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 | 915 | jjdelcerro | this.table().toString(),
|
365 | 599 | jjdelcerro | column.getName(), |
366 | sequenceName |
||
367 | ); |
||
368 | sqls.add(sql); |
||
369 | } |
||
370 | } |
||
371 | 609 | fdiaz | |
372 | 599 | jjdelcerro | for (ColumnDescriptor column : columns) {
|
373 | 609 | fdiaz | if (column.isGeometry()) {
|
374 | 599 | jjdelcerro | /**
|
375 | 609 | fdiaz | * Inserting the geomtype and column on
|
376 | * OGIS_GEOMETRY_COLUMNS.
|
||
377 | 599 | jjdelcerro | *
|
378 | 609 | fdiaz | * 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 | 599 | jjdelcerro | *
|
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 | 609 | fdiaz | * 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 | 599 | jjdelcerro | */
|
391 | int gvsigType = column.getGeometryType();
|
||
392 | 913 | jjdelcerro | int gvsigSubtype = column.getGeometrySubtype();
|
393 | 599 | jjdelcerro | 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 | 913 | jjdelcerro | column.getName() |
398 | 609 | fdiaz | ); |
399 | 599 | jjdelcerro | sqls.add(sql); |
400 | sql = MessageFormat.format(
|
||
401 | INSERT_OGIS_GEOMETRY_COLUMNS_QUERY, |
||
402 | this.table().getSchema(),
|
||
403 | this.table().getName().toUpperCase(),
|
||
404 | 913 | jjdelcerro | column.getName(), |
405 | 599 | jjdelcerro | Integer.toString(GeometryTypeUtils.toSFSGeometryTypeCode(gvsigType, gvsigSubtype))
|
406 | ); |
||
407 | sqls.add(sql); |
||
408 | Envelope tablebbox = column.getTableBBox(); |
||
409 | 609 | fdiaz | if (tablebbox != null) { |
410 | 600 | jjdelcerro | sql = SpatialIndexUtils.getSQLDeleteUserMetadata( |
411 | 609 | fdiaz | this.table().getSchema(),
|
412 | this.table().getName(),
|
||
413 | 600 | jjdelcerro | column.getName() |
414 | 599 | jjdelcerro | ); |
415 | sqls.add(sql); |
||
416 | sql = SpatialIndexUtils.getSQLInsertUserMetadata( |
||
417 | 609 | fdiaz | this.table().getName(),
|
418 | 599 | jjdelcerro | tablebbox, |
419 | 609 | fdiaz | column.getName(), |
420 | 600 | jjdelcerro | (int) column.getGeometrySRSId()
|
421 | ); |
||
422 | sqls.add(sql); |
||
423 | sql = SpatialIndexUtils.getSQLCreateSpatialIndex( |
||
424 | 609 | fdiaz | this.table().getName(),
|
425 | column.getName(), |
||
426 | 913 | jjdelcerro | gvsigType, |
427 | gvsigSubtype |
||
428 | 599 | jjdelcerro | ); |
429 | sqls.add(sql); |
||
430 | } |
||
431 | } |
||
432 | } |
||
433 | 56 | jjdelcerro | return sqls;
|
434 | } |
||
435 | } |
||
436 | 609 | fdiaz | |
437 | 178 | fdiaz | public class OracleSelectBuilder extends SelectBuilderBase { |
438 | 56 | jjdelcerro | |
439 | 181 | fdiaz | @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 | 609 | fdiaz | boolean involveWithLimitOrOffset = ((this.has_offset() || this.has_limit()) |
446 | 647 | jjdelcerro | && (this.has_aggregate_functions() || this.has_group_by() || this.has_order_by())); |
447 | 609 | fdiaz | |
448 | 181 | fdiaz | 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 | 609 | fdiaz | |
464 | 56 | jjdelcerro | builder.append("SELECT ");
|
465 | 161 | omartinez | if (this.distinct) { |
466 | 56 | jjdelcerro | builder.append("DISTINCT ");
|
467 | } |
||
468 | boolean first = true; |
||
469 | 161 | omartinez | for (SelectColumnBuilder column : columns) {
|
470 | if (first) {
|
||
471 | 56 | jjdelcerro | first = false;
|
472 | } else {
|
||
473 | builder.append(", ");
|
||
474 | } |
||
475 | 178 | fdiaz | builder.append(column.toString(formatter)); |
476 | 56 | jjdelcerro | } |
477 | |||
478 | 161 | omartinez | if (this.has_from()) { |
479 | 56 | jjdelcerro | builder.append(" FROM ");
|
480 | 178 | fdiaz | builder.append(this.from.toString(formatter));
|
481 | 56 | jjdelcerro | } |
482 | 161 | omartinez | if (this.has_where()) { |
483 | 56 | jjdelcerro | builder.append(" WHERE ");
|
484 | 178 | fdiaz | builder.append(this.where.toString(formatter));
|
485 | 56 | jjdelcerro | } |
486 | |||
487 | 609 | fdiaz | if (!involveWithLimitOrOffset) {
|
488 | 181 | fdiaz | if (this.has_limit() || this.has_offset()) { |
489 | if (this.has_where()) { |
||
490 | builder.append(" AND ");
|
||
491 | } else {
|
||
492 | builder.append(" WHERE ");
|
||
493 | } |
||
494 | 178 | fdiaz | } |
495 | 609 | fdiaz | |
496 | 181 | fdiaz | if (this.has_limit()) { |
497 | builder.append(" ROWNUM <= ");
|
||
498 | 609 | fdiaz | if (this.has_offset()) { |
499 | builder.append(this.offset + this.limit); |
||
500 | 181 | fdiaz | } 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 | 178 | fdiaz | |
509 | 181 | fdiaz | 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 | 56 | jjdelcerro | builder.append(", ");
|
514 | 181 | fdiaz | builder.append(this.groupColumn.get(i).toString(formatter));
|
515 | 56 | jjdelcerro | } |
516 | } |
||
517 | 181 | fdiaz | |
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 | 56 | jjdelcerro | } |
532 | |||
533 | 609 | fdiaz | if (involveWithLimitOrOffset) {
|
534 | 494 | fdiaz | 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 | 161 | omartinez | builder.append(") a");
|
547 | if (this.has_limit()) { |
||
548 | builder.append(" WHERE ROWNUM <= ");
|
||
549 | 609 | fdiaz | if (this.has_offset()) { |
550 | builder.append(this.offset + this.limit); |
||
551 | 181 | fdiaz | } else {
|
552 | builder.append(this.limit);
|
||
553 | } |
||
554 | 161 | omartinez | } |
555 | if (this.has_offset()) { |
||
556 | builder.append(" )");
|
||
557 | 181 | fdiaz | builder.append(" WHERE rnum > ");
|
558 | 161 | omartinez | builder.append(this.offset);
|
559 | } |
||
560 | 494 | fdiaz | |
561 | 58 | cmartinez | } |
562 | 56 | jjdelcerro | return builder.toString();
|
563 | } |
||
564 | 178 | fdiaz | |
565 | 56 | jjdelcerro | } |
566 | |||
567 | 95 | jjdelcerro | @Override
|
568 | 167 | omartinez | public TableNameBuilder createTableNameBuilder() {
|
569 | 58 | cmartinez | return new OracleTableNameBuilder(); |
570 | } |
||
571 | 56 | jjdelcerro | |
572 | @Override
|
||
573 | protected CreateTableBuilder createCreateTableBuilder() {
|
||
574 | return new OracleCreateTableBuilder(); |
||
575 | } |
||
576 | |||
577 | @Override
|
||
578 | 602 | fdiaz | public SelectBuilder createSelectBuilder() {
|
579 | 178 | fdiaz | return new OracleSelectBuilder(); |
580 | 56 | jjdelcerro | } |
581 | 180 | fdiaz | |
582 | @Override
|
||
583 | 178 | fdiaz | protected FromBuilder createFromBuilder() {
|
584 | return new OracleFromBuilder(); |
||
585 | } |
||
586 | 56 | jjdelcerro | |
587 | @Override
|
||
588 | protected UpdateTableStatisticsBuilder createUpdateTableStatisticsBuilder() {
|
||
589 | return new OracleUpdateTableStatisticsBuilderBase(); |
||
590 | } |
||
591 | 161 | omartinez | |
592 | 916 | jjdelcerro | 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 | 58 | cmartinez | protected class OracleTableNameBuilder extends TableNameBuilderBase { |
609 | 161 | omartinez | |
610 | 58 | cmartinez | @Override
|
611 | 178 | fdiaz | public boolean has_database() { |
612 | return false; |
||
613 | } |
||
614 | 180 | fdiaz | |
615 | 178 | fdiaz | @Override
|
616 | 58 | cmartinez | public String toString() { |
617 | 609 | fdiaz | return this.toString(formatter()); |
618 | } |
||
619 | |||
620 | 915 | jjdelcerro | protected String databaseName2provider() { |
621 | return null; |
||
622 | } |
||
623 | |||
624 | protected String schemaName2provider() { |
||
625 | return this.schemaName; |
||
626 | } |
||
627 | |||
628 | protected String tableName2provider() { |
||
629 | 916 | jjdelcerro | return getProviderTableName(this.tableName); |
630 | 915 | jjdelcerro | } |
631 | |||
632 | |||
633 | 609 | fdiaz | @Override
|
634 | public String toString(Formatter<ExpressionBuilder.Value> formatter) { |
||
635 | if (formatter != null && formatter.canApply(this)) { |
||
636 | return formatter.format(this); |
||
637 | } |
||
638 | 161 | omartinez | if (this.has_schema()) { |
639 | 915 | jjdelcerro | return as_identifier(this.schemaName2provider()) + "." |
640 | + as_identifier(this.tableName2provider());
|
||
641 | 58 | cmartinez | } |
642 | 609 | fdiaz | |
643 | 915 | jjdelcerro | OracleConnectionParameters params = getHelper().getConnectionParameters(); |
644 | 609 | fdiaz | if (params != null) { |
645 | if ((StringUtils.isNotBlank(params.getUser()))) {
|
||
646 | return as_identifier(params.getUser()) + "." |
||
647 | 915 | jjdelcerro | + as_identifier(this.tableName2provider());
|
648 | 609 | fdiaz | } |
649 | } |
||
650 | 915 | jjdelcerro | return as_identifier(this.tableName2provider()); |
651 | 58 | cmartinez | } |
652 | } |
||
653 | 180 | fdiaz | |
654 | 178 | fdiaz | public class OracleFromBuilder extends FromBuilderBase { |
655 | 180 | fdiaz | |
656 | 178 | fdiaz | @Override
|
657 | public String toString(Formatter<ExpressionBuilder.Value> formatter) { |
||
658 | 180 | fdiaz | if (formatter != null && formatter.canApply(this)) { |
659 | 178 | fdiaz | return formatter.format(this); |
660 | } |
||
661 | if (!StringUtils.isEmpty(passthrough)) {
|
||
662 | return passthrough;
|
||
663 | } |
||
664 | if (!StringUtils.isEmpty(subquery)) {
|
||
665 | 180 | fdiaz | return "( " + this.subquery + ") " + quote_for_identifiers() + "_subquery_alias_" + quote_for_identifiers() + " "; |
666 | 178 | fdiaz | } |
667 | 180 | fdiaz | if (this.joins == null || this.joins.isEmpty()) { |
668 | 178 | fdiaz | return this.tableName.toString(formatter); |
669 | } |
||
670 | StringBuilder builder = new StringBuilder(); |
||
671 | builder.append(this.tableName.toString(formatter));
|
||
672 | 609 | fdiaz | for (JoinBuilder join : this.joins) { |
673 | 178 | fdiaz | builder.append(" ");
|
674 | builder.append(join.toString(formatter)); |
||
675 | } |
||
676 | return builder.toString();
|
||
677 | } |
||
678 | } |
||
679 | 58 | cmartinez | |
680 | 68 | cmartinez | public class DisposableBlobs implements Disposable { |
681 | |||
682 | 161 | omartinez | private final ArrayList<BLOB> blobList = new ArrayList<>(); |
683 | |||
684 | public void add(BLOB blob) { |
||
685 | blobList.add(blob); |
||
686 | } |
||
687 | |||
688 | 95 | jjdelcerro | @Override
|
689 | public void dispose() { |
||
690 | 180 | fdiaz | blobList.forEach((blob) -> { |
691 | 95 | jjdelcerro | try {
|
692 | blob.freeTemporary(); |
||
693 | } catch (SQLException ex) { |
||
694 | 180 | fdiaz | LOGGER.warn("Can't dispose blob " + blob.toString(), ex);
|
695 | 101 | jjdelcerro | } |
696 | 180 | fdiaz | }); |
697 | 95 | jjdelcerro | } |
698 | |||
699 | 68 | cmartinez | } |
700 | |||
701 | 161 | omartinez | protected void setBlob(PreparedStatement st, int columnIndex, |
702 | byte[] bytes, DisposableBlobs blobList) throws SQLException, IOException { |
||
703 | 68 | cmartinez | Connection conn = st.getConnection();
|
704 | if (conn instanceof DelegatingConnection) { |
||
705 | 161 | omartinez | conn = ((DelegatingConnection) conn).getInnermostDelegate(); |
706 | 68 | cmartinez | } |
707 | BLOB blob = BLOB.createTemporary(conn, false, BLOB.DURATION_SESSION);
|
||
708 | blobList.add(blob); |
||
709 | 180 | fdiaz | 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 | 68 | cmartinez | } |
718 | inputStream.close(); |
||
719 | st.setBlob(columnIndex, blob); |
||
720 | } |
||
721 | |||
722 | @Override
|
||
723 | public Disposable setStatementParameters(
|
||
724 | 161 | omartinez | PreparedStatement st,
|
725 | List values,
|
||
726 | 320 | fdiaz | List<Integer> types, //Can be null |
727 | 68 | cmartinez | GeometrySupportType geometrySupportType) throws SQLException { |
728 | 161 | omartinez | |
729 | DisposableBlobs blobList = new DisposableBlobs();
|
||
730 | 68 | cmartinez | 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 | 161 | omartinez | switch (geometrySupportType) {
|
739 | 68 | cmartinez | case WKT:
|
740 | value = ((Geometry) value).convertToWKT(); |
||
741 | st.setObject(columnIndex, value); |
||
742 | break;
|
||
743 | case NATIVE:
|
||
744 | 161 | omartinez | case WKB:
|
745 | 68 | cmartinez | 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 | 609 | fdiaz | if (types == null) { |
755 | 320 | fdiaz | st.setObject(columnIndex, value); |
756 | } else {
|
||
757 | 609 | fdiaz | this.setStatementValue(st, columnIndex, types.get(columnIndex - 1), value); |
758 | 320 | fdiaz | } |
759 | 68 | cmartinez | } |
760 | columnIndex++; |
||
761 | } |
||
762 | 161 | omartinez | } catch (Exception ex) { |
763 | 68 | cmartinez | throw new SQLException("Can't set values for the prepared statement.", ex); |
764 | } |
||
765 | return blobList;
|
||
766 | } |
||
767 | 180 | fdiaz | |
768 | @Override
|
||
769 | 178 | fdiaz | public List<Object> getParameters(FeatureProvider feature) { |
770 | 320 | fdiaz | return getParameters(feature, null); |
771 | } |
||
772 | 609 | fdiaz | |
773 | 890 | fdiaz | @Override
|
774 | 320 | fdiaz | public List<Object> getParameters(FeatureProvider feature, List<Integer> types) { |
775 | 178 | fdiaz | try {
|
776 | FeatureType type = feature.getType(); |
||
777 | 181 | fdiaz | FeatureType providerType = this.getHelper().getProviderFeatureType();
|
778 | 178 | fdiaz | List<Object> values = new ArrayList<>(); |
779 | Object value;
|
||
780 | 320 | fdiaz | DataTypesManager dataTypesManager = ToolsLocator.getDataTypesManager(); |
781 | 178 | fdiaz | for (ExpressionBuilder.Parameter parameter : this.parameters()) { |
782 | if (parameter.is_constant()) {
|
||
783 | value = parameter.value(); |
||
784 | values.add(value); |
||
785 | 609 | fdiaz | if (types != null) { |
786 | if (value == null) { |
||
787 | if (values.get(values.size() - 2) == EMPTY_POINT) { |
||
788 | 320 | fdiaz | 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 | 178 | fdiaz | } else {
|
798 | String name = parameter.name();
|
||
799 | value = feature.get(name); |
||
800 | FeatureAttributeDescriptor attrDesc = type.getAttributeDescriptor(name); |
||
801 | 181 | fdiaz | Coercion convert = null;
|
802 | 609 | fdiaz | if (providerType != null) { |
803 | 181 | fdiaz | FeatureAttributeDescriptor attrDescProvider = providerType.getAttributeDescriptor(name); |
804 | 609 | fdiaz | if (attrDescProvider != null && attrDescProvider.getType() != attrDesc.getType()) { |
805 | 181 | fdiaz | convert = attrDescProvider.getDataType().getCoercion(); |
806 | } |
||
807 | } |
||
808 | 180 | fdiaz | 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 | 320 | fdiaz | //Es muy guarro pero es para evitar un error de NullPointerException en Oracle
|
816 | 180 | fdiaz | if (geom == null) { |
817 | 320 | fdiaz | //NVL2((NULL),SDO_GEOMETRY((EMPTY_POINT), (?)),NULL)
|
818 | 250 | fdiaz | values.add(null);
|
819 | 180 | fdiaz | values.add(EMPTY_POINT); |
820 | } else {
|
||
821 | 320 | fdiaz | //NVL2((1),SDO_GEOMETRY((WKB), (?)),NULL)
|
822 | 250 | fdiaz | values.add(1);
|
823 | 180 | fdiaz | values.add(geom); |
824 | } |
||
825 | 609 | fdiaz | if (types != null) { |
826 | 320 | fdiaz | types.add(DataTypes.INTEGER); |
827 | types.add(DataTypes.GEOMETRY); |
||
828 | } |
||
829 | 180 | fdiaz | } |
830 | break;
|
||
831 | 181 | fdiaz | case org.gvsig.fmap.dal.DataTypes.BOOLEAN:
|
832 | if (value == null) { |
||
833 | values.add(null);
|
||
834 | } else {
|
||
835 | 320 | fdiaz | if (convert == null) { |
836 | 181 | fdiaz | values.add(value); |
837 | } else {
|
||
838 | String s = (String) convert.coerce(value); |
||
839 | 320 | fdiaz | values.add(s == null ? null : s.substring(0, 1)); |
840 | 181 | fdiaz | } |
841 | } |
||
842 | 320 | fdiaz | if (types != null) { |
843 | types.add(DataTypes.STRING); |
||
844 | } |
||
845 | 181 | fdiaz | break;
|
846 | 180 | fdiaz | default:
|
847 | 181 | fdiaz | if (convert == null) { |
848 | values.add(value); |
||
849 | } else {
|
||
850 | values.add(convert.coerce(value)); |
||
851 | } |
||
852 | 320 | fdiaz | if (types != null) { |
853 | int t = attrDesc.getDataType().getType();
|
||
854 | types.add(t); |
||
855 | 609 | fdiaz | if (t == DataTypes.INTEGER) {
|
856 | Object x = values.get(values.size() - 1); |
||
857 | if (x != null && !(x instanceof Integer)) { |
||
858 | 320 | fdiaz | LOGGER.debug("Esto est? mal");
|
859 | } |
||
860 | } |
||
861 | 609 | fdiaz | |
862 | 320 | fdiaz | } |
863 | 180 | fdiaz | break;
|
864 | 178 | fdiaz | } |
865 | } |
||
866 | } |
||
867 | 609 | fdiaz | |
868 | 180 | fdiaz | return values;
|
869 | 178 | fdiaz | } 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 | 161 | omartinez | |
880 | 178 | fdiaz | @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 | 180 | fdiaz | if (BooleanUtils.isTrue((Boolean) param.getProperty(ORACLE_GEOMETRY_PARAMETER))) { |
902 | 178 | fdiaz | params.add(s); |
903 | } |
||
904 | |||
905 | } |
||
906 | return params;
|
||
907 | } |
||
908 | |||
909 | 95 | jjdelcerro | public String blob(byte[] data) { |
910 | 68 | cmartinez | 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 | 161 | omartinez | |
920 | 680 | jjdelcerro | @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 | 95 | jjdelcerro | public class OracleCreateIndexBuilder extends CreateIndexBuilderBase { |
937 | 161 | omartinez | |
938 | 95 | jjdelcerro | @Override
|
939 | public List<String> toStrings() { |
||
940 | 161 | omartinez | List<String> sqls = new ArrayList<>(); |
941 | if (!this.isSpatial) { |
||
942 | 180 | fdiaz | // 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 | 161 | omartinez | } |
949 | 180 | fdiaz | builder.append("INDEX ");
|
950 | 248 | fdiaz | // if (this.ifNotExist) {
|
951 | // builder.append("IF NOT EXISTS ");
|
||
952 | // }
|
||
953 | 180 | fdiaz | 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 | 248 | fdiaz | builder.append(as_identifier(column)); |
965 | 180 | fdiaz | } |
966 | builder.append(" )");
|
||
967 | 161 | omartinez | sqls.add(builder.toString()); |
968 | } |
||
969 | 95 | jjdelcerro | return sqls;
|
970 | 161 | omartinez | } |
971 | 95 | jjdelcerro | } |
972 | 161 | omartinez | |
973 | 81 | cmartinez | public class OracleAlterTableBuilder extends AlterTableBuilderBase { |
974 | 95 | jjdelcerro | |
975 | 778 | jjdelcerro | public OracleAlterTableBuilder(SQLBuilderBase sqlbuilder) {
|
976 | super(sqlbuilder);
|
||
977 | } |
||
978 | |||
979 | 81 | cmartinez | @Override
|
980 | public List<String> toStrings() { |
||
981 | List<String> sqls = new ArrayList<>(); |
||
982 | 95 | jjdelcerro | List<String> pks = new ArrayList<>(); |
983 | 81 | cmartinez | |
984 | 182 | fdiaz | for (String column : drops) { |
985 | 81 | cmartinez | StringBuilder builder = new StringBuilder(); |
986 | builder.append("ALTER TABLE ");
|
||
987 | builder.append(this.table.toString());
|
||
988 | builder.append(" DROP COLUMN ");
|
||
989 | 161 | omartinez | builder.append(as_identifier(column)); |
990 | 81 | cmartinez | sqls.add(builder.toString()); |
991 | 182 | fdiaz | } |
992 | for (ColumnDescriptor column : adds) {
|
||
993 | 81 | cmartinez | StringBuilder builder = new StringBuilder(); |
994 | builder.append("ALTER TABLE ");
|
||
995 | builder.append(this.table.toString());
|
||
996 | 178 | fdiaz | builder.append(" ADD "); //COLUMN "); |
997 | 161 | omartinez | builder.append(as_identifier(column.getName())); |
998 | 81 | cmartinez | builder.append(" ");
|
999 | builder.append(sqltype( |
||
1000 | 95 | jjdelcerro | column.getType(), |
1001 | 178 | fdiaz | column.getSize(), |
1002 | 95 | jjdelcerro | column.getPrecision(), |
1003 | 178 | fdiaz | column.getScale(), |
1004 | 95 | jjdelcerro | column.getGeometryType(), |
1005 | column.getGeometrySubtype() |
||
1006 | ) |
||
1007 | 81 | cmartinez | ); |
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 | 95 | jjdelcerro | pks.add(column.getName()); |
1024 | 81 | cmartinez | } |
1025 | sqls.add(builder.toString()); |
||
1026 | 95 | jjdelcerro | if (pks.size() > 0) { |
1027 | builder = new StringBuilder("ALTER TABLE "); |
||
1028 | 182 | fdiaz | builder.append(this.table.toString());
|
1029 | 95 | jjdelcerro | builder.append(" ADD PRIMARY KEY (");
|
1030 | for (int i = 0; i < pks.size(); i++) { |
||
1031 | if (i != 0) { |
||
1032 | builder.append(", ");
|
||
1033 | } |
||
1034 | 161 | omartinez | builder.append(as_identifier(pks.get(i))); |
1035 | 95 | jjdelcerro | } |
1036 | builder.append(")");
|
||
1037 | sqls.add(builder.toString()); |
||
1038 | } |
||
1039 | if (column.isAutomatic()) {
|
||
1040 | String sequenceName = getSerialSequenceName(this.table.getName(), column.getName()); |
||
1041 | 81 | cmartinez | String sql = MessageFormat.format( |
1042 | 95 | jjdelcerro | ADD_SERIAL_COLUMN_SEQUENCE_QUERY, |
1043 | sequenceName |
||
1044 | ); |
||
1045 | 81 | cmartinez | sqls.add(sql); |
1046 | 181 | fdiaz | String autoTriggerName = getSerialTriggerName(this.table.getName(), column.getName()); |
1047 | 81 | cmartinez | sql = MessageFormat.format(
|
1048 | 95 | jjdelcerro | ADD_SERIAL_COLUMN_TRIGGER_QUERY, |
1049 | 81 | cmartinez | autoTriggerName, |
1050 | 181 | fdiaz | this.table.toString(formatter),
|
1051 | 81 | cmartinez | column.getName(), |
1052 | sequenceName |
||
1053 | 95 | jjdelcerro | ); |
1054 | 81 | cmartinez | sqls.add(sql); |
1055 | } |
||
1056 | 182 | fdiaz | |
1057 | if (column.isGeometry()) {
|
||
1058 | 609 | fdiaz | /**
|
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 | 182 | fdiaz | } |
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 | 609 | fdiaz | 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 | 81 | cmartinez | } |
1121 | 182 | fdiaz | } |
1122 | for (Pair<String, String> pair : renames) { |
||
1123 | 81 | cmartinez | StringBuilder builder = new StringBuilder(); |
1124 | builder.append("ALTER TABLE ");
|
||
1125 | builder.append(this.table.toString());
|
||
1126 | builder.append(" RENAME COLUMN ");
|
||
1127 | 161 | omartinez | builder.append(as_identifier(pair.getLeft())); |
1128 | 81 | cmartinez | builder.append(" TO ");
|
1129 | 161 | omartinez | builder.append(as_identifier(pair.getRight())); |
1130 | 81 | cmartinez | sqls.add(builder.toString()); |
1131 | 182 | fdiaz | } |
1132 | 81 | cmartinez | return sqls;
|
1133 | } |
||
1134 | } |
||
1135 | |||
1136 | @Override
|
||
1137 | protected AlterTableBuilder createAlterTableBuilder() {
|
||
1138 | 778 | jjdelcerro | return new OracleAlterTableBuilder(this); |
1139 | 81 | cmartinez | } |
1140 | 161 | omartinez | |
1141 | 101 | jjdelcerro | @Override
|
1142 | 890 | fdiaz | public CreateIndexBuilder createCreateIndexBuilder() {
|
1143 | 95 | jjdelcerro | return new OracleCreateIndexBuilder(); |
1144 | } |
||
1145 | 161 | omartinez | |
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 | 180 | fdiaz | |
1156 | @Override
|
||
1157 | 178 | fdiaz | protected SelectColumnBuilder createSelectColumnBuilder() {
|
1158 | 495 | jjdelcerro | return new OracleSelectColumnBuilder(this); |
1159 | 178 | fdiaz | } |
1160 | |||
1161 | 180 | fdiaz | public class OracleSelectColumnBuilder extends SelectColumnBuilderBase { |
1162 | |||
1163 | 495 | jjdelcerro | public OracleSelectColumnBuilder(SQLBuilder sqlbuilder) {
|
1164 | super(sqlbuilder);
|
||
1165 | } |
||
1166 | |||
1167 | 178 | fdiaz | @Override
|
1168 | public String toString(Formatter<ExpressionBuilder.Value> formatter) { |
||
1169 | 180 | fdiaz | if (formatter != null && formatter.canApply(this)) { |
1170 | 178 | fdiaz | return formatter.format(this); |
1171 | } |
||
1172 | StringBuilder builder = new StringBuilder(); |
||
1173 | if (this.asGeometry) { |
||
1174 | 609 | fdiaz | 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 | 178 | fdiaz | } else {
|
1180 | 603 | jjdelcerro | if (this.value == null) { |
1181 | builder.append(this.name.toString(formatter));
|
||
1182 | 178 | fdiaz | } 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 | 180 | fdiaz | |
1194 | 178 | fdiaz | @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 | 439 | jjdelcerro | |
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 | 609 | fdiaz | if (precision > 38 && scale < 0) { |
1287 | if (precision > 126) { |
||
1288 | 439 | jjdelcerro | 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 | 609 | fdiaz | return MessageFormat.format(type_decimal_p, precision); |
1297 | 439 | jjdelcerro | } |
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 | 522 | jjdelcerro | @Override
|
1305 | public int getMaxRecomendedSQLLength() { |
||
1306 | return 2048; |
||
1307 | } |
||
1308 | 915 | jjdelcerro | |
1309 | 56 | jjdelcerro | } |