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:
In B4J:
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:
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.
- 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:
B4X:
sqlite3 %1 ".dump '%2'" > %3In B4J:
B4X:
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 SubWhat 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:
B4X:
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.
