Kexi/Plugins/Tables/Simple alter table

Design page for task: Simple approach to fulfill "Add support for alter table's design without losing data" wish.

• Started: Jstaniek (talk) August 2012
• Wish #125253
• Target: 2.6.0

Rationale

Approach when modification to table schema is saved directly after user performed it is much easier than full alter table tired in Kexi 1.x and never delivered.

Analysis

• Altering some physical properties would require immediate saving of the design but will not remove all the data.
• Other properties (for which KexiDB::isExtendedTableFieldProperty() return true) can be modified without altering any data.

Alter actions

This section publishes details on particular actions of table altering.

Caption rename

• triggers name change in property editor, so:
  • A: if name changed, ask for accepting name change:
    • if accepted go to Name change section
    • if not accepted, revert the recent name change and go to B
  • B: else, do nothing, caption should be saved on Save action

Name change

• can happen after change of caption (see Caption rename)
• can happen after changed name value in the property editor
• request instant applying
• Algorithm:
  • if name is duplicated, display info and abort
  • ALTER TABLE available: send to the backend
  • ALTER TABLE not available:
    • Create full table copy with all columns but with one column renamed
    • Copy all records to the new table
    • Drop the original table
    • Rename table to the original name

Requirements

• Full data safety
  • It's better to warn and request data removal than claiming it is not necessary and then doing that
  • Enclose operations within transactions

Hints

• Table rename in SQLite: ALTER TABLE RENAME TO...
• Full table copy in SQLite: CREATE TABLE copy AS SELECT * FROM t;

Links

• SQLite3's ALTER TABLE