Examples with SqlColumnInfo com.developmentontheedge.be5.metadata.sql.pojo.SqlColumnInfo used on opensource projects

Example 1 with SqlColumnInfo

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

the class Db2SchemaReader 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 tabname,colname,typename,nulls,default,length,scale,identity,text FROM syscat.columns c " + (defSchema == null ? "" : "WHERE c.tabschema='" + defSchema + "' ") + " ORDER BY c.tabname,c.colno");
    try {
        while (rs.next()) {
            String tableName = rs.getString(1).toLowerCase();
            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(3));
            info.setCanBeNull(rs.getString(4).equals("Y"));
            info.setDefaultValue(rs.getString(5));
            info.setSize(rs.getInt(6));
            info.setPrecision(rs.getInt(7));
            info.setAutoIncrement(rs.getString(8).equals("Y"));
            String text = rs.getString(9);
            if (text != null) {
                Matcher m = GENERIC_COLUMN_PATTERN.matcher(text);
                if (m.matches()) {
                    String colName = m.group(2);
                    info.setDefaultValue(new ColumnFunction(colName, ColumnFunction.TRANSFORM_GENERIC).toString());
                }
            }
        }
    } finally {
        connector.close(rs);
    }
    for (Entry<String, List<SqlColumnInfo>> table : result.entrySet()) {
        HashMap<String, String[]> enums = loadEntityEnums(connector, table.getKey());
        for (SqlColumnInfo column : table.getValue()) {
            column.setEnumValues(enums.get(column.getName()));
        }
    }
    return result;
}

Example 2 with SqlColumnInfo

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

the class MySqlSchemaReader 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 table_name,column_name,column_type,column_default,is_nullable," + "numeric_precision,numeric_scale,character_maximum_length,extra " + "FROM information_schema.columns " + "WHERE table_schema='" + defSchema + "' ORDER BY table_name, ordinal_position");
    try {
        while (rs.next()) {
            String tableName = rs.getString(1).toLowerCase();
            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));
            String type = rs.getString(3);
            info.setCanBeNull("YES".equals(rs.getString(5)));
            String defaultValue = rs.getString(4);
            if (defaultValue != null) {
                if (type.startsWith("text") || type.startsWith("enum") || type.startsWith("varchar") || type.startsWith("char")) {
                    defaultValue = "'" + defaultValue + "'";
                } else if (type.startsWith("date") || type.startsWith("time")) {
                    if (defaultValue.equalsIgnoreCase("CURRENT_TIMESTAMP")) {
                        defaultValue = "NOW()";
                    } else {
                        defaultValue = "'" + defaultValue + "'";
                    }
                }
            }
            info.setDefaultValue(defaultValue);
            info.setSize(rs.getInt(8));
            if (rs.wasNull()) {
                info.setSize(rs.getInt(6));
            }
            info.setPrecision(rs.getInt(7));
            info.setAutoIncrement("auto_increment".equals(rs.getString(9)));
            if (type.startsWith("enum(")) {
                String[] enumValues = type.substring("enum(".length(), type.length() - 1).split(",", -1);
                for (int i = 0; i < enumValues.length; i++) {
                    enumValues[i] = enumValues[i].substring(1, enumValues[i].length() - 1);
                }
                type = "enum";
                info.setEnumValues(enumValues);
            }
            type = UNNECESSARY_TYPE_LENGTH_PATTERN.matcher(type).replaceFirst("$1");
            info.setType(type.toUpperCase(Locale.ENGLISH));
            // Just to check for interrupts
            controller.setProgress(0);
        }
    } finally {
        connector.close(rs);
    }
    return result;
}

Example 3 with SqlColumnInfo

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

the class OracleSchemaReader method readColumns.

