[sqlite] Re: [unclassified] [sqlite] Re: Why does the FAQ example use 2 copies for adding/deleting a column

2008-01-28 Thread Igor Tandetnik 

Lothar Scholz
 wrote:

Transactions do not rewind drop table statements and other schema
manipulations.


That's not true. Schema changes, including DROP TABLE statemens, done 
within a transaction can be happily rolled back. Just try it and see.


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: [unclassified] [sqlite] Re: Why does the FAQ example use 2 copies for adding/deleting a column

2008-01-28 Thread Lothar Scholz 
Hello Igor,

Tuesday, January 29, 2008, 9:06:56 AM, you wrote:

IT> Lothar Scholz
IT>  wrote:
>> This is copying the whole table twice. Is there any reason why it
>> shouldn't be:
>>
>> BEGIN TRANSACTION;
>> CREATE TEMPORARY TABLE t1_backup(a,b);
>> INSERT INTO t1_backup SELECT a,b FROM t1;
>> DROP TABLE t1;
>> ALTER TABLE t1_backup RENAME TO t1;
>> COMMIT;

IT> t1_backup is a temporary table. You want a permanent table in the main
IT> database. ALTER TABLE cannot magically move a table from temp database
IT> to main - the two databases are physically in two different files.

Oopps, you are right but this wasn't really my point, so please
substitute it with:

CREATE TABLE t1_backup(a,b)

The code is also very dangerous because if there are any problems, for
example running out of space, then all the data is lost.
Transactions do not rewind drop table statements and other schema
manipulations.

-- 
Best regards,
 Lothar Scholzmailto:[EMAIL PROTECTED]


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Why does the FAQ example use 2 copies for adding/deleting a column

2008-01-28 Thread Igor Tandetnik 

Lothar Scholz
 wrote:

This is copying the whole table twice. Is there any reason why it
shouldn't be:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
ALTER TABLE t1_backup RENAME TO t1;
COMMIT;


t1_backup is a temporary table. You want a permanent table in the main 
database. ALTER TABLE cannot magically move a table from temp database 
to main - the two databases are physically in two different files.


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-