Merge ~ines-almeida/launchpad:project-tokens/db-patch into launchpad:master

[Ines Almeida]

DB patch: Add project to access token table

[Colin Watson (cjwatson)]

Normally speaking, the index changes would have to be done in a separate hot patch. However, given that there's a grand total of 409 `AccessToken` rows on staging today, I think we can get away with it in this case.

Adding William for a second DB review.

review: Approve (db)

[Colin Watson (cjwatson)]

Oh, I only just noticed - this is a cold patch, so you'll have to transplant it onto db-devel and re-propose this merge proposal with the target branch set to db-devel.

review: Needs Resubmitting

bd42b70... by Ines Almeida on 2023-09-21

Rework DB indexes from access token targets

[Ines Almeida (ines-almeida)]

I updated the indexes and resubmitted the MP

Unmerged commits

bd42b70... by Ines Almeida on 2023-09-21

Rework DB indexes from access token targets

	docs:0 (build)
	lint:0 (build)
	mypy:0 (build)

1 → 3 of 3 results

First • Previous • Next • Last

f9e08c2... by Ines Almeida on 2023-09-01

DB patch: Add project to access token table

	docs:0 (build)
	lint:0 (build)
	mypy:0 (build)

1 → 3 of 3 results

First • Previous • Next • Last

diff --git a/database/schema/patch-2211-22-0.sql b/database/schema/patch-2211-22-0.sql
new file mode 100644
index 0000000..27c6dea
--- /dev/null
+++ b/database/schema/patch-2211-22-0.sql
@@ -0,0 +1,25 @@
+-- Copyright 2023 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+ALTER TABLE AccessToken
+    ADD COLUMN project integer REFERENCES product;
+
+COMMENT ON COLUMN AccessToken.project IS 'The project for which the token was issued.';
+
+CREATE INDEX accesstoken__project__idx
+    ON AccessToken (project) WHERE project IS NOT NULL;
+
+DROP INDEX accesstoken__git_repository__idx;
+CREATE INDEX accesstoken__git_repository__idx
+    ON AccessToken (git_repository) WHERE git_repository IS NOT NULL;
+
+-- There can only be either a git_repository or a project as target
+ALTER TABLE AccessToken
+    ALTER COLUMN git_repository DROP NOT NULL,
+    ADD CONSTRAINT one_target CHECK (
+        (public.null_count(ARRAY[git_repository, project]) = 1)
+    );
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2211, 22, 0);