Mercurial > dive4elements > framework
changeset 305:f33401ea2a6c
Artifact database: Refactorized the usage of dialect independent SQL to be reusable.
artifacts/trunk@2412 c6561f87-3c4e-4783-a992-168aeb5c3f6f
line wrap: on
line diff
--- a/ChangeLog Wed Jul 27 13:51:30 2011 +0000 +++ b/ChangeLog Thu Jul 28 10:19:35 2011 +0000 @@ -1,3 +1,26 @@ +2011-07-28 Sascha L. Teichmann <teichmann@intevation.de> + + Refactorized the usage of dialect independent SQL to be reusable. + + * artifact-database/src/main/java/de/intevation/artifactdatabase/db/SQL.java, + artifact-database/src/main/java/de/intevation/artifactdatabase/db/DBConnection.java, + artifact-database/src/main/java/de/intevation/artifactdatabase/db/SQLExecutor.java: + New. Generalized versions to make code reusable for datacage. + + * artifact-database/src/main/java/de/intevation/artifactdatabase/DBConnection.java, + artifact-database/src/main/java/de/intevation/artifactdatabase/SQL.java, + artifact-database/src/main/java/de/intevation/artifactdatabase/SQLExecutor.java: + Deleted. + + * artifact-database/src/main/java/de/intevation/artifactdatabase/DBConfig.java: + New. Centralizes the SQL database configuration of the backend. + + * artifact-database/src/main/java/de/intevation/artifactdatabase/App.java, + artifact-database/src/main/java/de/intevation/artifactdatabase/h2/CollectionAccessUpdateTrigger.java, + artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java, + artifact-database/src/main/java/de/intevation/artifactdatabase/DatabaseCleaner.java: + A lot of adjustment to make the new infrastructure work. Needs heavy testing! + 2011-07-27 Sascha L. Teichmann <teichmann@intevation.de> * artifact-database/src/main/java/de/intevation/artifactdatabase/LifetimeListener.java:
--- a/artifact-database/src/main/java/de/intevation/artifactdatabase/App.java Wed Jul 27 13:51:30 2011 +0000 +++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/App.java Thu Jul 28 10:19:35 2011 +0000 @@ -70,7 +70,7 @@ bootstrap, backend); DatabaseCleaner cleaner = new DatabaseCleaner( - bootstrap.getContext(), backend); + bootstrap.getContext(), backend, backend.getConfig()); HTTPServer httpServer = bootstrap.getHTTPServer();
--- a/artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java Wed Jul 27 13:51:30 2011 +0000 +++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java Thu Jul 28 10:19:35 2011 +0000 @@ -20,6 +20,9 @@ import de.intevation.artifacts.common.utils.StringUtils; import de.intevation.artifacts.common.utils.XMLUtils; +import de.intevation.artifactdatabase.db.SQLExecutor; +import de.intevation.artifactdatabase.db.SQL; + import java.sql.SQLException; import java.sql.Timestamp; import java.sql.Types; @@ -48,172 +51,92 @@ /** * The SQL statement to create new artifact id inside the database. */ - public static final String SQL_NEXT_ID = - SQL.get("artifacts.id.nextval"); + public String SQL_NEXT_ID; /** * The SQL statement to insert an artifact into the database. */ - public static final String SQL_INSERT = - SQL.get("artifacts.insert"); + public String SQL_INSERT; /** * The SQL statement to update some columns of an existing * artifact in the database. */ - public static final String SQL_UPDATE = - SQL.get("artifacts.update"); + public String SQL_UPDATE; /** * The SQL statement to touch the access time of an * artifact inside the database. */ - public static final String SQL_TOUCH = - SQL.get("artifacts.touch"); + public String SQL_TOUCH; /** * The SQL statement to load an artifact by a given * identifier from the database. */ - public static final String SQL_LOAD_BY_GID = - SQL.get("artifacts.select.gid"); + public String SQL_LOAD_BY_GID; /** * The SQL statement to get the database id of an artifact * identified by the identifier. */ - public static final String SQL_GET_ID = - SQL.get("artifacts.get.id"); + public String SQL_GET_ID; /** * The SQL statement to replace the content of an * existing artifact inside the database. */ - public static final String SQL_REPLACE = - SQL.get("artifacts.replace"); + public String SQL_REPLACE; // USER SQL - public static final String SQL_USERS_NEXT_ID = - SQL.get("users.id.nextval"); - - public static final String SQL_USERS_INSERT = - SQL.get("users.insert"); - - public static final String SQL_USERS_SELECT_ID_BY_GID = - SQL.get("users.select.id.by.gid"); - - public static final String SQL_USERS_SELECT_GID = - SQL.get("users.select.gid"); - - public static final String SQL_USERS_DELETE_ID = - SQL.get("users.delete.id"); - - public static final String SQL_USERS_DELETE_COLLECTIONS = - SQL.get("users.delete.collections"); - - public static final String SQL_USERS_SELECT_ALL = - SQL.get("users.select.all"); - - public static final String SQL_USERS_COLLECTIONS = - SQL.get("users.collections"); - - public static final String SQL_USERS_COLLECTION_IDS = - SQL.get("users.collection.ids"); - - public static final String SQL_USERS_DELETE_ALL_COLLECTIONS = - SQL.get("users.delete.all.collections"); - - public static final String SQL_ARTIFACTS_IN_ONLY_COLLECTION_ONLY = - SQL.get("artifacts.in.one.collection.only"); - - public static final String SQL_OUTDATE_ARTIFACTS_COLLECTION = - SQL.get("outdate.artifacts.collection"); - - public static final String SQL_UPDATE_COLLECTION_TTL = - SQL.get("collections.update.ttl"); - - public static final String SQL_UPDATE_COLLECTION_NAME = - SQL.get("collections.update.name"); - - public static final String SQL_OUTDATE_ARTIFACTS_USER = - SQL.get("outdate.artifacts.user"); - - public static final String SQL_DELETE_USER_COLLECTION_ITEMS = - SQL.get("delete.user.collection.items"); - - public static final String SQL_COLLECTIONS_NEXT_ID = - SQL.get("collections.id.nextval"); - - public static final String SQL_COLLECTIONS_INSERT = - SQL.get("collections.insert"); - - public static final String SQL_COLLECTIONS_SELECT_USER = - SQL.get("collections.select.user"); - - public static final String SQL_COLLECTIONS_SELECT_ALL = - SQL.get("collections.select.all"); - - public static final String SQL_COLLECTIONS_SELECT_GID = - SQL.get("collections.select.by.gid"); - - public static final String SQL_COLLECTIONS_CREATION_TIME = - SQL.get("collection.creation.time"); - - public static final String SQL_COLLECTIONS_ID_BY_GID = - SQL.get("collections.id.by.gid"); - - public static final String SQL_DELETE_COLLECTION_ITEMS = - SQL.get("delete.collection.items"); - - public static final String SQL_DELETE_COLLECTION = - SQL.get("delete.collection"); - - public static final String SQL_COLLECTION_CHECK_ARTIFACT = - SQL.get("collection.check.artifact"); - - public static final String SQL_COLLECTION_ITEMS_ID_NEXTVAL = - SQL.get("collection.items.id.nextval"); - - public static final String SQL_COLLECTION_ITEMS_INSERT = - SQL.get("collection.items.insert"); - - public static final String SQL_COLLECTION_GET_ATTRIBUTE = - SQL.get("collection.get.attribute"); - - public static final String SQL_COLLECTION_SET_ATTRIBUTE = - SQL.get("collection.set.attribute"); - - public static final String SQL_COLLECTION_ITEM_GET_ATTRIBUTE = - SQL.get("collection.item.get.attribute"); - - public static final String SQL_COLLECTION_ITEM_SET_ATTRIBUTE = - SQL.get("collection.item.set.attribute"); - - public static final String SQL_COLLECTIONS_TOUCH_BY_GID = - SQL.get("collections.touch.by.gid"); - - public static final String SQL_COLLECTION_ITEM_ID_CID_AID = - SQL.get("collection.item.id.cid.aid"); - - public static final String SQL_COLLECTION_ITEM_OUTDATE_ARTIFACT = - SQL.get("collection.item.outdate.artifact"); - - public static final String SQL_COLLECTION_ITEM_DELETE = - SQL.get("collection.item.delete"); - - public static final String SQL_COLLECTIONS_TOUCH_BY_ID = - SQL.get("collections.touch.by.id"); - - public static final String SQL_COLLECTION_ITEMS_LIST_GID = - SQL.get("collection.items.list.gid"); - - public static final String SQL_ALL_ARTIFACTS = - SQL.get("all.artifacts"); + public String SQL_USERS_NEXT_ID; + public String SQL_USERS_INSERT; + public String SQL_USERS_SELECT_ID_BY_GID; + public String SQL_USERS_SELECT_GID; + public String SQL_USERS_DELETE_ID; + public String SQL_USERS_DELETE_COLLECTIONS; + public String SQL_USERS_SELECT_ALL; + public String SQL_USERS_COLLECTIONS; + public String SQL_USERS_COLLECTION_IDS; + public String SQL_USERS_DELETE_ALL_COLLECTIONS; + public String SQL_ARTIFACTS_IN_ONLY_COLLECTION_ONLY; + public String SQL_OUTDATE_ARTIFACTS_COLLECTION; + public String SQL_UPDATE_COLLECTION_TTL; + public String SQL_UPDATE_COLLECTION_NAME; + public String SQL_OUTDATE_ARTIFACTS_USER; + public String SQL_DELETE_USER_COLLECTION_ITEMS; + public String SQL_COLLECTIONS_NEXT_ID; + public String SQL_COLLECTIONS_INSERT; + public String SQL_COLLECTIONS_SELECT_USER; + public String SQL_COLLECTIONS_SELECT_ALL; + public String SQL_COLLECTIONS_SELECT_GID; + public String SQL_COLLECTIONS_CREATION_TIME; + public String SQL_COLLECTIONS_ID_BY_GID; + public String SQL_DELETE_COLLECTION_ITEMS; + public String SQL_DELETE_COLLECTION; + public String SQL_COLLECTION_CHECK_ARTIFACT; + public String SQL_COLLECTION_ITEMS_ID_NEXTVAL; + public String SQL_COLLECTION_ITEMS_INSERT; + public String SQL_COLLECTION_GET_ATTRIBUTE; + public String SQL_COLLECTION_SET_ATTRIBUTE; + public String SQL_COLLECTION_ITEM_GET_ATTRIBUTE; + public String SQL_COLLECTION_ITEM_SET_ATTRIBUTE; + public String SQL_COLLECTIONS_TOUCH_BY_GID; + public String SQL_COLLECTION_ITEM_ID_CID_AID; + public String SQL_COLLECTION_ITEM_OUTDATE_ARTIFACT; + public String SQL_COLLECTION_ITEM_DELETE; + public String SQL_COLLECTIONS_TOUCH_BY_ID; + public String SQL_COLLECTION_ITEMS_LIST_GID; + public String SQL_ALL_ARTIFACTS; /** The singleton.*/ protected static Backend instance; + protected SQLExecutor sqlExecutor; + + protected DBConfig config; + /** * The database cleaner. Reference is stored here because * the cleaner is woken up if the backend finds an outdated @@ -336,15 +259,25 @@ public Backend() { } + public Backend(DBConfig config) { + this.config = config; + sqlExecutor = new SQLExecutor(config.getDBConnection()); + setupSQL(config.getSQL()); + } + /** * Constructor to create a backend with a link to the database cleaner. * @param cleaner The clean which periodically removes outdated * artifacts from the database. */ - public Backend(DatabaseCleaner cleaner) { + public Backend(DBConfig config, DatabaseCleaner cleaner) { + this(config); this.cleaner = cleaner; } + public DBConfig getConfig() { + return config; + } /** * Returns the singleton of this Backend. @@ -353,12 +286,69 @@ */ public static synchronized Backend getInstance() { if (instance == null) { - instance = new Backend(); + instance = new Backend(DBConfig.getInstance()); } return instance; } + protected void setupSQL(SQL sql) { + SQL_NEXT_ID = sql.get("artifacts.id.nextval"); + SQL_INSERT = sql.get("artifacts.insert"); + SQL_UPDATE = sql.get("artifacts.update"); + SQL_TOUCH = sql.get("artifacts.touch"); + SQL_LOAD_BY_GID = sql.get("artifacts.select.gid"); + SQL_GET_ID = sql.get("artifacts.get.id"); + SQL_REPLACE = sql.get("artifacts.replace"); + SQL_USERS_NEXT_ID = sql.get("users.id.nextval"); + SQL_USERS_INSERT = sql.get("users.insert"); + SQL_USERS_SELECT_ID_BY_GID = sql.get("users.select.id.by.gid"); + SQL_USERS_SELECT_GID = sql.get("users.select.gid"); + SQL_USERS_DELETE_ID = sql.get("users.delete.id"); + SQL_USERS_DELETE_COLLECTIONS = sql.get("users.delete.collections"); + SQL_USERS_SELECT_ALL = sql.get("users.select.all"); + SQL_USERS_COLLECTIONS = sql.get("users.collections"); + SQL_USERS_COLLECTION_IDS = sql.get("users.collection.ids"); + SQL_USERS_DELETE_ALL_COLLECTIONS = + sql.get("users.delete.all.collections"); + SQL_ARTIFACTS_IN_ONLY_COLLECTION_ONLY = + sql.get("artifacts.in.one.collection.only"); + SQL_OUTDATE_ARTIFACTS_COLLECTION = + sql.get("outdate.artifacts.collection"); + SQL_UPDATE_COLLECTION_TTL = sql.get("collections.update.ttl"); + SQL_UPDATE_COLLECTION_NAME = sql.get("collections.update.name"); + SQL_OUTDATE_ARTIFACTS_USER = sql.get("outdate.artifacts.user"); + SQL_DELETE_USER_COLLECTION_ITEMS = + sql.get("delete.user.collection.items"); + SQL_COLLECTIONS_NEXT_ID = sql.get("collections.id.nextval"); + SQL_COLLECTIONS_INSERT = sql.get("collections.insert"); + SQL_COLLECTIONS_SELECT_USER = sql.get("collections.select.user"); + SQL_COLLECTIONS_SELECT_ALL = sql.get("collections.select.all"); + SQL_COLLECTIONS_SELECT_GID = sql.get("collections.select.by.gid"); + SQL_COLLECTIONS_CREATION_TIME = sql.get("collection.creation.time"); + SQL_COLLECTIONS_ID_BY_GID = sql.get("collections.id.by.gid"); + SQL_DELETE_COLLECTION_ITEMS = sql.get("delete.collection.items"); + SQL_DELETE_COLLECTION = sql.get("delete.collection"); + SQL_COLLECTION_CHECK_ARTIFACT = sql.get("collection.check.artifact"); + SQL_COLLECTION_ITEMS_ID_NEXTVAL = + sql.get("collection.items.id.nextval"); + SQL_COLLECTION_ITEMS_INSERT = sql.get("collection.items.insert"); + SQL_COLLECTION_GET_ATTRIBUTE = sql.get("collection.get.attribute"); + SQL_COLLECTION_SET_ATTRIBUTE = sql.get("collection.set.attribute"); + SQL_COLLECTION_ITEM_GET_ATTRIBUTE = + sql.get("collection.item.get.attribute"); + SQL_COLLECTION_ITEM_SET_ATTRIBUTE = + sql.get("collection.item.set.attribute"); + SQL_COLLECTIONS_TOUCH_BY_GID = sql.get("collections.touch.by.gid"); + SQL_COLLECTION_ITEM_ID_CID_AID = sql.get("collection.item.id.cid.aid"); + SQL_COLLECTION_ITEM_OUTDATE_ARTIFACT = + sql.get("collection.item.outdate.artifact"); + SQL_COLLECTION_ITEM_DELETE = sql.get("collection.item.delete"); + SQL_COLLECTIONS_TOUCH_BY_ID = sql.get("collections.touch.by.id"); + SQL_COLLECTION_ITEMS_LIST_GID = sql.get("collection.items.list.gid"); + SQL_ALL_ARTIFACTS = sql.get("all.artifacts"); + } + /** * Sets the factory lookup mechanism to decouple ArtifactDatabase * and Backend. @@ -511,7 +501,7 @@ final Object [] loaded = new Object[1]; - SQLExecutor exec = new SQLExecutor() { + SQLExecutor.Instance exec = sqlExecutor.new Instance() { public boolean doIt() throws SQLException { prepareStatement(SQL_LOAD_BY_GID); stmnt.setString(1, identifer); @@ -601,7 +591,7 @@ final int [] id = new int[1]; - SQLExecutor exec = new SQLExecutor() { + SQLExecutor.Instance exec = sqlExecutor.new Instance() { public boolean doIt() throws SQLException { prepareStatement(SQL_GET_ID); @@ -687,7 +677,7 @@ ) { final int [] id = new int[1]; - SQLExecutor exec = new SQLExecutor() { + SQLExecutor.Instance exec = sqlExecutor.new Instance() { public boolean doIt() throws SQLException { prepareStatement(SQL_NEXT_ID); result = stmnt.executeQuery(); @@ -738,7 +728,7 @@ * @param artifact The persistent wrapper around the living artifact. */ public void touch(final PersistentArtifact artifact) { - new SQLExecutor() { + sqlExecutor.new Instance() { public boolean doIt() throws SQLException { prepareStatement(SQL_TOUCH); stmnt.setInt(1, artifact.getId()); @@ -755,7 +745,7 @@ * artifact. */ public void store(final PersistentArtifact artifact) { - new SQLExecutor() { + sqlExecutor.new Instance() { public boolean doIt() throws SQLException { prepareStatement(SQL_UPDATE); stmnt.setInt(2, artifact.getId()); @@ -782,7 +772,7 @@ final byte [] roleData = XMLUtils.toByteArray(role, true); - SQLExecutor exec = new SQLExecutor() { + SQLExecutor.Instance exec = sqlExecutor.new Instance() { public boolean doIt() throws SQLException { prepareStatement(SQL_USERS_NEXT_ID); @@ -829,7 +819,7 @@ return false; } - SQLExecutor exec = new SQLExecutor() { + SQLExecutor.Instance exec = sqlExecutor.new Instance() { public boolean doIt() throws SQLException { prepareStatement(SQL_USERS_SELECT_ID_BY_GID); @@ -895,7 +885,7 @@ final User [] user = new User[1]; - SQLExecutor exec = new SQLExecutor() { + SQLExecutor.Instance exec = sqlExecutor.new Instance() { public boolean doIt() throws SQLException { prepareStatement(SQL_USERS_SELECT_GID); stmnt.setString(1, identifier); @@ -924,7 +914,7 @@ ) { final ArrayList<User> users = new ArrayList<User>(); - SQLExecutor exec = new SQLExecutor() { + SQLExecutor.Instance exec = sqlExecutor.new Instance() { public boolean doIt() throws SQLException { prepareStatement(SQL_USERS_SELECT_ALL); result = stmnt.executeQuery(); @@ -970,7 +960,7 @@ final byte [] data = XMLUtils.toByteArray(attribute, true); - SQLExecutor exec = new SQLExecutor() { + SQLExecutor.Instance exec = sqlExecutor.new Instance() { public boolean doIt() throws SQLException { // fetch owner id prepareStatement(SQL_USERS_SELECT_ID_BY_GID); @@ -1065,7 +1055,7 @@ final ArtifactCollection[] ac = new ArtifactCollection[1]; - SQLExecutor exec = new SQLExecutor() { + SQLExecutor.Instance exec = sqlExecutor.new Instance() { public boolean doIt() throws SQLException { prepareStatement(SQL_COLLECTIONS_SELECT_GID); @@ -1126,7 +1116,7 @@ final ArrayList<ArtifactCollection> collections = new ArrayList<ArtifactCollection>(); - SQLExecutor exec = new SQLExecutor() { + SQLExecutor.Instance exec = sqlExecutor.new Instance() { public boolean doIt() throws SQLException { @@ -1188,7 +1178,7 @@ logger.debug("Invalid collection id: '" + collectionId + "'"); return false; } - SQLExecutor exec = new SQLExecutor() { + SQLExecutor.Instance exec = sqlExecutor.new Instance() { public boolean doIt() throws SQLException { // fetch collection id prepareStatement(SQL_COLLECTIONS_ID_BY_GID); @@ -1232,7 +1222,7 @@ final byte[][] data = new byte[1][1]; - SQLExecutor exec = new SQLExecutor() { + SQLExecutor.Instance exec = sqlExecutor.new Instance() { public boolean doIt() throws SQLException { prepareStatement(SQL_COLLECTION_GET_ATTRIBUTE); stmnt.setString(1, collectionId); @@ -1263,7 +1253,7 @@ final byte [] data = XMLUtils.toByteArray(attribute, true); - return new SQLExecutor() { + return sqlExecutor.new Instance() { public boolean doIt() throws SQLException { // set the column in collection items @@ -1304,7 +1294,7 @@ final byte [][] data = new byte[1][1]; - SQLExecutor exec = new SQLExecutor() { + SQLExecutor.Instance exec = sqlExecutor.new Instance() { public boolean doIt() throws SQLException { prepareStatement(SQL_COLLECTION_ITEM_GET_ATTRIBUTE); stmnt.setString(1, collectionId); @@ -1340,7 +1330,7 @@ final byte [] data = XMLUtils.toByteArray(attribute, true); - return new SQLExecutor() { + return sqlExecutor.new Instance() { public boolean doIt() throws SQLException { // set the column in collection items @@ -1384,7 +1374,7 @@ final byte [] data = XMLUtils.toByteArray(attribute, true); - SQLExecutor exec = new SQLExecutor() { + SQLExecutor.Instance exec = sqlExecutor.new Instance() { public boolean doIt() throws SQLException { // fetch artifact id prepareStatement(SQL_GET_ID); @@ -1457,7 +1447,7 @@ logger.debug("Invalid collection id: '" + collectionId + "'"); return false; } - return new SQLExecutor() { + return sqlExecutor.new Instance() { public boolean doIt() throws SQLException { // fetch id, collection id and artitfact id @@ -1510,7 +1500,7 @@ final ArrayList<CollectionItem> collectionItems = new ArrayList<CollectionItem>(); - SQLExecutor exec = new SQLExecutor() { + SQLExecutor.Instance exec = sqlExecutor.new Instance() { public boolean doIt() throws SQLException { prepareStatement(SQL_COLLECTION_ITEMS_LIST_GID); stmnt.setString(1, collectionId); @@ -1538,7 +1528,7 @@ return false; } - return new SQLExecutor() { + return sqlExecutor.new Instance() { public boolean doIt() throws SQLException { prepareStatement(SQL_UPDATE_COLLECTION_TTL); if (ttl == null) { @@ -1563,7 +1553,7 @@ return false; } - return new SQLExecutor() { + return sqlExecutor.new Instance() { public boolean doIt() throws SQLException { prepareStatement(SQL_UPDATE_COLLECTION_NAME); stmnt.setString(1, name); @@ -1583,7 +1573,7 @@ return false; } - return new SQLExecutor() { + return sqlExecutor.new Instance() { public boolean doIt() throws SQLException { // a little cache to avoid too much deserializations.
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/DBConfig.java Thu Jul 28 10:19:35 2011 +0000 @@ -0,0 +1,83 @@ +package de.intevation.artifactdatabase; + +import de.intevation.artifacts.common.utils.Config; + +import de.intevation.artifactdatabase.db.SQL; +import de.intevation.artifactdatabase.db.DBConnection; + +public class DBConfig +{ + /** + * XPath to access the database driver within the global configuration. + */ + public static final String DB_DRIVER = + "/artifact-database/database/driver/text()"; + /** + * XPath to access the database URL within the global configuration. + */ + public static final String DB_URL = + "/artifact-database/database/url/text()"; + /** + * XPath to access the database use within the global configuration. + */ + public static final String DB_USER = + "/artifact-database/database/user/text()"; + /** + * XPath to access the database password within the global configuration. + */ + public static final String DB_PASSWORD = + "/artifact-database/database/password/text()"; + + private static DBConfig instance; + + private DBConnection dbConnection; + private SQL sql; + + private DBConfig() { + } + + private DBConfig(DBConnection dbConnection, SQL sql) { + this.dbConnection = dbConnection; + this.sql = sql; + } + + public static synchronized DBConfig getInstance() { + if (instance == null) { + instance = createInstance(); + } + return instance; + } + + public SQL getSQL() { + return sql; + } + + public DBConnection getDBConnection() { + return dbConnection; + } + + private static DBConfig createInstance() { + + String driver = Config.getStringXPath( + DB_DRIVER, DBConnection.DEFAULT_DRIVER); + + String url = Config.getStringXPath( + DB_URL, DBConnection.DEFAULT_URL); + + url = Config.replaceConfigDir(url); + + String user = Config.getStringXPath( + DB_USER, DBConnection.DEFAULT_USER); + + String password = Config.getStringXPath( + DB_PASSWORD, DBConnection.DEFAULT_PASSWORD); + + DBConnection dbConnection = new DBConnection( + driver, url, user, password); + + SQL sql = new SQL(driver); + + return new DBConfig(dbConnection, sql); + } +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :
--- a/artifact-database/src/main/java/de/intevation/artifactdatabase/DBConnection.java Wed Jul 27 13:51:30 2011 +0000 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,149 +0,0 @@ -/* - * Copyright (c) 2010 by Intevation GmbH - * - * This program is free software under the LGPL (>=v2.1) - * Read the file LGPL.txt coming with the software for details - * or visit http://www.gnu.org/licenses/ if it does not exist. - */ - -package de.intevation.artifactdatabase; - -import de.intevation.artifacts.common.utils.Config; - -import java.io.File; - -import java.sql.SQLException; - -import javax.sql.DataSource; - -import org.apache.commons.dbcp.BasicDataSource; - -import org.apache.log4j.Logger; - -/** - * This class encapsulate the creation and pooling of database connections used - * by the artifact database. The credential to open the database connections - * are taken from the global configuratiion. - * @author <a href="mailto:sascha.teichmann@intevation.de">Sascha L. Teichmann</a> - */ -public class DBConnection -{ - private static Logger logger = Logger.getLogger(DBConnection.class); - - /** - * XPath to access the database driver within the global configuration. - */ - public static final String DB_DRIVER = - "/artifact-database/database/driver/text()"; - /** - * XPath to access the database URL within the global configuration. - */ - public static final String DB_URL = - "/artifact-database/database/url/text()"; - /** - * XPath to access the database use within the global configuration. - */ - public static final String DB_USER = - "/artifact-database/database/user/text()"; - /** - * XPath to access the database password within the global configuration. - */ - public static final String DB_PASSWORD = - "/artifact-database/database/password/text()"; - - /** - * The default database driver: H2 - */ - public static final String DEFAULT_DRIVER = - "org.h2.Driver"; - - /** - * The default database name: artifacts.db - */ - public static final String DEFAULT_DATABASE_FILE = - "artifacts.db"; - - /** - * The default database URL: This is created once by #getDefaultURL() - */ - public static final String DEFAULT_URL = getDefaultURL(); - - /** - * The default database user: "" - */ - public static final String DEFAULT_USER = ""; - - /** - * The default database password: "" - */ - public static final String DEFAULT_PASSWORD = ""; - - private DBConnection() { - } - - /** - * Constructs the default databse URL. It concats the - * config directory and the #DEFAULT_DATABASE_FILE - * to the string with is needed to access H2 databases. - * @return The default URL. - */ - public static final String getDefaultURL() { - File configDir = Config.getConfigDirectory(); - File databaseFile = new File(configDir, DEFAULT_DATABASE_FILE); - return "jdbc:h2:" + databaseFile; - } - - private static BasicDataSource dataSource; - - private static final void addShutdownHook() { - Runtime.getRuntime().addShutdownHook(new Thread() { - @Override - public void run() { - if (dataSource != null) { - try { - dataSource.close(); - } - catch (SQLException sqle) { - } - dataSource = null; - } - } - }); - } - - /** - * Static method to fetch a database connection. - * @return a DataSource to access the database connection. - */ - public static synchronized DataSource getDataSource() { - if (dataSource == null) { - dataSource = new BasicDataSource(); - - String driver = Config.getStringXPath( - DB_DRIVER, DEFAULT_DRIVER); - - String url = Config.getStringXPath( - DB_URL, DEFAULT_URL); - - url = Config.replaceConfigDir(url); - - String user = Config.getStringXPath( - DB_USER, DEFAULT_USER); - - String password = Config.getStringXPath( - DB_PASSWORD, DEFAULT_PASSWORD); - - logger.info("database driver: " + driver); - logger.info("database url: " + url); - - dataSource.setDriverClassName(driver); - dataSource.setUsername(user); - dataSource.setPassword(password); - dataSource.setUrl(url); - addShutdownHook(); - } - - return dataSource; - } -} -// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :
--- a/artifact-database/src/main/java/de/intevation/artifactdatabase/DatabaseCleaner.java Wed Jul 27 13:51:30 2011 +0000 +++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/DatabaseCleaner.java Thu Jul 28 10:19:35 2011 +0000 @@ -13,6 +13,9 @@ import de.intevation.artifacts.Artifact; +import de.intevation.artifactdatabase.db.SQL; +import de.intevation.artifactdatabase.db.DBConnection; + import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; @@ -78,23 +81,16 @@ /** * The SQL statement to select the outdated artifacts. */ - public static final String SQL_OUTDATED = - SQL.get("artifacts.outdated"); + public String SQL_OUTDATED; - public static final String SQL_OUTDATED_COLLECTIONS = - SQL.get("collections.outdated"); - - public static final String SQL_DELETE_COLLECTION_ITEMS = - SQL.get("delete.collection.items"); - - public static final String SQL_DELETE_COLLECTION = - SQL.get("delete.collection"); + public String SQL_OUTDATED_COLLECTIONS; + public String SQL_DELETE_COLLECTION_ITEMS; + public String SQL_DELETE_COLLECTION; /** * The SQL statement to delete some artifacts from the database. */ - public static final String SQL_DELETE_ARTIFACT = - SQL.get("artifacts.delete"); + public String SQL_DELETE_ARTIFACT; /** * XPath to figure out how long the cleaner should sleep between @@ -136,6 +132,8 @@ */ protected ArtifactReviver reviver; + protected DBConnection dbConnection; + /** * Default constructor. */ @@ -148,11 +146,21 @@ * @param context The global context of the artifact database * @param reviver The reviver to awake artifact one last time. */ - public DatabaseCleaner(Object context, ArtifactReviver reviver) { + public DatabaseCleaner(Object context, ArtifactReviver reviver, DBConfig config) { setDaemon(true); sleepTime = getSleepTime(); this.context = context; this.reviver = reviver; + this.dbConnection = config.getDBConnection(); + setupSQL(config.getSQL()); + } + + protected void setupSQL(SQL sql) { + SQL_OUTDATED = sql.get("artifacts.outdated"); + SQL_OUTDATED_COLLECTIONS = sql.get("collections.outdated"); + SQL_DELETE_COLLECTION_ITEMS = sql.get("delete.collection.items"); + SQL_DELETE_COLLECTION = sql.get("delete.collection"); + SQL_DELETE_ARTIFACT = sql.get("artifacts.delete"); } /** @@ -231,7 +239,7 @@ int removedCollections = 0; int removedArtifacts = 0; - DataSource dataSource = DBConnection.getDataSource(); + DataSource dataSource = dbConnection.getDataSource(); Set<Integer> lockedIds = lockedIdsProvider != null ? lockedIdsProvider.getLockedIds()
--- a/artifact-database/src/main/java/de/intevation/artifactdatabase/SQL.java Wed Jul 27 13:51:30 2011 +0000 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,104 +0,0 @@ -/* - * Copyright (c) 2010 by Intevation GmbH - * - * This program is free software under the LGPL (>=v2.1) - * Read the file LGPL.txt coming with the software for details - * or visit http://www.gnu.org/licenses/ if it does not exist. - */ - -package de.intevation.artifactdatabase; - -import de.intevation.artifacts.common.utils.Config; - -import java.io.IOException; -import java.io.InputStream; - -import java.util.Properties; - -import org.apache.log4j.Logger; - -/** - * Singleton to provide SQL statement strings as key/value pairs. - * This mechanism is used to encapsulate database specific SQL - * dialects. - * - * @author <a href="mailto:sascha.teichmann@intevation.de">Sascha L. Teichmann</a> - */ -public final class SQL -{ - private static Logger logger = Logger.getLogger(SQL.class); - - private SQL() { - } - - private static Properties statements; - - /** - * Returns key/value pairs of SQL statements for the used database - * backend. - * The concrete set of SQL statements is determined by the - * used JDBC database driver which is configured in conf.xml. - * The class name of the driver is transformed by replacing - * all '.' with '_' and lower case the resulting string. - * The transformed string is used to load a properties file - * in '/sql/' which should contain the statements. - * Example:<br> - * <code>org.postgresql.Driver</code> results in loading of - * <code>/sql/org-postgresql-driver.properties</code>. - * @return The key/value pairs of SQL statements. - */ - public static final synchronized Properties getStatements() { - if (statements == null) { - statements = loadStatements(); - } - return statements; - } - - private static final Properties loadStatements() { - String driver = Config.getStringXPath( - DBConnection.DB_DRIVER, DBConnection.DEFAULT_DRIVER); - - Properties properties = new Properties(); - - InputStream in = null; - try { - String res = "/sql/" + driver.replace('.', '-').toLowerCase() - + ".properties"; - in = SQL.class.getResourceAsStream(res); - - if (in == null) { - logger.warn("No SQL file for driver '" + driver + "' found."); - res = "/sql/" - + DBConnection.DEFAULT_DRIVER.replace('.', '-') - .toLowerCase() - + ".properties"; - if ((in = SQL.class.getResourceAsStream(res)) == null) { - logger.error("No SQL file found"); - } - } - - properties.load(in); - } - catch (IOException ioe) { - logger.error(ioe.getLocalizedMessage(), ioe); - } - finally { - if (in != null) { - try { in.close(); } catch (IOException ioe) {} - } - } - - return properties; - } - - /** - * Returns a particular SQL statement for a given key. - * @param key The key of the statement. - * @return The corresponing SQL statement or null if none - * is found. - */ - public static final String get(String key) { - return getStatements().getProperty(key); - } -} -// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :
--- a/artifact-database/src/main/java/de/intevation/artifactdatabase/SQLExecutor.java Wed Jul 27 13:51:30 2011 +0000 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,102 +0,0 @@ -/* - * Copyright (c) 2011 by Intevation GmbH - * - * This program is free software under the LGPL (>=v2.1) - * Read the file LGPL.txt coming with the software for details - * or visit http://www.gnu.org/licenses/ if it does not exist. - */ -package de.intevation.artifactdatabase; - -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; - -import javax.sql.DataSource; - -import org.apache.log4j.Logger; - -public class SQLExecutor { - - private static Logger logger = Logger.getLogger(SQLExecutor.class); - - public Connection conn; - public PreparedStatement stmnt; - public ResultSet result; - - public SQLExecutor() { - } - - public void reset() throws SQLException { - if (result != null) { - result.close(); - result = null; - } - if (stmnt != null) { - result = null; - stmnt.close(); - } - } - - public PreparedStatement prepareStatement(String query) - throws SQLException { - return stmnt = conn.prepareStatement(query); - } - - public void close() { - if (result != null) { - try { result.close(); } - catch (SQLException sqle) {} - } - if (stmnt != null) { - try { stmnt.close(); } - catch (SQLException sqle) {} - } - if (conn != null) { - try { conn.close(); } - catch (SQLException sqle) {} - } - } - - public boolean runWrite() { - DataSource dataSource = DBConnection.getDataSource(); - try { - conn = dataSource.getConnection(); - try { - conn.setAutoCommit(false); - return doIt(); - } - catch (SQLException sqle) { - conn.rollback(); - throw sqle; - } - } - catch (SQLException sqle) { - logger.error(sqle.getLocalizedMessage(), sqle); - } - finally { - close(); - } - return false; - } - - public boolean runRead() { - DataSource dataSource = DBConnection.getDataSource(); - try { - conn = dataSource.getConnection(); - return doIt(); - } - catch (SQLException sqle) { - logger.error(sqle.getLocalizedMessage(), sqle); - } - finally { - close(); - } - return false; - } - - public boolean doIt() throws SQLException { - return true; - } -} -// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/db/DBConnection.java Thu Jul 28 10:19:35 2011 +0000 @@ -0,0 +1,110 @@ +package de.intevation.artifactdatabase.db; + +import java.sql.SQLException; + +import javax.sql.DataSource; + +import java.io.File; + +import org.apache.commons.dbcp.BasicDataSource; + +import de.intevation.artifacts.common.utils.Config; + +public class DBConnection +{ + public static final String DEFAULT_DRIVER = "org.h2.Driver"; + public static final String DEFAULT_USER = ""; + public static final String DEFAULT_PASSWORD = ""; + public static final String DEFAULT_DATABASE_FILE = "artifacts.db"; + public static final String DEFAULT_URL = getDefaultURL(); + + public static final String getDefaultURL() { + File configDir = Config.getConfigDirectory(); + File databaseFile = new File(configDir, DEFAULT_DATABASE_FILE); + return "jdbc:h2:" + databaseFile; + } + + protected BasicDataSource dataSource; + + protected String driver; + protected String url; + protected String user; + protected String password; + + public DBConnection() { + } + + public DBConnection( + String driver, + String url, + String user, + String password + ) { + this.driver = driver; + this.url = url; + this.user = user; + this.password = password; + } + + public String getUser() { + return user; + } + + public void setUser(String user) { + this.user = user; + } + + public String getPassword() { + return password; + } + + public void setPassword(String password) { + this.password = password; + } + + public String getDriver() { + return driver; + } + + public void setDriver(String driver) { + this.driver = driver; + } + + public String getUrl() { + return url; + } + + public void setUrl(String url) { + this.url = url; + } + + protected void addShutdownHook() { + Runtime.getRuntime().addShutdownHook(new Thread() { + @Override + public void run() { + if (dataSource != null) { + try { + dataSource.close(); + } + catch (SQLException sqle) { + } + dataSource = null; + } + } + }); + } + + public synchronized DataSource getDataSource() { + if (dataSource == null) { + dataSource = new BasicDataSource(); + + dataSource.setDriverClassName(driver); + dataSource.setUsername(user); + dataSource.setPassword(password); + dataSource.setUrl(url); + addShutdownHook(); + } + return dataSource; + } +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/db/SQL.java Thu Jul 28 10:19:35 2011 +0000 @@ -0,0 +1,90 @@ +package de.intevation.artifactdatabase.db; + +import java.util.Properties; + +import java.io.IOException; +import java.io.InputStream; + +import org.apache.log4j.Logger; + +public class SQL { + + private static Logger logger = Logger.getLogger(SQL.class); + + protected Properties statements; + + public SQL() { + } + + public SQL(String driver) { + this(SQL.class, driver); + } + + public SQL(Class clazz, String driver) { + this(clazz, "/sql", driver); + } + + public SQL(Class clazz, String resourcePath, String driver) { + } + + public static final String driverToProperties(String driver) { + return driver.replace('.', '-').toLowerCase() + ".properties"; + } + + /** + * Returns key/value pairs of SQL statements for the used database + * backend. + * The concrete set of SQL statements is determined by the + * used JDBC database driver which is configured in conf.xml. + * The class name of the driver is transformed by replacing + * all '.' with '_' and lower case the resulting string. + * The transformed string is used to load a properties file + * in '/sql/' which should contain the statements. + * Example:<br> + * <code>org.postgresql.Driver</code> results in loading of + * <code>/sql/org-postgresql-driver.properties</code>. + * @return The key/value pairs of SQL statements. + */ + protected Properties loadStatements( + Class clazz, + String resourcePath, + String driver + ) { + Properties properties = new Properties(); + + String resDriver = driverToProperties(driver); + + InputStream in = null; + try { + String res = resourcePath + "/" + resDriver; + + in = clazz.getResourceAsStream(res); + + if (in == null) { + logger.warn("No SQL file for driver '" + driver + "' found."); + resDriver = driverToProperties(DBConnection.DEFAULT_DRIVER); + res = resourcePath + "/" + resDriver; + + in = clazz.getResourceAsStream(res); + if (in == null) { + logger.error("No SQL file for driver '" + + DBConnection.DEFAULT_DRIVER + "' found."); + } + } + + if (in != null) { + properties.load(in); + } + } + catch (IOException ioe) { + logger.error(ioe); + } + + return properties; + } + + public String get(String key) { + return statements.getProperty(key); + } +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/db/SQLExecutor.java Thu Jul 28 10:19:35 2011 +0000 @@ -0,0 +1,111 @@ +package de.intevation.artifactdatabase.db; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; + +import javax.sql.DataSource; + +import org.apache.log4j.Logger; + +public class SQLExecutor +{ + private static Logger logger = Logger.getLogger(SQLExecutor.class); + + public class Instance { + + public Connection conn; + public PreparedStatement stmnt; + public ResultSet result; + + public Instance() { + } + + public void reset() throws SQLException { + if (result != null) { + result.close(); + result = null; + } + if (stmnt != null) { + result = null; + stmnt.close(); + } + } + + public PreparedStatement prepareStatement(String query) + throws SQLException { + return stmnt = conn.prepareStatement(query); + } + + public void close() { + if (result != null) { + try { result.close(); } + catch (SQLException sqle) {} + } + if (stmnt != null) { + try { stmnt.close(); } + catch (SQLException sqle) {} + } + if (conn != null) { + try { conn.close(); } + catch (SQLException sqle) {} + } + } + + public boolean runWrite() { + DataSource dataSource = dbConnection.getDataSource(); + try { + conn = dataSource.getConnection(); + try { + conn.setAutoCommit(false); + return doIt(); + } + catch (SQLException sqle) { + conn.rollback(); + throw sqle; + } + } + catch (SQLException sqle) { + logger.error(sqle.getLocalizedMessage(), sqle); + } + finally { + close(); + } + return false; + } + + public boolean runRead() { + DataSource dataSource = dbConnection.getDataSource(); + try { + conn = dataSource.getConnection(); + return doIt(); + } + catch (SQLException sqle) { + logger.error(sqle.getLocalizedMessage(), sqle); + } + finally { + close(); + } + return false; + } + + public boolean doIt() throws SQLException { + return true; + } + } // class Instance + + protected DBConnection dbConnection; + + public SQLExecutor() { + } + + public SQLExecutor(DBConnection dbConnection) { + this.dbConnection = dbConnection; + } + + public DBConnection getDBConnection() { + return dbConnection; + } +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :
--- a/artifact-database/src/main/java/de/intevation/artifactdatabase/h2/CollectionAccessUpdateTrigger.java Wed Jul 27 13:51:30 2011 +0000 +++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/h2/CollectionAccessUpdateTrigger.java Thu Jul 28 10:19:35 2011 +0000 @@ -6,7 +6,9 @@ import java.sql.SQLException; import java.sql.PreparedStatement; -import de.intevation.artifactdatabase.SQL; +import de.intevation.artifactdatabase.DBConfig; + +import de.intevation.artifactdatabase.db.SQL; import org.apache.log4j.Logger; @@ -16,8 +18,7 @@ private static Logger logger = Logger.getLogger(CollectionAccessUpdateTrigger.class); - public static final String COLLECTIONS_TOUCH_TRIGGER_FUNCTION = - SQL.get("collections.touch.trigger.function"); + public String COLLECTIONS_TOUCH_TRIGGER_FUNCTION; public void init( Connection conn, @@ -29,6 +30,12 @@ ) throws SQLException { logger.debug("CollectionAccessUpdateTrigger.init"); + setupSQL(DBConfig.getInstance().getSQL()); + } + + protected void setupSQL(SQL sql) { + COLLECTIONS_TOUCH_TRIGGER_FUNCTION = + sql.get("collections.touch.trigger.function"); } public void fire(