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

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

allow extra column in this table to allow ordering by date where no date info present in temporal table...

  • 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                original_document_filename varchar(255),
11                discovery_id    varchar(255) unique not null,
12                original_format text,
13                datacentre_name text,
14                dataset_name text,
15                dataset_metadata_update_date timestamp,
16                dataset_start_date_nom timestamp, -- simplest method to allow ordering by date when no date info present in temporal table!!
17                original_document text,
18                document_ts_vector      tsvector,
19                authors_ts_vector       tsvector,
20                parameters_ts_vector    tsvector,
21                scope_ts_vector tsvector,
22                create_date     timestamp,
23                update_date timestamp,
24                harvest_count int,      -- this is a counter to store the number of times a doc is repeatedly harvested, even though it hasn't changed
25                resultset_count int,    -- this is a counter to store the number of times a doc has been included in a resultset
26                access_count int,       -- this is a counter to store the number of times a link within the document has been used
27                scn                     int
28);
29
30-- create the mirror table for this table - to store audit histories
31DROP TABLE original_document_history CASCADE;
32SELECT * INTO original_document_history FROM original_document; 
33
34-- add a trigger to ensure the history table is populated by records when these change
35CREATE TRIGGER add_to_history_table
36   BEFORE UPDATE ON original_document
37                FOR EACH ROW EXECUTE PROCEDURE add_document_to_history();
38
39-- Create index on searchable column to speed up searches
40CREATE INDEX textsearch_idx ON original_document USING gin(document_ts_vector);
Note: See TracBrowser for help on using the repository browser.