# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1311689695 0
# Node ID 2306340d754038cf8747f1ef10a38cf405f88529
# Parent  5e40fe173a536c63e5c3a207ac982fec70035a7e
Added schema for datacage db

flys-artifacts/trunk@2406 c6561f87-3c4e-4783-a992-168aeb5c3f6f

diff -r 5e40fe173a53 -r 2306340d7540 flys-artifacts/ChangeLog
--- 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,
diff -r 5e40fe173a53 -r 2306340d7540 flys-artifacts/doc/conf/datacage.sql
--- /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;
diff -r 5e40fe173a53 -r 2306340d7540 flys-artifacts/pom.xml
--- 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>
diff -r 5e40fe173a53 -r 2306340d7540 flys-artifacts/src/main/java/de/intevation/flys/artifacts/datacage/DBConnection.java
--- /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 :