Application: gvSIG desktop

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.Iterator;

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.spi.FeatureProvider;

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

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

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

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

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.spi.JDBCSQLBuilderBase;

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

import org.gvsig.tools.dispose.Disposable;

import org.gvsig.tools.dispose.DisposeUtils;

public class PerformChangesOperation extends AbstractConnectionWritableOperation {

    protected String dbName;

    protected String schemaName;

    protected String tableName;

    protected FeatureType featureType;

    protected Iterator<FeatureReferenceProviderServices> deleteds;

    protected Iterator<FeatureStoreProvider.FeatureTypeChanged> featureTypesChanged;

    protected Iterator<FeatureProvider> updateds;

    protected Iterator<FeatureProvider> inserteds;

    protected boolean typeChanged = false;

    public PerformChangesOperation(JDBCHelper helper) {

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

    }

    public PerformChangesOperation(JDBCHelper helper,

            String dbName,

            String schemaName,

            String tableName,

            FeatureType featureType,

            Iterator<FeatureReferenceProviderServices> deleteds,

            Iterator<FeatureProvider> inserteds,

            Iterator<FeatureProvider> updateds,

            Iterator<FeatureStoreProvider.FeatureTypeChanged> featureTypesChanged) {

        super(helper);

        this.dbName = dbName;

        this.deleteds = deleteds;

        this.inserteds = inserteds;

        this.updateds = updateds;

        this.schemaName = schemaName;

        this.tableName = tableName;

        this.featureType = featureType;

        this.featureTypesChanged = featureTypesChanged;

    }

    public boolean isTypeChanged() {

        return typeChanged;

    }

    @Override

    public Object perform(Connection conn) throws DataException {

        if (featureTypesChanged.hasNext()) {

            FeatureStoreProvider.FeatureTypeChanged item = featureTypesChanged.next();

            this.performUpdateTable(conn, dbName, schemaName, tableName, item.getSource(), item.getTarget());

            typeChanged = true;

        } else {

            typeChanged = false;

        }

        if (deleteds.hasNext()) {

            performDeletes(conn, dbName, schemaName, tableName, featureType, deleteds);

        }

        if (updateds.hasNext()) {

            performUpdates(conn, dbName, schemaName, tableName, featureType, updateds);

        }

        if (inserteds.hasNext()) {

            performInserts(conn, dbName, schemaName, tableName, featureType, inserteds);

        }

        return true;

    }

    protected JDBCSQLBuilderBase buildDeleteSQL(

            String database,

            String schema,

            String table,

            FeatureType type

        ) {

        JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

        sqlbuilder.delete().table().database(database).schema(schema).name(table);

        for (FeatureAttributeDescriptor attr : type) {

            if (attr.isPrimaryKey()) {

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

                        sqlbuilder.eq(

                                sqlbuilder.column(attr.getName()),

                                sqlbuilder.parameter(attr.getName()).as_variable()

                        )

                );

            }

        }

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

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

        }

        return sqlbuilder;

    }

    public void performDeletes(Connection conn,

            String database,

            String schema,

            String table,

            FeatureType type,

            Iterator<FeatureReferenceProviderServices> deleteds) throws DataException {

        JDBCSQLBuilderBase sqlbuilder = buildDeleteSQL(database, schema, table, type);

        PreparedStatement st = null;

        Disposable paramsDisposer = null;

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

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

        }

    }

    protected JDBCSQLBuilderBase buildInsertSQL(

            String database,

            String schema,

            String table,

            FeatureType type

        ) {

        JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

        sqlbuilder.insert().table().database(database).schema(schema).name(table);

        for (FeatureAttributeDescriptor attr : type) {

            if( attr.isAutomatic() ) {

                continue;

            }

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

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

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

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

                    )

                );

            } else {

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

                        sqlbuilder.parameter(attr.getName())

                );

            }

        }

        return sqlbuilder;

    }

    public void performInserts(Connection conn,

            String database,

            String schema,

            String table,

            FeatureType type,

            Iterator<FeatureProvider> inserteds) throws DataException {

        JDBCSQLBuilderBase sqlbuilder = buildInsertSQL(database, schema, table, type);

        PreparedStatement st;

        Disposable paramsDisposer = null;

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

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

        }

    }

    protected JDBCSQLBuilderBase buildUpdateSQL(

            String database,

            String schema,

            String table,

            FeatureType type

        ) {

        JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

        sqlbuilder.update().table().database(database).schema(schema).name(table);

        for (FeatureAttributeDescriptor attr : type) {

            if (attr.isPrimaryKey()) {

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

                        sqlbuilder.eq(

                                sqlbuilder.column(attr.getName()),

                                sqlbuilder.parameter(attr.getName()).as_variable()

                        )

                );

            } if ( ! attr.isAutomatic() &&  !attr.isReadOnly() ) {

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

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

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

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

                        )

                    );

                } else {

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

                        sqlbuilder.parameter(attr.getName()).as_variable()

                    );

                }

            }

        }

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

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

        }

        return sqlbuilder;

    }

    public void performUpdates(Connection conn,

            String database,

            String schema,

            String table,

            FeatureType type,

            Iterator<FeatureProvider> updateds) throws DataException {

        JDBCSQLBuilderBase sqlbuilder = buildUpdateSQL(database, schema, table, type);

        PreparedStatement st = null;

        Disposable paramsDisposer = null;

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

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

        }

    }

    protected JDBCSQLBuilderBase buildUpdateTableSQL(

            String database,

            String schema,

            String table,

            FeatureType original,

            FeatureType target

        ) {

        JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

        sqlbuilder.update().table().database(database).schema(schema).name(table);

        for (FeatureAttributeDescriptor attrOrgiginal : original) {

            FeatureAttributeDescriptor attrTarget = target.getAttributeDescriptor(

                    attrOrgiginal.getName()

            );

            if (attrTarget == null) {

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

            } else {

                sqlbuilder.alter_table().alter_column(

                        attrTarget.getName(),

                        attrTarget.getType(),

                        attrTarget.getPrecision(),

                        attrTarget.getSize(),

                        attrTarget.isPrimaryKey(),

                        attrTarget.isIndexed(),

                        attrTarget.allowNull(),

                        attrTarget.isAutomatic(),

                        attrTarget.getDefaultValue()

                );

            }

        }

        for (FeatureAttributeDescriptor attrTarget : target) {

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

                sqlbuilder.alter_table().add_column(

                        attrTarget.getName(),

                        attrTarget.getType(),

                        attrTarget.getPrecision(),

                        attrTarget.getSize(),

                        attrTarget.isPrimaryKey(),

                        attrTarget.isIndexed(),

                        attrTarget.allowNull(),

                        attrTarget.isAutomatic(),

                        attrTarget.getDefaultValue()

                );

            }

        }

        return sqlbuilder;

    }

    public void performUpdateTable(Connection conn,

            String database,

            String schema,

            String table,

            FeatureType original,

            FeatureType target) throws DataException {

        SQLBuilderBase sqlbuilder = buildUpdateTableSQL(null, null, table, original, target);

        Statement st = null;

        try {

            st = conn.createStatement();

            for (String sql : sqlbuilder.alter_table().toStrings()) {

                JDBCUtils.execute(st, sql);

            }

        } catch (SQLException e) {

            throw new JDBCSQLException(e);

        } finally {

            JDBCUtils.closeQuietly(st);

        }

    }

}