Application: gvSIG desktop » gvSIG data provider for MS SQLServer

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

    <modelVersion>4.0.0</modelVersion>

    <artifactId>org.gvsig.mssqlserver.provider</artifactId>

    <packaging>jar</packaging>

    <name>${project.artifactId}</name>

    <description>DAL provider for MS SQLServer</description>

    <parent>

        <groupId>org.gvsig</groupId>

        <artifactId>org.gvsig.mssqlserver</artifactId>

        <version>1.0.30</version>

    </parent>

    <dependencies>

        <dependency>

            <groupId>com.googlecode.log4jdbc</groupId>

            <artifactId>log4jdbc</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.tools.lib</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.projection.api</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.fmap.dal.api</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.fmap.dal.impl</artifactId>

            <scope>test</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.fmap.dal.spi</artifactId>

            <scope>test</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.fmap.dal.db.jdbc</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.fmap.dal.db.lib</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.fmap.dal.spi</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.metadata.lib.basic.api</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.fmap.geometry.api</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>${org.gvsig.fmap.geometry.impl}</artifactId>

            <scope>test</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.timesupport.lib.api</artifactId>

            <scope>compile</scope>

        </dependency>  

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.utils</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>com.microsoft.sqlserver</groupId>

            <artifactId>sqlserver-jdbc</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>commons-dbcp</groupId>

            <artifactId>commons-dbcp</artifactId>

            <scope>compile</scope>

        </dependency>

        <dependency>

            <groupId>commons-collections</groupId>

            <artifactId>commons-collections</artifactId>

            <scope>compile</scope>

        </dependency>

        <!-- TESTS -->

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.tools.lib</artifactId>

            <type>test-jar</type>

            <scope>test</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.compat.se</artifactId>

            <scope>test</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.fmap.dal.impl</artifactId>

            <type>test-jar</type>

            <scope>test</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.metadata.lib.basic.impl</artifactId>

            <scope>compile</scope>

        </dependency>    

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.projection.cresques.impl</artifactId>

            <scope>test</scope>

        </dependency>

        <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.timesupport.lib.impl</artifactId>

            <scope>test</scope>

        </dependency>

        <dependency>

            <groupId>junit</groupId>

            <artifactId>junit</artifactId>

            <version>3.8.1</version>

            <type>jar</type>

        </dependency>

    </dependencies>

    <build>

        <plugins>

            <plugin>

                <groupId>org.codehaus.mojo</groupId>

                <artifactId>animal-sniffer-maven-plugin</artifactId>

                <executions>

                    <execution>

                        <id>check-java-api</id>

                        <phase>package</phase>

                        <goals>

                            <goal>check</goal>

                        </goals>

                        <configuration>

                            <skip>true</skip>

                            <signature>

                                <groupId>org.codehaus.mojo.signature</groupId>

                                <artifactId>java18</artifactId>

                                <version>1.0</version>

                            </signature>

                        </configuration>

                    </execution>

                </executions>

            </plugin>            

        </plugins>

    </build>

</project>

org.gvsig.fmap.dal.store.jdbc2.JDBCLibrary

org.gvsig.mssqlserver.dal.MSSQLServerLibrary

<?xml version="1.0"?>

<!--

    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.

-->

<definitions>

  <version>1.0.0</version>

  <classes>

    <class name="JDBC2" namespace="Metadata">

      <extends>

      	<class name="SpatialProvider" namespace="Metadata"/>

      </extends>

      <description>Metadata of a JDBC store</description>

      <fields>

      </fields>

    </class>

  </classes>

</definitions>  

<?xml version="1.0"?>

<!--

    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.

-->

