use of com.developmentontheedge.be5.metadata.model.ColumnFunction 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;
}
use of com.developmentontheedge.be5.metadata.model.ColumnFunction 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;
}
use of com.developmentontheedge.be5.metadata.model.ColumnFunction 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;
}
use of com.developmentontheedge.be5.metadata.model.ColumnFunction in project be5 by DevelopmentOnTheEdge.
the class DefaultTypeManager method getColumnDefinitionClause.
@Override
public String getColumnDefinitionClause(ColumnDef column) {
StringBuilder sb = new StringBuilder(normalizeIdentifier(column.getName()));
sb.append(' ').append(getTypeClause(column));
if (column.isAutoIncrement()) {
sb.append(' ').append(getAutoIncrementClause(column));
}
if (column.getDefaultValue() != null) {
String defaultValue = getDefaultValue(column);
ColumnFunction function = new ColumnFunction(defaultValue);
if (function.isTransformed()) {
if (getGeneratedPrefix() != null) {
sb.append(' ').append(getGeneratedPrefix()).append(' ').append(function.getDefinition(column.getProject().getDatabaseSystem(), column.getEntity().getName()));
}
} else {
sb.append(' ').append("DEFAULT ").append(defaultValue);
}
}
addCanBeNullAndAndConstraintClause(column, sb);
if (column.isPrimaryKey()) {
sb.append(' ').append("PRIMARY KEY");
}
return sb.toString();
}
use of com.developmentontheedge.be5.metadata.model.ColumnFunction in project be5 by DevelopmentOnTheEdge.
the class OracleTypeManager method getDropTriggerDefinition.
@Override
public String getDropTriggerDefinition(ColumnDef column) {
String defaultValue = column.getDefaultValue();
if (defaultValue == null)
return "";
ColumnFunction function = new ColumnFunction(defaultValue);
if (ColumnFunction.TRANSFORM_GENERIC.equals(function.getTransform())) {
return "DROP TRIGGER " + getTriggerName(column) + ";\n";
}
return "";
}
Aggregations