Set up basic database with SQLAlchemy

[ ] Simplified interactions with the database
[ ] Easy conversions from tables in the database to the proper Python objects (e.g. Players, Matches, RatingPeriods)
[ ] Some sort of way to manage migrations (Alembic?)

SQL representation of the database structure


PRAGMA foreign_keys = off;
BEGIN TRANSACTION;

CREATE TABLE guild_settings (
    id     BIGINT PRIMARY KEY ON CONFLICT ROLLBACK,
    prefix TEXT   NOT NULL
                  DEFAULT [;]
);

CREATE TABLE matches (
    id            INTEGER  PRIMARY KEY ON CONFLICT ROLLBACK,
    rating_period INTEGER  REFERENCES rating_periods (id),
    player1       BIGINT   REFERENCES players (id) 
                           NOT NULL,
    player2       BIGINT   REFERENCES players (id) 
                           NOT NULL,
    in_progress   BOOLEAN  NOT NULL,
    completed     BOOLEAN  NOT NULL,
    player1_score INTEGER,
    player2_score INTEGER,
    start_time    DATETIME,
    end_time      DATETIME
);

CREATE TABLE players (
    id               BIGINT PRIMARY KEY ON CONFLICT ROLLBACK,
    display_name     TEXT   UNIQUE ON CONFLICT ROLLBACK,
    rating           DOUBLE NOT NULL,
    rating_deviation DOUBLE NOT NULL,
    volatility       DOUBLE NOT NULL
);

CREATE TABLE rating_periods (
    id         INTEGER  PRIMARY KEY ON CONFLICT ROLLBACK,
    start_time DATETIME NOT NULL
);

COMMIT TRANSACTION;
PRAGMA foreign_keys = on;

mazziechai / DonguriGaeru

Set up basic database with SQLAlchemy #1

SQL representation of the database structure