Android Question What is best way to allow user to undo changes to Sqlite DB?

[Widget]

I have a B4A app where the user can display a few dozen records from a table, modify any of the records at random, tweak some values to see the new results. He could do this for 10 minutes or so. He would not be accessing all of the rows from the table, maybe 10 to 100 rows at a time. Each row has around 1k to 5k of text in it so that would amount to 10k to 500k of data being loaded from the table in total for that session.

I am currently using DbUtils and it works just fine. Whenever the user updates a record, it gets written back to the Sqlite database.

But I got to thinking that it would be nice to add a feature where the user can roll back the changes he's made if he doesn't like the results after all that tweaking. So if he has changed 20 records and doesn't want to save it, he should be able to press an "Undo" button and all the changes he's made to the table will be reverted back to when he started (first loaded the data).

There are a few ways to achieve this.

1. When the rows are loaded from the Sqlite table (Table1), start a transaction. He can make as many changes as he likes and if he wants to save them, commit the transaction. If he doesn't want to save the changes, rollback the changes. But will this create a problem with the transactions that are being used by DBUtils because it means the transactions will have to be nested? Can I nest Sql1.BeginTransaction/Sql.EndTransaction?
2. Can B4A use Sqlite SavePoints? A Sqlite SavePoint works similar to a transaction but with it is given name so the changes can be rolled back to the point when the "savepoint <name>" was started. These savepoints can be nested. See https://sqlite.org/lang_savepoint.html
3. Instead of accessing the data directly from Table1, create a temporary table "TableTmp" and load it with the 10 to 100 rows from Table1 and allow the user to make changes to TableTmp. If he wants to save it, write the modified rows in TableTmp back to Table1. This will be slower because it will take more time to copy the data to a temporary table and then save the results back to the table. It will of course require more storage for the temporary table.
4. Load the data from Table1 into a map and have the B4A application access the map instead of a SQLite table. This uses considerably more memory and I will have to handle writing the data to disk when the app is suspended. And it will have to be securely encrypted like it is with Sqlite.

These are the possible solutions I've come up with.
Can anyone make a recommendation or offer a better solution?

TIA

 

[edgar_ortiz]

I prefer:
- Create a "LogTable", when the user "save" the data, "Table1" receive it, and the "LogTable" keep the "original" info... if the user wants a "undo" you can show the data stored in "LogTable" and apply the "undo".

Regards,

Edgar

 

[Erel]

Can B4A use Sqlite SavePoints?

The SQL commands are passed as is to the SQLite engine. I guess that it should work. Note that save points are only valid until the transaction is committed.

Worth checking the UndoManager class: https://www.b4x.com/android/forum/threads/b4x-class-undomanager.73019/#content
Though I'm not sure that it is suitable for your case.

What is the size of the database file?

 

[Widget]

The SQL commands are passed as is to the SQLite engine. I guess that it should work. Note that save points are only valid until the transaction is committed.

I believe all I have to do is execute:
"SAVEPOINT BeforeTableUpdates"
... make changes to table ...

Then to save the changes, execute:
"RELEASE SAVEPOINT BeforeTableUpdates" and this will implicitly commit the transaction.

or roll them back with:
"ROLLBACK TRANSACTION TO SAVEPOINT BeforeTableUpdates"

I shouldn't have to explicitly execute a "Commit" otherwise it may erroneously commit a previously started transaction that this sub does not have the authority to commit. The SavePoints can be nested within another transaction. For example, a parent sub may have started its own transaction and this edit sub won't have the authority to commit the transaction that was started by the parent sub. It only has the authority to commit or rollback the work that it started. That's why SAVEPOINTS are so clever.

Worth checking the UndoManager class: https://www.b4x.com/android/forum/threads/b4x-class-undomanager.73019/#content
Though I'm not sure that it is suitable for your case.

Yeah, just not practical. But it could be useful in the future for another application. Thanks for mentioning it.

What is the size of the database file?

The entire database could be quite large, 5MB or 10MB.

I think Edgar's solution of a LogTable will be the most efficient in my case because only records that are being edited or deleted will need to be copied to the LogTable. I would use SavePoints except the record is saved whenever a certain view value (gauge) is changed and this would create a rather large transaction file. With Edgar's solution the records only need to be copied back if the user decides to execute the Undo. Otherwise I just drop the logtable.