How you modify SQL Lite tables which have many records?
bobs at twft.com
Thu Oct 28 12:09:02 CDT 2010
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.
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
> At this URL
> 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
> 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;
> I wonder if this is true? And there is no any other more effective way ??
> COPY the whole table to just add/rename column?
> Or just add Foreign Key ?
> 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
> [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:
More information about the use-livecode