def select_users_txn(txn, now_ms, renew_at):
txn.execute(
"""
SELECT user_id, expiration_ts_ms FROM email_account_validity
WHERE email_sent = ? AND (expiration_ts_ms - ?) <= ?
""",
(False, now_ms, renew_at),
)
return DatabasePool.cursor_to_dict(txn)
def populate_table_txn(txn: LoggingTransaction,
batch_size: int) -> int:
# Populate the database with the users that are in the users table but not in
# the email_account_validity one.
txn.execute(
"""
SELECT users.name FROM users
LEFT JOIN email_account_validity
ON (users.name = email_account_validity.user_id)
WHERE email_account_validity.user_id IS NULL
LIMIT ?
""",
(batch_size, ),
)
missing_users = DatabasePool.cursor_to_dict(txn)
if not missing_users:
return 0
# Figure out the state of these users in the account_validity table.
# Note that at some point we'll want to get rid of the account_validity table
# and we'll need to get rid of this code as well.
rows = DatabasePool.simple_select_many_txn(
txn=txn,
table="account_validity",
column="user_id",
iterable=tuple([user["name"] for user in missing_users]),
keyvalues={},
retcols=(
"user_id",
"expiration_ts_ms",
"email_sent",
"renewal_token",
"token_used_ts_ms",
),
)
# Turn the results into a dictionary so we can later merge it with the list
# of registered users on the homeserver.
users_to_insert = {}
for row in rows:
users_to_insert[row["user_id"]] = row
# Look for users that are registered but don't have a state in the
# account_validity table, and set a default state for them. This default
# state includes an expiration timestamp close to now + validity period, but
# is slightly randomised to avoid sending huge bursts of renewal emails at
# once.
default_expiration_ts = self._api.current_time_ms() + self._period
for user in missing_users:
if users_to_insert.get(user["name"]) is None:
users_to_insert[user["name"]] = {
"user_id":
user["name"],
"expiration_ts_ms":
self._rand.randrange(
default_expiration_ts -
self._expiration_ts_max_delta,
default_expiration_ts,
),
"email_sent":
False,
"renewal_token":
None,
"token_used_ts_ms":
None,
}
# Insert the users in the table.
DatabasePool.simple_insert_many_txn(
txn=txn,
table="email_account_validity",
values=list(users_to_insert.values()),
)
return len(missing_users)