Mercurial > dive4elements > river

changeset 1025:02c327ffbad7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Datacage: Extended schema for artifacts and collections to have creation times, too. flys-artifacts/trunk@2484 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 10 Aug 2011 14:57:12 +0000
parents ba88157c6aa3
children a39511688679
files flys-artifacts/ChangeLog flys-artifacts/doc/conf/datacage.sql flys-artifacts/src/main/java/de/intevation/flys/artifacts/datacage/Datacage.java flys-artifacts/src/main/resources/datacage-sql/org-h2-driver.properties flys-artifacts/src/main/resources/datacage-sql/org-postgresql-driver.properties
diffstat 5 files changed, 58 insertions(+), 18 deletions(-) [+]
line wrap: on
line diff
--- a/flys-artifacts/ChangeLog	Wed Aug 10 14:20:31 2011 +0000
+++ b/flys-artifacts/ChangeLog	Wed Aug 10 14:57:12 2011 +0000
@@ -1,3 +1,25 @@
+2011-08-10  Sascha L. Teichmann <sascha.teichmann@intevation.de>
+
+	* doc/conf/datacage.sql: Extended schema for artifacts and collections
+	  to have creation times, too.
+
+	  To update existing databases:
+
+	    ALTER TABLE artifacts ADD COLUMN creation TIMESTAMP NOT NULL DEFAULT current_timestamp;
+	    ALTER TABLE collections ADD COLUMN creation TIMESTAMP NOT NULL DEFAULT current_timestamp;
+
+	* src/main/java/de/intevation/flys/artifacts/datacage/Datacage.java:
+	  Store creation times for artifacts and collections, too.
+
+	* src/main/resources/datacage-sql/org-h2-driver.properties,
+	  src/main/resources/datacage-sql/org-postgresql-driver.properties:
+	  Adjusted SQL statements.
+
+2011-08-10  Sascha L. Teichmann <sascha.teichmann@intevation.de>
+
+	* src/main/java/de/intevation/flys/artifacts/datacage/Datacage.java:
+	  Make it compilable again (BackendListener interface changed).
+
 2011-08-10  Sascha L. Teichmann <sascha.teichmann@intevation.de>
 
 	* doc/conf/meta-data-user.xml: Added grouping element around w/q of each
--- a/flys-artifacts/doc/conf/datacage.sql	Wed Aug 10 14:20:31 2011 +0000
+++ b/flys-artifacts/doc/conf/datacage.sql	Wed Aug 10 14:57:12 2011 +0000
@@ -10,18 +10,20 @@
 CREATE SEQUENCE COLLECTIONS_ID_SEQ;
 
 CREATE TABLE collections (
-    id      INT  PRIMARY KEY NOT NULL,
-    gid     UUID             NOT NULL UNIQUE,
-    user_id INT              NOT NULL REFERENCES users(id) ON DELETE CASCADE,
-    name    VARCHAR(256)     NOT NULL
+    id       INT  PRIMARY KEY NOT NULL,
+    gid      UUID             NOT NULL UNIQUE,
+    user_id  INT              NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    name     VARCHAR(256)     NOT NULL,
+    creation TIMESTAMP        NOT NULL
 );
 
 CREATE SEQUENCE ARTIFACTS_ID_SEQ;
 
 CREATE TABLE artifacts (
-    id    INT  PRIMARY KEY NOT NULL,
-    gid   UUID             NOT NULL UNIQUE,
-    state VARCHAR(256)     NOT NULL
+    id       INT  PRIMARY KEY NOT NULL,
+    gid      UUID             NOT NULL UNIQUE,
+    state    VARCHAR(256)     NOT NULL,
+    creation TIMESTAMP        NOT NULL
 );
 
 CREATE SEQUENCE COLLECTION_ITEMS_ID_SEQ;
--- a/flys-artifacts/src/main/java/de/intevation/flys/artifacts/datacage/Datacage.java	Wed Aug 10 14:20:31 2011 +0000
+++ b/flys-artifacts/src/main/java/de/intevation/flys/artifacts/datacage/Datacage.java	Wed Aug 10 14:57:12 2011 +0000
@@ -7,6 +7,7 @@
 import java.sql.SQLException;
 import java.sql.PreparedStatement;
 import java.sql.Types;
+import java.sql.Timestamp;
 
 import de.intevation.artifacts.GlobalContext;
 import de.intevation.artifacts.ArtifactCollection;
@@ -121,9 +122,10 @@
             FLYSArtifact flysArtifact = (FLYSArtifact)artifact;
 
             Integer uId = getUserId(userId);
-            Integer cId = getCollectionId(collectionId, uId, collectionName);
+            Integer cId = getCollectionId(
+                collectionId, uId, collectionName, collectionCreated);
 
