lib/maria-db.php at a1c5570a2396004940e4f338594d76f62c4ff455 · veryroundbird.house/smallbird-social

friendship ended with social-app. php is my new best friend
smallbird-social / lib / maria-db.php
at a1c5570a2396004940e4f338594d76f62c4ff455 8.6 kB view raw
  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');
  6print_r($db->character_set_name());
  7
  8// check for existence of db
  9$db->real_query('create database if not exists '.DB_NAME.';');
 10// select db
 11$db->real_query('use '.DB_NAME.';');
 12// create tables if they don't exist
 13$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);');
 14$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);');
 15$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);');
 16$db->real_query('create table if not exists plc_cache (did varchar(64) not null primary key, plcdoc text, expires int not null);');
 17$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);');
 18$db->real_query('create table if not exists pds_bans (domain varchar(255) not null primary key, reason text);');
 19$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);');
 20$db->real_query('create table if not exists bans (did varchar(64) not null primary key, reason text);');
 21// ok! we're good let's go
 22$db->close();
 23
 24function deleteExpired(string $table): void {
 25  $db = mysqli_init();
 26  $db->real_connect(DB_HOST, DB_USERNAME, DB_PASS);
 27  $db->real_query('use '.DB_NAME.';');
 28  $current_time = strtotime('now');
 29  $db->real_query('delete from '.$table.' where expires < '.$current_time.';');
 30  $db->close();
 31}
 32
 33function requestUserCache(string $value, string $field = 'handle'): ?object {
 34  $db = mysqli_init();
 35  $db->real_connect(DB_HOST, DB_USERNAME, DB_PASS);
 36  $db->real_query('use '.DB_NAME.';');
 37  deleteExpired('user_cache');
 38  $result = $db->query('select * from user_cache where '.$field.'="'.$db->real_escape_string($value).'";');
 39  return $result->fetch_object();
 40  $db->close();
 41}
 42
 43function requestMinidocCache(string $value, string $field = 'handle'): ?object {
 44  $db = mysqli_init();
 45  $db->real_connect(DB_HOST, DB_USERNAME, DB_PASS);
 46  $db->real_query('use '.DB_NAME.';');
 47  deleteExpired('minidoc_cache');
 48  $result = $db->query('select * from minidoc_cache where '.$field.'="'.$db->real_escape_string($value).'";');
 49  return $result->fetch_object();
 50  $db->close();
 51}
 52
 53function requestPostCache(string $rkey): ?object {
 54  $db = mysqli_init();
 55  $db->real_connect(DB_HOST, DB_USERNAME, DB_PASS);
 56  $db->real_query('use '.DB_NAME.';');
 57  deleteExpired('post_cache');
 58  $result = $db->query('select * from post_cache where rkey="'.$db->real_escape_string($rkey).'";');
 59  return $result->fetch_object();
 60  $db->close();
 61}
 62
 63function requestPlcCache(string $did): ?object {
 64  $db = mysqli_init();
 65  $db->real_connect(DB_HOST, DB_USERNAME, DB_PASS);
 66  $db->real_query('use '.DB_NAME.';');
 67  deleteExpired('plc_cache');
 68  $result = $db->query('select * from plc_cache where did="'.$db->real_escape_string($did).'";');
 69  return $result->fetch_object();
 70  $db->close();
 71}
 72
 73function requestFeedCache(string $at_uri): ?object {
 74  $db = mysqli_init();
 75  $db->real_connect(DB_HOST, DB_USERNAME, DB_PASS);
 76  $db->real_query('use '.DB_NAME.';');
 77  deleteExpired('feed_cache');
 78  $result = $db->query('select * from feed_cache where at_uri="'.$db->real_escape_string($at_uri).'";');
 79  return $result->fetch_object();
 80  $db->close();
 81}
 82
 83function updateUserCache(string $handle, string $did, ?string $display_name, string $pds, ?string $avatar, ?string $banner, ?string $description, ?string $pinned): void {
 84  $db = mysqli_init();
 85  $db->real_connect(DB_HOST, DB_USERNAME, DB_PASS);
 86  $db->real_query('use '.DB_NAME.';');
 87  $expires = strtotime('now') + 60*60;
 88  if (requestUserCache($did, 'did')) {
 89    $db->query('update user_cache where did="'.$db->real_escape_string($did).'" 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);
 90  }
 91  $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).'", "'.$db->real_escape_string($avatar).'", "'.$db->real_escape_string($banner).'", "'.$db->real_escape_string($pds).'", "'.$db->real_escape_string($pinned).'", '.$expires.');');
 92  $db->close();
 93}
 94
 95function updateMinidocCache(string $handle, string $did, string $pds, string $signingKey): void {
 96  $db = mysqli_init();
 97  $db->real_connect(DB_HOST, DB_USERNAME, DB_PASS);
 98  $db->real_query('use '.DB_NAME.';');
 99  $expires = strtotime('now') + 60*60*48;
100  if (requestMinidocCache($did, 'did')) {
101    $db->query('update minidoc_cache where did="'.$db->real_escape_string($did).'" set handle="'.$db->real_escape_string($handle).'", pds="'.$db->real_escape_string($pds).'", signing_key="'.$db->real_escape_string($signingKey).'", expires='.$expires);
102  }
103  $db->query('insert into minidoc_cache (handle, did, pds, signing_key, expires) values("'.$db->real_escape_string($handle).'", "'.$db->real_escape_string($did).'", "'.$db->real_escape_string($pds).'", "'.$db->real_escape_string($signing_key).'", '.$expires.');');
104  $db->close();
105}
106
107function updatePlcCache(string $did, string $plcdoc): void {
108  $db = mysqli_init();
109  $db->real_connect(DB_HOST, DB_USERNAME, DB_PASS);
110  $db->real_query('use '.DB_NAME.';');
111  $expires = strtotime('now') + 60*60*48;
112  if (requestMinidocCache($did)) {
113    $db->query('update plc_cache where did="'.$db->real_escape_string($did).'" set plcdoc="'.$db->real_escape_string($plcdoc).'", expires='.$expires);
114  }
115  $db->query('insert into plc_cache (did, plcdoc, expires) values("'.$db->real_escape_string($did).'", "'.$db->real_escape_string($plcdoc).'", '.$expires.');');
116  $db->close();
117}
118
119function updateFeedCache(string $atUri, string $title, ?string $description, ?string $avatar, string $creator_did): void {
120  $db = mysqli_init();
121  $db->real_connect(DB_HOST, DB_USERNAME, DB_PASS);
122  $db->real_query('use '.DB_NAME.';');
123  $expires = strtotime('now') + 60*60*4;
124  if (requestFeedCache($atUri)) {
125    $db->query('update feed_cache where at_uri="'.$db->real_escape_string($atUri).'" 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);
126  }
127  $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.');');
128  $db->close();
129}
130
131function updatePostCache(string $rkey, string $did, string $text, ?string $embedType, ?string $embedData, string $createdAt): void {
132  $db = mysqli_init();
133  $db->real_connect(DB_HOST, DB_USERNAME, DB_PASS);
134  $db->real_query('use '.DB_NAME.';');
135  if (requestPostCache($rkey)) {
136    $db->query('update post_cache where rkey="'.$db->real_escape_string($rkey).'" 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);
137  }
138  $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.');');
139  $db->close();
140}
141?>