Search in sources :

Example 11 with DbmsConnector

use of com.developmentontheedge.dbms.DbmsConnector 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 12 with DbmsConnector

use of com.developmentontheedge.dbms.DbmsConnector in project be5 by DevelopmentOnTheEdge.

the class SqlServerSchemaReader method readColumns.

@Override
public Map<String, List<SqlColumnInfo>> readColumns(SqlExecutor sql, String defSchema, ProcessController controller) throws SQLException, ProcessInterruptedException {
    DbmsConnector connector = sql.getConnector();
    Map<String, List<SqlColumnInfo>> result = new HashMap<>();
    ResultSet rs = connector.executeQuery("SELECT " + "c.table_name, " + "c.column_name, " + "c.column_default, " + "c.data_type, " + "c.character_maximum_length, " + "c.numeric_precision, " + "c.numeric_scale, " + "c.is_nullable, " + "sc.is_identity, " + "ch.check_clause, " + "scc.definition " + "FROM information_schema.columns c " + "JOIN sys.columns sc ON (sc.object_id=object_id(c.table_name) AND sc.name = c.column_name) " + "LEFT JOIN sys.computed_columns scc ON (scc.object_id=object_id(c.table_name) AND scc.name = c.column_name) " + "LEFT JOIN information_schema.constraint_column_usage cc ON (cc.table_name=c.table_name AND cc.table_schema=c.table_schema AND cc.column_name=c.column_name) " + "LEFT JOIN information_schema.table_constraints tc ON (cc.constraint_name = tc.constraint_name) " + "LEFT JOIN information_schema.check_constraints ch ON (cc.constraint_name = ch.constraint_name) " + "WHERE (ch.check_clause IS NULL OR tc.constraint_type = 'CHECK') " + (defSchema == null ? "" : "AND c.table_schema='" + defSchema + "' ") + "ORDER BY c.table_name,c.ordinal_position");
    try {
        while (rs.next()) {
            // Just to check for interrupts
            controller.setProgress(0);
            String tableName = rs.getString(1).toLowerCase(Locale.ENGLISH);
            List<SqlColumnInfo> list = result.get(tableName);
            if (list == null) {
                list = new ArrayList<>();
                result.put(tableName, list);
            }
            SqlColumnInfo info = new SqlColumnInfo();
            list.add(info);
            info.setName(rs.getString(2));
            info.setType(rs.getString(4));
            info.setCanBeNull("YES".equals(rs.getString(8)));
            String defaultValue = rs.getString(3);
            while (defaultValue != null && defaultValue.startsWith("(") && defaultValue.endsWith(")")) {
                defaultValue = defaultValue.substring(1, defaultValue.length() - 1);
            }
            if (defaultValue != null) {
                defaultValue = DATE_DEFVALUE_PATTERN.matcher(defaultValue).replaceFirst("'$1'");
            }
            info.setDefaultValue(defaultValue);
            info.setSize(rs.getInt(5));
            if (rs.wasNull()) {
                info.setSize(rs.getInt(6));
            }
            info.setPrecision(rs.getInt(7));
            info.setAutoIncrement(rs.getBoolean(9));
            // ENUM VALUES
            String check = rs.getString(10);
            if (check != null) {
                Matcher matcher = ENUM_VALUES_PATTERN.matcher(check);
                List<String> vals = new ArrayList<>();
                while (matcher.find()) {
                    vals.add(matcher.group(1));
                }
                Collections.sort(vals);
                info.setEnumValues(vals.toArray(new String[vals.size()]));
            }
            // GENERATED column
            String definition = rs.getString(11);
            if (definition != null) {
                Matcher matcher = GENERIC_REF_COLUMN_PATTERN.matcher(definition);
                if (matcher.matches()) {
                    String colName = matcher.group(2);
                    info.setDefaultValue(new ColumnFunction(colName, ColumnFunction.TRANSFORM_GENERIC).toString());
                }
            }
        }
    } finally {
        connector.close(rs);
    }
    return result;
}
Also used : HashMap(java.util.HashMap) Matcher(java.util.regex.Matcher) ColumnFunction(com.developmentontheedge.be5.metadata.model.ColumnFunction) ArrayList(java.util.ArrayList) DbmsConnector(com.developmentontheedge.dbms.DbmsConnector) ResultSet(java.sql.ResultSet) ArrayList(java.util.ArrayList) List(java.util.List) SqlColumnInfo(com.developmentontheedge.be5.metadata.sql.pojo.SqlColumnInfo)

Example 13 with DbmsConnector

use of com.developmentontheedge.dbms.DbmsConnector 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 14 with DbmsConnector

use of com.developmentontheedge.dbms.DbmsConnector in project be5 by DevelopmentOnTheEdge.

the class DefaultSchemaReader method readTableNames.

