Application: gvSIG desktop

/* gvSIG. Geographic Information System of the Valencian Government

 *

 * Copyright (C) 2007-2016 Infrastructures and Transports Department

 * of the Valencian Government (CIT)

 *

 * This program is free software; you can redistribute it and/or

 * modify it under the terms of the GNU General Public License

 * as published by the Free Software Foundation; either version 3

 * of the License, or (at your option) any later version.

 *

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 * GNU General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,

 * MA  02110-1301, USA.

 *

 */

package org.gvsig.sqlite.dal;

import java.io.File;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import org.apache.commons.codec.binary.Hex;

import org.gvsig.expressionevaluator.GeometryExpressionBuilderHelper.GeometrySupportType;

import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.FIELD_CONFIGURATION_NAME;

import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.FIELD_CONFIGURATION_VALUE;

import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.FIELD_RESOURCES_NAME;

import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.FIELD_RESOURCES_RESOURCE;

import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.TABLE_CONFIGURATION_NAME;

import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.TABLE_RESOURCES_NAME;

import org.gvsig.fmap.dal.exception.DataException;

import org.gvsig.fmap.dal.exception.InitializeException;

import org.gvsig.fmap.dal.resource.exception.AccessResourceException;

import org.gvsig.fmap.dal.spi.DataServerExplorerProviderServices;

import org.gvsig.fmap.dal.spi.DataTransactionServices;

import org.gvsig.fmap.dal.store.jdbc.JDBCConnectionParameters;

import org.gvsig.fmap.dal.store.jdbc.JDBCNewStoreParameters;

import org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorerParameters;

import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCDriverClassNotFoundException;

import org.gvsig.fmap.dal.store.jdbc2.JDBCConnection;

import org.gvsig.fmap.dal.store.jdbc2.JDBCServerExplorer;

import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils;

import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory;

import org.gvsig.fmap.dal.store.jdbc2.spi.AbstractConnectionProvider;

import org.gvsig.fmap.dal.store.jdbc2.spi.ConnectionProvider;

import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCHelperBase;

import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase;

import org.gvsig.fmap.dal.store.jdbc2.spi.SRSSolver;

import org.gvsig.fmap.dal.store.jdbc2.spi.SRSSolverDumb;

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.aggregate.MultiLine;

import org.gvsig.fmap.geom.aggregate.MultiPoint;

import org.gvsig.fmap.geom.aggregate.MultiPolygon;

import org.gvsig.fmap.geom.exception.CreateGeometryException;

import org.gvsig.fmap.geom.primitive.Primitive;

import org.gvsig.fmap.geom.type.GeometryType;

import org.gvsig.sqlite.dal.functions.Functions;

import org.gvsig.sqlite.dal.geopackage.GeopackageGeometryParser;

import org.gvsig.sqlite.dal.geopackage.GeopackageUtils;

import org.gvsig.sqlite.dal.operations.SQLiteOperationsFactory;

import org.gvsig.tools.dispose.DisposeUtils;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.sqlite.SQLiteConfig;

import org.sqlite.SQLiteConfig.TransactionMode;

import org.sqlite.SQLiteConnection;

import org.sqlite.SQLiteOpenMode;

@SuppressWarnings("UseSpecificCatch")

public class SQLiteHelper extends JDBCHelperBase {

    /*

        https://www.jpab.org/Hibernate/H2/embedded/Hibernate/SQLite/embedded.html

    */

    @SuppressWarnings("FieldNameHidesFieldInSuperclass")

    protected static final Logger LOGGER = LoggerFactory.getLogger(SQLiteHelper.class);

    public static class ConnectionProviderImpl extends AbstractConnectionProvider implements ConnectionProvider {

        private static boolean needRegisterDriver = true;

        private SQLiteConnectionParameters connectionParameters;

        public ConnectionProviderImpl(SQLiteConnectionParameters connectionParameters) {

            this.connectionParameters = connectionParameters;

        }

        @Override

        public synchronized Connection getConnection() throws SQLException {

            if (!this.isRegistered()) {

                this.registerDriver();

            }

            File f = SQLiteUtils.getLocalFile(connectionParameters);

            boolean newdb = f != null && !f.exists();

            SQLiteConfig config = new SQLiteConfig();

            config.setSharedCache(true);

            config.enableLoadExtension(true);

//            config.setTransactionMode(TransactionMode.IMMEDIATE);

//            config.setOpenMode(SQLiteOpenMode.NOMUTEX);

            config.setJournalMode(SQLiteConfig.JournalMode.WAL);

            config.setTransactionMode(SQLiteConfig.TransactionMode.DEFERRED);

            config.setSynchronous(SQLiteConfig.SynchronousMode.OFF);

            config.setOpenMode(SQLiteOpenMode.FULLMUTEX);

            config.setPragma(SQLiteConfig.Pragma.CASE_SENSITIVE_LIKE, "true");

            SQLiteConnection conn;

            try {

                conn = org.sqlite.JDBC.createConnection(connectionParameters.getUrl(), config.toProperties());

            } catch (Throwable th) {

                throw th;

            }

            Functions.register_all(conn);

            if (newdb) {

                String[] sqls2 = new String[]{

                    "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 + "\"))",

                    "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 + "\"))"

                };

                for (String sql : sqls2) {

                    try {

                        conn.createStatement().execute(sql);

                    } catch (SQLException ex) {

                        LOGGER.debug("Can't configure gvsig tables.", ex);

                        LOGGER.warn("Can't configure gvsig tables. " + sql);

                        // Ignore this error.

                    }

                }

            }

            return conn;

        }

