Soshot/app/DataBase/DataBase.php

<?php

namespace App\DataBase;

use Exception;
use PDO;

use function App\Soshot\n_print as SoshotN_print;

if (!function_exists('n_print')) {
    function n_print($data, $name = '') {
        print_r($data, 1);
    }
}

class DataBase {

    private $dataBase = __DIR__ . '/../../datas/soshot.sqlite';
    private $db;
    private $params = [
        'id' => '',
        'url' => '',
        'complete' => '',
        'full' => '',
        'hd' => '',
        'nhd' => '',
        'thumb' => '',
        'fav' => '',
        'og' => '',
        'pdf' => '',
        'created' => ''
    ];


    /**
     * Constructs a new instance of the DataBase class.
     *
     * This method creates a new SQLite database connection and sets the default fetch
     * mode to PDO::FETCH_ASSOC. It also sets the error mode to PDO::ERRMODE_EXCEPTION.
     * If the database file does not exist, it creates a new table named 'soshot' with
     * the specified schema. If the $params parameter is not empty, the setParams()
     * method is called to set the object's properties.
     *
     * @param object $params An associative array of parameters to set the object's
     *                      properties.
     *
     * @return DataBase The constructed DataBase object.
     */
    function __construct($params = null) {
        try {
            if (!file_exists($this->dataBase)) {
                $this->db = new PDO('sqlite:' . $this->dataBase);
                $this->db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
                $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION, PDO::ERRMODE_WARNING); // ERRMODE_WARNING | ERRMODE_EXCEPTION | ERRMODE_SILENT

                $this->db->query("CREATE TABLE IF NOT EXISTS soshot ( 
                    id string PRIMARY KEY NOT NULL,
                    url text,
                    complete tinyint,
                    full tinyint,
                    hd tinyint,
                    nhd tinuyint,
                    thumb tinyint,
                    fav tinyint,
                    og tinyint,
                    pdf tinyint,
                    created DATETIME
                );");
            } else {
                $this->db = new PDO('sqlite:' . $this->dataBase);
                $this->db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
                $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // ERRMODE_WARNING | ERRMODE_EXCEPTION | ERRMODE_SILENT

                $this->updateTable();
            }
        } catch (Exception $e) {
            echo $e->getMessage();
            die();
        }

