famsite

1 <?php 2 # Note that www group needs write access to the upload dir 3 $GLOBALS['uploads_dir'] = '/htdocs/famsite/uploads/'; 4 $GLOBALS['db'] = new PDO('sqlite:/data/famsite.db'); 5 $GLOBALS['user_id'] = null; 6 7 # Make a DB query with optional bound parameters. 8 # Returns a PDOStatement object with these methods: 9 # fetch() 10 # fetchAll() 11 # rowCount() -- ONLY for rows affected by delete, insert, update 12 # 13 function dbq($query, $params=null, $debug=false) { 14 $sth = $GLOBALS['db']->prepare($query); 15 16 # This will avoid having both field names _and_ numeric indices. 17 $sth->setFetchMode(PDO::FETCH_ASSOC); 18 19 $result = $sth->execute($params); 20 21 if($debug){ 22 $sth->debugDumpParams(); 23 } 24 25 if(!$result){ 26 error_log("ERROR: Unable to execute query: " 27 . $sth->errorCode 28 . " " . print_r($sth->errorInfo(), true)); 29 error(500, "Fam-Error logged (DBQ)."); 30 } 31 32 return $sth; 33 } 34 35 # Returns user record OR redirects to not-logged-in message page. 36 function require_user() { 37 if(!isset($_COOKIE['login'])) { 38 header('Location: not-logged-in.html'); 39 exit; 40 } 41 42 $login = $_COOKIE['login']; 43 44 $q = dbq('select id, name from users where login = :login', 45 ['login'=>$login]); 46 $row = $q->fetch(); 47 48 if(!$row){ 49 # Not really sure what happened here because the login cookie 50 # *was* set, but it didn't match anything in the DB. 51 header('Location: not-logged-in.html?weird_login'); 52 exit; 53 } 54 55 $GLOBALS['user_id'] = $row['id']; 56 57 # Return user record 58 return $row; 59 } 60 61 function get_simple_user_list() { 62 $users = []; 63 $q = dbq('select id, name from users'); 64 $users_list = $q->fetchAll(); 65 foreach($users_list as &$u){ 66 $users[$u['id']] = $u; 67 } 68 return $users; 69 } 70 71 72 # Posts 73 # =================================================================== 74 75 $routes = []; 76 77 # Fetch Posts 78 # Re: Storing the "posted" and "updated" stamps as UTC, see 79 # https://ratfactor.com/cards/sqlite-today-date 80 # Particularly, "What, even, is a day" on why I'm not storing 81 # timezones with these. 82 $routes['GET posts'] = function ($params = []) { 83 $q = "SELECT p.rowid, 84 p.user, 85 p.txt, 86 p.posted, 87 p.updated, 88 p.touched, 89 p.filename, 90 (SELECT json_group_array( 91 json_object( 92 'rowid', r.rowid, 93 'user', r.user, 94 'emoji', r.emoji, 95 'txt', r.txt 96 )) 97 FROM reactions r 98 WHERE r.post = p.rowid 99 ) as reactions 100 FROM posts p 101 LEFT JOIN reactions r ON p.rowid = r.post 102 ?WHERE? 103 GROUP BY p.rowid 104 "; 105 106 # For testing front-end: delay response 107 if(isset($params['sleep']) && $params['sleep'] < 10){ 108 sleep($params['sleep']); 109 } 110 111 # Request is for a specific post by rowid 112 if(isset($params['rowid'])){ 113 $q = str_replace('?WHERE?', 'WHERE p.rowid = :rowid', $q); 114 $post = dbq($q, [ 'rowid' => $params['rowid'] ]); 115 return [$post->fetch()]; 116 } 117 118 # Request is for new posts after a specific post 'touched' value, 119 # which will give us any posts with edits, reactions, etc. 120 if(isset($params['after'])){ 121 $q = str_replace('?WHERE?', 'WHERE p.touched > :after', $q); 122 $posts = dbq($q, [ 'after' => $params['after'] ]); 123 return $posts->fetchAll(); 124 } 125 126 # Default: Return some recent posts 127 $q = str_replace('?WHERE?', '', $q); 128 $posts = dbq("$q ORDER BY p.rowid DESC LIMIT 30"); 129 return $posts->fetchAll(); 130 }; 131 132 # New Post 133 $routes['POST posts'] = function ($params){ 134 global $routes; 135 136 if(!is_numeric($params['parent'])){ 137 $params['parent'] = null; 138 } 139 140 $dest_fname = null; 141 142 # If there was an image file attached, move it to final location 143 if($_FILES['image']['size'] > 0){ 144 # Sanitize filename - alphanumeric only and prepend timestamp 145 $upload_fname = basename($_FILES['image']['name']); 146 $dest_fname = time() . '_' . preg_replace('[^a-zA-Z\d]', '', $upload_fname); 147 148 # Now save image (and thumbnail with 'tn_' prefix) 149 save_file_upload($_FILES['image'], $dest_fname); 150 save_file_upload($_FILES['thumb'], "tn_$dest_fname"); 151 } 152 153 # Get a new highest value for 'touched' field. Coalesce only needed on the 154 # very first post, but maybe this is the first post! 155 $touched = 'COALESCE((SELECT touched FROM posts ORDER BY touched DESC LIMIT 1), 0) + 1'; 156 157 $query = "INSERT INTO posts (user, txt, parent, posted, touched, filename) 158 VALUES (:user, :txt, :parent, unixepoch('now'), $touched, :filename) 159 RETURNING rowid"; 160 $result = dbq($query, [ 161 'user' => $GLOBALS['user_id'], 162 'txt' => $params['txt'], 163 'parent' => $params['parent'], 164 'filename' => $dest_fname, 165 ]); 166 167 $post_rowid = $result->fetch()['rowid']; 168 169 # Return the post we just inserted so it can be rendered 170 return $routes['GET posts'](['rowid' => $post_rowid]); 171 }; 172 173 # Update Post 174 # Note the singular "post" here instead of "posts" 175 $routes['POST post'] = function ($params){ 176 global $routes; 177 178 if(!is_numeric($params['rowid'])){ 179 error(400, "Can't edit a post without an id ('rowid')!"); 180 } 181 182 # New highest value for 'touched' field 183 $touched = '(SELECT touched FROM posts ORDER BY touched DESC LIMIT 1) + 1'; 184 185 $query = "UPDATE posts SET txt=:txt, updated=unixepoch('now'), touched=$touched 186 WHERE rowid=:rowid"; 187 $result = dbq($query, [ 188 'txt' => $params['txt'], 189 'rowid' => $params['rowid'], 190 ]); 191 192 # Return the post we just inserted so it can be rendered 193 return $routes['GET posts'](['rowid' => $params['rowid']]); 194 }; 195 196 # Add reaction 197 $routes['POST reaction'] = function ($params){ 198 global $routes; 199 200 $query = "INSERT INTO reactions (user, post, emoji, txt) 201 VALUES (:user, :post, :emoji, :txt)"; 202 $result = dbq($query, [ 203 'user' => $GLOBALS['user_id'], 204 'post' => $params['post'], # fkey to posts.rowid 205 'emoji' => $params['emoji'], 206 'txt' => $params['txt'], 207 ]); 208 209 # Update post also with a new highest value for 'touched' field 210 $touched = '(SELECT touched FROM posts ORDER BY touched DESC LIMIT 1) + 1'; 211 $query = "UPDATE posts SET touched=$touched WHERE rowid=:post"; 212 $result = dbq($query, ['post' => $params['post']]); 213 214 # Return the whole post we reacted to so it can be re-rendered 215 return $routes['GET posts'](['rowid' => $params['post']]); 216 }; 217 218 219 function save_file_upload($input_file, $dest_fname){ 220 $dest_path = $GLOBALS['uploads_dir'] . $dest_fname; 221 222 if(!move_uploaded_file($input_file['tmp_name'], $dest_path)){ 223 error(500, 'Failed to handle file upload.'); 224 } 225 } 226 227 class MyHttpErrorResponse extends Exception {} 228 function error($code, $msg){ 229 throw new MyHttpErrorResponse($msg, $code); 230 } 231 232 function get_exception(){ 233 error(400, 'bad request - which you asked for!'); 234 } 235 236 # ========================================================= 237 # If this page was called directly, it's an API request. 238 239 if(str_contains($_SERVER['REQUEST_URI'], 'fam.php')){ 240 require_user(); 241 $method = $_SERVER['REQUEST_METHOD']; 242 $resource = $_REQUEST['r']; 243 $fn = "{$method} {$resource}"; 244 if(!is_callable($routes[$fn])){ 245 http_response_code(404); 246 echo "No such {$method} method for resource '{$resource}'"; 247 exit; 248 } 249 250 try{ 251 $result = $routes[$fn]($_REQUEST); 252 echo json_encode($result); 253 exit; # return with HTTP 200 OK 254 } catch (MyHttpErrorResponse $e){ 255 error_log(print_r($e,true)); 256 257 } catch (Exception $e){ 258 error_log("Fam-Error for route '$fn':"); 259 error_log($e->getMessage() . "\n"); 260 // Have to do our own http_response_code() here 261 // MyHttpErrorResponse exception caught above! 262 http_response_code(500); 263 echo "Error logged for: $fn"; 264 exit; 265 } 266 }