        private boolean isRegistered() {

            return needRegisterDriver;

        }

        @Override

        public void registerDriver() throws SQLException {

            String className = this.connectionParameters.getJDBCDriverClassName();

            if (className == null) {

                return;

            }

            try {

                Class theClass = Class.forName(className);

                if (theClass == null) {

                    throw new JDBCDriverClassNotFoundException(SQLiteLibrary.NAME, className);

                }

            } catch (Exception e) {

                throw new SQLException("Can't register JDBC driver '" + className + "'.", e);

            }

            needRegisterDriver = false;

        }

        @Override

        public void dispose() {

            if (!DisposeUtils.release(this)) {

                return;

            }

            this.connectionParameters = null;

        }

        @Override

        public boolean isDisposed() {

            return this.connectionParameters == null;

        }

    }

    private ConnectionProvider connectionProvider = null;

    /**

     * Constructor for use only for testing purposes.

     *

     * @param connectionParameters

     * @param connectionProvider

     */

    public SQLiteHelper(JDBCConnectionParameters connectionParameters, ConnectionProvider connectionProvider) {

        super(connectionParameters);

        this.srssolver = new SRSSolverDumb(this);

        this.connectionProvider = connectionProvider;

    }

    public SQLiteHelper(JDBCConnectionParameters connectionParameters) {

        super(connectionParameters);

        this.srssolver = null; // GeopackageUtils.createSRSSolver(this);

    }

    @Override

    public synchronized JDBCConnection getConnection() throws AccessResourceException {

        try {

            SQLiteConnectionParameters connectionParameters = this.getConnectionParameters();

            JDBCConnection conn = (JDBCConnection) DataTransactionServices.getConnection(

                    this.getTransaction(),

                    this.getConnectionProviderKey(connectionParameters)

            );

            if (conn != null) {

                return conn;

            }

            if (this.connectionProvider == null) {

                if (connectionParameters == null) {

                    return null; // Testing mode?

                }

                this.connectionProvider = new ConnectionProviderImpl(connectionParameters);

            }

            JDBCConnection connection = SQLiteJDBCConnection.create(this);

            if (LOGGER.isDebugEnabled()) {

                LOGGER.debug("[" + JDBCUtils.getConnId(connection.get()) + "] getConnection " + connectionProvider.getStatus() + " " + connectionProvider.toString());

            }

            return connection;

        } catch (SQLException ex) {

            throw new AccessResourceException(SQLiteLibrary.NAME, ex);

        }

    }

    @Override

    public ConnectionProvider getConnectionProvider() {

        if (this.connectionProvider == null) {

          SQLiteConnectionParameters connectionParameters = this.getConnectionParameters();

          if( connectionParameters==null ) {

            return null; // Testing mode?

          }

          this.connectionProvider = new ConnectionProviderImpl(connectionParameters);

        }

        return this.connectionProvider;

    }

    @Override

    public SQLiteConnectionParameters getConnectionParameters() {

        return (SQLiteConnectionParameters) super.getConnectionParameters();

    }

    @Override

    public String getConnectionURL() {

        return SQLiteUtils.getConnectionURL(this.getConnectionParameters());

    }

    @Override

    protected String getResourceType() {

        return SQLiteLibrary.NAME;

    }

    @Override

    public String getProviderName() {

        return SQLiteLibrary.NAME;

    }

    @Override

    public JDBCSQLBuilderBase createSQLBuilder() {

        return new SQLiteSQLBuilder(this);

    }

    @Override

    public OperationsFactory getOperations() {

        if (this.operationsFactory == null) {

            this.operationsFactory = new SQLiteOperationsFactory(this);

        }

        return operationsFactory;

    }

    @Override

    public GeometrySupportType getGeometrySupportType() {

        return GeometrySupportType.NATIVE;

//        return GeometrySupportType.EWKB;

    }

    @Override

    public boolean hasSpatialFunctions() {

        return true;

    }

    @Override

    public boolean canWriteGeometry(int geometryType, int geometrySubtype) {

        return true;

    }