        if (!empty($params)) {
            $this->setParams($params);
        }
        return $this;
    }


    public function updateTable() {
        $stmt = $this->db->prepare("SELECT * FROM soshot LIMIT 1;");
        $stmt->execute();
        $existingColumns = $stmt->fetch(PDO::FETCH_ASSOC);

        if (empty($existingColumns)) {
            return true;
        }

        $existingColumns = array_keys($existingColumns);

        $newColumns = array_diff(array_keys($this->params), $existingColumns);

        foreach ($newColumns as $column) {
            $sql = "ALTER TABLE soshot ADD `{$column}` tinyint";
            $this->db->exec($sql);
        }
    }

    /**
     * Sets the parameters for the database query.
     *
     * This method takes an object containing the parameters for the database query and
     * sets the corresponding properties of the object.
     *
     * @param object $params An object containing the parameters for the database query.
     *
     * @return void
     */
    public function setParams(object $params) {
        $this->params = (object)$this->params;
        $this->params->id = $params->hmac;
        $this->params->url = $params->url;
        $this->params->type = $params->type;
        $this->params->created = date("Y-m-d H:i:s");
        $this->params = $this->params;
    }

    /**
     * Adds or updates a record in the database.
     *
     * This method takes an update value and an optional type parameter. If the type
     * parameter is not empty, it sets the type property of the object. It then checks
     * if a record with the same ID already exists in the database using the testExit
     * method. If a record exists, it calls the update method to update the existing
     * record. If no record exists, it calls the insert method to insert a new record.
     *
     * @param int $update The update value to be inserted or updated in the database.
     * @param string $type The type of the update.
     *
     * @return void
     */
    public function addUpdate(int $update, string $type = '') {
        if (!empty($type)) {
            $this->params->type = $type;
        }
        if ($this->testExit($this->params->id)) {
            $this->update($update);
        } else {
            $this->insert($update);
        }
    }

    /**
     * Inserts a new record into the database.
     *
     * This method prepares an SQL INSERT statement using the provided parameters, and
     * executes it to insert a new record into the "soshot" table in the database.
     *
     * @param int $update The value to insert into the type column of the new record.
     *
     * @return void
     */
    public function insert(int $update) {
        $stmt = $this->db->prepare("INSERT INTO soshot (id, url, " . $this->params->type . ", created) VALUES 
        (:id, :url, :" . $this->params->type . ", :created)");
        //$stmt->debugDumpParams();

        $result = $stmt->execute(array(
            'id' => $this->params->id,
            'url' => $this->params->url,
            $this->params->type => $update,
            'created' => $this->params->created
        ));
    }

    /**
     * Updates a record in the database.
     *
     * This method prepares an SQL UPDATE statement using the provided parameters, and
     * executes it to update a record in the "soshot" table in the database.
     *
     * @param string $update The value to update in the type column of the record.
     *
     * @return void
     */
    private function update(string $update) {
        $stmt = $this->db->prepare("UPDATE soshot
        SET " . $this->params->type . "=:" . $this->params->type . "
        WHERE id=:id;");

        $result = $stmt->execute([
            ':' . $this->params->type => $update,
            ':id' => $this->params->id
        ]);
    }

    /**
     * Reset .
     *
     * This method prepares an SQL UPDATE statement using the provided parameters, and
     * executes it to update a record in the "soshot" table in the database.
     *
     * @param string $update The value to update in the type column of the record.
     *
     * @return void
     */
    public function reset(string $id) {

        $resetParam = [];

        $updateParams = $this->params;
        unset($updateParams['id'], $updateParams['url'], $updateParams['created']);

        foreach ($updateParams as $key => $value) {
            $resetParam[] = $key . ' = null';
        }

        $strUpdateParam = implode(',', $resetParam);

        $stmt = $this->db->prepare("UPDATE soshot
        SET $strUpdateParam
        WHERE id=:id;");

        $result = $stmt->execute([
            ':id' => $id
        ]);
    }

    /**
     * Checks if a record with the given ID exists in the database.
     *
     * This method prepares an SQL SELECT statement using the provided ID, and executes
     * it to retrieve the corresponding record from the "soshot" table in the database.
     * If a record is found, the method returns true. Otherwise, it returns false.
     *
     * @param string $id The ID of the record to check.
     *
     * @return bool True if a record with the given ID exists in the database, false otherwise.
     */
    private function testExit(string $id): bool {
        $stmt = $this->db->prepare("SELECT id FROM soshot WHERE id=:id LIMIT 1;");
        $stmt->execute(array(':id' => $id));
        $result = $stmt->fetchAll(PDO::FETCH_OBJ);
        if (!empty($result)) {
            return true;
        }
        return false;
    }

    /**
     * Retrieves the total number of records in the "soshot" table.
     *
     * This method prepares an SQL SELECT statement to retrieve the total number of
     * records in the "soshot" table, executes the statement, and returns the result.
     *
     * @return int The total number of records in the "soshot" table.
     */
    public function getTotal(): int {
        $stmt = $this->db->prepare("SELECT COUNT(id) AS nb FROM soshot;");
        $stmt->execute();
        $result = $stmt->fetch();
        return $result['nb'];
    }

    /**
     * Retrieves the number of records with errors in the "soshot" table.
     *
     * This method prepares an SQL SELECT statement to retrieve the number of records
     * with errors in the "soshot" table, executes the statement, and returns the result.
     *
     * @return int The number of records with errors in the "soshot" table.
     */
    public function getInError(): int {
        $stmt = $this->db->prepare("SELECT COUNT(DISTINCT id) AS nb FROM soshot WHERE 
        complete = 2 OR
        full = 2 OR
        hd = 2 OR
        nhd = 2 OR
        thumb = 2 OR
        fav = 2 OR
        og = 2 OR
        pdf = 2;");
        $stmt->execute();
        $result = $stmt->fetch();
        return $result['nb'];
    }

    /**
     * Retrieves a list of records from the "soshot" table.
     *
     * This method prepares an SQL SELECT statement to retrieve a list of records from
     * the "soshot" table, based on the specified start and end indices, orders the
     * results by the "created" column in descending order, executes the statement, and
     * returns the result set as an array of objects.
     *
     * @param int $start The starting index of the record to retrieve.
     * @param int $end The ending index of the record to retrieve.
     *
     * @return array An array of objects representing the records in the "soshot" table.
     */
    public function getList(int $start, int $end, string $search = null) {
        if ($search != null) {
            $stmt = $this->db->prepare("SELECT * FROM soshot WHERE url like '%$search%' ORDER BY created DESC limit :start, :end;");
        } else {
            $stmt = $this->db->prepare("SELECT * FROM soshot ORDER BY created DESC limit :start, :end;");
        }
        $stmt->execute(array(':start' => $start, ':end' => $end));
        $result = $stmt->fetchAll(PDO::FETCH_OBJ);
        return $result;
    }
}