Kexi/Plugins/Tables/Backups

Design page for task: "Add support for table backups".

Started: Jstaniek September 2012
Wish #306270
Target: 2.7.0

Rationale

Support for table backups would be a simple but useful feature for many reasons. Before doing data edit user may decide create backup of relevant table(s). It may be faster and lighter than backing up entire database.

Other uses

Another case can be user editing schema for table that already contains data.

I propose to also backup table automatically before performing schema alteration using the new more powerful Alter Table feature. We'll have to test the feature for some extended time, so backing up by default is good. Moreover tables are not that big considering performance of the current hardware.

Details

Advantages. Explicit backup can be considered as more systematic approach to plain tables copying:

backups are listed within context of given table, so unlike the plain copies they do not clutter the object list
user do not have to 'invent' a name for copied table, and backup description is optional; presentation of backup data is often enough for the user to identify backups

Integration with the Kexi GUI. Proposed backup should be available within Tools, as a Backup Data button. There should be also Show Backups button to show existing backups for current object (if it's a table), enabling retrieving table from backup.

Physical layer. Backups shall be indexed by backup date and optional textual description. kexi__objectdata can store this metadata. ~~Table backups can be created by deep-copying original table to a different name, preferable is kexi__**** so the backup is hidden even in previous Kexi versions.~~ Table backups can be created by deep-copying original table to a different name, preferable is unique name kexi__backup_{originalName}{timestamp}. To hide the table even in previous Kexi versions, kexi__object.o_type should be of extra BACKUP number, not =1. (TODO: verify how this behaves in Kexi < 2.6)

Restoring from backup should (?) first backup the existing version of table, and then it should be physically replaced.

Further work

Tables with cascade triggers. Relationships (foreign keys with cascade triggers) have to be removed between copied table and related tables. This is needed because table backup is a kind of snapshot that should not be altered over time. This implies that ideally backups of tables joined with cascade triggers should be made. This is advanced feature that can be added. When one-to-many relationship exists (MASTER->DETAILS) and only the DETAILS is backed up, subsequent removal or update of records in MASTER will leave unreferenced values in the backed DETAILS table.
Backing up other types of objects, including forms and reports
Backing up entire database
Private backups (i.e. owned by users). Metadata for these backups can be kept in kexi__userdata introduced in Kexi 2.6. Other physical properties can be kept unchanged. The "Backups list" view for a table can show indication which backup is private and which is public. Users can easily make a backup public or private (physically this is only a matter of altering metadata).
- For backends with access control (eg. mysql, pgsql, not sqlite3), copied tables can be made private by altering permissions.
Ability of restoring backed up table under different name.
Ability of checking which version within the backup list is equal to the current table. Because changes to table contents can happen in the meantime, this can be only performed by deep comparison.
Advanced "diff" tool for tables would be a good supplement for the Backup feature, and can be integrated as an option in the Backups view.
Ability of keeping backups in other database, it can be existing database or created specifically (perhaps automatically) for backups.