    @Override

    public String getQuoteForIdentifiers() {

        return "\"";

    }

    @Override

    public boolean allowAutomaticValues() {

        return true;

    }

    @Override

    public boolean supportOffsetInSelect() {

        return true;

    }

    @Override

    public String getQuoteForStrings() {

        return "'";

    }

    @Override

    public String getSourceId(JDBCStoreParameters parameters) {

        return parameters.getDBName() + "."

                + parameters.getSchema() + "."

                + parameters.getTable();

    }

    @Override

    public JDBCNewStoreParameters createNewStoreParameters() {

        return new SQLiteNewStoreParameters();

    }

    @Override

    public JDBCStoreParameters createOpenStoreParameters() {

        return new SQLiteStoreParameters();

    }

    @Override

    public JDBCServerExplorerParameters createServerExplorerParameters() {

        return new SQLiteExplorerParameters();

    }

    public Geometry forceGeometryType(GeometryType geomtype, Geometry geom) throws CreateGeometryException {

        if (geom == null) {

            return null;

        }

        switch (geomtype.getType()) {

            case Geometry.TYPES.MULTIPOLYGON:

                if (geom.getType() == Geometry.TYPES.POLYGON) {

                    MultiPolygon x = getGeometryManager().createMultiPolygon(geomtype.getSubType());

                    x.addPrimitive((Primitive) geom);

                    geom = x;

                }

                break;

            case Geometry.TYPES.MULTILINE:

                if (geom.getType() == Geometry.TYPES.LINE) {

                    MultiLine x = getGeometryManager().createMultiLine(geomtype.getSubType());

                    x.addPrimitive((Primitive) geom);

                    geom = x;

                }

                break;

            case Geometry.TYPES.MULTIPOINT:

                if (geom.getType() == Geometry.TYPES.POINT) {

                    MultiLine x = getGeometryManager().createMultiLine(geomtype.getSubType());

                    x.addPrimitive((Primitive) geom);

                    geom = x;

                }

                break;

            case Geometry.TYPES.POLYGON:

                if (geom.getType() == Geometry.TYPES.MULTIPOLYGON) {

                    MultiPolygon x = (MultiPolygon) geom;

                    if (x.getPrimitivesNumber() == 1) {

                        geom = x.getPrimitiveAt(0);

                    }

                }

                break;

            case Geometry.TYPES.LINE:

                if (geom.getType() == Geometry.TYPES.MULTILINE) {

                    MultiLine x = (MultiLine) geom;

                    if (x.getPrimitivesNumber() == 1) {

                        geom = x.getPrimitiveAt(0);

                    }

                }

                break;

            case Geometry.TYPES.POINT:

                if (geom.getType() == Geometry.TYPES.MULTIPOINT) {

                    MultiPoint x = (MultiPoint) geom;

                    if (x.getPrimitivesNumber() == 1) {

                        geom = x.getPrimitiveAt(0);

                    }

                }

        }

        return geom;

    }

    @Override

    public SRSSolver getSRSSolver() {

        if (this.srssolver == null) {

            JDBCConnection conn = null;

            try {

                conn = this.getConnection();

                this.srssolver = GeopackageUtils.createSRSSolver((SQLiteConnection) conn.get());

            } catch (Exception ex) {

                throw new RuntimeException("Can't get SRSSolver", ex);

            } finally {

                JDBCConnection.closeQuietly(conn);

            }

        }

        return this.srssolver;

    }

    private GeopackageGeometryParser geometryParser = null;

    @Override

    public Geometry getGeometryFromColumn(ResultSet rs, int index) throws DataException {

        try {

            if( this.getGeometrySupportType() == GeometrySupportType.NATIVE ) {

                byte[] bytes = rs.getBytes(index);

                if( GeopackageUtils.isGeopackageGeometry(bytes) ) {

                    if(geometryParser == null){

                        geometryParser = GeopackageUtils.createGeometryParser();

                    } else {

                        geometryParser.clean();

                    }

                    Geometry geom = geometryParser.parseToGeometry(null, bytes);

                    return geom;

                }

            }

        } catch (Exception ex) {

            LOGGER.debug("Can't process native geometry", ex);

        }

        return super.getGeometryFromColumn(rs, index);

    }

    @Override

    public JDBCServerExplorer createServerExplorer(

            JDBCServerExplorerParameters parameters, 

            DataServerExplorerProviderServices providerServices

        ) throws InitializeException {

        JDBCServerExplorer explorer = new SQLiteExplorer(

                parameters, 

                providerServices, 

                this

        );

        this.initialize(explorer, parameters, null);

        return explorer;

    }

    @Override

    public String getConnectionProviderKey(JDBCConnectionParameters connectionParameters) {

        return super.getConnectionProviderKey(connectionParameters);

    }

 }