friendship ended with social-app. php is my new best friend
veryroundbird.house
1<?php
2require_once('config.php');
3$db = mysqli_init();
4$db->real_connect(DB_HOST, DB_USERNAME, DB_PASS);
5$db->set_charset('utf8mb4');
6
7// check for existence of db
8$db->real_query('create database if not exists '.DB_NAME.';');
9// select db
10$db->real_query('use '.DB_NAME.';');
11// create tables if they don't exist
12$db->real_query('create table if not exists user_cache (did varchar(64) not null unique primary key, handle varchar(255) not null unique, display_name text, description text, avatar text, banner text, pds text not null, pinned_post text, expires int not null);');
13$db->real_query('create table if not exists minidoc_cache (did varchar(64) not null primary key, handle varchar(255) not null unique, pds text not null, expires int not null);');
14$db->real_query('create table if not exists post_cache (rkey varchar(64) not null primary key, did varchar(64) not null, text text, embed_type varchar(32), embed_data text, created_at datetime, expires int not null);');
15$db->real_query('create table if not exists plc_cache (did varchar(64) not null primary key, plcdoc text, expires int not null);');
16$db->real_query('create table if not exists feed_cache (at_uri varchar(255) not null primary key, title varchar(255) not null, description text, avatar text, creator_did varchar(64) not null, expires int not null);');
17$db->real_query('create table if not exists pds_bans (domain varchar(255) not null primary key, reason text);');
18$db->real_query('create table if not exists client_suspensions (id int not null auto_increment primary key, did varchar(64) not null, reason text, expires int not null);');
19$db->real_query('create table if not exists bans (did varchar(64) not null primary key, reason text);');
20// ok! we're good let's go
21$db->close();
22
23function newConn() {
24 $db = mysqli_init();
25 $db->real_connect(DB_HOST, DB_USERNAME, DB_PASS);
26 $db->real_query('use '.DB_NAME.';');
27 $db->set_charset('utf8mb4');
28 return $db;
29}
30
31function deleteExpired(string $table): void {
32 $db = newConn();
33 $current_time = strtotime('now');
34 $db->real_query('delete from '.$table.' where expires < '.$current_time.';');
35 $db->close();
36}
37
38function requestUserCache(string $value, string $field = 'handle'): ?object {
39 $db = newConn();
40 deleteExpired('user_cache');
41 $result = $db->query('select * from user_cache where '.$field.'="'.$db->real_escape_string($value).'";');
42 if ($result->num_rows === 0) return null;
43 $obj = $result->fetch_object();
44 $obj->displayName = $obj->display_name;
45 $obj->pinnedPost = $obj->pinned_post;
46 unset($obj->display_name);
47 unset($obj->pinned_post);
48 return $obj;
49 $db->close();
50}
51
52function requestMinidocCache(string $value, string $field = 'handle'): ?object {
53 $db = newConn();
54 deleteExpired('minidoc_cache');
55 $result = $db->query('select * from minidoc_cache where '.$field.'="'.$db->real_escape_string($value).'";');
56 if ($result->num_rows === 0) return null;
57 return $result->fetch_object();
58 $db->close();
59}
60
61function requestPostCache(string $rkey): ?object {
62 $db = newConn();
63 deleteExpired('post_cache');
64 $result = $db->query('select * from post_cache where rkey="'.$db->real_escape_string($rkey).'";');
65 if ($result->num_rows === 0) return null;
66 $obj = $result->fetch_object();
67 $obj->createdAt = $obj->created_at;
68 $obj->embedType = $obj->embed_type;
69 $obj->embeds = $obj->embed_data;
70 unset($obj->created_at);
71 unset($obj->embed_type);
72 unset($obj->embed_data);
73 return $obj;
74 $db->close();
75}
76
77function requestPlcCache(string $did): ?object {
78 $db = newConn();
79 deleteExpired('plc_cache');
80 $result = $db->query('select * from plc_cache where did="'.$db->real_escape_string($did).'";');
81 if ($result->num_rows === 0) return null;
82 return $result->fetch_object();
83 $db->close();
84}
85
86function requestFeedCache(string $at_uri): ?object {
87 $db = newConn();
88 deleteExpired('feed_cache');
89 $result = $db->query('select * from feed_cache where at_uri="'.$db->real_escape_string($at_uri).'";');
90 if ($result->num_rows === 0) return null;
91 $obj = $result->fetch_object();
92 $obj->atUri = $obj->at_uri;
93 $obj->creatorDid = $obj->creator_did;
94 unset($obj->at_uri);
95 unset($obj->creator_did);
96 return $obj;
97 $db->close();
98}
99
100function updateUserCache(string $handle, string $did, ?string $display_name, string $pds, ?string $avatar, ?string $banner, string $description, ?string $pinned): void {
101 $db = newConn();
102 $expires = strtotime('now') + 60*60;
103 if (!empty(requestUserCache($did, 'did'))) {
104 $db->query('update user_cache set handle="'.$db->real_escape_string($handle).'", display_name="'.$db->real_escape_string($display_name).'", description="'.$db->real_escape_string($description).'", avatar="'.$db->real_escape_string($avatar).'", banner="'.$db->real_escape_string($banner).'", pds="'.$db->real_escape_string($pds).'", pinned_post="'.$db->real_escape_string($pinned).'", expires='.$expires.' where did="'.$db->real_escape_string($did).'";');
105 return;
106 }
107 $db->query('insert into user_cache (did, handle, display_name, description, avatar, banner, pds, pinned_post, expires) values("'.$db->real_escape_string($did).'", "'.$db->real_escape_string($handle).'", "'.$db->real_escape_string($display_name).'", "'.$db->real_escape_string($description).'", "'.($avatar ? $db->real_escape_string($avatar) : 'NULL').'", "'.($banner ? $db->real_escape_string($banner) : 'NULL').'", "'.$db->real_escape_string($pds).'", "'.($pinned ? $db->real_escape_string($pinned) : 'NULL').'", '.$expires.');');
108 $db->close();
109}
110
111function updateMinidocCache(string $handle, string $did, string $pds): void {
112 $db = newConn();
113 $expires = strtotime('now') + 60*60*48;
114 if (!empty(requestMinidocCache($did, 'did'))) {
115 $db->query('update minidoc_cache set handle="'.$db->real_escape_string($handle).'", pds="'.$db->real_escape_string($pds).'", expires='.$expires.' where did="'.$db->real_escape_string($did).'";');
116 return;
117 }
118 $db->query('insert into minidoc_cache (handle, did, pds, expires) values("'.$db->real_escape_string($handle).'", "'.$db->real_escape_string($did).'", "'.$db->real_escape_string($pds).'", '.$expires.');');
119 $db->close();
120}
121
122function updatePlcCache(string $did, string $plcdoc): void {
123 $db = newConn();
124 $expires = strtotime('now') + 60*60*48;
125 if (!empty(requestMinidocCache($did))) {
126 $db->query('update plc_cache set plcdoc="'.$db->real_escape_string($plcdoc).'", expires='.$expires.' where did="'.$db->real_escape_string($did).'";');
127 return;
128 }
129 $db->query('insert into plc_cache (did, plcdoc, expires) values("'.$db->real_escape_string($did).'", "'.$db->real_escape_string($plcdoc).'", '.$expires.');');
130 $db->close();
131}
132
133function updateFeedCache(string $atUri, string $title, ?string $description, ?string $avatar, string $creator_did): void {
134 $db = newConn();
135 $expires = strtotime('now') + 60*60*4;
136 if (!empty(requestFeedCache($atUri))) {
137 $db->query('update feed_cache set title="'.$db->real_escape_string($title).'", description="'.$db->real_escape_string($description).'", avatar="'.$db->real_escape_string($avatar).'", creator_did="'.$db->real_escape_string($creator_did).'", expires='.$expires.' where at_uri="'.$db->real_escape_string($atUri).'";');
138 return;
139 }
140 $db->query('insert into feed_cache (at_uri, title, description, avatar, creator_did, expires) values("'.$db->real_escape_string($atUri).'", "'.$db->real_escape_string($title).'", "'.$db->real_escape_string($description).'", "'.$db->real_escape_string($avatar).'", "'.$db->real_escape_string($creator_did).'", '.$expires.');');
141 $db->close();
142}
143
144function updatePostCache(string $rkey, string $did, string $text, string $embedType, string $embedData, string $createdAt): void {
145 $db = newConn();
146 $dbdate = date('Y-m-d h:i:s', strtotime($createdAt));
147 $expires = strtotime('now') + 60*60*48;
148 if (!empty(requestPostCache($rkey))) {
149 $db->query('update post_cache set did="'.$db->real_escape_string($did).'", text="'.$db->real_escape_string($text).'", embed_type="'.$db->real_escape_string($embedType).'", embed_data="'.$db->real_escape_string($embedData).'", created_at="'.$db->real_escape_string($dbdate).'", expires='.$expires.' where rkey="'.$db->real_escape_string($rkey).'";');
150 return;
151 }
152 $db->query('insert into post_cache (rkey, did, text, embed_type, embed_data, created_at, expires) values("'.$db->real_escape_string($rkey).'", "'.$db->real_escape_string($did).'", "'.$db->real_escape_string($text).'", "'.$db->real_escape_string($embedType).'", "'.$db->real_escape_string($embedData).'", "'.$db->real_escape_string($dbdate).'", '.$expires.');');
153 $db->close();
154}
155?>