[sqlite] Determine type of prepared statement via C Interface?
On Fri, May 8, 2015 at 7:01 PM, Stephen Broberg wrote: > (e.g., insert, update, delete, select, etc.). Specifically, I?d like to > know whether a statement that either has run or is about to be run (via > sqlite3_step) is a read-only (select) or write (pretty much everything > else) operation. Not a complete answer, but a SELECT (or select-like pragma) will have a column count of >0. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
[sqlite] Determine type of prepared statement via C Interface?
On Fri, May 8, 2015 at 8:20 PM, Simon Slavin wrote: > Suppose you have this statement > > DELETE FROM myTable WHERE id=600 > > and there are no rows with id=600. I presume that the function will return > FALSE but a literal reading of the description says that it will return TRUE. > If I'm right then it might be useful to rewrite the documentation a little. I don't think sqlite_stmt_readonly does that. If you want to know if a statement has actually written to the database, call sqlite_total_changes before and after executing the statement, and compare their return values. Make sure you don't run other statements or write operations on the database during that time. -- Ambrus
[sqlite] Determine type of prepared statement via C Interface?
On 8 May 2015, at 6:43pm, Peter Aronson wrote: > Well, there's sqlite3_stmt_readonly which appears to do pretty much what > you're asking for: https://www.sqlite.org/c3ref/stmt_readonly.html. Suppose you have this statement DELETE FROM myTable WHERE id=600 and there are no rows with id=600. I presume that the function will return FALSE but a literal reading of the description says that it will return TRUE. If I'm right then it might be useful to rewrite the documentation a little. Simon.
[sqlite] Determine type of prepared statement via C Interface?
Using versions 3.8.5 and 3.7.7.1: Is there something in the SQLite C API that will tell what type of statement a prepared sqlite3_stmt is (e.g., insert, update, delete, select, etc.). Specifically, I?d like to know whether a statement that either has run or is about to be run (via sqlite3_step) is a read-only (select) or write (pretty much everything else) operation. I?m implementing a reader/writer lock mechanism around our access to the sqlite database, and as the consumer of the interface will need to specify the lock type when they construct a query object (save point objects would always be write, of course), I?d want to runtime sanity-check their choice after preparing the statement, and log or throw an error if they flagged a non-select statement as a reader. Perhaps it?s possible to interrogate the connection object after a statement runs to determine if the database was changed? A possible approach might be to use sqlite3_update_hook or sqlite3_commit_hook to identify the statements that modify the db. Does sqlite3_commit_hook fire when a statement is implicitly committed (e.g., an update executed outside the context of a savepoint when in autocommit mode)? Another option might be to use two database connections, one read-only, the other normal, but the design of our current system makes this option a major redesign (this is legacy code I?ve inherited. If I had my druthers, I?d be using a dedicated sqlite connection per thread and running in multi-thread mode, but right now that?s too great a refactoring task). Or am I doing this wrong? Our multithreaded app produces a lot of SQLITE_BUSY results when concurrency increases; serializing our transactions, both read & write, globally solved this problem and increased throughput, but I?m interested in moving beyond a simple mutex to a read/write one. We?re running Serialized mode, but is there some other sqlite configuration that automatically does what I?m trying to accomplish: serialize all write operations and save points for all threads, allow selects to run concurrently? Thanks.
[sqlite] Determine type of prepared statement via C Interface?
On Fri, May 8, 2015 at 11:20 AM, Simon Slavin wrote: > On 8 May 2015, at 6:43pm, Peter Aronson wrote: >> Well, there's sqlite3_stmt_readonly which appears to do pretty much what >> you're asking for: https://www.sqlite.org/c3ref/stmt_readonly.html. > > Suppose you have this statement > > DELETE FROM myTable WHERE id=600 > > and there are no rows with id=600. I presume that the function will return > FALSE but a literal reading of the description says that it will return TRUE. > If I'm right then it might be useful to rewrite the documentation a little. Why would you presume that it would return FALSE? That would essentially require executing the statement, which definitely _would_ be something useful to add to the documentation (but I doubt it does that). -scott
[sqlite] Determine type of prepared statement via C Interface?
Well, there's sqlite3_stmt_readonly which appears to do pretty much what you're asking for: https://www.sqlite.org/c3ref/stmt_readonly.html. Or, if you want more detailed control, there's the whole authorizer interface: https://www.sqlite.org/c3ref/set_authorizer.html. Peter On 5/8/2015 10:01 AM, Stephen Broberg wrote: > Using versions 3.8.5 and 3.7.7.1: > > Is there something in the SQLite C API that will tell what type of statement > a prepared sqlite3_stmt is (e.g., insert, update, delete, select, etc.). > Specifically, I?d like to know whether a statement that either has run or is > about to be run (via sqlite3_step) is a read-only (select) or write (pretty > much everything else) operation. I?m implementing a reader/writer lock > mechanism around our access to the sqlite database, and as the consumer of > the interface will need to specify the lock type when they construct a query > object (save point objects would always be write, of course), I?d want to > runtime sanity-check their choice after preparing the statement, and log or > throw an error if they flagged a non-select statement as a reader. Perhaps > it?s possible to interrogate the connection object after a statement runs to > determine if the database was changed? > > A possible approach might be to use sqlite3_update_hook or > sqlite3_commit_hook to identify the statements that modify the db. Does > sqlite3_commit_hook fire when a statement is implicitly committed (e.g., an > update executed outside the context of a savepoint when in autocommit mode)? > Another option might be to use two database connections, one read-only, the > other normal, but the design of our current system makes this option a major > redesign (this is legacy code I?ve inherited. If I had my druthers, I?d be > using a dedicated sqlite connection per thread and running in multi-thread > mode, but right now that?s too great a refactoring task). > > Or am I doing this wrong? Our multithreaded app produces a lot of > SQLITE_BUSY results when concurrency increases; serializing our transactions, > both read & write, globally solved this problem and increased throughput, but > I?m interested in moving beyond a simple mutex to a read/write one. We?re > running Serialized mode, but is there some other sqlite configuration that > automatically does what I?m trying to accomplish: serialize all write > operations and save points for all threads, allow selects to run concurrently? > > Thanks. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Determine type of prepared statement via C Interface?
sqlite3_stmt_readonly(stmt)? This hits INSERT/UPDATE/DELETE, but not BEGIN/COMMIT/ROLLBACK. Or sqlite3_sql(stmt) if you want to do it heuristically by inspecting the statement. I think a "BEGIN READONLY" would be a sensible transaction type. Having a wrapper API force the developer to select read-only or immediate for the transaction type up front has some benefits. My experience is that most developers don't write their code in a way which allows them to correctly handle SQLITE_BUSY after the transaction starts, and once they've written it it can be hard to refactor things to allow the overall transaction to be correctly restarted. -scott On Fri, May 8, 2015 at 10:01 AM, Stephen Broberg wrote: > Using versions 3.8.5 and 3.7.7.1: > > Is there something in the SQLite C API that will tell what type of statement > a prepared sqlite3_stmt is (e.g., insert, update, delete, select, etc.). > Specifically, I?d like to know whether a statement that either has run or is > about to be run (via sqlite3_step) is a read-only (select) or write (pretty > much everything else) operation. I?m implementing a reader/writer lock > mechanism around our access to the sqlite database, and as the consumer of > the interface will need to specify the lock type when they construct a query > object (save point objects would always be write, of course), I?d want to > runtime sanity-check their choice after preparing the statement, and log or > throw an error if they flagged a non-select statement as a reader. Perhaps > it?s possible to interrogate the connection object after a statement runs to > determine if the database was changed? > > A possible approach might be to use sqlite3_update_hook or > sqlite3_commit_hook to identify the statements that modify the db. Does > sqlite3_commit_hook fire when a statement is implicitly committed (e.g., an > update executed outside the context of a savepoint when in autocommit mode)? > Another option might be to use two database connections, one read-only, the > other normal, but the design of our current system makes this option a major > redesign (this is legacy code I?ve inherited. If I had my druthers, I?d be > using a dedicated sqlite connection per thread and running in multi-thread > mode, but right now that?s too great a refactoring task). > > Or am I doing this wrong? Our multithreaded app produces a lot of > SQLITE_BUSY results when concurrency increases; serializing our transactions, > both read & write, globally solved this problem and increased throughput, but > I?m interested in moving beyond a simple mutex to a read/write one. We?re > running Serialized mode, but is there some other sqlite configuration that > automatically does what I?m trying to accomplish: serialize all write > operations and save points for all threads, allow selects to run concurrently? > > Thanks. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
