freezing-model

SQLAlchemy model for Freezing Saddles database

This package uses SQLAlchemy to model the database tables for the Freezing Saddles database. It uses alembic to perform database migrations.

Usage

This is intended for use with the other Freezing Saddles projects projects including freezing-web. When used from freezing-web it will retrieve its database configuration from the Flask application configuration. When used from the command line, it will take its configuration from the alembic.ini file and optionally from the environment.

You can override the database URL by specifying a SQLALCHEMY_URL environment variable, for example:

export SQLALCHEMY_URL='mysql+pymysql://user:password@127.0.0.1/freezing?charset=utf8mb4&binary_prefix=true'
PYTHONPATH=$(pwd) alembic current
PYTHONPATH=$(pwd) alembic upgrade head

Coding standards

The freezing-web code is intended to be PEP-8 compliant. Code formatting is done with black and isort and can be linted with flake8. See the .flake8 file and install the dev dependencies to get these tools (pip install -e '.[dev]').

Developing

This project uses setuptools for packaging with a modern pyproject.toml configuration. To get started, create a virtual environment and install the dependencies:

python3.10 -m venv env  # new pythons cause sadness and upset
source env/bin/activate
pip install -e '.[dev]'  # install with linters for development

Linting

This project uses flake8 for linting. To run the linter:

flake8 freezing

This project uses black for code formatting. To format the code:

black freezing

This project uses isort for sorting imports. To sort the imports:

isort freezing

Altering the schema

Alter orm.py to add your new tables or fields to the ORM. This will be used when initializing a database from scratch.

Alter your PYTHONPATH so Python can find freezing:

export PYTHONPATH=$(pwd)

Create a migration script:

alembic revision -m "description of change"

Edit the resulting migration script to upgrade and downgrade.

Check your current version:

alembic current

Apply the changes:

alembic upgrade head

Check your table with mysql:

mysql -u freezing -p -D freezing
mysql> show columns from some_table;

Unapply the changes using the version from the current command:

alembic downgrade <version>

You can then re-upgrade and be done.

Useful Queries

(TODO: This is probably not the best place for this documentation, but I'm not sure where else to put it)

Beyond the model definitions there are a few other useful SQL utilities and queries that can help in operations:

The script bin/registrants.py, given a CSV export from the WordPress registration site for Freezing Saddles, can generate a registrants table in the freezing database that is useful for determining who has registered but has not authorized properly in the database.

These queries can find users who still need to authorize with Strava and generate a list of emails for those users:

select regnum, id, username, name, email, registered_on from registrants r where id not in (select id from athletes); /* Athletes who have never authorized with the freezingsaddles.org site */

select r.regnum, a.id, r.username, r.name, r.email, r.registered_on from registrants r inner join athletes a on (r.id = a.id) where a.team_id is null; /* Athletes that need to re-authorize because we can't read their teams */

select email from registrants where id not in (select id from athletes) union select r.email from registrants r inner join athletes a on (r.id = a.id) where a.team_id is null; /* Emails of users from both of the above groups who need to authorize in Strava */