Add create_window_function() to sqlite3.Connection

[BigStone]

BPO	34916
Nosy	@ronaldoussoren, @berkerpeksag, @coleifer, @erlend-aasland
PRs	gh-79097: Add support for aggregate window functions in sqlite3 #20903

^{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 = 'https://github.com/berkerpeksag'
closed_at = None
created_at = <Date 2018-10-06.17:29:26.073>
labels = ['extension-modules', 'type-feature', '3.8']
title = 'Add create_window_function() to sqlite3.Connection'
updated_at = <Date 2020-06-15.21:48:40.567>
user = 'https://bugs.python.org/BigStone'

bugs.python.org fields:

activity = <Date 2020-06-15.21:48:40.567>
actor = 'erlendaasland'
assignee = 'berker.peksag'
closed = False
closed_date = None
closer = None
components = ['Extension Modules']
creation = <Date 2018-10-06.17:29:26.073>
creator = 'Big Stone'
dependencies = []
files = []
hgrepos = []
issue_num = 34916
keywords = ['patch']
message_count = 8.0
messages = ['327253', '327387', '327429', '327433', '331948', '342206', '349506', '370967']
nosy_count = 7.0
nosy_names = ['ghaering', 'ronaldoussoren', 'berker.peksag', 'Big Stone', 'Scott Stevens', 'coleifer', 'erlendaasland']
pr_nums = ['20903']
priority = 'normal'
resolution = None
stage = 'patch review'
status = 'open'
superseder = None
type = 'enhancement'
url = 'https://bugs.python.org/issue34916'
versions = ['Python 3.8']

[BigStone]

sqlite-3.25 now includes window functions, something very important in modern SQL.

https://www.sqlite.org/windowfunctions.html

Could it be included in next Python maintenance release ?

[ronaldoussoren]

To completely do this requires two things:

1. Upgrade SQLite included in a number of binary distributions

2. Update the sqlite extension to allow creating custom window functions in Python

The latter is definitely something that cannot be done in a maintenance release. The former is up to the release managers.

[BigStone]

hum. On Windows at least, just drop-in replace the sqlite3.dll per the new sqlite-3.25 dll and the window functions just works.
==> I don't see the need for your latter point, at least on Windows.

checked with sqlite_bro on Python-3.6.7rc1 with example:
CREATE TABLE t2(a, b);
INSERT INTO t2 VALUES('a', 'one'),
('a', 'two'),
('a', 'three'),
('b', 'four'),
('c', 'five'),
('c', 'six');
SELECT a AS a,
row_number() OVER win AS row_number,
rank() OVER win AS rank,
dense_rank() OVER win AS dense_rank,
percent_rank() OVER win AS percent_rank,
cume_dist() OVER win AS cume_dist
FROM t2
WINDOW win AS (ORDER BY a);

[ronaldoussoren]

My latter points makes it possible to define custom windowing functions in Python, similar to how it is already possible to define other SQLite functions in Python.

[ScottStevens]

Due to the SQLite "Magellan" bug, I'd suggest this be 3.26+.

Details: https://blade.tencent.com/magellan/index_en.html
See also: https://bugs.python.org/issue35360

[berkerpeksag]

bpo-35360 will cover updating our Windows and macOS installers. I'm retargeting this issue to add a create_window_function() method to the Connection object. I already have a WIP branch and am going to submit a PR later this weekend.

[coleifer]

I've implemented this in a fork / standalone packaging of the Python 3.x sqlite3 module. You can find the relevant portions (and a couple unrelated changes) in this commit:

coleifer/pysqlite3@91c2001

[erlend-aasland]

Berker, are you still working on this? If not, I've got a proof-of-concept version of this w/unit tests ready for review. If you'd like to have a look at it, I can create a draft PR.