1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
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);
|