2 -- Enforce unique user names
5 create unique index users_namename_uniq on users(user_name);
7 DROP INDEX idx_users_username;
12 CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
13 AS '$libdir/plpgsql', 'plpgsql_call_handler'
16 CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
17 HANDLER plpgsql_call_handler
18 LANCOMPILER 'PL/pgSQL';
21 -- Define a trigger so when you create a new ArtifactType
22 -- You automatically create a related row over in the counters table
24 CREATE FUNCTION forumgrouplist_insert_agg () RETURNS OPAQUE AS '
26 INSERT INTO forum_agg_msg_count (group_forum_id,count) \
27 VALUES (NEW.group_forum_id,0);
32 CREATE TRIGGER forumgrouplist_insert_trig AFTER INSERT ON forum_group_list
33 FOR EACH ROW EXECUTE PROCEDURE forumgrouplist_insert_agg();
36 -- Define a rule so that when new forum messages are submitted,
37 -- the counters increment
39 CREATE RULE forum_insert_agg AS
41 DO UPDATE forum_agg_msg_count SET count=count+1
42 WHERE group_forum_id=new.group_forum_id;
44 CREATE RULE forum_delete_agg AS
46 DO UPDATE forum_agg_msg_count SET count=count-1
47 WHERE group_forum_id=old.group_forum_id;
51 -- People want the open counts added to the artifact counts
53 ALTER TABLE artifact_counts_agg ADD COLUMN open_count int;
56 -- Define a trigger so when you create a new ArtifactType
57 -- You automatically create a related row over in the counters table
59 CREATE FUNCTION artifactgrouplist_insert_agg () RETURNS OPAQUE AS '
61 INSERT INTO artifact_counts_agg (group_artifact_id,count,open_count) \
62 VALUES (NEW.group_artifact_id,0,0);
67 CREATE TRIGGER artifactgrouplist_insert_trig AFTER INSERT ON artifact_group_list
68 FOR EACH ROW EXECUTE PROCEDURE artifactgrouplist_insert_agg();
71 -- Define a rule so that when new artifacts are submitted,
72 -- the counters increment
74 CREATE RULE artifact_insert_agg AS
76 DO UPDATE artifact_counts_agg SET count=count+1,open_count=open_count+1
77 WHERE group_artifact_id=new.group_artifact_id;
82 drop TRIGGER artifactgroup_update_trig ON artifact;
84 CREATE FUNCTION artifactgroup_update_agg () RETURNS OPAQUE AS '
87 -- see if they are moving to a new artifacttype
88 -- if so, its a more complex operation
90 IF NEW.group_artifact_id <> OLD.group_artifact_id THEN
92 -- transferred artifacts always have a status of 1
93 -- so we will increment the new artifacttypes sums
95 UPDATE artifact_counts_agg SET count=count+1, open_count=open_count+1 \
96 WHERE group_artifact_id=NEW.group_artifact_id;
99 -- now see how to increment/decrement the old types sums
101 IF NEW.status_id <> OLD.status_id THEN
102 IF OLD.status_id = 2 THEN
103 UPDATE artifact_counts_agg SET count=count-1 \
104 WHERE group_artifact_id=OLD.group_artifact_id;
106 -- no need to do anything if it was in deleted status
111 -- Was already in open status before
113 UPDATE artifact_counts_agg SET count=count-1, open_count=open_count-1 \
114 WHERE group_artifact_id=OLD.group_artifact_id;
118 -- just need to evaluate the status flag and
119 -- increment/decrement the counter as necessary
121 IF NEW.status_id <> OLD.status_id THEN
122 IF new.status_id = 1 THEN
123 UPDATE artifact_counts_agg SET open_count=open_count+1 \
124 WHERE group_artifact_id=new.group_artifact_id;
126 IF new.status_id = 2 THEN
127 UPDATE artifact_counts_agg SET open_count=open_count-1 \
128 WHERE group_artifact_id=new.group_artifact_id;
130 IF new.status_id = 3 THEN
131 UPDATE artifact_counts_agg SET open_count=open_count-1,count=count-1 \
132 WHERE group_artifact_id=new.group_artifact_id;
140 ' LANGUAGE 'plpgsql';
142 CREATE TRIGGER artifactgroup_update_trig AFTER UPDATE ON artifact
143 FOR EACH ROW EXECUTE PROCEDURE artifactgroup_update_agg();