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


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
INNER JOIN carnivore_emails ON (carnivore_names.id = carnivore_emails.id)
INNER JOIN d2r_t_carnivore_mailtosha1 ON (d2r_t_carnivore_mailtosha1.email = carnivore_emails.email)
LEFT OUTER JOIN carnivore_keys ON (carnivore_keys.id = carnivore_names.id);


-- Distinct emails and their SHA1s from BUGS
CREATE TABLE d2r_t_bugsubmitters_mailtosha1 AS
SELECT distinct submitter_email, sha1mailto(submitter_email)
FROM bugs;

-- map submitter emails and carnivore emails
CREATE VIEW d2r_v_bugsubm_carn AS
SELECT d2r_t_bugsubmitters_mailtosha1.submitter_email, d2r_t_bugsubmitters_mailtosha1.sha1mailto, d2r_t_carnivore_mailtosha1.id AS carnivore_id, d2r_t_carnivore_mailtosha1.mailtosha1
FROM d2r_t_bugsubmitters_mailtosha1
LEFT JOIN d2r_t_carnivore_mailtosha1 ON d2r_t_bugsubmitters_mailtosha1.submitter_email = d2r_t_carnivore_mailtosha1.email;

-- link bugs - foaf
CREATE VIEW d2r_v_bug_submitters AS
SELECT bugs.id, bugs.submitter_name, d2r_v_bugsubm_carn.sha1mailto, d2r_v_bugsubm_carn.mailtosha1
FROM bugs, d2r_v_bugsubm_carn
WHERE bugs.submitter_email = d2r_v_bugsubm_carn.submitter_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_spackage AS 
SELECT DISTINCT source
FROM bugs_packages
ORDER BY source;

CREATE VIEW d2r_bpackage AS 
SELECT DISTINCT package, source
FROM bugs_packages
ORDER BY package;