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 tried 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
• unit testing: 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
• unit testing: 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
• unit testing: 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
• unit testing: 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
• unit testing: 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
• unit testing: 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
• unit testing: 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
• unit testing: 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
• unit testing: 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
• unit testing: 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
• unit testing: 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
• unit testing: 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
• unit testing: 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
• unit testing: 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
• unit testing: 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
• unit testing: 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
• unit testing: 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
• unit testing: 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

Lookup fields change

Lookup fields are defined by extended schema's properties: rowSourceType, rowSource, boundColumn, visibleColumn.

Altering requirements: ExtendedSchemaAlteringRequired

• status: DONE
• unit testing:
  • adding DONE
  • removing TODO
  • altering TODO

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