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.19</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.215</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.19</connection>

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

        <url>https://devel.gvsig.org/redmine/projects/gvsig-sqlite/repository/show/tags/org.gvsig.spatialite-1.0.19</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>

                </configuration>

            </plugin>                     

        </plugins>

    </build>

    <dependencyManagement>

        <dependencies>

            <dependency>

                <groupId>org.gvsig</groupId>

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

                <version>1.0.19</version>

            </dependency>

            <dependency>

                <groupId>org.gvsig</groupId>

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

                <version>1.0.19</version>

                <type>zip</type>

            </dependency>

            <dependency>

                <groupId>org.xerial</groupId>

                <artifactId>sqlite-jdbc</artifactId>

                <version>3.21.0</version>

            </dependency>

        </dependencies>

    </dependencyManagement>

    <modules>

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

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

    </modules>

</project>

package org.gvsig.spatialite.dal;

import java.util.ArrayList;

import java.util.List;

import org.apache.commons.lang3.ArrayUtils;

import org.cresques.cts.IProjection;

import org.gvsig.fmap.crs.CRSFactory;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.ExpressionBuilder;

import org.gvsig.fmap.dal.ExpressionBuilder.Parameter;

import org.gvsig.fmap.dal.ExpressionBuilder.Variable;

import org.gvsig.fmap.dal.SQLBuilder;

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

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

import org.gvsig.fmap.geom.DataTypes;

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.GeometryLocator;

import org.gvsig.fmap.geom.GeometryManager;

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

import org.gvsig.tools.junit.AbstractLibraryAutoInitTestCase;

public class SpatiaLiteSQLBuilderTest extends AbstractLibraryAutoInitTestCase {

    private SpatiaLiteHelper helper;

    @Override

    protected void doSetUp() throws Exception {

        DataManagerProviderServices dataman = 

                (DataManagerProviderServices) DALLocator.getDataManager();

        DBHelper.registerParametersDefinition(

                SpatiaLiteLibrary.NAME + "StoreParameters",

                SpatiaLiteStoreParameters.class,

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

        );

        SpatiaLiteStoreParameters params = new SpatiaLiteStoreParameters();

        params.setDBName("test.sqlite");

        params.setTable("test1");

        helper = new SpatiaLiteHelper(params);

    }

    List<String> getVariableNames(ExpressionBuilder builder) {

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

        for (Variable var : builder.getVariables()) {

            vars.add(var.getName());

        }

        return vars;

    }

    List<String> getParameterNames(ExpressionBuilder builder) {

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

        for (Parameter param : builder.getParameters()) {

            String s;

            switch(param.getType()) {

                case Constant:

                    Object value = param.getValue();

                    if( value==null ) {

                        s = "null";

                    } else if( value instanceof String ) {

                        s = "'" + (String)value + "'";

                    } else {

                        s = value.toString();

                    }    

                    break;

                case Geometry:

                case Variable:

                default:

                    s = "\"" + param.getName() + "\"";

            }

            params.add(s);

        }

        return params;

    }

    public SQLBuilder createSQLBuilder() {

        return new SpatiaLiteSQLBuilder(helper);

    }

