Application: gvSIG desktop » gvSIG data provider for MS SQLServer

//package org.gvsig.fmap.dal.store.jdbc2;

//

//import java.util.ArrayList;

//import java.util.List;

//import org.apache.commons.lang3.ArrayUtils;

//import org.cresques.cts.IProjection;

//import org.gvsig.fmap.crs.CRSFactory;

//import org.gvsig.expressionevaluator.ExpressionBuilder;

//import org.gvsig.expressionevaluator.ExpressionBuilder.Parameter;

//import org.gvsig.expressionevaluator.ExpressionBuilder.Variable;

//import org.gvsig.fmap.dal.SQLBuilder;

//import org.gvsig.fmap.dal.SQLBuilder.Privilege;

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

//import org.gvsig.fmap.geom.DataTypes;

//import org.gvsig.fmap.geom.Geometry;

//import org.gvsig.fmap.geom.GeometryLocator;

//import org.gvsig.fmap.geom.GeometryManager;

//import org.gvsig.fmap.geom.primitive.Polygon;

//import org.gvsig.tools.junit.AbstractLibraryAutoInitTestCase;

//

//public class SQLBuilderTest extends AbstractLibraryAutoInitTestCase {

//    

//    @Override

//    protected void doSetUp() throws Exception {

//

//    }

////    

////    List<String> getVariableNames(ExpressionBuilder builder) {

////        List<String> vars = new ArrayList<>();

////        for (Variable var : builder.getVariables()) {

////            vars.add(var.getName());

////        }

////        return vars;

////    }

////    

////    List<String> getParameterNames(ExpressionBuilder builder) {

////        List<String> params = new ArrayList<>();

////        for (Parameter param : builder.getParameters()) {

////            String s;

////            switch(param.getType()) {

////                case Constant:

////                    Object value = param.getValue();

////                    if( value==null ) {

////                        s = "null";

////                    } else if( value instanceof String ) {

////                        s = "'" + (String)value + "'";

////                    } else {

////                        s = value.toString();

////                    }    

////                    break;

////                case Geometry:

////                case Variable:

////                default:

////                    s = "\"" + param.getName() + "\"";

////            }

////            params.add(s);

////        }

////        return params;

//    }

//    

// 

//    public void testCalulateEnvelope() throws Exception {

//        GeometryManager geometryManager = GeometryLocator.getGeometryManager();

//        IProjection proj = CRSFactory.getCRS("EPSG:4326");

//        

//        Polygon limit = geometryManager.createPolygon(Geometry.SUBTYPES.GEOM2D);

//        limit.addVertex(0, 0);

//        limit.addVertex(0, 100);

//        limit.addVertex(100, 100);

//        limit.addVertex(100, 0);

//        limit.addVertex(0, 0);

//        

//        SQLBuilder builder = new SQLBuilderBase();

//        

//        builder.select().column().value(

//            builder.getAsGeometry(

//              builder.ST_ExtentAggregate(

//                builder.column("the_geom")

//              )

//            )

//        ).as("envelope");

//        builder.select().from().table().database("master").schema("dbo").name("test1");

//        builder.select().where().set(

//            builder.ST_Intersects(

//                builder.ST_Envelope(

//                    builder.column("the_geom")

//                ),

//                builder.geometry(limit, proj)

//            )

//        );

//        builder.select().where().and(

//                builder.custom("x = 27")

//        );

//        

//        // SELECT ST_AsBinary(ST_Extent("the_geom")) AS "envelope" FROM "master"."dbo"."test1" WHERE ST_Intersects((ST_Envelope("the_geom")), (ST_GeomFromText('POLYGON ((0 0, 0 100, 100 100, 100 0, 0 0))', (4326)))) AND x = 27

//        

//        System.out.println("# Test:: testCalulateEnvelope");

//        System.out.println("# SQL:: " + builder.toString());

//        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

//        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

//        assertEquals(

//                "SELECT ST_AsBinary(ST_Extent(\"the_geom\")) AS \"envelope\" FROM \"master\".\"dbo\".\"test1\" WHERE ST_Intersects((ST_Envelope(\"the_geom\")), (ST_GeomFromText('POLYGON ((0 0, 0 100, 100 100, 100 0, 0 0))', (4326)))) AND x = 27",

//                builder.toString()

//        );

//        assertEquals(

//                "[the_geom]",

//                ArrayUtils.toString(getVariableNames(builder))

//        );

//        assertEquals(

//                "[]",

//                ArrayUtils.toString(getParameterNames(builder))

//        );

//    }

//

//    public void testCount() throws Exception {

//        SQLBuilder builder = new SQLBuilderBase();

//        

//        builder.select().column().value(builder.count().all());