<definitions>

  <version>1.0.0</version>

  <classes>

    <class name="JDBC2ResourceParameters">

      <description></description>

      <extends>

        <class>DBResourceParameters</class>

      </extends>

      <fields>

        <field name="JDBCDriverClass" type="string" mandatory="true" group="Advanced">

          <description>JDBC Driver class</description>

        </field>

        <field name="URL" type="string" mandatory="true" group="Connection">

          <description>JDBC url connection</description>

        </field>

        <field name="Catalog" type="string" mandatory="false" group="Basic">

          <description>Database catalog</description>

        </field>

        <field name="Schema" type="string" mandatory="false" group="Basic">

          <description>Database schema</description>

        </field>

      </fields>

    </class>

    <class name="JDBC2StoreParameters">

      <extends>

        <class>JDBC2ResourceParameters</class>

        <class>DBStoreParameters</class>

      </extends>

      <tags>

          <value name="defaultGroup" value="Basic"/>

      </tags>

      <fields>

      </fields>

    </class>

    <class name="JDBC2NewStoreParameters">

      <description></description>

      <extends>

        <class>JDBC2ResourceParameters</class>

        <class>DBNewStoreParameters</class>

      </extends>

      <tags>

          <value name="defaultGroup" value="Basic"/>

      </tags>

      <fields>

        <field name="selectRole" label="Roles for select" type="String" mandatory="false" defaultValue="" group="Permissions">

          <description>Roles asigned to have select privilegies</description>

        </field>

        <field name="insertRole" label="Roles for insert" type="String" mandatory="false" defaultValue="" group="Permissions">

          <description>Roles asigned to have insert privilegies</description>

        </field>

        <field name="updateRole" label="Roles for update" type="String" mandatory="false" defaultValue="" group="Permissions">

          <description>Roles asigned to have update privilegies</description>

        </field>

        <field name="deleteRole" label="Roles for delete" type="String" mandatory="false" defaultValue="" group="Permissions">

          <description>Roles asigned to have delete privilegies</description>

        </field>

        <field name="truncateRole" label="Roles for truncate" type="String" mandatory="false" defaultValue="" group="Permissions">

          <description>Roles asigned to have truncate privilegies</description>

        </field>

        <field name="referenceRole" label="Roles for reference" type="String" mandatory="false" defaultValue="" group="Permissions">

          <description>Roles asigned to have reference privilegies</description>

        </field>

        <field name="triggerRole" label="Roles for trigger" type="String" mandatory="false" defaultValue="" group="Permissions">

          <description>Roles asigned to have trigger privilegies</description>

        </field>

        <field name="allRole" label="Roles for all" type="String" mandatory="false" defaultValue="" group="Permissions">

          <description>Roles asigned to have all privilegies</description>

        </field>

        <field name="postCreatingStatement" label="Post-creating statement" type="String" mandatory="false" defaultValue="" group="Advanced">

          <description>Roles asigned to have all privilegies</description>

        </field>

      </fields>

    </class>

    <class name="JDBC2ServerExplorerParameters">

      <extends>

        <class>JDBC2ResourceParameters</class>

        <class>DBServerExplorerParameters</class>

      </extends>

      <tags>

          <value name="defaultGroup" value="Basic"/>

      </tags>

      <fields>

        <field name="ShowInformationDBTables" type="boolean" 

            mandatory="false" defaultValue="false" group="Basic">

          <description>Show information tables</description>

        </field>

      </fields>

    </class>

  </classes>

</definitions>  

<?xml version="1.0"?>

