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