Sign in

pulls/pr_01m47d24b0e6n91zwymwxg0vpx

public

pr_01m47d24b0e6n91zwymwxg0vpx/services/identity/migrations/0002_text_ids.sql

48 lines1,403 bytesCodeBlame
1-- Identity now owns only accounts and credentials, with prefixed text ids.
2-- Repositories moved to the repos service.
3DROP TABLE repos;
4DROP TABLE sessions;
5DROP TABLE access_tokens;
6DROP TABLE ssh_keys;
7
8ALTER TABLE users RENAME TO users_old;
9
10CREATE TABLE users (
11 id TEXT PRIMARY KEY,
12 username TEXT NOT NULL UNIQUE,
13 email TEXT,
14 password_hash TEXT NOT NULL,
15 created_at INTEGER NOT NULL DEFAULT (unixepoch())
16);
17
18INSERT INTO users (id, username, email, password_hash, created_at)
19SELECT 'usr_' || lower(hex(randomblob(13))), username, email, password_hash, created_at
20FROM users_old;
21
22DROP TABLE users_old;
23
24-- id is the SHA-256 of the session token.
25CREATE TABLE sessions (
26 id TEXT PRIMARY KEY,
27 user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
28 expires_at INTEGER NOT NULL
29);
30
31CREATE TABLE access_tokens (
32 id TEXT PRIMARY KEY,
33 user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
34 name TEXT NOT NULL,
35 token_hash TEXT NOT NULL UNIQUE,
36 created_at INTEGER NOT NULL DEFAULT (unixepoch())
37);
38CREATE INDEX access_tokens_user ON access_tokens (user_id);
39
40CREATE TABLE ssh_keys (
41 id TEXT PRIMARY KEY,
42 user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
43 title TEXT NOT NULL,
44 public_key TEXT NOT NULL,
45 fingerprint TEXT NOT NULL UNIQUE,
46 created_at INTEGER NOT NULL DEFAULT (unixepoch())
47);
48CREATE INDEX ssh_keys_user ON ssh_keys (user_id);