Rollback and $undowork

Wed Jul 13 15:24:50 UTC 2022

interesting question.  if you are asking:

once I determine changes to a record are ok and save all to the database
-and then-
I want to let the user have an undo feature so that they can say ’no, I didn’t mean that last change, undo it’

then begin/rollback will NOT help you.

why? because you have already made the change to the database by using a begin/commit BEFORE the user does the rollback

and in the intervening time, some other user could get that record and change it, so there is no real undo possible.

the only way you could ‘undo’ database updates in such a manner is to:

1) create a record of the data before the change — eg in a separate list
2) Make the change to the database and commit it.

3) the user thne says undo
4) you re-read the database record and compare it to the version in a separate list in some way (maybe you have a verison number field in the data record)

5) if the data is what you expect, then you can do another update to change it back, and then remove the undo from your separate list
6) if the data is not what you expect, you have to say that another user changed the data.

thats a lot of work to undo data changes.

Some people, though, like to undo deletes.   that is a bit more simple by ‘fake’ deleting records and adding a management process.

what you can do in that case is
1) add a field to the data record called ‘logically_deleted’
2) if somebody deletes the record, you set ‘logically_deleted=true’ and UPDATE the data

then if somebody wants to undo the delete, you change the ‘logically_deleted’ field to ‘false’

you’d have to build something into your database reading mechanism’s to ignore ‘logically_deleted=true’ records so that uses don’t see them, except in special cases.

sometime later, perhaps after a month, you could automatically ‘delete’ the ‘logically_deleted’ records for real.

but is either of these a candidate for.  begin/rollback. — no.

begin/rollback are for aborting the current transaction only.

Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978

> On Jul 13, 2022, at 6:52 AM, Martin Obongita via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:
> 
> Hi Doug,
> I am starting to think about this rollback feature and how apply in my application.
> Does this feature apply to undo? Say you successfully save a record and then you realize you made a mistake.
> To correct that mistake, should I use transaction?
> Rgds,Martin O.
> 
>    On Wednesday, July 13, 2022 at 04:42:55 PM GMT+3, Doug Easterbrook via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:
> 
> I’m not sure if you are asking a conceptual question here, or not.
> 
> the purpose of a transaction boundary ($begin through $commit) is to assume things are going to work as you do sql updates.
> 
> and if you find failure for any reason (somebody else locked a record, you can’t read something you expected to exist, some referential integrity failure occurred, you hit deadlock, a database connection failure, a stored procedure error, malformed sql, anything really), then you rollback your changes.
> 
> 
> what do you know when you rollback changes — the database is in exatly the same state as it was before your started.    None of the updates actually occurred to the data.
> 
> 
> so, can you do an undowork in omnis .  By that I assume you mean, can you tell all your table classes within the $begin and $comit boundary  to put all your data back to square one —  no.  you cannot.
> 
> you can only go back to square one by rereading all the data and starting over.
> 
> 
> pseudo code example:
> 
> read 4 tables: customer,  inventory, orders, and payments
> sell a widget which will need to update
> — customer balance
> — decrease inventory
> — make an order
> — add a payment
> 
> 
> $begin()
> 
> customer table:
> 
> add to balance
> $dowork.    <— omnis has completed the changes in this table, database has the change
> 
> 
> Inventory table
> 
> Decrease count of inventory for widget and adjust inventory balance
> $dowork.    <— omnis has completed the changes in this table, database has the change
> 
> 
> Order table:
> insert an order record
> update balance
> associate with customer
> $dowork.    <— omnis has completed the changes in this table, database has the change
> 
> 
> make a link record between the order and the inventory table to track what was sold as a detail item
> 
> insert order detail link record
> 
> BOOM — lets say we have a failure here because of a programming error and the serial number field was not set up correctly, so the record will not insert (I’m using this as an example, since you asked about this with in the past week).
> 
> 
> $dowork.    <———  WILL FAIL for this table
> 
> 
> your reaction to the $dowork failing is to do a $rollback.
> 
> 
> at this point, the database is back in its original state, as if nothing ever happened.
> 
> 
> 
> what about omnis tables?
> 
> 
> 1) well, they all have the new values.
> 2) you can’t tell omnis to go back to original values before you started the transaction.
> 3) so you start over and
> 
> Read all 4 tables like you did at the start of the process  to get the data back to original within your omnis lists.
> 
> 
> 
> 
> 
> ——————————
> 
> As for transactions, we use them a lot of the time, especially when updating two or more tables that must be consistent.  the best example of why you’d do that is a banking one
> 
> you deposit money
> the bank updates their account with a debit
> the bank updates your account with a credit so that things balance
> 
> 
> if that was not in a transaction, and the process failed after the bank said they had more money, but before they said it was in your account, you’d end up feeding the bank money, and you’d have none.      it must be done in a transaction for banking system integrity.
> 
> 
> 
> 
> 
> 
> 
> 
> Doug Easterbrook
> Arts Management Systems Ltd.
> mailto:doug at artsman.com
> http://www.artsman.com
> Phone (403) 650-1978
> 
>> On Jul 12, 2022, at 3:26 PM, Das Goravani <goravanis at gmail.com> wrote:
>> 
>> 
>> 
>> Dear List,
>> 
>> I can’t figure this out and nowhere is this covered…
>> 
>> If you have a SQL Transaction going, and you are some ways through it, and you have done a $dowork command on a certain list, and you have to rollback, do you ALSO issue $undowork?
>> 
>> Said another way, if you $dowork in a transaction, and you rollback, do you need to do $undowork?
>> 
>> Thanks,
>> 
>> Das Goravani
>> _____________________________________________________________
>> Manage your list subscriptions at https://lists.omnis-dev.com
>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
> 
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
> 
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com