src/db/tables.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# DROP TABLE IF EXISTS access;
# DROP TABLE IF EXISTS users;

CREATE TABLE IF NOT EXISTS users
(
    id           INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email        VARCHAR(48) NOT NULL UNIQUE,
    username     VARCHAR(24) NOT NULL UNIQUE,
    password     VARCHAR(64) NOT NULL,
    verification VARCHAR(64) NOT NULL UNIQUE,
    dark_theme   BOOLEAN     NOT NULL DEFAULT true,
    is_admin     BOOLEAN     NOT NULL DEFAULT false
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE IF NOT EXISTS access
(
    id   INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uid  INT(6) UNSIGNED,
    path VARCHAR(64) NOT NULL,
    FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;