225 lines
4.5 KiB
Python
225 lines
4.5 KiB
Python
#!/bin/python3
|
|
|
|
import sqlite3
|
|
from pathlib import Path
|
|
|
|
|
|
DB_NAME = Path(__file__).parent / "calendar.db"
|
|
|
|
conn = sqlite3.connect(DB_NAME)
|
|
conn.execute("PRAGMA foreign_keys = ON;")
|
|
cur = conn.cursor()
|
|
|
|
|
|
cur.execute("""
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT NOT NULL UNIQUE,
|
|
email TEXT UNIQUE,
|
|
email_verified INTEGER NOT NULL DEFAULT 0,
|
|
password_hash TEXT NOT NULL
|
|
);
|
|
""")
|
|
|
|
|
|
cur.execute("""
|
|
CREATE TABLE IF NOT EXISTS email_verifications (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
expires_at TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES users(id)
|
|
ON DELETE CASCADE
|
|
);
|
|
""")
|
|
|
|
|
|
cur.execute("""
|
|
CREATE TABLE IF NOT EXISTS password_reset_tokens (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
expires_at TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES users(id)
|
|
ON DELETE CASCADE
|
|
);
|
|
""")
|
|
|
|
|
|
cur.execute("""
|
|
CREATE TABLE IF NOT EXISTS rooms (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE,
|
|
owner_id INTEGER NOT NULL,
|
|
invite_code TEXT NOT NULL UNIQUE,
|
|
|
|
FOREIGN KEY (owner_id)
|
|
REFERENCES users(id)
|
|
);
|
|
""")
|
|
|
|
|
|
cur.execute("""
|
|
CREATE TABLE IF NOT EXISTS room_members (
|
|
room_id INTEGER NOT NULL,
|
|
user_id INTEGER NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'member'
|
|
CHECK(role IN ('owner', 'admin', 'member')),
|
|
|
|
PRIMARY KEY (room_id, user_id),
|
|
|
|
FOREIGN KEY (room_id)
|
|
REFERENCES rooms(id)
|
|
ON DELETE CASCADE,
|
|
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES users(id)
|
|
ON DELETE CASCADE
|
|
);
|
|
""")
|
|
|
|
|
|
cur.execute("""
|
|
CREATE TABLE IF NOT EXISTS events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
room_id INTEGER NOT NULL,
|
|
creator_id INTEGER NOT NULL,
|
|
|
|
type TEXT NOT NULL
|
|
CHECK(type IN ('private', 'busy', 'proposal')),
|
|
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
|
|
start_time TEXT NOT NULL,
|
|
end_time TEXT NOT NULL,
|
|
|
|
visibility TEXT NOT NULL
|
|
CHECK(visibility IN ('private', 'busy_only', 'room')),
|
|
|
|
status TEXT NOT NULL DEFAULT 'draft'
|
|
CHECK(status IN ('draft', 'proposed', 'confirmed', 'cancelled')),
|
|
|
|
min_people INTEGER DEFAULT 0,
|
|
|
|
invitation_mode TEXT NOT NULL DEFAULT 'room'
|
|
CHECK(invitation_mode IN ('room', 'custom')),
|
|
|
|
FOREIGN KEY (room_id)
|
|
REFERENCES rooms(id)
|
|
ON DELETE CASCADE,
|
|
|
|
FOREIGN KEY (creator_id)
|
|
REFERENCES users(id)
|
|
);
|
|
""")
|
|
|
|
|
|
cur.execute("""
|
|
CREATE TABLE IF NOT EXISTS event_invitations (
|
|
event_id INTEGER NOT NULL,
|
|
user_id INTEGER NOT NULL,
|
|
|
|
status TEXT NOT NULL DEFAULT 'pending'
|
|
CHECK(status IN ('pending', 'accepted', 'declined')),
|
|
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
PRIMARY KEY (event_id, user_id),
|
|
|
|
FOREIGN KEY (event_id)
|
|
REFERENCES events(id)
|
|
ON DELETE CASCADE,
|
|
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES users(id)
|
|
ON DELETE CASCADE
|
|
);
|
|
""")
|
|
|
|
|
|
cur.execute("""
|
|
CREATE TABLE IF NOT EXISTS event_signups (
|
|
event_id INTEGER NOT NULL,
|
|
user_id INTEGER NOT NULL,
|
|
|
|
status TEXT NOT NULL DEFAULT 'going'
|
|
CHECK(status IN ('going', 'maybe', 'declined')),
|
|
|
|
PRIMARY KEY (event_id, user_id),
|
|
|
|
FOREIGN KEY (event_id)
|
|
REFERENCES events(id)
|
|
ON DELETE CASCADE,
|
|
|
|
FOREIGN KEY (user_id)
|
|
REFERENCES users(id)
|
|
ON DELETE CASCADE
|
|
);
|
|
""")
|
|
|
|
|
|
cur.execute("""
|
|
CREATE INDEX IF NOT EXISTS idx_room_members_user
|
|
ON room_members(user_id);
|
|
""")
|
|
|
|
|
|
cur.execute("""
|
|
CREATE INDEX IF NOT EXISTS idx_events_room
|
|
ON events(room_id);
|
|
""")
|
|
|
|
|
|
cur.execute("""
|
|
CREATE INDEX IF NOT EXISTS idx_event_invitations_user
|
|
ON event_invitations(user_id);
|
|
""")
|
|
|
|
|
|
cur.execute("""
|
|
CREATE INDEX IF NOT EXISTS idx_event_invitations_event
|
|
ON event_invitations(event_id);
|
|
""")
|
|
|
|
|
|
cur.execute("""
|
|
CREATE INDEX IF NOT EXISTS idx_event_signups_user
|
|
ON event_signups(user_id);
|
|
""")
|
|
|
|
|
|
cur.execute("""
|
|
CREATE INDEX IF NOT EXISTS idx_email_verifications_user
|
|
ON email_verifications(user_id);
|
|
""")
|
|
|
|
|
|
cur.execute("""
|
|
CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_user
|
|
ON password_reset_tokens(user_id);
|
|
""")
|
|
|
|
|
|
conn.commit()
|
|
|
|
print("Database initialized.")
|
|
print(f"Database: {DB_NAME}")
|
|
print("Tables:")
|
|
|
|
for row in cur.execute("""
|
|
SELECT name
|
|
FROM sqlite_master
|
|
WHERE type = 'table'
|
|
ORDER BY name;
|
|
"""):
|
|
print(" -", row[0])
|
|
|
|
|
|
conn.close() |