Re: [sqlite] sqlite3_update_hook
Thanks Dennis for your reply. I would like to avoid triggers for performance reasons. I haven't found an official solution so I am implementing my own sqlite3_update_notify API that is executed before the operation takes place. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 18, 2007, at 4:41 PM, Dennis Cote wrote: Marco Bambini wrote: with sqlite3_update_hook I can get the rowid of the row AFTER it has been INSERTed, DELETEd or UPDATEd. Is there a way to get the rowid of a row BEFORE it is DELETEd or UPDATEd ? If not, can someone suggest a good approach to this problem? Marco, You can use a "before update on table" or "before delete on table" trigger to get the rowid of the row before it is deleted. You can access the value old.rowid from within the trigger and save it into another table for example. See http://www.sqlite.org/ lang_createtrigger.html for more details. HTH Dennis Cote -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3_update_hook
Marco Bambini wrote: with sqlite3_update_hook I can get the rowid of the row AFTER it has been INSERTed, DELETEd or UPDATEd. Is there a way to get the rowid of a row BEFORE it is DELETEd or UPDATEd ? If not, can someone suggest a good approach to this problem? Marco, You can use a "before update on table" or "before delete on table" trigger to get the rowid of the row before it is deleted. You can access the value old.rowid from within the trigger and save it into another table for example. See http://www.sqlite.org/lang_createtrigger.html for more details. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3_update_hook and transactions
Make the hook queue a GUI update transaction and execute those transactions following the COMMIT. Discard the list of GUI update transactions on a ROLLBACK. Jef Driesen wrote: I was planning to use the sqlite3_update_hook function to notify my GUI about changes. The idea was that every part of the GUI can update itself when a database change is detected. But during testing, I encountered some problems with this approach together with transactions. When I group some SQL statements inside a transaction (for performance or because they have to succeed or fail all together), the callback function is executed for every statement: BEGIN statement 1 -> callback function called statement 2 -> callback function called COMMIT But if the COMMIT is replaced with a ROLLBACK (for instance when an error is detected), the callback functions are still called and the GUI is updated with data that is not actually written to the database. Any ideas on how to prevent this from happening? - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3_update_hook and transactions
Jef Driesen <[EMAIL PROTECTED]> wrote: > > I think that approach should work. But the function sqlite3_commit_hook > is marked experimental in the documentation. What does that means? Is it > safe to rely on it? > "Experimental" means that we reserve the right to change it in future releases of SQLite. Most of the API is guaranteed to continue to be supported in exactly its current form. So, for example, when we wanted to enhance the behavior of sqlite3_prepare() we had to do so by adding sqlite3_prepare_v2(), not by changing the existing sqlite3_prepare(). And when the reason for existance of the sqlite3_global_recover() API went away, we still have to have an API with that name that is a no-op. With sqlite3_commit_hook(), we don't want to be bound by that contract. If in the future somebody comes up with a great idea for enhancing the behavior of sqlite3_commit_hook(), we want to be able to make the change without adding sqlite3_commit_hook_v2(). Or if somebody finds a fatal flaw in the whole sqlite3_commit_hook() concept, we'd like to be able to remove the API all together. Sqlite3_commit_hook() will not change without good reason. But if a good reason does come up, because it is experimental, it might change. Does that mean you can't rely on it? I suppose the answer to that question depends on what you mean by "rely on". Most libraries treat *every* API as if it were experimental. This is especially true the open-source world. SQLite tries to provide some guarantee of compatibility from one release to the next in order to preserve backwards compatibility. But we also try to give ourselves some wiggle room by explicitly declaring certain functions as experimental and therefore subject to change. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3_update_hook and transactions
On Mon, 2007-03-12 at 10:51 +0100, Jef Driesen wrote: > I was planning to use the sqlite3_update_hook function to notify my GUI > about changes. The idea was that every part of the GUI can update itself > when a database change is detected. But during testing, I encountered > some problems with this approach together with transactions. > > When I group some SQL statements inside a transaction (for performance > or because they have to succeed or fail all together), the callback > function is executed for every statement: > > BEGIN > statement 1 -> callback function called > statement 2 -> callback function called > COMMIT > > But if the COMMIT is replaced with a ROLLBACK (for instance when an > error is detected), the callback functions are still called and the GUI > is updated with data that is not actually written to the database. > > Any ideas on how to prevent this from happening? Accumulate updates in a custom data structure (list or something) each time the update_hook() callback is invoked. Also register callbacks with sqlite3_commit_hook() and sqlite3_rollback_hook(). When the commit_hook() callback is invoked, update the GUI. When either the commit or rollback hooks are invoked, reset the data structure to empty. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3_update_hook
A wise computer scientist once told me "In Computer Science every problem can be solved by yet another level of indirection". Your problem is no exception. JS Cameron Tofer wrote: The rowid of the table's record in the sqlite_master table would be great, but really any unique integer that I can later use to get the table's name would be fine. Dennis Cote wrote: Cameron Tofer wrote: Hi, I'd like to modifiy sqlite3_update_hook so the callback returns the table's id instead of the table's name. I'm not worried about proposing any official changes to the spec but rather a small customization for myself. Can anyone point me in the right direction for this? At the point where the callback is made I'm not sure what's available. thanks. -cam Cameron, What do you mean by the table's ID? Do you want the table's root page, the rowid of the table's record in the sqlite_master table, or something else? In SQL the table's name is its ID. Dennis Cote
Re: [sqlite] sqlite3_update_hook
Cameron Tofer wrote: The rowid of the table's record in the sqlite_master table would be great, but really any unique integer that I can later use to get the table's name would be fine. Cameron, Then you should be able to execute the following SQL query in your sqlite3_update_hook handler itself to convert the table name to an integer rowid. select rowid from database_name.sqlite_master where type = 'table' and name = table_name This rowid value will not be useful if you are using multiple databases, because you won't know which database it comes from. But, if you have a single database then you can ignore the database name (i.e. it is always the same and matches the open database). The query then simplifies to select rowid from sqlite_master where type = 'table' and name = table_name To do anything useful with the table you will have to convert this rowid back into a table name to build an SQL query. You can convert the rowid back to a table name using the inverse lookup: select name from sqlite_master where rowid = table_id You could make a modified version of SQLite that does this before calling the sqlite3_update_hook handler, and then passing this rowid back. If you do this you will have a nonstandard library which you have to maintain yourself. If you are trying to avoid storing the names, you could create a small cache in your application that maps the names to integers. A vector or array of strings should work. You scan the array looking for the name. If found, use its index as your integer id, if not, add it to the array and then use its index as your id. I don't know what your applications requires, but it seems to me there must be a better way than creating a modified library that returns pseudo ids for tables which you can't use directly. HTH Dennis Cote
Re: [sqlite] sqlite3_update_hook
The rowid of the table's record in the sqlite_master table would be great, but really any unique integer that I can later use to get the table's name would be fine. Dennis Cote wrote: Cameron Tofer wrote: Hi, I'd like to modifiy sqlite3_update_hook so the callback returns the table's id instead of the table's name. I'm not worried about proposing any official changes to the spec but rather a small customization for myself. Can anyone point me in the right direction for this? At the point where the callback is made I'm not sure what's available. thanks. -cam Cameron, What do you mean by the table's ID? Do you want the table's root page, the rowid of the table's record in the sqlite_master table, or something else? In SQL the table's name is its ID. Dennis Cote
Re: [sqlite] sqlite3_update_hook
Cameron Tofer wrote: Hi, I'd like to modifiy sqlite3_update_hook so the callback returns the table's id instead of the table's name. I'm not worried about proposing any official changes to the spec but rather a small customization for myself. Can anyone point me in the right direction for this? At the point where the callback is made I'm not sure what's available. thanks. -cam Cameron, What do you mean by the table's ID? Do you want the table's root page, the rowid of the table's record in the sqlite_master table, or something else? In SQL the table's name is its ID. Dennis Cote
