<?php
namespace RDB;
trait SqlVerbs {
/**
* Convert an array `['key'=>$val, ':key2'=>$val]` into binds: `[':key'=>$val, ':key2'=>$val]`. Very forgiving
*
* @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;
}
/**
* Convert an array `['key'=>$val, ':key2'=>$val]` into a 'WHERE' sql: `key = :key AND key2 LIKE :key2`. Very forgiving
* string value yields LIKE. array value yields IN (list). Other yields =
* Returned string does NOT include 'WHERE'
*
* @return string sql for a WHERE statement
*/
static public function colsToWhereSql($row){
$binds = static::keysToBinds($row);
//generate sql
$pieces = [];
$copy = $binds;
foreach ($copy as $k=>$v){
$col = substr($k,1);
if (is_string($v)){
$pieces[] = "`$col` LIKE $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;
}
/**
* Create a bean, assign the values & store it
*
* @return bean
*/
static public function insert($type, array $values=[]){
$bean = static::dispense($type);
foreach ($values as $key=>$value){
$bean->$key = $value;
}
\RDB::store($bean);
return $bean;
}
/**
*
* Update all beans matching the given $whereVals or $values[id] if count($whereVals)==0
*
* @param $type the type of bean/table name
* @param $values the new values to set. set `$values[id]` and omit `$whereVals` for a shorthand
* @param $whereVals Existing values to identify the rows you're updating
*/
static public function update($type, array $values, array $whereVals=[]){
if (count($whereVals)==0&&!isset($values['id'])){
static::handleException('update',
new \Exception("You must provide an 'id' in \$values array or provide a \$whereVals array")
);
}
$beans = null;
if (count($whereVals)==0){
$beans = static::select($type, ['id'=>$values['id']]);
} else {
$beans = static::select($type, $whereVals);
}
foreach ($beans as $bean){
foreach ($values as $k=>$v){
$bean->$k = $v;
}
}
\RDB::storeAll($beans);
}
/**
* Delete rows from database via raw sql (no beans involved)
*
* @param $type the bean type / table to delete from
* @param $whereVals Existing values to identify the rows you're deleting. Passing an empty array will delete all items of $type, unless $sql is provided.
* @param $sql OPTIONAL sql to use for your WHERE statement. Don't include 'WHERE' verb. $whereVals are bound to the query
*/
static public function delete(string $type, ?array $whereVals=null, ?string $sql=null){
$type = static::cleanType($type);
if ($whereVals===null){
static::handleException('delete',
new \Exception("You MUST provide a \$whereVals array. To delete all of '$type', pass an empty array.")
);
}
if ($sql===null){
$sql = static::colsToWhereSql($whereVals);
}
if ($sql!=null)$sql = 'WHERE '.$sql;
$sql = "DELETE FROM `{$type}` {$sql}";
static::execute($sql, $whereVals);
}
/**
* Select rows from database as beans
*
* @param $type the bean type / table to select from
* @param $whereVals values to search for
* @param $sql OPTIONAL sql to use for your WHERE statement. Don't include 'WHERE' verb. $whereVals are bound to your sql
* @return array of beans
*/
static public function select(string $type, array $whereVals=[], ?string $sql=null){
$binds = [];
$clean = [];
$binds = static::keysToBinds($whereVals);
if ($sql===null){
$sql = static::colsToWhereSql($binds);
}
$items = static::find($type, $sql, $binds);
$items = array_values($items);
return $items;
}
}