Application: gvSIG desktop

package org.gvsig.sqlite.dal;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

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.tools.dispose.Disposable;

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

        // FIXME

        this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table = null;

        this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_table = null;

        this.STMT_UPDATE_TABLE_STATISTICS_table = "SELECT UpdateLayerStatistics({0})";

        this.type_boolean = "INTEGER";

        this.type_byte = "INTEGER";

        this.type_bytearray = "BLOB";

        this.type_geometry = "BLOB";

        this.type_char = "TEXT";

        this.type_date = "TEXT";

        this.type_double = "REAL"; 

        this.type_decimal_ps = "REAL";

        this.type_decimal_p = "REAL";

        this.type_float = "REAL";

        this.type_int = "INTEGER";

        this.type_long = "INTEGER";

        this.type_string = "TEXT";

        this.type_string_p = "TEXT";

        this.type_time = "TEXT";

        this.type_timestamp = "TEXT";

        this.type_version = "TEXT";

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

            StringBuilder builder = new StringBuilder();

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

                }

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

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

                            values.add(new java.sql.Date(value.getTime()));

                        }

                        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

    public TableNameBuilder createTableNameBuilder() {

        return new SQLiteTableNameBuilderBase();

    }

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

    }    

}