flabk/migrate/1662887862_schema.up.sql

39 lines
948 B
MySQL
Raw Permalink Normal View History

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
2022-09-11 16:52:37 +01:00
CREATE TABLE users (
id CHAR(22) NOT NULL PRIMARY KEY,
username TEXT NOT NULL,
host TEXT,
display_name TEXT,
2022-09-13 01:35:05 +01:00
email TEXT NOT NULL,
2022-09-13 17:51:23 +01:00
password_hash TEXT NOT NULL,
avatar_uri TEXT,
bio TEXT,
2022-09-11 16:52:37 +01:00
);
CREATE UNIQUE INDEX u_username_host ON users (username, host);
CREATE UNIQUE INDEX u_username_local ON users (username) WHERE host IS NULL;
--id UUID NOT NULL PRIMARY KEY DEFAULT uuid_generate_v4(),
CREATE TABLE keys (
key TEXT NOT NULL PRIMARY KEY,
value TEXT NOT NULL
);
CREATE TABLE follows (
user_id CHAR(22) NOT NULL REFERENCES users(id),
follows_id CHAR(22) NOT NULL REFERENCES users(id),
created_at TIMESTAMP NOT NULL DEFAULT now(),
PRIMARY KEY (user_id, follows_id)
);
CREATE TABLE follow_requests(
user_id CHAR(22) NOT NULL REFERENCES users(id),
follows_id CHAR(22) NOT NULL REFERENCES users(id),
created_at TIMESTAMP NOT NULL DEFAULT now(),
PRIMARY KEY (user_id, follows_id)
);