org.sakaiproject.util.storage
Class BaseDbFlatStorage

java.lang.Object
  extended byorg.sakaiproject.util.storage.BaseDbFlatStorage
Direct Known Subclasses:
DbAliasService.DbStorage, DbAuthzGroupService.DbStorage, DbSiteService.DbStorage, DbUserService.DbStorage

public class BaseDbFlatStorage
extends Object

BaseDbFlatStorage is a class that stores Resources (of some type) in a database, provides (optional) locked access, and generally implements a services "storage" class. The service's storage class can extend this to provide covers to turn Resource and Edit into something more type specific to the service.

Note: the methods here are all "id" based, with the following assumptions: - just the Resource Id field is enough to distinguish one Resource from another - a resource's reference is based on no more than the resource id - a resource's id cannot change. In order to handle Unicode characters properly, the SQL statements executed by this class should not embed Unicode characters into the SQL statement text; rather, Unicode values should be inserted as fields in a PreparedStatement. Databases handle Unicode better in fields.

Author:
Sakai Software Development Team

Field Summary
protected  boolean m_caseInsensitive
          If set, we treat reasource ids as case insensitive.
protected  boolean m_locking
          If false, we are not doing any locking, else we are.
protected  Hashtable m_locks
          Locks (if used), keyed by reference, holding Edits.
protected  boolean m_locksAreInTable
          If true, we do our locks in the remote database using a separate locking table, otherwise we do them in the class.
protected  SqlReader m_reader
          SqlReader to use when reading the record.
protected  String m_resourcePropertyTableName
          Table name for the resource properties.
protected  String m_resourceTableDbidField
          The extra db field for an integer 'db' id - auto-written on insert only.
protected  String m_resourceTableIdField
          The field in the resource table that holds the resource id.
protected  String[] m_resourceTableInsertFields
          The full set of fields in the table for insert - just field (not counting a dbid field).
protected  String[] m_resourceTableInsertValues
          The full set of value expressions for an insert - either null or ?
protected  String m_resourceTableName
          Table name for resource records.
protected  String[] m_resourceTableReadFields
          The full set of fields in the table to read.
protected  String m_resourceTableSortField1
          The field in the resource table that is used for sorting (first sort).
protected  String m_resourceTableSortField2
          The field in the resource table that is used for sorting (second sort).
protected  String[] m_resourceTableUpdateFields
          The full set of fields in the table for update - can be field name only, or field=xxx expression.
protected  SqlService m_sql
          Injected (by constructor) SqlService.
 
Constructor Summary
BaseDbFlatStorage(String resourceTableName, String resourceTableIdField, String[] resourceTableFields, String propertyTableName, boolean locksInTable, SqlReader reader, SqlService sqlService)
          Construct.
 
Method Summary
 void cancelResource(Edit edit)
          Cancel the changes and release the lock.
protected  String caseId(String id)
          Fix the case of resource ids to support case insensitive ids if enabled
 boolean checkResource(String id)
          Check if a Resource by this id exists.
 void close()
          Close.
 void commitResource(Connection conn, Edit edit, Object[] fields, ResourceProperties props, Object key)
          Commit the changes and release the lock - optionally in a transaction.
 void commitResource(Edit edit, Object[] fields, ResourceProperties props)
          Commit the changes and release the lock.
 int countAllResources()
           
 int countSelectedResources(String where, Object[] values)
          Count all Resources matching a SQL where clause.
 int countSelectedResources(String where, Object[] values, String join)
          Count all Resources matching a SQL where clause.
protected  void deleteProperties(Connection conn, Entity r, Object key)
          Remove all properties for this resource from the db.
 Edit editResource(Connection conn, String id)
          Get a lock on the Resource with this id, or null if a lock cannot be gotten.
 Edit editResource(String id)
          Get a lock on the Resource with this id, or null if a lock cannot be gotten.
