Re: [sqlite] Once again about file change counter

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

> Or did you mean something else?

What I meant was that you leave the main database using the normal SQLite
VFS.  Then you implement a custom VFS whose intent is to be used as the
target for backups, and source for restores.  ie the backup file is a
separate file than the database file.

Since backups are made under app control they can be done at logical
points for the user, and possibly even annotated as to why they were made.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk9XHR0ACgkQmOOfHg372QRU3ACgppC5pULlwoloeNfttW+tyNvG
ueoAoLbpbkZD5SAtLLCfB/nUW5xXpZUg
=Zmf8
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Once again about file change counter

[Max Vlasov]

On Tue, Mar 6, 2012 at 9:55 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 06/03/12 01:56, Max Vlasov wrote:
> > I don't see a problem here.
>
> ... For example the user could use the app for 3 hours and perform what
> they
> consider to be 10 actions over that time, but behind the scenes that could
> be 300 transactions.  Offering 300 "restore" points would be very
> confusing to them.
>

Good point, I think that on the user side the time is right for this task
and choosing the start of the day or the end of the day could be a next
setting if necessary.


>
> Incidentally you could make a VFS intended for backups.  It could checksum
> pages being written and only save one copy per checksum.  Consequently
> every time you backup to it only changed pages would be stored/added to
> the backup file.
>

I planned it as compact implementation, even compress the history data. I
thinkg as a side effect this system would allow restoring the exact copy of
the db for particular date. Or did you mean something else?

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Once again about file change counter

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/03/12 01:56, Max Vlasov wrote:
> I don't see a problem here.

Apologies for not being clear enough.  What I meant is that there is a
user using the app and the app in turn is using the database.  When the
user does actions that can result in one or more transactions.  For
example the user could use the app for 3 hours and perform what they
consider to be 10 actions over that time, but behind the scenes that could
be 300 transactions.  Offering 300 "restore" points would be very
confusing to them.

Incidentally you could make a VFS intended for backups.  It could checksum
pages being written and only save one copy per checksum.  Consequently
every time you backup to it only changed pages would be stored/added to
the backup file.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk9WT4EACgkQmOOfHg372QTzNgCg3CdrhnNLg8OjrYn2Q8OZocQD
QBMAnjmN4ImuV7ZdT+M4dNYmyh6ymRFG
=anXt
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Once again about file change counter

[Max Vlasov]

On Tue, Mar 6, 2012 at 4:00 PM, Dan Kennedy  wrote:

> On 03/06/2012 06:45 PM, Max Vlasov wrote:
>
>>
>>>
>> It's an interesting suggestion, thanks. I just thought that using xSync I
>> even can only implement my own db change counter. In any case the question
>> is will it be called for rollback?
>>
>
>
> If this is a big deal, you probably could filter these states out
> by keeping an eye on the change-counter.
>
>
Ok, I already have plenty of information for field testing :)

Thanks
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Once again about file change counter

[Dan Kennedy]

On 03/06/2012 06:45 PM, Max Vlasov wrote:

On Tue, Mar 6, 2012 at 2:37 PM, Dan Kennedy  wrote:


On 03/06/2012 02:41 PM, Max Vlasov wrote:


 But for correct work this vfs should rely on the
fact that file change counter will stay the same until the final write to
the first sector.




Do you just want some hook called when a transaction is committed
and the file is completely updated? That might be the xSync() on
the db file.



It's an interesting suggestion, thanks. I just thought that using xSync I
even can only implement my own db change counter. In any case the question
is will it be called for rollback?


It can. If a large transaction (one that won't fit in the pager
cache) begins modifying the database before it is committed, then
is rolled back, the database file will be synced after the rollback
is finished. So if you stored a snapshot at that point it would be
a duplicate of the previous snapshot.

If this is a big deal, you probably could filter these states out
by keeping an eye on the change-counter.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Once again about file change counter

[Max Vlasov]

On Tue, Mar 6, 2012 at 2:37 PM, Dan Kennedy  wrote:

> On 03/06/2012 02:41 PM, Max Vlasov wrote:
>
>>  But for correct work this vfs should rely on the
>> fact that file change counter will stay the same until the final write to
>> the first sector.
>>
>
>
> Do you just want some hook called when a transaction is committed
> and the file is completely updated? That might be the xSync() on
> the db file.
>
>
It's an interesting suggestion, thanks. I just thought that using xSync I
even can only implement my own db change counter. In any case the question
is will it be called for rollback?

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Once again about file change counter

