Re: [sqlite] structure question
nci...@aquarelo.com> wrote: > Thanks Dannis, > > The problem is a little bigger. I must have 2 instances of same table: > original and latest. Then my problem is what is the best way to: > - transform 'original' with same data as 'latest'. This is 'save'. > - transform 'latest' with same data as 'original'. This is 'undo'. > > I must always have 2 tables. Client will read from original and > Administrator will make changes. Then, if Administrator wants to apply > those changes to Client a copy from 'latest' to 'original' must happen. > > Francisco A > Francisco, you probably want to find some easy solution and I understand that. But what about just adding some extra logic inside your own code. What is undo feature, it's just information about what should be done in order to reverse changes. So you could create your own log table inside your sqlite base logging your changes.It works if all the changes is controlled by you, so before any DELETE, INSERT, UPDATE you can prepare this information and write it into your log table. In case you want to apply undo, you read this information and make all necessary undo operations. Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] structure question
Thanks Dannis, The problem is a little bigger. I must have 2 instances of same table: original and latest. Then my problem is what is the best way to: - transform 'original' with same data as 'latest'. This is 'save'. - transform 'latest' with same data as 'original'. This is 'undo'. I must always have 2 tables. Client will read from original and Administrator will make changes. Then, if Administrator wants to apply those changes to Client a copy from 'latest' to 'original' must happen. Francisco A > On 10-02-26 2:25 PM, Francisco Azevedo wrote: >> Hi all, >> >> I want to create a "publish/undo system" for some tables but i don't >> know what is the best approach to do it. >> Imagine i have a table with columns id (auto-inc), data (text) then i >> want to edit table data (eg: create 2 new rows now, delete one tomorrow, >> update 3 rows tomorow too) and then decide if i want to preserve that >> modification or reverse it to the state it was before start that >> modifications. >> >> > Hi Francisco, > > You should read this page in the wiki > http://www.sqlite.org/cvstrac/wiki?p=UndoRedo. It explains how to do > this sort of undo system using triggers. > > HTH > Dennis Cote > ___ > 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] structure question
On 10-02-26 2:25 PM, Francisco Azevedo wrote: > Hi all, > > I want to create a "publish/undo system" for some tables but i don't > know what is the best approach to do it. > Imagine i have a table with columns id (auto-inc), data (text) then i > want to edit table data (eg: create 2 new rows now, delete one tomorrow, > update 3 rows tomorow too) and then decide if i want to preserve that > modification or reverse it to the state it was before start that > modifications. > > Hi Francisco, You should read this page in the wiki http://www.sqlite.org/cvstrac/wiki?p=UndoRedo. It explains how to do this sort of undo system using triggers. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] structure question
Hi all, I want to create a "publish/undo system" for some tables but i don't know what is the best approach to do it. Imagine i have a table with columns id (auto-inc), data (text) then i want to edit table data (eg: create 2 new rows now, delete one tomorrow, update 3 rows tomorow too) and then decide if i want to preserve that modification or reverse it to the state it was before start that modifications. How can i do this? I started with a auxiliar table with same columns, then, before edition i clean auxiliar data and copy from original: delete from _table; insert into _table select * from table; now, user can edit table (inserts, deletes, updates) and then, if we want to apply changes i copy back: delete from table; insert into table select * from _table; if we don't want to preserve, i don't need to do anything because on new edit, auxiliar table will be cleaned again. This works but it's not efficient at all (imagine table with 1.000.000 records). My question is, what is the best approach to do such thing? best regards and thanks for your time, Francisco A ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] structure question
On 26 Feb 2010, at 9:25pm, Francisco Azevedo wrote: > I started with a auxiliar table with same columns, > then, before edition i clean auxiliar data and copy from original: > > delete from _table; insert into _table select * from table; > > now, user can edit table (inserts, deletes, updates) and then, if we > want to apply changes i copy back: > > delete from table; insert into table select * from _table; > > if we don't want to preserve, i don't need to do anything because on new > edit, auxiliar table will be cleaned again. > > This works but it's not efficient at all (imagine table with 1.000.000 > records). My question is, what is the best approach to do such thing? You only have that solution and one other: remember the table as it was at a certain point (call it a backup), and every command given since then. If you want to undo a command, restore the backup version of the table then replay all the commands up to that one you want to undo. The 'certain point' mentioned above, of course, might be the original creation of the table: back to the time when it had no rows. Those two systems are really your only options which will correctly cope with any kind of SQL commands. If your application has reduced possibilities, and you can be sure that only certain types of SQL commands will be given, then you may have other options. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] structure question
Hi all, I want to create a "publish/undo system" for some tables but i don't know what is the best approach to do it. Imagine i have a table with columns id (auto-inc), data (text) then i want to edit table data (eg: create 2 new rows now, delete one tomorrow, update 3 rows tomorow too) and then decide if i want to preserve that modification or reverse it to the state it was before start that modifications. How can i do this? I started with a auxiliar table with same columns, then, before edition i clean auxiliar data and copy from original: delete from _table; insert into _table select * from table; now, user can edit table (inserts, deletes, updates) and then, if we want to apply changes i copy back: delete from table; insert into table select * from _table; if we don't want to preserve, i don't need to do anything because on new edit, auxiliar table will be cleaned again. This works but it's not efficient at all (imagine table with 1.000.000 records). My question is, what is the best approach to do such thing? best regards and thanks for your time, Francisco A ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
