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. _______________________________________________ Mailing list: https://launchpad.net/~launchpad-reviewers Post to : [email protected] Unsubscribe : https://launchpad.net/~launchpad-reviewers More help : https://help.launchpad.net/ListHelp
