Application: gvSIG desktop

    private static final String FORMAT_NOTISNULL = "( ({0}) IS NOT NULL )";

        return builtin_function("IS NOT NULL", FORMAT_NOTISNULL, value);

                    switch(next.getType()) {

                        case Token.NOTNULL:

                            lexer.next();

                            op1 = codeBuilder.is(op1, codeBuilder.constant(null));

                            op1 = codeBuilder.not(op1);

                            break;

                        case Token.OP_NOT:

                            lexer.next();

                            next = lexer.look();

                            if( next.getType() == Token.NULL ) {

                                lexer.next();

                                op1 = codeBuilder.is(op1, codeBuilder.constant(null));

                            } else {

                                op2 = parse_sum();

                                if( op2==null ) {

                                    throw new ExpressionSyntaxException(I18N.Cant_recognize_the_second_operand_of_IS_operator(),lexer);

                                }

                                op1 = codeBuilder.is(op1, op2);

                            }

                            op1 = codeBuilder.not(op1);

                            break;

                        case Token.NULL:

                            lexer.next();

                            op1 = codeBuilder.is(op1, codeBuilder.constant(null));

                            break;

                        default:    

                            op2 = parse_sum();

                            if( op2==null ) {

                                throw new ExpressionSyntaxException(I18N.Cant_recognize_the_second_operand_of_IS_operator(),lexer);

                            }

                            op1 = codeBuilder.is(op1, op2);

import org.gvsig.expressionevaluator.ExpressionBuilder;

import org.gvsig.expressionevaluator.ExpressionUtils;

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

        } else {

            FeatureType ft = featureStore.getDefaultFeatureType();

            ExpressionBuilder expbuilder = ExpressionUtils.createExpressionBuilder();

            featureQuery.addFilter(

                expbuilder.not_is_null(

                    expbuilder.column(

                            ft.getDefaultGeometryAttributeName()

                    )

                ).toString()

            );

    public static class DefaultTableReference implements TableReference {

//            expbuilder.ifnull(

//                expbuilder.column(columnName), 

//                expbuilder.constant(null), 

//            )

//        sqlbuilder.select().group_by(expbuilder.column(columnName));

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

            expbuilder.not_is_null(expbuilder.column(columnName))

        );

import org.apache.commons.lang3.StringUtils;

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

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

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

    public void testCalulateEnvelopeOfColumn() throws Exception {

        TableReference table = new OperationsFactoryBase.DefaultTableReference("master","dbo","test1", null);

        String columnName = "the_geom";

        SQLBuilder sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

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

            expbuilder.as_geometry(

                expbuilder.ST_ExtentAggregate(

                        expbuilder.column(columnName)

                )

            )

        );

        //sqlbuilder.select().group_by(expbuilder.column(columnName));

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

                .database(table.getDatabase())

                .schema(table.getSchema())

                .name(table.getTable());

        sqlbuilder.select().from().subquery(table.getSubquery());

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

            expbuilder.not_is_null(expbuilder.column(columnName))

        );

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

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

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

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

        assertEquals(

                "SELECT ST_AsBinary(ST_Extent(\"the_geom\")) FROM \"dbo\".\"test1\" WHERE ( (\"the_geom\") NOT IS NULL )",

                sqlbuilder.toString()

        );

        assertEquals(

                "[the_geom]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

    }

                "SELECT ST_AsBinary(ST_Extent(\"the_geom\")) AS \"envelope\" FROM \"dbo\".\"test1\" WHERE (( ((ST_Envelope(\"the_geom\")) && (ST_GeomFromWKB((x'000000000300000001000000050000000000000000000000000000000000000000000000004059000000000000405900000000000040590000000000004059000000000000000000000000000000000000000000000000000000000000'), (4326)))) AND ST_Intersects((ST_Envelope(\"the_geom\")),(ST_GeomFromWKB((x'000000000300000001000000050000000000000000000000000000000000000000000000004059000000000000405900000000000040590000000000004059000000000000000000000000000000000000000000000000000000000000'), (4326))) )) AND x = 27)",

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

                "CREATE TABLE \"dbo\".\"test1\" (\"name\" VARCHAR(45) DEFAULT NULL, \"id\" INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL, \"geom\" GEOMETRY ); ALTER TABLE \"dbo\".\"test1\" ADD CONSTRAINT IF NOT EXISTS \"constraint_test1_geom_dim\" CHECK NVL2(\"geom\", ST_CoordDim(\"geom\") = 2, TRUE)",

                "DROP TABLE \"dbo\".\"test1\"",

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

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

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

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

                "UPDATE \"dbo\".\"test1\" SET \"name\" = ?, \"geom\" = ST_GeomFromWKB((?), (4326)) WHERE ( (\"id\") = (?) )",

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

		<field name="port" type="integer" mandatory="false" defaultValue="9123" group="Connection">

        <!--

        <field name="LOCK_TIMEOUT" type="integer" mandatory="false" group="Advanced" defaultValue="1000" >

          <description>Sets the lock timeout (in milliseconds) for the current session. The default value for this setting is 1000 (one second).</description>

        </field>

        <field name="MULTI_THREADED" type="integer" mandatory="false" group="Advanced" defaultValue="1000" >

          <description>Enabled (1) or disabled (0) multi-threading inside the database engine. MULTI_THREADED is enabled by default with default MVStore storage engine. MULTI_THREADED is disabled by default when using PageStore storage engine, enabling this with PageStore is experimental only.

