Application: gvSIG desktop » gvSIG data provider for MS SQLServer

<?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>  

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

org.gvsig.mssqlserver.dal.MSSQLServerLibrary

package org.gvsig.mssqlserver.dal;

import java.text.MessageFormat;

import java.util.ArrayList;

import java.util.List;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import org.gvsig.expressionevaluator.Formatter;

import org.gvsig.fmap.dal.DataTypes;

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

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

import org.gvsig.mssqlserver.dal.expressionbuilderformatter.MSSQLServerFormatter;

public class MSSQLServerSQLBuilder extends JDBCSQLBuilderBase {

    protected Formatter formatter = null;

    public static final String FORMAT_ST_GEOMFROMEWKB= "geometry::STGeomFromWKB({0}, {1})";

    public static final String FORMAT_ST_GEOMFROMWKB= "geometry::STGeomFromWKB({0}, {1})";

    public static final String FORMAT_ST_GEOMFROMTEXT= "geometry::STGeomFromText({0}, {1})";

    public static final String FORMAT_OPERATOR_ILIKE = "LOWER({0}) LIKE LOWER({1})";

    public static final String FORMAT_OPERATOR_NOTISNULL = "( ({0}) IS NOT NULL )";

//    public interface MSSQLServerSQLConfig extends SQLConfig {

//        public static final String ST_GeomFromTextEx = "ST_GeomFromTextEx";

//        public static final String ST_GeomFromWKBEx = "ST_GeomFromWKBEx";

//        public static final String ST_GeomFromEWKBEx = "ST_GeomFromEWKBEx";

//        public static final String ST_ExtentAggregateEx = "ST_ExtentAggregateEx";

//        public static final String ST_UnionAggregateEx = "ST_UnionAggregateEx";

//    }

    public MSSQLServerSQLBuilder(MSSQLServerHelper helper) {

        super(helper);

        this.defaultSchema = "dbo";

        this.allowAutomaticValues = true;

        this.geometrySupportType = this.helper.getGeometrySupportType();

        this.hasSpatialFunctions = this.helper.hasSpatialFunctions();

        STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table = null;

        STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_table = null;

        this.STMT_UPDATE_TABLE_STATISTICS_table ="UPDATE STATISTICS {0}";

        this.type_geometry = "GEOMETRY";

        this.constant_true = "1=1"; // TODO ?

        this.constant_false = "1<>1"; // TODO ?

        this.type_boolean = "BIT";

        this.type_double = "FLOAT"; //float con 53 bits de mantisa = float(54)

        this.type_numeric_p = "NUMERIC({0})";

        this.type_numeric_ps = "NUMERIC({0},{1})";

        this.type_bigdecimal = "NUMERIC({0},{1})";

        this.type_float = "REAL"; //float con 24 bits de mantisa = float(24)

        this.type_int = "INT";

        this.type_long = "BIGINT";        

        this.type_byte = "TINYINT";

        this.type_date = "DATE";

        this.type_time = "TIME";

        this.type_char = "CHAR(1)";

        this.type_string = "TEXT";

        this.type_string_p = "VARCHAR({0})";

        this.type_version = "VARCHAR(30)";

        this.type_URI = "TEXT";

        this.type_URL = "TEXT";

        this.type_FILE = "TEXT";

        this.type_FOLDER = "TEXT";

        this.type_bytearray = "VARBINARY";

        this.type_timestamp = "TIMESTAMP";

//       DID this.ST_SRID, "(({0}).STSrid)");

//       DID this.ST_AsText, "({0}).STAsText()");

//       DID this.ST_AsBinary, "({0}).STAsBinary()");

//       DID this.ST_AsEWKB, "({0}).STAsBinary()");

//       DID this.ST_ExtentAggregate, "geometry::EnvelopeAggregate({0})");

//       DID this.ST_UnionAggregate, "geometry::UnionAggregate({0})");

//       DID this.ST_Contains, "(({0}).STContains({1})=1)");

//       DID this.ST_Crosses, "(({0}).STCrosses({1})=1)");

//       DID this.ST_Disjoint, "(({0}).STDisjoint({1})=1)");

//       DID this.ST_Equals, "(({0}).STEquals({1})=1)");

//       DID this.ST_IsClosed, "(({0}).STIsClosed()=1)");

//       DID this.ST_Overlaps, "(({0}).STOverlaps({1})=1)");

//       DID this.ST_Touches, "(({0}).STTouches({1})=1)");

//       DID this.ST_Within, "(({0}).STWithin ({1})=1)");

//       DID this.ST_Envelope, "({0}).STEnvelope()");

//       DID this.ST_Intersects, "(({0}).STIntersects({1})=1)");

//       DID this.ST_GeomFromText, "geometry::STGeomFromText({0}, {1})");

//       DID this.ST_GeomFromWKB, "geometry::STGeomFromWKB({0}, {1})");

//       DID this.ST_GeomFromEWKB, "geometry::STGeomFromWKB({0}, {1})");

//       DID this.ST_Simplify, "({0}).Reduce({1})");

//        this.lcase, "LOWER({0})");

//        this.ucase, "UPPER({0})");

//

//        config.set(MSSQLServerSQLConfig.ST_ExtentAggregateEx, "{1}::EnvelopeAggregate({0})");

//        config.set(MSSQLServerSQLConfig.ST_UnionAggregateEx, "{1}::UnionAggregate({0})");

//        config.set(MSSQLServerSQLConfig.ST_GeomFromTextEx, "{2}::STGeomFromText({0}, {1})");

//        config.set(MSSQLServerSQLConfig.ST_GeomFromWKBEx, "{2}::STGeomFromWKB({0}, {1})");

//        config.set(MSSQLServerSQLConfig.ST_GeomFromEWKBEx, "{2}::STGeomFromWKB({0}, {1})");

    }

