fedora-accounts cvsacct.sql,1.17,1.18

[Toshio Ernie Kuratomi (toshio)]

Author: toshio

Update of /cvs/fedora/fedora-accounts
In directory cvs-int.fedora.redhat.com:/tmp/cvs-serv14501

Modified Files:
	cvsacct.sql 
Log Message:
SQL tables and a trigger to store role changes in a queue when they affect
bugzilla.



Index: cvsacct.sql
===================================================================
RCS file: /cvs/fedora/fedora-accounts/cvsacct.sql,v
retrieving revision 1.17
retrieving revision 1.18
diff -u -r1.17 -r1.18
--- cvsacct.sql	28 Sep 2005 20:07:21 -0000	1.17
+++ cvsacct.sql	23 Aug 2007 22:45:13 -0000	1.18
@@ -65,6 +65,52 @@
 -- 	UNIQUE (person_id, project_group_id, role_domain)
 -- );
 
+-- action r == remove
+-- action a == add
+-- create table bugzilla_queue (
+--   person_id int references person(id),
+--   project_group_id int references project_group(id),
+--   action char(1) not null,
+--   primary key (person_id, project_group_id),
+--   check (action ~ '[ar]')
+-- );
+
+-- create trusted procedural language plpgsql
+--   handler plpgsql_call_handler
+--   validator plpgsql_validator;
+
+-- create or replace function bugzilla_sync() returns trigger AS $bz_sync$
+-- DECLARE
+--   newaction char(1);
+--   ROW role%ROWTYPE;
+-- BEGIN
+--   if TG_OP = 'DELETE' then
+--     newaction:='r';
+--     ROW := OLD;
+--   else
+--     -- INSERT or UPDATE
+--     ROW := NEW;
+--     if NEW.role_status = 'approved' then
+--       newaction := 'a';
+--     else
+--       newaction := 'r';
+--     end if;
+--   end if;
+--   if ROW.project_group_id = id from project_group where name = 'fedorabugs' then
+--     if person_id is not Null from bugzilla_queue where person_id = ROW.person_id then
+--       update bugzilla_queue set action = newaction where person_id = ROW.person_id;
+--     else
+--       insert into bugzilla_queue values (ROW.person_id, ROW.project_group_id, newaction);
+--     end if;
+--   end if;
+--   return ROW;
+-- END;
+-- $bz_sync$ language plpgsql;
+
+-- create trigger role_bugzilla_sync before update or insert or delete
+--  on role
+--  for each row execute procedure bugzilla_sync();
+
 -- INSERT INTO role VALUES ((SELECT id FROM person WHERE username = 'admin'),
 -- 		  	 (SELECT id FROM project_group WHERE name = 'cla_done'),
 -- 			 'administrator', NULL, 'approved', NULL, NULL);