SQLite Forum

php sqlite3_create_window_function

php sqlite3_create_window_function

(1) By anonymous on 2021-06-28 20:42:36

I'm very familiar with sqlite3 in php since years, now using PHP V 8.0.7 and SQLite V3.26<br>
Also learning SQLite window functions (OVER clause)<br>
I need to use User-Defined Aggregate Window Functions (sqlite3_create_window_function)<br>
Already succeeded using sqlite3_create_function, as createFunction and createAggregate, either in PDO:: and SQLite3::, I used them for years too.
But create_window_function is not documented in PHP, nor under PDO nor under SQLite3<br>
And I get "Fatal error: Uncaught Error: Call to undefined method" when i call ceateWindowFunction, create_window_function or sqlite3_create_window_function<br>
echo 'PHP Version: ' . phpversion(); //PHP Version: 8.0.7
$db = new SQLite3('mibdsqlite.db');
echo 'SQlite3 version: ' . $db->querySingle('select sqlite_version();');//SQlite3 version: 3.26.0
$db->createWindowFunction( ... );//Fatal error: Uncaught Error: Call to undefined method SQLite3::createWindowFunction() in folder/filename
//same error using create_window_function or sqlite3_create_window_function
Can sqlite3_create_window_function be "hidden"? (already present in sqlite3.26 I check)<br> Is there a way to make it accessible using .htaccess or php.ini, load a PHP extension, etc? (I'm working on a shared hosting)<br>

Thank you in advance<br>
Best regards

(2) By anonymous on 2021-06-28 22:12:13 in reply to 1 [link]

That is presumably because PHP doesn't have that function. In addition to the version of SQLite with that function, you will also need the version of PHP that knows how to call that function of SQLite.

(3) By RandomCoder on 2021-06-28 22:29:01 in reply to 1 [link]

PHP's SQLite interface has no method to call sqlite3_create_window_function at present.  You will need to work with the PHP community to add such a feature or create an extension to do so (assuming that's possible).

(4) By Adrian Ho (lexfiend) on 2021-06-29 01:52:48 in reply to 1 [link]

Aside from what others have noted (that PHP doesn't currently support `sqlite3_create_window_function`), the [function documentation](https://sqlite.org/c3ref/create_function.html) also states (emphasis mine):

> The sixth, seventh, eighth and ninth parameters (`xStep`, `xFinal`, `xValue` and `xInverse`) passed to `sqlite3_create_window_function` are pointers to **C-language callbacks** that implement the new function.

I'm curious as to how you're expecting the PHP equivalent to work. What exactly are the parameters you're passing to `$db->createWindowFunction()`?

(5) By anonymous on 2021-06-29 08:17:27 in reply to 4 [link]

The PHP module runtime could provide the C functions that would call back into the PHP code written by the user, the same way it's implemented in [DBD::SQlite], for example.

[DBD::SQLite]: https://metacpan.org/pod/DBD::SQLite#$dbh-%3Esqlite_create_function(-$name,-$argc,-$code_ref,-$flags-)

(6) By Adrian Ho (lexfiend) on 2021-06-30 05:29:18 in reply to 5 [link]

That's how it would have to be done, certainly, but I guess my point wasn't clear. When I asked the OP (presumably [Mar Zama](https://stackoverflow.com/questions/68166477/php-sqlite3-create-window-function)):

> What exactly are the parameters you're passing to 

I meant that literally: How are you using this hypothetical function? If the answer is "I haven't really thought that far yet", then how would Mar Zama implement [sumint()](https://sqlite.org/windowfunctions.html#udfwinfunc) in PHP with `createWindowFunction()`?

Answering these questions goes a long way towards defining Mar Zama's requirements--and maybe suggest a alternative solution to a function that doesn't yet exist.

(7) By anonymous on 2021-07-08 05:52:34 in reply to 6 [link]

The call would be same as [SQlite3::createAggregate](https://www.php.net/manual/en/sqlite3.createaggregate) or [PDO::sqliteCreateAggregate](https://www.php.net/manual/en/pdo.sqlitecreateaggregate), parameters are PHP function names, as strings.

So, something like:
$db->sqliteCreateWindow('sumint', 'sumintStep', 'sumintFinal', 'sumintValue', 'sumintInverse')
where those strings are names of window function being defined, and functions user must write in php to implement it.

And, yes, it requires PHP/PDO modifications that are beyond my knowledge.<br>

Thank you for your answers<br>
Best regards<br>
Mar Zama