-            storeArtifact(artifactId, cId, flysArtifact);
+            storeArtifact(artifactId, cId, flysArtifact, artifactCreated);
         }
 
         protected Integer getId(
@@ -163,7 +165,8 @@
         protected void storeArtifact(
             final String       artifactId,
             Integer            collectionId,
-            final FLYSArtifact artifact
+            final FLYSArtifact artifact,
+            final Date         artifactCreated
         ) {
             Integer aId = getId(artifacts, artifactId, SQL_ARTIFACT_BY_GID);
 
@@ -190,6 +193,10 @@
                     stmnt.setInt   (1, res[0]);
                     stmnt.setString(2, artifactId);
                     stmnt.setString(3, artifact.getCurrentStateId());
+                    Timestamp timestamp = new Timestamp(artifactCreated != null
+                        ? artifactCreated.getTime()
+                        : System.currentTimeMillis());
+                    stmnt.setTimestamp(4, timestamp);
                     stmnt.execute();
                     conn.commit();
                     return true;
@@ -243,7 +250,8 @@
         protected Integer getCollectionId(
             final String  collectionId,
             final Integer ownerId,
-            final String  collectionName
+            final String  collectionName,
+            final Date    collectionCreated
         ) {
             Integer c = getId(collections, collectionId, SQL_COLLECTION_BY_GID);
 
@@ -268,6 +276,10 @@
                     stmnt.setString(2, collectionId);
                     stmnt.setInt   (3, ownerId);
                     setString(stmnt, 4, collectionName);
+                    Timestamp timestamp = new Timestamp(collectionCreated != null
+                        ? collectionCreated.getTime()
+                        : System.currentTimeMillis());
+                    stmnt.setTimestamp(5, timestamp);
                     stmnt.execute();
                     conn.commit();
                     return true;
@@ -497,9 +509,11 @@
                 res[0] = result.getInt(1);
                 reset();
                 prepareStatement(SQL_INSERT_ARTIFACT);
-                stmnt.setInt   (1, res[0]);
-                stmnt.setString(2, flys.identifier());
-                stmnt.setString(3, flys.getCurrentStateId());
+                stmnt.setInt      (1, res[0]);
+                stmnt.setString   (2, flys.identifier());
+                stmnt.setString   (3, flys.getCurrentStateId());
+                stmnt.setTimestamp(4,
+                    new Timestamp(System.currentTimeMillis()));
                 stmnt.execute();
                 conn.commit();
                 return true;
@@ -691,6 +705,8 @@
                 stmnt.setString(2, identifier);
                 stmnt.setInt(3, uId);
                 setString(stmnt, 4, name);
+                stmnt.setTimestamp(5,
+                    new Timestamp(System.currentTimeMillis()));
                 stmnt.execute();
 
                 conn.commit();
--- a/flys-artifacts/src/main/resources/datacage-sql/org-h2-driver.properties	Wed Aug 10 14:20:31 2011 +0000
+++ b/flys-artifacts/src/main/resources/datacage-sql/org-h2-driver.properties	Wed Aug 10 14:57:12 2011 +0000
@@ -5,12 +5,12 @@
 insert.user = INSERT INTO users (id, gid) VALUES (?, ?)
 collection.by.gid = SELECT id FROM collections WHERE gid = ?
 collection.id.nextval = SELECT NEXTVAL('COLLECTIONS_ID_SEQ')
-insert.collection = INSERT INTO collections (id, gid, user_id, name) VALUES (?, ?, ?, ?)
+insert.collection = INSERT INTO collections (id, gid, user_id, name, creation) VALUES (?, ?, ?, ?, ?)
 artifact.by.gid = SELECT id FROM artifacts WHERE gid = ?
 collection.item.id.nextval = SELECT NEXTVAL('COLLECTION_ITEMS_ID_SEQ')
 insert.collection.item = INSERT INTO collection_items (id, collection_id, artifact_id) VALUES (?, ?, ?)
 artifact.id.nextval = SELECT NEXTVAL('ARTIFACTS_ID_SEQ')
-insert.artifact = INSERT INTO artifacts (id, gid, state) VALUES (?, ?, ?)
+insert.artifact = INSERT INTO artifacts (id, gid, state, creation) VALUES (?, ?, ?, ?)
 artifact.data.id.nextval = SELECT NEXTVAL('ARTIFACT_DATA_ID_SEQ')
 insert.artifact.data = INSERT INTO artifact_data (id, artifact_id, kind, k, v) VALUES (?, ?, ?, ?, ?)
 out.id.nextval = SELECT NEXTVAL('OUTS_ID_SEQ')
--- a/flys-artifacts/src/main/resources/datacage-sql/org-postgresql-driver.properties	Wed Aug 10 14:20:31 2011 +0000
+++ b/flys-artifacts/src/main/resources/datacage-sql/org-postgresql-driver.properties	Wed Aug 10 14:57:12 2011 +0000
@@ -5,12 +5,12 @@
 insert.user = INSERT INTO users (id, gid) VALUES (?, ?::uuid)
 collection.by.gid = SELECT id FROM collections WHERE gid = ?::uuid
 collection.id.nextval = SELECT NEXTVAL('COLLECTIONS_ID_SEQ')
-insert.collection = INSERT INTO collections (id, gid, user_id, name) VALUES (?, ?::uuid, ?, ?)
+insert.collection = INSERT INTO collections (id, gid, user_id, name, creation) VALUES (?, ?::uuid, ?, ?, ?)
 artifact.by.gid = SELECT id FROM artifacts WHERE gid = ?::uuid
 collection.item.id.nextval = SELECT NEXTVAL('COLLECTION_ITEMS_ID_SEQ')
 insert.collection.item = INSERT INTO collection_items (id, collection_id, artifact_id) VALUES (?, ?, ?)
 artifact.id.nextval = SELECT NEXTVAL('ARTIFACTS_ID_SEQ')
-insert.artifact = INSERT INTO artifacts (id, gid, state) VALUES (?, ?::uuid, ?)
+insert.artifact = INSERT INTO artifacts (id, gid, state, creation) VALUES (?, ?::uuid, ?, ?)
 artifact.data.id.nextval = SELECT NEXTVAL('ARTIFACT_DATA_ID_SEQ')
 insert.artifact.data = INSERT INTO artifact_data (id, artifact_id, kind, k, v) VALUES (?, ?, ?, ?, ?)
 out.id.nextval = SELECT NEXTVAL('OUTS_ID_SEQ')
http://dive4elements.wald.intevation.org