Data Access Object

DAO.php

<?php
namespace Todo;

// Data Access Object
abstract class DAO{
    protected array $row = [];
    public const KEYS = [];
    protected static string $table = '';

    public static function SetTable(string $table){
        static::$table = $table;
    }

    public static function GetTable(): string {
        return static::$table;
    }

    /**
     * Get a specific item in the row
     * @param string $key
     * @param $default
     * @return mixed|null
     */
    public function get(?string $key = null, $default = null){
        if (is_null($key)){
            return $this->row;
        }
        return $this->row[$key] ?? $default;
    }

    /**
     * Magic getter for values in in $this->row
     * @param $key
     * @return mixed
     * @throws \Exception
     */
    public function __get($key){
        if (isset($this->row[$key])){
            return $this->row[$key];
        }else{
            throw new \Exception("Invalid key: $key");
        }
    }

    /**
     * Get the underlying row
     * @return array
     */
    public function getRow(): array {
        return $this->row;
    }

    /**
     * Set a specific item in the row
     * @param string $key
     * @param $value
     * @return void
     * @throws \Exception
     */
    public function set(string $key, $value){
        if (!in_array($key, static::KEYS)){
            throw new \Exception($key.' is an invalid key.');
        }
        $this->row[$key] = $value;
    }

    /**
     * Magic setter
     * @param $key
     * @param $value
     * @return void
     * @throws \Exception
     */
    public function __set($key, $value){
        $this->set($key, $value);
    }

    public static function AssertSaveableRowKeysAreSameAsKEYS(array $row): bool {
        $keys = static::KEYS;
        $key = array_search('id', $keys);
        if ($key !== false) {
            unset($keys[$key]);
        }

        if (count($keys) != count($row)){
            return false;
        }
        foreach($row as $key => $value){
            if (!in_array($key, $keys)){
                return false;
            }
        }
        return true;
    }

    /**
     * Save the record (everything in $this->row
     * @return int|null
     * @throws \Exception
     */
    public function save(): ?int{
        if (empty($this->row['id'])){
            $this->row['id'] = $this->insert();
            if (!empty($this->row['id'])){
                return $this->row['id'];
            }else{
                return null;
            }
        }else{
            if ($this->update()){
                return $this->row['id'];
            }else{
                return null;
            }
        }
    }

    /**
     * Update everything stored in $this->row (besides id)
     * Must have a row['id'] or it will throw an exception.
     * @return bool
     * @throws \Exception
     */
    public function update(): bool {
        if (empty($this->row['id'])){
            throw new \Exception("No id or it is invalid, cannot update.");
        }
        $updrow =$this->row;
        static::AssertSaveableRowKeysAreSameAsKEYS($updrow);
        $sql = "UPDATE ".static::$table.' SET ';
        $sqlset = '';
        foreach($updrow as $key => $value){
            if ($key == 'id'){
                //do not set id, we will use it in the WHERE clause
                continue;
            }

            if (!empty($sqlset)) {
                $sqlset .= ', ';
            }
            $sqlset .= $key.' = :'.$key;
        }
        $sqlend = ' WHERE id = :id ';
        $sql .= $sqlset.$sqlend;
        return (bool) Db::execute($sql, $updrow);
    }

    /**
     * Insert row into database (ignores $this->row['id'] value)
     * @return int|null
     * @throws \Exception
     */
    public function insert(): ?int{
        $insrow = $this->row;
        unset($insrow['id']);
        if (!static::AssertSaveableRowKeysAreSameAsKEYS($insrow)){
            throw new \Exception(var_export(static::KEYS, true).' '.var_export($insrow, true). ' has invalid keys.');
        }
        $sql = "INSERT INTO ".static::$table.' ('.
            implode(', ', array_keys($insrow)).') VALUES(';
        $csvkeys = '';
        foreach($insrow as $key => $value){
            if (!empty($csvkeys)){
                $csvkeys .= ', ';
            }
            $csvkeys .= ':'.$key;
        }
        $sql .= $csvkeys;
        $sql .= ') ';

        $id = Db::insert($sql, $insrow);
        if (empty($id)){
            return null;
        }else{
            return $id;
        }
    }

    /**
     * Delete the specified row (permanently! (not soft delete))
     * @return bool
     * @throws \Exception
     */
    public function delete(): bool {
        if (empty($this->row['id'])){
            throw new \Exception("Empty id, cannot delete");
        }
        $sql = 'DELETE FROM '.static::$table.' WHERE id = :id';
        $params = ['id'=>$this->id];
        return Db::execute($sql, $params);
    }

    /**
     * Fetch first row from database table into this object
     * @param array|null $conditions
     * @return false|void
     */
    public function fetch(?array $conditions = null): ?array{
        // Every time we call fetch, we will first clear out the row
        $this->row = [];

        if (empty($conditions) && !empty($this->row['id'])){
            $conditions = ['id'=>$this->row['id']];
        }
        $sql = "SELECT * FROM ".static::$table;
        if (!empty($conditions)){
            $sql .= ' WHERE ';
            $sqlwhere = '';
            foreach($conditions as $key => $value){
                if (!in_array($key, static::KEYS)){
                    throw new \Exception("Invalid key: $key");
                }
                if ($sqlwhere){
                    $sqlwhere .= ' AND ';
                }
                $sqlwhere .= "$key = :$key";
            }
            $sql .= $sqlwhere;
        }else{
            //no conditions just return null
            return null;
        }
        $sql .= ' LIMIT 1';
        $row = Db::fetchFirst($sql, $conditions);
        if (empty($row)){
            return null;
        }else{
            $this->row = $row;
            return $this->row;
        }
    }
}

class TodoList extends DAO {
    public const KEYS = ['id','skey','listname','notes'];
    static protected string $table = 'list';

    public function __construct(?int $id = null){
        $this->row['id'] = $id;
        if (empty($id)){
            $this->row['skey'] = Util::GenerateRandomToken(15);
        }
    }

    public function removeItems(): bool {
        if (empty($this->row['id'])){
            throw new \Exception("No cardlist id specified");
        }
        $sql = "DELETE FROM ".Item::GetTable().' WHERE list_id = :list_id';
        $params = ['list_id'=>$this->id];
        return Db::execute($sql, $params);
    }

    public function countItems(): ?int {
        $sql = "SELECT COUNT(*) AS count_items FROM ".Item::GetTable().' WHERE list_id = :list_id';
        $params = ['list_id'=>$this->get('id')];
        $first = Db::fetchFirst($sql, $params);
        return $first['count_items'] ?? null;
    }

    public function deleteAllItems(){
        $sql = "DELETE FROM ".Item::GetTable().' WHERE list_id = :list_id';
        $params = ['list_id'=>$this->get('id')];
        return Db::execute($sql, $params);
    }

    public function getAllItems(): ?array {
        $sql = "SELECT * FROM ".Item::GetTable().' WHERE list_id = :list_id ORDER BY what ASC, id ASC ';
        $params = ['list_id'=>$this->get('id')];
        $rows = Db::fetchAll($sql, $params);
        return is_array($rows) ? $rows : null;
    }

    public static function GetAllByEmail(string $email): ?array {
        $sql = "SELECT list.id, list.skey, list.listname, list.notes, list_user.privs 
            FROM list JOIN list_user ON list.id = list_user.list_id 
            WHERE email LIKE :email
ORDER BY list.listname ASC, list.id ASC";
        $params = ['email'=>$email];
        $rows = Db::fetchAll($sql, $params);
        if (is_array($rows)){
            return $rows;
        }else{
            return null;
        }
    }
}