According to the documentation of Virtual Tables and Opcodes: xBegin( table ) is called to announce that SQlite intends to write to the table. There is no cursor involved. Do whatever is necessary to write to the backing store and set any VT implementation specific information in the table structure (e.g. a file handle enabled for writing)
xUpdate( table, rowid[, values] ) is called to perform a change (write to) the VT. Insert, delete or rewrite the record, or at least save whatever is required to actually perform the changes when xCommit is called. xSync( table ) is called on ALL virtual tables involved in a transaction that is READY to commit. If anything is known that would cause a commit to fail, tell SQLite about that NOW. xCommit( table ) resp. xRollback( table ) is called to instruct the VT to commit resp. rollback all the changes (made via xUpdate) since the last call to xBegin. xOpen( table, cursor) is called to announce that SQLite intends to read from a table. A cursor cannot be used to write to a table. Do whatever is necessary to read from the backing store and set any VT implementation specific fields in the cursor structure (e.g. a file handle enabled for reading) xFilter( cursor ) is called to set the cursor to the first row of data. If ths VT is in an inner loop (RH side, after reordering by the quera planner) of a join, xFilter may be called multiple times for the same cursor. xRowid( cursor ), xColumn( cursor, field number ) return the unique(!!) rowid or the contents of the specfiied field of the current record xNext( cursor ) advance to the next row xEof( cursor ) query if there is a "current row" xClose( cursor ) is called when SQLite no longer requires the cursor. NOTE: If you are using posix advisory locks in your VT implementation, it would be wise to postpone closing the file handle until the end of the transaction. Closing ANY file handle on a certain file in ANY thread will cause ALL the locks on that file held by ANY thread in the whole process to be released. To avoid anomalies when changing "key fields", SQLite will scan through the whole cursor first, saving the rowids and new contents of the record(s) satisfying the WHERE clause. It will then close the cursor and call xUpdate for the affected records. The main sequence is: xBegin() - xOpen() - xFilter() - xNext()... - xClose() - xUpdate()... - xSync() - xCommit() -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:[email protected]] Im Auftrag von Dan Kennedy Gesendet: Mittwoch, 29. März 2017 10:03 An: [email protected] Betreff: Re: [sqlite] VT table behavior change between 3.10 and 3.17 On 03/29/2017 02:48 AM, Bob Friesenhahn wrote: > We are trying to update from sqlite3 3.10 to 3.17. Our virtual table > modules are encountering problems with 3.17 since the 'xOpen' callback > is now being called for value change and row deletion operations. > Previously it was only being called for read-only queries. Something else is going on I think. There has never been a version of SQLite that could do an UPDATE or DELETE on a virtual table without invoking xOpen() to create a cursor. It needs the cursor to determine which rows are matched by the WHERE clause. > > We are using reader/writer locks and there is not a convenient way to > transition from being a reader to being a writer. A file is opened by > the 'xOpen' callback and we need to know if the file should be opened > read only, or read/write. > > The change in behavior can only work with virtual table modules which > are able to smoothly transition between the state established by > 'xOpen' to the 'xUpdate' call or know the intent when 'xOpen' is > called. This did not seem to be a requirement before. > > In what sqlite3 version did this behavior change? > > Is there a way to know when the 'xOpen' callback is called if it is to > support an update transaction (i.e. 'xUpdate' callback will be called)? When a write-transaction is opened on a virtual table the xBegin method is called. xCommit() or xRollback() to end the transaction. Any xOpen() that is part of an UPDATE or DELETE operation will occur within a transaction, and any xOpen() outside of a transaction must be a read-only query. But within an open transaction there is no way to tell whether a specific xOpen() call is part of a read-only or read-write statement. Dan. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: [email protected] This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