This is a global setting, which means it is not possible to open multiple databases with different modes at the same time in the same virtual machine. This setting is not persistent, however the value is kept until the virtual machine exits or it is changed.

Admin rights are required to execute this command, as it affects all connections</description>

        </field>

        <field name="CHACHE_SIZE" type="integer" mandatory="false" group="Advanced" defaultValue="-1" >

          <description>Sets the size of the cache in KB (each KB being 1024 bytes) for the current database. The default is 65536 per available GB of RAM, i.e. 64 MB per GB. The value is rounded to the next higher power of two. Depending on the virtual machine, the actual memory required may be higher.

This setting is persistent and affects all connections as there is only one cache per database. Using a very small value (specially 0) will reduce performance a lot. This setting only affects the database engine (the server in a client/server environment; in embedded mode, the database engine is in the same process as the application). It has no effect for in-memory databases.

Admin rights are required to execute this command, as it affects all connections. This command commits an open transaction in this connection. This setting is persistent.?

If -1 use the default value.</description>

        </field>

        <field name="LOG" type="integer" mandatory="false" group="Advanced" defaultValue="2" >

          <availableValues>

            <value label="0 - Transaction log is disabled completely">0</value>

            <value label="1 - Transaction log is enabled, but FileDescriptor.sync is disabled">1</value>

            <value label="2 - Transaction log is enabled, and FileDescriptor.sync is called for each checkpoint">2</value>

          </availableValues>

          <description>Sets the transaction log mode. The values 0, 1, and 2 are supported, the default is 2. This setting affects all connections.

LOG 0 means the transaction log is disabled completely. It is the fastest mode, but also the most dangerous: if the process is killed while the database is open in this mode, the data might be lost. It must only be used if this is not a problem, for example when initially loading a database, or when running tests.

LOG 1 means the transaction log is enabled, but FileDescriptor.sync is disabled. This setting is about half as fast as with LOG 0. This setting is useful if no protection against power failure is required, but the data must be protected against killing the process.

LOG 2 (the default) means the transaction log is enabled, and FileDescriptor.sync is called for each checkpoint. This setting is about half as fast as LOG 1. Depending on the file system, this will also protect against power failure in the majority if cases.

Admin rights are required to execute this command, as it affects all connections</description>

        <field name="LOCK_MODE" type="integer" mandatory="false" group="Advanced" defaultValue="3" >

          <availableValues>

            <value label="0 - READ_UNCOMMITTED, no locking">0</value>

            <value label="1 - SERIALIZABLE, table level locking">1</value>

            <value label="2 - Table level locking with garbage collection">2</value>

            <value label="3 - READ_COMMITTED, table level locking+read locks released immediately">3</value>

          </availableValues>

          <description>Sets the lock mode. The values 0, 1, 2, and 3 are supported. The default is 3 (READ_COMMITTED). This setting affects all connections.

