# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1253266454 0
# Node ID 93edc04f3a105ba6aa665617ceac4645575c549b
# Parent  3f03aee55c2f8d21ad720127b99bb3a1c0f84628
Added postgresql support.


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

diff -r 3f03aee55c2f -r 93edc04f3a10 Changelog
--- a/Changelog	Wed Sep 16 12:43:03 2009 +0000
+++ b/Changelog	Fri Sep 18 09:34:14 2009 +0000
@@ -1,14 +1,34 @@
+2009-09-18	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
+
+	* artifact-database/doc/schema.sql: removed.
+	* artifact-database/doc/schema-h2.sql: re-added here. It is H2 specific.
+
+	* artifact-database/doc/schema-pg.sql: New. Schema for PostgreSQL 8.3 +
+
+	* artifact-database/src/main/resources/sql/org-postgresql-driver.properties: New.
+	SQL statements for PostgreSQL.
+
+	* artifact-database/src/main/java/de/intevation/artifactdatabase/DBConnection.java:
+	XPath expressions to access config were wrong. Added some debug logging.
+
+	* artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java:
+	Added some debug logging.
+
+	* artifact-database/pom.xml: Added dependency to postgresql JDBC driver
+
+	* contrib/run.sh: Added postgresql JDBC to CLASSPATH.
+
 2009-09-16  Tim Englich  <tim.englich@intevation.de>
 
-    * src/main/java/de/intevation/artifactdatabase/rest/ArtifactOutResource.java (represent) Edited: 
-      Switched from Get to Post communication.
-      
+	* src/main/java/de/intevation/artifactdatabase/rest/ArtifactOutResource.java (represent) Edited: 
+	Switched from Get to Post communication.
+
 2009-09-16  Tim Englich  <tim.englich@intevation.de>
 
-    * src/main/java/de/intevation/artifactdatabase/ArtifactDatabaseImpl.java (feed) Edited:
-      Bug fixed: Now the method will call the feed Method of the used Artifact instead 
-      of the advance Method. 
-      
+	* src/main/java/de/intevation/artifactdatabase/ArtifactDatabaseImpl.java (feed) Edited:
+	Bug fixed: Now the method will call the feed Method of the used Artifact instead 
+	of the advance Method.
+
 2009-09-15	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
 
 	* artifact-database/src/main/java/de/intevation/artifactdatabase/rest/ArtifactResource.java:
diff -r 3f03aee55c2f -r 93edc04f3a10 artifact-database/doc/schema-h2.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/artifact-database/doc/schema-h2.sql	Fri Sep 18 09:34:14 2009 +0000
@@ -0,0 +1,20 @@
+--
+-- schema to store artifacts in H2 databases.
+--
+
+BEGIN;
+
+-- not using AUTO_INCREMENT to be more compatible with
+-- other dbms.
+CREATE SEQUENCE ARTIFACTS_ID_SEQ;
+
+CREATE TABLE artifacts (
+    id          INT PRIMARY KEY NOT NULL,
+    gid         UUID            NOT NULL UNIQUE,
+    creation    TIMESTAMP       NOT NULL,
+    last_access TIMESTAMP       NOT NULL,
+    ttl         BIGINT, -- NULL means eternal
+    data        BINARY
+);
+
+COMMIT;
diff -r 3f03aee55c2f -r 93edc04f3a10 artifact-database/doc/schema-pg.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/artifact-database/doc/schema-pg.sql	Fri Sep 18 09:34:14 2009 +0000
@@ -0,0 +1,20 @@
+--
+-- schema to store artifacts in PostgreSQL databases.
+--
+
+BEGIN;
+
+-- not using AUTO_INCREMENT to be more compatible with
+-- other dbms.
+CREATE SEQUENCE ARTIFACTS_ID_SEQ;
+
+CREATE TABLE artifacts (
+    id          int PRIMARY KEY NOT NULL,
+    gid         uuid            NOT NULL UNIQUE,
+    creation    timestamp       NOT NULL,
+    last_access timestamp       NOT NULL,
+    ttl         bigint, -- NULL means eternal
+    data        bytea
+);
+
+COMMIT;
diff -r 3f03aee55c2f -r 93edc04f3a10 artifact-database/doc/schema.sql
--- a/artifact-database/doc/schema.sql	Wed Sep 16 12:43:03 2009 +0000
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,20 +0,0 @@
---
--- schema to store artifacts in H2 databases.
---
-
-BEGIN;
-
--- not using AUTO_INCREMENT to be more compatible with
--- other dbms.
-CREATE SEQUENCE ARTIFACTS_ID_SEQ;
-
-CREATE TABLE artifacts (
-    id          INT PRIMARY KEY NOT NULL,
-    gid         UUID            NOT NULL UNIQUE,
-    creation    TIMESTAMP       NOT NULL,
-    last_access TIMESTAMP       NOT NULL,
-    ttl         BIGINT, -- NULL means eternal
-    data        BINARY
-);
-
-COMMIT;
diff -r 3f03aee55c2f -r 93edc04f3a10 artifact-database/pom.xml
--- a/artifact-database/pom.xml	Wed Sep 16 12:43:03 2009 +0000
+++ b/artifact-database/pom.xml	Fri Sep 18 09:34:14 2009 +0000
@@ -62,6 +62,11 @@
       <version>1.1.117</version>
     </dependency>
     <dependency>
