--- a/Changelog	Sun Sep 06 16:16:54 2009 +0000
+++ b/Changelog	Mon Sep 07 08:41:05 2009 +0000
@@ -1,3 +1,21 @@
+2009-09-07	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
+
+	* artifact-database/src/main/java/de/intevation/artifactdatabase/SQL.java:
+	Properties wrapper for SQL statements. The properties are looked up
+	in resorces /sql/DRIVER.properties (DRIVER is the name of the db driver class,
+	lowercased and '.' replaced by '-'. 'org.h2.Driver' turns to
+	'org-h2-driver.properties', e.g.
+
+	* artifact-database/src/main/resources/sql/org-h2-driver.properties:
+	SQL statements for H2 database.
+
+	* artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java:
+	Artifacts are proxies/wrapped by an special Artifact implementation
+	to bound db activities transparentely to the Artifact. XXX: It has
+	to be evaluated if a more direct concept like extending the Artifact API
+	to store itself would be more robust for future implementations.
+	TODO: Loading Artifact from db is not implemented yet.
+
 2009-09-06	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
 
 	* artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java:

--- a/artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java	Sun Sep 06 16:16:54 2009 +0000
+++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java	Mon Sep 07 08:41:05 2009 +0000
@@ -1,5 +1,23 @@
 package de.intevation.artifactdatabase;
 
+import org.w3c.dom.Document;
+
+import java.util.UUID;
+
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.sql.PreparedStatement;
+import java.sql.Types;
+import java.sql.ResultSet;
+
+import javax.sql.DataSource;
+
+import java.io.IOException;
+import java.io.ByteArrayOutputStream;
+import java.io.ObjectOutputStream;
+
+import java.util.zip.GZIPOutputStream;
+
 import de.intevation.artifacts.ArtifactFactory;
 import de.intevation.artifacts.Artifact;
 
@@ -8,6 +26,118 @@
  */
 public class Backend
 {
+    public static final String SQL_NEXT_ID =
+        SQL.get("artifacts.id.nextval");
+
+    public static final String SQL_INSERT =
+        SQL.get("artifacts.insert");
+
+    public static final String SQL_UPDATE =
+        SQL.get("artifacts.update");
+
+    public static final String SQL_TOUCH =
+        SQL.get("artifacts.touch");
+
+    /**
+     * Used to wrap the calls to invole database actions.
+     */
+    public class ArtifactProxy
+    implements   Artifact
+    {
+        protected Artifact original;
+        protected int      id;
+        protected boolean  unwritten;
+
+        public ArtifactProxy() {
+        }
+
+        public ArtifactProxy(Artifact original, int id, boolean unwritten) {
+            this.original  = original;
+            this.id        = id;
+            this.unwritten = unwritten;
+        }
+
+        public Artifact getOriginal() {
+            return original;
+        }
+
+        public int getId() {
+            return id;
+        }
+
+        public boolean isUnwritten() {
+            return unwritten;
+        }
+
+        public String identifier() {
+            return original.identifier();
+        }
+
+        public String hash() {
+            return original.hash();
+        }
+
+        public Document describe(Object context) {
+            try {
+                return original.describe(context);
+            }
+            finally {
+                touch(this);
+            }
+        }
+
+        public Document advance(Document target, Object context) {
+            try {
+                return original.advance(target, context);
+            }
+            finally {
+                store(this);
+            }
+        }
+
+        public Document feed(Document data, Object context) {
+            try {
+                return original.feed(data, context);
+            }
+            finally {
+                store(this);
+            }
+        }
+
+        public byte [] out(Document format, Object context) {
+            try {
+                return original.out(format, context);
+            }
+            finally {
+                touch(this);
+            }
+        }
+
+        public void setup(String identifier, Object context) {
+            original.setup(identifier, context);
+        }
+
+        public void endOfLife(Object context) {
+            original.endOfLife(context);
+        }
+
+        public byte [] toBytes() {
+            try {
+                ByteArrayOutputStream bos = new ByteArrayOutputStream();
+                GZIPOutputStream      gos = new GZIPOutputStream(bos);
+                ObjectOutputStream    oos = new ObjectOutputStream(gos);
+
+                oos.writeObject(original);
+                oos.flush();
+
+                return bos.toByteArray();
+            }
+            catch (IOException ioe) {
+                throw new RuntimeException(ioe);
+            }
+        }
+    } // class ArtifactProxy
+
     public Backend() {
     }
 
@@ -18,7 +148,155 @@
     public Artifact createArtifactWithFactory(
         ArtifactFactory factory, Object context
     ) {
-        return null;
+        UUID uuid = UUID.randomUUID();
+        Artifact artifact = factory.createArtifact(
+            uuid.toString(), context);
+
+        Long ttl = factory.timeToLiveUntouched(
+            artifact, context);
+
+        int id = insertDatabase(uuid, ttl);
+
+        return new ArtifactProxy(artifact, id, true);
     }
+
+    protected int insertDatabase(UUID uuid, Long ttl) {
+        Connection        connection    = null;
+        PreparedStatement stmnt_next_id = null;
+        PreparedStatement stmnt_insert  = null;
+        ResultSet         res_id        = null;
+
+        DataSource dataSource = DBConnection.getDataSource();
+        try {
+            connection = dataSource.getConnection();
+            try {
+                connection.setAutoCommit(false);
+
+                stmnt_next_id = connection.prepareStatement(SQL_NEXT_ID);
+                stmnt_insert  = connection.prepareStatement(SQL_INSERT);
+
+                res_id = stmnt_next_id.executeQuery();
+
+                if (!res_id.next()) {
+                    throw new RuntimeException("No id generated");
+                }
+
+                int id = res_id.getInt(1);
+
+                stmnt_insert.setInt(1, id);
+                stmnt_insert.setString(2, uuid.toString());
+                if (ttl == null) {
+                    stmnt_insert.setNull(3, Types.BIGINT);
+                }
+                else {
+                    stmnt_insert.setLong(3, ttl.longValue());
+                }
+
+                stmnt_insert.execute();
+
+                connection.commit();
+
+                return id;
+            }
+            catch (SQLException sqle) {
+                connection.rollback();
+                throw sqle;
+            }
+        }
+        catch (SQLException sqle) {
+            sqle.printStackTrace(System.err);
+        }
+        finally {
+            if (res_id != null) {
+                try { res_id.close(); }
+                catch (SQLException sqle) {}
+            }
+            if (stmnt_insert != null) {
+                try { stmnt_insert.close(); }
+                catch (SQLException sqle) {}
+            }
+            if (stmnt_next_id != null) {
+                try { stmnt_next_id.close(); }
+                catch (SQLException sqle) {}
+            }
+            if (connection != null) {
+                try { connection.close(); }
+                catch (SQLException sqle) {}
+            }
+        }
+        throw new RuntimeException("failed insert artifact into database");
+    }
+
+    public void touch(ArtifactProxy proxy) {
+        System.err.println("touch: " + proxy);
+        if (proxy.isUnwritten()) {
+            store(proxy);
+            return;
+        }
+        Connection        connection  = null;
+        PreparedStatement stmnt_touch = null;
+        DataSource        dataSource  = DBConnection.getDataSource();
+        try {
+            connection = dataSource.getConnection();
+            try {
+                connection.setAutoCommit(false);
+                stmnt_touch = connection.prepareStatement(SQL_UPDATE);
+                stmnt_touch.setInt(1, proxy.getId());
+                stmnt_touch.execute();
+                connection.commit();
+            }
+            catch (SQLException sqle) {
+                connection.rollback();
+            }
+        }
+        catch (SQLException sqle) {
+            sqle.printStackTrace(System.err);
+        }
+        finally {
+            if (stmnt_touch != null) {
+                try { stmnt_touch.close(); }
+                catch (SQLException sqle) {}
+            }
+            if (connection != null) {
+                try { connection.close(); }
+                catch (SQLException sqle) {}
+            }
+        }
+    }
+
+    public void store(ArtifactProxy proxy) {
+        System.err.println("store: " + proxy);
+        Connection        connection   = null;
+        PreparedStatement stmnt_update = null;
+        DataSource        dataSource   = DBConnection.getDataSource();
+        try {
+            connection = dataSource.getConnection();
+            try {
+                connection.setAutoCommit(false);
+                stmnt_update = connection.prepareStatement(SQL_UPDATE);
+                stmnt_update.setInt(1, proxy.getId());
+                stmnt_update.setBytes(2, proxy.toBytes());
+                stmnt_update.execute();
+                connection.commit();
+            }
+            catch (SQLException sqle) {
+                connection.rollback();
+            }
+        }
+        catch (SQLException sqle) {
+            sqle.printStackTrace(System.err);
+        }
+        finally {
+            if (stmnt_update != null) {
+                try { stmnt_update.close(); }
+                catch (SQLException sqle) {}
+            }
+            if (connection != null) {
+                try { connection.close(); }
+                catch (SQLException sqle) {}
+            }
+        }
+    }
+
 }
 // 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/SQL.java	Mon Sep 07 08:41:05 2009 +0000
@@ -0,0 +1,65 @@
+package de.intevation.artifactdatabase;
+
+import java.util.Properties;
+
+import java.io.IOException;
+import java.io.InputStream;
+
+/**
+ *  @author Sascha L. Teichmann
+ */
+public final class SQL
+{
+    private SQL() {
+    }
+
+    private static Properties 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) {
+                System.err.println("WARNING: no SQL file for driver '"
+                    + driver + "' found.");
+                res = "/sql/" + DBConnection.DEFAULT_DRIVER.replace('.', '-').toLowerCase()
+                    + ".properties";
+                if ((in = SQL.class.getResourceAsStream(res)) == null) {
+                    System.err.println("ERROR: no SQL file found");
+                }
+            }
+
+            properties.load(in);
+        }
+        catch (IOException ioe) {
+            ioe.printStackTrace(System.err);
+        }
+        finally {
+            if (in != null) {
+                try { in.close(); } catch (IOException ioe) {}
+            }
+        }
+
+        return properties;
+    }
+
+    public static final String get(String key) {
+        return getStatements().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/resources/sql/org-h2-driver.properties	Mon Sep 07 08:41:05 2009 +0000
@@ -0,0 +1,19 @@
+artifacts.id.nextval=SELECT NEXTVAL('ARTIFACTS_ID_SEQ')
+
+artifacts.insert=INSERT INTO artifacts \
+    (id, gid, creation, last_access, ttl) \
+    VALUES (?, ?, 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 FROM artifacts WHERE ttl IS NOT NULL \
+    AND CURRENT_TIMESTAMP - last_access > ttl
+
+artifacts.select.id=SELECT gid, last_access, ttl, data FROM artifacts WHERE id = ?
+
+artifacts.select.gid=SELECT id, last_access, ttl, data FROM artifacts WHERE gid = ?
+
+artifacts.delete=DELETE FROM artifacts WHERE id = ?