# HG changeset patch
# User Ingo Weinzierl <ingo.weinzierl@intevation.de>
# Date 1307466565 0
# Node ID 92166f7c3842d545cbe60039cb6b1fe5185a2444
# Parent  22a90706d32d9f1c5e1187dc3adb255964f4f50b
#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

diff -r 22a90706d32d -r 92166f7c3842 ChangeLog
--- 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,
diff -r 22a90706d32d -r 92166f7c3842 artifact-database/doc/schema-h2.sql
--- 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)
 );
 
diff -r 22a90706d32d -r 92166f7c3842 artifact-database/doc/schema-pg.sql
--- 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)
 );
 
diff -r 22a90706d32d -r 92166f7c3842 artifact-database/src/main/resources/sql/org-h2-driver.properties
--- 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
 
diff -r 22a90706d32d -r 92166f7c3842 artifact-database/src/main/resources/sql/org-postgresql-driver.properties
--- 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