Backup · WAL · Operational Checkpoints

The sample's DatabaseOption block also contains operational settings. They aren't manipulated by scripts, but they're the settings most likely to save you in production — worth a chapter of their own.

DB_Sqlite.xmp Operational Options

"DatabaseOption": {
  "Connections": [ /* ... */ ],
  "BackupFolder": "XDatabase/Backup",
  "AutoBackupEnabled": false,
  "AutoBackupIntervalHours": 24,
  "BackupKeepLast": 30,
  "JournalMode": "WAL"
}

Meanings and recommended values, option by option.

1) JournalMode — WAL Recommended

SQLite's journal mode determines how transactional data is recorded.

The sample is set to "WAL". WAL benefits:

• The equipment sequence keeps INSERT-ing while DB Studio or external analysis tools SELECT simultaneously.
• Transaction commits get faster — much better responsiveness for many-short-transaction patterns (like the sample).
• Auto-recovery on abnormal termination.

To change it, just edit the JournalMode key in connections.json / .xmp. You'll see two auxiliary files appear in XDatabase/: LocalDB.db-wal and LocalDB.db-shm.

Backup caveat — copy the WAL/SHM files together too, or pre-checkpoint with PRAGMA wal_checkpoint(TRUNCATE) to flush WAL changes into the main file before copying just .db.

2) BackupFolder + Manual Backup

A SQLite file is a single file, so it can be copied while the system is running — especially after a WAL checkpoint.

The sample sets "XDatabase/Backup" as the backup folder. Files actually appear in that folder when auto backup is on, or when the tool runs a manual backup.

Auto backup files are typically named {db_name}_{YYYYMMDD_HHMMSS}.db — alphabetical sort = chronological sort.

Pattern for OS-level Backup

If you schedule backups at the OS level instead, the recommended flow:

1. PRAGMA wal_checkpoint(TRUNCATE);   // run from SQL tab
2. LocalDB.db   →  copy to external disk/network
3. (optional)   verify file integrity

3) ConnectionTimeout / CommandTimeout / Reconnect

The four timing options inside Connections:

Mostly minor for a single-file SQLite, but they carry over verbatim when you move to MSSQL — they're pre-set in the sample for that reason.

4) Six Operational Checkpoints in the Code

The six defensive patterns shared by every DB function in the sample.

A. IsOpen guard

if( DB["local"].IsOpen == false )
{
   ShowMessage(EB_Ok, "DB is not open. Press [Open] first.");
   return false;
}

B. Selected row guard

if( SelectIndex < 0 || SelectIndex >= DB["local"].RowCount )
{
   ShowMessage(EB_Ok, "Select a row first.");
   return false;
}

C. LastError logging

if( DB["local"].RunSqlQueryParam(sql, p) == false )
{
   LogError($"DB_xxx failed : {DB["local"].LastError}");
   ShowMessage(EB_Ok, $"DB xxx failed : {DB["local"].LastError}");
   return false;
}

D. Rollback on transaction failure

if( DB["local"].RunSqlQueryParam(sql, p) == false )
{
   DB["local"].Rollback();
   // ...
}

E. Sync status label on Open failure

if( DB["local"].Open() == false )
{
   DbStatusText = "● CLOSED";
   return false;
}
DbStatusText = "● OPEN";

F. Refresh after mutation

return DB_Refresh();

After every INSERT / UPDATE / DELETE, refresh the screen so DB state and screen state never drift.

5) Pre-production Checklist

That's everything the DB_Sqlite sample shows about production-grade database integration. To take this into a real project, port the patterns from these 9 chapters and just swap in your domain table.