+      <groupId>postgresql</groupId>
+      <artifactId>postgresql</artifactId>
+      <version>8.3-603.jdbc4</version>
+    </dependency>
+    <dependency>
       <groupId>commons-dbcp</groupId>
       <artifactId>commons-dbcp</artifactId>
       <version>1.2.2</version>
diff -r 3f03aee55c2f -r 93edc04f3a10 artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java
--- a/artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java	Wed Sep 16 12:43:03 2009 +0000
+++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java	Fri Sep 18 09:34:14 2009 +0000
@@ -62,10 +62,16 @@
         }
 
         public void store() {
+            if (logger.isDebugEnabled()) {
+                logger.debug("storing artifact id = " + getId());
+            }
             Backend.this.store(this);
         }
 
         public void touch() {
+            if (logger.isDebugEnabled()) {
+                logger.debug("touching artifact id = " + getId());
+            }
             Backend.this.touch(this);
         }
     } // class ArtifactWithId
diff -r 3f03aee55c2f -r 93edc04f3a10 artifact-database/src/main/java/de/intevation/artifactdatabase/DBConnection.java
--- a/artifact-database/src/main/java/de/intevation/artifactdatabase/DBConnection.java	Wed Sep 16 12:43:03 2009 +0000
+++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/DBConnection.java	Fri Sep 18 09:34:14 2009 +0000
@@ -8,19 +8,23 @@
 
 import java.io.File;
 
+import org.apache.log4j.Logger;
+
 /**
  *  @author Sascha L. Teichmann
  */
 public class DBConnection
 {
+    private static Logger logger = Logger.getLogger(DBConnection.class);
+
     public static final String DB_DRIVER =
-        "/artifacts-database/database/driver/text()";
+        "/artifact-database/database/driver/text()";
     public static final String DB_URL =
-        "/artifacts-database/database/url/text()";
+        "/artifact-database/database/url/text()";
     public static final String DB_USER =
-        "/artifacts-database/database/user/text()";
+        "/artifact-database/database/user/text()";
     public static final String DB_PASSWORD =
-        "/artifacts-database/database/password/text()";
+        "/artifact-database/database/password/text()";
 
     public static final String DEFAULT_DRIVER =
         "org.h2.Driver";
@@ -75,6 +79,9 @@
             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);
diff -r 3f03aee55c2f -r 93edc04f3a10 artifact-database/src/main/resources/sql/org-postgresql-driver.properties
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/artifact-database/src/main/resources/sql/org-postgresql-driver.properties	Fri Sep 18 09:34:14 2009 +0000
@@ -0,0 +1,17 @@
+artifacts.id.nextval=SELECT nextval('ARTIFACTS_ID_SEQ')
+
+artifacts.insert=INSERT INTO artifacts \
+    (id, gid, creation, last_access, ttl, data) \
+    VALUES (?, ?::uuid, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?, ?)
+
+artifacts.update=UPDATE artifacts SET last_access = CURRENT_TIMESTAMP, \
+    data = ? WHERE id = ?
+
+artifacts.touch=UPDATE last_access = CURRENT_TIMESTAMP WHERE id = ?
+
+artifacts.outdated=SELECT id, data FROM artifacts WHERE ttl IS NOT NULL \
+    AND CURRENT_TIMESTAMP - last_access > (ttl || ' microseconds')::interval LIMIT 50
+
+artifacts.select.gid=SELECT id, last_access, ttl, data FROM artifacts WHERE gid = ?::uuid
+
+artifacts.delete=DELETE FROM artifacts WHERE id = ?
diff -r 3f03aee55c2f -r 93edc04f3a10 contrib/run.sh
--- a/contrib/run.sh	Wed Sep 16 12:43:03 2009 +0000
+++ b/contrib/run.sh	Fri Sep 18 09:34:14 2009 +0000
@@ -5,10 +5,11 @@
 LOG4J=`find -L ~/.m2 -name log4j-1.2.13\*.jar`
 DBCP=`find -L ~/.m2 -name commons-dbcp-\*.jar`
 POOL=`find -L ~/.m2 -name commons-pool-*.jar | head -1`
+POSTGRES=`find -L ~/.m2 -name postgresql-8.3\*.jar`
 DIR=`dirname $0`/..
 CLASSPATH=$DIR/artifact-database/target/classes
 CLASSPATH=$CLASSPATH:$DIR/artifacts/target/classes
 CLASSPATH=$CLASSPATH:$RESTLET_CORE:$RESTLET_XML
-CLASSPATH=$CLASSPATH:$LOG4J:$H2:$DBCP:$POOL
+CLASSPATH=$CLASSPATH:$LOG4J:$H2:$DBCP:$POOL:$POSTGRES
 export CLASSPATH
 java "$@"