Application: gvSIG desktop » gvSIG data provider for MS SQLServer

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

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

  <modelVersion>4.0.0</modelVersion>

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

  <packaging>pom</packaging>

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

  <parent>

      <groupId>org.gvsig</groupId>

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

      <version>1.0.32</version>

  </parent>

  <modules>

    <module>org.gvsig.mssqlserver.app.mainplugin</module>

  </modules>

</project>

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

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

  <modelVersion>4.0.0</modelVersion>

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

  <packaging>jar</packaging>

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

  <description>MS SQLServer support</description>

  <parent>

      <groupId>org.gvsig</groupId>

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

      <version>1.0.32</version>

  </parent>

  <dependencies>

    <dependency>

        <groupId>org.gvsig</groupId>

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

        <scope>compile</scope>

    </dependency>

    <dependency>

        <groupId>org.gvsig</groupId>

        <artifactId>org.gvsig.andami</artifactId>

        <scope>compile</scope>

    </dependency>

    <dependency>

        <groupId>org.gvsig</groupId>

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

        <scope>compile</scope>

    </dependency>

    <dependency>

        <groupId>org.gvsig</groupId>

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

        <scope>compile</scope>

    </dependency>

    <dependency>

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

      <artifactId>sqlserver-jdbc</artifactId>

      <scope>runtime</scope>

    </dependency>

    <dependency>

      <groupId>commons-dbcp</groupId>

      <artifactId>commons-dbcp</artifactId>

      <scope>runtime</scope>

    </dependency>

    <dependency>

      <groupId>commons-collections</groupId>

      <artifactId>commons-collections</artifactId>

      <scope>runtime</scope>

    </dependency>

    <dependency>

      <groupId>commons-pool</groupId>

      <artifactId>commons-pool</artifactId>

      <scope>runtime</scope>

    </dependency>

  </dependencies>

    <properties>

        <!-- Package info property values -->

        <!-- Default values in org.gvsig.desktop -->

        <gvsig.package.info.name>Formats: MS SQLServer support</gvsig.package.info.name>

        <gvsig.package.info.state>testing</gvsig.package.info.state>

        <gvsig.package.info.official>true</gvsig.package.info.official>

        <gvsig.package.info.javaVM>j1_8</gvsig.package.info.javaVM>

        <gvsig.package.info.dependencies>required: org.gvsig.app.mainplugin -ge 2.3.0</gvsig.package.info.dependencies>

        <gvsig.package.info.categories>Formats,Vector,Database</gvsig.package.info.categories>

        <gvsig.package.info.poolURL>https://devel.gvsig.org/download/projects/gvsig-mssqlserver/pool/</gvsig.package.info.poolURL>

    </properties>

</project>

#Tue Jan 15 14:22:50 CET 2019

buildNumber=34

Notas acerca de la implementacion del proveedor de MS SQLServer.

====================================================================

Software

----------

Las pruebas realizadas durante el desarrollo se han hecho sobre:

- Microsoft SQL Server Express 2014 (64-bit), versi?n 12.0.2000.8

- Microsoft Windows NT 6.3 (9600), plataforma NT x64

El acceso al servidor se realiza a traves de JDBC usando el driver:

- Microsoft JDBC Driver 6.0 for SQL Server, may 2016 (sqljdbc42.jar).

Configuraci?n

--------------

Las pruebas se han realizado usando la instancia por defecto "SQLEXPRESS".

Para poder acceder al servidor es necesario:

- Habilitar la conexion al servidor a traves de TCP/IP. Esto se activara

  desde:

  - Sql Server Configuration Manager

  - Configuracion de red de SQL Server

  - Protocolos de SQLEXPRESS

  - TCP/IP

   - General

     - Habilitado: Si

   - Direcciones IP

     - IP4

       - Activo: Si

       - Habilitado: Si

- Deberan estar "En ejecucion" los servicios de:

  - SQL Server (SQLEXPRESS)

  - SQL Server Browser.

    Este servicio es necesario para poder aceptar conexiones TCP/IP contra el

    servidor y por defecto, tras la instalacion de MS SQL Sever, no se iniciaba

    automaticamente en mi equipo.

