/**
* Delete row from the RDBMS.
*
* @param context Current DSpace context
* @param row The row to delete
* @return The number of rows affected (1 or 0)
* @exception SQLException If a database error occurs
*/
public static int delete(Context context, TableRow row) throws SQLException {
if (null == row.getTable()) {
throw new IllegalArgumentException("Row not associated with a table");
}
String pk = getPrimaryKeyColumn(row);
if (row.isColumnNull(pk)) {
throw new IllegalArgumentException("Primary key value is null");
}
return delete(context, row.getTable(), row.getIntColumn(pk));
}
/**
* 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;
}
/**
* A sanity check that ensures a given element and qualifier are unique within a given schema. The
* check happens in code as we cannot use a database constraint.
*
* @param context dspace context
* @param schemaID
* @param element
* @param qualifier
* @return true if unique
* @throws AuthorizeException
* @throws SQLException
* @throws IOException
*/
private boolean unique(Context context, int schemaID, String element, String qualifier)
throws IOException, SQLException, AuthorizeException {
int count = 0;
Connection con = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
con = context.getDBConnection();
TableRow reg = DatabaseManager.row("MetadataFieldRegistry");
String qualifierClause = "";
if (qualifier == null) {
qualifierClause = "and qualifier is null";
} else {
qualifierClause = "and qualifier = ?";
}
String query =
"SELECT COUNT(*) FROM "
+ reg.getTable()
+ " WHERE metadata_schema_id= ? "
+ " and metadata_field_id != ? "
+ " and element= ? "
+ qualifierClause;
statement = con.prepareStatement(query);
statement.setInt(1, schemaID);
statement.setInt(2, fieldID);
statement.setString(3, element);
if (qualifier != null) {
statement.setString(4, qualifier);
}
rs = statement.executeQuery();
if (rs.next()) {
count = rs.getInt(1);
}
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException sqle) {
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException sqle) {
}
}
}
return (count == 0);
}
/**
* Return the name of the primary key column. We assume there's only one primary key per table; if
* there are more, only the first one will be returned.
*
* @param row The TableRow to return the primary key for.
* @return The name of the primary key column, or null if the row has no primary key.
* @exception SQLException If a database error occurs
*/
public static String getPrimaryKeyColumn(TableRow row) throws SQLException {
return getPrimaryKeyColumn(row.getTable());
}
/**
* 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) {
}
}
}
}
