private void executeTableCreate(
Connection connection, IDialectSpecifier dialectSpecifier, JsonObject dsDefinitionObject)
throws SQLException {
StringBuilder sql = new StringBuilder();
String tableName = dsDefinitionObject.get(TABLE_NAME).getAsString();
sql.append(CREATE_TABLE + tableName + " ("); // $NON-NLS-1$
JsonArray columns = dsDefinitionObject.get(COLUMNS).getAsJsonArray();
int i = 0;
for (JsonElement jsonElement : columns) {
if (jsonElement instanceof JsonObject) {
if (i > 0 && i < columns.size()) {
sql.append(", "); // $NON-NLS-1$
}
JsonObject jsonObject = (JsonObject) jsonElement;
String name = jsonObject.get(COLUMN_NAME).getAsString();
String type =
dbUtils.specifyDataType(connection, jsonObject.get(COLUMN_TYPE).getAsString());
String length = jsonObject.get(COLUMN_LENGTH).getAsString();
boolean notNull = jsonObject.get(COLUMN_NOT_NULL).getAsBoolean();
boolean primaryKey = jsonObject.get(COLUMN_PRIMARY_KEY).getAsBoolean();
String defaultValue = jsonObject.get(COLUMN_DEFAULT_VALUE).getAsString();
sql.append(name + " " + type); // $NON-NLS-1$
if (DBSupportedTypesMap.VARCHAR.equals(type) || DBSupportedTypesMap.CHAR.equals(type)) {
sql.append("(" + length + ") "); // $NON-NLS-1$ //$NON-NLS-2$
} else {
sql.append(" "); // $NON-NLS-1$
}
if (notNull) {
sql.append(NOT_NULL);
}
if (primaryKey) {
sql.append(PRIMARY_KEY);
}
if (defaultValue != null && !"".equals(defaultValue)) { // $NON-NLS-1$
sql.append(DEFAULT + defaultValue + " "); // $NON-NLS-1$
}
}
i++;
}
sql.append(")"); // $NON-NLS-1$
String sqlExpression = sql.toString();
try {
logger.info(sqlExpression);
executeUpdateSQL(connection, sqlExpression);
} catch (SQLException e) {
logger.error(sqlExpression);
throw new SQLException(sqlExpression, e);
}
}
private void executeTableUpdate(
Connection connection, IDialectSpecifier dialectSpecifier, JsonObject dsDefinitionObject)
throws SQLException {
StringBuilder sql = new StringBuilder();
String tableName = dsDefinitionObject.get(TABLE_NAME).getAsString().toUpperCase();
Map<String, String> columnDefinitions = new HashMap<String, String>();
ResultSet rsColumns =
connection.getMetaData().getColumns(null, null, tableName, "%"); // $NON-NLS-1$
while (rsColumns.next()) {
String typeName = DBSupportedTypesMap.getTypeName(rsColumns.getInt(5));
columnDefinitions.put(rsColumns.getString(4).toUpperCase(), typeName);
}
sql.append(ALTER_TABLE + tableName + " "); // $NON-NLS-1$
JsonArray columns = dsDefinitionObject.get(COLUMNS).getAsJsonArray();
int i = 0;
StringBuffer addSql = new StringBuffer();
addSql.append(dialectSpecifier.getAlterAddOpen());
for (JsonElement jsonElement : columns) {
if (jsonElement instanceof JsonObject) {
JsonObject jsonObject = (JsonObject) jsonElement;
String name = jsonObject.get(COLUMN_NAME).getAsString().toUpperCase();
String type =
dbUtils.specifyDataType(
connection, jsonObject.get(COLUMN_TYPE).getAsString().toUpperCase());
String length = jsonObject.get(COLUMN_LENGTH).getAsString();
boolean notNull = jsonObject.get(COLUMN_NOT_NULL).getAsBoolean();
boolean primaryKey = jsonObject.get(COLUMN_PRIMARY_KEY).getAsBoolean();
String defaultValue = jsonObject.get(COLUMN_DEFAULT_VALUE).getAsString();
if (!columnDefinitions.containsKey(name)) {
if (i > 0) {
addSql.append(", "); // $NON-NLS-1$
}
addSql.append(name + " " + type); // $NON-NLS-1$
if (DBSupportedTypesMap.VARCHAR.equals(type) || DBSupportedTypesMap.CHAR.equals(type)) {
addSql.append("(" + length + ") "); // $NON-NLS-1$ //$NON-NLS-2$
} else {
addSql.append(" "); // $NON-NLS-1$
}
if (notNull) {
// sql.append("NOT NULL ");
throw new SQLException(
INCOMPATIBLE_CHANGE_OF_TABLE
+ tableName
+ AND_COLUMN
+ name
+ ADDING_NOT_NULL_COLUMN);
}
if (primaryKey) {
// sql.append("PRIMARY KEY ");
throw new SQLException(
INCOMPATIBLE_CHANGE_OF_TABLE
+ tableName
+ AND_COLUMN
+ name
+ ADDING_PRIMARY_KEY_COLUMN);
}
if (defaultValue != null && !"".equals(defaultValue)) { // $NON-NLS-1$
sql.append(DEFAULT + defaultValue + " "); // $NON-NLS-1$
}
i++;
} else if (!columnDefinitions.get(name).equals(type)) {
throw new SQLException(
INCOMPATIBLE_CHANGE_OF_TABLE
+ tableName
+ AND_COLUMN
+ name
+ TYPE2
+ columnDefinitions.get(name)
+ CANNOT_BE_CHANGED_TO
+ type);
}
}
}
// TODO Derby does not support multiple ADD in a single statement!
if (i > 0) {
addSql.append(dialectSpecifier.getAlterAddClose());
sql.append(addSql.toString());
}
if (columnDefinitions.size() > columns.size()) {
throw new SQLException(
INCOMPATIBLE_CHANGE_OF_TABLE + tableName + DASH + AUTOMATIC_DROP_COLUMN_NOT_SUPPORTED);
}
if (i > 0) {
String sqlExpression = sql.toString();
try {
logger.info(sqlExpression);
executeUpdateSQL(connection, sqlExpression);
} catch (SQLException e) {
logger.error(sqlExpression);
throw new SQLException(sqlExpression, e);
}
}
}