- Durante todas las pruebas se ha utilizado "Autenticaci?n de SQL Server".

  Es posible que para otros tipos de autenticaci?n sea necesario incluir alguna

  otra libreria (como sqljdbc_auth.dll x64/x86).

Soporte espacial

-------------------

Siempre que se exporta a BBDD se utilizara el tipo "geometry" para crear los

campos de tipo geometria, y en general se usara siempre este tipo de datos.

Se ha implementado un soporte limitado para el tipo de datos "geography". Si una

tabla contiene un campo de este tipo se leera y gvSIG podra trabajar con el. Si

una tabla contiene mas de un campo de tipo geometria siendo estos de tipos distintos,

unos "geometry" y otros "geography", gvSIG puede dar problemas para acceder a estos,

especialmente al de tipo "geography".

Ahora mismo el tipo de geometria asociado a una columna de la BBDD es GEOMETRY,

es decir, no se sabe si es punto, linea o poligono.

Sistemas de referencia

------------------------

La lista de sistemas de referencia disponibles esta almacenada en la tabla

sys.spatial_reference_systems, y solo tiene definidas unos 390 sistemas

de referencia. Habria que averiguar cargar otros sistemas de referencia.

Valores por defecto

---------------------

Los datos de conexion por defecto son:

- Host: 127.0.0.1

- Instance: SQLEXPRESS

- Database: master

- Schema: dbo

- user: sa

- password: 123

Los valores instance, database, schema y user se corresponden con valores

validos para una instalacion por defecto de SQL Server.

Problemas conocidos

--------------------

- No se guardan los datos de la conexion entre sesiones de gvSIG.

- La herramienta "Selection by layer" no funciona.

- Si una capa de gvSIG declara un campo "double" con precision y size,

  al expportarlo a BBDD lo exporta como "DOUBLE PRECISION" en lugar de como

  "NUMERIC(size,precision)".

- No se estan creando indices sobre los campos al exportar a BBDD.

- En la exportacion a BBDD, en el paso que pide el nombre de la tabla,

  primero muestra como esquema "public" y cuando termina de leer las tablas

  de la BBDD para mostrarlas (tarda unos segundos) cambia de public a "dbo".

  Esto puede causar problemas si el usuario pulsa en siguiente antes de que se

  haya actualizado el esquema con el que se corresponde con la BBDD ya que

  intentaria crear la tabla en el esquema "public" y este no existe en SQLServer.

- Solo se ha implememtado soporte para geometrias 2D (no 3D, 2DM o 3DM).

MICROSOFT SOFTWARE LICENSE TERMS

MICROSOFT JDBC DRIVER 6.0 FOR SQL SERVER

These license terms are an agreement between Microsoft Corporation (or based on where you 

live, one of its affiliates) and you. Please read them. They apply to the software 

named above, which includes the media on which you received it, if any. The terms also apply to 

any Microsoft

? updates,

? supplements,

? Internet-based services, and

? support services

for this software, unless other terms accompany those items. If so, those terms apply.

BY USING THE SOFTWARE, YOU ACCEPT THESE TERMS. IF YOU DO NOT 

ACCEPT THEM, DO NOT USE THE SOFTWARE.

If you comply with these license terms, you have the rights below.

1. INSTALLATION AND USE RIGHTS. 

a. Installation and Use.

i. You may install and use any number of copies of the software on your devices.

b. Third Party Programs. The software may include third party programs that Microsoft, 

not the third party, licenses to you under this agreement. Notices, if any, for the third 

party program are included for your information only.

2. ADDITIONAL LICENSING REQUIREMENTS AND/OR USE RIGHTS.

a. Distributable Code.

i.	Right to Use and Distribute. You are permitted to distribute the software in programs you 

develop if you comply with the terms below:

* You may copy and distribute the object code form of the software (?Distributable Code?) in 

programs you develop. You may not modify the software.

* You may permit distributors of your programs to copy and distribute the Distributable Code 

as part of those programs.

ii.	Distribution Requirements. For any Distributable Code you distribute, you must

