/**
* Sync this column with the database
*
* @return
*/
public String syncDatabase() {
MTable table = new MTable(getCtx(), getAD_Table_ID(), get_TrxName());
table.set_TrxName(get_TrxName()); // otherwise table.getSQLCreate may miss current column
if (table.get_ID() == 0)
throw new AdempiereException("@NotFound@ @AD_Table_ID@ " + getAD_Table_ID());
// Find Column in Database
Connection conn = null;
try {
conn = DB.getConnectionRO();
DatabaseMetaData md = conn.getMetaData();
String catalog = DB.getDatabase().getCatalog();
String schema = DB.getDatabase().getSchema();
String tableName = table.getTableName();
if (md.storesUpperCaseIdentifiers()) {
tableName = tableName.toUpperCase();
} else if (md.storesLowerCaseIdentifiers()) {
tableName = tableName.toLowerCase();
}
int noColumns = 0;
String sql = null;
//
ResultSet rs = md.getColumns(catalog, schema, tableName, null);
while (rs.next()) {
noColumns++;
String columnName = rs.getString("COLUMN_NAME");
if (!columnName.equalsIgnoreCase(getColumnName())) continue;
// update existing column
boolean notNull = DatabaseMetaData.columnNoNulls == rs.getInt("NULLABLE");
sql = getSQLModify(table, isMandatory() != notNull);
break;
}
rs.close();
rs = null;
// No Table
if (noColumns == 0) sql = table.getSQLCreate();
// No existing column
else if (sql == null) sql = getSQLAdd(table);
if (sql == null) return "No sql";
int no = 0;
if (sql.indexOf(DB.SQLSTATEMENT_SEPARATOR) == -1) {
DB.executeUpdateEx(sql, get_TrxName());
} else {
String statements[] = sql.split(DB.SQLSTATEMENT_SEPARATOR);
for (int i = 0; i < statements.length; i++) {
DB.executeUpdateEx(statements[i], get_TrxName());
}
}
return sql;
} catch (SQLException e) {
throw new AdempiereException(e);
} finally {
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
}
}
}
}
/**
* Check if column exists in database and modify. If not create column.
*
* @param tablename
* @param columnname
* @param v_AD_Reference_ID
* @param v_FieldLength
* @param v_DefaultValue
* @param v_IsMandatory
*/
private int createColumn(Properties ctx, MTable table, MColumn column, boolean doAlter) {
int no = 0;
String sql = null;
ResultSet rst = null;
ResultSet rsc = null;
Connection conn = null;
Trx trx = Trx.get(getTrxName(ctx), true);
if (!trx.commit()) return 0;
try {
// Find Column in Database
conn = trx.getConnection();
DatabaseMetaData md = conn.getMetaData();
String catalog = DB.getDatabase().getCatalog();
String schema = DB.getDatabase().getSchema();
String tableName = table.getTableName();
String columnName = column.getColumnName();
if (DB.isOracle()) {
tableName = tableName.toUpperCase();
columnName = columnName.toUpperCase();
} else if (DB.isPostgreSQL()) {
tableName = tableName.toLowerCase();
columnName = columnName.toLowerCase();
}
rst = md.getTables(catalog, schema, tableName, new String[] {"TABLE"});
if (!rst.next()) {
// table doesn't exist
sql = table.getSQLCreate();
} else {
//
rsc = md.getColumns(catalog, schema, tableName, columnName);
if (rsc.next()) {
if (doAlter) {
// update existing column
boolean notNull = DatabaseMetaData.columnNoNulls == rsc.getInt("NULLABLE");
sql = column.getSQLModify(table, column.isMandatory() != notNull);
}
} else {
// No existing column
sql = column.getSQLAdd(table);
}
rsc.close();
rsc = null;
}
rst.close();
rst = null;
// execute modify or add if needed
if (sql != null && sql.trim().length() > 0) {
log.info(sql);
if (sql.indexOf(DB.SQLSTATEMENT_SEPARATOR) == -1) {
no = DB.executeUpdate(sql, false, trx.getTrxName());
if (no == -1) return 0;
} else {
String statements[] = sql.split(DB.SQLSTATEMENT_SEPARATOR);
for (int i = 0; i < statements.length; i++) {
int count = DB.executeUpdate(statements[i], false, trx.getTrxName());
if (count == -1) {
return 0;
}
no += count;
}
}
}
trx.commit(true);
} catch (SQLException e) {
log.log(Level.SEVERE, e.getLocalizedMessage(), e);
if (rsc != null) {
try {
rsc.close();
} catch (SQLException e1) {
}
rsc = null;
}
if (rst != null) {
try {
rst.close();
} catch (SQLException e1) {
}
rst = null;
}
trx.rollback();
return 0;
}
return 1;
}