129 lines
2.9 KiB
SQL
129 lines
2.9 KiB
SQL
CREATE TABLE IF NOT EXISTS USERS
|
|
(
|
|
id INTEGER PRIMARY KEY,
|
|
name VARCHAR(12) UNIQUE,
|
|
image VARCHAR(100),
|
|
image_medium VARCHAR(100),
|
|
pool VARCHAR(100),
|
|
lang VARCHAR(3) DEFAULT 'fr',
|
|
active TIMESTAMP DEFAULT 0,
|
|
campus INTEGER DEFAULT 1,
|
|
tag TEXT DEFAULT NULL,
|
|
custom_image_link TEXT DEFAULT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS COOKIES
|
|
(
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
userid INTEGER,
|
|
uuid VARCHAR(65) UNIQUE,
|
|
name VARCHAR(40) DEFAULT NULL,
|
|
creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (userid) REFERENCES USERS (id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS FRIENDS
|
|
(
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
who INTEGER,
|
|
has INTEGER,
|
|
relation INTEGER DEFAULT 0,
|
|
UNIQUE (who, has),
|
|
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,
|
|
station VARCHAR(8),
|
|
issue INTEGER,
|
|
solved INTEGER DEFAULT 0,
|
|
since TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE (issuer, station),
|
|
FOREIGN KEY (issuer) REFERENCES USERS (id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS PROFILES
|
|
(
|
|
userid INTEGER PRIMARY KEY REFERENCES USERS (id),
|
|
website TEXT DEFAULT '',
|
|
github TEXT DEFAULT '',
|
|
discord VARCHAR(40) DEFAULT '',
|
|
recit TEXT DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS THEME
|
|
(
|
|
userid INTEGER PRIMARY KEY,
|
|
javascript TEXT DEFAULT NULL,
|
|
css TEXT DEFAULT NULL,
|
|
enabled INTEGER DEFAULT 0,
|
|
FOREIGN KEY (userid) REFERENCES USERS (id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS WHITELIST
|
|
(
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER,
|
|
user_login TEXT,
|
|
UNIQUE (user_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS PERMISSIONS
|
|
(
|
|
user_id INTEGER PRIMARY KEY,
|
|
level INTEGER DEFAULT 0,
|
|
show_tag INTEGER DEFAULT 0,
|
|
tag TEXT DEFAULT 'admin'
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS PISCINES
|
|
(
|
|
id INTEGER PRIMARY KEY,
|
|
campus INTEGER DEFAULT 1,
|
|
cluster TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS SILENTS
|
|
(
|
|
id INTEGER PRIMARY KEY,
|
|
campus INTEGER DEFAULT 1,
|
|
cluster TEXT
|
|
);
|
|
|
|
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
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS PISCINE_DATES
|
|
(
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
month TEXT,
|
|
year TEXT
|
|
);
|
|
|
|
/*
|
|
CREATE TABLE IF NOT EXISTS PREFERENCES
|
|
(
|
|
userid INTEGER PRIMARY KEY,
|
|
show_piscine INTEGER DEFAULT 0,
|
|
low_performance INTEGER DEFAULT 0,
|
|
telegram_uid TEXT DEFAULT NULL,
|
|
send_notif INTEGER DEFAULT 0,
|
|
theme INTEGER DEFAULT 0,
|
|
lang VARCHAR DEFAULT 'fr'
|
|
privacy INTEGER DEFAULT 0,
|
|
FOREIGN KEY (userid) REFERENCES USERS (id)
|
|
);
|
|
*/
|