Application: gvSIG desktop

package org.gvsig.sqlite.dal;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.sql.Time;

import java.sql.Timestamp;

import java.text.MessageFormat;

import java.time.LocalDateTime;

import java.time.format.DateTimeFormatter;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import org.gvsig.expressionevaluator.ExpressionBuilder.Parameter;

import org.gvsig.expressionevaluator.Formatter;

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

import org.gvsig.fmap.geom.Geometry;

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

import org.gvsig.sqlite.dal.expressionbuilderformatter.SQLiteFormatter;

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

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

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

import org.gvsig.sqlite.dal.geopackage.index.GeopackageIndexRTree;

import org.gvsig.sqlite.dal.utils.TemplateUtils;

import org.gvsig.tools.dataTypes.DataTypeUtils;

import org.gvsig.tools.dispose.Disposable;

import org.sqlite.SQLiteConnection;

public class SQLiteSQLBuilder extends JDBCSQLBuilderBase {

    protected Formatter formatter = null;

    public SQLiteSQLBuilder(SQLiteHelper helper) {

        super(helper);      

        //

        // SQLite 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

        //

        this.defaultSchema = "";

        this.supportSchemas = false;

        this.allowAutomaticValues = true;

        this.geometrySupportType = this.helper.getGeometrySupportType();

        this.hasSpatialFunctions = this.helper.hasSpatialFunctions();

        this.expressionBuilder.geometry_support_type(this.geometrySupportType);

        this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table = null; //"DELETE FROM gpkg_geometry_columns WHERE table_name = {1}";

        this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_table = "DELETE FROM gpkg_geometry_columns WHERE table_name = {0}";

        this.STMT_UPDATE_TABLE_STATISTICS_table = "ANALYZE {0}";

        this.type_boolean = "BOOLEAN";

        this.type_byte = "TINYINT";

        this.type_bytearray = "BLOB";

        this.type_geometry = "BLOB";

        this.type_char = "CHARACTER(1)";

        this.type_date = "DATE";

        this.type_double = "DOUBLE PRECISION"; 

        this.type_decimal_ps = "DECIMAL({0,Number,##########},{1,Number,##########})";

        this.type_decimal_p = "DECIMAL({0,Number,##########})";

        this.type_float = "FLOAT";

        this.type_int = "MEDIUMINT";

        this.type_long = "BIGINT";

        this.type_string = "TEXT";

        this.type_string_p = "VARCHAR({0,Number,#######})";

        this.type_time = "TIME";

        this.type_timestamp = "DATETIME";

        this.type_version = "VARCHAR(30)";

        this.type_URI = "TEXT";

        this.type_URL = "TEXT";

        this.type_FILE = "TEXT";

        this.type_FOLDER = "TEXT";           

//        config.set(SQLConfig.ST_ExtentAggregate, "(1)");

//        config.set(SQLConfig.ST_UnionAggregate, "(1)");

//        config.set(SQLConfig.ST_ExtentAggregate, "(1)");        

//        config.set(SQLConfig.ST_UnionAggregate, "(1)");

//        config.set(SQLConfig.Find_SRID, "(1)");

//        config.set(SQLConfig.ST_SRID, "(1)");

//        config.set(SQLConfig.ST_Contains, "(1)");

//        config.set(SQLConfig.ST_Crosses, "(1)");

//        config.set(SQLConfig.ST_Disjoint, "(1)");

//        config.set(SQLConfig.ST_Equals, "(1)");

//        config.set(SQLConfig.ST_IsClosed, "(1)");

//        config.set(SQLConfig.ST_Overlaps, "(1)");

//        config.set(SQLConfig.ST_Touches, "(1)");

//        config.set(SQLConfig.ST_Within, "(1)");

//        config.set(SQLConfig.ST_Intersects, "(1)");

//        config.set(SQLConfig.ST_Simplify, "(1)");

//        config.set(SQLConfig.ST_Within, "(1)");

//        config.set(SQLConfig.ST_GeomFromWKB, "({0})");

//        config.set(SQLConfig.ST_AsBinary, "({0})");

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

//        config.set(SQLConfig.ST_AsText, "({0})");

    }

    public class SQLiteTableNameBuilderBase extends TableNameBuilderBase {

        @Override

