Search in sources :

Example 1 with IndexInfo

use of com.developmentontheedge.be5.metadata.sql.pojo.IndexInfo in project be5 by DevelopmentOnTheEdge.

the class OracleSchemaReader method readIndices.

@Override
public Map<String, List<IndexInfo>> readIndices(SqlExecutor sql, String defSchema, ProcessController controller) throws SQLException, ProcessInterruptedException {
    DbmsConnector connector = sql.getConnector();
    Map<String, List<IndexInfo>> result = new HashMap<>();
    ResultSet rs = connector.executeQuery("SELECT " + "i.table_name,i.index_name,ic.column_name,i.uniqueness " + "FROM user_indexes i " + "JOIN user_ind_columns ic ON i.index_name=ic.index_name " + "JOIN entities e ON (UPPER(e.name)=i.table_name) " + "ORDER BY i.table_name,i.index_name,ic.column_position");
    try {
        IndexInfo curIndex = null;
        String lastTable = null;
        while (rs.next()) {
            String tableName = rs.getString(1).toLowerCase();
            String indexName = rs.getString(2);
            if (!tableName.equals(lastTable) || curIndex == null || !curIndex.getName().equals(indexName)) {
                List<IndexInfo> list = result.get(tableName);
                if (list == null) {
                    list = new ArrayList<>();
                    result.put(tableName, list);
                }
                curIndex = new IndexInfo();
                lastTable = tableName;
                list.add(curIndex);
                curIndex.setName(indexName);
                curIndex.setUnique("UNIQUE".equals(rs.getString(4)));
            }
            String column = rs.getString(3);
            curIndex.addColumn(column);
        }
    } finally {
        connector.close(rs);
    }
    // Read functional indices separately
    // as this query contains streaming column which is read slowly
    rs = connector.executeQuery("SELECT " + "i.table_name,i.index_name,ic.column_position,c.data_default " + "FROM user_indexes i " + "JOIN user_ind_columns ic ON i.index_name=ic.index_name " + "JOIN entities e ON (UPPER(e.name)=i.table_name) " + "JOIN user_tab_cols c ON (c.column_name=ic.column_name AND c.table_name=ic.table_name) " + "WHERE c.virtual_column='YES' " + "ORDER BY i.table_name,i.index_name,ic.column_position");
    try {
        while (rs.next()) {
            // Read streaming column at first
            String defaultValue = rs.getString(4);
            String tableName = rs.getString(1).toLowerCase();
            String indexName = rs.getString(2);
            int pos = rs.getInt(3) - 1;
            List<IndexInfo> list = result.get(tableName);
            if (list == null)
                continue;
            for (IndexInfo indexInfo : list) {
                if (indexInfo.getName().equals(indexName)) {
                    defaultValue = GENERIC_REF_INDEX_PATTERN.matcher(defaultValue).replaceFirst("generic($2)");
                    defaultValue = UPPER_INDEX_PATTERN.matcher(defaultValue).replaceFirst("upper($1)");
                    defaultValue = LOWER_INDEX_PATTERN.matcher(defaultValue).replaceFirst("lower($1)");
                    indexInfo.getColumns().set(pos, defaultValue);
                }
            }
        }
    } finally {
        connector.close(rs);
    }
    return result;
}
Also used : DbmsConnector(com.developmentontheedge.dbms.DbmsConnector) HashMap(java.util.HashMap) ResultSet(java.sql.ResultSet) ArrayList(java.util.ArrayList) List(java.util.List) IndexInfo(com.developmentontheedge.be5.metadata.sql.pojo.IndexInfo)

Example 2 with IndexInfo

use of com.developmentontheedge.be5.metadata.sql.pojo.IndexInfo in project be5 by DevelopmentOnTheEdge.

the class PostgresSchemaReader method readIndices.

