# HG changeset patch # User Sascha L. Teichmann # Date 1312988232 0 # Node ID 02c327ffbad758c71148782f3d288397f51d4331 # Parent ba88157c6aa3422e01bdb1392429b3dc28585173 Datacage: Extended schema for artifacts and collections to have creation times, too. flys-artifacts/trunk@2484 c6561f87-3c4e-4783-a992-168aeb5c3f6f diff -r ba88157c6aa3 -r 02c327ffbad7 flys-artifacts/ChangeLog --- 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 + + * 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 + + * src/main/java/de/intevation/flys/artifacts/datacage/Datacage.java: + Make it compilable again (BackendListener interface changed). + 2011-08-10 Sascha L. Teichmann * doc/conf/meta-data-user.xml: Added grouping element around w/q of each diff -r ba88157c6aa3 -r 02c327ffbad7 flys-artifacts/doc/conf/datacage.sql --- 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; diff -r ba88157c6aa3 -r 02c327ffbad7 flys-artifacts/src/main/java/de/intevation/flys/artifacts/datacage/Datacage.java --- 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(); diff -r ba88157c6aa3 -r 02c327ffbad7 flys-artifacts/src/main/resources/datacage-sql/org-h2-driver.properties --- 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') diff -r ba88157c6aa3 -r 02c327ffbad7 flys-artifacts/src/main/resources/datacage-sql/org-postgresql-driver.properties --- 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')