B4J Tutorial Backup (dump) Sqlite tables via jShell + *.bat file

If you use SQlite in your apps in you need to do backups. A bad decision is just to copy the database file:

- there will be more than one file when the database is online (e.g. *.wal and other files)
- this will create a broken db-file when you copy it when the db is busy doing i/o operations

So the best way is to use a tool to "dump" the complete db or tables inside the db. Sqlite offers a command-line tool called "sqlite3.exe". You can download it from here: https://www.sqlite.org/2018/sqlite-tools-win32-x86-3220000.zip

Create a folder called "dump" under the "objects" folder of you project

Copy the sqlite3.exe file to the dump folder (change the name if you want) and create a new textfile named "dump.bat" with this content:

sqlite3 %1 ".dump '%2'" > %3

In B4J:

Sub BackupTable (DBName As String, TableName As String, BackupFileName As String)
    Dim BackupSh As Shell
    BackupSh.Initialize("BackupTable", File.DirApp &  "\dump\dump.bat", Array As String(DBName,TableName,BackupFileName))
    BackupSh.WorkingDirectory = File.DirApp &  "\dump\"
    BackupSh.Run(-1)
End Sub

Sub BackupTable_ProcessCompleted (Success As Boolean, ExitCode As Int, StdOut As String, StdErr As String)
    If Success And ExitCode = 0 Then
        Log(StdOut)
     Else
        'Do some stuff
    End If
End Sub

What it does:

Via jShell the dump.bat will be called with 3 parameters (dbname, tablename and the name of the backup file). Inside the dump.bat file the parms are handled with placeholders %1, %2 and %3 (= parm 1-3).

As a result you get a backup file like this:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "artikel" (
    `aid`    INTEGER PRIMARY KEY AUTOINCREMENT,
    `Barcode`    TEXT,
    `iid`    TEXT NOT NULL,
    `aname`    TEXT NOT NULL,
    `apreis`    REAL NOT NULL,
    `agrid`    INTEGER NOT NULL,
    `mwstid`    INTEGER NOT NULL,
    FOREIGN KEY(`mwstid`) REFERENCES `mwst`(`mwstid`),
    FOREIGN KEY(`agrid`) REFERENCES `artikelgruppen`(`agrid`)
);
INSERT INTO artikel VALUES(1,NULL,'243','Pizza Salami',5.5,2,1);
INSERT INTO artikel VALUES(2,NULL,'243','Pizza Magherita klein',4.0,2,1);
INSERT INTO artikel VALUES(3,NULL,'243','Pizza Magherita gross',5.5,2,1);
CREATE INDEX `BC_Index` ON `artikel` (
    `Barcode`    ASC
);
COMMIT;

As you can see the structure AND the data will be backed up. If you like you can call 7Zip in a 2nd step to compress the file.

PS: Add some checks like "If File.Exists..." and check the file size to ensure the backup was done correctly. I did not check path's with spaces or other things. Change the code if needed.

 

[Mashiane]

Thank you so much. I was just thinking about this yesterday needing it for my ABM app and perhaps run on a timer for un-attended dumps.

 

[KMatle]

A timer is a good idea. If the db get's huge, think about a redesign. E.g. day based tables with an index table. So you only need to backup the index table and the one "from today" which has changes. I use this concept a very long time to decrease backup times.

 

[LucaMs]

Thanks for sharing.

It is clear that... pizza is everywhere ?

 

[Magma]

Super!

For the record (correct me if i am wrong) as I know if SQLITE3 databased is "cleanly" closed... then it is one file... but if it is on the air - someone working on it... then we need your code !

Thanks again!!!

 

[LucaMs]

Library here.

 

[Magma]

@LucaMs ....cool... i am waiting for the Google Drive Backup version !!!!

 

[LucaMs]

@LucaMs ....cool... i am waiting for the Google Drive Backup version !!!!

Me tooooo ?