Merge lp:~cjwatson/launchpad/db-git into lp:launchpad/db-devel

Proposed by Colin Watson on 2015-01-27
Status: Merged
Approved by: Colin Watson on 2015-02-10
Approved revision: no longer in the source branch.
Merged at revision: 12905
Proposed branch: lp:~cjwatson/launchpad/db-git
Merge into: lp:launchpad/db-devel
Diff against target: 340 lines (+196/-8)
4 files modified
database/sampledata/current-dev.sql (+24/-4)
database/sampledata/current.sql (+24/-4)
database/schema/patch-2209-61-0.sql (+135/-0)
database/schema/security.cfg (+13/-0)
To merge this branch: bzr merge lp:~cjwatson/launchpad/db-git
Reviewer Review Type Date Requested Status
William Grant db 2015-01-27 Approve on 2015-02-10
Stuart Bishop db 2015-01-27 Pending
Review via email: mp+247752@code.launchpad.net

Commit message

Add GitRepository and GitRef tables.

Description of the change

Here's a first pass at the core database model for Git repository hosting. The main complexity is in information type handling, but this is mostly cargo-culted from other privacy-supporting tables. I've managed to use this to do basic repository creation via a modified webapp, although little else as yet.

To post a comment you must log in.
William Grant (wgrant) :
review: Needs Fixing (db)
Colin Watson (cjwatson) :
Colin Watson (cjwatson) :
Colin Watson (cjwatson) wrote :

All these comments should be addressed now and I have at least target defaults working correctly in my local code megabranch, so please re-review.

