KexiDBDesign:

Alter Table Issues

 started by: jstaniek, march 2006
 status of ideas mentioned here: planed for 1.1

Table of Contents    Introduction    Current state    SQLite Capatibilities    Needs

Introduction

Default file-based db driver - SQLite - does not allow to perform sophisticated modifications of existing table design without a need of recreating the table from screatch. User expects such operations will be performed in the background, just as easy as with spreadsheets.

Current state

Currently used 3.0.7 only allows to:

• Rename table by copying its contents to a new table and removing old table. Following steps are performed:
  1. drop exisitng destination table if one exists;
  2. create new table with identical schema as the original
  3. copy all rows to the new table (using INSERT INTO {new} SELECT * FROM {old})
  4. drop old table
  5. update kexi__objects meta data

this is costly o(n) operation performed within transaction.

;; TODO - reimplement this using ALTER TABLE for other engines.

SQLite Capatibilities

3.3.x version allows to rename a table and add a table column. However, the latter operation requires VACUUM for the table to maintain compatibility with SQLite 3.1.x.

Needs

Above operation are not enough. Following operations are needed (n is # of rows in a table):

• o(1) table renaming
• o(1) column renaming
• o(n) column type changing (the cost can be such because data may need casting and converting upon type change)