PHP implementation of SQLite udf/udaf

SQLite UDF/UDAF

<?php

// sqlite UDF/UDAF  Support 
// https://blog.csdn.net/wyqwilliam/article/details/84500578

//  Reference resources ：
// php  Version implementation ：https://www.php.net/manual/en/ref.pdo-sqlite.connection.php
// go  Version implementation ：https://github.com/mattn/go-sqlite3/pull/229/files

$db = new \PDO('sqlite::memory:');
//$db->setAttribute(PDO::ATTR)
// $db = new \PDO('sqlite:');
// $db = new \PDO('sqlite:tmp.db');
$db->exec(' CREATE TABLE "role" ( "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "actor" TEXT, "movie" TEXT, "year" INTEGER ); ');
$insert = $db->exec(' INSERT INTO role (actor, movie, year) VALUES (" Yang Mi ", " New Liaozhai has different aspirations ", 2005), (" Yang Mi ", " Fairy sword legend ", 2009), (" Hu Ge ", " Fairy sword legend ", 2005), (" Hu Ge ", " List of reed {langya} ", 2015), (" Hu Ge ", " myth ", 2010), (" Xun Zhou ", " Daming Palace ci ", 2000), (" Xun Zhou ", " April in the world ", 2000) ; ');

$db->sqliteCreateFunction('IF', function ($expr, $value1, $value2) {
    
    if ($expr) {
    
        return $value1;
    }
    return $value2;
});

$db->sqliteCreateFunction('GREATEST', function ($value1, $value2) {
    
    return max([$value1, $value2]);
});

$query = $db
    ->query('SELECT actor, movie, year, IF (year <= 2005, "old", "new") AS `type` FROM role');
while ($data = $query->fetchObject()) {
    
    print_r($data);
}
/* stdClass Object ( [actor] =>  Yang Mi  [movie] =>  New Liaozhai has different aspirations  [year] => 2005 [type] => old ) stdClass Object ( [actor] =>  Yang Mi  [movie] =>  Fairy sword legend  [year] => 2009 [type] => new ) stdClass Object ( [actor] =>  Hu Ge  [movie] =>  Fairy sword legend  [year] => 2005 [type] => old ) stdClass Object ( [actor] =>  Hu Ge  [movie] =>  List of reed {langya}  [year] => 2015 [type] => new ) stdClass Object ( [actor] =>  Hu Ge  [movie] =>  myth  [year] => 2010 [type] => new ) stdClass Object ( [actor] =>  Xun Zhou  [movie] =>  Daming Palace ci  [year] => 2000 [type] => old ) stdClass Object ( [actor] =>  Xun Zhou  [movie] =>  April in the world  [year] => 2000 [type] => old ) */

$query = $db
    ->query('SELECT actor, GROUP_CONCAT(movie) AS all_movies FROM role GROUP BY actor');
while ($data = $query->fetchObject()) {
    
    print_r($data);
}
/* stdClass Object ( [actor] =>  Xun Zhou  [all_movies] =>  Daming Palace ci , April in the world  ) stdClass Object ( [actor] =>  Yang Mi  [all_movies] =>  New Liaozhai has different aspirations , Fairy sword legend  ) stdClass Object ( [actor] =>  Hu Ge  [all_movies] =>  Fairy sword legend , List of reed {langya} , myth  ) */

$db->sqliteCreateAggregate('GROUP_CONCAT', function ($context, $row_number, $field) {
    
    if (is_null($context)) {
    
        return $field;
    }
    return $context . '/' . $field;
}, function ($context, $rowcount) {
     return $context; });
/* stdClass Object ( [actor] =>  Xun Zhou  [all_movies] =>  Daming Palace ci / April in the world  ) stdClass Object ( [actor] =>  Yang Mi  [all_movies] =>  New Liaozhai has different aspirations / Fairy sword legend  ) stdClass Object ( [actor] =>  Hu Ge  [all_movies] =>  Fairy sword legend / List of reed {langya} / myth  ) */