Changeset 3849

Timestamp:

07/05/08 10:13:17 (13 years ago)

Author:

cbyrom

Message:

Adjust tables to use timestamps instead of dates - and add create dates to
the spatiotemporal tables + add delete cascade to the spatial_temporal_data
to allow easy removal of spatial and temporal records.

Location:

TI01-discovery/branches/ingestAutomation-upgrade/database

Files:

• original_document.sql (modified) (2 diffs)
• spatial_data.sql (modified) (1 diff)
• spatial_temporal_data.sql (modified) (1 diff)
• temporal_data.sql (modified) (1 diff)
• transformed_document.sql (modified) (1 diff)

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

@@ -9 +9 @@
         original_document_id SERIAL primary key,
                 original_document_filename varchar(255),
-                discovery_id    varchar(255) unique,
+                discovery_id    varchar(255) unique not null,
                 original_format text,
                 original_document text,
                 ts_vector       tsvector,
-                create_date     date,
-                update_date date,
+                create_date     timestamp,
+                update_date timestamp,
                 harvest_count int,      -- this is a counter to store the number of times a doc is repeatedly harvested, even though it hasn't changed
                 scn                     int
@@ -24 +24 @@
 
 -- add a trigger to ensure the history table is populated by records when these change
-DROP TRIGGER add_to_history_table ON original_document;
 CREATE TRIGGER add_to_history_table
    BEFORE UPDATE ON original_document 
                 FOR EACH ROW EXECUTE PROCEDURE add_document_to_history();
 
-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>
-        <name>Aggregation of KML documents by NDG Portal</name>
-    <visibility>0</visibility>
-        <open>0</open>
-        <description>The NetworkLink KML resources are those selected by the user when creating the document in the NDG portal (include URL?)</description>
-                    <NetworkLink>
-                        <name>ARGO Active float data; latitude range 45S to 90S; 18 March</name>
-                        <visibility>0</visibility>
-                        <open>0</open>
-                        <description>ARGO Active float data; latitude range 45S to 90S; 18 March - aggregated by the NDG Portal</description>
-                        <refreshVisibility>0</refreshVisibility>
-                        <flyToView>0</flyToView>
-                        <Link>
-                                <href>
-                                        http://psdocean.nerc-bas.ac.uk/ARGO/web/Active45S-90S.kmz
-                                </href>
-                        </Link>
-                </NetworkLink>
-  </Folder>
-</kml>',
-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);
-
 -- Create index on searchable column to speed up searches
 CREATE INDEX textsearch_idx ON original_document USING gin(ts_vector);
-
-select original_document_id, ts_vector, ts_rank(ts_vector, query) as rank from original_document, to_tsquery('english', 'FRED') query WHERE query @@ coalesce(ts_vector,'') order by rank desc;

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

@@ -9 +9 @@
 (
         spatial_data_id SERIAL primary key,
-        description character varying (255)
+        description character varying (255),
+        create_date     timestamp
 );
-ALTER TABLE spatial_data OWNER TO postgres;
+--ALTER TABLE spatial_data OWNER TO postgres;
 
 -- add 2D geometry column, 'geometry', to table - with SRS val of 4326

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

@@ -10 +10 @@
         spatial_temporal_data_id SERIAL PRIMARY KEY,
         original_record_id      int references original_document(original_document_id),
-        spatial_data_id         int references spatial_data(spatial_data_id),
-        temporal_data_id        int references temporal_data(temporal_data_id)
+        spatial_data_id         int references spatial_data(spatial_data_id) ON DELETE CASCADE,
+        temporal_data_id        int references temporal_data(temporal_data_id) ON DELETE CASCADE,
+        create_date     timestamp
 );
-ALTER TABLE spatial_temporal_data OWNER TO postgres;
+--ALTER TABLE spatial_temporal_data OWNER TO postgres;

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

@@ -10 +10 @@
         temporal_data_id SERIAL primary key,
         start_time timestamp without time zone,
-        end_time timestamp without time zone
+        end_time timestamp without time zone,
+        create_date     timestamp
 );
-ALTER TABLE temporal_data OWNER TO postgres;
+--ALTER TABLE temporal_data OWNER TO postgres;

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

@@ -13 +13 @@
                 transformed_format      text,
                 transformed_document    text,
-                create_date     date,
-                update_date date,
+                create_date     timestamp,
+                update_date timestamp,
                 scn                     int
 );