96 lines
2.8 KiB
SQL
96 lines
2.8 KiB
SQL
CREATE TABLE IF NOT EXISTS USERS (
|
|
id INTEGER PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
image TEXT,
|
|
image_medium TEXT,
|
|
pool TEXT NOT NULL,
|
|
lang TEXT NOT NULL DEFAULT 'fr',
|
|
active TEXT NOT NULL DEFAULT 0,
|
|
campus INTEGER NOT NULL DEFAULT 1,
|
|
tag TEXT DEFAULT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS COOKIES (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
userid INTEGER NOT NULL,
|
|
uuid TEXT NOT NULL UNIQUE,
|
|
name TEXT DEFAULT NULL,
|
|
creation TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY(userid) REFERENCES USERS(id)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS FRIENDS (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
who INTEGER NOT NULL,
|
|
has INTEGER NOT NULL,
|
|
relation INTEGER DEFAULT 0,
|
|
FOREIGN KEY(who) REFERENCES USERS(id),
|
|
FOREIGN KEY(has) REFERENCES USERS(id)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS DEAD_PC (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
issuer INTEGER NOT NULL,
|
|
station TEXT NOT NULL,
|
|
issue INTEGER NOT NULL,
|
|
solved INTEGER NOT NULL DEFAULT 0,
|
|
since TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY(issuer) REFERENCES USERS(id)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS PROFILES (
|
|
userid INTEGER PRIMARY KEY,
|
|
website TEXT NOT NULL DEFAULT '',
|
|
github TEXT NOT NULL DEFAULT '',
|
|
discord TEXT NOT NULL DEFAULT '',
|
|
recit TEXT NOT NULL DEFAULT '',
|
|
FOREIGN KEY(userid) REFERENCES USERS(id)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS THEME (
|
|
userid INTEGER PRIMARY KEY,
|
|
javascript TEXT DEFAULT NULL,
|
|
css TEXT DEFAULT NULL,
|
|
enabled INTEGER NOT NULL DEFAULT 0,
|
|
FOREIGN KEY(userid) REFERENCES USERS(id)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS WHITELIST (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL UNIQUE,
|
|
user_login TEXT NOT NULL UNIQUE,
|
|
FOREIGN KEY(id) REFERENCES USERS(id)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS PERMISSIONS (
|
|
user_id INTEGER PRIMARY KEY,
|
|
tag TEXT DEFAULT 'admin',
|
|
FOREIGN KEY(user_id) REFERENCES USERS(id)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS PISCINES (
|
|
id INTEGER PRIMARY KEY,
|
|
campus INTEGER NOT NULL DEFAULT 1,
|
|
cluster TEXT NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS SILENTS (
|
|
id INTEGER PRIMARY KEY,
|
|
campus INTEGER DEFAULT 1,
|
|
cluster TEXT NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS TUTOR_STATION (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
campus INTEGER DEFAULT 1,
|
|
station TEXT UNIQUE
|
|
);
|
|
CREATE TABLE IF NOT EXISTS TUTORS (
|
|
id INTEGER PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
FOREIGN KEY(id) REFERENCES USERS(id)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS PISCINE_DATES (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
month TEXT NOT NULL,
|
|
year TEXT NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS IMAGES (
|
|
id INTEGER PRIMARY KEY,
|
|
by INTEGER NOT NULL,
|
|
at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY(id) REFERENCES USERS(id),
|
|
FOREIGN KEY(by) REFERENCES USERS(id)
|
|
);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS _4668f241eac04010947c92265192817d ON FRIENDS (who, has);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS _95b45dfd944d4132ac418e15ebe15edb ON DEAD_PC (issuer, station);
|