//        builder.select().from().table().database("master").schema("dbo").name("test1");

//        builder.select().from().subquery(null);

//        builder.select().where().set( builder.custom("pp = 200"));

//

//        System.out.println("# Test:: testCount");

//        System.out.println("# SQL:: " + builder.toString());

//        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

//        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

//

//        //# Test:: testCount

//        //# SQL:: SELECT COUNT(*) FROM "master"."dbo"."test1" WHERE pp = 200

//        //# Variables:: []

//        //# Parametros:: []

//

//        assertEquals(

//                "SELECT COUNT(*) FROM \"master\".\"dbo\".\"test1\" WHERE pp = 200",

//                builder.toString()

//        );

//        assertEquals(

//                "[]",

//                ArrayUtils.toString(getVariableNames(builder))

//        );

//        assertEquals(

//                "[]",

//                ArrayUtils.toString(getParameterNames(builder))

//        );

//    }

//    

//    public void testCreateTable() throws Exception {

//        SQLBuilder builder = new SQLBuilderBase();

//

//        builder.create_table().table().database("master").schema("dbo").name("test1");

//        builder.create_table().add_column(

//                "name",

//                DataTypes.STRING,

//                45,

//                0,

//                false,

//                false,

//                true,

//                false,

//                null

//        );

//        builder.create_table().add_column(

//                "id",

//                DataTypes.INT,

//                0,

//                0,

//                true,

//                false,

//                false,

//                true,

//                0

//        );

//        builder.create_table().add_column(

//                "geom",

//                DataTypes.GEOMETRY,

//                0,

//                0,

//                false,

//                false,

//                true,

//                false,

//                null

//        );

//

//        

//        // CREATE TABLE "master"."dbo"."test1" ("name" VARCHAR(45) DEFAULT NULL NULL, "id" SERIAL DEFAULT '0' NOT NULL PRIMARY KEY, "geom" TEXT DEFAULT NULL NULL )

//        System.out.println("# Test:: testCreateTable");

//        System.out.println("# SQL:: " + builder.toString());

//        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

//        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

//        assertEquals(

//                "CREATE TABLE \"master\".\"dbo\".\"test1\" (\"name\" VARCHAR(45) DEFAULT NULL NULL, \"id\" SERIAL DEFAULT '0' NOT NULL PRIMARY KEY, \"geom\" TEXT DEFAULT NULL NULL )",

//                builder.toString()

//        );

//        assertEquals(

//                "[]",

//                ArrayUtils.toString(getVariableNames(builder))

//        );

//        assertEquals(

//                "[]",

//                ArrayUtils.toString(getParameterNames(builder))

//        );

//    }

//

//    public void testDropTable() throws Exception {

//        SQLBuilder builder = new SQLBuilderBase();

//        

//        builder.drop_table().table().database("master").schema("dbo").name("test1");

//

//        // DROP TABLE "master"."dbo"."test1"; DELETE FROM GEOMETRY_COLUMNS WHERE f_table_schema = 'dbo' AND f_table_name = 'test1'

//        

//        System.out.println("# Test:: testDropTable");

//        System.out.println("# SQL:: " + builder.toString());

//        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

//        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

//        assertEquals(

//                "DROP TABLE \"master\".\"dbo\".\"test1\"; DELETE FROM GEOMETRY_COLUMNS WHERE f_table_schema = 'dbo' AND f_table_name = 'test1'",

//                builder.toString()

//        );

//        assertEquals(

//                "[]",

//                ArrayUtils.toString(getVariableNames(builder))

//        );

//        assertEquals(

//                "[]",

//                ArrayUtils.toString(getParameterNames(builder))

//        );

//    }

//    

//    public void testFetchFeatureProviderByReference() throws Exception {

//        SQLBuilder builder = new SQLBuilderBase();

//        

//        String value = "yoyo";

//        builder.select().column().name("name");

//        builder.select().column().name("id");

//        builder.select().column().name("geom").as_geometry();

//        builder.select().from().table().database("master").schema("dbo").name("test1");

//        builder.select().where().set(

//            builder.eq(

//                builder.column("name"),

//                builder.parameter(value).as_constant()

//            )

//        );

//        builder.select().limit(1);

//

//        // SELECT "name", "id", ST_AsBinary("geom") FROM "master"."dbo"."test1" WHERE ( ("name") = (?) ) LIMIT 1

//

//        System.out.println("# Test:: testFetchFeatureProviderByReference");

//        System.out.println("# SQL:: " + builder.toString());

//        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

//        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

//        assertEquals(

//                "SELECT \"name\", \"id\", ST_AsBinary(\"geom\") FROM \"master\".\"dbo\".\"test1\" WHERE ( (\"name\") = (?) ) LIMIT 1",

