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)
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
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
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
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
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)
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)
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
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 []
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
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
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
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)