feat: we are using SQL databases now

6 files changed, 149 insertions, 96 deletions

diff --git a/database.sql b/database.sql
new file mode 100644
index 0000000..b7efa83
--- /dev/null
+++ b/database.sql
@@ -0,0 +1,19 @@
+CREATE TABLE IF NOT EXISTS files (
+    id CHAR(32) NOT NULL PRIMARY KEY,
+    mime TEXT NOT NULL,
+    extension TEXT NOT NULL,
+    size BIGINT NOT NULL,
+    title TEXT,
+    password TEXT,
+    uploaded_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
+    expires_at TIMESTAMP,
+    views BIGINT NOT NULL DEFAULT 0
+);
+
+CREATE TABLE IF NOT EXISTS file_metadata (
+    id CHAR(32) NOT NULL PRIMARY KEY REFERENCES files(id) ON DELETE CASCADE,
+    width BIGINT,
+    height BIGINT,
+    duration BIGINT,
+    line_count BIGINT
+);
\ No newline at end of file
diff --git a/lib/file.php b/lib/file.php
index fe7dae1..fc3e38a 100644
--- a/lib/file.php
+++ b/lib/file.php
@@ -19,12 +19,11 @@ function verify_mimetype(string $file_path, string $mimetype): bool
     throw new RuntimeException("Illegal type for MIME verifications: $mimetype");
 }
 
-function delete_file(string $file_id, string $file_extension): bool
+function delete_file(string $file_id, string $file_extension, PDO|null $db = null): bool
 {
     $paths = [
         FILE_UPLOAD_DIRECTORY . "/{$file_id}.{$file_extension}",
-        FILE_THUMBNAIL_DIRECTORY . "/{$file_id}.webp",
-        FILE_METADATA_DIRECTORY . "/{$file_id}.metadata.json"
+        FILE_THUMBNAIL_DIRECTORY . "/{$file_id}.webp"
     ];
 
     foreach ($paths as $path) {
@@ -33,6 +32,10 @@ function delete_file(string $file_id, string $file_extension): bool
         }
     }
 
+    if ($db) {
+        $db->prepare('DELETE FROM files WHERE id = ? AND extension = ?')->execute([$file_id, $file_extension]);
+    }
+
     return true;
 }
 
diff --git a/lib/partials.php b/lib/partials.php
index 5b94ce2..6c69c62 100644
--- a/lib/partials.php
+++ b/lib/partials.php
@@ -66,12 +66,14 @@ function html_mini_navbar()
 
 function html_footer()
 {
-    $files = glob(FILE_UPLOAD_DIRECTORY . "/*.*");
-    $file_size = 0;
+    $db = new PDO(DB_URL, DB_USER, DB_PASS);
+    $stmt = $db->query('SELECT COUNT(*) AS file_count, SUM(size) AS file_overall_size FROM files');
+    $stmt->execute();
 
-    foreach ($files as $file) {
-        $file_size += filesize($file);
-    }
+    $row = $stmt->fetch(PDO::FETCH_ASSOC);
+
+    $file_count = $row['file_count'];
+    $file_size = $row['file_overall_size'];
 
     $suffix = 'MB';
     $file_size /= 1024 * 1024; // MB
@@ -119,7 +121,7 @@ function html_footer()
         <?php if (!empty(INSTANCE_TOR)): ?>
             <p><a href="<?= INSTANCE_TOR ?>">[Tor]</a></p>
         <?php endif; ?>
-        <p>Serving <?= count($files) ?> files and <?= $file_size ?> of active content</p>
+        <p>Serving <?= $file_count ?> files and <?= $file_size ?> of active content</p>
     </footer>
     <?php ;
 }