<definitions>

  <version>1.0.0</version>

  <classes>

    <class name="JDBC2">

      <extends>

      </extends>

      <fields>

        <field name="function_ST_AsWKB" type="string" group="functions" 

          defaultValue="ST_AsWKB({0})" >

          <description></description>

        </field>

        <field name="function_ST_envelope" type="string" group="functions" 

          defaultValue="ST_envelope({0})" >

          <description></description>

        </field>

        <field name="function_ST_Extent" type="string" group="functions" 

          defaultValue="ST_Extent({0})" >

          <description></description>

        </field>

        <field name="function_ST_intersects" type="string" group="functions" 

          defaultValue="ST_intersects({0}, {1})" >

          <description></description>

        </field>

        <field name="function_ST_GeomFromText" type="string" group="functions" 

          defaultValue="ST_GeomFromText({0}, {1})" >

          <description></description>

        </field>

        <field name="function_ST_GeomFromWKB" type="string" group="functions" 

          defaultValue="ST_GeomFromWKB({0}, {1})" >

          <description></description>

        </field>

        <field name="function_ST_GeomFromEWKB" type="string" group="functions" 

          defaultValue="ST_GeomFromEWKB({0}, {1})" >

          <description></description>

        </field>

        <field name="function_current_schema" type="string" group="functions" 

          defaultValue="current_schema()" >

          <description></description>

        </field>

        <field name="function_lcase" type="string" group="functions" 

          defaultValue="LCASE({0})" >

          <description></description>

        </field>

        <field name="function_count" type="string" group="functions" 

          defaultValue="count({0})" >

          <description></description>

        </field>

        <field name="function_crs" type="string" group="functions" 

          defaultValue="{0}" >

          <description>Use {0} for the abbreviature, {1} for the autority name and {2} for the autority code</description>

        </field>

        <!-- operators -->

        <field name="operator_isnull" type="string" group="operators" 

          defaultValue="{0} is null" >

          <description></description>

        </field>

        <field name="operator_eq" type="string" group="operators" 

          defaultValue="{0} = {1}" >

          <description></description>

        </field>

        <field name="operator_neq" type="string" group="operators" 

          defaultValue="{0} <> {1}" >

          <description></description>

        </field>

        <field name="operator_lt" type="string" group="operators" 

          defaultValue="{0} < {1}" >

          <description></description>

        </field>

        <field name="operator_le" type="string" group="operators" 

          defaultValue="{0} <= {1}" >

          <description></description>

        </field>

        <field name="operator_gt" type="string" group="operators" 

          defaultValue="{0} > {1}" >

          <description></description>

        </field>

        <field name="operator_ge" type="string" group="operators" 

          defaultValue="{0} >= {1}" >

          <description></description>

        </field>

        <field name="operator_and" type="string" group="operators" 

          defaultValue="{0} AND {1}" >

          <description></description>

        </field>

        <field name="operator_or" type="string" group="operators" 

          defaultValue="{0} OR {1}" >

          <description></description>

        </field>

        <field name="operator_like" type="string" group="operators" 

          defaultValue="{0} LIKE {1}" >

          <description></description>

        </field>

        <field name="operator_ilike" type="string" group="operators" 

          defaultValue="{0} ILIKE {1}" >

          <description></description>

        </field>

        <!-- properties -->

        <field name="AllowAutomaticValues" type="boolean" group="properties" 

          defaultValue="true" >

          <description></description>

        </field>

        <field name="QuoteForStrings" type="string" group="properties" 

          defaultValue="'" >

          <description></description>

        </field>

        <field name="QuoteForIdentifiers" type="string" group="properties" 

          defaultValue="" >

          <description></description>

        </field>

        <field name="GeometrySupportType" type="string" group="properties" 

            defaultValue="WKB" >

            <description></description>

            <availableValues>

                <value label="WKT">WKT</value>

                <value label="WKB">WKB</value>

                <value label="EWKB">EWKB</value>

            </availableValues>          

        </field>

      </fields>

    </class>

    <class name="MSSQLServer">

      <extends>

          <class>JDBC2</class>

      </extends>

      <fields>

        <field name="function_ST_AsWKB" type="string" group="functions" 

          defaultValue="({0}).STAsWKB" >

          <description></description>

        </field>

        <field name="function_ST_envelope" type="string" group="functions" 

          defaultValue="({0}).STEnvelope" >

          <description></description>

        </field>

        <field name="function_ST_Extent" type="string" group="functions" 

          defaultValue="({0}).STEnvelope" >

          <description></description>

        </field>

        <field name="function_ST_intersects" type="string" group="functions" 

          defaultValue="({0}).STIntersects({1})" >

          <description></description>

        </field>

        <field name="function_ST_GeomFromText" type="string" group="functions" 

          defaultValue="geometry::STGeomFromText({0}, {1})" >

          <description></description>

        </field>

        <field name="function_ST_GeomFromWKB" type="string" group="functions" 

          defaultValue="geometry::STGeomFromWKB({0}, {1})" >

          <description></description>

        </field>

        <field name="function_ST_GeomFromEWKB" type="string" group="functions" 

          defaultValue="geometry::STGeomFromWKB({0}, {1})" >

          <description></description>

        </field>

        <field name="function_current_schema" type="string" group="functions" 

          defaultValue="current_schema()" >

          <description></description>

        </field>

        <field name="function_crs" type="string" group="functions" 

          defaultValue="{2}" >

          <description>Use {0} for the abbreviature, {1} for the autority name and {2} for the autority code</description>

        </field>

        <field name="function_lcase" type="string" group="functions" 

          defaultValue="LOWER({0})" >

          <description></description>

        </field>

        <!-- operators -->

        <field name="operator_ilike" type="string" group="operators" 

          defaultValue="{0} ILIKE {1}" >

          <description></description>

        </field>

  </classes>

</definitions>  

<?xml version="1.0"?>

<definitions>

  <version>1.0.0</version>

  <classes>

    <class name="MSSQLServer" namespace="Metadata">

      <extends>

      	<class name="JDBC2" namespace="Metadata"/>

      </extends>

      <description>Metadata for MSSQLServer</description>

      <fields>

      </fields>

    </class>

  </classes>

</definitions>  

<?xml version="1.0"?>