William Grant (wgrant) :
review: Approve (db)

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'database/sampledata/current-dev.sql'
2--- database/sampledata/current-dev.sql 2014-12-07 22:36:30 +0000
3+++ database/sampledata/current-dev.sql 2015-02-10 00:48:10 +0000
4@@ -1,4 +1,4 @@
5--- Copyright 2010-2014 Canonical Ltd. This software is licensed under the
6+-- Copyright 2010-2015 Canonical Ltd. This software is licensed under the
7 -- GNU Affero General Public License version 3 (see the file LICENSE).
8 -- Created using pg_dump (PostgreSQL) 9.3.5
9
10@@ -1272,6 +1272,13 @@
11 ALTER TABLE distribution ENABLE TRIGGER ALL;
12
13
14+ALTER TABLE gitrepository DISABLE TRIGGER ALL;
15+
16+
17+
18+ALTER TABLE gitrepository ENABLE TRIGGER ALL;
19+
20+
21 ALTER TABLE productseries DISABLE TRIGGER ALL;
22
23 INSERT INTO productseries (id, product, name, summary, releasefileglob, releaseverstyle, datecreated, driver, owner, status, translations_autoimport_mode, branch, translations_branch) VALUES (1, 4, 'trunk', 'The "trunk" series represents the primary line of development rather than a stable release branch. This is sometimes also called MAIN or HEAD.', NULL, NULL, '2005-06-06 08:59:51.895136', NULL, 12, 2, 1, NULL, NULL);
24@@ -1364,9 +1371,9 @@
25
26 ALTER TABLE accessartifact DISABLE TRIGGER ALL;
27
28-INSERT INTO accessartifact (id, bug, branch, specification) VALUES (1, 14, NULL, NULL);
29-INSERT INTO accessartifact (id, bug, branch, specification) VALUES (2, NULL, 29, NULL);
30-INSERT INTO accessartifact (id, bug, branch, specification) VALUES (3, NULL, 30, NULL);
31+INSERT INTO accessartifact (id, bug, branch, specification, gitrepository) VALUES (1, 14, NULL, NULL, NULL);
32+INSERT INTO accessartifact (id, bug, branch, specification, gitrepository) VALUES (2, NULL, 29, NULL, NULL);
33+INSERT INTO accessartifact (id, bug, branch, specification, gitrepository) VALUES (3, NULL, 30, NULL, NULL);
34
35
36 ALTER TABLE accessartifact ENABLE TRIGGER ALL;
37@@ -3850,6 +3857,19 @@
38 ALTER TABLE garbojobstate ENABLE TRIGGER ALL;
39
40
41+ALTER TABLE gitref DISABLE TRIGGER ALL;
42+
43+
44+
45+ALTER TABLE gitref ENABLE TRIGGER ALL;
46+
47+
48+
49+
50+
51+
52+
53+
54
55
56
57
58=== modified file 'database/sampledata/current.sql'
59--- database/sampledata/current.sql 2014-12-07 22:36:30 +0000
60+++ database/sampledata/current.sql 2015-02-10 00:48:10 +0000
61@@ -1,4 +1,4 @@
62--- Copyright 2010-2014 Canonical Ltd. This software is licensed under the
63+-- Copyright 2010-2015 Canonical Ltd. This software is licensed under the
64 -- GNU Affero General Public License version 3 (see the file LICENSE).
65 -- Created using pg_dump (PostgreSQL) 9.3.5
66
67@@ -1270,6 +1270,13 @@
68 ALTER TABLE distribution ENABLE TRIGGER ALL;
69
70
71+ALTER TABLE gitrepository DISABLE TRIGGER ALL;
72+
73+
74+
75+ALTER TABLE gitrepository ENABLE TRIGGER ALL;
76+
77+
78 ALTER TABLE productseries DISABLE TRIGGER ALL;
79
80 INSERT INTO productseries (id, product, name, summary, releasefileglob, releaseverstyle, datecreated, driver, owner, status, translations_autoimport_mode, branch, translations_branch) VALUES (1, 4, 'trunk', 'The "trunk" series represents the primary line of development rather than a stable release branch. This is sometimes also called MAIN or HEAD.', NULL, NULL, '2005-06-06 08:59:51.895136', NULL, 12, 2, 1, NULL, NULL);
81@@ -1362,9 +1369,9 @@
82
83 ALTER TABLE accessartifact DISABLE TRIGGER ALL;
84
85-INSERT INTO accessartifact (id, bug, branch, specification) VALUES (1, 14, NULL, NULL);
86-INSERT INTO accessartifact (id, bug, branch, specification) VALUES (2, NULL, 29, NULL);
87-INSERT INTO accessartifact (id, bug, branch, specification) VALUES (3, NULL, 30, NULL);
88+INSERT INTO accessartifact (id, bug, branch, specification, gitrepository) VALUES (1, 14, NULL, NULL, NULL);
89+INSERT INTO accessartifact (id, bug, branch, specification, gitrepository) VALUES (2, NULL, 29, NULL, NULL);
90+INSERT INTO accessartifact (id, bug, branch, specification, gitrepository) VALUES (3, NULL, 30, NULL, NULL);
91
92
93 ALTER TABLE accessartifact ENABLE TRIGGER ALL;
94@@ -3777,6 +3784,19 @@
95 ALTER TABLE garbojobstate ENABLE TRIGGER ALL;
96
97
98+ALTER TABLE gitref DISABLE TRIGGER ALL;
99+
100+
101+
102+ALTER TABLE gitref ENABLE TRIGGER ALL;
103+
104+
105+
106+
107+
108+
109+
110+
111
112
113
114
115=== added file 'database/schema/patch-2209-61-0.sql'
116--- database/schema/patch-2209-61-0.sql 1970-01-01 00:00:00 +0000
117+++ database/schema/patch-2209-61-0.sql 2015-02-10 00:48:10 +0000
118@@ -0,0 +1,135 @@
119+-- Copyright 2015 Canonical Ltd. This software is licensed under the
120+-- GNU Affero General Public License version 3 (see the file LICENSE).
121+
122+SET client_min_messages=ERROR;
123+
124+CREATE OR REPLACE FUNCTION valid_git_repository_name(text) RETURNS boolean
125+ LANGUAGE plpythonu IMMUTABLE STRICT
126+ AS $_$
127+ import re
128+ name = args[0]
129+ pat = r"^(?i)[a-z0-9][a-z0-9+\.\-@_]*\Z"
130+ if not name.endswith(".git") and re.match(pat, name):
131+ return 1
132+ return 0
133+$_$;
134+
135+COMMENT ON FUNCTION valid_git_repository_name(text) IS 'validate a Git repository name.
136+
137+ As per valid_branch_name, except we disallow names ending in ".git".';
138+
139+CREATE TABLE GitRepository (
140+ id serial PRIMARY KEY,
141+ date_created timestamp without time zone DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC') NOT NULL,
142+ date_last_modified timestamp without time zone DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC') NOT NULL,
143+ registrant integer NOT NULL REFERENCES person,
144+ owner integer NOT NULL REFERENCES person,
145+ project integer REFERENCES product,
146+ distribution integer REFERENCES distribution,
147+ sourcepackagename integer REFERENCES sourcepackagename,
148+ name text NOT NULL,
149+ information_type integer NOT NULL,
150+ owner_default boolean DEFAULT false NOT NULL,
151+ target_default boolean DEFAULT false NOT NULL,
152+ access_policy integer,
153+ access_grants integer[],
154+ CONSTRAINT one_container CHECK (((project IS NULL) OR (distribution IS NULL)) AND ((distribution IS NULL) = (sourcepackagename IS NULL))),
155+ CONSTRAINT valid_name CHECK (valid_git_repository_name(name)),
156+ CONSTRAINT default_implies_target CHECK (((project IS NOT NULL) OR (distribution IS NOT NULL)) OR (NOT owner_default AND NOT target_default)),
157+ CONSTRAINT target_implies_owner CHECK (owner_default OR NOT target_default)
158+);
159+
160+CREATE UNIQUE INDEX gitrepository__owner__project__name__key
161+ ON GitRepository(owner, project, name) WHERE project IS NOT NULL;
162+CREATE UNIQUE INDEX gitrepository__owner__distribution__sourcepackagename__name__key
163+ ON GitRepository(owner, distribution, sourcepackagename, name) WHERE distribution IS NOT NULL;
164+CREATE UNIQUE INDEX gitrepository__owner__name__key
165+ ON GitRepository(owner, name) WHERE project IS NULL AND distribution IS NULL;
166+
167+COMMENT ON TABLE GitRepository IS 'Git repository';
168+COMMENT ON COLUMN GitRepository.registrant IS 'The user who registered the repository.';
169+COMMENT ON COLUMN GitRepository.owner IS 'The owner of the repository.';
170+COMMENT ON COLUMN GitRepository.project IS 'The project that this repository belongs to.';
171+COMMENT ON COLUMN GitRepository.distribution IS 'The distribution that this repository belongs to.';
172+COMMENT ON COLUMN GitRepository.sourcepackagename IS 'The source package that this repository belongs to.';
173+COMMENT ON COLUMN GitRepository.name IS 'The name of this repository.';
174+COMMENT ON COLUMN GitRepository.information_type IS 'Enum describing what type of information is stored, such as type of private or security related data, and used to determine how to apply an access policy.';
175+COMMENT ON COLUMN GitRepository.owner_default IS 'True if this repository is the default for its owner and target.';
176+COMMENT ON COLUMN GitRepository.target_default IS 'True if this repository is the default for its target.';
177+
178+ALTER TABLE AccessArtifact
179+ ADD COLUMN gitrepository integer REFERENCES gitrepository;
180+
181+CREATE UNIQUE INDEX accessartifact__gitrepository__key
182+ ON AccessArtifact(gitrepository) WHERE gitrepository IS NOT NULL;
183+
184+ALTER TABLE AccessArtifact DROP CONSTRAINT has_artifact;
185+
186+ALTER TABLE AccessArtifact
187+ ADD CONSTRAINT has_artifact CHECK ((null_count(ARRAY[bug, branch, gitrepository, specification]) = 3));
188+
189+CREATE OR REPLACE FUNCTION gitrepository_denorm_access(gitrepository_id integer)
190+ RETURNS void LANGUAGE sql SECURITY DEFINER SET search_path = public AS $$
191+ UPDATE GitRepository
192+ SET access_policy = policies[1], access_grants = grants
193+ FROM
194+ build_access_cache(
195+ (SELECT id FROM accessartifact WHERE gitrepository = $1),
196+ (SELECT information_type FROM gitrepository WHERE id = $1))
197+ AS (policies integer[], grants integer[])
198+ WHERE id = $1;
199+$$;
200+
201+CREATE OR REPLACE FUNCTION accessartifact_denorm_to_artifacts(artifact_id integer)
202+ RETURNS void
203+ LANGUAGE plpgsql
204+ AS $$
205+DECLARE
206+ artifact_row accessartifact%ROWTYPE;
207+BEGIN
208+ SELECT * INTO artifact_row FROM accessartifact WHERE id = artifact_id;
209+ IF artifact_row.bug IS NOT NULL THEN
210+ PERFORM bug_flatten_access(artifact_row.bug);
211+ END IF;
212+ IF artifact_row.branch IS NOT NULL THEN
213+ PERFORM branch_denorm_access(artifact_row.branch);
214+ END IF;
215+ IF artifact_row.gitrepository IS NOT NULL THEN
216+ PERFORM gitrepository_denorm_access(artifact_row.gitrepository);
217+ END IF;
218+ IF artifact_row.specification IS NOT NULL THEN
219+ PERFORM specification_denorm_access(artifact_row.specification);
220+ END IF;
221+ RETURN;
222+END;
223+$$;
224+
225+COMMENT ON FUNCTION accessartifact_denorm_to_artifacts(artifact_id integer) IS
226+ 'Denormalize the policy access and artifact grants to bugs, branches, Git repositories, and specifications.';
227+
228+-- A trigger to handle gitrepository.information_type changes.
229+CREATE OR REPLACE FUNCTION gitrepository_maintain_access_cache_trig() RETURNS trigger
230+ LANGUAGE plpgsql AS $$
231+BEGIN
232+ PERFORM gitrepository_denorm_access(NEW.id);
233+ RETURN NULL;
234+END;
235+$$;
236+
237+CREATE TRIGGER gitrepository_maintain_access_cache
238+ AFTER INSERT OR UPDATE OF information_type ON GitRepository
239+ FOR EACH ROW EXECUTE PROCEDURE gitrepository_maintain_access_cache_trig();
240+
241+CREATE TABLE GitRef (
242+ repository integer NOT NULL REFERENCES gitrepository,
243+ path text NOT NULL,
244+ commit_sha1 character(40) NOT NULL,
245+ PRIMARY KEY (repository, path)
246+);
247+
248+COMMENT ON TABLE GitRef IS 'A reference in a Git repository.';
249+COMMENT ON COLUMN GitRef.repository IS 'The repository containing this reference.';
250+COMMENT ON COLUMN GitRef.path IS 'The full path of the reference, e.g. refs/heads/master.';
251+COMMENT ON COLUMN GitRef.commit_sha1 IS 'The SHA-1 hash of the object pointed to by this reference.';
252+
253+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 61, 0);
254
255=== modified file 'database/schema/security.cfg'
256--- database/schema/security.cfg 2015-02-02 14:25:00 +0000
257+++ database/schema/security.cfg 2015-02-10 00:48:10 +0000
258@@ -47,6 +47,7 @@
259 public._ftq(text) = EXECUTE
260 public.generate_openid_identifier() = EXECUTE
261 public.getlocalnodeid() = EXECUTE
262+public.gitrepository_denorm_access(integer) = EXECUTE
263 public.is_blacklisted_name(text, integer) = EXECUTE
264 public.is_person(text) = EXECUTE
265 public.is_printable_ascii(text) = EXECUTE
266@@ -84,6 +85,7 @@
267 public.valid_cve(text) = EXECUTE
268 public.valid_debian_version(text) = EXECUTE
269 public.valid_fingerprint(text) = EXECUTE
270+public.valid_git_repository_name(text) = EXECUTE
271 public.valid_keyid(text) = EXECUTE
272 public.valid_name(text) = EXECUTE
273 public.valid_regexp(text) = EXECUTE
274@@ -194,6 +196,8 @@
275 public.featureflag = SELECT, INSERT, UPDATE, DELETE
276 public.featureflagchangelogentry = SELECT, INSERT, UPDATE
277 public.flatpackagesetinclusion = SELECT, INSERT, UPDATE, DELETE
278+public.gitref = SELECT, INSERT, UPDATE, DELETE
279+public.gitrepository = SELECT, INSERT, UPDATE, DELETE
280 public.hwdevice = SELECT
281 public.hwdeviceclass = SELECT, INSERT, DELETE
282 public.hwdevicedriverlink = SELECT
283@@ -369,6 +373,7 @@
284 public.distroseries = SELECT, UPDATE
285 public.distroserieslanguage = SELECT, INSERT, UPDATE, DELETE
286 public.distroseriespackagecache = SELECT, INSERT, UPDATE, DELETE
287+public.gitrepository = SELECT
288 public.language = SELECT
289 public.launchpadstatistic = SELECT, INSERT, UPDATE, DELETE
290 public.person = SELECT
291@@ -682,6 +687,8 @@
292 public.distributionsourcepackage = SELECT, UPDATE
293 public.distroseries = SELECT
294 public.emailaddress = SELECT
295+public.gitref = SELECT, INSERT, UPDATE, DELETE
296+public.gitrepository = SELECT, UPDATE
297 public.incrementaldiff = SELECT
298 public.job = SELECT, INSERT, UPDATE, DELETE
299 public.karma = SELECT, INSERT
300@@ -828,6 +835,8 @@
301 public.branchrevision = SELECT
302 public.distribution = SELECT
303 public.distroseries = SELECT
304+public.gitref = SELECT
305+public.gitrepository = SELECT
306 public.karma = SELECT, INSERT
307 public.karmaaction = SELECT
308 public.karmacategory = SELECT
309@@ -1183,6 +1192,7 @@
310 public.distroarchseries = SELECT, INSERT, UPDATE
311 public.distroseries = SELECT, INSERT, UPDATE
312 public.emailaddress = SELECT, INSERT, UPDATE
313+public.gitrepository = SELECT, INSERT, UPDATE
314 public.gpgkey = SELECT, INSERT, UPDATE, DELETE
315 public.ircid = SELECT, INSERT, UPDATE, DELETE
316 public.jabberid = SELECT, INSERT, UPDATE, DELETE
317@@ -1959,6 +1969,7 @@
318 public.bugtaskflat = SELECT
319 public.bugwatch = SELECT
320 public.distribution = SELECT
321+public.gitrepository = SELECT
322 public.emailaddress = SELECT
323 public.job = SELECT, INSERT, UPDATE
324 public.person = SELECT
325@@ -2142,6 +2153,7 @@
326 public.emailaddress = SELECT, UPDATE, DELETE
327 public.faq = SELECT, UPDATE
328 public.featureflagchangelogentry = SELECT, UPDATE
329+public.gitrepository = SELECT, UPDATE
330 public.gpgkey = SELECT, UPDATE
331 public.hwsubmission = SELECT, UPDATE
332 public.ircid = SELECT, UPDATE
333@@ -2428,6 +2440,7 @@
334 public.distribution = SELECT
335 public.distroseries = SELECT
336 public.emailaddress = SELECT
337+public.gitrepository = SELECT
338 public.job = SELECT, INSERT, UPDATE
339 public.person = SELECT
340 public.packaging = SELECT

Subscribers

People subscribed via source and target branches

to status/vote changes: