Is it possible to use sqlite online-backup system in an ongoing manner?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Is it possible to use sqlite online-backup system in an ongoing manner?

John Smith
I am working with IN-MEMORY database.
When my program starts I load data from file-system DB into my IN-MEMORY DB.
All other SQL operations are performed directly on my IN-MEMORY database.
This is in order to keep performance high.

However, I have a requirement that my original file-system database will remain updated with the program modifications every few seconds.
My idea to implement this was to have a worker-thread that will work as follows:

void WorkerThread()
{
    // Initialize SQLite online-backup ONCE:
    p = sqlite3_backup_init(...);

    loop{
        Sleep(5 seconds);

         // Save only intermediate changes (?)
         sqlite3_backup_step(p, -1); // Backup all modifications from last time

    }  while( program is running);

    // No program is exiting...
    // Release object resources
    sqlite3_backup_finish(p);
}

The problem is that I see that first time around all data is saved, but all follwing calls to 'sqlite3_backup_step()' do not save anything.

My question:
Is there a way to use this online-backup system in an incremental way: that it will save only difference from last time BUT ALL the difference from last time?

Many thanks, John
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to use sqlite online-backup system in an ongoing manner?

Simon Slavin-3
On 13 Mar 2019, at 2:31pm, John Smith <[hidden email]> wrote:

> I am working with IN-MEMORY database.
> When my program starts I load data from file-system DB into my IN-MEMORY DB.
> All other SQL operations are performed directly on my IN-MEMORY database.
> This is in order to keep performance high.

First, make sure you really need to do this.  SQLite performance is normally very high, even without taking special measures.  You may be wasting programming time and introducing complexity which will be difficult to debug.  Run some time-trials.  Of course, you may have already run some time-trials.

>         // Save only intermediate changes (?)
>         sqlite3_backup_step(p, -1); // Backup all modifications from last time

You cannot combine these two things.  The Online Backup API backs up an entire database.  It does it page by page, without understanding individual rows of data.  It cannot select only changes.  So you might want to use it, but if you do you'll create a new copy of the entire database every time.

You might want to instead use the Resumable Bulk Update extension:

<https://sqlite.org/rbu.html>

" An RBU Update is a bulk update of a database file that may include many insert, update and delete operations on one or more tables. "

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to use sqlite online-backup system in an ongoing manner?

Barry Smith
I think the sessions extension can be used for what you want to do. I haven't used it myself, but from it's documented behaviour it looks like you could record change sets for every three second interval then apply them back to your database on disk. If your app is multi-threaded it might be a pain (unless there's an atomic 'stop this changeset and immediately start another one'

> On 13 Mar 2019, at 8:40 am, Simon Slavin <[hidden email]> wrote:
>
>> On 13 Mar 2019, at 2:31pm, John Smith <[hidden email]> wrote:
>>
>> I am working with IN-MEMORY database.
>> When my program starts I load data from file-system DB into my IN-MEMORY DB.
>> All other SQL operations are performed directly on my IN-MEMORY database.
>> This is in order to keep performance high.
>
> First, make sure you really need to do this.  SQLite performance is normally very high, even without taking special measures.  You may be wasting programming time and introducing complexity which will be difficult to debug.  Run some time-trials.  Of course, you may have already run some time-trials.
>
>>        // Save only intermediate changes (?)
>>        sqlite3_backup_step(p, -1); // Backup all modifications from last time
>
> You cannot combine these two things.  The Online Backup API backs up an entire database.  It does it page by page, without understanding individual rows of data.  It cannot select only changes.  So you might want to use it, but if you do you'll create a new copy of the entire database every time.
>
> You might want to instead use the Resumable Bulk Update extension:
>
> <https://sqlite.org/rbu.html>
>
> " An RBU Update is a bulk update of a database file that may include many insert, update and delete operations on one or more tables. "
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users