Application: gvSIG desktop

-- testToDoubleWithSpecialFunctions SQL

-- Select 

SELECT "public"."ARENA2_CONDUCTORES"."LID_CONDUCTOR", "public"."ARENA2_CONDUCTORES"."ID_ACCIDENTE", 

"public"."ARENA2_CONDUCTORES"."LID_VEHICULO", "public"."ARENA2_CONDUCTORES"."ID_VEHICULO", 

"public"."ARENA2_CONDUCTORES"."POSIBLE_RESPONSABLE", "public"."ARENA2_CONDUCTORES"."FECHA_NACIMIENTO", 

"public"."ARENA2_CONDUCTORES"."SEXO", "public"."ARENA2_CONDUCTORES"."NACIONALIDAD", 

"public"."ARENA2_CONDUCTORES"."PAIS_RESIDENCIA", "public"."ARENA2_CONDUCTORES"."PROVINCIA_RESIDENCIA", 

"public"."ARENA2_CONDUCTORES"."MUNICIPIO_RESIDENCIA", "public"."ARENA2_CONDUCTORES"."ASISTENCIA_SANITARIA", 

"public"."ARENA2_CONDUCTORES"."INFLU_FACT_ATENCION", "public"."ARENA2_CONDUCTORES"."FACTORES_ATENCION", 

"public"."ARENA2_CONDUCTORES"."INFLU_PRES_ERROR", "public"."ARENA2_CONDUCTORES"."PRESUNTOS_ERRORES", 

"public"."ARENA2_CONDUCTORES"."CARACT_PERMISO", "public"."ARENA2_CONDUCTORES"."CLASE_PERMISO", 

"public"."ARENA2_CONDUCTORES"."FECHA_PERMISO", "public"."ARENA2_CONDUCTORES"."MOTIVO_DESPLAZAMIENTO", 

"public"."ARENA2_CONDUCTORES"."DESPLAZAMIENTO_PREVISTO", "public"."ARENA2_CONDUCTORES"."ACC_SEG_CINTURON", 

"public"."ARENA2_CONDUCTORES"."ACC_SEG_CASCO", "public"."ARENA2_CONDUCTORES"."ACC_SEG_BRAZOS", 

"public"."ARENA2_CONDUCTORES"."ACC_SEG_ESPALDA", "public"."ARENA2_CONDUCTORES"."ACC_SEG_TORSO", 

"public"."ARENA2_CONDUCTORES"."ACC_SEG_MANOS", "public"."ARENA2_CONDUCTORES"."ACC_SEG_PIERNAS", 

"public"."ARENA2_CONDUCTORES"."ACC_SEG_PIES", "public"."ARENA2_CONDUCTORES"."ACC_SEG_PRENDA_REF", 

"public"."ARENA2_CONDUCTORES"."INFLU_ALCOHOL", "public"."ARENA2_CONDUCTORES"."PRUEBA_ALCOHOLEMIA", 

"public"."ARENA2_CONDUCTORES"."TASA_ALCOHOLEMIA1", "public"."ARENA2_CONDUCTORES"."TASA_ALCOHOLEMIA2", 

"public"."ARENA2_CONDUCTORES"."PRUEBA_ALC_SANGRE", "public"."ARENA2_CONDUCTORES"."SIGNOS_INFLU_ALCOHOL", 

"public"."ARENA2_CONDUCTORES"."INFLU_DROGAS", "public"."ARENA2_CONDUCTORES"."PRUEBA_DROGAS", 

"public"."ARENA2_CONDUCTORES"."AMP", "public"."ARENA2_CONDUCTORES"."CONFIRMADO_AMP", 

"public"."ARENA2_CONDUCTORES"."BDZ", "public"."ARENA2_CONDUCTORES"."CONFIRMADO_BDZ", 

"public"."ARENA2_CONDUCTORES"."COC", "public"."ARENA2_CONDUCTORES"."CONFIRMADO_COC", "public"."ARENA2_CONDUCTORES"."THC", 

"public"."ARENA2_CONDUCTORES"."CONFIRMADO_THC", "public"."ARENA2_CONDUCTORES"."METH", 

"public"."ARENA2_CONDUCTORES"."CONFIRMADO_METH", "public"."ARENA2_CONDUCTORES"."OPI", "public"."ARENA2_CONDUCTORES"."CONFIRMADO_OPI", 

