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.io.ByteArrayInputStream;

import java.io.IOException;

import java.io.InputStream;

import java.io.OutputStream;

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.util.ArrayList;

import java.util.List;

import java.util.Objects;

import oracle.sql.BLOB;

import org.apache.commons.dbcp.DelegatingConnection;

import org.apache.commons.lang3.BooleanUtils;

import org.apache.commons.lang3.StringUtils;

import org.apache.commons.lang3.tuple.Pair;

import org.gvsig.expressionevaluator.DelegatedGeometryExpressionBuilder;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import static org.gvsig.expressionevaluator.ExpressionBuilder.PARAMETER_TYPE_CONSTANT;

import static org.gvsig.expressionevaluator.ExpressionBuilder.PARAMETER_TYPE_VARIABLE;

import static org.gvsig.expressionevaluator.ExpressionBuilder.VALUE_NULL;

import org.gvsig.expressionevaluator.ExpressionUtils;

import org.gvsig.expressionevaluator.Formatter;

import org.gvsig.expressionevaluator.GeometryExpressionBuilder;

import org.gvsig.expressionevaluator.GeometryExpressionBuilderHelper.GeometrySupportType;

import static org.gvsig.expressionevaluator.GeometryExpressionBuilderHelper.GeometrySupportType.WKT;

import org.gvsig.fmap.dal.DataTypes;

import org.gvsig.fmap.dal.SQLBuilder;

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.feature.spi.SQLBuilderBase;

import org.gvsig.fmap.dal.store.jdbc.JDBCConnectionParameters;

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

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

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.GeometryUtils;

import org.gvsig.fmap.geom.primitive.Envelope;

import org.gvsig.oracle.dal.expressionbuilderformatter.OracleFormatter;

import static org.gvsig.oracle.dal.expressionbuilderformatter.OracleGeometryParameter.ORACLE_GEOMETRY_PARAMETER;

import org.gvsig.tools.ToolsLocator;

import org.gvsig.tools.dataTypes.Coercion;

import org.gvsig.tools.dataTypes.DataType;

import org.gvsig.tools.dataTypes.DataTypeUtils;

import org.gvsig.tools.dataTypes.DataTypesManager;

import org.gvsig.tools.dispose.Disposable;

public class OracleSQLBuilder extends JDBCSQLBuilderBase {

    protected static final String ADD_SERIAL_COLUMN_SEQUENCE_QUERY = "CREATE SEQUENCE \"{0}\"";

    protected static final String ADD_SERIAL_COLUMN_TRIGGER_QUERY = "CREATE OR REPLACE TRIGGER \"{0}\" BEFORE INSERT ON {1} FOR EACH ROW "

            + "BEGIN SELECT \"{3}\".NEXTVAL INTO :new.\"{2}\" FROM dual; END;";

    protected static final String DELETE_FROM_OGIS_GEOMETRY_COLUMNS_QUERY = "DELETE FROM MDSYS.OGIS_GEOMETRY_COLUMNS "

            + "WHERE F_TABLE_SCHEMA = ''{0}'' AND F_TABLE_NAME = ''{1}'' AND F_GEOMETRY_COLUMN = ''{2}''";

    protected static final String INSERT_OGIS_GEOMETRY_COLUMNS_QUERY = "INSERT INTO MDSYS.OGIS_GEOMETRY_COLUMNS "

            + "(F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN, GEOMETRY_TYPE) VALUES "

            + "(''{0}'', ''{1}'', ''{2}'', {3})";

    private final String quote_for_identifiers;

    private final String quote_for_strings;

    public static final String ST_GEOMFROMTEXT = "SDO_GEOMETRY(({0}), ({1}))";

    public static final String ST_GEOMFROMWKB = "SDO_GEOMETRY(({0}), ({1}))";

    public static final String ST_GEOMFROMEWKB = "SDO_GEOMETRY(({0}), ({1}))";

    public static final String FORMAT_OPERATOR_NOTISNULL = "( (({0}) IS NOT NULL) )";

    public static final String FORMAT_OPERATOR_AND = "( ({0}) AND ({1}) )";

    public static final String FORMAT_OPERATOR_OR = "( ({0}) OR ({1}) )";

    public static final String FORMAT_OPERATOR_NE = "( ({0}) != ({1}) )";

    public static final String FORMAT_OPERATOR_ILIKE = "( LOWER({0}) LIKE LOWER({1}) )";

    public static final String FORMAT_OPERATOR_CONCAT = "{0} + {1}";

    public static final int BOOLEAN_STRING_DEFAULT_LENGTH = 1;

