Statistics
| Revision:

svn-gvsig-desktop / trunk / org.gvsig.desktop / org.gvsig.desktop.plugin / org.gvsig.sqlite / org.gvsig.sqlite.provider / src / main / java / org / gvsig / sqlite / dal / SQLiteSQLBuilder.java @ 47456

History | View | Annotate | Download (13.3 KB)

1
package org.gvsig.sqlite.dal;
2

    
3
import java.sql.PreparedStatement;
4
import java.sql.SQLException;
5
import java.util.ArrayList;
6
import java.util.Date;
7
import java.util.List;
8
import org.gvsig.expressionevaluator.ExpressionBuilder.Parameter;
9
import org.gvsig.expressionevaluator.Formatter;
10
import org.gvsig.fmap.dal.DataTypes;
11
import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor;
12
import org.gvsig.fmap.dal.feature.FeatureType;
13
import org.gvsig.fmap.dal.feature.spi.FeatureProvider;
14
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase;
15
import org.gvsig.fmap.geom.Geometry;
16
import org.gvsig.fmap.geom.exception.CreateGeometryException;
17
import org.gvsig.sqlite.dal.expressionbuilderformatter.SQLiteFormatter;
18
import org.gvsig.tools.dispose.Disposable;
19

    
20
public class SQLiteSQLBuilder extends JDBCSQLBuilderBase {
21
    
22
    protected Formatter formatter = null;
23
        
24
    public SQLiteSQLBuilder(SQLiteHelper helper) {
25
        super(helper);      
26
        //
27
        // SQLite 4.3.0, SQL functions reference list
28
        //
29
        // http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.3.0.html
30
        //
31
        // https://sqlite.org/lang.html
32
        //
33
        
34
        this.defaultSchema = "";
35
        this.supportSchemas = false;
36
        this.allowAutomaticValues = true;
37
        this.geometrySupportType = this.helper.getGeometrySupportType();
38
        this.hasSpatialFunctions = this.helper.hasSpatialFunctions();
39
        this.expressionBuilder.geometry_support_type(this.geometrySupportType);
40

    
41
        // FIXME
42
        this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table = null;
43
        this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_table = null;
44
         
45
        this.STMT_UPDATE_TABLE_STATISTICS_table = "SELECT UpdateLayerStatistics({0})";
46
        
47
        this.type_boolean = "INTEGER";
48
        this.type_byte = "INTEGER";
49
        this.type_bytearray = "BLOB";
50
        this.type_geometry = "BLOB";
51
        this.type_char = "TEXT";
52
        this.type_date = "TEXT";
53
        this.type_double = "REAL"; 
54
        this.type_decimal_ps = "REAL";
55
        this.type_decimal_p = "REAL";
56
        this.type_float = "REAL";
57
        this.type_int = "INTEGER";
58
        this.type_long = "INTEGER";
59
        this.type_string = "TEXT";
60
        this.type_string_p = "TEXT";
61
        this.type_time = "TEXT";
62
        this.type_timestamp = "TEXT";
63
        this.type_version = "TEXT";
64
        this.type_URI = "TEXT";
65
        this.type_URL = "TEXT";
66
        this.type_FILE = "TEXT";
67
        this.type_FOLDER = "TEXT";           
68
        
69
//        config.set(SQLConfig.ST_ExtentAggregate, "(1)");
70
//        config.set(SQLConfig.ST_UnionAggregate, "(1)");
71
//        config.set(SQLConfig.ST_ExtentAggregate, "(1)");        
72
//        config.set(SQLConfig.ST_UnionAggregate, "(1)");
73
//        config.set(SQLConfig.Find_SRID, "(1)");
74
//        config.set(SQLConfig.ST_SRID, "(1)");
75
//        config.set(SQLConfig.ST_Contains, "(1)");
76
//        config.set(SQLConfig.ST_Crosses, "(1)");
77
//        config.set(SQLConfig.ST_Disjoint, "(1)");
78
//        config.set(SQLConfig.ST_Equals, "(1)");
79
//        config.set(SQLConfig.ST_IsClosed, "(1)");
80
//        config.set(SQLConfig.ST_Overlaps, "(1)");
81
//        config.set(SQLConfig.ST_Touches, "(1)");
82
//        config.set(SQLConfig.ST_Within, "(1)");
83
//        config.set(SQLConfig.ST_Intersects, "(1)");
84
//        config.set(SQLConfig.ST_Simplify, "(1)");
85
//        config.set(SQLConfig.ST_Within, "(1)");
86
//        config.set(SQLConfig.ST_GeomFromWKB, "({0})");
87
//        config.set(SQLConfig.ST_AsBinary, "({0})");
88
//        config.set(SQLConfig.ST_AsEWKB, "({0})");
89
//        config.set(SQLConfig.ST_AsText, "({0})");
90
  
91
    }
92

    
93
    public class SQLiteTableNameBuilderBase extends TableNameBuilderBase {
94

    
95
        @Override
96
        public boolean has_schema() {
97
            return false;
98
        }
99

    
100
        @Override
101
        public boolean has_database() {
102
            return false;
103
        }
104
        
105
    }
106
    
107
    protected class SQLiteCreateTableBuilder extends CreateTableBuilderBase {
108

    
109
        @Override
110
        public List<String> toStrings(Formatter formatter) {
111
            //
112
            // Respecto al base cambia la declaracion de campo automaticos
113
            // SQLite usa AUTOINCREMENT en lugar de SERIAL y que hay que usar
114
            // la funcion AddGeometryColumn para a?adir las columnas de tipo
115
            // geometria.
116
            // El orden en el que hay que declarar las constrains tambien cambia
117
            // respecto al que hay por defecto.
118
            //
119
            List<String> sqls = new ArrayList<>();
120
            StringBuilder builder = new StringBuilder();
121

    
122
            builder.append("CREATE TABLE ");
123
            builder.append(this.table.toString(formatter));
124
            builder.append(" (");
125
            boolean first = true;
126
            for (ColumnDescriptor column : columns) {
127
                if (first) {
128
                    first = false;
129
                } else {
130
                    builder.append(", ");
131
                }
132
                builder.append(as_identifier(column.getName()));
133
                builder.append(" ");
134
                builder.append(
135
                    sqltype(
136
                        column.getType(), 
137
                        column.getSize(),
138
                        column.getPrecision(), 
139
                        column.getScale(), 
140
                        column.getGeometryType(), 
141
                        column.getGeometrySubtype()
142
                    )
143
                );
144
                if (column.isPrimaryKey()) {
145
                    builder.append(" PRIMARY KEY");
146
                }
147
                if( column.isAutomatic() ) {
148
                    builder.append(" AUTOINCREMENT");
149
                }
150
                if (column.getDefaultValue() == null) {
151
                    if (column.allowNulls()) {
152
                        builder.append(" DEFAULT NULL");
153
                    }
154
                } else {
155
                    if( column.getType() == DataTypes.DATE ) {
156
                        builder.append(" DEFAULT ( date('");
157
                        builder.append(column.getDefaultValue().toString());
158
                        builder.append("') )");
159
                    } else {
160
                        builder.append(" DEFAULT '");
161
                        builder.append(column.getDefaultValue().toString());
162
                        builder.append("'");
163
                    }
164
                }
165
                if (!column.allowNulls()) {
166
                    builder.append(" NOT NULL");
167
                }
168
            }
169
            builder.append(" )");
170
            sqls.add(builder.toString());
171
            
172
            return sqls;
173
        }
174
    }
175

    
176
    public class SQLiteSelectBuilderBase extends SelectBuilderBase {
177
        
178
        @Override
179
        protected boolean isValid(StringBuilder message) {
180
            if( message == null ) {
181
                message = new StringBuilder();
182
            }
183
            if( this.has_offset() && !this.has_order_by() ) {
184
                // Algunos gestores de BBDD requieren que se especifique un
185
                // orden para poder usar OFFSET. Como eso parece buena idea para
186
                // asegurar que siempre tengamos los mismo resultados, lo exijimos
187
                // siempre.
188
                message.append("Can't use OFFSET without an ORDER BY.");
189
                return false;
190
            }
191
            return true;
192
        }        
193
        
194
        @Override
195
        public String toString(Formatter formatter) {
196
            // SQLite requiere que si se especifica OFFSET deba especificarse
197
            // LIMIT obligatoriamente. Se le puede poner un LIMIT -1 cuando 
198
            // queramos un OFFSET sin especificar un LIMIT.
199
            StringBuilder builder = new StringBuilder();
200
            if( !isValid(builder) ) {
201
                throw new IllegalStateException(builder.toString());
202
            }
203
            builder.append("SELECT ");
204
            if( this.distinct ) {
205
                builder.append("DISTINCT ");
206
            }
207
            boolean first = true;
208
            for (SelectColumnBuilder column : columns) {
209
                if (first) {
210
                    first = false;
211
                } else {
212
                    builder.append(", ");
213
                }
214
                builder.append(column.toString(formatter));
215
            }
216

    
217
            if ( this.has_from() ) {
218
                builder.append(" FROM ");
219
                builder.append(this.from.toString(formatter));
220
            }
221
            if ( this.has_where() ) {
222
                builder.append(" WHERE ");
223
                builder.append(this.where.toString(formatter));
224
            }
225
            
226
            if( this.has_order_by() ) {
227
                builder.append(" ORDER BY ");
228
                first = true;
229
                for (OrderByBuilder item : this.order_by) {
230
                    if (first) {
231
                        first = false;
232
                    } else {
233
                        builder.append(", ");
234
                    }
235
                    builder.append(item.toString(formatter));                    
236
                }   
237
            }
238
            
239
            if ( this.has_limit() && this.has_offset() ) {
240
                builder.append(" LIMIT ");
241
                builder.append(this.limit);
242
                builder.append(" OFFSET ");
243
                builder.append(this.offset);
244
                
245
            } else if ( this.has_limit()) {
246
                builder.append(" LIMIT ");
247
                builder.append(this.limit);
248

    
249
            } else if ( this.has_offset() ) {
250
                builder.append(" LIMIT -1 OFFSET ");
251
                builder.append(this.offset);    
252
            }
253
            return builder.toString();
254

    
255
        }
256
    }
257

    
258
//    @Override
259
//    public String bytearray(byte[] data) {
260
//        // SQLite usa un formato diferencte para especificar un array de 
261
//        // bytes. En lugar de 0x... usa x'...' .
262
//        StringBuilder builder = new StringBuilder();
263
//        builder.append("x'");
264
//        for (byte abyte : data) {
265
//            int v = abyte & 0xff;
266
//            builder.append(String.format("%02x", v));
267
//        }
268
//        builder.append("'");
269
//        return builder.toString();
270
//    }
271

    
272
    @Override
273
    public Object sqlgeometrydimension(int type, int subtype) {
274
        //'XY' or 2: 2D points, identified by X and Y coordinates
275
        //'XYM': 2D points, identified by X and Y coordinates. Each point stores an M-value (measure) as well
276
        //'XYZ' or 3: 3D points, identified by X, Y and Z coordinates
277
        //'XYZM' or 4: 3D points, identified by X, Y and Z coordinates. Each point stores an M-value (measure) as well
278
        switch(subtype) {
279
            case Geometry.SUBTYPES.GEOM2D:
280
            default:
281
                return "XY";
282
            case Geometry.SUBTYPES.GEOM2DM:
283
                return "XYM";
284
            case Geometry.SUBTYPES.GEOM3D:
285
                return "XYZ";
286
            case Geometry.SUBTYPES.GEOM3DM:
287
                return "XYZM";
288
        }
289
    }
290

    
291
    @Override
292
    public SQLiteHelper getHelper() {
293
        return (SQLiteHelper) this.helper;
294
    }
295
    
296
    @Override
297
    public Disposable setParameters(PreparedStatement st, FeatureProvider feature) {        
298
        try {
299
            FeatureType featureType = feature.getType();
300
            List<Object> values = new ArrayList<>();
301
            for (Parameter parameter : this.parameters()) {
302
                if (parameter.is_constant()) {
303
                    values.add(parameter.value());
304
                } else {
305
                    String name = parameter.name();
306
                    FeatureAttributeDescriptor descriptor = featureType.getAttributeDescriptor(name);
307
                    switch( descriptor.getType() ) {
308
                    case DataTypes.DATE:
309
                        Date value = (Date)(feature.get(name));
310
                        if( value == null ) {
311
                            values.add(null);
312
                        } else {
313
                            values.add(new java.sql.Date(value.getTime()));
314
                        }
315
                        break;
316
                    case DataTypes.GEOMETRY:
317
                        Geometry geom = this.forceGeometryType(
318
                            descriptor.getGeomType(),
319
                            (Geometry)(feature.get(name))
320
                        );
321
                        values.add(geom);
322
                        break;
323
                    default:
324
                        values.add(feature.get(name));
325
                        break;
326
                    }
327
                }
328
            }
329
            return this.setStatementParameters(st, values, this.geometry_support_type());
330
        } catch (SQLException | CreateGeometryException ex) {
331
            String f = "unknow";
332
            try {
333
                f = feature.toString();
334
            } catch (Exception ex2) {
335
                // Do nothing
336
            }
337
            throw new RuntimeException("Can't set parameters to prepared statement from the feature (" + f + ")", ex);
338
        }
339
    }
340
            
341
    @Override
342
    public TableNameBuilder createTableNameBuilder() {
343
        return new SQLiteTableNameBuilderBase();
344
    }
345
    
346
    @Override
347
    protected CreateTableBuilder createCreateTableBuilder() {
348
        return new SQLiteCreateTableBuilder();
349
    }
350

    
351
    @Override
352
    public SelectBuilder createSelectBuilder() {
353
        return new SQLiteSelectBuilderBase();
354
    }
355

    
356
    @Override
357
    public Formatter formatter() {
358
        if( this.formatter==null ) {
359
            this.formatter = new SQLiteFormatter(this);
360
        }
361
        return this.formatter;
362
    }    
363
}