On Wed, Jun 11, 2008 at 3:55 PM, Robert Lehr <[EMAIL PROTECTED]> wrote:
> Oracle does not allow DDL statements to be executed in transactions,
> i.e., it does but the statements are COMMITted as they are executed,
> thus cannot be rolled back.
>
> PostgreSQL does allow DDL statements to be executed in transactions,
> i.e., if a DDL query fails then then entire transaction is rolled back
> and no tables, indices, etc., are created, modified, etc.
>
> Which behaviour is implemented in SQLite?
>
> -rlehr
> Robert Lehr
Let's find out!
C:\temp>sqlite3
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> create table foo (id integer, name text);
sqlite> begin immediate;
sqlite> alter table foo add number text;
sqlite> .schema
CREATE TABLE foo (id integer, name text, number text);
sqlite> rollback;
sqlite> .schema
CREATE TABLE foo (id integer, name text);
sqlite> begin immediate;
sqlite> alter table foo add number text;
sqlite> insert into foo (name, number) values ('Jenny', '867-5309');
sqlite> alter table foo add number2 integer not null;
SQL error: Cannot add a NOT NULL column with default value NULL
sqlite> .schema
CREATE TABLE foo (id integer, name text, number text);
sqlite> select * from foo;
|Jenny|867-5309
sqlite> rollback;
sqlite> select * from foo;
sqlite> .schema
CREATE TABLE foo (id integer, name text);
sqlite>
So: first off, at least *some* DDL statements can be performed inside of a
transaction.
Second off, at least *some* failed DDL statements do *not* automatically
roll back the transaction.
Based on the simplistic locking mechanisms used by SQLite, I'm betting that
*any* DDL statement can be done inside a transaction.
Based on http://www.sqlite.org/c3ref/get_autocommit.html, your code should
call get_autocommit() to verify whether or not the transaction has been
rolled back after a DDL statement fails.
--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