    private static final Geometry EMPTY_POINT = GeometryUtils.createPoint(0, 0);

    protected Formatter formatter = null;

    public OracleSQLBuilder(JDBCHelper helper) {

        super(helper);

        this.defaultSchema = "";

        this.allowAutomaticValues = true;

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

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

        this.constant_true = "TRUE";

        this.constant_false = "FALSE";

        this.quote_for_identifiers = "\"";

        this.quote_for_strings = "\'";

        this.type_boolean = "CHAR(1)";

        this.type_byte = "NUMBER(" + (DataType.BYTE_MAX_PRECISION) + ",0)";

        this.type_bytearray = "BLOB";

        this.type_geometry = "SDO_GEOMETRY";

        this.type_char = "CHAR(1)";

        this.type_date = "DATE";

        this.type_double = "BINARY_DOUBLE"; //float con 53 bits de mantisa = float(54)

        this.type_decimal_p = "NUMBER({0})";

        this.type_decimal_ps = "NUMBER({0},{1})";

//        this.type_bigdecimal = "NUMBER({0},{1})";

        this.type_float = "BINARY_FLOAT"; //float con 24 bits de mantisa = float(24)

        this.type_int = "NUMBER(" + (DataType.INT_MAX_PRECISION - 1) + ",0)";

        this.type_long = "NUMBER(" + (DataType.LONG_MAX_PRECISION - 1) + ",0)";

        this.type_string = "NCLOB";

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

        this.type_time = "TIMESTAMP";

        this.type_timestamp = "TIMESTAMP";

        this.type_version = "VARCHAR2(30)";

        this.type_URI = "NVARCHAR2(2048)";

        this.type_URL = "NVARCHAR2(2048)";

        this.type_FILE = "NVARCHAR2(2048)";

        this.type_FOLDER = "NVARCHAR2(2048)";

        STMT_DELETE_FROM_table_WHERE_expresion = "DELETE FROM {0} WHERE {1}";

        STMT_DELETE_FROM_table = "DELETE FROM {0}";

        STMT_INSERT_INTO_table_columns_VALUES_values = "INSERT INTO {0} ( {1} ) VALUES ( {2} )";

        STMT_UPDATE_TABLE_STATISTICS_table = "VACUUM ANALYZE {0}"; // FIXME: ALTER INDEX [schema.]index REBUILD  INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS (''LAYER_GTYPE={3}'';

//        config.remove_functionality(SQLConfig.UPDATE_TABLE_STATISTICS_table; // FIXME

        STMT_DROP_TABLE_table = "DROP TABLE {0}";

        STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_table = null;

        STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table = null;

        STMT_UPDATE_table_SET_columnsAndValues_WHERE_expresion = "UPDATE {0} SET {1} WHERE {2}";

        STMT_UPDATE_table_SET_columnsAndValues = "UPDATE {0} SET {1}";

        STMT_GRANT_privileges_ON_table_TO_role = "GRANT {0} ON {1} TO {2}";

    }

    public OracleHelper getHelper() {

        return (OracleHelper) super.getHelper();

    }

    @Override

    public Formatter formatter() {

        if (this.formatter == null) {

            this.formatter = new OracleFormatter(this);

        }

        return this.formatter;

    }

    private class OracleGeometryExpressionBuilder extends DelegatedGeometryExpressionBuilder {

        public OracleGeometryExpressionBuilder(GeometryExpressionBuilder expressionBuilder) {

            super(expressionBuilder);

        }

        @Override

        public Formatter<Value> formatter() {

            return OracleSQLBuilder.this.formatter();

        }

    }

    private OracleGeometryExpressionBuilder oracleExpressionBuilder = null;

    @Override

    public GeometryExpressionBuilder expression() {

        if (this.oracleExpressionBuilder == null) {

            this.oracleExpressionBuilder = new OracleGeometryExpressionBuilder(super.expression());

        }

        return this.oracleExpressionBuilder;

    }

    @Override

    public String default_schema() {

        JDBCConnectionParameters params = this.helper.getConnectionParameters();

        if (params != null) {

            if (StringUtils.isBlank(params.getSchema())) {

                if ((StringUtils.isNotBlank(params.getUser()))) {

                    return params.getUser();

                }

            } else {

                return params.getSchema();

            }

        }

        return this.defaultSchema;

    }

    public class OracleUpdateTableStatisticsBuilderBase extends UpdateTableStatisticsBuilderBase {

        @Override

