Application: gvSIG desktop » gvSIG data provider for MS SQLServer

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.fmap.dal.ExpressionBuilder;

import org.gvsig.fmap.dal.ExpressionBuilder.Parameter;

import org.gvsig.fmap.dal.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 MSSQLServerSQLHelper 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 MSSQLServerSQLHelper(params);

    }

    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 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 builder = createSQLBuilder();

        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").add( builder.variable("x") )

        );

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

        //# 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",

                builder.toString()

        );

        assertEquals(

                "[the_geom, x]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void testCount() throws Exception {

        SQLBuilder builder = createSQLBuilder();

        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").add(builder.variable("pp")));

        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(

                "[pp]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void testUpdateStatistics() throws Exception {

        SQLBuilder builder = createSQLBuilder();

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

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

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

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

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

        //# Test:: testUpdateStatistics

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

        //# Variables:: []

        //# Parametros:: []

        assertEquals(

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

                builder.toString()

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void testCreateTable() throws Exception {

        SQLBuilder builder = createSQLBuilder();

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

        builder.create_table().add_column(

                "name",

                DataTypes.STRING,

                45,

                0,

                false,

                true,

                false,

                null

        );

        builder.create_table().add_column(

                "id",

                DataTypes.INT,

                0,

                0,

                true,

                false,

                true,

                0

        );

        builder.create_table().add_column(

                "geom",

                DataTypes.GEOMETRY,

                0,

                0,

                false,

                true,

                false,

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

        //# 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 )",

                builder.toString()

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void testDropTable() throws Exception {

        SQLBuilder builder = createSQLBuilder();

        builder.drop_table().table().database("master").schema("dbo").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)));

        //# Test:: testDropTable

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

        //# Variables:: []

        //# Parametros:: []

        assertEquals(

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

                builder.toString()

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void testFetchFeatureProviderByReference() throws Exception {

        SQLBuilder builder = createSQLBuilder();

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

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

        //# 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]) = (?) )",

                builder.toString()

        );

        assertEquals(

                "[geom, id, name]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "['yoyo']",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void testIsNull() throws Exception {

        SQLBuilder builder = createSQLBuilder();

        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.and(

                builder.isNull(

                    builder.column("name")

                ),

                builder.notIsNull(

                        builder.column("id")

                )

            )

        );

        builder.select().limit(2);

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

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

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

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

        //# 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 )",

                builder.toString()

        );

        assertEquals(

                "[geom, id, name]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void testFetchFeatureType() throws Exception {

        SQLBuilder builder = createSQLBuilder();

        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 TOP 1 * FROM [master].[dbo].[test1]

        //# Variables:: []

        //# Parametros:: []

        assertEquals(

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

                builder.toString()

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void testPerformDeletes() throws Exception {

        SQLBuilder builder = createSQLBuilder();

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

            )

        );

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

        //# 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]) = (?) )",

                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 = createSQLBuilder();

        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().srs(proj));

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

        //# 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) )",

                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 = createSQLBuilder();

        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().srs(builder.parameter(proj)));

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

        //# 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(?, ?) )",

                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 = createSQLBuilder();

        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().srs( builder.parameter(proj)) 

        );

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

        //# 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]) = (?) )",

                builder.toString()

        );

        assertEquals(

                "[geom, id, name]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

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

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

}

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.fmap.dal.ExpressionBuilder;

import org.gvsig.fmap.dal.ExpressionBuilder.Config;

import org.gvsig.fmap.dal.ExpressionBuilder.GeometrySupportType;

import org.gvsig.fmap.dal.ExpressionBuilder.Parameter;

import static org.gvsig.fmap.dal.ExpressionBuilder.ParameterType.Constant;

import org.gvsig.fmap.dal.ExpressionBuilder.Variable;

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

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.GeometryLocator;

import org.gvsig.fmap.geom.GeometryManager;

import org.gvsig.fmap.geom.exception.CreateGeometryException;

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

import org.gvsig.tools.junit.AbstractLibraryAutoInitTestCase;

public class ExpressionBuilderTest 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 test2() {

        ExpressionBuilder builder = new ExpressionBuilderBase();

        builder.and(

          builder.eq(

            builder.lcase(builder.variable("colum_name_c")),

            builder.parameter("colum_name_p")

          )

        );

