-- Tables and views needed by D2R mapping

-- We mainly need to convert emails to their mailto+sha1sum versions
-- to anonymize them, and make them compatible with FOAF / SIOC
-- standard

-- $Id: views.sql 306 2010-08-04 16:29:40Z berger_o $

-- Changes :
--  * In Debian, TABLE 'carnivore_emails' and 'carnivore_names' have
--    been added to avoid having to have to split it ourselves, thanks
--    to Lucas Nussbaum

-----------------

-- Things relating to carnivore (excerpt from Debian keyrings : maps
-- multiple identities of users)

-- Links carnivore_emails to corresponding sha1mailtos
-- Distinct emails and their SHA1s from carnivore
-- Copy into another table to save computing
-- Depends on function sha1mailto()
CREATE TABLE d2r_t_carnivore_mailtosha1 AS
SELECT id, email, sha1mailto(email) as mailtosha1
FROM carnivore_emails;


-- Joins all new carnivore tables into a single big view
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);

----------------

-- Things relating to bug submitters

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

----------------

-- Joins carnivore and bug sumitters

-- Depends on d2r_t_bugsubmitters_mailtosha1, d2r_t_carnivore_mailtosha1
-- 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;

-- Links bugs IDs with submitter's name and sha1mailto
-- Depends on d2r_v_bugsubm_carn
-- 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;


--- Ubuntu/Launchpad bug tasks

-- Create a table that links LP bugs to Debian bugs when the watch
-- contains an URL of a Debian bug
CREATE TABLE d2r_t_debian_bugs_tasks AS
SELECT bug, int4(substring(watch from 'http://bugs.debian.org/cgi-bin/bugreport\\.cgi\\?bug=(\\d+)')) as debian_bugid
FROM ubuntu_bugs_tasks WHERE
watch LIKE 'http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=%';