Re: [sqlite] Committing to a savepoint
Max, I understand your point of view. But you assume that any insert ends up with call to xWrite in the VFS (so that VFS could track the changes). But it's not that way in SQLite. xWrite is called only during commit and it's provided with full database pages, not individual changes. Pavel On Fri, Jan 14, 2011 at 1:31 PM, Max Vlasov wrote: > On Fri, Jan 14, 2011 at 8:55 PM, Pavel Ivanov wrote: > >> > There are some challenges, for example to allow arbitrary undo >> > operations we should also log transaction boundaries since undoing to >> some >> > points in between not only makes little sense, but also dangerous. But I >> > think if implemented with those challenges solved, such implementation >> would >> > help to implement you "commit to save point'. >> >> Also you will have to re-write SQLite to make it distinguish between >> different statements inside transaction, not cache changes from >> different statements to the same database page in memory and transfer >> all changed database pages to VFS after each statement, not in the end >> of transaction... Sounds like a huge change... >> >> >> > Pavel, you seem to see this on a higher level, I simply thought about an > implementation not knowing about sql at all. Imagine your base in different > times, what's the difference between them regardless of the types of > changes? It's just slightly different data at different positions. For > restoring any previous state one will need only full log of writes (offset, > length and data). Everying that is going to be overwritten can be tracked > inside xWrite. Restoring to a point in the past is just writing this log > backwards till the correct state (out of a transaction). One of the solution > to logging transaction boundaries is to track header changes (the only place > to know sqlite file format), i.e. there's a known last actions about writing > some fields when the transaction ends. When xWrite filter detects this > special write, it writes EndOfTransaction record to the log. The following > undo operations limits restoring only to those states when this special > record is reached. > > Max Vlasov > ___ > 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] Committing to a savepoint
On Fri, Jan 14, 2011 at 8:55 PM, Pavel Ivanov wrote: > > There are some challenges, for example to allow arbitrary undo > > operations we should also log transaction boundaries since undoing to > some > > points in between not only makes little sense, but also dangerous. But I > > think if implemented with those challenges solved, such implementation > would > > help to implement you "commit to save point'. > > Also you will have to re-write SQLite to make it distinguish between > different statements inside transaction, not cache changes from > different statements to the same database page in memory and transfer > all changed database pages to VFS after each statement, not in the end > of transaction... Sounds like a huge change... > > > Pavel, you seem to see this on a higher level, I simply thought about an implementation not knowing about sql at all. Imagine your base in different times, what's the difference between them regardless of the types of changes? It's just slightly different data at different positions. For restoring any previous state one will need only full log of writes (offset, length and data). Everying that is going to be overwritten can be tracked inside xWrite. Restoring to a point in the past is just writing this log backwards till the correct state (out of a transaction). One of the solution to logging transaction boundaries is to track header changes (the only place to know sqlite file format), i.e. there's a known last actions about writing some fields when the transaction ends. When xWrite filter detects this special write, it writes EndOfTransaction record to the log. The following undo operations limits restoring only to those states when this special record is reached. Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Committing to a savepoint
> There are some challenges, for example to allow arbitrary undo > operations we should also log transaction boundaries since undoing to some > points in between not only makes little sense, but also dangerous. But I > think if implemented with those challenges solved, such implementation would > help to implement you "commit to save point'. Also you will have to re-write SQLite to make it distinguish between different statements inside transaction, not cache changes from different statements to the same database page in memory and transfer all changed database pages to VFS after each statement, not in the end of transaction... Sounds like a huge change... Pavel On Fri, Jan 14, 2011 at 4:25 AM, Max Vlasov wrote: > On Fri, Jan 14, 2011 at 2:16 AM, Charles Samuels wrote: > >> >> Here's more or less what I need: >> >> A * sqlite gets some inserts >> B * we're at a checkpoint, so everything after this point shouldn't get >> committed now. So "savepoint SP" >> C * insert some more into sqlite >> D * The checkpoint is ready to go, so we do "commit to savepoint SP" >> E * now, on-disk, the sqlite db contains everything in step A, but nothing >> in >> step C >> >> > If your design allows, you can move everything in C into one transaction > that either committed or rolled back depending on your condition in the > following steps. I assume you already considered this solution or I did not > get this right so possibly it's not an option. > > Some more complex solution. Recently I thought about implementing full or > partial undo with sqlite vfs. You probably know that sqlite works with the > file via virtual file system. If we forget about simulating access and > locking sqlite basically asks the vfs to read something at some offset and > write something at some offset. So if we take some file format that supports > multiply streams (microsoft compound for example), we can dedicate one > stream to sqlite native stream and another - for undo writings that is > basically just data blocks from the native stream before writing applied to > the corresponding file ranges. With unlimited space this should work as > unlimited undo, but even with limited space this will allow several steps > undo. There are some challenges, for example to allow arbitrary undo > operations we should also log transaction boundaries since undoing to some > points in between not only makes little sense, but also dangerous. But I > think if implemented with those challenges solved, such implementation would > help to implement you "commit to save point'. > > Max Vlasov > ___ > 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] Committing to a savepoint
On Fri, Jan 14, 2011 at 2:16 AM, Charles Samuels wrote: > > Here's more or less what I need: > > A * sqlite gets some inserts > B * we're at a checkpoint, so everything after this point shouldn't get > committed now. So "savepoint SP" > C * insert some more into sqlite > D * The checkpoint is ready to go, so we do "commit to savepoint SP" > E * now, on-disk, the sqlite db contains everything in step A, but nothing > in > step C > > If your design allows, you can move everything in C into one transaction that either committed or rolled back depending on your condition in the following steps. I assume you already considered this solution or I did not get this right so possibly it's not an option. Some more complex solution. Recently I thought about implementing full or partial undo with sqlite vfs. You probably know that sqlite works with the file via virtual file system. If we forget about simulating access and locking sqlite basically asks the vfs to read something at some offset and write something at some offset. So if we take some file format that supports multiply streams (microsoft compound for example), we can dedicate one stream to sqlite native stream and another - for undo writings that is basically just data blocks from the native stream before writing applied to the corresponding file ranges. With unlimited space this should work as unlimited undo, but even with limited space this will allow several steps undo. There are some challenges, for example to allow arbitrary undo operations we should also log transaction boundaries since undoing to some points in between not only makes little sense, but also dangerous. But I think if implemented with those challenges solved, such implementation would help to implement you "commit to save point'. Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Committing to a savepoint
> Is there a way to do this today? How hard would it be to add "commit to > savepoint" if not? It's impossible to do the thing you want in SQLite. And it won't be feasible to add that. What you really want is for database engine to allow to have two parallel writing transactions and for it to not lock the whole database in those transactions but do some fine-grained locking instead. In this case you will be able to do A in one transaction (over one connection) and C in another transaction (over another connection). Then when you are ready you commit first transaction leaving second uncommitted. You should use some other RDBMS for that, SQLite is not suitable for such task. If you insist on using SQLite you should cache yourself all changes in C somewhere outside the database. Then when you commit everything done in A you can apply all cached changes... Pavel On Thu, Jan 13, 2011 at 6:16 PM, Charles Samuels wrote: > > Hi, > > I have a program that uses sqlite to do bookkeeping for another set of data. I > do a commit on sqlite once I'm certain the other set of data is done. While > I'm waiting for the other set of data to process, I might make other changes > to my Sqlite database. However, I don't want to commit the stuff made after I > started to process the other set of data. > > In short, I want a "commit to savepoint savepoint-name" which commits up until > a savepoint begins, but not anything after. > > Here's more or less what I need: > > A * sqlite gets some inserts > B * we're at a checkpoint, so everything after this point shouldn't get > committed now. So "savepoint SP" > C * insert some more into sqlite > D * The checkpoint is ready to go, so we do "commit to savepoint SP" > E * now, on-disk, the sqlite db contains everything in step A, but nothing in > step C > > In this example, doing the commit at D is the same as doing in between A and > B. > > Is there a way to do this today? How hard would it be to add "commit to > savepoint" if not? > > Thanks, > > Charles > > ps: I think the documentation on savepoint is a little bit unclear on if > "rollback to savepoint" and "release savepoint" keep the savepoint in question > as the active savepoint. > ___ > 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] Committing to a savepoint
On Thursday, January 13, 2011 3:23:55 pm Simon Slavin wrote: > Look at savepoints: > > http://www.sqlite.org/lang_savepoint.html Yes, I have been, and I use them quite a bit. However, they don't appear to meet my needs, which is why I asked my question. Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Committing to a savepoint
On 13 Jan 2011, at 11:16pm, Charles Samuels wrote: > In short, I want a "commit to savepoint savepoint-name" which commits up > until > a savepoint begins, but not anything after. Look at savepoints: http://www.sqlite.org/lang_savepoint.html Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
