source: TI01-discovery-Ingest/trunk/v4n_MEDIN/ingestAutomation-upgrade/database/original_document.sql @ 6444

Subversion URL: http://proj.badc.rl.ac.uk/svn/ndg/TI01-discovery-Ingest/trunk/v4n_MEDIN/ingestAutomation-upgrade/database/original_document.sql@6444
Revision 6444, 1.8 KB checked in by sdonegan, 12 years ago (diff)

Re-updated to include 5 generic MEDIN fields plus abstract column to support improved searchReturn function

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