kp/backend/database/__init__.py

# -*- coding: utf-8 -*-
from sqlite3 import connect

db = connect('landing_page.db')
cur = db.cursor()


cur.execute('''CREATE TABLE IF NOT EXISTS user (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    login TEXT NOT NULL,
    password TEXT NOT NULL,
    role INTEGER NOT NULL,
    access_token TEXT NOT NULL
);''')

cur.execute('''CREATE TABLE IF NOT EXISTS role (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL
);''')

cur.execute('''CREATE TABLE IF NOT EXISTS event (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    author INTEGER NOT NULL,
    title TEXT NOT NULL,
    date INTEGER NOT NULL /* timestamp */
);''')

cur.execute('''CREATE TABLE IF NOT EXISTS event_user (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    event_id INTEGER NOT NULL
);''')
db.commit()


def default(table: str, max_size: int, values: dict[str, object]):
    t = cur.execute(f'SELECT * FROM {table}').fetchall()
    if len(t) < max_size:
        cur.execute(
            f'INSERT INTO {table} ({",".join(values.keys())}) VALUES ({",".join(["?" for i in values.keys()])});',
            tuple([i for i in values.values()])
        )
        db.commit()


def defaults(table: str, *values: dict[str, object]):
    size = len(values)
    for i in values:
        default(table, size, i)


defaults(
    'role',
    {'title': 'Администратор'},  # id = 1
    {'title': 'Спикер'},  # id = 2
    {'title': 'Слушатель'},  # id = 3
)