fedora-accounts export-bugzilla.py,1.11,1.12 cvsacct.sql,1.19,1.20

[Toshio Ernie Kuratomi (toshio)]

Author: toshio

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

Modified Files:
	export-bugzilla.py cvsacct.sql 
Log Message:
Add functionality to check for changes to email address.



Index: export-bugzilla.py
===================================================================
RCS file: /cvs/fedora/fedora-accounts/export-bugzilla.py,v
retrieving revision 1.11
retrieving revision 1.12
diff -u -r1.11 -r1.12
--- export-bugzilla.py	25 Aug 2007 18:47:06 -0000	1.11
+++ export-bugzilla.py	25 Aug 2007 22:48:52 -0000	1.12
@@ -26,7 +26,7 @@
     server = xmlrpclib.Server(BZSERVER)
 
     # Select all queued bugzilla changes for this group
-    qry = "SELECT p.email, p.human_name, b.action, p.id, g.id" \
+    qry = "SELECT b.email, p.human_name, b.action, p.id, g.id" \
         " from bugzilla_queue as b, person as p, project_group as g" \
         " where b.person_id = p.id and b.project_group_id = g.id" \
         " and g.name = %s"


Index: cvsacct.sql
===================================================================
RCS file: /cvs/fedora/fedora-accounts/cvsacct.sql,v
retrieving revision 1.19
retrieving revision 1.20
diff -u -r1.19 -r1.20
--- cvsacct.sql	25 Aug 2007 18:47:06 -0000	1.19
+++ cvsacct.sql	25 Aug 2007 22:48:52 -0000	1.20
@@ -3,6 +3,10 @@
 -- --- cla_done (has completed the CLA agreement)
 
 
+-- create trusted procedural language plpgsql
+--   handler plpgsql_call_handler
+--   validator plpgsql_validator;
+
 -- DROP TABLE role;
 -- DROP TABLE project_group;
 -- DROP TABLE person;
@@ -68,17 +72,14 @@
 -- action r == remove
 -- action a == add
 -- create table bugzilla_queue (
---   person_id int references person(id),
---   project_group_id int references project_group(id),
+--   email text not null,
+--   project_group_id int references project_group(id) not null,
+--   person_id int references person(id) not null,
 --   action char(1) not null,
---   primary key (person_id, project_group_id),
+--   primary key (email, 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);
@@ -97,10 +98,10 @@
 --     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;
+--     if b.email is not Null from bugzilla_queue as b, person as p where p.id = ROW.person_id and b.email = p.email then
+--       update bugzilla_queue set action = newaction where email in (select email from person where id = ROW.person_id);
 --     else
---       insert into bugzilla_queue values (ROW.person_id, ROW.project_group_id, newaction);
+--       insert into bugzilla_queue select p.email, ROW.project_group_id, ROW.person_id, newaction from person as p where p.id = ROW.person_id;
 --     end if;
 --   end if;
 --   return ROW;
@@ -108,8 +109,38 @@
 -- $bz_sync$ language plpgsql;
 
 -- create trigger role_bugzilla_sync before update or insert or delete
---  on role
---  for each row execute procedure bugzilla_sync();
+--   on role
+--   for each row execute procedure bugzilla_sync();
+
+-- create or replace function bugzilla_sync_email() returns trigger AS $bz_sync_e$
+-- BEGIN
+--   if OLD.email = NEW.email then
+--     -- We only care if the email has been changed
+--     return NEW;
+--   end if;
+-- 
+--   if p.id is not Null from person as p, role as r, project_group as g where p.id = OLD.id and g.name = 'fedorabugs' and r.role_status = 'approved' and r.project_group_id = g.id and r.person_id = p.id then
+--     -- Person belongs to the bugzilla changing group
+--     -- Remove old email
+--     if b.email is not Null from bugzilla_queue as b where b.email = OLD.email then
+--       update bugzilla_queue set action = 'r' where email = OLD.email;
+--     else
+--       insert into bugzilla_queue (select OLD.email, cast(g.id as int), OLD.id, 'r' from project_group as g where g.name = 'fedorabugs' limit 1);
+--     end if;
+--     -- Add new email
+--     if b.email is not Null from bugzilla_queue as b where b.email = NEW.email then
+--       update bugzilla_queue set action = 'a' where email = NEW.email;
+--     else
+--       insert into bugzilla_queue (select NEW.email, cast(g.id as int), NEW.id, 'a' from project_group as g where g.name = 'fedorabugs' limit 1);
+--     end if;
+--   end if;
+--   return NEW;
+-- END;
+-- $bz_sync_e$ language plpgsql;
+
+-- create trigger email_bugzilla_sync before update
+--  on person
+--  for each row execute procedure bugzilla_sync_email();
 
 -- INSERT INTO role VALUES ((SELECT id FROM person WHERE username = 'admin'),
 -- 		  	 (SELECT id FROM project_group WHERE name = 'cla_done'),