        public List<String> toStrings() {

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

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

            if (table.has_schema()) {

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

            }

            String sql = MessageFormat.format(

                    "ANALYZE TABLE {0} COMPUTE STATISTICS",

                    name

            );

            if (!StringUtils.isEmpty(sql)) {

                sqls.add(sql);

            }

            return sqls;

        }

    }

    public String getSerialSequenceName(String tableName, String columnName) {

        return ("GVSEQ_" + tableName + "_" + columnName).toUpperCase();

    }

    public String getSerialTriggerName(String tableName, String columnName) {

        return ("GVSER_" + tableName + "_" + columnName).toUpperCase();

    }

    protected class OracleCreateTableBuilder extends CreateTableBuilderBase {

        @Override

        public List<String> toStrings(Formatter formatter) {

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

            StringBuilder builder = new StringBuilder();

            builder.append("CREATE TABLE ");

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

            builder.append(" (");

            boolean first = true;

            ArrayList<String> pks = new ArrayList<>();

            boolean automaticPrimaryKey = false;

            for (ColumnDescriptor column : columns) {

                if (first) {

                    first = false;

                } else {

                    builder.append(", ");

                }

                if (column.isGeometry()) {

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

                    builder.append(" ");

                    builder.append(" SDO_GEOMETRY");

                    if (!column.allowNulls()) {

                        builder.append(" NOT NULL");

                    }

                } else {

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

                    if (column.isAutomatic()) {

                        // if the pk is automatic, ignore any other PK

                        automaticPrimaryKey = true;

                        pks.clear();

                        pks.add(column.getName());

                    } else if (!automaticPrimaryKey) {

                        pks.add(column.getName());

                    }

                } else {

                    if (column.isAutomatic()) {

//                        builder.append(" AUTO_INCREMENT");

                        builder.append(" GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1)");

                    }

                    if (column.getDefaultValue() == null

                            || ExpressionUtils.isDynamicText(Objects.toString(column.getDefaultValue(), null))) {

                        if (column.allowNulls()) {

                            builder.append(" DEFAULT NULL");

                        }

                    } else {

                        switch (column.getType()) {

                            case DataTypes.TIMESTAMP:

                                builder.append(" DEFAULT ( TIMESTAMP '");

                                Timestamp dtimestamp = (Timestamp) DataTypeUtils.toTimestamp(column.getDefaultValue());

                                builder.append(MessageFormat.format("{0,date,yyyy-MM-dd HH:mm:ss.S}", dtimestamp));

                                builder.append("' )");

                                break;

                            case DataTypes.TIME:

                                builder.append(" DEFAULT ( TIME '");

                                Time dtime = (Time) DataTypeUtils.toTime(column.getDefaultValue());

                                builder.append(MessageFormat.format("{0,date,HH:mm:ss}", dtime));

                                builder.append("' )");

                                break;

                            case DataTypes.DATE:

                                builder.append(" DEFAULT ( DATE '");

                                java.sql.Date ddate = (java.sql.Date) DataTypeUtils.toDate(column.getDefaultValue());

                                builder.append(MessageFormat.format("{0,date,yyyy-MM-dd}", ddate));

                                builder.append("' )");

                                break;

                            default:

                                builder.append(" DEFAULT '");

                                builder.append(Objects.toString(column.getDefaultValue(), ""));

                                builder.append("'");

                        }

                    }

                }

            }

            builder.append(" )");

            sqls.add(builder.toString());

            if (pks.size() > 0) {

                builder = new StringBuilder("ALTER TABLE ");

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

                builder.append(" ADD PRIMARY KEY (");

                for (int i = 0; i < pks.size(); i++) {

                    if (i != 0) {

                        builder.append(", ");

                    }

                    builder.append(as_identifier(pks.get(i)));

                }

                builder.append(")");

                sqls.add(builder.toString());

            }

            for (ColumnDescriptor column : columns) {

                if (column.isAutomatic()) {

                    String sequenceName = getSerialSequenceName(this.table.getName(), column.getName());

                    String sql = MessageFormat.format(

                            ADD_SERIAL_COLUMN_SEQUENCE_QUERY,

                            sequenceName

                    );

                    sqls.add(sql);

                    String autoTriggerName = getSerialTriggerName(this.table.getName(), column.getName());

                    sql = MessageFormat.format(

                            ADD_SERIAL_COLUMN_TRIGGER_QUERY,

                            autoTriggerName,

                            this.table().toString(),

                            column.getName(),

                            sequenceName

                    );

                    sqls.add(sql);

                }

            }

            for (ColumnDescriptor column : columns) {

                if (column.isGeometry()) {

                    /**

                     * Inserting the geomtype and column on

                     * OGIS_GEOMETRY_COLUMNS.

                     *

                     * It is useful to retrieve the geometry type when the

                     * spatial index can't be defined (for instance users

                     * creating tables on a different schema).

                     *

                     * Note: We don't insert the SRID because it is unusable as

                     * defined on OGIS_GEOMETRY_COLUMNS, as it must reference an

                     * existing SRID on OGIS_SPATIAL_REFERENCE_SYSTEM.

                     * OGIS_SPATIAL_REFERENCE_SYSTEM is empty and it has a

                     * trigger that duplicates on SDO_COORD_REF_SYS any SRID

                     * inserted on OGIS_SPATIAL_REFERENCE_SYSTEM.

                     * SDO_COORD_REF_SYS is not empty and we don't want to

                     * duplicate the SRID definitions!!

                     */

                    int gvsigType = column.getGeometryType();

                    int gvsigSubtype = column.getGeometrySubtype();

                    String sql = MessageFormat.format( // delete before inserting to avoid creating duplicates

                            DELETE_FROM_OGIS_GEOMETRY_COLUMNS_QUERY,

                            this.table().getSchema(),

                            this.table().getName().toUpperCase(),

                            column.getName()

                    );

                    sqls.add(sql);

                    sql = MessageFormat.format(

                            INSERT_OGIS_GEOMETRY_COLUMNS_QUERY,

                            this.table().getSchema(),

                            this.table().getName().toUpperCase(),

                            column.getName(),

                            Integer.toString(GeometryTypeUtils.toSFSGeometryTypeCode(gvsigType, gvsigSubtype))

                    );

                    sqls.add(sql);

                    Envelope tablebbox = column.getTableBBox();

                    if (tablebbox != null) {

                        sql = SpatialIndexUtils.getSQLDeleteUserMetadata(

                                this.table().getSchema(),

                                this.table().getName(),

                                column.getName()

                        );

                        sqls.add(sql);

                        sql = SpatialIndexUtils.getSQLInsertUserMetadata(

                                this.table().getName(),

                                tablebbox,

                                column.getName(),

                                (int) column.getGeometrySRSId()

                        );

                        sqls.add(sql);

                        sql = SpatialIndexUtils.getSQLCreateSpatialIndex(

                                this.table().getName(),

                                column.getName(),

                                gvsigType,

                                gvsigSubtype

                        );

                        sqls.add(sql);

                    }

                }

            }

            return sqls;

        }

    }

