gvSIG data provider for Oracle

package org.gvsig.oracle.dal.operations;

import java.sql.Connection;

import java.sql.DatabaseMetaData;

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.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters;

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

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

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

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

import org.gvsig.fmap.geom.type.GeometryType;

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 ALL_VIEWS_QUERY = "SELECT OWNER, VIEW_NAME TABLE_NAME FROM ALL_VIEWS";

    private static final String ALL_TABLES_QUERY = "SELECT OWNER, TABLE_NAME FROM ALL_TABLES";

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

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

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

                        boolean informationTables) {

                super(helper, mode, baseParameters, informationTables);

        }

    public List<JDBCStoreParameters> listTables(

            Connection conn,

            int mode,

            JDBCStoreParameters baseParameters,

            boolean informationTables

    ) {

        try {

                String schema = baseParameters.getSchema();

                return this.getAllTablesAndViews(baseParameters, schema, informationTables);                

        } catch (SQLException ex) {

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

        }

    }

    protected List<JDBCStoreParameters> getAllTablesAndViews(

                    JDBCStoreParameters baseParameters,

                    String schema,

                    boolean includeSystemTbls) throws SQLException {

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

            try {

                    StringBuilder builder = new StringBuilder();

                    builder.append(ALL_TABLES_QUERY);

                    boolean where = false;

                    if (!includeSystemTbls) {

                            builder.append(" WHERE ");

                            where = true;

                            builder.append(EXCLUDE_SYSTEM_TABLES_FILTER);

                    }

                    if (schema!=null) {

                            if (where) {

                                    builder.append(" AND ");

                            }

                            else {

                                    builder.append(" WHERE ");

                            }

                            builder.append(METADATA_COLUMN_TABLE_OWNER);

                            builder.append(" = '");

                            builder.append(schema);

                            builder.append("'");

                    }

                    builder.append(" UNION ");

                    builder.append(ALL_VIEWS_QUERY);

                    if (!includeSystemTbls) {

                            builder.append(" WHERE ");

                            where = true;

                            builder.append(EXCLUDE_SYSTEM_VIEWS_FILTER);

                    }

                    else {

                            where = false;

                    }

                    if (schema!=null) {

                            if (where) {

                                    builder.append(" AND ");

                            }

                            else {

                                    builder.append(" WHERE ");

                            }

                            builder.append(METADATA_COLUMN_TABLE_OWNER);

                            builder.append(" = '");

                            builder.append(schema);

                            builder.append("'");

                    }

                Statement st = null;

                ResultSet rs = null;

                try {

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

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

                    while (rs.next()) {

                        JDBCStoreParameters params = baseParameters.getCopy();

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

                        params.setTable(rs.getString(METADATA_COLUMN_TABLE_NAME));

                        tables.add(params);

                    }

                } finally {

                    JDBCUtils.closeQuietly(rs);

                    JDBCUtils.closeQuietly(st);

                }

            } catch (Exception ex) {

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

                throw new SQLException(ex);

            }

            return tables;

    }

}