def prompt_date_range() -> dict[str, date]:
"""Get the first and last recorded Prompt dates."""
dates = {}
# Get the oldest prompt date
sql = "SELECT DISTINCT `date` FROM prompts ORDER BY `date` ASC LIMIT 1"
with connect_to_db() as db:
dates["oldest"] = db.query(sql).one().date
# Get the newest prompt date
sql = "SELECT DISTINCT `date` FROM prompts ORDER BY `date` DESC LIMIT 1"
with connect_to_db() as db:
dates["newest"] = db.query(sql).one().date
return dates
def exists(*, pid: str, date: str) -> bool:
"""Find an existing prompt."""
sql = """SELECT 1
FROM prompts
WHERE (tweet_id = :tweet_id OR date = :date)"""
with connect_to_db() as db:
return bool(db.query(sql, tweet_id=pid, date=date).first())
def update(permissions: dict) -> bool:
"""Update an API key's permissions."""
# Convert the boolean fields to integers
permissions = __bool_to_int(permissions)
# Update the key
sql = """UPDATE api_keys
SET
`desc` = :desc,
has_api_key = :has_api_key,
has_archive = :has_archive,
has_broadcast = :has_broadcast,
has_host = :has_host,
has_prompt = :has_prompt,
has_settings = :has_settings,
has_subscription = :has_subscription
WHERE token = :token
"""
try:
with connect_to_db() as db:
db.query(sql, **permissions)
return True
except DataError as exc:
print(f"API key update exception: {exc}")
return False
def create(permissions: dict) -> Dict[str, str]:
"""Create an API key with specified permissions."""
# Convert the boolean fields to integers
permissions = __bool_to_int(permissions)
# Generate a new token for the API key
new_token = token_hex()
# Create the record
sql = """INSERT INTO api_keys (
token, `desc`, has_api_key, has_archive,
has_broadcast, has_host, has_prompt,
has_settings, has_subscription
) VALUES (
:token, :desc, :has_api_key, :has_archive,
:has_broadcast, :has_host, :has_prompt,
:has_settings, :has_subscription
)
"""
try:
with connect_to_db() as db:
db.query(sql, token=new_token, **permissions)
return {"token": new_token}
except DataError as exc:
print(f"API key creation exception: {exc}")
return {}
def get_all() -> List[ApiKey]:
"""Get all recorded API key's permissions."""
sql = "SELECT * FROM api_keys"
with connect_to_db() as db:
return [
ApiKey(**__int_to_bool(record.as_dict()))
for record in db.query(sql)
]
def get_latest() -> list[Prompt]:
"""Get the newest prompt."""
# Get the latest date in the database
latest_date_sql = "SELECT date FROM prompts ORDER BY date DESC LIMIT 1"
with connect_to_db() as db:
latest_date = db.query(latest_date_sql).one().date
# Using the latest date, fetch the prompt(s) for the date
sql = """
SELECT prompts.*, writers.handle AS writer_handle
FROM prompts
JOIN writers ON prompts.uid = writers.uid
WHERE date = :latest_date
ORDER BY date DESC
"""
with connect_to_db() as db:
return [Prompt(record) for record in db.query(sql, latest_date=latest_date)]
def get_years() -> list[str]:
"""Get a list of years of recorded Prompts."""
sql = """
SELECT DISTINCT CAST(YEAR(date) AS CHAR)
FROM prompts
ORDER BY date ASC
"""
with connect_to_db() as db:
return flatten_records(db.query(sql).all())
def get_all() -> List[Host]:
"""Get a list of all Hosts."""
sql = """
SELECT writers.uid, handle
FROM writers
ORDER BY handle
"""
with connect_to_db() as db:
return [Host(**host) for host in db.query(sql)]
def create(host_info: dict) -> Optional[Host]:
"""Create a new Host."""
sql = "INSERT INTO writers (uid, handle) VALUES (:uid, :handle)"
try:
with connect_to_db() as db:
db.query(sql, uid=host_info["uid"], handle=host_info["handle"])
return Host(**host_info)
except DataError as exc:
print(exc)
return None
def update(host_info: dict) -> bool:
"""Update a Host's handle."""
sql = "UPDATE writers SET handle = :handle WHERE uid = :uid"
try:
with connect_to_db() as db:
db.query(sql, uid=host_info["uid"], handle=host_info["handle"])
return True
except DataError as exc:
print(exc)
return False
def get(*, uid: str = "", handle: str = "") -> Optional[Host]:
"""Get Host info by either their Twitter ID or handle."""
sql = """
SELECT writers.uid, handle
FROM writers
WHERE writers.uid = :uid OR UPPER(handle) = UPPER(:handle)
"""
with connect_to_db() as db:
r = db.query(sql, uid=uid, handle=handle).one()
return Host(**r) if r is not None else None
def get_by_host(handle: str) -> list[Prompt]:
"""Get a prompt tweet by the Host who prompted it."""
sql = """
SELECT prompts.*, writers.handle AS writer_handle
FROM prompts
JOIN writers ON writers.uid = prompts.uid
WHERE prompts.date <= CURRENT_TIMESTAMP()
AND writers.handle = :handle
"""
with connect_to_db() as db:
return [Prompt(record) for record in db.query(sql, handle=handle)]
def get_by_date(date: str) -> Optional[Host]:
"""Get the Host for the given date."""
sql = """
SELECT writers.uid, handle
FROM writers
JOIN writer_dates ON writer_dates.uid = writers.uid
WHERE writer_dates.date = :date
"""
with connect_to_db() as db:
r = db.query(sql, date=date).one()
return Host(**r) if r is not None else None
def exists_date(uid: str, date: str) -> bool:
"""Find an existing hosting date for the Host."""
sql = """
SELECT 1
FROM writer_dates
WHERE
uid = :uid AND
date = STR_TO_DATE(:date, '%Y-%m-%d')
"""
with connect_to_db() as db:
return bool(db.query(sql, uid=uid, date=date).first())
def get_by_year_month(year: str, month: str) -> List[Host]:
"""Get all the Hosts in a year-month combination."""
sql = """
SELECT writers.uid, handle
FROM writers
JOIN writer_dates ON writer_dates.uid = writers.uid
WHERE
YEAR(writer_dates.date) = :year
AND MONTH(writer_dates.date) = :month
"""
with connect_to_db() as db:
return [Host(**host) for host in db.query(sql, year=year, month=month)]
def get_column_widths(year: int) -> Record:
"""Determine the best column widths for the yearly data."""
sql = """
SELECT
MAX(LENGTH(word)) + 2 AS longest_word,
MAX(LENGTH(handle)) + 2 AS longest_handle,
MAX(LENGTH(handle)) + MAX(LENGTH(tweet_id)) + 29 AS longest_url
FROM prompts
JOIN writers ON writers.uid = prompts.uid
WHERE YEAR(`date`) = :year"""
with connect_to_db() as db:
return db.query(sql, year=year).one()
def search(word: str) -> list[Prompt]:
"""Search for prompts by partial or full word."""
sql = """
SELECT prompts.*, writers.handle AS writer_handle
FROM prompts
JOIN writers ON writers.uid = prompts.uid
WHERE prompts.date <= CURRENT_TIMESTAMP()
AND prompts.word LIKE CONCAT('%', :word, '%')
ORDER BY UPPER(word)
"""
with connect_to_db() as db:
return [Prompt(record) for record in db.query(sql, word=word)]
def get_by_year(year: str) -> List[Host]:
"""Get a list of all Hosts for a given year."""
sql = """
SELECT writers.uid, handle
FROM writers
JOIN writer_dates ON writer_dates.uid = writers.uid
WHERE YEAR(writer_dates.date) = :year
AND DATE_FORMAT(writer_dates.date, '%Y-%m') <=
DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y-%m')
ORDER BY writer_dates.date ASC
"""
with connect_to_db() as db:
return [Host(**host) for host in db.query(sql, year=year)]
def get(year: int) -> list[Record]:
"""Get the full word archive for the given year."""
sql = """
SELECT
`date`,
word,
handle AS `writer_handle`,
tweet_id
FROM prompts
JOIN writers ON writers.uid = prompts.uid
WHERE YEAR(`date`) = :year
ORDER BY word ASC"""
with connect_to_db() as db:
return db.query(sql, year=year)
def create_date(host_info: dict) -> bool:
"""Create a new hosting date."""
sql = """INSERT INTO writer_dates (
uid, date
) VALUES (
:uid, STR_TO_DATE(:date, '%Y-%m-%d 00:00:00')
)"""
try:
with connect_to_db() as db:
db.query(sql, uid=host_info["uid"], date=host_info["date"])
return True
except DataError as exc:
print(exc)
return False
def update(prompt: dict[str, Optional[str]]) -> None:
"""Update an existing prompt."""
sql = """
UPDATE prompts
SET
date = :date,
content = :content,
word = :word,
media = :media,
media_alt_text = :media_alt_text
WHERE tweet_id = :id
"""
with connect_to_db() as db:
db.query(sql, **prompt)
def login(username: str, password: str) -> bool:
"""Atempt to login a user."""
# Query the database for this username
sql = get_sql("user-login")
with connect_to_db() as db:
db.execute(sql, {"username": username.strip()})
user_pass = db.fetchone()
# That username can't be found
if user_pass is None:
return False
# Confirm this is a correct password
return pbkdf2_sha256.verify(password.strip(), user_pass["password"])
def delete(uid: str) -> bool:
"""Delete a Host from the database by their Twitter ID.
Due to database FK constraints, this will only succeed
if the Host does not have any Prompts associated with them.
The presence of any Prompts will stop all deletion so as to
prevent orphaned records or an incomplete record.
"""
sql = "DELETE FROM writers WHERE uid = :uid"
try:
with connect_to_db() as db:
db.query(sql, uid=uid)
return True
except IntegrityError:
return False
def get_date(handle: str) -> List[datetime]:
"""Get the hosting periods for the given Host."""
sql = """
SELECT date
FROM writer_dates
WHERE uid = (
SELECT uid FROM writers
WHERE handle = :handle
)
ORDER BY date DESC
"""
with connect_to_db() as db:
return [
datetime.combine(record["date"], datetime.min.time())
for record in db.query(sql, handle=handle)
]
def get_months(year: str) -> list[str]:
"""Make all Prompts dates for a given year into a unique set.
For some months in 2017, November 2020, and in 2021 and beyond,
there are multiple Hosts per month giving out the prompts.
While the individual dates are stored distinctly,
we need a unique month list in order to correctly display
the year browsing page.
"""
sql = """
SELECT DISTINCT DATE_FORMAT(date, '%m')
FROM prompts
WHERE YEAR(date) = :year AND
:year <= YEAR(CURRENT_TIMESTAMP())
ORDER BY MONTH(date) ASC
"""
with connect_to_db() as db:
return flatten_records(db.query(sql, year=year).all())
def create(prompt: dict[str, Optional[str]]) -> bool:
"""Create a new prompt."""
sql = """
INSERT INTO prompts (
tweet_id, date, uid, content, word, media, media_alt_text
)
VALUES (
:id, :date, :uid, :content, :word, :media, :media_alt_text
)
"""
try:
with connect_to_db() as db:
db.query(sql, **prompt)
return True
# A prompt with this ID already exists
except IntegrityError as exc:
print(f"Prompt creation exception: {exc}")
print(prompt)
return False
def email_create(addr: str) -> bool:
"""Add a subscription email address."""
try:
sql = "INSERT INTO emails (email) VALUES (:addr)"
with connect_to_db() as db:
db.query(sql, addr=addr.lower())
return True
# That address aleady exists in the database.
# However, to prevent data leakage, pretend it added
except IntegrityError as exc:
print(f"New subscription exception: {exc}")
print(addr)
return True
# An error occurred trying to record the email
except DBAPIError as exc:
print(f"New subscription exception: {exc}")
print(addr)
return False
def get_info(username: str) -> User:
"""Get the user's information."""
sql = get_sql("user-fetch-info")
# Start by getting the user's account info
with connect_to_db() as db:
db.execute(sql, {"username": username})
user_info = convert_int_to_bool(dict(db.fetchone()))
# Get the user's token permissions
token_perms: dict = api.get("api-key",
user_token=False,
params={"token": user_info["api_token"]})
# Store the permissions in a dataclass too
token_perms = {
k: v
for k, v in token_perms.items() if k.startswith("has_")
}
token = Token(user_info["api_token"], **token_perms)
return User(username, **user_info), token
def get_by_date(date: str, *, date_range: bool = False) -> list[Prompt]:
"""Get prompts by a single date or in a date range."""
# Base query info
sql = """
SELECT prompts.*, writers.handle as writer_handle
FROM prompts
JOIN writers ON prompts.uid = writers.uid"""
# Use the proper filter depending on if
# we want a date range or single date
if date_range:
sql = f"""{sql}
WHERE DATE_FORMAT(prompts.date, '%Y-%m') = :date
AND prompts.date <= CURRENT_TIMESTAMP()
ORDER BY prompts.date ASC"""
else:
sql = f"""{sql}
WHERE prompts.date = STR_TO_DATE(:date, '%Y-%m-%d')
AND STR_TO_DATE(:date, '%Y-%m-%d') <= CURRENT_TIMESTAMP()"""
# Finally perform the query
with connect_to_db() as db:
return [Prompt(record) for record in db.query(sql, date=date)]
def delete(pid: str) -> Literal[True]:
"""Delete an existing prompt."""
sql = "DELETE FROM prompts WHERE tweet_id = :id"
with connect_to_db() as db:
db.query(sql, **{"id": pid})
return True
