def test_print_created_event(self):
self.logger.prepare()
self.cursor.execute(
Query.from_(self.log_events).select(analytics.Count(
Star())).get_sql())
cnt = self.cursor.fetchone()[0]
self.connection.commit()
self.assertEqual(cnt, 0)
self.logger.print(Level.TRACE, 'msg')
self.connection.commit()
self.cursor.execute(
Query.from_(self.log_events).select(analytics.Count(
Star())).get_sql())
cnt = self.cursor.fetchone()[0]
self.connection.commit()
self.assertEqual(cnt, 1)
self.cursor.execute(
Query.from_(self.log_events).select('level', 'application_id',
'identifier_id',
'message').get_sql())
lvl, appid, idenid, msg = self.cursor.fetchone()
self.connection.commit()
self.assertEqual(lvl, int(Level.TRACE))
self.assertEqual(appid, self.logger.app_id)
self.assertEqual(idenid, self.logger.iden_id)
self.assertEqual(msg, 'msg')
def test_delete_returning_star(self):
q1 = (PostgreSQLQuery.from_(self.table_abc).where(
self.table_abc.foo == self.table_abc.bar).delete().returning(
Star()))
self.assertEqual('DELETE FROM "abc" WHERE "foo"="bar" RETURNING *',
str(q1))
def retrieveAccountInfo(repo: Dolt, account_id: int) -> dict:
government: Table = Table("government")
query: QueryBuilder = Query.from_(government) \
.select(Star()) \
.where(government.twitter_user_id == account_id)
return repo.sql(query=query.get_sql(quote_char=None),
result_format='json')["rows"]
def handle_loan_paid(version, body):
"""Called when we detect a loan was repaid. If there are no more loans unpaid
by the borrower, and the borrower is banned, we unban them.
"""
with LazyIntegrations(logger_iden=LOGGER_IDEN) as itgs:
borrower_username = body['borrower']['username']
borrower_id = body['borrower']['id']
was_unpaid = body['was_unpaid']
itgs.logger.print(Level.TRACE, 'Detected /u/{} repaid a loan',
borrower_username)
if not was_unpaid:
itgs.logger.print(
Level.TRACE,
'Nothing to do about /u/{} repaying a loan - was not unpaid',
borrower_username)
return
info = perms.manager.fetch_info(itgs, borrower_username, RPIDEN,
version)
if not info['borrow_banned']:
itgs.logger.print(
Level.TRACE,
'Nothing to do about /u/{} repaying a loan - not banned',
borrower_username)
return
loans = Table('loans')
itgs.read_cursor.execute(
Query.from_(loans).select(Count(Star())).where(
loans.deleted_at.isnull()).where(
loans.unpaid_at.notnull()).where(
loans.borrower_id == Parameter('%s')).get_sql(),
(borrower_id, ))
(cnt, ) = itgs.read_cursor.fetchone()
if cnt > 0:
itgs.logger.print(
Level.TRACE,
'Nothing to do about /u/{} repaying a loan - still has {} unpaid loans',
borrower_username, cnt)
return
itgs.logger.print(Level.DEBUG,
'Unbanning /u/{} (no more loans unpaid)',
borrower_username)
utils.reddit_proxy.send_request(itgs, RPIDEN, version, 'unban_user', {
'subreddit': 'borrow',
'username': borrower_username
})
perms.manager.flush_cache(itgs, borrower_username.lower())
itgs.logger.print(
Level.INFO, 'Unbanned /u/{} - repaid all outstanding unpaid loans',
borrower_username)
def _select_field_str(self, term):
if 0 == len(self._from):
raise QueryException('Cannot select {term}, no FROM table specified.'
.format(term=term))
if term == '*':
self._select_star = True
self._selects = [Star()]
return
self._select_field(Field(term, table=self._from[0]))
def test_creates_iden_id(self):
self.cursor.execute(
Query.from_(self.log_idens).select(analytics.Count(
Star())).get_sql())
cnt = self.cursor.fetchone()[0]
self.connection.commit()
self.assertEqual(cnt, 0)
self.logger.prepare()
self.cursor.execute(
Query.from_(self.log_idens).select(analytics.Count(
Star())).get_sql())
cnt = self.cursor.fetchone()[0]
self.connection.commit()
self.assertEqual(cnt, 1)
self.cursor.execute(
Query.from_(self.log_idens).select(
self.log_idens.id, self.log_idens.identifier).get_sql())
id_, iden = self.cursor.fetchone()
self.connection.commit()
self.assertEqual(iden, self.logger.identifier)
self.assertEqual(id_, self.logger.iden_id)
def test_creates_app_id(self):
self.cursor.execute(
Query.from_(self.log_apps).select(analytics.Count(
Star())).get_sql())
cnt = self.cursor.fetchone()[0]
self.connection.commit()
self.assertEqual(cnt, 0)
self.logger.prepare()
self.cursor.execute(
Query.from_(self.log_apps).select(analytics.Count(
Star())).get_sql())
cnt = self.cursor.fetchone()[0]
self.connection.commit()
self.assertEqual(cnt, 1)
self.cursor.execute(
Query.from_(self.log_apps).select(self.log_apps.id,
self.log_apps.name).get_sql())
id_, nm = self.cursor.fetchone()
self.connection.commit()
self.assertEqual(nm, self.logger.appname)
self.assertEqual(id_, self.logger.app_id)
def _return_field_str(self, term):
if term == "*":
self._set_returns_for_star()
self._returns.append(Star())
return
if self._insert_table:
self._return_field(Field(term, table=self._insert_table))
elif self._update_table:
self._return_field(Field(term, table=self._update_table))
elif self._delete_from:
self._return_field(Field(term, table=self._from[0]))
else:
raise QueryException("Returning can't be used in this query")
def retrieveTweet(repo: Dolt,
table: str,
tweet_id: str,
hide_deleted_tweets: bool = False,
only_deleted_tweets: bool = False) -> Optional[dict]:
tweets: Table = Table(table)
query: QueryBuilder = Query.from_(tweets) \
.select(Star()) \
.where(tweets.id == tweet_id) \
.limit(1)
if hide_deleted_tweets:
# Filter Out Deleted Tweets
query: QueryBuilder = query.where(tweets.isDeleted == 0)
elif only_deleted_tweets:
# Only Show Deleted Tweets
query: QueryBuilder = query.where(tweets.isDeleted == 1)
return repo.sql(query=query.get_sql(quote_char=None),
result_format='json')["rows"]
def update_stats():
the_time = time.time()
with LazyIntegrations(logger_iden=LOGGER_IDEN) as itgs:
plots = {}
for unit in ('count', 'usd'):
plots[unit] = {}
frequency = 'monthly'
frequency_unit = 'month'
plots[unit][frequency] = {
'title': f'{frequency} {unit}'.title(),
'x_axis': frequency_unit.title(),
'y_axis': unit.title(),
'generated_at': the_time,
'data': {
# Categories will be added later
'series': {} # Will be listified later
}
}
for style in ('lent', 'repaid', 'unpaid'):
plots[unit][frequency]['data']['series'][style] = {}
loans = Table('loans')
moneys = Table('moneys')
principals = moneys.as_('principals')
time_parts = {
'month': DatePart('month', loans.created_at),
'year': DatePart('year', loans.created_at)
}
query = (
Query.from_(loans)
.join(principals).on(principals.id == loans.principal_id)
.select(
time_parts['year'], # Which month are we counting?
time_parts['month'], # Which year are we counting?
Count(Star()), # Total # of Loans Lent In Interval
Sum(principals.amount_usd_cents) # Total USD of Loans Lent In Interval
)
.groupby(time_parts['year'], time_parts['month'])
.where(loans.deleted_at.isnull())
)
sql = query.get_sql()
itgs.logger.print(Level.TRACE, sql)
count_series = plots['count']['monthly']['data']['series']['lent']
usd_series = plots['usd']['monthly']['data']['series']['lent']
itgs.read_cursor.execute(sql)
row = itgs.read_cursor.fetchone()
while row is not None:
count_series[(row[0], row[1])] = row[2]
usd_series[(row[0], row[1])] = row[3] / 100
row = itgs.read_cursor.fetchone()
time_parts = {
'month': DatePart('month', loans.repaid_at),
'year': DatePart('year', loans.repaid_at)
}
query = (
Query.from_(loans)
.join(principals).on(principals.id == loans.principal_id)
.select(
time_parts['year'],
time_parts['month'],
Count(Star()),
Sum(principals.amount_usd_cents)
)
.groupby(time_parts['year'], time_parts['month'])
.where(loans.deleted_at.isnull())
.where(loans.repaid_at.notnull())
)
sql = query.get_sql()
itgs.logger.print(Level.TRACE, sql)
count_series = plots['count']['monthly']['data']['series']['repaid']
usd_series = plots['usd']['monthly']['data']['series']['repaid']
itgs.read_cursor.execute(sql)
row = itgs.read_cursor.fetchone()
while row is not None:
count_series[(row[0], row[1])] = row[2]
usd_series[(row[0], row[1])] = row[3] / 100
row = itgs.read_cursor.fetchone()
time_parts = {
'month': DatePart('month', loans.unpaid_at),
'year': DatePart('year', loans.unpaid_at)
}
query = (
Query.from_(loans)
.join(principals).on(principals.id == loans.principal_id)
.select(
time_parts['year'],
time_parts['month'],
Count(Star()),
Sum(principals.amount_usd_cents)
)
.groupby(time_parts['year'], time_parts['month'])
.where(loans.deleted_at.isnull())
.where(loans.unpaid_at.notnull())
)
sql = query.get_sql()
itgs.logger.print(Level.TRACE, sql)
count_series = plots['count']['monthly']['data']['series']['unpaid']
usd_series = plots['usd']['monthly']['data']['series']['unpaid']
itgs.read_cursor.execute(sql)
row = itgs.read_cursor.fetchone()
while row is not None:
count_series[(row[0], row[1])] = row[2]
usd_series[(row[0], row[1])] = row[3] / 100
row = itgs.read_cursor.fetchone()
# We've now fleshed out all the monthly plots. We first standardize the
# series to a categories list and series list, rather than a series dict.
# So series[k]: {"foo": 3, "bar": 2} -> "categories": ["foo", "bar"],
# series[k]: [3, 2]. This introduces time-based ordering
all_keys = set()
for unit_dict in plots.values():
for plot in unit_dict.values():
for series in plot['data']['series'].values():
for key in series.keys():
all_keys.add(key)
categories = sorted(all_keys)
categories_pretty = [f'{int(year)}-{int(month)}' for (year, month) in categories]
for unit_dict in plots.values():
for plot in unit_dict.values():
plot['data']['categories'] = categories_pretty
for key in tuple(plot['data']['series'].keys()):
dict_fmted = plot['data']['series'][key]
plot['data']['series'][key] = [
dict_fmted.get(cat, 0) for cat in categories
]
# We now map series from a dict to a list, moving the key into name
for unit_dict in plots.values():
for plot in unit_dict.values():
plot['data']['series'] = [
{
'name': key.title(),
'data': val
}
for (key, val) in plot['data']['series'].items()
]
# We can now augment monthly to quarterly. 1-3 -> q1, 4-6 -> q2, etc.
def map_month_to_quarter(month):
return int((month - 1) / 3) + 1
quarterly_categories = []
for (year, month) in categories:
quarter = map_month_to_quarter(month)
pretty_quarter = f'{int(year)}Q{quarter}'
if not quarterly_categories or quarterly_categories[-1] != pretty_quarter:
quarterly_categories.append(pretty_quarter)
for unit, unit_dict in plots.items():
monthly_plot = unit_dict['monthly']
quarterly_plot = {
'title': f'Quarterly {unit}'.title(),
'x_axis': 'Quarter',
'y_axis': unit.title(),
'generated_at': the_time,
'data': {
'categories': quarterly_categories,
'series': []
}
}
unit_dict['quarterly'] = quarterly_plot
for series in monthly_plot['data']['series']:
quarterly_series = []
quarterly_plot['data']['series'].append({
'name': series['name'],
'data': quarterly_series
})
last_year_and_quarter = None
for idx, (year, month) in enumerate(categories):
quarter = map_month_to_quarter(month)
year_and_quarter = (year, quarter)
if year_and_quarter == last_year_and_quarter:
quarterly_series[-1] += series['data'][idx]
else:
last_year_and_quarter = year_and_quarter
quarterly_series.append(series['data'][idx])
# And finally we fill caches
for unit, unit_dict in plots.items():
for frequency, plot in unit_dict.items():
cache_key = f'stats/loans/{unit}/{frequency}'
jsonified = json.dumps(plot)
itgs.logger.print(Level.TRACE, '{} -> {}', cache_key, jsonified)
encoded = jsonified.encode('utf-8')
itgs.cache.set(cache_key, encoded)
itgs.logger.print(Level.INFO, 'Successfully updated loans statistics')
def test__star_tables(self):
star = Star()
self.assertEqual(star.tables_, set())
def test_update_returning_star(self):
q = PostgreSQLQuery.update(self.table_abc).where(
self.table_abc.foo == 0).set("foo", "bar").returning(Star())
self.assertEqual(
'UPDATE "abc" SET "foo"=\'bar\' WHERE "foo"=0 RETURNING *', str(q))
def handle_loan_paid(version, event):
"""Called shortly after a loan is paid.
"""
with LazyItgs(logger_iden=LOGGER_IDEN) as itgs:
itgs.logger.print(
Level.TRACE,
'Detected /u/{} had a payment toward one of the loans he gave out...',
event['lender']['username'])
trusts = Table('trusts')
itgs.read_cursor.execute(
Query.from_(trusts).select(1).where(
trusts.user_id == Parameter('%s')).get_sql(),
(event['lender']['id'], ))
if itgs.read_cursor.fetchone():
itgs.logger.print(
Level.TRACE, '/u/{} already has a trust entry - nothing to do',
event['lender']['username'])
return
loans = Table('loans')
itgs.read_cursor.execute(
Query.from_(loans).select(Count(
Star())).where(loans.lender_id == Parameter('%s')).where(
loans.repaid_at.notnull()).where(
loans.deleted_at.isnull()).get_sql(),
(event['lender']['id'], ))
(loans_compl_as_lender, ) = itgs.read_cursor.fetchone()
if loans_compl_as_lender < THRESHOLD_LOANS:
itgs.logger.print(
Level.DEBUG,
'/u/{} now has {} loans completed as lender, which is below threshold of {}',
event['lender']['username'], loans_compl_as_lender,
THRESHOLD_LOANS)
return
itgs.logger.print(
Level.DEBUG,
'/u/{} reached threshold of {} loans completed as lender, '
'which is above the threshold of {}, queuing trust entry...',
event['lender']['username'], loans_compl_as_lender,
THRESHOLD_LOANS)
itgs.write_cursor.execute(
Query.into(trusts).columns(
trusts.user_id, trusts.status,
trusts.reason).insert(*(Parameter('%s')
for _ in range(3))).get_sql(),
(event['lender']['id'], 'unknown', 'Vetting required'))
delayed_queue.store_event(
itgs,
delayed_queue.QUEUE_TYPES['trust'],
datetime.now(), {'username': event['lender']['username'].lower()},
commit=True)
itgs.logger.print(
Level.INFO,
'Gave /u/{} an explicit unknown status and added to trust queue',
event['lender']['username'])
(subject,
body) = get_letter_response(itgs,
'queue_trust_pm',
username=event['lender']['username'])
utils.reddit_proxy.send_request(itgs, 'lender_queue_trusts', version,
'compose', {
'recipient': '/r/borrow',
'subject': subject,
'body': body
})
itgs.logger.print(
Level.TRACE,
'Successfully alerted modmail of the new entry in trust queue')
def handle_loan_paid(version, event):
"""Handles a loan.paid event (typically sent from utils/paid_utils.py); if
the lender does not have the recheck permission and has an unknown/good
trust status, and they don't have the recheck permission we grant them the
recheck permission.
"""
with LazyItgs(logger_iden=LOGGER_IDEN) as itgs:
lender_id = event['lender']['id']
lender_username = event['lender']['username']
itgs.logger.print(
Level.TRACE,
'Detected /u/{} (id={}) had one of the loans he gave out paid back',
lender_username, lender_id)
trusts = Table('trusts')
itgs.read_cursor.execute(
Query.from_(trusts).select(1).where(
trusts.user_id == Parameter('%s')).where(
trusts.status == Parameter('%s')).get_sql(),
(lender_id, 'bad'))
row = itgs.read_cursor.fetchone()
if row is not None:
itgs.logger.print(
Level.TRACE,
'Ignoring lender /u/{} (id={}); he has bad trust status',
lender_username, lender_id)
return
passwd_auths = Table('password_authentications')
itgs.read_cursor.execute(
Query.from_(passwd_auths).select(passwd_auths.id).where(
passwd_auths.user_id == Parameter('%s')).where(
passwd_auths.human == Parameter('%s')).where(
passwd_auths.deleted == Parameter('%s')).get_sql(),
(lender_id, True, False))
row = itgs.read_cursor.fetchone()
if row is None:
itgs.logger.print(
Level.TRACE,
'Ignoring lender /u/{} (id={}); he has not signed up')
return
(passwd_auth_id, ) = row
passwd_auth_perms = Table('password_auth_permissions')
perms = Table('permissions')
itgs.read_cursor.execute(
Query.from_(passwd_auth_perms).join(perms).on(
perms.id == passwd_auth_perms.permission_id).select(1).where(
passwd_auth_perms.password_authentication_id == Parameter(
'%s')).where(perms.name == Parameter('%s')).get_sql(),
(passwd_auth_id, RECHECK_PERMISSION))
if itgs.read_cursor.fetchone():
itgs.logger.print(
Level.TRACE,
'Ignoring lender /u/{} (id={}); already has recheck permission',
lender_username, lender_id)
return
loans = Table('loans')
itgs.read_cursor.execute(
Query.from_(loans).select(Count(
Star())).where(loans.lender_id == Parameter('%s')).where(
loans.repaid_at.notnull()).where(
loans.deleted_at.isnull()).get_sql(), (lender_id, ))
(num_loans_compl_as_lender, ) = itgs.read_cursor.fetchone()
if num_loans_compl_as_lender < MINIMUM_COMPLETED_LOANS:
itgs.logger.print(
Level.TRACE,
'Ignoring lender /u/{} (has {} completed, threshold is {})',
lender_username, num_loans_compl_as_lender,
MINIMUM_COMPLETED_LOANS)
return
itgs.logger.print(
Level.TRACE, 'Going to grant recheck permission to lender '
'/u/{} (has {} completed, threshold is {})', lender_username,
num_loans_compl_as_lender, MINIMUM_COMPLETED_LOANS)
itgs.read_cursor.execute(
Query.from_(perms).select(
perms.id).where(perms.name == Parameter('%s')).get_sql(),
(RECHECK_PERMISSION, ))
row = itgs.read_cursor.fetchone()
if row is None:
itgs.logger.print(
Level.INFO,
'Detected there is no recheck permission in the database, '
'automatically adding with name=%s', RECHECK_PERMISSION)
itgs.write_cursor.execute(
Query.into(perms).columns(
perms.name, perms.description).insert(
Parameter('%s'),
Parameter('%s')).returning(perms.id).get_sql(),
(RECHECK_PERMISSION,
'Ability to have the LoansBot revisit a comment'))
row = itgs.write_cursor.fetchone()
(recheck_perm_id, ) = row
itgs.write_cursor.execute(
Query.into(passwd_auth_perms).columns(
passwd_auth_perms.password_authentication_id,
passwd_auth_perms.permission_id).insert(
Parameter('%s'), Parameter('%s')).get_sql(),
(passwd_auth_id, recheck_perm_id))
itgs.write_conn.commit()
itgs.logger.print(
Level.INFO,
'Granted /u/{} access to recheck permission - signed up and has '
'{} loans completed as lender (threshold is {})', lender_username,
num_loans_compl_as_lender, MINIMUM_COMPLETED_LOANS)
(subject, body) = get_letter_response(itgs,
'user_granted_recheck_pm',
username=lender_username)
utils.reddit_proxy.send_request(itgs, 'recheck_permission', version,
'compose', {
'recipient': lender_username,
'subject': subject,
'body': body
})
def star(self):
return Star(self)
def test__table_star_tables(self):
table = Table("a")
star = Star(table=table)
self.assertEqual(star.tables_, {table})
def __init__(self, param, alias=None):
is_star = isinstance(param, str) and '*' == param
super(Count, self).__init__('COUNT',
Star() if is_star else param,
alias=alias)
def handle_loan_created(version, body):
"""Called whenever we detect that a loan was just created.
Arguments:
- `version (float)`: The version for communicating with the reddit-proxy
- `body (dict)`: The body of the loans.create event
"""
with LazyIntegrations(logger_iden=LOGGER_IDEN) as itgs:
lender_username = body['lender']['username']
borrower_username = body['borrower']['username']
borrower_id = body['borrower']['id']
itgs.logger.print(Level.TRACE,
'Detected that /u/{} received a loan from /u/{}',
borrower_username, lender_username)
loans = Table('loans')
itgs.read_cursor.execute(
Query.from_(loans).select(Count(Star())).where(
loans.deleted_at.isnull()).where(
loans.lender_id == Parameter('%s')).get_sql(),
(borrower_id, ))
(num_as_lender, ) = itgs.read_cursor.fetchone()
if num_as_lender == 0:
itgs.logger.print(Level.TRACE,
'Nothing to do - /u/{} has no loans as lender',
borrower_username)
return
substitutions = {
'lender_username':
lender_username,
'borrower_username':
borrower_username,
'loan_id':
body['loan_id'],
'loans_table':
loan_format_helper.get_and_format_all_or_summary(
itgs, borrower_username)
}
info = perms.manager.fetch_info(itgs, borrower_username, RPIDEN,
version)
if info['borrow_moderator']:
itgs.logger.print(
Level.DEBUG,
'Ignoring that moderator /u/{} received a loan as lender',
borrower_username)
return
if info['borrow_approved_submitter']:
itgs.logger.print(
Level.DEBUG,
'/u/{} - who previously acted as lender - received a loan, '
'but they are on the approved submitter list. Sending a pm but '
'not taking any other action.', borrower_username)
utils.reddit_proxy.send_request(
itgs, RPIDEN, version, 'compose', {
'recipient':
'/r/borrow',
'subject':
get_response(
itgs, 'approved_lender_received_loan_modmail_pm_title',
**substitutions),
'body':
get_response(
itgs, 'approved_lender_received_loan_modmail_pm_body',
**substitutions)
})
return
itgs.logger.print(
Level.DEBUG,
'/u/{} - who has previously acted as a lender - received a loan. '
'Messaging moderators and ensuring they are not in /r/lenderscamp',
borrower_username)
utils.reddit_proxy.send_request(
itgs, RPIDEN, version, 'compose', {
'recipient':
'/r/borrow',
'subject':
get_response(itgs, 'lender_received_loan_modmail_pm_title', **
substitutions),
'body':
get_response(itgs, 'lender_received_loan_modmail_pm_body', **
substitutions)
})
is_approved = utils.reddit_proxy.send_request(
itgs, RPIDEN, version, 'user_is_approved', {
'subreddit': 'lenderscamp',
'username': borrower_username
})
is_moderator = utils.reddit_proxy.send_request(
itgs, RPIDEN, version, 'user_is_moderator', {
'subreddit': 'lenderscamp',
'username': borrower_username
})
if is_moderator:
itgs.logger.print(
Level.DEBUG,
'Removing /u/{} as contributor on /r/lenderscamp suppressed - they are a mod there',
borrower_username)
return
if is_approved:
utils.reddit_proxy.send_request(itgs, RPIDEN, version,
'disapprove_user', {
'subreddit': 'lenderscamp',
'username': borrower_username
})
itgs.logger.print(
Level.INFO,
'Finished alerting about lender-gone-borrower /u/{} and removing from lenderscamp',
borrower_username)
else:
itgs.logger.print(
Level.INFO,
'Alerted /r/borrow about /u/{} receiving a loan. They were '
'already not a contributor to /r/lenderscamp.',
borrower_username)
