Java tutorial
/********************************************************************************** * $URL: https://source.sakaiproject.org/svn/kernel/trunk/kernel-util/src/main/java/org/sakaiproject/util/BaseDbDoubleStorage.java $ * $Id: BaseDbDoubleStorage.java 66393 2009-09-10 08:19:24Z david.horwitz@uct.ac.za $ *********************************************************************************** * * Copyright (c) 2005, 2006, 2007, 2008 Sakai Foundation * * Licensed under the Educational Community License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.opensource.org/licenses/ECL-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. * **********************************************************************************/ package org.sakaiproject.util; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Hashtable; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Stack; import java.util.Vector; import org.apache.commons.lang.ArrayUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.sakaiproject.db.api.SqlReader; import org.sakaiproject.db.api.SqlReaderFinishedException; import org.sakaiproject.db.api.SqlService; import org.sakaiproject.entity.api.Edit; import org.sakaiproject.entity.api.Entity; import org.sakaiproject.entity.api.ResourceProperties; import org.sakaiproject.event.cover.UsageSessionService; import org.sakaiproject.javax.Filter; import org.sakaiproject.javax.Order; import org.sakaiproject.javax.PagingPosition; import org.sakaiproject.javax.Search; import org.sakaiproject.javax.SearchFilter; import org.sakaiproject.time.api.Time; import org.sakaiproject.time.cover.TimeService; import org.w3c.dom.Document; import org.w3c.dom.Element; /** * <p> * BaseDbDoubleStorage is a class that stores collections of Resources (of some type) in a database, <br /> * provides locked access, and generally implements a services "storage" class. The <br /> * service's storage class can extend this to provide covers to turn Resource and <br /> * Edit into something more type specific to the service. * </p> * <p> * Note: the methods here are all "id" based, with the following assumptions: * <ul> * <li>just the Resource Id field is enough to distinguish one Resource from another (or, for resource, the container's id and the resource id).</li> * <li>a resource's reference is based on no more than the resource id, for containers </li> * <li>and no more than resource and container id for resources</li> * <li>a resource's id and container id cannot change</li> * </ul> * </p> * <br /> * In order to handle Unicode characters properly, the SQL statements executed by this class should not embed Unicode characters into the SQL * statement text; <br /> * rather, Unicode values should be inserted as fields in a PreparedStatement. Databases handle Unicode better in fields. * </p> */ public class BaseDbDoubleStorage { /** Our logger. */ private static Log M_log = LogFactory.getLog(BaseDbDoubleStorage.class); /** Table name for container records. */ protected String m_containerTableName = null; /** The field in the table that holds the container id. */ protected String m_containerTableIdField = null; /** Table name for resource records. */ protected String m_resourceTableName = null; /** The field in the resource table that holds the resource id. */ protected String m_resourceTableIdField = null; /** The field in the resource table that holds the container id. */ protected String m_resourceTableContainerIdField = null; /** The additional field names in the resource table that go between the two ids and the xml. */ protected String[] m_resourceTableOtherFields = null; /** The string searchable field names in the resource table. This must be either null * (i.e. no fields) or this is assumed to be the only fields which participate in search. */ protected String[] m_resourceTableSearchFields = null; /** The field name in the resource table for ordering. */ protected String m_resourceTableOrderField = null; /** The xml tag name for the element holding each actual resource entry. */ protected String m_resourceEntryTagName = null; /** The xml tag name for the element holding each actual container entry. */ protected String m_containerEntryTagName = null; /** The field in the record that has the user id of the resource owner. */ protected String m_resourceTableOwnerField = null; /** The field in the record that has the draft indicator ('0' for no, '1' for yes). */ protected String m_resourceTableDraftField = null; /** The field in the record that has the pubview indicator ('0' for no, '1' for yes). */ protected String m_resourceTablePubViewField = null; /** If true, we do our locks in the remote database. */ protected boolean m_locksAreInDb = true; /** If true, we do our locks in the remove database using a separate locking table. */ protected boolean m_locksAreInTable = true; /** The StorageUser to callback for new Resource and Edit objects. */ protected DoubleStorageUser m_user = null; /** * Locks, keyed by reference, holding Connections (or, if locks are done locally, holding an Edit). Note: keying by reference allows botu * container and resource locks to be stored, the reference distinguishes them. */ protected Hashtable m_locks = null; /** For container, the extra field is (no longer used) NEXT_ID */ protected static final String[] M_containerExtraFields = { "NEXT_ID" }; /** Injected (by constructor) SqlService. */ protected SqlService m_sql = null; /** contains a map of the database dependent handlers. */ protected static Map<String, DoubleStorageSql> databaseBeans; /** The db handler we are using. */ protected DoubleStorageSql doubleStorageSql; public void setDatabaseBeans(Map databaseBeans) { this.databaseBeans = databaseBeans; } /** * sets which bean containing database dependent code should be used depending on the database vendor. */ public void setDoubleStorageSql(String vendor) { this.doubleStorageSql = (databaseBeans.containsKey(vendor) ? databaseBeans.get(vendor) : databaseBeans.get("default")); } // since spring is not used and this class is instatiated directly, we need to "inject" these values ourselves static { databaseBeans = new Hashtable<String, DoubleStorageSql>(); databaseBeans.put("default", new DoubleStorageSqlDefault()); databaseBeans.put("hsqldb", new DoubleStorageSqlHSql()); databaseBeans.put("mysql", new DoubleStorageSqlMySql()); databaseBeans.put("oracle", new DoubleStorageSqlOracle()); } /** * Construct. * * @param containerTableName * Table name for containers. * @param containerTableIdField * The field in the container table that holds the id. * @param resourceTableName * Table name for resources. * @param resourceTableIdField * The field in the resource table that holds the id. * @param resourceTableContainerIdField * The field in the resource table that holds the container id. * @param resourceTableOrderField * The field in the resource table that is used for ordering results. * @param resourceTableOtherFields * The other fields in the resource table (between the two id fields and the xml field). * @param locksInDb * If true, we do our locks in the remote database, otherwise we do them here. * @param containerEntryName * The xml tag name for the element holding each actual container entry. * @param resourceEntryName * The xml tag name for the element holding each actual resource entry. * @param user * The StorageUser class to call back for creation of Resource and Edit objects. * @param sqlService * The SqlService. */ public BaseDbDoubleStorage(String containerTableName, String containerTableIdField, String resourceTableName, String resourceTableIdField, String resourceTableContainerIdField, String resourceTableOrderField, String resourceTableOwnerField, String resourceTableDraftField, String resourceTablePubViewField, String[] resourceTableOtherFields, String[] resourceTableSearchFields, boolean locksInDb, String containerEntryName, String resourceEntryName, DoubleStorageUser user, SqlService sqlService) { m_containerTableName = containerTableName; m_containerTableIdField = containerTableIdField; m_resourceTableName = resourceTableName; m_resourceTableIdField = resourceTableIdField; m_resourceTableContainerIdField = resourceTableContainerIdField; m_resourceTableOrderField = resourceTableOrderField; m_resourceTableOtherFields = resourceTableOtherFields; m_resourceTableSearchFields = resourceTableSearchFields; m_locksAreInDb = locksInDb; m_containerEntryTagName = containerEntryName; m_resourceEntryTagName = resourceEntryName; m_resourceTableOwnerField = resourceTableOwnerField; m_resourceTableDraftField = resourceTableDraftField; m_resourceTablePubViewField = resourceTablePubViewField; m_user = user; m_sql = sqlService; setDoubleStorageSql(m_sql.getVendor()); } /** Backwards compatibility constructor for using DbDouble without search fields */ public BaseDbDoubleStorage(String containerTableName, String containerTableIdField, String resourceTableName, String resourceTableIdField, String resourceTableContainerIdField, String resourceTableOrderField, String resourceTableOwnerField, String resourceTableDraftField, String resourceTablePubViewField, String[] resourceTableOtherFields, // String[] resourceTableSearchFields, boolean locksInDb, String containerEntryName, String resourceEntryName, DoubleStorageUser user, SqlService sqlService) { m_containerTableName = containerTableName; m_containerTableIdField = containerTableIdField; m_resourceTableName = resourceTableName; m_resourceTableIdField = resourceTableIdField; m_resourceTableContainerIdField = resourceTableContainerIdField; m_resourceTableOrderField = resourceTableOrderField; m_resourceTableOtherFields = resourceTableOtherFields; m_resourceTableSearchFields = null; // resourceTableSearchFields; m_locksAreInDb = locksInDb; m_containerEntryTagName = containerEntryName; m_resourceEntryTagName = resourceEntryName; m_resourceTableOwnerField = resourceTableOwnerField; m_resourceTableDraftField = resourceTableDraftField; m_resourceTablePubViewField = resourceTablePubViewField; m_user = user; m_sql = sqlService; setDoubleStorageSql(m_sql.getVendor()); } /** * Open and be ready to read / write. */ public void open() { // setup for locks m_locks = new Hashtable(); } /** * Close. */ public void close() { if (!m_locks.isEmpty()) { M_log.warn("close(): locks remain!"); // %%% } m_locks.clear(); m_locks = null; } /** * Read one Container Resource from xml * * @param xml * An string containing the xml which describes the Container resource. * @return The Container Resource object created from the xml. */ protected Entity readContainer(String xml) { try { if (m_user instanceof SAXEntityReader) { SAXEntityReader sm_user = (SAXEntityReader) m_user; DefaultEntityHandler deh = sm_user.getDefaultHandler(sm_user.getServices()); StorageUtils.processString(xml, deh); return deh.getEntity(); } else { // read the xml Document doc = StorageUtils.readDocumentFromString(xml); // verify the root element Element root = doc.getDocumentElement(); if (!root.getTagName().equals(m_containerEntryTagName)) { M_log.warn("readContainer(): not = " + m_containerEntryTagName + " : " + root.getTagName()); return null; } // re-create a resource Entity entry = m_user.newContainer(root); return entry; } } catch (Exception e) { M_log.warn("readContainer(): " + e.getMessage()); M_log.info("readContainer(): ", e); return null; } } /** * Check if a Container by this id exists. * * @param ref * The container reference. * @return true if a Container by this id exists, false if not. */ public boolean checkContainer(String ref) { // just see if the record exists String sql = doubleStorageSql.getSelect1Sql(m_containerTableName, m_containerTableIdField); Object[] fields = new Object[1]; fields[0] = ref; List ids = m_sql.dbRead(sql, fields, null); return (!ids.isEmpty()); } /** * Get the Container with this id, or null if not found. * * @param ref * The container reference. * @return The Container with this id, or null if not found. */ public Entity getContainer(String ref) { Entity entry = null; // get the user from the db String sql = doubleStorageSql.getSelectXml2Sql(m_containerTableName, m_containerTableIdField); Object[] fields = new Object[1]; fields[0] = ref; List xml = m_sql.dbRead(sql, fields, null); if (!xml.isEmpty()) { // create the Resource from the db xml entry = readContainer((String) xml.get(0)); } return entry; } /** * Get all Containers. * * @return The list (Resource) of all Containers. */ public List getAllContainers() { List all = new Vector(); // read all users from the db String sql = doubleStorageSql.getSelectXml1Sql(m_containerTableName); // %%% order by... List xml = m_sql.dbRead(sql); // process all result xml into user objects if (!xml.isEmpty()) { for (int i = 0; i < xml.size(); i++) { Entity entry = readContainer((String) xml.get(i)); if (entry != null) all.add(entry); } } return all; } /** * Add a new Container with this id. * * @param ref * The container reference. * @return The locked Container object with this id, or null if the id is in use. */ public Edit putContainer(String ref) { // create one with just the id Entity entry = m_user.newContainer(ref); // form the XML and SQL for the insert Document doc = StorageUtils.createDocument(); entry.toXml(doc, new Stack()); String xml = StorageUtils.writeDocumentToString(doc); String statement = doubleStorageSql.getInsertSql(m_containerTableName, insertFields(m_containerTableIdField, null, M_containerExtraFields, "XML")); Object[] fields = new Object[2]; fields[0] = entry.getReference(); fields[1] = xml; // process the insert boolean ok = m_sql.dbWrite(statement, fields); // if this failed, assume a key conflict (i.e. id in use) if (!ok) return null; // now get a lock on the record for edit Edit edit = editContainer(ref); if (edit == null) { M_log.warn("putContainer(): didn't get a lock!"); return null; } return edit; } /** * Get a lock on the Container with this id, or null if a lock cannot be gotten. * * @param ref * The container reference. * @return The locked Container with this id, or null if this cannot be locked. */ public Edit editContainer(String ref) { Edit edit = null; if (m_locksAreInDb) { if ("oracle".equals(m_sql.getVendor())) { // read the record and get a lock on it (non blocking) String statement = doubleStorageSql.getSelectXml3Sql(m_containerTableName, m_containerTableIdField, StorageUtils.escapeSql(ref)); StringBuilder result = new StringBuilder(); Connection lock = m_sql.dbReadLock(statement, result); // for missing or already locked... if ((lock == null) || (result.length() == 0)) return null; // make first a Resource, then an Edit Entity entry = readContainer(result.toString()); edit = m_user.newContainerEdit(entry); // store the lock for this object m_locks.put(entry.getReference(), lock); } else { throw new UnsupportedOperationException( "Record locking only available when configured with Oracle database"); } } // if the locks are in a separate table in the db else if (m_locksAreInTable) { // get, and return if not found Entity entry = getContainer(ref); if (entry == null) return null; // write a lock to the lock table - if we can do it, we get the lock String statement = doubleStorageSql.getInsertSql2(); // we need session id String sessionId = UsageSessionService.getSessionId(); if (sessionId == null) { sessionId = ""; } // collect the fields Object fields[] = new Object[4]; fields[0] = m_containerTableName; fields[1] = doubleStorageSql.getRecordId(ref); fields[2] = TimeService.newTime(); fields[3] = sessionId; // add the lock - if fails, someone else has the lock boolean ok = m_sql.dbWriteFailQuiet(null, statement, fields); if (!ok) { return null; } // make the edit from the Resource edit = m_user.newContainerEdit(entry); } // otherwise, get the lock locally else { // get, and return if not found Entity entry = getContainer(ref); if (entry == null) return null; // we only sync this getting - someone may release a lock out of sync synchronized (m_locks) { // if already locked if (m_locks.containsKey(entry.getReference())) return null; // make the edit from the Resource edit = m_user.newContainerEdit(entry); // store the edit in the locks by reference m_locks.put(entry.getReference(), edit); } } return edit; } /** * Commit the changes and release the lock. * * @param user * The Edit to commit. */ public void commitContainer(Edit edit) { // form the SQL statement and the var w/ the XML Document doc = StorageUtils.createDocument(); edit.toXml(doc, new Stack()); String xml = StorageUtils.writeDocumentToString(doc); String statement = doubleStorageSql.getUpdateSql(m_containerTableName, m_containerTableIdField); Object[] fields = new Object[2]; fields[0] = xml; fields[1] = edit.getReference(); if (m_locksAreInDb) { // use this connection that is stored with the lock Connection lock = (Connection) m_locks.get(edit.getReference()); if (lock == null) { M_log.warn("commitContainer(): edit not in locks"); return; } // update, commit, release the lock's connection m_sql.dbUpdateCommit(statement, fields, null, lock); // remove the lock m_locks.remove(edit.getReference()); } else if (m_locksAreInTable) { // process the update m_sql.dbWrite(statement, fields); // remove the lock statement = doubleStorageSql.getDeleteLocksSql(); // collect the fields Object lockFields[] = new Object[2]; lockFields[0] = m_containerTableName; lockFields[1] = doubleStorageSql.getRecordId(edit.getReference()); boolean ok = m_sql.dbWrite(statement, lockFields); if (!ok) { M_log.warn("commitContainer: missing lock for table: " + lockFields[0] + " key: " + lockFields[1]); } } else { // just process the update m_sql.dbWrite(statement, fields); // remove the lock m_locks.remove(edit.getReference()); } } /** * Cancel the changes and release the lock. * * @param user * The Edit to cancel. */ public void cancelContainer(Edit edit) { if (m_locksAreInDb) { // use this connection that is stored with the lock Connection lock = (Connection) m_locks.get(edit.getReference()); if (lock == null) { M_log.warn("cancelContainer(): edit not in locks"); return; } // rollback and release the lock's connection m_sql.dbCancel(lock); // release the lock m_locks.remove(edit.getReference()); } else if (m_locksAreInTable) { // remove the lock String statement = doubleStorageSql.getDeleteLocksSql(); // collect the fields Object lockFields[] = new Object[2]; lockFields[0] = m_containerTableName; lockFields[1] = doubleStorageSql.getRecordId(edit.getReference()); boolean ok = m_sql.dbWrite(statement, lockFields); if (!ok) { M_log.warn("cancelContainer: missing lock for table: " + lockFields[0] + " key: " + lockFields[1]); } } else { // release the lock m_locks.remove(edit.getReference()); } } /** * Remove this (locked) Container. * * @param user * The Edit to remove. */ public void removeContainer(Edit edit) { // form the SQL delete statement String statement = doubleStorageSql.getDeleteSql(m_containerTableName, m_containerTableIdField); Object[] fields = new Object[1]; fields[0] = edit.getReference(); if (m_locksAreInDb) { // use this connection that is stored with the lock Connection lock = (Connection) m_locks.get(edit.getReference()); if (lock == null) { M_log.warn("removeContainer(): edit not in locks"); return; } // process the delete statement, commit, and release the lock's connection m_sql.dbUpdateCommit(statement, fields, null, lock); // release the lock m_locks.remove(edit.getReference()); } else if (m_locksAreInTable) { // process the delete statement m_sql.dbWrite(statement, fields); // remove the lock statement = doubleStorageSql.getDeleteLocksSql(); // collect the fields Object lockFields[] = new Object[2]; lockFields[0] = m_containerTableName; lockFields[1] = doubleStorageSql.getRecordId(edit.getReference()); boolean ok = m_sql.dbWrite(statement, lockFields); if (!ok) { M_log.warn("remove: missing lock for table: " + lockFields[0] + " key: " + lockFields[1]); } } else { // process the delete statement m_sql.dbWrite(statement, fields); // release the lock m_locks.remove(edit.getReference()); } } /** * Read one Resource from xml * * @param container * The container for this resource. * @param xml * An string containing the xml which describes the resource. * @return The Resource object created from the xml. */ protected Entity readResource(Entity container, String xml) { try { if (m_user instanceof SAXEntityReader) { SAXEntityReader sm_user = (SAXEntityReader) m_user; DefaultEntityHandler deh = sm_user.getDefaultHandler(sm_user.getServices()); deh.setContainer(container); StorageUtils.processString(xml, deh); return deh.getEntity(); } else { // read the xml Document doc = StorageUtils.readDocumentFromString(xml); //The resulting doc could be null if (doc == null) { M_log.warn("null xml document passed to readResource for container" + container.getId()); return null; } // verify the root element Element root = doc.getDocumentElement(); if (!root.getTagName().equals(m_resourceEntryTagName)) { M_log.warn("readResource(): not = " + m_resourceEntryTagName + " : " + root.getTagName()); return null; } // re-create a resource Entity entry = m_user.newResource(container, root); return entry; } } catch (Exception e) { M_log.warn("readResource(): " + e.getMessage()); M_log.info("readResource(): ", e); return null; } } /** * Check if a Resource by this id exists. * * @param container * The container for this resource. * @param id * The id. * @return true if a Resource by this id exists, false if not. */ public boolean checkResource(Entity container, String id) { // just see if the record exists String sql = doubleStorageSql.getSelectIdSql(m_resourceTableName, m_resourceTableIdField, m_resourceTableContainerIdField); Object[] fields = new Object[2]; fields[0] = container.getReference(); fields[1] = id; List ids = m_sql.dbRead(sql, fields, null); return (!ids.isEmpty()); } /** * Get the Resource with this id, or null if not found. * * @param container * The container for this resource. * @param id * The id. * @return The Resource with this id, or null if not found. */ public Entity getResource(Entity container, String id) { Entity entry = null; // get the user from the db String sql = doubleStorageSql.getSelectXml4Sql(m_resourceTableName, m_resourceTableIdField, m_resourceTableContainerIdField); Object[] fields = new Object[2]; fields[0] = container.getReference(); fields[1] = id; List xml = m_sql.dbRead(sql, fields, null); if (!xml.isEmpty()) { // create the Resource from the db xml entry = readResource(container, (String) xml.get(0)); } return entry; } /** * Count all Resources * @param container * The container for this resource. */ public int getCount(Entity container) { // read With or without a filter String sql = doubleStorageSql.getCountSql(m_resourceTableName, m_resourceTableContainerIdField); Object[] fields = new Object[1]; fields[0] = container.getReference(); List countList = m_sql.dbRead(sql, fields, null); if (countList.isEmpty()) return 0; Object obj = countList.get(0); String str = (String) obj; return Integer.parseInt(str); } /** * Count all Resources - This takes two approaches depending * on whether this table has search fields. If searchfields * are available we can do a SELECT COUNT WHERE. Otherwise * we retrieve the records and run the filter on each record. * @param container * The container for this resource. * @param filter * A filter object to accept / reject the searches * @param search * Search string */ public int getCount(Entity container, Filter filter) { if (filter == null) return getCount(container); // System.out.println("getCount e="+container+" sf="+filter); // If we have search fields - do a quick select count with a where clause if (m_resourceTableSearchFields != null && filter instanceof SearchFilter) { int searchFieldCount = 0; String searchString = ((SearchFilter) filter).getSearchString(); if (searchString != null && searchString.length() > 0) { String searchWhere = doubleStorageSql.getSearchWhereClause(m_resourceTableSearchFields); if (searchWhere != null && searchWhere.length() > 0) { searchFieldCount = m_resourceTableSearchFields.length; String sql = doubleStorageSql.getCountSqlWhere(m_resourceTableName, m_resourceTableContainerIdField, searchWhere); Object[] fields = new Object[1 + searchFieldCount]; fields[0] = container.getReference(); for (int i = 0; i < searchFieldCount; i++) fields[i + 1] = "%" + searchString + "%"; List countList = m_sql.dbRead(sql, fields, null); if (countList.isEmpty()) return 0; Object obj = countList.get(0); String str = (String) obj; return Integer.parseInt(str); } } } // No search fields - retrieve, filter and count String sql = doubleStorageSql.getSelectXml5Sql(m_resourceTableName, m_resourceTableContainerIdField, null, false); Object[] fields = new Object[1]; fields[0] = container.getReference(); List all = m_sql.dbRead(sql, fields, new SearchFilterReader(container, filter, null, true)); int count = all.size(); return count; } /** * Get all Resources. * * @param container * The container for this resource. * @return The list (Resource) of all Resources. */ public List getAllResources(Entity container) { return getAllResources(container, null, null, true, null); } /** * Get all Resources. * * @param container * The container for this resource. * @param filter * conditional for select statement * @return The list (Resource) of all Resources. */ public List getAllResources(Entity container, Filter filter) { return getAllResources(container, filter, null, true, null); } /** * Get all Resources. * * @param container * The container for this resource. * @param sqlFilter * conditional for select statement * @return The list (Resource) of all Resources. */ public List getAllResources(Entity container, String sqlFilter) { return getAllResources(container, null, sqlFilter, true, null); } /** * Deal with the fact that we can get a PagingPosition from a query filter * or directly as a parameter. In time remove the option to use * PagingPosition. */ // TODO: Remove all methods with PagingPostition and switch to Filter private PagingPosition fixPagingPosition(Filter filter, PagingPosition pos) { if (filter == null) return pos; if (pos != null) { M_log.warn("The use of methods with PagingPosition should switch to using Search (SAK-13584) - Chuck"); return pos; } if (filter instanceof Search) { Search q = (Search) filter; if (q.getLimit() > 0 && q.getLimit() >= q.getStart()) { return new PagingPosition((int) q.getStart(), (int) q.getLimit()); } } return null; } /** * Get all Resources. * * @param container * The container for this resource. * @param softFilter * an optional software filter * @param sqlFilter * an optional conditional for select statement * @param asc * true means ascending * @param pager * an optional range of elements to return inclusive * @return The list (Resource) of all Resources. */ public List getAllResources(Entity container, Filter softFilter, String sqlFilter, boolean asc, PagingPosition pager) { return getAllResources(container, softFilter, sqlFilter, asc, pager, null); } /** * Get all Resources. * * @param container * The container for this resource. * @param softFilter * an optional software filter * @param sqlFilter * an optional conditional for select statement * @param asc * true means ascending * @param pager * an optional range of elements to return inclusive * @param bindVariables * an optional list of bind variables * @return The list (Resource) of all Resources. */ public List getAllResources(Entity container, Filter softFilter, String sqlFilter, boolean asc, PagingPosition pager, List<Object> bindVariables) { pager = fixPagingPosition(softFilter, pager); // System.out.println("getAllResources e="+container+" sf="+softFilter+" sqlf="+sqlFilter+" asc="+asc+" pag="+pager); // Get the orders and get the ORDER BY clause Order[] orders = null; if (softFilter instanceof Search) { orders = ((Search) softFilter).getOrders(); } String orderString = doubleStorageSql.getOrderClause(orders, m_resourceTableOrderField, asc); // Turn the search string into a WHERE clause if we can int searchFieldCount = 0; String searchString = null; if (m_resourceTableSearchFields != null && softFilter instanceof SearchFilter) { searchString = ((SearchFilter) softFilter).getSearchString(); if (searchString != null && searchString.length() > 0) { String searchWhere = doubleStorageSql.getSearchWhereClause(m_resourceTableSearchFields); if (searchWhere != null && searchWhere.length() > 0) { if (sqlFilter == null) { sqlFilter = searchWhere; } else { sqlFilter = sqlFilter + " and " + searchWhere; } searchFieldCount = m_resourceTableSearchFields.length; } } } String sql = doubleStorageSql.getSelectXml5filterSql(m_resourceTableName, m_resourceTableContainerIdField, orderString, sqlFilter); // Add Paging to the Search if requested // TODO: Someday make this think Filter and emulate PagingPosition boolean pagedInSql = false; if (pager != null) { String limitedSql = doubleStorageSql.addLimitToQuery(sql, pager.getFirst() - 1, pager.getLast() - 1); if (limitedSql != null) { pagedInSql = true; sql = limitedSql; } else { // We don't subtract 1 because TOP is a count, not zero based like LIMIT String topSql = doubleStorageSql.addTopToQuery(sql, pager.getLast()); if (topSql != null) { sql = topSql; } } } Object[] fields = new Object[1 + searchFieldCount]; fields[0] = container.getReference(); for (int i = 0; i < searchFieldCount; i++) fields[i + 1] = "%" + searchString + "%"; if (bindVariables != null && bindVariables.size() > 0) { // Add the bind variables to the fields to substitute in the prepared statement fields = ArrayUtils.addAll(fields, bindVariables.toArray(new Object[fields.length])); } // System.out.println("getAllResources="+sql); // If we are paged in SQL - then do not pass in the pager List all = m_sql.dbRead(sql, fields, new SearchFilterReader(container, softFilter, pagedInSql ? null : pager, false)); return all; } /** matchXml - Perform an optional pre-de-serialize match if desired * * This is just a dummy implementation - this wil be overridden in the * class that extends this class if a particular storage wants to take * advantage of this feature. * * Return value: * -1 indicates - definite "no" * 0 indicates - maybe - continue and parse the Xml * 1 indicates - "yes" - we know in this rouinte this is a match */ public int matchXml(String xml, String search) { return 0; } public class SearchFilterReader implements SqlReader { private Filter m_filter; private String m_search = null; private PagingPosition m_pager; private Entity m_container; private boolean m_doCount = false; private int count = 0; // If we are only counting - return a tiny thing - not a big thing private final Integer intValue = 1; public SearchFilterReader(Entity container, Filter filter, PagingPosition pager, boolean doCount) { m_container = container; m_filter = filter; if (filter instanceof SearchFilter) m_search = ((SearchFilter) filter).getSearchString(); m_pager = pager; m_doCount = doCount; } public Object readSqlResultRecord(ResultSet result) throws SqlReaderFinishedException { try { String theXml = result.getString(1); if (m_pager != null && count > m_pager.getLast()) { throw new SqlReaderFinishedException(); } int iTest = 0; // Don't know if we have a match if (m_search != null) { iTest = matchXml(theXml, m_search); } // If it is clearly rejected from pre-parse match if (iTest == -1) return null; // If it is a match and we are just counting - no parsing // needed if (iTest == 1 && m_doCount) return intValue; // If it is known to be accepted (1) or unsure (0), // parse the Xml and continue Entity entry = readResource(m_container, theXml); if (entry == null) return null; // If there is no indication from matchXml if (iTest == 0 && m_search != null) { if (!m_filter.accept(entry)) return null; } count++; if (m_pager != null && count < m_pager.getFirst()) return null; if (m_pager != null && count > m_pager.getLast()) { throw new SqlReaderFinishedException(); } if (m_doCount) return intValue; return entry; } catch (SQLException ignore) { return null; } } } /** * Add a new Resource with this id. * * @param container * The container for this resource. * @param id * The id. * @param others * Other fields for the newResource call * @return The locked Resource object with this id, or null if the id is in use. */ public Edit putResource(Entity container, String id, Object[] others) { // create one with just the id, and perhaps some other fields, too Entity entry = m_user.newResource(container, id, others); // form the XML and SQL for the insert Document doc = StorageUtils.createDocument(); entry.toXml(doc, new Stack()); String xml = StorageUtils.writeDocumentToString(doc); String statement = doubleStorageSql.getInsertSql3(m_resourceTableName, insertFields(m_containerTableIdField, m_resourceTableIdField, m_resourceTableOtherFields, "XML"), valuesParams(m_resourceTableOtherFields)); Object[] flds = m_user.storageFields(entry); if (flds == null) flds = new Object[0]; Object[] fields = new Object[flds.length + 3]; System.arraycopy(flds, 0, fields, 2, flds.length); fields[0] = container.getReference(); fields[1] = entry.getId(); fields[fields.length - 1] = xml; // process the insert boolean ok = m_sql.dbWrite(statement, fields); // if this failed, assume a key conflict (i.e. id in use) if (!ok) return null; // now get a lock on the record for edit Edit edit = editResource(container, id); if (edit == null) { M_log.warn("putResource(): didn't get a lock!"); return null; } return edit; } /** * Get a lock on the Resource with this id, or null if a lock cannot be gotten. * * @param container * The container for this resource. * @param id * The user id. * @return The locked Resource with this id, or null if this records cannot be locked. */ public Edit editResource(Entity container, String id) { Edit edit = null; if (m_locksAreInDb) { if ("oracle".equals(m_sql.getVendor())) { // read the record and get a lock on it (non blocking) String statement = doubleStorageSql.getSelectXml6Sql(m_resourceTableName, m_resourceTableIdField, m_resourceTableContainerIdField, StorageUtils.escapeSql(id), StorageUtils.escapeSql(container.getReference())); StringBuilder result = new StringBuilder(); Connection lock = m_sql.dbReadLock(statement, result); // for missing or already locked... if ((lock == null) || (result.length() == 0)) return null; // make first a Resource, then an Edit Entity entry = readResource(container, result.toString()); edit = m_user.newResourceEdit(container, entry); // store the lock for this object m_locks.put(entry.getReference(), lock); } else { throw new UnsupportedOperationException( "Record locking only available when configured with Oracle database"); } } // if the locks are in a separate table in the db else if (m_locksAreInTable) { // get the entry, and check for existence Entity entry = getResource(container, id); if (entry == null) return null; // write a lock to the lock table - if we can do it, we get the lock String statement = doubleStorageSql.getInsertSql2(); // we need session id and user id String sessionId = UsageSessionService.getSessionId(); if (sessionId == null) { sessionId = ""; } // collect the fields Object fields[] = new Object[4]; fields[0] = m_resourceTableName; fields[1] = doubleStorageSql.getRecordId(container.getReference() + "/" + id); fields[2] = TimeService.newTime(); fields[3] = sessionId; // add the lock - if fails, someone else has the lock boolean ok = m_sql.dbWriteFailQuiet(null, statement, fields); if (!ok) { return null; } // make the edit from the Resource edit = m_user.newResourceEdit(container, entry); } // otherwise, get the lock locally else { // get the entry, and check for existence Entity entry = getResource(container, id); if (entry == null) return null; // we only sync this getting - someone may release a lock out of sync synchronized (m_locks) { // if already locked if (m_locks.containsKey(entry.getReference())) return null; // make the edit from the Resource edit = m_user.newResourceEdit(container, entry); // store the edit in the locks by reference m_locks.put(entry.getReference(), edit); } } return edit; } /** * Commit the changes and release the lock. * * @param container * The container for this resource. * @param user * The Edit to commit. */ public void commitResource(Entity container, Edit edit) { // form the SQL statement and the var w/ the XML Document doc = StorageUtils.createDocument(); edit.toXml(doc, new Stack()); String xml = StorageUtils.writeDocumentToString(doc); String statement = doubleStorageSql.getUpdate2Sql(m_resourceTableName, m_resourceTableIdField, m_resourceTableContainerIdField, updateSet(m_resourceTableOtherFields)); Object[] flds = m_user.storageFields(edit); if (flds == null) flds = new Object[0]; Object[] fields = new Object[flds.length + 3]; System.arraycopy(flds, 0, fields, 0, flds.length); fields[fields.length - 3] = xml; fields[fields.length - 2] = container.getReference(); fields[fields.length - 1] = edit.getId(); if (m_locksAreInDb) { // use this connection that is stored with the lock Connection lock = (Connection) m_locks.get(edit.getReference()); if (lock == null) { M_log.warn("commitResource(): edit not in locks"); return; } // update, commit, release the lock's connection m_sql.dbUpdateCommit(statement, fields, null, lock); // remove the lock m_locks.remove(edit.getReference()); } else if (m_locksAreInTable) { // process the update m_sql.dbWrite(statement, fields); // remove the lock statement = doubleStorageSql.getDeleteLocksSql(); // collect the fields Object lockFields[] = new Object[2]; lockFields[0] = m_resourceTableName; lockFields[1] = doubleStorageSql.getRecordId(container.getReference() + "/" + edit.getId()); boolean ok = m_sql.dbWrite(statement, lockFields); if (!ok) { M_log.warn("commitResource: missing lock for table: " + lockFields[0] + " key: " + lockFields[1]); } } else { // just process the update m_sql.dbWrite(statement, fields); // remove the lock m_locks.remove(edit.getReference()); } } /** * Cancel the changes and release the lock. * * @param container * The container for this resource. * @param user * The Edit to cancel. */ public void cancelResource(Entity container, Edit edit) { if (m_locksAreInDb) { // use this connection that is stored with the lock Connection lock = (Connection) m_locks.get(edit.getReference()); if (lock == null) { M_log.warn("cancelResource(): edit not in locks"); return; } // rollback and release the lock's connection m_sql.dbCancel(lock); // release the lock m_locks.remove(edit.getReference()); } else if (m_locksAreInTable) { // remove the lock String statement = doubleStorageSql.getDeleteLocksSql(); // collect the fields Object lockFields[] = new Object[2]; lockFields[0] = m_resourceTableName; lockFields[1] = doubleStorageSql.getRecordId(container.getReference() + "/" + edit.getId()); boolean ok = m_sql.dbWrite(statement, lockFields); if (!ok) { M_log.warn("cancelResource: missing lock for table: " + lockFields[0] + " key: " + lockFields[1]); } } else { // release the lock m_locks.remove(edit.getReference()); } } /** * Remove this (locked) Resource. * * @param container * The container for this resource. * @param user * The Edit to remove. */ public void removeResource(Entity container, Edit edit) { // form the SQL delete statement String statement = doubleStorageSql.getDelete2Sql(m_resourceTableName, m_resourceTableIdField, m_resourceTableContainerIdField); Object[] fields = new Object[2]; fields[0] = container.getReference(); fields[1] = edit.getId(); if (m_locksAreInDb) { // use this connection that is stored with the lock Connection lock = (Connection) m_locks.get(edit.getReference()); if (lock == null) { M_log.warn("removeResource(): edit not in locks"); return; } // process the delete statement, commit, and release the lock's connection m_sql.dbUpdateCommit(statement, fields, null, lock); // release the lock m_locks.remove(edit.getReference()); } else if (m_locksAreInTable) { // process the delete statement m_sql.dbWrite(statement, fields); // remove the lock statement = doubleStorageSql.getDeleteLocksSql(); // collect the fields Object lockFields[] = new Object[2]; lockFields[0] = m_resourceTableName; lockFields[1] = doubleStorageSql.getRecordId(container.getReference() + "/" + edit.getId()); boolean ok = m_sql.dbWrite(statement, lockFields); if (!ok) { M_log.warn("removeResource: missing lock for table: " + lockFields[0] + " key: " + lockFields[1]); } } else { // process the delete statement m_sql.dbWrite(statement, fields); // release the lock m_locks.remove(edit.getReference()); } } /** * 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. * * @param values * The values to be inserted into the sql statement. * @return A sql statement fragment for the values part of an insert, one for each value in the array. */ protected String valuesParams(String[] fields) { if ((fields == null) || (fields.length == 0)) return ""; StringBuilder buf = new StringBuilder(); for (int i = 0; i < fields.length; i++) { buf.append(" ?,"); } return buf.toString(); } /** * 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. * * @param values * The values to be inserted into the sql statement. * @return A sql statement fragment for the values part of an insert, one for each value in the array. */ protected String updateSet(String[] fields) { if ((fields == null) || (fields.length == 0)) return ""; StringBuilder buf = new StringBuilder(); for (int i = 0; i < fields.length; i++) { buf.append(fields[i] + " = ?,"); } return buf.toString(); } /** * Form a string of (field, field, field), for sql insert statements, one for each item in the fields array, plus one before, and one after. * * @param before1 * The first field name. * @param before2 * (options) second field name. * @param values * The extra field names, in the middle. * @param after * The last field name. * @return A sql statement fragment for the insert fields. */ protected String insertFields(String before1, String before2, String[] fields, String after) { StringBuilder buf = new StringBuilder(); buf.append(" ("); buf.append(before1); buf.append(","); if (before2 != null) { buf.append(before2); buf.append(","); } if (fields != null) { for (int i = 0; i < fields.length; i++) { buf.append(fields[i] + ","); } } buf.append(after); buf.append(")"); return buf.toString(); } /** * Get resources filtered by date and count and drafts, in descending (latest first) order * * @param afterDate * if null, no date limit, else limited to only messages after this date. * @param limitedToLatest * if 0, no count limit, else limited to only the latest this number of messages. * @param draftsForId * how to handle drafts: null means no drafts, "*" means all, otherwise drafts only if created by this userId. * @param pubViewOnly * if true, include only messages marked pubview, else include any. * @return A list of Message objects that meet the criteria; may be empty */ public List getResources(final Entity container, Time afterDate, int limitedToLatest, String draftsForId, boolean pubViewOnly) { // if we are limiting, and are filtering out drafts or doing pubview, and don't have draft/owner/pubview support, filter here after boolean canLimit = true; boolean filterAfter = false; if ((limitedToLatest > 0) && ((((m_resourceTableDraftField == null) || (m_resourceTableOwnerField == null)) && (!"*".equals(draftsForId))) || ((m_resourceTablePubViewField == null) && pubViewOnly))) { canLimit = false; filterAfter = true; } StringBuilder buf = new StringBuilder(); int numFields = 1; // start the outer statement, later finished with a limiting clause if ((limitedToLatest > 0) && canLimit) { if ("oracle".equals(m_sql.getVendor())) { buf.append("select XML from ("); buf.append("select XML from " + m_resourceTableName); } else if ("mysql".equals(m_sql.getVendor())) { buf.append("select messages.XML from ("); buf.append("select XML from " + m_resourceTableName); } else // if ("hsqldb".equals(m_sql.getVendor())) { // according to SQL2000 specification (used by HSQLDB) the limit clause appears first buf.append("select limit 0 " + limitedToLatest + " XML from " + m_resourceTableName); } } else { buf.append("select XML from " + m_resourceTableName); } buf.append(" where (" + m_resourceTableContainerIdField + " = ?"); if ((m_resourceTableOrderField != null) && (afterDate != null)) { buf.append(" and " + m_resourceTableOrderField + " > ?"); numFields++; } // deal with drafts if we can if ((m_resourceTableDraftField != null) && (m_resourceTableOwnerField != null)) { // if draftsForId is null, we don't want any drafts if (draftsForId == null) { buf.append(" and " + m_resourceTableDraftField + " = '0'"); } // else a "*" means we take all drafts else if (!"*".equals(draftsForId)) { // we want only drafts if the owner field matches buf.append(" and ( " + m_resourceTableDraftField + " = '0' or " + m_resourceTableOwnerField + " = ? )"); numFields++; } } // pubview if ((m_resourceTablePubViewField != null) && pubViewOnly) { buf.append(" and " + m_resourceTablePubViewField + " = '1'"); } // close the where buf.append(")"); if (m_resourceTableOrderField != null) { buf.append(" order by " + m_resourceTableOrderField + " desc"); } boolean useLimitField = false; if ((limitedToLatest > 0) && canLimit) { if ("oracle".equals(m_sql.getVendor())) { buf.append(" ) where rownum <= ?"); numFields++; useLimitField = true; } else if ("mysql".equals(m_sql.getVendor())) { buf.append(" ) AS messages LIMIT " + limitedToLatest); useLimitField = false; } else // if ("hsqldb".equals(m_sql.getVendor())) { // the limit clause appears elsewhere in HSQLDB SQL statements, not here. } } // build up the fields Object fields[] = new Object[numFields]; fields[0] = container.getReference(); int pos = 1; if ((m_resourceTableOrderField != null) && (afterDate != null)) { fields[pos++] = afterDate; } if ((m_resourceTableDraftField != null) && (m_resourceTableOwnerField != null) && (draftsForId != null) && (!"*".equals(draftsForId))) { fields[pos++] = draftsForId; } if (useLimitField) { fields[pos++] = Integer.valueOf(limitedToLatest); } List all = m_sql.dbRead(buf.toString(), fields, new SqlReader() { public Object readSqlResultRecord(ResultSet result) { try { // get the xml and parse into a Resource String xml = result.getString(1); Entity entry = readResource(container, xml); return entry; } catch (SQLException ignore) { return null; } } }); // after filter for draft / pubview and limit if (filterAfter) { Vector v = new Vector(); // deal with drafts / pubview for (Iterator i = all.iterator(); i.hasNext();) { Entity r = (Entity) i.next(); Entity candidate = null; if (m_user.isDraft(r)) { // if some drafts if ((draftsForId != null) && (m_user.getOwnerId(r).equals(draftsForId))) { candidate = r; } } else { candidate = r; } // if we have a candidate to add, and we need pub view only if ((candidate != null) && pubViewOnly) { // if this is not pub view, skip it if ((candidate.getProperties().getProperty(ResourceProperties.PROP_PUBVIEW) == null)) { candidate = null; } } if (candidate != null) { v.add(candidate); } } // pick what we need if (limitedToLatest < v.size()) { all = v.subList(0, limitedToLatest); } else { all = v; } } return all; } /** * Access a list of container ids match (start with) the root. * * @param context * The id root to match. * @return A List (String) of container id which match the root. */ public List getContainerIdsMatching(String root) { // the id of each container will be the part that follows the root reference final int pos = root.length(); // read all users from the db String sql = doubleStorageSql.getSelect9Sql(m_containerTableName, m_containerTableIdField); Object fields[] = new Object[1]; fields[0] = root + "%"; List all = m_sql.dbRead(sql, fields, new SqlReader() { public Object readSqlResultRecord(ResultSet result) { try { // get the reference form and pull off the id (what follows after the root) String ref = result.getString(1); String id = ref.substring(pos); return id; } catch (SQLException ignore) { return null; } } }); return all; } }