"public"."ARENA2_CONDUCTORES"."OTRAS", "public"."ARENA2_CONDUCTORES"."CONFIRMADO_OTRAS", 

"public"."ARENA2_CONDUCTORES"."SIGNOS_INFLU_DROGAS", "public"."ARENA2_CONDUCTORES"."INFLU_PRES_INFRAC_COND", 

"public"."ARENA2_CONDUCTORES"."PRES_INFRAC_COND", "public"."ARENA2_CONDUCTORES"."PRES_INFRAC_SIN_LUCES", 

"public"."ARENA2_CONDUCTORES"."PRES_INFRAC_SIN_TRIANGULO", "public"."ARENA2_CONDUCTORES"."INFLU_PRES_INFRAC_VEL", 

"public"."ARENA2_CONDUCTORES"."PRES_INFRAC_VEL_COND", "public"."ARENA2_CONDUCTORES"."INFLU_OTRA_INFRAC", 

"public"."ARENA2_CONDUCTORES"."OTRA_INFRAC_COND", "public"."ARENA2_CONDUCTORES"."EXTRA", 

"public"."ARENA2_ACCIDENTES"."KM" AS "AccKm0", 

("public"."ARENA2_ACCIDENTES"."KM" * 100) AS "AccKm1", 

("public"."ARENA2_ACCIDENTES"."KM" * 222) AS "AccKm2", 

"public"."ARENA2_ACCIDENTES"."KM" 

FROM "public"."ARENA2_CONDUCTORES" 

LEFT JOIN "public"."ARENA2_ACCIDENTES" ON ( ("public"."ARENA2_CONDUCTORES"."ID_ACCIDENTE") = ("public"."ARENA2_ACCIDENTES"."ID_ACCIDENTE") ) 

ORDER BY "LID_CONDUCTOR" ASC NULLS LAST LIMIT 15;

-- Count

SELECT COUNT(*) FROM "public"."ARENA2_CONDUCTORES";

package org.gvsig.fmap.dal.store.h2.operations.usecases.arena2;

import java.util.List;

import junit.framework.TestCase;

import org.gvsig.expressionevaluator.ExpressionUtils;

import org.gvsig.fmap.dal.DataTypes;

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

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

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

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

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

import org.gvsig.fmap.dal.impl.expressionevaluator.DefaultFeatureAttributeEmulatorExpression;

import org.gvsig.fmap.dal.store.h2.TestUtils;

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

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

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

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

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

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

import org.gvsig.tools.library.impl.DefaultLibrariesInitializer;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

@SuppressWarnings("UseSpecificCatch")

public class TestArena2GroupByForeignValue extends TestCase {

  private static final Logger LOGGER = LoggerFactory.getLogger(TestArena2GroupByForeignValue.class);

  public TestArena2GroupByForeignValue(String testName) {

    super(testName);

  }

  @Override

  protected void setUp() throws Exception {

    super.setUp();

    new DefaultLibrariesInitializer().fullInitialize();

  }

  @Override

  protected void tearDown() throws Exception {

    super.tearDown();

  }

  protected List<String> getExpectedSQLs(String name) throws Exception {

	  return TestUtils.getSQLs(name);

  }

