summaryrefslogtreecommitdiff
path: root/engine/database
diff options
context:
space:
mode:
Diffstat (limited to 'engine/database')
-rw-r--r--engine/database/CommonDatabase.php107
-rw-r--r--engine/database/MySQLConnection.php79
-rw-r--r--engine/database/SQLiteConnection.php90
3 files changed, 276 insertions, 0 deletions
diff --git a/engine/database/CommonDatabase.php b/engine/database/CommonDatabase.php
new file mode 100644
index 0000000..13ea79c
--- /dev/null
+++ b/engine/database/CommonDatabase.php
@@ -0,0 +1,107 @@
+<?php
+
+abstract class CommonDatabase {
+
+ abstract public function query(string $sql, ...$args);
+ abstract public function escape(string $s): string;
+ abstract public function fetch($q): ?array;
+ abstract public function fetchAll($q): ?array;
+ abstract public function fetchRow($q): ?array;
+ abstract public function result($q, int $field = 0);
+ abstract public function insertId(): ?int;
+ abstract public function numRows($q): ?int;
+
+ protected function prepareQuery(string $sql, ...$args): string {
+ global $config;
+ if (!empty($args)) {
+ $mark_count = substr_count($sql, '?');
+ $positions = array();
+ $last_pos = -1;
+ for ($i = 0; $i < $mark_count; $i++) {
+ $last_pos = strpos($sql, '?', $last_pos + 1);
+ $positions[] = $last_pos;
+ }
+ for ($i = $mark_count - 1; $i >= 0; $i--) {
+ $arg_val = $args[$i];
+ if (is_null($arg_val)) {
+ $v = 'NULL';
+ } else {
+ $v = '\''.$this->escape($arg_val) . '\'';
+ }
+ $sql = substr_replace($sql, $v, $positions[$i], 1);
+ }
+ }
+ if (!empty($config['db']['log']))
+ logDebug(__METHOD__.': ', $sql);
+ return $sql;
+ }
+
+ public function insert(string $table, array $fields) {
+ return $this->performInsert('INSERT', $table, $fields);
+ }
+
+ public function replace(string $table, array $fields) {
+ return $this->performInsert('REPLACE', $table, $fields);
+ }
+
+ protected function performInsert(string $command, string $table, array $fields) {
+ $names = [];
+ $values = [];
+ $count = 0;
+ foreach ($fields as $k => $v) {
+ $names[] = $k;
+ $values[] = $v;
+ $count++;
+ }
+
+ $sql = "{$command} INTO `{$table}` (`" . implode('`, `', $names) . "`) VALUES (" . implode(', ', array_fill(0, $count, '?')) . ")";
+ array_unshift($values, $sql);
+
+ return $this->query(...$values);
+ }
+
+ public function update(string $table, array $rows, ...$cond) {
+ $fields = [];
+ $args = [];
+ foreach ($rows as $row_name => $row_value) {
+ $fields[] = "`{$row_name}`=?";
+ $args[] = $row_value;
+ }
+ $sql = "UPDATE `$table` SET ".implode(', ', $fields);
+ if (!empty($cond)) {
+ $sql .= " WHERE ".$cond[0];
+ if (count($cond) > 1)
+ $args = array_merge($args, array_slice($cond, 1));
+ }
+ return $this->query($sql, ...$args);
+ }
+
+ public function multipleInsert(string $table, array $rows) {
+ list($names, $values) = $this->getMultipleInsertValues($rows);
+ $sql = "INSERT INTO `{$table}` (`".implode('`, `', $names)."`) VALUES ".$values;
+ return $this->query($sql);
+ }
+
+ public function multipleReplace(string $table, array $rows) {
+ list($names, $values) = $this->getMultipleInsertValues($rows);
+ $sql = "REPLACE INTO `{$table}` (`".implode('`, `', $names)."`) VALUES ".$values;
+ return $this->query($sql);
+ }
+
+ protected function getMultipleInsertValues(array $rows): array {
+ $names = [];
+ $sql_rows = [];
+ foreach ($rows as $i => $fields) {
+ $row_values = [];
+ foreach ($fields as $field_name => $field_val) {
+ if ($i == 0) {
+ $names[] = $field_name;
+ }
+ $row_values[] = $this->escape($field_val);
+ }
+ $sql_rows[] = "('".implode("', '", $row_values)."')";
+ }
+ return [$names, implode(', ', $sql_rows)];
+ }
+
+} \ No newline at end of file
diff --git a/engine/database/MySQLConnection.php b/engine/database/MySQLConnection.php
new file mode 100644
index 0000000..9b473cb
--- /dev/null
+++ b/engine/database/MySQLConnection.php
@@ -0,0 +1,79 @@
+<?php
+
+class MySQLConnection extends CommonDatabase {
+
+ protected ?mysqli $link = null;
+
+ public function __construct(
+ protected string $host,
+ protected string $user,
+ protected string $password,
+ protected string $database) {}
+
+ public function __destruct() {
+ if ($this->link)
+ $this->link->close();
+ }
+
+ public function connect(): bool {
+ $this->link = new mysqli();
+ return !!$this->link->real_connect($this->host, $this->user, $this->password, $this->database);
+ }
+
+ public function query(string $sql, ...$args): mysqli_result|bool {
+ $sql = $this->prepareQuery($sql, ...$args);
+ $q = $this->link->query($sql);
+ if (!$q)
+ logError(__METHOD__.': '.$this->link->error."\n$sql\n".backtrace(1));
+ return $q;
+ }
+
+ public function fetch($q): ?array {
+ $row = $q->fetch_assoc();
+ if (!$row) {
+ $q->free();
+ return null;
+ }
+ return $row;
+ }
+
+ public function fetchAll($q): ?array {
+ if (!$q)
+ return null;
+ $list = [];
+ while ($f = $q->fetch_assoc()) {
+ $list[] = $f;
+ }
+ $q->free();
+ return $list;
+ }
+
+ public function fetchRow($q): ?array {
+ return $q?->fetch_row();
+ }
+
+ public function result($q, $field = 0) {
+ return $q?->fetch_row()[$field];
+ }
+
+ public function insertId(): int {
+ return $this->link->insert_id;
+ }
+
+ public function numRows($q): ?int {
+ return $q?->num_rows;
+ }
+
+ // public function affectedRows() {
+ // return $this->link->affected_rows;
+ // }
+ //
+ // public function foundRows() {
+ // return $this->fetch($this->query("SELECT FOUND_ROWS() AS `count`"))['count'];
+ // }
+
+ public function escape(string $s): string {
+ return $this->link->real_escape_string($s);
+ }
+
+}
diff --git a/engine/database/SQLiteConnection.php b/engine/database/SQLiteConnection.php
new file mode 100644
index 0000000..f124ced
--- /dev/null
+++ b/engine/database/SQLiteConnection.php
@@ -0,0 +1,90 @@
+<?php
+
+class SQLiteConnection extends CommonDatabase {
+
+ const SCHEMA_VERSION = 2;
+
+ protected SQLite3 $link;
+
+ public function __construct(string $db_path) {
+ $will_create = !file_exists($db_path);
+ $this->link = new SQLite3($db_path);
+ if ($will_create)
+ setperm($db_path);
+ $this->link->enableExceptions(true);
+ $this->upgradeSchema();
+ }
+
+ protected function upgradeSchema() {
+ $cur = $this->getSchemaVersion();
+ if ($cur == self::SCHEMA_VERSION)
+ return;
+
+ if ($cur < 1) {
+ $this->link->exec("CREATE TABLE users (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ username TEXT,
+ password TEXT
+ )");
+ }
+ if ($cur < 2) {
+ $this->link->exec("CREATE TABLE vk_processed (
+ last_message_time INTEGER
+ )");
+ $this->link->exec("INSERT INTO vk_processed (last_message_time) VALUES (0)");
+ }
+ $this->syncSchemaVersion();
+ }
+
+ protected function getSchemaVersion() {
+ return $this->link->query("PRAGMA user_version")->fetchArray()[0];
+ }
+
+ protected function syncSchemaVersion() {
+ $this->link->exec("PRAGMA user_version=".self::SCHEMA_VERSION);
+ }
+
+ public function query(string $sql, ...$params): SQLite3Result {
+ return $this->link->query($this->prepareQuery($sql, ...$params));
+ }
+
+ public function exec(string $sql, ...$params) {
+ return $this->link->exec($this->prepareQuery($sql, ...$params));
+ }
+
+ public function querySingle(string $sql, ...$params) {
+ return $this->link->querySingle($this->prepareQuery($sql, ...$params));
+ }
+
+ public function querySingleRow(string $sql, ...$params) {
+ return $this->link->querySingle($this->prepareQuery($sql, ...$params), true);
+ }
+
+ public function insertId(): int {
+ return $this->link->lastInsertRowID();
+ }
+
+ public function escape(string $s): string {
+ return $this->link->escapeString($s);
+ }
+
+ public function fetch($q): ?array {
+ // TODO: Implement fetch() method.
+ }
+
+ public function fetchAll($q): ?array {
+ // TODO: Implement fetchAll() method.
+ }
+
+ public function fetchRow($q): ?array {
+ // TODO: Implement fetchRow() method.
+ }
+
+ public function result($q, int $field = 0) {
+ return $q?->fetchArray()[$field];
+ }
+
+ public function numRows($q): ?int {
+ // TODO: Implement numRows() method.
+ }
+} \ No newline at end of file
generated by cgit v1.2.3 (git 2.25.1) at 2024-11-21 17:11:38 +0000