@Override
public Map<String, List<IndexInfo>> readIndices(SqlExecutor sql, String defSchema, ProcessController controller) throws SQLException, ProcessInterruptedException {
    DbmsConnector connector = sql.getConnector();
    Map<String, List<IndexInfo>> result = new HashMap<>();
    ResultSet rs = connector.executeQuery("SELECT ct.relname AS TABLE_NAME, i.indisunique AS IS_UNIQUE, ci.relname AS INDEX_NAME, " + "pg_catalog.pg_get_indexdef(ci.oid, (i.keys).n, false) AS COLUMN_NAME, (i.keys).n AS ORDINAL " + "FROM pg_catalog.pg_class ct " + "JOIN pg_catalog.pg_namespace n " + "ON (ct.relnamespace = n.oid)" + "JOIN (" + "SELECT i.indexrelid, i.indrelid, i.indisunique, " + "information_schema._pg_expandarray(i.indkey) AS keys FROM pg_catalog.pg_index i " + ") i " + "ON (ct.oid = i.indrelid) " + "JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid) " + (defSchema == null ? "" : "AND n.nspname = '" + defSchema + "' ") + " ORDER BY 1,3,5");
    try {
        IndexInfo curIndex = null;
        String lastTable = null;
        while (rs.next()) {
            String tableName = rs.getString(1);
            String indexName = rs.getString(3);
            if (indexName == null)
                continue;
            if (!tableName.equals(lastTable) || curIndex == null || !curIndex.getName().equals(indexName)) {
                List<IndexInfo> list = result.get(tableName);
                if (list == null) {
                    list = new ArrayList<>();
                    result.put(tableName, list);
                }
                curIndex = new IndexInfo();
                lastTable = tableName;
                list.add(curIndex);
                curIndex.setName(indexName);
                curIndex.setUnique(rs.getBoolean(2));
            }
            String column = rs.getString(4);
            column = GENERIC_REF_INDEX_PATTERN.matcher(column).replaceFirst("generic($2)");
            column = UPPER_INDEX_PATTERN.matcher(column).replaceFirst("upper($1)");
            column = LOWER_INDEX_PATTERN.matcher(column).replaceFirst("lower($1)");
            column = QUOTE_INDEX_PATTERN.matcher(column).replaceFirst("$1");
            curIndex.addColumn(column);
            controller.setProgress(0);
        }
    } finally {
        connector.close(rs);
    }
    return result;
}
Also used : DbmsConnector(com.developmentontheedge.dbms.DbmsConnector) HashMap(java.util.HashMap) ResultSet(java.sql.ResultSet) ArrayList(java.util.ArrayList) List(java.util.List) IndexInfo(com.developmentontheedge.be5.metadata.sql.pojo.IndexInfo)

Example 3 with IndexInfo

use of com.developmentontheedge.be5.metadata.sql.pojo.IndexInfo in project be5 by DevelopmentOnTheEdge.

the class SqlServerSchemaReader method readIndices.

@Override
public Map<String, List<IndexInfo>> readIndices(SqlExecutor sql, String defSchema, ProcessController controller) throws SQLException, ProcessInterruptedException {
    DbmsConnector connector = sql.getConnector();
    Map<String, List<IndexInfo>> result = new HashMap<>();
    ResultSet rs = connector.executeQuery("SELECT st.name AS \"table_name\"," + "si.name AS \"index_name\"," + "sc.name AS \"column_name\"," + "si.is_unique " + "FROM sys.indexes si " + "JOIN sys.tables st ON (si.object_ID=st.object_ID) " + "JOIN sys.index_columns sic ON (sic.object_id=si.object_id AND sic.index_id = si.index_id) " + "JOIN sys.columns sc ON (sc.object_id=sic.object_id AND sc.column_id=sic.column_id) " + "JOIN sys.schemas ss ON (st.schema_id=ss.schema_id) " + (defSchema == null ? "" : "WHERE ss.name='" + defSchema + "' ") + "ORDER by st.object_id,si.index_id,sic.key_ordinal ");
    try {
        IndexInfo curIndex = null;
        String lastTable = null;
        while (rs.next()) {
            String tableName = rs.getString(1).toLowerCase(Locale.ENGLISH);
            String indexName = rs.getString(2);
            if (!tableName.equals(lastTable) || curIndex == null || !curIndex.getName().equals(indexName)) {
                List<IndexInfo> list = result.get(tableName);
                if (list == null) {
                    list = new ArrayList<>();
                    result.put(tableName, list);
                }
                curIndex = new IndexInfo();
                lastTable = tableName;
                list.add(curIndex);
                curIndex.setName(indexName);
                curIndex.setUnique(rs.getBoolean(4));
            }
            String column = rs.getString(3);
            curIndex.addColumn(column);
        }
    } finally {
        connector.close(rs);
    }
    return result;
}
Also used : DbmsConnector(com.developmentontheedge.dbms.DbmsConnector) HashMap(java.util.HashMap) ResultSet(java.sql.ResultSet) ArrayList(java.util.ArrayList) List(java.util.List) IndexInfo(com.developmentontheedge.be5.metadata.sql.pojo.IndexInfo)

Example 4 with IndexInfo

use of com.developmentontheedge.be5.metadata.sql.pojo.IndexInfo in project be5 by DevelopmentOnTheEdge.

the class Db2SchemaReader method readIndices.