//                builder.toString()

//        );

//        assertEquals(

//                "[geom, id, name]",

//                ArrayUtils.toString(getVariableNames(builder))

//        );

//        assertEquals(

//                "['yoyo']",

//                ArrayUtils.toString(getParameterNames(builder))

//        );

//    }

//    

//    public void testFetchFeatureType() throws Exception {

//        SQLBuilder builder = new SQLBuilderBase();

//

//        builder.select().column().all();

//        builder.select().from().table().database("master").schema("dbo").name("test1");

//        builder.select().limit(1);

//

//        System.out.println("# Test:: testFetchFeatureType");

//        System.out.println("# SQL:: " + builder.toString());

//        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

//        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

//        

//        //# Test:: testFetchFeatureType

//        //# SQL:: SELECT * FROM [master].[dbo].[test1] LIMMIT 1

//        //# Variables:: []

//        //# Parametros:: []        

//        

//        assertEquals(

//                "SELECT * FROM \"master\".\"dbo\".\"test1\" LIMIT 1",

//                builder.toString()

//        );

//        assertEquals(

//                "[]",

//                ArrayUtils.toString(getVariableNames(builder))

//        );

//        assertEquals(

//                "[]",

//                ArrayUtils.toString(getParameterNames(builder))

//        );

//    }

//        

//    public void testPerformDeletes() throws Exception {

//        SQLBuilder builder = new SQLBuilderBase();

//

//        builder.delete().table().database("master").schema("dbo").name("test1");

//        builder.delete().where().and(

//            builder.eq( 

//                builder.column("id1"),

//                builder.parameter("id1").as_variable()

//            )

//        );

//        builder.delete().where().and(

//            builder.eq( 

//                builder.column("id2"),

//                builder.parameter("id2").as_variable()

//            )

//        );

//

//        // DELETE FROM "master"."dbo"."test1" WHERE ( ("id1") = (?) ) AND ( ("id2") = (?) )

//

//        System.out.println("# Test:: testPerformDeletes");

//        System.out.println("# SQL:: " + builder.toString());

//        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

//        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

//        assertEquals(

//                "DELETE FROM \"master\".\"dbo\".\"test1\" WHERE ( (\"id1\") = (?) ) AND ( (\"id2\") = (?) )",

//                builder.toString()

//        );

//        assertEquals(

//                "[id1, id2]",

//                ArrayUtils.toString(getVariableNames(builder))

//        );

//        assertEquals(

//                "[\"id1\", \"id2\"]",

//                ArrayUtils.toString(getParameterNames(builder))

//        );

//    }

//

//    public void testPerformInserts1() throws Exception {

//        IProjection proj = CRSFactory.getCRS("EPSG:4326");

//

//        SQLBuilder builder = new SQLBuilderBase();

//

//        builder.insert().table().database("master").schema("dbo").name("test1");

//        builder.insert().column().name("id").with_value(builder.parameter("id"));

//        builder.insert().column().name("name").with_value(builder.parameter("name"));

//        builder.insert().column().name("geom").with_value(builder.parameter("geom").as_geometry_variable().srs(proj));

//        

//        // INSERT INTO "master"."dbo"."test1" ( "id", "name", "geom" ) VALUES ( ?, ?, ST_GeomFromText(?, (4326)) )

//        

//        System.out.println("# Test:: testPerformInserts1");

//        System.out.println("# SQL:: " + builder.toString());

//        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

//        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

//        assertEquals(

//                "INSERT INTO \"master\".\"dbo\".\"test1\" ( \"id\", \"name\", \"geom\" ) VALUES ( ?, ?, ST_GeomFromText(?, (4326)) )",

//                builder.toString()

//        );

//        assertEquals(

//                "[geom, id, name]",

//                ArrayUtils.toString(getVariableNames(builder))

//        );

//        assertEquals(

//                "[\"id\", \"name\", \"geom\"]",

//                ArrayUtils.toString(getParameterNames(builder))

//        );

//    }

//

//    public void testPerformInserts2() throws Exception {

//        IProjection proj = CRSFactory.getCRS("EPSG:4326");

//

//        SQLBuilder builder = new SQLBuilderBase();

//

//        builder.insert().table().database("master").schema("dbo").name("test1");

//        builder.insert().column().name("id").with_value(builder.parameter("id"));

//        builder.insert().column().name("name").with_value(builder.parameter("name"));

//        builder.insert().column().name("geom").with_value(builder.parameter("geom").as_geometry_variable().srs(builder.parameter().value(proj)));

//        

//        // INSERT INTO "master"."dbo"."test1" ( "id", "name", "geom" ) VALUES ( ?, ?, ST_GeomFromText(?, (?)) )

