Re: [sqlite] Saving and loading SQLite pages from a buffer

[Kees Nuyt]

On Wed, 4 Feb 2009 17:55:38 -0500, "D. Richard Hipp"
 wrote in General Discussion of SQLite
Database :

>The hot backup API has not yet been released.  Look for version 3.6.11.
>
>If you open your database using the special name ":memory:" then it is  
>held entirely in memory with no duplication of pages (accept as  
>required to support ROLLBACK).  Such a :memory: database is initially  
>empty, of course.  So then initialize it  from a file using the backup  
>API.

Very nice to see it will also be available in the command
line tool:

sqlite> .help
.backup ?DB? FILE   Backup DB (default "main") to FILE

.restore ?DB? FILE  Restore content of DB
   (default "main") from FILE

http://www.sqlite.org/draft/sqlite.html

>D. Richard Hipp
>d...@hwaci.com
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Saving and loading SQLite pages from a buffer

[Dan]

On Feb 7, 2009, at 4:56 PM, Alexey Pechnikov wrote:

> Hello!
>
> В сообщении от Thursday 05 February 2009 02:48:25 D. Richard Hipp  
> написал(а):
>> The SQLite core will call the locking methods, but they can be no-
>> ops.  The read method can be a no-op too, I suppose, if you are only
>> using the VFS to make a backup.   Oh, and the writes will all be  
>> page-
>> sized and page-aligned.
>
> Can I use online baclup feature to restore disk-based database with  
> 4k page instead of 1k default
> page (and vice versa)? I did see in online backup code commentary  
> about only 1k pages...

Yes. The backup code should work with all page sizes. There is some  
example
code up on the draft website now. This is meant to complement the  
existing
API documentation.

   http://www.sqlite.org/draft/backup.html

Please post feedback and bug-fixes etc.

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

Re: [sqlite] Saving and loading SQLite pages from a buffer

[Alexey Pechnikov]

Hello!

В сообщении от Thursday 05 February 2009 02:48:25 D. Richard Hipp написал(а):
> The SQLite core will call the locking methods, but they can be no-
> ops.  The read method can be a no-op too, I suppose, if you are only  
> using the VFS to make a backup.   Oh, and the writes will all be page-
> sized and page-aligned.

Can I use online baclup feature to restore disk-based database with 4k page 
instead of 1k default 
page (and vice versa)? I did see in online backup code commentary about only 1k 
pages...

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

Re: [sqlite] Saving and loading SQLite pages from a buffer

[D. Richard Hipp]

On Feb 4, 2009, at 6:38 PM, Brown, Daniel wrote:

> Hi Richard,
>
> Thanks for the advice: I will take a look into a barebones VFS
> implementation for backup purposes.  I assume it won't need the usual
> locking functionality that normal VFS implementations require: pretty
> much just read, write, open and close?

The SQLite core will call the locking methods, but they can be no- 
ops.  The read method can be a no-op too, I suppose, if you are only  
using the VFS to make a backup.   Oh, and the writes will all be page- 
sized and page-aligned.

>
> What is the ETA for 3.6.11?
>

http://www.sqlite.org/draft/releaselog/3_6_11.html



D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Saving and loading SQLite pages from a buffer

[Brown, Daniel]

Hi Richard,

Thanks for the advice: I will take a look into a barebones VFS
implementation for backup purposes.  I assume it won't need the usual
locking functionality that normal VFS implementations require: pretty
much just read, write, open and close?

What is the ETA for 3.6.11?

Daniel

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of D. Richard Hipp
> Sent: Wednesday, February 04, 2009 3:19 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Saving and loading SQLite pages from a buffer
> 
> 
> On Feb 4, 2009, at 6:09 PM, Brown, Daniel wrote:
> >
> > I currently use SQLite by opening ":memory:" and then copying the
> > tables
> > I want from read only storage into ":memory:" via an attached read
> > only
> > database, which I drop after copying is complete.  My issue is
saving
> > and loading the ":memory:" database once I've used it and changed
its
> > contents after this initial creation.  I cannot write the backup
> > back to
> > the file system as it is read only but I can potentially save and
> load
> > it via an API that saves and loads blocks of memory to a persistent
> > memory location on my platform.
> >
> 
> So, create a VFS that instead of writing to disk, writes to the
> persistent memory location on your platform.  That backup to your
> VFS.  If the only operation your VFS supports is a backup, it can be
> very simply - omitting much of the functionality found in a general-
> purpose VFS.
> 
> 
> D. Richard Hipp
> d...@hwaci.com
> 
> 
> 
> ___
> 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] Saving and loading SQLite pages from a buffer

