Merge lp:~al-maisan/launchpad/psds-399186 into lp:launchpad/db-devel

[Muharem Hrnjadovic]

[Muharem Hrnjadovic (al-maisan)]

Hello there!

The branch at hand introduces the association between package sets and
distro series as well as a new table ('packagesetgroup') that
facilitates the tracking of equivalent package sets across distro series
boundaries.

For more detail on these changes and why they are needed, please see:

  * the original "package set and distro series" meeting minutes:
    http://pastebin.ubuntu.com/302054/
  * Julian's update: http://pastebin.ubuntu.com/302058/

Please note also that the only change introduced to the underlying
trigger procedures was to make sure that when a package set P includes
another package set Q then both P and Q must belong to the same distro
series.

I have tested the schema patch at hand, particularly the data migration
part and the additional trigger procedure check using a SQL script
(http://pastebin.ubuntu.com/302082 = some play data + plus the schema
patch + plus the stricter trigger procedure) against the unpatched
database as follows:

{{{
$ psql -d launchpad_dev -f /tmp/patch.sql
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
CREATE SEQUENCE
CREATE TABLE
ALTER SEQUENCE
psql:/tmp/patch.sql:74: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "packagesetgroup_pkey" for table "packagesetgroup"
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
INSERT 0 5
UPDATE 5
UPDATE 3
ALTER TABLE
ALTER TABLE
UPDATE 5
UPDATE 4
ALTER TABLE
ALTER TABLE
psql:/tmp/patch.sql:129: NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "packageset__name__distroseries__key" for table "packageset"
ALTER TABLE
UPDATE 4
INSERT 0 1
UPDATE 1
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE FUNCTION

$ psql -d launchpad_dev
Welcome to psql 8.3.8, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

-----------------------------------------------------------------------
--
-- Please note below how the
-- * appropiate package set groups have been created
-- * equivalent package sets have been associated
--
-----------------------------------------------------------------------
launchpad_dev=# SELECT ps.packagesetgroup, ps.id AS packageset, ps.name AS ps_name, ds.name AS ds_name FROM packageset ps, distroseries ds WHERE ps.distroseries = ds.id ORDER BY ps.packagesetgroup, ps.id;
 packagesetgroup | packageset | ps_name | ds_name
-----------------+------------+-------------+---------
               1 | 1 | umbrella | karmic
               2 | 2 | deprecated | karmic
               3 | 3 | mozilla | karmic
               3 | 6 | mozilla | lucid
               4 | 4 | firefox | karmic
               4 | 7 | firefox | lucid
               5 | 5 | thunderbird | karmic
               5 | 8 | thunderbird | lucid
               6 | 9 | only | lucid
(9 rows)

-----------------------------------------------------------------------
--
-- Package sets belonging ...

[Stuart Bishop (stub)]

We need an index on packagesetgroup.owner:

-- Needed for people merge.
CREATE INDEX packagesetgroup__owner__idx ON PackageSetGroup(owner);

Otherwise looks fine (if a little unreadable - this looks like the output of pg_dump and doesn't use shortcuts like 'SERIAL PRIMARY KEY' or defining constraints inline with the table definition).

review: Approve (db)

[Stuart Bishop (stub)]

patch number is patch-2207-07-0.sql

[Jonathan Lange (jml)]

Hello Muharem,

We talked about this on the phone and agreed that the patch is what we agreed on during the meeting, so there's not much to add beyond that.

Thanks,
jml

review: Approve (db)

=== modified file 'database/schema/comments.sql'
--- database/schema/comments.sql	2009-10-07 12:54:03 +0000
+++ database/schema/comments.sql	2009-10-27 11:23:12 +0000
@@ -2239,11 +2239,20 @@
 
 -- Packageset
 
-COMMENT ON TABLE Packageset IS 'Package sets facilitate the grouping of packages for purposes like the control of upload permissions, et.';
+COMMENT ON TABLE Packageset IS 'Package sets facilitate the grouping of packages (in a given distro series) for purposes like the control of upload permissions, etc.';
 COMMENT ON COLUMN Packageset.date_created IS 'Date and time of creation.';
 COMMENT ON COLUMN Packageset.owner IS 'The Person or team who owns the package set';
 COMMENT ON COLUMN Packageset.name IS 'The name for the package set on hand.';
 COMMENT ON COLUMN Packageset.description IS 'The description for the package set on hand.';
+COMMENT ON COLUMN Packageset.packagesetgroup IS 'The group this package set is affiliated with.';
+COMMENT ON COLUMN Packageset.distroseries IS 'The distro series this package set belongs to.';
+
+-- PackagesetGroup
+
+COMMENT ON TABLE PackagesetGroup IS 'Package set groups keep track of equivalent package sets across distro series boundaries.';
+COMMENT ON COLUMN Packageset.date_created IS 'Date and time of creation.';
+COMMENT ON COLUMN Packageset.owner IS 'The Person or team who owns the package
+set group.';
 
 -- PackagesetSources
 

=== added file 'database/schema/patch-2207-06-0.sql'
--- database/schema/patch-2207-06-0.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2207-06-0.sql	2009-10-27 11:23:12 +0000
@@ -0,0 +1,122 @@
+-- Copyright 2009 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+-- ** PART 1 ** Create the 'packagesetgroup' table and the
+--              'packageset.packagesetgroup' foreign key,
+--              populate the 'packagesetgroup' table
+
+-- This table keeps track of package sets that are equivalent across
+-- distro series boundaries.
+CREATE SEQUENCE packagesetgroup_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+CREATE TABLE packagesetgroup (
+    id integer NOT NULL DEFAULT nextval('packagesetgroup_id_seq'),
+    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
+    owner integer NOT NULL,
+    -- Please note: the 'name' column is only here to ease the data migration
+    -- and will be dropped at the end of this patch.
+    name text NOT NULL
+);
+ALTER SEQUENCE packagesetgroup_id_seq OWNED BY packagesetgroup.id;
+ALTER TABLE ONLY packagesetgroup
+    ADD CONSTRAINT packagesetgroup_pkey PRIMARY KEY (id);
+ALTER TABLE ONLY packagesetgroup
+    ADD CONSTRAINT packagesetgroup__owner__fk
+    FOREIGN KEY (owner) REFERENCES person(id);
+
+-- Package sets and their clones belong to the same package set group.
+ALTER TABLE ONLY packageset ADD COLUMN packagesetgroup integer;
+ALTER TABLE ONLY packageset
+  ADD CONSTRAINT packageset__packagesetgroup__fk
+  FOREIGN KEY (packagesetgroup) REFERENCES packagesetgroup(id);
+
+-- Create a group for each of the original (karmic koala) package sets.
+INSERT INTO packagesetgroup(owner, name)
+SELECT packageset.owner, packageset.name
+FROM packageset WHERE NOT packageset.name LIKE('lucid-%');
+
+
+-- ** PART 2 ** Associate the karmic koala package sets and their lucid lynx
+--              clones with the appropriate package set groups
+
+-- Update the karmic koala package sets so they reference their groups.
+UPDATE packageset SET packagesetgroup = packagesetgroup.id
+FROM packagesetgroup WHERE packageset.name = packagesetgroup.name;
+
+-- Update the lucid lynx package set *clones* so they reference their groups
+-- as well.
+UPDATE packageset SET packagesetgroup = packagesetgroup.id
+FROM packagesetgroup WHERE packageset.name = 'lucid-' || packagesetgroup.name;
+
+-- ** PART 3 ** Add the 'packageset.distroseries' foreign key and
+--              initialise it for the existing package sets.
+
+-- A package set lives in a distro series context.
+ALTER TABLE ONLY packageset ADD COLUMN distroseries integer;
+
+-- Define the foreign key constraint.
+ALTER TABLE ONLY packageset
+  ADD CONSTRAINT packageset__distroseries__fk
+  FOREIGN KEY (distroseries) REFERENCES distroseries(id);
+
+-- First migrate the original package sets created for the karmic koala.
+UPDATE packageset SET distroseries = distroseries.id FROM distroseries
+WHERE distroseries.name = 'karmic' AND NOT packageset.name LIKE('lucid-%');
+
+-- Migrate the lucid lynx package sets next.
+UPDATE packageset SET distroseries = distroseries.id FROM distroseries
+WHERE distroseries.name = 'lucid' AND packageset.name LIKE('lucid-%');
+
+-- Make the 'distroseries' foreign key mandatory.
+ALTER TABLE ONLY packageset ALTER COLUMN distroseries SET NOT NULL;
+
+-- The package set name is now only unique in conjunction with a distro series.
+ALTER TABLE ONLY packageset
+    DROP CONSTRAINT packageset_name_key;
+ALTER TABLE ONLY packageset
+    ADD CONSTRAINT packageset__name__distroseries__key UNIQUE (name, distroseries);
+
+-- ** PART 4 ** Strip off the 'lucid-' prefix of the lucid lynx
+--              package set names
+UPDATE packageset SET name = substring(name FROM length('lucid-')+1)
+WHERE name LIKE('lucid-%');
+
+-- ** PART 5 ** Create package set groups for package sets that were added in
+--              lucid lynx but do not exist in the karmic koala,
+--              associate these package sets with their newly created groups
+INSERT INTO packagesetgroup(owner, name)
+SELECT packageset.owner, packageset.name
+FROM packageset, distroseries WHERE
+    packageset.packagesetgroup IS NULL
+    AND packageset.distroseries = distroseries.id
+    AND distroseries.name = 'lucid';
+
+UPDATE packageset SET packagesetgroup = packagesetgroup.id
+FROM packagesetgroup, distroseries
+WHERE
+    packageset.packagesetgroup IS NULL
+    AND packageset.distroseries = distroseries.id
+    AND distroseries.name = 'lucid'
+    AND packageset.name = packagesetgroup.name;
+
+-- ** PART 6 ** Make the 'packageset.packagesetgroup' foreign key mandatory
+ALTER TABLE ONLY packageset ALTER COLUMN packagesetgroup SET NOT NULL;
+
+-- ** PART 7 ** Drop the 'packagesetgroup.name' column that was only added
+--              for data migration purposes.
+ALTER TABLE ONLY packagesetgroup DROP COLUMN name;
+
+-- Define indices on the newly added foreign keys.
+CREATE INDEX packageset__packagesetgroup__idx
+    ON packageset(packagesetgroup);
+CREATE INDEX packageset__distroseries__idx
+    ON packageset(distroseries);
+CREATE INDEX packagesetgroup__owner__idx ON PackageSetGroup(owner);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2207, 06, 0);

=== modified file 'database/schema/security.cfg'
--- database/schema/security.cfg	2009-10-15 23:26:39 +0000
+++ database/schema/security.cfg	2009-10-27 11:23:12 +0000
@@ -226,6 +226,7 @@
 public.packagediff                      = SELECT, INSERT, UPDATE, DELETE
 public.packagediff                      = SELECT, INSERT, UPDATE, DELETE
 public.packageset                       = SELECT, INSERT, UPDATE, DELETE
+public.packagesetgroup                  = SELECT, INSERT, UPDATE, DELETE
 public.packagesetsources                = SELECT, INSERT, UPDATE, DELETE
 public.packagesetinclusion              = SELECT, INSERT, UPDATE, DELETE
 public.flatpackagesetinclusion          = SELECT, INSERT, UPDATE, DELETE
@@ -777,6 +778,7 @@
 public.packagecopyrequest                       = SELECT, INSERT, UPDATE
 public.packagediff                              = SELECT, INSERT, UPDATE
 public.packageset                               = SELECT
+public.packagesetgroup                          = SELECT
 public.packagesetsources                        = SELECT, INSERT, UPDATE, DELETE
 public.packagesetinclusion                      = SELECT, INSERT, UPDATE, DELETE
 public.flatpackagesetinclusion                  = SELECT, INSERT, UPDATE, DELETE
@@ -854,6 +856,7 @@
 public.teammembership                           = SELECT
 public.gpgkey                                   = SELECT
 public.packageset                               = SELECT
+public.packagesetgroup                          = SELECT
 public.packagesetsources                        = SELECT
 public.packagesetinclusion                      = SELECT
 public.flatpackagesetinclusion                  = SELECT
@@ -1054,6 +1057,7 @@
 public.archive                          = SELECT, INSERT, UPDATE
 public.archivearch                      = SELECT, INSERT, UPDATE
 public.packageset                       = SELECT
+public.packagesetgroup                  = SELECT
 public.packagesetsources                = SELECT
 public.packagesetinclusion              = SELECT
 public.flatpackagesetinclusion          = SELECT
@@ -1242,6 +1246,7 @@
 public.personlanguage                   = SELECT
 public.structuralsubscription           = SELECT
 public.packageset                       = SELECT
+public.packagesetgroup                  = SELECT
 public.packagesetsources                = SELECT
 public.packagesetinclusion              = SELECT
 public.flatpackagesetinclusion          = SELECT

=== modified file 'database/schema/trusted.sql'
--- database/schema/trusted.sql	2009-08-19 15:35:13 +0000
+++ database/schema/trusted.sql	2009-10-27 11:23:12 +0000
@@ -1029,7 +1029,22 @@
     DECLARE
         parent_name text;
         child_name text;
+        parent_distroseries text;
+        child_distroseries text;
     BEGIN
+        -- Make sure that the package sets being associated here belong
+        -- to the same distro series.
+        IF (SELECT parent.distroseries != child.distroseries
+            FROM packageset parent, packageset child
+            WHERE parent.id = NEW.parent AND child.id = NEW.child)
+        THEN
+            SELECT name INTO parent_name FROM packageset WHERE id = NEW.parent;
+            SELECT name INTO child_name FROM packageset WHERE id = NEW.child;
+            SELECT ds.name INTO parent_distroseries FROM packageset ps, distroseries ds WHERE ps.id = NEW.parent AND ps.distroseries = ds.id;
+            SELECT ds.name INTO child_distroseries FROM packageset ps, distroseries ds WHERE ps.id = NEW.child AND ps.distroseries = ds.id;
+            RAISE EXCEPTION 'Package sets % and % belong to different distro series (to % and % respectively) and thus cannot be associated.', child_name, parent_name, child_distroseries, parent_distroseries;
+        END IF;
+
         IF EXISTS(
             SELECT * FROM flatpackagesetinclusion
             WHERE parent = NEW.child AND child = NEW.parent LIMIT 1)