CREATE TABLE d2r_t_bugsubmitters AS
SELECT distinct submitter, email_phrase("submitter"),email_address("submitter"), sha1mailto(email_address("submitter")), sha1mail(email_address("submitter"))
FROM bugs

CREATE view d2r_carnivore AS
SELECT carnivore_names.id, carnivore_names.name, carnivore_emails.email, sha1mailto(carnivore_emails.email) AS sha1mailto
FROM carnivore_names, carnivore_emails
WHERE carnivore_names.id = carnivore_emails.id;


CREATE VIEW d2r_bugsumcar AS
SELECT d2r_t_bugsubmitters.submitter, d2r_t_bugsubmitters.email_address,d2r_t_bugsubmitters.sha1mail, carnivore_emails.id AS carnivore_id, carnivore_emails.email
FROM d2r_t_bugsubmitters
LEFT JOIN carnivore_emails ON d2r_t_bugsubmitters.email_address = carnivore_emails.email;



CREATE VIEW d2r_bugsouterjoin AS 
SELECT bugs.id, bugs.package, bugs.source, bugs.arrival, bugs.status, bugs.severity::character varying AS severity, bugs.owner, bugs.title, bugs.last_modified, bugs.affects_stable, bugs.affects_testing, bugs.affects_unstable, bugs.affects_experimental, bugs.forwarded, bugs.done, d2r_bugsumcar.sha1mail, d2r_bugsumcar.carnivore_id
FROM bugs, d2r_bugsumcar
WHERE bugs.submitter = d2r_bugsumcar.submitter;

CREATE VIEW d2r_package AS 
SELECT DISTINCT bugs.package
FROM bugs 
ORDER BY bugs.package;