CREATE TABLE d2r_t_carnivore_mailtosha1 AS
SELECT id, email, sha1mailto(email) as mailtosha1
FROM carnivore_emails;


-- 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_v_carnivore AS
SELECT carnivore_names.id, carnivore_names.name, carnivore_emails.email, d2r_t_carnivore_mailtosha1.mailtosha1, carnivore_keys.key
FROM carnivore_names, carnivore_emails, d2r_t_carnivore_mailtosha1, carnivore_keys
WHERE carnivore_names.id = carnivore_emails.id AND d2r_t_carnivore_mailtosha1.email = carnivore_emails.email AND carnivore_keys.id = carnivore_names.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;