Changeset 3834 for TI01-discovery


Ignore:
Timestamp:
01/05/08 13:56:05 (11 years ago)
Author:
cbyrom
Message:

Changes to data model:
i) Simplify creation script - to take input parameters and reuse these in a env variable
ii) Create history table direct from the main table
iii) Create procedure to do the backup to the history table
iv) Add trigger to call the backup procedure
v) Tidy up of spatio + temporal tables + additional docs.

Location:
TI01-discovery/branches/ingestAutomation-upgrade/database
Files:
1 added
1 deleted
5 edited

Legend:

Unmodified
Added
Removed
  • TI01-discovery/branches/ingestAutomation-upgrade/database/create_database.sh

    r3831 r3834  
    1 # set up postgres path first 
    2 psql -f original_document.sql -U postgres -d test 
    3 psql -f transformed_document.sql -U postgres -d test 
    4 psql -f spatial_data.sql -U postgres -d test 
    5 psql -f temporal_data.sql -U postgres -d test 
    6 psql -f spatial_temporal_data.sql -U postgres -d test 
     1#!/bin/bash 
     2 
     3if [ $# -ne 3 ] 
     4then 
     5         echo 1>&2 Usage: $0 username database hostname 
     6         exit 127 
     7fi 
     8 
     9dbOptions="-U $1 -d $2 -h $3" 
     10echo $dbOptions 
     11 
     12psql -f ingest_procedures.sql $dbOptions 
     13psql -f original_document.sql $dbOptions 
     14psql -f transformed_document.sql $dbOptions 
     15psql -f spatial_data.sql $dbOptions 
     16psql -f temporal_data.sql $dbOptions 
     17psql -f spatial_temporal_data.sql $dbOptions 
  • TI01-discovery/branches/ingestAutomation-upgrade/database/original_document.sql

    r3824 r3834  
    11/*  
    2  * Basic DB structure to use in test of NERC Discovery upgrad 
     2 * Table storing the original document with its format and the TSVector format - for text searches 
    33 *  
    44 * C Byrom Apr 08 
    55 *  
    66 */ 
    7 --CREATE SCHEMA badc; 
    8 --CREATE TABLE badc.records ( 
    9 DROP TABLE original_document; 
     7DROP TABLE original_document CASCADE; 
    108CREATE TABLE original_document ( 
    119        original_document_id SERIAL primary key, 
     
    1917                scn                     int 
    2018); 
     19 
     20-- create the mirror table for this table - to store audit histories 
     21DROP TABLE original_document_history CASCADE; 
     22COMMIT; 
     23SELECT * INTO original_document_history FROM original_document;  
     24 
     25-- add a trigger to ensure the history table is populated by records when these change 
     26DROP TRIGGER add_to_history_table ON original_document; 
     27CREATE TRIGGER add_to_history_table 
     28   BEFORE UPDATE ON original_document  
     29                FOR EACH ROW EXECUTE PROCEDURE add_document_to_history(); 
    2130 
    2231INSERT INTO original_document VALUES (DEFAULT, 'test_dummy', 'DIF', null, null, current_date, null, 1, 1); 
     
    4554null, current_date, null, 1, 1); 
    4655 
    47 INSERT INTO original_document VALUES (DEFAULT, 'test_dummy', 'DIF', 'freds freds bloo' , null, current_date, null, 1, 1); 
    48 INSERT INTO original_document VALUES (DEFAULT, 'test_dummy', null, 'fred fred bloo fred', null, current_date, null, 1, 1); 
    49 INSERT INTO original_document VALUES (DEFAULT, 'test_dummy', null, 'fred fred bloo, fred, fred, fred, fre, fred', null, current_date, null, 1, 1); 
     56INSERT INTO original_document VALUES (DEFAULT, 'test_dummy1', 'DIF', 'freds freds bloo' , null, current_date, null, 1, 1); 
     57INSERT INTO original_document VALUES (DEFAULT, 'test_dummy2', null, 'fred fred bloo fred', null, current_date, null, 1, 1); 
     58INSERT INTO original_document VALUES (DEFAULT, 'test_dummy3', null, 'fred fred bloo, fred, fred, fred, fre, fred', null, current_date, null, 1, 1); 
    5059 
    5160UPDATE original_document SET ts_vector = to_tsvector('english', original_document); 
  • TI01-discovery/branches/ingestAutomation-upgrade/database/spatial_data.sql

    r3831 r3834  
    1 DROP TABLE spatial_data; 
     1/*  
     2 * Table storing spatial information 
     3 *  
     4 * C Byrom Apr 08 
     5 *  
     6 */ 
     7DROP TABLE spatial_data CASCADE; 
    28CREATE TABLE spatial_data 
    39( 
    410        spatial_data_id SERIAL primary key, 
    5         description character varying (255), 
     11        description character varying (255) 
    612); 
    7 ALTER TABLE test_location OWNER TO postgres; 
     13ALTER TABLE spatial_data OWNER TO postgres; 
    814 
    915-- add 2D geometry column, 'geometry', to table - with SRS val of 4326 
  • TI01-discovery/branches/ingestAutomation-upgrade/database/spatial_temporal_data.sql

    r3831 r3834  
    1 DROP TABLE spatial_temporal_data; 
     1/*  
     2 * Table to relate spatial and temporal data to each other and to a particular ingested document 
     3 *  
     4 * C Byrom Apr 08 
     5 *  
     6 */ 
     7DROP TABLE spatial_temporal_data CASCADE; 
    28CREATE TABLE spatial_temporal_data 
    39( 
  • TI01-discovery/branches/ingestAutomation-upgrade/database/temporal_data.sql

    r3831 r3834  
    1 DROP TABLE temporal_data; 
     1/*  
     2 * Table storing temporal information 
     3 *  
     4 * C Byrom Apr 08 
     5 *  
     6 */ 
     7DROP TABLE temporal_data CASCADE; 
    28CREATE TABLE temporal_data 
    39( 
    410        temporal_data_id SERIAL primary key, 
    511        start_time timestamp without time zone, 
    6         end_time timestamp without time zone, 
     12        end_time timestamp without time zone 
    713); 
    814ALTER TABLE temporal_data OWNER TO postgres; 
Note: See TracChangeset for help on using the changeset viewer.