0001: /*
0002: * The contents of this file are subject to the Mozilla Public License
0003: * Version 1.1 (the "License"); you may not use this file except in
0004: * compliance with the License. You may obtain a copy of the License at
0005: * http://www.mozilla.org/MPL/
0006: *
0007: * Software distributed under the License is distributed on an "AS IS"
0008: * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
0009: * License for the specific language governing rights and limitations
0010: * under the License.
0011: *
0012: * The Original Code is iSQL-Viewer, A Mutli-Platform Database Tool.
0013: *
0014: * The Initial Developer of the Original Code is iSQL-Viewer, A Mutli-Platform Database Tool.
0015: * Portions created by Mark A. Kobold are Copyright (C) 2000-2007. All Rights Reserved.
0016: *
0017: * Contributor(s):
0018: * Mark A. Kobold [mkobold <at> isqlviewer <dot> com].
0019: *
0020: * If you didn't download this code from the following link, you should check
0021: * if you aren't using an obsolete version: http://www.isqlviewer.com
0022: */
0023: package org.isqlviewer.sql.embedded;
0024:
0025: import java.io.BufferedReader;
0026: import java.io.File;
0027: import java.io.IOException;
0028: import java.io.InputStream;
0029: import java.io.InputStreamReader;
0030: import java.net.MalformedURLException;
0031: import java.net.URL;
0032: import java.sql.Connection;
0033: import java.sql.DriverManager;
0034: import java.sql.PreparedStatement;
0035: import java.sql.ResultSet;
0036: import java.sql.SQLException;
0037: import java.sql.Statement;
0038: import java.sql.Timestamp;
0039: import java.sql.Types;
0040: import java.text.MessageFormat;
0041: import java.util.ArrayList;
0042: import java.util.Collection;
0043: import java.util.Date;
0044: import java.util.Enumeration;
0045:
0046: import org.apache.derby.jdbc.EmbeddedDriver;
0047: import org.isqlviewer.BindVariable;
0048: import org.isqlviewer.ServiceReference;
0049: import org.isqlviewer.bookmarks.Bookmark;
0050: import org.isqlviewer.bookmarks.BookmarkFolder;
0051: import org.isqlviewer.bookmarks.BookmarkReference;
0052: import org.isqlviewer.bookmarks.ColorLabel;
0053: import org.isqlviewer.history.CommandType;
0054: import org.isqlviewer.history.HistoricalCommand;
0055: import org.isqlviewer.util.BasicUtilities;
0056: import org.isqlviewer.util.IsqlToolkit;
0057:
0058: /**
0059: * Embedded Database Wrapper for providing high-level application functionaliy backed by a JDBC database.
0060: * <p>
0061: * This class currently uses Apache Derby as the backed. The DDL that defines the structures are defined by the
0062: * dbinit.sql script within this package.
0063: *
0064: * @author Mark A. Kobold <mkobold at isqlviewer dot com>
0065: * @version 1.0
0066: */
0067: public class EmbeddedDatabase {
0068:
0069: private static final String HISTORY_DELETE_ALL = "DELETE FROM HISTORY";
0070: private static final String CONSTRAINT_VIOLATION = "23505";
0071: private static final String NO_DATA_AVAILABLE = "24000";
0072:
0073: private static final String BOOKMARK_INSERT = "INSERT INTO BOOKMARK (IS_FAVORITE, CREATION_TIME, LAST_ACCESS, NAME, COMMAND_TEXT, USAGE_COUNT, FOLDER, COLOR_LABEL) VALUES (?, ?, ?, ?, ?, ? ,?, ?)";
0074: private static final String BOOKMARK_UPDATE = "UPDATE BOOKMARK SET IS_FAVORITE = ?, LAST_ACCESS = CURRENT_TIMESTAMP, NAME = ?, COMMAND_TEXT = ?, USAGE_COUNT = ?, FOLDER = ?, COLOR_LABEL=? WHERE BOOKMARK_ID=?";
0075: private static final String BOOKMARK_UPDATE_LITE = "UPDATE BOOKMARK SET NAME = ?, IS_FAVORITE = ?, LAST_ACCESS = CURRENT_TIMESTAMP, FOLDER = ?, COLOR_LABEL=? WHERE BOOKMARK_ID=?";
0076:
0077: private static final String BOOKMARK_SELECT_LITE = "SELECT BOOKMARK_ID,NAME,FOLDER,IS_FAVORITE, COLOR_LABEL FROM BOOKMARK ORDER BY FOLDER";
0078: private static final String BOOKMARK_SELECT_SINGLE = "SELECT BOOKMARK_ID, IS_FAVORITE, CREATION_TIME, LAST_ACCESS, NAME, COMMAND_TEXT, USAGE_COUNT, COLOR_LABEL FROM BOOKMARK WHERE BOOKMARK_ID = ?";
0079: private static final String BOOKMARK_DELETE = "DELETE FROM BOOKMARK WHERE BOOKMARK_ID=?";
0080: private static final String BOOKMARK_DELETE_FOLDER = "DELETE FROM BOOKMARK WHERE FOLDER LIKE ''{0}%'' ";
0081: private static final String BOOKMARK_FOLDER_RENAME = "UPDATE BOOKMARK SET FOLDER = REGEX_REPLACE(FOLDER, ?, ?) WHERE FOLDER LIKE ?";
0082:
0083: private static final String BOOKMARK_BIND_VARIABLE_INSERT = "INSERT INTO BOOKMARK_BIND_VARIABLE (BOOKMARK_ID, PARAMETER_INDEX, SQL_TYPE, USER_DATA, OPTIONS) VALUES (?, ?, ?, ?, ?)";
0084:
0085: private static final String SERVICE_SELECT_ALL = "SELECT SERVICE_ID, NAME, ORDER_PREFERENCE, LAST_USED, CREATION_TIME, RESOURCE_URL FROM SERVICE WHERE SERVICE_ID > 0 ORDER BY SERVICE_ID";
0086: private static final String SERVICE_SELECT_BY_NAME = "SELECT SERVICE_ID, NAME, ORDER_PREFERENCE, LAST_USED, CREATION_TIME, RESOURCE_URL FROM SERVICE WHERE NAME = ?";
0087: private static final String SERVICE_INSERT = "INSERT INTO SERVICE (NAME, ORDER_PREFERENCE, LAST_USED,RESOURCE_URL, CREATION_TIME) values (?, ?, ?, ?, ?)";
0088: private static final String SERVICE_ID_FOR_NAME = "SELECT SERVICE_ID FROM SERVICE WHERE NAME = ?";
0089: private static final String SERVICE_UPDATE = "UPDATE SERVICE SET NAME = ?, ORDER_PREFERENCE = ?, LAST_USED = ?, RESOURCE_URL = ? WHERE SERVICE_ID=?";
0090: private static final String SERVICE_INSTALL_UNKNOWN = "INSERT INTO SERVICE (NAME, ORDER_PREFERENCE, RESOURCE_URL, CREATION_TIME) values ( '???', -1, '', CURRENT_TIMESTAMP)";
0091: private static final String SERVICE_DELETE = "DELETE FROM SERVICE WHERE SERVICE_ID = ?";
0092:
0093: private static final String HISTORY_INSERT = "INSERT INTO HISTORY (SERVICE_ID, QUERY_TIME, TRANSACTION_ID, COMMAND_TYPE, COMMAND_TEXT) values (?, ?, ?, ?, ?)";
0094: private static final String HISTORY_SELECT_ALL = "SELECT HISTORY_ID, QUERY_TIME, TRANSACTION_ID, COMMAND_TYPE, COMMAND_TEXT FROM HISTORY ORDER BY HISTORY_ID, QUERY_TIME";
0095: private static final String HISTORY_SELECT_SINGLE = "SELECT H.HISTORY_ID, S.NAME, H.QUERY_TIME, H.TRANSACTION_ID, H.COMMAND_TYPE, H.COMMAND_TEXT FROM HISTORY H, SERVICE S WHERE HISTORY_ID = ? AND H.SERVICE_ID = S.SERVICE_ID";
0096: private static final String HISTORY_SELECT_SINGLE_BATCH = "SELECT H.HISTORY_ID, S.NAME, H.QUERY_TIME, H.TRANSACTION_ID, H.COMMAND_TYPE, H.COMMAND_TEXT FROM HISTORY H, SERVICE S WHERE TRANSACTION_ID = ? AND H.SERVICE_ID = S.SERVICE_ID ORDER BY HISTORY_ID";
0097: private static final String HISTORY_DELETE_SERVICE = "DELETE FROM HISTORY WHERE SERVICE_ID = ?";
0098:
0099: private static final String PRIMARY_KEY_QUERY = "values IDENTITY_VAL_LOCAL()";
0100:
0101: private static final EmbeddedDatabase sharedInstance;
0102:
0103: public static EmbeddedDatabase getSharedInstance() {
0104:
0105: return sharedInstance;
0106: }
0107:
0108: static {
0109: sharedInstance = new EmbeddedDatabase();
0110: }
0111:
0112: private Connection embeddedConnection = null;
0113: private File databaseHome = new File(
0114: IsqlToolkit.getBaseDirectory(), "derby");
0115:
0116: // private Logger logger = IsqlToolkit.getApplicationLogger();
0117: // private static final String BUNDLE_NAME = "org.isqlviewer.sql.embedded.ResourceBundle";
0118: // private LocalMessages messages = new LocalMessages(BUNDLE_NAME);
0119:
0120: private EmbeddedDatabase() {
0121:
0122: // just create the driver and register it with the connection manager //
0123: new EmbeddedDriver();
0124: }
0125:
0126: public boolean addService(ServiceReference serviceReference)
0127: throws SQLException {
0128:
0129: Object[] bindVariables = new Object[5];
0130: bindVariables[0] = serviceReference.getName();
0131: bindVariables[1] = new Integer(serviceReference.getOrder());
0132: Date lastUsed = serviceReference.getLastUsed();
0133: bindVariables[2] = new Timestamp(lastUsed == null ? 0
0134: : lastUsed.getTime());
0135: bindVariables[3] = serviceReference.getResourceURL()
0136: .toExternalForm();
0137: Date createdOn = serviceReference.getCreatedOn();
0138: bindVariables[4] = new Timestamp(createdOn == null ? System
0139: .currentTimeMillis() : createdOn.getTime());
0140:
0141: PreparedStatement preparedStatement = null;
0142: try {
0143: preparedStatement = embeddedConnection
0144: .prepareStatement(SERVICE_INSERT);
0145: for (int i = 0; i < bindVariables.length; i++) {
0146: preparedStatement.setObject(i + 1, bindVariables[i]);
0147: }
0148: int affectedCount = preparedStatement.executeUpdate();
0149: long identityValue = getInsertedPrimaryKey();
0150: serviceReference.setId(identityValue);
0151: return affectedCount == 1;
0152: } finally {
0153: if (preparedStatement != null) {
0154: try {
0155: preparedStatement.close();
0156: } catch (SQLException ignored) {
0157: }
0158: }
0159: }
0160: }
0161:
0162: public HistoricalCommand addHistoricalCommand(
0163: HistoricalCommand command) throws SQLException {
0164:
0165: HistoricalCommand reference = null;
0166: if (command.hasChildCommands()) {
0167: reference = addHistoricalCommand0(command);
0168: Enumeration<HistoricalCommand> childCommands = command
0169: .elements();
0170: while (childCommands.hasMoreElements()) {
0171: HistoricalCommand next = childCommands.nextElement();
0172: reference.addSubcommand(addHistoricalCommand0(next));
0173: }
0174: } else {
0175: return addHistoricalCommand0(command);
0176: }
0177: return reference;
0178:
0179: }
0180:
0181: public HistoricalCommand getHistoricalCommand(
0182: HistoricalCommand reference) throws SQLException {
0183:
0184: ensureConnection();
0185: PreparedStatement preparedStatement = null;
0186: HistoricalCommand parentCommand = null;
0187: ResultSet cursor = null;
0188: try {
0189: preparedStatement = embeddedConnection
0190: .prepareStatement(HISTORY_SELECT_SINGLE_BATCH);
0191: preparedStatement.setLong(1, reference.getTransactionId());
0192: boolean hasResults = preparedStatement.execute();
0193: if (hasResults) {
0194: cursor = preparedStatement.getResultSet();
0195: while (cursor.next()) {
0196: HistoricalCommand command = new HistoricalCommand();
0197: command.setId(cursor.getLong(1));
0198: command.setService(cursor.getString(2));
0199: Date executionTime = new Date(cursor
0200: .getTimestamp(3).getTime());
0201: command.setQueryTime(executionTime);
0202: command.setTransactionId(cursor.getLong(4));
0203: command.setType(CommandType.valueOf(cursor
0204: .getString(5)));
0205: String commandText = cursor.getString(6);
0206: command.setCommandText(commandText);
0207: if (parentCommand == null) {
0208: parentCommand = command;
0209: } else {
0210: parentCommand.addSubcommand(command);
0211: }
0212: }
0213: }
0214: } finally {
0215: if (cursor != null) {
0216: try {
0217: cursor.close();
0218: } catch (SQLException ignored) {
0219: }
0220: }
0221: if (preparedStatement != null) {
0222: try {
0223: preparedStatement.close();
0224: } catch (SQLException ignored) {
0225: }
0226: }
0227: }
0228: return parentCommand;
0229: }
0230:
0231: public HistoricalCommand getHistoricalCommand(long historyId)
0232: throws SQLException {
0233:
0234: PreparedStatement preparedStatement = null;
0235: ResultSet cursor = null;
0236: try {
0237: preparedStatement = embeddedConnection
0238: .prepareStatement(HISTORY_SELECT_SINGLE);
0239: preparedStatement.setLong(1, historyId);
0240: boolean hasResults = preparedStatement.execute();
0241: if (hasResults) {
0242: cursor = preparedStatement.getResultSet();
0243: if (cursor.next()) {
0244: HistoricalCommand command = new HistoricalCommand();
0245: command.setId(cursor.getLong(1));
0246: command.setService(cursor.getString(2));
0247: command.setQueryTime(cursor.getTimestamp(3));
0248: command.setTransactionId(cursor.getLong(4));
0249: CommandType type = CommandType.valueOf(cursor
0250: .getString(5));
0251: command.setType(type);
0252: command.setCommandText(cursor.getString(6));
0253: return command;
0254: }
0255: }
0256: return null;
0257: } finally {
0258: if (cursor != null) {
0259: try {
0260: cursor.close();
0261: } catch (SQLException ignored) {
0262: }
0263: }
0264: if (preparedStatement != null) {
0265: try {
0266: preparedStatement.close();
0267: } catch (SQLException ignored) {
0268: }
0269: }
0270: }
0271: }
0272:
0273: public Collection<HistoricalCommand> getHistory()
0274: throws SQLException {
0275:
0276: ensureConnection();
0277: ArrayList<HistoricalCommand> historySet = new ArrayList<HistoricalCommand>();
0278: Statement stmt = embeddedConnection.createStatement();
0279: ResultSet cursor = null;
0280: try {
0281: boolean hasResults = stmt.execute(HISTORY_SELECT_ALL);
0282: if (hasResults) {
0283: cursor = stmt.getResultSet();
0284: long currentTransactionId = -1;
0285: HistoricalCommand parentCommand = null;
0286: while (cursor.next()) {
0287: HistoricalCommand reference = new HistoricalCommand();
0288: reference.setId(cursor.getLong(1));
0289: Date executionTime = new Date(cursor
0290: .getTimestamp(2).getTime());
0291: reference.setQueryTime(executionTime);
0292: reference.setTransactionId(cursor.getLong(3));
0293: reference.setType(CommandType.valueOf(cursor
0294: .getString(4)));
0295: String commandText = cursor.getString(5);
0296: StringBuilder builder = new StringBuilder("");
0297: for (int i = 0; i < commandText.length(); i++) {
0298: char c = commandText.charAt(i);
0299: if (Character.isWhitespace(c)) {
0300: break;
0301: }
0302: builder.append(c);
0303: }
0304:
0305: long transactionId = reference.getTransactionId();
0306: if (transactionId > 0 && parentCommand == null) {
0307: parentCommand = reference;
0308: currentTransactionId = transactionId;
0309: historySet.add(reference);
0310: } else if (transactionId > 0
0311: && parentCommand != null) {
0312: if (transactionId == currentTransactionId) {
0313: parentCommand.addSubcommand(reference);
0314: } else {
0315: parentCommand = reference;
0316: currentTransactionId = transactionId;
0317: historySet.add(reference);
0318: }
0319: } else if (transactionId < 0) {
0320: parentCommand = null;
0321: currentTransactionId = -1;
0322: historySet.add(reference);
0323: }
0324: }
0325: }
0326: } finally {
0327: if (cursor != null) {
0328: try {
0329: cursor.close();
0330: } catch (SQLException ignored) {
0331: }
0332: }
0333: if (stmt != null) {
0334: try {
0335: stmt.close();
0336: } catch (SQLException ignored) {
0337: }
0338: }
0339: }
0340: return historySet;
0341: }
0342:
0343: public long getServiceIDForName(String service) throws SQLException {
0344:
0345: PreparedStatement preparedStatement = null;
0346: ResultSet cursor = null;
0347: try {
0348: preparedStatement = embeddedConnection
0349: .prepareStatement(SERVICE_ID_FOR_NAME);
0350: preparedStatement.setString(1, service);
0351: boolean hasResults = preparedStatement.execute();
0352: if (hasResults) {
0353: cursor = preparedStatement.getResultSet();
0354: if (cursor.next()) {
0355: long serviceID = cursor.getLong(1);
0356: if (cursor.wasNull()) {
0357: return 1;
0358: }
0359: return serviceID;
0360: }
0361: }
0362: } catch (SQLException sqle) {
0363: if (NO_DATA_AVAILABLE.equals(sqle.getSQLState())) {
0364: return 1;
0365: }
0366: throw sqle;
0367: } finally {
0368: if (cursor != null) {
0369: try {
0370: cursor.close();
0371: } catch (SQLException ignored) {
0372: }
0373: }
0374: if (preparedStatement != null) {
0375: try {
0376: preparedStatement.close();
0377: } catch (SQLException ignored) {
0378: }
0379: }
0380: }
0381: return 1;
0382: }
0383:
0384: public boolean addBookmarkFolder(BookmarkFolder bookmarkFolder)
0385: throws SQLException {
0386:
0387: int childCount = bookmarkFolder.getChildCount();
0388: PreparedStatement preparedStatement = null;
0389: try {
0390: preparedStatement = embeddedConnection
0391: .prepareStatement(BOOKMARK_INSERT);
0392: for (int i = 0; i < childCount; i++) {
0393: Object childElement = bookmarkFolder.getChild(i);
0394: if (childElement instanceof Bookmark) {
0395: addBookmark0((Bookmark) childElement,
0396: bookmarkFolder, preparedStatement);
0397: } else if (childElement instanceof BookmarkFolder) {
0398: addBookmarkFolder0((BookmarkFolder) childElement,
0399: 1, preparedStatement);
0400: }
0401: }
0402: preparedStatement.executeBatch();
0403: } finally {
0404: if (preparedStatement != null) {
0405: try {
0406: preparedStatement.close();
0407: } catch (SQLException ignored) {
0408: }
0409: }
0410: }
0411: return true;
0412: }
0413:
0414: public Bookmark getBookmark(BookmarkReference reference)
0415: throws SQLException {
0416:
0417: Bookmark bookmark = getBookmark(reference.getId());
0418: bookmark.setFolder(reference.getFolder());
0419: return bookmark;
0420: }
0421:
0422: public Bookmark getBookmark(long bookmarkId) throws SQLException {
0423:
0424: PreparedStatement preparedStatement = null;
0425: ResultSet cursor = null;
0426: try {
0427: preparedStatement = embeddedConnection
0428: .prepareStatement(BOOKMARK_SELECT_SINGLE);
0429: preparedStatement.setLong(1, bookmarkId);
0430: boolean hasResults = preparedStatement.execute();
0431: if (hasResults) {
0432: cursor = preparedStatement.getResultSet();
0433: if (cursor.next()) {
0434: Bookmark bookmark = new Bookmark();
0435: bookmark.setId(cursor.getLong(1));
0436: bookmark.setFavorite(cursor.getBoolean(2));
0437: bookmark.setCreationTime(cursor.getTimestamp(3));
0438: bookmark.setLastAccess(cursor.getTimestamp(4));
0439: bookmark.setName(cursor.getString(5));
0440: bookmark.setCommandText(cursor.getString(6));
0441: bookmark.setUseCount(cursor.getLong(7));
0442: String labelColorText = cursor.getString(8);
0443: if (labelColorText != null) {
0444: bookmark.setColorLabel(ColorLabel
0445: .valueOf(labelColorText));
0446: }
0447: return bookmark;
0448: }
0449: }
0450: return null;
0451: } finally {
0452: if (cursor != null) {
0453: try {
0454: cursor.close();
0455: } catch (SQLException ignored) {
0456: }
0457: }
0458: if (preparedStatement != null) {
0459: try {
0460: preparedStatement.close();
0461: } catch (SQLException ignored) {
0462: }
0463: }
0464: }
0465: }
0466:
0467: public boolean updateService(ServiceReference serviceReference)
0468: throws SQLException {
0469:
0470: Object[] bindVariables = new Object[5];
0471: int[] types = new int[5];
0472:
0473: types[0] = Types.VARCHAR;
0474: types[1] = Types.INTEGER;
0475: types[2] = Types.TIMESTAMP;
0476: types[2] = Types.VARCHAR;
0477: types[4] = Types.BIGINT;
0478:
0479: bindVariables[0] = serviceReference.getName();
0480: bindVariables[1] = new Integer(serviceReference.getOrder());
0481: Date lastUsed = serviceReference.getLastUsed();
0482: bindVariables[2] = new Timestamp(lastUsed == null ? System
0483: .currentTimeMillis() : lastUsed.getTime());
0484: bindVariables[3] = serviceReference.getResourceURL();
0485: bindVariables[3] = new Long(serviceReference.getId());
0486:
0487: PreparedStatement preparedStatement = null;
0488: try {
0489: preparedStatement = embeddedConnection
0490: .prepareStatement(SERVICE_UPDATE);
0491: for (int i = 0; i < bindVariables.length; i++) {
0492: if (bindVariables[i] == null) {
0493: preparedStatement.setNull(i + 1, types[i]);
0494: } else {
0495: preparedStatement
0496: .setObject(i + 1, bindVariables[i]);
0497: }
0498: }
0499: int affected = preparedStatement.executeUpdate();
0500: return affected == 1;
0501: } finally {
0502: if (preparedStatement != null) {
0503: try {
0504: preparedStatement.close();
0505: } catch (SQLException ignored) {
0506: }
0507: }
0508: }
0509: }
0510:
0511: public boolean updateBookmark(BookmarkReference bookmark)
0512: throws SQLException {
0513:
0514: Object[] bindVariables = new Object[5];
0515: int[] types = new int[5];
0516:
0517: types[0] = Types.VARCHAR;
0518: types[1] = Types.BOOLEAN;
0519: types[2] = Types.VARCHAR;
0520: types[2] = Types.VARCHAR;
0521: types[4] = Types.BIGINT;
0522:
0523: bindVariables[0] = bookmark.getName();
0524: bindVariables[1] = Boolean.valueOf(bookmark.isFavorite());
0525: bindVariables[2] = bookmark.getPath();
0526: ColorLabel colorLabel = bookmark.getColorLabel();
0527: bindVariables[3] = colorLabel == null ? null : colorLabel
0528: .name();
0529: bindVariables[4] = new Long(bookmark.getId());
0530:
0531: PreparedStatement preparedStatement = null;
0532: try {
0533: preparedStatement = embeddedConnection
0534: .prepareStatement(BOOKMARK_UPDATE_LITE);
0535: for (int i = 0; i < bindVariables.length; i++) {
0536: if (bindVariables[i] == null) {
0537: preparedStatement.setNull(i + 1, types[i]);
0538: } else {
0539: preparedStatement
0540: .setObject(i + 1, bindVariables[i]);
0541: }
0542: }
0543: int affected = preparedStatement.executeUpdate();
0544: return affected == 1;
0545: } finally {
0546: if (preparedStatement != null) {
0547: try {
0548: preparedStatement.close();
0549: } catch (SQLException ignored) {
0550: }
0551: }
0552: }
0553: }
0554:
0555: public boolean updateBookmark(Bookmark bookmark)
0556: throws SQLException {
0557:
0558: Object[] bindVariables = new Object[7];
0559: int[] types = new int[7];
0560:
0561: types[0] = Types.BOOLEAN;
0562: types[1] = Types.VARCHAR;
0563: types[2] = Types.VARCHAR;
0564: types[3] = Types.BIGINT;
0565: types[4] = Types.VARCHAR;
0566: types[5] = Types.VARCHAR;
0567: types[6] = Types.BIGINT;
0568:
0569: bindVariables[0] = Boolean.valueOf(bookmark.isFavorite());
0570: bindVariables[1] = bookmark.getName();
0571: bindVariables[2] = bookmark.getCommandText();
0572: bindVariables[3] = new Long(bookmark.getUseCount());
0573: bindVariables[4] = bookmark.getPath();
0574: ColorLabel colorLabel = bookmark.getColorLabel();
0575: bindVariables[5] = colorLabel == null ? null : colorLabel
0576: .name();
0577: bindVariables[6] = new Long(bookmark.getId());
0578:
0579: PreparedStatement preparedStatement = null;
0580: try {
0581: preparedStatement = embeddedConnection
0582: .prepareStatement(BOOKMARK_UPDATE);
0583: for (int i = 0; i < bindVariables.length; i++) {
0584: if (bindVariables[i] == null) {
0585: preparedStatement.setNull(i + 1, types[i]);
0586: } else {
0587: preparedStatement
0588: .setObject(i + 1, bindVariables[i]);
0589: }
0590: }
0591: int affected = preparedStatement.executeUpdate();
0592: return affected == 1;
0593: } finally {
0594: if (preparedStatement != null) {
0595: try {
0596: preparedStatement.close();
0597: } catch (SQLException ignored) {
0598: }
0599: }
0600: }
0601: }
0602:
0603: public boolean removeBookmarkFolder(BookmarkFolder folder)
0604: throws SQLException {
0605:
0606: Statement statement = null;
0607: String query = MessageFormat.format(BOOKMARK_DELETE_FOLDER,
0608: new Object[] { folder.getPath() });
0609: try {
0610: statement = embeddedConnection.createStatement();
0611: int affectedCount = statement.executeUpdate(query);
0612: return affectedCount == 1;
0613: } catch (SQLException sqle) {
0614: if (NO_DATA_AVAILABLE.equals(sqle.getSQLState())) {
0615: return false;
0616: }
0617: throw sqle;
0618: } finally {
0619: if (statement != null) {
0620: try {
0621: statement.close();
0622: } catch (SQLException ignored) {
0623: }
0624: }
0625: }
0626: }
0627:
0628: public boolean removeBookmark(BookmarkReference bookmark)
0629: throws SQLException {
0630:
0631: PreparedStatement preparedStatement = null;
0632: try {
0633: preparedStatement = embeddedConnection
0634: .prepareStatement(BOOKMARK_DELETE);
0635: preparedStatement.setLong(1, bookmark.getId());
0636: int affectedCount = preparedStatement.executeUpdate();
0637: return affectedCount == 1;
0638: } catch (SQLException sqle) {
0639: if (NO_DATA_AVAILABLE.equals(sqle.getSQLState())) {
0640: return false;
0641: }
0642: throw sqle;
0643: } finally {
0644: if (preparedStatement != null) {
0645: try {
0646: preparedStatement.close();
0647: } catch (SQLException ignored) {
0648: }
0649: }
0650: }
0651: }
0652:
0653: public boolean removeService(ServiceReference reference)
0654: throws SQLException {
0655:
0656: ensureConnection();
0657: PreparedStatement statement = null;
0658: try {
0659: statement = embeddedConnection
0660: .prepareStatement(SERVICE_DELETE);
0661: statement.setLong(1, reference.getId());
0662: int affectedCount = statement.executeUpdate();
0663: statement.close();
0664:
0665: statement = embeddedConnection
0666: .prepareStatement(HISTORY_DELETE_SERVICE);
0667: statement.setLong(1, reference.getId());
0668: statement.executeUpdate();
0669:
0670: return affectedCount == 1;
0671: } catch (SQLException sqle) {
0672: if (NO_DATA_AVAILABLE.equals(sqle.getSQLState())) {
0673: return false;
0674: }
0675: throw sqle;
0676: } finally {
0677: if (statement != null) {
0678: try {
0679: statement.close();
0680: } catch (SQLException ignored) {
0681: }
0682: }
0683: }
0684: }
0685:
0686: public BookmarkFolder getBookmarks() throws SQLException {
0687:
0688: ensureConnection();
0689: BookmarkFolder rootFolder = BookmarkFolder.createRootFolder();
0690:
0691: Statement stmt = embeddedConnection.createStatement();
0692: ResultSet cursor = null;
0693: try {
0694: boolean hasResults = stmt.execute(BOOKMARK_SELECT_LITE);
0695: if (hasResults) {
0696: cursor = stmt.getResultSet();
0697: while (cursor.next()) {
0698: BookmarkReference bookmark = new BookmarkReference();
0699: bookmark.setId(cursor.getLong(1));
0700: bookmark.setName(cursor.getString(2));
0701: bookmark.setFavorite(cursor.getBoolean(4));
0702: String labelColorText = cursor.getString(5);
0703: if (labelColorText != null) {
0704: bookmark.setColorLabel(ColorLabel
0705: .valueOf(labelColorText));
0706: }
0707: BookmarkFolder folder = rootFolder.mkdirs(cursor
0708: .getString(3));
0709: if (folder != null) {
0710: folder.addBookmark(bookmark);
0711: bookmark.setFolder(folder);
0712: }
0713: }
0714: }
0715: } finally {
0716: if (cursor != null) {
0717: try {
0718: cursor.close();
0719: } catch (SQLException ignored) {
0720: }
0721: }
0722: if (stmt != null) {
0723: try {
0724: stmt.close();
0725: } catch (SQLException ignored) {
0726: }
0727: }
0728: }
0729: return rootFolder;
0730: }
0731:
0732: public ServiceReference getServiceForName(String serviceName)
0733: throws SQLException {
0734:
0735: ensureConnection();
0736: PreparedStatement preparedStatement = null;
0737: ResultSet cursor = null;
0738: try {
0739: preparedStatement = embeddedConnection
0740: .prepareStatement(SERVICE_SELECT_BY_NAME);
0741: preparedStatement.setString(1, serviceName);
0742: boolean hasResults = preparedStatement.execute();
0743: if (hasResults) {
0744: cursor = preparedStatement.getResultSet();
0745: if (cursor.next()) {
0746: ServiceReference reference = new ServiceReference();
0747: reference.setId(cursor.getLong(1));
0748: reference.setName(cursor.getString(2));
0749: reference.setOrder(cursor.getInt(3));
0750: reference.setLastUsed(cursor.getDate(4));
0751: reference.setCreatedOn(cursor.getDate(5));
0752: reference.setResourceURL(new URL(cursor
0753: .getString(6)));
0754: return reference;
0755: }
0756: }
0757: } catch (MalformedURLException e) {
0758: e.printStackTrace();
0759: } finally {
0760: if (cursor != null) {
0761: try {
0762: cursor.close();
0763: } catch (SQLException ignored) {
0764: }
0765: }
0766: if (preparedStatement != null) {
0767: try {
0768: preparedStatement.close();
0769: } catch (SQLException ignored) {
0770: }
0771: }
0772: }
0773: return null;
0774: }
0775:
0776: public Collection<ServiceReference> getRegisteredServices()
0777: throws SQLException {
0778:
0779: ensureConnection();
0780: ArrayList<ServiceReference> serviceReferenceSet = new ArrayList<ServiceReference>();
0781: Statement stmt = embeddedConnection.createStatement();
0782: ResultSet cursor = null;
0783: try {
0784: boolean hasResults = stmt.execute(SERVICE_SELECT_ALL);
0785: if (hasResults) {
0786: cursor = stmt.getResultSet();
0787: while (cursor.next()) {
0788: ServiceReference reference = new ServiceReference();
0789:
0790: reference.setId(cursor.getLong(1));
0791: reference.setName(cursor.getString(2));
0792: reference.setOrder(cursor.getInt(3));
0793: reference.setLastUsed(cursor.getDate(4));
0794: reference.setCreatedOn(cursor.getDate(5));
0795: try {
0796: reference.setResourceURL(new URL(cursor
0797: .getString(6)));
0798: } catch (MalformedURLException e) {
0799: continue;
0800: }
0801: serviceReferenceSet.add(reference);
0802: }
0803: }
0804: } finally {
0805: if (cursor != null) {
0806: try {
0807: cursor.close();
0808: } catch (SQLException ignored) {
0809: }
0810: }
0811: if (stmt != null) {
0812: try {
0813: stmt.close();
0814: } catch (SQLException ignored) {
0815: }
0816: }
0817: }
0818: return serviceReferenceSet;
0819: }
0820:
0821: public BookmarkReference addBookmark(Bookmark bookmark)
0822: throws SQLException {
0823:
0824: if (addBookmark0(bookmark, null, null)) {
0825: return bookmark.toBookmarkReference();
0826: }
0827: return null;
0828: }
0829:
0830: public int removeAllHistory() throws SQLException {
0831:
0832: ensureConnection();
0833: Statement stmt = embeddedConnection.createStatement();
0834: try {
0835: return stmt.executeUpdate(HISTORY_DELETE_ALL);
0836: } finally {
0837: if (stmt != null) {
0838: try {
0839: stmt.close();
0840: } catch (SQLException ignored) {
0841: }
0842: }
0843: }
0844: }
0845:
0846: public boolean renameBookmarkFolder(String existingPath,
0847: String newPath) throws SQLException {
0848:
0849: Object[] bindVariables = new Object[3];
0850: int[] types = new int[3];
0851:
0852: types[0] = Types.VARCHAR;
0853: types[1] = Types.VARCHAR;
0854: types[2] = Types.VARCHAR;
0855:
0856: bindVariables[0] = MessageFormat.format("({0})(.*)",
0857: new Object[] { existingPath });
0858: bindVariables[1] = MessageFormat.format("{0}$2",
0859: new Object[] { newPath });
0860: bindVariables[2] = MessageFormat.format("{0}%",
0861: new Object[] { existingPath });
0862:
0863: PreparedStatement preparedStatement = null;
0864: try {
0865: preparedStatement = embeddedConnection
0866: .prepareStatement(BOOKMARK_FOLDER_RENAME);
0867: for (int i = 0; i < bindVariables.length; i++) {
0868: if (bindVariables[i] == null) {
0869: preparedStatement.setNull(i + 1, types[i]);
0870: } else {
0871: preparedStatement
0872: .setObject(i + 1, bindVariables[i]);
0873: }
0874: }
0875: int affected = preparedStatement.executeUpdate();
0876: return affected >= 1;
0877: } finally {
0878: if (preparedStatement != null) {
0879: try {
0880: preparedStatement.close();
0881: } catch (SQLException ignored) {
0882: }
0883: }
0884: }
0885: }
0886:
0887: /**
0888: * Initializes the internal databases by creating the connection and creating all the nessecary database structures.
0889: * <p>
0890: * If this method returns true, the calling method should perform any nessecary upgrade and or import functions as
0891: * deemed nessecary by the caller.
0892: * <p>
0893: * This method should only be called once.
0894: *
0895: * @return <tt>true</tt> if the database was initialized as a new instance, <tt>false</tt> if the database was
0896: * pre-existing.
0897: * @throws SQLException
0898: */
0899: public synchronized boolean initialize() throws SQLException {
0900:
0901: boolean createTables = false;
0902: if (!databaseHome.exists()) {
0903: // if (!databaseHome.mkdirs()) {
0904: // String msg = messages.format("embedded_database.failed_to_create_database_home", databaseHome);
0905: // throw new SQLException(msg);
0906: // }
0907: createTables = true;
0908: }
0909:
0910: ensureConnection();
0911: if (createTables) {
0912: InputStream initStream = EmbeddedDatabase.class
0913: .getResourceAsStream("dbinit.sql");
0914: try {
0915: BufferedReader reader = new BufferedReader(
0916: new InputStreamReader(initStream));
0917: StringBuilder sqlBuffer = new StringBuilder("");
0918: while (reader.ready()) {
0919: String line = reader.readLine();
0920: if (line.length() == 0) {
0921: try {
0922: if (sqlBuffer.length() > 0) {
0923: executeStatement(sqlBuffer.toString());
0924: }
0925: } finally {
0926: sqlBuffer.setLength(0);
0927: }
0928: } else {
0929: sqlBuffer.append(line);
0930: sqlBuffer.append(' ');
0931: }
0932: }
0933: if (sqlBuffer.length() > 0) {
0934: executeStatement(sqlBuffer.toString());
0935: }
0936: executeStatement(SERVICE_INSTALL_UNKNOWN);
0937: } catch (IOException error) {
0938: SQLException sqlError = new SQLException(error
0939: .getMessage());
0940: BasicUtilities.wrapThrowable(error, sqlError);
0941: throw sqlError;
0942: }
0943: }
0944: return createTables;
0945: }
0946:
0947: private boolean addBookmark0(Bookmark bookmark,
0948: BookmarkFolder folder, PreparedStatement preparedStatement)
0949: throws SQLException {
0950:
0951: Object[] bindVariables = new Object[8];
0952: int[] types = new int[8];
0953:
0954: types[0] = Types.BOOLEAN;
0955: types[1] = Types.TIMESTAMP;
0956: types[2] = Types.TIMESTAMP;
0957: types[3] = Types.VARCHAR;
0958: types[4] = Types.VARCHAR;
0959: types[5] = Types.BIGINT;
0960: types[6] = Types.VARCHAR;
0961: types[7] = Types.VARCHAR;
0962:
0963: bindVariables[0] = Boolean.valueOf(bookmark.isFavorite());
0964: Date time = bookmark.getCreationTime();
0965: bindVariables[1] = new Timestamp(time == null ? System
0966: .currentTimeMillis() : time.getTime());
0967: time = bookmark.getLastAccess();
0968: bindVariables[2] = new Timestamp(time == null ? System
0969: .currentTimeMillis() : time.getTime());
0970: bindVariables[3] = bookmark.getName();
0971: bindVariables[4] = bookmark.getCommandText();
0972: bindVariables[5] = new Long(bookmark.getUseCount());
0973: bindVariables[6] = folder == null ? bookmark.getPath() : folder
0974: .getPath();
0975: ColorLabel colorLabel = bookmark.getColorLabel();
0976: bindVariables[7] = colorLabel == null ? null : colorLabel
0977: .name();
0978:
0979: boolean doBatch = (preparedStatement != null);
0980: boolean hasError = true;
0981: embeddedConnection.setAutoCommit(false);
0982:
0983: PreparedStatement statement = null;
0984: try {
0985: if (preparedStatement == null) {
0986: statement = embeddedConnection
0987: .prepareStatement(BOOKMARK_INSERT);
0988: } else {
0989: statement = preparedStatement;
0990: }
0991: for (int i = 0; i < bindVariables.length; i++) {
0992: if (bindVariables[i] == null) {
0993: statement.setNull(i + 1, types[i]);
0994: } else {
0995: statement.setObject(i + 1, bindVariables[i]);
0996: }
0997: }
0998: try {
0999: int affectedCount = statement.executeUpdate();
1000: long identityValue = getInsertedPrimaryKey();
1001: bookmark.setId(identityValue);
1002: addBindVariables(bookmark);
1003: hasError = false;
1004: return affectedCount == 1;
1005: } catch (SQLException exception) {
1006: if (CONSTRAINT_VIOLATION
1007: .equals(exception.getSQLState())) {
1008: return false;
1009: }
1010: throw exception;
1011: }
1012: } finally {
1013: if (hasError) {
1014: embeddedConnection.rollback();
1015: } else {
1016: embeddedConnection.commit();
1017: }
1018: embeddedConnection.setAutoCommit(true);
1019: if (preparedStatement != null) {
1020: if (!doBatch) {
1021: try {
1022: preparedStatement.close();
1023: } catch (SQLException ignored) {
1024: }
1025: } else if (doBatch) {
1026: preparedStatement.clearParameters();
1027: preparedStatement.clearWarnings();
1028: }
1029: }
1030: }
1031: }
1032:
1033: private HistoricalCommand addHistoricalCommand0(
1034: HistoricalCommand command) throws SQLException {
1035:
1036: Object[] bindVariables = new Object[5];
1037: bindVariables[0] = new Long(getServiceIDForName(command
1038: .getService()));
1039: Date executionTime = command.getQueryTime();
1040: bindVariables[1] = new Timestamp(executionTime == null ? 0
1041: : executionTime.getTime());
1042: bindVariables[2] = new Long(command.getTransactionId());
1043: bindVariables[3] = command.getType().toString();
1044: bindVariables[4] = command.getCommandText();
1045:
1046: PreparedStatement preparedStatement = null;
1047: try {
1048: preparedStatement = embeddedConnection
1049: .prepareStatement(HISTORY_INSERT);
1050: for (int i = 0; i < bindVariables.length; i++) {
1051: preparedStatement.setObject(i + 1, bindVariables[i]);
1052: }
1053: int affectedCount = preparedStatement.executeUpdate();
1054: if (affectedCount == 1) {
1055: long identityValue = getInsertedPrimaryKey();
1056: command.setId(identityValue);
1057:
1058: HistoricalCommand referenceObject = new HistoricalCommand();
1059: referenceObject.setId(identityValue);
1060: referenceObject.setQueryTime(command.getQueryTime());
1061: referenceObject.setTransactionId(command
1062: .getTransactionId());
1063: referenceObject.setType(command.getType());
1064:
1065: String commandText = (String) bindVariables[4];
1066: StringBuilder builder = new StringBuilder("");
1067: for (int i = 0; i < commandText.length(); i++) {
1068: char c = commandText.charAt(i);
1069: if (Character.isWhitespace(c)) {
1070: break;
1071: }
1072: builder.append(c);
1073: }
1074: return referenceObject;
1075: }
1076: return null;
1077: } finally {
1078: if (preparedStatement != null) {
1079: try {
1080: preparedStatement.close();
1081: } catch (SQLException ignored) {
1082: }
1083: }
1084: }
1085: }
1086:
1087: private void addBindVariables(Bookmark bookmark)
1088: throws SQLException {
1089:
1090: Object[] bindVariables = new Object[5];
1091: bindVariables[0] = new Long(bookmark.getId());
1092:
1093: Enumeration<BindVariable> variables = bookmark.variables();
1094: PreparedStatement preparedStatement = null;
1095: int localParameterIndex = 1;
1096: try {
1097: preparedStatement = embeddedConnection
1098: .prepareStatement(BOOKMARK_BIND_VARIABLE_INSERT);
1099:
1100: while (variables.hasMoreElements()) {
1101: BindVariable next = variables.nextElement();
1102: int index = next.getIndex();
1103: if (index <= 0) {
1104: index = localParameterIndex;
1105: }
1106: localParameterIndex++;
1107: bindVariables[1] = new Integer(index);
1108: bindVariables[2] = new Integer(next.getType());
1109: bindVariables[3] = next.getUserData();
1110: bindVariables[4] = next.getFormatOptions();
1111:
1112: for (int i = 0; i < bindVariables.length; i++) {
1113: preparedStatement
1114: .setObject(i + 1, bindVariables[i]);
1115: }
1116: preparedStatement.executeUpdate();
1117: }
1118: } finally {
1119: if (preparedStatement != null) {
1120: try {
1121: preparedStatement.close();
1122: } catch (SQLException ignored) {
1123: }
1124: }
1125: }
1126: }
1127:
1128: private void addBookmarkFolder0(BookmarkFolder bookmarkFolder,
1129: int stackDepth, PreparedStatement preparedStatement)
1130: throws SQLException {
1131:
1132: int childCount = bookmarkFolder.getChildCount();
1133: for (int i = 0; i < childCount; i++) {
1134: Object childElement = bookmarkFolder.getChild(i);
1135: if (childElement instanceof Bookmark) {
1136: addBookmark0((Bookmark) childElement, bookmarkFolder,
1137: preparedStatement);
1138: } else if (childElement instanceof BookmarkFolder) {
1139: addBookmarkFolder0((BookmarkFolder) childElement,
1140: stackDepth + 1, preparedStatement);
1141: }
1142: }
1143: }
1144:
1145: private void executeStatement(String sqlBuffer) throws SQLException {
1146:
1147: Statement stmt = null;
1148: try {
1149: stmt = embeddedConnection.createStatement();
1150: stmt.execute(sqlBuffer.toString());
1151: } finally {
1152: if (stmt != null) {
1153: try {
1154: stmt.close();
1155: } catch (SQLException ignored) {
1156: }
1157: }
1158: }
1159: }
1160:
1161: private void ensureConnection() throws SQLException {
1162:
1163: if (embeddedConnection != null) {
1164: return;
1165: }
1166: String url = constructURL();
1167: boolean hasError = true;
1168: try {
1169: embeddedConnection = DriverManager.getConnection(url);
1170: hasError = false;
1171: } catch (SQLException error) {
1172: throw error;
1173: } finally {
1174: if (hasError) {
1175: invalidateConnection();
1176: }
1177: }
1178: }
1179:
1180: private void invalidateConnection() {
1181:
1182: if (embeddedConnection != null) {
1183: try {
1184: embeddedConnection.close();
1185: } catch (Exception ignored) {
1186: } finally {
1187: embeddedConnection = null;
1188: }
1189: }
1190: }
1191:
1192: private long getInsertedPrimaryKey() throws SQLException {
1193:
1194: Statement stmt = null;
1195: ResultSet identity = null;
1196: try {
1197: stmt = embeddedConnection.createStatement();
1198: boolean hasResults = stmt.execute(PRIMARY_KEY_QUERY);
1199: if (hasResults) {
1200: identity = stmt.getResultSet();
1201: if (identity.next()) {
1202: return identity.getLong(1);
1203: }
1204: }
1205: return -1;
1206: } finally {
1207: if (identity != null) {
1208: try {
1209: identity.close();
1210: } catch (SQLException ignored) {
1211: }
1212: }
1213: if (stmt != null) {
1214: try {
1215: stmt.close();
1216: } catch (SQLException ignored) {
1217: }
1218: }
1219: }
1220: }
1221:
1222: private String constructURL() {
1223:
1224: MessageFormat urlFormat = new MessageFormat(
1225: "jdbc:derby:{0}/;create=true;upgrade=true;");
1226: Object[] parameters = new Object[1];
1227: parameters[0] = databaseHome.getAbsolutePath();
1228: return urlFormat.format(parameters);
1229: }
1230:
1231: }
|