113 lines
3.6 KiB
Python
113 lines
3.6 KiB
Python
import os
|
|
import sqlite3
|
|
import json
|
|
import time
|
|
from typing import Optional, Dict, Any, List
|
|
from app.config import settings
|
|
|
|
# Default DB lives in storage/; tests override via SONICFORGE_DB_PATH so the
|
|
# dev database is never touched by the test suite.
|
|
DB_PATH = os.getenv("SONICFORGE_DB_PATH") or os.path.join(settings.STORAGE_DIR, "sonicforge.db")
|
|
|
|
def get_db_connection():
|
|
os.makedirs(settings.STORAGE_DIR, exist_ok=True)
|
|
conn = sqlite3.connect(DB_PATH)
|
|
conn.row_factory = sqlite3.Row
|
|
# WAL improves concurrent read/write; FK enforcement makes quota/backup
|
|
# cleanup consistent when users are deleted.
|
|
conn.execute("PRAGMA journal_mode=WAL")
|
|
conn.execute("PRAGMA foreign_keys=ON")
|
|
return conn
|
|
|
|
def init_db():
|
|
conn = get_db_connection()
|
|
cursor = conn.cursor()
|
|
|
|
# Bảng Users
|
|
cursor.execute("""
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id TEXT PRIMARY KEY,
|
|
username TEXT UNIQUE NOT NULL,
|
|
email TEXT UNIQUE NOT NULL,
|
|
hashed_password TEXT NOT NULL,
|
|
role TEXT DEFAULT 'standard',
|
|
must_change_password BOOLEAN DEFAULT 1,
|
|
created_at REAL NOT NULL,
|
|
is_active BOOLEAN DEFAULT 1
|
|
);
|
|
""")
|
|
|
|
# Bảng Quotas
|
|
cursor.execute("""
|
|
CREATE TABLE IF NOT EXISTS user_quotas (
|
|
user_id TEXT PRIMARY KEY,
|
|
storage_limit_mb INTEGER DEFAULT 500,
|
|
max_tracks INTEGER DEFAULT 16,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
""")
|
|
|
|
# Bảng Projects (Bao gồm Cloud Project & Temp Auto-Save)
|
|
cursor.execute("""
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
data_json TEXT NOT NULL,
|
|
is_temp BOOLEAN DEFAULT 0,
|
|
size_bytes INTEGER DEFAULT 0,
|
|
updated_at REAL NOT NULL,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
""")
|
|
|
|
# Bảng System Flags
|
|
cursor.execute("""
|
|
CREATE TABLE IF NOT EXISTS system_flags (
|
|
flag_key TEXT PRIMARY KEY,
|
|
description TEXT,
|
|
is_enabled BOOLEAN DEFAULT 1,
|
|
updated_at REAL NOT NULL
|
|
);
|
|
""")
|
|
|
|
# Migration: thêm cột backup nếu chưa tồn tại
|
|
try:
|
|
cursor.execute("ALTER TABLE projects ADD COLUMN is_backup INTEGER DEFAULT 0")
|
|
except Exception:
|
|
pass # column already exists
|
|
try:
|
|
cursor.execute("ALTER TABLE projects ADD COLUMN original_id TEXT DEFAULT NULL")
|
|
except Exception:
|
|
pass
|
|
|
|
# Bảng Project Backups (snapshot riêng, không lẫn với projects chính)
|
|
cursor.execute("""
|
|
CREATE TABLE IF NOT EXISTS project_backups (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL,
|
|
project_id TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
data_json TEXT NOT NULL,
|
|
size_bytes INTEGER DEFAULT 0,
|
|
created_at REAL NOT NULL,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
""")
|
|
|
|
# Placeholder user for anonymous autosave: projects are saved with
|
|
# user_id='anonymous' when no token is present, so the FK must resolve.
|
|
cursor.execute("SELECT id FROM users WHERE id = 'anonymous'")
|
|
if not cursor.fetchone():
|
|
import secrets as _secrets
|
|
cursor.execute("""
|
|
INSERT OR IGNORE INTO users (id, username, email, hashed_password, role, must_change_password, created_at, is_active)
|
|
VALUES ('anonymous', 'anonymous', 'anonymous@local', ?, 'standard', 0, ?, 0)
|
|
""", (_secrets.token_hex(32), time.time()))
|
|
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
# Tự động khởi tạo DB khi module được import
|
|
init_db()
|