Kexi/Plugins/Tables/Simple alter table: Difference between revisions

From KDE Community Wiki
< Kexi‎ | Plugins‎ | Tables
No edit summary
 
(20 intermediate revisions by the same user not shown)
Line 1: Line 1:
Design page for task: '''Simple approach to fulfill "Add support for alter table's design without losing data" wish'''.
Design page for task: '''"Add support for alter table's design without losing data"'''.


*Started: [[User:Jstaniek|Jstaniek]] ([[User talk:Jstaniek|talk]]) August 2012
*Started: [[User:Jstaniek|Jstaniek]] August 2012
*{{Wish|125253}}
*{{Wish|125253}}
*Target: 2.6.0
*{{KexiBounty|1}}
*Target: ???
*https://invent.kde.org/office/kexi/-/issues/138


__TOC__
__TOC__
==Rationale==
==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.
The "Simple alter table" is the following way of modification of table schema: changes are saved immediately after the user performs destructive changes. It is much easier to deliver than a "full" alter table that was in development for Kexi 1.x series and has never been delivered.


==Analysis==
==Analysis==
Line 15: Line 17:
==Alter actions==
==Alter actions==
This section publishes details on particular actions of table altering.
This section publishes details on particular actions of table altering.
===Caption rename===
===Caption rename===
*status: TODO
*unit testing: TODO
*triggers name change in property editor, so:
*triggers name change in property editor, so:
**A: if name changed, ask for accepting name change:
**A: if name changed, ask for accepting name change:
***if accepted go to ''Name change'' section
***if accepted go to [[#Name change]] section
***if not accepted, revert the recent name change and go to B
***if not accepted, revert the recent name change and go to B
**B: else, do nothing, caption should be saved on Save action
**B: else, do nothing, caption should be saved on Save action


===Name change===
===Name change===
*can happen after change of caption (see Caption rename)
*status: TODO
*can happen after changed ''name'' value in the property editor
*unit testing: TODO
*can happen after:
**change of caption (see Caption rename)
**or ''name'' value changes in the property editor
*request instant applying
*request instant applying
*Algorithm:
*Algorithm:
**if name is duplicated, display info and abort
**ALTER TABLE available: send to the backend
**ALTER TABLE available: send to the backend
**ALTER TABLE not available:
**ALTER TABLE not available:
***
***Create full table copy with all fields but with one field renamed
 
***Copy all records to the new table
use ALTER TABLE RENAME TO
***Drop the original table
 
***Rename table to the original name
===Field removing===
***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===
===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===
===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===
===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, rowSourceValues.
Altering requirements: ExtendedSchemaAlteringRequired
*status: DONE
*unit testing:
**adding DONE
**removing TODO
**altering TODO


==Requirements==
==Requirements==
*Full data safety
*Full data safety
**It's better to warn and request data removal than claiming it is not necessary and then doing that
**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==
*[http://www.sqlite.org/lang_altertable.html SQLite3's ALTER TABLE]

Latest revision as of 16:59, 14 April 2023

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

Rationale

The "Simple alter table" is the following way of modification of table schema: changes are saved immediately after the user performs destructive changes. It is much easier to deliver than a "full" alter table that was in development for Kexi 1.x series and has never been 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, rowSourceValues.

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