Merge lp:~wgrant/launchpad/xref-db into lp:launchpad/db-devel

[William Grant]

Add an XRef table for generic cross-references between artifacts.

Add an XRef table for generic cross-references between artifacts.

Model code is at <https://code.launchpad.net/~wgrant/launchpad/xref-model/+merge/272588>.

An artifact is identified as a tuple of two Unicode strings: ('type', 'id'). Examples from the initial series include ('bug', '1'), ('specification', '10'), ('question', '100') and ('cve', '2015-0001').

To optimise SQL joins against tables with integral PKs, *_id_int columns are available and populated automatically if the ID looks like a decimal number.

Model code automatically creates links in both directions. This won't always be the case when the table can also refer to remote objects, but it makes queries faster and easier today.

[Colin Watson (cjwatson)]

review: Approve (db)

=== added file 'database/schema/patch-2209-70-0.sql'
--- database/schema/patch-2209-70-0.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2209-70-0.sql	2015-09-28 12:40:41 +0000
@@ -0,0 +1,31 @@
+-- Copyright 2015 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+CREATE TABLE XRef (
+    from_type text NOT NULL,
+    from_id text NOT NULL,
+    from_id_int integer,
+    to_type text NOT NULL,
+    to_id text NOT NULL,
+    to_id_int integer,
+    creator integer REFERENCES Person,
+    date_created timestamp without time zone
+        DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC') NOT NULL,
+    metadata text,
+    PRIMARY KEY (from_type, from_id, to_type, to_id)
+);
+
+CREATE UNIQUE INDEX xref__inverse__key
+    ON XRef(to_type, to_id, from_type, from_id);
+CREATE UNIQUE INDEX xref__int__key
+    ON XRef(from_type, from_id_int, to_type, to_id_int);
+CREATE UNIQUE INDEX xref__int_inverse__key
+    ON XRef(to_type, to_id_int, from_type, from_id_int);
+CREATE INDEX xref__from_type__to_type__idx ON XRef(from_type, to_type);
+CREATE INDEX xref__to_type__from_type__idx ON XRef(to_type, from_type);
+
+CREATE INDEX xref__creator__idx ON XRef(creator);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 70, 0);
 
=== modified file 'database/schema/security.cfg'
--- database/schema/security.cfg	2015-09-09 16:48:14 +0000
+++ database/schema/security.cfg	2015-09-28 12:40:41 +0000
@@ -328,6 +328,7 @@
 public.webhook                          = SELECT, INSERT, UPDATE, DELETE
 public.webhookjob                       = SELECT, INSERT, UPDATE, DELETE
 public.wikiname                         = SELECT, INSERT, UPDATE, DELETE
+public.xref                             = SELECT, INSERT, UPDATE, DELETE
 type=user
 
 [launchpad]
@@ -2268,6 +2269,7 @@
 public.votecast                         = SELECT, UPDATE
 public.webhook                          = SELECT, UPDATE
 public.wikiname                         = SELECT, UPDATE
+public.xref                             = SELECT, UPDATE
 type=user
 
 [garbo]