-- Copyright 2009 Canonical Ltd.  This software is licensed under the
-- GNU Affero General Public License version 3 (see the file LICENSE).


-- ** 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
--              initialize 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);
