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

sqlite3.Cursor.lastrowid isn't populated when executing a SQL REPLACE statement #61068

Closed
jimminter mannequin opened this issue Jan 4, 2013 · 15 comments
Closed

sqlite3.Cursor.lastrowid isn't populated when executing a SQL REPLACE statement #61068

jimminter mannequin opened this issue Jan 4, 2013 · 15 comments
Labels
stdlib Python modules in the Lib dir type-feature A feature request or enhancement

Comments

@jimminter
Copy link
Mannequin

jimminter mannequin commented Jan 4, 2013

BPO 16864
Nosy @bitdancer, @berkerpeksag
Files
  • Issue16864_py35.patch
  • sqlite_lastrowid_35.patch
  • sqlite_lastrowid_35_v2.patch
  • sqlite_lastrowid_35_updated.patch
  • sqlite_lastrowid_35_updated_2.patch
  • sqlite_after_review_1.patch
  • sqlite_review_2.patch
  • replace_lastrowid_3_6.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 2016-06-14.12:26:00.807>
    created_at = <Date 2013-01-04.17:24:20.292>
    labels = ['type-feature', 'library']
    title = "sqlite3.Cursor.lastrowid isn't populated when executing a SQL REPLACE statement"
    updated_at = <Date 2016-06-14.12:26:00.805>
    user = 'https://bugs.python.org/jimminter'

    bugs.python.org fields:

    activity = <Date 2016-06-14.12:26:00.805>
    actor = 'berker.peksag'
    assignee = 'ghaering'
    closed = True
    closed_date = <Date 2016-06-14.12:26:00.807>
    closer = 'berker.peksag'
    components = ['Library (Lib)']
    creation = <Date 2013-01-04.17:24:20.292>
    creator = 'jim_minter'
    dependencies = []
    files = ['34953', '39336', '39339', '39366', '39367', '39411', '39425', '39677']
    hgrepos = []
    issue_num = 16864
    keywords = ['patch']
    message_count = 15.0
    messages = ['179049', '179058', '216666', '216735', '241137', '242867', '242868', '242882', '243326', '243431', '243442', '243560', '245153', '268553', '268554']
    nosy_count = 6.0
    nosy_names = ['ghaering', 'r.david.murray', 'python-dev', 'berker.peksag', 'jim_minter', 'Alex.LordThorsen']
    pr_nums = []
    priority = 'normal'
    resolution = 'fixed'
    stage = 'resolved'
    status = 'closed'
    superseder = None
    type = 'enhancement'
    url = 'https://bugs.python.org/issue16864'
    versions = ['Python 3.6']

    @jimminter
    Copy link
    Mannequin Author

    jimminter mannequin commented Jan 4, 2013

    sqlite3 doesn't populate the lastrowid member of the Cursor object when a SQL REPLACE statement is executed.

    The following snippet doesn't work as I would expect:
    cursor = db.execute("REPLACE INTO table(column) VALUES ('datum')")
    print cursor.lastrowid # prints None

    The following snippet, with SQL which is in effect identical to SQLite, does work as expected:
    cursor = db.execute("INSERT OR REPLACE INTO table(column) VALUES ('datum')")
    print cursor.lastrowid # prints some rowid

    Looking at Modules/_sqlite/cursor.c, in _pysqlite_query_execute(), the following snippet is found:

    if (!multiple && statement_type == STATEMENT_INSERT) {
        Py_BEGIN_ALLOW_THREADS
        lastrowid = sqlite3_last_insert_rowid(self->connection->db);
        Py_END_ALLOW_THREADS
        self->lastrowid = PyLong_FromLong((long)lastrowid);
    } else {
        Py_INCREF(Py_None);
        self->lastrowid = Py_None;
    }
    
    I suggest this should read something like:
    if (!multiple && (statement_type == STATEMENT_INSERT || statement_type == STATEMENT_REPLACE)) {
    instead of:
    if (!multiple && statement_type == STATEMENT_INSERT) {

    Thanks,

    Jim

    @jimminter jimminter mannequin added type-feature A feature request or enhancement stdlib Python modules in the Lib dir labels Jan 4, 2013
    @bitdancer
    Copy link
    Member

    bitdancer commented Jan 4, 2013

    Considering that sqlite's 'replace' is a synonym for 'insert or replace', I think the logic error is actually in the detect_statement_type function. Since actions are conditionally taken on the REPLACE statement type in the code, including at least one that adjusts the lastrowid, I don't think the fix for lastrowid is as simple as just always setting it. But I'm not that familiar with sqlite internals, so perhaps someone with more knowledge will weigh in.

    @AlexLordThorsen
    Copy link
    Mannequin

    AlexLordThorsen mannequin commented Apr 17, 2014

    Have a unit test that replicates this bug. Working on the C code to fix it right now.

    @pitrou pitrou added type-bug An unexpected behavior, bug, or error and removed type-feature A feature request or enhancement labels Apr 17, 2014
    @AlexLordThorsen
    Copy link
    Mannequin

    AlexLordThorsen mannequin commented Apr 17, 2014

    Patch that fixes bpo-16864. Follows Jim Minters suggestion.

    Unit test will reproduce the issue without the c modifications. C modifications fix the issue.

    @bitdancer
    Copy link
    Member

    bitdancer commented Apr 15, 2015

    Made some review comments. There also needs to be a documentation change since the docs currently say it is set for insert only. There should be a .. versionchanged directive. This may be small enough not to pass the what's new threshold, but I'd rather add a note and let the person who does the final edit on that document decide, so please add that as well.

    (You will note that I've changed this to enhancement...it is documented as *only* working for INSERT, so I believe it must be treated as an enhancement to make it work for REPLACE.)

    @bitdancer bitdancer added type-feature A feature request or enhancement and removed type-bug An unexpected behavior, bug, or error labels Apr 15, 2015
    @AlexLordThorsen
    Copy link
    Mannequin

    AlexLordThorsen mannequin commented May 10, 2015

    Updated the patch to have a versionchanged for lastrowid in Doc/Library/sqlite3.rst and Doc/whatsnew/3.5.rst

    One thing of note is that I wasn't able to get the indentation to be on the same level for sqlite3.rst (it would either intent the description text or the versionchange text).

    Now that I'm actually starting to understand the dbapi and sqlite3 I've come to the conclusion that the lastrowid method should update the lastrowid for the INSERT OR ROLLBACK, INSERT OR ABORT, INSERT OR FAIL, INSERT OR IGNORE statements as well as the INSERT and INSERT OR REPLACE statements.

    I'm unsure how hard or simple supporting those statements will be

    The code in question is

    704 Py_DECREF(self->lastrowid);$
    705 if (!multiple && statement_type == STATEMENT_INSERT) {$
    706 sqlite_int64 lastrowid;$
    707 Py_BEGIN_ALLOW_THREADS$
    708 lastrowid = sqlite3_last_insert_rowid(self->connection->db);$
    709 Py_END_ALLOW_THREADS$
    710 self->lastrowid = _pysqlite_long_from_int64(lastrowid);

    And the difficulty will be if sqlite3_last_insert_rowid (line 708) does or doesn't return a row id if the OR STATEMENT portion of those inserts are triggered.

    The Problem I'm having is that when I tried to find sqlite3_last_insert_rowid in the Modules/_sqlite directory I got nothing

    $ grep -r "sqlite3_last_insert_rowid" Modules/_sqlite/
    Modules/_sqlite//cursor.c:            lastrowid = sqlite3_last_insert_rowid(self->connection->db);

    When I pulled the grep out to the entire cpython repository

    $ grep -r "sqlite3_last_insert_rowid" .
    Binary file ./build/lib.macosx-10.10-x86_64-3.5-pydebug/_sqlite3.cpython-35dm-darwin.so matches
    Binary file ./build/lib.macosx-10.10-x86_64-3.5-pydebug/_sqlite3.so matches
    Binary file ./build/temp.macosx-10.10-x86_64-3.5-pydebug/Users/alexlord/mercurial/cpython/Modules/_sqlite/cursor.o matches
    ./Modules/_sqlite/cursor.c:            lastrowid = sqlite3_last_insert_rowid(self->connection->db);

    I still didn't find anything and I'm not sure where to go from here.

    @AlexLordThorsen
    Copy link
    Mannequin

    AlexLordThorsen mannequin commented May 10, 2015

    Thanks for Alex_gayner and lifeless. They pointed out the sqlite3_last_row_id is part of the sqlite3 module itself (not cpython).

    https://www.sqlite.org/c3ref/last_insert_rowid.html

    According the documentation we can expect that if a constraint stops an insertion then the lostrowid is not modified. As such the changes required to add full INSERT OR CONDITION support for last row id is unit tests for each statement and changing the conditional to recognize all insert cases.

    @AlexLordThorsen
    Copy link
    Mannequin

    AlexLordThorsen mannequin commented May 11, 2015

    Went back to test to see if the other statements are covered already. Unit tests show that lastrowid is set properly no matter what form of sqlite insert statement is used.

    sqlite_lastrowid_35_v2.patch contains the doc changes, unit test changes, and code change.

    I believe that the latest version of this patch is ready for a code review by a core dev.

    @bitdancer
    Copy link
    Member

    bitdancer commented May 16, 2015

    Added review comments.

    @AlexLordThorsen
    Copy link
    Mannequin

    AlexLordThorsen mannequin commented May 17, 2015

    There was a bunch of things wrong with that patch.

    In addition to the issues you brought up in the review I was mixing up what the actual problem is

    REPLACE INTO is an alias for INSERT OR REPLACE. INSERT OR REPLACE was correctly setting the lastrowid values but REPLACE INTO was not setting the last rowid value. I changed the documentation modifications to reflect this.

    In addition I cleaned up the unit tests. The unit tests were kind of a mess because I was trying to figure out what the problem was and never refactored after getting a reproduction.

    I at first went down the path of making the tests use a for loop like you suggested

        for statement in ["INSERT OR REPLACE", "REPLACE"]:
            sql = "{} INTO test(id, unique_test) VALUES (?, ?)".format(
                statement)
            self.cu.execute(sql, (1, "foo"))
            self.assertEqual(self.cu.lastrowid, 1)
            self.cu.execute(sql, (1, "foo"))$
            self.assertEqual(self.cu.lastrowid, 1) 

    Which I don't think is as nice as a cleaned up unrolled version

    self.cu.execute("INSERT OR REPLACE INTO test(id, unique_test) VALUES (?, ?)", (1, "bar",))
    self.assertEqual(self.cu.lastrowid, 1)
    self.cu.execute("INSERT OR REPLACE INTO test(id, unique_test) VALUES (?, ?)", (1, "bar",))
    self.assertEqual(self.cu.lastrowid, 1)
    self.cu.execute("REPLACE INTO test(id, unique_test) VALUES (?, ?)", (1, "bar",))
    self.assertEqual(self.cu.lastrowid, 1)
    

    I've created a patch that fixes all of the issues brought up in the code review and is just generally much cleaner.

    @bitdancer
    Copy link
    Member

    bitdancer commented May 18, 2015

    All lines need to be wrapped to <80 columns.

    The idea behind the loop is this:

    sql = "{} INTO test(id, unique_test) VALUES (?, ?)"
    self.cu.execute(sql.format('INSERT OR REPLACE, (1, "foo")
    for statement in ["INSERT OR REPLACE", "REPLACE"]:
        with self.subTest(statement=statement):
            self.cu.execute(sql.format(statement), (1, "foo"))
            self.assertEqual(self.cu.lastrowid, 1) 
    

    What this does is run *both* tests, even if one fails, and reports the results separately (labeled with 'statement='INSERT OR REPLACE', ect).

    @AlexLordThorsen
    Copy link
    Mannequin

    AlexLordThorsen mannequin commented May 19, 2015

    Oh, alright. That makes a lot of sense. Sorry for being dense. I should have read the docs on subtest.

    All lines are under 80 characters and I modified the unit test to use subtest.

    Thanks for taking the time to walk me through this.

    @AlexLordThorsen
    Copy link
    Mannequin

    AlexLordThorsen mannequin commented Jun 11, 2015

    Adding a patch for 3.6 since 3.5 is in beta.

    @ghaering ghaering mannequin self-assigned this Aug 19, 2015
    @python-dev
    Copy link
    Mannequin

    python-dev mannequin commented Jun 14, 2016

    New changeset 2126e8cbc12f by Berker Peksag in branch 'default':
    Issue bpo-16864: Cursor.lastrowid now supports REPLACE statement
    https://hg.python.org/cpython/rev/2126e8cbc12f

    @berkerpeksag
    Copy link
    Member

    berkerpeksag commented Jun 14, 2016

    Thanks for the patch, Alex.

    @ezio-melotti ezio-melotti transferred this issue from another repository Apr 10, 2022
    Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
    Labels
    stdlib Python modules in the Lib dir type-feature A feature request or enhancement
    Projects
    None yet
    Development

    No branches or pull requests

    3 participants