gvSIG data provider for Oracle

package org.gvsig.oracle.dal.operations;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

import org.cresques.cts.IProjection;

import org.gvsig.fmap.crs.CRSFactory;

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

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

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

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

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

import org.gvsig.oracle.dal.OracleStoreParameters;

@SuppressWarnings("UseSpecificCatch")

public class OracleListTablesOperation extends ListTablesOperation {

    private static final String METADATA_COLUMN_TABLE_OWNER = "OWNER";

    private static final String METADATA_COLUMN_TABLE_NAME = "TABLE_NAME";

    private static final String METADATA_COLUMN_AUTHORITY = "AUTHORITY";

    private static final String METADATA_COLUMN_SRID = "SRID";

    private static final String ALL_TABLES_NAME = "ALL_TABLES";

    private static final String ALL_VIEWS_NAME = "ALL_VIEWS";

    private static final String ALL_TABLES_QUERY = "SELECT ALL_TABLES.OWNER, ALL_TABLES.TABLE_NAME, coordref.DATA_SOURCE as AUTHORITY, meta.srid as SRID FROM ALL_TABLES\n" +

        "left join MDSYS.ALL_SDO_GEOM_METADATA meta on meta.TABLE_NAME = ALL_TABLES.TABLE_NAME \n" +

        "left join SDO_COORD_REF_SYS coordRef ON coordRef.SRID = meta.SRID"; //SELECT OWNER, TABLE_NAME FROM ALL_TABLES";

    private static final String ALL_VIEWS_QUERY = "SELECT ALL_VIEWS.OWNER, ALL_VIEWS.VIEW_NAME TABLE_NAME, coordref.DATA_SOURCE as AUTHORITY, meta.srid as SRID FROM ALL_VIEWS\n" +

        "left join MDSYS.ALL_SDO_GEOM_METADATA meta on meta.TABLE_NAME = ALL_VIEWS.VIEW_NAME\n" +

        "left join SDO_COORD_REF_SYS coordRef ON coordRef.SRID = meta.SRID"; //SELECT OWNER, VIEW_NAME TABLE_NAME FROM ALL_VIEWS";

    private static final String EXCLUDE_SYSTEM_TABLES_FILTER = "ALL_TABLES.OWNER != 'MDSYS' AND ALL_TABLES.OWNER != 'CTXSYS' AND ALL_TABLES.OWNER != 'EXFSYS' AND ALL_TABLES.OWNER != 'OLAPSYS' AND ALL_TABLES.OWNER != 'ORDDATA' AND ALL_TABLES.OWNER != 'ORDSYS' AND ALL_TABLES.OWNER != 'SYS' AND ALL_TABLES.OWNER != 'SYSTEM' AND ALL_TABLES.OWNER != 'WMSYS' AND ALL_TABLES.OWNER != 'XDB' AND ALL_TABLES.OWNER NOT LIKE 'APEX_%' AND ALL_TABLES.TABLE_NAME NOT LIKE '%$%'";

    private static final String EXCLUDE_SYSTEM_VIEWS_FILTER = "ALL_VIEWS.OWNER != 'MDSYS' AND ALL_VIEWS.OWNER != 'CTXSYS' AND ALL_VIEWS.OWNER != 'EXFSYS' AND ALL_VIEWS.OWNER != 'OLAPSYS' AND ALL_VIEWS.OWNER != 'ORDDATA' AND ALL_VIEWS.OWNER != 'ORDSYS' AND ALL_VIEWS.OWNER != 'SYS' AND ALL_VIEWS.OWNER != 'SYSTEM' AND ALL_VIEWS.OWNER != 'WMSYS' AND ALL_VIEWS.OWNER != 'XDB' AND ALL_VIEWS.OWNER NOT LIKE 'APEX_%' AND ALL_VIEWS.VIEW_NAME NOT LIKE '%$%'";

    public OracleListTablesOperation(JDBCHelper helper, int mode, JDBCServerExplorerParameters baseParameters,

            boolean informationTables, int tablesOrViews) {

        super(helper, mode, baseParameters, informationTables, tablesOrViews);

    }

    @Override

    public List<JDBCStoreParameters> listTables(

            JDBCConnection conn,

            int mode,

            JDBCServerExplorerParameters serverParameters,

            boolean informationTables,

            int tablesOrViews

    ) {

        try {

            String schema = serverParameters.getSchema();

            return this.getAllTablesAndViews(serverParameters, schema, informationTables, tablesOrViews);

        } catch (SQLException ex) {

            throw new RuntimeException("Can't fetch tables information", ex);

        }

    }

