Re: [sqlite] Realtime Backup of Database
Just in case anybody else is interessted. I created triggers for my
database that look like:
SELECT CASE
WHEN (SELECT redundance('') !=0)
THEN RAISE (ABORT, 'backup on redudant database was not successfull')
END;
My custom function redundance then executes the statements on the
redundant database (by sending the statements over a socket to the
second device) When it failes it returns something not equal 0 so the
statement has no effect.
I think that is fine for my purpose.
So last question. Is it difficult to deploy my custom function
(redundance()) with the shared library so that it is accessable from
all applications including the PHP scripts?
Is there a tutorial for building the librarys with custom functions?
I bought the SQLite Guide Book, but found nothing yet.
Thanks for your support so far!
Till
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Realtime backup of database
On Tue, 5 Aug 2008 18:57:10 +0200, you wrote: >Hi everyone, >i need to backup a sqlite database on a remote device for >configuration redundancy purposes. Due to a really slow connection >between the devices triggering rsync is not the best solution yet. >Although rsync is efficient it is transfering kilobytes of data to see >what it has to update. Because the updates are always one-way (from >the live database to the backup database) it must be sufficient only >to transfer the updates. >I don't get the right idea for my application. Something like >capturing the querys that update the database and transmit them to the >remote system would fit the purpose, but that seems to me very >complicated to teach the database. I would add a "last update date time stamp" column to every table you want to synchronize and maintain it using a trigger (on update and on insert). Then SELECT the rows which were updated since the last synchronisation, in a format proper for import at the other side. Alternatively, at every synchronisation, .dump your database, and run a diff on the .dump result and the previous version of the .dump output. Transfer the diff and use it to patch the other side (also .dump format). After the patch, create a new database with the patched .dump file. Initially you transfer a full dump to build your initial target database. Update the status on the source machine after a successful update of the target. You need quite some smart scripting. Not easy, but probably feasible. Both methods only work if your schema is stable. >I'm stuck with my problem so perhaps here is someone with a really >clever idea. > >Sorry for my bad english, No problem, it's clear. >greetings from hamburg, germany >Till Greetings from Rotterdam, Netherlands. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Realtime backup of database
The new VFS implementation is probably the way to go. If you wrote some wrapper code around the default VFS, you could capture all the writes that go to the main db and clone/wirexfer those writes to a 2nd sync file. Just an idea -- haven't worked with VFS's yet, though I plan on implementing them in the ADO.NET provider. SQLite may be one of the very few database engines that can work on Microsoft's "Isolated Storage" mechanism fairly soon! If it works as well as I think, I could probably implement several different VFS implementations in the provider for doing sync stuff, SQLite over streams, etc. Robert -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Till Steinbach Sent: Tuesday, August 05, 2008 9:57 AM To: sqlite-users@sqlite.org Subject: [sqlite] Realtime backup of database Hi everyone, i need to backup a sqlite database on a remote device for configuration redundancy purposes. Due to a really slow connection between the devices triggering rsync is not the best solution yet. Although rsync is efficient it is transfering kilobytes of data to see what it has to update. Because the updates are always one-way (from the live database to the backup database) it must be sufficient only to transfer the updates. I don't get the right idea for my application. Something like capturing the querys that update the database and transmit them to the remote system would fit the purpose, but that seems to me very complicated to teach the database. I'm stuck with my problem so perhaps here is someone with a really clever idea. Sorry for my bad english, greetings from hamburg, germany Till ___ 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] Realtime Backup of Database
Hi Stephen, I have a slightly different requirement. Because the second device is the hot-standby part of my device hosting the database, I must be sure that the change was successfull. Everything else would lead to confusion when there is a failover and some data is not yet changed. I'm just looking into Ingos code (thanks for that!) i have had no idea how to log these statements. Now I'm far closer to the solution. To be conform with my requirements i thought of changing the triggers. My idea is to write a custom function (with the extension api) that takes the statement as argument. That function does the transfer to my redundant database on the second device and returns with success or error. When there is an error due to a lost link between the devices. I can do a RAISE() to show the user-programm that the setting could not be saved. Do you think that is a straight solution for my problem? greetings Till > The way I accomplished this is by giving each setting a "dirty" flag. > When the setting is changed for any reason, the "dirty" flag is set. > When the device reports in (so I know that it is still working), it > checks for any "dirty" settings and includes them in the report. When > the server they report to receives and stores those settings, it sends > back a response indicating such. Upon receiving that response, the > device clears the "dirty" flag for all settings. > > This scenario works fine so long as it is impossible for a setting to > be changed while the device is reporting in. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Realtime Backup of Database
On Tue, Aug 5, 2008 at 4:36 PM, Till Steinbach <[EMAIL PROTECTED]> wrote: > Hi Ingo! > Although I'm limited to C-Code your code would be really useful for > me. The triggers will be the same for me. When I have the right > triggers the most difficult part is done. The idea with the seperate > table for logging is great. I have no idea yet how to log whole > statements. I'm looking forward to see your code. > > greetings Till Side note: I once looked into using rsync to reduce remote firmware update times for an embedded device over a slow link, and I found just what you were finding -- rsync's overhead is HUGE unless you're dealing with tens or hundreds of megabytes. That said, these devices can also be configured remotely, and they can also request a refresh of all their configuration settings in the event of a problem. The way I accomplished this is by giving each setting a "dirty" flag. When the setting is changed for any reason, the "dirty" flag is set. When the device reports in (so I know that it is still working), it checks for any "dirty" settings and includes them in the report. When the server they report to receives and stores those settings, it sends back a response indicating such. Upon receiving that response, the device clears the "dirty" flag for all settings. This scenario works fine so long as it is impossible for a setting to be changed while the device is reporting in. This is possible for my devices, but it may not be for yours. If that is the case, then a more sophisticated solution will do the job: First, create a table called "generation": create table generation ( id int AUTOINCREMENT not null primary key, -- the autoincrement is kind of important here date date not null default(current_timestamp), reported int not null ) Then, when a configuration row (or other row that needs to be tracked) is to be inserted/changed, do the following steps: 1. Get the max(id) from generation where reported=0. 2. If that's null, insert a new row into generation with reported=0 and get the new row ID 3. Insert/update the relevant row, including generationId= When the device needs to report in: 1. If the 'generation' table is empty, there is nothing to do. Stop now. 2. Select the maximum generation ID from the 'generation' table. We will call this generation G. 3. Mark every generation with ID <= G.ID as reported. 4. Report in, including all rows with generation.Id <= G.ID 5. If the server confirms receipt of the data, delete all rows from generation where generation.Id <= G.ID That *should* make sure that no row gets missed, but I'd feel better if somebody else could sanity check and confirm. -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Realtime Backup of Database
Hi Ingo! Although I'm limited to C-Code your code would be really useful for me. The triggers will be the same for me. When I have the right triggers the most difficult part is done. The idea with the seperate table for logging is great. I have no idea yet how to log whole statements. I'm looking forward to see your code. greetings Till > > I've written a small C# app to add the triggers needed for statement > logging to all or a selected subset of the database tables. > I can send you the source if it is useful for you. > > Ingo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Realtime Backup of Database
Hello! I think you need to add callback function for insert operations and are logging in the function. Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Realtime Backup of Database
Till Steinbach wrote: > I don't get the right idea for my application. Something like > capturing the querys that update the database and transmit them to the > remote system would fit the purpose, but that seems to me very > complicated to teach the database. > You could write triggers for the insert, update and delete events, which write SQL statements for the events into a separate table. Then write a small app or a thread in your main app to read this table periodically and send the statements to your backup database where they are executed by a third app. Report success or failure back to the master db and delete the succesfully executed statements from the logging table. I've written a small C# app to add the triggers needed for statement logging to all or a selected subset of the database tables. I can send you the source if it is useful for you. Ingo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
