🔨Scheduled Tasks

Since v0.14; this is an evolution of the former maintenance configuration, allowing for multiple schedulings. The old form is deprecated but still supported for retrocompatibility; a warning will be displayed when using it, and an error if both forms are used.

Going back to this snippet of the configuration file:

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

The scheduledTasks node represent the structure that tells ws4sqlite to provide execute tasks in a scheduled fashion. This can be useful for maintenance, for example; each task can be:

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

• performed at startup.

The task itself can be comprised of one or more actions, i.e.:

• 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.

If multiple actions are configured for a task, they are executed in the following order: first the VACUUM, then the backup, then the sql statements (in the order they're listed).

It's a list of objects. We'll now discuss the configurations of each node of the list.

schedule

Line 2; string; it's mandatory to set either 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, 10; boolean; it's mandatory to set either this (as true) or schedule

If present and set to true, performs the task at engine startup.

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, performs a backup of the database according to the scheduling and the configurations.

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

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

Depending on the configurations, it is possible that ws4sqlite creates more than one backup in the same minute; in this case, the policy id not to overwrite an existing file.

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.

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.