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 / SQLiteHelper.java @ 47779
History | View | Annotate | Download (16.3 KB)
1 | 47456 | jjdelcerro | /* gvSIG. Geographic Information System of the Valencian Government
|
---|---|---|---|
2 | *
|
||
3 | * Copyright (C) 2007-2016 Infrastructures and Transports Department
|
||
4 | * of the Valencian Government (CIT)
|
||
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 | */
|
||
22 | package org.gvsig.sqlite.dal; |
||
23 | |||
24 | import java.io.File; |
||
25 | import java.sql.Connection; |
||
26 | import java.sql.ResultSet; |
||
27 | import java.sql.SQLException; |
||
28 | 47579 | fdiaz | import org.apache.commons.codec.binary.Hex; |
29 | 47456 | jjdelcerro | import org.gvsig.expressionevaluator.GeometryExpressionBuilderHelper.GeometrySupportType; |
30 | import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.FIELD_CONFIGURATION_NAME; |
||
31 | import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.FIELD_CONFIGURATION_VALUE; |
||
32 | import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.FIELD_RESOURCES_NAME; |
||
33 | import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.FIELD_RESOURCES_RESOURCE; |
||
34 | import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.TABLE_CONFIGURATION_NAME; |
||
35 | import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.TABLE_RESOURCES_NAME; |
||
36 | import org.gvsig.fmap.dal.exception.DataException; |
||
37 | 47542 | jjdelcerro | import org.gvsig.fmap.dal.exception.InitializeException; |
38 | 47456 | jjdelcerro | import org.gvsig.fmap.dal.resource.exception.AccessResourceException; |
39 | 47542 | jjdelcerro | import org.gvsig.fmap.dal.spi.DataServerExplorerProviderServices; |
40 | 47456 | jjdelcerro | import org.gvsig.fmap.dal.spi.DataTransactionServices; |
41 | import org.gvsig.fmap.dal.store.jdbc.JDBCConnectionParameters; |
||
42 | import org.gvsig.fmap.dal.store.jdbc.JDBCNewStoreParameters; |
||
43 | import org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorerParameters; |
||
44 | import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters; |
||
45 | import org.gvsig.fmap.dal.store.jdbc.exception.JDBCDriverClassNotFoundException; |
||
46 | import org.gvsig.fmap.dal.store.jdbc2.JDBCConnection; |
||
47 | 47542 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.JDBCServerExplorer; |
48 | 47456 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils; |
49 | import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory; |
||
50 | import org.gvsig.fmap.dal.store.jdbc2.spi.AbstractConnectionProvider; |
||
51 | import org.gvsig.fmap.dal.store.jdbc2.spi.ConnectionProvider; |
||
52 | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCHelperBase; |
||
53 | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
||
54 | import org.gvsig.fmap.dal.store.jdbc2.spi.SRSSolver; |
||
55 | import org.gvsig.fmap.dal.store.jdbc2.spi.SRSSolverDumb; |
||
56 | import org.gvsig.fmap.geom.Geometry; |
||
57 | import org.gvsig.fmap.geom.aggregate.MultiLine; |
||
58 | import org.gvsig.fmap.geom.aggregate.MultiPoint; |
||
59 | import org.gvsig.fmap.geom.aggregate.MultiPolygon; |
||
60 | import org.gvsig.fmap.geom.exception.CreateGeometryException; |
||
61 | import org.gvsig.fmap.geom.primitive.Primitive; |
||
62 | import org.gvsig.fmap.geom.type.GeometryType; |
||
63 | import org.gvsig.sqlite.dal.functions.Functions; |
||
64 | import org.gvsig.sqlite.dal.geopackage.GeopackageGeometryParser; |
||
65 | import org.gvsig.sqlite.dal.geopackage.GeopackageUtils; |
||
66 | import org.gvsig.sqlite.dal.operations.SQLiteOperationsFactory; |
||
67 | import org.gvsig.tools.dispose.DisposeUtils; |
||
68 | import org.slf4j.Logger; |
||
69 | import org.slf4j.LoggerFactory; |
||
70 | import org.sqlite.SQLiteConfig; |
||
71 | import org.sqlite.SQLiteConfig.TransactionMode; |
||
72 | import org.sqlite.SQLiteConnection; |
||
73 | 47579 | fdiaz | import org.sqlite.SQLiteOpenMode; |
74 | 47456 | jjdelcerro | |
75 | @SuppressWarnings("UseSpecificCatch") |
||
76 | public class SQLiteHelper extends JDBCHelperBase { |
||
77 | |||
78 | /*
|
||
79 | https://www.jpab.org/Hibernate/H2/embedded/Hibernate/SQLite/embedded.html
|
||
80 | */
|
||
81 | |||
82 | @SuppressWarnings("FieldNameHidesFieldInSuperclass") |
||
83 | protected static final Logger LOGGER = LoggerFactory.getLogger(SQLiteHelper.class); |
||
84 | |||
85 | public static class ConnectionProviderImpl extends AbstractConnectionProvider implements ConnectionProvider { |
||
86 | |||
87 | private static boolean needRegisterDriver = true; |
||
88 | |||
89 | private SQLiteConnectionParameters connectionParameters;
|
||
90 | |||
91 | public ConnectionProviderImpl(SQLiteConnectionParameters connectionParameters) {
|
||
92 | this.connectionParameters = connectionParameters;
|
||
93 | } |
||
94 | |||
95 | @Override
|
||
96 | public synchronized Connection getConnection() throws SQLException { |
||
97 | if (!this.isRegistered()) { |
||
98 | this.registerDriver();
|
||
99 | } |
||
100 | |||
101 | File f = SQLiteUtils.getLocalFile(connectionParameters);
|
||
102 | boolean newdb = f != null && !f.exists(); |
||
103 | |||
104 | SQLiteConfig config = new SQLiteConfig();
|
||
105 | config.setSharedCache(true);
|
||
106 | config.enableLoadExtension(true);
|
||
107 | 47606 | fdiaz | // config.setTransactionMode(TransactionMode.IMMEDIATE);
|
108 | // config.setOpenMode(SQLiteOpenMode.NOMUTEX);
|
||
109 | 47579 | fdiaz | |
110 | 47606 | fdiaz | config.setJournalMode(SQLiteConfig.JournalMode.WAL); |
111 | config.setTransactionMode(SQLiteConfig.TransactionMode.DEFERRED); |
||
112 | config.setSynchronous(SQLiteConfig.SynchronousMode.OFF); |
||
113 | config.setOpenMode(SQLiteOpenMode.FULLMUTEX); |
||
114 | 47579 | fdiaz | |
115 | |||
116 | 47456 | jjdelcerro | config.setPragma(SQLiteConfig.Pragma.CASE_SENSITIVE_LIKE, "true");
|
117 | |||
118 | SQLiteConnection conn; |
||
119 | |||
120 | try {
|
||
121 | conn = org.sqlite.JDBC.createConnection(connectionParameters.getUrl(), config.toProperties()); |
||
122 | } catch (Throwable th) { |
||
123 | throw th;
|
||
124 | } |
||
125 | |||
126 | Functions.register_all(conn); |
||
127 | |||
128 | if (newdb) {
|
||
129 | String[] sqls2 = new String[]{ |
||
130 | 47579 | fdiaz | "CREATE TABLE IF NOT EXISTS \"" + TABLE_RESOURCES_NAME + "\"(\"" + FIELD_RESOURCES_NAME + "\" VARCHAR(150) NOT NULL, \"" + FIELD_RESOURCES_RESOURCE + "\" BLOB DEFAULT NULL , PRIMARY KEY(\"" + FIELD_RESOURCES_NAME + "\"))", |
131 | "CREATE TABLE IF NOT EXISTS \"" + TABLE_CONFIGURATION_NAME + "\"(\"" + FIELD_CONFIGURATION_NAME + "\" VARCHAR(200) NOT NULL, \"" + FIELD_CONFIGURATION_VALUE + "\" CLOB DEFAULT NULL, PRIMARY KEY(\"" + FIELD_CONFIGURATION_NAME + "\"))" |
||
132 | }; |
||
133 | 47456 | jjdelcerro | for (String sql : sqls2) { |
134 | try {
|
||
135 | conn.createStatement().execute(sql); |
||
136 | } catch (SQLException ex) { |
||
137 | LOGGER.debug("Can't configure gvsig tables.", ex);
|
||
138 | LOGGER.warn("Can't configure gvsig tables. " + sql);
|
||
139 | // Ignore this error.
|
||
140 | } |
||
141 | } |
||
142 | } |
||
143 | return conn;
|
||
144 | } |
||
145 | |||
146 | private boolean isRegistered() { |
||
147 | return needRegisterDriver;
|
||
148 | } |
||
149 | |||
150 | @Override
|
||
151 | public void registerDriver() throws SQLException { |
||
152 | String className = this.connectionParameters.getJDBCDriverClassName(); |
||
153 | if (className == null) { |
||
154 | return;
|
||
155 | } |
||
156 | try {
|
||
157 | Class theClass = Class.forName(className); |
||
158 | if (theClass == null) { |
||
159 | throw new JDBCDriverClassNotFoundException(SQLiteLibrary.NAME, className); |
||
160 | } |
||
161 | } catch (Exception e) { |
||
162 | throw new SQLException("Can't register JDBC driver '" + className + "'.", e); |
||
163 | } |
||
164 | needRegisterDriver = false;
|
||
165 | } |
||
166 | |||
167 | @Override
|
||
168 | public void dispose() { |
||
169 | if (!DisposeUtils.release(this)) { |
||
170 | return;
|
||
171 | } |
||
172 | this.connectionParameters = null; |
||
173 | } |
||
174 | |||
175 | @Override
|
||
176 | public boolean isDisposed() { |
||
177 | return this.connectionParameters == null; |
||
178 | } |
||
179 | |||
180 | } |
||
181 | |||
182 | private ConnectionProvider connectionProvider = null; |
||
183 | |||
184 | /**
|
||
185 | * Constructor for use only for testing purposes.
|
||
186 | *
|
||
187 | * @param connectionParameters
|
||
188 | * @param connectionProvider
|
||
189 | */
|
||
190 | public SQLiteHelper(JDBCConnectionParameters connectionParameters, ConnectionProvider connectionProvider) {
|
||
191 | super(connectionParameters);
|
||
192 | this.srssolver = new SRSSolverDumb(this); |
||
193 | this.connectionProvider = connectionProvider;
|
||
194 | } |
||
195 | |||
196 | public SQLiteHelper(JDBCConnectionParameters connectionParameters) {
|
||
197 | super(connectionParameters);
|
||
198 | this.srssolver = null; // GeopackageUtils.createSRSSolver(this); |
||
199 | } |
||
200 | 47579 | fdiaz | |
201 | 47456 | jjdelcerro | @Override
|
202 | public synchronized JDBCConnection getConnection() throws AccessResourceException { |
||
203 | try {
|
||
204 | SQLiteConnectionParameters connectionParameters = this.getConnectionParameters();
|
||
205 | JDBCConnection conn = (JDBCConnection) DataTransactionServices.getConnection( |
||
206 | 47611 | fdiaz | this.getTransaction(),
|
207 | 47456 | jjdelcerro | this.getConnectionProviderKey(connectionParameters)
|
208 | ); |
||
209 | if (conn != null) { |
||
210 | return conn;
|
||
211 | } |
||
212 | if (this.connectionProvider == null) { |
||
213 | if (connectionParameters == null) { |
||
214 | return null; // Testing mode? |
||
215 | } |
||
216 | this.connectionProvider = new ConnectionProviderImpl(connectionParameters); |
||
217 | } |
||
218 | 47579 | fdiaz | JDBCConnection connection = SQLiteJDBCConnection.create(this);
|
219 | 47456 | jjdelcerro | if (LOGGER.isDebugEnabled()) {
|
220 | LOGGER.debug("[" + JDBCUtils.getConnId(connection.get()) + "] getConnection " + connectionProvider.getStatus() + " " + connectionProvider.toString()); |
||
221 | } |
||
222 | return connection;
|
||
223 | } catch (SQLException ex) { |
||
224 | throw new AccessResourceException(SQLiteLibrary.NAME, ex); |
||
225 | } |
||
226 | } |
||
227 | |||
228 | @Override
|
||
229 | 47579 | fdiaz | public ConnectionProvider getConnectionProvider() {
|
230 | if (this.connectionProvider == null) { |
||
231 | SQLiteConnectionParameters connectionParameters = this.getConnectionParameters();
|
||
232 | if( connectionParameters==null ) { |
||
233 | return null; // Testing mode? |
||
234 | } |
||
235 | this.connectionProvider = new ConnectionProviderImpl(connectionParameters); |
||
236 | } |
||
237 | return this.connectionProvider; |
||
238 | } |
||
239 | |||
240 | |||
241 | @Override
|
||
242 | 47456 | jjdelcerro | public SQLiteConnectionParameters getConnectionParameters() {
|
243 | return (SQLiteConnectionParameters) super.getConnectionParameters(); |
||
244 | } |
||
245 | |||
246 | @Override
|
||
247 | public String getConnectionURL() { |
||
248 | return SQLiteUtils.getConnectionURL(this.getConnectionParameters()); |
||
249 | } |
||
250 | |||
251 | @Override
|
||
252 | protected String getResourceType() { |
||
253 | return SQLiteLibrary.NAME;
|
||
254 | } |
||
255 | |||
256 | @Override
|
||
257 | public String getProviderName() { |
||
258 | return SQLiteLibrary.NAME;
|
||
259 | } |
||
260 | |||
261 | @Override
|
||
262 | public JDBCSQLBuilderBase createSQLBuilder() {
|
||
263 | return new SQLiteSQLBuilder(this); |
||
264 | } |
||
265 | |||
266 | @Override
|
||
267 | public OperationsFactory getOperations() {
|
||
268 | if (this.operationsFactory == null) { |
||
269 | this.operationsFactory = new SQLiteOperationsFactory(this); |
||
270 | } |
||
271 | return operationsFactory;
|
||
272 | } |
||
273 | |||
274 | @Override
|
||
275 | public GeometrySupportType getGeometrySupportType() {
|
||
276 | return GeometrySupportType.NATIVE;
|
||
277 | // return GeometrySupportType.EWKB;
|
||
278 | } |
||
279 | |||
280 | @Override
|
||
281 | public boolean hasSpatialFunctions() { |
||
282 | return true; |
||
283 | } |
||
284 | |||
285 | @Override
|
||
286 | public boolean canWriteGeometry(int geometryType, int geometrySubtype) { |
||
287 | return true; |
||
288 | } |
||
289 | |||
290 | @Override
|
||
291 | public String getQuoteForIdentifiers() { |
||
292 | return "\""; |
||
293 | } |
||
294 | |||
295 | @Override
|
||
296 | public boolean allowAutomaticValues() { |
||
297 | return true; |
||
298 | } |
||
299 | |||
300 | @Override
|
||
301 | public boolean supportOffsetInSelect() { |
||
302 | return true; |
||
303 | } |
||
304 | |||
305 | @Override
|
||
306 | public String getQuoteForStrings() { |
||
307 | return "'"; |
||
308 | } |
||
309 | |||
310 | @Override
|
||
311 | public String getSourceId(JDBCStoreParameters parameters) { |
||
312 | return parameters.getDBName() + "." |
||
313 | + parameters.getSchema() + "."
|
||
314 | + parameters.getTable(); |
||
315 | } |
||
316 | |||
317 | @Override
|
||
318 | public JDBCNewStoreParameters createNewStoreParameters() {
|
||
319 | return new SQLiteNewStoreParameters(); |
||
320 | } |
||
321 | |||
322 | @Override
|
||
323 | public JDBCStoreParameters createOpenStoreParameters() {
|
||
324 | return new SQLiteStoreParameters(); |
||
325 | } |
||
326 | |||
327 | @Override
|
||
328 | public JDBCServerExplorerParameters createServerExplorerParameters() {
|
||
329 | return new SQLiteExplorerParameters(); |
||
330 | } |
||
331 | |||
332 | public Geometry forceGeometryType(GeometryType geomtype, Geometry geom) throws CreateGeometryException { |
||
333 | if (geom == null) { |
||
334 | return null; |
||
335 | } |
||
336 | switch (geomtype.getType()) {
|
||
337 | case Geometry.TYPES.MULTIPOLYGON:
|
||
338 | if (geom.getType() == Geometry.TYPES.POLYGON) {
|
||
339 | MultiPolygon x = getGeometryManager().createMultiPolygon(geomtype.getSubType()); |
||
340 | x.addPrimitive((Primitive) geom); |
||
341 | geom = x; |
||
342 | } |
||
343 | break;
|
||
344 | case Geometry.TYPES.MULTILINE:
|
||
345 | if (geom.getType() == Geometry.TYPES.LINE) {
|
||
346 | MultiLine x = getGeometryManager().createMultiLine(geomtype.getSubType()); |
||
347 | x.addPrimitive((Primitive) geom); |
||
348 | geom = x; |
||
349 | } |
||
350 | break;
|
||
351 | case Geometry.TYPES.MULTIPOINT:
|
||
352 | if (geom.getType() == Geometry.TYPES.POINT) {
|
||
353 | MultiLine x = getGeometryManager().createMultiLine(geomtype.getSubType()); |
||
354 | x.addPrimitive((Primitive) geom); |
||
355 | geom = x; |
||
356 | } |
||
357 | break;
|
||
358 | case Geometry.TYPES.POLYGON:
|
||
359 | if (geom.getType() == Geometry.TYPES.MULTIPOLYGON) {
|
||
360 | MultiPolygon x = (MultiPolygon) geom; |
||
361 | if (x.getPrimitivesNumber() == 1) { |
||
362 | geom = x.getPrimitiveAt(0);
|
||
363 | } |
||
364 | } |
||
365 | break;
|
||
366 | case Geometry.TYPES.LINE:
|
||
367 | if (geom.getType() == Geometry.TYPES.MULTILINE) {
|
||
368 | MultiLine x = (MultiLine) geom; |
||
369 | if (x.getPrimitivesNumber() == 1) { |
||
370 | geom = x.getPrimitiveAt(0);
|
||
371 | } |
||
372 | } |
||
373 | break;
|
||
374 | case Geometry.TYPES.POINT:
|
||
375 | if (geom.getType() == Geometry.TYPES.MULTIPOINT) {
|
||
376 | MultiPoint x = (MultiPoint) geom; |
||
377 | if (x.getPrimitivesNumber() == 1) { |
||
378 | geom = x.getPrimitiveAt(0);
|
||
379 | } |
||
380 | } |
||
381 | } |
||
382 | return geom;
|
||
383 | } |
||
384 | |||
385 | @Override
|
||
386 | public SRSSolver getSRSSolver() {
|
||
387 | if (this.srssolver == null) { |
||
388 | 47606 | fdiaz | JDBCConnection conn = null;
|
389 | 47456 | jjdelcerro | try {
|
390 | 47606 | fdiaz | conn = this.getConnection();
|
391 | this.srssolver = GeopackageUtils.createSRSSolver((SQLiteConnection) conn.get());
|
||
392 | 47456 | jjdelcerro | } catch (Exception ex) { |
393 | throw new RuntimeException("Can't get SRSSolver", ex); |
||
394 | 47606 | fdiaz | } finally {
|
395 | JDBCConnection.closeQuietly(conn); |
||
396 | 47456 | jjdelcerro | } |
397 | } |
||
398 | return this.srssolver; |
||
399 | } |
||
400 | 47606 | fdiaz | |
401 | private GeopackageGeometryParser geometryParser = null; |
||
402 | |||
403 | 47456 | jjdelcerro | @Override
|
404 | public Geometry getGeometryFromColumn(ResultSet rs, int index) throws DataException { |
||
405 | try {
|
||
406 | if( this.getGeometrySupportType() == GeometrySupportType.NATIVE ) { |
||
407 | byte[] bytes = rs.getBytes(index); |
||
408 | if( GeopackageUtils.isGeopackageGeometry(bytes) ) {
|
||
409 | 47606 | fdiaz | if(geometryParser == null){ |
410 | geometryParser = GeopackageUtils.createGeometryParser(); |
||
411 | } else {
|
||
412 | geometryParser.clean(); |
||
413 | } |
||
414 | Geometry geom = geometryParser.parseToGeometry(null, bytes);
|
||
415 | 47456 | jjdelcerro | return geom;
|
416 | } |
||
417 | } |
||
418 | } catch (Exception ex) { |
||
419 | LOGGER.debug("Can't process native geometry", ex);
|
||
420 | } |
||
421 | return super.getGeometryFromColumn(rs, index); |
||
422 | } |
||
423 | |||
424 | 47542 | jjdelcerro | @Override
|
425 | public JDBCServerExplorer createServerExplorer(
|
||
426 | JDBCServerExplorerParameters parameters, |
||
427 | DataServerExplorerProviderServices providerServices |
||
428 | ) throws InitializeException {
|
||
429 | |||
430 | JDBCServerExplorer explorer = new SQLiteExplorer(
|
||
431 | parameters, |
||
432 | providerServices, |
||
433 | this
|
||
434 | ); |
||
435 | this.initialize(explorer, parameters, null); |
||
436 | return explorer;
|
||
437 | } |
||
438 | 47579 | fdiaz | |
439 | @Override
|
||
440 | public String getConnectionProviderKey(JDBCConnectionParameters connectionParameters) { |
||
441 | return super.getConnectionProviderKey(connectionParameters); |
||
442 | } |
||
443 | |||
444 | 47542 | jjdelcerro | } |