0001: /*
0002: *
0003: * JMoney - A Personal Finance Manager
0004: * Copyright (c) 2004 Nigel Westbury <westbury@users.sourceforge.net>
0005: *
0006: *
0007: * This program is free software; you can redistribute it and/or modify
0008: * it under the terms of the GNU General Public License as published by
0009: * the Free Software Foundation; either version 2 of the License, or
0010: * (at your option) any later version.
0011: *
0012: * This program is distributed in the hope that it will be useful,
0013: * but WITHOUT ANY WARRANTY; without even the implied warranty of
0014: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
0015: * GNU General Public License for more details.
0016: *
0017: * You should have received a copy of the GNU General Public License
0018: * along with this program; if not, write to the Free Software
0019: * Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
0020: *
0021: */
0022:
0023: package net.sf.jmoney.jdbcdatastore;
0024:
0025: import java.lang.ref.WeakReference;
0026: import java.lang.reflect.InvocationTargetException;
0027: import java.sql.Connection;
0028: import java.sql.DatabaseMetaData;
0029: import java.sql.PreparedStatement;
0030: import java.sql.ResultSet;
0031: import java.sql.ResultSetMetaData;
0032: import java.sql.SQLException;
0033: import java.sql.Statement;
0034: import java.text.DateFormat;
0035: import java.text.SimpleDateFormat;
0036: import java.util.ArrayList;
0037: import java.util.Collection;
0038: import java.util.Date;
0039: import java.util.HashMap;
0040: import java.util.Hashtable;
0041: import java.util.Map;
0042: import java.util.Vector;
0043:
0044: import net.sf.jmoney.JMoneyPlugin;
0045: import net.sf.jmoney.model2.Account;
0046: import net.sf.jmoney.model2.CapitalAccount;
0047: import net.sf.jmoney.model2.Commodity;
0048: import net.sf.jmoney.model2.CurrencyAccount;
0049: import net.sf.jmoney.model2.DatastoreManager;
0050: import net.sf.jmoney.model2.Entry;
0051: import net.sf.jmoney.model2.ExtendableObject;
0052: import net.sf.jmoney.model2.ExtendablePropertySet;
0053: import net.sf.jmoney.model2.ExtensionPropertySet;
0054: import net.sf.jmoney.model2.IEntryQueries;
0055: import net.sf.jmoney.model2.IListManager;
0056: import net.sf.jmoney.model2.IObjectKey;
0057: import net.sf.jmoney.model2.IValues;
0058: import net.sf.jmoney.model2.ListKey;
0059: import net.sf.jmoney.model2.ListPropertyAccessor;
0060: import net.sf.jmoney.model2.PropertyAccessor;
0061: import net.sf.jmoney.model2.PropertySet;
0062: import net.sf.jmoney.model2.ReferencePropertyAccessor;
0063: import net.sf.jmoney.model2.ScalarPropertyAccessor;
0064: import net.sf.jmoney.model2.Session;
0065: import net.sf.jmoney.model2.SessionInfo;
0066: import net.sf.jmoney.model2.Transaction;
0067:
0068: import org.eclipse.ui.IMemento;
0069: import org.eclipse.ui.IPersistableElement;
0070: import org.eclipse.ui.IWorkbenchWindow;
0071:
0072: /**
0073: * Manages a session that is held in a JDBC database.
0074: *
0075: * @author Nigel Westbury
0076: */
0077: public class SessionManager extends DatastoreManager implements
0078: IEntryQueries {
0079:
0080: /**
0081: * Date format used for embedding dates in SQL statements:
0082: * yyyy-MM-dd
0083: */
0084: private static SimpleDateFormat dateFormat = (SimpleDateFormat) DateFormat
0085: .getDateInstance();
0086: static {
0087: dateFormat.applyPattern("yyyy-MM-dd");
0088: }
0089:
0090: private boolean isHsqldb = false;
0091:
0092: private Connection connection;
0093:
0094: private Statement reusableStatement;
0095:
0096: private IDatabaseRowKey sessionKey;
0097:
0098: /**
0099: * For each <code>PropertySet</code> for which objects are required
0100: * to be cached, map the <code>PropertySet</code> to a <Map>.
0101: * Each map is itself a map of integer ids to extendable objects.
0102: * <P>
0103: * If a PropertySet is cached then so are all property sets
0104: * derived from that property set. However, derived property
0105: * sets do not have their own map. Instead objects of the
0106: * derived property set are put in the map for the base property
0107: * set.
0108: */
0109: private Map<ExtendablePropertySet<?>, Map<Integer, WeakReference<ExtendableObject>>> objectMaps = new HashMap<ExtendablePropertySet<?>, Map<Integer, WeakReference<ExtendableObject>>>();
0110:
0111: private class ParentList {
0112: ExtendablePropertySet<?> parentPropertySet;
0113: ListPropertyAccessor listProperty;
0114:
0115: ParentList(ExtendablePropertySet<?> parentPropertySet,
0116: ListPropertyAccessor listProperty) {
0117: this .parentPropertySet = parentPropertySet;
0118: this .listProperty = listProperty;
0119: }
0120:
0121: public String getColumnName() {
0122: return listProperty.getName().replace('.', '_');
0123: }
0124: }
0125:
0126: /**
0127: * A map of PropertySet objects to non-null Vector objects.
0128: * Each Vector object is a list of ParentList objects.
0129: * An entry exists in this map for all property sets that are
0130: * not extension property sets. For each property set, the
0131: * vector contains a list of the list properties in all property
0132: * sets that contain a list of objects of the property set.
0133: */
0134: private Map<ExtendablePropertySet<?>, Vector<ParentList>> tablesMap = null;
0135:
0136: public SessionManager(Connection connection) throws SQLException {
0137: this .connection = connection;
0138:
0139: /*
0140: * Set on the flag that indicates if we are operating with a database that
0141: * has non-standard features that affect us.
0142: */
0143: String databaseProductName = connection.getMetaData()
0144: .getDatabaseProductName();
0145: if (databaseProductName.equals("HSQL Database Engine")) {
0146: isHsqldb = true;
0147: }
0148:
0149: // Create a weak reference map for every base property set.
0150: for (ExtendablePropertySet<?> propertySet : PropertySet
0151: .getAllExtendablePropertySets()) {
0152: if (propertySet.getBasePropertySet() == null) {
0153: objectMaps
0154: .put(
0155: propertySet,
0156: new HashMap<Integer, WeakReference<ExtendableObject>>());
0157: }
0158: }
0159:
0160: try {
0161: this .reusableStatement = connection.createStatement();
0162: } catch (SQLException e) {
0163: e.printStackTrace();
0164: throw new RuntimeException("SQL Exception: "
0165: + e.getMessage());
0166: }
0167:
0168: // Create a statement for our use.
0169: Statement stmt = connection.createStatement();
0170:
0171: /*
0172: * Find all properties in any property set that are a list of objects
0173: * with the type as this property set. A column must exist in this table
0174: * for each such property that exists in another property set.
0175: *
0176: * The exception is that no such column exists if the list property is
0177: * in the session object (not including extentions of the session
0178: * object). This is an optimization that saves columns. The optimization
0179: * works because we know there is only ever a single session object.
0180: *
0181: * The reason why extensions are not included is because we do not know
0182: * what lists may be added in extensions. A list may be added that
0183: * contains the same object type as one of the session lists. For
0184: * example, an extension to the session object may contain a list of
0185: * currencies. A parent column must exist in the currency table to
0186: * indicate if a currency is in such a list. Otherwise we would know the
0187: * currency is in a list property of the session object but we would not
0188: * know which list.
0189: */
0190: tablesMap = new Hashtable<ExtendablePropertySet<?>, Vector<ParentList>>();
0191: for (ExtendablePropertySet<?> propertySet : PropertySet
0192: .getAllExtendablePropertySets()) {
0193: Vector<ParentList> list = new Vector<ParentList>(); // List of PropertyAccessors
0194: for (ExtendablePropertySet<?> propertySet2 : PropertySet
0195: .getAllExtendablePropertySets()) {
0196: for (ListPropertyAccessor<?> listAccessor : propertySet2
0197: .getListProperties2()) {
0198: if (listAccessor.getPropertySet() != SessionInfo
0199: .getPropertySet()) {
0200: if (propertySet.getImplementationClass() == listAccessor
0201: .getElementPropertySet()
0202: .getImplementationClass()) {
0203: // Add to the list of possible parents.
0204: list.add(new ParentList(propertySet2,
0205: listAccessor));
0206: }
0207: }
0208: }
0209: }
0210: tablesMap.put(propertySet, list);
0211: }
0212:
0213: // Check that all the required tables and columns exist.
0214: // Any missing tables or columns are created at this time.
0215: checkDatabase(connection, stmt);
0216:
0217: /*
0218: * Create the single row in the session table, if it does not
0219: * already exist. Create this row with default values for
0220: * the session properties.
0221: */
0222: String sql3 = "SELECT * FROM "
0223: + SessionInfo.getPropertySet().getId()
0224: .replace('.', '_');
0225: System.out.println(sql3);
0226: ResultSet rs3 = stmt.executeQuery(sql3);
0227: if (!rs3.next()) {
0228:
0229: String sql = "INSERT INTO "
0230: + SessionInfo.getPropertySet().getId().replace('.',
0231: '_') + " (";
0232:
0233: String columnNames = "";
0234: String columnValues = "";
0235: String separator = "";
0236:
0237: for (ScalarPropertyAccessor<?> propertyAccessor : SessionInfo
0238: .getPropertySet().getScalarProperties2()) {
0239: String columnName = getColumnName(propertyAccessor);
0240: Object value = propertyAccessor.getDefaultValue();
0241:
0242: columnNames += separator + "\"" + columnName + "\"";
0243: columnValues += separator + valueToSQLText(value);
0244:
0245: separator = ", ";
0246: }
0247:
0248: sql += columnNames + ") VALUES(" + columnValues + ")";
0249:
0250: try {
0251: System.out.println(sql);
0252: stmt.execute(sql);
0253: } catch (SQLException e) {
0254: // TODO Handle this properly
0255: e.printStackTrace();
0256: throw new RuntimeException("internal error");
0257: }
0258:
0259: // Now try again to get the session row.
0260: rs3 = stmt.executeQuery(sql3);
0261: rs3.next();
0262: }
0263:
0264: // Now create the session object from the session database row
0265: this .sessionKey = new ObjectKey(rs3, SessionInfo
0266: .getPropertySet(), null, this );
0267:
0268: rs3.close();
0269: stmt.close();
0270: }
0271:
0272: @Override
0273: public Session getSession() {
0274: return (Session) sessionKey.getObject();
0275: }
0276:
0277: /**
0278: * @return
0279: */
0280: public IDatabaseRowKey getSessionKey() {
0281: return sessionKey;
0282: }
0283:
0284: /**
0285: * @return
0286: */
0287: public Connection getConnection() {
0288: return connection;
0289: }
0290:
0291: /**
0292: * This method provides a statement object to consumers
0293: * which need a statement object and can guarantee that
0294: * they will have finished with the statement before
0295: * another call to this method is made. Realistically
0296: * that means this method should only be called when the
0297: * caller has closed the result set before the calling
0298: * method returns and, furthermore, no calls are made to
0299: * methods that may make SQL queries.
0300: */
0301: public Statement getReusableStatement() {
0302: return reusableStatement;
0303: }
0304:
0305: @Override
0306: public boolean canClose(IWorkbenchWindow window) {
0307: // A JDBC database can always be closed without further
0308: // input from the user.
0309: return true;
0310: }
0311:
0312: @Override
0313: public void close() {
0314: try {
0315: reusableStatement.close();
0316: connection.close();
0317: } catch (SQLException e) {
0318: e.printStackTrace();
0319: throw new RuntimeException("SQL Exception: "
0320: + e.getMessage());
0321: }
0322: }
0323:
0324: @Override
0325: public String getBriefDescription() {
0326: // TODO: improve this implementation to give more
0327: // details of the database.
0328: return "JDBC database";
0329: }
0330:
0331: private IPersistableElement persistableElement = new IPersistableElement() {
0332: public String getFactoryId() {
0333: return "net.sf.jmoney.jdbcdatastore.SessionFactory";
0334: }
0335:
0336: public void saveState(IMemento memento) {
0337: /*
0338: * The open session must be using the database as
0339: * specified in the preference pages. Therefore there
0340: * is no need to save anything further here.
0341: *
0342: * If we were to give the user the option at 'open' time
0343: * to open a database other than the database specified in
0344: * the prefence page then we would have to save that information
0345: * here.
0346: */
0347: }
0348: };
0349:
0350: public Object getAdapter(Class adapter) {
0351: if (adapter == IPersistableElement.class) {
0352: return persistableElement;
0353: }
0354:
0355: if (adapter == IEntryQueries.class) {
0356: return this ;
0357: }
0358:
0359: return null;
0360: }
0361:
0362: public <E extends ExtendableObject> E getObjectIfMaterialized(
0363: ExtendablePropertySet<E> basemostPropertySet, int id) {
0364: Map<Integer, WeakReference<ExtendableObject>> result = objectMaps
0365: .get(basemostPropertySet);
0366: WeakReference<ExtendableObject> object = result.get(id);
0367: if (object == null) {
0368: // Indicate that the object is not cached.
0369: return null;
0370: } else {
0371: return basemostPropertySet.getImplementationClass().cast(
0372: object.get());
0373: }
0374: }
0375:
0376: public <E extends ExtendableObject> void setMaterializedObject(
0377: ExtendablePropertySet<E> basemostPropertySet, int id,
0378: E extendableObject) {
0379: Map<Integer, WeakReference<ExtendableObject>> result = objectMaps
0380: .get(basemostPropertySet);
0381: result.put(id, new WeakReference<ExtendableObject>(
0382: extendableObject));
0383: }
0384:
0385: /**
0386: * This method builds a select statement that joins the table for a given
0387: * property set with all the ancestor tables. This results in a result set
0388: * that contains all the columns necessary to construct the objects.
0389: *
0390: * The caller would normally append a WHERE clause to the returned statement
0391: * before executing it.
0392: *
0393: * @param propertySet
0394: * @return
0395: * @throws SQLException
0396: */
0397: String buildJoins(ExtendablePropertySet<?> finalPropertySet) {
0398: /*
0399: * Some databases (e.g. HDBSQL) execute queries faster if JOIN ON is
0400: * used rather than a WHERE clause, and will also execute faster if the
0401: * smallest table is put first. The smallest table in this case is the
0402: * table represented by typedPropertySet and the larger tables are the
0403: * base tables.
0404: */
0405: String tableName = finalPropertySet.getId().replace('.', '_');
0406: String sql = "SELECT * FROM " + tableName;
0407: for (ExtendablePropertySet<?> propertySet2 = finalPropertySet
0408: .getBasePropertySet(); propertySet2 != null; propertySet2 = propertySet2
0409: .getBasePropertySet()) {
0410: String tableName2 = propertySet2.getId().replace('.', '_');
0411: sql += " JOIN " + tableName2 + " ON " + tableName
0412: + "._ID = " + tableName2 + "._ID";
0413: }
0414:
0415: return sql;
0416: }
0417:
0418: /**
0419: * This method creates a new statement on each call. This allows callers
0420: * to have multiple result sets active at the same time.
0421: *
0422: * listProperty may be a list of a derivable property set. We thus will not
0423: * know the exact property set of each element in advance. The caller must
0424: * pass an the actual property set (a final property set) and this method will
0425: * return a result set containing only elements of that property set and containing
0426: * columns for all properties of that result set.
0427: *
0428: * @param parentKey
0429: * @param listProperty
0430: * @param finalPropertySet
0431: * @return
0432: * @throws SQLException
0433: */
0434: ResultSet executeListQuery(DatabaseListKey<?> listKey,
0435: ExtendablePropertySet<?> finalPropertySet)
0436: throws SQLException {
0437: String sql = buildJoins(finalPropertySet);
0438:
0439: /*
0440: * Add the WHERE clause. There is a parent column with the same name as
0441: * the name of the list property. Only if the number in this column is
0442: * the same as the id of the owner of this list is the object in this
0443: * list.
0444: *
0445: * Note that there is an optimization. If the parent object is the
0446: * session object then no such column will exist. We instead check that
0447: * all the other parent columns (if any) are null.
0448: */
0449: if (listKey.listPropertyAccessor.getPropertySet() == SessionInfo
0450: .getPropertySet()) {
0451: String whereClause = "";
0452: String separator = "";
0453: for (ExtendablePropertySet<?> propertySet = finalPropertySet; propertySet != null; propertySet = propertySet
0454: .getBasePropertySet()) {
0455: Vector<ParentList> possibleContainingLists = tablesMap
0456: .get(propertySet);
0457: for (ParentList parentList : possibleContainingLists) {
0458: whereClause += separator + "\""
0459: + parentList.getColumnName() + "\" IS NULL";
0460: separator = " AND";
0461: }
0462: }
0463: if (whereClause.length() != 0) {
0464: sql += " WHERE " + whereClause;
0465: }
0466:
0467: Statement stmt = connection.createStatement();
0468: System.out.println(sql);
0469: return stmt.executeQuery(sql);
0470: } else {
0471: /*
0472: * Add a WHERE clause that limits the result set to those rows
0473: * that are in the appropriate list in the appropriate parent object.
0474: */
0475: sql += " WHERE \""
0476: + listKey.listPropertyAccessor.getName().replace(
0477: '.', '_') + "\" = ?";
0478:
0479: System.out.println(sql + " : "
0480: + listKey.parentKey.getRowId());
0481: PreparedStatement stmt = connection.prepareStatement(sql);
0482: stmt.setInt(1, listKey.parentKey.getRowId());
0483: return stmt.executeQuery();
0484: }
0485: }
0486:
0487: /**
0488: * @param propertySet
0489: * @param values
0490: * @param listProperty
0491: * @param parent
0492: * @param sessionManager
0493: *
0494: * @return The id of the inserted row
0495: */
0496: public int insertIntoDatabase(ExtendablePropertySet<?> propertySet,
0497: ExtendableObject newObject, DatabaseListKey<?> listKey) {
0498: int rowId = -1;
0499:
0500: // We must insert into the base table first, then the table for the objects
0501: // derived from the base and so on. The reason is that each derived table
0502: // has a primary key field that is a foreign key into its base table.
0503: // We can get the chain of property sets only by starting at the given
0504: // property set and repeatedly getting the base property set. We must
0505: // therefore store these so that we can loop through the property sets in
0506: // the reverse order.
0507:
0508: Vector<ExtendablePropertySet<?>> propertySets = new Vector<ExtendablePropertySet<?>>();
0509: for (ExtendablePropertySet<?> propertySet2 = propertySet; propertySet2 != null; propertySet2 = propertySet2
0510: .getBasePropertySet()) {
0511: propertySets.add(propertySet2);
0512: }
0513:
0514: for (int index = propertySets.size() - 1; index >= 0; index--) {
0515: ExtendablePropertySet<?> propertySet2 = propertySets
0516: .get(index);
0517:
0518: String sql = "INSERT INTO "
0519: + propertySet2.getId().replace('.', '_') + " (";
0520:
0521: String columnNames = "";
0522: String columnValues = "";
0523: String separator = "";
0524:
0525: /*
0526: * If this is a basemost property set then the _ID column will be
0527: * auto-generated by the database. If this is a derived property
0528: * set then we must insert the id that had been assigned when the
0529: * row in the basemost table was inserted.
0530: */
0531: if (index != propertySets.size() - 1) {
0532: columnNames += separator + "_ID";
0533: columnValues += separator + Integer.toString(rowId);
0534: separator = ", ";
0535: }
0536:
0537: for (ScalarPropertyAccessor<?> propertyAccessor : propertySet2
0538: .getScalarProperties2()) {
0539: String columnName = getColumnName(propertyAccessor);
0540:
0541: // Get the value from the passed property value array.
0542: Object value = newObject
0543: .getPropertyValue(propertyAccessor);
0544:
0545: columnNames += separator + "\"" + columnName + "\"";
0546: columnValues += separator + valueToSQLText(value);
0547:
0548: separator = ", ";
0549: }
0550:
0551: /* Set the parent id in the appropriate column.
0552: *
0553: * If the containing list property is a property in one of the three
0554: * lists in the session object
0555: * then, as an optimization, there is no parent column.
0556: */
0557: if (listKey.listPropertyAccessor.getElementPropertySet() == propertySet2
0558: && listKey.listPropertyAccessor.getPropertySet() != SessionInfo
0559: .getPropertySet()) {
0560: String valueString = Integer.toString(listKey.parentKey
0561: .getRowId());
0562: String parentColumnName = listKey.listPropertyAccessor
0563: .getName().replace('.', '_');
0564: columnNames += separator + "\"" + parentColumnName
0565: + "\"";
0566: columnValues += separator + valueString;
0567: separator = ", ";
0568: }
0569:
0570: /*
0571: * If the base-most property set and it is derivable, the
0572: * _PROPERTY_SET column must be set.
0573: */
0574: if (propertySet2.getBasePropertySet() == null
0575: && propertySet2.isDerivable()) {
0576: columnNames += separator + "_PROPERTY_SET";
0577: // Set to the id of the final
0578: // (non-derivable) property set for this object.
0579: ExtendablePropertySet<?> finalPropertySet = propertySets
0580: .get(0);
0581: columnValues += separator + "\'"
0582: + finalPropertySet.getId() + "\'";
0583: separator = ", ";
0584: }
0585:
0586: sql += columnNames + ") VALUES(" + columnValues + ")";
0587:
0588: try {
0589: System.out.println(sql);
0590: /*
0591: * Insert the row and, if this is a basemost table, get the
0592: * value of the auto-generated key.
0593: */
0594: if (index == propertySets.size() - 1) {
0595: ResultSet rs;
0596: if (isHsqldb) {
0597: /*
0598: * HSQLDB does not, as of 1.8.0.7, support the JDBC
0599: * standard way of getting the generated key. We must do
0600: * things slightly differently.
0601: */
0602: reusableStatement.execute(sql);
0603: rs = reusableStatement
0604: .executeQuery("CALL IDENTITY()");
0605: } else {
0606: reusableStatement.execute(sql,
0607: Statement.RETURN_GENERATED_KEYS);
0608: rs = reusableStatement.getGeneratedKeys();
0609: }
0610: rs.next();
0611: rowId = rs.getInt(1);
0612: rs.close();
0613: } else {
0614: reusableStatement.execute(sql);
0615: }
0616: } catch (SQLException e) {
0617: // TODO Handle this properly
0618: e.printStackTrace();
0619: throw new RuntimeException("internal error");
0620: }
0621: }
0622:
0623: return rowId;
0624: }
0625:
0626: public <E extends ExtendableObject> void reparentInDatabase(
0627: E extendableObject, DatabaseListKey<E> newListKey) {
0628: IDatabaseRowKey objectKey = (IDatabaseRowKey) extendableObject
0629: .getObjectKey();
0630: ListKey originalListKey = extendableObject.getParentListKey();
0631: IDatabaseRowKey originalParentKey = (IDatabaseRowKey) originalListKey
0632: .getParentKey();
0633:
0634: try {
0635: boolean priorAutocommitState = connection.getAutoCommit();
0636: connection.setAutoCommit(false);
0637:
0638: /*
0639: * We may need one or two updates, depending on whether the column containing
0640: * the original parent and the column containing the new parent are in the same
0641: * table.
0642: */
0643: try {
0644:
0645: /*
0646: * If the containing list property is a property in one of the three
0647: * lists in the session object then, as an optimization, there is no
0648: * parent column.
0649: *
0650: * Clear out the original parent id (unless the containing list is one
0651: * of the three lists in the extendable session object).
0652: */
0653: if (originalListKey.getListPropertyAccessor()
0654: .getPropertySet() != SessionInfo
0655: .getPropertySet()) {
0656: String parentColumnName = originalListKey
0657: .getListPropertyAccessor().getName()
0658: .replace('.', '_');
0659: String sql = "UPDATE "
0660: + originalListKey.getListPropertyAccessor()
0661: .getElementPropertySet().getId()
0662: .replace('.', '_') + " SET "
0663: + parentColumnName + "=NULL"
0664: + " WHERE _ID=" + objectKey.getRowId()
0665: + " AND " + parentColumnName + "="
0666: + originalParentKey.getRowId();
0667:
0668: System.out.println(sql);
0669: int numberUpdated = reusableStatement
0670: .executeUpdate(sql);
0671: if (numberUpdated != 1) {
0672: throw new RuntimeException("internal error");
0673: }
0674:
0675: connection.commit();
0676: }
0677:
0678: /*
0679: * Set the new parent id (unless the containing list is one of the three
0680: * lists in the extendable session object).
0681: */
0682: if (newListKey.listPropertyAccessor.getPropertySet() != SessionInfo
0683: .getPropertySet()) {
0684: String parentColumnName = newListKey.listPropertyAccessor
0685: .getName().replace('.', '_');
0686: String sql = "UPDATE "
0687: + newListKey.listPropertyAccessor
0688: .getElementPropertySet().getId()
0689: .replace('.', '_') + " SET "
0690: + parentColumnName + "="
0691: + newListKey.parentKey.getRowId()
0692: + " WHERE _ID=" + objectKey.getRowId()
0693: + " AND " + parentColumnName + " IS NULL";
0694:
0695: System.out.println(sql);
0696: int numberUpdated = reusableStatement
0697: .executeUpdate(sql);
0698: if (numberUpdated != 1) {
0699: throw new RuntimeException("internal error");
0700: }
0701: }
0702: } finally {
0703: connection.setAutoCommit(priorAutocommitState);
0704: }
0705: } catch (SQLException e) {
0706: // TODO Handle this properly
0707: e.printStackTrace();
0708: throw new RuntimeException("internal error");
0709: }
0710: }
0711:
0712: /**
0713: * Execute SQL UPDATE statements to update the database with
0714: * the new values of the properties of an object.
0715: * <P>
0716: * The SQL statements will verify the old values of the properties
0717: * in the WHERE clause. If the database does not contain an object
0718: * with the expected old property values that an exception is raised,
0719: * causing the transaction to be rolled back.
0720: *
0721: * @param rowId
0722: * @param oldValues
0723: * @param newValues
0724: * @param sessionManager
0725: */
0726: public void updateProperties(ExtendablePropertySet<?> propertySet,
0727: int rowId, Object[] oldValues, Object[] newValues) {
0728: Statement stmt = getReusableStatement();
0729:
0730: // The array of property values contains the properties from the
0731: // base table first, then the table derived from that and so on.
0732: // We therefore process the tables starting with the base table
0733: // first. This requires first copying the property sets into
0734: // an array so that we can iterate them in reverse order.
0735: Vector<ExtendablePropertySet<?>> propertySets = new Vector<ExtendablePropertySet<?>>();
0736: for (ExtendablePropertySet<?> propertySet2 = propertySet; propertySet2 != null; propertySet2 = propertySet2
0737: .getBasePropertySet()) {
0738: propertySets.add(propertySet2);
0739: }
0740:
0741: int propertyIndex = 0;
0742:
0743: for (int index = propertySets.size() - 1; index >= 0; index--) {
0744: ExtendablePropertySet<?> propertySet2 = propertySets
0745: .get(index);
0746:
0747: String sql = "UPDATE "
0748: + propertySet2.getId().replace('.', '_') + " SET ";
0749:
0750: String updateClauses = "";
0751: String whereTerms = "";
0752: String separator = "";
0753:
0754: for (ScalarPropertyAccessor<?> propertyAccessor : propertySet2
0755: .getScalarProperties2()) {
0756:
0757: if (propertyAccessor.getIndexIntoScalarProperties() != propertyIndex) {
0758: throw new RuntimeException("index mismatch");
0759: }
0760: // See if the value of the property has changed.
0761: Object oldValue = oldValues[propertyIndex];
0762: Object newValue = newValues[propertyIndex];
0763: propertyIndex++;
0764:
0765: if (!JMoneyPlugin.areEqual(oldValue, newValue)) {
0766: String columnName = getColumnName(propertyAccessor);
0767:
0768: updateClauses += separator + "\"" + columnName
0769: + "\"=" + valueToSQLText(newValue);
0770:
0771: if (oldValue != null) {
0772: whereTerms += " AND \"" + columnName + "\"="
0773: + valueToSQLText(oldValue);
0774: } else {
0775: whereTerms += " AND \"" + columnName
0776: + "\" IS NULL";
0777: }
0778: separator = ", ";
0779: }
0780: }
0781:
0782: // If no properties have been updated in a table then no update
0783: // statement should be executed.
0784:
0785: if (!separator.equals("")) {
0786: sql += updateClauses + " WHERE _ID=" + rowId
0787: + whereTerms;
0788:
0789: try {
0790: System.out.println(sql);
0791: int numberUpdated = stmt.executeUpdate(sql);
0792: if (numberUpdated != 1) {
0793: // This could happen if a column in the table contains a string
0794: // serialization of a custom object, and the column contained a string
0795: // that failed to construct a value. In that case, the prior property value will
0796: // be null be the value in the database will be non-null.
0797: throw new RuntimeException(
0798: "Update failed. Row with expected data was not found.");
0799: }
0800: } catch (SQLException e) {
0801: // TODO Handle this properly
0802: e.printStackTrace();
0803: throw new RuntimeException("internal error");
0804: }
0805: }
0806: }
0807: }
0808:
0809: /**
0810: * Given a value of a property as an Object, return the text that
0811: * represents the value in an SQL statement.
0812: *
0813: * @param newValue
0814: * @return
0815: */
0816: // TODO: If we always used prepared statements with parameters
0817: // then we may not need this method at all.
0818: private static String valueToSQLText(Object value) {
0819: String valueString;
0820:
0821: if (value != null) {
0822: Class<?> valueClass = value.getClass();
0823: if (valueClass == String.class || valueClass == char.class
0824: || valueClass == Character.class) {
0825: valueString = '\'' + value.toString().replaceAll("'",
0826: "''") + '\'';
0827: } else if (value instanceof Date) {
0828: Date date = (Date) value;
0829: valueString = '\'' + dateFormat.format(date) + '\'';
0830: } else if (value instanceof Boolean) {
0831: // MS SQL does not allow true and false,
0832: // even though HSQL does. So we cannot use toString.
0833: Boolean bValue = (Boolean) value;
0834: valueString = bValue.booleanValue() ? "1" : "0";
0835: } else if (ExtendableObject.class
0836: .isAssignableFrom(valueClass)) {
0837: ExtendableObject extendableObject = (ExtendableObject) value;
0838: IDatabaseRowKey key = (IDatabaseRowKey) extendableObject
0839: .getObjectKey();
0840: valueString = Integer.toString(key.getRowId());
0841: } else if (Number.class.isAssignableFrom(valueClass)) {
0842: valueString = value.toString();
0843: } else {
0844: /*
0845: * All other objects are serialized to a string.
0846: */
0847: valueString = '\'' + value.toString().replaceAll("'",
0848: "''") + '\'';
0849: }
0850: } else {
0851: valueString = "NULL";
0852: }
0853:
0854: return valueString;
0855: }
0856:
0857: /**
0858: * Execute SQL DELETE statements to remove the given object
0859: * from the database.
0860: *
0861: * @param objectKey
0862: * @return true if the object was deleted, false if the object
0863: * was not not deleted because there are references to it
0864: * in the database
0865: * @throws RuntimeException if either an SQLException occurs or if
0866: * the object did not exist in the database or if some
0867: * other integrity violation was found in the database
0868: */
0869: public boolean deleteFromDatabase(IDatabaseRowKey objectKey) {
0870: ExtendablePropertySet<?> propertySet = PropertySet
0871: .getPropertySet(objectKey.getObject().getClass());
0872:
0873: Statement stmt = getReusableStatement();
0874:
0875: /*
0876: * Because we cannot always use CASCADE, we must first delete objects
0877: * in list properties contained in this object. This is a recursive
0878: * process.
0879: */
0880: deleteListElements(objectKey);
0881:
0882: /*
0883: * Because each table for a derived class contains a foreign key
0884: * constraint to the table for the base class, we must delete the rows
0885: * starting with the most derived table and ending with the base-most
0886: * table.
0887: *
0888: * Alternatively, we could have set the 'CASCADE' option for delete in
0889: * the database and just delete the row in the base-most table. However,
0890: * it is perhaps safer not to use 'CASCADE'.
0891: */
0892: for (ExtendablePropertySet<?> propertySet2 = propertySet; propertySet2 != null; propertySet2 = propertySet2
0893: .getBasePropertySet()) {
0894:
0895: String sql = "DELETE FROM "
0896: + propertySet2.getId().replace('.', '_')
0897: + " WHERE _ID=" + objectKey.getRowId();
0898:
0899: try {
0900: System.out.println(sql);
0901: int rowCount = stmt.executeUpdate(sql);
0902: if (rowCount != 1) {
0903: if (rowCount == 0 && propertySet2 == propertySet) {
0904: /*
0905: * The object does not exist in the database. It is
0906: * possible that another process deleted it so we ignore
0907: * this condition.
0908: */
0909: } else {
0910: throw new RuntimeException(
0911: "database is inconsistent");
0912: }
0913: }
0914: } catch (SQLException e) {
0915: if (e.getSQLState().equals("23000")) {
0916: /*
0917: * An attempt has been made to delete an object that has
0918: * references to it. This particular error, unlike all other
0919: * SQL errors, is treated as a valid result, not an error.
0920: * This is because the caller may legitimately attempt to
0921: * delete such rows because this saves the caller from
0922: * having to check for references itself (not a trivial
0923: * task, so why not let the database do the check).
0924: */
0925: return false;
0926: }
0927: // TODO Handle this properly
0928: e.printStackTrace();
0929: throw new RuntimeException("internal error");
0930: }
0931: }
0932:
0933: return true;
0934: }
0935:
0936: /**
0937: * This method deletes the child elements of a given object (objects
0938: * contained in list properties of the given object). This method is
0939: * recursive, so all descendant objects are deleted.
0940: *
0941: * We could have used ON DELETE CASCADE to delete these objects. However,
0942: * not all databases fully support this. For example, Microsoft SQL Server
0943: * does not support ON DELETE CASCADE when a column in a table is
0944: * referencing another row in the same table. This makes it unusable for us
0945: * because columns can reference other rows in the same table (for example,
0946: * an account can have sub-accounts which are rows in the same table).
0947: *
0948: * @param rowId
0949: * @param extendableObject
0950: * @param propertySet
0951: */
0952: private void deleteListElements(IDatabaseRowKey objectKey) {
0953: ExtendableObject extendableObject = objectKey.getObject();
0954: ExtendablePropertySet<?> propertySet = PropertySet
0955: .getPropertySet(extendableObject.getClass());
0956:
0957: for (ListPropertyAccessor<?> listProperty : propertySet
0958: .getListProperties3()) {
0959: /*
0960: * Find all elements in the list. The child elements will almost
0961: * certainly already be cached in memory so this is unlikely to
0962: * result in any queries being sent to the database.
0963: */
0964: for (ExtendableObject child : extendableObject
0965: .getListPropertyValue(listProperty)) {
0966: deleteFromDatabase((IDatabaseRowKey) child
0967: .getObjectKey());
0968: }
0969: }
0970: }
0971:
0972: /**
0973: * Construct an object with default property values.
0974: *
0975: * @param propertySet
0976: * @param objectKey The key to this object. This is required by this
0977: * method because it must be passed to the constructor.
0978: * This method does not call the setObject or setRowId
0979: * methods on this key. It is the caller's responsibility
0980: * to call these methods.
0981: * @param parent
0982: * @return
0983: */
0984: public <E extends ExtendableObject> E constructExtendableObject(
0985: ExtendablePropertySet<E> propertySet,
0986: IDatabaseRowKey objectKey, ListKey<? super E> listKey) {
0987: E extendableObject = propertySet
0988: .constructDefaultImplementationObject(objectKey,
0989: listKey);
0990:
0991: setMaterializedObject(getBasemostPropertySet(propertySet),
0992: objectKey.getRowId(), extendableObject);
0993:
0994: return extendableObject;
0995: }
0996:
0997: private <E2 extends ExtendableObject> IListManager<E2> createListManager(
0998: DatabaseListKey<E2> listKey) {
0999: return new ListManagerCached<E2>(this , listKey, true);
1000: }
1001:
1002: /**
1003: * Construct an object with the given property values.
1004: *
1005: * @param propertySet
1006: * @param objectKey The key to this object. This is required by this
1007: * method because it must be passed to the constructor.
1008: * This method does not call the setObject or setRowId
1009: * methods on this key. It is the caller's responsibility
1010: * to call these methods.
1011: * @param parent
1012: * @param values the values of the scalar properties to be set into this object,
1013: * with ExtendableObject properties having the object key in this array
1014: * @return
1015: */
1016: public <E extends ExtendableObject> E constructExtendableObject(
1017: ExtendablePropertySet<E> propertySet,
1018: IDatabaseRowKey objectKey,
1019: DatabaseListKey<? super E> listKey, IValues values) {
1020: E extendableObject = propertySet.constructImplementationObject(
1021: objectKey, constructListKey(listKey), values);
1022:
1023: setMaterializedObject(getBasemostPropertySet(propertySet),
1024: objectKey.getRowId(), extendableObject);
1025:
1026: return extendableObject;
1027: }
1028:
1029: /**
1030: * Materialize an object from a row of data.
1031: * <P>
1032: * This version of this method should be called when
1033: * the caller knows the parent of the object to
1034: * be materialized (or at least, the key to the parent).
1035: * The parent key is passed to this method by the caller
1036: * and that saves this method from needing to build a
1037: * new parent key from the object's data in the database.
1038: *
1039: * @param rs
1040: * @param propertySet
1041: * @param objectKey
1042: * @param parentKey
1043: * @return
1044: * @throws SQLException
1045: */
1046: <E extends ExtendableObject> E materializeObject(
1047: final ResultSet rs,
1048: final ExtendablePropertySet<E> propertySet,
1049: final IDatabaseRowKey objectKey, ListKey<? super E> listKey)
1050: throws SQLException {
1051: /**
1052: * The list of parameters to be passed to the constructor
1053: * of this object.
1054: */
1055: IValues values = new IValues() {
1056:
1057: public <V> V getScalarValue(
1058: ScalarPropertyAccessor<V> propertyAccessor) {
1059: String columnName = getColumnName(propertyAccessor);
1060:
1061: try {
1062: Class<V> valueClass = propertyAccessor
1063: .getClassOfValueObject();
1064: if (valueClass == Character.class) {
1065: return valueClass.cast(rs.getString(columnName)
1066: .charAt(0));
1067: } else if (valueClass == Long.class) {
1068: return valueClass.cast(rs.getLong(columnName));
1069: } else if (valueClass == Integer.class) {
1070: return valueClass.cast(rs.getInt(columnName));
1071: } else if (valueClass == String.class) {
1072: return valueClass
1073: .cast(rs.getString(columnName));
1074: } else if (valueClass == Boolean.class) {
1075: return valueClass.cast(rs
1076: .getBoolean(columnName));
1077: } else if (valueClass == Date.class) {
1078: return valueClass.cast(rs.getDate(columnName));
1079: } else {
1080: /*
1081: * Must be a user defined object. Construct it using
1082: * the string constructor.
1083: */
1084: String text = rs.getString(columnName);
1085: if (rs.wasNull() || text.length() == 0) {
1086: return null;
1087: } else {
1088: /*
1089: * The property value is an class that is in none of the
1090: * above categories. We therefore use the string
1091: * constructor to construct the object.
1092: */
1093: try {
1094: return valueClass.getConstructor(
1095: new Class[] { String.class })
1096: .newInstance(
1097: new Object[] { text });
1098: } catch (InvocationTargetException e) {
1099: /*
1100: * An exception was thrown in the constructor. Log
1101: * the original exception. We then set the value to
1102: * null and continue on the basis that it is better
1103: * for the user to lose the value (which was
1104: * probably corrupted anyway) than to have the
1105: * entire accounting datastore unreadable.
1106: */
1107: // TODO: There is a problem with this. Optimistic locking
1108: // fails. Any attempt to update this row in the database
1109: // will generate an update that tests for the value being
1110: // null. However, the value was in actual fact not null.
1111: e.getCause().printStackTrace();
1112: return null;
1113: } catch (Exception e) {
1114: /*
1115: * The classes used in the data model should be
1116: * checked when the PropertySet and PropertyAccessor
1117: * static fields are initialized. Therefore other
1118: * plug-ins should not be able to cause an error
1119: * here.
1120: */
1121: // TODO: put the above mentioned check into
1122: // the initialization code.
1123: e.printStackTrace();
1124: throw new RuntimeException(
1125: "internal error");
1126: }
1127: }
1128: }
1129: } catch (SQLException e) {
1130: e.printStackTrace();
1131: throw new RuntimeException("database error");
1132: }
1133: }
1134:
1135: public IObjectKey getReferencedObjectKey(
1136: ReferencePropertyAccessor<?> propertyAccessor) {
1137: String columnName = getColumnName(propertyAccessor);
1138: try {
1139: int rowIdOfProperty = rs.getInt(columnName);
1140: if (rs.wasNull()) {
1141: return null;
1142: } else {
1143: ExtendablePropertySet<? extends ExtendableObject> propertySetOfProperty = PropertySet
1144: .getPropertySet(propertyAccessor
1145: .getClassOfValueObject());
1146:
1147: /*
1148: * We must obtain an object key. However, we do not have to create
1149: * the object or obtain a reference to the object itself at this time.
1150: * Nor do we want to for performance reasons.
1151: */
1152: return new ObjectKey(rowIdOfProperty,
1153: propertySetOfProperty,
1154: SessionManager.this );
1155: }
1156: } catch (SQLException e) {
1157: e.printStackTrace();
1158: throw new RuntimeException("database error");
1159: }
1160: }
1161:
1162: public <E2 extends ExtendableObject> IListManager<E2> getListManager(
1163: IObjectKey listOwnerKey,
1164: ListPropertyAccessor<E2> listAccessor) {
1165: return objectKey.constructListManager(listAccessor);
1166: }
1167:
1168: public Collection<ExtensionPropertySet<?>> getNonDefaultExtensions() {
1169: /*
1170: * In order to find out which extensions have non-default values, we have to read the property
1171: * values from the rowset and compare against the default values given by the accessor.
1172: * Note that the values may be null so we use the utility method to do the comparison.
1173: */
1174: Collection<ExtensionPropertySet<?>> nonDefaultExtensions = new Vector<ExtensionPropertySet<?>>();
1175: outerLoop: for (ExtensionPropertySet<?> extensionPropertySet : propertySet
1176: .getExtensionPropertySets()) {
1177: boolean nonDefaultValueFound = false;
1178: for (ScalarPropertyAccessor accessor : extensionPropertySet
1179: .getScalarProperties1()) {
1180: // TODO: Complete this implementation, if it is worth it. On the other hand, perhaps it
1181: // is not unacceptable to always create extensions.
1182: if (true) {
1183: // if (!JMoneyPlugin.areEqual(getValue(rs, accessor), accessor.getDefaultValue())) {
1184: nonDefaultExtensions
1185: .add(extensionPropertySet);
1186: continue outerLoop;
1187: }
1188: }
1189: for (ListPropertyAccessor accessor : extensionPropertySet
1190: .getListProperties1()) {
1191: // For time being, always create an extension if there is a list property in it.
1192: nonDefaultExtensions.add(extensionPropertySet);
1193: continue outerLoop;
1194: }
1195: }
1196:
1197: return nonDefaultExtensions;
1198: }
1199:
1200: };
1201:
1202: E extendableObject = propertySet.constructImplementationObject(
1203: objectKey, listKey, values);
1204:
1205: setMaterializedObject(getBasemostPropertySet(propertySet),
1206: objectKey.getRowId(), extendableObject);
1207:
1208: return extendableObject;
1209: }
1210:
1211: /**
1212: * Given a property, return the name of the database column that holds the
1213: * values of the property.
1214: *
1215: * Unless the property is an extension property, we simply use the
1216: * unqualified name. That must be unique within the property set and so the
1217: * column name will be unique within the table. If the property is an
1218: * extension property, however, then the name must be fully qualified
1219: * because two plug-ins may use the same name for an extension property and
1220: * these two properties cannot have the same column name as they are in the
1221: * same table. The the dots are replaced by underscores to keep the names
1222: * SQL compliant.
1223: *
1224: * @param propertyAccessor
1225: * @return an SQL compliant column name, guaranteed to be unique within the
1226: * table
1227: */
1228: String getColumnName(ScalarPropertyAccessor<?> propertyAccessor) {
1229: if (propertyAccessor.getPropertySet().isExtension()) {
1230: return propertyAccessor.getName().replace('.', '_');
1231: } else {
1232: return propertyAccessor.getLocalName();
1233: }
1234: }
1235:
1236: /**
1237: * Materialize an object from a row of data.
1238: * <P>
1239: * This version of this method should be called when the caller does not
1240: * know the parent of the object to be materialized. This is the situation
1241: * if one object has a reference to another object (ie. the referenced
1242: * object is not in a list property) and we need to materialize the
1243: * referenced object.
1244: * <P>
1245: * The parent key is built from data in the row.
1246: *
1247: * @param rs
1248: * @param propertySet
1249: * @param key
1250: * @return
1251: * @throws SQLException
1252: */
1253: <E extends ExtendableObject> E materializeObject(ResultSet rs,
1254: ExtendablePropertySet<E> propertySet, IDatabaseRowKey key)
1255: throws SQLException {
1256: /*
1257: * We need to obtain the key for the containing list. We do this by
1258: * creating one from the data in the result set.
1259: */
1260: DatabaseListKey<? super E> parentListKey = buildParentKey(rs,
1261: propertySet);
1262:
1263: ListKey<? super E> listKey = constructListKey(parentListKey);
1264:
1265: E extendableObject = materializeObject(rs, propertySet, key,
1266: listKey);
1267:
1268: return extendableObject;
1269: }
1270:
1271: /**
1272: * Helper method.
1273: */
1274: <E extends ExtendableObject> ListKey<E> constructListKey(
1275: DatabaseListKey<E> parentListKey) {
1276: return new ListKey<E>(parentListKey.parentKey,
1277: parentListKey.listPropertyAccessor);
1278: }
1279:
1280: /**
1281: * Used for returning result from following method, as Java does not allow methods to
1282: * return more than a single value.
1283: */
1284: static class DatabaseListKey<E extends ExtendableObject> {
1285: IDatabaseRowKey parentKey;
1286: ListPropertyAccessor<E> listPropertyAccessor;
1287:
1288: public static <E extends ExtendableObject> DatabaseListKey<E> construct(
1289: IDatabaseRowKey parentKey,
1290: ListPropertyAccessor<E> listProperty) {
1291: return new DatabaseListKey<E>(parentKey, listProperty);
1292: }
1293:
1294: public DatabaseListKey(IDatabaseRowKey parentKey,
1295: ListPropertyAccessor<E> listPropertyAccessor) {
1296: this .parentKey = parentKey;
1297: this .listPropertyAccessor = listPropertyAccessor;
1298: }
1299: }
1300:
1301: /*
1302: * We need to obtain the key for the parent object. We do this by
1303: * creating one from the data in the result set.
1304: *
1305: * The property set of the parent object may not be known without
1306: * looking at the row data. For example, the parent of an account may be
1307: * another account (if the account is a sub-account) or may be the
1308: * session.
1309: */
1310: <E extends ExtendableObject> DatabaseListKey<? super E> buildParentKey(
1311: ResultSet rs, ExtendablePropertySet<E> propertySet)
1312: throws SQLException {
1313: /*
1314: * A column exists in this table for each list which can contain objects
1315: * of this type. Only one of these columns can be non-null so we must
1316: * find that column. The value of that column will be the integer id of
1317: * the parent.
1318: *
1319: * An optimization allows the column to be absent when the parent
1320: * object is the session object (as only one session object may exist).
1321: *
1322: * For each list that may contain this object, see if the appropriate
1323: * column is non-null.
1324: */
1325: ParentList matchingParentList = null;
1326: int parentId = -1;
1327: boolean nonNullValueFound = false;
1328:
1329: ExtendablePropertySet<? super E> propertySet2 = propertySet;
1330: do {
1331: Vector<ParentList> list = tablesMap.get(propertySet2);
1332:
1333: /*
1334: * Find all properties in any property set that are a list of objects
1335: * with the type as this property set. A column must exist in this table
1336: * for each such property that exists in another property set.
1337: */
1338: for (ParentList parentList : list) {
1339: parentId = rs.getInt(parentList.getColumnName());
1340: if (!rs.wasNull()) {
1341: matchingParentList = parentList;
1342: nonNullValueFound = true;
1343: break;
1344: }
1345: }
1346: propertySet2 = propertySet2.getBasePropertySet();
1347: } while (propertySet2 != null);
1348:
1349: DatabaseListKey<? super E> listKey;
1350:
1351: if (!nonNullValueFound) {
1352: /*
1353: * A database optimization causes no parent column to exist for the
1354: * case where the parent object is the session.
1355: */
1356: ListPropertyAccessor<? super E> listProperty;
1357: if (Commodity.class.isAssignableFrom(propertySet
1358: .getImplementationClass())) {
1359: listProperty = (ListPropertyAccessor<? super E>) SessionInfo
1360: .getCommoditiesAccessor();
1361: } else if (Account.class.isAssignableFrom(propertySet
1362: .getImplementationClass())) {
1363: listProperty = (ListPropertyAccessor<? super E>) SessionInfo
1364: .getAccountsAccessor();
1365: } else if (Transaction.class.isAssignableFrom(propertySet
1366: .getImplementationClass())) {
1367: listProperty = (ListPropertyAccessor<? super E>) SessionInfo
1368: .getTransactionsAccessor();
1369: } else {
1370: throw new RuntimeException("bad case");
1371: }
1372: listKey = DatabaseListKey.construct(sessionKey,
1373: listProperty);
1374: } else {
1375: IDatabaseRowKey parentKey = new ObjectKey(parentId,
1376: matchingParentList.parentPropertySet, this );
1377: ListPropertyAccessor<? super E> listProperty = matchingParentList.listProperty;
1378: listKey = DatabaseListKey
1379: .construct(parentKey, listProperty);
1380: }
1381:
1382: return listKey;
1383: }
1384:
1385: class ColumnInfo {
1386: String columnName;
1387: String columnDefinition;
1388: ExtendablePropertySet<?> foreignKeyPropertySet = null;
1389: ColumnNature nature;
1390: }
1391:
1392: private enum ColumnNature {
1393: PARENT, SCALAR_PROPERTY
1394: }
1395:
1396: /**
1397: * Build a list of columns that we must have in the table that
1398: * holds the data for a particular property set.
1399: * <P>
1400: * The list will depend on the set of installed plug-ins.
1401: * <P>
1402: * The "_ID" column is required in all tables as a primary
1403: * key and is not returned by this method.
1404: *
1405: * @return A Vector containing objects of class
1406: * <code>ColumnInfo</code>.
1407: */
1408: private Vector<ColumnInfo> buildColumnList(
1409: ExtendablePropertySet<?> propertySet) {
1410: Vector<ColumnInfo> result = new Vector<ColumnInfo>();
1411:
1412: /*
1413: * The parent column requirements depend on which other property sets
1414: * have lists. A parent column exists in the table for property set A
1415: * for each list property (in any property set) that contains elements
1416: * of type A.
1417: *
1418: * If there is a single place where the property set is listed and that
1419: * place is in the session object (or an extension thereof) then no
1420: * parent column is necessary because there is only one session object.
1421: *
1422: * If there is a single place where the property set is listed and that
1423: * place is not in the session object (or an extension thereof) then a
1424: * parent column is created with the name being the same as the fully
1425: * qualified name of the property that lists these objects. The column
1426: * will not allow null values.
1427: *
1428: * If there are multiple places where a property set is listed then a
1429: * column is created for each place (but if one of the places is the
1430: * session object that no column is created for that place). The columns
1431: * will allow null values. At most one of the columns may be non-null.
1432: * If all the columns are null then the parent is the session object.
1433: * The names of the columns are the fully qualified names of the
1434: * properties that list these objects.
1435: */
1436: Vector<ParentList> list = tablesMap.get(propertySet);
1437:
1438: /*
1439: * Find all properties in any property set that are a list of objects
1440: * with the element type as this property set. A column must exist in
1441: * this table for each such property that exists in another property
1442: * set.
1443: *
1444: * These columns default to NULL so that, when objects are inserted,
1445: * we need only to set the parent id into the appropriate column and
1446: * not worry about the other parent columns.
1447: *
1448: * If there is only one list property of a type in which an object could
1449: * be placed, then we could make the column NOT NULL. However, we would
1450: * need more code to adjust the database schema (altering columns to be
1451: * NULL or NOT NULL) if plug-ins are added to create other lists in which
1452: * the object could be placed.
1453: */
1454: for (ParentList parentList : list) {
1455: ColumnInfo info = new ColumnInfo();
1456: info.nature = ColumnNature.PARENT;
1457: info.columnName = parentList.getColumnName();
1458: info.columnDefinition = "INT DEFAULT NULL NULL";
1459: info.foreignKeyPropertySet = parentList.parentPropertySet;
1460: result.add(info);
1461: }
1462:
1463: // The columns for each property in this property set
1464: // (including the extension property sets).
1465: for (ScalarPropertyAccessor<?> propertyAccessor : propertySet
1466: .getScalarProperties2()) {
1467: ColumnInfo info = new ColumnInfo();
1468:
1469: info.nature = ColumnNature.SCALAR_PROPERTY;
1470: info.columnName = getColumnName(propertyAccessor);
1471:
1472: Class<?> valueClass = propertyAccessor
1473: .getClassOfValueObject();
1474: if (valueClass == Integer.class) {
1475: info.columnDefinition = "INT";
1476: } else if (valueClass == Long.class) {
1477: info.columnDefinition = "BIGINT";
1478: } else if (valueClass == Character.class) {
1479: info.columnDefinition = "CHAR";
1480: } else if (valueClass == Boolean.class) {
1481: info.columnDefinition = "BIT";
1482: } else if (valueClass == String.class) {
1483: /*
1484: * HSQLDB is fine with just VARCHAR, but MS SQL will default the
1485: * maximum length to 1 which is obviously no good. We therefore
1486: * specify the maximum length as 255.
1487: */
1488: info.columnDefinition = "VARCHAR(255)";
1489: } else if (valueClass == Date.class) {
1490: /*
1491: * Although some databases support date types that may be
1492: * better suited for dates without times (MS SQL has SMALLDATETIME
1493: * and HSQLDB has DATE), only DATETIME is standard and should
1494: * be supported by all JDBC implementations.
1495: */
1496: info.columnDefinition = "DATETIME";
1497: } else if (ExtendableObject.class
1498: .isAssignableFrom(valueClass)) {
1499: info.columnDefinition = "INT";
1500:
1501: // This call does not work. The method works only when the class
1502: // is a class of an actual object and only non-derivable property
1503: // sets are returned.
1504: // info.foreignKeyPropertySet = PropertySet.getPropertySet(valueClass);
1505:
1506: // This works.
1507: // The return type from a getter for a property that is a reference
1508: // to an extendable object must be the getter interface.
1509: info.foreignKeyPropertySet = null;
1510: for (ExtendablePropertySet<?> propertySet2 : PropertySet
1511: .getAllExtendablePropertySets()) {
1512: if (propertySet2.getImplementationClass() == valueClass) {
1513: info.foreignKeyPropertySet = propertySet2;
1514: break;
1515: }
1516: }
1517: } else {
1518: // All other types are stored as a string by
1519: // using the String constructor and
1520: // the toString method for conversion.
1521:
1522: // HSQL is fine with just VARCHAR, but MS SQL will default
1523: // the maximum length to 1 which is obviously no good.
1524: info.columnDefinition = "VARCHAR(255)";
1525: }
1526:
1527: // If the property is an extension property then we set
1528: // a default value. This saves us from having to set default
1529: // value in every insert statement and is a better solution
1530: // if other applications (outside JMoney) access the database.
1531:
1532: if (propertyAccessor.getPropertySet().isExtension()) {
1533: Object defaultValue = propertyAccessor
1534: .getDefaultValue();
1535: info.columnDefinition += " DEFAULT "
1536: + valueToSQLText(defaultValue);
1537: }
1538:
1539: if (propertyAccessor.isNullAllowed()) {
1540: info.columnDefinition += " NULL";
1541: } else {
1542: info.columnDefinition += " NOT NULL";
1543: }
1544: result.add(info);
1545: }
1546:
1547: /*
1548: * If the property set is a derivable property set and is the base-most
1549: * property set then we must have a column called _PROPERTY_SET. This
1550: * column contains the id of the actual (non-derivable) property set of
1551: * this object. This column is required because otherwise we would not
1552: * know which further tables need to be joined to get the complete set
1553: * of properties with which we can construct the object.
1554: */
1555: if (propertySet.getBasePropertySet() == null
1556: && propertySet.isDerivable()) {
1557: ColumnInfo info = new ColumnInfo();
1558: info.columnName = "_PROPERTY_SET";
1559: // 200 should be enough for property set ids.
1560: info.columnDefinition = "VARCHAR(200) NOT NULL";
1561: result.add(info);
1562: }
1563:
1564: return result;
1565: }
1566:
1567: private static String[] tableOnlyType = new String[] { "TABLE" };
1568:
1569: private void traceResultSet(ResultSet rs) {
1570: if (JDBCDatastorePlugin.DEBUG) {
1571: try {
1572: String x = "";
1573: ResultSetMetaData rsmd = rs.getMetaData();
1574: int cols = rsmd.getColumnCount();
1575: for (int i = 1; i <= cols; i++) {
1576: x += rsmd.getColumnLabel(i) + ", ";
1577: }
1578: System.out.println(x);
1579:
1580: while (rs.next()) {
1581: x = "";
1582: for (int i = 1; i <= cols; i++) {
1583: x += rs.getString(i) + ", ";
1584: }
1585: System.out.println(x);
1586: }
1587: } catch (Exception SQLException) {
1588: throw new RuntimeException("database error");
1589: }
1590: System.out.println("");
1591: }
1592: }
1593:
1594: /**
1595: * Check the tables and columns in the database.
1596: * If a required table does not exist it will be created.
1597: * If a table exists but it does not contain all the required
1598: * columns, then the required columns will be added to the table.
1599: * <P>
1600: * There may be additional tables and there may be
1601: * additional columns in the required tables. These are
1602: * ignored and the data in them are left alone.
1603: */
1604: private void checkDatabase(Connection con, Statement stmt)
1605: throws SQLException {
1606:
1607: DatabaseMetaData dmd = con.getMetaData();
1608:
1609: for (ExtendablePropertySet<?> propertySet : PropertySet
1610: .getAllExtendablePropertySets()) {
1611: String tableName = propertySet.getId().replace('.', '_');
1612:
1613: // Check that the table exists.
1614: ResultSet tableResultSet = dmd.getTables(null, null,
1615: tableName.toUpperCase(), tableOnlyType);
1616:
1617: if (tableResultSet.next()) {
1618: Vector<ColumnInfo> columnInfos = buildColumnList(propertySet);
1619: for (ColumnInfo columnInfo : columnInfos) {
1620: ResultSet columnResultSet = dmd.getColumns(null,
1621: null, tableName.toUpperCase(),
1622: columnInfo.columnName);
1623: if (columnResultSet.next()) {
1624: // int dataType = columnResultSet.getInt("DATA_TYPE");
1625: // String typeName = columnResultSet.getString("TYPE_NAME");
1626: // TODO: Check that the column information is
1627: // correct. Display a fatal error if it is not.
1628: } else {
1629: // The column does not exist so we add it.
1630: String sql = "ALTER TABLE " + tableName
1631: + " ADD \"" + columnInfo.columnName
1632: + "\" " + columnInfo.columnDefinition;
1633: System.out.println(sql);
1634: stmt.execute(sql);
1635: }
1636: columnResultSet.close();
1637: }
1638: } else {
1639: // Table does not exist, so create it.
1640: createTable(propertySet, stmt);
1641: }
1642:
1643: tableResultSet.close();
1644: }
1645:
1646: /*
1647: * Having ensured that all the tables exist, now create the foreign key
1648: * constraints. This must be done in a second pass because otherwise we
1649: * might try to create a foreign key constraint before the foreign key
1650: * has been created.
1651: */
1652: for (ExtendablePropertySet<?> propertySet : PropertySet
1653: .getAllExtendablePropertySets()) {
1654: String tableName = propertySet.getId().replace('.', '_');
1655:
1656: /*
1657: * Check the foreign keys in derived tables that point to the base
1658: * table row.
1659: */
1660: if (propertySet.getBasePropertySet() != null) {
1661: String primaryTableName = propertySet
1662: .getBasePropertySet().getId().replace('.', '_');
1663: checkForeignKey(dmd, stmt, tableName, "_ID",
1664: primaryTableName, true);
1665: }
1666:
1667: /*
1668: * Check the foreign keys for columns that reference other objects.
1669: *
1670: * These may be:
1671: * - objects that contain references (as scalar properties) to
1672: * other objects.
1673: * - the columns that contain the id of the parent object
1674: */
1675: Vector<ColumnInfo> columnInfos = buildColumnList(propertySet);
1676: for (ColumnInfo columnInfo : columnInfos) {
1677: if (columnInfo.foreignKeyPropertySet != null) {
1678: String primaryTableName = columnInfo.foreignKeyPropertySet
1679: .getId().replace('.', '_');
1680: // TODO: check if HSQL allows ON CASCADE DELETE with a self-referencing table
1681: checkForeignKey(dmd, stmt, tableName,
1682: columnInfo.columnName, primaryTableName,
1683: false/*columnInfo.nature == ColumnNature.PARENT*/);
1684: }
1685: }
1686: }
1687: }
1688:
1689: /**
1690: * Checks that the given foreign key exists. If it does not, it is
1691: * added.
1692: * <P>
1693: * There may be other foreign keys between the two tables. That is
1694: * ok.
1695: *
1696: * @param stmt
1697: * @param dmd
1698: * @param tableName
1699: * @param columnName
1700: * @param primaryTableName
1701: */
1702: private void checkForeignKey(DatabaseMetaData dmd, Statement stmt,
1703: String tableName, String columnName,
1704: String primaryTableName, boolean onDeleteCascade)
1705: throws SQLException {
1706: ResultSet columnResultSet2 = dmd.getCrossReference(null, null,
1707: primaryTableName.toUpperCase(), null, null, tableName
1708: .toUpperCase());
1709: traceResultSet(columnResultSet2);
1710: columnResultSet2.close();
1711:
1712: ResultSet columnResultSet = dmd.getCrossReference(null, null,
1713: primaryTableName.toUpperCase(), null, null, tableName
1714: .toUpperCase());
1715: try {
1716: while (columnResultSet.next()) {
1717:
1718: if (columnResultSet.getString("FKCOLUMN_NAME")
1719: .equalsIgnoreCase(columnName)) {
1720:
1721: // TODO: There seems to be a mixture of _id and _ID. SQL is not case sensitive
1722: // so do a case insensitive comparison.
1723: if (columnResultSet.getString("PKCOLUMN_NAME")
1724: .equalsIgnoreCase("_ID")) {
1725: // Foreign key found, so we are done.
1726: return;
1727: } else {
1728: throw new RuntimeException(
1729: "The database schema is invalid. "
1730: + "Table "
1731: + tableName.toUpperCase()
1732: + " contains a foreign key column called "
1733: + columnName
1734: + " but it is not constrained to primary key _ID in table "
1735: + primaryTableName
1736: .toUpperCase()
1737: + " as it should be.");
1738: }
1739: }
1740: }
1741:
1742: // The foreign key constraint does not exist so we add it.
1743: String sql = "ALTER TABLE " + tableName
1744: + " ADD FOREIGN KEY (\"" + columnName
1745: + "\") REFERENCES " + primaryTableName + "(_ID)";
1746:
1747: if (onDeleteCascade) {
1748: sql += " ON DELETE CASCADE";
1749: }
1750:
1751: System.out.println(sql);
1752: stmt.execute(sql);
1753: } finally {
1754: columnResultSet.close();
1755: }
1756: }
1757:
1758: /**
1759: * Create a table. This method should be called when
1760: * a new database is being initialized or when a new
1761: * table is needed because a new extendable property
1762: * set has been added.
1763: *
1764: * This method does not create any foreign keys. This is because
1765: * the referenced table may be yet exist. The caller must create
1766: * the foreign keys in a second pass.
1767: *
1768: * @param propertySet The property set whose table is to
1769: * be created. This property set must not be an
1770: * extension property set. (No tables exist for extension
1771: * property sets. Extension property sets are supported
1772: * by adding columns to the tables for the property sets
1773: * which they extend).
1774: * @param stmt A <code>Statement</code> object
1775: * that is to be used by this method
1776: * to submit the 'CREATE TABLE' command.
1777: * @throws SQLException
1778: */
1779: void createTable(ExtendablePropertySet<?> propertySet,
1780: Statement stmt) throws SQLException {
1781: /*
1782: * The _ID column is always a primary key. However, it has automatically
1783: * generated values only for the base tables. Derived tables contain ids
1784: * that match the base table.
1785: *
1786: * HSQLDB requires only IDENTITY be specified for the _ID column and it
1787: * is by default a primary key. MS SQL requires that PRIMARY KEY be
1788: * specifically specified. HSQLDB allows PRIMARY KEY provided it appears
1789: * after IDENTITY. We can keep both databases happy by specifically
1790: * including PRIMARY KEY after IDENTITY.
1791: */
1792: String sql = "CREATE TABLE "
1793: + propertySet.getId().replace('.', '_') + " (_id INT";
1794:
1795: if (propertySet.getBasePropertySet() == null) {
1796: sql += " IDENTITY";
1797: }
1798:
1799: sql += " PRIMARY KEY";
1800:
1801: Vector<ColumnInfo> columnInfos = buildColumnList(propertySet);
1802: for (ColumnInfo columnInfo : columnInfos) {
1803: sql += ", \"" + columnInfo.columnName + "\" "
1804: + columnInfo.columnDefinition;
1805: }
1806: sql += ")";
1807:
1808: System.out.println(sql);
1809: stmt.execute(sql);
1810: }
1811:
1812: /* (non-Javadoc)
1813: * @see net.sf.jmoney.model2.ISessionManager#hasEntries(net.sf.jmoney.model2.Account)
1814: */
1815: @Override
1816: public boolean hasEntries(Account account) {
1817: // TODO: improve efficiency of this??????
1818: // or should hasEntries be removed altogether and make caller
1819: // call getEntries().isEmpty() ??????
1820: // As long as collections are not being copied unneccessarily,
1821: // this is probably better.
1822: return !(new AccountEntriesList(this , (IDatabaseRowKey) account
1823: .getObjectKey()).isEmpty());
1824: }
1825:
1826: @Override
1827: public Collection<Entry> getEntries(Account account) {
1828: return new AccountEntriesList(this , (IDatabaseRowKey) account
1829: .getObjectKey());
1830: }
1831:
1832: /**
1833: * @see net.sf.jmoney.model2.IEntryQueries#sumOfAmounts(net.sf.jmoney.model2.CurrencyAccount, java.util.Date, java.util.Date)
1834: */
1835: public long sumOfAmounts(CurrencyAccount account, Date fromDate,
1836: Date toDate) {
1837: IDatabaseRowKey proxy = (IDatabaseRowKey) account
1838: .getObjectKey();
1839:
1840: try {
1841: String sql = "SELECT SUM(amount) FROM net_sf_jmoney_entry, net_sf_jmoney_transaction"
1842: + " WHERE account = "
1843: + proxy.getRowId()
1844: + " AND date >= "
1845: + fromDate
1846: + " AND date <= "
1847: + toDate;
1848: System.out.println(sql);
1849: ResultSet rs = getReusableStatement().executeQuery(sql);
1850: rs.next();
1851: return rs.getLong(0);
1852: } catch (SQLException e) {
1853: throw new RuntimeException("SQL statement failed");
1854: }
1855: }
1856:
1857: /* (non-Javadoc)
1858: * @see net.sf.jmoney.model2.IEntryQueries#getSortedReadOnlyCollection(net.sf.jmoney.model2.CapitalAccount, net.sf.jmoney.model2.PropertyAccessor, boolean)
1859: */
1860: public Collection<Entry> getSortedEntries(CapitalAccount account,
1861: PropertyAccessor sortProperty, boolean descending) {
1862: // TODO implement this.
1863: throw new RuntimeException("must implement");
1864: }
1865:
1866: /* (non-Javadoc)
1867: * @see net.sf.jmoney.model2.IEntryQueries#getEntryTotalsByMonth(int, int, int, boolean)
1868: */
1869: public long[] getEntryTotalsByMonth(CapitalAccount account,
1870: int startYear, int startMonth, int numberOfMonths,
1871: boolean includeSubAccounts) {
1872: IDatabaseRowKey proxy = (IDatabaseRowKey) account
1873: .getObjectKey();
1874:
1875: String startDateString = '\''
1876: + new Integer(startYear).toString() + "-"
1877: + new Integer(startMonth).toString() + "-"
1878: + new Integer(1).toString() + '\'';
1879:
1880: int endMonth = startMonth + numberOfMonths;
1881: int years = (endMonth - 1) / 12;
1882: endMonth -= years * 12;
1883: int endYear = startYear + years;
1884:
1885: String endDateString = '\'' + new Integer(endYear).toString()
1886: + "-" + new Integer(endMonth).toString() + "-"
1887: + new Integer(1).toString() + '\'';
1888:
1889: String accountList = "(" + proxy.getRowId();
1890: if (includeSubAccounts) {
1891: ArrayList<Integer> accountIds = new ArrayList<Integer>();
1892: addEntriesFromSubAccounts(account, accountIds);
1893: for (Integer accountId : accountIds) {
1894: accountList += "," + accountId;
1895: }
1896: }
1897: accountList += ")";
1898:
1899: try {
1900: String sql = "SELECT SUM(amount), DateSerial(Year(date),Month(date),1) FROM net_sf_jmoney_entry, net_sf_jmoney_transaction"
1901: + " GROUP BY DateSerial(Year(date),Month(date),1)"
1902: + " WHERE account IN "
1903: + accountList
1904: + " AND date >= "
1905: + startDateString
1906: + " AND date < "
1907: + endDateString
1908: + " ORDER BY DateSerial(Year(date),Month(date),1)";
1909: System.out.println(sql);
1910: ResultSet rs = getReusableStatement().executeQuery(sql);
1911:
1912: long[] totals = new long[numberOfMonths];
1913: for (int i = 0; i < numberOfMonths; i++) {
1914: rs.next();
1915: totals[i] = rs.getLong(0);
1916: }
1917: return totals;
1918: } catch (SQLException e) {
1919: throw new RuntimeException("SQL statement failed");
1920: }
1921: }
1922:
1923: private void addEntriesFromSubAccounts(CapitalAccount account,
1924: ArrayList<Integer> accountIds) {
1925: for (CapitalAccount subAccount : account
1926: .getSubAccountCollection()) {
1927: IDatabaseRowKey proxy = (IDatabaseRowKey) subAccount
1928: .getObjectKey();
1929: accountIds.add(proxy.getRowId());
1930: addEntriesFromSubAccounts(subAccount, accountIds);
1931: }
1932: }
1933:
1934: @Override
1935: public void startTransaction() {
1936: try {
1937: connection.setAutoCommit(false);
1938: } catch (SQLException e) {
1939: // TODO Auto-generated catch block
1940: e.printStackTrace();
1941: }
1942: }
1943:
1944: @Override
1945: public void commitTransaction() {
1946: try {
1947: connection.commit();
1948: } catch (SQLException e) {
1949: // TODO We need a mechanism to log and report errors
1950: e.printStackTrace();
1951: }
1952:
1953: /*
1954: * Note that we want to turn on auto-commit even if
1955: * the above commit failed.
1956: */
1957: try {
1958: connection.setAutoCommit(true);
1959: } catch (SQLException e) {
1960: // TODO We need a mechanism to log and report errors
1961: e.printStackTrace();
1962: }
1963: }
1964:
1965: /**
1966: * This is a helper method to get the base-most property set for
1967: * a given property set.
1968: *
1969: * @param propertySet
1970: * @return
1971: */
1972: public static <E extends ExtendableObject> ExtendablePropertySet<? super E> getBasemostPropertySet(
1973: ExtendablePropertySet<E> propertySet) {
1974: ExtendablePropertySet<? super E> basePropertySet = propertySet;
1975: while (basePropertySet.getBasePropertySet() != null) {
1976: basePropertySet = basePropertySet.getBasePropertySet();
1977: }
1978: return basePropertySet;
1979: }
1980: }
|