Backup incremental SQLite

ebaptistella · 2014-02-17 19:53:12

I'm thinking of using TSQLLog to log the queries executed in the database and have something like an incremental backup of the changes in the base, since SQLite does not provide incremental backup tools.

Use something like for capturing

TSQLLog.Family.Level := [sllSQL];

And with the generated log file using regular expressions, leaving only SQL statements:

SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE '% sqlite_';
INSERT INTO History (TableName, ExportStatus, CreatedAt, LastChange) VALUES (: ('Items'),: (0):,: (135,163,611,293),: (135,163,611,293) :);
select max (CreatedAt) the CreatedAt from History where TableName = 'Items' and Status = 0;
.....

That would be insurance or I'm too stupid? What better way to get a SQLite incremental backup?

You can create another level of log that displays only SQL statements (Delete, Insert and Updates)?

ab · 2014-02-17 20:34:58

I suppose we can do this at SQlite3 level itself also.
There are hooks to retrieve such tracing.

ebaptistella · 2014-02-17 21:03:04

Hi ab!

Do you have any idea how (and when) do this? I saw that SQLite has some features of trace http://www.sqlite.org/c3ref/profile.html
Can we use it?

ab · 2014-02-18 11:04:16

trace/profile are about low-level performance profiling.

I suppose you have more interest in searching in the update_hook() direction.
See https://www.sqlite.org/c3ref/update_hook.html

But there is some possibilities that the trigger is not launched.
See our comments about this function:

    {/ Register Data Change Notification Callbacks
     - The sqlite3.update_hook() interface registers a callback function with
       the database connection identified by the first argument to be invoked
       whenever a row is updated, inserted or deleted.
     - Any callback set by a previous call to this function for the same
       database connection is overridden.
     - sqlite3.update_hook(D,C,P) function returns the P argument from the
       previous call on the same database connection D, or nil for the first
       call on database connection D.
     - The update hook is not invoked when internal system tables are modified
       (i.e. sqlite_master and sqlite_sequence).
     - In the current implementation, the update hook is not invoked when
       duplication rows are deleted because of an ON CONFLICT REPLACE clause.
       Nor is the update hook invoked when rows are deleted using the truncate
       optimization. The exceptions defined in this paragraph might change in
       a future release of SQLite.
     - Note that you should also trace COMMIT and ROLLBACK commands (calling
       sqlite3.commit_hook() and sqlite3.rollback_hook() functions) if you want to
       ensure that the notified update was not canceled by a later Rollback. }
    update_hook: function(DB: TSQLite3DB; xCallback: TSQLUpdateCallback;
      pArg: pointer): pointer; {$ifndef SQLITE3_FASTCALL}cdecl;{$endif}

It could be used to maintain a per-table list of modified ID, able to create a backup feature via incremental updates.

ebaptistella · 2014-02-18 17:09:34

Hi ab!

I've been researching a bit on the sqlite3.update_hook but I do not understand it.
I have the following code:

begin
  sqlite3.update_hook(oServer.DB.DB, Test, nil);

  oClientSrv.DB.Execute('begin transaction');
  try
    oClientSrv.DB.Execute('create table t(a)');

	//Log: line 1
    oClientSrv.DB.Execute('insert into t values(1)');
	//Log: line 2
    oClientSrv.DB.Execute('insert into t values(2)');
	//Log: line 3
    oClientSrv.DB.Execute('update t set a = 3');
	//Log: line 4
    oClientSrv.DB.Execute('delete from t');
	
    oClientSrv.DB.Execute('drop table t');
  finally
    oClientSrv.DB.Execute('rollback');
  end;
end;

That's my hook:

procedure Test(pUpdateArg: Pointer; op: Integer; const zDb, zTbl: PUTF8Char; iRowID: Int64); {$IFNDEF SQLITE3_FASTCALL}cdecl; {$ENDIF}
var
  Operation: String;
begin
  case op of
    sqlite_insert:
      Operation := ' Insert';
    sqlite_delete:
      Operation := ' Delete';
    sqlite_update:
      Operation := ' Update';
  end;
  ShowMessage('OP: ' + IntToStr(op) + ' -> ' + Operation + ' zDb: ' + zDb + ' zTBL: ' + zTbl + ' iRowID: ' + IntToStr(iRowID));
end;

Running my example I have this result:
OP: 18 -> Insert zDb: main zTBL: t iRowID: 1
OP: 18 -> Insert zDb: main zTBL: t iRowID: 2
OP: 23 -> Update zDb: main zTBL: t iRowID: 1
OP: 23 -> Update zDb: main zTBL: t iRowID: 2

But I expected something like:
OP: 18 -> Insert zDb: main zTBL: t iRowID: 1
OP: 18 -> Insert zDb: main zTBL: t iRowID: 2
OP: 23 -> Update zDb: main zTBL: t iRowID: 1
OP: 23 -> Update zDb: main zTBL: t iRowID: 2
OP: 9 -> Delete zDb: main zTBL: t iRowID: ?? (two lines for deleting)

Is there something wrong?
How can I capture the pUpdateArg parameter that is a pointer?

Thanks!

Last edited by ebaptistella (2014-02-18 18:09:39)

ab · 2014-02-18 18:10:39

AFAIK 'update t set a = 3' will update all rows, so RowID=1 and RowID=2.
Does make sense to me.

The doc says: "Nor is the update hook invoked when rows are deleted using the truncate optimization."...
In fact, deleting the whole table content is indeed a "truncate optimization".
See https://www.sqlite.org/lang_delete.html
You can turn off this optimization:

SQLite3 doc wrote:

The truncate optimization can also be disabled at runtime using the sqlite3_set_authorizer() interface. If an authorizer callback returns SQLITE_IGNORE for an SQLITE_DELETE action code, then the DELETE operation will proceed but the truncate optimization will be bypassed and rows will be deleted one by one.

So all this sounds like expected.

ebaptistella · 2014-02-18 19:18:30

Yes, everything you said makes sense now .....

I tested using a query modified and log has been updated
oClientSrv.DB.Execute ('delete from t where 1 = 1');

OP: 18 ->  Insert zDb: main zTBL: t iRowID: 1 --> insert first line
OP: 18 ->  Insert zDb: main zTBL: t iRowID: 2 --> insert second line
OP: 23 ->  Update zDb: main zTBL: t iRowID: 1 --> update first line with ID=1 set to 3
OP: 23 ->  Update zDb: main zTBL: t iRowID: 2 --> update second line with ID=2 set to 3
OP: 9 ->  Delete zDb: main zTBL: t iRowID: 1 --> delete first line
OP: 9 ->  Delete zDb: main zTBL: t iRowID: 2 --> delete second line

Thanks...

Last edited by ebaptistella (2014-02-18 19:21:43)

mORMot Open Source

#1 2014-02-17 19:53:12

Backup incremental SQLite

#2 2014-02-17 20:34:58

Re: Backup incremental SQLite

#3 2014-02-17 21:03:04

Re: Backup incremental SQLite

#4 2014-02-18 11:04:16

Re: Backup incremental SQLite

#5 2014-02-18 17:09:34

Re: Backup incremental SQLite

#6 2014-02-18 18:10:39

Re: Backup incremental SQLite

#7 2014-02-18 19:18:30

Re: Backup incremental SQLite

Board footer