<?php
namespace Tlf;
/**
* A lil tiny database class for common sql functions like CREATE, UPDATE, SELECT, DELETE
*
* @tagline Simple interface for common SQL verbs like SELECT, CREATE, UPDATE, DELETE
*/
class LilDb {
/**
* a pdo instance
*/
public \PDO $pdo;
/**
* Convenience method to initialize with pdo
* @return Tlf\LilDb
*/
static public function new(string $user, string $password, string $db, $host='localhost') {
$pdo = new \PDO("mysql:dbname={$db};host={$host}", $user, $password);
$pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
$ldb = new static($pdo);
return $ldb;
}
/**
* Convenience method to initialize sqlite db in memory
* @return Tlf\LilDb
*/
static public function sqlite(string $dbName = ':memory:'){
$pdo = new \PDO('sqlite:'.$dbName);
$pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
$ldb = new static($pdo);
return $ldb;
}
/**
* Convenience method to initialize mysql db in memory
*/
static public function mysql($dbName = ':memory:'){
$pdo = new \PDO('mysql:'.$dbName);
$pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
$ldb = new static($pdo);
return $ldb;
}
/**
* Initialize with a db handle
* @param $pdo a pdo instance
*/
public function __construct(\PDO $pdo){
$this->pdo = $pdo;
}
/**
* Create a new table if it doesn't exist.
*
* @param2 $colDefinitions array of columns like: `['col_name'=>'VARCHAR(80)', 'col_two'=> 'integer']`
* @param3 $recreateIfExists true/false to include `DROP TABLE IF EXISTS table_name`
*/
public function create(string $tableName, array $colDefinitions, bool $recreateIfExists=false){
$colStatements = [];
foreach ($colDefinitions as $col => $definition){
$statement = '`'.$col.'` '. $definition;
$colStatements[] = $statement;
}
$colsSql = implode(", ", $colStatements);
$drop = $recreateIfExists ? "DROP TABLE IF EXISTS `{$tableName}`;\n" : '';
$sql =
<<<SQL
{$drop}
CREATE TABLE IF NOT EXISTS `{$tableName}`
(
{$colsSql}
)
;
SQL;
$this->exec($sql);
}
/**
* Execute an Sql statement & get rows back
* @throws if the statement fails to prepare
*/
public function query(string $sql, array $binds=[]) {
$pdo = $this->pdo;
$stmt = $pdo->prepare($sql);
if ($stmt===false){
$error = var_export($pdo->errorInfo(),true);
throw new \Exception("Sql problem: \n".$error."\n\n");
}
$stmt->execute($binds);
$rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
return $rows;
}
/**
* Get rows from a table with the given $whereCols
*/
public function select(string $tableName, array $whereCols=[]) {
$sql = "SELECT * FROM `{$tableName}` ";
$binds = static::keysToBinds($whereCols);
if (count($whereCols)>0){
$whereStr = "WHERE ".static::whereSqlFromCols($whereCols);
$sql .= $whereStr;
}
//echo "\n\n\nLilDb SELECT sql:\n";
//echo $sql;
//print_r($binds);
//echo "\n\n----\n\n";
$rows = $this->query($sql, $binds);
return $rows;
}
/**
* Insert a row into the database
* Converts array values to json
*
* @throws Exception if the insert fails
* @return the newly inserted id
*/
public function insert(string $table, array $row){
$pdo = $this->pdo;
$cols = [];
$binds = [];
foreach ($row as $key=>$value){
if (is_array($value))$value = json_encode($value);
$cols[] = $key;
$binds[":{$key}"] = $value;
}
$colsStr = '`'.implode('`, `',$cols).'`';
$bindsStr = implode(', ', array_keys($binds));
$query = "INSERT INTO `{$table}`({$colsStr})
VALUES ({$bindsStr})
";
$stmt = $pdo->prepare($query);
if ($stmt===false){
throw new \Exception("Could not insert values into databse.". print_r($pdo->errorInfo(),true));
}
$stmt->execute($binds);
if ($stmt->errorCode()!=='00000'){
print_r($stmt->errorInfo());
throw new \Exception("There was an error inserting data");
}
return $pdo->lastInsertId();
}
public function insertAll(string $table, array $rowSet){
$row = $rowSet[array_key_first($rowSet)] ?? null;
if ($row==null){
return 0;
}
$cols = [];
foreach ($row as $key=>$value){
$key = trim($key);
if (is_array($value))$value = json_encode($value);
$cols[] = $key;
}
$colsStr = '`'.implode('`, `',$cols).'`';
reset($rowSet);
$next = current($rowSet);
start_again:
$query = "INSERT INTO `{$table}`({$colsStr}) \n VALUES";
$all_binds = [];
$index = 0;
do {
echo "\n$index";
$pdo = $this->pdo;
$bind_keys = [];
foreach ($next as $key=>$value){
$key = trim($key);
$key = "{$key}_{$index}";
if (is_array($value))$value = json_encode($value);
$all_binds[":{$key}"] = $value;
$bind_keys[] = ":{$key}";
}
$bindsStr = implode(', ', $bind_keys);
$query .= "\n ({$bindsStr})";
$index++;
$next = array_shift($rowSet);
if ($index > 1000 && $next){
goto finish_query;
}
if ($next) $query .= ",";
} while ($next);
finish_query:
$query .= " ;";
$stmt = $pdo->prepare($query);
if ($stmt===false){
throw new \Exception("Could not insert values into databse.". print_r($pdo->errorInfo(),true));
}
$stmt->execute($all_binds);
if ($stmt->errorCode()!=='00000'){
print_r($stmt->errorInfo());
throw new \Exception("There was an error inserting data");
}
if ($next)goto start_again;
return $pdo->lastInsertId();
}
/**
* Update an existing row. Shorthand for `updateWhere()` with the id column set as the where values.
*/
public function update(string $table, array $newRowValues, string $idColumnName='id'){
return $this->updateWhere($table, $newRowValues, [$idColumnName=>$newRowValues[$idColumnName]]);
}
/**
*
* @param $whereVals To update ALL rows, pass `[]`
*/
public function updateWhere(string $table, array $newRowValues, array $whereVals){
$valueBinds = [];
$setSql = [];
foreach ($newRowValues as $col=>$value){
if (is_array($value))$value = json_encode($value);
$valueBinds[$bindKey=':'.$col.'_value'] = $value;
$setSql[] = "`$col` = $bindKey";
}
$setSql = implode(",\n", $setSql);
$whereSql = static::whereSqlFromCols($whereVals);
if (strlen(trim($whereSql))>0)$whereSql = "WHERE\n{$whereSql}";
$sql = <<<SQL
UPDATE `{$table}`
SET $setSql
{$whereSql}
SQL;
$binds = array_merge($valueBinds, $whereVals);
$binds = static::keysToBinds($binds);
$stmt = $this->execute($sql,$binds);
return $stmt->rowCount();
}
/**
* Delete rows from a table
* @return true if any rows were deleted. false otherwise
*/
public function delete(string $table, array $whereCols){
$sql = static::whereSqlFromCols($whereCols);
if ($sql!=null)$sql = 'WHERE '.$sql;
$sql = "DELETE FROM `{$table}` {$sql}";
$stmt = $this->execute($sql, $whereCols);
// var_dump($stmt->errorCode());
// exit;
// var_dump($stmt->rowCount());
// exit;
if ($stmt->errorCode()=='00000'
&&$stmt->rowCount()>0)return true;
return false;
// return $stmt;
}
/**
* Execute an Sql statement & get a PDOStatement back
* @throws if the statement fails to prepare
* @return PDOStatement
*/
public function execute(string $sql, array $binds=[]) {
$pdo = $this->pdo;
$stmt = $pdo->prepare($sql);
if ($stmt===false){
$error = var_export($pdo->errorInfo(),true);
throw new \Exception("Sql problem: \n".$error."\n\n");
}
$stmt->execute($binds);
return $stmt;
}
/**
* Alias for `execute()`
* @return PDOStatement
*/
public function exec(string $sql, array $binds=[]) {
return $this->execute($sql, $binds);
}
/** get the pdo object
*/
public function getPdo(){
return $this->pdo;
}
/** get the pdo object
*/
public function pdo(){
return $this->pdo;
}
/**
* Convert key=>value array into a 'WHERE' sql.
*
* @param $columns `['key'=>$val, ':key2'=>$val]`. `$val` can be string, array, or numeric.
* @return string sql for a WHERE statement. Does not include `WHERE`
* @exampleOutput: `key = :val1 AND key2 LIKE :val2, AND key3 IN (:val3_1,:val3_2)`.
*/
static public function whereSqlFromCols(array $columns){
$binds = static::keysToBinds($columns);
//generate sql
$pieces = [];
$copy = $binds;
foreach ($copy as $k=>$v){
$col = substr($k,1);
if (is_string($v)){
if (mb_check_encoding($v, 'UTF-8')){
$pieces[] = "`$col` LIKE $k";
} else {
// probably binary & not a regular string, according to stackoverflow
// see https://stackoverflow.com/a/76816100/802469
// BINARY mysql columns are not friendly with LIKE & produce unexpected results.
// This caused SOME uuids to succeed comparison, and others to fail
// So I added this pseudo-binary check to switch the operator from LIKE to = when
// when we THINK the value is binary.
$pieces[] = "`$col` = $k";
}
} else if (is_array($v)){
unset($binds[$k]);
$inList = [];
foreach ($v as $index=>$inValue){
$inKey = $k.$index;
$binds[$inKey] = $inValue;
$inList[] = $inKey;
}
$pieces[] = "`$col` IN (".implode(', ',$inList).")";
} else {
$pieces[] = "`$col` = $k";
}
}
$sql = implode(' AND ', $pieces);
return $sql;
}
/**
* Convert an array `['key'=>$val, ':key2'=>$val]` into binds: `[':key'=>$val, ':key2'=>$val]`.
*
* @return array where keys are prefixed with a colon (:)
*/
static public function keysToBinds(array $keyedValues){
$binds = [];
foreach ($keyedValues as $k=>$v){
if (!is_string($k)){
$binds[] = $v;
} else if (substr($k,0,1)==':'){
$binds[$k] = $v;
} else {
$binds[':'.$k] = $v;
}
}
return $binds;
}
}