* add significant primary functionality to it in your programs;

* require distributors and external end users to agree to terms that protect it at least as 

much as this agreement;

* display your valid copyright notice on your programs; and

* indemnify, defend, and hold harmless Microsoft from any claims, including attorneys? fees, 

related to the distribution or use of your programs.

iii.	Distribution Restrictions. You may not

* alter any copyright, trademark or patent notice in the Distributable Code;

* use Microsoft?s trademarks in your programs? names or in a way that suggests your programs 

come from or are endorsed by Microsoft;

* include Distributable Code in malicious, deceptive or unlawful programs; or

* modify or distribute the source code of any Distributable Code so that any part of it 

becomes subject to an Excluded License. An Excluded License is one that requires, as a 

condition of use, modification or distribution, that

* the code be disclosed or distributed in source code form; or

* others have the right to modify it.

3. SCOPE OF LICENSE. The software is licensed, not sold. This agreement only gives you 

some rights to use the software. Microsoft reserves all other rights. Unless applicable law 

gives you more rights despite this limitation, you may use the software only as expressly 

permitted in this agreement. In doing so, you must comply with any technical limitations in 

the software that only allow you to use it in certain ways. You may not

? work around any technical limitations in the software;

? reverse engineer, decompile or disassemble the software, except and only to the extent 

that applicable law expressly permits, despite this limitation;

? make more copies of the software than specified in this agreement or allowed by 

applicable law, despite this limitation;

? publish the software for others to copy;

? rent, lease or lend the software;

? transfer the software or this agreement to any third party; or

? use the software for commercial software hosting services.

4. EXPORT RESTRICTIONS. The software is subject to United States export laws and 

regulations. You must comply with all domestic and international export laws and 

regulations that apply to the software. These laws include restrictions on destinations, end 

users and end use. For additional information, see www.microsoft.com/exporting.

5. SUPPORT SERVICES. Because this software is ?as is,? we may not provide support 

services for it.

6. ENTIRE AGREEMENT. This agreement, and the terms for supplements, updates, Internet-

based services and support services that you use, are the entire agreement for the software 

and support services.

7. APPLICABLE LAW.

a. United States. If you acquired the software in the United States, Washington state law 

governs the interpretation of this agreement and applies to claims for breach of it, 

regardless of conflict of laws principles. The laws of the state where you live govern all 

other claims, including claims under state consumer protection laws, unfair competition 

laws, and in tort.

b. Outside the United States. If you acquired the software in any other country, the laws of 

that country apply.

8. LEGAL EFFECT. This agreement describes certain legal rights. You may have other rights 

under the laws of your country. You may also have rights with respect to the party from 

whom you acquired the software. This agreement does not change your rights under the laws 

of your country if the laws of your country do not permit it to do so.

9. DISCLAIMER OF WARRANTY. THE SOFTWARE IS LICENSED ?AS-IS.? YOU 

BEAR THE RISK OF USING IT. MICROSOFT GIVES NO EXPRESS 

WARRANTIES, GUARANTEES OR CONDITIONS. YOU MAY HAVE 

ADDITIONAL CONSUMER RIGHTS UNDER YOUR LOCAL LAWS WHICH THIS 

AGREEMENT CANNOT CHANGE. TO THE EXTENT PERMITTED UNDER 

YOUR LOCAL LAWS, MICROSOFT EXCLUDES THE IMPLIED WARRANTIES 

OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NON-

INFRINGEMENT.

10. LIMITATION ON AND EXCLUSION OF REMEDIES AND DAMAGES. YOU CAN 

RECOVER FROM MICROSOFT AND ITS SUPPLIERS ONLY DIRECT DAMAGES UP 

TO U.S. $5.00. YOU CANNOT RECOVER ANY OTHER DAMAGES, INCLUDING 

CONSEQUENTIAL, LOST PROFITS, SPECIAL, INDIRECT OR INCIDENTAL 

DAMAGES.

This limitation applies to

? anything related to the software, services, content (including code) on third party Internet 

sites, or third party programs, and

? claims for breach of contract, breach of warranty, guarantee or condition, strict liability, 