    public class OracleSelectBuilder extends SelectBuilderBase {

        @Override

        public String toString(Formatter<ExpressionBuilder.Value> formatter) {

            StringBuilder builder = new StringBuilder();

            if (!isValid(builder)) {

                throw new IllegalStateException(builder.toString());

            }

            boolean involveWithLimitOrOffset = ((this.has_offset() || this.has_limit())

                    && (this.has_aggregate_functions() || this.has_group_by() || this.has_order_by()));

            if (involveWithLimitOrOffset) {

                /**

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

                }

            }

            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 (!involveWithLimitOrOffset) {

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

                    if (this.has_where()) {

                        builder.append(" AND ");

                    } else {

                        builder.append(" WHERE ");

                    }

                }

                if (this.has_limit()) {

                    builder.append(" ROWNUM <= ");

                    if (this.has_offset()) {

                        builder.append(this.offset + this.limit);

                    } else {

                        builder.append(this.limit);

                    }

                }

                if (this.has_offset()) {

                    builder.append(" AND rnum > ");

                    builder.append(this.offset);

                }

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

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

                    }

                }

                builder.append(") a");

                if (this.has_limit()) {

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

                    if (this.has_offset()) {

                        builder.append(this.offset + this.limit);

                    } else {

                        builder.append(this.limit);

                    }

                }

                if (this.has_offset()) {

                    builder.append(" )");

                    builder.append(" WHERE rnum > ");

                    builder.append(this.offset);

                }

            }

            return builder.toString();

        }

    }

    @Override

    public TableNameBuilder createTableNameBuilder() {

        return new OracleTableNameBuilder();

    }

    @Override

    protected CreateTableBuilder createCreateTableBuilder() {

        return new OracleCreateTableBuilder();

    }

    @Override

    public SelectBuilder createSelectBuilder() {

        return new OracleSelectBuilder();

    }

    @Override

    protected FromBuilder createFromBuilder() {

        return new OracleFromBuilder();

    }

    @Override

    protected UpdateTableStatisticsBuilder createUpdateTableStatisticsBuilder() {

        return new OracleUpdateTableStatisticsBuilderBase();

    }

    protected class OracleTableNameBuilder extends TableNameBuilderBase {

        @Override

        public boolean has_database() {

            return false;

        }

        @Override

        public String toString() {

            return this.toString(formatter());

        }

        protected String databaseName2provider() {

            return null;

        }

        protected String schemaName2provider() {

            return this.schemaName;

        }

        protected String tableName2provider() {

            boolean forceUpperCase = true;

            OracleConnectionParameters params = getHelper().getConnectionParameters();

            if (params != null) {

                forceUpperCase = params.getForceUppercaseInTableName();

            }

            if( forceUpperCase ) {

                return StringUtils.upperCase(this.tableName);

            }

            return this.tableName;

        }

        @Override

        public String toString(Formatter<ExpressionBuilder.Value> formatter) {

            if (formatter != null && formatter.canApply(this)) {

                return formatter.format(this);

            }

            if (this.has_schema()) {

                return as_identifier(this.schemaName2provider()) + "."

                        + as_identifier(this.tableName2provider());

            }

            OracleConnectionParameters params = getHelper().getConnectionParameters();

            if (params != null) {

                if ((StringUtils.isNotBlank(params.getUser()))) {

                    return as_identifier(params.getUser()) + "."

                            + as_identifier(this.tableName2provider());

                }

            }

            return as_identifier(this.tableName2provider());

        }

    }

    public class OracleFromBuilder extends FromBuilderBase {

        @Override

        public String toString(Formatter<ExpressionBuilder.Value> formatter) {

            if (formatter != null && formatter.canApply(this)) {

                return formatter.format(this);

            }

            if (!StringUtils.isEmpty(passthrough)) {

                return passthrough;

            }

            if (!StringUtils.isEmpty(subquery)) {

                return "( " + this.subquery + ") " + quote_for_identifiers() + "_subquery_alias_" + quote_for_identifiers() + " ";

            }

            if (this.joins == null || this.joins.isEmpty()) {

                return this.tableName.toString(formatter);

            }

            StringBuilder builder = new StringBuilder();

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

            for (JoinBuilder join : this.joins) {

                builder.append(" ");

                builder.append(join.toString(formatter));

            }

            return builder.toString();

        }

    }

    public class DisposableBlobs implements Disposable {

        private final ArrayList<BLOB> blobList = new ArrayList<>();

        public void add(BLOB blob) {

            blobList.add(blob);

        }

        @Override

        public void dispose() {

            blobList.forEach((blob) -> {

                try {

                    blob.freeTemporary();

                } catch (SQLException ex) {

                    LOGGER.warn("Can't dispose blob " + blob.toString(), ex);

                }

            });

        }

    }

    protected void setBlob(PreparedStatement st, int columnIndex,

            byte[] bytes, DisposableBlobs blobList) throws SQLException, IOException {

        Connection conn = st.getConnection();

        if (conn instanceof DelegatingConnection) {

            conn = ((DelegatingConnection) conn).getInnermostDelegate();

        }

        BLOB blob = BLOB.createTemporary(conn, false, BLOB.DURATION_SESSION);

        blobList.add(blob);

        InputStream inputStream;

        try (OutputStream outputStream = blob.setBinaryStream(0L)) {

            inputStream = new ByteArrayInputStream(bytes);

            byte[] buffer = new byte[blob.getBufferSize()];

            int byteread;

            while ((byteread = inputStream.read(buffer)) != -1) {

                outputStream.write(buffer, 0, byteread);

            }

        }

        inputStream.close();

        st.setBlob(columnIndex, blob);

    }

    @Override

    public Disposable setStatementParameters(

            PreparedStatement st,

            List values,

            List<Integer> types, //Can be null

            GeometrySupportType geometrySupportType) throws SQLException {

        DisposableBlobs blobList = new DisposableBlobs();

        if (values == null) {

            return blobList;

        }

        try {

            byte[] bytes;

            int columnIndex = 1;

            for (Object value : values) {

                if (value instanceof Geometry) {

                    switch (geometrySupportType) {

                        case WKT:

                            value = ((Geometry) value).convertToWKT();

                            st.setObject(columnIndex, value);

                            break;

                        case NATIVE:

                        case WKB:

                            bytes = ((Geometry) value).convertToWKB();

                            setBlob(st, columnIndex, bytes, blobList);

                            break;

                        case EWKB:

                            bytes = ((Geometry) value).convertToEWKB();

                            setBlob(st, columnIndex, bytes, blobList);

                            break;

                    }

                } else {

                    if (types == null) {

                        st.setObject(columnIndex, value);

                    } else {

                        this.setStatementValue(st, columnIndex, types.get(columnIndex - 1), value);

                    }

                }

                columnIndex++;

            }

        } catch (Exception ex) {

            throw new SQLException("Can't set values for the prepared statement.", ex);

        }

        return blobList;

    }

    @Override

    public List<Object> getParameters(FeatureProvider feature) {

        return getParameters(feature, null);

    }

    @Override

    public List<Object> getParameters(FeatureProvider feature, List<Integer> types) {

        try {

            FeatureType type = feature.getType();

            FeatureType providerType = this.getHelper().getProviderFeatureType();

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

            Object value;

            DataTypesManager dataTypesManager = ToolsLocator.getDataTypesManager();

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

                if (parameter.is_constant()) {

                    value = parameter.value();

                    values.add(value);

                    if (types != null) {

                        if (value == null) {

                            if (values.get(values.size() - 2) == EMPTY_POINT) {

                                types.add(DataTypes.INTEGER);

                            } else {

                                types.add(DataTypes.OBJECT);

                            }

                        } else {

                            DataType dataType = dataTypesManager.getDataType(value.getClass());

                            types.add(dataType.getType());

                        }

                    }

                } else {

                    String name = parameter.name();

                    value = feature.get(name);

                    FeatureAttributeDescriptor attrDesc = type.getAttributeDescriptor(name);

                    Coercion convert = null;

                    if (providerType != null) {

                        FeatureAttributeDescriptor attrDescProvider = providerType.getAttributeDescriptor(name);

                        if (attrDescProvider != null && attrDescProvider.getType() != attrDesc.getType()) {

                            convert = attrDescProvider.getDataType().getCoercion();

                        }

                    }

                    switch (attrDesc.getType()) {

                        case org.gvsig.fmap.dal.DataTypes.GEOMETRY:

                            Geometry geom = this.forceGeometryType(

                                    attrDesc.getGeomType(),

                                    (Geometry) value

                            );

                            if (BooleanUtils.isTrue((Boolean) parameter.getProperty(ORACLE_GEOMETRY_PARAMETER))) {

                                //Es muy guarro pero es para evitar un error de NullPointerException en Oracle

                                if (geom == null) {

                                    //NVL2((NULL),SDO_GEOMETRY((EMPTY_POINT), (?)),NULL)

                                    values.add(null);

                                    values.add(EMPTY_POINT);

                                } else {

                                    //NVL2((1),SDO_GEOMETRY((WKB), (?)),NULL)

                                    values.add(1);

                                    values.add(geom);

                                }

                                if (types != null) {

                                    types.add(DataTypes.INTEGER);

                                    types.add(DataTypes.GEOMETRY);

                                }

                            }

                            break;

                        case org.gvsig.fmap.dal.DataTypes.BOOLEAN:

                            if (value == null) {

                                values.add(null);

                            } else {

                                if (convert == null) {

                                    values.add(value);

                                } else {

                                    String s = (String) convert.coerce(value);

                                    values.add(s == null ? null : s.substring(0, 1));

                                }

                            }

                            if (types != null) {

                                types.add(DataTypes.STRING);

                            }

                            break;

                        default:

                            if (convert == null) {

                                values.add(value);

                            } else {

                                values.add(convert.coerce(value));

                            }

                            if (types != null) {

                                int t = attrDesc.getDataType().getType();

                                types.add(t);

                                if (t == DataTypes.INTEGER) {

                                    Object x = values.get(values.size() - 1);

                                    if (x != null && !(x instanceof Integer)) {

                                        LOGGER.debug("Esto est? mal");

                                    }

                                }

                            }

                            break;

                    }

                }

            }

            return values;

        } catch (Exception ex) {

            String f = "unknow";

            try {

                f = feature.toString();

            } catch (Exception ex2) {

                // Do nothing

            }

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

        }

    }

    @Override

    public List<String> parameters_names() {

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

        for (ExpressionBuilder.Parameter param : parameters()) {

            String s;

            switch (param.type()) {

                case PARAMETER_TYPE_CONSTANT:

                    Object theValue = param.value();

                    if (theValue == null) {

                        s = "null";

                    } else if (theValue instanceof String) {

                        s = "'" + (String) theValue + "'";

                    } else {

                        s = theValue.toString();

                    }

                    break;

                case PARAMETER_TYPE_VARIABLE:

                default:

                    s = "\"" + param.name() + "\"";

            }

            params.add(s);

            if (BooleanUtils.isTrue((Boolean) param.getProperty(ORACLE_GEOMETRY_PARAMETER))) {

                params.add(s);

            }

        }

        return params;

    }

    public String blob(byte[] data) {

        StringBuilder builder = new StringBuilder();

        builder.append("TO_BLOB(HEXTORAW('");

        for (byte abyte : data) {

            int v = abyte & 0xff;

            builder.append(String.format("%02x", v));

        }

        builder.append("'))");

        return builder.toString();

    }

    @Override

    public String as_clob(String s) {

        int chunkSize = 1024;

        StringBuilder builder = new StringBuilder();

        builder.append("(TO_CLOB('");

        for (int i = 0; i < s.length(); i += chunkSize) {

            String chunk = s.substring(i, Math.min(s.length(), i + chunkSize));

            if( i>0 ) {

                builder.append("') || TO_CLOB('");

            }

            builder.append(StringUtils.replace(chunk, "'", "''"));

        }

        builder.append("'))");

        return builder.toString();

    }

    public class OracleCreateIndexBuilder extends CreateIndexBuilderBase {

        @Override

        public List<String> toStrings() {

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

            if (!this.isSpatial) {

                // Spatial index is created in a different way because it requires some queries based on the results of other queries

                // See OracleUpdateSpatialIndexAndMetadata class

                StringBuilder builder = new StringBuilder();

                builder.append("CREATE ");

                if (this.isUnique) {

                    builder.append("UNIQUE ");

                }

                builder.append("INDEX ");

//                if (this.ifNotExist) {

//                    builder.append("IF NOT EXISTS ");

//                }

                builder.append(as_identifier(this.indexName));

                builder.append(" ON ");

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

                builder.append(" ( ");

                boolean is_first_column = true;

                for (String column : this.columns) {

                    if (is_first_column) {

                        is_first_column = false;

                    } else {

                        builder.append(", ");

                    }

                    builder.append(as_identifier(column));

                }

                builder.append(" )");

                sqls.add(builder.toString());

            }

            return sqls;

        }

    }

    public class OracleAlterTableBuilder extends AlterTableBuilderBase {

        public OracleAlterTableBuilder(SQLBuilderBase sqlbuilder) {

            super(sqlbuilder);

        }

        @Override

        public List<String> toStrings() {

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

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

            for (String column : drops) {

                StringBuilder builder = new StringBuilder();

                builder.append("ALTER TABLE ");

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

                builder.append(" DROP COLUMN ");

                builder.append(as_identifier(column));

                sqls.add(builder.toString());

            }

            for (ColumnDescriptor column : adds) {

                StringBuilder builder = new StringBuilder();

                builder.append("ALTER TABLE ");

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

                builder.append(" ADD "); //COLUMN ");

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

                    pks.add(column.getName());

                }

                sqls.add(builder.toString());

                if (pks.size() > 0) {

                    builder = new StringBuilder("ALTER TABLE ");

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

                    builder.append(" ADD PRIMARY KEY (");

                    for (int i = 0; i < pks.size(); i++) {

                        if (i != 0) {

                            builder.append(", ");

                        }

                        builder.append(as_identifier(pks.get(i)));

                    }

                    builder.append(")");

                    sqls.add(builder.toString());

                }

                if (column.isAutomatic()) {

                    String sequenceName = getSerialSequenceName(this.table.getName(), column.getName());

                    String sql = MessageFormat.format(

                            ADD_SERIAL_COLUMN_SEQUENCE_QUERY,

                            sequenceName

                    );

                    sqls.add(sql);

                    String autoTriggerName = getSerialTriggerName(this.table.getName(), column.getName());

                    sql = MessageFormat.format(

                            ADD_SERIAL_COLUMN_TRIGGER_QUERY,

                            autoTriggerName,

                            this.table.toString(formatter),

                            column.getName(),

                            sequenceName

                    );

                    sqls.add(sql);

                }

                if (column.isGeometry()) {

                    /**

                     * Inserting the geomtype and column on

                     * OGIS_GEOMETRY_COLUMNS.

                     *

                     * It is useful to retrieve the geometry type when the

                     * spatial index can't be defined (for instance users

                     * creating tables on a different schema).

                     *

                     * Note: We don't insert the SRID because it is unusable as

                     * defined on OGIS_GEOMETRY_COLUMNS, as it must reference an

                     * existing SRID on OGIS_SPATIAL_REFERENCE_SYSTEM.

                     * OGIS_SPATIAL_REFERENCE_SYSTEM is empty and it has a

                     * trigger that duplicates on SDO_COORD_REF_SYS any SRID

                     * inserted on OGIS_SPATIAL_REFERENCE_SYSTEM.

                     * SDO_COORD_REF_SYS is not empty and we don't want to

                     * duplicate the SRID definitions!!

                     */

                    int gvsigType = column.getGeometryType();

                    int gvsigSubtype = column.getGeometryType();

                    String sql = MessageFormat.format( // delete before inserting to avoid creating duplicates

                            DELETE_FROM_OGIS_GEOMETRY_COLUMNS_QUERY,

                            this.table().getSchema(),

                            this.table().getName(),

                            column.getName());

                    sqls.add(sql);

                    sql = MessageFormat.format(

                            INSERT_OGIS_GEOMETRY_COLUMNS_QUERY,

                            this.table().getSchema(),

                            this.table().getName(),

                            column.getName(),

                            Integer.toString(GeometryTypeUtils.toSFSGeometryTypeCode(gvsigType, gvsigSubtype)));

                    sqls.add(sql);

                    // Note: The spatial index will be created after inserting/updating layer metadata.

                    //       See OracleUpdateSpatialIndexAndMetadata class

                }

            }