        public boolean has_schema() {

            return false;

        }

        @Override

        public boolean has_database() {

            return false;

        }

    }

    protected class SQLiteCreateTableBuilder extends CreateTableBuilderBase {

        @Override

        public List<String> toStrings(Formatter formatter) {

            //

            // Respecto al base cambia la declaracion de campo automaticos

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

            sqls.add("CREATE TABLE IF NOT EXISTS gpkg_contents (table_name TEXT NOT NULL PRIMARY KEY,data_type TEXT NOT NULL,identifier TEXT UNIQUE,description TEXT DEFAULT '',last_change DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),min_x DOUBLE, min_y DOUBLE,max_x DOUBLE, max_y DOUBLE,srs_id INTEGER,CONSTRAINT fk_gc_r_srs_id FOREIGN KEY (srs_id) REFERENCES gpkg_spatial_ref_sys(srs_id))");

            if(this.hasGeometry()) {

                sqls.add(TemplateUtils.getSqlTemplate(GeopackageIndexRTree.GROUP_NAME, "create_table_gpkg_extensions"));

                sqls.add(GeopackageGeometryColumns.buildCreateTableSQLIfNotExists());

                sqls.add(String.format(

                        "INSERT INTO \"gpkg_contents\" (\"table_name\", \"data_type\", \"identifier\", \"description\", \"last_change\", \"min_x\", \"min_y\", \"max_x\", \"max_y\", \"srs_id\") VALUES ('%s', '%s', '%s', '', '%s', NULL, NULL, NULL, NULL, NULL)",

                        this.table.getName(),

                        "features",

                        this.table.getName(),

                        new java.sql.Timestamp(new Date().getTime()).toString()

                ));

            } else {

                sqls.add(String.format(

                        "INSERT INTO \"gpkg_contents\" (\"table_name\", \"data_type\", \"identifier\", \"description\", \"last_change\", \"min_x\", \"min_y\", \"max_x\", \"max_y\", \"srs_id\") VALUES ('%s', '%s', '%s', '', '%s', NULL, NULL, NULL, NULL, NULL)",

                        this.table.getName(),

                        "attributes",

                        this.table.getName(),

                        new java.sql.Timestamp(new Date().getTime()).toString()

                ));

            }

            StringBuilder builder = new StringBuilder();

            String pkName = null;

            builder.append("CREATE TABLE ");

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

            builder.append(" (");

            boolean first = true;

            for (ColumnDescriptor column : columns) {

                if (first) {

                    first = false;

                } else {

                    builder.append(", ");

                }

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

                builder.append(" ");

                builder.append(

                    sqltype(

                        column.getType(), 

                        column.getSize(),

                        column.getPrecision(), 

                        column.getScale(), 

                        column.getGeometryType(), 

                        column.getGeometrySubtype()

                    )

                );

                if (column.isPrimaryKey()) {

                    builder.append(" PRIMARY KEY");

                    pkName = column.getName();

                }

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

            for (ColumnDescriptor column : columns) {

                if (column.isGeometry()) {

                    sqls.add(GeopackageGeometryColumns.buildInsertSQL(

                            this.table.getName(), 

                            column.getName(), 

                            column.getGeometryType(), 

                            column.getGeometrySubtype(),

                            column.getGeometrySRSId()

                    ));

                    GeopackageIndexRTree index = new GeopackageIndexRTree();

                    sqls.addAll(index.getCreateIndexSQL(this.table.getName(), column.getName(), pkName));

                }

            }

            return sqls;

        }

        private boolean hasGeometry() {

            for (ColumnDescriptor column : columns) {

                if (column.isGeometry()) {

                    return true;

                }

            }

            return false;

        }

    }

    protected class SQLiteDropTableBuilder extends DropTableBuilderBase {

        @Override

        public List<String> toStrings(Formatter formatter) {

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

            //No se pueden borrar aqui los indices espaciales, se hace en el explorer.remove

//            GeopackageIndexRTree index = new GeopackageIndexRTree();

//            sqls.addAll(index.getRemoveIndexSQL(this.table.getName(), column.getName(), pkName));

            sqls.add(GeopackageGeometryColumns.buildDeleteSQL(this.table.getName()));

            sqls.add(String.format(

                    "DELETE FROM \"gpkg_contents\" WHERE \"identifier\" = '%s'",

                    this.table.getName()

            ));

            sqls.add(String.format(

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

                    this.table.getName()

            ));

            sqls.add(

                    MessageFormat.format(

                            STMT_DROP_TABLE_table,

                            this.table.toString(formatter)

                    )

            );

            return sqls;

        }

    }

