/**
* Update changes to the RDBMS. Note that if the update fails, the values in the row will NOT be
* reverted.
*
* @param context Current DSpace context
* @param row The row to update
* @return The number of rows affected (1 or 0)
* @exception SQLException If a database error occurs
*/
public static int update(Context context, TableRow row) throws SQLException {
String table = row.getTable();
StringBuilder sql = new StringBuilder().append("update ").append(table).append(" set ");
List<ColumnInfo> columns = new ArrayList<ColumnInfo>();
ColumnInfo pk = getPrimaryKeyColumnInfo(table);
Collection<ColumnInfo> info = getColumnInfo(table);
String separator = "";
for (ColumnInfo col : info) {
// Only update this column if it has changed
if (!col.isPrimaryKey()) {
if (row.hasColumnChanged(col.getName())) {
sql.append(separator).append(col.getName()).append(" = ?");
columns.add(col);
separator = ", ";
}
}
}
// Only execute the update if there is anything to update
if (columns.size() > 0) {
sql.append(" where ").append(pk.getName()).append(" = ?");
columns.add(pk);
return executeUpdate(context.getDBConnection(), sql.toString(), columns, row);
}
return 1;
}
@Override
public String getUniqueHeaderColumnName(String proposedName) {
for (ColumnInfo info : getHeaderColumnInfoList()) {
this.nameValidator.addExistingName(info.getName());
}
String changedName = this.nameValidator.createUniqueName(proposedName);
String finalName = changedName == null ? proposedName : changedName;
this.nameValidator.clearExistingNames();
return finalName;
}
/**
* Return the names of all the columns of the given table.
*
* @param table The name of the table
* @return The names of all the columns of the given table, as a List. Each element of the list is
* a String.
* @exception SQLException If a database error occurs
*/
static List<String> getColumnNames(String table) throws SQLException {
List<String> results = new ArrayList<String>();
Collection<ColumnInfo> info = getColumnInfo(table);
for (ColumnInfo col : info) {
results.add(col.getName());
}
return results;
}
/**
* Add a new field into this result set. The field will be inserted in the order of the parameters
* in the select statement if those parameters were specified upon construction of the result set;
* otherwise, the field will be appended to the result set.
*
* <p>
*
* @param info The column information.
*/
public void addField(ColumnInfo info) {
// Add to ordered list of fields
if (fields == null) {
fields = new ArrayList();
}
fields.add(info.getName());
// Save column information
if (columnInfos == null) {
columnInfos = new HashMap();
}
columnInfos.put(info.getName(), info);
// Add new field to each record
if (records != null) {
for (int i = 0; i < records.size(); i++) {
List record = (List) records.get(i);
record.add(null);
}
}
}
/**
* Used for text output
*
* @param rList
*/
public static void updateColumnWidth(ResultList rList) {
if (rList == null || rList.getColumnDescriptor() == null || rList.getRows().size() == 0) return;
List<ColumnInfo> cols = rList.getColumnDescriptor().getColumns();
for (ColumnInfo col : cols) {
col.setMaxLength(col.getName().length());
}
for (ResultRow row : rList.getRows()) {
List<String> l = row.getColumns();
for (int i = 0; i < l.size(); i++) {
String s = l.get(i);
if (s != null && s.length() > cols.get(i).getMaxLength())
cols.get(i).setMaxLength(s.length());
}
}
}
/**
* Return the name of the primary key column in the given table. We assume there's only one
* primary key per table; if there are more, only the first one will be returned.
*
* @param table The name of the RDBMS table
* @return The name of the primary key column, or null if the table has no primary key.
* @exception SQLException If a database error occurs
*/
protected static String getPrimaryKeyColumn(String table) throws SQLException {
ColumnInfo info = getPrimaryKeyColumnInfo(table);
return (info == null) ? null : info.getName();
}
/**
* Generic version of row insertion with separate id get / insert
*
* @param context
* @param row
* @return
* @throws SQLException
*/
private static int doInsertGeneric(Context context, TableRow row) throws SQLException {
int newID = -1;
String table = row.getTable();
PreparedStatement statement = null;
ResultSet rs = null;
try {
// Get an ID (primary key) for this row by using the "getnextid"
// SQL function in Postgres, or directly with sequences in Oracle
if (isOracle) {
statement =
context
.getDBConnection()
.prepareStatement("SELECT " + table + "_seq" + ".nextval FROM dual");
} else {
statement = context.getDBConnection().prepareStatement("SELECT getnextid(?) AS result");
loadParameters(statement, new Object[] {table});
}
rs = statement.executeQuery();
rs.next();
newID = rs.getInt(1);
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException sqle) {
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException sqle) {
}
}
}
if (newID < 0) {
throw new SQLException("Unable to retrieve sequence ID");
}
// Set the ID in the table row object
row.setColumn(getPrimaryKeyColumn(table), newID);
Collection<ColumnInfo> info = getColumnInfo(table);
String sql = insertSQL.get(table);
if (sql == null) {
StringBuilder sqlBuilder =
new StringBuilder().append("INSERT INTO ").append(table).append(" ( ");
boolean firstColumn = true;
for (ColumnInfo col : info) {
if (firstColumn) {
sqlBuilder.append(col.getName());
firstColumn = false;
} else {
sqlBuilder.append(",").append(col.getName());
}
}
sqlBuilder.append(") VALUES ( ");
// Values to insert
firstColumn = true;
for (int i = 0; i < info.size(); i++) {
if (firstColumn) {
sqlBuilder.append("?");
firstColumn = false;
} else {
sqlBuilder.append(",").append("?");
}
}
// Watch the syntax
sqlBuilder.append(")");
sql = sqlBuilder.toString();
insertSQL.put(table, sql);
}
execute(context.getDBConnection(), sql, info, row);
return newID;
}
/**
* Postgres-specific row insert, combining getnextid() and insert into single statement for
* efficiency
*
* @param context
* @param row
* @return
* @throws SQLException
*/
private static int doInsertPostgres(Context context, TableRow row) throws SQLException {
String table = row.getTable();
Collection<ColumnInfo> info = getColumnInfo(table);
Collection<ColumnInfo> params = new ArrayList<ColumnInfo>();
String primaryKey = getPrimaryKeyColumn(table);
String sql = insertSQL.get(table);
boolean firstColumn = true;
boolean foundPrimaryKey = false;
if (sql == null) {
// Generate SQL and filter parameter columns
StringBuilder insertBuilder = new StringBuilder("INSERT INTO ").append(table).append(" ( ");
StringBuilder valuesBuilder = new StringBuilder(") VALUES ( ");
for (ColumnInfo col : info) {
if (firstColumn) {
firstColumn = false;
} else {
insertBuilder.append(",");
valuesBuilder.append(",");
}
insertBuilder.append(col.getName());
if (!foundPrimaryKey && col.isPrimaryKey()) {
valuesBuilder.append("getnextid('").append(table).append("')");
foundPrimaryKey = true;
} else {
valuesBuilder.append('?');
params.add(col);
}
}
sql =
insertBuilder
.append(valuesBuilder.toString())
.append(") RETURNING ")
.append(getPrimaryKeyColumn(table))
.toString();
insertSQL.put(table, sql);
} else {
// Already have SQL, just filter parameter columns
for (ColumnInfo col : info) {
if (!foundPrimaryKey && col.isPrimaryKey()) {
foundPrimaryKey = true;
} else {
params.add(col);
}
}
}
PreparedStatement statement = null;
if (log.isDebugEnabled()) {
log.debug("Running query \"" + sql + "\"");
}
ResultSet rs = null;
try {
statement = context.getDBConnection().prepareStatement(sql);
loadParameters(statement, params, row);
rs = statement.executeQuery();
rs.next();
return rs.getInt(1);
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException sqle) {
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException sqle) {
}
}
}
}
public static ResultList paserBindList(ResultList rList) {
if (rList != null && rList.getRows().size() > 0) {
logger.fine("BindController returns " + rList.getRows().size());
ResultList newList = new ResultList();
ColumnDescriptor desc = new ColumnDescriptor();
for (ColumnInfo col : rList.getColumnDescriptor().getColumns()) {
if ("binds_xml".equalsIgnoreCase(col.getName())) continue;
desc.addColumn(col.getName(), col.isNumberType(), desc.getColumns().size());
}
desc.addColumn("NAME", false, desc.getColumns().size());
desc.addColumn("POS", false, desc.getColumns().size());
desc.addColumn("DTYSTR", false, desc.getColumns().size());
desc.addColumn("MAXLEN", false, desc.getColumns().size());
desc.addColumn("LEN", false, desc.getColumns().size());
desc.addColumn("VALUE", false, desc.getColumns().size());
newList.setColumnDescriptor(desc);
int idx = rList.getColumnDescriptor().getColumnIndex("BINDS_XML");
XMLInputFactory inputFactory = XMLInputFactory.newInstance();
for (ResultRow row : rList.getRows()) {
// logger.info("binds_xml.idx="+idx+", "+ row.getColumns().size());
List<BindVariable> binds = null;
try {
String bindXml = row.getColumns().get(idx);
binds = parseBinds(bindXml, inputFactory);
} catch (Exception iex) {
}
if (binds == null || binds.size() == 0) {
ResultRow newRow = new ResultRow();
newRow.setColumnDescriptor(desc);
List<String> cols = new java.util.ArrayList<String>(row.getColumns().size() + 5);
for (int i = 0; i < row.getColumns().size(); i++) {
if (i == idx) continue;
cols.add(row.getColumns().get(i));
}
cols.add(null);
cols.add(null);
cols.add(null);
cols.add(null);
cols.add(null);
cols.add(null);
newRow.setColumns(cols);
newList.addRow(newRow);
} else {
for (BindVariable var : binds) {
ResultRow newRow = new ResultRow();
newRow.setColumnDescriptor(desc);
List<String> cols = new java.util.ArrayList<String>(row.getColumns().size() + 5);
for (int i = 0; i < row.getColumns().size(); i++) {
if (i == idx) continue;
cols.add(row.getColumns().get(i));
}
cols.add(var.name);
cols.add(var.pos);
cols.add(var.dtystr);
cols.add(var.maxLen);
cols.add(var.len);
cols.add(var.value);
newRow.setColumns(cols);
newList.addRow(newRow);
}
}
}
return newList;
}
return null;
}