Application: gvSIG desktop

/**

 * gvSIG. Desktop Geographic Information System.

 *

 * Copyright (C) 2007-2020 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.fmap.dal.store.jdbc2.spi.operations;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.Iterator;

import java.util.List;

import org.apache.commons.collections.CollectionUtils;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import org.gvsig.expressionevaluator.ExpressionBuilder.Parameter;

import org.gvsig.expressionevaluator.ExpressionBuilder.Variable;

import org.gvsig.expressionevaluator.ExpressionUtils;

import org.gvsig.expressionevaluator.GeometryExpressionBuilder;

import org.gvsig.fmap.dal.DataTypes;

import org.gvsig.fmap.dal.exception.DataException;

import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor;

import org.gvsig.fmap.dal.feature.FeatureReference;

import org.gvsig.fmap.dal.feature.FeatureType;

import org.gvsig.fmap.dal.feature.FeatureType.FeatureTypeChanged;

import org.gvsig.fmap.dal.feature.spi.FeatureProvider;

import org.gvsig.fmap.dal.feature.spi.FeatureReferenceProviderServices;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCExecuteSQLException;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCUpdateWithoutChangesException;

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

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

import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference;

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

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

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

import org.gvsig.tools.dispose.Disposable;

import org.gvsig.tools.dispose.DisposeUtils;

@SuppressWarnings("UseSpecificCatch")

public class PerformChangesOperation extends AbstractConnectionWritableOperation {

    protected TableReference table;

    protected FeatureType featureType;

    protected FeatureType featureTypeSource;

    protected FeatureType featureTypeTarget;

    protected Iterator<FeatureReferenceProviderServices> deleteds;

    protected Iterator<FeatureProvider> updateds;

    protected Iterator<FeatureProvider> inserteds;

    protected boolean typeChanged = false;

    public PerformChangesOperation(JDBCHelper helper) {

        this(helper, null, null, null, null, null, null);

    }

    public PerformChangesOperation(JDBCHelper helper,

            TableReference table,

            FeatureType featureType,

            Iterator<FeatureReferenceProviderServices> deleteds,

            Iterator<FeatureProvider> inserteds,

            Iterator<FeatureProvider> updateds,

            Iterator<FeatureTypeChanged> featureTypesChanged) {

        super(helper);

        this.deleteds = deleteds;

        this.inserteds = inserteds;

        this.updateds = updateds;

        this.table = table;

        this.featureType = featureType;

        if (featureTypesChanged.hasNext()) {

            FeatureTypeChanged item = featureTypesChanged.next();

            this.featureTypeSource = item.getSource();

            this.featureTypeTarget = item.getTarget();

            typeChanged = true;

        } else {

            this.featureTypeSource = null;

            this.featureTypeTarget = null;

            typeChanged = false;

        }

    }

    public boolean isTypeChanged() {

        return typeChanged;

    }

    @Override

    public Object perform(Connection conn) throws DataException {

        if (typeChanged) {

            this.performUpdateTable(conn);

        }

        if (inserteds.hasNext()) {

            performInserts(conn);

        }

        if (updateds.hasNext()) {

            performUpdates(conn);

        }

        if (deleteds.hasNext()) {

            performDeletes(conn);

        }

        return true;

    }

    public String getDeleteSQL() {

        JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

        return this.getDeleteSQL(sqlbuilder);

    }

    public String getDeleteSQL(JDBCSQLBuilderBase sqlbuilder) {

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.delete().table()

                .database(this.table.getDatabase())

                .schema(this.table.getSchema())

                .name(this.table.getTable());

        for (FeatureAttributeDescriptor attr : this.featureType) {

            if (attr.isPrimaryKey()) {

                sqlbuilder.delete().where().and(

                        expbuilder.eq(

                                expbuilder.column(attr.getName()),

                                expbuilder.parameter(attr.getName()).as_variable()

                        )

                );

            }

        }

        if (!sqlbuilder.delete().has_where() ) {

            throw new RuntimeException("Operation requires missing pk");

        }

        sqlbuilder.setProperties(

                Variable.class,

                PROP_TABLE, table

        );

        String sql = sqlbuilder.delete().toString();

        return sql;

    }

    public void performDeletes(Connection conn) throws DataException {

        JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

        String sql = getDeleteSQL(sqlbuilder);

        PreparedStatement st = null;

        Disposable paramsDisposer = null;

        try {

            st = conn.prepareStatement(sql);

            while (deleteds.hasNext()) {

                FeatureReference reference = (FeatureReference) deleteds.next();

                paramsDisposer = sqlbuilder.setParameters(st, reference);

                int nAffected = JDBCUtils.executeUpdate(st,sql);

                if (nAffected == 0) {

                    throw new JDBCUpdateWithoutChangesException(

                            sqlbuilder.delete().toString(),

                            null

                    );

                }

                if (nAffected > 1) {

                    LOGGER.warn("Remove statement affectst to {} rows ( {} )",

                            nAffected, sql

                    );

                }

            }

        } catch (SQLException e) {

            throw new JDBCSQLException(e);

        } finally {

            JDBCUtils.closeQuietly(st);

            DisposeUtils.disposeQuietly(paramsDisposer);

        }

    }

    public String getInsertSQL() {

        JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

        return this.getInsertSQL(sqlbuilder);

    }

    public String getInsertSQL(JDBCSQLBuilderBase sqlbuilder) {

        GeometryExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.insert().table()

                .database(this.table.getDatabase())

                .schema(this.table.getSchema())

                .name(this.table.getTable());

        for (FeatureAttributeDescriptor attr : this.featureType) {

            if( attr.isAutomatic() || attr.isComputed() ) {

                continue;

            }

            if (attr.getType() == DataTypes.GEOMETRY) {

                sqlbuilder.insert().column().name(attr.getName()).with_value(

                    expbuilder.parameter(attr.getName()).as_variable()

                        .srs(

                            expbuilder.parameter().value(

                                attr.getSRS()).as_constant()

                        )

                );

            } else {

                sqlbuilder.insert().column().name(attr.getName()).with_value(

                        expbuilder.parameter(attr.getName())

                );

            }

        }

        sqlbuilder.setProperties(

                Variable.class,

                PROP_FEATURE_TYPE, featureType,

                PROP_TABLE, table

        );

        sqlbuilder.setProperties(

                Parameter.class,

                PROP_FEATURE_TYPE, featureType,

                PROP_TABLE, table

        );

        String sql = sqlbuilder.insert().toString();

        return sql;

    }

    public void performInserts(Connection conn) throws DataException {

        JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

        String sql = getInsertSQL(sqlbuilder);

        PreparedStatement st;

        Disposable paramsDisposer;

        try {

            st = conn.prepareStatement(sql);

            while (inserteds.hasNext()) {

                FeatureProvider feature = inserteds.next();

                paramsDisposer = sqlbuilder.setParameters(st, feature);

                try {

                    if (JDBCUtils.executeUpdate(st,sql) == 0) {

                        throw new JDBCExecuteSQLException(

                                sqlbuilder.insert().toString(),

                                null

                        );

                    }

                } finally {

                    DisposeUtils.disposeQuietly(paramsDisposer);

                }

            }

        } catch (JDBCExecuteSQLException ex) {

            throw ex;

        } catch (Exception ex) {

            throw new JDBCExecuteSQLException(sql,ex);

        }

    }

    public String getUpdateSQL() {

        JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

        return this.getUpdateSQL(sqlbuilder);

    }

    public String getUpdateSQL(JDBCSQLBuilderBase sqlbuilder) {

        GeometryExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.update().table()

                .database(this.table.getDatabase())

                .schema(this.table.getSchema())

                .name(this.table.getTable());

        for (FeatureAttributeDescriptor attr : this.featureType) {

            if (attr.isPrimaryKey()) {

                sqlbuilder.update().where().and(

                        expbuilder.eq(

                                expbuilder.column(attr.getName()),

                                expbuilder.parameter(attr.getName()).as_variable()

                        )

                );

                continue;

            }

            if ( attr.isAutomatic() || attr.isReadOnly() || attr.isComputed() ) {

                continue;

            }

            if (attr.getType() == DataTypes.GEOMETRY) {

                if (attr.getSRS()==null) {

                    throw new RuntimeException("Geometries with null srs are not valid. SRS is required in "+this.table.toString()+"in the field:"+attr.getName());

                }

                sqlbuilder.update().column().name(attr.getName()).with_value(

                    expbuilder.parameter(attr.getName()).as_geometry_variable().srs(

                        expbuilder.parameter().value(attr.getSRS()).as_constant()

                    )

                );

            } else {

                sqlbuilder.update().column().name(attr.getName()).with_value(

                    expbuilder.parameter(attr.getName()).as_variable()

                );

            }

        }

        if (!sqlbuilder.update().has_where() ) {

            throw new RuntimeException("Operation requires missing pk");

        }

        sqlbuilder.setProperties(

                Variable.class,

                PROP_FEATURE_TYPE, this.featureType,

                PROP_TABLE, table

        );

        sqlbuilder.setProperties(

                Parameter.class,

                PROP_FEATURE_TYPE, featureType,

                PROP_TABLE, table

        );

        String sql = sqlbuilder.update().toString();

        return sql;

    }

    public void performUpdates(Connection conn) throws DataException {

        JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

        String sql = getUpdateSQL(sqlbuilder);

        PreparedStatement st = null;

        Disposable paramsDisposer = null;

        try {

            st = conn.prepareStatement(sql);

            while (updateds.hasNext()) {

                FeatureProvider featureProvider = (FeatureProvider) updateds.next();

                paramsDisposer = sqlbuilder.setParameters(st, featureProvider);

                if (JDBCUtils.executeUpdate(st,sql) == 0) {

                    throw new JDBCUpdateWithoutChangesException(sql,null);

                }

            }

        } catch (SQLException e) {

            throw new JDBCSQLException(e);

        } finally {

            JDBCUtils.closeQuietly(st);

            DisposeUtils.disposeQuietly(paramsDisposer);

        }

    }

    public List<String> getUpdateTableSQLs() {

        JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

        sqlbuilder.alter_table().table()

                .database(this.table.getDatabase())

                .schema(this.table.getSchema())

                .name(this.table.getTable());

        for (FeatureAttributeDescriptor attrOrgiginal : featureTypeSource) {

            FeatureAttributeDescriptor attrTarget = featureTypeTarget.getAttributeDescriptor(

                    attrOrgiginal.getName()

            );

            if (attrTarget == null) {

                if( attrOrgiginal.isComputed() ) {

                    continue;

                }

                sqlbuilder.alter_table().drop_column(attrOrgiginal.getName());

            } else if( !this.areEquals(attrOrgiginal, attrTarget) ) {

                if( attrTarget.isComputed() ) {

                    continue;

                }

                if( attrTarget.getType()==DataTypes.GEOMETRY ) {

                    sqlbuilder.alter_table().alter_geometry_column(

                            attrTarget.getName(),

                            attrTarget.getGeomType().getType(),

                            attrTarget.getGeomType().getSubType(),

                            attrTarget.getSRS(),

                            attrTarget.isIndexed(),

                            attrTarget.allowNull()

                    );

                } else {

                    Object defaultValue = attrTarget.getDefaultValue();

                    if( defaultValue instanceof CharSequence ) {

                        if( ExpressionUtils.isDynamicText(defaultValue.toString())) {

                            defaultValue = null;

                        }

                    }

                    sqlbuilder.alter_table().alter_column(

                            attrTarget.getName(),

                            attrTarget.getType(),

                            attrTarget.getSize(),

                            attrTarget.getPrecision(),

                            attrTarget.getScale(),

                            attrTarget.isPrimaryKey(),

                            attrTarget.isIndexed(),

                            attrTarget.allowNull(),

                            attrTarget.isAutomatic(),

                            defaultValue

                    );

                }

            }

        }

        for (FeatureAttributeDescriptor attrTarget : featureTypeTarget) {

            if( attrTarget.isComputed() ) {

                continue;

            }

            if (featureTypeSource.getAttributeDescriptor(attrTarget.getName()) == null) {

                if( attrTarget.getType()==DataTypes.GEOMETRY ) {

                    sqlbuilder.alter_table().add_geometry_column(

                            attrTarget.getName(),

                            attrTarget.getGeomType().getType(),

                            attrTarget.getGeomType().getSubType(),

                            attrTarget.getSRS(),

                            attrTarget.isIndexed(),

                            attrTarget.allowNull()

                    );

                } else {

                    Object defaultValue = attrTarget.getDefaultValue();

                    if( defaultValue instanceof CharSequence ) {

                        if( ExpressionUtils.isDynamicText(defaultValue.toString())) {

                            defaultValue = null;

                        }

                    }

                    sqlbuilder.alter_table().add_column(

                            attrTarget.getName(),

                            attrTarget.getType(),

                            attrTarget.getSize(),

                            attrTarget.getPrecision(),

                            attrTarget.getScale(),

                            attrTarget.isPrimaryKey(),

                            attrTarget.isIndexed(),

                            attrTarget.allowNull(),

                            attrTarget.isAutomatic(),

                            defaultValue

                    );

                }

            }

        }

        sqlbuilder.setProperties(

                Variable.class,

                PROP_TABLE, table

        );

        List<String> sqls = sqlbuilder.alter_table().toStrings();

        sqls.addAll(buildCreateIndexSQL());

        return sqls;

    }

    protected boolean areEquals(FeatureAttributeDescriptor attr1, FeatureAttributeDescriptor attr2) {

        // No interesa si son o no iguales en general, solo si son iguales en lo

        // que a los atributos usados para crear la columna de la tabla se refiere.

        if( !StringUtils.equals(attr1.getName(), attr2.getName()) ) {

            return false;

        }

        if( attr1.getType() != attr2.getType() ) {

            return false;

        }

        if( attr1.getPrecision() != attr2.getPrecision() ) {

            return false;

        }

        if( attr1.getScale() != attr2.getScale() ) {

            return false;

        }

        if( attr1.getSize() != attr2.getSize() ) {

            return false;

        }

        if( attr1.isPrimaryKey() != attr2.isPrimaryKey() ) {

            return false;

        }

//        if( attr1.isIndexed() != attr2.isIndexed() ) {

//            return false;

//        }

        if( attr1.allowNull() != attr2.allowNull() ) {

            return false;

        }

        if( attr1.isAutomatic() != attr2.isAutomatic() ) {

            return false;

        }

        if( attr1.getDefaultValue() != attr2.getDefaultValue() ) {

            if( attr1.getDefaultValue()==null || attr2.getDefaultValue()==null) {

                return false;

            }

            if( !attr1.getDefaultValue().equals(attr2.getDefaultValue()) ) {

                return false;

            }

        }

        return true;

    }

    protected List<String> buildCreateIndexSQL() {

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

        for (FeatureAttributeDescriptor attrTarget : featureTypeTarget) {

            boolean createIndex = false;

            if( attrTarget.isIndexed() ) {

                FeatureAttributeDescriptor attrOriginal = featureTypeSource.getAttributeDescriptor(attrTarget.getName());

                if ( attrOriginal == null) {

                    createIndex = true;

                } else {

                    if( attrOriginal.isIndexed() ) {

                        createIndex = false;

                    } else {

                        createIndex = true;

                    }

                }

            }

            if( createIndex ) {

                JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

                if( attrTarget.getType()==DataTypes.GEOMETRY ) {

                    sqlbuilder.create_index().spatial();

                }

                sqlbuilder.create_index().if_not_exist();

                sqlbuilder.create_index().name("idx_" + table.getTable() + "_" + attrTarget.getName());

                sqlbuilder.create_index().column(attrTarget.getName());

                sqlbuilder.create_index().table()

                    .database(this.table.getDatabase())

                    .schema(this.table.getSchema())

                    .name(this.table.getTable());

                sqlbuilder.setProperties(

                        Variable.class,

                        PROP_TABLE, table

                );

                sqls.addAll(sqlbuilder.create_index().toStrings());

            }

        }

        return sqls;

    }

    public void performUpdateTable(Connection conn) throws DataException {

        List<String> sqls = this.getUpdateTableSQLs();

        if( !CollectionUtils.isEmpty(sqls) ) {

            Statement st = null;

            try {

                st = conn.createStatement();

                for (String sql : sqls) {

                    if( !StringUtils.isBlank(sql) ) {

                        JDBCUtils.execute(st, sql);

                    }

                }

            } catch (SQLException e) {

                throw new JDBCSQLException(e);

            } finally {

                JDBCUtils.closeQuietly(st);

            }

        }

    }

}