Application: gvSIG desktop

/*

 * To change this license header, choose License Headers in Project Properties.

 * To change this template file, choose Tools | Templates

 * and open the template in the editor.

 */

package org.gvsig.sqlite.dal.geopackage;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Map;

import org.gvsig.expressionevaluator.ExpressionBuilder;

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

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

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

import org.gvsig.fmap.geom.Geometry;

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

import org.gvsig.sqlite.dal.operations.SQLiteFetchFeatureTypeOperation.SQLiteColumnInfo;

import org.sqlite.SQLiteException;

/**

 *

 * @author jjdelcerro

 */

public class GeopackageGeometryColumns {

    public static class GeopackageGeometryColumn extends SQLiteFetchFeatureTypeOperation.SQLiteColumnInfo {

        private String geometry_type_name;

        private int srs_id;

        private int z;

        private int m;

        /*friend*/ GeopackageGeometryColumn() {

            super();

        }

        public void set(

                String table_name,

                String column_column,

                String geometry_type_name,

                int srs_id,

                int z,

                int m

        ) {

            this.table_name = table_name;

            this.column_column = column_column;

            this.geometry_type_name = geometry_type_name;

            this.srs_id = srs_id;

            this.z = z;

            this.m = m;

        }

        /**

         * @return the geometry_type_name

         */

        public String getGeometry_type_name() {

            return geometry_type_name;

        }

        /**

         * @return the srs_id

         */

        public int getSrs_id() {

            return srs_id;

        }

        /**

         * @return the z

         */

        public int getZ() {

            return z;

        }

        /**

         * @return the m

         */

        public int getM() {

            return m;

        }

    }

    /*friend*/ static void load_gpkg_geometry_columns(Map<String,SQLiteColumnInfo> columns_info, JDBCConnection conn, String tableName, JDBCSQLBuilderBase sqlbuilder) {

        ExpressionBuilder expbuilder = sqlbuilder.expression();

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

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

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

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

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

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

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

                expbuilder.eq(

                        expbuilder.column("table_name"),

                        expbuilder.constant(tableName)

                )

        );                

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

        Statement st = null;

        ResultSet rs = null;

        try {

            String sql = sqlbuilder.toString();

            st = conn.createStatement(sql);

            rs = JDBCUtils.executeQuery(st, sql);

            while( rs.next() ) {

                String columnname = rs.getString("column_name");

                GeopackageGeometryColumn geominfo = new GeopackageGeometryColumn();

                geominfo.copyfrom(columns_info.get(columnname));

                geominfo.set(

                    rs.getString("table_name"),

                    columnname,

                    rs.getString("geometry_type_name"),

                    rs.getInt("srs_id"),

                    rs.getInt("z"),

                    rs.getInt("m")

                );

                columns_info.put(columnname, geominfo);

            }

        } catch(SQLiteException ex) {

            if( !ex.getMessage().contains("no such table") ) {

                throw new RuntimeException("Can't load information from table gpkg_geometry_columns",ex);

            }

        } catch (Throwable ex) {

            throw new RuntimeException("Can't load information from table gpkg_geometry_columns",ex);

        } finally {

            JDBCUtils.closeQuietly(rs);

            JDBCUtils.closeQuietly(st);

        }

    }    

    public static String buildCreateTableSQLIfNotExists() {

        return "CREATE TABLE IF NOT EXISTS \"gpkg_geometry_columns\" ( "

                + "\"table_name\" TEXT NOT NULL, "

                + "\"column_name\" TEXT NOT NULL, "

                + "\"geometry_type_name\" TEXT NOT NULL, "

                + "\"srs_id\" INTEGER NOT NULL, "

                + "\"z\" TINYINT NOT NULL, "

                + "\"m\" TINYINT NOT NULL, "

                + "CONSTRAINT \"pk_geom_cols\" PRIMARY KEY(\"table_name\",\"column_name\"), "

                + "CONSTRAINT \"fk_gc_tn\" FOREIGN KEY(\"table_name\") REFERENCES \"gpkg_contents\"(\"table_name\"), "

                + "CONSTRAINT \"uk_gc_table_name\" UNIQUE(\"table_name\"), "

                + "CONSTRAINT \"fk_gc_srs\" FOREIGN KEY(\"srs_id\") REFERENCES \"gpkg_spatial_ref_sys\"(\"srs_id\") "

                + ")";

    }

    public static String buildInsertSQL(String tableName, String geomName, int geometryType, int geometrySubtype, Object geometrySRSId) {

        String geometryTypeName = "GEOMETRY";

        switch(geometryType){

            case Geometry.TYPES.POINT:

                geometryTypeName = "POINT";

                break;

            case Geometry.TYPES.ARC:

            case Geometry.TYPES.CIRCUMFERENCE:

            case Geometry.TYPES.ELLIPTICARC:

            case Geometry.TYPES.LINE:

            case Geometry.TYPES.PERIELLIPSE:

            case Geometry.TYPES.SPLINE:

                geometryTypeName = "LINESTRING";

                break;

            case Geometry.TYPES.CIRCLE:

            case Geometry.TYPES.ELLIPSE:

            case Geometry.TYPES.FILLEDSPLINE:

            case Geometry.TYPES.POLYGON:

                geometryTypeName = "POLYGON";

                break;

            case Geometry.TYPES.MULTILINE:

                geometryTypeName = "MULTILINESTRING";

                break;

            case Geometry.TYPES.MULTIPOINT:

                geometryTypeName = "MULTIPOINT";

                break;

            case Geometry.TYPES.MULTIPOLYGON:

                geometryTypeName = "MULTIPOLYGON";

                break;

        }

        String s = String.format(

                "INSERT INTO \"gpkg_geometry_columns\" (\"table_name\", \"column_name\", \"geometry_type_name\", \"srs_id\", \"z\", \"m\") VALUES ('%s', '%s', '%s', '%s', '%s', '%s')",

                tableName,

                geomName,

                geometryTypeName,

                geometrySRSId.toString(),

                (geometrySubtype==Geometry.SUBTYPES.GEOM3D || geometrySubtype==Geometry.SUBTYPES.GEOM3DM)?1:0,

                (geometrySubtype==Geometry.SUBTYPES.GEOM2DM || geometrySubtype==Geometry.SUBTYPES.GEOM3DM)?1:0

                );

        return s;

    }

    public static String buildDeleteSQL(String tableName, String geomName) {

        String s = String.format(

                "DELETE FROM \"gpkg_geometry_columns\" WHERE \"table_name\" = '%s' and \"column_name\" = '%s'",

                tableName,

                geomName

                );

        return s;

    }

    public static String buildDeleteSQL(String tableName) {

        String s = String.format(

                "DELETE FROM \"gpkg_geometry_columns\" WHERE \"table_name\" = '%s'",

                tableName

                );

        return s;

    }

}