negligence, or other tort to the extent permitted by applicable law.

It also applies even if Microsoft knew or should have known about the possibility of the 

damages. The above limitation or exclusion may not apply to you because your country may 

not allow the exclusion or limitation of incidental, consequential or other damages.

Please note: As this software is distributed in Quebec, Canada, some of the clauses in this 

agreement are provided below in French.

Remarque : Ce logiciel ?tant distribu? au Qu?bec, Canada, certaines des clauses dans ce 

contrat sont fournies ci-dessous en fran?ais.

EXON?RATION DE GARANTIE. Le logiciel vis? par une licence est offert ? tel quel ?. 

Toute utilisation de ce logiciel est ? votre seule risque et p?ril. Microsoft n?accorde aucune 

autre garantie expresse. Vous pouvez b?n?ficier de droits additionnels en vertu du droit local 

sur la protection des consommateurs, que ce contrat ne peut modifier. La ou elles sont 

permises par le droit locale, les garanties implicites de qualit? marchande, d?ad?quation ? un 

usage particulier et d?absence de contrefa?on sont exclues.

LIMITATION DES DOMMAGES-INT?R?TS ET EXCLUSION DE 

RESPONSABILIT? POUR LES DOMMAGES. Vous pouvez obtenir de Microsoft et de 

ses fournisseurs une indemnisation en cas de dommages directs uniquement ? hauteur de 5,00 

$ US. Vous ne pouvez pr?tendre ? aucune indemnisation pour les autres dommages, y 

compris les dommages sp?ciaux, indirects ou accessoires et pertes de b?n?fices.

Cette limitation concerne :

? tout ce qui est reli? au logiciel, aux services ou au contenu (y compris le code) figurant 

sur des sites Internet tiers ou dans des programmes tiers ; et

? les r?clamations au titre de violation de contrat ou de garantie, ou au titre de 

responsabilit? stricte, de n?gligence ou d?une autre faute dans la limite autoris?e par la loi 

en vigueur.

Elle s?applique ?galement, m?me si Microsoft connaissait ou devrait conna?tre l??ventualit? 

d?un tel dommage. Si votre pays n?autorise pas l?exclusion ou la limitation de responsabilit? 

pour les dommages indirects, accessoires ou de quelque nature que ce soit, il se peut que la 

limitation ou l?exclusion ci-dessus ne s?appliquera pas ? votre ?gard.

EFFET JURIDIQUE. Le pr?sent contrat d?crit certains droits juridiques. Vous pourriez avoir 

d?autres droits pr?vus par les lois de votre pays. Le pr?sent contrat ne modifie pas les droits 

que vous conf?rent les lois de votre pays si celles-ci ne le permettent pas.

<?xml version="1.0" encoding="ISO-8859-1"?>

<plugin-config>

  <libraries library-dir="lib/"/>

  <depends plugin-name="org.gvsig.app.mainplugin"/>

  <resourceBundle name="text"/>

  <extensions>

     <extension class-name="org.gvsig.mssqlserver.MSSQLServerExtension" active="true"/>

  </extensions>    

</plugin-config>

<assembly>

  <id>gvsig-plugin-package</id>

  <formats>

    <format>zip</format>

  </formats>

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

  <includeBaseDirectory>true</includeBaseDirectory>

  <files>

    <file>

      <source>target/${project.artifactId}-${project.version}.jar</source>

      <outputDirectory>lib</outputDirectory>

    </file>

    <file>

      <source>target/package.info</source>

    </file>

    <file>

      <source>src/main/resources-plugin/config.xml</source>

    </file>

  </files>

  <fileSets>

    <fileSet>

      <directory>src/main/resources-plugin</directory>

      <outputDirectory>.</outputDirectory>

    </fileSet>

  </fileSets>

  <dependencySets>

    <dependencySet>

      <useProjectArtifact>false</useProjectArtifact>

      <useTransitiveDependencies>false</useTransitiveDependencies>

      <outputDirectory>lib</outputDirectory>

      <includes>

        <include>org.gvsig:org.gvsig.mssqlserver.provider</include>

      </includes>

    </dependencySet>

    <dependencySet>

      <useProjectArtifact>false</useProjectArtifact>

      <useTransitiveDependencies>false</useTransitiveDependencies>

      <outputDirectory>lib</outputDirectory>

      <includes>

		<include>com.microsoft.sqlserver:sqlserver-jdbc</include>

		<include>commons-dbcp:commons-dbcp</include>

		<include>commons-collections:commons-collections</include>

		<include>commons-pool:commons-pool</include>

      </includes>

    </dependencySet>

  </dependencySets>

