364 lines
9.6 KiB
PHP
364 lines
9.6 KiB
PHP
<?php
|
|
|
|
/**
|
|
* SQLite database wrapper class. Provides a non-static interface to interact with a
|
|
* SQLite database in as simple a way as possible.
|
|
*
|
|
* The wrapper provides a simple-to-use database API using PDO's SQLite driver.
|
|
* It was designed to have method signatures very close to Laravel's Eloquent ORM. There
|
|
* are not, however, any ORM features in the wrapper. These must be handled at
|
|
* the model level.
|
|
*
|
|
* By default, foreign keys are enabled and the database is in WAL mode. In general
|
|
* these defaults should be perfect for most use cases.
|
|
*/
|
|
class Database
|
|
{
|
|
/**
|
|
* The PDO handle.
|
|
*/
|
|
private PDO $c;
|
|
|
|
/**
|
|
* The current working table.
|
|
*/
|
|
private string $table = '';
|
|
|
|
/**
|
|
* The number of queries executed.
|
|
*/
|
|
private int $queries = 0;
|
|
|
|
/**
|
|
* The log of executed queries.
|
|
*/
|
|
private array $log = [];
|
|
|
|
/**
|
|
* The last error.
|
|
*/
|
|
private string $error = '';
|
|
|
|
/**
|
|
* The array for the query builder.
|
|
*/
|
|
private array $builder = [
|
|
'where' => [],
|
|
'order' => [],
|
|
'limit' => 0,
|
|
'values' => [],
|
|
];
|
|
|
|
/**
|
|
* Open a connection to the database.
|
|
*/
|
|
public function __construct(string $path, array $opts = [])
|
|
{
|
|
$opts = !empty($opts) ? $opts : [
|
|
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
|
|
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
|
|
];
|
|
|
|
try {
|
|
$this->c = new PDO("sqlite:$path", null, null, $opts);
|
|
$this->c->exec('PRAGMA foreign_keys = ON;'); // Enable foreign keys
|
|
$this->c->exec('PRAGMA journal_mode = WAL;'); // Enable WAL
|
|
} catch (PDOException $e) {
|
|
$this->error = "Failed to open database: " . $e->getMessage();
|
|
throw $e;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Change the current working table.
|
|
*/
|
|
public function table(string $name): Database
|
|
{
|
|
$this->table = $name;
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* Create a table with the name of the current working table. Will drop
|
|
* the table if it already exists.
|
|
*/
|
|
public function create(array $columns): PDOStatement|false
|
|
{
|
|
$query = "CREATE TABLE IF NOT EXISTS $this->table (" . implode(', ', $columns) . ');';
|
|
$this->addToLog($query);
|
|
return $this->c->query($query);
|
|
}
|
|
|
|
/**
|
|
* Drop the current working table if it exists.
|
|
*/
|
|
public function drop(): PDOStatement|false
|
|
{
|
|
$query = "DROP TABLE IF EXISTS $this->table;";
|
|
$this->addToLog($query);
|
|
return $this->c->query($query);
|
|
}
|
|
|
|
/**
|
|
* Return the current working table's schema.
|
|
*/
|
|
public function schema(): array
|
|
{
|
|
$query = "PRAGMA table_info($this->table);";
|
|
$this->addToLog($query);
|
|
return $this->c->query($query)->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
/**
|
|
* Insert data into the current working table. Pass an array of key/value
|
|
* pairs to insert one record, or an array of arrays to insert multiple. Returns
|
|
* the number of rows inserted, or false on failure.
|
|
*/
|
|
public function insert(array $data): int|false
|
|
{
|
|
// If the first element is not an array we can assume we're doing a single insert.
|
|
if (!isset($data[0])) {
|
|
$query = "INSERT INTO $this->table (" . implode(', ', array_keys($data)) . ')'
|
|
. ' VALUES (' . implode(', ', array_fill(0, count($data), '?')) . ');';
|
|
$this->addToLog($query);
|
|
return $this->c->prepare($query)->execute(array_values($data));
|
|
}
|
|
|
|
// Otherwise we will build a multi-insert query.
|
|
$query = "INSERT INTO $this->table (" . implode(', ', array_keys($data[0])) . ') VALUES ';
|
|
$placeholders = [];
|
|
$values = [];
|
|
|
|
foreach ($data as $row) {
|
|
$placeholders[] = '(' . implode(', ', array_fill(0, count($row), '?')) . ')';
|
|
foreach ($row as $value) $values[] = $value;
|
|
}
|
|
|
|
$query .= implode(', ', $placeholders) . ';';
|
|
|
|
$this->addToLog($query);
|
|
return $this->c->prepare($query)->execute($values);
|
|
}
|
|
|
|
/**
|
|
* Add a where clause to the builder. All values are paramaterized.
|
|
*/
|
|
public function where(string $column, string $operator, mixed $value): Database
|
|
{
|
|
$this->builder['where'][] = "$column $operator ?";
|
|
$this->builder['values'][] = $value;
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* Add an order clause to the builder.
|
|
*/
|
|
public function order(string $column, string $direction = 'ASC'): Database
|
|
{
|
|
$this->builder['order'] = "$column $direction";
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* Set a limit in the builder.
|
|
*/
|
|
public function limit(int $limit): Database
|
|
{
|
|
$this->builder['limit'] = $limit;
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* Build a select query and return the result. By default selects the entire
|
|
* row. Optionally use fetchAll, but there is also the selectAll() alias method
|
|
* for this.
|
|
*/
|
|
public function select(string $what = '*', bool $fetchAll = false): array|false
|
|
{
|
|
$query = "SELECT $what FROM $this->table";
|
|
if (!empty($this->builder['where'])) $query .= " WHERE " . implode(' AND ', $this->builder['where']);
|
|
if (!empty($this->builder['order'])) $query .= " ORDER BY " . $this->builder['order'];
|
|
if (!empty($this->builder['limit'])) $query .= " LIMIT " . $this->builder['limit'];
|
|
|
|
$this->addToLog($query);
|
|
|
|
try {
|
|
$stmt = $this->c->prepare($query);
|
|
$stmt->execute($this->builder['values']);
|
|
} catch (PDOException $e) {
|
|
$this->error = "Failed to execute query: " . $e->getMessage();
|
|
return false;
|
|
}
|
|
|
|
$this->resetBuilder();
|
|
return $fetchAll ? $stmt->fetchAll() : $stmt->fetch();
|
|
}
|
|
|
|
/**
|
|
* Delete records from the current working table. Returns the number of rows
|
|
* deleted, or false on failure. Uses where clauses if set.
|
|
*/
|
|
public function delete(): int|false
|
|
{
|
|
$query = "DELETE FROM $this->table";
|
|
if (!empty($this->builder['where'])) $query .= " WHERE " . implode(' AND ', $this->builder['where']);
|
|
$this->addToLog($query);
|
|
|
|
$this->resetBuilder();
|
|
return $this->c->exec($query);
|
|
}
|
|
|
|
/**
|
|
* Update records in the current working table. Returns the number of rows
|
|
* updated, or false on failure. Uses where clauses if set.
|
|
*/
|
|
public function update(array $data): int|false
|
|
{
|
|
$query = "UPDATE $this->table SET " . implode(', ', array_keys($data)) . ' = ?';
|
|
if (!empty($this->builder['where'])) $query .= " WHERE " . implode(' AND ', $this->builder['where']);
|
|
$this->addToLog($query);
|
|
|
|
$values = array_merge(array_values($data), $this->builder['values']);
|
|
|
|
$this->resetBuilder();
|
|
return $this->c->prepare($query)->execute($values);
|
|
}
|
|
|
|
/**
|
|
* Get the number of rows in the current working table. Can use where clauses.
|
|
*/
|
|
public function count(): int|false
|
|
{
|
|
return $this->select('COUNT(*)', true)[0]['COUNT(*)'];
|
|
}
|
|
|
|
/**
|
|
* Alias for $db->select(true)
|
|
*/
|
|
public function selectAll(string $what = '*'): array|false
|
|
{
|
|
return $this->select($what, true);
|
|
}
|
|
|
|
/**
|
|
* Return whether the given table exists.
|
|
*/
|
|
public function tableExists(string $name): bool
|
|
{
|
|
$query = "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='$name';";
|
|
$this->addToLog($query);
|
|
return $this->c->query($query)->fetchColumn() > 0;
|
|
}
|
|
|
|
/**
|
|
* Return the PDO instance; for when more complex operations are needed.
|
|
*/
|
|
public function c(): PDO
|
|
{
|
|
return $this->c;
|
|
}
|
|
|
|
/**
|
|
* Alias for PDO::prepare
|
|
*/
|
|
public function prepare(string $query): PDOStatement
|
|
{
|
|
return $this->c->prepare($query);
|
|
}
|
|
|
|
/**
|
|
* Alias for PDO::lastInsertId
|
|
*/
|
|
public function lastInsertId(): int
|
|
{
|
|
return $this->c->lastInsertId();
|
|
}
|
|
|
|
/**
|
|
* Alias for PDO::exec
|
|
*/
|
|
public function exec(string $query): int
|
|
{
|
|
$this->addToLog($query);
|
|
return $this->c->exec($query);
|
|
}
|
|
|
|
/**
|
|
* Alias for PDO::beginTransaction
|
|
*/
|
|
public function begin(): bool
|
|
{
|
|
return $this->c->beginTransaction();
|
|
}
|
|
|
|
/**
|
|
* Alias for PDO::commit
|
|
*/
|
|
public function commit(): bool
|
|
{
|
|
return $this->c->commit();
|
|
}
|
|
|
|
/**
|
|
* Alias for PDO::rollBack
|
|
*/
|
|
public function rollBack(): bool
|
|
{
|
|
return $this->c->rollBack();
|
|
}
|
|
|
|
/**
|
|
* Alias for PDO::query
|
|
*/
|
|
public function query(string $query): PDOStatement|false
|
|
{
|
|
$this->addToLog($query);
|
|
return $this->c->query($query);
|
|
}
|
|
|
|
/**
|
|
* Add the query to the log and increment the number of queries executed.
|
|
*/
|
|
private function addToLog(string $query): void
|
|
{
|
|
$this->log[] = $query;
|
|
$this->queries++;
|
|
}
|
|
|
|
/**
|
|
* Reset the builder to it's default structure.
|
|
*/
|
|
public function resetBuilder(): void
|
|
{
|
|
$this->builder = [
|
|
'where' => [],
|
|
'order' => [],
|
|
'limit' => 0,
|
|
'values' => [],
|
|
];
|
|
}
|
|
|
|
/**
|
|
* Return the log of executed queries.
|
|
*/
|
|
public function log(): array
|
|
{
|
|
return $this->log;
|
|
}
|
|
|
|
/**
|
|
* Return the number of queries executed.
|
|
*/
|
|
public function queries(): int
|
|
{
|
|
return $this->queries;
|
|
}
|
|
|
|
/**
|
|
* Return the last error message.
|
|
*/
|
|
public function error(): string
|
|
{
|
|
return $this->error;
|
|
}
|
|
} |