\ No newline at end of file
diff --git a/public/delete.php b/public/delete.php
index 2ca1d73..bb91640 100644
--- a/public/delete.php
+++ b/public/delete.php
@@ -40,7 +40,13 @@ if (!preg_match('/^[a-zA-Z0-9_-]+$/', $file_id) || !preg_match('/^[a-zA-Z0-9]+$/
     exit();
 }
 
-if (!is_file(FILE_UPLOAD_DIRECTORY . "/{$file_id}.{$file_ext}")) {
+$db = new PDO(DB_URL, DB_USER, DB_PASS);
+$stmt = $db->prepare('SELECT password FROM files WHERE id = ? AND extension = ?');
+$stmt->execute([$file_id, $file_ext]);
+
+$file = $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
+
+if (!$file) {
     generate_alert(
         "/",
         "File $file_id not found",
@@ -49,18 +55,7 @@ if (!is_file(FILE_UPLOAD_DIRECTORY . "/{$file_id}.{$file_ext}")) {
     exit();
 }
 
-if (!is_file(FILE_METADATA_DIRECTORY . "/{$file_id}.metadata.json")) {
-    generate_alert(
-        "/$file_id.$file_ext",
-        "File metadata $file_id not found",
-        404
-    );
-    exit();
-}
-
-$metadata = json_decode(file_get_contents(FILE_METADATA_DIRECTORY . "/{$file_id}.metadata.json"), true);
-
-if (!array_key_exists('password', $metadata)) {
+if (!isset($file['password'])) {
     generate_alert(
         "/$file_id.$file_ext",
         "File $file_id does not have a password. File cannot be deleted!",
@@ -78,7 +73,7 @@ if (!isset($_SESSION['is_moderator']) && !isset($password)) {
     exit();
 }
 
-if (!isset($_SESSION['is_moderator']) && !password_verify($password, $metadata['password'])) {
+if (!isset($_SESSION['is_moderator']) && !password_verify($password, $file['password'])) {
     generate_alert(
         "/$file_id.$file_ext",
         'Unauthorized',
@@ -87,7 +82,7 @@ if (!isset($_SESSION['is_moderator']) && !password_verify($password, $metadata['
     exit();
 }
 
-if (!delete_file($file_id, $file_ext)) {
+if (!delete_file($file_id, $file_ext, $db)) {
     generate_alert(
         "/$file_id.$file_ext",
         'Failed to remove files. Try again later',
diff --git a/public/index.php b/public/index.php
index f8b5e1e..7788bbe 100644
--- a/public/index.php
+++ b/public/index.php
@@ -6,12 +6,16 @@ include_once $_SERVER['DOCUMENT_ROOT'] . '/../lib/alert.php';
 
 session_start();
 
+$db = new PDO(DB_URL, DB_USER, DB_PASS);
+
 if (FILE_CATALOG_RANDOM && isset($_GET['random'])) {
-    $files = glob(FILE_UPLOAD_DIRECTORY . "/*.*");
-    $file = $files[random_int(0, count($files) - 1)];
-    $filename = basename($file);
-    header("Location: /{$filename}");
-    exit();
+    $stmt = $db->query('SELECT id, extension FROM files ORDER BY rand() LIMIT 1');
+    $stmt->execute();
+
+    if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
+        header("Location: /{$row['id']}.{$row['extension']}");
+        exit;
+    }
 }
 
 $file = null;
@@ -37,37 +41,34 @@ if (FILE_CATALOG_FANCY_VIEW && $file_id) {
     }
 
     $file_path = FILE_UPLOAD_DIRECTORY . "/{$file_id}.{$file_ext}";
-    $meta_path = FILE_METADATA_DIRECTORY . "/{$file_id}.metadata.json";
 
     if (!file_exists($file_path)) {
         http_response_code(404);
         exit();
     }
 
-    if (file_exists($meta_path)) {
-        $file = json_decode(file_get_contents($meta_path), true);
-
-        if (isset($file['views'])) {
-            $viewed_file_ids = $_SESSION['viewed_file_ids'] ?? [];
+    $stmt = $db->prepare('SELECT fm.*, f.*
+        FROM files f
+        LEFT JOIN file_metadata fm ON fm.id = f.id
+        WHERE f.id = ? AND f.extension = ?
+    ');
+    $stmt->execute([$file_id, $file_ext]);
+    $file = $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
 
-            if (!in_array($file['id'], $viewed_file_ids)) {
-                $file['views']++;
-                array_push($viewed_file_ids, $file['id']);
-                file_put_contents($meta_path, json_encode($file, JSON_UNESCAPED_SLASHES));
-            }
-
-            $_SESSION['viewed_file_ids'] = $viewed_file_ids;
+    if (!$file) {
+        http_response_code(404);
+        exit();
+    }
 
-            session_commit();
-        }
-    } else {
-        $file = [
-            'id' => $file_id,
-            'extension' => $file_ext,
-            'mime' => FILE_ACCEPTED_MIME_TYPES[$file_ext],
-            'size' => filesize($file_path)
-        ];
+    // counting views
+    $viewed_file_ids = $_SESSION['viewed_file_ids'] ?? [];
+    if (!in_array($file['id'], $viewed_file_ids)) {
+        $file['views']++;
+        array_push($viewed_file_ids, $file['id']);
+        $db->prepare('UPDATE files SET views = ? WHERE id = ? AND extension = ?')->execute([$file['views'], $file['id'], $file['extension']]);
     }
+    $_SESSION['viewed_file_ids'] = $viewed_file_ids;
+    session_commit();
 
     $file['full_url'] = FILE_UPLOAD_DIRECTORY_PREFIX . "/{$file['id']}.{$file['extension']}";
 
@@ -77,27 +78,23 @@ if (FILE_CATALOG_FANCY_VIEW && $file_id) {
     $factor = floor((strlen($size) - 1) / 3);
     $file['size_formatted'] = sprintf("%.2f", $size / pow(1024, $factor)) . ' ' . $units[$factor];
 
-    $file['name'] = $file['original_name'] ?? sprintf('%s.%s', $file['id'], $file['extension']);
+    $file['name'] = $file['title'] ?? sprintf('%s.%s', $file['id'], $file['extension']);
 
-    if (!isset($file['uploaded_at'])) {
-        $file['uploaded_at'] = filemtime($file_path);
+    $file['resolution'] = [];
+
+    if (isset($file['width'], $file['height'])) {
+        array_push($file['resolution'], sprintf('%sx%s', $file['width'], $file['height']));
     }
 
-    if (str_starts_with($file['mime'], 'image/')) {
-        $file['resolution'] = trim(shell_exec('identify -format "%wx%h" ' . escapeshellarg($file_path) . '[0]'));
-    } else if (str_starts_with($file['mime'], 'video/')) {
-        $info = shell_exec('ffprobe -v error -select_streams v:0 -show_entries stream=width,height,duration -of csv=p=0 ' . escapeshellarg($file_path));
-        [$width, $height, $duration] = explode(',', trim($info));
-        if ($duration == 'N/A') {
-            $file['resolution'] = sprintf('%sx%s', $width, $height);
-        } else {
-            $file['resolution'] = sprintf('%sx%s (%s seconds)', $width, $height, round($duration, 2));
-        }
-    } else if (str_starts_with($file['mime'], 'audio/')) {
-        $file['resolution'] = round(trim(shell_exec('ffprobe -v error -show_entries format=duration -of default=noprint_wrappers=1:nokey=1 ' . escapeshellarg($file_path))), 2) . ' seconds';
-    } else if (str_starts_with($file['mime'], 'text/')) {
-        $file['resolution'] = trim(shell_exec('wc -l < ' . escapeshellarg($file_path))) . ' lines';
+    if (isset($file['duration'])) {
+        array_push($file['resolution'], format_timestamp($file['duration']));
     }
+
+    if (isset($file['line_count'])) {
+        array_push($file['resolution'], sprintf('%d lines', $file['line_count']));
+    }
+
+    $file['resolution'] = implode(' ', $file['resolution']) ?: null;
 }
 
 $tos_exists = is_file($_SERVER['DOCUMENT_ROOT'] . '/static/TOS.txt');
@@ -144,7 +141,7 @@ $privacy_exists = is_file($_SERVER['DOCUMENT_ROOT'] . '/static/PRIVACY.txt');
                         <?php endif; ?>
                     </div>
                     <div class="row gap-8 grow align-bottom">
-                        <p>Uploaded <?= format_timestamp(time() - $file['uploaded_at']) ?> ago</p>
+                        <p>Uploaded <?= format_timestamp(time() - strtotime($file['uploaded_at'])) ?> ago</p>
                     </div>
                     <div class="row gap-8 grow align-bottom">
                         <?php if (FILE_COUNT_VIEWS && isset($file['views'])): ?>
@@ -160,8 +157,8 @@ $privacy_exists = is_file($_SERVER['DOCUMENT_ROOT'] . '/static/PRIVACY.txt');
                 <section class="box">
                     <div class="tab row gap-8">
                         <div class="grow">
-                            <?php if (isset($file['original_name'])): ?>
-                                <p><i><?= $file['original_name'] ?></i></p>
+                            <?php if (isset($file['title'])): ?>
+                                <p><i><?= $file['title'] ?></i></p>
                             <?php else: ?>
                                 <p>File <?= sprintf('%s.%s', $file['id'], $file['extension']) ?></p>
                             <?php endif; ?>
diff --git a/public/upload.php b/public/upload.php
index 11c0f15..4baea62 100644
--- a/public/upload.php
+++ b/public/upload.php
@@ -116,7 +116,7 @@ try {
         $file_data = [
             'size' => $file['size'],
             'mime' => $file_mime,
-            'extension' => $file_ext
+            'extension' => $file_ext,
         ];
     }
 
@@ -124,8 +124,11 @@ try {
         throw new RuntimeException('No URL or file specified');
     }
 
+    $db = new PDO(DB_URL, DB_USER, DB_PASS);
+
     $file_id_length = FILE_ID_LENGTH;
     $file_id_gen_attempts = 0;
+    $sql = 'SELECT id FROM files WHERE id = ? AND extension = ?';
     do {
         $file_id = FILE_ID_PREFIX . generate_random_char_sequence(FILE_ID_CHARACTERS, $file_id_length);
         if ($file_id_gen_attempts > 20) {
@@ -133,7 +136,10 @@ try {
             $file_id_gen_attempts = 0;
         }
         $file_id_gen_attempts++;
-    } while (is_file(FILE_UPLOAD_DIRECTORY . "/{$file_id}.{$file_data['extension']}"));
+
+        $stmt = $db->prepare($sql);
+        $stmt->execute([$file_id, $file_data['extension']]);
+    } while ($stmt->rowCount() > 0);
     $file_data['id'] = $file_id;
 
     if (isset($url)) {
@@ -160,9 +166,9 @@ try {
             delete_file($file_id, $file_data['extension']);
             throw new RuntimeException('Invalid file format.');
         }
-    } else if (isset($paste) && !file_put_contents(FILE_UPLOAD_DIRECTORY . sprintf('/%s.%s', $file_id, $file_data['extension']), $paste)) {
+    } else if (isset($paste) && !file_put_contents($file_path = FILE_UPLOAD_DIRECTORY . sprintf('/%s.%s', $file_id, $file_data['extension']), $paste)) {
         throw new RuntimeException('Failed to paste a text! Try again later.');
-    } else if (isset($file) && !move_uploaded_file($file['tmp_name'], FILE_UPLOAD_DIRECTORY . sprintf('/%s.%s', $file_id, $file_data['extension']))) {
+    } else if (isset($file) && !move_uploaded_file($file['tmp_name'], $file_path = FILE_UPLOAD_DIRECTORY . sprintf('/%s.%s', $file_id, $file_data['extension']))) {
         throw new RuntimeException("Failed to save the file. Try again later.");
     }
 
@@ -196,11 +202,36 @@ try {
         throw new RuntimeException("Failed to create a thumbnail (Error code {$thumbnail_error})");
     }
 
+    // getting file metadata
+    $file_data['metadata'] = [
+        'width' => null,
+        'height' => null,
+        'duration' => null,
+        'line_count' => null,
+    ];
+    $metadata_should_be_created = in_array(explode('/', $file_data['mime'])[0], ['image', 'video', 'audio', 'text']);
+
+    if (str_starts_with($file_data['mime'], 'image/')) {
+        [$width, $height] = explode('x', trim(shell_exec('identify -format "%wx%h" ' . escapeshellarg($file_path) . '[0]')));
+        $file_data['metadata']['width'] = intval($width);
+        $file_data['metadata']['height'] = intval($height);
+    } else if (str_starts_with($file_data['mime'], 'video/')) {
+        $info = shell_exec('ffprobe -v error -select_streams v:0 -show_entries stream=width,height,duration -of csv=p=0 ' . escapeshellarg($file_path));
+        [$width, $height, $duration] = explode(',', trim($info));
+        $file_data['metadata']['width'] = intval($width);
+        $file_data['metadata']['height'] = intval($height);
+        $file_data['metadata']['duration'] = $duration == 'N/A' ? null : intval(round($duration, 2));
+    } else if (str_starts_with($file_data['mime'], 'audio/')) {
+        $file_data['metadata']['duration'] = intval(round(trim(shell_exec('ffprobe -v error -show_entries format=duration -of default=noprint_wrappers=1:nokey=1 ' . escapeshellarg($file_path))), 2));
+    } else if (str_starts_with($file_data['mime'], 'text/')) {
+        $file_data['metadata']['line_count'] = intval(trim(shell_exec('wc -l < ' . escapeshellarg($file_path))));
+    }
+
     $file_data['urls'] = [
         'download_url' => INSTANCE_URL . "/{$file_data['id']}.{$file_data['extension']}"
     ];
 
-    if (FILE_METADATA && FILE_DELETION) {
+    if (FILE_DELETION) {
         $file_data['password'] = $_POST['password'] ?? generate_random_char_sequence(FILE_ID_CHARACTERS, FILE_DELETION_KEY_LENGTH);
         $file_data['urls']['deletion_url'] = INSTANCE_URL . "/delete.php?f={$file_data['id']}.{$file_data['extension']}&key={$file_data['password']}";
     }
@@ -212,30 +243,36 @@ try {
         $file_data
     );
 
-    if (FILE_METADATA) {
-        unset($file_data['urls']);
-        $file_data['password'] = password_hash($file_data['password'], PASSWORD_DEFAULT);
-        $file_data['views'] = 0;
-        $file_data['uploaded_at'] = time();
+    $file_data['password'] = isset($file_data['password']) ? password_hash($file_data['password'], PASSWORD_DEFAULT) : null;
+    $file_data['views'] = 0;
+    $file_data['uploaded_at'] = time();
 
-        if ($title) {
-            $file_data['original_name'] = $title;
-        }
+    if ($title) {
+        $file_data['original_name'] = $title;
+    }
 
-        if ($preserve_original_name && !$title) {
-            if ($file && !empty($file['name'])) {
-                $file_data['original_name'] = $file['name'];
-            } else if ($url) {
-                $file_data['original_name'] = $url;
-            }
+    if ($preserve_original_name && !$title) {
+        if ($file && !empty($file['name'])) {
+            $file_data['original_name'] = $file['name'];
+        } else if ($url) {
+            $file_data['original_name'] = $url;
         }
+    }
 
-        if (!is_dir(FILE_METADATA_DIRECTORY) && !mkdir(FILE_METADATA_DIRECTORY, 0777, true)) {
-            throw new RuntimeException('Failed to create a folder for file metadata');
-        }
-        if (!file_put_contents(FILE_METADATA_DIRECTORY . "/{$file_data['id']}.metadata.json", json_encode($file_data, JSON_UNESCAPED_SLASHES))) {
-            throw new RuntimeException('Failed to create a file metadata');
-        }
+    $db->prepare('INSERT INTO files(id, mime, extension, size, title, password) VALUES (?, ?, ?, ?, ?, ?)')
+        ->execute([
+            $file_data['id'],
+            $file_data['mime'],
+            $file_data['extension'],
+            $file_data['size'],
+            $file_data['original_name'] ?? null,
+            $file_data['password']
+        ]);
+
+    if ($metadata_should_be_created) {
+        $file_data['metadata']['id'] = $file_data['id'];
+        $db->prepare('INSERT INTO file_metadata(width, height, duration, line_count, id) VALUES (?, ?, ?, ?, ?)')
+            ->execute(array_values($file_data['metadata']));
     }
 } catch (RuntimeException $e) {
     generate_alert(