The value 0 means no locking (should only be used for testing; also known as READ_UNCOMMITTED). Please note that using SET LOCK_MODE 0 while at the same time using multiple connections may result in inconsistent transactions.

The value 1 means table level locking (also known as SERIALIZABLE).

The value 2 means table level locking with garbage collection (if the application does not close all connections).

The value 3 means table level locking, but read locks are released immediately (default; also known as READ_COMMITTED).

</description>

        </field>

        -->

        StringBuilder commonParameters = new StringBuilder();

        commonParameters.append(";MODE=PostgreSQL");

        commonParameters.append(";SCHEMA=PUBLIC");

        commonParameters.append(";ALLOW_LITERALS=ALL");

//        Integer LOCK_TIMEOUT = (Integer) params.getDynValue("LOCK_TIMEOUT");

//        if( LOCK_TIMEOUT!=null ) {

//            commonParameters.append(";LOCK_TIMEOUT=").append(LOCK_TIMEOUT);

//        }

//        Integer MULTI_THREADED = (Integer) params.getDynValue("MULTI_THREADED");

//        if( MULTI_THREADED!=null ) {

//            commonParameters.append(";MULTI_THREADED=").append(MULTI_THREADED);

//        }

//        Integer CHACHE_SIZE = (Integer) params.getDynValue("CHACHE_SIZE");

//        if( LOCK_TIMEOUT!=null ) {

//            commonParameters.append(";CHACHE_SIZE=").append(CHACHE_SIZE);

//        }

//        Integer LOG = (Integer) params.getDynValue("LOG");

//        if( LOCK_TIMEOUT!=null ) {

//            commonParameters.append(";LOG=").append(LOG);

//        }

//        Integer LOCK_MODE = (Integer) params.getDynValue("LOCK_MODE");

//        if( LOCK_TIMEOUT!=null ) {

//            commonParameters.append(";LOCK_MODE=").append(LOCK_MODE);

//        }

                    "jdbc:h2:zip:{0}!/{1}"+commonParameters.toString(),

                    "jdbc:h2:file:{0}"+commonParameters.toString(),

                "jdbc:h2:tcp://{0}/{1}"+commonParameters.toString(),

                "jdbc:h2:tcp://{0}:{1,number,#######}/{2}"+commonParameters.toString(),

                // Ignore this error.

        String r = MessageFormat.format("ST_AsBinary({0})", p1);

//        String r = MessageFormat.format("NVL2({0},ST_AsBinary({0}),NULL)", p1);

                        "ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT IF NOT EXISTS \"{2}\" CHECK ST_CoordDim(\"{3}\") = {4}",

                        this.table().getSchema(),

                        "ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT IF NOT EXISTS \"{2}\" CHECK ST_CoordDim(\"{3}\") = {4}",

                        this.table().getSchema(),

                        "ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT IF NOT EXISTS \"{2}\" CHECK NVL2(\"{3}\", ST_CoordDim(\"{3}\") = {4}, TRUE)",

                        this.table().getSchema(),

                            "ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT IF NOT EXISTS \"{2}\" CHECK NVL2(\"{3}\", ST_SRID(\"{3}\") = {4,number,#####}, TRUE)",

                            this.table().getSchema(), // 0

                            this.table().getName(), // 1

                            constraint_name2, // 2

                            column.getName(), // 3

                            column.getGeometrySRSId() // 4

        ExpressionBuilder.Variable column = builder.column(fad.getName());

                builder.and(

                    builder.not_is_null(column),

                    builder.ST_Intersects(

                            column,

                            builder.geometry(geometry, this.projection)

                    )

        ExpressionBuilder.Variable column = builder.column(fad.getName());

                builder.and(

                    builder.not_is_null(column)    ,

                    builder.ST_Intersects(

                            builder.ST_Envelope(column),

                            builder.geometry(envelope.getGeometry(), this.projection)

                    )