    public void testGroupByForeignValue() throws Exception {

        try {

            JDBCHelper helper = TestUtils.createJDBCHelper();

            JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder();

            OperationsFactory operations = helper.getOperations();

            List<String> expectedSQLs = getExpectedSQLs("usecases/arena2/testGroupByForeignValue.sql");

            FeatureStore arena2_conductores = TestUtils.openStoreTest("usecases/arena2/ARENA2_CONDUCTORES.csv");

            FeatureStore arena2_accidentes = TestUtils.openStoreTest("usecases/arena2/ARENA2_ACCIDENTES.csv");

            FeatureStore arena2_vehiculos = TestUtils.openStoreTest("usecases/arena2/ARENA2_VEHICULOS.csv");

            TableReference table = operations.createTableReference(

                    "dbtest",

                    "public", //sqlbuilder.default_schema(),

                    "ARENA2_CONDUCTORES",

                    null

            );

            FeatureType featureType = arena2_conductores.getDefaultFeatureType();

            EditableFeatureType eFeatureType = featureType.getEditable();

            FeatureQuery query = arena2_conductores.createFeatureQuery();

			query.setFilter("( ( ( (FOREING_VALUE('ID_ACCIDENTE.TITULARIDAD_VIA') = 2) )) AND \n" +

"( (( (FOREING_VALUE('LID_VEHICULO.TIPO_VEHICULO') = 5) ) OR ( (FOREING_VALUE('LID_VEHICULO.TIPO_VEHICULO') = 6) ) OR ( (FOREING_VALUE('LID_VEHICULO.TIPO_VEHICULO') = 7) )))) AND \n" +

"( (( (FOREING_VALUE('ID_ACCIDENTE.FECHA_ACCIDENTE') >= DATE '2019-01-01') ) AND ( (FOREING_VALUE('ID_ACCIDENTE.FECHA_ACCIDENTE') <= DATE '2019-12-31') )))");

            addExtraColumn(eFeatureType, query, "ano", DataTypes.INTEGER, "EXTRACT(YEAR FROM FOREING_VALUE('ID_ACCIDENTE.FECHA_ACCIDENTE'))");

            addExtraColumn(eFeatureType, query, "provincia", DataTypes.STRING, "FOREING_VALUE('ID_ACCIDENTE.COD_PROVINCIA')");

            addExtraColumn(eFeatureType, query, "lesividad", DataTypes.STRING, "CASE\n" +

"WHEN ASISTENCIA_SANITARIA=1 THEN 'Muerto'\n" +

"WHEN ASISTENCIA_SANITARIA=2 THEN 'Grave'\n" +

"WHEN ASISTENCIA_SANITARIA=3 or ASISTENCIA_SANITARIA=4 or ASISTENCIA_SANITARIA=5 or ASISTENCIA_SANITARIA=6 or ASISTENCIA_SANITARIA=7 THEN 'Leve'\n" +

"WHEN ASISTENCIA_SANITARIA=8 THEN 'Ileso'\n" +

"ELSE 'Se Desconoce'\n" +

"END CASE");

            addExtraColumn(eFeatureType, query, "Acc", DataTypes.INTEGER, "1");

            query.getGroupByColumns().add("ano");

            query.getGroupByColumns().add("provincia");

            query.getGroupByColumns().add("lesividad");

            query.getAggregateFunctions().put("Acc", "SUM");

			query.getOrder().add("provincia");

			CountOperation count = operations.createCount(eFeatureType, table, null, query);

			String sqlcount = count.getSQL();

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

			System.out.println("###### EXP:" + expectedSQLs.get(1));

            assertEquals("Count SQL", expectedSQLs.get(1), sqlcount);

            ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider(

                    table,

                    null,

                    null,

                    query,

                    eFeatureType,

                    eFeatureType,

                    0,

                    0,

                    0

            );

            String sql = resultSetForSetProvider.getSQL();

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

            System.out.println("###### EXP:" + expectedSQLs.get(0));

            assertEquals("Select SQL", expectedSQLs.get(0), sql);

        } catch (Throwable th) {

			th.printStackTrace();

            throw th;

        }

    }

	private void addExtraColumn(EditableFeatureType eFeatureType, FeatureQuery query, String name, int type, String exp) {

		EditableFeatureAttributeDescriptor extraColumn = query.getExtraColumn().add(name, type);

		extraColumn.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression(exp)));

	}

}

package org.gvsig.fmap.dal.store.h2.operations.usecases.arena2;

import java.util.List;

import junit.framework.TestCase;

import org.gvsig.expressionevaluator.ExpressionUtils;

import org.gvsig.fmap.dal.DataTypes;

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

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

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

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

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

import org.gvsig.fmap.dal.impl.expressionevaluator.DefaultFeatureAttributeEmulatorExpression;

import org.gvsig.fmap.dal.store.h2.TestUtils;

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

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

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

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

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

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

import org.gvsig.tools.library.impl.DefaultLibrariesInitializer;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

@SuppressWarnings("UseSpecificCatch")

public class TestMultipleExpansionOfCalculatedField extends TestCase {

    private static final Logger LOGGER = LoggerFactory.getLogger(TestMultipleExpansionOfCalculatedField.class);

    public TestMultipleExpansionOfCalculatedField(String testName) {

        super(testName);

    }

    @Override

