Re: [sqlite] Undo logs and transactions
Yes, I completely agree. But a single "undoable event" generates multiple changes to the database (which might as well be done inside a single transaction). Thus one click on Undo results in multiple changes to undo the event (ie, undoing the entire event's transaction). This implies that somehow the undo log either: - Tags multiple rows in the undo log as comprising a single event, or - Accumulates multiple queries into a single row in the undo log I really liked the elegance of using triggers to automatically create the undo log, but I don't see how to accomplish either of the above strategies (tagging a group of undo rows, or concatenating multiple undos into a single row) in that design without some manual legwork. So my question was whether there is some "COMMIT_COUNT()" SQL function or constant that represents "the number of committed transactions on this database", as then I could just stick that in the trigger query and insert it along with the undo query into the undo log as follows: CREATE TEMP TRIGGER _ex1_it AFTER INSERT ON ex1 BEGIN INSERT INTO undolog VALUES(NULL,COMMIT_COUNT(),'DELETE FROM ex1 WHERE rowid='||new.rowid); END; Then multiple INSERTs in a single transaction would generate multiple DELETEs in the undolog, all tagged with the same commit count. Later, I could look up the set of queries necessary to undo that transaction with: SELECT sql FROM undolog WHERE commitcount=XXX; The upshot is I could roll back one transaction at a time, all the way back to the beginning (or as far as the undo log goes). But lacking a COMMIT_COUNT() function, I might instead just create a simple table: CREATE TABLE commitcount(num) And then increment that at the start of each transaction: UPDATE commitcount SET num=(SELECT num FROM commitcount)+1; And then my trigger could be something like: CREATE TEMP TRIGGER _ex1_it AFTER INSERT ON ex1 BEGIN INSERT INTO undolog VALUES(NULL,(SELECT num FROM commitcount),'DELETE FROM ex1 WHERE rowid='||new.rowid); END; This'll do the same thing, but requires a subquery, an extra table, and manual incrementing of the commit count. Works, but is yucky. Another way might be to create a table that just keeps track of which range in the undolog corresponds to which distinct undoable event (aka transaction)... Perhaps a bit cleaner, but still not great. Anyway, I was just curious if the COMMIT_COUNT() function existed, because then it'd be really clean and easy. But it sounds it doesn't, and that's what I wanted to know. Thanks! -david D. Richard Hipp wrote: > On May 13, 2008, at 6:21 PM, David Barrett wrote: > >> True, but even an application would need to "undo" in transactions, >> I'd >> think. Like, if a user drags a widget from column A to B, it >> generates >> an INSERT in one column and a DELETE in another. Pressing Undo once >> would leave it in both columns, which is probably unexpected. >> > > And undo/redo mechanism typically groups actions together by user > input event. The application is typically event driven. It sits idle > waiting for a user event, such as a mouse click. That event might > trigger a cascade of related events, some of which involve database > changes. All database changes associated with that one event are > undone together. You know that you have reached the end of your event > cascade when the event loop goes idle again. > > For processing gestures (drags, and other inputs that involve multiple > events spread out over time) you acquire a lock or a "grab" at the > beginning of the gesture, hold it throughout the gesture, then release > it when the gesture completes. You do not finish the undo package > until the end of the gesture. So the complete rule for when you stop > one undoable entry and start a new one is: you are idle (no pending > events) and you are not holding a grab. > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Undo logs and transactions
True, but even an application would need to "undo" in transactions, I'd think. Like, if a user drags a widget from column A to B, it generates an INSERT in one column and a DELETE in another. Pressing Undo once would leave it in both columns, which is probably unexpected. Anyway, I was more thinking of a "transaction count" sort of variable, indicating how many total transactions have been committed to the database. Just something to group undo log entries by transaction without maintaining a manual transaction identifier. Thanks for considering it! -david Ken wrote: > the Undo/Redo mechanism described is not really for transactions but rather a > "Button" within an application. So that the user can undo/redo changes. > > Only one transaction can be active at a time. So to the best of my knowledge > there is no "transaction number" as there can be only 1. Now you may go ahead > and implement "pseudo transactions" and implement your own transaction > number. etc... > > HTH, > Ken > > > David Barrett <[EMAIL PROTECTED]> wrote: What's the best way to group undo > log entries by transaction? Is there > a function that returns the current transaction number? Or what's the > best way to set a global variable that is inserted into the undo log by > the trigger? > > As background, the wiki has a great page on undo/redo: > > http://www.sqlite.org/cvstrac/wiki?p=UndoRedo > > But unless I misunderstand, it seems that a single transaction will > create multiple entries in the log. For example, a transaction > containing both an insert and a delete will generate two entries in the > undo log. Therefore, to undo that transaction, I need to atomically > commit the "undo" of both the insert and delete in a single transaction. > > That's fine, but the log created by the example doesn't seem to indicate > which group of undo rows correspond to a single transaction. Indeed, > the word "transaction" doesn't even appear in the page. > > Now, digging through the archives I see reference to a "transaction > number" here: > > http://www.mail-archive.com/sqlite-users@sqlite.org/msg23962.html > > Is there any explicit way to get the current transaction number? I was > hoping to see some "transaction_number()" function here, but I don't: > > http://www.sqlite.org/lang_corefunc.html > > If such a function did exist, then I could just update the triggers to > insert that along with the corresponding SQL. > > Lacking that, what's the best way to manually to set some kind of global > variable at the start of each transaction? One way is to maintain a > separate table with a single cell containing the latest transaction > number (which I manually increment at the start of each transaction) and > then just SELECT that in the trigger sub-query, but that seems yucky. > Is there an easier way I'm missing? > > Thanks! > > -david > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Undo logs and transactions
the Undo/Redo mechanism described is not really for transactions but rather a "Button" within an application. So that the user can undo/redo changes. Only one transaction can be active at a time. So to the best of my knowledge there is no "transaction number" as there can be only 1. Now you may go ahead and implement "pseudo transactions" and implement your own transaction number. etc... HTH, Ken David Barrett <[EMAIL PROTECTED]> wrote: What's the best way to group undo log entries by transaction? Is there a function that returns the current transaction number? Or what's the best way to set a global variable that is inserted into the undo log by the trigger? As background, the wiki has a great page on undo/redo: http://www.sqlite.org/cvstrac/wiki?p=UndoRedo But unless I misunderstand, it seems that a single transaction will create multiple entries in the log. For example, a transaction containing both an insert and a delete will generate two entries in the undo log. Therefore, to undo that transaction, I need to atomically commit the "undo" of both the insert and delete in a single transaction. That's fine, but the log created by the example doesn't seem to indicate which group of undo rows correspond to a single transaction. Indeed, the word "transaction" doesn't even appear in the page. Now, digging through the archives I see reference to a "transaction number" here: http://www.mail-archive.com/sqlite-users@sqlite.org/msg23962.html Is there any explicit way to get the current transaction number? I was hoping to see some "transaction_number()" function here, but I don't: http://www.sqlite.org/lang_corefunc.html If such a function did exist, then I could just update the triggers to insert that along with the corresponding SQL. Lacking that, what's the best way to manually to set some kind of global variable at the start of each transaction? One way is to maintain a separate table with a single cell containing the latest transaction number (which I manually increment at the start of each transaction) and then just SELECT that in the trigger sub-query, but that seems yucky. Is there an easier way I'm missing? Thanks! -david ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
