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)