sqlite3: expose sqlite3_create_window_function

[iljau]

BPO	40617
Nosy	@berkerpeksag, @erlend-aasland
Superseder	bpo-34916: Add create_window_function() to sqlite3.Connection
Files	0001-Add-support-for-sqlite3-aggregate-window-functions.patch

^{Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.}

Show more details

GitHub fields:

assignee = None
closed_at = <Date 2021-05-05.19:32:17.598>
created_at = <Date 2020-05-13.18:52:51.149>
labels = ['type-feature', 'library', '3.9']
title = 'sqlite3: expose sqlite3_create_window_function'
updated_at = <Date 2021-05-05.19:32:17.597>
user = 'https://bugs.python.org/iljau'

bugs.python.org fields:

activity = <Date 2021-05-05.19:32:17.597>
actor = 'erlendaasland'
assignee = 'none'
closed = True
closed_date = <Date 2021-05-05.19:32:17.598>
closer = 'erlendaasland'
components = ['Library (Lib)']
creation = <Date 2020-05-13.18:52:51.149>
creator = 'iljau'
dependencies = []
files = ['49190']
hgrepos = []
issue_num = 40617
keywords = ['patch']
message_count = 6.0
messages = ['368794', '369822', '369828', '369834', '369851', '370880']
nosy_count = 3.0
nosy_names = ['berker.peksag', 'erlendaasland', 'iljau']
pr_nums = []
priority = 'normal'
resolution = 'duplicate'
stage = 'resolved'
status = 'closed'
superseder = '34916'
type = 'enhancement'
url = 'https://bugs.python.org/issue40617'
versions = ['Python 3.9']

[iljau]

Expose sqlite3_create_window_function as sqlite3.create_window_function.

There already exists sqlite3.create_aggregate, which uses sqlite3_create_function_v2 and is conceptually similar.

Window functions are available in sqlite starting from 2018-09-15 (3.25.0) release.

References:
https://www.sqlite.org/c3ref/create_function.html
https://www.sqlite.org/changes.html

[erlend-aasland]

This is a good idea, but I think the Python interface should stay the same.

From the user perspective, you'd only need to implement the 'value' and 'inverse' callbacks to transform your aggregate class into an aggregate window class.

From the CPython perspective, we'd only need to use sqlite3_create_window_function() instead of sqlite3_create_function_v2(), if we are compiling against SQLite 3.25.3 or newer.

I guess it would be nice to output a warning if the user aggregate class contains 'value' and 'inverse' callbacks, but sqlite3 _doesn't_ support aggregate window functions.

[erlend-aasland]

On a second thought and with regards to error reporting, it _would_ make sense to expose this as a function instead of embedding it into sqlite3.create_aggregate.

[erlend-aasland]

Please see attached patch, @iljau.

[erlend-aasland]

Another possibility (instead of adding sqlite3.create_window_function) is to add a "windowed" parameter to sqlite3.create_aggregate():

sqlite3.create_aggregate(name, narg, aggregate, windowed=True

Maybe that's a little bit cleaner regarding the Python interface.

[erlend-aasland]

Duplicate of bpo-34916