def authenticate(username: str, password: str):
# Remove whitespace and lowercase.
username = username.strip().lower()
errors = Account.validate(username, password)
if errors:
return {'errors': errors, 'username': username}
rows = db.execute_query(
"""
SELECT * FROM Account WHERE username = %(username)s;
""", {'username': username})
try:
row = rows.pop(0)
stored_hash = row['password']
if pw.matches(password, stored_hash):
# Successful authentication.
account = Account(row['id'], row['username'],
row['display_name'], row['admin'])
return {'user': account}
except:
pass
return {
'errors': ["Incorrect username or password."],
'username': username
}
def find_by_id(uid: int) -> 'Thread':
rows = db.execute_query(
"""
SELECT
Thread.*,
Account.username, Account.display_name
FROM Thread
INNER JOIN Account ON Account.id = Thread.author_id
AND Thread.id = %(id)s;
""", {'id': uid})
try:
row = rows.pop(0)
account = Account(row['author_id'], row['username'],
row['display_name'])
tags = Tag.find_by_thread_id(row['id'])
responses = Response.find_by_thread_id(row['id'])
thread = Thread(row['id'],
account,
tags,
row['title'],
row['created'],
responses=responses)
return thread
except:
return None
def find_all() -> List['Thread']:
rows = db.execute_query("""
SELECT
Thread.*,
Account.username, Account.display_name,
COUNT(Response.id) as response_count,
MAX(Response.created) as last_active
FROM Thread
INNER JOIN Account ON Account.id = Thread.author_id
LEFT JOIN Response ON Response.thread_id = Thread.id
GROUP BY Thread.id, Account.username, Account.display_name
ORDER BY last_active DESC;
""")
result = []
for row in rows:
account = Account(row['author_id'], row['username'],
row['display_name'])
tags = Tag.find_by_thread_id(row['id'])
thread = Thread(row['id'], account, tags, row['title'],
row['created'], row['last_active'],
row['response_count'])
result.append(thread)
return result
def find_by_author_id(author_id: int) -> List['Thread']:
rows = db.execute_query(
"""
SELECT
Thread.id, Thread.title, Thread.created,
COUNT(Response.id) as response_count,
MAX(Response.created) as last_active
FROM Thread
INNER JOIN Account ON Account.id = Thread.author_id
AND Thread.author_id = %(author_id)s
LEFT JOIN Response ON Response.thread_id = Thread.id
GROUP BY Thread.id
ORDER BY Thread.created DESC
LIMIT 5;
""", {'author_id': author_id})
result = []
for row in rows:
tags = Tag.find_by_thread_id(row['id'])
thread = Thread(row['id'], None, tags, row['title'],
row['created'], row['last_active'],
row['response_count'])
result.append(thread)
return result
def username_exists(username: str) -> bool:
rows = db.execute_query(
"""
SELECT id FROM Account WHERE username = %(username)s;
""", {'username': username})
try:
row = rows.pop(0)
return 'id' in row
except:
return False
def author_for_thread(uid: int) -> int:
rows = db.execute_query(
"""
SELECT author_id FROM Thread
WHERE id = %(id)s;
""", {'id': uid})
try:
row = rows.pop(0)
return row['author_id']
except:
return None
def find_all() -> List['Tag']:
rows = db.execute_query("""
SELECT Tag.*, COUNT(ThreadTag.tag_id) as count FROM Tag
LEFT JOIN ThreadTag ON ThreadTag.tag_id = Tag.id
GROUP BY Tag.id
ORDER BY count DESC, title;
""")
result = []
for row in rows:
tag = Tag(row['id'], row['title'], row['count'])
result.append(tag)
return result
def find_by_thread_id(thread_id: int) -> List['Tag']:
rows = db.execute_query(
"""
SELECT Tag.* FROM Tag
INNER JOIN ThreadTag ON ThreadTag.thread_id = %(thread_id)s
AND ThreadTag.tag_id = Tag.id
ORDER BY title;
""", {'thread_id': thread_id})
result = []
for row in rows:
tag = Tag(row['id'], row['title'])
result.append(tag)
return result
def find_by_title(title: str) -> 'Tag':
rows = db.execute_query(
"""
SELECT *, (
SELECT COUNT(*) FROM ThreadTag
WHERE ThreadTag.tag_id = Tag.id
) AS count FROM Tag
WHERE title = %(title)s;
""", {'title': title})
try:
row = rows.pop(0)
tag = Tag(row['id'], row['title'], row['count'])
return tag
except:
return None
def find_by_id(uid: int) -> 'Account':
rows = db.execute_query(
"""
SELECT id, username, display_name, admin FROM Account WHERE id = %(id)s;
""", {'id': uid})
try:
row = rows.pop(0)
# Deferred in order to prevent circular imports.
from src.models.thread import Thread
threads = Thread.find_by_author_id(row['id'])
account = Account(row['id'], row['username'], row['display_name'],
row['admin'], threads)
return account
except:
return None
def find_by_id(uid: int) -> 'Tag':
rows = db.execute_query(
"""
SELECT *, (
SELECT COUNT(*) FROM ThreadTag
WHERE ThreadTag.tag_id = %(id)s
) AS count FROM Tag
WHERE id = %(id)s;
""", {'id': uid})
try:
row = rows.pop(0)
# Deferred in order to prevent circular imports.
from src.models.thread import Thread
threads = Thread.find_by_tag_id(row['id'])
tag = Tag(row['id'], row['title'], row['count'], threads)
return tag
except:
return None
def find_by_thread_id(thread_id: int) -> List['Response']:
rows = db.execute_query(
"""
SELECT
Response.*,
Account.username, Account.display_name
FROM Response
INNER JOIN Account ON Account.id = Response.author_id
AND Response.thread_id = %(thread_id)s
ORDER BY created ASC;
""", {'thread_id': thread_id})
result = []
for row in rows:
account = Account(row['author_id'], row['username'],
row['display_name'])
response = Response(row['id'], account, row['content'],
row['created'])
result.append(response)
return result