protected  String fieldList(String[] fields, String dbidField)
          Form a string of field, field, field - one for each item in the fields array.
 List getAllResources()
           
 List getAllResources(int first, int last)
           
 Entity getResource(Connection conn, String id)
          Get the Resource with this id, or null if not found.
 Entity getResource(String id)
          Get the Resource with this id, or null if not found.
 List getSelectedResources(String where, Object[] values)
          Get all Resources matching a SQL where clause, with sorting
 List getSelectedResources(String where, Object[] values, int first, int last)
          Get all Resources matching a SQL where clause.
 List getSelectedResources(String where, String order, Object[] values)
          Get all Resources matching a SQL where clause, with sorting and ordering
 List getSelectedResources(String where, String order, Object[] values, int first, int last)
          Get all Resources matching a SQL where clause.
 List getSelectedResources(String where, String order, Object[] values, int first, int last, String join)
          Get all Resources matching a SQL where clause.
 List getSelectedResources(String where, String order, Object[] values, String join)
          Get all Resources matching a SQL where clause, with sorting and ordering
 boolean insertResource(String id, Object[] fields, Connection conn)
          Add a new Resource with this id - no edit is returned, no lock is held.
 void open()
          Open and be ready to read / write.
 Edit putResource(Connection conn, String id, Object[] fields)
          putResource with optional connection to use.
 Edit putResource(String id, Object[] fields)
          Add a new Resource with this id.
protected  String qualifyField(String field, String table)
          Qualify the fiel with the table name, if it's a field.
 void readProperties(Connection conn, Entity r, ResourcePropertiesEdit p)
          Read in properties from the database - when the properties and the main table are related by the id
 void readProperties(Connection conn, Integer dbid, ResourcePropertiesEdit p)
          Read in properties from the database - when the properties and the main table are related by the dbid
 void readProperties(Connection conn, String table, String idField, Object id, Properties p)
          Read in properties from the database.
 void readProperties(Connection conn, String table, String idField, Object id, ResourcePropertiesEdit p)
          Read in properties from the database.
 void readProperties(Entity r, ResourcePropertiesEdit p)
          Read in properties from the database - when the properties and the main table are related by the id
 void readProperties(Integer dbid, ResourcePropertiesEdit p)
          Read in properties from the database - when the properties and the main table are related by the dbid
 void removeResource(Connection conn, Edit edit, Object key)
          Remove this (locked) Resource.
 void removeResource(Edit edit)
          Remove this (locked) Resource.
protected  void setCaseInsensitivity(boolean setting)
          Enable / disable case insensitive ids.
 void setDbidField(String dbidField)
          Set a field that will be read after the field list, and
 void setLocking(boolean value)
          Set if we are doing locking or not.
 void setSortField(String sortField1, String sortField2)
          Set the sort field to be something perhaps other than the default of the id field.
 void setWriteFields(String[] updateFields, String[] insertFields, String[] insertValues)
          Establish a different set of fields for inserts and updated.
protected  Object[] updateFields(Object[] fields)
          For update, we don't want to include the first, primary key, field, so strip it off
protected  String updateSet(String[] fields)
          Form a string of n name=?
protected  String valuesParams(String[] fields, String dbidField)
          Form a string of n question marks with commas, for sql value statements, one for each item in the values array, or an empty string if null.
 void writeProperties(Connection conn, Entity r, ResourceProperties props)
          Replace any properties for this resource with the resource's current set of properties.
 void writeProperties(Connection conn, Entity r, ResourceProperties props, Object key)
          Replace any properties for this resource with the resource's current set of properties.
 void writeProperties(Connection conn, String table, String idField, Object id, String extraIdField, String extraId, Properties props)
          Replace any properties for this resource with the resource's current set of properties.
 void writeProperties(Connection conn, String table, String idField, Object id, String extraIdField, String extraId, Properties props, boolean deleteFirst)
          Replace any properties for this resource with the resource's current set of properties.
 void writeProperties(Connection conn, String table, String idField, Object id, String extraIdField, String extraId, ResourceProperties props)
           
 void writeProperties(Connection conn, String table, String idField, Object id, String extraIdField, String extraId, ResourceProperties props, boolean deleteFirst)
          Replace any properties for this resource with the resource's current set of properties.
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Field Detail

m_resourceTableName

protected String m_resourceTableName
Table name for resource records.


m_resourcePropertyTableName

protected String m_resourcePropertyTableName
Table name for the resource properties.


m_resourceTableIdField

protected String m_resourceTableIdField
The field in the resource table that holds the resource id.


m_resourceTableSortField1

protected String m_resourceTableSortField1
The field in the resource table that is used for sorting (first sort).


m_resourceTableSortField2

protected String m_resourceTableSortField2
The field in the resource table that is used for sorting (second sort).


m_resourceTableReadFields

protected String[] m_resourceTableReadFields
The full set of fields in the table to read.


