print dbUpdate.php

dbUpdate.php is the command line interface to the database update and backup system. This tool provides a means of keeping databases up-to-date in a scripted manner and for creating backups of the databases.

Updates:

The basic premise is that, the first deployed database schema(s) are exported and kept un-changed as a known starting point. From then on any changes (except for natural record growth) are logged to a specific class for that database. These changes could be the addition of indexes, tables, sorting of data, deleting items adding new class mappings etc.

This class then becomes a log of all operations that need to be applied to this base initialisation SQL script to bring it up to the current production version. As the intent is for this to be used in production, the update classes should be thoroughly tested in first development and then a staging environment.

Database updates can be either SQL statements or for more involved processes, methods within the class. When creating SQL it is imperative that the database is always specified when referencing tables - especially if the default database is not the database being operated on. The same applies if using multiple schemas within the same databases (e.g. Postgres).

dbUpdate has several modes of operation. The first is purely informational: to display the current version of the database and the number of updates that need to be applied. The second is a simulation of the update process. During this no changes are made to the database but all SQL queries are prepared which will highlight any obvious SQL errors and the functions to be executed will be listed. The final mode is actually executing the updates, which requires an explicit commit command as well as update.

Commit intrinsically calls status before and after the run.

dbUpdate logs all actions and their outcomes and will display the last update result. Should an error be encountered all updates cease and the script terminates the job for that database, logging what went wrong and any other information.

Backups:

The backup mechanism depends on the database adaptor. For example: SQLite database files are backed up by simply copying them to another folder, whereas MySQL databases use the CLI tool mysqldump. Other databases will need the appropriate adaptor extension to accommodate them.

The tool allows for file copying, as previously mentioned, compression via one of several CLI tools: 7zip (7za), Gzip and Zip - in that order. Compression can be run as a background task, though this is not recommended for large database backups. Further: there are limitations with both Gzip and Zip and their ability to handle large files so before relying on this utility, it should be thoroughly tested in your environment with your dataset.

By default all backups are stored in the data folder under backup/db/ and the backup file is chmod() to owner read-only.

As with other CLI tools, help is available by using dbUpdate.php help.

<  1  >