· 7 years ago · Oct 10, 2018, 06:28 AM
1diff --git a/data/db/createfunctions.sql b/data/db/createfunctions.sql
2index ecaf46f..9191752 100644
3--- a/data/db/createfunctions.sql
4+++ b/data/db/createfunctions.sql
5@@ -2783,6 +2783,81 @@ BEGIN
6 END;
7 $$ language plpgsql;
8
9+CREATE OR REPLACE FUNCTION fct_update_specimen_flat_multimedia()
10+RETURNS TRIGGER
11+AS
12+$$
13+DECLARE
14+ spec_id integer;
15+ ind_id integer;
16+ part_id integer;
17+ do_update boolean default false;
18+BEGIN
19+
20+ IF TG_OP = 'INSERT' THEN
21+ do_update := true;
22+ END IF;
23+
24+ IF TG_OP = 'UPDATE' THEN
25+ IF NEW.referenced_relation IS DISTINCT FROM OLD.referenced_relation OR
26+ NEW.record_id IS DISTINCT FROM OLD.record_id THEN
27+ do_update := true;
28+ END IF;
29+ END IF;
30+
31+ IF do_update THEN
32+ -- CHECK if we need to add with file flag
33+ IF NEW.referenced_relation = 'specimen_parts' THEN
34+ select i.specimen_ref into spec_id from specimen_parts p
35+ INNER JOIN specimen_individuals i on p.specimen_individual_ref = i.id
36+ WHERE p.id = NEW.record_id;
37+
38+ ELSIF NEW.referenced_relation = 'specimen_individuals' THEN
39+ select i.specimen_ref into spec_id from specimen_individuals i
40+ WHERE i.id = NEW.record_id;
41+
42+ ELSIF NEW.referenced_relation = 'specimens' THEN
43+ spec_id := NEW.record_id;
44+ END IF;
45+
46+ UPDATE specimens_flat SET with_files = true where specimen_ref = spec_id;
47+ END IF;
48+
49+ IF TG_OP = 'DELETE' OR (TG_OP = 'UPDATE' AND do_update) THEN
50+ -- CHECK if we need to remove with file flag
51+
52+ IF OLD.referenced_relation = 'specimen_parts' THEN
53+ select i.specimen_ref, i.id, p.id into spec_id, ind_id, part_id from specimen_parts p
54+ INNER JOIN specimen_individuals i on p.specimen_individual_ref = i.id
55+ WHERE p.id = OLD.record_id;
56+
57+ ELSIF OLD.referenced_relation = 'specimen_individuals' THEN
58+ raise warning 'ouuups';
59+ select i.specimen_ref, i.id into spec_id, ind_id from specimen_individuals i
60+ WHERE i.id = OLD.record_id;
61+
62+ ELSIF OLD.referenced_relation = 'specimens' THEN
63+ spec_id := OLD.record_id;
64+ ELSE -- ONly do something for spec, ind an part
65+ RETURN OLD;
66+ END IF;
67+
68+
69+ UPDATE specimens_flat SET with_files = FALSE where specimen_ref = spec_id AND NOT EXISTS (
70+ select 1 from multimedia where record_id = (
71+ CASE WHEN referenced_relation = 'specimens' THEN spec_id
72+ WHEN referenced_relation = 'specimen_individuals' THEN ind_id
73+ WHEN referenced_relation = 'specimen_parts' THEN part_id
74+ ELSE 0
75+ END
76+ )
77+ );
78+
79+ END IF;
80+ RETURN NEW;
81+END;
82+$$ language plpgsql;
83+
84 CREATE OR REPLACE FUNCTION fct_upd_people_in_flat() RETURNS TRIGGER
85 AS
86 $$
87diff --git a/data/db/createtables.sql b/data/db/createtables.sql
88index aefd714..815cce0 100644
89--- a/data/db/createtables.sql
90+++ b/data/db/createtables.sql
91@@ -1698,6 +1698,8 @@ CREATE TABLE specimens_flat (
92 spec_don_sel_ids integer[] not null default '{}',
93 with_types boolean not null default false,
94 with_individuals boolean not null default false,
95+ with_files boolean not null default false,
96+
97 collection_type varchar,
98 collection_code varchar,
99 collection_name varchar,
100diff --git a/data/db/createtriggers.sql b/data/db/createtriggers.sql
101index ca7083b..f8753da 100644
102--- a/data/db/createtriggers.sql
103+++ b/data/db/createtriggers.sql
104@@ -667,6 +667,9 @@ CREATE TRIGGER trg_fct_count_units_parts AFTER INSERT OR DELETE
105 ON specimen_parts FOR EACH ROW
106 EXECUTE PROCEDURE fct_count_units();
107
108+CREATE TRIGGER trg_update_specimen_flat_multimedia AFTER INSERT OR UPDATE OR DELETE
109+ ON multimedia FOR EACH ROW
110+ EXECUTE PROCEDURE fct_update_specimen_flat_multimedia();
111
112 CREATE TRIGGER trg_unpromotion_remove_cols AFTER UPDATE
113 ON users FOR EACH ROW
114diff --git a/data/db/tests/chk_darwin_flat_synchro.sql b/data/db/tests/chk_darwin_flat_synchro.sql
115index ff607d7..4419ac9 100755
116--- a/data/db/tests/chk_darwin_flat_synchro.sql
117+++ b/data/db/tests/chk_darwin_flat_synchro.sql
118@@ -237,5 +237,51 @@ SELECT diag('Test cascade delete of a specimen');
119 SELECT lives_ok('DELETE FROM specimens WHERE id = 100000', 'Delete seems to work');
120 SELECT is(0::bigint , (SELECT COUNT(*) FROM darwin_flat WHERE spec_ref = 100000), 'Effectively deleted');
121
122+/*** SPECIMENS FLAT ****/
123+
124+SELECT diag('Test with_files');
125+
126+
127+SELECT is( false , (SELECT with_files FROM specimens_flat WHERE specimen_ref = 100001), 'Check previously false');
128+
129+SELECT lives_ok('INSERT into multimedia (id, referenced_relation, record_id, title, mime_type) VALUES (12,''specimens'', 100001, ''test multimedia'', ''text/csv'')', 'Insert multimedia on spec');
130+SELECT is( true , (SELECT with_files FROM specimens_flat WHERE specimen_ref = 100001), 'Check now correct');
131+SELECT lives_ok('delete from multimedia where id = 12', 'delete multimedia on spec');
132+SELECT is( false , (SELECT with_files FROM specimens_flat WHERE specimen_ref = 100001), 'correctly unset multimedia');
133+
134+INSERT INTO specimen_individuals (id, specimen_ref, type) VALUES (240276, 100001, 'paratype');
135+
136+SELECT lives_ok('INSERT into multimedia (id, referenced_relation, record_id, title, mime_type) VALUES (12,''specimen_individuals'', 240276, ''test multimedia'', ''text/csv'')', 'Insert multimedia on ind');
137+SELECT is( true , (SELECT with_files FROM specimens_flat WHERE specimen_ref = 100001), 'Check now correct');
138+SELECT lives_ok('delete from multimedia where id = 12', 'delete multimedia on ind');
139+SELECT is( false , (SELECT with_files FROM specimens_flat WHERE specimen_ref = 100001), 'correctly unset multimedia');
140+
141+INSERT INTO specimen_parts (id, specimen_individual_ref, specimen_part) VALUES (240275, 240276, 'specimen');
142+
143+SELECT lives_ok('INSERT into multimedia (id, referenced_relation, record_id, title, mime_type) VALUES (12,''specimen_parts'', 240275, ''test multimedia'', ''text/csv'')', 'Insert multimedia on ind');
144+SELECT is( true , (SELECT with_files FROM specimens_flat WHERE specimen_ref = 100001), 'Check now correct');
145+SELECT lives_ok('delete from multimedia where id = 12', 'delete multimedia on ind');
146+SELECT is( false , (SELECT with_files FROM specimens_flat WHERE specimen_ref = 100001), 'correctly unset multimedia');
147+
148+
149+SELECT lives_ok('INSERT into multimedia (id, referenced_relation, record_id, title, mime_type) VALUES (12,''specimen_parts'', 240275, ''test multimedia'', ''text/csv'')', 'Insert multimedia on ind');
150+SELECT is( true , (SELECT with_files FROM specimens_flat WHERE specimen_ref = 100001), 'Check now correct');
151+SELECT lives_ok('delete from specimen_individuals where id = 240276', 'delete indiv for cascade');
152+SELECT is( false , (SELECT with_files FROM specimens_flat WHERE specimen_ref = 100001), 'Check now correct');
153+
154+INSERT INTO specimen_individuals (id, specimen_ref, type) VALUES (240276, 100001, 'paratype');
155+SELECT lives_ok('INSERT into multimedia (id, referenced_relation, record_id, title, mime_type) VALUES (12,''specimen_individuals'', 240276, ''test multimedia'', ''text/csv'')', 'Insert multimedia on ind');
156+SELECT lives_ok('INSERT into multimedia (id, referenced_relation, record_id, title, mime_type) VALUES (13,''specimens'', 100001, ''test multimedia'', ''text/csv'')', 'Insert multimedia on spec');
157+SELECT is( true , (SELECT with_files FROM specimens_flat WHERE specimen_ref = 100001), 'Check now correct');
158+
159+SELECT lives_ok('delete from multimedia where id = 13', 'delete multimedia on spec');
160+SELECT is( true , (SELECT with_files FROM specimens_flat WHERE specimen_ref = 100001), 'flag still there');
161+
162+SELECT lives_ok('UPDATE multimedia SET record_id = ''100000'' and referenced_relation=''taxonomy'' where id = 12', 'move multimedia to other table');
163+SELECT is( false , (SELECT with_files FROM specimens_flat WHERE specimen_ref = 100001), 'flag false');
164+
165+SELECT lives_ok('UPDATE multimedia SET record_id = ''240276'' and referenced_relation=''specimen_individuals'' where id = 2', 'move back multimedia from other table');
166+SELECT is( true , (SELECT with_files FROM specimens_flat WHERE specimen_ref = 100001), 'flag back true');
167+
168 SELECT * FROM finish();
169 ROLLBACK;