mazziechai / dongurigaeru Goto Github PK

• View all the matches played by a player or between two players
• Search and sort matches by date
• Recent matches list
• Administrative command for modifying player information

I also had the idea of allowing raw SQL queries through the Discord bot (whitelisted to only us), but I'm on the fence about allowing this. After all, I can just pull up the PostgreSQL shell and run commands through that. It would be a convenient option, but potentially unsafe.

• Pull all strings from an external file
• Pages with categories of commands in the main help command
• Full documentation for every command a user can run
• Automatically send help command on an invalid command

Define and demonstrate an interface between the Discord Bot and the application database. According to the MLP specification, the Discord Bot must support commands to:

1. Delete a match by match id.
2. Create a match by players (either player id, player name, or player discord handle) and their scores.
3. Fix a match score by match id, the player (either by player id, player name, or player discord handle), and their score.

The MLP specification outlines a streamlined interface for match creation for all users, and a more detailed functional interface for Discord moderators (white listed). The Discord Bot also performs some emoji reaction behavior (handshakes).

The Discord Bot also pokes users when not registered with the application; this functionality is out of scope for this issue given that application registration is not currently implemented.

• 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;

Develop some methodology for quantifying the effectiveness of the ranking system algorithm (in the MLP specification, currently proposed as Glicko2, potentially with some TBD modifications).

With Hiku's World Ranking as a benchmark, run the initial proposed algorithm against the test database and compare results to Hiku's rating system. I propose this to be done within a Jupyter Notebook with some plots and metrics to visualize rating distribution and how well the relative order and uncertainty correlates between the two systems.

Ultimately our own rating system goals will need to be defined with corresponding metrics to quantify algorithm effectiveness, but creating a simple visualization to compare systems and get a feel for the data will be a great first step.

Create PostgreSQL "materialized views" that are updated on a timed-basis to be displayed on a webpage as a table. According the the MLP specification, these views are generally:

1. Skill Ranking Table
2. Activity Ranking Table
3. Most-Improved Ranking Table
4. Handshake/Sportsmanship Ranking Table

Use the Hiku's World Ranking test database for generating these views. For now, skill ratings and handshakes can be generated randomly. Create a barebones Flask application to display these views on a webpage as a table.

With the Discord bot, certain planned features as defined in the Minimum Lovable Product document inherently require registration (handshaking, match submission, etc). However, registration is defined to be done only through the website. I suggest a method of registration through the website. This would involve no passwords or emails; all is linked to the Discord account. If a player wishes to sign in on the website after registering through the Discord bot, they can use their Discord account to sign in with Discord's OAuth and create a proper account with an email and password. With this, both ways can be used for registration and on the website we could have the option to sign in through Discord or email and password.

The reason why I bring this up is because I believe the website would come along slower than the Discord bot and would bottleneck development if we wait on the website to support registration in the future. This way, the Discord bot and website can be developed separately and then after proper support between the two can be implemented. After all, they both interact with the same database.

• Linking a Discord account to a player, either by a user or by an administrator
• Submitting matches with Discord accounts
  • Users don't have to specify they're a player in a match they're submitting
• Players can change their registered name