    @Override

    public MSSQLServerHelper getHelper() {

        return (MSSQLServerHelper) helper;

    }

    @Override

    protected Formatter formatter() {

        if( this.formatter==null ) {

            this.formatter = new MSSQLServerFormatter(this);

        }

        return this.formatter;

    }

    protected class MSSQLServerDropTableBuilder extends DropTableBuilderBase {

        @Override

           public List<String> toStrings(Formatter formatter) {

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

            sqls.add(

                    MessageFormat.format(

                            STMT_DROP_TABLE_table,

                            this.table.toString(formatter)

                    )

            );

            return sqls;

        }

    }

    public class MSSQLServerCreateIndexBuilder extends CreateIndexBuilderBase {

        private Envelope boundingBox;

        public MSSQLServerCreateIndexBuilder() {

            super();

            this.boundingBox = null;

        }

        public void setBoundingBox(Envelope boundingBox) {

            this.boundingBox = boundingBox;

        }

        private double getXMin() {

            // https://alastaira.wordpress.com/2011/07/26/determining-the-geographic-extent-of-spatial-features-in-a-sql-server-table/

            return Math.min(boundingBox.getUpperCorner().getX(), boundingBox.getLowerCorner().getX());

        }

        private double getYMin() {

            return Math.min(boundingBox.getUpperCorner().getY(), boundingBox.getLowerCorner().getY());

        }

        private double getXMax() {

            return Math.max(boundingBox.getUpperCorner().getX(), boundingBox.getLowerCorner().getX());

        }

        private double getYMax() {

            return Math.max(boundingBox.getUpperCorner().getY(), boundingBox.getLowerCorner().getY());

        }

        @Override

