Changeset 3849


Ignore:
Timestamp:
07/05/08 10:13:17 (11 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:
5 edited

Legend:

Unmodified
Added
Removed

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

    r3843 r3849  
    99        original_document_id SERIAL primary key, 
    1010                original_document_filename varchar(255), 
    11                 discovery_id    varchar(255) unique, 
     11                discovery_id    varchar(255) unique not null, 
    1212                original_format text, 
    1313                original_document text, 
    1414                ts_vector       tsvector, 
    15                 create_date     date, 
    16                 update_date date, 
     15                create_date     timestamp, 
     16                update_date timestamp, 
    1717                harvest_count int,      -- this is a counter to store the number of times a doc is repeatedly harvested, even though it hasn't changed 
    1818                scn                     int 
    … …  
    2424 
    2525-- add a trigger to ensure the history table is populated by records when these change 
    26 DROP TRIGGER add_to_history_table ON original_document; 
    2726CREATE TRIGGER add_to_history_table 
    2827   BEFORE UPDATE ON original_document  
    2928                FOR EACH ROW EXECUTE PROCEDURE add_document_to_history(); 
    3029 
    31 INSERT INTO original_document VALUES (DEFAULT, 'tst.xml', 'test_dummy', 'DIF', null, null, current_date, null, 1, 1); 
    32 INSERT INTO original_document VALUES (DEFAULT, 'tst1.xml', 'test_record', 'DIF', '<?xml version="1.0" encoding="utf-8"?> 
    33 <kml xmlns="http://earth.google.com/kml/2.2"> 
    34   <Folder> 
    35         <name>Aggregation of KML documents by NDG Portal</name> 
    36     <visibility>0</visibility> 
    37         <open>0</open> 
    38         <description>The NetworkLink KML resources are those selected by the user when creating the document in the NDG portal (include URL?)</description> 
    39                     <NetworkLink> 
    40                         <name>ARGO Active float data; latitude range 45S to 90S; 18 March</name> 
    41                         <visibility>0</visibility> 
    42                         <open>0</open> 
    43                         <description>ARGO Active float data; latitude range 45S to 90S; 18 March - aggregated by the NDG Portal</description> 
    44                         <refreshVisibility>0</refreshVisibility> 
    45                         <flyToView>0</flyToView> 
    46                         <Link> 
    47                                 <href> 
    48                                         http://psdocean.nerc-bas.ac.uk/ARGO/web/Active45S-90S.kmz 
    49                                 </href> 
    50                         </Link> 
    51                 </NetworkLink> 
    52   </Folder> 
    53 </kml>', 
    54 null, current_date, null, 1, 1); 
    55  
    56 INSERT INTO original_document VALUES (DEFAULT, 'tst2.xml', 'test_dummy1', 'DIF', 'freds freds bloo' , null, current_date, null, 1, 1); 
    57 INSERT INTO original_document VALUES (DEFAULT, 'tst3.xml', 'test_dummy2', null, 'fred fred bloo fred', null, current_date, null, 1, 1); 
    58 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); 
    59  
    60 UPDATE original_document SET ts_vector = to_tsvector('english', original_document); 
    61  
    6230-- Create index on searchable column to speed up searches 
    6331CREATE INDEX textsearch_idx ON original_document USING gin(ts_vector); 
    64  
    65 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

    r3834 r3849  
    99( 
    1010        spatial_data_id SERIAL primary key, 
    11         description character varying (255) 
     11        description character varying (255), 
     12        create_date     timestamp 
    1213); 
    13 ALTER TABLE spatial_data OWNER TO postgres; 
     14--ALTER TABLE spatial_data OWNER TO postgres; 
    1415 
    1516-- add 2D geometry column, 'geometry', to table - with SRS val of 4326 

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

    r3834 r3849  
    1010        spatial_temporal_data_id SERIAL PRIMARY KEY, 
    1111        original_record_id      int references original_document(original_document_id), 
    12         spatial_data_id         int references spatial_data(spatial_data_id), 
    13         temporal_data_id        int references temporal_data(temporal_data_id) 
     12        spatial_data_id         int references spatial_data(spatial_data_id) ON DELETE CASCADE, 
     13        temporal_data_id        int references temporal_data(temporal_data_id) ON DELETE CASCADE, 
     14        create_date     timestamp 
    1415); 
    15 ALTER TABLE spatial_temporal_data OWNER TO postgres; 
     16--ALTER TABLE spatial_temporal_data OWNER TO postgres; 

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

    r3834 r3849  
    1010        temporal_data_id SERIAL primary key, 
    1111        start_time timestamp without time zone, 
    12         end_time timestamp without time zone 
     12        end_time timestamp without time zone, 
     13        create_date     timestamp 
    1314); 
    14 ALTER TABLE temporal_data OWNER TO postgres; 
     15--ALTER TABLE temporal_data OWNER TO postgres; 

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

    r3807 r3849  
    1313                transformed_format      text, 
    1414                transformed_document    text, 
    15                 create_date     date, 
    16                 update_date date, 
     15                create_date     timestamp, 
     16                update_date timestamp, 
    1717                scn                     int 
    1818); 
Note: See TracChangeset for help on using the changeset viewer.