launchpad-reviewers team mailing list archive

[William Grant <mp+409463@xxxxxxxxxxxxxxxxxx>]

Review: Approve db



Diff comments:

> diff --git a/database/schema/patch-2210-36-0.sql b/database/schema/patch-2210-36-0.sql
> new file mode 100644
> index 0000000..fa08939
> --- /dev/null
> +++ b/database/schema/patch-2210-36-0.sql
> @@ -0,0 +1,38 @@
> +-- Copyright 2021 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 AccessToken (
> +    id serial PRIMARY KEY,
> +    date_created timestamp without time zone DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC') NOT NULL,
> +    token_sha256 text NOT NULL,
> +    owner integer NOT NULL REFERENCES person,
> +    description text NOT NULL,
> +    git_repository integer REFERENCES gitrepository NOT NULL,
> +    scopes jsonb NOT NULL,
> +    date_last_used timestamp without time zone,

Updating this every time may well result in awful locking issues. We might want to use `SKIP LOCKED` for the first time.

SessionData.last_accessed somewhat gets away with it because it's a separate DB, only the first set of requests every few minutes trigger it, and it's relatively uncommon for there to be lots of concurrent webapp requests from one browser.

> +    date_expires timestamp without time zone

Is revocation implemented just by setting this to now?

> +);
> +
> +COMMENT ON TABLE AccessToken IS 'A personal access token for the webservice API.';
> +COMMENT ON COLUMN AccessToken.date_created IS 'When the token was created.';
> +COMMENT ON COLUMN AccessToken.token_sha256 IS 'SHA-256 hash of the secret token.';
> +COMMENT ON COLUMN AccessToken.owner IS 'The person who created the token.';
> +COMMENT ON COLUMN AccessToken.description IS 'A short description of the token''s purpose.';
> +COMMENT ON COLUMN AccessToken.git_repository IS 'The Git repository for which the token was issued.';
> +COMMENT ON COLUMN AccessToken.scopes IS 'A list of scopes granted by the token.';
> +COMMENT ON COLUMN AccessToken.date_last_used IS 'When the token was last used.';
> +COMMENT ON COLUMN AccessToken.date_expires IS 'When the token should expire.';
> +
> +CREATE UNIQUE INDEX accesstoken__token_sha256__key
> +    ON AccessToken (token_sha256);
> +CREATE INDEX accesstoken__owner__idx
> +    ON AccessToken (owner);
> +CREATE INDEX accesstoken__git_repository__idx
> +    ON AccessToken (git_repository);
> +CREATE INDEX accesstoken__date_expires__idx
> +    ON AccessToken (date_expires)
> +    WHERE date_expires IS NOT NULL;
> +
> +INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 36, 0);


-- 
https://code.launchpad.net/~cjwatson/launchpad/+git/launchpad/+merge/409463
Your team Launchpad code reviewers is requested to review the proposed merge of ~cjwatson/launchpad:db-access-token into launchpad:db-devel.