feat: moved from SQLite to MySQL

2 files changed, 29 insertions, 26 deletions

diff --git a/public/emotes/index.php b/public/emotes/index.php
index b603cc0..b981a26 100644
--- a/public/emotes/index.php
+++ b/public/emotes/index.php
@@ -1,19 +1,24 @@
 <?php
 include "../../src/emote.php";
 include "../../src/accounts.php";
+include_once "../../src/config.php";
+
 authorize_user();
 
 function display_list_emotes(int $page, int $limit): array
 {
-    $db = new SQLite3("../../database.db");
-    $stmt = $db->prepare("SELECT * FROM emotes ORDER BY created_at ASC LIMIT :limit OFFSET :offset");
-    $stmt->bindValue(":offset", $page * $limit, SQLITE3_INTEGER);
-    $stmt->bindValue(":limit", $limit, SQLITE3_INTEGER);
-    $results = $stmt->execute();
+    $offset = $page * $limit;
+    $db = new PDO(DB_URL, DB_USER, DB_PASS);
+    $stmt = $db->prepare("SELECT * FROM emotes ORDER BY created_at ASC LIMIT ? OFFSET ?");
+    $stmt->bindParam(1, $limit, PDO::PARAM_INT);
+    $stmt->bindParam(2, $offset, PDO::PARAM_INT);
+    $stmt->execute();
 
     $emotes = [];
 
-    while ($row = $results->fetchArray()) {
+    $rows = $stmt->fetchAll();
+
+    foreach ($rows as $row) {
         array_push($emotes, new Emote(
             $row["id"],
             $row["code"],
@@ -28,12 +33,13 @@ function display_list_emotes(int $page, int $limit): array
 
 function display_emote(int $id)
 {
-    $db = new SQLite3("../../database.db");
-    $stmt = $db->prepare("SELECT * FROM emotes WHERE id = :id");
-    $stmt->bindValue(":id", $id, SQLITE3_INTEGER);
-    $results = $stmt->execute();
+    $db = new PDO(DB_URL, DB_USER, DB_PASS);
+    $stmt = $db->prepare("SELECT * FROM emotes WHERE id = ?");
+    $stmt->execute([$id]);
+
+    $emote = null;
 
-    if ($row = $results->fetchArray()) {
+    if ($row = $stmt->fetch()) {
         $emote = new Emote(
             $row["id"],
             $row["code"],
diff --git a/public/emotes/upload.php b/public/emotes/upload.php
index b0a3b71..ad581ca 100644
--- a/public/emotes/upload.php
+++ b/public/emotes/upload.php
@@ -1,13 +1,14 @@
 <?php
 include "../../src/accounts.php";
+include_once "../../src/config.php";
+
 authorize_user();
 
-function abort_upload(string $path, SQLite3 $db, string $id, string $response_text, int $response_code = 400)
+function abort_upload(string $path, PDO $db, string $id, string $response_text, int $response_code = 400)
 {
-    $stmt = $db->prepare("DELETE FROM emotes WHERE id = :id");
-    $stmt->bindValue(":id", $id, SQLITE3_INTEGER);
-    $stmt->execute();
-    $db->close();
+    $stmt = $db->prepare("DELETE FROM emotes WHERE id = ?");
+    $stmt->execute([$id]);
+    $db = null;
 
     array_map("unlink", glob("$path/*.*"));
     rmdir($path);
@@ -62,21 +63,17 @@ if (is_null(list($mime, $ext) = get_mime_and_ext($image["tmp_name"]))) {
 }
 
 // creating a new emote record
-$db = new SQLite3("../../database.db");
+$db = new PDO(DB_URL, DB_USER, DB_PASS);
 
 $uploaded_by = $_SESSION["user_id"] ?? null;
 
-$stmt = $db->prepare("INSERT INTO emotes(code, mime, ext, uploaded_by) VALUES (:code, :mime, :ext, :uploaded_by)");
-$stmt->bindValue(":code", $code);
-$stmt->bindValue(":mime", $mime);
-$stmt->bindValue(":ext", $ext);
-$stmt->bindValue(":uploaded_by", $uploaded_by);
-$results = $stmt->execute();
+$stmt = $db->prepare("INSERT INTO emotes(code, mime, ext, uploaded_by) VALUES (?, ?, ?, ?)");
+$stmt->execute([$code, $mime, $ext, $uploaded_by]);
 
-$id = $db->lastInsertRowID();
+$id = $db->lastInsertId();
 
 if ($id == 0) {
-    $db->close();
+    $db = null;
     http_response_code(500);
     echo json_encode([
         "status_code" => 500,
@@ -112,7 +109,7 @@ if ($resized_image) {
     abort_upload($path, $db, $id, $resized_image);
 }
 
-$db->close();
+$db = null;
 
 if (isset($_SERVER["HTTP_ACCEPT"]) && $_SERVER["HTTP_ACCEPT"] == "application/json") {
     http_response_code(201);