gvSIG data provider for MySQL

package org.gvsig.mysql.dal;

import java.util.ArrayList;

import java.util.List;

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

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

public class MySQLSQLBuilder extends JDBCSQLBuilderBase {

    public MySQLSQLBuilder(MySQLHelper helper) {

        super(helper);

        //

        // MySQL 5.5, SQL functions reference list

        //

        // https://dev.mysql.com/doc/refman/5.5/en/sql-syntax.html

        // https://dev.mysql.com/doc/refman/5.5/en/using-spatial-data.html

        //

        // https://dev.mysql.com/doc/refman/5.7/en/spatial-function-reference.html

        //

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

        config.set(SQLConfig.quote_for_identifiers, "`");

        config.set(SQLConfig.allowAutomaticValues, true);

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

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

        config.remove_functionality(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table);

        config.remove_functionality(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_table);

        // MySQL no tiene funciones de agregado sobre geometrias.

        // Esto no es nada eficiente y fallara con tablas grandes.

        // https://forums.mysql.com/read.php?23,249284,249284#msg-249284

        config.set(SQLConfig.ST_ExtentAggregate, "ST_AsWKB(ST_Envelope(ST_GeomCollFromText(GROUP_CONCAT(ST_AsWKT({0})))))");

        config.set(SQLConfig.ST_UnionAggregate, "null");

        config.set(SQLConfig.UPDATE_TABLE_STATISTICS_table,"ANALYZE TABLE {0}");

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

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

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

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

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

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

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

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

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

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

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

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

        config.set(SQLConfig.type_numeric_p, "DECIMAL({0})");

        config.set(SQLConfig.type_numeric_ps, "DECIMAL({0},{1})");

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

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

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

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

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

        config.set(SQLConfig.type_string_p, "VARCHAR({0})");

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

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

        config.set(SQLConfig.type_version, "VARCHAR(45)");

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

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

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

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

    }

    public MySQLHelper getHelper() {

        return (MySQLHelper) helper;

    }

    public class MySQLTableNameBuilderBase extends TableNameBuilderBase {

        @Override

        public boolean has_schema() {

            return false;

        }

    }

    protected class MySQLCreateTableBuilder extends CreateTableBuilderBase {

        @Override

        public List<String> toStrings() {

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

            StringBuilder builder = new StringBuilder();

            builder.append("CREATE TABLE ");

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

            builder.append(" (");

            boolean first = true;

            for (ColumnDescriptorBuilder column : columns) {

                if (first) {

                    first = false;

                } else {

                    builder.append(", ");

                }

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

                builder.append(" ");

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

                if (column.isPrimaryKey()) {

                    builder.append(" PRIMARY KEY");

                }

                if( column.isAutomatic() ) {

                    builder.append(" AUTO_INCREMENT");

                }

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

                    if (column.allowNulls()) {

                        builder.append(" DEFAULT NULL");

                    }

                } else {

                    builder.append(" DEFAULT '");

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

                    builder.append("'");

                }

                if (column.allowNulls()) {

                    builder.append(" NULL");

                } else {

                    builder.append(" NOT NULL");

                }

            }

            builder.append(" )");

            sqls.add(builder.toString());

            return sqls;

        }

    }

    public class MySQLSelectBuilderBase 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() {

            // MySQL requiere que si se especifica OFFSET deba especificarse

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

            // queramos un OFFSET sin especificar un LIMIT.

            StringBuilder builder = new StringBuilder();

            if( !isValid(builder) ) {

                throw new IllegalStateException(builder.toString());

            }

            builder.append("SELECT ");

            if( this.distinct ) {

                builder.append("DISTINCT ");

            }

            boolean first = true;

            for (SelectColumnBuilder column : columns) {

                if (first) {

                    first = false;

                } else {

                    builder.append(", ");

                }

                builder.append(column.toString());

            }

            if ( this.has_from() ) {

                builder.append(" FROM ");

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

            }

            if ( this.has_where() ) {

                builder.append(" WHERE ");

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

            }

            if( this.has_order_by() ) {

                builder.append(" ORDER BY ");

                first = true;

                for (OrderByBuilder item : this.order_by) {

                    if (first) {

                        first = false;

                    } else {

                        builder.append(", ");

                    }

                    builder.append(item.toString());                    

                }   

            }

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

                builder.append(" LIMIT ");

                builder.append(this.limit);

                builder.append(" OFFSET ");

                builder.append(this.offset);

            } else if ( this.has_limit()) {

                builder.append(" LIMIT ");

                builder.append(this.limit);

            } else if ( this.has_offset() ) {

                // See https://dev.mysql.com/doc/refman/5.7/en/select.html#idm140126933499488

                builder.append(" LIMIT 18446744073709551615 OFFSET ");

                builder.append(this.offset);    

            }

            return builder.toString();

        }

    }

    @Override

    public String bytearray(byte[] data) {

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

    protected TableNameBuilder createTableNameBuilder() {

        return new MySQLTableNameBuilderBase();

    }

    @Override

    protected CreateTableBuilder createCreateTableBuilder() {

        return new MySQLCreateTableBuilder();

    }

    @Override

    protected SelectBuilder createSelectBuilder() {

        return new MySQLSelectBuilderBase();

    }

}