    protected void setUp() throws Exception {

        super.setUp();

        new DefaultLibrariesInitializer().fullInitialize();

    }

    @Override

    protected void tearDown() throws Exception {

        super.tearDown();

    }

    protected List<String> getExpectedSQLs(String name) throws Exception {

        return TestUtils.getSQLs(name);

    }

    public void testMultipleExpansionOfCalculatedField() throws Exception {

        try {

            JDBCHelper helper = TestUtils.createJDBCHelper();

            JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder();

            OperationsFactory operations = helper.getOperations();

            List<String> expectedSQLs = getExpectedSQLs("usecases/arena2/testMultipleExpansionOfCalculatedField.sql");

            FeatureStore arena2_conductores = TestUtils.openStoreTest("usecases/arena2/ARENA2_CONDUCTORES.csv");

            FeatureStore arena2_accidentes = TestUtils.openStoreTest("usecases/arena2/ARENA2_ACCIDENTES.csv");

            FeatureStore arena2_vehiculos = TestUtils.openStoreTest("usecases/arena2/ARENA2_VEHICULOS.csv");

            TableReference table = operations.createTableReference(

                    "dbtest",

                    "public", //sqlbuilder.default_schema(),

                    "ARENA2_CONDUCTORES",

                    null

            );

            FeatureType featureType = arena2_conductores.getDefaultFeatureType();

            EditableFeatureType eFeatureType = featureType.getEditable();

            FeatureQuery query = arena2_conductores.createFeatureQuery();

            addExtraColumn(eFeatureType, query, "AccKm0", DataTypes.DOUBLE, "FOREING_VALUE('ID_ACCIDENTE.KM')");

            addExtraColumn(eFeatureType, query, "AccKm1", DataTypes.DOUBLE, "AccKm0 * 100");

            addExtraColumn(eFeatureType, query, "AccKm2", DataTypes.DOUBLE, "AccKm0 * 222");

            CountOperation count = operations.createCount(eFeatureType, table, null, query);

            String sqlcount = count.getSQL();

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

            System.out.println("###### EXP:" + expectedSQLs.get(1));

            assertEquals("Count SQL", expectedSQLs.get(1), sqlcount);

            ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider(

                    table,

                    null,

                    null,

                    query,

                    eFeatureType,

                    eFeatureType,

                    15,

                    0,

                    0

            );

            String sql = resultSetForSetProvider.getSQL();

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

            System.out.println("###### EXP:" + expectedSQLs.get(0));

            assertEquals("Select SQL", expectedSQLs.get(0), sql);

        } catch (Throwable th) {

            th.printStackTrace();

            throw th;

        }

    }

    private void addExtraColumn(EditableFeatureType eFeatureType, FeatureQuery query, String name, int type, String exp) {

        EditableFeatureAttributeDescriptor extraColumn = query.getExtraColumn().add(name, type);

        extraColumn.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression(exp)));

    }

}

package org.gvsig.fmap.dal.store.h2.operations.usecases.arena2;

import java.util.List;

import junit.framework.TestCase;

import org.gvsig.expressionevaluator.ExpressionUtils;

import org.gvsig.fmap.dal.DataTypes;

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

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

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

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

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

import org.gvsig.fmap.dal.impl.expressionevaluator.DefaultFeatureAttributeEmulatorExpression;

import org.gvsig.fmap.dal.store.h2.TestUtils;

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

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

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

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

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

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

import org.gvsig.tools.library.impl.DefaultLibrariesInitializer;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

@SuppressWarnings("UseSpecificCatch")

public class TestArena2OrderByExtraValue extends TestCase {

    private static final Logger LOGGER = LoggerFactory.getLogger(TestArena2OrderByExtraValue.class);

    public TestArena2OrderByExtraValue(String testName) {

        super(testName);

    }

    @Override

    protected void setUp() throws Exception {

        super.setUp();

        new DefaultLibrariesInitializer().fullInitialize();

    }

    @Override

    protected void tearDown() throws Exception {

        super.tearDown();

    }

    protected List<String> getExpectedSQLs(String name) throws Exception {

        return TestUtils.getSQLs(name);

    }

