1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
|
CREATE SEQUENCE activity_seq;
CREATE TABLE patch_audit (
patch_id integer not null,
change_type varchar not null,
changed_fields varchar[] not null,
commitfest_id integer not null,
original_name varchar not null,
from_commitfest_id integer,
to_commitfest_id integer,
commitfest_topic_id integer not null,
name varchar not null,
patch_status_id integer not null,
author varchar not null,
reviewers varchar not null,
date_closed date,
last_updater varchar,
last_updated_time timestamp with time zone not null
);
ALTER TABLE patch_audit ADD COLUMN activity_id INTEGER NOT NULL
DEFAULT nextval('activity_seq');
ALTER TABLE patch_audit ADD COLUMN committer VARCHAR NOT NULL
DEFAULT '';
CREATE OR REPLACE FUNCTION patch_audit() RETURNS trigger AS $$
DECLARE
cf varchar[] := '{}'::varchar[];
acid integer;
cid integer;
oname varchar;
BEGIN
IF (TG_OP = 'DELETE') THEN
NEW := OLD;
NEW.last_updated_time := now();
END IF;
IF (TG_OP = 'UPDATE') THEN
IF (OLD.last_updated_time = NEW.last_updated_time) THEN
-- Some kind of system update, just ignore it.
RETURN NULL;
END IF;
cf := CASE WHEN OLD.commitfest_topic_id != NEW.commitfest_topic_id
THEN '{commitfest_topic_id}'::varchar[]
ELSE '{}'::varchar[] END
|| CASE WHEN OLD.name != NEW.name
THEN '{name}'::varchar[]
ELSE '{}'::varchar[] END
|| CASE WHEN OLD.patch_status_id != NEW.patch_status_id
THEN '{patch_status_id}'::varchar[]
ELSE '{}'::varchar[] END
|| CASE WHEN OLD.author != NEW.author
THEN '{author}'::varchar[]
ELSE '{}'::varchar[] END
|| CASE WHEN OLD.reviewers != NEW.reviewers
THEN '{reviewers}'::varchar[]
ELSE '{}'::varchar[] END
|| CASE WHEN OLD.committer != NEW.committer
THEN '{committer}'::varchar[]
ELSE '{}'::varchar[] END
|| CASE WHEN OLD.date_closed IS DISTINCT FROM NEW.date_closed
THEN '{date_closed}'::varchar[]
ELSE '{}'::varchar[] END;
SELECT INTO cid
commitfest_id
FROM
commitfest_topic
WHERE
id = NEW.commitfest_topic_id;
SELECT INTO acid
commitfest_id
FROM
commitfest_topic
WHERE
id = OLD.commitfest_topic_id;
oname := OLD.name;
ELSE
SELECT INTO cid, acid
commitfest_id, commitfest_id
FROM
commitfest_topic
WHERE
id = NEW.commitfest_topic_id;
oname := NEW.name;
END IF;
IF (TG_OP = 'INSERT') THEN
cf := ARRAY['commitfest_id', 'commitfest_topic_id', 'name',
'patch_status_id', 'author', 'reviewers',
'committer', 'date_closed'];
END IF;
INSERT INTO patch_audit
(patch_id, change_type, changed_fields, commitfest_id, original_name,
to_commitfest_id, commitfest_topic_id, name,
patch_status_id, author, reviewers, committer, date_closed,
last_updater, last_updated_time)
VALUES
(NEW.id, TG_OP, cf, acid, oname,
CASE WHEN cid != acid THEN cid ELSE NULL END,
NEW.commitfest_topic_id, NEW.name, NEW.patch_status_id, NEW.author,
NEW.reviewers, NEW.committer, NEW.date_closed, NEW.last_updater,
NEW.last_updated_time);
-- For an update that changes the CommitFest, we enter two audit records,
-- one under each CommitFest.
IF (cid != acid) THEN
INSERT INTO patch_audit
(patch_id, change_type, changed_fields, commitfest_id,
original_name,
from_commitfest_id, commitfest_topic_id, name,
patch_status_id, author, reviewers, committer, date_closed,
last_updater, last_updated_time)
VALUES
(NEW.id, TG_OP, cf, cid, oname, acid,
NEW.commitfest_topic_id, NEW.name, NEW.patch_status_id, NEW.author,
NEW.reviewers, NEW.committer, NEW.date_closed, NEW.last_updater,
NEW.last_updated_time);
END IF;
RETURN NULL;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER patch_audit
AFTER INSERT OR UPDATE OR DELETE ON patch
FOR EACH ROW EXECUTE PROCEDURE patch_audit();
CREATE OR REPLACE FUNCTION patch_audit_details(patch_audit) RETURNS text AS $$
DECLARE
v varchar[];
BEGIN
IF ('name' = ANY($1.changed_fields)) THEN
v := v || ('Name = ' || $1.name)::varchar;
END IF;
IF ($1.from_commitfest_id IS NOT NULL) THEN
v := v || ('Moved From CommitFest = ' || COALESCE((SELECT name FROM
commitfest WHERE id = $1.from_commitfest_id), '???'))::varchar;
END IF;
IF ($1.to_commitfest_id IS NOT NULL) THEN
v := v || ('Moved To CommitFest = ' || COALESCE((SELECT name FROM
commitfest WHERE id = $1.to_commitfest_id), '???'))::varchar;
END IF;
IF ('commitfest_topic_id' = ANY($1.changed_fields)) THEN
v := v || ('Topic = ' || COALESCE((SELECT name FROM commitfest_topic
WHERE id = $1.commitfest_topic_id), '???'))::varchar;
END IF;
IF ('patch_status_id' = ANY($1.changed_fields)) THEN
v := v || ('Patch Status = ' || COALESCE((SELECT name FROM patch_status
WHERE id = $1.patch_status_id), '???'))::varchar;
END IF;
IF ('author' = ANY($1.changed_fields)) THEN
v := v || ('Author = ' || $1.author)::varchar;
END IF;
IF ('reviewers' = ANY($1.changed_fields)) THEN
v := v || ('Reviewers = ' || CASE WHEN $1.reviewers = '' THEN 'Nobody'
ELSE $1.reviewers END)::varchar;
END IF;
IF ('committer' = ANY($1.changed_fields)) THEN
v := v || ('Committer = ' || CASE WHEN $1.committer = '' THEN 'Nobody'
ELSE $1.committer END)::varchar;
END IF;
IF ('date_closed' = ANY($1.changed_fields)) THEN
v := v || ('Date Closed = '
|| COALESCE($1.date_closed::varchar, 'NULL'))::varchar;
END IF;
RETURN array_to_string(v, ', ');
END
$$ LANGUAGE plpgsql;
CREATE TABLE patch_comment_audit (
id integer not null,
patch_id integer not null,
change_type varchar not null,
changed_fields varchar[] not null,
commitfest_id integer not null,
patch_name varchar not null,
patch_comment_type_id integer not null,
message_id varchar,
content varchar,
last_updater varchar,
last_updated_time timestamp with time zone not null
);
ALTER TABLE patch_comment_audit ADD COLUMN activity_id INTEGER NOT NULL
DEFAULT nextval('activity_seq');
CREATE OR REPLACE FUNCTION patch_comment_audit() RETURNS trigger AS $$
DECLARE
cf varchar[] := '{}'::varchar[];
cid integer;
pname varchar;
BEGIN
IF (TG_OP = 'DELETE') THEN
NEW := OLD;
NEW.last_updated_time := now();
END IF;
IF (TG_OP = 'UPDATE') THEN
IF (OLD.last_updated_time = NEW.last_updated_time) THEN
-- Some kind of system update, just ignore it.
RETURN NULL;
END IF;
cf := CASE WHEN OLD.patch_comment_type_id != NEW.patch_comment_type_id
THEN '{patch_comment_type_id}'::varchar[]
ELSE '{}'::varchar[] END
|| CASE WHEN OLD.message_id != NEW.message_id
THEN '{message_id}'::varchar[]
ELSE '{}'::varchar[] END
|| CASE WHEN OLD.content != NEW.content
THEN '{content}'::varchar[]
ELSE '{}'::varchar[] END;
END IF;
SELECT INTO cid, pname
t.commitfest_id, p.name
FROM
patch p
JOIN commitfest_topic t ON p.commitfest_topic_id = t.id
WHERE
p.id = NEW.patch_id;
IF (TG_OP = 'INSERT') THEN
cf := ARRAY['patch_comment_type_id', 'message_id', 'content' ];
END IF;
INSERT INTO patch_comment_audit
(id, patch_id, change_type, changed_fields, commitfest_id, patch_name,
patch_comment_type_id, message_id, content, last_updater,
last_updated_time)
VALUES
(NEW.id, NEW.patch_id, TG_OP, cf, cid, pname,
NEW.patch_comment_type_id, NEW.message_id, NEW.content,
NEW.last_updater, NEW.last_updated_time);
RETURN NULL;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER patch_comment_audit
AFTER INSERT OR UPDATE OR DELETE ON patch_comment
FOR EACH ROW EXECUTE PROCEDURE patch_comment_audit();
CREATE OR REPLACE FUNCTION patch_comment_audit_details(patch_comment_audit)
RETURNS text AS $$
SELECT
(SELECT name FROM patch_comment_type WHERE id = $1.patch_comment_type_id)
|| ': ' || $1.content
|| CASE WHEN $1.message_id != '' THEN ' (Message-ID: '
|| $1.message_id || ')' ELSE '' END
$$ LANGUAGE sql;
|
