def _generate_cache_sqls(cls, accounts, block_date=None): if not block_date: block_date = get_adapter().head_time() sqls = [] for account in get_adapter().get_accounts(accounts): values = { 'name': account['name'], 'proxy': account['proxy'], 'post_count': account['post_count'], 'reputation': rep_log10(account['reputation']), 'proxy_weight': amount(account['vesting_shares']), 'vote_weight': amount(account['vesting_shares']) + amount(account['received_vesting_shares']) - amount(account['delegated_vesting_shares']), 'kb_used': int(account['lifetime_bandwidth']) / 1e6 / 1024, 'active_at': account['last_bandwidth_update'], 'cached_at': block_date, **cls._safe_account_metadata(account) } update = ', '.join([k + " = :" + k for k in values.keys()][1:]) sql = "UPDATE hive_accounts SET %s WHERE name = :name" % (update) sqls.append([(sql, values)]) return sqls
def get_post_stats(cls,post): net_rshares_adj = 0 neg_rshares = 0 total_votes = 0 up_votes = 0 for vote in post['active_votes']: if vote['percent'] == 0: continue total_votes += 1 rshares = int(vote['rshares']) sign = 1 if vote['percent'] > 0 else -1 if sign > 0: up_votes += 1 if sign < 0: neg_rshares += rshares # For graying: sum rshares, but ignore neg rep users and dust downvotes neg_rep = str(vote['reputation'])[0] == '-' if not (neg_rep and sign < 0 and len(str(rshares)) < 11): net_rshares_adj += rshares # take negative rshares, divide by 2, truncate 10 digits (plus neg sign), # and count digits. creates a cheap log10, stake-based flag weight. # result: 1 = approx $400 of downvoting stake; 2 = $4,000; etc flag_weight = max((len(str(neg_rshares / 2)) - 11, 0)) allow_delete = post['children'] == 0 and int(post['net_rshares']) <= 0 has_pending_payout = amount(post['pending_payout_value']) >= 0.02 author_rep = rep_log10(post['author_reputation']) gray_threshold = -9999999999 low_value_post = net_rshares_adj < gray_threshold and author_rep < 65 gray = not has_pending_payout and (author_rep < 1 or low_value_post) hide = not has_pending_payout and (author_rep < 0) return { 'hide': hide, 'gray': gray, 'allow_delete': allow_delete, 'author_rep': author_rep, 'flag_weight': flag_weight, 'total_votes': total_votes, 'up_votes': up_votes }
def generate_cached_post_sql(pid, post, updated_at): if not post['author']: raise Exception("ERROR: post id {} has no chain state.".format(pid)) md = None try: md = json.loads(post['json_metadata']) if not isinstance(md, dict): md = {} except json.decoder.JSONDecodeError: pass thumb_url = '' if md and 'image' in md: thumb_url = safe_img_url(first(md['image'])) or '' md['image'] = [thumb_url] # clean up tags, check if nsfw tags = [post['category']] if md and 'tags' in md and isinstance(md['tags'], list): tags = tags + md['tags'] tags = set(list(map(lambda str: (str or '').strip('# ').lower()[:32], tags))[0:5]) tags.discard('') is_nsfw = int('nsfw' in tags) # payout date is last_payout if paid, and cashout_time if pending. is_paidout = (post['cashout_time'][0:4] == '1969') payout_at = post['last_payout'] if is_paidout else post['cashout_time'] # get total rshares, and create comma-separated vote data blob rshares = sum(int(v['rshares']) for v in post['active_votes']) csvotes = "\n".join(map(vote_csv_row, post['active_votes'])) payout_declined = False if amount(post['max_accepted_payout']) == 0: payout_declined = True elif len(post['beneficiaries']) == 1: benny = first(post['beneficiaries']) if benny['account'] == 'null' and int(benny['weight']) == 10000: payout_declined = True full_power = int(post['percent_steem_dollars']) == 0 # total payout (completed and/or pending) payout = sum([ amount(post['total_payout_value']), amount(post['curator_payout_value']), amount(post['pending_payout_value']), ]) # total promotion cost promoted = amount(post['promoted']) # trending scores timestamp = parse_time(post['created']).timestamp() hot_score = score(rshares, timestamp, 10000) trend_score = score(rshares, timestamp, 480000) # TODO: add Posts.get_post_stats fields values = collections.OrderedDict([ ('post_id', '%d' % pid), ('author', "%s" % post['author']), ('permlink', "%s" % post['permlink']), ('title', "%s" % post['title']), ('preview', "%s" % post['body'][0:1024]), ('body', "%s" % post['body']), ('img_url', "%s" % thumb_url), ('payout', "%f" % payout), ('promoted', "%f" % promoted), ('payout_at', "%s" % payout_at), ('updated_at', "%s" % updated_at), ('created_at', "%s" % post['created']), ('rshares', "%d" % rshares), ('votes', "%s" % csvotes), ('json', "%s" % json.dumps(md)), ('is_nsfw', "%d" % is_nsfw), ('is_paidout', "%d" % is_paidout), ('sc_trend', "%f" % trend_score), ('sc_hot', "%f" % hot_score), #('payout_declined', "%d" % int(payout_declined)), #('full_power', "%d" % int(full_power)), ]) fields = values.keys() # Multiple SQL statements are generated for each post sqls = [] # Update main metadata in the hive_posts_cache table cols = ', '.join(fields) params = ', '.join([':'+k for k in fields]) update = ', '.join([k+" = :"+k for k in fields][1:]) sql = "INSERT INTO hive_posts_cache (%s) VALUES (%s) ON DUPLICATE KEY UPDATE %s" sqls.append((sql % (cols, params, update), values)) # update tag metadata only for top-level posts if post['depth'] == 0: sql = "DELETE FROM hive_post_tags WHERE post_id = :id" sqls.append((sql, {'id': pid})) if tags: sql = "INSERT IGNORE INTO hive_post_tags (post_id, tag) VALUES " params = {} vals = [] for i, tag in enumerate(tags): vals.append("(:id, :t%d)" % i) params["t%d"%i] = tag sqls.append((sql + ','.join(vals), {'id': pid, **params})) return sqls