database/models.py - master - volkovss/eye_tracker

database/models.py
203 строки8 KB
volkovss
Initial commit: Eye Tracker v1.0
03 июн 2026, 13:18
03 июн 2026, 13:18f9d57a2
from sqlalchemy import (
    create_engine, Column, Integer, String, Float,
    Boolean, DateTime, ForeignKey, Text
)
from sqlalchemy.orm import declarative_base, relationship, sessionmaker
from datetime import datetime
import sys, os
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from config import DATABASE_URL

Base = declarative_base()


# Таблица 1: Users 
class User(Base):
    __tablename__ = "users"

    user_id      = Column(Integer, primary_key=True, autoincrement=True)
    name         = Column(String(100), nullable=False)
    email        = Column(String(200), unique=True, nullable=False)
    password_hash= Column(String(255), nullable=False)
    role         = Column(String(20),  nullable=False, default="researcher")
    # roles: admin | researcher | subject
    consent_flag = Column(Boolean, default=False)
    created_at   = Column(DateTime, default=datetime.utcnow)
    is_active    = Column(Boolean, default=True)

    sessions = relationship("Session", back_populates="user")


# Таблица 2: Devices
class Device(Base):
    __tablename__ = "devices"

    device_id         = Column(Integer, primary_key=True, autoincrement=True)
    name              = Column(String(100), nullable=False)
    manufacturer      = Column(String(100))
    model             = Column(String(100))
    camera_resolution = Column(String(20))
    camera_fps        = Column(Integer)
    ir_enabled        = Column(Boolean, default=True)
    cam_left_index    = Column(Integer, default=0)
    cam_right_index   = Column(Integer, default=1)
    notes             = Column(Text)
    created_at        = Column(DateTime, default=datetime.utcnow)

    sessions = relationship("Session", back_populates="device")


# Таблица 3: Sessions
class Session(Base):
    __tablename__ = "sessions"

    session_id     = Column(Integer, primary_key=True, autoincrement=True)
    user_id        = Column(Integer, ForeignKey("users.user_id"),    nullable=False)
    device_id      = Column(Integer, ForeignKey("devices.device_id"))
    calibration_id = Column(Integer, ForeignKey("calibration_data.cal_id"))
    start_ts       = Column(DateTime, default=datetime.utcnow)
    end_ts         = Column(DateTime)
    task_name      = Column(String(200))
    status         = Column(String(20), default="active")
    # status: active | completed | failed
    notes          = Column(Text)

    user             = relationship("User",             back_populates="sessions")
    device           = relationship("Device",           back_populates="sessions")
    gaze_data        = relationship("GazeData",         back_populates="session")
    hardware_config  = relationship("HardwareConfig",   back_populates="session", uselist=False)
    software_config  = relationship("SoftwareConfig",   back_populates="session", uselist=False)
    env_params       = relationship("EnvironmentParams",back_populates="session", uselist=False)
    aggregated_stats = relationship("AggregatedStats",  back_populates="session", uselist=False)
    logs             = relationship("Log",              back_populates="session")


# Таблица 4: HardwareConfig
class HardwareConfig(Base):
    __tablename__ = "hardware_configs"

    hw_id       = Column(Integer, primary_key=True, autoincrement=True)
    session_id  = Column(Integer, ForeignKey("sessions.session_id"), nullable=False)
    cpu_model   = Column(String(200))
    cpu_cores   = Column(Integer)
    ram_mb      = Column(Integer)
    gpu_info    = Column(String(200))

    session = relationship("Session", back_populates="hardware_config")


# Таблица 5: SoftwareConfig
class SoftwareConfig(Base):
    __tablename__ = "software_configs"

    sw_id           = Column(Integer, primary_key=True, autoincrement=True)
    session_id      = Column(Integer, ForeignKey("sessions.session_id"), nullable=False)
    os_name         = Column(String(50))
    os_version      = Column(String(100))
    python_version  = Column(String(20))
    opencv_version  = Column(String(20))
    app_version     = Column(String(20))
    browser         = Column(String(100))
    browser_version = Column(String(50))

    session = relationship("Session", back_populates="software_config")


# ─── Таблица 6: EnvironmentParams 
class EnvironmentParams(Base):
    __tablename__ = "environment_params"

    env_id               = Column(Integer, primary_key=True, autoincrement=True)
    session_id           = Column(Integer, ForeignKey("sessions.session_id"), nullable=False)
    ambient_lux_estimate = Column(Float)
    screen_width_px      = Column(Integer)
    screen_height_px     = Column(Integer)
    screen_dpi           = Column(Float)
    distance_estimate_cm = Column(Float)
    lighting_condition   = Column(String(50))
    # lighting_condition: dark | dim | normal | bright

    session = relationship("Session", back_populates="env_params")


# Таблица 7: CalibrationData
class CalibrationData(Base):
    __tablename__ = "calibration_data"

    cal_id                   = Column(Integer, primary_key=True, autoincrement=True)
    session_id               = Column(Integer, ForeignKey("sessions.session_id"))
    calibration_points_json  = Column(Text)
    polynomial_coeffs_json   = Column(Text)
    calibration_error_metric = Column(Float)
    num_points               = Column(Integer)
    is_valid                 = Column(Boolean, default=True)
    created_at               = Column(DateTime, default=datetime.utcnow)


# Таблица 8: GazeData
class GazeData(Base):
    __tablename__ = "gaze_data"

    gaze_id       = Column(Integer, primary_key=True, autoincrement=True)
    session_id    = Column(Integer, ForeignKey("sessions.session_id"), nullable=False)
    timestamp     = Column(String(30), nullable=False)  # ISO 8601
    frame_index   = Column(Integer)
    x_px          = Column(Float)
    y_px          = Column(Float)
    x_norm        = Column(Float)
    y_norm        = Column(Float)
    confidence    = Column(Float)
    left_pupil_x  = Column(Float)
    left_pupil_y  = Column(Float)
    right_pupil_x = Column(Float)
    right_pupil_y = Column(Float)

    session = relationship("Session", back_populates="gaze_data")


# Таблица 9: AggregatedStats
class AggregatedStats(Base):
    __tablename__ = "aggregated_stats"

    stat_id                  = Column(Integer, primary_key=True, autoincrement=True)
    session_id               = Column(Integer, ForeignKey("sessions.session_id"), nullable=False)
    total_fixations          = Column(Integer)
    avg_fixation_duration_ms = Column(Float)
    total_fixation_time_ms   = Column(Float)
    saccade_count            = Column(Integer)
    avg_saccade_amplitude_px = Column(Float)
    total_data_points        = Column(Integer)
    data_quality_pct         = Column(Float)
    aois_summary_json        = Column(Text)
    heatmap_path             = Column(String(500))
    computed_at              = Column(DateTime, default=datetime.utcnow)

    session = relationship("Session", back_populates="aggregated_stats")


# Таблица 10: Logs 
class Log(Base):
    __tablename__ = "logs"

    log_id     = Column(Integer, primary_key=True, autoincrement=True)
    session_id = Column(Integer, ForeignKey("sessions.session_id"))
    level      = Column(String(10))   # INFO | WARNING | ERROR
    message    = Column(Text)
    module     = Column(String(50))
    ts         = Column(DateTime, default=datetime.utcnow)

    session = relationship("Session", back_populates="logs")


# Утилиты
def init_db():
    """Инициализирует БД и создаёт все таблицы."""
    engine = create_engine(DATABASE_URL, connect_args={"check_same_thread": False})
    Base.metadata.create_all(engine)
    return engine


def get_db_session(engine):
    """Возвращает сессию SQLAlchemy."""
    SessionLocal = sessionmaker(bind=engine)
    return SessionLocal()