aboutsummaryrefslogtreecommitdiffstats
path: root/visitors.php
diff options
context:
space:
mode:
authoralyx <alyx@aleteoryx.me>2024-05-24 23:07:06 -0400
committeralyx <alyx@aleteoryx.me>2024-05-24 23:07:06 -0400
commit9e379afd368ea6b8cfc6852a8265eba31fc3a758 (patch)
tree0b5ccd9bc95f2349cc16d55877d6b90e0ff1c0ef /visitors.php
parent0da78f77ebe4d97c9babc92836ab3147256a7305 (diff)
downloadvisitors_dot_php-9e379afd368ea6b8cfc6852a8265eba31fc3a758.tar.gz
visitors_dot_php-9e379afd368ea6b8cfc6852a8265eba31fc3a758.tar.bz2
visitors_dot_php-9e379afd368ea6b8cfc6852a8265eba31fc3a758.zip
DBs complete, stablize filedatabase ordering
Diffstat (limited to 'visitors.php')
-rw-r--r--visitors.php65
1 files 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;
+ }
+}