Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Add create_window_function() to sqlite3.Connection #79097

Closed
BigStone mannequin opened this issue Oct 6, 2018 · 8 comments · Fixed by #20903
Closed

Add create_window_function() to sqlite3.Connection #79097

BigStone mannequin opened this issue Oct 6, 2018 · 8 comments · Fixed by #20903
Assignees
@berkerpeksag
Labels
3.8 only security fixes extension-modules C modules in the Modules dir type-feature A feature request or enhancement

Comments

@BigStone
Copy link
Mannequin

BigStone mannequin commented Oct 6, 2018

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
    Copy link
    Mannequin Author

    BigStone mannequin commented Oct 6, 2018

    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
    Copy link
    Contributor

    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.

    @ronaldoussoren ronaldoussoren added the type-bug An unexpected behavior, bug, or error label Oct 9, 2018
    @BigStone
    Copy link
    Mannequin Author

    BigStone mannequin commented Oct 9, 2018

    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
    Copy link
    Contributor

    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
    Copy link
    Mannequin

    ScottStevens mannequin commented Dec 17, 2018

    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
    Copy link
    Member

    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.

    @berkerpeksag berkerpeksag added 3.8 only security fixes extension-modules C modules in the Modules dir labels May 11, 2019
    @berkerpeksag berkerpeksag changed the title include sqlite-3.25+ (with window functions) Add create_window_function() to sqlite3.Connection May 11, 2019
    @berkerpeksag berkerpeksag self-assigned this May 11, 2019
    @berkerpeksag berkerpeksag added type-feature A feature request or enhancement and removed type-bug An unexpected behavior, bug, or error labels May 11, 2019
    @coleifer
    Copy link
    Mannequin

    coleifer mannequin commented Aug 12, 2019

    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
    Copy link
    Contributor

    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.

    @ezio-melotti ezio-melotti transferred this issue from another repository Apr 10, 2022
    @erlend-aasland erlend-aasland mentioned this issue Apr 10, 2022
    Merged
    JelleZijlstra pushed a commit that referenced this issue Apr 12, 2022
    gh-79097: Add support for aggregate window functions in sqlite3 (GH-2…
    9ebcece 
    …0903)
    erlend-aasland pushed a commit to erlend-aasland/cpython that referenced this issue Apr 12, 2022
    Sync with main bco. pythongh-79097
    e15c087
    Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
    Assignees

    @berkerpeksag berkerpeksag

    Labels
    3.8 only security fixes extension-modules C modules in the Modules dir type-feature A feature request or enhancement
    Projects
    None yet
    Milestone
    No milestone
    Development

    Successfully merging a pull request may close this issue.

    gh-79097: Add support for aggregate window functions in sqlite3 erlend-aasland/cpython
    3 participants
    @berkerpeksag @ronaldoussoren @erlend-aasland