Re: [sqlite] 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  wrote:
> 
>> On 13 Mar 2019, at 2:31pm, John Smith  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:
> 
> 
> 
> " 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 13 Mar 2019, at 2:31pm, John Smith  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:



" 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users