gvSIG data provider for Sqlite

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

    <modelVersion>4.0.0</modelVersion>

    <artifactId>org.gvsig.spatialite</artifactId>

    <version>1.0.5</version>

    <packaging>pom</packaging>

    <name>${project.artifactId}</name>

    <description>SpatiaLite support for DAL and gvSIG</description>

    <parent>

        <groupId>org.gvsig</groupId>

        <artifactId>org.gvsig.desktop</artifactId>

        <version>2.0.195</version>

    </parent>

    <url>https://devel.gvsig.org/redmine/projects/gvsig-sqlite</url>

    <scm>

        <connection>scm:svn:https://devel.gvsig.org/svn/gvsig-sqlite/tags/org.gvsig.spatialite-1.0.5</connection>

        <developerConnection>scm:svn:https://devel.gvsig.org/svn/gvsig-sqlite/tags/org.gvsig.spatialite-1.0.5</developerConnection>

        <url>https://devel.gvsig.org/redmine/projects/gvsig-sqlite/repository/show/tags/org.gvsig.spatialite-1.0.5</url>

    </scm>

    <repositories>

        <repository>

            <id>gvsig-public-http-repository</id>

            <name>gvSIG maven public HTTP repository</name>

            <url>http://devel.gvsig.org/m2repo/j2se</url>

            <releases>

                <enabled>true</enabled>

                <updatePolicy>daily</updatePolicy>

                <checksumPolicy>warn</checksumPolicy>

            </releases>

            <snapshots>

                <enabled>true</enabled>

                <updatePolicy>daily</updatePolicy>

                <checksumPolicy>warn</checksumPolicy>

            </snapshots>

        </repository>

    </repositories>

    <build>

        <plugins>

            <plugin>

                <groupId>org.apache.maven.plugins</groupId>

                <artifactId>maven-release-plugin</artifactId>

                <configuration>

                    <tagBase>https://devel.gvsig.org/svn/gvsig-sqlite/tags/</tagBase>

                    <goals>deploy</goals>

                </configuration>

            </plugin>                     

        </plugins>

    </build>

    <dependencyManagement>

        <dependencies>

            <dependency>

                <groupId>org.gvsig</groupId>

                <artifactId>org.gvsig.spatialite.provider</artifactId>

                <version>1.0.5</version>

            </dependency>

            <dependency>

                <groupId>org.gvsig</groupId>

                <artifactId>org.gvsig.spatialite.app.mainplugin</artifactId>

                <version>1.0.5</version>

            </dependency>

            <dependency>

                <groupId>org.hdm</groupId>

                <artifactId>spatialite-jdbc</artifactId>

                <version>4.3.0a</version>

            </dependency>

            <dependency>

                <groupId>org.gvsig</groupId>

                <artifactId>org.gvsig.fmap.dal.file.lib</artifactId>

                <version>2.0.164</version>

            </dependency>

        </dependencies>

    </dependencyManagement>

    <modules>

        <module>org.gvsig.spatialite.app</module>

        <module>org.gvsig.spatialite.provider</module>

    </modules>

</project>

