source: TI01-discovery/branches/ingestAutomation-upgrade/database/original_document.sql @ 3834

Subversion URL: http://proj.badc.rl.ac.uk/svn/ndg/TI01-discovery/branches/ingestAutomation-upgrade/database/original_document.sql@3834
Revision 3834, 2.9 KB checked in by cbyrom, 12 years ago (diff)

Changes to data model:
i) Simplify creation script - to take input parameters and reuse these in a env variable
ii) Create history table direct from the main table
iii) Create procedure to do the backup to the history table
iv) Add trigger to call the backup procedure
v) Tidy up of spatio + temporal tables + additional docs.

  • Property svn:executable set to *
Line 
1/*
2 * Table storing the original document with its format and the TSVector format - for text searches
3 *
4 * C Byrom Apr 08
5 *
6 */
7DROP TABLE original_document CASCADE;
8CREATE TABLE original_document (
9        original_document_id SERIAL primary key,
10                discovery_id    varchar(255) unique,
11                original_format text,
12                original_document text,
13                ts_vector       tsvector,
14                create_date     date,
15                update_date date,
16                harvest_count int,      -- this is a counter to store the number of times a doc is repeatedly harvested, even though it hasn't changed
17                scn                     int
18);
19
20-- create the mirror table for this table - to store audit histories
21DROP TABLE original_document_history CASCADE;
22COMMIT;
23SELECT * INTO original_document_history FROM original_document; 
24
25-- add a trigger to ensure the history table is populated by records when these change
26DROP TRIGGER add_to_history_table ON original_document;
27CREATE TRIGGER add_to_history_table
28   BEFORE UPDATE ON original_document
29                FOR EACH ROW EXECUTE PROCEDURE add_document_to_history();
30
31INSERT INTO original_document VALUES (DEFAULT, 'test_dummy', 'DIF', null, null, current_date, null, 1, 1);
32INSERT INTO original_document VALUES (DEFAULT, '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>',
54null, current_date, null, 1, 1);
55
56INSERT INTO original_document VALUES (DEFAULT, 'test_dummy1', 'DIF', 'freds freds bloo' , null, current_date, null, 1, 1);
57INSERT INTO original_document VALUES (DEFAULT, 'test_dummy2', null, 'fred fred bloo fred', null, current_date, null, 1, 1);
58INSERT INTO original_document VALUES (DEFAULT, 'test_dummy3', null, 'fred fred bloo, fred, fred, fred, fre, fred', null, current_date, null, 1, 1);
59
60UPDATE original_document SET ts_vector = to_tsvector('english', original_document);
61
62-- Create index on searchable column to speed up searches
63CREATE INDEX textsearch_idx ON original_document USING gin(ts_vector);
64
65select 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;
Note: See TracBrowser for help on using the repository browser.