this repo has no description
edavis.dev
1import logging
2
3import apsw
4import apsw.ext
5
6from . import BaseFeed
7
8class PopularFeed(BaseFeed):
9 FEED_URI = 'at://did:plc:4nsduwlpivpuur4mqkbfvm6a/app.bsky.feed.generator/popular'
10
11 def __init__(self):
12 # use the posts from the most-liked feed for this
13 self.db_cnx = apsw.Connection('db/mostliked.db')
14 self.db_cnx.pragma('foreign_keys', True)
15 self.db_cnx.pragma('journal_mode', 'WAL')
16
17 def process_commit(self, commit):
18 pass
19
20 def commit_changes(self):
21 pass
22
23 def generate_sql(self, limit, offset, langs):
24 bindings = []
25 sql = """
26 select posts.uri, create_ts, likes, lang, unixepoch('now') - create_ts as age_seconds,
27 exp( -1 * ( ( unixepoch('now') - create_ts ) / 1800.0 ) ) as decay,
28 likes * exp( -1 * ( ( unixepoch('now') - create_ts ) / 1800.0 ) ) as score
29 from posts
30 left join langs on posts.uri = langs.uri
31 where
32 """
33 if not '*' in langs:
34 lang_values = list(langs.values())
35 bindings.extend(lang_values)
36 sql += " OR ".join(['lang = ?'] * len(lang_values))
37 else:
38 sql += " 1=1 "
39 sql += """
40 order by score desc
41 limit ? offset ?
42 """
43 bindings.extend([limit, offset])
44 return sql, bindings
45
46 def serve_feed(self, limit, offset, langs):
47 sql, bindings = self.generate_sql(limit, offset, langs)
48 cur = self.db_cnx.execute(sql, bindings)
49 return [row[0] for row in cur]
50
51 def serve_feed_debug(self, limit, offset, langs):
52 sql, bindings = self.generate_sql(limit, offset, langs)
53 return apsw.ext.format_query_table(
54 self.db_cnx, sql, bindings,
55 string_sanitize=2, text_width=9999, use_unicode=True
56 )