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

Subversion URL:
Revision 3849, 1.1 KB checked in by cbyrom, 12 years ago (diff)

Adjust tables to use timestamps instead of dates - and add create dates to
the spatiotemporal tables + add delete cascade to the spatial_temporal_data
to allow easy removal of spatial and temporal records.

  • Property svn:executable set to *
[3834]2 * Table storing the original document with its format and the TSVector format - for text searches
[3807]3 *
4 * C Byrom Apr 08
5 *
6 */
[3834]7DROP TABLE original_document CASCADE;
[3807]8CREATE TABLE original_document (
9        original_document_id SERIAL primary key,
[3843]10                original_document_filename varchar(255),
[3849]11                discovery_id    varchar(255) unique not null,
[3807]12                original_format text,
13                original_document text,
14                ts_vector       tsvector,
[3849]15                create_date     timestamp,
16                update_date timestamp,
[3807]17                harvest_count int,      -- this is a counter to store the number of times a doc is repeatedly harvested, even though it hasn't changed
18                scn                     int
[3834]21-- create the mirror table for this table - to store audit histories
22DROP TABLE original_document_history CASCADE;
23SELECT * INTO original_document_history FROM original_document; 
25-- add a trigger to ensure the history table is populated by records when these change
26CREATE TRIGGER add_to_history_table
27   BEFORE UPDATE ON original_document
28                FOR EACH ROW EXECUTE PROCEDURE add_document_to_history();
[3807]30-- Create index on searchable column to speed up searches
[3849]31CREATE INDEX textsearch_idx ON original_document USING gin(ts_vector);
Note: See TracBrowser for help on using the repository browser.