    protected List<JDBCStoreParameters> getAllTablesAndViews(

            JDBCServerExplorerParameters serverParameters,

            String schema,

            boolean includeSystemTbls, 

            int tablesOrViews) throws SQLException {

        List<JDBCStoreParameters> tables = new ArrayList<>();

        try {

            StringBuilder builder = new StringBuilder();

            boolean showTables = (tablesOrViews == JDBCServerExplorer.SHOW_TABLES_AND_VIEWS || tablesOrViews == JDBCServerExplorer.SHOW_TABLES);

            boolean showViews = (tablesOrViews == JDBCServerExplorer.SHOW_TABLES_AND_VIEWS || tablesOrViews == JDBCServerExplorer.SHOW_VIEWS);

            boolean showTablesAndViews = (tablesOrViews == JDBCServerExplorer.SHOW_TABLES_AND_VIEWS);

            String owner = schema;

            if (owner == null) {

                owner = serverParameters.getUser();

            }

            boolean where = false;

            if(showTables){

                builder.append(ALL_TABLES_QUERY);

                if (!includeSystemTbls) {

                    builder.append(" WHERE ");

                    where = true;

                    builder.append(EXCLUDE_SYSTEM_TABLES_FILTER);

                }

                if (owner != null) {

                    if (where) {

                        builder.append(" AND ");

                    } else {

                        builder.append(" WHERE ");

                    }

                    builder.append(ALL_TABLES_NAME);

                    builder.append(".");

                    builder.append(METADATA_COLUMN_TABLE_OWNER);

                    builder.append(" = '");

                    builder.append(owner);

                    builder.append("'");

                }

            }

            if(showTablesAndViews){

                builder.append(" UNION ");

            }

            if(showViews){

                builder.append(ALL_VIEWS_QUERY);

                if (!includeSystemTbls) {

                    builder.append(" WHERE ");

                    where = true;

                    builder.append(EXCLUDE_SYSTEM_VIEWS_FILTER);

                } else {

                    where = false;

                }

                if (owner != null) {

                    if (where) {

                        builder.append(" AND ");

                    } else {

                        builder.append(" WHERE ");

                    }

                    builder.append(ALL_VIEWS_NAME);

                    builder.append(".");

                    builder.append(METADATA_COLUMN_TABLE_OWNER);

                    builder.append(" = '");

                    builder.append(owner);

                    builder.append("'");

                }

            }

            Statement st = null;

            ResultSet rs = null;

            try {

                st = this.getConnection().createStatement();

                rs = JDBCUtils.executeQuery(st, builder.toString());

                while (rs.next()) {

                    OracleStoreParameters params = (OracleStoreParameters) this.helper.createOpenStoreParameters(serverParameters);

                    params.setSchema(rs.getString(METADATA_COLUMN_TABLE_OWNER));

                    String table = rs.getString(METADATA_COLUMN_TABLE_NAME);

                    params.setTable(table);

                    if( StringUtils.isNotBlank(table) && !table.equals(table.toUpperCase()) ) {

                        // !aaa.equals(AAA) -> !false --> true

                        // !AAA.equals(AAA) -> !true --> false

                        params.setForceUppercaseInTableName(false);

                    }

                    String authority = rs.getString(METADATA_COLUMN_AUTHORITY);

                    String srs = rs.getString(METADATA_COLUMN_SRID);

                    if(StringUtils.isBlank(authority)) {

                        if(srs!=null){

                            IProjection projection = this.helper.getSRSSolver().getProjection(this.getConnection(), Integer.valueOf(srs));

                            if (projection != null) {

                                params.setCRS(projection);

                            }

                        }

                    } else if(StringUtils.isNotBlank(srs)) {

                        try {

                            IProjection projection = CRSFactory.getCRS(authority + ":" + srs);

                            if (projection != null) {

                                params.setCRS(projection);

                            }

                        } catch (Exception e) {

                            //Si falla continuamos cargando el resto de tablas dejando esta sin proyeccion

                            LOGGER.trace("Can't get projection from table '"+table+"'",e);

                        }

                    }

                    tables.add(params);

                }

            } finally {

                JDBCUtils.closeQuietly(rs);

                JDBCUtils.closeQuietly(st);

            }

        } catch (Exception ex) {

            LOGGER.trace("Can't get the list of tables accessible by the user.", ex);

            throw new SQLException(ex);

        }

        return tables;

    }

    private IProjection getProjection(String table) {

        StringBuilder srsQuery = new StringBuilder();

        srsQuery.append("select coordRef.DATA_SOURCE, meta.SRID from MDSYS.ALL_SDO_GEOM_METADATA meta left join SDO_COORD_REF_SYS coordRef ON coordRef.SRID = meta.SRID  where TABLE_NAME = UPPER(?)");

        PreparedStatement st = null;

        String authority;

        String srs;

        IProjection projection = null;

        try {

            String sql = srsQuery.toString();

            st = conn.prepareStatement(sql);

            st.setString(1, table);

            ResultSet rs = JDBCUtils.executeQuery(st, sql);

            if (rs.next()) {

                authority = rs.getString(1);

                srs = rs.getString(2);

                projection = CRSFactory.getCRS(authority + ":" + srs);

            }

        } catch (SQLException ex) {

            throw new RuntimeException("Can't get srs from table " + table, ex);

        } finally {

            JDBCUtils.closeQuietly(st);

        }

        return projection;

    }

}