From 9e379afd368ea6b8cfc6852a8265eba31fc3a758 Mon Sep 17 00:00:00 2001
From: alyx <alyx@aleteoryx.me>
Date: Fri, 24 May 2024 23:07:06 -0400
Subject: DBs complete, stablize filedatabase ordering

---
 visitors.php | 65 +++++++++++++++++++++++++++++++++++++++++++++++-------------
 1 file changed, 51 insertions(+), 14 deletions(-)

diff --git a/visitors.php b/visitors.php
index e6c106a..47992d0 100644
--- a/visitors.php
+++ b/visitors.php
@@ -77,6 +77,8 @@ $config['db'] = 'visitors.csv';
 /* --- END OF CONFIG, CODE BELOW, PROBABLY DON'T EDIT PAST THIS POINT --- */
 
 
+/* DATABASES */
+
 // db_row: ['id' => int (only for list_rows), 'name' => string, 'message' => string, 'email' => ?string, 'website' => ?string, 'timestamp' => string (or DateTimeInterface for list_rows)]
 // message is pre-escaped at insert time.
 // timestamp is DateTimeInterface::ISO8601_EXPANDED format.
@@ -99,12 +101,14 @@ abstract class Database {
     return $this->_delete_row($id);
   }
 
+  abstract public function __construct(string $file);
+
   abstract protected function _append_row(array $db_row);
   abstract protected function _list_rows(): array;
   abstract protected function _delete_row(int $id): bool;
 }
 
-// notes: `id` is not stable
+// sigh, refactor later, it wont be a perf issue for any realistic number of entries.
 abstract class FileDatabase extends Database {
   protected string $file;
   protected array $data;
@@ -119,29 +123,24 @@ abstract class FileDatabase extends Database {
   }
 
   protected function _append_row(array $db_row) {
-    array_unshift($this->data, $db_row);
+    array_unshift($this->data, array(...$db_row, 'id' => max(array_map(fn($a) => $a['id'], $this->data))));
     $this->save();
   }
 
   protected function _list_rows(): array {
     $data = $this->data;
 
-    foreach($data as $id => &$row) {
-      $row['id'] = $id;
-    }
-
     return $data;
   }
 
   protected function _delete_row(int $id): bool {
-    if (!isset($this->data[$id]))
-      return false;
-
-    unset($this->data[$id]);
-    $this->data = array_values($this->data);
-    $this->save();
-
-    return true;
+    foreach(array_keys($this->data) as $key)
+      if ($this->data[$key]['id'] === $id) {
+        unset($this->data[$key]);
+        $this->save();
+        return true;
+      }
+    return false;
   }
 
   abstract protected function save();
@@ -191,3 +190,41 @@ final class CsvDatabase extends FileDatabase {
     return array_map(fn($s) => array_combine(self::KEY_ORDER, str_getcsv($s)), file($file));
   }
 }
+
+final class SqliteDatabase extends Database {
+  private Sqlite3 $handle;
+
+  public function __construct(string $file) {
+    $this->handle = new Sqlite3($file);
+    if (!$this->handle->querySingle('SELECT count(*) FROM sqlite_master'))
+      $this->handle->exec('CREATE TABLE visitors (id INTEGER PRIMARY KEY, name TEXT NOT NULL, message TEXT NOT NULL, email TEXT, website TEXT, timestamp TEXT NOT NULL)');
+  }
+
+  protected function _append_row(array $db_row) {
+    $stmt = $this->handle->prepare('INSERT INTO visitors (name, message, email, website, timestamp) VALUES (:name, :message, :email, :website, :timestamp)');
+    foreach($db_row as $key => $value)
+      $stmt->bindValue($key, $value);
+    $stmt->execute();
+  }
+  protected function _list_rows(): array {
+    $rows = array();
+
+    $res = $this->handle->query('SELECT * FROM visitors ORDER BY id DESC');
+    while ($row = $res->fetchArray(SQLITE3_ASSOC))
+      $rows[] = $row;
+
+    return $rows;
+  }
+  protected function _delete_row(int $id): bool {
+    $stmt = $this->handle->prepare('SELECT count(*) FROM visitors WHERE id=:id');
+    $stmt->bindValue('id', $id);
+    if (!$stmt->execute()->fetchArray(SQLITE3_NUM)[0])
+      return false;;
+
+    $stmt = $this->handle->prepare('DELETE FROM visitors WHERE id=:id');
+    $stmt->bindValue('id', $id);
+    $stmt->execute();
+
+    return true;
+  }
+}
-- 
cgit v1.2.3-70-g09d2