alter binary version view to include based on versions

[debbugs.git] / sql / debbugs_schema.sql

diff --git a/sql/debbugs_schema.sql b/sql/debbugs_schema.sql
index 5d179045a41e47b6305f38b2759c2ff5666a1427..cf1e950919b4de6f19521a2d731c733ea84a817d 100644 (file)
--- a/sql/debbugs_schema.sql
+++ b/sql/debbugs_schema.sql
@@ -22,11 +22,11 @@ DROP TABLE bin_associations CASCADE;
 DROP TABLE src_associations CASCADE;
 DROP TABLE maintainer CASCADE;
 DROP TABLE bug_message CASCADE;
-DROP TABLE message_corespondent CASCADE;
-DROP TABLE corespondent CASCADE;
+DROP TABLE message_correspondent CASCADE;
+DROP TABLE correspondent CASCADE;
 DROP TABLE message_refs CASCADE;
 DROP TABLE message CASCADE;
-DROP TYPE message_corespondent_type CASCADE;
+DROP TYPE message_correspondent_type CASCADE;
 DROP TABLE table_comments CASCADE;
 DROP TABLE column_comments CASCADE;
 
@@ -268,11 +268,14 @@ CREATE VIEW bug_package (bug_id,pkg_id,pkg_type,package) AS
               SELECT s.bug_id,s.src_pkg_id,'source',sp.pkg FROM bug_srcpackage s JOIN src_pkg sp ON sp.id=s.src_pkg_id;
 
 CREATE VIEW binary_versions (src_pkg, src_ver, bin_pkg, arch, bin_ver) AS
-       SELECT sp.pkg AS src_pkg, sv.ver AS src_ver, bp.pkg AS bin_pkg, a.arch AS arch, b.ver AS bin_ver
+       SELECT sp.pkg AS src_pkg, sv.ver AS src_ver, bp.pkg AS bin_pkg, a.arch AS arch, b.ver AS bin_ver,
+       svb.ver AS src_ver_based_on, spb.pkg AS src_pkg_based_on
        FROM bin_ver b JOIN arch a ON b.arch_id = a.id
                              JOIN bin_pkg bp ON b.bin_pkg_id  = bp.id
                       JOIN src_ver sv ON b.src_ver_id  = sv.id
-                      JOIN src_pkg sp ON sv.src_pkg_id = sp.id;
+                      JOIN src_pkg sp ON sv.src_pkg_id = sp.id
+                      LEFT OUTER JOIN src_ver svb ON sv.based_on = svb.id
+                      LEFT OUTER JOIN src_pkg spb ON spb.id = svb.src_pkg_id;
 
 CREATE TABLE suite (
        id SERIAL PRIMARY KEY,
@@ -370,7 +373,7 @@ INSERT INTO table_comments VALUES ('correspondent','Individual who has correspon
 INSERT INTO column_comments VALUES ('correspondent','id','Correspondent ID');
 INSERT INTO column_comments VALUES ('correspondent','addr','Correspondent address');
 
-CREATE TYPE message_corespondent_type AS ENUM ('to','from','envfrom','cc');
+CREATE TYPE message_correspondent_type AS ENUM ('to','from','envfrom','cc');
 
 CREATE TABLE message_correspondent (
        message INT NOT NULL REFERENCES message ON DELETE CASCADE ON UPDATE CASCADE,
@@ -391,7 +394,7 @@ CREATE TABLE bug_message (
        message INT NOT NULL REFERENCES message ON DELETE CASCADE ON UPDATE CASCADE,
        message_number INT NOT NULL,
        bug_log_offset INT,
-       offset_valid TIMESTAMP WITH TIMEZONE
+       offset_valid TIMESTAMP WITH TIME ZONE
 );
 INSERT INTO table_comments VALUES ('bug_mesage','Mapping between a bug and a message');
 INSERT INTO column_comments VALUES ('bug_message','bug','Bug id (matches bug)');