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

async def init_db(pg):
    queries = [
        "CREATE TABLE IF NOT EXISTS \
            guild (\
                id SERIAL PRIMARY KEY, \
                guild_id BIGINT UNIQUE NOT NULL \
            )\
        ",
        "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, \
            )\
        ",
        "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\
            )\
        ",
        "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)