kexi project
Rapid Database Application Development
"Microsoft Access for Linux"

Home Download FAQ Support Features Handbook Screenshots Screencasts Compile Kexi Development Authors Contact License Sponsorship Translate This Site

wiki navigation:

Front Page
[info] [diff] [login]
[recent changes]
[most popular]
You can donate
to Kexi Project:
Via PayPal

Spread the word about Kexi!
Get Kexi Now!

Built on the KDE technology

Member of the Calligra Suite

No Software Patents!


Alter Table Issues

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

Table of Contents
   Current state
   SQLite Capatibilities


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.

^ toc

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.

^ toc

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.

^ toc


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)

Kexi - "MS Access for Linux" ... and Windows
© 2002-2007 Kexi Team
This content is available under GFDL
Last edited: March 7, 2006 by js, visited 0 times.