Taeluf.com

Sql.php

<?php

namespace Tlf\LilDb\Test;

class Sql extends \Tlf\Tester {

    public function testLilSql(){
        $this->disable();
        $serialized_file = $this->file('test/input/sql/serialized.txt');
        file_put_contents($serialized_file, '--nothing--');

        $ls = new \Tlf\LilSql();
        $ls->load_files($this->file('test/input/sql/'));
        $ls->serialize($serialized_file);

        $queries = $ls->queries;
        // or 
        $queries = unserialize(file_get_contents($serialized_file));

        print_r($queries );
        // exit;

        // $this->compare_arrays(
            // $
        // );
    }

    public function testParseMultiQuery(){
        $queries_sql = 
            "SELECT * FROM `users`;
            DELETE FROM `users`; 
            UPDATE `users` SET `status` = 'banned'; -- delimiter";
        $sql = "-- @query(delete.users, ; -- delimiter) 
            $queries_sql
        ";

        $ls = new \Tlf\LilSql();
        $queries = $ls->parse_sql($sql);
        print_r($queries);

        $this->compare(trim($queries_sql), trim($queries['delete.users']));

    }
    public function testParseString3(){
        $this->disable();
        $sql = file_get_contents($this->file('test/input/sql/create.sql'));
        $ls = new \Tlf\LilSql();
        $queries = $ls->parse_sql(
            "
-- @query(user)
CREATE TABLE IF NOT EXISTS `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
    UNIQUE(`email`)
) ;

-- @query(code)
CREATE TABLE IF NOT EXISTS `code` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` int(10) unsigned NOT NULL,
    `type` varchar(30),
    `code` varchar(255) NOT NULL,
    `is_active` tinyint(1) NOT NULL DEFAULT '0',
    `activated_at` datetime NULL DEFAULT NULL,
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires_at` datetime NOT NULL,
    PRIMARY KEY (`id`)
) ;
"
);
// echo "\n\n\n-----------\n\n";
// echo "\n\n\n-----------\n\n";
// echo "\n\n\n-----------\n\n";
// echo "\n\n\n-----------\n\n";
        // print_r($queries);
        // exit;
    }

    public function testParseString2(){
        $this->disable();
        $sql = file_get_contents($this->file('test/input/sql/create.sql'));
        $ls = new \Tlf\LilSql();
        $queries = $ls->parse_sql($sql);
// echo "\n\n\n-----------\n\n";
// echo "\n\n\n-----------\n\n";
// echo "\n\n\n-----------\n\n";
// echo "\n\n\n-----------\n\n";
        print_r($queries);
        // exit;
    }
    public function testParseString(){
        $sql = "-- ok
            -- @query(delete.users, ; -- delimiter) 
            DELETE FROM `users`; -- delimiter

            -- @query(delete.roles, ;)
            DELETE FROM `roles`;


            -- @query(delete.perms)
            DELETE FROM `permissions`;

            -- @query(delete.throttles)
            DELETE FROM `throttles`;
            -- @query(delete.throttles2)
            DELETE FROM `throttles2`;
            -- @query(delete.throttles3)
            DELETE FROM `throttles3`;
        ";

        $ls = new \Tlf\LilSql();
        $queries = $ls->parse_sql($sql);
        print_r($queries);

        $this->compare_arrays(
            ['delete.users'=>'DELETE FROM `users`; -- delimiter',
            'delete.roles'=>'DELETE FROM `roles`;',
            'delete.perms'=>'DELETE FROM `permissions`;',
            'delete.throttles'=>'DELETE FROM `throttles`;',
            'delete.throttles2'=>'DELETE FROM `throttles2`;',
            'delete.throttles3'=>'DELETE FROM `throttles3`;',
            ]
            ,$queries
        );
    }
}