46 lines
1.3 KiB
Python
46 lines
1.3 KiB
Python
|
"""SQLite-слой: 3 таблицы, WAL-режим, один файл db.sqlite."""
|
||
|
|
from __future__ import annotations
|
||
|
|
|
||
|
|
import os
|
||
|
|
import sqlite3
|
||
|
|
|
||
|
|
SCHEMA = """
|
||
|
|
PRAGMA journal_mode=WAL;
|
||
|
|
CREATE TABLE IF NOT EXISTS videos (
|
||
|
|
filename TEXT PRIMARY KEY,
|
||
|
|
likes INTEGER NOT NULL DEFAULT 0,
|
||
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
||
|
|
);
|
||
|
|
CREATE TABLE IF NOT EXISTS tags (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL UNIQUE
|
||
|
|
);
|
||
|
|
CREATE TABLE IF NOT EXISTS video_tags (
|
||
|
|
filename TEXT NOT NULL REFERENCES videos(filename) ON DELETE CASCADE,
|
||
|
|
tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
|
||
|
|
UNIQUE (filename, tag_id)
|
||
|
|
);
|
||
|
|
CREATE TABLE IF NOT EXISTS likes_log (
|
||
|
|
ip TEXT NOT NULL,
|
||
|
|
filename TEXT NOT NULL,
|
||
|
|
day TEXT NOT NULL,
|
||
|
|
UNIQUE (ip, filename, day)
|
||
|
|
);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_video_tags_tag ON video_tags(tag_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_videos_likes ON videos(likes DESC);
|
||
|
|
"""
|
||
|
|
|
||
|
|
DB_PATH = os.environ.get("DB_PATH", os.path.join(os.path.dirname(__file__), "db.sqlite"))
|
||
|
|
|
||
|
|
|
||
|
|
def connect() -> sqlite3.Connection:
|
||
|
|
con = sqlite3.connect(DB_PATH)
|
||
|
|
con.row_factory = sqlite3.Row
|
||
|
|
con.execute("PRAGMA journal_mode=WAL")
|
||
|
|
con.execute("PRAGMA foreign_keys=ON")
|
||
|
|
return con
|
||
|
|
|
||
|
|
|
||
|
|
def init_db() -> None:
|
||
|
|
with connect() as con:
|
||
|
|
con.executescript(SCHEMA)
|