Application: gvSIG desktop

/**

 * gvSIG. Desktop Geographic Information System.

 *

 * Copyright (C) 2007-2020 gvSIG Association.

 *

 * This program is free software; you can redistribute it and/or

 * modify it under the terms of the GNU General Public License

 * as published by the Free Software Foundation; either version 3

 * of the License, or (at your option) any later version.

 *

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 * GNU General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,

 * MA  02110-1301, USA.

 *

 * For any additional information, do not hesitate to contact us

 * at info AT gvsig.com, or visit our website www.gvsig.com.

 */

package org.gvsig.fmap.dal.store.jdbc2.spi;

import java.io.File;

import java.sql.Blob;

import java.sql.Clob;

import java.sql.ResultSet;

import java.util.ArrayList;

import java.util.HashSet;

import java.util.Iterator;

import java.util.List;

import java.util.ListIterator;

import java.util.function.Predicate;

import org.apache.commons.collections.CollectionUtils;

import org.apache.commons.collections.iterators.ReverseListIterator;

import org.apache.commons.io.IOUtils;

import org.apache.commons.lang3.ArrayUtils;

import org.apache.commons.lang3.StringUtils;

import org.apache.commons.lang3.mutable.MutableBoolean;

import org.apache.commons.lang3.mutable.MutableObject;

import org.apache.commons.lang3.tuple.ImmutablePair;

import org.apache.commons.lang3.tuple.Pair;

import org.gvsig.expressionevaluator.Code;

import org.gvsig.expressionevaluator.Expression;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import static org.gvsig.expressionevaluator.ExpressionBuilder.FUNCTION_$CONSTANT;

import static org.gvsig.expressionevaluator.ExpressionBuilder.FUNCTION_$IDENTIFIER;

import org.gvsig.expressionevaluator.ExpressionEvaluatorLocator;

import org.gvsig.expressionevaluator.ExpressionEvaluatorManager;

import org.gvsig.expressionevaluator.Function;

import org.gvsig.expressionevaluator.GeometryExpressionBuilderHelper.GeometrySupportType;

import org.gvsig.expressionevaluator.SymbolTable;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.DataManager;

import static org.gvsig.fmap.dal.DataManager.FUNCTION_EXISTS;

import static org.gvsig.fmap.dal.DataManager.FUNCTION_FOREING_VALUE;

import org.gvsig.fmap.dal.DataTypes;

import org.gvsig.fmap.dal.SQLBuilder;

import org.gvsig.fmap.dal.SQLBuilder.Column;

import static org.gvsig.fmap.dal.SQLBuilder.PROP_ADD_TABLE_NAME_TO_COLUMNS;

import org.gvsig.fmap.dal.SQLBuilder.SelectBuilder;

import org.gvsig.fmap.dal.exception.DataException;

import org.gvsig.fmap.dal.exception.InitializeException;

import org.gvsig.fmap.dal.expressionevaluator.FeatureAttributeEmulatorExpression;

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

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

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

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

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

import org.gvsig.fmap.dal.feature.spi.FeatureProvider;

import org.gvsig.fmap.dal.feature.spi.SQLBuilderBase;

import org.gvsig.fmap.dal.resource.exception.AccessResourceException;

import org.gvsig.fmap.dal.resource.spi.ResourceConsumer;

import org.gvsig.fmap.dal.resource.spi.ResourceProvider;

import org.gvsig.fmap.dal.serverexplorer.filesystem.FilesystemStoreParameters;

import org.gvsig.fmap.dal.spi.DataServerExplorerProviderServices;

import org.gvsig.fmap.dal.spi.DataStoreProviderServices;

import org.gvsig.fmap.dal.spi.DataTransactionServices;

import org.gvsig.fmap.dal.store.db.DBHelper;

import org.gvsig.fmap.dal.store.jdbc.JDBCConnectionParameters;

import org.gvsig.fmap.dal.store.jdbc.JDBCNewStoreParameters;

import org.gvsig.fmap.dal.store.jdbc.JDBCNewStoreParametersBase;