    public void testOrderByExtraValue() throws Exception {

        try {

            JDBCHelper helper = TestUtils.createJDBCHelper();

            JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder();

            OperationsFactory operations = helper.getOperations();

            List<String> expectedSQLs = getExpectedSQLs("usecases/arena2/testOrderByExtraValue.sql");

            FeatureStore arena2_conductores = TestUtils.openStoreTest("usecases/arena2/ARENA2_CONDUCTORES.csv");

            FeatureStore arena2_accidentes = TestUtils.openStoreTest("usecases/arena2/ARENA2_ACCIDENTES.csv");

            FeatureStore arena2_vehiculos = TestUtils.openStoreTest("usecases/arena2/ARENA2_VEHICULOS.csv");

            TableReference table = operations.createTableReference(

                    "dbtest",

                    "public", //sqlbuilder.default_schema(),

                    "ARENA2_CONDUCTORES",

                    null

            );

            FeatureType featureType = arena2_conductores.getDefaultFeatureType();

            EditableFeatureType eFeatureType = featureType.getEditable();

            FeatureQuery query = arena2_conductores.createFeatureQuery();

            query.setFilter("( ( ( (FOREING_VALUE('ID_ACCIDENTE.TITULARIDAD_VIA') = 2) )) AND \n" +

"( (( (FOREING_VALUE('LID_VEHICULO.TIPO_VEHICULO') = 5) ) OR ( (FOREING_VALUE('LID_VEHICULO.TIPO_VEHICULO') = 6) ) OR ( (FOREING_VALUE('LID_VEHICULO.TIPO_VEHICULO') = 7) )))) AND \n" +

"( (( (FOREING_VALUE('ID_ACCIDENTE.FECHA_ACCIDENTE') >= DATE '2019-01-01') ) AND ( (FOREING_VALUE('ID_ACCIDENTE.FECHA_ACCIDENTE') <= DATE '2019-12-31') )))");

            addExtraColumn(eFeatureType, query, "ano", DataTypes.INTEGER, "EXTRACT(YEAR FROM FOREING_VALUE('ID_ACCIDENTE.FECHA_ACCIDENTE'))");

            addExtraColumn(eFeatureType, query, "provincia", DataTypes.STRING, "FOREING_VALUE('ID_ACCIDENTE.COD_PROVINCIA')");

            addExtraColumn(eFeatureType, query, "lesividad", DataTypes.STRING, "CASE\n"

                    + "WHEN ASISTENCIA_SANITARIA=1 THEN 'Muerto'\n"

                    + "WHEN ASISTENCIA_SANITARIA=2 THEN 'Grave'\n"

                    + "WHEN ASISTENCIA_SANITARIA=3 or ASISTENCIA_SANITARIA=4 or ASISTENCIA_SANITARIA=5 or ASISTENCIA_SANITARIA=6 or ASISTENCIA_SANITARIA=7 THEN 'Leve'\n"

                    + "WHEN ASISTENCIA_SANITARIA=8 THEN 'Ileso'\n"

                    + "ELSE 'Se Desconoce'\n"

                    + "END CASE");

            addExtraColumn(eFeatureType, query, "Acc", DataTypes.INTEGER, "1");

            query.getGroupByColumns().add("ano");

            query.getGroupByColumns().add("provincia");

            query.getGroupByColumns().add("lesividad");

            query.getAggregateFunctions().put("Acc", "SUM");

            query.getOrder().add("ano");

            query.getOrder().add("provincia");

            query.getOrder().add("lesividad");

            CountOperation count = operations.createCount(eFeatureType, table, null, query);

            String sqlcount = count.getSQL();

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

            System.out.println("###### EXP:" + expectedSQLs.get(1));

            assertEquals("Count SQL", expectedSQLs.get(1), sqlcount);

            ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider(

                    table,

                    null,

                    null,

                    query,

                    eFeatureType,

                    eFeatureType,

                    15,

                    0,

                    0

            );

            String sql = resultSetForSetProvider.getSQL();

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

            System.out.println("###### EXP:" + expectedSQLs.get(0));

            assertEquals("Select SQL", expectedSQLs.get(0), sql);

        } catch (Throwable th) {

            th.printStackTrace();

            throw th;

        }

    }

    private void addExtraColumn(EditableFeatureType eFeatureType, FeatureQuery query, String name, int type, String exp) {

        EditableFeatureAttributeDescriptor extraColumn = query.getExtraColumn().add(name, type);

        extraColumn.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression(exp)));

    }

}