kobold-ai
53 lines
2.1 KiB
Python
53 lines
2.1 KiB
Python
import os
|
|
from sqlalchemy import create_engine, text
|
|
from dotenv import load_dotenv
|
|
|
|
load_dotenv()
|
|
|
|
DB_USER = os.getenv("DB_USER", "v2_usr_quiz")
|
|
DB_PASS = os.getenv("DB_PASS", "Quiz_Pass_2026_Secure!")
|
|
DB_HOST = os.getenv("DB_HOST", "db.waiswuaschd.de")
|
|
DB_PORT = os.getenv("DB_PORT", "3306")
|
|
DB_NAME = os.getenv("DB_NAME", "v2_db_quiz")
|
|
|
|
DATABASE_URL = f"mysql+pymysql://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
|
|
engine = create_engine(DATABASE_URL)
|
|
|
|
def run_migration():
|
|
with engine.connect() as conn:
|
|
print("Running migrate_v4...")
|
|
|
|
# 1. Add duration_seconds to quiz_sessions
|
|
res = conn.execute(text("SHOW COLUMNS FROM quiz_sessions LIKE 'duration_seconds'")).fetchall()
|
|
if not res:
|
|
print("Adding duration_seconds to quiz_sessions...")
|
|
conn.execute(text("ALTER TABLE quiz_sessions ADD COLUMN duration_seconds INT NULL"))
|
|
conn.commit()
|
|
print("Added duration_seconds column.")
|
|
else:
|
|
print("Column duration_seconds already exists.")
|
|
|
|
# 2. Create leaderboard_entries table
|
|
print("Ensuring leaderboard_entries table exists...")
|
|
conn.execute(text("""
|
|
CREATE TABLE IF NOT EXISTS leaderboard_entries (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nickname VARCHAR(50) NOT NULL,
|
|
score INT NOT NULL,
|
|
total_questions INT NOT NULL DEFAULT 10,
|
|
time_seconds INT NOT NULL,
|
|
difficulty VARCHAR(20) NOT NULL DEFAULT 'all',
|
|
lang VARCHAR(10) NOT NULL DEFAULT 'de',
|
|
session_id VARCHAR(36) NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_leaderboard_score_time (score DESC, time_seconds ASC),
|
|
INDEX idx_leaderboard_diff (difficulty),
|
|
INDEX idx_leaderboard_created (created_at DESC)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
"""))
|
|
conn.commit()
|
|
print("leaderboard_entries table ready.")
|
|
|
|
if __name__ == "__main__":
|
|
run_migration()
|