        public List<String> toStrings() {

            StringBuilder builder = new StringBuilder();

            if( this.isSpatial ) {

                builder.append("CREATE SPATIAL INDEX ");

                builder.append(as_identifier(this.indexName));

                builder.append(" ON ");

                builder.append(this.table.toString());

                builder.append(" ( ");

                boolean is_first_column = true;

                for( String column : this.columns) {

                    if( is_first_column ) {

                        is_first_column = false;

                    } else {

                        builder.append(", ");

                    }

                    builder.append(column);

                }

                builder.append(" ) ");

                builder.append("USING GEOMETRY_GRID ");

                builder.append("WITH( ");

                builder.append("BOUNDING_BOX  = ( ");

                builder.append("xmin  = ").append(this.getXMin()).append(", ");

                builder.append("ymin  = ").append(this.getYMin()).append(", ");

                builder.append("xmax  = ").append(this.getXMax()).append(", ");

                builder.append("ymax  = ").append(this.getYMax());

                builder.append(" )");

//                builder.append(", DROP_EXISTING = ON");

//                builder.append(", GRIDS  = ( LEVEL_1  = MEDIUM, LEVEL_2  = MEDIUM, LEVEL_3  = MEDIUM, LEVEL_4  = MEDIUM)");

//                builder.append(", CELLS_PER_OBJECT  = 16");

//                builder.append(", STATISTICS_NORECOMPUTE = OFF");

//                builder.append(", ALLOW_ROW_LOCKS = ON");

//                builder.append(", ALLOW_PAGE_LOCKS = ON");

                builder.append(" ) ");

            } else {

                builder.append("CREATE ");

                if( this.isUnique ) {

                    builder.append("UNIQUE ");

                }

                builder.append("INDEX ");

//                if( this.ifNotExist ) {

//                    builder.append("IF NOT EXISTS ");

//                }

                builder.append(as_identifier(this.indexName));

                builder.append(" ON ");

                builder.append(this.table.toString());

                builder.append(" ( ");

                boolean is_first_column = true;

                for( String column : this.columns) {

                    if( is_first_column ) {

                        is_first_column = false;

                    } else {

                        builder.append(", ");

                    }

                    builder.append(column);

                }

                builder.append(" )");

            }

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

            sqls.add(builder.toString());

            return sqls;

        }

    }

//    public class MSSQLServerParameter extends ParameterBase {

//

//        public MSSQLServerParameter() {

//            super();

//        }

//

//        @Override

//        public String toString() {

//            if( this.type == ParameterType.Geometry ) {

//                String spatialType = getHelper().getSpatialType(this.getName());

//                switch( config.getGeometryTypeSupport() ) {

//                    case EWKB:

//                        return MessageFormat.format(

//                                config.getString(MSSQLServerSQLConfig.ST_GeomFromEWKBEx),

//                                "?",

//                                String.valueOf(this.srs.toString()),

//                                custom(spatialType)

//                        );

//                    case NATIVE:

//                    case WKB:

//                        return MessageFormat.format(

//                                config.getString(MSSQLServerSQLConfig.ST_GeomFromWKBEx),

//                                "?",

//                                String.valueOf(this.srs.toString()),

//                                custom(spatialType)

//                        );

//                    case WKT:

//                    default:

//                        return MessageFormat.format(

//                                config.getString(MSSQLServerSQLConfig.ST_GeomFromTextEx),

//                                "?",

//                                String.valueOf(this.srs.toString()),

//                                custom(spatialType)

//                        );                        

//                }                            

//            }

//            return super.toString();

//        }

//    }

//    

//    public class MSSQLServerGeometryValue extends GeometryValueBase {

//        

//        public MSSQLServerGeometryValue(Geometry geometry, IProjection projection) {

//            super(geometry, projection);

//        }

//        

//        @Override

//        public String toString() {

//            try {

//                String spatialType = getHelper().getSpatialType();

//                switch( config.getGeometryTypeSupport() ) {

//                    case EWKB:

//                        return MessageFormat.format(

//                                config.getString(MSSQLServerSQLConfig.ST_GeomFromEWKBEx),

//                                bytearray(this.geometry.convertToEWKB()),

//                                String.valueOf(getSRSId(this.projection)),

//                                custom(spatialType)

//                        );

//                    case NATIVE:

//                    case WKB:

//                        return MessageFormat.format(

//                                config.getString(MSSQLServerSQLConfig.ST_GeomFromWKBEx),

//                                bytearray(this.geometry.convertToWKB()),

//                                String.valueOf(getSRSId(this.projection)),

//                                custom(spatialType)

//                        );

//                    case WKT:

//                    default:

//                        return MessageFormat.format(

//                                config.getString(MSSQLServerSQLConfig.ST_GeomFromTextEx),

//                                string(this.geometry.convertToWKT()),

//                                String.valueOf(getSRSId(this.projection)),

//                                custom(spatialType)

//                        );                        

//                }

//            } catch (Exception ex) {

//                throw new RuntimeException("Can't convert geometry to string.",ex);

//            }

//        }

//    }

    protected class MSSQLServerSelectBuilder extends SelectBuilderBase {

        @Override

