Mercurial > dive4elements > river
changeset 979:2306340d7540
Added schema for datacage db
flys-artifacts/trunk@2406 c6561f87-3c4e-4783-a992-168aeb5c3f6f
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Tue, 26 Jul 2011 14:14:55 +0000 |
| parents | 5e40fe173a53 |
| children | f9a6a9cd918e |
| files | flys-artifacts/ChangeLog flys-artifacts/doc/conf/datacage.sql flys-artifacts/pom.xml flys-artifacts/src/main/java/de/intevation/flys/artifacts/datacage/DBConnection.java |
| diffstat | 4 files changed, 176 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- a/flys-artifacts/ChangeLog Tue Jul 26 09:50:58 2011 +0000 +++ b/flys-artifacts/ChangeLog Tue Jul 26 14:14:55 2011 +0000 @@ -1,3 +1,13 @@ +2011-07-26 Sascha L. Teichmann <sascha.teichmann@intevation.de> + + * doc/conf/datacage.sql: New. H2 Schema for the datacage database. + Uses special features like IDENTITY (autoincrement) typed columns. + + * src/main/java/de/intevation/flys/artifacts/datacage/DBConnection.java: + Pooled connection. + + * pom.xml: Added dependencies to H2 and Apache DBCP. + 2011-07-26 Sascha L. Teichmann <sascha.teichmann@intevation.de> * src/test/java/de/intevation/flys/artifacts/AppTest.java,
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-artifacts/doc/conf/datacage.sql Tue Jul 26 14:14:55 2011 +0000 @@ -0,0 +1,45 @@ +BEGIN; + +CREATE TABLE users ( + id IDENTITY PRIMARY KEY NOT NULL, + gid UUID NOT NULL UNIQUE +); + +CREATE TABLE collections ( + id IDENTITY PRIMARY KEY NOT NULL, + gid UUID NOT NULL UNIQUE, + user_id INT NOT NULL REFERENCES users(id), + name VARCHAR(256) NOT NULL +); + +CREATE TABLE artifacts ( + id IDENTITY PRIMARY KEY NOT NULL, + gid UUID NOT NULL UNIQUE, + state VARCHAR(256) NOT NULL +); + +CREATE TABLE collection_items ( + id IDENTITY PRIMARY KEY NOT NULL, + collection_id INT NOT NULL REFERENCES collections(id), + artifact_id INT NOT NULL REFERENCES artifacts(id) +); + +CREATE TABLE artifact_data ( + id IDENTITY PRIMARY KEY NOT NULL, + artifact_id INT NOT NULL REFERENCES artifacts(id), + k VARCHAR(256) NOT NULL, + v VARCHAR(256), -- Maybe too short + UNIQUE (artifact_id, k) +); + +CREATE TABLE facets ( + id IDENTITY PRIMARY KEY NOT NULL, + artifact_id INT NOT NULL REFERENCES artifacts(id), + name VARCHAR(256) NOT NULL, + num INT NOT NULL, + output VARCHAR(256) NOT NULL, + description VARCHAR(256), + UNIQUE (artifact_id, output, num, name) +); + +COMMIT;
--- a/flys-artifacts/pom.xml Tue Jul 26 09:50:58 2011 +0000 +++ b/flys-artifacts/pom.xml Tue Jul 26 14:14:55 2011 +0000 @@ -95,6 +95,16 @@ <artifactId>commons-math</artifactId> <version>2.2</version> </dependency> + <dependency> + <groupId>com.h2database</groupId> + <artifactId>h2</artifactId> + <version>1.3.158</version> + </dependency> + <dependency> + <groupId>commons-dbcp</groupId> + <artifactId>commons-dbcp</artifactId> + <version>1.2.2</version> + </dependency> </dependencies> <repositories> <repository>
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-artifacts/src/main/java/de/intevation/flys/artifacts/datacage/DBConnection.java Tue Jul 26 14:14:55 2011 +0000 @@ -0,0 +1,111 @@ +package de.intevation.flys.artifacts.datacage; + +import de.intevation.artifacts.common.utils.Config; + +import java.sql.SQLException; + +import javax.sql.DataSource; + +import org.apache.commons.dbcp.BasicDataSource; + +import org.apache.log4j.Logger; + +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/datacage/driver/text()"; + /** + * XPath to access the database URL within the global configuration. + */ + public static final String DB_URL = + "/artifact-database/datacage/url/text()"; + /** + * XPath to access the database use within the global configuration. + */ + public static final String DB_USER = + "/artifact-database/datacage/user/text()"; + /** + * XPath to access the database password within the global configuration. + */ + public static final String DB_PASSWORD = + "/artifact-database/datacage/password/text()"; + + /** + * The default database driver: H2 + */ + public static final String DEFAULT_DRIVER = + "org.h2.Driver"; + + /** + * The default database user: "" + */ + public static final String DEFAULT_USER = ""; + + /** + * The default database password: "" + */ + public static final String DEFAULT_PASSWORD = ""; + + + public static final String DEFAULT_URL = + "jdbc:h2:mem;INIT=RUNSCRIPT FROM '${artifacts.config.dir}/datacage.sql'"; + + private static BasicDataSource dataSource; + + private DBConnection() { + } + + 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; + } + } + }); + + } + + 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 :