@Override
public Map<String, List<SqlColumnInfo>> readColumns(SqlExecutor sql, String defSchema, ProcessController controller) throws SQLException {
    DbmsConnector connector = sql.getConnector();
    Map<String, List<SqlColumnInfo>> result = new HashMap<>();
    ResultSet rs = connector.executeQuery("SELECT " + "c.table_name," + "c.column_name," + "c.data_type," + "c.char_length," + "c.data_precision," + "c.data_scale," + "c.nullable " + "from user_tab_cols c" + " JOIN entities e ON (UPPER(e.name)=c.table_name)" + " WHERE NOT(c.column_id IS NULL) ORDER BY c.table_name,c.column_id");
    try {
        while (rs.next()) {
            String tableName = rs.getString(1).toLowerCase();
            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(3));
            info.setCanBeNull("Y".equals(rs.getString(7)));
            info.setSize(rs.getInt(5));
            if (rs.wasNull()) {
                info.setSize(rs.getInt(4));
            }
            info.setPrecision(rs.getInt(6));
        }
    } finally {
        connector.close(rs);
    }
    // Read default values as separate query, because it's LONG column which is streaming and
    // transmitted slowly
    rs = connector.executeQuery("SELECT " + "c.data_default," + "c.table_name," + "c.column_name " + "from user_tab_cols c" + " JOIN entities e ON (UPPER(e.name)=c.table_name)" + " WHERE NOT(c.column_id IS NULL) AND NOT (data_default IS NULL) ORDER BY c.table_name");
    try {
        while (rs.next()) {
            // Read streaming column at first
            String defaultValue = rs.getString(1);
            String tableName = rs.getString(2);
            String columnName = rs.getString(3);
            SqlColumnInfo column = findColumn(result, tableName, columnName);
            if (column == null)
                continue;
            defaultValue = defaultValue.trim();
            defaultValue = DEFAULT_DATE_PATTERN.matcher(defaultValue).replaceFirst("'$1'");
            if ("'auto-identity'".equals(defaultValue)) {
                column.setAutoIncrement(true);
            } else {
                column.setDefaultValue(defaultValue);
            }
        }
    } finally {
        connector.close(rs);
    }
    /*rs = connector.executeQuery( "SELECT uc.SEARCH_CONDITION,uc.TABLE_NAME FROM user_constraints uc "
            + " JOIN entities e ON (UPPER(e.name)=uc.table_name)"
            + " WHERE uc.CONSTRAINT_TYPE = 'C'" );*/
    // The following query works faster (much faster!) as it doesn't return "NOT NULL" constraints
    // though it's probably Oracle version specific (at least undocumented)
    // tested on Oracle 11r2
    rs = connector.executeQuery("SELECT c.condition,o.name " + "FROM sys.cdef$ c, sys.\"_CURRENT_EDITION_OBJ\" o,entities e " + "WHERE c.type#=1 AND c.obj# = o.obj# " + "AND o.owner# = userenv('SCHEMAID') " + "AND UPPER(e.name)=o.name");
    try {
        while (rs.next()) {
            String constr = rs.getString(1);
            String table = rs.getString(2);
            // ENUM VALUES
            // Copied from OperationSupport.loadEntityEnums
            StringTokenizer st = new StringTokenizer(constr.trim());
            int nTok = st.countTokens();
            if (nTok < 3) {
                continue;
            }
            String colName = st.nextToken().toUpperCase();
            String in = st.nextToken();
            if (!"IN".equalsIgnoreCase(in)) {
                continue;
            }
            SqlColumnInfo column = findColumn(result, table, colName);
            if (column == null) {
                continue;
            }
            List<String> values = new ArrayList<>();
            try {
                do {
                    String val = st.nextToken("(,')");
                    if (!val.trim().isEmpty()) {
                        values.add(val);
                    }
                } while (st.hasMoreTokens());
            } catch (NoSuchElementException ignore) {
            }
            if (values.size() > 0) {
                column.setEnumValues(values.toArray(new String[values.size()]));
            }
        }
    } finally {
        connector.close(rs);
    }
    rs = connector.executeQuery("SELECT trigger_name,table_name,trigger_body FROM user_triggers " + "WHERE triggering_event='INSERT OR UPDATE' " + "AND TRIGGER_TYPE='BEFORE EACH ROW'");
    try {
        while (rs.next()) {
            // Read streaming column as first
            String triggerBody = rs.getString(3);
            String tableName = rs.getString(2);
            Matcher matcher = GENERATED_TRIGGER_PATTERN.matcher(triggerBody);
            if (matcher.find()) {
                String columnName = matcher.group(1);
                String targetName = matcher.group(3);
                SqlColumnInfo column = findColumn(result, tableName, columnName);
                if (column == null)
                    continue;
                column.setDefaultValue(new ColumnFunction(targetName, ColumnFunction.TRANSFORM_GENERIC).toString());
            }
        }
    } finally {
        connector.close(rs);
    }
    return result;
}

Example 4 with SqlColumnInfo

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

the class PostgresSchemaReader 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.udt_name, " + "c.character_maximum_length, " + "c.numeric_precision, " + "c.numeric_scale, " + "c.is_nullable, " + "ch.check_clause " + "FROM information_schema.columns c " + "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()) {
            String tableName = rs.getString(1);
            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;
}

Example 5 with SqlColumnInfo

use of com.developmentontheedge.be5.metadata.sql.pojo.SqlColumnInfo 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;
}