<?php
namespace Tlf;
/**
*
*/
class BigDb {
/** for convenience sql commands like select, insert, update, delete */
use BigDb\SqlVerbs;
static public function mysql(string $user, string $password, string $db, $host='localhost'): BigDb {
$pdo = new \PDO("mysql:dbname={$db};host={$host}", $user, $password);
$pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
$bdb = new static($pdo);
return $bdb;
}
static public function mysql_pdo(string $user, string $password, string $db, $host='localhost'): \PDO {
$pdo = new \PDO("mysql:dbname={$db};host={$host}", $user, $password);
$pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
return $pdo;
}
/**
*
* @param $file_path path to a json file with configs: user, password, database, host
* @param $prefix for the keys that, such as `'mysql.'`
*/
static public function from_config(string $file_path, string $prefix=''){
$config_data = json_decode(file_get_contents($file_path), true);
if ($prefix == '')$config = $config_data;
else {
$config = [];
$len = strlen($prefix);
foreach ($config_data as $key=>$value){
if (substr($key, 0, $len) !== $prefix)continue;
$config[substr($key,$len)] = $value;
}
}
$bdb = static::mysql($config['user'],$config['password'],$config['database'],$config['host']??'localhost');
return $bdb;
}
protected \PDO $pdo;
public \Tlf\LilDb $ldb;
protected \Tlf\BigDb\AccessLayer $access_layer;
/**
* Query strings
*/
protected array $queries = [];
/**
* Namespaces to load ORM objects from
*/
protected array $namespaces = [];
public function __construct(\PDO $pdo){
$this->pdo = $pdo;
$this->ldb = new \Tlf\LilDb($pdo);
}
public function addOrmNamespace(string $namespace){
$this->namespaces[] = $namespace;
}
public function setAccessLayer(\Tlf\BigDb\AccessLayer $access_layer){
$this->access_layer = $access_layer;
}
/**
* Get the query string for a given key.
*/
public function get_query(string $key){
return $this->queries[$key];
}
/**
* Simply returns `$this->pdo`, which is used for all queries
*/
public function get_pdo(): \PDO {
return $this->pdo;
}
/**
* @param $class_name class name without namespace
*
* @return fully qualified class name
*/
public function get_orm_class(?string $class_name): string{
if ($class_name==null)return 'Tlf\\BigOrm';
if (class_exists($class_name,true))return $class_name;
$class = null;
foreach ($this->namespaces as $ns){
$class = $ns.'\\'.$class_name;
if (!class_exists($class,true))$class = null;
}
if ($class == null)$class = 'Tlf\\BigOrm';
return $class;
}
public function new(string $table, array $initial_values = [], ?string $class_name = null){
$class = $this->get_orm_class($class_name ?? $table);
$obj = new $class($this, $initial_values);
return $obj;
}
/**
* Run a query, fetch all rows (as classes) & return them
*
* @param $query sql
* @param $bind_columns array of key/value pairs to bind to the query
* @param $class_name a class name without namespace (see `addOrmNamespace()`)
* @return array of BigOrm instances
*
*/
public function query(string $query, array $bind_columns, ?string $class_name=null): array{
$stmt = $this->pdo->prepare($query);
$stmt->execute($bind_columns);
$class = $this->get_orm_class($class_name);
$items = $stmt->fetchAll(\PDO::FETCH_CLASS, $class, [$this]);
return $items;
}
/**
* @param $query_key the key that points to the stored query
* @param $class_name the class name (without namespace)
*/
public function load(string $query_key, ?string $class_name = null): array {
$query = $this->queries[$query_key];
return $this->query($query, [], $class_name);
}
public function from_id(string $table, int $id, ?string $class_name = null): ?BigOrm {
if ($class_name==null)$class_name = $table;
$class = $this->get_orm_class($class_name);
// $this->query("SELECT * FROM `$table` WHERE `id` = :id", ['id'=>$id], 'article')
$items = $this->query("SELECT * FROM `$table` WHERE `id` = :id ", ['id'=>$id], 'Article');
$item = $items[0] ?? null;
return $item;
}
/**
* @param $query_params array of values to `=`
*/
public function one(string $table, array $query_params, ?string $class_name = null): ?BigOrm {
if ($class_name==null)$class_name = $table;
$class = $this->get_orm_class($class_name);
// $this->query("SELECT * FROM `$table` WHERE `id` = :id", ['id'=>$id], 'article')
$items = $this->ldb->select($table, $query_params);
if (count($items)==0)return null;
return new $class($this, $items[0]);
}
/**
* Get an array of objects that point to this item
*
* @param $that the table name to select from
* @param $id_column the column to select against. Ex: `item_id` thus `this.id = that.item_id
*
* @return an array of rows
*/
public function many(string $table, string $where_column, $where_value, ?string $class_name = null): array {
if ($class_name = null)$class_name = $table;
$id = $this->id;
$items = $this->query("SELECT * FROM `table` WHERE `$where_column` = :value",['value'=>$where_value],$class_name);
return $items;
}
/**
*
* Execute a stored sql query & return the pdo statement
*
* @param $query_key the key that points to the stored query
* @param $params EXPERIMENTAL key/value array to bind to the query, except it is done via `str_replace(':key', $pdo->quote($value));
* @return Same as `PDO::exec($query)`;
*/
public function exec(string $query_key, array $params = []): int|false {
$query = $this->queries[$query_key];
// echo "\n\n".$query."\n\n";
foreach ($params as $key=>$value){
$query = str_replace(":$key", $this->pdo->quote($value), $query);
}
return $this->pdo->exec($query);
}
public function get_serial_file_path(string $dir, ?string $file_prefix): string {
$file_prefix = ($file_prefix == null) ? '' : $file_prefix.'.';
$serial_file = $dir.'/'.$file_prefix.'queries';
return $serial_file;
}
/**
* Check mtime of serialized file and delete it if
* @param $dir
*/
public function check_serialized_file(string $dir, ?string $file_prefix = ''){
$file_path = $this->get_serial_file_path($dir, $file_prefix);
if (!file_exists($file_path))return;
$serial_mtime = filemtime($file_path);
$dir = dirname($file_path);
// var_dump($dir);
// var_dump($serial_mtime);
foreach (scandir($dir) as $file){
if (is_dir($dir.'/'.$file))continue;
// echo 'checked!';
// var_dump($dir.'/'.$file);
if (filemtime($dir.'/'.$file) > $serial_mtime) goto delete;
}
return;
delete:
unlink($file_path);
}
/**
*
* Loads queries from a file containing a serialized array. If the serialized file does not exist, `$dir` will be scanned, queries built, and the file will be created. There is no cache invalidation, so delete the serialized file to regenerate.
* @param $dir a directory containing `.sql` files with the LilSql formatting
* @param $namespace file prefix for query files. Ex: pass `"form"` to load files like `"form.create.sql"` and `form.query.sql`
*/
public function load_queries(string $dir, string $file_prefix = '', string $query_key_prefix = ''){
$serial_file = $this->get_serial_file_path($dir, $file_prefix);
$prefix = $query_key_prefix == null ? '' : $query_key_prefix .'.';
if (file_exists($serial_file)){
$this->queries = array_merge(
$this->queries,
unserialize(file_get_contents($serial_file))
);
return;
}
$ls = new \Tlf\LilSql();
$ls->load_files($dir, $prefix);
$ls->serialize($serial_file);
if (!file_exists($serial_file)){
throw new \Exception("Failed to write serialized file '$serial_file'");
}
return $this->load_queries($dir, $file_prefix);
}
}