//        

//        System.out.println("# Test:: testPerformInserts2");

//        System.out.println("# SQL:: " + builder.toString());

//        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

//        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

//        assertEquals(

//                "INSERT INTO \"master\".\"dbo\".\"test1\" ( \"id\", \"name\", \"geom\" ) VALUES ( ?, ?, ST_GeomFromText(?, (?)) )",

//                builder.toString()

//        );

//        assertEquals(

//                "[geom, id, name]",

//                ArrayUtils.toString(getVariableNames(builder))

//        );

//        assertEquals(

//                "[\"id\", \"name\", \"geom\", 4326]",

//                ArrayUtils.toString(getParameterNames(builder))

//        );

//    }

//

//    public void testPerformUpdates() throws Exception {

//        IProjection proj = CRSFactory.getCRS("EPSG:4326");

//

//        SQLBuilder builder = new SQLBuilderBase();

//

//        builder.update().table().database("master").schema("dbo").name("test1");

//        builder.update().where().and(

//            builder.eq(

//                builder.column("id"), 

//                builder.parameter("id").as_variable()

//            )

//        );

//        builder.update().column().name("name").with_value(builder.parameter("name"));

//        builder.update().column().name("geom").with_value(

//                builder.parameter("geom").as_geometry_variable().srs( builder.parameter().value(proj)) 

//        );

//

//        // UPDATE "master"."dbo"."test1" SET "name" = ?, "geom" = ST_GeomFromText(?, (?)) WHERE ( ("id") = (?) )

//

//        System.out.println("# Test:: testPerformUpdates");

//        System.out.println("# SQL:: " + builder.toString());

//        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

//        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

//        assertEquals(

//                "UPDATE \"master\".\"dbo\".\"test1\" SET \"name\" = ?, \"geom\" = ST_GeomFromText(?, (?)) WHERE ( (\"id\") = (?) )",

//                builder.toString()

//        );

//        assertEquals(

//                "[geom, id, name]",

//                ArrayUtils.toString(getVariableNames(builder))

//        );

//        assertEquals(

//                "[\"name\", \"geom\", 4326, \"id\"]",

//                ArrayUtils.toString(getParameterNames(builder))

//        );

//    }

//

//    public void testGrant1() throws Exception {

//

//        SQLBuilder builder = new SQLBuilderBase();

//

//        builder.grant().table().database("master").schema("dbo").name("test1");

//        builder.grant().role("prueba").select().insert().update();

//        builder.grant().role("gis").all();

//                

//        

//        System.out.println("# Test:: testGrant1");

//        System.out.println("# SQL:: " + builder.toString());

//        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

//        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

//        assertEquals(

//                "",

//                builder.toString()

//        );

//        assertEquals(

//                "[]",

//                ArrayUtils.toString(getVariableNames(builder))

//        );

//        assertEquals(

//                "[]",

//                ArrayUtils.toString(getParameterNames(builder))

//        );

//    }

//

//    public void testGrant2() throws Exception {

//

//        SQLBuilder builder = new SQLBuilderBase();

//

//        builder.grant().table().database("master").schema("dbo").name("test1");

//        builder.grant().role("prueba").privilege(Privilege.SELECT)

//                .privilege(Privilege.INSERT)

//                .privilege(Privilege.UPDATE);

//        builder.grant().role("gis").privilege(Privilege.ALL);

//                

//        

//        System.out.println("# Test:: testGrant2");

//        System.out.println("# SQL:: " + builder.toString());

//        System.out.println("# Variables:: " + ArrayUtils.toString(getVariableNames(builder)));

//        System.out.println("# Parametros:: " + ArrayUtils.toString(getParameterNames(builder)));

//        assertEquals(

//                "",

//                builder.toString()

//        );

//        assertEquals(

//                "[]",

//                ArrayUtils.toString(getVariableNames(builder))

//        );

//        assertEquals(

//                "[]",

//                ArrayUtils.toString(getParameterNames(builder))

//        );

//    }

//

//

//}

package org.gvsig.mssqlserver.dal;

import java.util.ArrayList;

import java.util.List;

import org.apache.commons.lang3.ArrayUtils;

import org.cresques.cts.IProjection;

import org.gvsig.fmap.crs.CRSFactory;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import org.gvsig.expressionevaluator.ExpressionBuilder.Parameter;

import org.gvsig.expressionevaluator.ExpressionBuilder.Variable;

import org.gvsig.fmap.dal.SQLBuilder;

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

import org.gvsig.fmap.dal.store.db.DBHelper;

import org.gvsig.fmap.geom.DataTypes;

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.GeometryLocator;

import org.gvsig.fmap.geom.GeometryManager;

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

