Changeset 3843 for TI01-discovery

Timestamp:

02/05/08 11:23:11 (13 years ago)

Author:

cbyrom

Message:

Add filename column back into original_document + adjust trigger procedure
to ignore audit copies when only harvest count has been incremented.

Location:

TI01-discovery/branches/ingestAutomation-upgrade/database

Files:

• ingest_procedures.sql (modified) (1 diff)
• original_document.sql (modified) (4 diffs)

TI01-discovery/branches/ingestAutomation-upgrade/database/ingest_procedures.sql

@@ -8 +8 @@
 CREATE FUNCTION add_document_to_history() RETURNS OPAQUE AS '
     BEGIN
-       -- This inserts the currently stored information 
-        -- into the original_document_history table 
-        INSERT INTO original_document_history 
-                SELECT * FROM original_document WHERE original_document_id=NEW.original_document_id;
-
+        -- This inserts the currently stored information 
+        -- into the original_document_history table
+                -- NB, only do this when we are not just incrementing the harvest count
+                IF OLD.harvest_count=NEW.harvest_count THEN
+                INSERT INTO original_document_history 
+                SELECT * FROM original_document WHERE original_document_id=NEW.original_document_id;
+                END IF;
                 RETURN NEW;
      END;

TI01-discovery/branches/ingestAutomation-upgrade/database/original_document.sql

@@ -8 +8 @@
 CREATE TABLE original_document (
         original_document_id SERIAL primary key,
+                original_document_filename varchar(255),
                 discovery_id    varchar(255) unique,
                 original_format text,
@@ -20 +21 @@
 -- create the mirror table for this table - to store audit histories
 DROP TABLE original_document_history CASCADE;
-COMMIT;
 SELECT * INTO original_document_history FROM original_document; 
 
@@ -29 +29 @@
                 FOR EACH ROW EXECUTE PROCEDURE add_document_to_history();
 
-INSERT INTO original_document VALUES (DEFAULT, 'test_dummy', 'DIF', null, null, current_date, null, 1, 1);
-INSERT INTO original_document VALUES (DEFAULT, 'test_record', 'DIF', '<?xml version="1.0" encoding="utf-8"?>
+INSERT INTO original_document VALUES (DEFAULT, 'tst.xml', 'test_dummy', 'DIF', null, null, current_date, null, 1, 1);
+INSERT INTO original_document VALUES (DEFAULT, 'tst1.xml', 'test_record', 'DIF', '<?xml version="1.0" encoding="utf-8"?>
 <kml xmlns="http://earth.google.com/kml/2.2">
   <Folder>
@@ -54 +54 @@
 null, current_date, null, 1, 1);
 
-INSERT INTO original_document VALUES (DEFAULT, 'test_dummy1', 'DIF', 'freds freds bloo' , null, current_date, null, 1, 1);
-INSERT INTO original_document VALUES (DEFAULT, 'test_dummy2', null, 'fred fred bloo fred', null, current_date, null, 1, 1);
-INSERT INTO original_document VALUES (DEFAULT, 'test_dummy3', null, 'fred fred bloo, fred, fred, fred, fre, fred', null, current_date, null, 1, 1);
+INSERT INTO original_document VALUES (DEFAULT, 'tst2.xml', 'test_dummy1', 'DIF', 'freds freds bloo' , null, current_date, null, 1, 1);
+INSERT INTO original_document VALUES (DEFAULT, 'tst3.xml', 'test_dummy2', null, 'fred fred bloo fred', null, current_date, null, 1, 1);
+INSERT INTO original_document VALUES (DEFAULT, 'tst4.xml', 'test_dummy3', null, 'fred fred bloo, fred, fred, fred, fre, fred', null, current_date, null, 1, 1);
 
 UPDATE original_document SET ts_vector = to_tsvector('english', original_document);