            for (ColumnDescriptor column : alters) {

                if (!column.isGeometry()) { // FIXME: can't modify geom column. It could be done by adding a temporary one, copying values and then droping and renaming

                    StringBuilder builder = new StringBuilder();

                    builder.append("ALTER TABLE ");

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

                    builder.append(" MODIFY (");

                    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.getDefaultValue() == null) {

                        builder.append(" DEFAULT NULL");

                    } else {

                        builder.append(" DEFAULT '");

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

                        builder.append("'");

                    }

                    builder.append(")");

                    sqls.add(builder.toString());

                }

            }

            for (Pair<String, String> pair : renames) {

                StringBuilder builder = new StringBuilder();

                builder.append("ALTER TABLE ");

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

                builder.append(" RENAME COLUMN ");

                builder.append(as_identifier(pair.getLeft()));

                builder.append(" TO ");

                builder.append(as_identifier(pair.getRight()));

                sqls.add(builder.toString());

            }

            return sqls;

        }

    }

    @Override

    protected AlterTableBuilder createAlterTableBuilder() {

        return new OracleAlterTableBuilder(this);

    }

    @Override

    public CreateIndexBuilder createCreateIndexBuilder() {

        return new OracleCreateIndexBuilder();

    }

    @Override

    public String quote_for_identifiers() {

        return this.quote_for_identifiers;

    }

    @Override

    public String quote_for_strings() {

        return this.quote_for_strings;

    }

    @Override

    protected SelectColumnBuilder createSelectColumnBuilder() {

        return new OracleSelectColumnBuilder(this);

    }

    public class OracleSelectColumnBuilder extends SelectColumnBuilderBase {

        public OracleSelectColumnBuilder(SQLBuilder sqlbuilder) {

            super(sqlbuilder);

        }

        @Override

        public String toString(Formatter<ExpressionBuilder.Value> formatter) {

            if (formatter != null && formatter.canApply(this)) {

                return formatter.format(this);

            }

            StringBuilder builder = new StringBuilder();

            if (this.asGeometry) {

                if(this.value == VALUE_NULL){

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

                } else {

                    builder.append(expression().ST_AsBinary(this.name).toString(formatter));

                }

            } else {

                if (this.value == null) {

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

                } else {

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

                }

            }

            if (this.alias != null) {

                builder.append(" ");

                builder.append(as_identifier(this.alias));

            }

            return builder.toString();

        }

    }

    @Override

    public SelectBuilder select() {

        if (this.select == null) {

            this.select = this.createSelectBuilder();

        }

        return this.select;

    }

    @Override

    public UpdateBuilder update() {

        if (this.update == null) {

            this.update = this.createUpdateBuilder();

        }

        return this.update;

    }

    @Override

    public UpdateTableStatisticsBuilder update_table_statistics() {

        if (this.update_table_statistics == null) {

            this.update_table_statistics = this.createUpdateTableStatisticsBuilder();

        }

        return this.update_table_statistics;

    }

    @Override

    public DropTableBuilder drop_table() {

        if (this.drop_table == null) {

            this.drop_table = this.createDropTableBuilder();

        }

        return this.drop_table;

    }

    @Override

    public CreateIndexBuilder create_index() {

        if (this.create_index == null) {

            this.create_index = this.createCreateIndexBuilder();