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
=== 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 @@
22392239
2240-- Packageset2240-- Packageset
22412241
2242COMMENT ON TABLE Packageset IS 'Package sets facilitate the grouping of packages for purposes like the control of upload permissions, et.';2242COMMENT 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.';
2243COMMENT ON COLUMN Packageset.date_created IS 'Date and time of creation.';2243COMMENT ON COLUMN Packageset.date_created IS 'Date and time of creation.';
2244COMMENT ON COLUMN Packageset.owner IS 'The Person or team who owns the package set';2244COMMENT ON COLUMN Packageset.owner IS 'The Person or team who owns the package set';
2245COMMENT ON COLUMN Packageset.name IS 'The name for the package set on hand.';2245COMMENT ON COLUMN Packageset.name IS 'The name for the package set on hand.';
2246COMMENT ON COLUMN Packageset.description IS 'The description for the package set on hand.';2246COMMENT ON COLUMN Packageset.description IS 'The description for the package set on hand.';
2247COMMENT ON COLUMN Packageset.packagesetgroup IS 'The group this package set is affiliated with.';
2248COMMENT ON COLUMN Packageset.distroseries IS 'The distro series this package set belongs to.';
2249
2250-- PackagesetGroup
2251
2252COMMENT ON TABLE PackagesetGroup IS 'Package set groups keep track of equivalent package sets across distro series boundaries.';
2253COMMENT ON COLUMN Packageset.date_created IS 'Date and time of creation.';
2254COMMENT ON COLUMN Packageset.owner IS 'The Person or team who owns the package
2255set group.';
22472256
2248-- PackagesetSources2257-- PackagesetSources
22492258
22502259
=== 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 @@
1-- Copyright 2009 Canonical Ltd. This software is licensed under the
2-- GNU Affero General Public License version 3 (see the file LICENSE).
3
4SET client_min_messages=ERROR;
5
6-- ** PART 1 ** Create the 'packagesetgroup' table and the
7-- 'packageset.packagesetgroup' foreign key,
8-- populate the 'packagesetgroup' table
9
10-- This table keeps track of package sets that are equivalent across
11-- distro series boundaries.
12CREATE SEQUENCE packagesetgroup_id_seq
13 START WITH 1
14 INCREMENT BY 1
15 NO MAXVALUE
16 NO MINVALUE
17 CACHE 1;
18CREATE TABLE packagesetgroup (
19 id integer NOT NULL DEFAULT nextval('packagesetgroup_id_seq'),
20 date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
21 owner integer NOT NULL,
22 -- Please note: the 'name' column is only here to ease the data migration
23 -- and will be dropped at the end of this patch.
24 name text NOT NULL
25);
26ALTER SEQUENCE packagesetgroup_id_seq OWNED BY packagesetgroup.id;
27ALTER TABLE ONLY packagesetgroup
28 ADD CONSTRAINT packagesetgroup_pkey PRIMARY KEY (id);
29ALTER TABLE ONLY packagesetgroup
30 ADD CONSTRAINT packagesetgroup__owner__fk
31 FOREIGN KEY (owner) REFERENCES person(id);
32
33-- Package sets and their clones belong to the same package set group.
34ALTER TABLE ONLY packageset ADD COLUMN packagesetgroup integer;
35ALTER TABLE ONLY packageset
36 ADD CONSTRAINT packageset__packagesetgroup__fk
37 FOREIGN KEY (packagesetgroup) REFERENCES packagesetgroup(id);
38
39-- Create a group for each of the original (karmic koala) package sets.
40INSERT INTO packagesetgroup(owner, name)
41SELECT packageset.owner, packageset.name
42FROM packageset WHERE NOT packageset.name LIKE('lucid-%');
43
44
45-- ** PART 2 ** Associate the karmic koala package sets and their lucid lynx
46-- clones with the appropriate package set groups
47
48-- Update the karmic koala package sets so they reference their groups.
49UPDATE packageset SET packagesetgroup = packagesetgroup.id
50FROM packagesetgroup WHERE packageset.name = packagesetgroup.name;
51
52-- Update the lucid lynx package set *clones* so they reference their groups
53-- as well.
54UPDATE packageset SET packagesetgroup = packagesetgroup.id
55FROM packagesetgroup WHERE packageset.name = 'lucid-' || packagesetgroup.name;
56
57-- ** PART 3 ** Add the 'packageset.distroseries' foreign key and
58-- initialise it for the existing package sets.
59
60-- A package set lives in a distro series context.
61ALTER TABLE ONLY packageset ADD COLUMN distroseries integer;
62
63-- Define the foreign key constraint.
64ALTER TABLE ONLY packageset
65 ADD CONSTRAINT packageset__distroseries__fk
66 FOREIGN KEY (distroseries) REFERENCES distroseries(id);
67
68-- First migrate the original package sets created for the karmic koala.
69UPDATE packageset SET distroseries = distroseries.id FROM distroseries
70WHERE distroseries.name = 'karmic' AND NOT packageset.name LIKE('lucid-%');
71
72-- Migrate the lucid lynx package sets next.
73UPDATE packageset SET distroseries = distroseries.id FROM distroseries
74WHERE distroseries.name = 'lucid' AND packageset.name LIKE('lucid-%');
75
76-- Make the 'distroseries' foreign key mandatory.
77ALTER TABLE ONLY packageset ALTER COLUMN distroseries SET NOT NULL;
78
79-- The package set name is now only unique in conjunction with a distro series.
80ALTER TABLE ONLY packageset
81 DROP CONSTRAINT packageset_name_key;
82ALTER TABLE ONLY packageset
83 ADD CONSTRAINT packageset__name__distroseries__key UNIQUE (name, distroseries);
84
85-- ** PART 4 ** Strip off the 'lucid-' prefix of the lucid lynx
86-- package set names
87UPDATE packageset SET name = substring(name FROM length('lucid-')+1)
88WHERE name LIKE('lucid-%');
89
90-- ** PART 5 ** Create package set groups for package sets that were added in
91-- lucid lynx but do not exist in the karmic koala,
92-- associate these package sets with their newly created groups
93INSERT INTO packagesetgroup(owner, name)
94SELECT packageset.owner, packageset.name
95FROM packageset, distroseries WHERE
96 packageset.packagesetgroup IS NULL
97 AND packageset.distroseries = distroseries.id
98 AND distroseries.name = 'lucid';
99
100UPDATE packageset SET packagesetgroup = packagesetgroup.id
101FROM packagesetgroup, distroseries
102WHERE
103 packageset.packagesetgroup IS NULL
104 AND packageset.distroseries = distroseries.id
105 AND distroseries.name = 'lucid'
106 AND packageset.name = packagesetgroup.name;
107
108-- ** PART 6 ** Make the 'packageset.packagesetgroup' foreign key mandatory
109ALTER TABLE ONLY packageset ALTER COLUMN packagesetgroup SET NOT NULL;
110
111-- ** PART 7 ** Drop the 'packagesetgroup.name' column that was only added
112-- for data migration purposes.
113ALTER TABLE ONLY packagesetgroup DROP COLUMN name;
114
115-- Define indices on the newly added foreign keys.
116CREATE INDEX packageset__packagesetgroup__idx
117 ON packageset(packagesetgroup);
118CREATE INDEX packageset__distroseries__idx
119 ON packageset(distroseries);
120CREATE INDEX packagesetgroup__owner__idx ON PackageSetGroup(owner);
121
122INSERT INTO LaunchpadDatabaseRevision VALUES (2207, 06, 0);
0123
=== 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 @@
226public.packagediff = SELECT, INSERT, UPDATE, DELETE226public.packagediff = SELECT, INSERT, UPDATE, DELETE
227public.packagediff = SELECT, INSERT, UPDATE, DELETE227public.packagediff = SELECT, INSERT, UPDATE, DELETE
228public.packageset = SELECT, INSERT, UPDATE, DELETE228public.packageset = SELECT, INSERT, UPDATE, DELETE
229public.packagesetgroup = SELECT, INSERT, UPDATE, DELETE
229public.packagesetsources = SELECT, INSERT, UPDATE, DELETE230public.packagesetsources = SELECT, INSERT, UPDATE, DELETE
230public.packagesetinclusion = SELECT, INSERT, UPDATE, DELETE231public.packagesetinclusion = SELECT, INSERT, UPDATE, DELETE
231public.flatpackagesetinclusion = SELECT, INSERT, UPDATE, DELETE232public.flatpackagesetinclusion = SELECT, INSERT, UPDATE, DELETE
@@ -777,6 +778,7 @@
777public.packagecopyrequest = SELECT, INSERT, UPDATE778public.packagecopyrequest = SELECT, INSERT, UPDATE
778public.packagediff = SELECT, INSERT, UPDATE779public.packagediff = SELECT, INSERT, UPDATE
779public.packageset = SELECT780public.packageset = SELECT
781public.packagesetgroup = SELECT
780public.packagesetsources = SELECT, INSERT, UPDATE, DELETE782public.packagesetsources = SELECT, INSERT, UPDATE, DELETE
781public.packagesetinclusion = SELECT, INSERT, UPDATE, DELETE783public.packagesetinclusion = SELECT, INSERT, UPDATE, DELETE
782public.flatpackagesetinclusion = SELECT, INSERT, UPDATE, DELETE784public.flatpackagesetinclusion = SELECT, INSERT, UPDATE, DELETE
@@ -854,6 +856,7 @@
854public.teammembership = SELECT856public.teammembership = SELECT
855public.gpgkey = SELECT857public.gpgkey = SELECT
856public.packageset = SELECT858public.packageset = SELECT
859public.packagesetgroup = SELECT
857public.packagesetsources = SELECT860public.packagesetsources = SELECT
858public.packagesetinclusion = SELECT861public.packagesetinclusion = SELECT
859public.flatpackagesetinclusion = SELECT862public.flatpackagesetinclusion = SELECT
@@ -1054,6 +1057,7 @@
1054public.archive = SELECT, INSERT, UPDATE1057public.archive = SELECT, INSERT, UPDATE
1055public.archivearch = SELECT, INSERT, UPDATE1058public.archivearch = SELECT, INSERT, UPDATE
1056public.packageset = SELECT1059public.packageset = SELECT
1060public.packagesetgroup = SELECT
1057public.packagesetsources = SELECT1061public.packagesetsources = SELECT
1058public.packagesetinclusion = SELECT1062public.packagesetinclusion = SELECT
1059public.flatpackagesetinclusion = SELECT1063public.flatpackagesetinclusion = SELECT
@@ -1242,6 +1246,7 @@
1242public.personlanguage = SELECT1246public.personlanguage = SELECT
1243public.structuralsubscription = SELECT1247public.structuralsubscription = SELECT
1244public.packageset = SELECT1248public.packageset = SELECT
1249public.packagesetgroup = SELECT
1245public.packagesetsources = SELECT1250public.packagesetsources = SELECT
1246public.packagesetinclusion = SELECT1251public.packagesetinclusion = SELECT
1247public.flatpackagesetinclusion = SELECT1252public.flatpackagesetinclusion = SELECT
12481253
=== 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 @@
1029 DECLARE1029 DECLARE
1030 parent_name text;1030 parent_name text;
1031 child_name text;1031 child_name text;
1032 parent_distroseries text;
1033 child_distroseries text;
1032 BEGIN1034 BEGIN
1035 -- Make sure that the package sets being associated here belong
1036 -- to the same distro series.
1037 IF (SELECT parent.distroseries != child.distroseries
1038 FROM packageset parent, packageset child
1039 WHERE parent.id = NEW.parent AND child.id = NEW.child)
1040 THEN
1041 SELECT name INTO parent_name FROM packageset WHERE id = NEW.parent;
1042 SELECT name INTO child_name FROM packageset WHERE id = NEW.child;
1043 SELECT ds.name INTO parent_distroseries FROM packageset ps, distroseries ds WHERE ps.id = NEW.parent AND ps.distroseries = ds.id;
1044 SELECT ds.name INTO child_distroseries FROM packageset ps, distroseries ds WHERE ps.id = NEW.child AND ps.distroseries = ds.id;
1045 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;
1046 END IF;
1047
1033 IF EXISTS(1048 IF EXISTS(
1034 SELECT * FROM flatpackagesetinclusion1049 SELECT * FROM flatpackagesetinclusion
1035 WHERE parent = NEW.child AND child = NEW.parent LIMIT 1)1050 WHERE parent = NEW.child AND child = NEW.parent LIMIT 1)

Subscribers

People subscribed via source and target branches

to status/vote changes: