Store default times in database

[blinklet]

This one should be implemented after #64.

Currently, default adjudication and move times are hard-coded in the config.py module. Let's make them editable by the administrator and store the values in the database. So each festival can have its own default times.

I have some options for how to store default times in the database. In a python program, they would be best represented in a dictionary, as shown in the example below:

adjudication_time = { 
    Piano: { 
        Ensemble: 1800, # seconds
        Solo, 630,
        Duet, 800,
    },
    Vocal: {
        Ensemble: 1200,
        Solo, 400,
        Duet, 600,
    },
}

But to store that dictionary in a database, I need to store it as a JSON object. SQLAlchemy supports storing JSON objects. I could use the example code below:

from sqlalchemy import JSON
from sqlalchemy.orm import Mapped, mapped_column
from typing import Optional, Dict

class Festival(db.Model):
    __tablename__ = 'festivals'

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    name: Mapped[Optional[str]] = mapped_column(nullable=True)
    seasons: Mapped[List["Season"]] = relationship("Season", foreign_keys=[Season.festival_id])
    adjudication_times: Mapped[Optional[Dict[str, Dict[str, int]]]] = mapped_column(JSON, nullable=True)
    move_times: Mapped[Optional[Dict[str, Dict[str, int]]]] = mapped_column(JSON, nullable=True)

Or, I could organize the data as a table that contains default times for all festivals:

from sqlalchemy.orm import Mapped, mapped_column

class DefaultTime(db.Model):
    __tablename__ = 'default_times'

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    festival_id: Mapped[int] = mapped_column(ForeignKey('festivals.id'))
    default_type: Mapped[str] = mapped_column()
    discipline: Mapped[str] = mapped_column()
    class_type: Mapped[str] = mapped_column()
    seconds: Mapped[int] = mapped_column()

In my opinion, it's better to keep things in an SQL database in a normalized fashion. Adding in nested dictionaries in a nested field to support a single feature will lead to confusion later on. Performance or functionality will likely be the same in either case so the deciding factor is just to use the database in a consistent way in every case.

When processing a large syllabus, I can use SQLAlchemy's caching ability to avoid going back to the DB for the default times for each item in the syllabus. For example:

# Run an initial query to load multiple rows into the session
festival_id = 1  # Replace with the actual festival ID
stmt = select(Season).where(Season.festival_id == festival_id)
results = session.execute(stmt).scalars().all()

# Access a specific row from the cached results
# without launching a new SQL query to the DB
specific_season_id = 2  # Replace with the actual season ID you want to access
specific_season = session.get(Season, specific_season_id)