How you modify SQL Lite tables which have many records?

[Bob Sneidar]

Well I wonder what the max record count is for sqLite? I hardly think it's millions of records! But I may be mistaken. Still, I fail to see why you HAVE to drop a column if you don't want it anymore. Granted it's kind of dirty to leave a heretofore unused column stranded in your schema, but it's not strictly necessary to remove it. Also, the order the columns are in is irrelevant. 

Mind you I am not defending or supporting sqLite as a good engine for large databases. I'm just saying, given the name sqLITE, you can't hope for full SQL compliance. 

Bob


On Oct 28, 2010, at 9:11 AM, Ruslan Zasukhin wrote:

> 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]
> 
> 
> 
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution