🔨Maintenance

Going back to this snippet of the configuration file:

maintenance:
  schedule: "0 0 * * *"
  atStartup: false
  doVacuum: true
  doBackup: true
  backupTemplate: ~/first_%s.db
  numFiles: 3
  statements:
    - DELETE FROM myTable WHERE tstamp < CURRENT_TIMESTAMP - 3600
    - ... 

The maintenance node represent the structure that tells ws4sqlite to provide scheduled maintenance. This maintenance can be:

• scheduled, with a cron-like syntax; and/or

• performed at startup.

The maintenance itself can be comprised of one or more of:

• a VACUUM, to optimize and cleanup the internal structures;

• a backup, rotated as needed;

• a set of SQL statements, for example to cleanup temporary tables.

The last feature in particular is very powerful, in that it allows to perform statements at startup, or repeatedly; if for example you need to generate a sort of "run id" for one particular run, the relevant SQL can be executed at each startup of the server.

We'll now discuss the configurations.

schedule

Line 2; string; mandatory this or atStartup as true

Cron-like string, standard 5-fields (no seconds). See documentation for more details.

It's always better to put double quotes (") around the chron expression, as * is a special character for YAML.

atStartup

Line 3; boolean; mandatory this as true or schedule

If true, performs a maintenance routine at engine startup.

It is possible that ws4sqlite is started two times in the same minute; in this case, the backup file won't be overwritten.

doVacuum

Line 4; boolean

If present and set to true, performs a VACUUM on the database.

doBackup

Line 5; boolean

If present and set to true, takes a snapshot of the database.

If doVacuum is also true, the backup is taken after the VACUUM.

The backup is created with the VACUUM INTO... command.

The following parameters tell ws4sqlite how to manage the backup(s).

backupTemplate

Line 6; string; mandatory if doBackup is TRUE

Filename (with path) of the backup files. It must contain a single %s that will be replaced with the minute of the backup, in yyyyMMdd-HHmm format. For example:

../myFile_%s.db will be generated as ../myFile_20220127-1330.db

~ is expanded to the user's home directory path.

If the same pattern is specified for different databases (it doesn't make much sense, but still) it is possible that ws4sqlite generates the same backup file more than once. In this case, the first backup file "wins" and won't be overwritten. The order of operations is deterministic but unspecified; so it's much better to avoid the situation.

numFiles

Line 7; number; mandatory if doBackup is TRUE

Indicates how many files to keep for each database. After the limit is reached, the files rotate, with the least recent files being deleted.

statements

Lines 8-10; list of strings

A set of SQL statements (without parameters) to execute.

The statements are not run in a transaction: if one fails, the next one will be executed, as with initStatements. On the other hand, there is a mutex that ensures that the statements' block is not executed concurrently to a request.