/**
* Utility function to modify column definitions to the MetatData DB tables
*
* <p>This is used from the ALTER TABLE MODIFY
*/
public static void modifyTableColumn(SqlCreateTableColumn columnDefinition, SysTable targetTable)
throws Exception {
StringBuffer sbStatement = new StringBuffer("UPDATE xsyscolumns SET ");
SysColumn aSysCol = targetTable.getSysColumn(columnDefinition.columnName);
sbStatement.append("coltype = ").append(columnDefinition.getColumnType()).append(", ");
sbStatement.append("collength = ");
int colLength = columnDefinition.getColumnLength();
if (colLength > -1) {
sbStatement.append(colLength).append(", ");
} else {
sbStatement.append("null, ");
}
sbStatement.append("colscale = ");
int colScale = columnDefinition.getColumnScale();
if (colScale > -1) {
sbStatement.append(colScale).append(", ");
} else {
sbStatement.append("null, ");
}
sbStatement.append("colprecision = ");
int colPrecision = columnDefinition.getColumnPrecision();
if (colPrecision > -1) {
sbStatement.append(colPrecision).append(", ");
} else {
sbStatement.append("null, ");
}
sbStatement.append("isnullable = ").append(columnDefinition.isnullable).append(", ");
sbStatement.append("isserial = ").append(columnDefinition.isSerial() ? "1" : "0").append(", ");
sbStatement.append("defaultexpr = ");
String defaultExpr = columnDefinition.getDefaultValue();
if (defaultExpr == null) {
sbStatement.append("null, ");
} else {
sbStatement.append("'").append(defaultExpr.replaceAll("'", "''")).append("', ");
}
sbStatement
.append("nativecoldef = '")
.append(columnDefinition.rebuildString().replaceAll("'", "''"))
.append("' ");
sbStatement.append("WHERE colid = ").append(aSysCol.getColID());
MetaData.getMetaData().executeUpdate(sbStatement.toString());
}
/**
* Utility function to add column definitions to the MetatData DB tables
*
* <p>This is used from the CREATE TABLE Also used for ALTER TABLE ADD COLUMN.
*
* <p>It maintains the Restrictions imposed on execute() from IMetaDataUpdate.
*/
public static int[] addTableColumns(
int begColSeq, List<SqlCreateTableColumn> columnDefinitions, int tableid) throws Exception {
int[] colIDs = new int[columnDefinitions.size()];
SqlCreateTableColumn aSqlCreateTableColumn;
String sqlStatement;
String xsyscolumnsBaseStr =
"INSERT INTO xsyscolumns"
+ "("
+ "tableid, "
+ "colseq,"
+ "colname, "
+ "coltype, "
+ "collength, "
+ "colscale, "
+ "colprecision,"
+ "isnullable,"
+ "isserial,"
+ "DEFAULTEXPR, "
+ "CHECKEXPR, "
+ "nativecoldef)"
+ " values ";
for (int i = 0, seq = begColSeq + 1; i < columnDefinitions.size(); i++, seq++) {
aSqlCreateTableColumn = columnDefinitions.get(i);
sqlStatement =
xsyscolumnsBaseStr
+ "("
+ tableid
+ ","
+ seq
+ ","
+ "'"
+ aSqlCreateTableColumn.columnName
+ "',"
+ aSqlCreateTableColumn.getColumnType()
+ ","
+ (aSqlCreateTableColumn.getColumnLength() > -1
? aSqlCreateTableColumn.getColumnLength() + ","
: "null,")
+ (aSqlCreateTableColumn.getColumnScale() > -1
? aSqlCreateTableColumn.getColumnScale() + ","
: "null,")
+ (aSqlCreateTableColumn.getColumnPrecision() > -1
? aSqlCreateTableColumn.getColumnPrecision() + ","
: "null,")
+ aSqlCreateTableColumn.isnullable
+ ","
+ (aSqlCreateTableColumn.isSerial() ? "1" : "0")
+ ",";
String defaultValue = aSqlCreateTableColumn.getDefaultValue();
if (defaultValue != null) {
defaultValue = "'" + defaultValue.replaceAll("'", "''") + "'";
}
sqlStatement += defaultValue + ", ";
String checkString = aSqlCreateTableColumn.getcheckConditionString();
if (checkString != null) {
checkString = checkString.replaceAll("'", "''");
}
sqlStatement += "'" + checkString + "' ,";
sqlStatement +=
"'" + aSqlCreateTableColumn.rebuildString().replaceAll("'", "''") + "') RETURNING colid";
ResultSet keys = MetaData.getMetaData().executeUpdateReturning(sqlStatement);
if (keys.next()) {
colIDs[i] = keys.getInt(1);
} else {
throw new Exception("Error creating table");
}
}
return colIDs;
}