iris-discord-bot/bot/query/initialise_database.py

async def check_db(pg):
    await pg.fetchval("SELECT * FROM \"user\"")

async def init_db(pg):
    queries = [
        "CREATE TABLE IF NOT EXISTS \
            guild (\
                id SERIAL PRIMARY KEY, \
                guild_id BIGINT UNIQUE NOT NULL, \
                report_deleted BOOL DEFAULT FALSE, \
                report_edited BOOL DEFAULT FALSE\
            )\
        ",
        "CREATE TABLE IF NOT EXISTS \
            channel (\
                id SERIAL PRIMARY KEY, \
                channel_id BIGINT UNIQUE NOT NULL, \
                guild BIGINT REFERENCES guild (guild_id), \
                interact BOOL DEFAULT FALSE, \
                games BOOL DEFAULT FALSE\
            )\
        ",
        "ALTER TABLE guild \
            ADD COLUMN output_channel BIGINT REFERENCES channel (channel_id) \
        ",
        "CREATE TABLE IF NOT EXISTS \
            settings (\
                id SERIAL PRIMARY KEY, \
                crew_channel_id BIGINT UNIQUE NOT NULL\
            )\
        ",
#        "CREATE TABLE IF NOT EXISTS \
#            channel_settings (\
#                id SERIAL PRIMARY KEY, \
#                channel BIGINT UNIQUE NOT NULL REFERENCES channel (channel_id), \
#                guild BIGINT REFERENCES guild (guild_id)\
#            )\
#        ",
        "CREATE TABLE IF NOT EXISTS \
            \"user\" (\
                id SERIAL PRIMARY KEY, \
                user_id BIGINT UNIQUE NOT NULL, \
                ignore BOOL DEFAULT FALSE, \
                level INT DEFAULT 0, \
                xp_spent INT DEFAULT 0, \
                invites_created INT DEFAULT 0, \
                integrations_created INT DEFAULT 0, \
                member_updated INT DEFAULT 0, \
                user_updated INT DEFAULT 0, \
                member_banned INT DEFAULT 0, \
                member_unbanned INT DEFAULT 0, \
                presence_updated INT DEFAULT 0, \
                messages_edited INT DEFAULT 0, \
                messages_deleted INT DEFAULT 0, \
                reacted INT DEFAULT 0, \
                events_created INT DEFAULT 0, \
                events_joined INT DEFAULT 0, \
                threads_created INT DEFAULT 0, \
                threads_joined INT DEFAULT 0, \
                ability_points_spent INT DEFAULT 0, \
                coin INT DEFAULT 0, \
                karma INT DEFAULT 0, \
                defence_skill INT DEFAULT 0, \
                attack_skill  INT DEFAULT 0, \
                stealth_skill INT DEFAULT 0, \
                perception_skill INT DEFAULT 0, \
                theft_skill INT DEFAULT 0, \
                created TIMESTAMP NOT NULL DEFAULT now()\
            )\
        ",
        "CREATE TABLE IF NOT EXISTS \
            guild_access_token (\
                id SERIAL PRIMARY KEY, \
                guild BIGINT REFERENCES guild (guild_id), \
                \"user\" BIGINT NOT NULL REFERENCES \"user\" (user_id), \
                token varchar[40] UNIQUE NOT NULL, \
                created TIMESTAMP NOT NULL DEFAULT now()\
            )\
        ",
        "CREATE TABLE IF NOT EXISTS \
            channel_user (\
                id SERIAL PRIMARY KEY, \
                channel BIGINT NOT NULL REFERENCES channel (channel_id), \
                \"user\" BIGINT NOT NULL REFERENCES \"user\" (user_id), \
                total_messages BIGINT DEFAULT 1, \
                UNIQUE (channel, \"user\")\
            )\
        ",
    ]
    for query in queries:
        await pg.execute(query)