Application: gvSIG desktop » gvSIG data provider for MS SQLServer

/* gvSIG. Geographic Information System of the Valencian Government

 *

 * Copyright (C) 2007-2016 Infrastructures and Transports Department

 * of the Valencian Government (CIT)

 *

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

 *

 */

package org.gvsig.mssqlserver.dal;

import org.gvsig.mssqlserver.dal.operations.MSSQLServerOperationsFactory;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.text.MessageFormat;

import java.util.ArrayList;

import java.util.List;

import org.apache.commons.dbcp.BasicDataSource;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import org.gvsig.fmap.dal.DataParameters;

import org.gvsig.fmap.dal.DataTypes;

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.resource.exception.AccessResourceException;

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

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

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

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

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

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;

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

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

import org.gvsig.fmap.geom.Geometry;

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

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

public class MSSQLServerHelper extends JDBCHelperBase {

    private static final Logger logger = LoggerFactory.getLogger(MSSQLServerHelper.class);

    public static final String NAME = "MSSQLServer";

    public static final String INSTANCE_NAME = "SQLEXPRESS";

    public static final int PORT = 1433;

    public static final String MSSQLServerJDBCDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

//    private static final boolean ALLOW_AUTOMATIC_VALUES = true;

//    private static final String QUOTE_FOR_USE_IN_IDENTIFIERS = "\"";

//    private static final String QUOTE_FOR_USE_IN_STRINGS = "'";

    public static String getConnectionURL(JDBCConnectionParameters params) {

        String connectionURL = MessageFormat.format(

                //"jdbc:sqlserver://{0}:{1,number,#};databaseName={2};instanceName={3};",

                "jdbc:sqlserver://{0};databaseName={2};instanceName={3};",

                params.getHost(),

                params.getPort(),

                params.getDBName(),

                ((DataParameters) params).getDynValue("instanceName")

        );

        logger.debug("connectionURL: {}", connectionURL);

        return connectionURL;

    }

    private static class ConnectionProvider {

        private static boolean needRegisterDriver = true;

        private BasicDataSource dataSource = null;

        private final JDBCConnectionParameters connectionParameters;

        public ConnectionProvider(JDBCConnectionParameters connectionParameters) {

            this.connectionParameters = connectionParameters;

        }

        public Connection getConnection() throws SQLException {

            if (this.dataSource == null) {

                this.dataSource = this.createDataSource();

            }

            Connection conn = this.dataSource.getConnection();

            return conn;

        }

        private BasicDataSource createDataSource() throws SQLException {

            if (!this.isRegistered()) {

                this.registerDriver();

            }

            JDBCConnectionParameters params = connectionParameters;

            BasicDataSource dataSource = new BasicDataSource();

            dataSource.setDriverClassName(params.getJDBCDriverClassName());

            dataSource.setUsername(params.getUser());

            dataSource.setPassword(params.getPassword());

            dataSource.setUrl(params.getUrl());

            dataSource.setMaxWait(60L * 1000);

            return dataSource;

        }

        private boolean isRegistered() {

            return needRegisterDriver;

        }

        public void registerDriver() throws SQLException {

            String className = this.connectionParameters.getJDBCDriverClassName();

            if (className == null) {

                return;

            }

            try {

                Class theClass = Class.forName(className);

                if (theClass == null) {

                    throw new JDBCDriverClassNotFoundException(MSSQLServerLibrary.NAME, className);

                }

            } catch (Exception e) {

                throw new SQLException("Can't register JDBC driver '" + className + "'.", e);

            }

            needRegisterDriver = false;

        }

    }

    private ConnectionProvider connectionProvider = null;

    private FeatureType lastUsedFeatureType = null;

    private String lastUsedSpatialType = null;

    public MSSQLServerHelper(JDBCConnectionParameters connectionParameters) {

        super(connectionParameters);

    }

    @Override

    public Connection getConnection() throws AccessResourceException {

        try {

            if (this.connectionProvider == null) {

                this.connectionProvider = new ConnectionProvider(this.getConnectionParameters());

            }

            return this.connectionProvider.getConnection();

        } catch (SQLException ex) {

            throw new AccessResourceException(MSSQLServerLibrary.NAME, ex);

        }

    }

    @Override

    public String getConnectionURL() {

        return getConnectionURL(this.getConnectionParameters());

    }

    @Override

    protected String getResourceType() {

        return MSSQLServerLibrary.NAME;

    }

    @Override

    public String getProviderName() {

        return MSSQLServerLibrary.NAME;

    }

    @Override

    public JDBCSQLBuilderBase createSQLBuilder() {

        return new MSSQLServerSQLBuilder(this);

    }

    /**

     * Devuelbe el nombre del tipo de datos espacial de SQLServer asociado 

     * al ultimo FeatureType cargado.

     * 

     * Esta funcion es usada para dar un soporte limitado al tipo de datos

     * spaciales "geography".

     * 

     * Si no disponemos de un FeatureType asumimos "geometry".

     * Si hay un solo campo espacial asume el tipo de ese campo, y si 

     * hay mas de uno asume "geometry".

     * En caso de que no haya ningun campo espacial asumimos "geometry".

     * 

     * @return "geometry" or "geography" for the last used table.

     */

