1 | /* |
---|
2 | * Procedures used by the DB code as part of the ingest process |
---|
3 | * |
---|
4 | * C Byrom Apr 08 |
---|
5 | * |
---|
6 | */ |
---|
7 | DROP FUNCTION add_document_to_history() CASCADE; |
---|
8 | CREATE 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 | |
---|
22 | DROP FUNCTION create_document(filename_in varchar(255), discovery_id_in varchar(255), |
---|
23 | doc_type_in text, original_document_in text) CASCADE; |
---|
24 | CREATE 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 |
---|
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, 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 | |
---|
43 | DROP FUNCTION delete_document(original_document_id_in int) CASCADE; |
---|
44 | CREATE 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 | |
---|
57 | DROP FUNCTION update_document(original_document_id_in int, filename_in varchar(255), |
---|
58 | discovery_id_in varchar(255), doc_type_in text, original_document_in text, scn_in int) CASCADE; |
---|
59 | CREATE FUNCTION update_document(original_document_id_in int, filename_in varchar(255), |
---|
60 | discovery_id_in varchar(255), doc_type_in text, original_document_in text, scn_in int) |
---|
61 | RETURNS VOID AS |
---|
62 | $$ |
---|
63 | DECLARE |
---|
64 | scn_current integer; |
---|
65 | BEGIN |
---|
66 | -- This updates an existing document |
---|
67 | |
---|
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 |
---|
72 | RAISE EXCEPTION 'Invalid SCN specified (current scn=%, specified scn=%) - |
---|
73 | implies record has been changed during update - exiting...', scn_current, scn_in; |
---|
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 | |
---|
90 | DROP 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; |
---|
93 | CREATE 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 |
---|
132 | INSERT INTO SPATIAL_TEMPORAL_DATA (spatial_temporal_data_id, original_document_id, |
---|
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'; |
---|