mORMot Open Source

AOG

Member

Registered: 2014-02-24

Posts: 490

SQL backup of SQLite3 database

@Ab

I am trying to make a general SQL backup of a sqlite3 database created by the nice mORMot.
And with the use of mORMot !

The only way getting this to work is to execute a SQL like this:

SELECT ''INSERT INTO Dues (ID,Modified,Created) VALUES ('' || ID || '''','''' || Modified || '''','''' || Created || '');'' FROM Dues

I get back as first row:

INSERT INTO Dues (ID,Modified,Created) VALUES (' || ID || ''","'' || Modified || ''","'' || Created || ');

I get back as data:

INSERT INTO Dues (ID,Modified,Created) VALUES (1",135268576423,"135268576423);

Problem: extra double quotes in the reply!

This is the plain SQL that can be executed by SQLite3 with the help of an external tool like sqlitebrowser:

SELECT 'INSERT INTO Dues (ID,Modified,Created) VALUES (''' || ID || ''',''' || Modified || ''',''' || Created || ''');' FROM Dues 

This returns (by sqlitebrowser):

INSERT INTO Dues (ID, Modified, Created) VALUES ('1','135268576423','135268576423');

The mORMot does some extra processing on the SQL before executing it.
Double single quotes are (sometimes) replaced by single double quotes.

I do not know how to formulate the SQL to get the results in the right format from the table

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,666

Website

Re: SQL backup of SQLite3 database

How are you executing the SQL?

Why not use the background backup functions already available within SynSQLite3.pas?

Why use SQLite3 to make the string processing?
It would be IMHO much better to do it on the Delphi side, using e.g. TTextWriter.

AOG

Member

Registered: 2014-02-24

Posts: 490

Re: SQL backup of SQLite3 database

Sorry for this: I did not give enough info !

The database is an external MariaDB database. No possibilities to run a dedicated (mORMot REST) server as frontend.

On the client side, I have a TSQLRestServerDB with virtual tables, pointing to the external database. And a simple (TSQLRestClientDB) client in the same executable.

I need to make local backups of this external database, both for safety and for offline functionality.

Therefor, I have a second TSQLRestServerDB inside this exe, with same model, that uses RecordVersionSynchronizeSlave to get a local copy of the external database.

I am using this local copy to try to export pure SQL as a means of backup into some other database, if needed.
And I am using FPC latest.

I would prefer to use the background backup functions available in mORMot, but, at my place, the do not work with external databases (virtual tables).

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,666

Website

Re: SQL backup of SQLite3 database

Running such a complex SELECT statement with an external DB would involve the SQLite3 virtual table mechanism.
IMHO it is an uneeded overhead.

Just run a plain SELECT * FROM ... statement, which would by-pass the virtual tables, then process the "INSERT" text generation in Delphi code.

Last but not least, do not use SQL, but ORM methods.
See http://synopse.info/files/html/Synopse% … #TITLE_110

AOG

Member

Registered: 2014-02-24

Posts: 490

Re: SQL backup of SQLite3 database

I will do as advised ! Thanks.

Last question:
How do I know when the virtual tables are bypassed ?
Often, I have some very complex SQL spanning different tables.
If I use the normal client, execution can take up to 10 seconds (on virtual tables).
If I use the Props (TSQLDBConnectionProperties), execution is within 0.1 sec.

So, if you say bypass, do you mean using the Props ?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,666

Website

Re: SQL backup of SQLite3 database

Virtual tables have an overhead, which may be huge, since they tend to make a full scan (i.e. retrieve all information) before doing a JOIN.

The TSQLRestStorageExternal.AdaptSQLForEngineList() method, as defined in mORMotDB.pas, will return false if virtual tables are used, or true if Props could be directly executed - using modified/corrected SQL.

So for a complex SQL query, using directly Props is highly recommended.
Idea is to create a dedicated SOA service for such complex queries.