Example #1
0
def write_post_to_db(post_model: dict, table='posts'):
    post_model['embed'] = json.dumps(post_model['embed'])
    post_model['file'] = json.dumps(post_model['file'])
    for i in range(len(post_model['attachments'])):
        post_model['attachments'][i] = json.dumps(post_model['attachments'][i])

    columns = post_model.keys()
    data = ['%s'] * len(post_model.values())
    data[list(columns).index('attachments')] = '%s::jsonb[]'  # attachments
    query = "INSERT INTO {table} ({fields}) VALUES ({values})".format(
        table=table, fields=','.join(columns), values=','.join(data))
    if table == 'posts':
        query += """
            ON CONFLICT (id, service)
            DO UPDATE SET {updates}
        """.format(updates=','.join(
            [f'{column}=EXCLUDED.{column}' for column in columns]))

    conn = get_raw_conn()
    try:
        cursor = conn.cursor()
        cursor.execute(query, list(post_model.values()))
        conn.commit()
    finally:
        return_conn(conn)
Example #2
0
def discord_post_exists(server_id, channel_id, post_id):
    conn = get_raw_conn()
    cursor = conn.cursor()
    cursor.execute(
        "SELECT id FROM discord_posts WHERE id = %s AND server = %s AND channel = %s",
        (post_id, server_id, channel_id))
    existing_posts = cursor.fetchall()
    cursor.close()
    return_conn(conn)
    return len(existing_posts) > 0
Example #3
0
def post_flagged(service, artist_id, post_id):
    conn = get_raw_conn()
    cursor = conn.cursor()
    cursor.execute(
        'SELECT id FROM booru_flags WHERE service = %s AND "user" = %s AND id = %s',
        (service, artist_id, post_id))
    existing_flags = cursor.fetchall()
    cursor.close()
    return_conn(conn)
    return len(existing_flags) > 0
Example #4
0
def get_comments_for_posts(service, post_id):
    conn = get_raw_conn()
    cursor = conn.cursor()
    cursor.execute(
        "SELECT id FROM comments WHERE post_id = %s AND service = %s",
        (post_id, service))
    existing_posts = cursor.fetchall()
    cursor.close()
    return_conn(conn)
    return existing_posts
Example #5
0
def get_comment_ids_for_user(service, user_id):
    conn = get_raw_conn()
    cursor = conn.cursor()
    cursor.execute(
        "SELECT id FROM comments WHERE post_id IN (SELECT id FROM posts WHERE \"user\" = %s AND service = %s)",
        (user_id, service))
    existing_comment_ids = cursor.fetchall()
    cursor.close()
    return_conn(conn)
    return existing_comment_ids
Example #6
0
def query_db(query: str, query_args: Query_Args):
    """
    Performs an operation on a database.
    """
    conn = get_raw_conn()
    try:
        cursor = conn.cursor()
        cursor.execute(query, query_args)
        conn.commit()
    finally:
        return_conn(conn)
Example #7
0
def delete_post_flags(service, artist_id, post_id):
    conn = get_raw_conn()
    try:
        cursor = conn.cursor()
        cursor.execute(
            'DELETE FROM booru_flags WHERE service = %s AND "user" = %s AND id = %s',
            (service, artist_id, post_id))
        cursor.close()
        conn.commit()
    finally:
        return_conn(conn)
Example #8
0
def kill_service_keys(key_ids: List[str]):
    conn = get_raw_conn()
    cursor = conn.cursor()
    args_dict = dict(key_ids=key_ids)
    query = """
        UPDATE saved_session_keys
        SET dead = TRUE
        WHERE id = ANY (%(key_ids)s)
    """
    cursor.execute(query, args_dict)
    conn.commit()
    return_conn(conn)
    return True
Example #9
0
def get_service_keys(amount: int) -> List[int]:
    conn = get_raw_conn()
    cursor = conn.cursor()
    args_dict = dict(amount=amount)
    query = """
        SELECT id
        FROM saved_session_keys
        LIMIT %(amount)s
    """
    cursor.execute(query, args_dict)
    keys = cursor.fetchall()
    conn.commit()
    return_conn(conn)
    return [key['id'] for key in keys] if keys else []
Example #10
0
def post_exists(service, artist_id, post_id):
    conn = get_raw_conn()
    cursor = conn.cursor()
    cursor.execute(
        "SELECT id FROM posts WHERE id = %s AND \"user\" = %s AND service = %s",
        (
            post_id,
            artist_id,
            service,
        ))
    existing_posts = cursor.fetchall()
    cursor.close()
    return_conn(conn)
    return len(existing_posts) > 0
Example #11
0
def get_post(service, artist_id, post_id):
    conn = get_raw_conn()
    cursor = conn.cursor()
    cursor.execute(
        "SELECT * FROM posts WHERE id = %s AND \"user\" = %s AND service = %s",
        (
            post_id,
            artist_id,
            service,
        ))
    existing_post = cursor.fetchone()
    cursor.close()
    return_conn(conn)
    return existing_post
Example #12
0
def comment_exists(service, commenter_id, comment_id):
    conn = get_raw_conn()
    cursor = conn.cursor()
    cursor.execute(
        "SELECT id FROM comments WHERE id = %s AND commenter = %s AND service = %s",
        (
            comment_id,
            commenter_id,
            service,
        ))
    existing_posts = cursor.fetchall()
    cursor.close()
    return_conn(conn)
    return len(existing_posts) > 0
Example #13
0
def save_user_to_db(user: User):
    columns = user.keys()
    values = ['%s'] * len(user.values())
    query = """
        INSERT INTO lookup ({fields})
        VALUES ({values})
        ON CONFLICT (id, service)
            DO NOTHING
    """.format(fields=','.join(columns), values=','.join(values))

    conn = get_raw_conn()
    try:
        cursor = conn.cursor()
        cursor.execute(query, list(user.values()))
        conn.commit()
    finally:
        return_conn(conn)