Application: gvSIG desktop

import java.util.List;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.DataManager;

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

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

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

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

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

//        # Test:: testPerformDeletes

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

//        # Variables:: [id1, id2]

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

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

    public void testForeingValue() throws Exception {

        DataManager dataManager = DALLocator.getDataManager();

        JDBCHelperBase helper = new JDBCHelperBase(null);

        SQLBuilder sqlbuilder = createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        EditableFeatureAttributeDescriptor attr;

        EditableForeingKey foreingKey;

        EditableFeatureType ft = dataManager.createFeatureType();

        ft.add("ID", DataTypes.INT);

        ft.add("NAME", DataTypes.STRING, 80);

        attr = ft.add("TYPE", DataTypes.INT);

        foreingKey = attr.getForeingKey();

        foreingKey.setForeingKey(true);

        foreingKey.setClosedList(true);

        foreingKey.setCodeName("ID");

        foreingKey.setTableName("TYPES");

        attr = ft.add("PHONE_TYPE", DataTypes.INT);

        foreingKey = attr.getForeingKey();

        foreingKey.setForeingKey(true);

        foreingKey.setClosedList(true);

        foreingKey.setCodeName("ID");

        foreingKey.setTableName("PHONE_TYPES");

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

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

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

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

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

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

            expbuilder.and(

                expbuilder.like(

                    expbuilder.function(

                        "FOREING_VALUE",

                        expbuilder.constant("TYPE.DESCRIPTION")

                    ),

                    expbuilder.constant("A%")

                ),

                expbuilder.eq(

                    expbuilder.function(

                        "FOREING_VALUE",

                        expbuilder.constant("PHONE_TYPE.DESCRIPTION")

                    ),

                    expbuilder.constant("mobile")

                )

            )

        );

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

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

        String[] attrNames = helper.replaceForeingValueFunction(sqlbuilder, ft);

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

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

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

        System.out.println("# attrNames:: " + ArrayUtils.toString(attrNames));

        //# Test:: testForeingValue

        //# SQL1:: SELECT "ID", "NAME", "DESCRIPTION", "TYPE" FROM "dbo"."test1" WHERE (( (FOREING_VALUE('TYPE.DESCRIPTION')) LIKE ('A%') ) AND ( (FOREING_VALUE('PHONE_TYPE.DESCRIPTION')) = ('mobile') ))

        //# SQL2:: SELECT "ID", "NAME", "dbo"."test1"."DESCRIPTION", "TYPE", "dbo"."TYPES"."DESCRIPTION", "dbo"."PHONE_TYPES"."DESCRIPTION" FROM "dbo"."test1" LEFT JOIN "dbo"."TYPES" ON ( ("dbo"."test1"."TYPE") = ("dbo"."TYPES"."ID") ) LEFT JOIN "dbo"."PHONE_TYPES" ON ( ("dbo"."test1"."PHONE_TYPE") = ("dbo"."PHONE_TYPES"."ID") ) WHERE (( ("dbo"."TYPES"."DESCRIPTION") LIKE ('A%') ) AND ( ("dbo"."PHONE_TYPES"."DESCRIPTION") = ('mobile') ))

        //# Variables:: [DESCRIPTION, DESCRIPTION, DESCRIPTION, ID, NAME, TYPE]

        //# Parametros:: []

        //# attrNames:: [TYPE.DESCRIPTION, PHONE_TYPE.DESCRIPTION]

        assertEquals(

                "SELECT \"ID\", \"NAME\", \"dbo\".\"test1\".\"DESCRIPTION\", \"TYPE\", \"dbo\".\"TYPES\".\"DESCRIPTION\", \"dbo\".\"PHONE_TYPES\".\"DESCRIPTION\" FROM \"dbo\".\"test1\" LEFT JOIN \"dbo\".\"TYPES\" ON ( (\"dbo\".\"test1\".\"TYPE\") = (\"dbo\".\"TYPES\".\"ID\") ) LEFT JOIN \"dbo\".\"PHONE_TYPES\" ON ( (\"dbo\".\"test1\".\"PHONE_TYPE\") = (\"dbo\".\"PHONE_TYPES\".\"ID\") ) WHERE (( (\"dbo\".\"TYPES\".\"DESCRIPTION\") LIKE ('A%') ) AND ( (\"dbo\".\"PHONE_TYPES\".\"DESCRIPTION\") = ('mobile') ))",

                sqlbuilder.toString()

        );

        assertEquals(

                "[DESCRIPTION, DESCRIPTION, DESCRIPTION, ID, NAME, TYPE]",

                ArrayUtils.toString(sqlbuilder.variables_names())

        );

        assertEquals(

                "[]",

                ArrayUtils.toString(sqlbuilder.parameters_names())

        );

        assertEquals(

                "{TYPE.DESCRIPTION,PHONE_TYPE.DESCRIPTION}",

                ArrayUtils.toString(attrNames)

        );

    }