<definitions>

  <version>1.0.0</version>

  <classes>

    <class name="MSSQLServerResourceParameters">

      <extends>

        <class>JDBC2ResourceParameters</class>

      </extends>

      <fields>

        <field name="JDBCDriverClass" type="string" mandatory="true"

          defaultValue="com.microsoft.sqlserver.jdbc.SQLServerDriver" group="Advanced">

          <description>JDBC driver class for MS-SQlServer</description>

        </field>

        <field name="port" type="integer" mandatory="false"

          defaultValue="1433" group="Connection">

          <description></description>

        </field>

        <field name="host" type="string" mandatory="false" 

            defaultValue="127.0.0.1" group="Connection">

          <description></description>

        </field>

        <field name="instanceName" type="string" mandatory="false" 

            defaultValue="SQLEXPRESS" group="Connection">

          <description></description>

        </field>     

        <field name="dbname" type="string" mandatory="false" defaultValue="master" group="Basic">

          <description></description>

        </field>

        <field name="dbuser" type="string" mandatory="false" defaultValue="sa" group="Basic">

          <description></description>

        </field>

        <field name="password" type="string" mandatory="false" defaultValue="123" group="Basic">

          <description></description>

        </field>           

      </fields>

    </class>

    <class name="MSSQLServerStoreParameters">

      <extends>

        <class>JDBC2StoreParameters</class>

        <class>MSSQLServerResourceParameters</class>

      </extends>

      <fields/>

    </class>

    <class name="MSSQLServerNewStoreParameters">

      <extends>

        <class>JDBC2NewStoreParameters</class>

        <class>MSSQLServerResourceParameters</class>

      </extends>

      <fields/>

    </class>

    <class name="MSSQLServerServerExplorerParameters">

      <extends>

        <class>MSSQLServerResourceParameters</class>

        <class>JDBC2ServerExplorerParameters</class>

      </extends>

      <fields/>

    </class>

  </classes>

</definitions>  

package org.gvsig.mssqlserver.dal.operations;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.Statement;

import java.util.List;

import org.cresques.cts.IProjection;

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

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

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.FetchFeatureTypeOperation;

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.GeometryLocator;

import org.gvsig.fmap.geom.type.GeometryType;

import org.gvsig.mssqlserver.dal.MSSQLServerHelper;

public class MSSQLServerFetchFeatureTypeOperation extends FetchFeatureTypeOperation {

    public MSSQLServerFetchFeatureTypeOperation(

            JDBCHelper helper

    ) {

        super(helper);

    }

    public MSSQLServerFetchFeatureTypeOperation(

            JDBCHelper helper,

            EditableFeatureType featureType,

            String dbname,

            String schema,

            String table,

            List<String> primaryKeys,

            String defaultGeometryColumn,

            IProjection crs

    ) {

        super(helper, featureType, dbname, schema, table, primaryKeys, defaultGeometryColumn, crs);

    }

    @Override

    public void fetch(EditableFeatureType featureType, Connection conn, String dbname, String schema, String table, List<String> pks, String defaultGeometryColumn, IProjection crs) throws DataException {

        super.fetch(featureType, conn, dbname, schema, table, pks, defaultGeometryColumn, crs);

        ((MSSQLServerHelper) this.helper).setLastUsedFeatureType(featureType);

    }

    @Override

    protected void fetchGeometryTypeAndSRS(

            EditableFeatureAttributeDescriptor attr,

            ResultSetMetaData rsMetadata,

            int colIndex

    ) {

        try {

            GeometryType geomType = GeometryLocator.getGeometryManager().getGeometryType(

                    Geometry.TYPES.GEOMETRY,

                    Geometry.SUBTYPES.GEOM2D

            );

            attr.setGeometryType(geomType);

            String typeName = rsMetadata.getColumnTypeName(colIndex);

            attr.setAdditionalInfo("SQLServer_type_name", typeName);

            attr.setSRS(getProjectionOfColumn(attr.getName()));

        } catch (Exception ex) {

            logger.warn("Can't get default geometry type.", ex);

        }

    }

    private IProjection getProjectionOfColumn(String columnName) {

        JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();

        sqlbuilder.select().limit(1);

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

                sqlbuilder.ST_SRID(sqlbuilder.column(columnName))

        );

        sqlbuilder.select().where().set(

                sqlbuilder.and(

                        sqlbuilder.notIsNull(sqlbuilder.column(columnName)),

                        sqlbuilder.notIsNull(sqlbuilder.ST_SRID(sqlbuilder.column(columnName)))

                )

        );

