001: package net.xoetrope.optional.data.sql;
002:
003: import java.io.StringReader;
004: import java.sql.DatabaseMetaData;
005: import java.sql.ResultSet;
006: import java.sql.ResultSetMetaData;
007: import java.sql.SQLException;
008: import java.sql.Statement;
009: import java.sql.Timestamp;
010: import java.util.Enumeration;
011: import java.util.Vector;
012:
013: import net.xoetrope.debug.DebugLogger;
014: import net.xoetrope.optional.service.ServiceProxy;
015: import net.xoetrope.optional.service.XRouteManager;
016: import net.xoetrope.optional.service.XServiceProxyNotFoundException;
017: import net.xoetrope.xml.XmlElement;
018: import net.xoetrope.xml.XmlSource;
019: import net.xoetrope.xui.build.BuildProperties;
020:
021: /**
022: * An extended database connection that allows integration of a local database
023: * and replication to/from a remote database.
024: * <p>Copyright (c) Xoetrope Ltd. 2001-2004</p>
025: * $Revision: 1.3 $
026: */
027: public class DataConnection {
028: private static boolean hasTimeStamps = false;
029: private ConnectionObject connObj = null;
030: private String connName;
031: private ServiceProxy remoteDataService;
032: private static boolean lookupRemoteDataService = true;
033:
034: /**
035: * Construct a new connection
036: * @param name the connection name
037: */
038: public DataConnection(String name) {
039: connName = name;
040: try {
041: if (lookupRemoteDataService)
042: remoteDataService = XRouteManager.getInstance()
043: .getRoute("replicationService", null);
044: } catch (XServiceProxyNotFoundException ex) {
045: lookupRemoteDataService = false;
046: }
047: }
048:
049: /**
050: * Construct a new connection
051: * @param name the connection name
052: * @param replicate true to attempt use of the replication service
053: */
054: public DataConnection(String name, boolean replicate) {
055: connName = name;
056: try {
057: if (replicate && lookupRemoteDataService)
058: remoteDataService = XRouteManager.getInstance()
059: .getRoute("replicationService", null);
060: } catch (XServiceProxyNotFoundException ex) {
061: lookupRemoteDataService = false;
062: }
063: }
064:
065: /**
066: * Execute a SQL update statement.
067: * @param sql the SQL statement to be executed
068: * @return the result of the update query
069: * @throws Exception
070: */
071: public int executeUpdate(String sql) throws Exception {
072: try {
073: // Get a connection from the connection pool
074: connObj = NamedConnectionManager.getInstance()
075: .getConnection(connName);
076:
077: // Not sure if these are needed here
078: // checkLocalDatabase( "FROM xSysServerTimestamps", connName, DatabaseTable.PARANOID_UPDATE );
079: // ResultSet rs = checkLocalDatabase( sql, connName, DatabaseTable.NORMAL_UPDATE );
080: Statement statement = connObj.connection.createStatement();
081: statement.setEscapeProcessing(true);
082: return statement.executeUpdate(sql);
083: } catch (Exception e) {
084: throw e;
085: } finally {
086: // Return the connection to the pool
087: connObj.expireLease();
088: }
089: }
090:
091: /**
092: * Execute a SQL query
093: * @param sql the SQL statement to be executed
094: * @return the result of the query statement
095: * @throws SQLException
096: */
097: public ResultSet executeQuery(String sql) throws SQLException {
098: return executeQuery(sql, connName, false);
099: }
100:
101: /**
102: * Execute a SQL query.
103: * @param sql the SQL statement
104: * @param connName the connection name
105: * @param writable true to attempt to create an updatable resultset
106: * @return the results of the query
107: * @throws SQLException
108: */
109: public ResultSet executeQuery(String sql, String connName,
110: boolean writable) throws SQLException {
111: try {
112: // Get a connection from the connection pool
113: connObj = NamedConnectionManager.getInstance()
114: .getConnection(connName);
115:
116: checkLocalDatabase("FROM xSysServerTimestamps", connName,
117: DatabaseTable.PARANOID_UPDATE);
118: ResultSet rs = checkLocalDatabase(sql, connName,
119: DatabaseTable.NORMAL_UPDATE);
120:
121: // A non cached database might return a result set directly
122: if (rs != null)
123: return rs;
124:
125: Statement statement = null;
126: if (writable) {
127: try {
128: statement = connObj.connection.createStatement(
129: ResultSet.TYPE_SCROLL_INSENSITIVE,
130: ResultSet.CONCUR_UPDATABLE);
131: } catch (SQLException ex) {
132: }
133: }
134:
135: if (statement == null)
136: statement = connObj.connection.createStatement(
137: ResultSet.TYPE_SCROLL_INSENSITIVE,
138: ResultSet.CONCUR_READ_ONLY);
139:
140: statement.setEscapeProcessing(true);
141: statement.setFetchDirection(ResultSet.FETCH_FORWARD);
142:
143: if (BuildProperties.DEBUG)
144: DebugLogger.trace(sql);
145: return statement.executeQuery(sql);
146: } catch (SQLException e) {
147: System.err.println(e.getMessage());
148: throw e;
149: } finally {
150: // Return the connection to the pool
151: if (connObj != null)
152: connObj.expireLease();
153: }
154: }
155:
156: /**
157: * Get a list of the fields in a table.
158: * @param tableName the table name
159: * @return the meta data result set
160: */
161: public ResultSet getMetaData(String tableName) {
162: try {
163: // Get a connection from the connection pool
164: connObj = NamedConnectionManager.getInstance()
165: .getConnection(connName);
166: DatabaseMetaData dbmd = connObj.connection.getMetaData();
167: return dbmd.getColumns(null, null, tableName, null);
168: } catch (Exception e) {
169: e.printStackTrace();
170: } finally {
171: // Return the connection to the pool
172: connObj.expireLease();
173: }
174: return null;
175: }
176:
177: /**
178: * Borrow the connection object
179: * @return the connection object
180: */
181: ConnectionObject borrowConnection() throws SQLException {
182: return connObj = NamedConnectionManager.getInstance()
183: .getConnection(connName);
184: }
185:
186: /**
187: * Return the connection object to the pool
188: */
189: void returnConnection() {
190: connObj.expireLease();
191: }
192:
193: /**
194: * Check that the local database definition is up to date.
195: * @param sql a sql query
196: * @param connName the connection name
197: * @param updateStrategy the strategy used to update the database
198: * @return a result set for a non cachable query or if the data has been
199: * cached and the result set contains the subset of data matches the query.
200: * Null is returned if the the server returns the full dataset and the query
201: * needs to be run against the local copy.
202: */
203: private ResultSet checkLocalDatabase(String sql, String connName,
204: int updateStrategy) {
205: ResultSet rs = null;
206: try {
207: // If there is no route to the remote service don't try checking for updates
208: if (remoteDataService == null)
209: return null;
210:
211: // Extract the table name from the SQL
212: int pos = sql.indexOf("FROM ") + 5;
213: int endPos = sql.indexOf(' ', pos);
214: String tableName;
215: if (endPos < 0)
216: tableName = sql.substring(pos);
217: else
218: tableName = sql.substring(pos, endPos);
219: String[] argNames = new String[3];
220: Object[] argValues = new Object[3];
221: argNames[0] = "table";
222: argNames[1] = "key";
223: argNames[2] = "ts";
224: argValues[0] = tableName;
225:
226: // Get a connection from the connection pool
227: connObj = NamedConnectionManager.getInstance()
228: .getConnection(connName);
229:
230: // If the database does not exist locally create it.
231: boolean createTable = false;
232: long maxPseudoId = 0l;
233: try {
234: DatabaseMetaData dbmd = connObj.connection
235: .getMetaData();
236: ResultSet localMetaData = dbmd.getTables(null, null,
237: tableName.toUpperCase(), null);
238: if (localMetaData.next()) {
239: createTable = false;
240: if (tableName.compareTo("xSysServerTimestamps") != 0) {
241: // The Pseudo ID is an auto incrementing field used to uniquely identify a row.
242: ResultSet rsMax = doQuery("SELECT MAX(PSEUDOID) FROM "
243: + tableName);
244: rsMax.next();
245: maxPseudoId = rsMax.getLong(1);
246: }
247: } else
248: createTable = true;
249: } catch (Exception ex) {
250: createTable = true;
251: }
252:
253: if (createTable) {
254: // Get the remote Data Description Language descriptions and inject it into the local database.
255: String createSQL = (String) remoteDataService.call(
256: "getDDL", argNames, argValues);
257: doQuery(createSQL);
258: }
259:
260: argValues[2] = getLocalTimestamp(tableName);
261: long localTime = ((Timestamp) argValues[2]).getTime();
262: long serverTime = getServerTimestamp(tableName).getTime();
263: if (updateStrategy == DatabaseTable.PARANOID_UPDATE)
264: localTime = 0l;
265: else if (updateStrategy == DatabaseTable.OPTIMISTIC_UPDATE) {
266: if (localTime > 0l)
267: localTime = Long.MAX_VALUE;
268: }
269:
270: /**
271: * @todo propogate local updates to the server. If records have been added
272: * then they will need to be identified and saved and then their pseudo IDs
273: * will need to be fixed up.
274: */
275:
276: // Is the local data up to date
277: if ((serverTime == 0) || (localTime < serverTime)) {
278:
279: boolean localDataDropped = false;
280:
281: // Fetch several rows at a time but maybe not all. The complete attribute
282: // indicates if more records are to be sent
283: boolean isComplete = false;
284: Timestamp timestamp = null;
285: while (!isComplete) {
286: // Request the data from the server side
287: String xml = (String) remoteDataService.call(
288: "getData", argNames, argValues);
289: XmlSource src = new XmlSource();
290: XmlElement rsRemote = src
291: .read(new StringReader(xml));
292: timestamp = Timestamp.valueOf((String) rsRemote
293: .getAttribute("timestamp"));
294:
295: // The complete flag indicates whether more data is available.
296: String completeStr = rsRemote
297: .getAttribute("complete");
298: if (completeStr != null)
299: isComplete = completeStr
300: .compareToIgnoreCase("true") == 0;
301:
302: // The delete flag indicates that certain server side updates (e.g. row deletions)
303: // have occurred and hence the local data must be replaced in its entirety
304: boolean deleteRequired = false;
305: String delStr = rsRemote.getAttribute("delete");
306: if (delStr != null)
307: deleteRequired = delStr
308: .compareToIgnoreCase("true") == 0;
309:
310: // The key is used on the server side to track the resources used to know
311: // what has been transmitted and what remains for this session.
312: argValues[1] = rsRemote.getAttribute("key");
313:
314: // Clear out the local data
315: if (deleteRequired && !localDataDropped) {
316: clearLocalData(tableName);
317: localDataDropped = true;
318: maxPseudoId = 0;
319: }
320:
321: // Replace the local data
322: appendLocalData(tableName, rsRemote, maxPseudoId);
323: }
324:
325: // Record the current timestamp.
326: setLocalTimestamp(tableName, timestamp);
327: }
328:
329: // If there is no WHERE or other condition clause then the result set is
330: // fully matched
331: // if ( ( sql.indexOf( "WHERE" ) < 0 ) || ( sql.indexOf( "ORDER BY" ) < 0 ) || ( sql.indexOf( "GROUP BY" ) < 0 ) || ( sql.indexOf( "DISTINCT" ) < 0 ) )
332: // return null;
333: } catch (Exception e) {
334: DebugLogger.logError("Replication service call failed");
335: rs = null;
336: } finally {
337: // Return the connection to the pool
338: if (connObj != null)
339: connObj.expireLease();
340: }
341:
342: // Return the query data
343: return rs;
344: }
345:
346: /**
347: * Get a tables local timestamp. This timestamp represents the server time
348: * when the server's data was last updated
349: * @param tableName
350: * @param sysTable the name of the system table to access
351: * @return the timestamp
352: */
353: private java.sql.Timestamp getTimestamp(String tableName,
354: String sysTable) {
355: // Remove the local data
356: ResultSet rs = doQuery("SELECT lastUpdate FROM " + sysTable
357: + " WHERE tableName='" + tableName + "'");
358: if (rs != null) {
359: try {
360: if (rs.next()) {
361: if (!rs.isAfterLast())
362: return rs.getTimestamp(1);
363: }
364: } catch (SQLException ex) {
365: if (BuildProperties.DEBUG)
366: DebugLogger.trace("No timestamp available for "
367: + tableName + " in " + sysTable);
368: }
369: }
370: // Setup the timestamp table
371: if (!hasTimeStamps) {
372: doQuery("CREATE TABLE " + sysTable
373: + " (tableName VARCHAR(64), lastUpdate DATETIME)");
374: hasTimeStamps = true;
375:
376: // Force download of the server times.
377: checkLocalDatabase("FROM xSysServerTimestamps", connName,
378: DatabaseTable.PARANOID_UPDATE);
379: }
380:
381: java.sql.Timestamp date = new java.sql.Timestamp(0);
382: if (tableName.compareTo(sysTable) != 0) {
383: // Insert the new record.
384: String updateTime = date.toString();
385: doQuery("INSERT INTO " + sysTable
386: + " (tableName,lastUpdate) VALUES('" + tableName
387: + "','" + updateTime + "')");
388: }
389: return date;
390: }
391:
392: /**
393: * Set a tables local timestamp. This timestamp represents the server time
394: * when the server's data was last updated
395: * @param tableName
396: * @param sysTable the name of the system table to access
397: * @return the timestamp
398: */
399: private void setTimestamp(String tableName, Timestamp timeStamp,
400: String sysTable) {
401: doQuery("UPDATE " + sysTable + " SET lastUpdate='"
402: + timeStamp.toString() + "' WHERE tableName='"
403: + tableName + "'");
404: }
405:
406: /**
407: * Get the server timestamp for a particular table.
408: * @return
409: */
410: private Timestamp getLocalTimestamp(String tableName) {
411: return getTimestamp(tableName, "xSysLocalTimestamps");
412: }
413:
414: /**
415: * Set the server timestamp for a particular table.
416: */
417: private void setLocalTimestamp(String tableName, Timestamp ts) {
418: setTimestamp(tableName, ts, "xSysLocalTimestamps");
419: setServerTimestamp(tableName, ts);
420: }
421:
422: /**
423: * Get the server timestamp for a particular table.
424: * @return
425: */
426: private Timestamp getServerTimestamp(String tableName) {
427: return getTimestamp(tableName, "xSysServerTimestamps");
428: }
429:
430: /**
431: * Set the server timestamp for a particular table.
432: */
433: private void setServerTimestamp(String tableName, Timestamp ts) {
434: setTimestamp(tableName, ts, "xSysServerTimestamps");
435: }
436:
437: /**
438: * Invoke a SQL query and return the result set
439: * @param sql the query string
440: * @return the result set or null on failure
441: */
442: private ResultSet doQuery(String sql) {
443: try {
444: if (BuildProperties.DEBUG)
445: DebugLogger.trace(sql);
446: Statement statement = connObj.connection.createStatement();
447: statement.setEscapeProcessing(true);
448: return statement.executeQuery(sql);
449: } catch (SQLException ex1) {
450: if (BuildProperties.DEBUG)
451: DebugLogger.logError("Query failed");
452: }
453: return null;
454: }
455:
456: /**
457: * Delete the contents of a table
458: * @param tableName the table to clear
459: * @throws SQLException
460: */
461: private void clearLocalData(String tableName) throws SQLException {
462: // Remove the local data
463: String dropSQL = "DELETE FROM " + tableName;
464: doQuery(dropSQL);
465: // Statement statement = connObj.connection.createStatement();
466: // statement.setEscapeProcessing( true );
467: // statement.executeQuery( dropSQL );
468: }
469:
470: /**
471: * Append or replace data in the local table
472: * @param tableName the name of the local table
473: * @param rsRemote the data from the remote table
474: * @param maxPseudoId the max ID in the local table
475: * @throws SQLException
476: */
477: private void appendLocalData(String tableName, XmlElement rsRemote,
478: long maxPseudoId) throws SQLException {
479: Statement statement = connObj.connection.createStatement();
480: statement.setEscapeProcessing(true);
481:
482: // The XML data does not contain the proper field names so they need to be
483: // retrieved from the local database
484: Statement ps = connObj.connection.createStatement();
485: ResultSet rs = ps.executeQuery("SELECT TOP 1 * FROM "
486: + tableName);
487: ResultSetMetaData rsmd = rs.getMetaData();
488: int numCols = rsmd.getColumnCount();
489: int idCol = numCols - 1;
490: String idColName = null;
491:
492: // Save the data locally
493: Vector rows = rsRemote.getChildren();
494: int numRows = rows.size();
495: for (int i = 0; i < numRows; i++) {
496: XmlElement row = (XmlElement) rows.elementAt(i);
497: Enumeration fieldNames = row.enumerateAttributeNames();
498: String sql;
499: // The Pseudo ID column cannot be set in an update statement so it is
500: // necessary to find it and then exclude it from the queries
501: if (idColName == null) {
502: for (int j = 0; j < numCols - 2; j++)
503: fieldNames.nextElement();
504: idColName = (String) fieldNames.nextElement();
505:
506: fieldNames = row.enumerateAttributeNames();
507: }
508:
509: // If the record already exists in the table an update is needed instead of
510: // an insert
511: boolean bUpdate = false;
512: if ((maxPseudoId == 0)
513: || (maxPseudoId < new Long((String) row
514: .getAttribute(idColName)).longValue()))
515: sql = "INSERT INTO " + tableName + " VALUES(";
516: else {
517: bUpdate = true;
518: sql = "UPDATE " + tableName + " SET ";
519: }
520: int colIdx = 1;
521: for (int k = 0; k < numCols; k++) {
522: String fieldName = rsmd.getColumnName(colIdx++);
523: if (bUpdate) {
524: if (colIdx == (idCol + 1)) {
525: fieldNames.nextElement();
526: continue;
527: }
528: sql += fieldName + "=";
529: }
530: sql += row.getAttribute((String) fieldNames
531: .nextElement());
532: if (fieldNames.hasMoreElements())
533: sql += ",";
534: }
535: if (bUpdate)
536: sql += " WHERE PseudoId="
537: + (String) row.getAttribute(idColName);
538: else
539: sql += ")";
540:
541: if (BuildProperties.DEBUG)
542: DebugLogger.trace(sql);
543:
544: statement.executeQuery(sql);
545: }
546: }
547:
548: /**
549: * Execute a SHUTDOWN statement
550: * @param connParamName the name of the connection on which the shutdown
551: * statement is executed, or null for the default connection
552: * @throws Exception
553: */
554: public static void shutdown(String connParamName) throws Exception {
555: // Get a connection from the connection pool
556: ConnectionObject connObj = NamedConnectionManager.getInstance()
557: .getConnection(connParamName);
558:
559: Statement statement = connObj.connection.createStatement();
560: statement.executeQuery("SHUTDOWN");
561:
562: if (BuildProperties.DEBUG)
563: DebugLogger.trace("Database SHUTDOWN complete");
564: }
565: }
|