Kexi/Plugins/Tables/Simple alter table

Design page for task: "Add support for alter table's design without losing data".

Started: Jstaniek August 2012
Wish #125253
Target: 2.7.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

Field type change

Byte Field type change

status: TODO
To Byte - N/A
To ShortInteger
To Integer
To BigInteger
To Boolean
To Date
To DateTime
To Time
To Float
To Double
To Text
To LongText
To BLOB

ShortInteger Field type change

status: TODO
To Byte
To ShortInteger - N/A
To Integer
To BigInteger
To Boolean
To Date
To DateTime
To Time
To Float
To Double
To Text
To LongText
To BLOB

Integer Field type change

status: TODO
To Byte
To ShortInteger
To Integer - N/A
To BigInteger
To Boolean
To Date
To DateTime
To Time
To Float
To Double
To Text
To LongText
To BLOB

BigInteger Field type change

status: TODO
To Byte
To ShortInteger
To Integer
To BigInteger - N/A
To Boolean
To Date
To DateTime
To Time
To Float
To Double
To Text
To LongText
To BLOB

Boolean type change

status: TODO
To Byte
To ShortInteger
To Integer
To BigInteger
To Boolean - N/A
To Date
To DateTime
To Time
To Float
To Double
To Text
To LongText
To BLOB

Date type change

status: TODO
To Byte
To ShortInteger
To Integer
To BigInteger
To Boolean
To Date - N/A
To DateTime
To Time
To Float
To Double
To Text
To LongText
To BLOB

DateTime type change

status: TODO
To Byte
To ShortInteger
To Integer
To BigInteger
To Boolean
To Date
To DateTime - N/A
To Time
To Float
To Double
To Text
To LongText
To BLOB

Time type change

status: TODO
To Byte
To ShortInteger
To Integer
To BigInteger
To Boolean
To Date
To DateTime
To Time - N/A
To Float
To Double
To Text
To LongText
To BLOB

Float type change

status: TODO
To Byte
To ShortInteger
To Integer
To BigInteger
To Boolean
To Date
To DateTime
To Time
To Float - N/A
To Double
To Text
To LongText
To BLOB

Double type change

status: TODO
To Byte
To ShortInteger
To Integer
To BigInteger
To Boolean
To Date
To DateTime
To Time
To Float
To Double - N/A
To Text
To LongText
To BLOB

Text type change

status: TODO
To Byte
To ShortInteger
To Integer
To BigInteger
To Boolean
To Date
To DateTime
To Time
To Float
To Double
To Text - N/A
To LongText
To BLOB

LongText type change

status: TODO
To Byte
To ShortInteger
To Integer
To BigInteger
To Boolean
To Date
To DateTime
To Time
To Float
To Double
To Text
To LongText - N/A
To BLOB

BLOB type change

status: TODO
To Byte
To ShortInteger
To Integer
To BigInteger
To Boolean
To Date
To DateTime
To Time
To Float
To Double
To Text
To LongText
To BLOB - N/A

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
Altering requirement should depends on type of connection/driver. For example, MySQL has built-in ALTER TABLE while SQLite almost completely lacks it.

   //! Defines flags for possible altering requirements; can be combined.
   enum AlteringRequirements {
       /*! Physical table altering is required; e.g. ALTER TABLE ADD COLUMN. */
       PhysicalAlteringRequired = 1,

       /*! Data conversion is required; e.g. converting integer
        values to string after changing column type from integer to text. */
       DataConversionRequired = 2,

       /*! Changes to the main table schema (in kexi__fields) required,
        this does not require physical changes for the table;
        e.g. changing value of the "caption" or "description" property. */
       MainSchemaAlteringRequired = 4,

       /*! Only changes to extended table schema required,
        this does not require physical changes for the table;
        e.g. changing value of the "visibleDecimalPlaces" property
        or any of the custom properties. */
       ExtendedSchemaAlteringRequired = 8,

       /*! Convenience flag, changes to the main or extended schema is required. */
       SchemaAlteringRequired = ExtendedSchemaAlteringRequired | MainSchemaAlteringRequired
   };

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