source: TI01-discovery/branches/ingestAutomation-upgrade/database/ingest_procedures.sql @ 3863

Subversion URL: http://proj.badc.rl.ac.uk/svn/ndg/TI01-discovery/branches/ingestAutomation-upgrade/database/ingest_procedures.sql@3863
Revision 3863, 5.3 KB checked in by cbyrom, 12 years ago (diff)

Add new stored proc to cleanly delete an ingested document and all
its associated data.

RevLine 
[3834]1/*
2 * Procedures used by the DB code as part of the ingest process
3 *
4 * C Byrom Apr 08
5 *
6 */
7DROP FUNCTION add_document_to_history() CASCADE;
[3851]8CREATE FUNCTION add_document_to_history() RETURNS TRIGGER AS 
9$$
[3834]10    BEGIN
[3843]11        -- This inserts the currently stored information
12        -- into the original_document_history table
13                -- NB, only do this when we are not just incrementing the harvest count
14                IF OLD.harvest_count=NEW.harvest_count THEN
15                INSERT INTO original_document_history
16                SELECT * FROM original_document WHERE original_document_id=NEW.original_document_id;
17                END IF;
[3834]18                RETURN NEW;
19     END;
[3851]20$$ LANGUAGE 'plpgsql';
21
22DROP FUNCTION create_document(filename_in varchar(255), discovery_id_in varchar(255), 
23        doc_type_in text, original_document_in text) CASCADE;
24CREATE FUNCTION create_document(filename_in varchar(255), discovery_id_in varchar(255), 
25        doc_type_in text, original_document_in text) RETURNS integer AS 
26$$
27        DECLARE
[3860]28                db_id integer;
[3851]29    BEGIN
30        -- This inserts a new document into the DB
31        INSERT INTO ORIGINAL_DOCUMENT (original_document_id, original_document_filename, 
32            discovery_id, original_format, original_document, ts_vector, create_date, 
33                        harvest_count, scn) VALUES (
34            DEFAULT, filename_in, discovery_id_in, doc_type_in, original_document_in, 
35                        to_tsvector('english', original_document_in), current_timestamp, 1, 1);
36
37                SELECT original_document_id INTO db_id FROM ORIGINAL_DOCUMENT WHERE discovery_id = discovery_id_in;
38                RETURN db_id;
39     END;
40$$ LANGUAGE 'plpgsql';
41
42
[3863]43DROP FUNCTION delete_document(original_document_id_in int) CASCADE;
44CREATE FUNCTION delete_document(original_document_id_in int) RETURNS VOID AS 
45$$
46        DECLARE
47                db_id integer;
48    BEGIN
49        -- This deletes a document from the DB
50        DELETE FROM TRANSFORMED_DOCUMENT WHERE original_document_id = original_document_id_in;
51                DELETE FROM SPATIAL_TEMPORAL_DATA WHERE original_document_id = original_document_id_in;
52        DELETE FROM ORIGINAL_DOCUMENT WHERE original_document_id = original_document_id_in;
53        END;
54$$ LANGUAGE 'plpgsql';
55
56
[3851]57DROP FUNCTION update_document(original_document_id_in int, filename_in varchar(255), 
[3859]58        discovery_id_in varchar(255), doc_type_in text, original_document_in text, scn_in int) CASCADE;
[3851]59CREATE FUNCTION update_document(original_document_id_in int, filename_in varchar(255), 
[3859]60        discovery_id_in varchar(255), doc_type_in text, original_document_in text, scn_in int) 
61        RETURNS VOID AS 
[3851]62$$
63        DECLARE
[3859]64                scn_current integer;
[3851]65    BEGIN
66        -- This updates an existing document
[3859]67
[3851]68                -- check the scn is valid before an update is done
69                SELECT scn INTO scn_current FROM ORIGINAL_DOCUMENT WHERE 
70                        original_document_id = original_document_id_in;
71                IF scn_in <> scn_current THEN
[3859]72                        RAISE EXCEPTION 'Invalid SCN specified (current scn=%, specified scn=%) -
73                                implies record has been changed during update - exiting...', scn_current, scn_in;
[3851]74                END IF;
75
76        UPDATE ORIGINAL_DOCUMENT SET
77                        original_document_filename = filename_in,
78            discovery_id = discovery_id_in,
79                        original_format = doc_type_in,
80                        original_document = original_document_in,
81            ts_vector = to_tsvector('english', original_document_in),
82            update_date = current_timestamp, 
83                        harvest_count = 1,
84                        scn = scn_in + 1
85                        WHERE original_document_id = original_document_id_in;
86     END;
87$$ LANGUAGE 'plpgsql';
88
89
90DROP FUNCTION add_spatiotemporal_row(original_document_id_in int, north_in real, 
91        south_in real, west_in real, east_in real, start_time_in timestamp, 
92        end_time_in timestamp) CASCADE;
93CREATE FUNCTION add_spatiotemporal_row(original_document_id_in int, north_in real, 
94        south_in real, west_in real, east_in real, start_time_in timestamp, 
95        end_time_in timestamp) RETURNS void AS 
96$$
97        DECLARE
98                spatial_db_id integer := null;
99                temporal_db_id integer := null;
100                cmdStr text;
101
102    BEGIN
103        -- This inserts a new row in the spatial data table, if required
104                IF west_in is not null AND east_in is not null AND
105                        north_in is not null AND south_in is not null THEN
106
107                        cmdStr := 'INSERT INTO SPATIAL_DATA (spatial_data_id, geometry, create_date)
108                                VALUES (DEFAULT,
109                                SetSRID(''BOX3D(' || west_in || ' ' || south_in || ', ' || east_in || ' ' || 
110                                        north_in || ')''::box3d,4326),
111                                current_timestamp)'; 
112       
113                -- For debugging only
114                        --raise notice 'cmdStr is: %', cmdStr;
115
116                EXECUTE cmdStr;
117
118                        SELECT MAX(spatial_data_id) INTO spatial_db_id FROM SPATIAL_DATA;
119                        --RAISE NOTICE 'Spatial data added - with ID: %', spatial_db_id;
120                END IF;
121
122        -- This inserts a new row in the temporal data table, if required
123        IF start_time_in is not null OR end_time_in is not null THEN
124                INSERT INTO TEMPORAL_DATA (temporal_data_id, start_time, end_time, create_date) 
125                                VALUES (DEFAULT, start_time_in, end_time_in, current_timestamp);
126
127                        SELECT MAX(temporal_data_id) INTO temporal_db_id FROM TEMPORAL_DATA;
128                        --RAISE NOTICE 'Temporal data added - with ID: %', temporal_db_id;
129                END IF;
130
131                -- Now create row in link table
[3863]132                INSERT INTO SPATIAL_TEMPORAL_DATA (spatial_temporal_data_id, original_document_id,
[3851]133                        spatial_data_id, temporal_data_id, create_date) VALUES
134                        (DEFAULT, original_document_id_in, spatial_db_id, temporal_db_id, current_timestamp);
135               
136     END;
137$$ LANGUAGE 'plpgsql';
Note: See TracBrowser for help on using the repository browser.