        builder.and(

            builder.group(

                builder.or(

                    builder.like(

                        builder.lcase( builder.variable("uno")),

                        builder.constant("%10")

                    ),

                    builder.lt(

                        builder.variable("dos"),

                        builder.constant(-3.5)

                    )

                )

            )

        );

        builder.and(

                builder.ST_Intersects(

                    builder.variable("geom1"), 

                    builder.ST_Envelope(

                        builder.ST_GeomFromWKB(

                                builder.parameter("geom2"), 

                                builder.parameter(4326).as_constant()

                        )

                    )

                )

        );

        builder.and(

            builder.gt(

                builder.variable("tres"),

                builder.constant(123456789)

            )

        );

        assertEquals(

                "( (LCASE(\"colum_name_c\")) = (?) ) AND ( ( (LCASE(\"uno\")) LIKE ('%10') ) OR ( (\"dos\") < (-3.5) ) ) AND ST_Intersects((\"geom1\"), (ST_Envelope(ST_GeomFromWKB((?), (?))))) AND ( (\"tres\") > (123456789) )",

                builder.toString()

        );

        assertEquals(

                "[colum_name_c, dos, geom1, tres, uno]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[\"colum_name_p\", \"geom2\", 4326]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void test3() throws CreateGeometryException {

        ExpressionBuilder builder = new ExpressionBuilderBase();

        GeometryManager geometryManager = GeometryLocator.getGeometryManager();

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

        Point point = geometryManager.createPoint(10, 20, Geometry.SUBTYPES.GEOM2D);

        builder.set(        

            builder.ST_Intersects(

              builder.geometry(point,proj),

              builder.variable("the_geom")

            )

        );

        builder.getConfig().set(Config.geometry_type_support, GeometrySupportType.WKT);

        System.out.println(builder.toString());

        assertEquals(

                "ST_Intersects((ST_GeomFromText('POINT (10 20)', (4326))), (\"the_geom\"))",

                builder.toString()

        );

        builder.getConfig().set(Config.geometry_type_support, GeometrySupportType.WKB);

        System.out.println(builder.toString());

        assertEquals(

                "ST_Intersects((ST_GeomFromWKB((0x000000000140240000000000004034000000000000), (4326))), (\"the_geom\"))",

                builder.toString()

        );

        assertEquals(

                "[the_geom]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void test4() throws CreateGeometryException {

        ExpressionBuilder builder = new ExpressionBuilderBase();

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

        builder.and(

                builder.ST_Intersects(

                    builder.variable("geom1"), 

                    builder.ST_Envelope(

                        builder.ST_GeomFromWKB(

                                builder.parameter("geom2"), 

                                builder.parameter(proj).as_constant()

                        )

                    )

                )

        );

        System.out.println(builder.toString());

        assertEquals(

                "ST_Intersects((\"geom1\"), (ST_Envelope(ST_GeomFromWKB((?), (?)))))",

                builder.toString()

        );

        assertEquals(

                "[geom1]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[\"geom2\", 4326]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

    public void test5() throws CreateGeometryException {

        ExpressionBuilder builder = new ExpressionBuilderBase();

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

        builder.and(

                builder.eq(

                        builder.ST_SRID( builder.variable("geom") ),

                        builder.srs(proj)

                )

        );

        System.out.println(builder.toString());

        assertEquals(

                "( (ST_SRID(\"geom\")) = (4326) )",

                builder.toString()

        );

        assertEquals(

                "[geom]",

                ArrayUtils.toString(getVariableNames(builder))

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(getParameterNames(builder))

        );

    }

}

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.fmap.dal.ExpressionBuilder;

import org.gvsig.fmap.dal.ExpressionBuilder.Constant;

import org.gvsig.fmap.dal.ExpressionBuilder.Parameter;

import org.gvsig.fmap.dal.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,

                true,

                false,

                null

        );

        builder.create_table().add_column(

                "id",

                DataTypes.INT,

                0,

                0,

                true,

                false,

                true,

                0

        );

        builder.create_table().add_column(

                "geom",

                DataTypes.GEOMETRY,

                0,

                0,

                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().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().srs(builder.parameter(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().srs( builder.parameter(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))

        );

    }

}

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

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