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

• status: TODO
• 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

• status: TODO
• can happen after:
  • change of caption (see Caption rename)
  • or name value changes 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 fields but with one field renamed
    • Copy all records to the new table
    • Drop the original table
    • Rename table to the original name
    • Update table metadata
• Notes:
  • tables that reference to the renamed field via relationships may need updates
  • queries that reference to the renamed field may need updates

Field removing

• status: TODO
• triggered by "Delete Record" action, available in context menu
• request instant applying
• Algorithm:
  • ALTER TABLE available: send to the backend
  • ALTER TABLE not available:
    • Create full table copy with all fields but the one fields removed
    • Copy all records to the new table
    • Drop the original table
    • Rename table to the original name
  • Update table metadata
• Notes:
  • tables that reference to the renamed field via relationships may need updates
  • queries that reference to the renamed field may need updates

Fields reordering

• status: TODO
• triggered after accepting "Reorder Fields" dialog
• request instant applying
• Algorithm:
  • ALTER TABLE available: send to the backend
  • ALTER TABLE not available:
    • Create full table copy with all fields in destination order
    • Copy all records to the new table with new order
    • Drop the original table
    • Rename table to the original name
  • Update table metadata
• Notes:
  • queries that use "ORDER by <number>" clause will have to be updated

Field inserting

• status: TODO
• triggered after inserting new field's definition into empty row anywhere at 0..N position
• Algorithm:
  • ALTER TABLE available: send to the backend
  • ALTER TABLE available for appending field (e.g. SQLite), and the new field should be appended:
    • send use respective ALTER TABLE command
  • ALTER TABLE not available:
    • Create full table copy with all fields plus one new field
    • Copy all records to the new table with new specified order
    • Drop the original table
    • Rename table to the original name
  • Update table metadata
• Notes:
  • queries that already use name that has been added may need updates

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