import org.gvsig.fmap.dal.store.jdbc.JDBCResourceBase;

import org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorerParameters;

import org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorerParametersBase;

import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters;

import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParametersBase;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCCantFetchValueException;

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

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

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

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

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.ResulSetControler.ResultSetEntry;

import org.gvsig.fmap.dal.store.jdbc2.impl.ResulSetControlerBase;

import org.gvsig.fmap.dal.store.jdbc2.spi.expressionbuilder.formatters.ComputedAttribute;

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

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.GeometryLocator;

import org.gvsig.fmap.geom.GeometryManager;

import org.gvsig.tools.dispose.impl.AbstractDisposable;

import org.gvsig.tools.evaluator.Evaluator;

import org.gvsig.tools.exception.BaseException;

import org.gvsig.tools.exception.NotYetImplemented;

import org.gvsig.tools.locator.LocatorException;

import org.gvsig.tools.util.ContainerUtils;

import org.gvsig.tools.visitor.FilteredVisitable;

import org.gvsig.tools.visitor.VisitCanceledException;

import org.gvsig.tools.visitor.Visitor;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

@SuppressWarnings("UseSpecificCatch")

public class JDBCHelperBase extends AbstractDisposable implements ResourceConsumer, JDBCHelper {

  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;

  protected FeatureType providerFeatureType = null;

  protected DataTransactionServices transaction;

  public JDBCHelperBase(JDBCConnectionParameters connectionParameters) {

        if (connectionParameters == null) {

            throw new IllegalArgumentException("Connection parameters can't be null.");

        }

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

  }

    /**

     * @return the providerFeatureType

     */

  @Override

    public FeatureType getProviderFeatureType() {

        return providerFeatureType;

    }

    /**

     * @param providerFeatureType the providerFeatureType to set

     */

  @Override

    public void setProviderFeatureType(FeatureType providerFeatureType) {

        this.providerFeatureType = providerFeatureType;

    }

  @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.Callable 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

  @SuppressWarnings("Convert2Lambda")

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

    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;

    }

    return this.supportExpression(type, sql);

  }

    @Override

    @SuppressWarnings("Convert2Lambda")

    public boolean supportExpression(final FeatureType type, String sql) {

        // La expression no es valida si:

        // - Hay una subquery especificada en los parametros 

        // - Si la sql es null.

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

        // - Si se esta invocando a metodos de un objeto

        // - 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 (StringUtils.isEmpty(sql)) {

            return false;

        }

//        if (this.useSubquery()) {

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

//            // BBDD, asi que no podemos filtrar.

