-- This retrieves all the pl.id for perl-* packages in non-EOL branches where the owner is cweyl select pl.id from packagelisting as pl, package as p, collection as c where -- joins p.id = pl.packageid and c.id = pl.collectionid -- only active collections and c.statuscode in (1, 18) -- what we're interestd in and pl.owner = 'cweyl' and p.name like 'perl-%'; -- retrieve all existing comaintainership requests select ppla.id from personpackagelisting as ppl, personpackagelistingacl as ppla where -- joins ppl.id = ppla.personpackagelistingid -- packagelistings where cweyl is the owner and ppl.packagelistingid in ( select pl.id from packagelisting as pl, package as p, collection as c where -- joins p.id = pl.packageid and c.id = pl.collectionid -- only active collections and c.statuscode in (1, 18) -- what we're interestd in. and pl.owner = 'cweyl' and p.name like 'perl-%' ) -- comaint we're interested in and ppl.username in ('mmaslano', 'psabata', 'ppisar'); -- approve the existing comaintainership requests update personpackagelistingacl set statuscode = 3 where id in ( select ppla.id from personpackagelisting as ppl, personpackagelistingacl as ppla where -- joins ppl.id = ppla.personpackagelistingid -- packagelistings where cweyl is the owner and ppl.packagelistingid in ( select pl.id from packagelisting as pl, package as p, collection as c where -- joins p.id = pl.packageid and c.id = pl.collectionid -- only active collections and c.statuscode in (1, 18) -- what we're interestd in. and pl.owner = 'cweyl' and p.name like 'perl-%' ) -- comaint we're interested in and ppl.username in ('mmaslano', 'psabata', 'ppisar') ); -- Insert a ppl where needed -- username should cycle: mmaslano, psabata, ppisar -- In two places insert into personpackagelisting (username, packagelistingid) (select 'mmaslano', pl.id from packagelisting as pl where -- packagelisting in set of packagelistings owned by cweyl pl.id in ( select pl.id from packagelisting as pl, package as p, collection as c where -- joins p.id = pl.packageid and c.id = pl.collectionid -- only active collections and c.statuscode in (1, 18) -- what we're interestd in and pl.owner = 'cweyl' and p.name like 'perl-%' ) -- packagelisting not in the packagelistings that already have a ppl for this person and pl.id not in ( select ppl.packagelistingid from personpackagelisting as ppl where ppl.username = 'mmaslano' ) ); -- insert a ppla where needed -- username cycles between mmaslano, psabata, ppisar -- acl cycles between watchbugzilla, watchcommits, commit, approveacls insert into personpackagelistingacl (personpackagelistingid, acl, statuscode) (select ppl.id, 'approveacls', 3 from personpackagelisting as ppl where -- all ppls for the comaint and cweyl's packages ppl.id in ( select id from personpackagelisting as ppl where ppl.username = 'ppisar' and ppl.packagelistingid in ( select pl.id from packagelisting as pl, package as p, collection as c where -- joins p.id = pl.packageid and c.id = pl.collectionid -- only active collections and c.statuscode in (1, 18) -- what we're interestd in and pl.owner = 'cweyl' and p.name like 'perl-%' ) ) -- all ppls where a ppla already exists and ppl.id not in (select ppla.personpackagelistingid from personpackagelistingacl as ppla where ppla.acl = 'approveacls' and ppla.personpackagelistingid in (select id from personpackagelisting as ppl where username = 'ppisar') ) ); -- select comaintainers where pl.owner = 'cweyl' and p.name like 'perl-%' -- select comaintainers where pl.owner = 'mmaslano' and p.name like 'perl-%' -- update owner of cweyl packages to someone who is around update packagelisting set owner = 'mmaslano' where id in ( select pl.id from packagelisting as pl, package as p, collection as c where -- joins p.id = pl.packageid and c.id = pl.collectionid -- only active collections and c.statuscode in (1, 18) -- what we're interestd in. and pl.owner = 'cweyl' and p.name like 'perl-%');