@Override
public Map<String, List<IndexInfo>> readIndices(SqlExecutor sql, String defSchema, ProcessController controller) throws SQLException {
    DbmsConnector connector = sql.getConnector();
    Map<String, List<IndexInfo>> result = new HashMap<>();
    ResultSet rs = connector.executeQuery("SELECT i.tabname,i.indname,ic.colname,i.uniquerule " + "FROM syscat.indexes i " + "JOIN syscat.indexcoluse ic ON (i.indschema=ic.indschema AND i.indname=ic.indname) " + (defSchema == null ? "" : "WHERE i.tabschema='" + defSchema + "' ") + " ORDER BY i.tabname,i.indname,ic.colseq");
    try {
        IndexInfo curIndex = null;
        String lastTable = null;
        while (rs.next()) {
            String tableName = rs.getString(1).toLowerCase();
            String indexName = rs.getString(2);
            if (!tableName.equals(lastTable) || curIndex == null || !curIndex.getName().equals(indexName)) {
                List<IndexInfo> list = result.get(tableName);
                if (list == null) {
                    list = new ArrayList<>();
                    result.put(tableName, list);
                }
                curIndex = new IndexInfo();
                lastTable = tableName;
                list.add(curIndex);
                curIndex.setName(indexName);
                String unique = rs.getString(4);
                curIndex.setUnique("U".equals(unique) || "P".equals(unique));
            }
            String column = rs.getString(3);
            curIndex.addColumn(column);
        }
    } finally {
        connector.close(rs);
    }
    return result;
}
Also used : DbmsConnector(com.developmentontheedge.dbms.DbmsConnector) HashMap(java.util.HashMap) ResultSet(java.sql.ResultSet) ArrayList(java.util.ArrayList) List(java.util.List) IndexInfo(com.developmentontheedge.be5.metadata.sql.pojo.IndexInfo)

Example 5 with IndexInfo

use of com.developmentontheedge.be5.metadata.sql.pojo.IndexInfo in project be5 by DevelopmentOnTheEdge.

the class MySqlSchemaReader method readIndices.

@Override
public Map<String, List<IndexInfo>> readIndices(SqlExecutor sql, String defSchema, ProcessController controller) throws SQLException, ProcessInterruptedException {
    DbmsConnector connector = sql.getConnector();
    Map<String, List<IndexInfo>> result = new HashMap<>();
    ResultSet rs = connector.executeQuery("SELECT table_name,index_name,column_name,non_unique FROM information_schema.statistics " + "WHERE table_schema='" + defSchema + "' ORDER BY table_name,index_name,seq_in_index");
    try {
        IndexInfo curIndex = null;
        String lastTable = null;
        while (rs.next()) {
            String tableName = rs.getString(1).toLowerCase();
            String indexName = rs.getString(2);
            if (!tableName.equals(lastTable) || curIndex == null || !curIndex.getName().equals(indexName)) {
                List<IndexInfo> list = result.get(tableName);
                if (list == null) {
                    list = new ArrayList<>();
                    result.put(tableName, list);
                }
                curIndex = new IndexInfo();
                lastTable = tableName;
                list.add(curIndex);
                curIndex.setName(indexName);
                int nonUnique = rs.getInt(4);
                curIndex.setUnique(nonUnique == 0);
            }
            String column = rs.getString(3);
            curIndex.addColumn(column);
        }
    } finally {
        connector.close(rs);
    }
    return result;
}
Also used : DbmsConnector(com.developmentontheedge.dbms.DbmsConnector) HashMap(java.util.HashMap) ResultSet(java.sql.ResultSet) ArrayList(java.util.ArrayList) List(java.util.List) IndexInfo(com.developmentontheedge.be5.metadata.sql.pojo.IndexInfo)

Aggregations

IndexInfo (com.developmentontheedge.be5.metadata.sql.pojo.IndexInfo)6 ResultSet (java.sql.ResultSet)6 DbmsConnector (com.developmentontheedge.dbms.DbmsConnector)5 ArrayList (java.util.ArrayList)5 HashMap (java.util.HashMap)5 List (java.util.List)5 ColumnDef (com.developmentontheedge.be5.metadata.model.ColumnDef)1 Entity (com.developmentontheedge.be5.metadata.model.Entity)1 IndexColumnDef (com.developmentontheedge.be5.metadata.model.IndexColumnDef)1 IndexDef (com.developmentontheedge.be5.metadata.model.IndexDef)1 Module (com.developmentontheedge.be5.metadata.model.Module)1 Project (com.developmentontheedge.be5.metadata.model.Project)1 TableDef (com.developmentontheedge.be5.metadata.model.TableDef)1 ViewDef (com.developmentontheedge.be5.metadata.model.ViewDef)1 Rdbms (com.developmentontheedge.be5.metadata.sql.Rdbms)1 SqlColumnInfo (com.developmentontheedge.be5.metadata.sql.pojo.SqlColumnInfo)1 DbmsTypeManager (com.developmentontheedge.be5.metadata.sql.type.DbmsTypeManager)1 DefaultTypeManager (com.developmentontheedge.be5.metadata.sql.type.DefaultTypeManager)1