String schema = _info[0].getSchemaName();
String lookupTableName = _customDialog.getLookupTableName();
String lookupPrimaryKey = _customDialog.getLookupPrimaryKey();
TableColumnInfo sourceColumn = null;
for (TableColumnInfo info : md.getColumnInfo((ITableInfo) _info[0]))
{
if (info.getColumnName().equals(_customDialog.getSourceColumn()))
{
sourceColumn = info;
break;
}
}
if (sourceColumn == null)
throw new IllegalStateException("The selected source column was not found.");
String sourceTableName = sourceColumn.getTableName();
String sourceColumnName = sourceColumn.getColumnName();
// Creates the lookup table with it's columns (depenting on the mode).
ArrayList<TableColumnInfo> columns = new ArrayList<TableColumnInfo>();
ArrayList<TableColumnInfo> primaryKeys = new ArrayList<TableColumnInfo>();
if (_customDialog.getMode() == AddLookupTableDialog.MODE_KEEP)
{
TableColumnInfo pk =
new TableColumnInfo( catalog,
schema,
lookupTableName,
lookupPrimaryKey,
sourceColumn.getDataType(),
sourceColumn.getTypeName(),
sourceColumn.getColumnSize(),
sourceColumn.getDecimalDigits(),
sourceColumn.getRadix(),
sourceColumn.isNullAllowed(),
sourceColumn.getRemarks(),
sourceColumn.getDefaultValue(),
sourceColumn.getOctetLength(),
1,
sourceColumn.isNullable(),
md);
columns.add(pk);
primaryKeys.add(pk);
} else if (_customDialog.getMode() == AddLookupTableDialog.MODE_REPLACE)
{
TableColumnInfo pk =
new TableColumnInfo( catalog,
schema,
lookupTableName,
lookupPrimaryKey,
Types.INTEGER,
JDBCTypeMapper.getJdbcTypeName(Types.INTEGER),
0,
0,
0,
0,
null,
null,
0,
1,
"NO",
md);
TableColumnInfo second =
new TableColumnInfo( catalog,
schema,
lookupTableName,
_customDialog.getLookupSecondColumn(),
sourceColumn.getDataType(),
sourceColumn.getTypeName(),
sourceColumn.getColumnSize(),
sourceColumn.getDecimalDigits(),
sourceColumn.getRadix(),
0,
sourceColumn.getRemarks(),
sourceColumn.getDefaultValue(),
sourceColumn.getOctetLength(),
2,
"NO",
md);
columns.add(pk);
columns.add(second);
primaryKeys.add(pk);
}
results.add(_dialect.getCreateTableSQL(lookupTableName, columns, primaryKeys, _sqlPrefs, _qualifier));
if (_customDialog.getMode() == AddLookupTableDialog.MODE_KEEP)
{
// Copys the distinct values from the source column into the lookup table.
ArrayList<String> insertColumns = new ArrayList<String>();
insertColumns.add(lookupPrimaryKey);
String dataQuery = getDataQuery(schema, sourceTableName, sourceColumnName);
results.add(_dialect.getInsertIntoSQL(lookupTableName,
insertColumns,
dataQuery,
_qualifier,
_sqlPrefs));
// Adds a foreign key constraint to the source table.
String constraintName = _customDialog.getForeignKeyName();
ArrayList<String[]> refs = new ArrayList<String[]>();
refs.add(new String[] { sourceColumnName, lookupPrimaryKey });
String[] fkSQLs =
_dialect.getAddForeignKeyConstraintSQL(sourceTableName,
lookupTableName,
constraintName,
false,
false,
false,
false,
null,
refs,
"NO ACTION",
"NO ACTION",
_qualifier,
_sqlPrefs);
results.addAll(Arrays.asList(fkSQLs));
} else if (_customDialog.getMode() == AddLookupTableDialog.MODE_REPLACE)
{
// Selects the distinct values from the source column.
String dataQuery = getDataQuery(schema, sourceTableName, sourceColumnName);
List<String> data = executeStringQuery(dataQuery);
// Copys the gathered values into the lookup table.
ArrayList<String> insertColumns = new ArrayList<String>();
insertColumns.add(lookupPrimaryKey);
insertColumns.add(_customDialog.getLookupSecondColumn());
for (int i = 0; i < data.size(); i++)
{
// TODO: This won't work when the column has values with quotes in them.
// Use PreparedStatements instead. Hmmm... instead of insert statements, could we not
// just create a sequence and generate a select statement that:
// a) uses the sequence for the pkid
// b) selects distinct values
// That would be a more efficient approach that would also eliminate this quoting problem
String valuesPart = " VALUES ( " + i + ", '" + data.get(i) + "' )";
results.add(_dialect.getInsertIntoSQL(lookupTableName,
insertColumns,
valuesPart,
_qualifier,
_sqlPrefs));
}
// Renames the source column.
TableColumnInfo tempColumn =
new TableColumnInfo( catalog,
schema,
sourceTableName,
sourceColumnName + "_temp",
sourceColumn.getDataType(),
sourceColumn.getTypeName(),
sourceColumn.getColumnSize(),
sourceColumn.getDecimalDigits(),
sourceColumn.getRadix(),
sourceColumn.isNullAllowed(),
sourceColumn.getRemarks(),
sourceColumn.getDefaultValue(),
sourceColumn.getOctetLength(),
sourceColumn.getOrdinalPosition(),
sourceColumn.isNullable(),
md);
results.add(_dialect.getColumnNameAlterSQL(sourceColumn, tempColumn, _qualifier, _sqlPrefs));
// Adds the new column (type: integer).
TableColumnInfo newColumn =
new TableColumnInfo( catalog,
schema,
sourceTableName,
sourceColumnName,
Types.INTEGER,
JDBCTypeMapper.getJdbcTypeName(Types.INTEGER),
0,
0,
0,
1,
sourceColumn.getRemarks(),
null,
0,
sourceColumn.getOrdinalPosition(),
"YES",
md);
String[] addColumnResults = _dialect.getAddColumnSQL(newColumn, _qualifier, _sqlPrefs);
for (String addColumnResult : addColumnResults)
{
results.add(addColumnResult);
}
// Adds a foreign key constraint to the source table for the new column.
String constraintName = _customDialog.getForeignKeyName();
ArrayList<String[]> refs = new ArrayList<String[]>();
refs.add(new String[] { sourceColumnName, lookupPrimaryKey });
String[] fkSQLs =
_dialect.getAddForeignKeyConstraintSQL(sourceTableName,
lookupTableName,
constraintName,
false,
false,
false,
false,
null,
refs,
"NO ACTION",
"NO ACTION",
_qualifier,
_sqlPrefs);
results.addAll(Arrays.asList(fkSQLs));
// Inserts all keys into the new column depending on the content in the old column.
for (int i = 0; i < data.size(); i++)
{
// TODO: This won't work when the column has values with quotes in them.
// Use PreparedStatements instead.
results.addAll(Arrays.asList(_dialect.getUpdateSQL(sourceTableName,
new String[] { sourceColumnName },
new String[] { String.valueOf(i) },
null,
new String[] { sourceColumnName + "_temp" },
new String[] { "'" + data.get(i) + "'" },
_qualifier,
_sqlPrefs)));
}
// Sets constraints like NOT NULL for the new column, depending on the constraints for the old
// column.
if (sourceColumn.isNullAllowed() == 0)
{
TableColumnInfo newColumnNotNull =
new TableColumnInfo( catalog,
schema,
sourceTableName,
newColumn.getColumnName(),
newColumn.getDataType(),
newColumn.getTypeName(),