/* 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.spatialite.dal;

import java.io.File;

import org.gvsig.spatialite.dal.operations.SpatiaLiteOperationsFactory;

import java.sql.Connection;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Map;

import org.apache.commons.dbcp.BasicDataSource;

import org.apache.commons.io.FilenameUtils;

import org.apache.commons.lang3.StringUtils;

import org.cresques.cts.IProjection;

import org.gvsig.fmap.dal.SQLBuilder;

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

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.JDBCUtils;

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

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

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

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.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.spatialite.SQLiteConfig;

import org.spatialite.SQLiteConfig.TransactionMode;

public class SpatiaLiteHelper extends JDBCHelperBase {

    static final Logger logger = LoggerFactory.getLogger(SpatiaLiteHelper.class);

    public static final String NAME = "SpatiaLite";

    public static final String SpatiaLiteJDBCDriver = "org.spatialite.JDBC";

    public static String getConnectionURL(SpatiaLiteConnectionParameters params) {

        String fname = params.getFile().getAbsolutePath().replace("\\","/");

        if( StringUtils.isEmpty(FilenameUtils.getExtension(fname)) ) {

            fname = fname + ".sqlite";

            params.setFile(new File(fname));

        }

        String connectionURL = "jdbc:spatialite:" + fname;

        logger.debug("connectionURL: {}", connectionURL);

        return connectionURL;

    }

    private static class ConnectionProvider {

        private static boolean needRegisterDriver = true;

        private BasicDataSource dataSource = null;

        private final SpatiaLiteConnectionParameters connectionParameters;

        public ConnectionProvider(SpatiaLiteConnectionParameters connectionParameters) {

            this.connectionParameters = connectionParameters;

        }

        public Connection getConnection() throws SQLException {

            if (this.dataSource == null) {

                this.dataSource = this.createDataSource();               

            }

            Connection conn = this.dataSource.getConnection();

            try {

                Statement st = conn.createStatement();

                st.execute("SELECT srid FROM spatial_ref_sys WHERE srid=0 LIMIT 1");

                JDBCUtils.closeQuietly(st);

            } catch(Exception ex) {

                Statement st = conn.createStatement();

                JDBCUtils.executeQuery(st, "SELECT InitSpatialMetaData(1)");

                JDBCUtils.closeQuietly(st);

            }

            return conn;

        }

        private BasicDataSource createDataSource() throws SQLException {

            if (!this.isRegistered()) {

                this.registerDriver();

            }

            SpatiaLiteConnectionParameters params = connectionParameters;

            BasicDataSource ds = new BasicDataSource();

            ds.setDriverClassName(params.getJDBCDriverClassName());

            if( !StringUtils.isEmpty(params.getUser()) ) {

                ds.setUsername(params.getUser());

            }

            if( !StringUtils.isEmpty(params.getPassword()) ) {

                ds.setPassword(params.getPassword());

            }

            ds.setUrl(params.getUrl());

            SQLiteConfig config = new SQLiteConfig();

            config.setSharedCache(true);

            config.enableLoadExtension(true);

            config.enableSpatiaLite(true);

            config.setTransactionMode(TransactionMode.IMMEDIATE);

            for (Map.Entry e : config.toProperties().entrySet()) {

                ds.addConnectionProperty((String)e.getKey(), (String)e.getValue());

            }

            ds.setMaxWait(60L * 1000);

            return ds;

        }

        private boolean isRegistered() {

            return needRegisterDriver;

        }

        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(NAME, className);

                }

            } catch (Exception e) {

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

            }

            needRegisterDriver = false;

        }

    }

    private ConnectionProvider connectionProvider = null;

    public SpatiaLiteHelper(JDBCConnectionParameters connectionParameters) {

        super(connectionParameters);

    }

    @Override

    public synchronized Connection  getConnection() throws AccessResourceException {

        try {

            if (this.connectionProvider == null) {

                this.connectionProvider = new ConnectionProvider(this.getConnectionParameters());

            }

            Connection connection = this.connectionProvider.getConnection();

            logger.debug("getConnection: connection = "+connection.hashCode());

            return connection;

        } catch (SQLException ex) {

            throw new AccessResourceException(SpatiaLiteLibrary.NAME, ex);

        }

    }

    @Override

    public SpatiaLiteConnectionParameters getConnectionParameters() {

        return (SpatiaLiteConnectionParameters) super.getConnectionParameters();

    }

    @Override

    public String getConnectionURL() {

        return getConnectionURL(this.getConnectionParameters());

    }

    @Override

    protected String getResourceType() {

        return SpatiaLiteLibrary.NAME;

    }

    @Override

    public String getProviderName() {

        return SpatiaLiteLibrary.NAME;

    }

    @Override

    public JDBCSQLBuilderBase createSQLBuilder() {

        return new SpatiaLiteSQLBuilder(this);

    }

    @Override

    public OperationsFactory getOperations() {

        if (this.operationsFactory == null) {

            this.operationsFactory = new SpatiaLiteOperationsFactory(this);

        }

        return operationsFactory;

    }

    @Override

    public SQLBuilder.GeometrySupportType getGeometrySupportType() {

        return SQLBuilder.GeometrySupportType.WKB;

    }

    @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 int getSRSCode(IProjection crs) {

        // TODO: ir a buscarlo a la BBDD a ver donde puede estar

        return super.getSRSCode(crs);

    }

    @Override

    public IProjection getProjectionFromSRSId(int srsid) {

        // TODO: ir a buscarlo a la BBDD a ver donde puede estar

        return super.getProjectionFromSRSId(srsid);

    }

    @Override

    public String getSourceId(JDBCStoreParameters parameters) {

        return parameters.getDBName() + "." + 

               parameters.getSchema()+ "." + 

               parameters.getTable();

    }

    @Override

    public JDBCNewStoreParameters createNewStoreParameters() {

        return new SpatiaLiteNewStoreParameters();

    }

    @Override

    public JDBCStoreParameters createOpenStoreParameters() {

        return new SpatiaLiteStoreParameters();

    }

    @Override

    public JDBCServerExplorerParameters createServerExplorerParameters() {

        return new SpatiaLiteExplorerParameters();

    }

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

        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;

    }

}

package org.gvsig.spatialite.dal;

import org.gvsig.fmap.dal.DataParameters;

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

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

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.impl.JDBCStoreProviderFactory;

public class SpatiaLiteStoreProviderFactory extends JDBCStoreProviderFactory {

    private static final String NAME = SpatiaLiteLibrary.NAME;

    public SpatiaLiteStoreProviderFactory() {

        super(

                NAME, 

                "SpatiaLite store"

        );

    }

    @Override

    public JDBCStoreProvider createProvider(

            DataParameters parameters,

            DataStoreProviderServices providerServices

    ) throws InitializeException {

        JDBCHelper helper = new SpatiaLiteHelper((JDBCConnectionParameters) parameters);

        JDBCStoreProvider provider = helper.createProvider(

                (JDBCStoreParameters) parameters, 

                providerServices

        );

        return provider;

    }

    @Override

    public JDBCStoreParameters createParameters() {

        JDBCStoreParameters params = new SpatiaLiteStoreParameters();

        return params;

    }

}

/* gvSIG. Geographic Information System of the Valencian Government

*

* Copyright (C) 2007-2008 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 2

* 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.spatialite.dal;

import java.io.File;

import java.util.Properties;

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

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

public class SpatiaLiteStoreParameters extends JDBCStoreParameters implements SpatiaLiteConnectionParameters {

    private final SpatiaLiteConnectionParametersHelper helper;

    public SpatiaLiteStoreParameters() {

        super(

                SpatiaLiteLibrary.NAME + "StoreParameters",

                SpatiaLiteLibrary.NAME

        );

        this.helper = new SpatiaLiteConnectionParametersHelper(this);

    }

    @Override

    public String getUrl() {

        return this.helper.getUrl();

    }

    @Override

    public void validate() throws ValidateDataParametersException {

        this.helper.validate();

        super.validate();

    }

    @Override

    public boolean getEnableSpatiaLite() {

        return this.helper.getEnableSpatiaLite();

    }

    @Override

    public boolean getEnableLoadExtension() {

        return this.helper.getEnableLoadExtension();

    }

    @Override

    public boolean getEnableSharedCache() {

        return this.helper.getEnableSharedCache();

    }

    @Override

    public void setEnableSpatiaLite(boolean v) {

        this.helper.setEnableSpatiaLite(v);

    }

    @Override

    public void setEnableLoadExtension(boolean v) {

        this.helper.setEnableLoadExtension(v);

    }

    @Override

    public void setEnableSharedCache(boolean v) {

        this.helper.setEnableSharedCache(v);

    }

    @Override

    public Properties getProperties() {

        return this.helper.getProperties();

    }

    @Override

    public File getFile() {

        return this.helper.getFile();

    }

    @Override

    public void setFile(File database) {

        this.helper.setFile(database);

    }

}

package org.gvsig.spatialite.dal;

import java.util.Properties;

import org.gvsig.fmap.dal.serverexplorer.filesystem.FilesystemStoreParameters;

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

public interface SpatiaLiteConnectionParameters extends JDBCConnectionParameters, FilesystemStoreParameters {

    public boolean getEnableSpatiaLite();

    public boolean getEnableLoadExtension();

    public boolean getEnableSharedCache();

    public void setEnableSpatiaLite(boolean v);

    public void setEnableLoadExtension(boolean v);

    public void setEnableSharedCache(boolean v);

    public Properties getProperties();

}

package org.gvsig.spatialite.dal;

import java.io.File;

import java.util.Properties;

import org.apache.commons.io.FilenameUtils;

import org.apache.commons.lang3.BooleanUtils;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.fmap.dal.DataParameters;

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

import org.gvsig.fmap.dal.resource.db.DBParameters;

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

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

public class SpatiaLiteConnectionParametersHelper {

    private final JDBCConnectionParameters parameters;

    private static final String ENABLE_SHARED_CACHE = "enable_shared_cache";

    private static final String ENABLE_LOAD_EXTENSION = "enable_load_extension";

    private static final String ENABLE_SPATIALITE = "enable_spatialite";

    private static final String DATABASE_FILE = "database_file";

    public SpatiaLiteConnectionParametersHelper(JDBCConnectionParameters parameters) {

        this.parameters = parameters;

    }

    public String getUrl() {

        String url = (String) this.getDynValue(JDBCConnectionParameters.URL_PARAMTER_NAME);

        if( StringUtils.isEmpty(url) ) {

            url = SpatiaLiteHelper.getConnectionURL((SpatiaLiteConnectionParameters) this.parameters);

            this.setDynValue(JDBCConnectionParameters.URL_PARAMTER_NAME,url);

        }

        return url;

    }

    public void validate() throws ValidateDataParametersException {

        if (this.getDynValue(JDBCConnectionParameters.JDBC_DRIVER_CLASS_PARAMTER_NAME) == null) {

            this.setDynValue(

                JDBCConnectionParameters.JDBC_DRIVER_CLASS_PARAMTER_NAME,

                SpatiaLiteHelper.SpatiaLiteJDBCDriver

            );

        }

        if (getFile() == null) {

            throw new ValidateDataParametersException();

        }

    }

    private Object getDynValue(String name) {

        return ((DataParameters)this.parameters).getDynValue(name);

    }

    private void setDynValue(String name, Object value) {

        ((DataParameters)this.parameters).setDynValue(name,value);

    }

    public boolean getEnableSpatiaLite() {

        return (boolean) this.getDynValue(ENABLE_SPATIALITE);

    }

    public boolean getEnableLoadExtension() {

        return (boolean) this.getDynValue(ENABLE_LOAD_EXTENSION);

    }

    public boolean getEnableSharedCache() {

        return (boolean) this.getDynValue(ENABLE_SHARED_CACHE);

    }

    public void setEnableSpatiaLite(boolean v) {

        this.setDynValue(ENABLE_SPATIALITE, v);

    }

    public void setEnableLoadExtension(boolean v) {

        this.setDynValue(ENABLE_LOAD_EXTENSION, v);

    }

    public void setEnableSharedCache(boolean v) {

        this.setDynValue(ENABLE_SHARED_CACHE, v);

    }

    public File getFile() {

        File f = (File) this.getDynValue(DATABASE_FILE);

        if( this.getDynValue(DBParameters.DBNAME_PARAMTER_NAME)==null &&

            f != null ) {

            String dbname = FilenameUtils.getBaseName(f.getName());

            this.setDynValue(DBParameters.DBNAME_PARAMTER_NAME, dbname);

        }

        return f;

    }

    public void setFile(File database) {

        this.setDynValue(DATABASE_FILE, database);

        if( this.getDynValue(DBParameters.DBNAME_PARAMTER_NAME)==null &&

            database != null ) {

            String dbname = FilenameUtils.getBaseName(database.getName());

            this.setDynValue(DBParameters.DBNAME_PARAMTER_NAME, dbname);

        }

    }

    public Properties getProperties() {

        Properties props = new Properties();

        props.setProperty("enable_shared_cache", BooleanUtils.toStringTrueFalse(getEnableSharedCache()));

        props.setProperty("enable_load_extension", BooleanUtils.toStringTrueFalse(getEnableLoadExtension()));

        props.setProperty("enable_spatialite", BooleanUtils.toStringTrueFalse(getEnableSpatiaLite()));

        return props;

    }    

}

package org.gvsig.spatialite.dal.operations;

import java.util.List;

import org.cresques.cts.IProjection;

import org.gvsig.fmap.dal.feature.EditableFeatureType;

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

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.FetchFeatureTypeOperation;

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.OperationsFactoryBase;

public class SpatiaLiteOperationsFactory extends OperationsFactoryBase {

    public SpatiaLiteOperationsFactory(JDBCHelper helper) {

        super(helper);

    }

    @Override

    public FetchFeatureTypeOperation createFetchFeatureType(EditableFeatureType type, String database, String schema, String table, List<String> primaryKeys, String defaultGeometryField, IProjection crs) {

        return new SpatiaLiteFetchFeatureTypeOperation(

                helper, type, database, schema, table, primaryKeys, 

                defaultGeometryField, crs

        );

    }  

}

package org.gvsig.spatialite.dal.operations;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.logging.Level;

import java.util.logging.Logger;

import org.apache.commons.lang3.StringUtils;

import org.cresques.cts.IProjection;

import org.gvsig.fmap.dal.DataTypes;

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

import org.gvsig.fmap.dal.feature.EditableFeatureAttributeDescriptor;

import org.gvsig.fmap.dal.feature.EditableFeatureType;

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.FetchFeatureTypeOperation;

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.GeometryLocator;

import org.gvsig.fmap.geom.GeometryManager;

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

public class SpatiaLiteFetchFeatureTypeOperation extends FetchFeatureTypeOperation {

    private static Map<String,GeometryType>spatialiteGeometryTypes = null;

    private Map<String,String> columns_def;

    public SpatiaLiteFetchFeatureTypeOperation(

            JDBCHelper helper

        ) {

        super(helper);

    }

    private GeometryType getGT(

            GeometryManager manager, 

            int type, 

            int subtype

        ) {

        try {

            return manager.getGeometryType(type, subtype);

        } catch (Exception ex) {

            return null;

        }

    }

    public SpatiaLiteFetchFeatureTypeOperation(

            JDBCHelper helper,

            EditableFeatureType featureType,

            String dbname,

            String schema,

            String table,

            List<String> primaryKeys,

            String defaultGeometryColumn,

            IProjection crs

        ) {

        super(helper, featureType, dbname, schema, table, primaryKeys, defaultGeometryColumn, crs);

    }            

    @Override

    public void fetch(EditableFeatureType featureType, Connection conn, String dbname, String schema, String table, List<String> pks, String defaultGeometryColumn, IProjection crs) throws DataException {

        columns_def = new HashMap<>();

        try {

            ResultSet dbmetadata = this.getConnection().getMetaData().getColumns(null, null, table, null);

            while( dbmetadata.next() ) {

                columns_def.put(dbmetadata.getString("COLUMN_NAME"), StringUtils.defaultString(dbmetadata.getString("COLUMN_DEF")));

            }

        } catch (SQLException ex) {

            logger.warn("Can't read metadata from table '"+table+"'.",ex);

        }

        super.fetch(featureType, conn, dbname, schema, table, pks, defaultGeometryColumn, crs);

    }

    @Override

    protected boolean isInPrimaryKeys(List<String> pks, EditableFeatureAttributeDescriptor attr) {

        // En SpatiaLite, los nombres obtenidos de las pks de los metadados no 

        // coinciden conlos nombres de los campos ya que unos estan en mayusculas 

        // y otros en minusculas, asi que comparamos con IgnoreCase.

        for (String pk : pks) {

            if( StringUtils.equalsIgnoreCase(pk, attr.getName()) ) {

                return true;

            }

        }

        return false;

    }

    @Override

    protected int getDataTypeFromMetadata(

            ResultSetMetaData rsMetadata,

            int colIndex

        ) throws SQLException {

        // En SpatiaLite no existe el tipo de dato Geometria, las geometrias

        // se guardan en un BLOB, y en getColumnTypeName se indica el tipo

        // de la geometria.

        String columnTypeName = rsMetadata.getColumnTypeName(colIndex);

        GeometryType gt = getGeometryTypeFromSpatiaLiteTypeName(columnTypeName);

        if( gt!=null ) {

            return DataTypes.GEOMETRY;

        }

        int columnType = rsMetadata.getColumnType(colIndex);

        String columnName = rsMetadata.getColumnName(colIndex);

        String column_def = this.columns_def.getOrDefault(columnName,"").trim();

        if( columnType == java.sql.Types.INTEGER && (

            StringUtils.startsWithIgnoreCase(column_def, "date(") || 

            StringUtils.startsWithIgnoreCase(column_def, "datetime(") || 

            StringUtils.startsWithIgnoreCase(column_def, "strftime(") )

            ) {

            return DataTypes.DATE;

        }

        return super.getDataTypeFromMetadata(rsMetadata, colIndex);

    }

    @Override

    protected void fetchGeometryTypeAndSRS(

            EditableFeatureAttributeDescriptor attr,

            ResultSetMetaData rsMetadata,

            int colIndex

        ) {

        if( attr.getType()!=DataTypes.GEOMETRY ) {

            return;

        }

        try {

            String typeName = rsMetadata.getColumnTypeName(colIndex);

            GeometryType gt = getGeometryTypeFromSpatiaLiteTypeName(typeName);

            if( gt != null ) {

                attr.setGeometryType(gt);

                JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();

                sqlbuilder.select().column().name("f_table_name");

                sqlbuilder.select().column().name("f_geometry_column");

                sqlbuilder.select().column().name("geometry_type");

                sqlbuilder.select().column().name("coord_dimension");

                sqlbuilder.select().column().name("srid");

                sqlbuilder.select().column().name("spatial_index_enabled");

                sqlbuilder.select().where().set(

                        sqlbuilder.eq(

                                sqlbuilder.column("f_table_name"),

                                sqlbuilder.constant(this.getTablename())

                        )

                );                

                sqlbuilder.select().where().and(

                        sqlbuilder.eq(

                                sqlbuilder.column("f_geometry_column"),

                                sqlbuilder.constant(attr.getName())

                        )

                );         

                sqlbuilder.select().from().table().name("geometry_columns");

                Statement st = null;

                ResultSet rs = null;

                int srsid = -1;

                try {

                    st = this.getConnection().createStatement();

                    rs = JDBCUtils.executeQuery(st, sqlbuilder.toString());

                    if (rs.next()) {

                        srsid = rs.getInt("srid");

                    }

                } finally {

                    JDBCUtils.closeQuietly(rs);

                    JDBCUtils.closeQuietly(st);

                }

                if( srsid > 0 ) {

                    attr.setSRS(this.helper.getProjectionFromSRSId(srsid));

                }

            }

        } catch (Exception ex) {

            logger.debug("Can't get geometry type and srs from column '"+attr.getName()+"'.",ex);

        }

    }

    private GeometryType getGeometryTypeFromSpatiaLiteTypeName(String typeName) {

        if( spatialiteGeometryTypes==null ) {

            GeometryManager manager = GeometryLocator.getGeometryManager();

            spatialiteGeometryTypes = new HashMap<>();

            spatialiteGeometryTypes.put("POINT", getGT(manager, Geometry.TYPES.POINT,Geometry.SUBTYPES.GEOM2D));

            spatialiteGeometryTypes.put("POINTZ", getGT(manager, Geometry.TYPES.POINT,Geometry.SUBTYPES.GEOM3D));

            spatialiteGeometryTypes.put("POINTM", getGT(manager, Geometry.TYPES.POINT,Geometry.SUBTYPES.GEOM2DM));

            spatialiteGeometryTypes.put("POINTZM", getGT(manager, Geometry.TYPES.POINT,Geometry.SUBTYPES.GEOM3DM));

            spatialiteGeometryTypes.put("LINESTRING", getGT(manager, Geometry.TYPES.LINE,Geometry.SUBTYPES.GEOM2D));

            spatialiteGeometryTypes.put("LINESTRINGZ", getGT(manager, Geometry.TYPES.LINE,Geometry.SUBTYPES.GEOM3D));

            spatialiteGeometryTypes.put("LINESTRINGM", getGT(manager, Geometry.TYPES.LINE,Geometry.SUBTYPES.GEOM2DM));

            spatialiteGeometryTypes.put("LINESTRINGZM", getGT(manager, Geometry.TYPES.LINE,Geometry.SUBTYPES.GEOM3DM));

            spatialiteGeometryTypes.put("POLYGON", getGT(manager, Geometry.TYPES.POLYGON,Geometry.SUBTYPES.GEOM2D));

            spatialiteGeometryTypes.put("POLYGONZ", getGT(manager, Geometry.TYPES.POLYGON,Geometry.SUBTYPES.GEOM3D));

            spatialiteGeometryTypes.put("POLYGONM", getGT(manager, Geometry.TYPES.POLYGON,Geometry.SUBTYPES.GEOM2DM));

            spatialiteGeometryTypes.put("POLYGONZM", getGT(manager, Geometry.TYPES.POLYGON,Geometry.SUBTYPES.GEOM3DM));

            spatialiteGeometryTypes.put("MULTIPOINT", getGT(manager, Geometry.TYPES.MULTIPOINT,Geometry.SUBTYPES.GEOM2D));

            spatialiteGeometryTypes.put("MULTIPOINTZ", getGT(manager, Geometry.TYPES.MULTIPOINT,Geometry.SUBTYPES.GEOM3D));

            spatialiteGeometryTypes.put("MULTIPOINTM", getGT(manager, Geometry.TYPES.MULTIPOINT,Geometry.SUBTYPES.GEOM2DM));

            spatialiteGeometryTypes.put("MULTIPOINTZM", getGT(manager, Geometry.TYPES.MULTIPOINT,Geometry.SUBTYPES.GEOM3DM));

            spatialiteGeometryTypes.put("MULTILINESTRING", getGT(manager, Geometry.TYPES.MULTILINE,Geometry.SUBTYPES.GEOM2D));

            spatialiteGeometryTypes.put("MULTILINESTRINGZ", getGT(manager, Geometry.TYPES.MULTILINE,Geometry.SUBTYPES.GEOM3D));

            spatialiteGeometryTypes.put("MULTILINESTRINGM", getGT(manager, Geometry.TYPES.MULTILINE,Geometry.SUBTYPES.GEOM2DM));

            spatialiteGeometryTypes.put("MULTILINESTRINGZM", getGT(manager, Geometry.TYPES.MULTILINE,Geometry.SUBTYPES.GEOM3DM));

            spatialiteGeometryTypes.put("MULTIPOLYGON", getGT(manager, Geometry.TYPES.MULTIPOLYGON,Geometry.SUBTYPES.GEOM2D));

            spatialiteGeometryTypes.put("MULTIPOLYGONZ", getGT(manager, Geometry.TYPES.MULTIPOLYGON,Geometry.SUBTYPES.GEOM3D));

            spatialiteGeometryTypes.put("MULTIPOLYGONM", getGT(manager, Geometry.TYPES.MULTIPOLYGON,Geometry.SUBTYPES.GEOM2DM));

            spatialiteGeometryTypes.put("MULTIPOLYGONZM", getGT(manager, Geometry.TYPES.MULTIPOLYGON,Geometry.SUBTYPES.GEOM3DM));

            spatialiteGeometryTypes.put("GEOMETRY", getGT(manager, Geometry.TYPES.GEOMETRY,Geometry.SUBTYPES.GEOM2D));

            spatialiteGeometryTypes.put("GEOMETRYZ", getGT(manager, Geometry.TYPES.GEOMETRY,Geometry.SUBTYPES.GEOM3D));

            spatialiteGeometryTypes.put("GEOMETRYM", getGT(manager, Geometry.TYPES.GEOMETRY,Geometry.SUBTYPES.GEOM2DM));

            spatialiteGeometryTypes.put("GEOMETRYZM", getGT(manager, Geometry.TYPES.GEOMETRY,Geometry.SUBTYPES.GEOM3DM));

        }

        return spatialiteGeometryTypes.get(typeName);

    }

}

/* gvSIG. Geographic Information System of the Valencian Government

*

* Copyright (C) 2007-2008 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 2

* 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.spatialite.dal;

import java.io.File;

import java.util.Properties;

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

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

public class SpatiaLiteExplorerParameters extends

        JDBCServerExplorerParameters 

    implements

        SpatiaLiteConnectionParameters

    {

    private final SpatiaLiteConnectionParametersHelper helper;

    public SpatiaLiteExplorerParameters() {

        super(

                SpatiaLiteLibrary.NAME + "ServerExplorerParameters",

                SpatiaLiteLibrary.NAME

        );

        this.helper = new SpatiaLiteConnectionParametersHelper(this);

    }

    @Override

    public String getUrl() {

        return this.helper.getUrl();

    }

    @Override

    public void validate() throws ValidateDataParametersException {

        this.helper.validate();

        super.validate();

    }

    @Override

    public boolean getEnableSpatiaLite() {

        return this.helper.getEnableSpatiaLite();

    }

    @Override

    public boolean getEnableLoadExtension() {

        return this.helper.getEnableLoadExtension();

    }

    @Override

    public boolean getEnableSharedCache() {

        return this.helper.getEnableSharedCache();

    }

    @Override

    public void setEnableSpatiaLite(boolean v) {

        this.helper.setEnableSpatiaLite(v);

    }

    @Override

    public void setEnableLoadExtension(boolean v) {

        this.helper.setEnableLoadExtension(v);

    }

    @Override

    public void setEnableSharedCache(boolean v) {

        this.helper.setEnableSharedCache(v);

    }

    @Override

    public Properties getProperties() {

        return this.helper.getProperties();

    }

    @Override

    public File getFile() {

        return this.helper.getFile();

    }

    @Override

    public void setFile(File database) {

        this.helper.setFile(database);

    }

}

/* gvSIG. Geographic Information System of the Valencian Government

 *

 * Copyright (C) 2007-2008 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 2

 * 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.spatialite.dal;

import org.gvsig.fmap.dal.DALLibrary;

import org.gvsig.fmap.dal.DALLocator;

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

import org.gvsig.fmap.dal.store.db.DBHelper;

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

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

import org.gvsig.metadata.exceptions.MetadataException;

import org.gvsig.tools.library.AbstractLibrary;

import org.gvsig.tools.library.LibraryException;

public class SpatiaLiteLibrary extends AbstractLibrary {

    public static final String NAME = "SpatiaLite";

    @Override

    public void doRegistration() {

        registerAsServiceOf(DALLibrary.class);

        require(JDBCLibrary.class);

    }

    @Override

    protected void doInitialize() throws LibraryException {

    }

    @Override

    protected void doPostInitialize() throws LibraryException {

        LibraryException ex = null;

        DataManagerProviderServices dataman = 

                (DataManagerProviderServices) DALLocator.getDataManager();

        try {

            Class.forName(SpatiaLiteHelper.SpatiaLiteJDBCDriver);

        } catch(Throwable th) {

            SpatiaLiteHelper.logger.warn("Can't load SpatiaLite JDBC Driver.",th);

        }

        DBHelper.registerParametersDefinition(

                NAME + "StoreParameters",

                SpatiaLiteStoreParameters.class,

                dataman.getResourceAsStream(this, NAME + "Parameters.xml")

        );

        DBHelper.registerParametersDefinition(

                NAME + "NewStoreParameters",

                SpatiaLiteNewStoreParameters.class,

                dataman.getResourceAsStream(this, NAME + "Parameters.xml")

        );

        DBHelper.registerParametersDefinition(

                NAME + "ServerExplorerParameters",

                SpatiaLiteExplorerParameters.class,

                dataman.getResourceAsStream(this, NAME + "Parameters.xml")

        );

//        DBHelper.registerParametersDefinition(

//                NAME + "ResourceParameters",

//                SpatiaLiteResourceParameters.class,

//                dataman.getResourceAsStream(this, NAME + "Parameters.xml")

//        );

        try {

            DBHelper.registerMetadataDefinition(

                NAME,

                JDBCStoreProviderBase.class,

                dataman.getResourceAsStream(this, NAME + "Metadata.xml")

            );

        } catch (MetadataException e) {

            ex = new LibraryException(this.getClass(), e);

        }

//        ResourceManagerProviderServices resman = (ResourceManagerProviderServices) DALLocator

//                .getResourceManager();

//

//        if (!resman.getResourceProviders().contains(NAME)) {

//            resman.register(NAME,

//                "Resource for " + NAME,

//                SpatiaLiteResource.class,

//                SpatiaLiteResourceParameters.class

//            );

//        }

        if (!dataman.getStoreProviderRegister().exits(NAME)) {

            dataman.registerStoreProviderFactory(new SpatiaLiteStoreProviderFactory());

        }

        if (!dataman.getServerExplorerRegister().exits(NAME)) {

            dataman.registerServerExplorerFactory(new SpatiaLiteExplorerFactory());

        }

        if (ex != null) {

            throw ex;

        }

    }

}

package org.gvsig.spatialite.dal;

import java.sql.PreparedStatement;

import java.text.MessageFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import org.gvsig.fmap.dal.DataTypes;

import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor;

import org.gvsig.fmap.dal.feature.FeatureType;

import org.gvsig.fmap.dal.feature.spi.FeatureProvider;

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

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

import org.gvsig.fmap.geom.Geometry;

public class SpatiaLiteSQLBuilder extends JDBCSQLBuilderBase {

    private final SpatiaLiteHelper helper;

    public SpatiaLiteSQLBuilder(SpatiaLiteHelper helper) {

        super();

        this.helper = helper;

        //

        // SpatiaLite 4.3.0, SQL functions reference list

        //

        // http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.3.0.html

        //

        // https://sqlite.org/lang.html

        //

        config.set(SQLConfig.default_schema, "");

        config.set(SQLConfig.support_schemas, false);

        config.set(SQLConfig.allowAutomaticValues, true);

        config.set(SQLConfig.geometry_type_support, this.helper.getGeometrySupportType());

        config.set(SQLConfig.has_spatial_functions, this.helper.hasSpatialFunctions());

        config.remove_functionality(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table);

        config.remove_functionality(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_table);

        config.set(SQLConfig.UPDATE_TABLE_STATISTICS_table,"SELECT UpdateLayerStatistics({0})");

        config.set(SQLConfig.ST_GeomFromEWKB, "GeomFromWKB({0}, {1})");

        config.set(SQLConfig.ST_AsEWKB, "AsEWKB(({0}))");        

        config.set(SQLConfig.ST_ExtentAggregate, "Extent({0})");        

        config.set(SQLConfig.ST_UnionAggregate, "ST_Union({0})");

        config.set(SQLConfig.lcase, "lower({0})");

        config.set(SQLConfig.ucase, "upper({0})");

        config.set(SQLConfig.operator_ILIKE, "lower({0}) LIKE lower({1})");

        config.set(SQLConfig.isNull, "( ({0}) ISNULL )");

        config.set(SQLConfig.notIsNull, "( ({0}) NOT NULL )");

        config.set(SQLConfig.type_boolean, "INTEGER");

        config.set(SQLConfig.type_byte, "INTEGER");

        config.set(SQLConfig.type_bytearray, "");

        config.set(SQLConfig.type_geometry, "BLOB");

        config.set(SQLConfig.type_char, "TEXT");

        config.set(SQLConfig.type_date, "INTEGER");

        config.set(SQLConfig.type_double, "REAL"); 

        config.set(SQLConfig.type_numeric_p, "REAL");

        config.set(SQLConfig.type_numeric_ps, "REAL");

        config.set(SQLConfig.type_bigdecimal, "REAL");

        config.set(SQLConfig.type_float, "REAL");

        config.set(SQLConfig.type_int, "INTEGER");

        config.set(SQLConfig.type_long, "INTEGER");

        config.set(SQLConfig.type_string, "TEXT");

        config.set(SQLConfig.type_string_p, "TEXT");

        config.set(SQLConfig.type_time, "INTEGER");

        config.set(SQLConfig.type_timestamp, "INTEGER");

        config.set(SQLConfig.type_version, "TEXT");

        config.set(SQLConfig.type_URI, "TEXT");

        config.set(SQLConfig.type_URL, "TEXT");

        config.set(SQLConfig.type_FILE, "TEXT");

        config.set(SQLConfig.type_FOLDER, "TEXT");        

    }

    public class SpatiaLiteTableNameBuilderBase extends TableNameBuilderBase {

        @Override

        public boolean has_schema() {

            return false;

        }

        @Override

        public boolean has_database() {

            return false;

        }

    }

    protected class SpatiaLiteCreateTableBuilder extends CreateTableBuilderBase {

        @Override

        public List<String> toStrings() {

            //

            // Respecto al base cambia la declaracion de campo automaticos

            // SpatiaLite usa AUTOINCREMENT en lugar de SERIAL y que hay que usar

            // la funcion AddGeometryColumn para a?adir las columnas de tipo

            // geometria.

            // El orden en el que hay que declarar las constrains tambien cambia

            // respecto al que hay por defecto.

            //

            List<String> sqls = new ArrayList<>();

            StringBuilder builder = new StringBuilder();

            builder.append("CREATE TABLE ");

            builder.append(this.table.toString());

            builder.append(" (");

            boolean first = true;

            for (ColumnDescriptorBuilder column : columns) {

                if( column.isGeometry() ) {

                    continue;

                }

                if (first) {

                    first = false;

                } else {

                    builder.append(", ");

                }

                builder.append(identifier(column.getName()));

                builder.append(" ");

                builder.append(sqltype(column.getType(), column.getPrecision(), column.getSize()));

                if (column.isPrimaryKey()) {

                    builder.append(" PRIMARY KEY");

                }

                if( column.isAutomatic() ) {

                    builder.append(" AUTOINCREMENT");

                }

                if (column.getDefaultValue() == null) {

                    if (column.allowNulls()) {

                        builder.append(" DEFAULT NULL");

                    }

                } else {

                    if( column.getType() == DataTypes.DATE ) {

                        builder.append(" DEFAULT ( date('");

                        builder.append(column.getDefaultValue().toString());

                        builder.append("') )");

                    } else {

                        builder.append(" DEFAULT '");

                        builder.append(column.getDefaultValue().toString());

                        builder.append("'");

                    }

                }

                if (!column.allowNulls()) {

                    builder.append(" NOT NULL");

                }

            }

            builder.append(" )");

            sqls.add(builder.toString());

            String AddGeometryColumn = "SELECT AddGeometryColumn({0} , {1} , {2,number,#######} , {3}, {4}, {5})";

            for (ColumnDescriptorBuilderBase column : columns) {

                if( column.isGeometry() ) {

                    String sql = MessageFormat.format(

                        AddGeometryColumn,

                        this.table.toString(),

                        identifier(column.getName()),

                        column.getGeometrySRSId(),

                        constant(sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype())),

                        constant(sqlgeometrydimension(column.getGeometryType(), column.getGeometrySubtype())),

                        column.allowNulls()? 1:0

                    );

                    sqls.add(sql);

                }

            }

            return sqls;

        }

    }

    public class SpatiaLiteSelectBuilderBase extends SelectBuilderBase {

        protected boolean isValid(StringBuilder message) {

            if( message == null ) {

                message = new StringBuilder();

            }

            if( this.has_offset() && !this.has_order_by() ) {

                // Algunos gestores de BBDD requieren que se especifique un

                // orden para poder usar OFFSET. Como eso parece buena idea para

                // asegurar que siempre tengamos los mismo resultados, lo exijimos

                // siempre.

                message.append("Can't use OFFSET without an ORDER BY.");

                return false;

            }

            return true;

        }        

        @Override

        public String toString() {

            // SpatiaLite requiere que si se especifica OFFSET deba especificarse

            // LIMIT obligatoriamente. Se le puede poner un LIMIT -1 cuando 

            // queramos un OFFSET sin especificar un LIMIT.

            StringBuilder builder = new StringBuilder();

            if( !isValid(builder) ) {

                throw new IllegalStateException(builder.toString());

            }

            builder.append("SELECT ");

            if( this.distinct ) {

                builder.append("DISTINCT ");

            }

            boolean first = true;

            for (SelectColumnBuilder column : columns) {

                if (first) {

                    first = false;

                } else {

                    builder.append(", ");

                }

                builder.append(column.toString());

            }

            if ( this.has_from() ) {

                builder.append(" FROM ");

                builder.append(this.from.toString());

            }

            if ( this.has_where() ) {

                builder.append(" WHERE ");

                builder.append(this.where.toString());

            }

            if( this.has_order_by() ) {

                builder.append(" ORDER BY ");

                first = true;

                for (OrderByBuilder item : this.order_by) {

                    if (first) {

                        first = false;

                    } else {

                        builder.append(", ");

                    }

                    builder.append(item.toString());                    

                }   

            }

            if ( this.has_limit() && this.has_offset() ) {

                builder.append(" LIMIT ");

                builder.append(this.limit);

                builder.append(" OFFSET ");

                builder.append(this.offset);

            } else if ( this.has_limit()) {

                builder.append(" LIMIT ");

                builder.append(this.limit);

            } else if ( this.has_offset() ) {

                builder.append(" LIMIT -1 OFFSET ");

                builder.append(this.offset);    

            }

            return builder.toString();

        }

    }

    @Override

    public String bytearray(byte[] data) {

        // SpatiaLite usa un formato diferencte para especificar un array de 

        // bytes. En lugar de 0x... usa x'...' .

        StringBuilder builder = new StringBuilder();

        builder.append("x'");

        for (byte abyte : data) {

            int v = abyte & 0xff;

            builder.append(String.format("%02x", v));

        }

        builder.append("'");

        return builder.toString();

    }

    @Override

    public Object sqlgeometrydimension(int type, int subtype) {

        //'XY' or 2: 2D points, identified by X and Y coordinates

        //'XYM': 2D points, identified by X and Y coordinates. Each point stores an M-value (measure) as well

        //'XYZ' or 3: 3D points, identified by X, Y and Z coordinates

        //'XYZM' or 4: 3D points, identified by X, Y and Z coordinates. Each point stores an M-value (measure) as well

        switch(subtype) {

            case Geometry.SUBTYPES.GEOM2D:

            default:

                return "XY";

            case Geometry.SUBTYPES.GEOM2DM:

                return "XYM";

            case Geometry.SUBTYPES.GEOM3D:

                return "XYZ";

            case Geometry.SUBTYPES.GEOM3DM:

                return "XYZM";

        }

    }

    public SpatiaLiteHelper getHelper() {

        return this.helper;

    }

    @Override

    public void setParameters(PreparedStatement st, FeatureProvider feature) {

        try {

            FeatureType featureType = feature.getType();

            List<Object> values = new ArrayList<>();

            for (Parameter parameter : this.getParameters()) {

                if (parameter.is_constant()) {

                    values.add(parameter.getValue());

                } else {

                    String name = parameter.getName();

                    FeatureAttributeDescriptor descriptor = featureType.getAttributeDescriptor(name);

                    switch( descriptor.getType() ) {