        protected boolean isValid(StringBuilder message) {

            if( message == null ) {

                message = new StringBuilder();

            }

            if( this.has_offset() && !this.has_order_by() ) {

                // Algunos gestores de BBDD requieren que se especifique un

                // orden para poder usar OFFSET. Como eso parece buena idea para

                // asegurar que siempre tengamos los mismo resultados, lo exijimos

                // siempre.

                message.append("Can't use OFFSET without an ORDER BY.");

                return false;

            }

            return true;

        }

        @Override

        public String toString(Formatter<ExpressionBuilder.Value> formatter) {

            // MSSQLServer usa TOP en lugar de LIMIT y la sintaxis para OFFSET

            // es ligeramente distinta de la que hay en SelectBuilderBase

            StringBuilder builder = new StringBuilder();

            builder.append("SELECT ");

            if( this.distinct ) {

                builder.append("DISTINCT ");

            }

            if (this.has_limit() && !this.has_offset() ) {

                builder.append("TOP ");

                builder.append(this.limit);

                builder.append(" ");

           }

            boolean first = true;

            for (SelectColumnBuilder column : columns) {

                if (first) {

                    first = false;

                } else {

                    builder.append(", ");

                }

                builder.append(column.toString());

            }

            if (this.has_from()) {

                builder.append(" FROM ");

                builder.append(this.from.toString(formatter));

            }

            if (this.has_where()) {

                builder.append(" WHERE ");

                builder.append(this.where.toString(formatter));

            }

            if( this.has_order_by() ) {

                builder.append(" ORDER BY ");

                first = true;

                for (OrderByBuilder item : this.order_by) {

                    if (first) {

                        first = false;

                    } else {

                        builder.append(", ");

                    }

                    builder.append(item.toString());

                }

            }

            if (this.has_offset()) {

                // Require SQLSeerver >= 2012

                builder.append(" OFFSET ");

                builder.append(this.offset);

                builder.append(" ROWS");

                if( this.has_limit() ) {

                    builder.append(" FETCH NEXT ");

                    builder.append(this.limit);

                    builder.append(" ROWS ONLY ");

                }

            }

            return builder.toString();

        }

    }

    protected class MSSQLServerCreateTableBuilder extends CreateTableBuilderBase {

        @Override

//        List<String> toStrings(Formatter formatter)

        public List<String> toStrings(Formatter formatter) {

            //

            // Respecto al base cambia la declaracion de campo automaticos

            // SQLServer usa IDENTITY en lugar de SERIAL.

            //

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

            StringBuilder builder = new StringBuilder();

            builder.append("CREATE TABLE ");

            builder.append(this.table.toString());

            builder.append(" (");

            boolean first = true;

            for (ColumnDescriptor column : columns) {

                if (first) {

                    first = false;

                } else {

                    builder.append(", ");

                }

                builder.append(as_identifier(column.getName()));

                builder.append(" ");

                if( column.isAutomatic() && column.getType() == DataTypes.INT ) {

                    builder.append("INT");

                    builder.append(" IDENTITY(1,1)");

                } else if( column.isAutomatic() && column.getType() == DataTypes.LONG ) {

                    builder.append("BIGINT");

                    builder.append(" IDENTITY(1,1)");

                } else {

                    builder.append(

                        sqltype(

                            column.getType(), 

                            column.getPrecision(), 

                            column.getSize(),

                            column.getGeometryType(), 

                            column.getGeometrySubtype()

                        )

                    );

                }

                if (column.getDefaultValue() == null) {

                    if (column.allowNulls()) {

                        builder.append(" DEFAULT NULL");

                    }

                } else {

                    builder.append(" DEFAULT '");

                    builder.append(column.getDefaultValue().toString());

                    builder.append("'");

                }

                if (column.allowNulls()) {

                    builder.append(" NULL");

                } else {

                    builder.append(" NOT NULL");

                }

                if (column.isPrimaryKey()) {

                    builder.append(" PRIMARY KEY");

                }

            }

            builder.append(" )");

            sqls.add(builder.toString());

            return sqls;

        }

    }

    @Override

    protected SelectBuilder createSelectBuilder() {

        return new MSSQLServerSelectBuilder();

    }

    @Override

    protected CreateTableBuilder createCreateTableBuilder() {

        return new MSSQLServerCreateTableBuilder();

    }