m_resourceTableUpdateFields

protected String[] m_resourceTableUpdateFields
The full set of fields in the table for update - can be field name only, or field=xxx expression.


m_resourceTableInsertFields

protected String[] m_resourceTableInsertFields
The full set of fields in the table for insert - just field (not counting a dbid field).


m_resourceTableInsertValues

protected String[] m_resourceTableInsertValues
The full set of value expressions for an insert - either null or ? or an expression - to match m_resourceTableInsertFields (not counting a dbid field).


m_resourceTableDbidField

protected String m_resourceTableDbidField
The extra db field for an integer 'db' id - auto-written on insert only.


m_locking

protected boolean m_locking
If false, we are not doing any locking, else we are.


m_locksAreInTable

protected boolean m_locksAreInTable
If true, we do our locks in the remote database using a separate locking table, otherwise we do them in the class.


m_locks

protected Hashtable m_locks
Locks (if used), keyed by reference, holding Edits.


m_caseInsensitive

protected boolean m_caseInsensitive
If set, we treat reasource ids as case insensitive.


m_sql

protected SqlService m_sql
Injected (by constructor) SqlService.


m_reader

protected SqlReader m_reader
SqlReader to use when reading the record.

Constructor Detail

BaseDbFlatStorage

public BaseDbFlatStorage(String resourceTableName,
                         String resourceTableIdField,
                         String[] resourceTableFields,
                         String propertyTableName,
                         boolean locksInTable,
                         SqlReader reader,
                         SqlService sqlService)
Construct.

Parameters:
resourceTableName - Table name for resources.
resourceTableIdField - The field in the resource table that holds the id.
resourceTableFields - The complete set of fields to read / write for the resource.
propertyTableName - The table name for standard property support.
locksInTable - If true, we do our locks in the remote database in a locks table, otherwise we do them here.
reader - A SqlReader which will produce Edits given fields read from the table.
sqlService - The SqlService.
Method Detail

setSortField

public void setSortField(String sortField1,
                         String sortField2)
Set the sort field to be something perhaps other than the default of the id field.

Parameters:
sortField1 - The field name to use for sorting.
sortField2 - Optional second sort field.

setDbidField

public void setDbidField(String dbidField)
Set a field that will be read after the field list, and

Parameters:
dbidField -

setWriteFields

public void setWriteFields(String[] updateFields,
                           String[] insertFields,
                           String[] insertValues)
Establish a different set of fields for inserts and updated.


setLocking

public void setLocking(boolean value)
Set if we are doing locking or not.

Parameters:
value - If true, we should do locking, else not.

open

public void open()
Open and be ready to read / write.


close

public void close()
Close.


checkResource

public boolean checkResource(String id)
Check if a Resource by this id exists.

Parameters:
id - The id.
Returns:
true if a Resource by this id exists, false if not.

getResource

public Entity getResource(String id)
Get the Resource with this id, or null if not found.

Parameters:
id - The id.
Returns:
The Resource with this id, or null if not found.

getResource

public Entity getResource(Connection conn,
                          String id)
Get the Resource with this id, or null if not found.

Parameters:
id - The id.
Returns:
The Resource with this id, or null if not found.

getAllResources

public List getAllResources()

countAllResources

public int countAllResources()

getAllResources

public List getAllResources(int first,
                            int last)

getSelectedResources

public List getSelectedResources(String where,
                                 Object[] values)
Get all Resources matching a SQL where clause, with sorting

Parameters:
where - The SQL where clause with bind variables indicated (not including the preceeding "where ").
values - The bind values
Returns:
The list of all Resources that meet the criteria.

getSelectedResources

public List getSelectedResources(String where,
                                 String order,
                                 Object[] values)
Get all Resources matching a SQL where clause, with sorting and ordering

Parameters:
where - The SQL where clause with bind variables indicated (not including the preceeding "where ").
order - the SQL order clause (not including the preceeding "order by ").
values - The bind values
Returns:
The list of all Resources that meet the criteria.

getSelectedResources

public List getSelectedResources(String where,
                                 String order,
                                 Object[] values,
                                 String join)
Get all Resources matching a SQL where clause, with sorting and ordering

Parameters:
where - The SQL where clause with bind variables indicated (not including the preceeding "where ").
order - the SQL order clause (not including the preceeding "order by ").
values - The bind values
join - a single or comma separated set of other tables to join in the from clause
Returns:
The list of all Resources that meet the criteria.