        sqlbuilder.select().from().table().schema(this.getSchema()).name(this.getTablename());

        Statement st = null;

        ResultSet rs = null;

        try {

            st = this.getConnection().createStatement();

            rs = JDBCUtils.executeQuery(st, sqlbuilder.toString());

            if (rs.next()) {

                int code = rs.getInt(1);

                try {

                    IProjection proj = this.helper.getSRSSolver().getProjection(conn, code);

                    return proj;

                } catch (Exception ex) {

                    logger.warn("Can't create projection from code '" + code + "'.", ex);

                }

            }

        } catch (Exception ex) {

            // Do nothing, return null

        } finally {

            JDBCUtils.closeQuietly(rs);

            JDBCUtils.closeQuietly(st);

        }

        return null;

    }

}

package org.gvsig.mssqlserver.dal.operations;

import java.util.Iterator;

import java.util.List;

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

import org.cresques.cts.IProjection;

import org.gvsig.fmap.dal.SQLBuilder;

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.CanModifyTableOperation;

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.CreateTableOperation;

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.FetchFeatureTypeOperation;

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.OperationsFactoryBase;

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.PerformChangesOperation;

public class MSSQLServerOperationsFactory extends OperationsFactoryBase {

    public MSSQLServerOperationsFactory(JDBCHelper helper) {

        super(helper);

    }

    @Override

    public FetchFeatureTypeOperation createFetchFeatureType(EditableFeatureType type, String database, String schema, String table, List<String> primaryKeys, String defaultGeometryField, IProjection crs) {

        return new MSSQLServerFetchFeatureTypeOperation(

                helper, type, database, schema, table, primaryKeys, 

                defaultGeometryField, crs

        );

    }

    @Override

    public CanModifyTableOperation createCanModifyTableOperation(String dbName, String schemaName, String tableName) {

        return new MSSQLServerCanModifyTableOperation(

                helper, dbName, schemaName, tableName

        );

    }

    @Override

    public CreateTableOperation createTable(String dbName, String schemaName, String tableName, FeatureType type, List<Pair<String, SQLBuilder.Privilege>> userAndPrivileges, List<String> additionalSQLs) throws DataException {

        return new MSSQLServerCreateTableOperation( helper, dbName, schemaName, tableName, type, userAndPrivileges, additionalSQLs);

    }

    @Override

    public PerformChangesOperation createPerformChanges(String database, String schema, String table, FeatureType type, Iterator deleteds, Iterator inserteds, Iterator updateds, Iterator featureTypesChanged) {

        return new MSSQLServerPerformChangesOperation(helper, database, schema, table, type, deleteds, inserteds, updateds, featureTypesChanged);

    }

}

package org.gvsig.mssqlserver.dal.operations;

import java.util.ArrayList;

import java.util.List;

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

import org.gvsig.fmap.dal.SQLBuilder;

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

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

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.CreateTableOperation;

public class MSSQLServerCreateTableOperation extends CreateTableOperation {

    public MSSQLServerCreateTableOperation(JDBCHelper helper) {

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

    }

    public MSSQLServerCreateTableOperation(

            JDBCHelper helper,

            String dbName,

            String schemaName,

            String tableName,

            FeatureType type,

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

            List<String> additionalSQLs

        ) {

        super(helper, dbName, schemaName, tableName, type, userAndPrivileges, additionalSQLs);

    }

    @Override

    protected List<String> buildCreateIndexesSQL(

            String database,

            String schema,

            String table,

            FeatureType type

        ) {

        // No podemos crear indices espaciales con la tabla vacia ya que SQLServer

        // requiere que se indique en bounding box y aun no lo sabemos.

        return new ArrayList<>();

    }

}

package org.gvsig.mssqlserver.dal.operations;

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.*;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import org.apache.commons.lang3.StringUtils;

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

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

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

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

public class MSSQLServerCanModifyTableOperation extends CanModifyTableOperation {

    public MSSQLServerCanModifyTableOperation(

            JDBCHelper helper

        ) {

        this(helper, null, null, null);

    }

    public MSSQLServerCanModifyTableOperation(

            JDBCHelper helper,

            String dbName,

            String schemaName,

            String tableName

        ) {

        super(helper,dbName,schemaName,tableName);

    }