    protected DropTableBuilder createDropTableBuilder() {

        return new MSSQLServerDropTableBuilder();

    }

    @Override

    protected CreateIndexBuilder createCreateIndexBuilder() {

        return new MSSQLServerCreateIndexBuilder();

    }

    @Override

    public String as_identifier(String id) {

        // En SQLServer se aceptan las comillas dobles pero se prefiere

        // corchetes [xx]. Asi que si hay comillas dobles las quitamos

        // y ponemos los corchetes.

        String quote = this.quote_for_identifiers();

        if (id.startsWith(quote)) {

            id = id.substring(1, id.length()-1);

        } else if( id.startsWith("[") ) {

            return id;

        }

        if( id.contains("(") ) {

            LOGGER.warn("Suspicious use of 'identifier' in sql.");

        }

        return "[" + id + "]";

    }

//    @Override

//    public GeometryValue geometry(Geometry geom, IProjection projection) {

//        return new MSSQLServerGeometryValue(geom, projection);

//    }

//    @Override

//    public Parameter parameter() {

//        return new MSSQLServerParameter();

//    }

//    @Override

//    public Function ST_ExtentAggregate(Value geom) {

//        String spatialType = getHelper().getSpatialType();

//        return builtin_function("ST_ExtentAggregate", 

//                config.getString(MSSQLServerSQLConfig.ST_ExtentAggregateEx), 

//                geom,

//                custom(spatialType)

//        );

//    }

//

//    @Override

//    public Function ST_UnionAggregate(Value geom) {

//        String spatialType = getHelper().getSpatialType();

//        return builtin_function("ST_UnionAggregate", 

//                config.getString(MSSQLServerSQLConfig.ST_UnionAggregateEx), 

//                geom,

//                custom(spatialType)

//        );

//    }

//

//    @Override

//    public Function ST_GeomFromText(Value geom, Value crs) {

//        String spatialType = getHelper().getSpatialType();

//        return builtin_function("ST_GeomFromText", 

//                config.getString(MSSQLServerSQLConfig.ST_GeomFromTextEx), 

//                geom, 

//                crs,

//                custom(spatialType)

//        );

//    }

//

//    @Override

//    public Function ST_GeomFromWKB(Value geom, Value crs) {

//        String spatialType = getHelper().getSpatialType();

//        return builtin_function("ST_GeomFromWKB", 

//                config.getString(MSSQLServerSQLConfig.ST_GeomFromWKBEx), 

//                geom, 

//                crs,

//                custom(spatialType)

//        );

//    }

//

//    @Override

//    public Function ST_GeomFromEWKB(Value geom, Value crs) {

//        String spatialType = getHelper().getSpatialType();

//        return builtin_function("ST_GeomFromEWKB", 

//                config.getString(MSSQLServerSQLConfig.ST_GeomFromEWKBEx), 

//                geom, 

//                crs,

//                custom(spatialType)

//        );

//    }

}

package org.gvsig.mssqlserver.dal;

import org.gvsig.fmap.dal.DataServerExplorer;

import org.gvsig.fmap.dal.DataServerExplorerParameters;

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

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

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

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

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

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

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

public class MSSQLServerExplorerFactory extends JDBCServerExplorerFactory {

    private static final String NAME = MSSQLServerLibrary.NAME;

    public MSSQLServerExplorerFactory() {

        super(

                NAME,

                "Microsoft SQL Server"

        );

    }

    @Override

    public JDBCServerExplorer create(

            DataServerExplorerParameters parameters, 

            DataServerExplorerProviderServices providerServices

        ) throws InitializeException {

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

        JDBCServerExplorer server = helper.createServerExplorer(

                (JDBCServerExplorerParameters) parameters, 

                providerServices

        );

        return server;

    }

    @Override

    public JDBCServerExplorerParameters createParameters() {

        JDBCServerExplorerParameters params = new MSSQLServerExplorerParameters();

        return params;    

    }

}

