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

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

allow extra column in this table to allow ordering by date where no date info present in temporal table...

Line 
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;
8CREATE FUNCTION add_document_to_history() RETURNS TRIGGER AS 
9$$
10    BEGIN
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;
18                RETURN NEW;
19     END;
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, authors text, parameters text, scope text, dataset_name_in text, datacentre_name_in text, metadata_update_date_in timestamp, dataset_start_date_in timestamp) CASCADE;
24CREATE FUNCTION create_document(filename_in varchar(255), discovery_id_in varchar(255), 
25        doc_type_in text, original_document_in text, authors text, parameters text, scope text, dataset_name_in text, datacentre_name_in text, metadata_update_date_in timestamp, dataset_start_date_in timestamp) RETURNS integer AS 
26$$
27        DECLARE
28                db_id integer;
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, document_ts_vector, authors_ts_vector, 
33            parameters_ts_vector, scope_ts_vector, create_date, harvest_count, scn, dataset_name, datacentre_name, dataset_metadata_update_date, dataset_start_date_nom) VALUES (
34            DEFAULT, filename_in, discovery_id_in, doc_type_in, original_document_in, 
35                        to_tsvector('english', original_document_in), to_tsvector('english', authors), 
36                        to_tsvector('english', parameters), to_tsvector('english', scope), current_timestamp, 1, 1, dataset_name_in, datacentre_name_in, metadata_update_date_in, dataset_start_date_in);
37
38                SELECT original_document_id INTO db_id FROM ORIGINAL_DOCUMENT WHERE discovery_id = discovery_id_in;
39                RETURN db_id;
40     END;
41$$ LANGUAGE 'plpgsql';
42
43
44DROP FUNCTION delete_document(original_document_id_in int) CASCADE;
45CREATE FUNCTION delete_document(original_document_id_in int) RETURNS VOID AS 
46$$
47        DECLARE
48                db_id integer;
49                tdID integer;
50                sdID integer;
51    BEGIN
52        -- This deletes a document from the DB
53
54        -- get temporal_data_id from spatial_temporal_data (SJD 090209) --NOTE can't mix SELECT and DELETE bits here!!
55        SELECT INTO tdID temporal_data_id FROM SPATIAL_TEMPORAL_DATA WHERE original_document_id = original_document_id_in;
56        SELECT INTO sdID spatial_data_id FROM SPATIAL_TEMPORAL_DATA WHERE original_document_id = original_document_id_in;
57       
58        DELETE FROM spatial_data WHERE spatial_data_id = sdID;
59        DELETE FROM temporal_data WHERE temporal_data_id = tdID;
60        DELETE FROM TRANSFORMED_DOCUMENT WHERE original_document_id = original_document_id_in;
61        DELETE FROM SPATIAL_TEMPORAL_DATA WHERE original_document_id = original_document_id_in;
62        DELETE FROM ORIGINAL_DOCUMENT WHERE original_document_id = original_document_id_in;
63
64        END;
65$$ LANGUAGE 'plpgsql';
66
67
68DROP FUNCTION update_document(original_document_id_in int, filename_in varchar(255), 
69        discovery_id_in varchar(255), doc_type_in text, original_document_in text, 
70        authors text, parameters text, scope text, scn_in int,dataset_name_in text, datacentre_name_in text, metadata_update_date_in timestamp, dataset_start_date_in timestamp) CASCADE;
71CREATE FUNCTION update_document(original_document_id_in int, filename_in varchar(255), 
72        discovery_id_in varchar(255), doc_type_in text, original_document_in text, 
73        authors text, parameters text, scope text, scn_in int,dataset_name_in text, datacentre_name_in text, metadata_update_date_in timestamp, dataset_start_date_in timestamp) 
74        RETURNS VOID AS 
75$$
76        DECLARE
77                scn_current integer;
78    BEGIN
79        -- This updates an existing document
80
81                -- check the scn is valid before an update is done
82                SELECT scn INTO scn_current FROM ORIGINAL_DOCUMENT WHERE 
83                        original_document_id = original_document_id_in;
84                IF scn_in <> scn_current THEN
85                        RAISE EXCEPTION 'Invalid SCN specified (current scn=%, specified scn=%) -
86                                implies record has been changed during update - exiting...', scn_current, scn_in;
87                END IF;
88
89        UPDATE ORIGINAL_DOCUMENT SET
90                        original_document_filename = filename_in,
91            discovery_id = discovery_id_in,
92                        original_format = doc_type_in,
93                        original_document = original_document_in,
94            document_ts_vector = to_tsvector('english', original_document_in),
95            authors_ts_vector = to_tsvector('english', authors),
96            parameters_ts_vector = to_tsvector('english', parameters),
97            scope_ts_vector = to_tsvector('english', scope),
98            update_date = current_timestamp, 
99                        harvest_count = 1,
100                        scn = scn_in + 1,
101                dataset_name = dataset_name_in,
102                datacentre_name = datacentre_name_in,
103                dataset_metadata_update_date = metadata_update_date_in,
104                dataset_start_date_nom = dataset_start_date_in
105                        WHERE original_document_id = original_document_id_in;
106     END;
107$$ LANGUAGE 'plpgsql';
108
109
110DROP FUNCTION add_spatiotemporal_row(original_document_id_in int, north_in real, 
111        south_in real, west_in real, east_in real, start_time_in timestamp, 
112        end_time_in timestamp) CASCADE;
113CREATE FUNCTION add_spatiotemporal_row(original_document_id_in int, north_in real, 
114        south_in real, west_in real, east_in real, start_time_in timestamp, 
115        end_time_in timestamp) RETURNS void AS 
116$$
117        DECLARE
118                spatial_db_id integer := null;
119                temporal_db_id integer := null;
120                cmdStr text;
121
122    BEGIN
123        -- This inserts a new row in the spatial data table, if required
124                IF west_in is not null AND east_in is not null AND
125                        north_in is not null AND south_in is not null THEN
126
127                        cmdStr := 'INSERT INTO SPATIAL_DATA (spatial_data_id, geometry, create_date)
128                                VALUES (DEFAULT,
129                                SetSRID(''BOX3D(' || west_in || ' ' || south_in || ', ' || east_in || ' ' || 
130                                        north_in || ')''::box3d,4326),
131                                current_timestamp)'; 
132       
133                -- For debugging only
134                        --raise notice 'cmdStr is: %', cmdStr;
135
136                EXECUTE cmdStr;
137
138                        SELECT MAX(spatial_data_id) INTO spatial_db_id FROM SPATIAL_DATA;
139                        --RAISE NOTICE 'Spatial data added - with ID: %', spatial_db_id;
140                END IF;
141
142        -- This inserts a new row in the temporal data table, if required
143        IF start_time_in is not null OR end_time_in is not null THEN
144                INSERT INTO TEMPORAL_DATA (temporal_data_id, start_time, end_time, create_date) 
145                                VALUES (DEFAULT, start_time_in, end_time_in, current_timestamp);
146
147                        SELECT MAX(temporal_data_id) INTO temporal_db_id FROM TEMPORAL_DATA;
148                        --RAISE NOTICE 'Temporal data added - with ID: %', temporal_db_id;
149                END IF;
150
151                -- Now create row in link table
152                INSERT INTO SPATIAL_TEMPORAL_DATA (spatial_temporal_data_id, original_document_id,
153                        spatial_data_id, temporal_data_id, create_date) VALUES
154                        (DEFAULT, original_document_id_in, spatial_db_id, temporal_db_id, current_timestamp);
155               
156     END;
157$$ LANGUAGE 'plpgsql';
Note: See TracBrowser for help on using the repository browser.