import static org.gvsig.mssqlserver.dal.MSSQLServerLibrary.NAME;

import org.gvsig.tools.junit.AbstractLibraryAutoInitTestCase;

public class MSSQLServerSQLBuilderTest extends AbstractLibraryAutoInitTestCase {

    private MSSQLServerHelper helper;

    @Override

    protected void doSetUp() throws Exception {

        DataManagerProviderServices dataman = 

                (DataManagerProviderServices) DALLocator.getDataManager();

        DBHelper.registerParametersDefinition(

                NAME + "StoreParameters",

                MSSQLServerStoreParameters.class,

                dataman.getResourceAsStream(this, NAME + "Parameters.xml")

        );

        MSSQLServerStoreParameters params = new MSSQLServerStoreParameters();

        params.setHost("127.0.0.1");

        params.setDynValue("InstanceName", "SQLEXPRESS");

        params.setDBName("master");

        params.setSchema("dbo");

        params.setTable("test1");

        params.setPort(1433);

        params.setUser("sa");

        params.setPassword("123");

        helper = new MSSQLServerHelper(params);

    }

    public SQLBuilder createSQLBuilder() {

        return new MSSQLServerSQLBuilder(helper);

    }

    public void testCalulateEnvelope() throws Exception {

        GeometryManager geometryManager = GeometryLocator.getGeometryManager();

        IProjection proj = CRSFactory.getCRS("EPSG:4326");

        Polygon limit = geometryManager.createPolygon(Geometry.SUBTYPES.GEOM2D);

        limit.addVertex(0, 0);

        limit.addVertex(0, 100);

        limit.addVertex(100, 100);

        limit.addVertex(100, 0);

        limit.addVertex(0, 0);

        SQLBuilder sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

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

            expbuilder.as_geometry(

              expbuilder.ST_ExtentAggregate(

                expbuilder.column("the_geom")

              )

            )

        ).as("envelope");

        sqlbuilder.select().from().table().database("master").schema("dbo").name("test1");

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

