svn-gvsig-desktop / trunk / org.gvsig.desktop / org.gvsig.desktop.compat.cdc / org.gvsig.fmap.dal / org.gvsig.fmap.dal.db / org.gvsig.fmap.dal.db.h2 / src / main / java / org / gvsig / fmap / dal / store / h2 / H2SpatialSQLBuilder.java @ 43546
History | View | Annotate | Download (13.4 KB)
1 | 43377 | jjdelcerro | package org.gvsig.fmap.dal.store.h2; |
---|---|---|---|
2 | |||
3 | import java.sql.PreparedStatement; |
||
4 | import java.text.MessageFormat; |
||
5 | import java.util.ArrayList; |
||
6 | import java.util.Date; |
||
7 | import java.util.List; |
||
8 | import org.gvsig.fmap.dal.DataTypes; |
||
9 | import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor; |
||
10 | import org.gvsig.fmap.dal.feature.FeatureType; |
||
11 | import org.gvsig.fmap.dal.feature.spi.FeatureProvider; |
||
12 | import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils; |
||
13 | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
||
14 | import org.gvsig.fmap.geom.Geometry; |
||
15 | |||
16 | public class H2SpatialSQLBuilder extends JDBCSQLBuilderBase { |
||
17 | |||
18 | private final H2SpatialHelper helper; |
||
19 | |||
20 | public H2SpatialSQLBuilder(H2SpatialHelper helper) {
|
||
21 | super();
|
||
22 | |||
23 | this.helper = helper;
|
||
24 | |||
25 | //
|
||
26 | // H2/H2GIS SQL functions reference list
|
||
27 | //
|
||
28 | // http://www.h2database.com/html/functions.html
|
||
29 | // http://www.h2gis.org/docs/1.3/functions/
|
||
30 | //
|
||
31 | // http://www.h2database.com/html/grammar.html
|
||
32 | //
|
||
33 | // http://www.h2database.com/html/datatypes.html
|
||
34 | //
|
||
35 | //
|
||
36 | |||
37 | config.set(SQLConfig.default_schema, "");
|
||
38 | config.set(SQLConfig.support_schemas, false);
|
||
39 | config.set(SQLConfig.allowAutomaticValues, true);
|
||
40 | config.set(SQLConfig.geometry_type_support, this.helper.getGeometrySupportType());
|
||
41 | config.set(SQLConfig.has_spatial_functions, this.helper.hasSpatialFunctions());
|
||
42 | |||
43 | config.remove_functionality(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table); |
||
44 | config.remove_functionality(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_table); |
||
45 | |||
46 | // config.set(SQLConfig.UPDATE_TABLE_STATISTICS_table,"ANALYZE TABLE {0} SAMPLE_SIZE 0");
|
||
47 | config.set(SQLConfig.UPDATE_TABLE_STATISTICS_table,"ANALYZE SAMPLE_SIZE 0");
|
||
48 | |||
49 | config.set(SQLConfig.ST_GeomFromEWKB, "GeomFromWKB({0}, {1})");
|
||
50 | config.set(SQLConfig.ST_AsEWKB, "AsWKB(({0}))");
|
||
51 | 43388 | jjdelcerro | config.set(SQLConfig.ST_Intersects, "( (({0}) && ({1})) AND ST_Intersects(({0}),({1}) ))");
|
52 | 43546 | jjdelcerro | config.set(SQLConfig.ST_AsBinary, "NVL2({0}, ST_AsBinary({0}), null)");
|
53 | 43377 | jjdelcerro | |
54 | config.set(SQLConfig.lcase, "LOWER({0})");
|
||
55 | config.set(SQLConfig.ucase, "UPPER({0})");
|
||
56 | config.set(SQLConfig.operator_ILIKE, "({0}) ILIKE ({1})");
|
||
57 | config.set(SQLConfig.isNull, "( ({0}) IS NULL )");
|
||
58 | config.set(SQLConfig.notIsNull, "( ({0}) IS NOT NULL )");
|
||
59 | |||
60 | config.set(SQLConfig.type_boolean, "BOOLEAN");
|
||
61 | config.set(SQLConfig.type_byte, "TINYINT");
|
||
62 | config.set(SQLConfig.type_bytearray, "TINYINT");
|
||
63 | config.set(SQLConfig.type_geometry, "GEOMETRY");
|
||
64 | config.set(SQLConfig.type_char, "CHAR");
|
||
65 | config.set(SQLConfig.type_date, "DATE");
|
||
66 | config.set(SQLConfig.type_double, "DOUBLE");
|
||
67 | config.set(SQLConfig.type_numeric_p, "DECIMAL({0})");
|
||
68 | config.set(SQLConfig.type_numeric_ps, "DECIMAL({0},{1})");
|
||
69 | config.set(SQLConfig.type_bigdecimal, "DOUBLE");
|
||
70 | config.set(SQLConfig.type_float, "REAL");
|
||
71 | config.set(SQLConfig.type_int, "INTEGER");
|
||
72 | config.set(SQLConfig.type_long, "BIGINT");
|
||
73 | config.set(SQLConfig.type_string, "VARCHAR");
|
||
74 | config.set(SQLConfig.type_string_p, "VARCHAR({0})");
|
||
75 | config.set(SQLConfig.type_time, "TIME");
|
||
76 | config.set(SQLConfig.type_timestamp, "TIMESTAMP");
|
||
77 | config.set(SQLConfig.type_version, "VARCHAR");
|
||
78 | config.set(SQLConfig.type_URI, "VARCHAR");
|
||
79 | config.set(SQLConfig.type_URL, "VARCHAR");
|
||
80 | config.set(SQLConfig.type_FILE, "VARCHAR");
|
||
81 | config.set(SQLConfig.type_FOLDER, "VARCHAR");
|
||
82 | } |
||
83 | |||
84 | 43388 | jjdelcerro | public class H2SpatialTableNameBuilderBase extends TableNameBuilderBase { |
85 | 43377 | jjdelcerro | |
86 | @Override
|
||
87 | public boolean has_schema() { |
||
88 | return false; |
||
89 | } |
||
90 | |||
91 | @Override
|
||
92 | public boolean has_database() { |
||
93 | return false; |
||
94 | } |
||
95 | |||
96 | } |
||
97 | |||
98 | 43388 | jjdelcerro | protected class H2SpatialCreateTableBuilder extends CreateTableBuilderBase { |
99 | 43377 | jjdelcerro | |
100 | @Override
|
||
101 | public List<String> toStrings() { |
||
102 | |||
103 | List<String> sqls = new ArrayList<>(); |
||
104 | StringBuilder builder = new StringBuilder(); |
||
105 | |||
106 | builder.append("CREATE TABLE ");
|
||
107 | builder.append(this.table.toString());
|
||
108 | builder.append(" (");
|
||
109 | boolean first = true; |
||
110 | for (ColumnDescriptorBuilder column : columns) {
|
||
111 | |||
112 | if (first) {
|
||
113 | first = false;
|
||
114 | } else {
|
||
115 | builder.append(", ");
|
||
116 | } |
||
117 | builder.append(identifier(column.getName())); |
||
118 | builder.append(" ");
|
||
119 | if( column.isGeometry() ) {
|
||
120 | 43379 | jjdelcerro | String h2geomtype = getH2SpatialGeometryTypeFromGeometryType(
|
121 | 43377 | jjdelcerro | column.getGeometryType(), |
122 | column.getGeometrySubtype() |
||
123 | ); |
||
124 | 43379 | jjdelcerro | builder.append(h2geomtype); |
125 | 43377 | jjdelcerro | } else {
|
126 | 43379 | jjdelcerro | builder.append(sqltype(column.getType(), column.getPrecision(), column.getSize())); |
127 | 43377 | jjdelcerro | if (column.isPrimaryKey()) {
|
128 | builder.append(" PRIMARY KEY");
|
||
129 | } |
||
130 | if( column.isAutomatic() ) {
|
||
131 | builder.append(" AUTO_INCREMENT");
|
||
132 | } |
||
133 | if (column.getDefaultValue() == null) { |
||
134 | if (column.allowNulls()) {
|
||
135 | builder.append(" DEFAULT NULL");
|
||
136 | } |
||
137 | } else {
|
||
138 | if( column.getType() == DataTypes.DATE ) {
|
||
139 | 43388 | jjdelcerro | builder.append(" DEFAULT ( TIMESTAMP '");
|
140 | Date d = (Date) column.getDefaultValue(); |
||
141 | builder.append(MessageFormat.format( "{0,date,yyyy-MM-dd HH:mm:ss.S}",d)); |
||
142 | builder.append("' )");
|
||
143 | 43377 | jjdelcerro | } else {
|
144 | builder.append(" DEFAULT '");
|
||
145 | builder.append(column.getDefaultValue().toString()); |
||
146 | builder.append("'");
|
||
147 | } |
||
148 | } |
||
149 | } |
||
150 | if (!column.allowNulls()) {
|
||
151 | builder.append(" NOT NULL");
|
||
152 | } |
||
153 | } |
||
154 | builder.append(" )");
|
||
155 | sqls.add(builder.toString()); |
||
156 | |||
157 | return sqls;
|
||
158 | } |
||
159 | |||
160 | 43379 | jjdelcerro | public String getH2SpatialGeometryTypeFromGeometryType(int geomtype, int geomsubtype) { |
161 | 43377 | jjdelcerro | //
|
162 | // https://github.com/orbisgis/h2gis/wiki/1.-Spatial-data#geometry-columns-view
|
||
163 | //
|
||
164 | |||
165 | switch(geomtype) {
|
||
166 | case Geometry.TYPES.POINT:
|
||
167 | 43379 | jjdelcerro | return "POINT"; |
168 | 43377 | jjdelcerro | case Geometry.TYPES.MULTIPOINT:
|
169 | 43379 | jjdelcerro | return "MULTIPOINT"; |
170 | 43377 | jjdelcerro | case Geometry.TYPES.LINE:
|
171 | 43379 | jjdelcerro | return "LINESTRING"; |
172 | 43377 | jjdelcerro | case Geometry.TYPES.MULTILINE:
|
173 | 43379 | jjdelcerro | return "MULTILINESTRING"; |
174 | 43377 | jjdelcerro | case Geometry.TYPES.POLYGON:
|
175 | 43379 | jjdelcerro | return "POLYGON"; |
176 | 43377 | jjdelcerro | case Geometry.TYPES.MULTIPOLYGON:
|
177 | 43379 | jjdelcerro | return "MULTIPOLYGON"; |
178 | 43377 | jjdelcerro | default:
|
179 | 43379 | jjdelcerro | return "GEOMETRY"; |
180 | 43377 | jjdelcerro | } |
181 | } |
||
182 | } |
||
183 | |||
184 | 43388 | jjdelcerro | public class H2SpatialSelectBuilderBase extends SelectBuilderBase { |
185 | 43377 | jjdelcerro | |
186 | protected boolean isValid(StringBuilder message) { |
||
187 | if( message == null ) { |
||
188 | message = new StringBuilder(); |
||
189 | } |
||
190 | if( this.has_offset() && !this.has_order_by() ) { |
||
191 | // Algunos gestores de BBDD requieren que se especifique un
|
||
192 | // orden para poder usar OFFSET. Como eso parece buena idea para
|
||
193 | // asegurar que siempre tengamos los mismo resultados, lo exijimos
|
||
194 | // siempre.
|
||
195 | message.append("Can't use OFFSET without an ORDER BY.");
|
||
196 | return false; |
||
197 | } |
||
198 | return true; |
||
199 | } |
||
200 | |||
201 | @Override
|
||
202 | public String toString() { |
||
203 | StringBuilder builder = new StringBuilder(); |
||
204 | if( !isValid(builder) ) {
|
||
205 | throw new IllegalStateException(builder.toString()); |
||
206 | } |
||
207 | builder.append("SELECT ");
|
||
208 | if( this.distinct ) { |
||
209 | builder.append("DISTINCT ");
|
||
210 | } |
||
211 | boolean first = true; |
||
212 | for (SelectColumnBuilder column : columns) {
|
||
213 | if (first) {
|
||
214 | first = false;
|
||
215 | } else {
|
||
216 | builder.append(", ");
|
||
217 | } |
||
218 | builder.append(column.toString()); |
||
219 | } |
||
220 | |||
221 | if ( this.has_from() ) { |
||
222 | builder.append(" FROM ");
|
||
223 | builder.append(this.from.toString());
|
||
224 | } |
||
225 | if ( this.has_where() ) { |
||
226 | builder.append(" WHERE ");
|
||
227 | builder.append(this.where.toString());
|
||
228 | } |
||
229 | |||
230 | if( this.has_order_by() ) { |
||
231 | builder.append(" ORDER BY ");
|
||
232 | first = true;
|
||
233 | for (OrderByBuilder item : this.order_by) { |
||
234 | if (first) {
|
||
235 | first = false;
|
||
236 | } else {
|
||
237 | builder.append(", ");
|
||
238 | } |
||
239 | builder.append(item.toString()); |
||
240 | } |
||
241 | } |
||
242 | |||
243 | if ( this.has_limit() && this.has_offset() ) { |
||
244 | builder.append(" LIMIT ");
|
||
245 | builder.append(this.limit);
|
||
246 | builder.append(" OFFSET ");
|
||
247 | builder.append(this.offset);
|
||
248 | |||
249 | } else if ( this.has_limit()) { |
||
250 | builder.append(" LIMIT ");
|
||
251 | builder.append(this.limit);
|
||
252 | |||
253 | } else if ( this.has_offset() ) { |
||
254 | builder.append(" LIMIT -1 OFFSET ");
|
||
255 | builder.append(this.offset);
|
||
256 | } |
||
257 | return builder.toString();
|
||
258 | |||
259 | } |
||
260 | } |
||
261 | |||
262 | @Override
|
||
263 | public String bytearray(byte[] data) { |
||
264 | 43388 | jjdelcerro | // H2Spatial usa un formato diferencte para especificar un array de
|
265 | 43377 | jjdelcerro | // bytes. En lugar de 0x... usa x'...' .
|
266 | StringBuilder builder = new StringBuilder(); |
||
267 | builder.append("x'");
|
||
268 | for (byte abyte : data) { |
||
269 | int v = abyte & 0xff; |
||
270 | builder.append(String.format("%02x", v)); |
||
271 | } |
||
272 | builder.append("'");
|
||
273 | return builder.toString();
|
||
274 | } |
||
275 | |||
276 | @Override
|
||
277 | public Object sqlgeometrydimension(int type, int subtype) { |
||
278 | //'XY' or 2: 2D points, identified by X and Y coordinates
|
||
279 | //'XYM': 2D points, identified by X and Y coordinates. Each point stores an M-value (measure) as well
|
||
280 | //'XYZ' or 3: 3D points, identified by X, Y and Z coordinates
|
||
281 | //'XYZM' or 4: 3D points, identified by X, Y and Z coordinates. Each point stores an M-value (measure) as well
|
||
282 | switch(subtype) {
|
||
283 | case Geometry.SUBTYPES.GEOM2D:
|
||
284 | default:
|
||
285 | return "XY"; |
||
286 | case Geometry.SUBTYPES.GEOM2DM:
|
||
287 | return "XYM"; |
||
288 | case Geometry.SUBTYPES.GEOM3D:
|
||
289 | return "XYZ"; |
||
290 | case Geometry.SUBTYPES.GEOM3DM:
|
||
291 | return "XYZM"; |
||
292 | } |
||
293 | } |
||
294 | |||
295 | public H2SpatialHelper getHelper() {
|
||
296 | return this.helper; |
||
297 | } |
||
298 | |||
299 | @Override
|
||
300 | public void setParameters(PreparedStatement st, FeatureProvider feature) { |
||
301 | try {
|
||
302 | FeatureType featureType = feature.getType(); |
||
303 | List<Object> values = new ArrayList<>(); |
||
304 | for (Parameter parameter : this.getParameters()) { |
||
305 | if (parameter.is_constant()) {
|
||
306 | values.add(parameter.getValue()); |
||
307 | } else {
|
||
308 | String name = parameter.getName();
|
||
309 | FeatureAttributeDescriptor descriptor = featureType.getAttributeDescriptor(name); |
||
310 | switch( descriptor.getType() ) {
|
||
311 | case DataTypes.DATE:
|
||
312 | Date value = (Date)(feature.get(name)); |
||
313 | if( value == null ) { |
||
314 | values.add(null);
|
||
315 | } else {
|
||
316 | values.add(value.getTime()); |
||
317 | } |
||
318 | break;
|
||
319 | case DataTypes.GEOMETRY:
|
||
320 | Geometry geom = this.getHelper().forceGeometryType(
|
||
321 | descriptor.getGeomType(), |
||
322 | (Geometry)(feature.get(name)) |
||
323 | ); |
||
324 | values.add(geom); |
||
325 | break;
|
||
326 | default:
|
||
327 | values.add(feature.get(name)); |
||
328 | break;
|
||
329 | } |
||
330 | } |
||
331 | } |
||
332 | JDBCUtils.setObjects(st, values, this.geometry_support_type());
|
||
333 | } catch (Exception ex) { |
||
334 | String f = "unknow"; |
||
335 | try {
|
||
336 | f = feature.toString(); |
||
337 | } catch (Exception ex2) { |
||
338 | // Do nothing
|
||
339 | } |
||
340 | throw new RuntimeException("Can't set parameters to prepared statement from the feature (" + f + ")", ex); |
||
341 | } |
||
342 | } |
||
343 | |||
344 | @Override
|
||
345 | protected TableNameBuilder createTableNameBuilder() {
|
||
346 | 43388 | jjdelcerro | return new H2SpatialTableNameBuilderBase(); |
347 | 43377 | jjdelcerro | } |
348 | |||
349 | @Override
|
||
350 | protected CreateTableBuilder createCreateTableBuilder() {
|
||
351 | 43388 | jjdelcerro | return new H2SpatialCreateTableBuilder(); |
352 | 43377 | jjdelcerro | } |
353 | |||
354 | @Override
|
||
355 | protected SelectBuilder createSelectBuilder() {
|
||
356 | 43388 | jjdelcerro | return new H2SpatialSelectBuilderBase(); |
357 | 43377 | jjdelcerro | } |
358 | |||
359 | } |