</assembly>

package org.gvsig.mssqlserver;

import org.gvsig.andami.IconThemeHelper;

import org.gvsig.andami.plugins.Extension;

import org.gvsig.fmap.mapcontext.MapContextLocator;

import org.gvsig.fmap.mapcontext.MapContextManager;

public class MSSQLServerExtension  extends Extension {

    @Override

    public void initialize() {

    }

    @Override

    public void postInitialize() {

        MapContextManager mapContextMgr = MapContextLocator.getMapContextManager();

        IconThemeHelper.registerIcon("layer", "layer-icon-mssqlserver", this);

        mapContextMgr.registerIconLayer("MSSQLServer", "layer-icon-mssqlserver");

    }

    @Override

    public void execute(String actionCommand) {

    }

    @Override

    public boolean isEnabled() {

        return false;

    }

    @Override

    public boolean isVisible() {

        return false;

    }

}

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

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

    <modelVersion>4.0.0</modelVersion>

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

    <version>1.0.32</version>

    <packaging>pom</packaging>

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

    <description>MS SQLServer support fort DAL and gvSIG</description>

    <parent>

        <groupId>org.gvsig</groupId>

        <artifactId>org.gvsig.desktop</artifactId>

        <version>2.0.234</version>

    </parent>

    <url>https://devel.gvsig.org/redmine/projects/gvsig-mssqlserver</url>

    <scm>

        <connection>scm:svn:https://devel.gvsig.org/svn/gvsig-mssqlserver/tags/org.gvsig.mssqlserver-1.0.32</connection>

        <developerConnection>scm:svn:https://devel.gvsig.org/svn/gvsig-mssqlserver/tags/org.gvsig.mssqlserver-1.0.32</developerConnection>

        <url>https://devel.gvsig.org/redmine/projects/gvsig-mssqlserver/repository/show/tags/org.gvsig.mssqlserver-1.0.32</url>

    </scm>

    <repositories>

        <repository>

            <id>gvsig-public-http-repository</id>

            <name>gvSIG maven public HTTP repository</name>

            <url>http://devel.gvsig.org/m2repo/j2se</url>

            <releases>

                <enabled>true</enabled>

                <updatePolicy>daily</updatePolicy>

                <checksumPolicy>warn</checksumPolicy>

            </releases>

            <snapshots>

                <enabled>true</enabled>

                <updatePolicy>daily</updatePolicy>

                <checksumPolicy>warn</checksumPolicy>

            </snapshots>

        </repository>

    </repositories>

    <build>

        <plugins>

            <plugin>

                <groupId>org.apache.maven.plugins</groupId>

                <artifactId>maven-release-plugin</artifactId>

                <configuration>

                    <tagBase>https://devel.gvsig.org/svn/gvsig-mssqlserver/tags/</tagBase>

                    <goals>deploy</goals>

                </configuration>

            </plugin>                     

        </plugins>

    </build>

    <dependencyManagement>

        <dependencies>

            <dependency>

                <groupId>org.gvsig</groupId>

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

                <version>1.0.32</version>

            </dependency>

            <dependency>

                <groupId>org.gvsig</groupId>

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

                <version>1.0.32</version>

            </dependency>

            <dependency>

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

                <artifactId>sqlserver-jdbc</artifactId>

                <version>6.0.0</version>

            </dependency>

        </dependencies>

    </dependencyManagement>

    <modules>

        <module>org.gvsig.mssqlserver.app</module>

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

    </modules>

</project>

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().value(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().value(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,

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

    }

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