54 lines
1.7 KiB
Python
54 lines
1.7 KiB
Python
# database/connection.py
|
|
import sqlite3
|
|
|
|
def get_connection():
|
|
return sqlite3.connect("spanish_trainer.db")
|
|
|
|
def init_db():
|
|
conn = get_connection()
|
|
cursor = conn.cursor()
|
|
|
|
# 1. Main Phrases Table
|
|
cursor.execute("""
|
|
CREATE TABLE IF NOT EXISTS phrases (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
text TEXT NOT NULL,
|
|
language TEXT NOT NULL,
|
|
textbook TEXT,
|
|
unit INTEGER,
|
|
source_context TEXT,
|
|
word_type TEXT,
|
|
grammar_note TEXT,
|
|
voice_gender TEXT DEFAULT 'female',
|
|
base_speed REAL DEFAULT 1.0,
|
|
deck_name TEXT DEFAULT 'General',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
""")
|
|
|
|
# 2. Translations Cross-Reference Table
|
|
cursor.execute("""
|
|
CREATE TABLE IF NOT EXISTS translations (
|
|
source_phrase_id INTEGER,
|
|
target_phrase_id INTEGER,
|
|
PRIMARY KEY (source_phrase_id, target_phrase_id),
|
|
FOREIGN KEY (source_phrase_id) REFERENCES phrases(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (target_phrase_id) REFERENCES phrases(id) ON DELETE CASCADE
|
|
)
|
|
""")
|
|
|
|
# 3. Audio Tracks Metadata Table (Required by the glossary cleaner)
|
|
cursor.execute("""
|
|
CREATE TABLE IF NOT EXISTS audio_tracks (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
phrase_id INTEGER,
|
|
file_path TEXT NOT NULL,
|
|
sample_rate INTEGER,
|
|
duration REAL,
|
|
FOREIGN KEY (phrase_id) REFERENCES phrases(id) ON DELETE CASCADE
|
|
)
|
|
""")
|
|
|
|
conn.commit()
|
|
conn.close()
|
|
print("✅ Rich metadata database schema initialized successfully.")
|