Mercurial > dive4elements > framework

changeset 274:92166f7c3842

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
#50 Added code on database level to recognize the oldest collection item in a collection (-> master artifact). artifacts/trunk@2071 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Tue, 07 Jun 2011 17:09:25 +0000
parents 22a90706d32d
children e92d5944fe4b
files ChangeLog artifact-database/doc/schema-h2.sql artifact-database/doc/schema-pg.sql artifact-database/src/main/resources/sql/org-h2-driver.properties artifact-database/src/main/resources/sql/org-postgresql-driver.properties
diffstat 5 files changed, 34 insertions(+), 6 deletions(-) [+]
line wrap: on
line diff
--- a/ChangeLog	Tue Jun 07 16:27:47 2011 +0000
+++ b/ChangeLog	Tue Jun 07 17:09:25 2011 +0000
@@ -1,3 +1,27 @@
+2011-06-07  Ingo Weinzierl <ingo@intevation.de>
+
+	  flys/issue50 (Markierung von "Master-Artefakten" ermöglichen)
+
+	* artifact-database/doc/schema-pg.sql,
+	  artifact-database/doc/schema-h2.sql: Added a column 'creation' to the
+	  collection_items table. This column is set to the current timestamp when a
+	  new row is inserted in this table. The master artifact of a collection
+	  is the artifact which collection item is the oldest collection item in
+	  the collection.
+	  Note: there might be older artifacts in the collection than the master
+	  artifact, but there will never be an older collection item than the first
+	  item when the collection was created.
+
+	  To update the db, use the following statements:
+
+	    ALTER TABLE collection_items ADD COLUMN creation TIMESTAMP;
+	    UPDATE collection_items SET creation = CURRENT_TIMESTAMP;
+	    ALTER TABLE collection_items ALTER COLUMN creation SET NOT NULL;
+
+	* artifact-database/src/main/resources/sql/org-h2-driver.properties,
+	  artifact-database/src/main/resources/sql/org-postgresql-driver.properties:
+	  Order the list of collection items based on their creation time.
+
 2011-06-07  Ingo Weinzierl <ingo@intevation.de>
 
 	* artifact-database/src/main/java/de/intevation/artifactdatabase/ArtifactDatabaseImpl.java,
--- a/artifact-database/doc/schema-h2.sql	Tue Jun 07 16:27:47 2011 +0000
+++ b/artifact-database/doc/schema-h2.sql	Tue Jun 07 17:09:25 2011 +0000
@@ -47,6 +47,7 @@
     collection_id INT             NOT NULL REFERENCES collections(id),
     artifact_id   INT             NOT NULL REFERENCES artifacts(id),
     attribute     BINARY,
+    creation      TIMESTAMP       NOT NULL,
     UNIQUE (collection_id, artifact_id)
 );
 
--- a/artifact-database/doc/schema-pg.sql	Tue Jun 07 16:27:47 2011 +0000
+++ b/artifact-database/doc/schema-pg.sql	Tue Jun 07 17:09:25 2011 +0000
@@ -47,6 +47,7 @@
     collection_id int             NOT NULL REFERENCES collections(id),
     artifact_id   int             NOT NULL REFERENCES artifacts(id),
     attribute     bytea,
+    creation      timestamp       NOT NULL,
     UNIQUE (collection_id, artifact_id)
 );
 
--- a/artifact-database/src/main/resources/sql/org-h2-driver.properties	Tue Jun 07 16:27:47 2011 +0000
+++ b/artifact-database/src/main/resources/sql/org-h2-driver.properties	Tue Jun 07 17:09:25 2011 +0000
@@ -51,8 +51,8 @@
 collection.items.id.nextval=SELECT NEXTVAL('COLLECTION_ITEMS_ID_SEQ')
 
 collection.items.insert=INSERT INTO collection_items \
-    (id, collection_id, artifact_id, attribute) \
-    VALUES (?, ?, ?, ?)
+    (id, collection_id, artifact_id, attribute, creation) \
+    VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP)
 
 collection.item.get.attribute= \
     SELECT ci.attribute FROM collection_items ci \
@@ -85,7 +85,8 @@
 collection.items.list.gid= \
     SELECT a.gid, ci.attribute FROM collection_items ci \
     INNER JOIN artifacts a ON ci.artifact_id = a.id \
-    WHERE ci.collection_id IN (SELECT id FROM collections WHERE gid = ?)
+    WHERE ci.collection_id IN (SELECT id FROM collections WHERE gid = ?) \
+    ORDER BY ci.creation
 
 # COLLECTIONS
 
--- a/artifact-database/src/main/resources/sql/org-postgresql-driver.properties	Tue Jun 07 16:27:47 2011 +0000
+++ b/artifact-database/src/main/resources/sql/org-postgresql-driver.properties	Tue Jun 07 17:09:25 2011 +0000
@@ -51,8 +51,8 @@
 collection.items.id.nextval=SELECT NEXTVAL('COLLECTION_ITEMS_ID_SEQ')
 
 collection.items.insert=INSERT INTO collection_items \
-    (id, collection_id, artifact_id, attribute) \
-    VALUES (?, ?, ?, ?)
+    (id, collection_id, artifact_id, attribute, creation) \
+    VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP)
 
 collection.item.get.attribute= \
     SELECT ci.attribute FROM collection_items ci \
@@ -85,7 +85,8 @@
 collection.items.list.gid= \
     SELECT a.gid, ci.attribute FROM collection_items ci \
     INNER JOIN artifacts a ON ci.artifact_id = a.id \
-    WHERE ci.collection_id IN (SELECT id FROM collections WHERE gid = ?::uuid)
+    WHERE ci.collection_id IN (SELECT id FROM collections WHERE gid = ?::uuid) \
+    ORDER BY ci.creation
 
 # COLLECTIONS
http://dive4elements.wald.intevation.org