Taeluf.com

SqlFiles.php

<?php

namespace Tlf\BigDb;

/**
 * Provides support for loading .sql files & executing queries within, via LilDb's LilSql class.
 */
trait SqlFiles {

    /**
     *
     * Execute a stored sql query & return the pdo statement
     *
     * @param $query_key the key that points to the stored query
     * @param $params EXPERIMENTAL key/value array to bind to the query, except it is done via `str_replace(':key', $pdo->quote($value));
     * @return Same as `PDO::exec($query)`;
     */
    public function exec(string $query_key, array $params = []): int|false {
        $query = $this->sql[$query_key];
        // echo "\n\n".$query."\n\n";
        foreach ($params as $key=>$value){
            $query = str_replace(":$key", $this->pdo->quote($value), $query);
        }

        return $this->pdo->exec($query);
    }

    /**
     * Execute a stored query, and get an array of orm objects from the results
     *
     * @param $which_query the key for the sql query. Typically it is a `table.query_name` style, where `table` is actually the name of the stored .sql file.
     * @param $binds array<string, mixed> EXPERIMENTAL key/value array to bind to the stored query. Uses pdo_quote & str_replace NOT the built-in `pdo->bind()`
     */
    public function query_rows(string $which_query, array $binds = []): array {
        $this->init_sql();
        if (!isset($this->sql[$which_query])){
            $class = get_class($this);
            throw new \Exception("Query '$which_query' is not stored in this dabase class of '$class'");
        }
        $sql = $this->sql[$which_query];

        foreach ($binds as $key=>$value){
            $sql = str_replace(":$key", $this->pdo->quote($value), $sql);
        }

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        return $rows;
    }

    public function addSqlDir(string $dir, bool $force_recompile = false){
        $this->init_sql();
        if ($force_recompile)$this->check_serialized_file($dir);
        $this->load_queries($dir,'','');
    }

    /**
     * Initialize the sql statements from the compiled sql file, only if `$this->sql` is not set
     *
     * @return void
     *
     * @override to use a different sql storage system than LilDb's LilSql
     */
    public function init_sql(){
        if (isset($this->sql))return;
        $this->load_queries($this->get_root_dir().'/sql/');
        // print_r($this->sql);
    }

    /**
     * Re-generate the compiled sql file 
     * @return void
     */
    public function recompile_sql(){
        $this->check_serialized_file($this->get_root_dir().'/sql/');
        $this->init_sql();
    }

    /**
     *
     * Loads queries from a file containing a serialized array. If the serialized file does not exist, `$dir` will be scanned, queries built, and the file will be created. There is no cache invalidation, so delete the serialized file to regenerate.
     * @param $dir a directory containing `.sql` files with the LilSql formatting
     * @param $namespace file prefix for query files. Ex: pass `"form"` to load files like `"form.create.sql"` and `form.query.sql`
     */
    public function load_queries(string $dir, string $file_prefix = '', string $query_key_prefix = ''){
        if (!isset($this->sql))$this->sql = [];
        $serial_file = $this->get_serial_file_path($dir, $file_prefix);
        $prefix = $query_key_prefix == null ? '' : $query_key_prefix .'.';
        if (file_exists($serial_file)){
            $this->sql = array_merge(
                $this->sql,
                unserialize(file_get_contents($serial_file))
            );
            return;
        }

        // print_r($this->sql);
        // exit;
        
        $ls = new \Tlf\LilSql();
        $ls->load_files($dir, $prefix);
        $ls->serialize($serial_file);
        if (!file_exists($serial_file)){
            throw new \Exception("Failed to write serialized file '$serial_file'");
        }
        return $this->load_queries($dir, $file_prefix);
    }


    public function get_serial_file_path(string $dir, ?string $file_prefix): string {
        $file_prefix = ($file_prefix == null) ? '' : $file_prefix.'.';
        $serial_file = $dir.'/'.$file_prefix.'queries';
        return $serial_file;
    }

    /**
     * Check mtime of serialized file and delete it if
     * @param $dir
     */
    public function check_serialized_file(string $dir, ?string $file_prefix = ''){
        $file_path = $this->get_serial_file_path($dir, $file_prefix);

        if (!file_exists($file_path))return;
        $serial_mtime = filemtime($file_path);
        $dir = dirname($file_path);
        // var_dump($dir);
        // var_dump($serial_mtime);
        foreach (scandir($dir) as $file){
            if (is_dir($dir.'/'.$file))continue;
            // echo 'checked!';
            // var_dump($dir.'/'.$file);
            if (filemtime($dir.'/'.$file) > $serial_mtime) goto delete;
        }

        return;
        delete:
            unlink($file_path);
    }
}