Application: gvSIG desktop

    private static final boolean ALLOW_AUTOMATIC_VALUES = true;

    private static final String QUOTE_FOR_USE_IN_IDENTIFIERS = "\"";

    private static final String QUOTE_FOR_USE_IN_STRINGS = "'";

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

    private ResulSetControler resulSetControler = null;

    // Quien ha creado este helper.

    // Se le reenviaran las notificaciones del recurso asociado al helper.

    private ResourceConsumer helperClient = null;

    private GeometryManager geometryManager = null;

    private JDBCConnectionParameters connectionParameters;

    private JDBCStoreProvider store;

    protected OperationsFactory operationsFactory = null;

    protected SRSSolver srssolver;

    public JDBCHelperBase(JDBCConnectionParameters connectionParameters) {

        this.connectionParameters = connectionParameters;

        // If a particular treatment is required to convert SRS to the 

        // BBDD format, overwrite JDBCSRSsBase and use it instead of JDBCSRSsDumb.

        this.srssolver = new SRSSolverDumb(this);

    }

    protected void initialize(

            ResourceConsumer helperClient,

            JDBCConnectionParameters connectionParameters,

            JDBCStoreProvider store

    ) {

        this.store = store;

        this.helperClient = helperClient;

        this.connectionParameters = connectionParameters;

        initializeResource(connectionParameters);

    }

    protected String getResourceType() {

        return JDBCResource.NAME;

    }

    @Override

    public String getProviderName() {

        return JDBCLibrary.NAME;

    }

    @Override

    public GeometrySupportType getGeometrySupportType() {

        // El proveedor generico de JDBC guadara las geoemtrias como WKT

        return GeometrySupportType.WKT;

    }

    @Override

    public boolean hasSpatialFunctions() {

        // Por defecto el proveedor generico de JDBC asume que la BBDD no 

        // tiene soporte para funciones espaciales.

        return false;

    }

    @Override

    public boolean allowNestedOperations() {

        return false;

    }

    @Override

    public boolean canWriteGeometry(int geometryType, int geometrySubtype) {

        // Como va a guardar las geometrias en WKT, puede escribirlas todas.

        return true;

    }

    @Override

    public JDBCSQLBuilderBase createSQLBuilder() {

        return new JDBCSQLBuilderBase(this);

    }

    @Override

    public String getQuoteForIdentifiers() {

        return QUOTE_FOR_USE_IN_IDENTIFIERS;

    }

    @Override

    public boolean allowAutomaticValues() {

        return ALLOW_AUTOMATIC_VALUES;

    }

    @Override

    public boolean supportOffsetInSelect() {

        // Asumimos que la BBDD soporta OFFSET

        return true;

    }

    @Override

    public String getQuoteForStrings() {

        return QUOTE_FOR_USE_IN_STRINGS;

    }

    protected boolean supportCaller(Code.Caller caller) {

        if (StringUtils.equalsIgnoreCase(caller.name(), "FOREING_VALUE")) {

            return true;

        }

        Function function = caller.function();

        if (function == null) {

            return false;

        }

        if (!function.isSQLCompatible()) {

            return false;

        }

        if (!this.hasSpatialFunctions()) {

            if (StringUtils.equalsIgnoreCase(function.group(), Function.GROUP_OGC)) {

                return false;

            }

        }

        return true;

    @Override

    public boolean supportFilter(final FeatureType type, Evaluator filter) {

        // No podemos filtrar cuando:

        // - Hay una subquery especificada en los parametros 

        // - Si hay un filtro y el getSQL devuelbe null.

        // - Si se esta usando alguna funcion no-sql en el getSQL

        // - Si se estan usando funciones OGC y no soportamos funciones espaciales

        // - Si se esta usando algun campo calculado en la expresion de filtro.

        // 

        // Un proveedor especifico podria sobreescribir el metodo,

        // para hilar mas fino al comprobar si soporta el filtro o no.

        //

        if (this.useSubquery()) {

            // Se esta usando una subquery en los parametros de acceso a la

            // BBDD, asi que no podemos filtrar.

            return false;

        }

        if (filter == null) {

            // No hay que filtrar nada, asi que se p?ede.

            return true;

        }

        String sql = filter.getSQL();

        if (StringUtils.isEmpty(sql)) {

            // Hay un filtro, pero la SQL es null, con lo que no podemos

            // filtrar por el.

            return false;

        }

        // Ahora vamos a comprobar que las funciones que se usan son 

        // compatibles sql, y que no se usan funciones OGC si el 

        // proveedor dice que no soporta funciones espaciales.

        // Tambien comprobaremos que el filtro no usa ningun campo calculado.

        final MutableBoolean isCompatible = new MutableBoolean(true);

        ExpressionEvaluatorManager manager = ExpressionEvaluatorLocator.getManager();

        Code code = manager.compile(sql);

        SymbolTable symbolTable = manager.createSymbolTable();

        code.link(symbolTable);

        try {

            code.accept(new Visitor() {

                @Override

                public void visit(Object code_obj) throws VisitCanceledException, BaseException {

                    Code code = (Code) code_obj;

                    switch (code.code()) {

                        case Code.CALLER:

                            Code.Caller caller = (Code.Caller) code;

                            if (!supportCaller(caller)) {

                                isCompatible.setValue(false);

                                throw new VisitCanceledException();

                            }

                            break;

                        case Code.IDENTIFIER:

                            Code.Identifier identifier = (Code.Identifier) code;

                            if (type != null) {

                                FeatureAttributeDescriptor attrdesc = type.getAttributeDescriptor(identifier.name());

                                if (attrdesc != null) {

                                    if (attrdesc.isComputed()) {

                                        isCompatible.setValue(false);

                                        throw new VisitCanceledException();

                                    }

                                }

                            }

                            break;

                    }

            });

        } catch (VisitCanceledException ex) {

            // Do nothing

        } catch (Exception ex) {

            LOGGER.warn("Can't calculate if is SQL compatible.", ex);

        return isCompatible.booleanValue();

    @Override

    public boolean supportOrder(FeatureType type, FeatureQueryOrder order) {

        if (this.useSubquery()) {

            return false;

        if (order == null) {

            return true;

        }

        for (FeatureQueryOrder.FeatureQueryOrderMember member : order.members()) {

            if (member.hasEvaluator()) {

                if (!this.supportFilter(type, member.getEvaluator())) {

                    return false;

                }

            }

        }

        return true;

    @Override

    public OperationsFactory getOperations() {

        if (this.operationsFactory == null) {

            this.operationsFactory = new OperationsFactoryBase(this);

        }

        return operationsFactory;

    protected void initializeResource(JDBCConnectionParameters params) {

//            logger.debug("Can't initialize resource (" + ArrayUtils.toString(resourceParams) + ").", ex);

    }

    @Override

    public String getSourceId() {

        return this.store.getSourceId();

    }

    @Override

    public JDBCResource getResource() {

        return null;

    }

    @Override

    public Connection getConnection() throws AccessResourceException {

        throw new NotYetImplemented();

    }

    @Override

    public Connection getConnectionWritable() throws AccessResourceException {

        return this.getConnection();

    }

    @Override

    public String getConnectionURL() {

        return null;

    }

    @Override

    public JDBCConnectionParameters getConnectionParameters() {

        return connectionParameters;

    }

    @Override

    public void closeConnection(Connection connection) {

        if (connection != null) {

            LOGGER.debug("Clossing connection " + connection.hashCode());

            try {

                connection.close();

            } catch (Exception ex) {

                LOGGER.warn("Can't close connection.", ex);

            }

        }

    @Override

    public void closeConnectionQuietly(Connection connection) {

        if (connection != null) {

            LOGGER.debug("Clossing connection quietly " + connection.hashCode());

            try {

                connection.close();

            } catch (Exception ex) {

                LOGGER.warn("Can't close connection.", ex);

            }

        }

    @Override

    protected void doDispose() throws BaseException {

        JDBCUtils.closeQuietly(this);

    }

    @Override

    public void close() throws Exception {

        JDBCUtils.closeQuietly(this.resulSetControler);

        this.resulSetControler = null;

    }

    @Override

    public boolean closeResourceRequested(ResourceProvider resource) {

        return this.helperClient.closeResourceRequested(resource);

    }

    @Override

    public void resourceChanged(ResourceProvider resource) {

        this.helperClient.resourceChanged(resource);

    }

    @Override

    public GeometryManager getGeometryManager() {

        if (this.geometryManager == null) {

            this.geometryManager = GeometryLocator.getGeometryManager();

        }

        return this.geometryManager;

    @Override

    public ResulSetControler getResulSetControler() {

        if (this.resulSetControler == null) {

            this.resulSetControler = new ResulSetControlerBase(this);

        }

        return this.resulSetControler;

    @Override

    public void fetchFeature(FeatureProvider feature, ResultSetEntry rs) throws DataException {

        fetchFeature(feature, rs.get(), rs.getColumns(), rs.getExtraValueNames());

    }

    @Override

    public void fetchFeature(FeatureProvider feature, ResultSet rs, FeatureAttributeDescriptor[] columns, String[] extraValueNames) throws DataException {

        Object value;

        try {

            int rsIndex = 1;

            for (FeatureAttributeDescriptor column : columns) {

                switch (column.getType()) {

                    case DataTypes.GEOMETRY:

                        value = this.getGeometryFromColumn(rs, rsIndex++);

                        break;

                    default:

                        value = rs.getObject(rsIndex++);

                        if (value instanceof Blob) {

                            Blob blob = (Blob) value;

                            value = blob.getBytes(0, (int) blob.length());

                            blob.free();

                        }

                }

                feature.set(column.getIndex(), value);

            if (ArrayUtils.isNotEmpty(extraValueNames)) {

                feature.setExtraValueNames(extraValueNames);

                for (int index = 0; index < extraValueNames.length; index++) {

                    value = rs.getObject(rsIndex++);

                    if (value instanceof Blob) {

                        Blob blob = (Blob) value;

                        value = blob.getBytes(0, (int) blob.length());

                        blob.free();

                    }

                    feature.setExtraValue(index, value);

                }

            }

        } catch (Exception ex) {

            throw new JDBCCantFetchValueException(ex);

    @Override

    public Geometry getGeometryFromColumn(ResultSetEntry rs, int index) throws DataException {

        return getGeometryFromColumn(rs.get(), index);

    }

    @Override

    public Geometry getGeometryFromColumn(ResultSet rs, int index) throws DataException {

        try {

            Object value;

            switch (this.getGeometrySupportType()) {

                case NATIVE:

                case WKB:

                    value = rs.getBytes(index);

                    if (value == null) {

                        return null;

                    }

                    return this.getGeometryManager().createFrom((byte[]) value);

                case EWKB:

                    value = rs.getBytes(index);

                    if (value == null) {

                        return null;

                    }

                    return this.getGeometryManager().createFrom((byte[]) value);

                case WKT:

                default:

                    value = rs.getString(index);

                    if (value == null) {

                        return null;

                    }

                    return this.getGeometryManager().createFrom((String) value);

            }

        } catch (Exception ex) {

            throw new JDBCCantFetchValueException(ex);

        }

    @Override

    public FeatureProvider createFeature(FeatureType featureType) throws DataException {

        return this.store.getStoreServices().createDefaultFeatureProvider(featureType);

    }

    @Override

    public boolean useSubquery() {

        if (this.store == null) {

            return false;

        }

        return !StringUtils.isEmpty(this.store.getParameters().getSQL());

    @Override

    public SRSSolver getSRSSolver() {

        return this.srssolver;

    }

    @Override

    public JDBCStoreProvider createProvider(

            JDBCStoreParameters parameters,

            DataStoreProviderServices providerServices

    ) throws InitializeException {

        JDBCStoreProviderBase theStore = new JDBCStoreProviderBase(

                parameters,

                providerServices,

                DBHelper.newMetadataContainer(JDBCLibrary.NAME),

                this

        );

        this.initialize(theStore, parameters, theStore);

        return theStore;

    }

    @Override

    public JDBCServerExplorer createServerExplorer(

            JDBCServerExplorerParameters parameters,

            DataServerExplorerProviderServices providerServices

    ) throws InitializeException {

        JDBCServerExplorer explorer = new JDBCServerExplorerBase(

                parameters,

                providerServices,

                this

        );

        this.initialize(explorer, parameters, null);

        return explorer;

    }

    @Override

    public JDBCNewStoreParameters createNewStoreParameters() {

        return new JDBCNewStoreParameters();

    }

    @Override

    public JDBCStoreParameters createOpenStoreParameters() {

        return new JDBCStoreParameters();

    }

    @Override

    public JDBCServerExplorerParameters createServerExplorerParameters() {

        return new JDBCServerExplorerParameters();

    }

    @Override

    public String getSourceId(JDBCStoreParameters parameters) {

        return parameters.getHost() + ":"

                + parameters.getDBName() + ":"

                + parameters.getSchema() + ":"

                + parameters.tableID();

    }

    @Override

    public boolean isThreadSafe() {

        return true;

    }

    @Override

    public void processSpecialFunctions(

            SQLBuilder sqlbuilder, 

            FeatureType type,

            List<String> extra_column_names) {

      replaceForeingValueFunction(sqlbuilder, type, extra_column_names);

      replaceExistsFunction(sqlbuilder, type, extra_column_names);

    private void replaceExistsFunction(

            SQLBuilder sqlbuilder, 

            FeatureType type,

            List<String> extra_column_names) {

      // TODO: replaceExistsFunction

    }

    private void replaceForeingValueFunction(

            SQLBuilder sqlbuilder, 

            FeatureType type,

            List<String> extra_column_names) {

      try {

        // See test SQLBuilderTest->testForeingValue()

        final ExpressionBuilder where = sqlbuilder.select().where();

        if (where == null || where.isEmpty()) {

            return;

        final SQLBuilder.TableNameBuilder table = sqlbuilder.select().from().table();

        final ExpressionBuilder expbuilder = sqlbuilder.expression();

        final List<String> foreing_value_args;

        if( extra_column_names==null ) {

          foreing_value_args = new ArrayList<>();

        } else {

          foreing_value_args = extra_column_names;

        final List<ExpressionBuilder.Value[]> value_replacements = new ArrayList<>();

        // Buscamos las llamadas a la funcion "foreing_value" y nos quedamos

        // el argumento de esta asi como por que tendriamos que sustituirla 

        // una vez hechos los left joins que toquen.

        where.accept(new ExpressionBuilder.Visitor() {

            @Override

            public void visit(ExpressionBuilder.Visitable value) {

                // Requiere que sea la funcion "FOREING_VALUE con un solo 

                // argumento que sea una constante de tipo string.

                if (!(value instanceof ExpressionBuilder.Function)) {

                    return;

                }

                ExpressionBuilder.Function function = (ExpressionBuilder.Function) value;

                if (!StringUtils.equalsIgnoreCase(function.name(), "FOREING_VALUE")) {

                    return;

                }

                if (function.parameters().size() != 1) {

                    return;

                }

                ExpressionBuilder.Value arg = function.parameters().get(0).getValue();

                if (!(arg instanceof ExpressionBuilder.Constant)) {

                    return;

                }

                Object arg_value = ((ExpressionBuilder.Constant) arg).value();

                if (!(arg_value instanceof CharSequence)) {

                    return;

                }

                String foreing_value_arg = arg_value.toString();

                String[] foreingNameParts = StringUtils.split(foreing_value_arg, "[.]");

                if (foreingNameParts.length != 2) {

                    // De momento solo tratamos joins entre dos tablas.

                    return;

                }

                String columnNameLocal = foreingNameParts[0];

                String columnNameForeing = foreingNameParts[1];

                FeatureAttributeDescriptor attr = type.getAttributeDescriptor(columnNameLocal);

                if (!attr.isForeingKey()) {

                    // Uhm... si el argumento no referencia a un campo que es

                    // clave ajena no lo procesamos. 

                    // ? Deberiamos lanzar un error ?

                    return;

                }

                // Nos guardaremos por que hay que reemplazar la funcion 

                // FOREING_VALUE, y su argumento para mas tarde.

                ForeingKey foreingKey = attr.getForeingKey();

                SQLBuilder.TableNameBuilder foreingTable = sqlbuilder.createTableNameBuilder()

                        .database(table.getDatabase())

                        .schema(table.getSchema())

                        .name(foreingKey.getTableName());

                // Reemplzaremos la funcion FOREING_VALUE, por el acceso al campo

                // que toca de la tabla a la que referencia la clave ajena.

                ExpressionBuilder.Variable function_replacement = sqlbuilder.column(foreingTable, columnNameForeing);

                value_replacements.add(

                        new ExpressionBuilder.Value[]{

                            function,

                            function_replacement

                        }

                );

                foreing_value_args.add(foreing_value_arg);

            }

        }, null);

        // Si no habia ningun llamada a la funcion FOREING_VALUE, no hay que

        // hacer nada.

        if (foreing_value_args.isEmpty()) {

        // Calculamos que referencias de columnas hemos de cambiar para 

        // que no aparezcan ambiguedades al hacer el join (nombres de

        // columna de una y otra tabla que coincidan).

        // Para las columnas que puedan dar conflicto se prepara un reemplazo 

        // de estas que tenga el nombre de tabla.

        for (ExpressionBuilder.Variable variable : sqlbuilder.variables()) {

            if (variable instanceof SQLBuilderBase.ColumnBase) {

                continue;

            }

            for (String foreingName : foreing_value_args) {

                String[] foreingNameParts = foreingName.split("[.]");

                if (foreingNameParts.length != 2) {

                    continue;

                }

                String columnNameLocal = foreingNameParts[0];

                String columnNameForeing = foreingNameParts[1];

                if (StringUtils.equalsIgnoreCase(variable.name(), columnNameForeing)

                        || StringUtils.equalsIgnoreCase(variable.name(), columnNameLocal)) {

                    ExpressionBuilder.Variable variable_replacement = sqlbuilder.column(

                            table,

                            variable.name()

                    );

                    value_replacements.add(

                            new ExpressionBuilder.Value[]{

                                variable,

                                variable_replacement

                            }

                    );

                }

            }

        }

        // Realizamos los reemplazos calculados previamente (value_replacements).

        for (ExpressionBuilder.Value[] replaceValue : value_replacements) {

            ExpressionBuilder.Value target = replaceValue[0];

            ExpressionBuilder.Value replacement = replaceValue[1];

            sqlbuilder.select().replace(target, replacement);

        // A?adimos a la SQL los "LEFT JOIN" que toca para poder acceder

        // a los valores referenciados por la funcion FOREING_VALUE.

        // Ademas a?adimos los valores referenciados por la funcion FOREING_VALUE

        // como columnas de la SQL para poder acceder a ellos si fuese necesario.

            String[] foreingNameParts = foreingName.split("[.]");

            if (foreingNameParts.length != 2) {

                continue;

            }

            String columnNameLocal = foreingNameParts[0];

            String columnNameForeing = foreingNameParts[1];

            FeatureAttributeDescriptor attr = type.getAttributeDescriptor(columnNameLocal);

            if (attr.isForeingKey()) {

                ForeingKey foreingKey = attr.getForeingKey();

                SQLBuilder.TableNameBuilder foreingTable = sqlbuilder.createTableNameBuilder()

                        .database(table.getDatabase())

                        .schema(table.getSchema())

                        .name(foreingKey.getTableName());

                SQLBuilder.TableNameBuilder mainTable = sqlbuilder.createTableNameBuilder()

                        .database(table.getDatabase())

                        .schema(table.getSchema())

                        .name(table.getName());

                sqlbuilder.select().from()

                        .left_join(

                                foreingTable,

                                expbuilder.eq(

                                        sqlbuilder.column(mainTable, columnNameLocal),

                                        sqlbuilder.column(foreingTable, foreingKey.getCodeName())

                                )

                        );

                sqlbuilder.select().column().name(foreingTable, columnNameForeing);

            }

      } catch(Throwable th) {

        LOGGER.warn("Can't replace FORENG_VALUE function.", th);

        throw th;

      } finally {

        LOGGER.debug("Exist from replaceForeingValueFunction.");