friendship ended with social-app. php is my new best friend
veryroundbird.house
1<?php
2$db = new SQLite3(DB_LOCATION, SQLITE3_OPEN_READWRITE);
3$db->exec("pragma journal_mode=wal2;");
4$db->exec("pragma synchronous=normal;");
5
6/* TABLE OF CONTENTS ==========
7I. Cleanup
8II. Cache Requests
9III. Cache Updates
10IV. User Ban/Suspension Lookup
11V. User Ban/Susppension Management
12VI. Create Tables If Needed
13*/
14
15/* I. CLEANUP */
16
17function deleteExpired(string $table): void {
18 global $db;
19 $db->exec('delete from '.$table.' where expires <= '.strtotime('now').';');
20}
21
22/* II. CACHE REQUESTS */
23
24function requestUserCache(string $value, string $field = "handle"): object|bool {
25 global $db;
26 findOrCreateUserTable();
27 deleteExpired('user_cache');
28 $result = $db->query("select * from user_cache where ".$field."='".$value."' limit 1;");
29 $arr = $result->fetchArray(SQLITE3_ASSOC);
30 if (!is_array($arr) || count($arr) === 0) return false;
31 $arr['displayName'] = $arr['display_name'];
32 $arr['pinnedPost'] = $arr['pinned_post'];
33 unset($arr['display_name']);
34 unset($arr['pinned_post']);
35 return (object) $arr;
36}
37
38function requestMinidocCache(string $value): object|bool {
39 global $db;
40 findOrCreateMinidocTable();
41 deleteExpired('minidoc_cache');
42 $result = $db->query("select * from minidoc_cache where did='".$value."' limit 1;");
43 $arr = $result->fetchArray(SQLITE3_ASSOC);
44 if (!is_array($arr) || count($arr) === 0) return false;
45 return (object) $arr;
46}
47
48function requestPlcCache(string $did): object|bool {
49 global $db;
50 findOrCreatePlcTable();
51 deleteExpired('plc_cache');
52 $result = $db->query("select * from plc_cache where did='".$did."' limit 1;");
53 $arr = $result->fetchArray(SQLITE3_ASSOC);
54 if (is_array($arr) && count($arr) > 0) return (object) $arr;
55 return false;
56}
57
58function requestFeedCache(string $atUri): object|bool {
59 global $db;
60 findOrCreateFeedsTable();
61 deleteExpired('feed_cache');
62 $result = $db->query("select * from feed_cache where at_uri='".$atUri."' limit 1;");
63 $arr = $result->fetchArray(SQLITE3_ASSOC);
64 if (is_array($arr) && count($arr) > 0) return (object) $arr;
65 return false;
66}
67
68function requestPostCache(string $rkey): object|bool {
69 global $db;
70 findOrCreatePostTable();
71 deleteExpired('post_cache');
72 $result = $db->query("select * from post_cache where rkey='".$rkey."' limit 1;");
73 $arr = $result->fetchArray(SQLITE3_ASSOC);
74 if (is_array($arr) && count($arr) > 0) {
75 $arr['embedType'] = $arr['embed_type'];
76 $arr['embeds'] = $arr['embed_data'];
77 $arr['createdAt'] = $arr['created_at'];
78 unset($arr['embed_type']);
79 unset($arr['embed_data']);
80 unset($arr['created_at']);
81 return (object) $arr;
82 }
83 return false;
84}
85
86/* III. CACHE UPDATES */
87
88function updateUserCache(string $handle, string $did, ?string $displayName, string $pds, ?string $avatar, ?string $banner, ?string $description, ?string $pinned): void {
89 global $db;
90 findOrCreateUserTable();
91 $newExpiration = strtotime('now') + 60*60;
92 if (requestUserCache($did, 'did')) {
93 $db->exec("update user_cache set handle='".$handle."', display_name='".SQLite3::escapeString($displayName)."', pds='".$pds."', avatar='".$avatar."', banner='".$banner."', description='".SQLite3::escapeString($description)."', pinned_post='".$pinned."', expires='".$newExpiration."' where did='".$did."';");
94 } else {
95 $db->exec("insert into user_cache (handle, did, display_name, pds, avatar, banner, description, pinned_post, expires) values ('".$handle."', '".$did."', '".SQLite3::escapeString($displayName)."', '".$pds."', '".$avatar."', '".$banner."', '".SQLite3::escapeString($description)."', '".$pinned."', '".$newExpiration."');");
96 }
97}
98
99function updateMinidocCache(string $handle, string $did, string $pds, string $signingKey): void {
100 global $db;
101 findOrCreateMinidocTable();
102 $newExpiration = strtotime('now') + 60*60*48;
103 if (requestMinidocCache($handle)) {
104 $db->exec("update minidoc_cache set handle='".$handle."', did='".$did."', pds='".$pds."', signing_key='".$signingKey."', expires=".$newExpiration.";");
105 } else {
106 $db->exec("insert into minidoc_cache (handle, did, pds, signing_key, expires) values ('".$handle."', '".$did."', '".$pds."', '".$signingKey."', ".$newExpiration.");");
107 }
108}
109
110function updatePlcCache(string $did, string $plcdoc): void {
111 global $db;
112 findOrcreatePlcTable();
113 $newExpiration = strtotime('now') + 60*60*48;
114 if (requestPlcCache($did)) {
115 $db->exec("update plc_cache set plcdoc='".$plcdoc."', expires='".$newExpiration."' where did='".$did."';");
116 } else {
117 $db->exec("insert into plc_cache (did, plcdoc, expires) values ('".$did."', '".$plcdoc."', '".$newExpiration."');");
118 }
119}
120
121function updateFeedCache(string $atUri, string $title, ?string $description, ?string $avatar, string $creator_did) {
122 global $db;
123 findOrCreateFeedsTable();
124 $newExpiration = strtotime('now') + 60*60*4;
125 if (requestFeedCache($atUri)) {
126 $db->exec("update feed_cache set title='".SQLite3::escapeString($title)."', description='".SQLite3::escapeString($description)."', avatar='".$avatar."', creator_did='".$creator_did."', expires='".$newExpiration."' where at_uri='".$atUri."';");
127 } else {
128 $db->exec("insert into feed_cache (at_uri, title, description, avatar, creator_did, expires) values ('".$atUri."', '".SQLite3::escapeString($title)."', '".SQLite3::escapeString($description)."', '".$avatar."', '".$creator_did."', '".$newExpiration."');");
129 }
130}
131
132function updatePostCache(string $rkey, string $did, string $text, ?string $embedType, ?string $embedData, string $createdAt): void {
133 global $db;
134 findOrCreatePostTable();
135 $newExpiration = strtotime('now') + 60*60*48;
136 if (requestPostCache($rkey)) {
137 $db->exec("update post_cache set text='".SQLite3::escapeString($text)."', embed_type='".$embedType."', embed_data='".SQLite3::escapeString($embedData)."', expires='".$newExpiration."', created_at='".$createdAt."' where rkey='".$rkey."';");
138 } else {
139 $db->exec("insert into post_cache (rkey, did, text, embed_type, embed_data, created_at, expires) values ('".$rkey."', '".$did."', '".SQLite3::escapeString($text)."', '".$embedType."', '".SQLite3::escapeString($embedData)."', '".$createdAt."', '".$newExpiration."');");
140 }
141}
142
143/* IV. USER BAN/SUSPENSION LOOKUP */
144
145function isUserCensured($did, $pds) {
146 global $db;
147 deleteExpired("client_suspensions");
148 $ban = $db->query("select count(*) from bans where did='".$did."';");
149 $pdsban = $db->query("select count(*) from pds_bans where domain='".$domain."';");
150 $suspension = $db->query("select count(*) from client_suspensions where did='".$did."' and expires > ".strtotime('now').";");
151 if ($ban > 0 || $pdsban > 0 || $suspension > 0) {
152 return true;
153 }
154 return false;
155}
156
157function getUserCensureInfo($did, $pds) {
158
159}
160
161/* V. USER BAN/SUSPENSION MANAGEMENT */
162
163/* VI. CREATE TABLES IF NEEDED */
164function checkTableExists(string $name):bool {
165 global $db;
166 $check = $db->query("select name from sqlite_master where type='table' and name='".$name."'");
167 return $check ? true : false;
168}
169
170function findOrCreateBansTable(): void {
171 global $db;
172 $check = checkTableExists('bans');
173 if (!$check) {
174 $db->exec('CREATE TABLE "bans" (
175 "did" TEXT NOT NULL UNIQUE,
176 "reason" TEXT,
177 PRIMARY KEY("did")
178);');
179 }
180 return;
181}
182
183function findOrCreateSuspensionsTable(): void {
184 global $db;
185 $check = checkTableExists('client_suspensions');
186 if (!$check) {
187 $db->exec('CREATE TABLE "client_suspensions" (
188 "id" INTEGER NOT NULL UNIQUE,
189 "did" TEXT NOT NULL,
190 "reason" TEXT,
191 "expires" INTEGER NOT NULL,
192 PRIMARY KEY("id")
193);');
194 }
195 return;
196}
197
198function findOrCreateFeedsTable(): void {
199 global $db;
200 $check = checkTableExists('feed_cache');
201 if (!$check) {
202 $db->exec('CREATE TABLE "feed_cache" (
203 "at_uri" TEXT NOT NULL UNIQUE,
204 "title" TEXT NOT NULL,
205 "description" TEXT NOT NULL,
206 "avatar" TEXT NOT NULL,
207 "creator_did" TEXT NOT NULL,
208 "expires" INTEGER NOT NULL,
209 PRIMARY KEY("at_uri")
210 );');
211 }
212 return;
213}
214
215function findOrCreatePdsBans(): void {
216 global $db;
217 $check = checkTableExists('pds_bans');
218 if (!$check) {
219 $db->exec('CREATE TABLE "pds_bans" (
220 "domain" TEXT NOT NULL UNIQUE,
221 "reason" TEXT
222 );');
223 }
224 return;
225}
226
227function findOrCreatePlcTable(): void {
228 global $db;
229 $check = checkTableExists('user_cache');
230 if (!$check) {
231 $db->exec('CREATE TABLE "plc_cache" (
232 "did" TEXT NOT NULL UNIQUE,
233 "plcdoc" TEXT NOT NULL,
234 "expires" INTEGER NOT NULL,
235 PRIMARY KEY("did")
236 );');
237 }
238 return;
239}
240
241function findOrCreatePostTable(): void {
242 global $db;
243 $check = checkTableExists('user_cache');
244 if (!$check) {
245 $db->exec('CREATE TABLE "post_cache" (
246 "rkey" TEXT NOT NULL UNIQUE,
247 "did" TEXT NOT NULL,
248 "text" TEXT,
249 "embed_type" TEXT,
250 "embed_data" TEXT,
251 "created_at" TEXT,
252 "expires" INTEGER,
253 PRIMARY KEY("rkey")
254 );');
255 }
256}
257
258function findOrCreateMinidocTable(): void {
259 global $db;
260 $check = checkTableExists('minidoc_cache');
261 if (!$check) {
262 $db->exec('CREATE TABLE "minidoc_cache" (
263 "handle" TEXT NOT NULL UNIQUE,
264 "did" TEXT NOT NULL UNIQUE,
265 "pds" TEXT NOT NULL,
266 "signing_key" NOT NULL,
267 "expires" INTEGER,
268 PRIMARY KEY("handle")
269 );');
270 }
271 return;
272}
273
274function findOrCreateUserTable(): void {
275 global $db;
276 $check = checkTableExists('post_cache');
277 if (!$check) {
278 $db->exec('CREATE TABLE "post_cache" (
279 "rkey" TEXT NOT NULL UNIQUE,
280 "did" TEXT NOT NULL,
281 "text" TEXT,
282 "embed_type" TEXT,
283 "embed_data" TEXT,
284 "expires" INTEGER,
285 PRIMARY KEY("rkey")
286 );');
287 }
288 return;
289}
290
291?>