/**
* Get all workflow items for a particular collection.
*
* @param context the context object
* @param c the collection
* @return array of the corresponding workflow items
*/
public static WorkflowItem[] findByCollection(Context context, Collection c) throws SQLException {
List wsItems = new ArrayList();
TableRowIterator tri =
DatabaseManager.queryTable(
context,
"workflowitem",
"SELECT workflowitem.* FROM workflowitem WHERE " + "workflowitem.collection_id= ? ",
c.getID());
try {
while (tri.hasNext()) {
TableRow row = tri.next();
// Check the cache
WorkflowItem wi =
(WorkflowItem) context.fromCache(WorkflowItem.class, row.getIntColumn("workflow_id"));
// not in cache? turn row into workflowitem
if (wi == null) {
wi = new WorkflowItem(context, row);
}
wsItems.add(wi);
}
} finally {
if (tri != null) tri.close();
}
WorkflowItem[] wsArray = new WorkflowItem[wsItems.size()];
wsArray = (WorkflowItem[]) wsItems.toArray(wsArray);
return wsArray;
}
/* (non-Javadoc)
* @see org.dspace.browse.BrowseDAO#doValueQuery()
*/
public List<String[]> doValueQuery() throws BrowseException {
String query = getQuery();
Object[] params = getQueryParams();
log.debug(LogManager.getHeader(context, "executing_value_query", "query=" + query));
TableRowIterator tri = null;
try {
// now run the query
tri = DatabaseManager.query(context, query, params);
// go over the query results and process
List<String[]> results = new ArrayList<String[]>();
while (tri.hasNext()) {
TableRow row = tri.next();
String valueResult = row.getStringColumn("value");
String authorityResult = row.getStringColumn("authority");
if (enableBrowseFrequencies) {
long frequency = row.getLongColumn("num");
results.add(new String[] {valueResult, authorityResult, String.valueOf(frequency)});
} else results.add(new String[] {valueResult, authorityResult, ""});
}
return results;
} catch (SQLException e) {
log.error("caught exception: ", e);
throw new BrowseException(e);
} finally {
if (tri != null) {
tri.close();
}
}
}
public void flagSpiders(Context context) throws SQLException {
String sql = "select closed from stats.control where control_id=1";
TableRow row = DatabaseManager.querySingle(context, sql);
if (row != null) {
Date closed = row.getDateColumn("closed");
// mark spider events if the IP is in spiders
// dont change events before closed date
sql =
"update "
+ table
+ " set spider=true "
+ "where date > ? and spider=false "
+ "and ip in (select ip from stats.ip_spider)";
DatabaseManager.updateQuery(context, sql, new java.sql.Date(closed.getTime()));
// unmark spider events if the IP isnt in spiders
// dont change events before closed date
sql =
"update "
+ table
+ " set spider=false "
+ "where date > ? and spider=true "
+ "and ip not in (select ip from stats.ip_spider)";
DatabaseManager.updateQuery(context, sql, new java.sql.Date(closed.getTime()));
}
}
/**
* Return a List of the policies for a group
*
* @param c current context
* @param g group to retrieve policies for
* @return List of <code>ResourcePolicy</code> objects
*/
public static List<ResourcePolicy> getPoliciesForGroup(Context c, Group g) throws SQLException {
TableRowIterator tri =
DatabaseManager.queryTable(
c,
"resourcepolicy",
"SELECT * FROM resourcepolicy WHERE epersongroup_id= ? ",
g.getID());
List<ResourcePolicy> policies = new ArrayList<ResourcePolicy>();
try {
while (tri.hasNext()) {
TableRow row = tri.next();
// first check the cache (FIXME: is this right?)
ResourcePolicy cachepolicy =
(ResourcePolicy) c.fromCache(ResourcePolicy.class, row.getIntColumn("policy_id"));
if (cachepolicy != null) {
policies.add(cachepolicy);
} else {
policies.add(new ResourcePolicy(c, row));
}
}
} finally {
if (tri != null) {
tri.close();
}
}
return policies;
}
/* (non-Javadoc)
* @see org.dspace.browse.BrowseDAO#doQuery()
*/
public List<BrowseItem> doQuery() throws BrowseException {
String query = getQuery();
Object[] params = getQueryParams();
if (log.isDebugEnabled()) {
log.debug(LogManager.getHeader(context, "executing_full_query", "query=" + query));
}
TableRowIterator tri = null;
try {
// now run the query
tri = DatabaseManager.query(context, query, params);
// go over the query results and process
List<BrowseItem> results = new ArrayList<BrowseItem>();
while (tri.hasNext()) {
TableRow row = tri.next();
BrowseItem browseItem =
new BrowseItem(context, row.getIntColumn("item_id"), itemsInArchive, itemsWithdrawn);
results.add(browseItem);
}
return results;
} catch (SQLException e) {
log.error("caught exception: ", e);
throw new BrowseException("problem with query: " + query, e);
} finally {
if (tri != null) {
tri.close();
}
}
}
/**
* Utility method for reading in a group from a group ID in a column. If the column is null, null
* is returned.
*
* @param col the column name to read
* @return the group referred to by that column, or null
* @throws SQLException
*/
private Group groupFromColumn(TableRow row, String col) {
if (row.isColumnNull(col)) {
return null;
}
return groupDAO.retrieve(row.getIntColumn(col));
}
/**
* Return the workflow item to the workspace of the submitter. The workflow item is removed, and a
* workspace item created.
*
* @param c Context
* @param wfi WorkflowItem to be 'dismantled'
* @return the workspace item
*/
private static WorkspaceItem returnToWorkspace(Context c, WorkflowItem wfi)
throws SQLException, IOException, AuthorizeException {
Item myitem = wfi.getItem();
Collection mycollection = wfi.getCollection();
// FIXME: How should this interact with the workflow system?
// FIXME: Remove license
// FIXME: Provenance statement?
// Create the new workspace item row
TableRow row = DatabaseManager.create(c, "workspaceitem");
row.setColumn("item_id", myitem.getID());
row.setColumn("collection_id", mycollection.getID());
DatabaseManager.update(c, row);
int wsi_id = row.getIntColumn("workspace_item_id");
WorkspaceItem wi = WorkspaceItem.find(c, wsi_id);
wi.setMultipleFiles(wfi.hasMultipleFiles());
wi.setMultipleTitles(wfi.hasMultipleTitles());
wi.setPublishedBefore(wfi.isPublishedBefore());
wi.update();
// myitem.update();
log.info(
LogManager.getHeader(
c,
"return_to_workspace",
"workflow_item_id=" + wfi.getID() + "workspace_item_id=" + wi.getID()));
// Now remove the workflow object manually from the database
DatabaseManager.updateQuery(c, "DELETE FROM WorkflowItem WHERE workflow_id=" + wfi.getID());
return wi;
}
/**
* Obtiene todas las revisiones finalizadas.
*
* @param context DSpace context object
* @return an iterator over the items in the archive.
* @throws SQLException
*/
public static RevisionToken[] findAllRevisiones(Context context) throws SQLException {
String myQuery = "SELECT * FROM revision_token WHERE tipo='R' and revision_id is not null";
TableRowIterator rows = DatabaseManager.queryTable(context, "revision_token", myQuery);
try {
List<TableRow> revisionRows = rows.toList();
RevisionToken[] revisionToken = new RevisionToken[revisionRows.size()];
for (int i = 0; i < revisionRows.size(); i++) {
TableRow row = (TableRow) revisionRows.get(i);
// First check the cache
RevisionToken fromCache =
(RevisionToken)
context.fromCache(RevisionToken.class, row.getIntColumn("revision_token_id"));
if (fromCache != null) {
revisionToken[i] = fromCache;
} else {
revisionToken[i] = new RevisionToken(row);
}
}
return revisionToken;
} finally {
if (rows != null) {
rows.close();
}
}
}
/* (non-Javadoc)
* @see org.dspace.browse.BrowseDAO#doValueQuery()
*/
public List doValueQuery() throws BrowseException {
String query = getQuery();
Object[] params = getQueryParams();
log.debug(LogManager.getHeader(context, "executing_value_query", "query=" + query));
TableRowIterator tri = null;
try {
// now run the query
tri = DatabaseManager.query(context, query, params);
// go over the query results and process
List results = new ArrayList();
while (tri.hasNext()) {
TableRow row = tri.next();
String stringResult = row.getStringColumn("value");
results.add(stringResult);
}
return results;
} catch (SQLException e) {
log.error("caught exception: ", e);
throw new BrowseException(e);
} finally {
if (tri != null) {
tri.close();
}
}
}
/**
* 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;
}
/* (non-Javadoc)
* @see org.dspace.browse.BrowseDAO#doDistinctOffsetQuery(java.lang.String, java.lang.String, java.lang.String)
*/
public int doDistinctOffsetQuery(String column, String value) throws BrowseException {
TableRowIterator tri = null;
try {
List paramsList = new ArrayList();
StringBuffer queryBuf = new StringBuffer();
queryBuf.append("COUNT(").append(column).append(") AS offset ");
buildSelectStatementDistinct(queryBuf, paramsList);
queryBuf.append(" WHERE ").append(column).append("<?");
paramsList.add(value);
if (containerTable != null && tableMap != null) {
queryBuf.append(" AND ").append("mappings.distinct_id=");
queryBuf.append(table).append(".id");
}
tri = DatabaseManager.query(context, queryBuf.toString(), paramsList.toArray());
TableRow row;
if (tri.hasNext()) {
row = tri.next();
return (int) row.getLongColumn("offset");
} else {
return 0;
}
} catch (SQLException e) {
throw new BrowseException(e);
} finally {
if (tri != null) {
tri.close();
}
}
}
/* (non-Javadoc)
* @see org.dspace.browse.BrowseDAO#doCountQuery()
*/
public int doCountQuery() throws BrowseException {
String query = getQuery();
Object[] params = getQueryParams();
if (log.isDebugEnabled()) {
log.debug(LogManager.getHeader(context, "executing_count_query", "query=" + query));
}
TableRowIterator tri = null;
try {
// now run the query
tri = DatabaseManager.query(context, query, params);
if (tri.hasNext()) {
TableRow row = tri.next();
return (int) row.getLongColumn("num");
} else {
return 0;
}
} catch (SQLException e) {
log.error("caught exception: ", e);
throw new BrowseException(e);
} finally {
if (tri != null) {
tri.close();
}
}
}
/**
* Perform a database query to obtain the string array of values corresponding to the passed
* parameters. This is only really called from <code>
* getMetadata(schema, element, qualifier, lang);
* </code> which will obtain the value from cache if available first.
*
* @param schema
* @param element
* @param qualifier
* @param lang
*/
@Override
public List<DCValue> getMetadata(
Item item, String schema, String element, String qualifier, String lang) {
List<DCValue> metadata = new ArrayList<DCValue>();
try {
TableRowIterator tri;
if (qualifier == null) {
Object[] params = {item.getID(), element, schema};
tri = DatabaseManager.query(context, getByMetadataElement, params);
} else if (Item.ANY.equals(qualifier)) {
Object[] params = {item.getID(), element, schema};
tri = DatabaseManager.query(context, getByMetadataAnyQualifier, params);
} else {
Object[] params = {item.getID(), element, qualifier, schema};
tri = DatabaseManager.query(context, getByMetadata, params);
}
while (tri.hasNext()) {
TableRow tr = tri.next();
DCValue dcv = new DCValue();
dcv.schema = schema;
dcv.element = element;
dcv.qualifier = qualifier;
dcv.language = lang;
dcv.value = tr.getStringColumn("text_value");
metadata.add(dcv);
}
} catch (SQLException sqle) {
throw new RuntimeException(sqle);
}
return metadata;
}
/**
* Returns the total number of groups returned by a specific query, without the overhead of
* creating the Group objects to store the results.
*
* @param context DSpace context
* @param query The search string
* @return the number of groups matching the query
*/
public static int searchResultCount(Context context, String query) throws SQLException {
String params = "%" + query.toLowerCase() + "%";
String dbquery =
"SELECT count(*) as gcount FROM epersongroup WHERE LOWER(name) LIKE LOWER(?) OR eperson_group_id = ? ";
// When checking against the eperson-id, make sure the query can be made into a number
Integer int_param;
try {
int_param = Integer.valueOf(query);
} catch (NumberFormatException e) {
int_param = Integer.valueOf(-1);
}
// Get all the epeople that match the query
TableRow row = DatabaseManager.querySingle(context, dbquery, new Object[] {params, int_param});
// use getIntColumn for Oracle count data
Long count;
if ("oracle".equals(ConfigurationManager.getProperty("db.name"))) {
count = Long.valueOf(row.getIntColumn("gcount"));
} else // getLongColumn works for postgres
{
count = Long.valueOf(row.getLongColumn("gcount"));
}
return count.intValue();
}
// load caches if necessary
private static synchronized void initCache(Context context) throws SQLException {
if (!isCacheInitialized()) {
Map<Integer, MetadataField> new_id2field = new HashMap<Integer, MetadataField>();
log.info("Loading MetadataField elements into cache.");
// Grab rows from DB
TableRowIterator tri =
DatabaseManager.queryTable(
context, "MetadataFieldRegistry", "SELECT * from MetadataFieldRegistry");
try {
while (tri.hasNext()) {
TableRow row = tri.next();
int fieldID = row.getIntColumn("metadata_field_id");
new_id2field.put(Integer.valueOf(fieldID), new MetadataField(row));
}
} finally {
// close the TableRowIterator to free up resources
if (tri != null) {
tri.close();
}
}
id2field = new_id2field;
}
}
/**
* 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);
}
/**
* Returns the ID of the item as opposed to the item itself when we are iterating over the
* TableRow array.
*
* @return the item id, or -1 if none
*/
private int nextByRowID() throws SQLException {
if (itemRows.hasNext()) {
TableRow row = itemRows.next();
return row.getIntColumn("item_id");
} else {
return -1;
}
}
/**
* set owner of WorkflowItem
*
* @param ep owner
*/
public void setOwner(EPerson ep) {
owner = ep;
if (ep == null) {
wfRow.setColumnNull("owner");
} else {
wfRow.setColumn("owner", ep.getID());
}
}
public static boolean estaFinalizado(Context context, String token) throws SQLException {
TableRow qResult =
DatabaseManager.querySingle(
context, "SELECT revision_id FROM revision_token WHERE token = ?", token);
if (qResult != null && qResult.getStringColumn("revision_id") != null) {
return true;
}
return false;
}
public void flagAsAggregated(Context context) throws SQLException {
String sql = "select today_date from stats.z_today_date";
TableRow row = DatabaseManager.querySingle(context, sql);
Date currentDate = row.getDateColumn("today_date");
sql = "update " + table + " " + "set aggregated=true " + "where aggregated=false and date < ?";
DatabaseManager.updateQuery(context, sql, new java.sql.Date(currentDate.getTime()));
}
private void populateTableRowFromEPerson(EPerson eperson, TableRow row) {
for (EPersonMetadataField f : EPersonMetadataField.values()) {
row.setColumn(f.toString(), eperson.getMetadata(f));
}
row.setColumn("can_log_in", eperson.canLogIn());
row.setColumn("require_certificate", eperson.getRequireCertificate());
row.setColumn("self_registered", eperson.getSelfRegistered());
}
/**
* Constructor to load the object from the database.
*
* @param row database row from which to populate object.
*/
public MetadataField(TableRow row) {
if (row != null) {
this.fieldID = row.getIntColumn("metadata_field_id");
this.schemaID = row.getIntColumn("metadata_schema_id");
this.element = row.getStringColumn("element");
this.qualifier = row.getStringColumn("qualifier");
this.scopeNote = row.getStringColumn("scope_note");
this.row = row;
}
}
private List<Collection> returnAsList(TableRowIterator tri) throws SQLException {
List<Collection> collections = new ArrayList<Collection>();
for (TableRow row : tri.toList()) {
int id = row.getIntColumn("collection_id");
collections.add(retrieve(id));
}
return collections;
}
private List<Item> returnAsList(TableRowIterator tri) throws SQLException {
List<Item> items = new ArrayList<Item>();
for (TableRow row : tri.toList()) {
int id = row.getIntColumn("item_id");
items.add(retrieve(id));
}
return items;
}
// creates workflow tasklist entries for a workflow
// for all the given EPeople
private static void createTasks(Context c, WorkflowItem wi, EPerson[] epa) throws SQLException {
// create a tasklist entry for each eperson
for (int i = 0; i < epa.length; i++) {
// can we get away without creating a tasklistitem class?
// do we want to?
TableRow tr = DatabaseManager.create(c, "tasklistitem");
tr.setColumn("eperson_id", epa[i].getID());
tr.setColumn("workflow_id", wi.getID());
DatabaseManager.update(c, tr);
}
}
public static void updateWorkspaceID(Context context, String token, String workspaceID)
throws SQLException, AuthorizeException {
TableRow qResult =
DatabaseManager.querySingle(context, "SELECT * FROM revision_token WHERE token = ?", token);
qResult.setTable("revision_token");
if (qResult != null) {
RevisionToken rt = new RevisionToken(qResult);
rt.setWorkspaceId(workspaceID);
rt.update(context);
}
}
public static boolean IsInstitution(Context context, String ip) throws SQLException {
String sql = "select ip_range from stats.ip_institution";
TableRowIterator iterator = DatabaseManager.query(context, sql);
while (iterator.hasNext()) {
TableRow row = iterator.next();
String range = row.getStringColumn("ip_range");
if (ip.indexOf(range) == 0) return true;
}
return false;
}
private void populateEPersonFromTableRow(EPerson eperson, TableRow row) {
UUID uuid = UUID.fromString(row.getStringColumn("uuid"));
for (EPersonMetadataField f : EPersonMetadataField.values()) {
eperson.setMetadata(f, row.getStringColumn(f.toString()));
}
eperson.setIdentifier(new ObjectIdentifier(uuid));
eperson.setCanLogIn(row.getBooleanColumn("can_log_in"));
eperson.setRequireCertificate(row.getBooleanColumn("require_certificate"));
eperson.setSelfRegistered(row.getBooleanColumn("self_registered"));
}
/**
* 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));
}
/**
* Finds all groups in the site
*
* @param context DSpace context
* @param sortField field to sort by -- Group.ID or Group.NAME
* @return array of all groups in the site
*/
public static Group[] findAll(Context context, int sortField) throws SQLException {
String s;
switch (sortField) {
case ID:
s = "eperson_group_id";
break;
case NAME:
s = "name";
break;
default:
s = "name";
}
// NOTE: The use of 's' in the order by clause can not cause an SQL
// injection because the string is derived from constant values above.
TableRowIterator rows =
DatabaseManager.queryTable(
context, "epersongroup", "SELECT * FROM epersongroup ORDER BY " + s);
try {
List<TableRow> gRows = rows.toList();
Group[] groups = new Group[gRows.size()];
for (int i = 0; i < gRows.size(); i++) {
TableRow row = gRows.get(i);
// First check the cache
Group fromCache =
(Group) context.fromCache(Group.class, row.getIntColumn("eperson_group_id"));
if (fromCache != null) {
groups[i] = fromCache;
} else {
groups[i] = new Group(context, row);
}
}
return groups;
} finally {
if (rows != null) {
rows.close();
}
}
}
