76 lines
2.1 KiB
MySQL
76 lines
2.1 KiB
MySQL
|
-- schema.sql
|
||
|
|
CREATE TABLE IF NOT EXISTS users (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
username TEXT UNIQUE NOT NULL,
|
||
|
|
email TEXT UNIQUE NOT NULL,
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
password TEXT NOT NULL,
|
||
|
|
status INTEGER DEFAULT 0
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
user_id INTEGER NOT NULL,
|
||
|
|
session_id TEXT UNIQUE NOT NULL,
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS tasks (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
user_id INTEGER NOT NULL,
|
||
|
|
task TEXT UNIQUE NOT NULL,
|
||
|
|
description TEXT,
|
||
|
|
status TEXT,
|
||
|
|
external_url TEXT,
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS permissions (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
user_id INTEGER NOT NULL,
|
||
|
|
permission TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS accounts (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
account_id INTEGER NOT NULL,
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS user_accounts (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
user_id INTEGER NOT NULL,
|
||
|
|
account_id INTEGER NOT NULL,
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
|
||
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
|
||
|
|
|
||
|
|
UNIQUE (user_id, account_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS pageviews (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
account_id INTEGER NOT NULL,
|
||
|
|
page TEXT NOT NULL,
|
||
|
|
user_agent TEXT,
|
||
|
|
referrer TEXT,
|
||
|
|
ip TEXT,
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_pageviews_account_id
|
||
|
|
ON pageviews(account_id);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_pageviews_created_at
|
||
|
|
ON pageviews(created_at);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_pageviews_account_time
|
||
|
|
ON pageviews(account_id, created_at);
|
||
|
|
|