    public class SQLiteSelectBuilderBase extends SelectBuilderBase {

        @Override

        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(Formatter formatter) {

            // SQLite 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(formatter));

            }

            if ( this.has_from() ) {

                builder.append(" FROM ");

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

            }

            if ( this.has_where() ) {

                builder.append(" WHERE ");

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

            }

            if( this.has_group_by() ) {

                builder.append(" GROUP BY ");

                builder.append(this.groupColumn.get(0).toString(formatter));

                for (int i = 1; i < groupColumn.size(); i++) {

                    builder.append(", ");

                    builder.append(this.groupColumn.get(i).toString(formatter));

                }

            }

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

                }   

            }

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

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

        }

    }

    @Override

    public SQLiteHelper getHelper() {

        return (SQLiteHelper) this.helper;

    }

    @Override

    public Disposable setParameters(PreparedStatement st, FeatureProvider feature) {        

        try {

            FeatureType featureType = feature.getType();

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

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

                if (parameter.is_constant()) {

                    values.add(parameter.value());

                } else {

                    String name = parameter.name();

                    FeatureAttributeDescriptor descriptor = featureType.getAttributeDescriptor(name);

                    switch( descriptor.getType() ) {

                    case DataTypes.DATE:

                        Date value = (Date)(feature.get(name));

                        if( value == null ) {

                            values.add(null);

                        } else {

                            LocalDateTime localDate = DataTypeUtils.toLocalDateTime(value);

                            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");

                            values.add(localDate.format(formatter));

                        }

                        break;

                    case DataTypes.TIME:

                        Time time = (Time)(feature.get(name));

                        if( time == null ) {

                            values.add(null);

                        } else {

                            LocalDateTime localDate = DataTypeUtils.toLocalDateTime(time);

                            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("HH:mm:ss");

                            values.add(localDate.format(formatter));

                        }

                        break;

                    case DataTypes.TIMESTAMP:

                        Timestamp timestamp= (Timestamp)(feature.get(name));

                        if( timestamp == null ) {

                            values.add(null);

                        } else {

                            LocalDateTime localDate = DataTypeUtils.toLocalDateTime(timestamp);

                            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

                            values.add(localDate.format(formatter));

                        }

                        break;

                    case DataTypes.GEOMETRY:

                        Geometry geom = this.forceGeometryType(

                            descriptor.getGeomType(),

                            (Geometry)(feature.get(name))

                        );

                        values.add(geom);

                        break;

                    default:

                        values.add(feature.get(name));

                        break;

                    }

                }

            }

            return this.setStatementParameters(st, values, this.geometry_support_type());

        } catch (SQLException | CreateGeometryException ex) {

            String f = "unknow";

            try {

                f = feature.toString();

            } catch (Exception ex2) {

                // Do nothing

            }

            throw new RuntimeException("Can't set parameters to prepared statement from the feature (" + f + ")", ex);

        }

    }

    @Override

    protected byte[] getNativeBytes(Connection conn, Geometry geometry) throws Exception {

        GeopackageGeometryBuilder builder = GeopackageUtils.createGeometryBuilder();

        builder.setGeometry((SQLiteConnection) conn, geometry);

        byte[] bytes = builder.build();

        return bytes;

    }

    @Override

    public TableNameBuilder createTableNameBuilder() {

        return new SQLiteTableNameBuilderBase();

    }

    protected DropTableBuilder createDropTableBuilder() {

        return new SQLiteDropTableBuilder();

    }

    @Override

    protected CreateTableBuilder createCreateTableBuilder() {

        return new SQLiteCreateTableBuilder();

    }

    @Override

    public SelectBuilder createSelectBuilder() {

        return new SQLiteSelectBuilderBase();

    }

    @Override

    public Formatter formatter() {

        if( this.formatter==null ) {

            this.formatter = new SQLiteFormatter(this);

        }

        return this.formatter;

    }      

}