@Override
protected Column readColumn(DatabaseMetaDataWrapper metaData, Map<String, Object> values)
throws SQLException {
Column column = super.readColumn(metaData, values);
if ("SERIAL".equalsIgnoreCase(column.getJdbcTypeName())
|| "BIGSERIAL".equalsIgnoreCase(column.getJdbcTypeName())) {
column.setAutoIncrement(true);
}
return column;
}
/*
* Tries to determine whether the given column is an identity column.
*
* @param table The table
*
* @param column The column
*
* @return <code>true</code> if the column is an identity column
*/
protected boolean isAutoIncrement(Connection connection, Table table, Column column)
throws SQLException {
// TODO: For now, we only check whether there is a sequence & trigger as
// generated by DdlUtils
// But once sequence/trigger support is in place, it might be possible
// to 'parse' the
// trigger body (via SELECT trigger_name, trigger_body FROM
// user_triggers) in order to
// determine whether it fits our auto-increment definition
PreparedStatement prepStmt = null;
IDdlBuilder builder = getPlatform().getDdlBuilder();
String triggerName =
builder.getConstraintName(OracleDdlBuilder.PREFIX_TRIGGER, table, column.getName(), null);
String seqName =
builder.getConstraintName(OracleDdlBuilder.PREFIX_SEQUENCE, table, column.getName(), null);
if (!getPlatform().getDdlBuilder().isDelimitedIdentifierModeOn()) {
triggerName = triggerName.toUpperCase();
seqName = seqName.toUpperCase();
}
try {
prepStmt = connection.prepareStatement("SELECT * FROM user_triggers WHERE trigger_name = ?");
prepStmt.setString(1, triggerName);
ResultSet resultSet = prepStmt.executeQuery();
if (!resultSet.next()) {
resultSet.close();
return false;
}
// we have a trigger, so lets check the sequence
prepStmt.close();
prepStmt =
connection.prepareStatement("SELECT * FROM user_sequences WHERE sequence_name = ?");
prepStmt.setString(1, seqName);
resultSet = prepStmt.executeQuery();
boolean resultFound = resultSet.next();
resultSet.close();
return resultFound;
} finally {
if (prepStmt != null) {
prepStmt.close();
}
}
}
private void alterCaseToMatchLogicalCase(Table table) {
table.setName(table.getName().toUpperCase());
Column[] columns = table.getColumns();
for (Column column : columns) {
column.setName(column.getName().toUpperCase());
}
IIndex[] indexes = table.getIndices();
for (IIndex index : indexes) {
index.setName(index.getName().toUpperCase());
IndexColumn[] indexColumns = index.getColumns();
for (IndexColumn indexColumn : indexColumns) {
indexColumn.setName(indexColumn.getName().toUpperCase());
}
}
}
protected void createDatabase() {
if (databasePlatform == null) {
ResettableBasicDataSource ds = new ResettableBasicDataSource();
ds.setDriverClassName(Driver.class.getName());
ds.setMaxActive(1);
ds.setInitialSize(1);
ds.setMinIdle(1);
ds.setMaxIdle(1);
databaseName = UUID.randomUUID().toString();
if (inMemoryCompare) {
ds.setUrl("jdbc:h2:mem:" + databaseName);
} else {
ds.setUrl("jdbc:h2:file:./" + databaseName);
}
databasePlatform =
JdbcDatabasePlatformFactory.createNewPlatformInstance(
ds, new SqlTemplateSettings(), true, false);
Model inputModel = context.getFlowStep().getComponent().getInputModel();
List<ModelEntity> entities = inputModel.getModelEntities();
for (ModelEntity entity : entities) {
Table table = new Table();
table.setName(entity.getName() + "_1");
List<ModelAttribute> attributes = entity.getModelAttributes();
for (ModelAttribute attribute : attributes) {
DataType dataType = attribute.getDataType();
Column column = new Column(attribute.getName());
if (dataType.isNumeric()) {
column.setTypeCode(Types.DECIMAL);
} else if (dataType.isBoolean()) {
column.setTypeCode(Types.BOOLEAN);
} else if (dataType.isTimestamp()) {
column.setTypeCode(Types.TIMESTAMP);
} else if (dataType.isBinary()) {
column.setTypeCode(Types.BLOB);
} else {
column.setTypeCode(Types.LONGVARCHAR);
}
column.setPrimaryKey(attribute.isPk());
table.addColumn(column);
}
alterCaseToMatchLogicalCase(table);
databasePlatform.createTables(false, false, table);
table.setName(entity.getName().toUpperCase() + "_2");
databasePlatform.createTables(false, false, table);
}
log(LogLevel.INFO, "Creating databasePlatform with the following url: %s", ds.getUrl());
}
}
public static void write(Table table, Writer output) {
try {
output.write("\t<table name=\"" + StringEscapeUtils.escapeXml(table.getName()) + "\">\n");
for (Column column : table.getColumns()) {
output.write("\t\t<column name=\"" + StringEscapeUtils.escapeXml(column.getName()) + "\"");
if (column.isPrimaryKey()) {
output.write(" primaryKey=\"" + column.isPrimaryKey() + "\"");
}
if (column.isRequired()) {
output.write(" required=\"" + column.isRequired() + "\"");
}
if (column.getMappedType() != null) {
output.write(" type=\"" + column.getMappedType() + "\"");
}
if (column.getSize() != null) {
output.write(" size=\"" + column.getSize() + "\"");
}
if (column.getDefaultValue() != null) {
output.write(
" default=\"" + StringEscapeUtils.escapeXml(column.getDefaultValue()) + "\"");
}
if (column.isAutoIncrement()) {
output.write(" autoIncrement=\"" + column.isAutoIncrement() + "\"");
}
if (column.getJavaName() != null) {
output.write(" javaName=\"" + column.getJavaName() + "\"");
}
if (column.getPlatformColumns() != null && column.getPlatformColumns().size() > 0) {
Collection<PlatformColumn> platformColumns = column.getPlatformColumns().values();
output.write(">\n");
for (PlatformColumn platformColumn : platformColumns) {
output.write("\t\t\t<platform-column name=\"" + platformColumn.getName() + "\"");
output.write(" type=\"" + platformColumn.getType() + "\"");
if (platformColumn.getSize() > 0) {
output.write(" size=\"" + platformColumn.getSize() + "\"");
}
if (platformColumn.getDecimalDigits() > 0) {
output.write(" decimalDigits=\"" + platformColumn.getDecimalDigits() + "\"");
}
if (platformColumn.getDefaultValue() != null) {
output.write(
" default=\""
+ StringEscapeUtils.escapeXml(platformColumn.getDefaultValue())
+ "\"");
}
output.write("/>\n");
}
output.write("\t\t</column>\n");
} else {
output.write("/>\n");
}
}
for (ForeignKey fk : table.getForeignKeys()) {
output.write(
"\t\t<foreign-key name=\""
+ StringEscapeUtils.escapeXml(fk.getName())
+ "\" foreignTable=\""
+ StringEscapeUtils.escapeXml(fk.getForeignTableName())
+ "\">\n");
for (Reference ref : fk.getReferences()) {
output.write(
"\t\t\t<reference local=\""
+ StringEscapeUtils.escapeXml(ref.getLocalColumnName())
+ "\" foreign=\""
+ StringEscapeUtils.escapeXml(ref.getForeignColumnName())
+ "\"/>\n");
}
output.write("\t\t</foreign-key>\n");
}
for (IIndex index : table.getIndices()) {
if (index.isUnique()) {
output.write(
"\t\t<unique name=\"" + StringEscapeUtils.escapeXml(index.getName()) + "\">\n");
for (IndexColumn column : index.getColumns()) {
output.write(
"\t\t\t<unique-column name=\""
+ StringEscapeUtils.escapeXml(column.getName())
+ "\"/>\n");
}
output.write("\t\t</unique>\n");
} else {
output.write(
"\t\t<index name=\"" + StringEscapeUtils.escapeXml(index.getName()) + "\">\n");
for (IndexColumn column : index.getColumns()) {
output.write(
"\t\t\t<index-column name=\""
+ StringEscapeUtils.escapeXml(column.getName())
+ "\"");
if (column.getSize() != null) {
output.write(" size=\"" + column.getSize() + "\"");
}
output.write("/>\n");
}
output.write("\t\t</index>\n");
}
}
output.write("\t</table>\n");
} catch (IOException e) {
throw new IoException(e);
}
}
public static Table nextTable(XmlPullParser parser, String catalog, String schema) {
try {
Table table = null;
ForeignKey fk = null;
IIndex index = null;
boolean done = false;
int eventType = parser.getEventType();
while (eventType != XmlPullParser.END_DOCUMENT && !done) {
switch (eventType) {
case XmlPullParser.START_TAG:
String name = parser.getName();
if (name.equalsIgnoreCase("table")) {
table = new Table();
table.setCatalog(catalog);
table.setSchema(schema);
for (int i = 0; i < parser.getAttributeCount(); i++) {
String attributeName = parser.getAttributeName(i);
String attributeValue = parser.getAttributeValue(i);
if (attributeName.equalsIgnoreCase("name")) {
table.setName(attributeValue);
} else if (attributeName.equalsIgnoreCase("description")) {
table.setDescription(attributeValue);
}
}
} else if (name.equalsIgnoreCase("column")) {
Column column = new Column();
for (int i = 0; i < parser.getAttributeCount(); i++) {
String attributeName = parser.getAttributeName(i);
String attributeValue = parser.getAttributeValue(i);
if (attributeName.equalsIgnoreCase("name")) {
column.setName(attributeValue);
} else if (attributeName.equalsIgnoreCase("primaryKey")) {
column.setPrimaryKey(FormatUtils.toBoolean(attributeValue));
} else if (attributeName.equalsIgnoreCase("required")) {
column.setRequired(FormatUtils.toBoolean(attributeValue));
} else if (attributeName.equalsIgnoreCase("type")) {
column.setMappedType(attributeValue);
} else if (attributeName.equalsIgnoreCase("size")) {
column.setSize(attributeValue);
} else if (attributeName.equalsIgnoreCase("default")) {
if (StringUtils.isNotBlank(attributeValue)) {
column.setDefaultValue(attributeValue);
}
} else if (attributeName.equalsIgnoreCase("autoIncrement")) {
column.setAutoIncrement(FormatUtils.toBoolean(attributeValue));
} else if (attributeName.equalsIgnoreCase("javaName")) {
column.setJavaName(attributeValue);
} else if (attributeName.equalsIgnoreCase("description")) {
column.setDescription(attributeValue);
}
}
if (table != null) {
table.addColumn(column);
}
} else if (name.equalsIgnoreCase("platform-column")) {
PlatformColumn platformColumn = new PlatformColumn();
for (int i = 0; i < parser.getAttributeCount(); i++) {
String attributeName = parser.getAttributeName(i);
String attributeValue = parser.getAttributeValue(i);
if (attributeName.equalsIgnoreCase("name")) {
platformColumn.setName(attributeValue);
} else if (attributeName.equalsIgnoreCase("type")) {
platformColumn.setType(attributeValue);
} else if (attributeName.equalsIgnoreCase("default")) {
platformColumn.setDefaultValue(attributeValue);
} else if (attributeName.equalsIgnoreCase("size")) {
if (isNotBlank(attributeValue)) {
platformColumn.setSize(Integer.parseInt(attributeValue));
}
} else if (attributeName.equalsIgnoreCase("decimalDigits")) {
if (isNotBlank(attributeValue)) {
platformColumn.setDecimalDigits(Integer.parseInt(attributeValue));
}
}
}
if (table != null && table.getColumnCount() > 0) {
table.getColumn(table.getColumnCount() - 1).addPlatformColumn(platformColumn);
}
} else if (name.equalsIgnoreCase("foreign-key")) {
fk = new ForeignKey();
for (int i = 0; i < parser.getAttributeCount(); i++) {
String attributeName = parser.getAttributeName(i);
String attributeValue = parser.getAttributeValue(i);
if (attributeName.equalsIgnoreCase("name")) {
fk.setName(attributeValue);
} else if (attributeName.equalsIgnoreCase("foreignTable")) {
fk.setForeignTableName(attributeValue);
}
}
table.addForeignKey(fk);
} else if (name.equalsIgnoreCase("reference")) {
Reference ref = new Reference();
for (int i = 0; i < parser.getAttributeCount(); i++) {
String attributeName = parser.getAttributeName(i);
String attributeValue = parser.getAttributeValue(i);
if (attributeName.equalsIgnoreCase("local")) {
ref.setLocalColumnName(attributeValue);
} else if (attributeName.equalsIgnoreCase("foreign")) {
ref.setForeignColumnName(attributeValue);
}
}
fk.addReference(ref);
} else if (name.equalsIgnoreCase("index") || name.equalsIgnoreCase("unique")) {
if (name.equalsIgnoreCase("index")) {
index = new NonUniqueIndex();
} else {
index = new UniqueIndex();
}
for (int i = 0; i < parser.getAttributeCount(); i++) {
String attributeName = parser.getAttributeName(i);
String attributeValue = parser.getAttributeValue(i);
if (attributeName.equalsIgnoreCase("name")) {
index.setName(attributeValue);
}
}
table.addIndex(index);
} else if (name.equalsIgnoreCase("index-column")
|| name.equalsIgnoreCase("unique-column")) {
IndexColumn indexColumn = new IndexColumn();
for (int i = 0; i < parser.getAttributeCount(); i++) {
String attributeName = parser.getAttributeName(i);
String attributeValue = parser.getAttributeValue(i);
if (attributeName.equalsIgnoreCase("name")) {
indexColumn.setName(attributeValue);
} else if (attributeName.equalsIgnoreCase("size")) {
indexColumn.setSize(attributeValue);
}
}
indexColumn.setColumn(table.getColumnWithName(indexColumn.getName()));
if (index != null) {
index.addColumn(indexColumn);
}
}
break;
case XmlPullParser.END_TAG:
name = parser.getName();
if (name.equalsIgnoreCase("table")) {
done = true;
} else if (name.equalsIgnoreCase("index") || name.equalsIgnoreCase("unique")) {
index = null;
} else if (name.equalsIgnoreCase("table")) {
table = null;
} else if (name.equalsIgnoreCase("foreign-key")) {
fk = null;
}
break;
}
if (!done) {
eventType = parser.next();
}
}
return table;
} catch (XmlPullParserException e) {
throw new IoException(e);
} catch (IOException e) {
throw new IoException(e);
}
}
@Override
protected Column readColumn(DatabaseMetaDataWrapper metaData, Map<String, Object> values)
throws SQLException {
Column column = super.readColumn(metaData, values);
if (column.getMappedTypeCode() == Types.DECIMAL) {
// We're back-mapping the NUMBER columns returned by Oracle
// Note that the JDBC driver returns DECIMAL for these NUMBER
// columns
if (column.getScale() <= -127 || column.getScale() >= 127) {
if (column.getSizeAsInt() == 0) {
/*
* Latest oracle jdbc drivers for 11g return (0,-127) for
* types defined as integer resulting in bad mappings.
* NUMBER without scale or precision looks the same as
* INTEGER in the jdbc driver. We must check the Oracle
* meta data to see if type is an INTEGER.
*/
if (isColumnInteger(
(String) values.get("TABLE_NAME"), (String) values.get("COLUMN_NAME"))) {
column.setMappedTypeCode(Types.BIGINT);
}
} else if (column.getSizeAsInt() <= 63) {
column.setMappedTypeCode(Types.REAL);
} else {
column.setMappedTypeCode(Types.DOUBLE);
}
}
} else if (column.getMappedTypeCode() == Types.FLOAT) {
// Same for REAL, FLOAT, DOUBLE PRECISION, which all back-map to
// FLOAT but with
// different sizes (63 for REAL, 126 for FLOAT/DOUBLE PRECISION)
switch (column.getSizeAsInt()) {
case 63:
column.setMappedTypeCode(Types.REAL);
break;
case 126:
column.setMappedTypeCode(Types.DOUBLE);
break;
}
} else if ((column.getMappedTypeCode() == Types.DATE)
|| (column.getMappedTypeCode() == Types.TIMESTAMP)) {
// we also reverse the ISO-format adaptation, and adjust the default
// value to timestamp
if (column.getDefaultValue() != null) {
Timestamp timestamp = null;
Matcher matcher = oracleIsoTimestampPattern.matcher(column.getDefaultValue());
if (matcher.matches()) {
String timestampVal = matcher.group(1);
timestamp = Timestamp.valueOf(timestampVal);
} else {
matcher = oracleIsoDatePattern.matcher(column.getDefaultValue());
if (matcher.matches()) {
String dateVal = matcher.group(1);
timestamp = new Timestamp(Date.valueOf(dateVal).getTime());
} else {
matcher = oracleIsoTimePattern.matcher(column.getDefaultValue());
if (matcher.matches()) {
String timeVal = matcher.group(1);
timestamp = new Timestamp(Time.valueOf(timeVal).getTime());
}
}
}
if (timestamp != null) {
column.setDefaultValue(timestamp.toString());
}
}
} else if (TypeMap.isTextType(column.getMappedTypeCode())) {
String defaultValue = column.getDefaultValue();
if (isNotBlank(defaultValue)
&& defaultValue.startsWith("('")
&& defaultValue.endsWith("')")) {
defaultValue = defaultValue.substring(2, defaultValue.length() - 2);
}
column.setDefaultValue(unescape(defaultValue, "'", "''"));
}
return column;
}