countSelectedResources

public int countSelectedResources(String where,
                                  Object[] values)
Count all Resources matching a SQL where clause.

Parameters:
where - The SQL where clause with bind variables indicated (not including the preceeding "where ").
values - The bind values
Returns:
The count of all Resources that meet the criteria.

countSelectedResources

public int countSelectedResources(String where,
                                  Object[] values,
                                  String join)
Count all Resources matching a SQL where clause.

Parameters:
where - The SQL where clause with bind variables indicated (not including the preceeding "where ").
values - The bind values
join - a single or comma separated set of other tables to join in the from clause
Returns:
The count of all Resources that meet the criteria.

getSelectedResources

public List getSelectedResources(String where,
                                 Object[] values,
                                 int first,
                                 int last)
Get all Resources matching a SQL where clause.

Parameters:
where - The SQL where clause with bind variables indicated (not including the preceeding "where ".
values - The bind values
Returns:
The list of all Resources that meet the criteria.

getSelectedResources

public List getSelectedResources(String where,
                                 String order,
                                 Object[] values,
                                 int first,
                                 int last)
Get all Resources matching a SQL where clause.

Parameters:
where - The SQL where clause with bind variables indicated (not including the preceeding "where ".
order - the SQL order clause (not including the preceeding "order by ").
values - The bind values
Returns:
The list of all Resources that meet the criteria.

getSelectedResources

public List getSelectedResources(String where,
                                 String order,
                                 Object[] values,
                                 int first,
                                 int last,
                                 String join)
Get all Resources matching a SQL where clause.

Parameters:
where - The SQL where clause with bind variables indicated (not including the preceeding "where ".
order - the SQL order clause (not including the preceeding "order by ").
values - The bind values
join - a single or comma separated set of other tables to join in the from clause
Returns:
The list of all Resources that meet the criteria.

putResource

public Edit putResource(String id,
                        Object[] fields)
Add a new Resource with this id.

Parameters:
id - The id.
fields - The fields to write.
Returns:
The locked Resource object with this id, or null if the id is in use.

putResource

public Edit putResource(Connection conn,
                        String id,
                        Object[] fields)
putResource with optional connection to use.

Parameters:
conn - The optional database connection to use.
id - The id.
fields - The fields to write.
Returns:
The locked Resource object with this id, or null if the id is in use.

insertResource

public boolean insertResource(String id,
                              Object[] fields,
                              Connection conn)
Add a new Resource with this id - no edit is returned, no lock is held.

Parameters:
id - The id.
fields - The fields to write.
Returns:
True if successful, false if not.

editResource

public Edit editResource(String id)
Get a lock on the Resource with this id, or null if a lock cannot be gotten.

Parameters:
id - The user id.
Returns:
The locked Resource with this id, or null if this records cannot be locked.

editResource

public Edit editResource(Connection conn,
                         String id)
Get a lock on the Resource with this id, or null if a lock cannot be gotten.

Parameters:
conn - The optional database connection to use.
id - The user id.
Returns:
The locked Resource with this id, or null if this records cannot be locked.

commitResource

public void commitResource(Edit edit,
                           Object[] fields,
                           ResourceProperties props)
Commit the changes and release the lock.

Parameters:
edit - The Edit to commit.
fields - The set of fields to write to the db, plus the id field as it is to be written again at the end.

commitResource

public void commitResource(Connection conn,
                           Edit edit,
                           Object[] fields,
                           ResourceProperties props,
                           Object key)
Commit the changes and release the lock - optionally in a transaction.

Parameters:
conn - The Db Connection of the transaction (optional).
edit - The Edit to commit.
fields - The set of fields to write to the db, plus the id field as it is to be written again at the end.
key - The object key used to relate to the properties - if null, we use the object id to relate.

cancelResource

public void cancelResource(Edit edit)
Cancel the changes and release the lock.


removeResource

public void removeResource(Edit edit)
Remove this (locked) Resource.


removeResource

public void removeResource(Connection conn,
                           Edit edit,
                           Object key)
Remove this (locked) Resource.

Parameters:
conn - Optional db connection to use.
edit - The Edit to remove.
key - The key to relate resource to properties, of if null, id is assumed.

readProperties

public void readProperties(Entity r,
                           ResourcePropertiesEdit p)
Read in properties from the database - when the properties and the main table are related by the id

Parameters:
r - The resource for which properties are to be read.
p - The properties object to fill.

readProperties

public void readProperties(Integer dbid,
                           ResourcePropertiesEdit p)
Read in properties from the database - when the properties and the main table are related by the dbid

Parameters:
p - The properties object to fill.

readProperties

public void readProperties(Connection conn,
                           Entity r,
                           ResourcePropertiesEdit p)
Read in properties from the database - when the properties and the main table are related by the id

Parameters:
r - The resource for which properties are to be read.
p - The properties object to fill.

readProperties

public void readProperties(Connection conn,
                           Integer dbid,
                           ResourcePropertiesEdit p)
Read in properties from the database - when the properties and the main table are related by the dbid

Parameters:
p - The properties object to fill.

readProperties

public void readProperties(Connection conn,
                           String table,
                           String idField,
                           Object id,
                           ResourcePropertiesEdit p)
Read in properties from the database.


readProperties

public void readProperties(Connection conn,
                           String table,
                           String idField,
                           Object id,
                           Properties p)
Read in properties from the database.


writeProperties

public void writeProperties(Connection conn,
                            Entity r,
                            ResourceProperties props)
Replace any properties for this resource with the resource's current set of properties.

Parameters:
conn - optional database connection to use.
r - The resource for which properties are to be written.
props - The properties to write.

writeProperties

public void writeProperties(Connection conn,
                            Entity r,
                            ResourceProperties props,
                            Object key)
Replace any properties for this resource with the resource's current set of properties.

Parameters:
conn - optional database connection to use.
r - The resource for which properties are to be written.
props - The properties to write.
key - The key used to relate the props to the resource.

writeProperties

public void writeProperties(Connection conn,
                            String table,
                            String idField,
                            Object id,
                            String extraIdField,
                            String extraId,
                            ResourceProperties props)

writeProperties

public void writeProperties(Connection conn,
                            String table,
                            String idField,
                            Object id,
                            String extraIdField,
                            String extraId,
                            ResourceProperties props,
                            boolean deleteFirst)
Replace any properties for this resource with the resource's current set of properties.


writeProperties

public void writeProperties(Connection conn,
                            String table,
                            String idField,
                            Object id,
                            String extraIdField,
                            String extraId,
                            Properties props)
Replace any properties for this resource with the resource's current set of properties.


writeProperties

public void writeProperties(Connection conn,
                            String table,
                            String idField,
                            Object id,
                            String extraIdField,
                            String extraId,
                            Properties props,
                            boolean deleteFirst)
Replace any properties for this resource with the resource's current set of properties.


deleteProperties

protected void deleteProperties(Connection conn,
                                Entity r,
                                Object key)
Remove all properties for this resource from the db.

Parameters:
conn - Optional db connection to use.
r - The resource for which properties are to be deleted.

valuesParams

protected String valuesParams(String[] fields,
                              String dbidField)
Form a string of n question marks with commas, for sql value statements, one for each item in the values array, or an empty string if null. If the fields are "(...)" values, use these instead of ?.

Returns:
A sql statement fragment for the values part of an insert, one for each value in the array.

updateSet

protected String updateSet(String[] fields)
Form a string of n name=?, for sql update set statements, one for each item in the values array, or an empty string if null.

Returns:
A sql statement fragment for the values part of an insert, one for each value in the array.

updateFields

protected Object[] updateFields(Object[] fields)
For update, we don't want to include the first, primary key, field, so strip it off

Parameters:
fields - The full set of fields
Returns:
The fields with the first removed

fieldList

protected String fieldList(String[] fields,
                           String dbidField)
Form a string of field, field, field - one for each item in the fields array.

Parameters:
fields - The field names.
Returns:
A string of field, field, field - one for each item in the fields array.

qualifyField

protected String qualifyField(String field,
                              String table)
Qualify the fiel with the table name, if it's a field.

Parameters:
field - The field.
table - The table name.
Returns:
The field name qualified with the table name.

caseId

protected String caseId(String id)
Fix the case of resource ids to support case insensitive ids if enabled

Returns:
The id, case modified as needed.

setCaseInsensitivity

protected void setCaseInsensitivity(boolean setting)
Enable / disable case insensitive ids.

Parameters:
setting - true to set case insensitivity, false to set case sensitivity.