Taeluf.com

LilSql.php

<?php

namespace Tlf;

/**
 * Facilitates storing SQL on-disk in sql files and converting those sql files to an array of commands & then serializing that.
 *
 * This is idealy a build tool, not a runtime tool, so you will `unserialize(file_get_contents(...))` to load the array of queries
 *
 * Or `@query(some.name, delimiter)` if you don't want to stop on a semi-colon
 *
 * @tagline Name queries in sql files with `@query(some.name)` and serialize them into a keyed array for later user
 *
 */
class LilSql {

    /** 
     * array of queries to use/serialize (you should serialize them & skip this processing in production!)
     */
    public $queries = [];

    /**
     * Load sql files from the following dir into the queries
     * @param $dir a directory path
     */
    public function load_files(string $dir, string $namespace_prefix=''){
        foreach (scandir($dir) as $f){
            if (is_dir($dir.'/'.$f))continue;
            if (substr($f,-4)!='.sql')continue;
            $prefix = $namespace_prefix.substr($f,0,-3);
            $file = file_get_contents($dir.'/'.$f);
            $this->parse_sql($file, $prefix, $this->queries);
        }

        return $this->queries;
    }

    public function serialize(string $file){
        file_put_contents($file, serialize($this->queries));
    }

    /**
     * Convert an SQL string containing multiple queries into an array. All queries must have an explicit identifier.
     * 
     * @see(\Tlf\LilDb\Test\Sql::testParseString()) for an example
     *
     * @param $sql sql containing multiple queries and an `@query(name, delimiter)` identifier
     * @param $prefix prefix for the query name
     * @param &$queries queries array to modify
     */
    public function parse_sql(string $sql, string $prefix='', &$queries = []){


        $pos = -1;
        while(($pos = strpos($sql, '@query(', $pos+1))!==false){
            $delimiter = ';';
            $line_start = strrpos(substr($sql,0,$pos), "\n");
            // var_dump($line_start);
            // var_dump($pos);

            $begin_of_command = strpos($sql, "(", $pos);
            $end_of_command = strpos($sql, ")", $begin_of_command);
            $next_line = strpos($sql, "\n", $end_of_command)+1;

            
            $comma = strpos($sql, ',', $begin_of_command);
            
            if ($comma === false || $comma > $end_of_command){
                $name_stop = $end_of_command - $begin_of_command;
            } else {
                $name_stop = $comma - $begin_of_command;
                $delimiter = trim(substr($sql, $comma+1, $end_of_command - $comma-1));
            }
            // $name_stop = $comma === false || $comma > $end_of_command ? $end_of_command - $begin_of_command :
            // var_dump($begin_of_command);
            // var_dump($end_of_command);
            // var_dump($comma);
            // var_dump($name_stop);

            $name = trim(substr($sql, $begin_of_command+1, $name_stop-1));

            // exit;
            // var_dump($name);
            // var_dump($delimiter);

            

            $sql_end = strpos($sql, $delimiter, $end_of_command);
            // make the sql block start at the next line after the @query() tag
            $sql_block = trim(substr($sql, $next_line, $sql_end-$next_line+strlen($delimiter)));
            // // make the sql block include the @query() tag that defines it
            // $sql_block = trim(substr($sql, $line_start, $sql_end-$line_start+strlen($delimiter)));

            // var_dump($sql_end);
            // var_dump($sql_block);
            // // $sql =
            // exit;
            if (isset($queries[$prefix.$name])){
                throw new \Exception("There is already a query with name '$name'");
            }
            $queries[$prefix.$name] = $sql_block;
        }

        return $queries;
    }
}