gvSIG data provider for Oracle

/**

 * gvSIG. Desktop Geographic Information System.

 *

 * Copyright (C) 2007-2013 gvSIG Association.

 *

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

 *

 * For any additional information, do not hesitate to contact us

 * at info AT gvsig.com, or visit our website www.gvsig.com.

 */

package org.gvsig.oracle.dal;

import java.text.MessageFormat;

import java.util.ArrayList;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

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

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

public class OracleSQLBuilder extends JDBCSQLBuilderBase {

    private final JDBCHelper helper;

    public OracleSQLBuilder(JDBCHelper helper) {

        super();

        this.helper = helper;

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

        config.set(SQLConfig.allowAutomaticValues, true);

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

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

        config.set(SQLConfig.constant_true, "TRUE");

        config.set(SQLConfig.constant_false, "FALSE");

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

        config.set(SQLConfig.quote_for_strings, "\'");

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

        config.set(SQLConfig.ST_SRID, "(({0}).SDO_SRID)");

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

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

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

        config.set(SQLConfig.ST_Contains, "(SDO_RELATE(({1}), ({0}), ''mask=CONTAINS+COVER'') = ''TRUE'')");

        config.remove_functionality(SQLConfig.ST_Crosses);

        config.set(SQLConfig.ST_Disjoint, "(SDO_RELATE(({1}), ({0}), ''mask=ANYINTERACT'') = ''FALSE'')");

        config.set(SQLConfig.ST_Equals, "(SDO_RELATE(({1}), ({0}), ''mask=EQUAL'') = ''TRUE'')");

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

        config.set(SQLConfig.ST_Overlaps, "SDO_OVERLAPS(({0}))");

        config.set(SQLConfig.ST_Touches, "(SDO_RELATE(({0}), ''mask=TOUCH'') = ''TRUE'')");

        config.set(SQLConfig.ST_Within, "(SDO_RELATE(({1}), ({0}), ''mask=INSIDE+COVERERBY'') = ''TRUE'')");

        config.set(SQLConfig.ST_Envelope, "SDO_MBR({0})");

        config.set(SQLConfig.ST_Intersects, "(SDO_RELATE({1}, {0}, ''mask=ANYINTERACT'') = ''TRUE'')");

        config.set(SQLConfig.ST_GeomFromText, "SDO_GEOMETRY({0},{1})");

        config.set(SQLConfig.ST_GeomFromWKB, "SDO_GEOMETRY(TO_BLOB({0}),{1})");

        config.set(SQLConfig.ST_GeomFromEWKB, "SDO_GEOMETRY(TO_BLOB({0}),{1})");

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

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

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

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

        config.set(SQLConfig.operator_not, "( NOT ({0}) )");

        config.set(SQLConfig.operator_AND,"( ({0}) AND ({1}) )");

        config.set(SQLConfig.operator_OR, "( ({0}) OR ({1}) )");

        config.set(SQLConfig.operator_EQ, "( ({0}) = ({1}) )");

        config.set(SQLConfig.operator_NE, "( ({0}) != ({1}) )");

        config.set(SQLConfig.operator_GT, "( ({0}) > ({1}) )");

        config.set(SQLConfig.operator_GE, "( ({0}) >= ({1}) )");

        config.set(SQLConfig.operator_LT, "( ({0}) < ({1}) )");

        config.set(SQLConfig.operator_LE, "( ({0}) <= ({1}) )");

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

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

        config.set(SQLConfig.operator_add, "{0} + {1}");

        config.set(SQLConfig.operator_subst, "{0} - {1}");

        config.set(SQLConfig.operator_mult, "{0} * {1}");

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

        config.set(SQLConfig.operator_concat, "{0} + {1}");

        config.set(SQLConfig.ST_ExtentAggregate, "SDO_AGGR_MBR({0})");

        config.set(SQLConfig.ST_UnionAggregate, "SDO_AGGR_UNION({0})");

        config.set(SQLConfig.count, "COUNT({0})");

        config.set(SQLConfig.count_distinct, "COUNT(DISTINCT {0})");

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

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

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

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

        config.set(SQLConfig.type_char, "CHARACTER(1)");

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

        config.set(SQLConfig.type_double, "BINARY_DOUBLE"); //float con 53 bits de mantisa, float(54)

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

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

        config.set(SQLConfig.type_bigdecimal, "NUMBER({0},{1})");

        config.set(SQLConfig.type_float, "BINARY_FLOAT"); //float con 24 bits de mantisa, float(24)

        config.set(SQLConfig.type_int, "NUMBER(10,0)");

        config.set(SQLConfig.type_long, "NUMBER(*,0)");

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

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

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

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

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

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

        config.set(SQLConfig.DELETE_FROM_table_WHERE_expresion, "DELETE FROM {0} WHERE {1}");

        config.set(SQLConfig.DELETE_FROM_table, "DELETE FROM {0}");

        config.set(SQLConfig.INSERT_INTO_table_columns_VALUES_values, "INSERT INTO {0} ( {1} ) VALUES ( {2} )");

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

        config.set(SQLConfig.DROP_TABLE_table, "DROP TABLE {0}");

        config.remove_functionality(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table);

        config.remove_functionality(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_table);

        config.set(SQLConfig.UPDATE_table_SET_columnsAndValues_WHERE_expresion, "UPDATE {0} SET {1} WHERE {2}");

        config.set(SQLConfig.UPDATE_table_SET_columnsAndValues, "UPDATE {0} SET {1}");

        config.set(SQLConfig.GRANT_privileges_ON_table_TO_role, "GRANT {0} ON {1} TO {2}");

        config.set(SQLConfig.CREATE_INDEX_name_ON_table_column, "CREATE INDEX {0} ON {1} ({2})");

        config.set(SQLConfig.CREATE_INDEX_name_ON_table_USING_GIST_column, "CREATE INDEX {0} ON {1}({2}) INDEXTYPE IS MDSYS.SPATIAL_INDEX");

        config.remove_functionality(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table);

        config.remove_functionality(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_table);

    }

