classification
Title: include sqlite-3.25+ (with window functions)
Type: behavior Stage:
Components: Versions:
process
Status: open Resolution:
Dependencies: Superseder:
Assigned To: Nosy List: Big Stone, Scott Stevens, ghaering, ronaldoussoren
Priority: normal Keywords:

Created on 2018-10-06 17:29 by Big Stone, last changed 2018-12-17 07:10 by Scott Stevens.

Messages (5)
msg327253 - (view) Author: Big Stone (Big Stone) Date: 2018-10-06 17:29
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 ?
msg327387 - (view) Author: Ronald Oussoren (ronaldoussoren) * (Python committer) Date: 2018-10-09 09:40
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.
msg327429 - (view) Author: Big Stone (Big Stone) Date: 2018-10-09 18:19
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);
msg327433 - (view) Author: Ronald Oussoren (ronaldoussoren) * (Python committer) Date: 2018-10-09 19:43
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.
msg331948 - (view) Author: Scott Stevens (Scott Stevens) Date: 2018-12-17 07:10
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
History
Date User Action Args
2018-12-17 07:10:35Scott Stevenssetnosy: + Scott Stevens
messages: + msg331948
2018-10-09 19:43:04ronaldoussorensetmessages: + msg327433
2018-10-09 18:19:14Big Stonesetmessages: + msg327429
2018-10-09 09:40:41ronaldoussorensetnosy: + ghaering, ronaldoussoren
type: behavior
messages: + msg327387
2018-10-06 17:29:26Big Stonecreate