SQLite Forum

php sqlite3_create_window_function

php sqlite3_create_window_function

(1) By anonymous on 2021-06-28 20:42:36 [link] [source]

I'm very familiar with sqlite3 in php since years, now using PHP V 8.0.7 and SQLite V3.26
Also learning SQLite window functions (OVER clause)
I need to use User-Defined Aggregate Window Functions (sqlite3_create_window_function)
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
And I get "Fatal error: Uncaught Error: Call to undefined method" when i call ceateWindowFunction, create_window_function or sqlite3_create_window_function

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)
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)

Thank you in advance
Best regards

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

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] [source]

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 [source]

Aside from what others have noted (that PHP doesn't currently support sqlite3_create_window_function), the function documentation 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] [source]

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.

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

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):

What exactly are the parameters you're passing to $db->createWindowFunction()?

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() 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] [source]

The call would be same as SQlite3::createAggregate or 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.

Thank you for your answers
Best regards
Mar Zama