The best way to handle undo and redo.

[skyzoframe[hun] 4]

Hello, I need advice from all of you. I know I am not the only one who has got these problems. Maybe someone has already solved it.

1.-How to handle undo and redo in code?

2.-What are the best strategies for handling it in the relational databases (InterBase and Firebird)?

best wishes, k.z.

Edited August 8, 2022 by skyzoframe[hun]

[Der schöne Günther 316]

12 minutes ago, skyzoframe[hun] said:

Maybe someone has already solved it.

The Command Design Pattern (sourcemaking.com) is generally used for undo/redo operations.

 

Regarding databases: Not sure if you're looking for undo/redo or just transactions. Maybe this is relevant to you:

Managing Transactions (FireDAC) - RAD Studio (embarcadero.com)

Edited August 8, 2022 by Der schöne Günther

[skyzoframe[hun] 4]

1 hour ago, Der schöne Günther said:

The Command Design Pattern (sourcemaking.com) is generally used for undo/redo operations.

Thanks for the information. I will buy the book!

[David Heffernan 2345]

If there can be multiple users modifying the same database then this task becomes very challenging. 

[A.M. Hoornweg 144]

7 hours ago, skyzoframe[hun] said:

Hello, I need advice from all of you. I know I am not the only one who has got these problems. Maybe someone has already solved it.

1.-How to handle undo and redo in code?

2.-What are the best strategies for handling it in the relational databases (InterBase and Firebird)?

best wishes, k.z.

 

A classical way to achieve undo/redo is if your object can serialize itself, for example using XML or Json.   That way you can keep the last xxx modified versions of the object in a temporary folder on disk.

[David Heffernan 2345]

3 hours ago, A.M. Hoornweg said:

 

A classical way to achieve undo/redo is if your object can serialize itself, for example using XML or Json.   That way you can keep the last xxx modified versions of the object in a temporary folder on disk.

Very rare to find a scenario where this works well.  The serialised state can be huge.  And the asker here has a database to work with. You are going to serialise that in its entirety?

[Dave Novo 51]

11 hours ago, skyzoframe[hun] said:

Thanks for the information. I will buy the book! 

There is a nice Delphi specific design patterns book as well.

 

https://www.amazon.com/dp/1789343240

 

For databases, I know interbase is supposed to be designed that it stores transactions as part of its structure, which makes it easier to roll back.

[Fr0sT.Brutal 900]

Best way WRT databases is generated SQL stored at client side or a change log stored in a table. But it could be tricky. Anyway the question is too broad to give concrete answers.

[skyzoframe[hun] 4]

I'm interested in all options because I don't know what the best solution would be.

hm... I stay in the database table.  I will use the unique ID of the database in negative ranges and make a copy of the row there. Before the line changes.

Also, I need a column to store the unique ID of the changed row. (which was in the positive range)
Also, I need a column that contains the user ID. because after logging out I need to delete the rows stored in the negative range.

Maybe I can solve the log with two triggers.

I don't know how it will work.

I will share the final result here.

Edited August 9, 2022 by skyzoframe[hun]

[A.M. Hoornweg 144]

15 hours ago, David Heffernan said:

Very rare to find a scenario where this works well.  The serialised state can be huge.  And the asker here has a database to work with. You are going to serialise that in its entirety?

Certainly not! But if the Delphi object represents a document or record in a database, backing up old versions on disk is often possible.  A colleague of mine has written a reporting tool that does precisely this.

[Uwe Raabe 2057]

3 hours ago, skyzoframe[hun] said:

hm... I stay in the database table.  I will use the unique ID of the database in negative ranges and make a copy of the row there. Before the line changes.

Also, I need a column to store the unique ID of the changed row. (which was in the positive range)
Also, I need a column that contains the user ID. because after logging out I need to delete the rows stored in the negative range.

 

You might be interested in the Delta property of TFDQuery.

[skyzoframe[hun] 4]

The first step is solved, by using the update flag, creating a negative and positive  ID with one trigger when inserting anything into the database. // interbase database // before insert

AS
declare variable COUNT_ID INTEGER;declare variable I INTEGER;/**/

begin
 COUNT_ID=0;
 i=0;
 new.TIME_CREATE=Current_TimeStamp;
 
 
