Description

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'

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']

Activity

1. BigStone commented on Oct 6, 2018

   

   BigStonemannequin

   on Oct 6, 2018

   MannequinAuthor

   More actions

   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 ?
2. ronaldoussoren commented on Oct 9, 2018

   

   ronaldoussoren

   on Oct 9, 2018

   Contributor

   More actions

   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.
3. ronaldoussoren

   added

   type-bugAn unexpected behavior, bug, or errorAn unexpected behavior, bug, or error

   on Oct 9, 2018
4. BigStone commented on Oct 9, 2018

   

   BigStonemannequin

   on Oct 9, 2018

   MannequinAuthor

   More actions

   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);
5. ronaldoussoren commented on Oct 9, 2018

   

   ronaldoussoren

   on Oct 9, 2018

   Contributor

   More actions

   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.
6. ScottStevens commented on Dec 17, 2018

   

   ScottStevensmannequin

   on Dec 17, 2018

   Mannequin

   More actions

   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
7. berkerpeksag commented on May 11, 2019

   

   berkerpeksag

   on May 11, 2019

   Member

   More actions

   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.
8. berkerpeksag

   added

   3.8 (EOL)end of lifeend of life

   extension-modulesC modules in the Modules dirC modules in the Modules dir

   on May 11, 2019
9. berkerpeksag

   changed the title [-]include sqlite-3.25+ (with window functions)[/-] [+]Add create_window_function() to sqlite3.Connection[/+] on May 11, 2019
10. berkerpeksag

    self-assigned this

    on May 11, 2019
11. berkerpeksag

    added

    type-featureA feature request or enhancementA feature request or enhancement

    and removed

    type-bugAn unexpected behavior, bug, or errorAn unexpected behavior, bug, or error

    on May 11, 2019
12. coleifer commented on Aug 12, 2019

    

    coleifermannequin

    on Aug 12, 2019

    Mannequin

    More actions

    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
13. erlend-aasland commented on Jun 8, 2020

    

    erlend-aasland

    on Jun 8, 2020

    Contributor

    More actions

    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.
14. ezio-melotti

    transferred this issue fromon Apr 10, 2022
15. JelleZijlstra

    closed this as completedin #20903on Apr 12, 2022
16. JelleZijlstra

    added a commit that references this issue on Apr 12, 2022

    gh-79097: Add support for aggregate window functions in sqlite3 (GH-2…

    9ebcece
17. erlend-aasland

    added a commit that references this issue on Apr 12, 2022

    Sync with main bco. pythongh-79097

    e15c087

Issue actions