Application: gvSIG desktop

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

import java.sql.Blob;

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

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

import java.sql.Connection;

import java.sql.ResultSet;

import java.util.ArrayList;

import java.util.List;

import org.apache.commons.lang3.ArrayUtils;

import org.apache.commons.lang3.StringUtils;

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

import org.gvsig.expressionevaluator.Code;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import org.gvsig.expressionevaluator.ExpressionBuilder.GeometrySupportType;

import static org.gvsig.expressionevaluator.ExpressionBuilder.GeometrySupportType.EWKB;

import static org.gvsig.expressionevaluator.ExpressionBuilder.GeometrySupportType.NATIVE;

import static org.gvsig.expressionevaluator.ExpressionBuilder.GeometrySupportType.WKB;

import static org.gvsig.expressionevaluator.ExpressionBuilder.GeometrySupportType.WKT;

import org.gvsig.expressionevaluator.ExpressionEvaluatorLocator;

import org.gvsig.expressionevaluator.ExpressionEvaluatorManager;

import org.gvsig.expressionevaluator.Function;

import org.gvsig.expressionevaluator.SymbolTable;

import org.gvsig.fmap.dal.DataTypes;

import org.gvsig.fmap.dal.SQLBuilder;

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

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

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

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.spi.DataServerExplorerProviderServices;

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

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

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

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

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

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

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

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

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

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

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

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

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

//            params.getUrl(),

//            params.getHost(),

//            params.getPort(),

//            params.getDBName(),

//            params.getUser(),

//            params.getPassword(),

//            params.getJDBCDriverClassName()

//        };

//

//        try {

//            ResourceManagerProviderServices manager

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

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

//                    this.getResourceType(),

//                    resourceParams

//            );

//            this.resource = resource;

//            this.resource.addConsumer(this);

//        } catch (InitializeException ex) {

//            logger.debug("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 JDBCResource getResource() {

        return null;

//        return this.resource;

    }

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

//        this.resource.removeConsumer(this);

        JDBCUtils.closeQuietly(this.resulSetControler);

//        this.resource = null;

        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 String[] replaceForeingValueFunction(SQLBuilder sqlbuilder, FeatureType type) {

        // See test SQLBuilderTest->testForeingValue()

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

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

            return null;

        }

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

        final ExpressionBuilder expbuilder = sqlbuilder.expression();

        final List<String> foreing_value_args = new ArrayList<>();

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

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

            return null;

        }

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

                    continue;

                }

                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.

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

                sqlbuilder.select().from()

                    .left_join(

                        foreingTable,

                        expbuilder.eq(

                                sqlbuilder.column(mainTable, columnNameLocal),

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

                        )

                    );

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

            }

        }

        return foreing_value_args.toArray(new String[foreing_value_args.size()]);

    }

}