            expbuilder.ST_Intersects(

                expbuilder.ST_Envelope(

                    expbuilder.column("the_geom")

                ),

                expbuilder.geometry(limit, proj)

            )

        );

        sqlbuilder.select().where().and(

                expbuilder.custom("x = 27").add( expbuilder.variable("x") )

        );

        System.out.println("# Test:: testCalulateEnvelope");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        //# Test:: testCalulateEnvelope

        //# SQL:: SELECT (geometry::EnvelopeAggregate([the_geom])).STAsBinary() AS [envelope] FROM [master].[dbo].[test1] WHERE (([the_geom]).STEnvelope()).STIntersects(geometry::STGeomFromWKB(0x000000000300000001000000050000000000000000000000000000000000000000000000004059000000000000405900000000000040590000000000004059000000000000000000000000000000000000000000000000000000000000, 4326)) AND x = 27

        //# Variables:: [the_geom, x]

        //# Parametros:: []    

        assertEquals(

                "SELECT (geometry::EnvelopeAggregate([the_geom])).STAsBinary() AS [envelope] FROM [master].[dbo].[test1] WHERE (([the_geom]).STEnvelope()).STIntersects(geometry::STGeomFromWKB(0x000000000300000001000000050000000000000000000000000000000000000000000000004059000000000000405900000000000040590000000000004059000000000000000000000000000000000000000000000000000000000000, 4326)) AND x = 27",

                sqlbuilder.toString()

        );

        assertEquals(

                "[the_geom, x]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testCount() throws Exception {

        SQLBuilder sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.select().column().value(sqlbuilder.count().all());

        sqlbuilder.select().from().table().database("master").schema("dbo").name("test1");

        sqlbuilder.select().from().subquery(null);

        sqlbuilder.select().where().set( expbuilder.custom("pp = 200").add(expbuilder.variable("pp")));

        System.out.println("# Test:: testCount");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        //# Test:: testCount

        //# SQL:: SELECT COUNT(*) FROM [master].[dbo].[test1] WHERE pp = 200

        //# Variables:: []

        //# Parametros:: []

        assertEquals(

                "SELECT COUNT(*) FROM [master].[dbo].[test1] WHERE pp = 200",

                sqlbuilder.toString()

        );

        assertEquals(

                "[pp]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testUpdateStatistics() throws Exception {

        SQLBuilder sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.update_table_statistics().table().database("master").schema("dbo").name("test1");

        System.out.println("# Test:: testUpdateStatistics");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        //# Test:: testUpdateStatistics

        //# SQL:: UPDATE STATISTICS [master].[dbo].[test1]

        //# Variables:: []

        //# Parametros:: []

        assertEquals(

                "UPDATE STATISTICS [master].[dbo].[test1]",

                sqlbuilder.toString()

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testCreateTable() throws Exception {

        SQLBuilder sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.create_table().table().database("master").schema("dbo").name("test1");

        sqlbuilder.create_table().add_column(

                "name",

                DataTypes.STRING,

                45,

                0,

                false,

                false,

                true,

                false,

                null

        );

        sqlbuilder.create_table().add_column(

                "id",

                DataTypes.INT,

                0,

                0,

                true,

                false,

                false,

                true,

                0

        );

        sqlbuilder.create_table().add_column(

                "geom",

                DataTypes.GEOMETRY,

                0,

                0,

                false,

                false,

                true,

                false,

                null

        );

        System.out.println("# Test:: testCreateTable");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        //# Test:: testCreateTable

        //# SQL:: CREATE TABLE [master].[dbo].[test1] ([name] VARCHAR(45) DEFAULT NULL NULL, [id] INT IDENTITY(1,1) DEFAULT '0' NOT NULL PRIMARY KEY, [geom] GEOMETRY DEFAULT NULL NULL )

        //# Variables:: []

        //# Parametros:: []

        assertEquals(

                "CREATE TABLE [master].[dbo].[test1] ([name] VARCHAR(45) DEFAULT NULL NULL, [id] INT IDENTITY(1,1) DEFAULT '0' NOT NULL PRIMARY KEY, [geom] GEOMETRY DEFAULT NULL NULL )",

                sqlbuilder.toString()

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testDropTable() throws Exception {

        SQLBuilder sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.drop_table().table().database("master").schema("dbo").name("test1");

        System.out.println("# Test:: testDropTable");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        //# Test:: testDropTable

        //# SQL:: DROP TABLE [master].[dbo].[test1]

        //# Variables:: []

        //# Parametros:: []

        assertEquals(

                "DROP TABLE [master].[dbo].[test1]",

                sqlbuilder.toString()

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testFetchFeatureProviderByReference() throws Exception {

        SQLBuilder sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        String value = "yoyo";

        sqlbuilder.select().column().name("name");

        sqlbuilder.select().column().name("id");

        sqlbuilder.select().column().name("geom").as_geometry();

        sqlbuilder.select().from().table().database("master").schema("dbo").name("test1");

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

            expbuilder.eq(

                expbuilder.column("name"),

                expbuilder.parameter(value).as_constant()

            )

        );

        sqlbuilder.select().limit(1);

        System.out.println("# Test:: testFetchFeatureProviderByReference");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        //# Test:: testFetchFeatureProviderByReference

        //# SQL:: SELECT TOP 1 [name], [id], ([geom]).STAsBinary() FROM [master].[dbo].[test1] WHERE ( ([name]) = (?) )

        //# Variables:: [geom, id, name]

        //# Parametros:: ['yoyo']

        assertEquals(

                "SELECT TOP 1 [name], [id], ([geom]).STAsBinary() FROM [master].[dbo].[test1] WHERE ( ([name]) = (?) )",

                sqlbuilder.toString()

        );

        assertEquals(

                "[geom, id, name]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "['yoyo']",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testIsNull() throws Exception {

        SQLBuilder sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.select().column().name("name");

        sqlbuilder.select().column().name("id");

        sqlbuilder.select().column().name("geom").as_geometry();

        sqlbuilder.select().from().table().database("master").schema("dbo").name("test1");

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

            expbuilder.and(

                expbuilder.is_null(

                    expbuilder.column("name")

                ),

                expbuilder.not_is_null(

                    expbuilder.column("id")

                )

            )

        );

        sqlbuilder.select().limit(2);

        System.out.println("# Test:: testIsNull");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        //# Test:: testIsNull

        //# SQL:: SELECT TOP 2 [name], [id], ([geom]).STAsBinary() FROM [master].[dbo].[test1] WHERE ( ([name]) IS NULL ) AND ( ([id]) NOT IS NULL )

        //# Variables:: [geom, id, name]

        //# Parametros:: []

        assertEquals(

                "SELECT TOP 2 [name], [id], ([geom]).STAsBinary() FROM [master].[dbo].[test1] WHERE ( ([name]) IS NULL ) AND ( ([id]) NOT IS NULL )",

                sqlbuilder.toString()

        );

        assertEquals(

                "[geom, id, name]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testFetchFeatureType() throws Exception {

        SQLBuilder sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.select().column().all();

        sqlbuilder.select().from().table().database("master").schema("dbo").name("test1");

        sqlbuilder.select().limit(1);

        System.out.println("# Test:: testFetchFeatureType");

        System.out.println("# SQL:: " + sqlbuilder.toString());

         System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        //# Test:: testFetchFeatureType

        //# SQL:: SELECT TOP 1 * FROM [master].[dbo].[test1]

        //# Variables:: []

        //# Parametros:: []

        assertEquals(

                "SELECT TOP 1 * FROM [master].[dbo].[test1]",

                sqlbuilder.toString()

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testPerformDeletes() throws Exception {

        SQLBuilder sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.delete().table().database("master").schema("dbo").name("test1");

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

            expbuilder.eq( 

                expbuilder.column("id1"),

                expbuilder.parameter("id1").as_variable()

            )

        );

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

            expbuilder.eq( 

                expbuilder.column("id2"),

                expbuilder.parameter("id2").as_variable()

            )

        );

        System.out.println("# Test:: testPerformDeletes");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        //# Test:: testPerformDeletes

        //# SQL:: DELETE FROM [master].[dbo].[test1] WHERE ( ([id1]) = (?) ) AND ( ([id2]) = (?) )

        //# Variables:: [id1, id2]

        //# Parametros:: ["id1", "id2"]

        assertEquals(

                "DELETE FROM [master].[dbo].[test1] WHERE ( ([id1]) = (?) ) AND ( ([id2]) = (?) )",

                sqlbuilder.toString()

        );

        assertEquals(

                "[id1, id2]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[\"id1\", \"id2\"]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testPerformInserts1() throws Exception {

        IProjection proj = CRSFactory.getCRS("EPSG:4326");

        SQLBuilder sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.insert().table().database("master").schema("dbo").name("test1");

        sqlbuilder.insert().column().name("id").with_value(expbuilder.parameter("id"));

        sqlbuilder.insert().column().name("name").with_value(expbuilder.parameter("name"));

        sqlbuilder.insert().column().name("geom").with_value(expbuilder.parameter("geom").as_geometry_variable().srs(proj));

        System.out.println("# Test:: testPerformInserts1");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        //# Test:: testPerformInserts1

        //# SQL:: INSERT INTO [master].[dbo].[test1] ( [id], [name], [geom] ) VALUES ( ?, ?, geometry::STGeomFromWKB(?, 4326) )

        //# Variables:: [geom, id, name]

        //# Parametros:: ["id", "name", "geom"]

        assertEquals(

                "INSERT INTO [master].[dbo].[test1] ( [id], [name], [geom] ) VALUES ( ?, ?, geometry::STGeomFromWKB(?, 4326) )",

                sqlbuilder.toString()

        );

        assertEquals(

                "[geom, id, name]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[\"id\", \"name\", \"geom\"]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testPerformInserts2() throws Exception {

        IProjection proj = CRSFactory.getCRS("EPSG:4326");

        SQLBuilder sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.insert().table().database("master").schema("dbo").name("test1");

        sqlbuilder.insert().column().name("id").with_value(expbuilder.parameter("id"));

        sqlbuilder.insert().column().name("name").with_value(expbuilder.parameter("name"));

        sqlbuilder.insert().column().name("geom").with_value(expbuilder.parameter("geom").as_geometry_variable().srs(proj));

        System.out.println("# Test:: testPerformInserts2");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        //# Test:: testPerformInserts2

        //# SQL:: INSERT INTO [master].[dbo].[test1] ( [id], [name], [geom] ) VALUES ( ?, ?, geometry::STGeomFromWKB(?, ?) )

        //# Variables:: [geom, id, name]

        //# Parametros:: ["id", "name", "geom", 4326]

        assertEquals(

                "INSERT INTO [master].[dbo].[test1] ( [id], [name], [geom] ) VALUES ( ?, ?, geometry::STGeomFromWKB(?, ?) )",

                sqlbuilder.toString()

        );

        assertEquals(

                "[geom, id, name]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[\"id\", \"name\", \"geom\", 4326]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

    public void testPerformUpdates() throws Exception {

        IProjection proj = CRSFactory.getCRS("EPSG:4326");

        SQLBuilder sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.update().table().database("master").schema("dbo").name("test1");

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

            expbuilder.eq(

                expbuilder.column("id"), 

                expbuilder.parameter("id").as_variable()

            )

        );

        sqlbuilder.update().column().name("name").with_value(expbuilder.parameter("name"));

        sqlbuilder.update().column().name("geom").with_value(

                expbuilder.parameter("geom").as_geometry_variable().srs(

                        expbuilder.parameter().value(proj))

        );

        System.out.println("# Test:: testPerformUpdates");

        System.out.println("# SQL:: " + sqlbuilder.toString());

        System.out.println("# Variables:: " + ArrayUtils.toString(sqlbuilder.variables_names()));

        System.out.println("# Parametros:: " + ArrayUtils.toString(sqlbuilder.parameters_names()));

        //# Test:: testPerformUpdates

        //# SQL:: UPDATE [master].[dbo].[test1] SET [name] = ?, [geom] = geometry::STGeomFromWKB(?, ?) WHERE ( ([id]) = (?) )

        //# Variables:: [geom, id, name]

        //# Parametros:: ["name", "geom", 4326, "id"]

        assertEquals(

                "UPDATE [master].[dbo].[test1] SET [name] = ?, [geom] = geometry::STGeomFromWKB(?, ?) WHERE ( ([id]) = (?) )",

                sqlbuilder.toString()

        );

        assertEquals(

                "[geom, id, name]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[\"name\", \"geom\", 4326, \"id\"]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

}

/* 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.exception.ValidateDataParametersException;

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

public class MSSQLServerExplorerParameters extends

        JDBCServerExplorerParameters {

    public MSSQLServerExplorerParameters() {

        super(

                MSSQLServerLibrary.NAME + "ServerExplorerParameters",

                MSSQLServerLibrary.NAME

        );

    }

    @Override

    public String getUrl() {

        String url = super.getUrl();

        if( StringUtils.isEmpty(url) ) {

            url = MSSQLServerHelper.getConnectionURL(this);

            this.setUrl(url);

        }

        return url;

    }

    @Override

    public void validate() throws ValidateDataParametersException {

        if (getJDBCDriverClassName() == null) {

            setJDBCDriverClassName(MSSQLServerHelper.MSSQLServerJDBCDriver);

        }

        if (getPort() == null) {

            setPort(MSSQLServerHelper.PORT);

        }

        if( getDynValue("instanceName") == null ) {

            setDynValue("InstanceName", MSSQLServerHelper.INSTANCE_NAME);

        }

        super.validate();

    }

}

/* 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.gvsig.fmap.dal.DALLibrary;

import org.gvsig.fmap.dal.DALLocator;

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

import org.gvsig.fmap.dal.store.db.DBHelper;

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

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

import org.gvsig.metadata.exceptions.MetadataException;

import org.gvsig.tools.library.AbstractLibrary;

import org.gvsig.tools.library.LibraryException;

public class MSSQLServerLibrary extends AbstractLibrary {

    // com.microsoft.sqlserver.jdbc.SQLServerDriver

    public static final String NAME = "MSSQLServer";

    @Override

    public void doRegistration() {

        registerAsServiceOf(DALLibrary.class);

        require(JDBCLibrary.class);

    }

    @Override

    protected void doInitialize() throws LibraryException {

    }

    @Override

    protected void doPostInitialize() throws LibraryException {

        LibraryException ex = null;

        DataManagerProviderServices dataman = 

                (DataManagerProviderServices) DALLocator.getDataManager();

        DBHelper.registerParametersDefinition(

                NAME + "StoreParameters",

                MSSQLServerStoreParameters.class,

                dataman.getResourceAsStream(this, NAME + "Parameters.xml")

        );

        DBHelper.registerParametersDefinition(

                NAME + "NewStoreParameters",

                MSSQLServerNewStoreParameters.class,

                dataman.getResourceAsStream(this, NAME + "Parameters.xml")

        );

        DBHelper.registerParametersDefinition(

                NAME + "ServerExplorerParameters",

                MSSQLServerExplorerParameters.class,

                dataman.getResourceAsStream(this, NAME + "Parameters.xml")

        );

//        DBHelper.registerParametersDefinition(

//                NAME + "ResourceParameters",

//                MSSQLServerResourceParameters.class,

//                dataman.getResourceAsStream(this, NAME + "Parameters.xml")

//        );

        try {

            DBHelper.registerMetadataDefinition(

                NAME,

                JDBCStoreProviderBase.class,

                dataman.getResourceAsStream(this, NAME + "Metadata.xml")

            );

        } catch (MetadataException e) {

            ex = new LibraryException(this.getClass(), e);

        }

//        ResourceManagerProviderServices resman = (ResourceManagerProviderServices) DALLocator

//                .getResourceManager();

//

//        if (!resman.getResourceProviders().contains(NAME)) {

//            resman.register(NAME,

//                "Resource for " + NAME,

//                MSSQLServerResource.class,

//                MSSQLServerResourceParameters.class

//            );

//        }

        if (!dataman.getStoreProviderRegister().exits(NAME)) {

            dataman.registerStoreProviderFactory(new MSSQLServerStoreProviderFactory());

        }

        if (!dataman.getServerExplorerRegister().exits(NAME)) {

            dataman.registerServerExplorerFactory(new MSSQLServerExplorerFactory());

        }

        if (ex != null) {

            throw ex;

        }

    }

}

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

//        }

//