@Override
public Map<String, String> readTableNames(SqlExecutor sql, String defSchema, ProcessController controller) throws SQLException {
    DbmsConnector connector = sql.getConnector();
    Connection connection = connector.getConnection();
    ResultSet rs = null;
    Map<String, String> result = new HashMap<>();
    try {
        rs = connection.getMetaData().getTables(null, defSchema, null, new String[] { "TABLE", "VIEW" });
        while (rs.next()) {
            String name = rs.getString(3).toLowerCase();
            String type = rs.getString(4);
            result.put(name, type);
        }
    } finally {
        connector.close(rs);
        connector.releaseConnection(connection);
    }
    return result;
}
Also used : DbmsConnector(com.developmentontheedge.dbms.DbmsConnector) HashMap(java.util.HashMap) Connection(java.sql.Connection) ResultSet(java.sql.ResultSet)

Example 15 with DbmsConnector

use of com.developmentontheedge.dbms.DbmsConnector in project be5 by DevelopmentOnTheEdge.

the class H2SchemaReader method readColumns.

@Override
public Map<String, List<SqlColumnInfo>> readColumns(SqlExecutor sql, String defSchema, ProcessController controller) throws SQLException, ProcessInterruptedException {
    DbmsConnector connector = sql.getConnector();
    Map<String, List<SqlColumnInfo>> result = new HashMap<>();
    ResultSet rs = connector.executeQuery("SELECT " + "c.table_name, " + "c.column_name, " + "c.column_default, " + "c.TYPE_NAME, " + "c.character_maximum_length, " + "c.numeric_precision, " + "c.numeric_scale, " + "c.is_nullable, " + "c.CHECK_CONSTRAINT " + "FROM INFORMATION_SCHEMA.COLUMNS c " + "WHERE c.TABLE_SCHEMA <> 'INFORMATION_SCHEMA'");
    try {
        while (rs.next()) {
            String tableName = rs.getString(1).toLowerCase();
            if (!tableName.equals(tableName.toLowerCase()))
                continue;
            List<SqlColumnInfo> list = result.get(tableName);
            if (list == null) {
                list = new ArrayList<>();
                result.put(tableName, list);
            }
            SqlColumnInfo info = new SqlColumnInfo();
            list.add(info);
            info.setName(rs.getString(2));
            info.setType(rs.getString(4));
            info.setCanBeNull("YES".equals(rs.getString(8)));
            String defaultValue = rs.getString(3);
            if (defaultValue != null) {
                for (String suffix : SUFFICES) {
                    if (defaultValue.endsWith(suffix))
                        defaultValue = defaultValue.substring(0, defaultValue.length() - suffix.length());
                }
                defaultValue = DEFAULT_DATE_PATTERN.matcher(defaultValue).replaceFirst("'$1'");
            }
            info.setDefaultValue(defaultValue);
            info.setSize(rs.getInt(5));
            if (rs.wasNull()) {
                info.setSize(rs.getInt(6));
            }
            info.setPrecision(rs.getInt(7));
            info.setAutoIncrement(defaultValue != null && defaultValue.startsWith("nextval"));
            String check = rs.getString(9);
            if (check == null)
                continue;
            Matcher matcher = ENUM_VALUES_PATTERN.matcher(check);
            List<String> vals = new ArrayList<>();
            while (matcher.find()) {
                vals.add(matcher.group(1));
            }
            info.setEnumValues(vals.toArray(new String[vals.size()]));
            // Just to check for interrupts
            controller.setProgress(0);
        }
    } finally {
        connector.close(rs);
    }
    return result;
}
Also used : DbmsConnector(com.developmentontheedge.dbms.DbmsConnector) HashMap(java.util.HashMap) Matcher(java.util.regex.Matcher) ResultSet(java.sql.ResultSet) ArrayList(java.util.ArrayList) ArrayList(java.util.ArrayList) List(java.util.List) SqlColumnInfo(com.developmentontheedge.be5.metadata.sql.pojo.SqlColumnInfo)

Aggregations

DbmsConnector (com.developmentontheedge.dbms.DbmsConnector)19 ResultSet (java.sql.ResultSet)18 HashMap (java.util.HashMap)17 ArrayList (java.util.ArrayList)12 List (java.util.List)12 SqlColumnInfo (com.developmentontheedge.be5.metadata.sql.pojo.SqlColumnInfo)6 IndexInfo (com.developmentontheedge.be5.metadata.sql.pojo.IndexInfo)5 Matcher (java.util.regex.Matcher)5 ColumnFunction (com.developmentontheedge.be5.metadata.model.ColumnFunction)3 SQLException (java.sql.SQLException)2 Connection (java.sql.Connection)1 NoSuchElementException (java.util.NoSuchElementException)1 StringTokenizer (java.util.StringTokenizer)1