Sample queries

sample.sql

/* The proposed tables */

    /* multi-table */
        CREATE TABLE IF NOT EXISTS attr_table(
            id SERIAL PRIMARY KEY,
            everything /* pretend this expands to the 20 or so player fields */
        );

        CREATE TABLE IF NOT EXISTS player_table(
            id SERIAL PRIMARY KEY,
            player_uuid varchar(36),
            curr_attr_id int
        );

        CREATE TABLE IF NOT EXISTS history_table(
            id SERIAL PRIMARY KEY,
            player_id int,
            attr_id int,
            captured_at timestamp
        );

    /* single table */
        CREATE TABLE IF NOT EXISTS player_table(
            id SERIAL PRIMARY KEY,
            uuid varchar(36),
            validUntil timestamp,
            everything /* pretend this expands to the 20 or so player fields */
        );


/* Insert new player data for player 1234 */

    /* multi-table */
        new_attr = INSERT INTO attr_table (everything)
                       VALUES (everything) 
                       RETURNING id;

        p_id = INSERT INTO player_table (player_uuid, curr_attr_id)
                    VALUES (1234, new_attr)
                    ON CONFLICT(player_uuid)
                    DO UPDATE
                        SET curr_attr_id = new_attr
                    RETURNING id;

        INSERT INTO history_table (player_id, attr_id, capturedAt)
            VALUES (p_id, new_attr, theCorrectTimestamp);

    /* Single table */
        UPDATE player_table
            SET validUntil = theCorrectTimestamp
            WHERE
                uuid = 1234 AND
                validUntil = Null;

        INSERT INTO player_table (everything)
            VALUES (everything);


/* Current player data for player 1234 */

    /* multi-table */
        SELECT * 
            FROM player_table player
            INNER JOIN attr_table attr
                ON player.curr_attr_id = attr.id
            WHERE player.uuid = 1234;

    /* single table */
        SELECT *
            FROM player_table
            WHERE
                uuid = 1234 AND 
                validUntil = Null;


/* player data from timestamp 987654321 for player 1234 */

    /* multi-table */
        SELECT * 
            FROM player_table player
            INNER JOIN history_table hist
                ON player.id = hist.player_id
            INNER JOIN attr_table attr
                ON hist.attr_id = attr.id
            WHERE
                player.uuid = 1234 AND
                hist.capturedAt < 987654321
            ORDER BY hist.capturedAt DESC
            LIMIT 1;

    /* single table */
        SELECT * 
            FROM player_table player
            WHERE
                uuid = 1234 AND
                validUntil > 987654321
            ORDER BY hist.capturedAt ASC
            LIMIT 1;