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

Subversion URL:
Revision 3967, 1.2 KB checked in by cbyrom, 13 years ago (diff)

Update code to allow the extraction of authors, parameters and scope
from moles files + adjust the data model to handle these new data.

  • Property svn:executable set to *
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_document text,
14                document_ts_vector      tsvector,
15                authors_ts_vector       tsvector,
16                parameters_ts_vector    tsvector,
17                scope_ts_vector tsvector,
18                create_date     timestamp,
19                update_date timestamp,
20                harvest_count int,      -- this is a counter to store the number of times a doc is repeatedly harvested, even though it hasn't changed
21                scn                     int
24-- create the mirror table for this table - to store audit histories
25DROP TABLE original_document_history CASCADE;
26SELECT * INTO original_document_history FROM original_document; 
28-- add a trigger to ensure the history table is populated by records when these change
29CREATE TRIGGER add_to_history_table
30   BEFORE UPDATE ON original_document
31                FOR EACH ROW EXECUTE PROCEDURE add_document_to_history();
33-- Create index on searchable column to speed up searches
34CREATE INDEX textsearch_idx ON original_document USING gin(document_ts_vector);
Note: See TracBrowser for help on using the repository browser.