/* gvSIG. Geographic Information System of the Valencian Government

 *

 * Copyright (C) 2007-2008 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 2

 * 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.apache.commons.lang3.StringUtils;

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

public class MSSQLServerNewStoreParameters extends JDBCNewStoreParameters {

    public MSSQLServerNewStoreParameters() {

        super(

            MSSQLServerLibrary.NAME + "NewStoreParameters", 

            MSSQLServerLibrary.NAME

        );

    }

    @Override

    public String getUrl() {

        String url = super.getUrl();

        if( StringUtils.isEmpty(url) ) {

            url = MSSQLServerHelper.getConnectionURL(this);

            this.setUrl(url);

        }

        return url;

    }

}

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.OperationsFactory.TableReference;

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

    }

    public MSSQLServerCreateTableOperation(

            JDBCHelper helper,

            TableReference table,

            FeatureType type,

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

            List<String> additionalSQLs

        ) {

        super(helper, table, type, userAndPrivileges, additionalSQLs);

    }

    @Override

    protected List<String> buildCreateIndexesSQL(

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

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

public class MSSQLServerCanModifyTableOperation extends CanModifyTableOperation {

    public MSSQLServerCanModifyTableOperation(

            JDBCHelper helper

        ) {

        this(helper, null);

    }

    public MSSQLServerCanModifyTableOperation(

            JDBCHelper helper,

            TableReference table

        ) {

        super(helper,table);

    }

    private String getFullTableName( 

            TableReference table

    ) {

        StringBuilder builder = new StringBuilder();

        if( table.hasDatabase() ) {

            builder.append(table.getDatabase());

        }

        if( table.hasSchema() ) {

            if( builder.length()>0 ) {

                builder.append(".");

            }

            builder.append(table.getSchema());

        }

        if( table.hasTable() ) {

            if( builder.length()>0 ) {

                builder.append(".");

            }

            builder.append(table.getTable());

        }

        return builder.toString();

    }

    @Override

    public boolean canModifyTable(Connection conn,

            TableReference table

        ) throws DataException {

        String sql = "SELECT count(*) " + 

                     "FROM fn_my_permissions('"+getFullTableName(table)+"', '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.OperationsFactory.TableReference;

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;

@SuppressWarnings("UseSpecificCatch")

public class MSSQLServerPerformChangesOperation extends PerformChangesOperation {

    public MSSQLServerPerformChangesOperation(JDBCHelper helper) {

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

    }

    public MSSQLServerPerformChangesOperation(JDBCHelper helper,

            TableReference table,

            FeatureType featureType,

            Iterator<FeatureReferenceProviderServices> deleteds,

            Iterator<FeatureProvider> inserteds,

            Iterator<FeatureProvider> updateds,

            Iterator<FeatureStoreProvider.FeatureTypeChanged> featureTypesChanged) {

        super(helper, table, featureType, deleteds, inserteds, updateds, featureTypesChanged);

    }

    protected MSSQLServerHelper getHelper() {

        return (MSSQLServerHelper) this.helper;

    }

    @Override

    protected List<String> buildCreateIndexSQL(

            TableReference 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(),

                                table.getDatabase(),

                                table.getSchema(),

                                table.getTable(),

                                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(table.getDatabase())

                        .schema(table.getSchema())

                        .name(table.getTable());

                    return create_index.toStrings();

                }

            }

        }

        return new ArrayList<>();

    }    

}

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.expressionevaluator.ExpressionBuilder;

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.OperationsFactory.TableReference;

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,

            TableReference table,

            List<String> primaryKeys,

            String defaultGeometryColumn,

            IProjection crs

    ) {

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

    }

    @Override

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

        super.fetch(featureType, conn, 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();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.select().limit(1);

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

                expbuilder.ST_SRID(expbuilder.column(columnName))

        );

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

                expbuilder.and(

                        expbuilder.not_is_null(expbuilder.column(columnName)),

                        expbuilder.not_is_null(expbuilder.ST_SRID(expbuilder.column(columnName)))

                )

        );

        sqlbuilder.select().from().table()

                .database(this.getTable().getDatabase())

                .schema(this.getTable().getSchema())

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

        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, TableReference table, List<String> primaryKeys, String defaultGeometryField, IProjection crs) {

        return new MSSQLServerFetchFeatureTypeOperation(

                helper, type, table, primaryKeys, 

                defaultGeometryField, crs

        );

    }

    @Override

    public CanModifyTableOperation createCanModifyTableOperation(TableReference table) {

        return new MSSQLServerCanModifyTableOperation(

                helper, table

        );

    }