Spaces:
Sleeping
Sleeping
File size: 3,413 Bytes
b584a3f 6a23285 b584a3f | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 | from conexion_bd import ConexionBD
def iniciar_historial_usuario() -> None:
with ConexionBD.instancia().obtener_conexion() as conn:
conn.execute("PRAGMA foreign_keys = ON")
conn.execute(
"""
CREATE TABLE IF NOT EXISTS Usuarios (
id TEXT PRIMARY KEY,
username TEXT UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
session_token TEXT,
created_at TEXT NOT NULL
)
"""
)
conn.execute(
"""
CREATE TABLE IF NOT EXISTS Peliculas (
id TEXT PRIMARY KEY,
titulo TEXT NOT NULL,
genero TEXT
)
"""
)
conn.execute(
"""
CREATE TABLE IF NOT EXISTS Emociones (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT NOT NULL,
texto_analizado TEXT,
emocion TEXT NOT NULL,
valencia TEXT NOT NULL,
analizado_en TEXT NOT NULL,
FOREIGN KEY (user_id) REFERENCES Usuarios(id) ON DELETE CASCADE
)
"""
)
conn.execute(
"""
CREATE INDEX IF NOT EXISTS idx_emociones_user_tiempo
ON Emociones (user_id, analizado_en DESC)
"""
)
conn.execute(
"""
CREATE TABLE IF NOT EXISTS Historial_Peliculas (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT NOT NULL,
pelicula_id TEXT NOT NULL,
emocion_id INTEGER,
valoracion REAL,
texto_sesion TEXT,
visto_en TEXT NOT NULL,
FOREIGN KEY (user_id) REFERENCES Usuarios(id) ON DELETE CASCADE,
FOREIGN KEY (pelicula_id) REFERENCES Peliculas(id) ON DELETE RESTRICT,
FOREIGN KEY (emocion_id) REFERENCES Emociones(id) ON DELETE SET NULL
)
"""
)
conn.execute(
"""
CREATE INDEX IF NOT EXISTS idx_historial_user_tiempo
ON Historial_Peliculas (user_id, visto_en DESC)
"""
)
conn.execute(
"""
CREATE TABLE IF NOT EXISTS Ciclo_Recomendacion (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT NOT NULL,
emocion_pre_id INTEGER NOT NULL,
estrategia INTEGER NOT NULL,
creado_en TEXT NOT NULL,
pelicula_id TEXT,
emocion_post_id INTEGER,
FOREIGN KEY (user_id) REFERENCES Usuarios(id) ON DELETE CASCADE,
FOREIGN KEY (emocion_pre_id) REFERENCES Emociones(id) ON DELETE RESTRICT,
FOREIGN KEY (emocion_post_id) REFERENCES Emociones(id) ON DELETE SET NULL,
FOREIGN KEY (pelicula_id) REFERENCES Peliculas(id) ON DELETE SET NULL
)
"""
)
conn.execute(
"""
CREATE INDEX IF NOT EXISTS idx_ciclo_user_tiempo
ON Ciclo_Recomendacion (user_id, creado_en DESC)
"""
)
conn.commit()
|