//            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 code1 = (Code) code_obj;

                    switch (code1.code()) {

                        case Code.CALLABLE:

                            Code.Callable caller = (Code.Callable) code1;

                            if (!supportCaller(caller)) {

                                isCompatible.setValue(false);

                                throw new VisitCanceledException();

                            }

                            break;

                        case Code.METHOD:

                            isCompatible.setValue(false);

                            throw new VisitCanceledException();

                        case Code.IDENTIFIER:

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

                            if (type != null) {

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

                                if (attrdesc != null) {

                                    if (attrdesc.isComputed()) {

                                        FeatureAttributeEmulator emulator = attrdesc.getFeatureAttributeEmulator();

                                        if (!(emulator instanceof FeatureAttributeEmulatorExpression)) {

                                            isCompatible.setValue(false);

                                            throw new VisitCanceledException();

                                        }

                                        Expression expr = ((FeatureAttributeEmulatorExpression) emulator).getExpression();

                                        if (!supportExpression(type, expr.getPhrase())) {

                                            isCompatible.setValue(false);

                                            throw new VisitCanceledException();

                                        }

                                    }

                                }

                            }

                            break;

                    }

                } 

            }, new Predicate<FilteredVisitable>() {

                @Override

                public boolean test(FilteredVisitable code0) {

                    if (code0 instanceof Code.Callable) {

                        String name = ((Code.Callable) code0).name();

                        return StringUtils.equalsIgnoreCase(name, FUNCTION_$CONSTANT)

                                || StringUtils.equalsIgnoreCase(name, FUNCTION_$IDENTIFIER);

                    }

                    return false;

                }

            });

        } 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 && !isClosed()) {

      this.operationsFactory = new OperationsFactoryBase(this);

    }

    return operationsFactory;

  }

  protected void initializeResource(JDBCConnectionParameters params) {

//        Object[] resourceParams = new Object[]{

//            params.getUrl(),

//            params.getHost(),

//            params.getPort(),

//            params.getDBName(),

//            params.getUser(),

//            params.getPassword(),

//            params.getJDBCDriverClassName()

//        };

//

//        try {

//            ResourceManagerProviderServices manager

//                    = (ResourceManagerProviderServices) DALLocator.getResourceManager();

//            JDBCResourceBase resource = (JDBCResourceBase) manager.createAddResource(

//                    this.getResourceType(),

//                    resourceParams

//            );

//            this.resource = resource;

//            this.resource.addConsumer(this);

//        } catch (InitializeException ex) {

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

//            throw new RuntimeException("Can't initialize resource (" + ArrayUtils.toString(resourceParams) + ").", ex);

//        }

  }

  @Override

  public String getSourceId() {

    return this.store.getSourceId();

  }

  @Override

  public JDBCResourceBase getResource() {

    return null;

//        return this.resource;

  }

  @Override

  public JDBCConnection getConnection() throws AccessResourceException {

    throw new NotYetImplemented();

  }

  @Override

  public JDBCConnection getConnectionWritable() throws AccessResourceException {

    return this.getConnection();

  }

  @Override

  public String getConnectionURL() {

    return null;

  }

  @Override

  public JDBCConnectionParameters getConnectionParameters() {

    return connectionParameters;

  }

  @Override

  protected void doDispose() throws BaseException {

    JDBCUtils.closeQuietly(this);

  }

  @Override

  public void close() throws Exception {

    JDBCUtils.closeQuietly(this.resulSetControler);

    this.resulSetControler = null;

    this.operationsFactory = null;

    this.srssolver = null;

    this.connectionParameters = null;

    this.helperClient = null;

    this.geometryManager = null;

    this.providerFeatureType = null;

    this.store = null;

    this.transaction = null;

  }

  public boolean isClosed() {

      return this.srssolver == 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(1, (int) blob.length());

              blob.free();

            } else if(value instanceof Clob) {

              Clob clob = (Clob) value;

              value = new String(IOUtils.toCharArray(clob.getCharacterStream()));

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

      Geometry geom;

      switch (this.getGeometrySupportType()) {

        case NATIVE:

        case WKB:

          value = rs.getBytes(index);

          if (value == null) {

            return null;

          }

          geom = this.getGeometryManager().createFrom((byte[]) value);

          if( geom == null ) {

              LOGGER.debug("Can't get geometry from bytes of column "+index);

          }

          return geom;

        case EWKB:

          value = rs.getBytes(index);

          if (value == null) {

            return null;

          }

          geom = this.getGeometryManager().createFrom((byte[]) value);

          if( geom == null ) {

              LOGGER.debug("Can't get geometry from bytes of column "+index);

          }

          return geom;

        case WKT:

        default:

          value = rs.getString(index);

          if (value == null) {

            return null;

          }

          geom = this.getGeometryManager().createFrom((String) value);

          if( geom == null ) {

              LOGGER.debug("Can't get geometry from bytes of column "+index);

          }

          return geom;

      }

    } 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 JDBCNewStoreParametersBase();

  }

  @Override

  public JDBCStoreParameters createOpenStoreParameters() {

    return new JDBCStoreParametersBase();

  }

  @Override

  public JDBCStoreParameters createOpenStoreParameters(JDBCServerExplorerParameters parameters) {

    JDBCStoreParameters params = this.createOpenStoreParameters();

    params.setHost(parameters.getHost());

    params.setPort(parameters.getPort());

    params.setDBName(parameters.getDBName());

    params.setUser(parameters.getUser());

    params.setPassword(parameters.getPassword());

    params.setCatalog(parameters.getCatalog());

    params.setSchema(parameters.getSchema());

    params.setJDBCDriverClassName(parameters.getJDBCDriverClassName());

    params.setUrl(parameters.getUrl());

    if( parameters instanceof FilesystemStoreParameters ) {

        File f = ((FilesystemStoreParameters) parameters).getFile();

        ((FilesystemStoreParameters) params).setFile(f);

    }

    params.setBatchSize(parameters.getBatchSize());

    return params;

  }

  @Override

  public JDBCServerExplorerParameters createServerExplorerParameters() {

    return new JDBCServerExplorerParametersBase();

  }

  @Override

  public String getSourceId(JDBCStoreParameters parameters) {

    return parameters.getHost() + ":"

            + parameters.getDBName() + ":"

            + parameters.getSchema() + ":"

            + parameters.tableID();

  }

  @Override

  public boolean isThreadSafe() {

    return true;

  }

  /** 

   * This method has been overriden in Oracle provider

   * 

   * @param sqlbuilder

   * @param type

   * @param extra_column_names 

   */

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

    addTableToColumnReferences(sqlbuilder, type);

  }

  @SuppressWarnings("Convert2Lambda")

  protected void replaceExistsFunction(

          SQLBuilder sqlbuilder,

          FeatureType type,

          final List<String> extra_column_names) {

    //  Si lse encuentra una construccion del tipo:

    //    SELECT ... FROM ... WHERE ... EXISTS(list, 'EXISTS_ID') ...

    //  se traslada a:

    //    SELECT ... EXISTS(list) AS EXISTS_ID FROM ... WHERE ... EXISTS(list) ...

    //  Y se a?ade el valor ESISTS_ID a las columnas extra a recuperar de la consulta.

    //          

    final SQLBuilder.SelectBuilder select = sqlbuilder.select();

    final ExpressionBuilder where = select.where();

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

      return;

    }

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

    where.accept(new ExpressionBuilder.Visitor() {

      @Override

      public void visit(ExpressionBuilder.Visitable value) {

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

          return;

        }

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

        if (!StringUtils.equalsIgnoreCase(function.name(), FUNCTION_EXISTS)) {

          return;

        }

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

          return;

        }

        ExpressionBuilder.Value arg0 = function.parameters().get(0);

        ExpressionBuilder.Value arg1 = function.parameters().get(1);

        if (arg1 == null) {

          return;

        }

        String columnName = (String) ((ExpressionBuilder.Constant) arg1).value();

        SQLBuilder.SelectColumnBuilder column = select.column();

        column.value(

                sqlbuilder.expression().function(FUNCTION_EXISTS, arg0)

        );

        column.as(columnName);

        if( extra_column_names!=null ) {

          extra_column_names.add(columnName);

        }

      }

    }, null);

    if (value_replacements.isEmpty()) {

      return;

    }

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

    }

  }

  @SuppressWarnings("Convert2Lambda")

  protected 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 || sqlbuilder.select().has_group_by() || sqlbuilder.select().has_aggregate_functions() ) {

        foreing_value_args = new ArrayList<>();

      } else {

        foreing_value_args = extra_column_names;

      }

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

      MutableObject<Boolean> hasForeignValueFunctions = new MutableObject<>(false);

      // 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.

      sqlbuilder.select().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(), FUNCTION_FOREING_VALUE)) {

            return;

          }

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

            return;

          }

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

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

            return;

          }

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

          if (!(arg_value instanceof CharSequence)) {

            return;

          }

          hasForeignValueFunctions.setValue(true);

          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==null) {

              throw new RuntimeException("Cannot find in feature type attribute:"+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

                  }

          );

          if (!foreing_value_args.contains(foreing_value_arg)) {

            foreing_value_args.add(foreing_value_arg);

          }

        }

      }, null);

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

      // hacer nada.

      if ( !hasForeignValueFunctions.getValue() || foreing_value_args.isEmpty()) {

        return;

      }

      // 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 == null || variable instanceof SQLBuilderBase.ColumnBase) {

          continue;

        }

        if (ContainerUtils.contains(extra_column_names, variable.name(), ContainerUtils.EQUALS_IGNORECASE_COMPARATOR)) {

            continue;

        }

        boolean alreadyReplaced = false;

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

              if(alreadyReplaced){

                  throw new RuntimeException("Column reference \""+ columnNameForeing+"\" is ambiguous");

              }

              alreadyReplaced = true;

              FeatureAttributeDescriptor attr = type.getAttributeDescriptor(columnNameLocal);

              if (attr == null) {

                  throw new RuntimeException("Cannot find in feature type attribute:" + columnNameLocal);

              }

              if (attr.isForeingKey()) {

                  // 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());

                  ExpressionBuilder.Variable variable_replacement = sqlbuilder.column(

                          foreingTable,

                          variable.name()

                  );

                  value_replacements.add(

                          new ExpressionBuilder.Value[]{

                              variable,

                              variable_replacement

                          }

                  );

              }

          }

          if (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.

        HashSet usedLeftJoins = new HashSet();

      for (String foreingName : foreing_value_args) {

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

          if (!usedLeftJoins.contains(foreingTable.getName())) {

            sqlbuilder.select().from()

                    .left_join(

                            foreingTable,

                            expbuilder.eq(

                                    sqlbuilder.column(mainTable, columnNameLocal),

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

                            )

                    );

            usedLeftJoins.add(foreingTable.getName());

          }

          //No est? claro si debe a?adirse esta columna o no, OJO quitarlo o ponerlo altera los test de  H2

          //Si se comentarizan ojo con extra_column_names ya que se habr?n a?adido columnas que no estar?n en la select

          if ( !(sqlbuilder.select().has_group_by() || sqlbuilder.select().has_aggregate_functions()) ) {

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

          }

        }

      }

    } catch (Throwable th) {

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

      throw th;

    } finally {

//      LOGGER.trace("Exit from replaceForeingValueFunction.");

    }

  }

    protected void addTableToColumnReferences(SQLBuilder sqlbuilder, FeatureType type) {

        SelectBuilder select = sqlbuilder.select();

        List<Pair<SQLBuilder.TableNameBuilder, FeatureType>> tables = new ArrayList<>();

        collectTablesFromSelect(select, tables, type);

        addTableToColumnReferencesInSingleSelect(

                sqlbuilder, 

                select, 

                tables

        );

    }

    private void collectTablesFromSelect(SelectBuilder select, List<Pair<SQLBuilder.TableNameBuilder, FeatureType>> tables, FeatureType type) throws LocatorException {

        DataManager dataManager = DALLocator.getDataManager();

        List<SQLBuilder.JoinBuilder> joins = select.from().getJoins();

        if(!CollectionUtils.isEmpty(joins)){

            for (SQLBuilder.JoinBuilder join : joins) {

                SQLBuilder.TableNameBuilder joinTable = join.getTable();

                FeatureType featureType = dataManager.getStoresRepository().getFeatureType(joinTable.getName());

                if(featureType != null){

                    tables.add(new ImmutablePair<>(joinTable,featureType));

                }

            }

        }

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

        if(type == null){

            type = dataManager.getStoresRepository().getFeatureType(table.getName());

        }

        if(type != null){

            tables.add(new ImmutablePair<>(table,type));

        }

    }

    protected void addTableToColumnReferencesInSingleSelect(SQLBuilder sqlbuilder, SelectBuilder select, List<Pair<SQLBuilder.TableNameBuilder,FeatureType>> outerTables) {

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

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

        List<ExpressionBuilder.Variable> variables = new ArrayList<>();

//        List<ExpressionBuilder.Value> variablesToExclude = new ArrayList<>();

        select.accept((ExpressionBuilder.Visitable value) -> {

            if(value instanceof Column) {

                Column c = (Column)value;

                if(c.table() == null || !c.table().has_name() || !c.table().has_schema()){

                    variables.add(c);

                }

            } else if(value instanceof ExpressionBuilder.Variable) {

                variables.add((ExpressionBuilder.Variable) value);

            }

        }, new ExpressionBuilder.VisitorFilter() {

            @Override

            public boolean skipChildren() {

                return true;

            }

            @Override

            public boolean accept(ExpressionBuilder.Visitable visitable) {

                if(select == visitable){

                    return true;

                }

                if(visitable instanceof SelectBuilder) {

                    ArrayList<Pair<SQLBuilder.TableNameBuilder, FeatureType>> tables = new ArrayList<>(outerTables);

                    collectTablesFromSelect((SelectBuilder) visitable, tables, null);

                    addTableToColumnReferencesInSingleSelect(sqlbuilder, (SelectBuilder) visitable, tables);

                    return false;

                }

                return true;

            }

        });

        for (ExpressionBuilder.Variable variable : variables) {

            ExpressionBuilder.Variable variable_replacement = null;

            Pair<SQLBuilder.TableNameBuilder, FeatureType> tableNameAndFeatureType = getTableAndFeatureType(outerTables, variable.name());

            if (tableNameAndFeatureType != null) {

                SQLBuilder.TableNameBuilder variableTable = tableNameAndFeatureType.getLeft();

                if (variable instanceof SQLBuilder.Column) {

                    Column column = (SQLBuilder.Column) variable;

                    if (column.table() != null && column.table().has_name()) {

                        if (column.table().has_schema()) {

                            //La columna tiene tabla y esquema => No hacemos nada

                        } else {

                            if(column.table().getName().equals(variableTable.getName())){

                                SQLBuilder.TableNameBuilder t = sqlbuilder.createTableNameBuilder()

                                        .name(column.table().getName())

                                        .schema(variableTable.getSchema());

                                variable_replacement = sqlbuilder.column_from(

                                        t,

                                        variable

                                );

                            } else {

                                if(fromTable.has_schema()){

                                    SQLBuilder.TableNameBuilder t = sqlbuilder.createTableNameBuilder()

                                            .name(column.table().getName())

                                            .schema(fromTable.getSchema()); //Mismo esquema que en el FROM

                                    variable_replacement = sqlbuilder.column_from(

                                            t,

                                            variable

                                    );

                                } else {

                                    // No tiene esquema ni podemos averiguarlo => No hacemos nada

                                }

                            }

                        }

                    } else {

                        column = sqlbuilder.column_from(tableNameAndFeatureType.getLeft(), variable);

                        column.setProperty(SQLBuilder.PROP_FEATURE_TYPE, tableNameAndFeatureType.getRight());

                        variable_replacement = column;

                    }

                } else {

                    Column column = sqlbuilder.column_from(tableNameAndFeatureType.getLeft(), variable);

                    column.setProperty(SQLBuilder.PROP_FEATURE_TYPE, tableNameAndFeatureType.getRight());

                    variable_replacement = column;

                }

            } else {

                if (variable instanceof SQLBuilder.Column) {

                    Column column = (SQLBuilder.Column) variable;

                    if (column.table() == null || !column.table().has_name()) {

                        variable_replacement = sqlbuilder.column_from(

                                fromTable,

                                variable

                        );

                    } else if (!column.table().has_schema()) {

                        if(fromTable.has_schema()){

                            SQLBuilder.TableNameBuilder t = sqlbuilder.createTableNameBuilder()

                                    .name(column.table().getName())

                                    .schema(fromTable.getSchema()); //Mismo esquema que en el FROM

                            variable_replacement = sqlbuilder.column_from(

                                    t,

                                    variable

                            );

                        } else {

                            // No tiene esquema ni podemos averiguarlo => No hacemos nada

                        }

                    }

                } else {

                    variable_replacement = sqlbuilder.column_from(

                            fromTable,

                            variable

                    );

                }

            }

            if(variable_replacement != null){

                value_replacements.add(

                        new ExpressionBuilder.Value[]{

                            variable,

                            variable_replacement

                        }

                );

            }

        }

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

            ExpressionBuilder.Value target = replaceValue[0];

            ExpressionBuilder.Value replacement = replaceValue[1];

            Boolean addTableName = (Boolean) target.getProperty(PROP_ADD_TABLE_NAME_TO_COLUMNS);

            if(addTableName == null || !addTableName){

                continue;

            }

            select.replace(target, replacement);

        }

    }

    protected Pair<SQLBuilder.TableNameBuilder, FeatureType> getTableAndFeatureType(List<Pair<SQLBuilder.TableNameBuilder, FeatureType>> outerTables, String columnName) {

//        ListIterator<Pair<SQLBuilder.TableNameBuilder, FeatureType>> it = outerTables.listIterator(outerTables.size());

//        

//        while (it.hasPrevious()) {

//            Pair<SQLBuilder.TableNameBuilder, FeatureType> pair = it.previous();

//            FeatureType featureType = pair.getRight();

//            if(featureType.get(columnName) != null){

//                return pair;

//            }

//        }

//        ListIterator<Pair<SQLBuilder.TableNameBuilder, FeatureType>> it = new ReverseListIterator(outerTables);

//        while (it.hasNext()) {

//            Pair<SQLBuilder.TableNameBuilder, FeatureType> pair = it.next();

//            FeatureType featureType = pair.getRight();

//            if(featureType.get(columnName) != null){

//                return pair;

//            }

//        }

        for (Iterator<Pair<SQLBuilder.TableNameBuilder, FeatureType>> iterator = new ReverseListIterator(outerTables); iterator.hasNext();) {

            Pair<SQLBuilder.TableNameBuilder, FeatureType> pair = iterator.next();

            FeatureType featureType = pair.getRight();

            if(featureType.get(columnName) != null){

                return pair;

            }

        }

        return null;

    }

    @Override

    public void setTransaction(DataTransactionServices transaction) {

        this.transaction = transaction;

    }

    @Override

    public String toString() {

        try {

            return String.format("%s %x %s", this.getClass().getSimpleName(), this.hashCode(), this.connectionParameters.getUrl());

        } catch (Exception e) {

            return super.toString();

        }

    }

  @Override

    public String getConnectionProviderStatus() {

        return "";

    }

  @Override

        public void expandCalculedColumns(JDBCSQLBuilderBase sqlbuilder) {

            ComputedAttribute computedAttributeFormater = new ComputedAttribute(sqlbuilder, sqlbuilder.formatter());

            for (int i = 0; i < 10; i++) {

                List<Pair<ExpressionBuilder.Variable, ExpressionBuilder.Value>> variablesToReplace = new ArrayList<>();

                sqlbuilder.accept((ExpressionBuilder.Visitable value) -> {

                    if (computedAttributeFormater.canApply((ExpressionBuilder.Value) value)) {

                        ExpressionBuilder.Variable variable = (ExpressionBuilder.Variable) value;

                        ExpressionBuilder.Value replace = computedAttributeFormater.expandedValue(variable);

                        variablesToReplace.add(Pair.of(variable, replace));

                    }

                }, null);

                if (variablesToReplace.isEmpty()) {

                    break;

                } 

                for (Pair<ExpressionBuilder.Variable, ExpressionBuilder.Value> entry : variablesToReplace) {

                    ExpressionBuilder.Value variable = entry.getKey();

                    ExpressionBuilder.Value replace = entry.getValue();

                    Boolean addTableName = (Boolean) variable.getProperty(PROP_ADD_TABLE_NAME_TO_COLUMNS);

                    if (addTableName != null && addTableName) {

                        sqlbuilder.setProperties(replace, null, PROP_ADD_TABLE_NAME_TO_COLUMNS,true);

                    }

                    sqlbuilder.select().replace(variable, replace);

                }

            }

    }

    @Override

    public DataTransactionServices getTransaction() {

        return this.transaction;

    }

    public ConnectionProvider getConnectionProvider() {

        return new FakeConnectionProvider(connectionParameters);

    }

}