Application: gvSIG desktop

package org.gvsig.fmap.dal.store.jdbc2.spi.operations;

import java.sql.Connection;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

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

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

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

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

import org.gvsig.fmap.dal.SQLBuilder;

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

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

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

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

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

import org.gvsig.fmap.geom.DataTypes;

public class CreateTableOperation extends AbstractConnectionWritableOperation {

    private final TableReference table;

    private final FeatureType type;

    private final List<Pair<String, SQLBuilder.Privilege>> userAndPrivileges;

    private final List<String> additionalSQLs;

    public CreateTableOperation(

            JDBCHelper helper

        ) {

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

    }

    public CreateTableOperation(

            JDBCHelper helper,

            TableReference table,

            FeatureType type,

            List<Pair<String, SQLBuilder.Privilege>> userAndPrivileges,

            List<String> additionalSQLs

        ) {

        super(helper);

        this.table = table;

        this.type = type;

        this.userAndPrivileges = userAndPrivileges;

        this.additionalSQLs = additionalSQLs;

    }

    @Override

    public final Object perform(Connection conn) throws DataException {

        return this.performCreateTable(conn, 

                table, type, userAndPrivileges, additionalSQLs);

    }

    protected List<String> buildCreateIndexesSQL(

            TableReference table,

            FeatureType type

        ) {

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

        for (FeatureAttributeDescriptor attr : type) {

            if( attr.isIndexed() ) {

                JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

                if( attr.getType()==org.gvsig.fmap.dal.DataTypes.GEOMETRY ) {

                    sqlbuilder.create_index().spatial();

                }

                sqlbuilder.create_index().if_not_exist();

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

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

                sqlbuilder.create_index().table()

                        .database(this.table.getDatabase())

                        .schema(this.table.getSchema())

                        .name(this.table.getTable()

                );

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

            }

        }

        return sqls;

    }

    public boolean performCreateTable(Connection conn,

            TableReference table,

            FeatureType type,

            List<Pair<String, SQLBuilder.Privilege>> rolesAndPrivileges,

            List<String> additionalSQLs) throws DataException {

        JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();

        sqlbuilder.create_table().table()

                .database(this.table.getDatabase())

                .schema(this.table.getSchema())

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

        for (FeatureAttributeDescriptor attr : type) {

            if( attr.isComputed() ) {

                continue;

            }

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

                sqlbuilder.create_table().add_geometry_column(

                        attr.getName(),

                        attr.getGeomType().getType(),

                        attr.getGeomType().getSubType(),

                        attr.getSRS(),

                        attr.isIndexed(),

                        attr.allowNull()

                );

            } else {

                sqlbuilder.create_table().add_column(

                        attr.getName(),

                        attr.getType(),

                        attr.getSize(),

                        attr.getPrecision(),

                        attr.getScale(),

                        attr.isPrimaryKey(),

                        attr.isIndexed(),

                        attr.allowNull(),

                        attr.isAutomatic(),

                        attr.getDefaultValue()

                );

            }

        }

        for (Pair<String, SQLBuilder.Privilege> roleAndPrivilege : rolesAndPrivileges) {

            sqlbuilder.grant().role(roleAndPrivilege.getLeft()).privilege(roleAndPrivilege.getRight());

        }

        List<String> sqls;

        sqls = sqlbuilder.create_table().toStrings();

        sqls.addAll(buildCreateIndexesSQL(this.table,type));

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

        sqls.addAll(additionalSQLs);

        Statement st = null;

        try {

            st = conn.createStatement();

            for (String sql : sqls) {

                JDBCUtils.execute(st, sql);

            }

        } catch (SQLException ex) {

            throw new JDBCSQLException(ex);

        } finally {

            JDBCUtils.closeQuietly(st);

        }

        return true;

    }

}