source: TI01-discovery-Ingest/trunk/v4.3.0/ingestAutomation-upgrade/database/original_document.sql @ 7192

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

got rid of bug with new column names

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                original_format_name text,
14                original_format_version text,
15                datacentre_name text,
16                dataset_name text,
17                dataset_abstract text,
18                dataset_metadata_update_date timestamp, -- update/revision of actual metadata content by DC
19                dataset_metadata_creation_date timestamp, -- creation date of actual metadata content by DC
20                dataset_metadata_publication_date timestamp, -- publication date of actual metadata content by DC
21                dataset_start_date_nom timestamp, -- simplest method to allow ordering by date when no date info present in temporal table!!
22                dataset_end_date_nom timestamp, -- ditto for end data ordering
23                document_ts_vector      tsvector, -- note original_document taken out
24                authors text, --original text version so can return original info
25                authors_ts_vector       tsvector,
26                parameters text, --original text version so can return original info
27                parameters_ts_vector    tsvector,
28                resource_type text, --original text version so can return original info
29                resource_type_ts_vector   tsvector,
30                resource_locator boolean, -- is there an online data resource or not?
31                INSPIRE_themes text,
32                INSPIRE_themes_ts_vector tsvector,
33                vertical_extent text, -- from parameter names
34                vertical_extent_ts_vector tsvector,
35                topic_category text, --original text version so can return original info
36                topic_category_ts_vector  tsvector,             
37                lineage text, --original text version so can return original info
38                lineage_ts_vector tsvector,
39                limitations_public_access text, --original text version so can return original info
40                limitations_public_access_ts_vector   tsvector,
41                data_formats text, -- original text version for data formats
42                data_formats_tsvector tsvector,
43                data_originator text, -- for returning actual value of data originator
44                data_originator_tsvector tsvector,      -- to allow searching of data originator as a targeted search
45                scope_ts_vector tsvector,
46                create_date     timestamp, -- creation date of upload of record in the Discovery DB
47                update_date timestamp, -- subsequent date of update to the record in the Discovery DB
48                harvest_count int,      -- this is a counter to store the number of times a doc is repeatedly harvested, even though it hasn't changed
49                resultset_count int,    -- this is a counter to store the number of times a doc has been included in a resultset
50                access_count int,       -- this is a counter to store the number of times a link within the document has been used
51                scn                     int
52);
53
54-- create the mirror table for this table - to store audit histories
55DROP TABLE original_document_history CASCADE;
56SELECT * INTO original_document_history FROM original_document; 
57
58-- add a trigger to ensure the history table is populated by records when these change
59CREATE TRIGGER add_to_history_table
60   BEFORE UPDATE ON original_document
61                FOR EACH ROW EXECUTE PROCEDURE add_document_to_history();
62
63-- Create index on searchable column to speed up searches
64CREATE INDEX textsearch_idx ON original_document USING gin(document_ts_vector);
Note: See TracBrowser for help on using the repository browser.