feat: moved from SQLite to MySQL

12 files changed, 113 insertions, 109 deletions

diff --git a/README.md b/README.md
index 6af002e..b64fa94 100644
--- a/README.md
+++ b/README.md
@@ -5,7 +5,7 @@ a free, easy-to-install web emote provider for Twitch chats!
 ## Prerequisites
 
 + PHP >= 8.3
-+ SQLite 3
++ MySQL/MariaDB
 
 ## License
 
diff --git a/database.sql b/database.sql
index ccd4c7e..3436b2c 100644
--- a/database.sql
+++ b/database.sql
@@ -1,25 +1,25 @@
-CREATE TABLE IF NOT EXISTS "users" (
-    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-    "username" TEXT NOT NULL UNIQUE,
-    "password" TEXT,
-    "secret_key" TEXT NOT NULL,
-    "joined_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
+CREATE TABLE IF NOT EXISTS users (
+    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
+    username TEXT NOT NULL UNIQUE,
+    password TEXT,
+    secret_key TEXT NOT NULL,
+    joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
 );
 
-CREATE TABLE IF NOT EXISTS "connections" (
-    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-    "user_id" INTEGER NOT NULL,
-    "alias_id" TEXT NOT NULL,
-    "platform" TEXT NOT NULL,
-    "data" TEXT NOT NULL,
-    "connected_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
+CREATE TABLE IF NOT EXISTS connections (
+    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
+    user_id INTEGER NOT NULL REFERENCES users(id),
+    alias_id TEXT NOT NULL,
+    platform TEXT NOT NULL,
+    data TEXT NOT NULL,
+    connected_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
 );
 
-CREATE TABLE IF NOT EXISTS "emotes" (
-    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-    "code" TEXT NOT NULL,
-    "mime" TEXT NOT NULL,
-    "ext" TEXT NOT NULL,
-    "uploaded_by" INTEGER,
-    "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
+CREATE TABLE IF NOT EXISTS emotes (
+    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
+    code TEXT NOT NULL,
+    mime TEXT NOT NULL,
+    ext TEXT NOT NULL,
+    uploaded_by INTEGER REFERENCES users(id),
+    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
 );
\ No newline at end of file
diff --git a/public/account/delete.php b/public/account/delete.php
index af8a093..ecfcc80 100644
--- a/public/account/delete.php
+++ b/public/account/delete.php
@@ -1,5 +1,6 @@
 <?php
 include "../../src/utils.php";
+include_once "../../src/config.php";
 
 session_start();
 
@@ -10,26 +11,23 @@ if (!isset($_SESSION["user_id"])) {
 
 $id = $_SESSION["user_id"];
 
-$db = new SQLite3("../../database.db");
+$db = new PDO(DB_URL, DB_USER, DB_PASS);
 
-$stmt = $db->prepare("UPDATE emotes SET uploaded_by = NULL WHERE uploaded_by = :id");
-$stmt->bindValue(":id", $id);
-$stmt->execute();
+$stmt = $db->prepare("UPDATE emotes SET uploaded_by = NULL WHERE uploaded_by = ?");
+$stmt->execute([$id]);
 
-$stmt = $db->prepare("DELETE FROM connections WHERE user_id = :id");
-$stmt->bindValue(":id", $id);
-$stmt->execute();
+$stmt = $db->prepare("DELETE FROM connections WHERE user_id = ?");
+$stmt->execute([$id]);
 
-$stmt = $db->prepare("DELETE FROM users WHERE id = :id");
-$stmt->bindValue(":id", $id);
-$stmt->execute();
+$stmt = $db->prepare("DELETE FROM users WHERE id = ?");
+$stmt->execute([$id]);
 
 session_unset();
 session_destroy();
 
 setcookie("secret_key", "", time() - 1000);
 
-$db->close();
+$db = null;
 
 $path = "../static/userdata/avatars/$id";
 if (is_file($path)) {
diff --git a/public/account/login/index.php b/public/account/login/index.php
index 146fde9..e104a88 100644
--- a/public/account/login/index.php
+++ b/public/account/login/index.php
@@ -1,7 +1,6 @@
 <?php
 include "../../../src/accounts.php";
-// FIXME
-//authorize_user();
+authorize_user();
 
 include "../../../src/partials.php";
 ?>
diff --git a/public/account/login/twitch.php b/public/account/login/twitch.php
index ff2fe51..7866eb5 100644
--- a/public/account/login/twitch.php
+++ b/public/account/login/twitch.php
@@ -1,8 +1,9 @@
 <?php
 include "../../../src/utils.php";
+include_once "../../../src/config.php";
 
-$client_id = "";
-$client_secret = "";
+$client_id = "472prq7kqn0a21l5um2lz7374471pp";
+$client_secret = "koho369mw8p51di4fx34jm2ogdmbj2";
 $redirect_uri = "http://localhost:8000/account/login/twitch.php";
 
 if (isset($_GET["error"])) {
@@ -66,32 +67,29 @@ $_SESSION["twitch_access_token"] = $response["access_token"];
 $_SESSION["twitch_refresh_token"] = $response["refresh_token"];
 $_SESSION["twitch_expires_on"] = time() + intval($response["expires_in"]);
 
-$db = new SQLite3("../../../database.db");
+$db = new PDO(DB_URL, DB_USER, DB_PASS);
 
 // creating user if not exists
-$stmt = $db->prepare("SELECT id, user_id FROM connections WHERE alias_id = :alias_id AND platform = 'twitch'");
-$stmt->bindValue("alias_id", $twitch_user["id"]);
-
-$results = $stmt->execute();
+$stmt = $db->prepare("SELECT id, user_id FROM connections WHERE alias_id = ? AND platform = 'twitch'");
+$stmt->execute([$twitch_user["id"]]);
 
 $user_id = "";
 $user_secret_key = "";
 $user_name = "";
 
-if ($row = $results->fetchArray()) {
+if ($row = $stmt->fetch()) {
     $id = $row["id"];
     $user_id = $row["user_id"];
 
-    $stmt = $db->prepare("SELECT * FROM users WHERE id = :id");
-    $stmt->bindValue(":id", $id);
-    $results = $stmt->execute();
+    $stmt = $db->prepare("SELECT * FROM users WHERE id = ?");
+    $stmt->execute([$user_id]);
 
-    if ($row = $results->fetchArray()) {
+    if ($row = $stmt->fetch()) {
         $user_name = $row["username"];
         $user_secret_key = $row["secret_key"];
         $user_id = $row["id"];
     } else {
-        $db->close();
+        $db = null;
         echo "Connection found, but not user?";
         exit;
     }
@@ -99,32 +97,28 @@ if ($row = $results->fetchArray()) {
     $user_secret_key = generate_random_string(32);
     $user_name = $twitch_user["login"];
 
-    $stmt = $db->prepare("INSERT INTO users(username, secret_key) VALUES (:username, :secret_key)");
-    $stmt->bindValue(":username", $user_name);
-    $stmt->bindValue(":secret_key", $user_secret_key);
-    if (!$stmt->execute()) {
-        $db->close();
+    $stmt = $db->prepare("INSERT INTO users(username, secret_key) VALUES (?, ?)");
+    if (!$stmt->execute([$user_name, $user_secret_key])) {
+        $db = null;
         echo "Failed to create a user";
         exit;
     }
 
-    $user_id = $db->lastInsertRowID();
+    $user_id = $db->lastInsertId();
 
-    $stmt = $db->prepare("INSERT INTO connections(user_id, alias_id, platform, data) VALUES (:user_id, :alias_id, 'twitch', :data)");
-    $stmt->bindValue(":user_id", $user_id);
-    $stmt->bindValue(":alias_id", $twitch_user["id"]);
-    $stmt->bindValue(
-        ":data",
+    $stmt = $db->prepare("INSERT INTO connections(user_id, alias_id, platform, data) VALUES (?, ?, 'twitch', ?)");
+    $stmt->execute([
+        $user_id,
+        $twitch_user["id"],
         $_SESSION["twitch_access_token"] . ":" . $_SESSION["twitch_refresh_token"] . ":" . $_SESSION["twitch_expires_on"]
-    );
-    $stmt->execute();
+    ]);
 }
 
 $_SESSION["user_id"] = $user_id;
 $_SESSION["user_name"] = $user_name;
 setcookie("secret_key", $user_secret_key, time() + 86400 * 30, "/");
 
-$db->close();
+$db = null;
 
 // downloading profile picture
 $path = "../../static/userdata/avatars";
diff --git a/public/account/signout.php b/public/account/signout.php
index dd1d0f9..66a0cac 100644
--- a/public/account/signout.php
+++ b/public/account/signout.php
@@ -1,5 +1,6 @@
 <?php
 include "../../src/utils.php";
+include_once "../../src/config.php";
 
 session_start();
 
@@ -8,18 +9,16 @@ if (!isset($_SESSION["user_id"])) {
     exit;
 }
 
-$db = new SQLite3("../../database.db");
+$db = new PDO(DB_URL, DB_USER, DB_PASS);
 
-$stmt = $db->prepare("UPDATE users SET secret_key = :secret_key WHERE id = :id");
-$stmt->bindValue(":id", $_SESSION["user_id"]);
-$stmt->bindValue(":secret_key", generate_random_string(32));
-$stmt->execute();
+$stmt = $db->prepare("UPDATE users SET secret_key = ? WHERE id = ?");
+$stmt->execute([generate_random_string(32), $_SESSION["user_id"]]);
 
 session_unset();
 session_destroy();
 
 setcookie("secret_key", "", time() - 1000);
 
-$db->close();
+$db = null;
 
 header("Location: /account");
\ No newline at end of file
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);
diff --git a/public/index.php b/public/index.php
index 14d2c9a..5619208 100644
--- a/public/index.php
+++ b/public/index.php
@@ -1,3 +1,6 @@
+<?php
+include_once "../src/config.php";
+?>
 <html>
 
 <head>
@@ -25,11 +28,9 @@
 
             <div class="counter">
                 <?php
-                $db = new SQLite3("../database.db");
+                $db = new PDO(DB_URL, DB_USER, DB_PASS);
                 $results = $db->query("SELECT COUNT(*) FROM emotes");
-                $count = $results->fetchArray()[0];
-
-                $db->close();
+                $count = $results->fetch()[0];
 
                 foreach (str_split($count) as $c) {
                     echo "<img src=\"/static/img/counter/$c.png\" alt=\"\" />";
diff --git a/src/accounts.php b/src/accounts.php
index 4273964..330ad3c 100644
--- a/src/accounts.php
+++ b/src/accounts.php
@@ -1,4 +1,6 @@
 <?php
+include_once "config.php";
+
 function authorize_user()
 {
     session_start();
@@ -11,13 +13,14 @@ function authorize_user()
         return;
     }
 
-    $db = new SQLite3("../../database.db");
+    include_once "config.php";
+
+    $db = new PDO(DB_URL, DB_USER, DB_PASS);
 
-    $stmt = $db->prepare("SELECT id, username FROM users WHERE secret_key = :secret_key");
-    $stmt->bindValue("secret_key", $_COOKIE["secret_key"]);
-    $results = $stmt->execute();
+    $stmt = $db->prepare("SELECT id, username FROM users WHERE secret_key = ?");
+    $stmt->execute([$_COOKIE["secret_key"]]);
 
-    if ($row = $results->fetchArray()) {
+    if ($row = $stmt->fetch()) {
         $_SESSION["user_id"] = $row["id"];
         $_SESSION["user_name"] = $row["username"];
     } else {
@@ -25,5 +28,5 @@ function authorize_user()
         setcookie("secret_key", "", time() - 1000);
     }
 
-    $db->close();
+    $db = null;
 }
\ No newline at end of file
diff --git a/src/config.php b/src/config.php
new file mode 100644
index 0000000..188b7bb
--- /dev/null
+++ b/src/config.php
@@ -0,0 +1,5 @@
+<?php
+// DATABASE
+define("DB_USER", "kochan");
+define("DB_PASS", "kochan");
+define("DB_URL", "mysql:host=localhost;dbname=tinyemotes;port=3306");
\ No newline at end of file
diff --git a/src/emotes/single_page.php b/src/emotes/single_page.php
index fb87789..900003c 100644
--- a/src/emotes/single_page.php
+++ b/src/emotes/single_page.php
@@ -1,3 +1,6 @@
+<?php
+include_once "../../src/config.php";
+?>
 <html>
 
 <head>
@@ -76,17 +79,16 @@
                                 $link = "#";
 
                                 if ($emote->get_uploaded_by()) {
-                                    $db = new SQLite3("../../database.db");
-                                    $stmt = $db->prepare("SELECT username FROM users WHERE id = :id");
-                                    $stmt->bindValue(":id", $emote->get_uploaded_by());
-                                    $results = $stmt->execute();
+                                    $db = new PDO(DB_URL, DB_USER, DB_PASS);
+                                    $stmt = $db->prepare("SELECT username FROM users WHERE id = ?");
+                                    $stmt->execute([$emote->get_uploaded_by()]);
 
-                                    if ($row = $results->fetchArray()) {
+                                    if ($row = $stmt->fetch()) {
                                         $username = $row["username"];
                                         $link = "/users/" . $emote->get_uploaded_by();
                                     }
 
-                                    $db->close();
+                                    $db = null;
                                 }
 
                                 echo "<a href=\"$link\">";