[Dan Kennedy]

On 03/06/2012 02:41 PM, Max Vlasov wrote:

Hi,

there are some ideas for vfs implementation that would require tracking
file change counter. As I recall, I asked about specific api for working
with file change counter, and seems like there was no such. On the other
side it looks like it's not a problem since probably until db format change
significantly the location of this value in the file will stay unchanged.

One of the recent thought I had was implementing time machine vfs. So on
the developer level he opens the db either in general mode or history
(read-only) mode providing the date (or version) for working with the db as
it was in the past. Technically implementing such vfs should not be a
problem. For any file write (sector write) we save the prior contents of
the location together with offset-size and version information (file change
counter is good for this). The history mode instead of reading the main
data, uses the saved history data and searches for best fit to get
requested sector contents. But for correct work this vfs should rely on the
fact that file change counter will stay the same until the final write to
the first sector.


In rollback mode (not journal_mode=wal), if the locking mode is normal
(not locking_mode=exclusive), the file-change counter will be updated
as each transaction is committed. But it is not the last thing written
to the file.

Do you just want some hook called when a transaction is committed
and the file is completely updated? That might be the xSync() on
the db file.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Once again about file change counter

[Max Vlasov]

On Tue, Mar 6, 2012 at 12:22 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 05/03/12 23:41, Max Vlasov wrote:
> > One of the recent thought I had was implementing time machine vfs. So
> > on the developer level he opens the db either in general mode or
> > history (read-only) mode providing the date (or version) for working
> > with the db as it was in the past.
>
> I don't see why a VFS has to be used.  Surely the backup API is a far
> better solution for this?
>
> Only the app knows where the logical boundaries are during database change
> and use.  This could be as simple as one transaction, or it could be a
> group of transactions. You would want those history points to be well
> chosen.
>
>
Roger, I don't see a problem here. Any vfs can be used only by
applications/developer knowing what for this vfs was created. So if this
vfs advertise itself as allowing any "out of transaction" temporary
restoration then any developer that sees that his internal logic is
compatible with it will be able to use it.

As for real world examples, I admit they can be very specific, but
generally it's like adding time domain without changing the schema. Imagine
some note-taking application. Virtually the developer will only need to add
"Time-machine feature (requires more space)" in the "new..." dialog and if
a file of such kind is opened, enable a menu item "Go to the date
(read-only)..." with only dozens of additional lines of code on his side.

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Once again about file change counter

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/03/12 23:41, Max Vlasov wrote:
> One of the recent thought I had was implementing time machine vfs. So
> on the developer level he opens the db either in general mode or
> history (read-only) mode providing the date (or version) for working
> with the db as it was in the past.

I don't see why a VFS has to be used.  Surely the backup API is a far
better solution for this?

Only the app knows where the logical boundaries are during database change
and use.  This could be as simple as one transaction, or it could be a
group of transactions. You would want those history points to be well chosen.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk9VyTUACgkQmOOfHg372QS5rACg5W75WhMahjLRGEYA5o1HCubT
v0YAoORedNP1OPQPQWeP7fL7paTPGI3o
=jXRA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Once again about file change counter

[Max Vlasov]

Hi,

there are some ideas for vfs implementation that would require tracking
file change counter. As I recall, I asked about specific api for working
with file change counter, and seems like there was no such. On the other
side it looks like it's not a problem since probably until db format change
significantly the location of this value in the file will stay unchanged.

One of the recent thought I had was implementing time machine vfs. So on
the developer level he opens the db either in general mode or history
(read-only) mode providing the date (or version) for working with the db as
it was in the past. Technically implementing such vfs should not be a
problem. For any file write (sector write) we save the prior contents of
the location together with offset-size and version information (file change
counter is good for this). The history mode instead of reading the main
data, uses the saved history data and searches for best fit to get
requested sector contents. But for correct work this vfs should rely on the
fact that file change counter will stay the same until the final write to
the first sector. And the bad thing here is that I should read this value
manually so some conflict with existing sqlite cache is possible. So can
vfs in this case work in sync with sqlite or it's too complicated?

Thanks,

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users