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

Proposed by Muharem Hrnjadovic on 2009-10-26
Status: Merged
Merged at revision: not available
Proposed branch: lp:~al-maisan/launchpad/psds-399186
Merge into: lp:launchpad/db-devel
Diff against target: 223 lines
4 files modified
database/schema/comments.sql (+10/-1)
database/schema/patch-2207-06-0.sql (+122/-0)
database/schema/security.cfg (+5/-0)
database/schema/trusted.sql (+15/-0)
To merge this branch: bzr merge lp:~al-maisan/launchpad/psds-399186
Reviewer Review Type Date Requested Status
Jonathan Lange (community) db 2009-10-26 Approve on 2009-10-27
Stuart Bishop db 2009-10-26 Approve on 2009-10-27
Review via email: mp+13959@code.launchpad.net
To post a comment you must log in.
Muharem Hrnjadovic (al-maisan) wrote :
Download full text (4.2 KiB)

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 ...

Read more...

Stuart Bishop (stub) wrote :

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) wrote :

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

Jonathan Lange (jml) wrote :

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)

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'database/schema/comments.sql'
2--- database/schema/comments.sql 2009-10-07 12:54:03 +0000
3+++ database/schema/comments.sql 2009-10-27 11:23:12 +0000
4@@ -2239,11 +2239,20 @@
5
6 -- Packageset
7
8-COMMENT ON TABLE Packageset IS 'Package sets facilitate the grouping of packages for purposes like the control of upload permissions, et.';
9+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.';
10 COMMENT ON COLUMN Packageset.date_created IS 'Date and time of creation.';
11 COMMENT ON COLUMN Packageset.owner IS 'The Person or team who owns the package set';
12 COMMENT ON COLUMN Packageset.name IS 'The name for the package set on hand.';
13 COMMENT ON COLUMN Packageset.description IS 'The description for the package set on hand.';
14+COMMENT ON COLUMN Packageset.packagesetgroup IS 'The group this package set is affiliated with.';
15+COMMENT ON COLUMN Packageset.distroseries IS 'The distro series this package set belongs to.';
16+
17+-- PackagesetGroup
18+
19+COMMENT ON TABLE PackagesetGroup IS 'Package set groups keep track of equivalent package sets across distro series boundaries.';
20+COMMENT ON COLUMN Packageset.date_created IS 'Date and time of creation.';
21+COMMENT ON COLUMN Packageset.owner IS 'The Person or team who owns the package
22+set group.';
23
24 -- PackagesetSources
25
26
27=== added file 'database/schema/patch-2207-06-0.sql'
28--- database/schema/patch-2207-06-0.sql 1970-01-01 00:00:00 +0000
29+++ database/schema/patch-2207-06-0.sql 2009-10-27 11:23:12 +0000
30@@ -0,0 +1,122 @@
31+-- Copyright 2009 Canonical Ltd. This software is licensed under the
32+-- GNU Affero General Public License version 3 (see the file LICENSE).
33+
34+SET client_min_messages=ERROR;
35+
36+-- ** PART 1 ** Create the 'packagesetgroup' table and the
37+-- 'packageset.packagesetgroup' foreign key,
38+-- populate the 'packagesetgroup' table
39+
40+-- This table keeps track of package sets that are equivalent across
41+-- distro series boundaries.
42+CREATE SEQUENCE packagesetgroup_id_seq
43+ START WITH 1
44+ INCREMENT BY 1
45+ NO MAXVALUE
46+ NO MINVALUE
47+ CACHE 1;
48+CREATE TABLE packagesetgroup (
49+ id integer NOT NULL DEFAULT nextval('packagesetgroup_id_seq'),
50+ date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
51+ owner integer NOT NULL,
52+ -- Please note: the 'name' column is only here to ease the data migration
53+ -- and will be dropped at the end of this patch.
54+ name text NOT NULL
55+);
56+ALTER SEQUENCE packagesetgroup_id_seq OWNED BY packagesetgroup.id;
57+ALTER TABLE ONLY packagesetgroup
58+ ADD CONSTRAINT packagesetgroup_pkey PRIMARY KEY (id);
59+ALTER TABLE ONLY packagesetgroup
60+ ADD CONSTRAINT packagesetgroup__owner__fk
61+ FOREIGN KEY (owner) REFERENCES person(id);
62+
63+-- Package sets and their clones belong to the same package set group.
64+ALTER TABLE ONLY packageset ADD COLUMN packagesetgroup integer;
65+ALTER TABLE ONLY packageset
66+ ADD CONSTRAINT packageset__packagesetgroup__fk
67+ FOREIGN KEY (packagesetgroup) REFERENCES packagesetgroup(id);
68+
69+-- Create a group for each of the original (karmic koala) package sets.
70+INSERT INTO packagesetgroup(owner, name)
71+SELECT packageset.owner, packageset.name
72+FROM packageset WHERE NOT packageset.name LIKE('lucid-%');
73+
74+
75+-- ** PART 2 ** Associate the karmic koala package sets and their lucid lynx
76+-- clones with the appropriate package set groups
77+
78+-- Update the karmic koala package sets so they reference their groups.
79+UPDATE packageset SET packagesetgroup = packagesetgroup.id
80+FROM packagesetgroup WHERE packageset.name = packagesetgroup.name;
81+
82+-- Update the lucid lynx package set *clones* so they reference their groups
83+-- as well.
84+UPDATE packageset SET packagesetgroup = packagesetgroup.id
85+FROM packagesetgroup WHERE packageset.name = 'lucid-' || packagesetgroup.name;
86+
87+-- ** PART 3 ** Add the 'packageset.distroseries' foreign key and
88+-- initialise it for the existing package sets.
89+
90+-- A package set lives in a distro series context.
91+ALTER TABLE ONLY packageset ADD COLUMN distroseries integer;
92+
93+-- Define the foreign key constraint.
94+ALTER TABLE ONLY packageset
95+ ADD CONSTRAINT packageset__distroseries__fk
96+ FOREIGN KEY (distroseries) REFERENCES distroseries(id);
97+
98+-- First migrate the original package sets created for the karmic koala.
99+UPDATE packageset SET distroseries = distroseries.id FROM distroseries
100+WHERE distroseries.name = 'karmic' AND NOT packageset.name LIKE('lucid-%');
101+
102+-- Migrate the lucid lynx package sets next.
103+UPDATE packageset SET distroseries = distroseries.id FROM distroseries
104+WHERE distroseries.name = 'lucid' AND packageset.name LIKE('lucid-%');
105+
106+-- Make the 'distroseries' foreign key mandatory.
107+ALTER TABLE ONLY packageset ALTER COLUMN distroseries SET NOT NULL;
108+
109+-- The package set name is now only unique in conjunction with a distro series.
110+ALTER TABLE ONLY packageset
111+ DROP CONSTRAINT packageset_name_key;
112+ALTER TABLE ONLY packageset
113+ ADD CONSTRAINT packageset__name__distroseries__key UNIQUE (name, distroseries);
114+
115+-- ** PART 4 ** Strip off the 'lucid-' prefix of the lucid lynx
116+-- package set names
117+UPDATE packageset SET name = substring(name FROM length('lucid-')+1)
118+WHERE name LIKE('lucid-%');
119+
120+-- ** PART 5 ** Create package set groups for package sets that were added in
121+-- lucid lynx but do not exist in the karmic koala,
122+-- associate these package sets with their newly created groups
123+INSERT INTO packagesetgroup(owner, name)
124+SELECT packageset.owner, packageset.name
125+FROM packageset, distroseries WHERE
126+ packageset.packagesetgroup IS NULL
127+ AND packageset.distroseries = distroseries.id
128+ AND distroseries.name = 'lucid';
129+
130+UPDATE packageset SET packagesetgroup = packagesetgroup.id
131+FROM packagesetgroup, distroseries
132+WHERE
133+ packageset.packagesetgroup IS NULL
134+ AND packageset.distroseries = distroseries.id
135+ AND distroseries.name = 'lucid'
136+ AND packageset.name = packagesetgroup.name;
137+
138+-- ** PART 6 ** Make the 'packageset.packagesetgroup' foreign key mandatory
139+ALTER TABLE ONLY packageset ALTER COLUMN packagesetgroup SET NOT NULL;
140+
141+-- ** PART 7 ** Drop the 'packagesetgroup.name' column that was only added
142+-- for data migration purposes.
143+ALTER TABLE ONLY packagesetgroup DROP COLUMN name;
144+
145+-- Define indices on the newly added foreign keys.
146+CREATE INDEX packageset__packagesetgroup__idx
147+ ON packageset(packagesetgroup);
148+CREATE INDEX packageset__distroseries__idx
149+ ON packageset(distroseries);
150+CREATE INDEX packagesetgroup__owner__idx ON PackageSetGroup(owner);
151+
152+INSERT INTO LaunchpadDatabaseRevision VALUES (2207, 06, 0);
153
154=== modified file 'database/schema/security.cfg'
155--- database/schema/security.cfg 2009-10-15 23:26:39 +0000
156+++ database/schema/security.cfg 2009-10-27 11:23:12 +0000
157@@ -226,6 +226,7 @@
158 public.packagediff = SELECT, INSERT, UPDATE, DELETE
159 public.packagediff = SELECT, INSERT, UPDATE, DELETE
160 public.packageset = SELECT, INSERT, UPDATE, DELETE
161+public.packagesetgroup = SELECT, INSERT, UPDATE, DELETE
162 public.packagesetsources = SELECT, INSERT, UPDATE, DELETE
163 public.packagesetinclusion = SELECT, INSERT, UPDATE, DELETE
164 public.flatpackagesetinclusion = SELECT, INSERT, UPDATE, DELETE
165@@ -777,6 +778,7 @@
166 public.packagecopyrequest = SELECT, INSERT, UPDATE
167 public.packagediff = SELECT, INSERT, UPDATE
168 public.packageset = SELECT
169+public.packagesetgroup = SELECT
170 public.packagesetsources = SELECT, INSERT, UPDATE, DELETE
171 public.packagesetinclusion = SELECT, INSERT, UPDATE, DELETE
172 public.flatpackagesetinclusion = SELECT, INSERT, UPDATE, DELETE
173@@ -854,6 +856,7 @@
174 public.teammembership = SELECT
175 public.gpgkey = SELECT
176 public.packageset = SELECT
177+public.packagesetgroup = SELECT
178 public.packagesetsources = SELECT
179 public.packagesetinclusion = SELECT
180 public.flatpackagesetinclusion = SELECT
181@@ -1054,6 +1057,7 @@
182 public.archive = SELECT, INSERT, UPDATE
183 public.archivearch = SELECT, INSERT, UPDATE
184 public.packageset = SELECT
185+public.packagesetgroup = SELECT
186 public.packagesetsources = SELECT
187 public.packagesetinclusion = SELECT
188 public.flatpackagesetinclusion = SELECT
189@@ -1242,6 +1246,7 @@
190 public.personlanguage = SELECT
191 public.structuralsubscription = SELECT
192 public.packageset = SELECT
193+public.packagesetgroup = SELECT
194 public.packagesetsources = SELECT
195 public.packagesetinclusion = SELECT
196 public.flatpackagesetinclusion = SELECT
197
198=== modified file 'database/schema/trusted.sql'
199--- database/schema/trusted.sql 2009-08-19 15:35:13 +0000
200+++ database/schema/trusted.sql 2009-10-27 11:23:12 +0000
201@@ -1029,7 +1029,22 @@
202 DECLARE
203 parent_name text;
204 child_name text;
205+ parent_distroseries text;
206+ child_distroseries text;
207 BEGIN
208+ -- Make sure that the package sets being associated here belong
209+ -- to the same distro series.
210+ IF (SELECT parent.distroseries != child.distroseries
211+ FROM packageset parent, packageset child
212+ WHERE parent.id = NEW.parent AND child.id = NEW.child)
213+ THEN
214+ SELECT name INTO parent_name FROM packageset WHERE id = NEW.parent;
215+ SELECT name INTO child_name FROM packageset WHERE id = NEW.child;
216+ SELECT ds.name INTO parent_distroseries FROM packageset ps, distroseries ds WHERE ps.id = NEW.parent AND ps.distroseries = ds.id;
217+ SELECT ds.name INTO child_distroseries FROM packageset ps, distroseries ds WHERE ps.id = NEW.child AND ps.distroseries = ds.id;
218+ 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;
219+ END IF;
220+
221 IF EXISTS(
222 SELECT * FROM flatpackagesetinclusion
223 WHERE parent = NEW.child AND child = NEW.parent LIMIT 1)

Subscribers

People subscribed via source and target branches

to status/vote changes: