this repo has no description
edavis.dev
1import logging
2
3import apsw
4import apsw.ext
5
6from . import BaseFeed
7
8class RatioFeed(BaseFeed):
9 FEED_URI = 'at://did:plc:4nsduwlpivpuur4mqkbfvm6a/app.bsky.feed.generator/ratio'
10 SERVE_FEED_QUERY = """
11 with served as (
12 select
13 uri,
14 create_ts,
15 ( unixepoch('now') - create_ts ) as age_seconds,
16 replies,
17 quoteposts,
18 likes,
19 reposts,
20 ( replies + quoteposts ) / ( likes + reposts + 1 ) as ratio,
21 exp( -1 * ( ( unixepoch('now') - create_ts ) / ( 3600.0 * 16 ) ) ) as decay
22 from posts
23 )
24 select
25 *,
26 ( ratio * decay ) as score
27 from served
28 where replies > 15 and ratio > 2.5
29 order by score desc
30 limit :limit offset :offset
31 """
32 DELETE_OLD_POSTS_QUERY = """
33 delete from posts
34 where
35 create_ts < unixepoch('now', '-5 days')
36 """
37
38 def __init__(self):
39 self.db_cnx = apsw.Connection('db/ratio.db')
40 self.db_cnx.pragma('journal_mode', 'WAL')
41 self.db_cnx.pragma('wal_autocheckpoint', '0')
42
43 with self.db_cnx:
44 self.db_cnx.execute("""
45 create table if not exists posts (
46 uri text, create_ts timestamp,
47 replies float, likes float, reposts float, quoteposts float
48 );
49 create unique index if not exists uri_idx on posts(uri);
50 """)
51
52 self.logger = logging.getLogger('feeds.ratio')
53
54 def process_commit(self, commit):
55 if commit['opType'] != 'c':
56 return
57
58 subject_uri = None
59 is_reply = False
60 is_quotepost = False
61
62 if commit['collection'] in {'app.bsky.feed.like', 'app.bsky.feed.repost'}:
63 record = commit.get('record')
64 ts = self.safe_timestamp(record.get('createdAt')).timestamp()
65 try:
66 subject_uri = record['subject']['uri']
67 except KeyError:
68 return
69 elif commit['collection'] == 'app.bsky.feed.post':
70 record = commit.get('record')
71 ts = self.safe_timestamp(record.get('createdAt')).timestamp()
72 if record.get('reply') is not None:
73 is_reply = True
74 try:
75 subject_uri = record['reply']['parent']['uri']
76 except KeyError:
77 return
78
79 # only count non-OP replies
80 if subject_uri.startswith('at://' + commit['did']):
81 return
82
83 elif record.get('embed') is not None:
84 is_quotepost = True
85 t = record['embed']['$type']
86 if t == 'app.bsky.embed.record':
87 try:
88 subject_uri = record['embed']['record']['uri']
89 except KeyError:
90 return
91 elif t == 'app.bsky.embed.recordWithMedia':
92 try:
93 subject_uri = record['embed']['record']['record']['uri']
94 except KeyError:
95 return
96
97 if subject_uri is None:
98 return
99
100 params = {
101 'uri': subject_uri,
102 'ts': ts,
103 'is_reply': int(is_reply),
104 'is_like': int(commit['collection'] == 'app.bsky.feed.like'),
105 'is_repost': int(commit['collection'] == 'app.bsky.feed.repost'),
106 'is_quotepost': int(is_quotepost),
107 }
108
109 self.transaction_begin(self.db_cnx)
110
111 self.db_cnx.execute("""
112 insert into posts(uri, create_ts, replies, likes, reposts, quoteposts)
113 values (:uri, :ts,
114 case when :is_reply then 1 else 0 end,
115 case when :is_like then 1 else 0 end,
116 case when :is_repost then 1 else 0 end,
117 case when :is_quotepost then 1 else 0 end)
118 on conflict(uri)
119 do update set
120 replies = replies + case when :is_reply then 1 else 0 end,
121 likes = likes + case when :is_like then 1 else 0 end,
122 reposts = reposts + case when :is_repost then 1 else 0 end,
123 quoteposts = quoteposts + case when :is_quotepost then 1 else 0 end
124 """, params)
125
126 def delete_old_posts(self):
127 self.db_cnx.execute(self.DELETE_OLD_POSTS_QUERY)
128
129 def commit_changes(self):
130 self.logger.debug('committing changes')
131 self.delete_old_posts()
132 self.transaction_commit(self.db_cnx)
133 self.wal_checkpoint(self.db_cnx, 'RESTART')
134
135 def serve_feed(self, limit, offset, langs):
136 cur = self.db_cnx.execute(self.SERVE_FEED_QUERY, dict(limit=limit, offset=offset))
137 return [row[0] for row in cur]
138
139 def serve_feed_debug(self, limit, offset, langs):
140 bindings = dict(limit=limit, offset=offset)
141 return apsw.ext.format_query_table(
142 self.db_cnx, self.SERVE_FEED_QUERY, bindings,
143 string_sanitize=2, text_width=9999, use_unicode=True
144 )