Re: [sqlite] Design of application using embedded database
[EMAIL PROTECTED] wrote: > In all of the desktop apps that I write, I omit the File->Save > option altogether. Incidentally, this is excellent from a usability point of view. It seems rather ludicrous in this day and age that users of other applications have to know the difference between persistent (HD) and transient (RAM) storage and babysit programs moving data between them. Roger - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Design of application using embedded database
If you maintain a log of each transaction, perhaps in SQL or some similar form, then you can create the inverse of that to undo or run it again to redo. Each transaction can have its own registration ID. I should anticipate that you would use some form of circular buffer to make the process self maintaining. If you application is not orthogonal then you will have a lot of difficulty implementing an undo and might review the design accordingly. Ran wrote: What exactly do you mean by "own log of each transaction"? As I explained, I have a complex database with many tables and triggers. It will not be simple to implement undo the way it is explained in the wiki, and I suspect it will cost much in performance (but maybe I am wrong here...). If journals can be used the way I explained, this simplify the undo (because the database should not be changed at all), and also I suspect that the performance is not affected (apart from the fact that we have to store the journals somewhere). I am not sure about the size of the journals, and especially if they could be used at all after the transaction is committed. On 11/19/06, John Stanton <[EMAIL PROTECTED]> wrote: Ran wrote: > I think I didn't explain my question well enough. > I know all what you wrote about transactions. The undo functionality I am > looking for is over _several_ transactions. > > I just wonder if it is possible to twist sqlite to keep the journals > created > during transactions (so to store them just before they are deleted when a > commit is called). > Then, when one wants to undo several transaction (each might include indeed > many inserts/updates/deletes), those kept journals will help him to > "rollback" several times to a former situation. > > So to implement undo by keeping journals. This will give undo functionality > for practically any database (so the database design - the tables, triggers > etc. will not have to be taken into account). I just wonder if it is > possible/reasonable to implement that way undo functionality, and if not - > why not. > > Ran > > On 11/19/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: > >> >> On 11/18/06, Ran <[EMAIL PROTECTED]> wrote: >> > The way the undo-redo is described in the wiki involves triggers to >> insert >> > the information of the change in each table to other table which logs >> the >> > changes. This will have a price in performance. It also complicates >> things >> > when triggers are already used for other things. >> > >> > So I wonder if journals might be used to implement undo: >> > If I understand it correctly, for each transaction there is journal >> that >> > keeps the information so the transaction could be rolled back. If the >> > journals are kept somewhere could they be used to rollback _successful_ >> > transactions? >> >> Transactions let you "undo" whatever you have done since the transaction >> started. You decide if it was "successful" or not and either commit >> the transaction >> to make it permanent or roll it back to undo it. >> >> In the case of large numbers of insertions it's faster to put them >> into a transaction >> and commit them than to do them separately. Performance in most >> installations is >> very quick since the database generally ends up in operating system >> cache. >> I do recall the author of mysql writing "he had no intention of >> implementing >> transaction since it was much slower and proper design eliminated the >> need >> for them." Sqlite has a much lower code overhead than mysql so I >> imagine >> it's just as fast or faster in most cases. >> >> >> - >> >> To unsubscribe, send email to [EMAIL PROTECTED] >> >> - >> Why not maintain your own log of each transaction? Then you can undo and redo without limit. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Design of application using embedded database
Ran wants the transaction journal to be a permanent transaction log, with the ability to explicitly run a rollback operation on it (after the transaction was committed). Unlimited undo support using only the transaction control statements. To answer the question, no, there's no support for that. I do not know how hard it would be to do, but considering the journal is designed to be temporary and database crash recovery depends on that property, it would probably be nontrivial work. Also note that the journal is page-oriented with respect to DB changes, so several transactions in journal form would likely take up more space than a row-oriented log. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Design of application using embedded database
I am not sure what that means. I am looking for undo feature the way it is implemented for example in a drawing application or in a word processor. When the user makes a mistake or change his mind, he can undo several steps and try again. On 11/19/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: On 11/19/06, Ran <[EMAIL PROTECTED]> wrote: > I think I didn't explain my question well enough. > I know all what you wrote about transactions. The undo functionality I am > looking for is over _several_ transactions. Does nested transactions do what you want? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Design of application using embedded database
What exactly do you mean by "own log of each transaction"? As I explained, I have a complex database with many tables and triggers. It will not be simple to implement undo the way it is explained in the wiki, and I suspect it will cost much in performance (but maybe I am wrong here...). If journals can be used the way I explained, this simplify the undo (because the database should not be changed at all), and also I suspect that the performance is not affected (apart from the fact that we have to store the journals somewhere). I am not sure about the size of the journals, and especially if they could be used at all after the transaction is committed. On 11/19/06, John Stanton <[EMAIL PROTECTED]> wrote: Ran wrote: > I think I didn't explain my question well enough. > I know all what you wrote about transactions. The undo functionality I am > looking for is over _several_ transactions. > > I just wonder if it is possible to twist sqlite to keep the journals > created > during transactions (so to store them just before they are deleted when a > commit is called). > Then, when one wants to undo several transaction (each might include indeed > many inserts/updates/deletes), those kept journals will help him to > "rollback" several times to a former situation. > > So to implement undo by keeping journals. This will give undo functionality > for practically any database (so the database design - the tables, triggers > etc. will not have to be taken into account). I just wonder if it is > possible/reasonable to implement that way undo functionality, and if not - > why not. > > Ran > > On 11/19/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: > >> >> On 11/18/06, Ran <[EMAIL PROTECTED]> wrote: >> > The way the undo-redo is described in the wiki involves triggers to >> insert >> > the information of the change in each table to other table which logs >> the >> > changes. This will have a price in performance. It also complicates >> things >> > when triggers are already used for other things. >> > >> > So I wonder if journals might be used to implement undo: >> > If I understand it correctly, for each transaction there is journal >> that >> > keeps the information so the transaction could be rolled back. If the >> > journals are kept somewhere could they be used to rollback _successful_ >> > transactions? >> >> Transactions let you "undo" whatever you have done since the transaction >> started. You decide if it was "successful" or not and either commit >> the transaction >> to make it permanent or roll it back to undo it. >> >> In the case of large numbers of insertions it's faster to put them >> into a transaction >> and commit them than to do them separately. Performance in most >> installations is >> very quick since the database generally ends up in operating system >> cache. >> I do recall the author of mysql writing "he had no intention of >> implementing >> transaction since it was much slower and proper design eliminated the >> need >> for them." Sqlite has a much lower code overhead than mysql so I >> imagine >> it's just as fast or faster in most cases. >> >> >> - >> >> To unsubscribe, send email to [EMAIL PROTECTED] >> >> - >> Why not maintain your own log of each transaction? Then you can undo and redo without limit. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Design of application using embedded database
On 11/19/06, Ran <[EMAIL PROTECTED]> wrote: I think I didn't explain my question well enough. I know all what you wrote about transactions. The undo functionality I am looking for is over _several_ transactions. Does nested transactions do what you want? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Design of application using embedded database
Ran wrote: I think I didn't explain my question well enough. I know all what you wrote about transactions. The undo functionality I am looking for is over _several_ transactions. I just wonder if it is possible to twist sqlite to keep the journals created during transactions (so to store them just before they are deleted when a commit is called). Then, when one wants to undo several transaction (each might include indeed many inserts/updates/deletes), those kept journals will help him to "rollback" several times to a former situation. So to implement undo by keeping journals. This will give undo functionality for practically any database (so the database design - the tables, triggers etc. will not have to be taken into account). I just wonder if it is possible/reasonable to implement that way undo functionality, and if not - why not. Ran On 11/19/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: On 11/18/06, Ran <[EMAIL PROTECTED]> wrote: > The way the undo-redo is described in the wiki involves triggers to insert > the information of the change in each table to other table which logs the > changes. This will have a price in performance. It also complicates things > when triggers are already used for other things. > > So I wonder if journals might be used to implement undo: > If I understand it correctly, for each transaction there is journal that > keeps the information so the transaction could be rolled back. If the > journals are kept somewhere could they be used to rollback _successful_ > transactions? Transactions let you "undo" whatever you have done since the transaction started. You decide if it was "successful" or not and either commit the transaction to make it permanent or roll it back to undo it. In the case of large numbers of insertions it's faster to put them into a transaction and commit them than to do them separately. Performance in most installations is very quick since the database generally ends up in operating system cache. I do recall the author of mysql writing "he had no intention of implementing transaction since it was much slower and proper design eliminated the need for them." Sqlite has a much lower code overhead than mysql so I imagine it's just as fast or faster in most cases. - To unsubscribe, send email to [EMAIL PROTECTED] - Why not maintain your own log of each transaction? Then you can undo and redo without limit. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Design of application using embedded database
I think I didn't explain my question well enough. I know all what you wrote about transactions. The undo functionality I am looking for is over _several_ transactions. I just wonder if it is possible to twist sqlite to keep the journals created during transactions (so to store them just before they are deleted when a commit is called). Then, when one wants to undo several transaction (each might include indeed many inserts/updates/deletes), those kept journals will help him to "rollback" several times to a former situation. So to implement undo by keeping journals. This will give undo functionality for practically any database (so the database design - the tables, triggers etc. will not have to be taken into account). I just wonder if it is possible/reasonable to implement that way undo functionality, and if not - why not. Ran On 11/19/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: On 11/18/06, Ran <[EMAIL PROTECTED]> wrote: > The way the undo-redo is described in the wiki involves triggers to insert > the information of the change in each table to other table which logs the > changes. This will have a price in performance. It also complicates things > when triggers are already used for other things. > > So I wonder if journals might be used to implement undo: > If I understand it correctly, for each transaction there is journal that > keeps the information so the transaction could be rolled back. If the > journals are kept somewhere could they be used to rollback _successful_ > transactions? Transactions let you "undo" whatever you have done since the transaction started. You decide if it was "successful" or not and either commit the transaction to make it permanent or roll it back to undo it. In the case of large numbers of insertions it's faster to put them into a transaction and commit them than to do them separately. Performance in most installations is very quick since the database generally ends up in operating system cache. I do recall the author of mysql writing "he had no intention of implementing transaction since it was much slower and proper design eliminated the need for them." Sqlite has a much lower code overhead than mysql so I imagine it's just as fast or faster in most cases. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Design of application using embedded database
On 11/18/06, Ran <[EMAIL PROTECTED]> wrote: The way the undo-redo is described in the wiki involves triggers to insert the information of the change in each table to other table which logs the changes. This will have a price in performance. It also complicates things when triggers are already used for other things. So I wonder if journals might be used to implement undo: If I understand it correctly, for each transaction there is journal that keeps the information so the transaction could be rolled back. If the journals are kept somewhere could they be used to rollback _successful_ transactions? Transactions let you "undo" whatever you have done since the transaction started. You decide if it was "successful" or not and either commit the transaction to make it permanent or roll it back to undo it. In the case of large numbers of insertions it's faster to put them into a transaction and commit them than to do them separately. Performance in most installations is very quick since the database generally ends up in operating system cache. I do recall the author of mysql writing "he had no intention of implementing transaction since it was much slower and proper design eliminated the need for them." Sqlite has a much lower code overhead than mysql so I imagine it's just as fast or faster in most cases. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Design of application using embedded database
The way the undo-redo is described in the wiki involves triggers to insert the information of the change in each table to other table which logs the changes. This will have a price in performance. It also complicates things when triggers are already used for other things. So I wonder if journals might be used to implement undo: If I understand it correctly, for each transaction there is journal that keeps the information so the transaction could be rolled back. If the journals are kept somewhere could they be used to rollback _successful_ transactions? Ran On 11/17/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: ... When your changes are saved to disk immediately, it is important to have a good undo/redo mechanism. There is some example code on the wiki showing how to implement undo/redo using triggers. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Design of application using embedded database
> > Upon loading a saved file into the application the database on filesystem is > > loaded into an ADO.Net DataSet. This is used by the application until the > user > > saves to disk again when all of the changes to the DataSet are saved back to > > the database on disk. > > > > In all of the desktop apps that I write, I omit the File->Save > option altogether. File->Open opens the SQLite database and > there after all changes are saved to the disk as they are made. If your data has to be used by more than one client suggested method is the simpler one to guarantee for cooperative editing but - if not - I'd suggest a third way: - upon load you copy your file to temp dir and open it - you write data directly to temp file - you remember temp file name so that if application crashes you can suggest for recovery - upon file close or application queryunload you ask user whether to discard changes or not. - of course you can still implement undo/redo tecniques my 2 cents, Claudio - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Design of application using embedded database
[EMAIL PROTECTED] wrote: > [EMAIL PROTECTED] wrote: > > Hi, > > I would like a bit of advice before starting to make changes to my > > application. > > > > I've written a program in C# for personnel departments and at present all of > > the data is stored in memory until the user saves and then it is written to > > disk as an XML file. > > > > I'd like to switch to an embedded SQLite database where the database becomes > > the file format for the application. I plan to use the ADO.Net wrapper for > > SQLite. > > > > Can anybody tell me if the following is a reasonable design for implementing > > this- > > > > Upon loading a saved file into the application the database on filesystem is > > loaded into an ADO.Net DataSet. This is used by the application until the > user > > saves to disk again when all of the changes to the DataSet are saved back to > > the database on disk. > > > > In all of the desktop apps that I write, I omit the File->Save > option altogether. File->Open opens the SQLite database and > there after all changes are saved to the disk as they are made. > > I find this works much better since you avoid losing all your > work when the power goes off unexpectedly - or when you power > down at the end of the day and forget to close out the file > you were working on. Would that OpenOffice did the same... > > Another advange is that if you open the same file twice in two > separate windows, the second one opened still sees all the > latest changes. And you cannot trash work by doing File->Save > in the wrong order. > > When your changes are saved to disk immediately, it is important > to have a good undo/redo mechanism. There is some example code > on the wiki showing how to implement undo/redo using triggers. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > Thanks for the speedy reply. It seems like a good way of doing things and will certainly make it a lot simpler to implement which I'm all in favour of. Cheers, Jon Leadbeater - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Design of application using embedded database
[EMAIL PROTECTED] wrote: > Hi, > I would like a bit of advice before starting to make changes to my > application. > > I've written a program in C# for personnel departments and at present all of > the data is stored in memory until the user saves and then it is written to > disk as an XML file. > > I'd like to switch to an embedded SQLite database where the database becomes > the file format for the application. I plan to use the ADO.Net wrapper for > SQLite. > > Can anybody tell me if the following is a reasonable design for implementing > this- > > Upon loading a saved file into the application the database on filesystem is > loaded into an ADO.Net DataSet. This is used by the application until the user > saves to disk again when all of the changes to the DataSet are saved back to > the database on disk. > In all of the desktop apps that I write, I omit the File->Save option altogether. File->Open opens the SQLite database and there after all changes are saved to the disk as they are made. I find this works much better since you avoid losing all your work when the power goes off unexpectedly - or when you power down at the end of the day and forget to close out the file you were working on. Would that OpenOffice did the same... Another advange is that if you open the same file twice in two separate windows, the second one opened still sees all the latest changes. And you cannot trash work by doing File->Save in the wrong order. When your changes are saved to disk immediately, it is important to have a good undo/redo mechanism. There is some example code on the wiki showing how to implement undo/redo using triggers. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