    public String getSpatialType() {

        if( this.lastUsedSpatialType != null ) {

            return this.lastUsedSpatialType;

        }

        if( this.lastUsedFeatureType == null ) {

            this.lastUsedSpatialType = "geometry";

            return this.lastUsedSpatialType;

        }

        String spatialType = null;

        for (FeatureAttributeDescriptor attr : lastUsedFeatureType) {

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

                if( spatialType != null ) {

                    this.lastUsedSpatialType = "geometry";

                    return this.lastUsedSpatialType;

                }

                spatialType = (String) attr.getAdditionalInfo("SQLServer_type_name");

            }

        }

        if( StringUtils.isEmpty(spatialType) ) {

            this.lastUsedSpatialType = "geometry";

            return this.lastUsedSpatialType;

        }

        this.lastUsedSpatialType = spatialType;

        return this.lastUsedSpatialType;

    }

    /**

     * Devuelbe el tipo de datos espacial de SQLServer asociado a la columna 

     * indicada.

     * 

     * Esta funcion es usada para dar un soporte limitado al tipo de datos

     * spaciales "geography".

     * 

     * Si no disponemos de un FeatureType asumimos "geometry".

     * Si no existe el campo solicitado o no es de tipo geoemtria, asumimos 

     * el tipo espacia asociado al FeatureType (getSpatialType()).

     * 

     * @param columnName

     * @return "geometry" or "geography" for column in the last used table.

     */

    public String getSpatialType(String columnName) {

        if( this.lastUsedFeatureType == null ) {

            return "geometry";

        }

        FeatureAttributeDescriptor attr = this.lastUsedFeatureType.getAttributeDescriptor(columnName);

        if( attr == null ) {

            return this.getSpatialType();

        }

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

            return this.getSpatialType();

        }

        String spatialType = (String) attr.getAdditionalInfo("SQLServer_type_name");

        if( StringUtils.isEmpty(spatialType) ) {

            return "geometry";

        }

        return spatialType;

    }

    public void setLastUsedFeatureType(FeatureType featureType) {

        this.lastUsedSpatialType = null;

        this.lastUsedFeatureType = featureType;

    }

    public void createOrUpdateSpatialIndex(

            Connection conn,

            String database,

            String schema,

            String table,

            String columnName

        ) throws JDBCSQLException {

        List<String> sqls = this.createOrUpdateSpatialIndexSql(

                conn, database, schema, table, columnName

        );

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

        }

    }

    public List<String> createOrUpdateSpatialIndexSql(

            Connection conn,

            String database,

            String schema,

            String table,

            String columnName

        ) throws JDBCSQLException {

        JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

        MSSQLServerSQLBuilder.MSSQLServerCreateIndexBuilder create_index = (MSSQLServerSQLBuilder.MSSQLServerCreateIndexBuilder) sqlbuilder.create_index();

        sqlbuilder.create_index().spatial();

        create_index.setBoundingBox(

                this.getBoundingBox(

                        conn,

                        database,

                        schema,

                        table,

                        columnName

                )

        );

        create_index.if_not_exist();

        create_index.name("idx_" + table + "_" + columnName);

        create_index.column(columnName);

        create_index.table().database(database).schema(schema).name(table);

        return create_index.toStrings();

    }    

    public Envelope getBoundingBox(

            Connection conn,

            String database,

            String schema,

            String table,

            String columnName

        ) throws JDBCSQLException {

        JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.select().column().value(

            expbuilder.as_geometry(

                expbuilder.ST_ExtentAggregate(

                        expbuilder.column(columnName)

                )

            )

        );

        sqlbuilder.select().from().table().database(database).schema(schema).name(table);

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

        Statement st = null;

        ResultSet rs = null;

        try {

            st = conn.createStatement();

            rs = JDBCUtils.executeQuery(st, sql);

            if (!rs.next()) {

                return null;

            }

            Geometry geom = this.getGeometryFromColumn(rs, 1);

            if (geom == null) {

                return null;

            }

            return geom.getEnvelope();

        } catch (Exception ex) {

            throw new JDBCSQLException(ex);

        } finally {

            JDBCUtils.closeQuietly(st);

            JDBCUtils.closeQuietly(rs);

        }        

    }

    @Override

    public OperationsFactory getOperations() {

        if (this.operationsFactory == null) {

            this.operationsFactory = new MSSQLServerOperationsFactory(this);

        }

        return operationsFactory;

    }

    @Override

    public ExpressionBuilder.GeometrySupportType getGeometrySupportType() {

        return ExpressionBuilder.GeometrySupportType.WKB;

    }

    @Override

    public boolean hasSpatialFunctions() {

        return true;

    }

    @Override

    public boolean canWriteGeometry(int geometryType, int geometrySubtype) {

        return true;

    }

    @Override

    public String getQuoteForIdentifiers() {

        return "\"";

    }

    @Override

    public boolean allowAutomaticValues() {

        return true;

    }

    @Override

    public boolean supportOffsetInSelect() {

        return true;

    }

    @Override

    public String getQuoteForStrings() {

        return "'";

    }

    @Override

    public String getSourceId(JDBCStoreParameters parameters) {

        return parameters.getHost() + ":" +

               parameters.getDynValue("InstanceName")+ ":" + 

               parameters.getDBName() + "." + 

               parameters.getSchema()+ "." + 

               parameters.getTable();

    }

    @Override

    public JDBCNewStoreParameters createNewStoreParameters() {

        return new MSSQLServerNewStoreParameters();

    }

    @Override

    public JDBCStoreParameters createOpenStoreParameters() {

        return new MSSQLServerStoreParameters();

    }

    @Override

    public JDBCServerExplorerParameters createServerExplorerParameters() {

        return new MSSQLServerExplorerParameters();

    }

}