[D. Richard Hipp]

On Feb 4, 2009, at 6:09 PM, Brown, Daniel wrote:
>
> I currently use SQLite by opening ":memory:" and then copying the  
> tables
> I want from read only storage into ":memory:" via an attached read  
> only
> database, which I drop after copying is complete.  My issue is saving
> and loading the ":memory:" database once I've used it and changed its
> contents after this initial creation.  I cannot write the backup  
> back to
> the file system as it is read only but I can potentially save and load
> it via an API that saves and loads blocks of memory to a persistent
> memory location on my platform.
>

So, create a VFS that instead of writing to disk, writes to the  
persistent memory location on your platform.  That backup to your  
VFS.  If the only operation your VFS supports is a backup, it can be  
very simply - omitting much of the functionality found in a general- 
purpose VFS.


D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Saving and loading SQLite pages from a buffer

[Brown, Daniel]

-Original Message-
>The hot backup API has not yet been released.  Look for version 3.6.11.

I will look forward to seeing that release!  Is there anything I can do
to help with the development?

>If you open your database using the special name ":memory:" then it is

>held entirely in memory with no duplication of pages (accept as  
>required to support ROLLBACK).  Such a :memory: database is initially  
>empty, of course.  So then initialize it from a file using the backup  
>API.

I currently use SQLite by opening ":memory:" and then copying the tables
I want from read only storage into ":memory:" via an attached read only
database, which I drop after copying is complete.  My issue is saving
and loading the ":memory:" database once I've used it and changed its
contents after this initial creation.  I cannot write the backup back to
the file system as it is read only but I can potentially save and load
it via an API that saves and loads blocks of memory to a persistent
memory location on my platform.

I guess I could implement an in memory VFS to use with the existing
backup API but that seems more complicated than extending the backup API
to accept save or load from a memory buffer.  As I said I'd be happy to
contribute some time to extending the back system to support this.

Daniel


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

Re: [sqlite] Saving and loading SQLite pages from a buffer

[Eric Minbiole]

> That depends.  If the change was made using the same database  
> connection that was passed into sqlite3_backup_init(), then only those  
> pages that changed are recopied.  However, if an independent database  
> connection made the change, then the backup process has no way of  
> knowing exactly which pages changed, so it has to start over again  
> from the beginning.

That makes sense, thank you.  (I wasn't sure if the individual pages had 
a "Change Counter", similar to the one in the File Header, that could be 
used.)

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

Re: [sqlite] Saving and loading SQLite pages from a buffer

[D. Richard Hipp]

On Feb 4, 2009, at 5:48 PM, Nicolas Williams wrote:

> On Wed, Feb 04, 2009 at 05:19:13PM -0500, D. Richard Hipp wrote:
>> http://www.sqlite.org/draft/c3ref/backup_finish.html
>
> Does this work at the page level?  Or can one have pre-created the
> destination DB with a different page size than the source DB?
>

It does a binary copy - so you cannot change the page size on the  
fly.  If you want a backup with a different page size, first make the  
backup, then change the page size on the backup using:

 PRAGMA page_size=NEWSIZE;
 VACUUM;


D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Saving and loading SQLite pages from a buffer

[D. Richard Hipp]

On Feb 4, 2009, at 5:51 PM, Eric Minbiole wrote:
>
> "If the source database is modified [...] then the backup will be
> transparently restarted by the next call to sqlite3_backup_step()"
>
> Does this mean that the backup is restarted *to the beginning*, or  
> is it
> able to re-copy only those pages which have just been modified?


That depends.  If the change was made using the same database  
connection that was passed into sqlite3_backup_init(), then only those  
pages that changed are recopied.  However, if an independent database  
connection made the change, then the backup process has no way of  
knowing exactly which pages changed, so it has to start over again  
from the beginning.


D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Saving and loading SQLite pages from a buffer

[Nicolas Williams]

On Wed, Feb 04, 2009 at 05:19:13PM -0500, D. Richard Hipp wrote:
> http://www.sqlite.org/draft/c3ref/backup_finish.html

Does this work at the page level?  Or can one have pre-created the
destination DB with a different page size than the source DB?

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

Re: [sqlite] Saving and loading SQLite pages from a buffer

[D. Richard Hipp]

