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
|
--
-- Enforce unique user names
--
create unique index users_namename_uniq on users(user_name);
DROP INDEX user_user;
DROP INDEX idx_users_username;
--
-- INSTALL PL/pgSQL
--
CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
AS '$libdir/plpgsql', 'plpgsql_call_handler'
LANGUAGE c;
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
--
-- Define a trigger so when you create a new ArtifactType
-- You automatically create a related row over in the counters table
--
CREATE FUNCTION forumgrouplist_insert_agg () RETURNS OPAQUE AS '
BEGIN
INSERT INTO forum_agg_msg_count (group_forum_id,count) \
VALUES (NEW.group_forum_id,0);
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER forumgrouplist_insert_trig AFTER INSERT ON forum_group_list
FOR EACH ROW EXECUTE PROCEDURE forumgrouplist_insert_agg();
--
-- Define a rule so that when new forum messages are submitted,
-- the counters increment
--
CREATE RULE forum_insert_agg AS
ON INSERT TO forum
DO UPDATE forum_agg_msg_count SET count=count+1
WHERE group_forum_id=new.group_forum_id;
CREATE RULE forum_delete_agg AS
ON DELETE TO forum
DO UPDATE forum_agg_msg_count SET count=count-1
WHERE group_forum_id=old.group_forum_id;
--
-- People want the open counts added to the artifact counts
--
ALTER TABLE artifact_counts_agg ADD COLUMN open_count int;
--
-- Define a trigger so when you create a new ArtifactType
-- You automatically create a related row over in the counters table
--
CREATE FUNCTION artifactgrouplist_insert_agg () RETURNS OPAQUE AS '
BEGIN
INSERT INTO artifact_counts_agg (group_artifact_id,count,open_count) \
VALUES (NEW.group_artifact_id,0,0);
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER artifactgrouplist_insert_trig AFTER INSERT ON artifact_group_list
FOR EACH ROW EXECUTE PROCEDURE artifactgrouplist_insert_agg();
--
-- Define a rule so that when new artifacts are submitted,
-- the counters increment
--
CREATE RULE artifact_insert_agg AS
ON INSERT TO artifact
DO UPDATE artifact_counts_agg SET count=count+1,open_count=open_count+1
WHERE group_artifact_id=new.group_artifact_id;
--
--
--
drop TRIGGER artifactgroup_update_trig ON artifact;
CREATE FUNCTION artifactgroup_update_agg () RETURNS OPAQUE AS '
BEGIN
--
-- see if they are moving to a new artifacttype
-- if so, its a more complex operation
--
IF NEW.group_artifact_id <> OLD.group_artifact_id THEN
--
-- transferred artifacts always have a status of 1
-- so we will increment the new artifacttypes sums
--
UPDATE artifact_counts_agg SET count=count+1, open_count=open_count+1 \
WHERE group_artifact_id=NEW.group_artifact_id;
--
-- now see how to increment/decrement the old types sums
--
IF NEW.status_id <> OLD.status_id THEN
IF OLD.status_id = 2 THEN
UPDATE artifact_counts_agg SET count=count-1 \
WHERE group_artifact_id=OLD.group_artifact_id;
--
-- no need to do anything if it was in deleted status
--
END IF;
ELSE
--
-- Was already in open status before
--
UPDATE artifact_counts_agg SET count=count-1, open_count=open_count-1 \
WHERE group_artifact_id=OLD.group_artifact_id;
END IF;
ELSE
--
-- just need to evaluate the status flag and
-- increment/decrement the counter as necessary
--
IF NEW.status_id <> OLD.status_id THEN
IF new.status_id = 1 THEN
UPDATE artifact_counts_agg SET open_count=open_count+1 \
WHERE group_artifact_id=new.group_artifact_id;
ELSE
IF new.status_id = 2 THEN
UPDATE artifact_counts_agg SET open_count=open_count-1 \
WHERE group_artifact_id=new.group_artifact_id;
ELSE
IF new.status_id = 3 THEN
UPDATE artifact_counts_agg SET open_count=open_count-1,count=count-1 \
WHERE group_artifact_id=new.group_artifact_id;
END IF;
END IF;
END IF;
END IF;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER artifactgroup_update_trig AFTER UPDATE ON artifact
FOR EACH ROW EXECUTE PROCEDURE artifactgroup_update_agg();
|