    @Override

    public String default_schema() {

        if( this.helper.getConnectionParameters()!=null && 

            !StringUtils.isEmpty(this.helper.getConnectionParameters().getUser())

            ) {

            return this.helper.getConnectionParameters().getUser();

        }

        return config.getString(SQLConfig.default_schema);

    }

    public class OracleUpdateTableStatisticsBuilderBase extends UpdateTableStatisticsBuilderBase {

        @Override

        public List<String> toStrings() {

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

            if( config.has_functionality(SQLConfig.UPDATE_TABLE_STATISTICS_table) ) {

                String name = identifier(this.table.getName());

                if( table.has_schema() ) {

                    name = identifier(this.table.getSchema()) + "." + name;

                }

                String sql = MessageFormat.format(

                    config.getString(SQLConfig.UPDATE_TABLE_STATISTICS_table),

                    name

                );

                if( !StringUtils.isEmpty(sql) ) {

                    sqls.add(sql);

                }

            }

            return sqls;

        }

    }

    protected class OracleCreateTableBuilder 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( column.isGeometry() ) {

                    continue;

                }

                if( first ) {

                    first = false;

                } else {

                    builder.append(", ");

                }

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

                builder.append(" ");

                if( column.isAutomatic() ) {

                    builder.append(" SERIAL");

                } else {

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

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

                    }

                }

                if( column.isPrimaryKey() ) {

                    builder.append(" PRIMARY KEY");

                }

            }

            builder.append(" )");

            sqls.add(builder.toString());

            String AddGeometryColumn1 = "ALTER TABLE {0} ADD {1} MDSYS.SDO_GEOMETRY";

//            String AddGeometryColumn2 = "INSERT INTO USER_SDO_GEOM_METADATA  VALUES ('{0}', '{1}', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X', 0, 20, 0.005),    MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, 0.005)), the_srid )";

            for( ColumnDescriptorBuilderBase column : columns ) {

                if( column.isGeometry() ) {

                    String sql = MessageFormat.format(

                        AddGeometryColumn1,

                        this.table.toString(),

                        column.getName()

                    );

                    sqls.add(sql);

                }

            }

            for( ColumnDescriptorBuilderBase column : columns ) {

                if( column.isIndexed() ) {

                    String sql;

                    String name = "idx_" + this.table().getName() + column.getName();

                    if( column.isGeometry() ) {

//                        sql = MessageFormat.format(

//                            config.getString(SQLConfig.CREATE_INDEX_name_ON_table_USING_GIST_column),

//                            name,

//                            this.table().toString(),

//                            column.getName()

//                        );

//                        sqls.add(sql);

                    } else {

                        sql = MessageFormat.format(

                            config.getString(SQLConfig.CREATE_INDEX_name_ON_table_column),

                            name,

                            this.table().toString(),

                            column.getName()

                        );

                        sqls.add(sql);

                    }

                }

            }

            return sqls;

        }

    }

    public class OracleSelectBuilderBase extends SelectBuilderBase {

        protected StringBuilder appendMainClause(StringBuilder builder) {

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

                }

            }

            return builder;

        }

        @Override

        public String toString() {

            StringBuilder builder = new StringBuilder();

            if( !isValid(builder) ) {

                throw new IllegalStateException(builder.toString());

            }

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

                    /**

                     *  Using ROWNUM for limit & offset, compatible with any Oracle version

                                 *        This requires wrapping the original query as shown in the example:

                         *     select * from (

                         *              select a.*, ROWNUM rnum from (

                                 *                  <select statement with order by clause>

                                 *              ) a where ROWNUM <= MAX_ROW

                                 *     ) where rnum >= MIN_ROW

                                 *  because ROWNUM is considered before applying order by and group by

                     */

                builder.append("SELECT * FROM ( ");

                if (this.has_offset()) {

                        builder.append("SELECT a.*, ROWNUM rnum FROM (");

                }

                    this.appendMainClause(builder);

                    builder.append(") a");

                    if (this.has_limit()) {

                            builder.append(" WHERE ROWNUM <= ");

                            builder.append(this.limit);

                    }

                    if (this.has_offset()) {

                            builder.append(" )");

                            builder.append(" WHERE rnum >= ");

                        builder.append(this.offset);

                    }

            }

            else {

                    this.appendMainClause(builder);

            }

            return builder.toString();

        }

    }

    protected TableNameBuilder createTableNameBuilder() {

        return new OracleTableNameBuilder();

    }

    @Override

    public String bytearray(byte[] data) {

            return new StringBuilder("HEXTORAW('").append(bytearray_hex(data)).append("')").toString();

    }

    @Override

    protected CreateTableBuilder createCreateTableBuilder() {

        return new OracleCreateTableBuilder();

    }

    @Override

    protected SelectBuilder createSelectBuilder() {

        return new OracleSelectBuilderBase();

    }

    @Override

    protected UpdateTableStatisticsBuilder createUpdateTableStatisticsBuilder() {

        return new OracleUpdateTableStatisticsBuilderBase();

    }

    protected class OracleTableNameBuilder extends TableNameBuilderBase {

        @Override

        public String toString() {

            if( this.has_schema()) {

                return identifier(this.schemaName) + "." + 

                       identifier(this.tableName);

            }

            return identifier(this.tableName);

        }

    }

}