On Feb 4, 2009, at 5:48 PM, Brown, Daniel wrote:
>
> What version(s) of SQLite contain this experimental functionality?   
> I am
> using version 3.6.10 non-amalgamation but I can't find the functions
> mentioned on that page.  If I understand this backup API correctly  
> then
> its functionality is pretty close to what I need.   I would need the
> option for either the source or destination to be a buffer in memory  
> or
> a database connection.
>

The hot backup API has not yet been released.  Look for version 3.6.11.

If you open your database using the special name ":memory:" then it is  
held entirely in memory with no duplication of pages (accept as  
required to support ROLLBACK).  Such a :memory: database is initially  
empty, of course.  So then initialize it  from a file using the backup  
API.

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Saving and loading SQLite pages from a buffer

[Eric Minbiole]

  > http://www.sqlite.org/draft/c3ref/backup_finish.html

This is excellent!  I've been looking for a clean way to perform live 
backups.  This (draft) API looks perfect.

I have one clarification question about source database changes made 
during the backup.  The documentation states:

"If the source database is modified [...] then the backup will be 
transparently restarted by the next call to sqlite3_backup_step()"

Does this mean that the backup is restarted *to the beginning*, or is it 
able to re-copy only those pages which have just been modified?

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

Re: [sqlite] Saving and loading SQLite pages from a buffer

[Brown, Daniel]

Thanks for the link Richard, very interesting! 

What version(s) of SQLite contain this experimental functionality?  I am
using version 3.6.10 non-amalgamation but I can't find the functions
mentioned on that page.  If I understand this backup API correctly then
its functionality is pretty close to what I need.   I would need the
option for either the source or destination to be a buffer in memory or
a database connection.

Daniel

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp
Sent: Wednesday, February 04, 2009 2:19 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Saving and loading SQLite pages from a buffer


On Feb 4, 2009, at 5:12 PM, Brown, Daniel wrote:

> Good Afternoon List,
>
> I've been looking at how best to save/load SQLite database that is  
> 100%
> in memory to and from a memory buffer instead of a file via a VFS
> operating system wrapper.  I had initially thought that implementing a
> Virtual File System (VFS) was a solution but then I realised that  
> would
> double my memory usage.  As I would be holding the pages internally in
> the VFS and SQLite would also be holding duplicates internally in its
> page system, as well as another partial set of duplicates in the  
> SQLite
> page cache.
>
> After thinking about this for a bit, a more efficient implementation
> could be to save or load the database pages held in SQLite's internal
> page system.  This would effectively involve two new functions being
> added to the SQLite API: one to seed the page system with pages
> contained in a supplied buffer (load) and another to copy pages out of
> the page system and into a buffer (save).
>
> I've had a look through the O'Reilly eBook "Inside SQLite" and through
> the SQLite source code and I think this solution is possible.  I would
> like to know if this approach seems a reasonable solution or not?  I
> would also appreciate any advice about safely modifying the page  
> system
> that anyone would be able to share.
>

http://www.sqlite.org/draft/c3ref/backup_finish.html

D. Richard Hipp
d...@hwaci.com



___
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] Saving and loading SQLite pages from a buffer

[D. Richard Hipp]

On Feb 4, 2009, at 5:12 PM, Brown, Daniel wrote:

> Good Afternoon List,
>
> I've been looking at how best to save/load SQLite database that is  
> 100%
> in memory to and from a memory buffer instead of a file via a VFS
> operating system wrapper.  I had initially thought that implementing a
> Virtual File System (VFS) was a solution but then I realised that  
> would
> double my memory usage.  As I would be holding the pages internally in
> the VFS and SQLite would also be holding duplicates internally in its
> page system, as well as another partial set of duplicates in the  
> SQLite
> page cache.
>
> After thinking about this for a bit, a more efficient implementation
> could be to save or load the database pages held in SQLite's internal
> page system.  This would effectively involve two new functions being
> added to the SQLite API: one to seed the page system with pages
> contained in a supplied buffer (load) and another to copy pages out of
> the page system and into a buffer (save).
>
> I've had a look through the O'Reilly eBook "Inside SQLite" and through
> the SQLite source code and I think this solution is possible.  I would
> like to know if this approach seems a reasonable solution or not?  I
> would also appreciate any advice about safely modifying the page  
> system
> that anyone would be able to share.
>

http://www.sqlite.org/draft/c3ref/backup_finish.html

D. Richard Hipp
d...@hwaci.com



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