LilDb.php

<?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;
    }
}