def edit_post(self, post_id: str, text: str):
with sql_connection(self._config) as conn:
with conn.cursor() as cur:
cur.execute("UPDATE Posts SET text=%s WHERE id=%s;",
(text, post_id))
return self.get_post(post_id)
def truncate_tables(self):
"""
delete contents of all tables
:return:
"""
with sql_connection(self._config) as conn:
with conn.cursor() as cur:
for table in Database.TABLES:
cur.execute(f"TRUNCATE TABLE {self._config.db_name}.{table};")
def _get_latest_post_from_user(self, username):
with sql_connection(self._config) as conn:
with conn.cursor() as cur:
cur.execute(
"SELECT * FROM Posts "
"WHERE username=%s "
"ORDER BY editedOn DESC LIMIT 1", (username))
row = cur.fetchone()
return _post_from_row(row)
def create_post(self, username: str, text: str, picture: str = None):
with sql_connection(self._config) as conn:
with conn.cursor() as cur:
cur.execute(
"INSERT INTO Posts "
"(username, picture, text) "
"VALUES (%s, %s, %s);", (username, picture, text))
# created post is the latest post
return self._get_latest_post_from_user(username)
def all_posts(self, constraints: QueryConstraints):
with sql_connection(self._config) as conn:
with conn.cursor() as cur:
cur.execute(f"SELECT * FROM Posts "
f"ORDER BY postedOn {constraints.sort_by} "
f"LIMIT {constraints.total} "
f"OFFSET {constraints.first};")
posts = _posts_from_rows(cur)
return self._attach_extra_post_data(posts)
def _attach_extra_post_data(self, posts):
with sql_connection(self._config) as conn:
with conn.cursor() as cur:
for post in posts:
cur.execute(
"SELECT profilePicture FROM Users WHERE username=%s",
(post.username, ))
row = cur.fetchone()
post.profile_picture = row[0]
cur.execute("SELECT tag FROM PostTags WHERE postId=%s",
(post.id, ))
post.tags = flatten(cur.fetchall())
return posts
def feed_posts(self, constraints: QueryConstraints, username: str):
with sql_connection(self._config) as conn:
with conn.cursor() as cur:
cur.execute(
f"SELECT * FROM Posts "
f"WHERE username IN "
f"(SELECT following FROM Follows WHERE follower=%s) "
f"ORDER BY postedOn DESC "
f"LIMIT {constraints.total} "
f"OFFSET {constraints.first};", (username, ))
posts = _posts_from_rows(cur)
return self._attach_extra_post_data(posts)
def update_post_picture(self, post_id: str, picture: str):
image_labels = get_labels(picture)
with sql_connection(self._config) as conn:
with conn.cursor() as cur:
# update post tags
cur.execute("DELETE FROM PostTags WHERE postId=%s;",
(post_id, ))
cur.executemany(
"INSERT INTO PostTags(postId, tag) VALUES (%s, %s);",
[(post_id, tag) for tag in image_labels])
# update posts table
cur.execute("UPDATE Posts SET picture=%s WHERE id=%s;",
(picture, post_id))
return self.get_post(post_id)
def get_post(self, post_id: str):
with sql_connection(self._config) as conn:
with conn.cursor() as cur:
# get main post data
cur.execute("SELECT * FROM Posts WHERE id=%s;", (post_id, ))
row = cur.fetchone()
post = _post_from_row(row)
# get profile pic for associated user
cur.execute(
"SELECT profilePicture FROM Users WHERE username=%s",
(post.username, ))
row = cur.fetchone()
post.profile_picture = row[0]
# get tags for image
cur.execute("SELECT tag FROM PostTags WHERE postId=%s",
(post_id, ))
post.tags = flatten(cur.fetchall())
return post
def search_posts(self, constraints: QueryConstraints, search_string: str):
with sql_connection(self._config) as conn:
with conn.cursor() as cur:
cur.execute(
f"SELECT * FROM Posts "
f"WHERE text LIKE CONCAT('%%', %s, '%%') "
f"ORDER BY postedOn {constraints.sort_by} "
f"LIMIT {constraints.total} "
f"OFFSET {constraints.first};", (search_string, ))
posts_by_text = _posts_from_rows(cur)
# get posts by tags
cur.execute(
"SELECT postId from PostTags WHERE tag LIKE CONCAT('%%', %s, '%%');",
search_string)
post_ids = flatten(cur.fetchall())
post_ids_formatted = ','.join('\'{0}\''.format(id)
for id in post_ids)
post_query = f"SELECT * FROM Posts WHERE id IN ({post_ids_formatted});"
cur.execute(post_query)
posts_by_tag = _posts_from_rows(cur)
posts = list(itertools.chain(posts_by_text, posts_by_tag))
return self._attach_extra_post_data(posts)
def delete_post(self, post_id: str):
with sql_connection(self._config) as conn:
with conn.cursor() as cur:
cur.execute("DELETE FROM Posts WHERE id=%s", (post_id, ))
def drop_tables(self):
with sql_connection(self._config) as conn:
with conn.cursor() as cur:
for table in Database.TABLES:
cur.execute(f"DROP TABLE {self._config.db_name}.{table};")