kexi project
Rapid Database Application Development
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
KDE

Member of the Calligra Suite

No Software Patents!

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.

^ 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

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)


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