How you modify SQL Lite tables which have many records?

[Ruslan Zasukhin]

Hi Guys,

One developer, which try now implement some GUI around SQL Lite have touch
me with wonder that SQL Lite do not have any way to modify schema of
database.

At this URL
        http://www.sqlite.org/omitted.html

You can read that 

> Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are
> supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER
> COLUMN, ADD CONSTRAINT, and so forth are omitted.

And at this URL
    http://www.sqlite.org/faq.html#q11

Is suggested "solution"

> SQLite has limited ALTER TABLE support that you can use to add a column to the
> end of a table or to change the name of a table. If you want to make more
> complex changes in the structure of a table, you will have to recreate the
> table. You can save existing data to a temporary table, drop the old table,
> create the new table, then copy the data back in from the temporary table.
> 
> For example, suppose you have a table named "t1" with columns names "a", "b",
> and "c" and that you want to delete column "c" from this table. The following
> steps illustrate how this could be done:
> 
> BEGIN TRANSACTION;
> CREATE TEMPORARY TABLE t1_backup(a,b);
> INSERT INTO t1_backup SELECT a,b FROM t1;
> DROP TABLE t1;
> CREATE TABLE t1(a,b);
> INSERT INTO t1 SELECT a,b FROM t1_backup;
> DROP TABLE t1_backup;
> COMMIT;


I wonder if this is true? And there is no any other more effective way ??


P.S.

COPY the whole table to just add/rename column?
Or just add Foreign Key ?

Wow? 

And if table has million record?  100MB size?  1GB size ?  More?

This is of course not a problem, if table has small records count ...
But still ... Somehow not comfortable as for me ...

Good rule of good developer says:
     never believe your app will have small number of items ...


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]