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
- A: if name changed, ask for accepting name change:
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;