    public void testCalulateEnvelope() throws Exception {

        GeometryManager geometryManager = GeometryLocator.getGeometryManager();

        IProjection proj = CRSFactory.getCRS("EPSG:4326");

        Polygon limit = geometryManager.createPolygon(Geometry.SUBTYPES.GEOM2D);

        limit.addVertex(0, 0);

        limit.addVertex(0, 100);

        limit.addVertex(100, 100);

        limit.addVertex(100, 0);

        limit.addVertex(0, 0);

        SQLBuilder builder = createSQLBuilder();

        builder.select().column().value(

            builder.getAsGeometry(

              builder.ST_ExtentAggregate(

                builder.column("the_geom")

              )

            )

        ).as("envelope");

        builder.select().from().table().database("master").schema("dbo").name("test1");

        builder.select().where().set(

            builder.ST_Intersects(

                builder.ST_Envelope(

                    builder.column("the_geom")

                ),

                builder.geometry(limit, proj)

            )

        );

        builder.select().where().and(

                builder.custom("x = 27").add( builder.variable("x") )

        );

        System.out.println("# Test:: testCalulateEnvelope");

        System.out.println("# SQL:: " + builder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

        //# Test:: testCalulateEnvelope

        //# SQL:: SELECT ST_AsBinary(Extent("the_geom")) AS "envelope" FROM "test1" WHERE ST_Intersects((ST_Envelope("the_geom")), (ST_GeomFromWKB((x'000000000300000001000000050000000000000000000000000000000000000000000000004059000000000000405900000000000040590000000000004059000000000000000000000000000000000000000000000000000000000000'), (4326)))) AND x = 27

        //# Variables:: [the_geom, x]

        //# Parametros:: []  

        assertEquals(

                "SELECT ST_AsBinary(Extent(\"the_geom\")) AS \"envelope\" FROM \"test1\" WHERE ST_Intersects((ST_Envelope(\"the_geom\")), (ST_GeomFromWKB((x'000000000300000001000000050000000000000000000000000000000000000000000000004059000000000000405900000000000040590000000000004059000000000000000000000000000000000000000000000000000000000000'), (4326)))) AND x = 27",

                builder.toString()

        );

        assertEquals(

                "[the_geom, x]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void testCount() throws Exception {

        SQLBuilder builder = createSQLBuilder();

        builder.select().column().value(builder.count().all());

        builder.select().from().table().database("master").schema("dbo").name("test1");

        builder.select().from().subquery(null);

        builder.select().where().set( builder.custom("pp = 200").add(builder.variable("pp")));

        System.out.println("# Test:: testCount");

        System.out.println("# SQL:: " + builder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

        //# Test:: testCount

        //# SQL:: SELECT COUNT(*) FROM "test1" WHERE pp = 200

        //# Variables:: [pp]

        //# Parametros:: []

        assertEquals(

                "SELECT COUNT(*) FROM \"test1\" WHERE pp = 200",

                builder.toString()

        );

        assertEquals(

                "[pp]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void testUpdateStatistics() throws Exception {

        SQLBuilder builder = createSQLBuilder();

        builder.update_table_statistics().table().database("master").schema("dbo").name("test1");

        System.out.println("# Test:: testUpdateStatistics");

        System.out.println("# SQL:: " + builder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

        //# Test:: testUpdateStatistics

        //# SQL:: SELECT UpdateLayerStatistics("test1")

        //# Variables:: []

        //# Parametros:: []

        assertEquals(

                "SELECT UpdateLayerStatistics(\"test1\")",

                builder.toString()

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void testCreateTable() throws Exception {

        SQLBuilder builder = createSQLBuilder();

        builder.create_table().table().database("master").schema("dbo").name("test1");

        builder.create_table().add_column(

                "name",

                DataTypes.STRING,

                45,

                0,

                false,

                false,

                true,

                false,

                null

        );

        builder.create_table().add_column(

                "id",

                DataTypes.INT,

                0,

                0,

                true,

                false,

                false,

                true,

                0

        );

        builder.create_table().add_column(

                "geom",

                DataTypes.GEOMETRY,

                0,

                0,

                false,

                false,

                true,

                false,

                null

        );

        System.out.println("# Test:: testCreateTable");

        System.out.println("# SQL:: " + builder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

        //# Test:: testCreateTable

        //# SQL:: CREATE TABLE "test1" ("name" TEXT DEFAULT NULL NULL, "id" INTEGER PRIMARY KEY AUTOINCREMENT DEFAULT '0' NOT NULL ); SELECT AddGeometryColumn("test1" , "geom" , -1 , 'GEOMETRY' , 'XYZ', 1)

        //# Variables:: []

        //# Parametros:: []

        assertEquals(

                "CREATE TABLE \"test1\" (\"name\" TEXT DEFAULT NULL NULL, \"id\" INTEGER PRIMARY KEY AUTOINCREMENT DEFAULT '0' NOT NULL ); SELECT AddGeometryColumn(\"test1\" , \"geom\" , -1 , 'GEOMETRY' , 'XYZ', 1)",

                builder.toString()

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void testDropTable() throws Exception {

        SQLBuilder builder = createSQLBuilder();

        builder.drop_table().table().database("master").schema("dbo").name("test1");

        System.out.println("# Test:: testDropTable");

        System.out.println("# SQL:: " + builder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

        //# Test:: testDropTable

        //# SQL:: DROP TABLE [master].[dbo].[test1]

        //# Variables:: []

        //# Parametros:: []

        assertEquals(

                "DROP TABLE [master].[dbo].[test1]",

                builder.toString()

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void testFetchFeatureProviderByReference() throws Exception {

        SQLBuilder builder = createSQLBuilder();

        String value = "yoyo";

        builder.select().column().name("name");

        builder.select().column().name("id");

        builder.select().column().name("geom").as_geometry();

        builder.select().from().table().database("master").schema("dbo").name("test1");

        builder.select().where().set(

            builder.eq(

                builder.column("name"),

                builder.parameter(value).as_constant()

            )

        );

        builder.select().limit(1);

        System.out.println("# Test:: testFetchFeatureProviderByReference");

        System.out.println("# SQL:: " + builder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

        //# Test:: testFetchFeatureProviderByReference

        //# SQL:: SELECT TOP 1 [name], [id], ([geom]).STAsBinary() FROM [master].[dbo].[test1] WHERE ( ([name]) = (?) )

        //# Variables:: [geom, id, name]

        //# Parametros:: ['yoyo']

        assertEquals(

                "SELECT TOP 1 [name], [id], ([geom]).STAsBinary() FROM [master].[dbo].[test1] WHERE ( ([name]) = (?) )",

                builder.toString()

        );

        assertEquals(

                "[geom, id, name]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "['yoyo']",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void testIsNull() throws Exception {

        SQLBuilder builder = createSQLBuilder();

        builder.select().column().name("name");

        builder.select().column().name("id");

        builder.select().column().name("geom").as_geometry();

        builder.select().from().table().database("master").schema("dbo").name("test1");

        builder.select().where().set(

            builder.and(

                builder.isNull(

                    builder.column("name")

                ),

                builder.notIsNull(

                        builder.column("id")

                )

            )

        );

        builder.select().limit(2);

        System.out.println("# Test:: testIsNull");

        System.out.println("# SQL:: " + builder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

        //# Test:: testIsNull

        //# SQL:: SELECT TOP 2 [name], [id], ([geom]).STAsBinary() FROM [master].[dbo].[test1] WHERE ( ([name]) IS NULL ) AND ( ([id]) NOT IS NULL )

        //# Variables:: [geom, id, name]

        //# Parametros:: []

        assertEquals(

                "SELECT TOP 2 [name], [id], ([geom]).STAsBinary() FROM [master].[dbo].[test1] WHERE ( ([name]) IS NULL ) AND ( ([id]) NOT IS NULL )",

                builder.toString()

        );

        assertEquals(

                "[geom, id, name]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void testFetchFeatureType() throws Exception {

        SQLBuilder builder = createSQLBuilder();

        builder.select().column().all();

        builder.select().from().table().database("master").schema("dbo").name("test1");

        builder.select().limit(1);

        System.out.println("# Test:: testFetchFeatureType");

        System.out.println("# SQL:: " + builder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

        //# Test:: testFetchFeatureType

        //# SQL:: SELECT TOP 1 * FROM [master].[dbo].[test1]

        //# Variables:: []

        //# Parametros:: []

        assertEquals(

                "SELECT TOP 1 * FROM [master].[dbo].[test1]",

                builder.toString()

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void testPerformDeletes() throws Exception {

        SQLBuilder builder = createSQLBuilder();

        builder.delete().table().database("master").schema("dbo").name("test1");

        builder.delete().where().and(

            builder.eq( 

                builder.column("id1"),

                builder.parameter("id1").as_variable()

            )

        );

        builder.delete().where().and(

            builder.eq( 

                builder.column("id2"),

                builder.parameter("id2").as_variable()

            )

        );

        System.out.println("# Test:: testPerformDeletes");

        System.out.println("# SQL:: " + builder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

        //# Test:: testPerformDeletes

        //# SQL:: DELETE FROM [master].[dbo].[test1] WHERE ( ([id1]) = (?) ) AND ( ([id2]) = (?) )

        //# Variables:: [id1, id2]

        //# Parametros:: ["id1", "id2"]

        assertEquals(

                "DELETE FROM [master].[dbo].[test1] WHERE ( ([id1]) = (?) ) AND ( ([id2]) = (?) )",

                builder.toString()

        );

        assertEquals(

                "[id1, id2]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[\"id1\", \"id2\"]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void testPerformInserts1() throws Exception {

        IProjection proj = CRSFactory.getCRS("EPSG:4326");

        SQLBuilder builder = createSQLBuilder();

        builder.insert().table().database("master").schema("dbo").name("test1");

        builder.insert().column().name("id").with_value(builder.parameter("id"));

        builder.insert().column().name("name").with_value(builder.parameter("name"));

        builder.insert().column().name("geom").with_value(builder.parameter("geom").as_geometry_variable().srs(builder.parameter().srs(proj)));

        System.out.println("# Test:: testPerformInserts1");

        System.out.println("# SQL:: " + builder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

        //# Test:: testPerformInserts1

        //# SQL:: INSERT INTO [master].[dbo].[test1] ( [id], [name], [geom] ) VALUES ( ?, ?, geometry::STGeomFromWKB(?, 4326) )

        //# Variables:: [geom, id, name]

        //# Parametros:: ["id", "name", "geom"]

        assertEquals(

                "INSERT INTO [master].[dbo].[test1] ( [id], [name], [geom] ) VALUES ( ?, ?, geometry::STGeomFromWKB(?, 4326) )",

                builder.toString()

        );

        assertEquals(

                "[geom, id, name]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[\"id\", \"name\", \"geom\"]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void testPerformInserts2() throws Exception {

        IProjection proj = CRSFactory.getCRS("EPSG:4326");

        SQLBuilder builder = createSQLBuilder();

        builder.insert().table().database("master").schema("dbo").name("test1");

        builder.insert().column().name("id").with_value(builder.parameter("id"));

        builder.insert().column().name("name").with_value(builder.parameter("name"));

        builder.insert().column().name("geom").with_value(builder.parameter("geom").as_geometry_variable().srs(builder.parameter().srs(proj)));

        System.out.println("# Test:: testPerformInserts2");

        System.out.println("# SQL:: " + builder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

        //# Test:: testPerformInserts2

        //# SQL:: INSERT INTO [master].[dbo].[test1] ( [id], [name], [geom] ) VALUES ( ?, ?, geometry::STGeomFromWKB(?, ?) )

        //# Variables:: [geom, id, name]

        //# Parametros:: ["id", "name", "geom", 4326]

        assertEquals(

                "INSERT INTO [master].[dbo].[test1] ( [id], [name], [geom] ) VALUES ( ?, ?, geometry::STGeomFromWKB(?, ?) )",

                builder.toString()

        );

        assertEquals(

                "[geom, id, name]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[\"id\", \"name\", \"geom\", 4326]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void testPerformUpdates() throws Exception {

        IProjection proj = CRSFactory.getCRS("EPSG:4326");

        SQLBuilder builder = createSQLBuilder();

        builder.update().table().database("master").schema("dbo").name("test1");

        builder.update().where().and(

            builder.eq(

                builder.column("id"), 

                builder.parameter("id").as_variable()

            )

        );

        builder.update().column().name("name").with_value(builder.parameter("name"));

        builder.update().column().name("geom").with_value(

            builder.parameter("geom").as_geometry_variable().srs(builder.parameter().srs(proj))

        );

        System.out.println("# Test:: testPerformUpdates");

        System.out.println("# SQL:: " + builder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

        //# Test:: testPerformUpdates

        //# SQL:: UPDATE [master].[dbo].[test1] SET [name] = ?, [geom] = geometry::STGeomFromWKB(?, ?) WHERE ( ([id]) = (?) )

        //# Variables:: [geom, id, name]

        //# Parametros:: ["name", "geom", 4326, "id"]

        assertEquals(

                "UPDATE [master].[dbo].[test1] SET [name] = ?, [geom] = geometry::STGeomFromWKB(?, ?) WHERE ( ([id]) = (?) )",

                builder.toString()

        );

        assertEquals(

                "[geom, id, name]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[\"name\", \"geom\", 4326, \"id\"]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

}

org.gvsig.spatialite.dal.SpatiaLiteLibrary

<?xml version="1.0"?>

<definitions>

  <version>1.0.0</version>

  <classes>

    <class name="SpatiaLiteResourceParameters">

      <extends>

        <class>JDBC2ResourceParameters</class>

      </extends>

      <fields>

        <field name="JDBCDriverClass" type="string" mandatory="true"

          defaultValue="org.sqlite.JDBC" group="Advanced">

          <description>JDBC driver class for SpatiaLite</description>

        </field>        

        <field name="url_format" type="string" mandatory="true"

          defaultValue="jdbc:sqlite:%s" group="Advanced">

          <description></description>

        </field>

        <field name="port" type="integer" mandatory="false"

          defaultValue="0" group="Connection" hidden="true">

          <description></description>

        </field>

        <field name="host" type="string" mandatory="false" 

            defaultValue="" group="Connection" hidden="true">

          <description></description>

        </field>

        <field name="dbname" type="string" mandatory="false" hidden="true" defaultValue="" group="Basic">

          <description></description>

        </field>

        <field name="schema" type="string" mandatory="false" hidden="true" defaultValue="" group="Basic">

          <description></description>

        </field>

        <field name="catalog" type="string" mandatory="false" hidden="true" defaultValue="" group="Basic">

          <description></description>

        </field>

        <field name="url" type="string" mandatory="false" hidden="true" defaultValue="" group="Basic">

          <description></description>

        </field>

        <field name="dbuser" type="string" mandatory="false" defaultValue="" group="Basic">

          <description></description>

        </field>

        <field name="password" type="string" mandatory="false" defaultValue="" group="Basic">

          <description></description>

        </field>           

        <field name="database_file" label="Database file" type="file" mandatory="true" defaultValue="" group="Basic">

          <description></description>

        </field>            

        <field name="enable_shared_cache" label="Enable shared cache" type="boolean" mandatory="false" defaultValue="true" group="Advanced">

          <description></description>

        </field>

        <field name="enable_load_extension" label="Enable load extension" type="boolean" mandatory="false" defaultValue="true" group="Advanced">

          <description></description>

        </field>

        <field name="enable_spatialite" label="Enable spatialite" type="boolean" mandatory="false" defaultValue="true" group="Advanced">

          <description></description>

        </field>

      </fields>

    </class>

    <class name="SpatiaLiteStoreParameters">

      <extends>

        <class>JDBC2StoreParameters</class>

        <class>SpatiaLiteResourceParameters</class>

      </extends>

      <fields/>

    </class>

    <class name="SpatiaLiteNewStoreParameters">

      <extends>

        <class>JDBC2NewStoreParameters</class>

        <class>SpatiaLiteResourceParameters</class>

      </extends>

      <fields/>

    </class>

    <class name="SpatiaLiteServerExplorerParameters">

      <extends>

        <class>SpatiaLiteResourceParameters</class>

        <class>JDBC2ServerExplorerParameters</class>

      </extends>

      <fields/>

    </class>

  </classes>

</definitions>  

<?xml version="1.0"?>

<definitions>

  <version>1.0.0</version>

  <classes>

    <class name="JDBC2">

      <extends>

      </extends>

      <fields>

        <field name="function_ST_AsWKB" type="string" group="functions" 

          defaultValue="ST_AsWKB({0})" >

          <description></description>

        </field>

        <field name="function_ST_envelope" type="string" group="functions" 

          defaultValue="ST_envelope({0})" >

          <description></description>

        </field>

        <field name="function_ST_Extent" type="string" group="functions" 

          defaultValue="ST_Extent({0})" >

          <description></description>

        </field>

        <field name="function_ST_intersects" type="string" group="functions" 

          defaultValue="ST_intersects({0}, {1})" >

          <description></description>

        </field>

        <field name="function_ST_GeomFromText" type="string" group="functions" 

          defaultValue="ST_GeomFromText({0}, {1})" >

          <description></description>

        </field>

        <field name="function_ST_GeomFromWKB" type="string" group="functions" 

          defaultValue="ST_GeomFromWKB({0}, {1})" >

          <description></description>

        </field>

        <field name="function_ST_GeomFromEWKB" type="string" group="functions" 

          defaultValue="ST_GeomFromEWKB({0}, {1})" >

          <description></description>

        </field>

        <field name="function_current_schema" type="string" group="functions" 

          defaultValue="current_schema()" >

          <description></description>

        </field>

        <field name="function_lcase" type="string" group="functions" 

          defaultValue="LCASE({0})" >

          <description></description>

        </field>

        <field name="function_count" type="string" group="functions" 

          defaultValue="count({0})" >

          <description></description>

        </field>

        <field name="function_crs" type="string" group="functions" 

          defaultValue="{0}" >

          <description>Use {0} for the abbreviature, {1} for the autority name and {2} for the autority code</description>

        </field>

        <!-- operators -->

        <field name="operator_isnull" type="string" group="operators" 

          defaultValue="{0} is null" >

          <description></description>

        </field>

        <field name="operator_eq" type="string" group="operators" 

          defaultValue="{0} = {1}" >

          <description></description>

        </field>

        <field name="operator_neq" type="string" group="operators" 

          defaultValue="{0} <> {1}" >

          <description></description>

        </field>

        <field name="operator_lt" type="string" group="operators" 

          defaultValue="{0} < {1}" >

          <description></description>

        </field>

        <field name="operator_le" type="string" group="operators" 

          defaultValue="{0} <= {1}" >

          <description></description>

        </field>

        <field name="operator_gt" type="string" group="operators" 

          defaultValue="{0} > {1}" >

          <description></description>

        </field>

        <field name="operator_ge" type="string" group="operators" 

          defaultValue="{0} >= {1}" >

          <description></description>

        </field>

        <field name="operator_and" type="string" group="operators" 

          defaultValue="{0} AND {1}" >

          <description></description>

        </field>

        <field name="operator_or" type="string" group="operators" 

          defaultValue="{0} OR {1}" >

          <description></description>

        </field>

        <field name="operator_like" type="string" group="operators" 

          defaultValue="{0} LIKE {1}" >

          <description></description>

        </field>

        <field name="operator_ilike" type="string" group="operators" 

          defaultValue="{0} ILIKE {1}" >

          <description></description>

        </field>

        <!-- properties -->

        <field name="AllowAutomaticValues" type="boolean" group="properties" 

          defaultValue="true" >

          <description></description>

        </field>

        <field name="QuoteForStrings" type="string" group="properties" 

          defaultValue="'" >

          <description></description>

        </field>

        <field name="QuoteForIdentifiers" type="string" group="properties" 

          defaultValue="" >

          <description></description>

        </field>

        <field name="GeometrySupportType" type="string" group="properties" 

            defaultValue="WKB" >

            <description></description>

            <availableValues>

                <value label="WKT">WKT</value>

                <value label="WKB">WKB</value>

                <value label="EWKB">EWKB</value>

            </availableValues>          

        </field>

      </fields>

    </class>

    <class name="SpatiaLite">

      <extends>

          <class>JDBC2</class>

      </extends>

      <fields>

        <field name="function_ST_AsWKB" type="string" group="functions" 

          defaultValue="({0}).STAsWKB" >

          <description></description>

        </field>

        <field name="function_ST_envelope" type="string" group="functions" 

          defaultValue="({0}).STEnvelope" >

          <description></description>

        </field>

        <field name="function_ST_Extent" type="string" group="functions" 

          defaultValue="({0}).STEnvelope" >

          <description></description>

        </field>

        <field name="function_ST_intersects" type="string" group="functions" 

          defaultValue="({0}).STIntersects({1})" >

          <description></description>

        </field>

        <field name="function_ST_GeomFromText" type="string" group="functions" 

          defaultValue="geometry::STGeomFromText({0}, {1})" >

          <description></description>

        </field>

        <field name="function_ST_GeomFromWKB" type="string" group="functions" 

          defaultValue="geometry::STGeomFromWKB({0}, {1})" >

          <description></description>

        </field>

        <field name="function_ST_GeomFromEWKB" type="string" group="functions" 

          defaultValue="geometry::STGeomFromWKB({0}, {1})" >

          <description></description>

        </field>

        <field name="function_current_schema" type="string" group="functions" 

          defaultValue="current_schema()" >

          <description></description>

        </field>

        <field name="function_crs" type="string" group="functions" 

          defaultValue="{2}" >

          <description>Use {0} for the abbreviature, {1} for the autority name and {2} for the autority code</description>

        </field>

        <field name="function_lcase" type="string" group="functions" 

          defaultValue="LOWER({0})" >

          <description></description>

        </field>

        <!-- operators -->

        <field name="operator_ilike" type="string" group="operators" 

          defaultValue="{0} ILIKE {1}" >

          <description></description>

        </field>

  </classes>

</definitions>  

<?xml version="1.0"?>

<definitions>

  <version>1.0.0</version>

  <classes>

    <class name="SpatiaLite" namespace="Metadata">

      <extends>

      	<class name="JDBC2" namespace="Metadata"/>

      </extends>

      <description>Metadata for SpatiaLite</description>

      <fields>

      </fields>

    </class>

  </classes>

</definitions>  

/* 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.sqlite.SQLiteConfig;

import org.sqlite.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.sqlite.JDBC";

    public static final String URL_FORMAT = "jdbc:sqlite:%s";

    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 driverClassName = params.getJDBCDriverClassName();

        if( StringUtils.isNotBlank(driverClassName) && 

            !StringUtils.equals(driverClassName, SpatiaLiteJDBCDriver) ) {

            try {

                Class.forName(driverClassName);

            } catch(Throwable th) {

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

            }            

        }

        String connectionURLFormat = params.getURLFormat(); 

        if( StringUtils.isBlank(connectionURLFormat) ) {

            connectionURLFormat = URL_FORMAT;

        }

        String connectionURL = String.format(connectionURLFormat, 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();

            loadExtension(conn);

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

        }

        public void loadExtension(Connection conn) {

    		Statement st = null;

            try {

                st = conn.createStatement();

                st.execute("SELECT load_extension('mod_spatialite')");

            } catch(Exception ex) {

                logger.warn("Can't load mod_spatialite extension module for SQLite (" +

                        " driver class "+StringUtils.defaultString(connectionParameters.getJDBCDriverClassName()) +

                        " url format "+StringUtils.defaultString(connectionParameters.getURLFormat()) +

                        " file "+connectionParameters.getFile()==null?"null":connectionParameters.getFile().getAbsolutePath() +

                        ").", ex);

            }

            finally {

            	JDBCUtils.closeQuietly(st);

            }

    	}

    }

    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 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 {

        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;

    }

}

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);

    }

    @Override

    public String getURLFormat() {

        return this.helper.getURLFormat();

    }

}

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();

    public String getURLFormat();

}

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";