    private String getFullTableName( 

            String dbName,

            String schemaName,

            String tableName

    ) {

        StringBuilder builder = new StringBuilder();

        if( !StringUtils.isEmpty(dbName) ) {

            builder.append(dbName);

        }

        if( !StringUtils.isEmpty(schemaName) ) {

            if( builder.length()>0 ) {

                builder.append(".");

            }

            builder.append(schemaName);

        }

        if( !StringUtils.isEmpty(tableName) ) {

            if( builder.length()>0 ) {

                builder.append(".");

            }

            builder.append(tableName);

        }

        return builder.toString();

    }

    @Override

    public boolean canModifyTable(Connection conn,

            String dbName,

            String schemaName,

            String tableName

        ) throws DataException {

        String sql = "SELECT count(*) " + 

                     "FROM fn_my_permissions('"+getFullTableName(dbName, schemaName, tableName)+"', 'OBJECT') " +

                     "WHERE permission_name in ('UPDATE', 'INSERT', 'DELETE') AND subentity_name = ''";

        Statement st = null;

        ResultSet rs = null;

        try {

            st = conn.createStatement();

            rs = JDBCUtils.executeQuery(st, sql);

            if (!rs.next()) {

                return false;

            }

            return rs.getInt(1)==3;

        } catch (SQLException ex) {

            throw new JDBCSQLException(ex);

        } finally {

            JDBCUtils.closeQuietly(st);

            JDBCUtils.closeQuietly(rs);

        }

    }

}

package org.gvsig.mssqlserver.dal.operations;

import java.util.ArrayList;

import java.util.Iterator;

import java.util.List;

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

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.PerformChangesOperation;

import org.gvsig.mssqlserver.dal.MSSQLServerHelper;

import org.gvsig.mssqlserver.dal.MSSQLServerSQLBuilder.MSSQLServerCreateIndexBuilder;

public class MSSQLServerPerformChangesOperation extends PerformChangesOperation {

    public MSSQLServerPerformChangesOperation(JDBCHelper helper) {

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

    }

    public MSSQLServerPerformChangesOperation(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, dbName, schemaName, tableName, featureType, deleteds, inserteds, updateds, featureTypesChanged);

    }

    protected MSSQLServerHelper getHelper() {

        return (MSSQLServerHelper) this.helper;

    }

    @Override

    protected List<String> buildCreateIndexSQL(

            String database,

            String schema,

            String table,

            FeatureType original,

            FeatureType target

        ) {

        for (FeatureAttributeDescriptor attrTarget : target) {

            boolean createIndex = false;

            if( attrTarget.isIndexed() ) {

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

                if ( attrOriginal == null) {

                    createIndex = true;

                } else {

                    if( attrOriginal.isIndexed() ) {

                        createIndex = false;

                    } else {

                        createIndex = true;

                    }

                }

            }

            if( createIndex ) {

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

                    try {

                        return this.getHelper().createOrUpdateSpatialIndexSql(

                                this.getConnection(),

                                database,

                                schema,

                                table,

                                attrTarget.getName()

                        );

                    } catch (Exception ex) {

                        throw new RuntimeException("Can't create or update spatial index.", ex);

                    }

                } else {

                    JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

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

                    create_index.if_not_exist();

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

                    create_index.column(attrTarget.getName());

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

                    return create_index.toStrings();

                }

            }

        }

        return new ArrayList<>();

    }    

}

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

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

            sqlbuilder.getAsGeometry(

                sqlbuilder.ST_ExtentAggregate(

                        sqlbuilder.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 SQLBuilder.GeometrySupportType getGeometrySupportType() {

        return SQLBuilder.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();

    }

}

package org.gvsig.mssqlserver.dal;

import org.gvsig.fmap.dal.DataParameters;

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

import org.gvsig.fmap.dal.spi.DataStoreProviderServices;

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.impl.JDBCStoreProviderFactory;

public class MSSQLServerStoreProviderFactory extends JDBCStoreProviderFactory {

    private static final String NAME = MSSQLServerLibrary.NAME;

    public MSSQLServerStoreProviderFactory() {

        super(

                NAME, 

                "Microsoft SQL Server store"

        );

    }

    @Override

    public JDBCStoreProvider createProvider(

            DataParameters parameters,

            DataStoreProviderServices providerServices

    ) throws InitializeException {

        JDBCHelper helper = new MSSQLServerHelper((JDBCConnectionParameters) parameters);

        JDBCStoreProvider provider = helper.createProvider(

                (JDBCStoreParameters) parameters, 

                providerServices

        );

        return provider;

    }

    @Override

    public JDBCStoreParameters createParameters() {

        JDBCStoreParameters params = new MSSQLServerStoreParameters();