/*
UPDATE_KEY -> default False!
*/ 
if (new.UPDATE_KEY=False) then
begin 
 /*when create*/
 if (not exists( select * from MGR_SZABASZAT where ID>-1)) then
 begin
  new.ID=0;
 end
 else
 begin
  select count(*) from MGR_SZABASZAT where ID>-1 into :COUNT_ID;
  i=:COUNT_ID;
  WHILE (i>0) DO
  begin
   if (not exists (select ID from MGR_SZABASZAT where ID=:i))
   then 
   begin
    new.ID=:i;
    i=0;
   end else i=i-1;
  end /* WHILE (i>0) DO*/
 end /*if (not exists( select * from MGR_SZABASZAT)) then*/
end
else
begin
 /*update*/
  if (not exists( select * from MGR_SZABASZAT where ID<0)) then
 begin
  new.ID=-1;
 end
 else
 begin
  select count(*) from MGR_SZABASZAT where ID<0 into :COUNT_ID;
  i=:COUNT_ID+1;
  i=i*-1;
  WHILE (i<0) DO
  begin
   if (not exists (select ID from MGR_SZABASZAT where ID=:i))
   then 
   begin
    new.ID=:i;
    i=0;
   end else i=i+1;
  end /* WHILE (i<0) DO*/
 end /*if (not exists( select * from MGR_SZABASZAT)) then*/
end
end

 

Edited March 14, 2023 by skyzoframe[hun]

[programmerdelphi2k 237]

I think that in a multiuser environment or even in "multiple read and write concurrency", this would not be a sensible thing to do!!!!
You would need to "lock" the entire database to make sure you actually completed what you wanted to do!

Who could perform the task? What task would be wanted at the end of it all? And, if the first task was desirable, however, another one was carried out at the end of it all?

 

This situation is very complicated!!!

 

I think it's too much of a headache for data engineers to implement an incremental "backup/restore" with the database online, isn't it? Now, imagine deciding which data in a record will be effectively recorded after thousands of requests?

In a more simplistic way, perhaps create a specific table to receive the "logs" (a kind of "historical table: time, recordID, record-fields-values, etc...") of records from "a table" that want to do such a task, it could be a little easier... or not!

 

now imagine that this history-table receives thousands of entries per second... isn't it complicated?

[DelphiUdIT 176]

With respect to your request, and speaking of databases, I've honestly never heard the concepts of UNDO / REDO but the concepts of COMMIT / ROLLBACK.

 

All databases support the concepts of TRANSACTION / COMMIT / ROLLBACK. The main difference between theoretical and practical use is defined by both the DB used and the component used to manage the database (e.g. FIREDAC, ZEOS, ADO).....

 

I've never used Interbase, but I've used FireBird even if not "heavily" and the TRANSACTION support from the DB is complete and it's multi-user with the possibility of setting the LOCK resolution mode.

 

Now I don't remeber if FIREDAC supports transactions at DB level (therefore multiuser, auto lock, ....) or at connection level (1 transaction for each connection, therefore not native multiuser). ZEOS only supports connection-level transactions, therefore 1 ACTIVE TRANSACTION for each connection, no concurrent multi-user.

 

EDIT: my fault, there is no concurrent MULTI TRANSACTION with same client side application. Means that only one sql editing instruction (for example) = one transaction may be in use concurrently in that client side app. MULTIUSER from different client is always possible and even with multiple database connections, multiple concurrent transactions can be made.

 

In any case the solution can be using only the transactions, and this is expressly true if there is multiuser. The resolution of inconsistencies, locks and all other problems is very complex and cannot be solved "manually" at the application code level.

 

This is the page for the FireBird documentation, where the mechanisms of LOCK, TRANSACTION, COMMIT / ROLLBACK are explained very well.

 

Bye

 

P.S.: of course, a careful design of a database is always necessary, regardless of the use of TRANSACTIONs.

Edited March 13, 2023 by DelphiUdIT

[Rollo62 536]

On 8/9/2022 at 9:24 AM, Fr0sT.Brutal said:

Best way WRT databases is generated SQL stored at client side or a change log stored in a table. But it could be tricky. Anyway the question is too broad to give concrete answers.

I would call it a managment of "history" in the data itself, instead of log. The log could be external too and its hard to keep that on track.

What I consider is a history management within each table, where for example copies were made before changes and instead deletion its just setting flags with a timestamp.

That way the latest version in a table keeps always intact and easy to undo the changes, just by manipulating the flags and timestamps.

While you can decide at any point to remove the "garbage" in a table without much side-effects.

 

Of course managing multiple, related tables can always be a headache still, but I think its more clean and easier to understand if tables can handle and encapsule their own, specific undo/redo behaviour,

in contrary to a possible external, universal log management which can be very complex and tricky and failure prone.

Edited March 14, 2023 by Rollo62