# 2005 February 18

 #

 # The author disclaims copyright to this source code.  In place of

 # a legal notice, here is a blessing:

 #

 #    May you do good and not evil.

 #    May you find forgiveness for yourself and forgive others.

 #    May you share freely, never taking more than you give.

 #

 #*************************************************************************

 # This file implements regression tests for SQLite library.  The

 # focus of this script is testing that SQLite can handle a subtle 

 # file format change that may be used in the future to implement

 # "ALTER TABLE ... ADD COLUMN".

 #

 # $Id: alter2.test,v 1.13 2008/03/19 00:21:31 drh Exp $

 #

 set testdir [file dirname $argv0]

 source $testdir/tester.tcl

 # We have to have pragmas in order to do this test

 ifcapable {!pragma} return

 # These tests do not work if there is a codec. 

 #

 #if {[catch {sqlite3 -has_codec} r] || $r} return

 # The file format change affects the way row-records stored in tables (but 

 # not indices) are interpreted. Before version 3.1.3, a row-record for a 

 # table with N columns was guaranteed to contain exactly N fields. As

 # of version 3.1.3, the record may contain up to N fields. In this case

 # the M fields that are present are the values for the left-most M 

 # columns. The (N-M) rightmost columns contain NULL.

 #

 # If any records in the database contain less fields than their table

 # has columns, then the file-format meta value should be set to (at least) 2. 

 #

 # This procedure sets the value of the file-format in file 'test.db'

 # to $newval. Also, the schema cookie is incremented.

 # 

 proc set_file_format {newval} {

   hexio_write test.db 44 [hexio_render_int32 $newval]

   set schemacookie [hexio_get_int [hexio_read test.db 40 4]]

   incr schemacookie

   hexio_write test.db 40 [hexio_render_int32 $schemacookie]

   return {}

 }

 # This procedure returns the value of the file-format in file 'test.db'.

 # 

 proc get_file_format {{fname test.db}} {

   return [hexio_get_int [hexio_read $fname 44 4]]

 }

 # This procedure sets the SQL statement stored for table $tbl in the

 # sqlite_master table of file 'test.db' to $sql. Also set the file format

 # to the supplied value. This is 2 if the added column has a default that is

 # NULL, or 3 otherwise. 

 #

 proc alter_table {tbl sql {file_format 2}} {

   sqlite3 dbat test.db

   set s [string map {' ''} $sql]

   set t [string map {' ''} $tbl]

   dbat eval [subst {

     PRAGMA writable_schema = 1;

     UPDATE sqlite_master SET sql = '$s' WHERE name = '$t' AND type = 'table';

     PRAGMA writable_schema = 0;

   }]

   dbat close

   set_file_format 2

 }

 #-----------------------------------------------------------------------

 # Some basic tests to make sure short rows are handled.

 #

 do_test alter2-1.1 {

   execsql {

     CREATE TABLE abc(a, b);

     INSERT INTO abc VALUES(1, 2);

     INSERT INTO abc VALUES(3, 4);

     INSERT INTO abc VALUES(5, 6);

   }

 } {}

 do_test alter2-1.2 {

   # ALTER TABLE abc ADD COLUMN c;

   alter_table abc {CREATE TABLE abc(a, b, c);}

 } {}

 do_test alter2-1.3 {

   execsql {

     SELECT * FROM abc;

   }

 } {1 2 {} 3 4 {} 5 6 {}}

 do_test alter2-1.4 {

   execsql {

     UPDATE abc SET c = 10 WHERE a = 1;

     SELECT * FROM abc;

   }

 } {1 2 10 3 4 {} 5 6 {}}

 do_test alter2-1.5 {

   execsql {

     CREATE INDEX abc_i ON abc(c);

   }

 } {}

 do_test alter2-1.6 {

   execsql {

     SELECT c FROM abc ORDER BY c;

   }

 } {{} {} 10}

 do_test alter2-1.7 {

   execsql {

     SELECT * FROM abc WHERE c = 10;

   }

 } {1 2 10}

 do_test alter2-1.8 {

   execsql {

     SELECT sum(a), c FROM abc GROUP BY c;

   }

 } {8 {} 1 10}

 do_test alter2-1.9 {

   # ALTER TABLE abc ADD COLUMN d;

   alter_table abc {CREATE TABLE abc(a, b, c, d);}

   execsql { SELECT * FROM abc; }

   execsql {

     UPDATE abc SET d = 11 WHERE c IS NULL AND a<4;

     SELECT * FROM abc;

   }

 } {1 2 10 {} 3 4 {} 11 5 6 {} {}}

 do_test alter2-1.10 {

   execsql {

     SELECT typeof(d) FROM abc;

   }

 } {null integer null}

 do_test alter2-1.99 {

   execsql {

     DROP TABLE abc;

   }

 } {}

 #-----------------------------------------------------------------------

 # Test that views work when the underlying table structure is changed.

 #

 ifcapable view {

   do_test alter2-2.1 {

     execsql {

       CREATE TABLE abc2(a, b, c);

       INSERT INTO abc2 VALUES(1, 2, 10);

       INSERT INTO abc2 VALUES(3, 4, NULL);

       INSERT INTO abc2 VALUES(5, 6, NULL);

       CREATE VIEW abc2_v AS SELECT * FROM abc2;

       SELECT * FROM abc2_v;

     }

   } {1 2 10 3 4 {} 5 6 {}}

   do_test alter2-2.2 {

     # ALTER TABLE abc ADD COLUMN d;

     alter_table abc2 {CREATE TABLE abc2(a, b, c, d);}

     execsql {

       SELECT * FROM abc2_v;

     }

   } {1 2 10 {} 3 4 {} {} 5 6 {} {}}

   do_test alter2-2.3 {

     execsql {

       DROP TABLE abc2;

       DROP VIEW abc2_v;

     }

   } {}

 }

 #-----------------------------------------------------------------------

 # Test that triggers work when a short row is copied to the old.*

 # trigger pseudo-table.

 #

 ifcapable trigger {

   do_test alter2-3.1 {

     execsql {

       CREATE TABLE abc3(a, b);

       CREATE TABLE blog(o, n);

       CREATE TRIGGER abc3_t AFTER UPDATE OF b ON abc3 BEGIN

         INSERT INTO blog VALUES(old.b, new.b);

       END;

     }

   } {}

   do_test alter2-3.2 {

     execsql {

       INSERT INTO abc3 VALUES(1, 4);

       UPDATE abc3 SET b = 2 WHERE b = 4;

       SELECT * FROM blog;

     }

   } {4 2}

   do_test alter2-3.3 {

     execsql {

       INSERT INTO abc3 VALUES(3, 4);

       INSERT INTO abc3 VALUES(5, 6);

     }

     alter_table abc3 {CREATE TABLE abc3(a, b, c);}

     execsql {

       SELECT * FROM abc3;

     }

   } {1 2 {} 3 4 {} 5 6 {}}

   do_test alter2-3.4 {

     execsql {

       UPDATE abc3 SET b = b*2 WHERE a<4;

       SELECT * FROM abc3;

     }

   } {1 4 {} 3 8 {} 5 6 {}}

   do_test alter2-3.5 {

     execsql {

       SELECT * FROM blog;

     }

   } {4 2 2 4 4 8}

   do_test alter2-3.6 {

     execsql {

       CREATE TABLE clog(o, n);

       CREATE TRIGGER abc3_t2 AFTER UPDATE OF c ON abc3 BEGIN

         INSERT INTO clog VALUES(old.c, new.c);

       END;

       UPDATE abc3 SET c = a*2;

       SELECT * FROM clog;

     }

   } {{} 2 {} 6 {} 10}

 }

 #---------------------------------------------------------------------

 # Check that an error occurs if the database is upgraded to a file

 # format that SQLite does not support (in this case 5). Note: The 

 # file format is checked each time the schema is read, so changing the

 # file format requires incrementing the schema cookie.

 #

 do_test alter2-4.1 {

   db close

   set_file_format 5

   sqlite3 db test.db

 } {}

 do_test alter2-4.2 {

   # We have to run two queries here because the Tcl interface uses

   # sqlite3_prepare_v2(). In this case, the first query encounters an 

   # SQLITE_SCHEMA error. Then, when trying to recompile the statement, the

   # "unsupported file format" error is encountered. So the error code

   # returned is SQLITE_SCHEMA, not SQLITE_ERROR as required by the following

   # test case.

   #

   # When the query is attempted a second time, the same error message is

   # returned but the error code is SQLITE_ERROR, because the unsupported

   # file format was detected during a call to sqlite3_prepare(), not

   # sqlite3_step().

   #

   catchsql { SELECT * FROM sqlite_master; }

   catchsql { SELECT * FROM sqlite_master; }

 } {1 {unsupported file format}}

 do_test alter2-4.3 {

   sqlite3_errcode db

 } {SQLITE_ERROR}

 do_test alter2-4.4 {

   set ::DB [sqlite3_connection_pointer db]

   catchsql {

     SELECT * FROM sqlite_master;

   }

 } {1 {unsupported file format}}

 do_test alter2-4.5 {

   sqlite3_errcode db

 } {SQLITE_ERROR}

 #---------------------------------------------------------------------

 # Check that executing VACUUM on a file with file-format version 2

 # resets the file format to 1.

 #

 set default_file_format [expr $SQLITE_DEFAULT_FILE_FORMAT==4 ? 4 : 1]

 ifcapable vacuum {

   do_test alter2-5.1 {

     set_file_format 2

     db close

     sqlite3 db test.db

     execsql {SELECT 1 FROM sqlite_master LIMIT 1;}

     get_file_format

   } {2}

   do_test alter2-5.2 {

     execsql {    

       VACUUM;

     }

   } {}

   do_test alter2-5.3 {

     get_file_format

   } $default_file_format

 }

 #---------------------------------------------------------------------

 # Test that when a database with file-format 2 is opened, new 

 # databases are still created with file-format 1.

 #

 do_test alter2-6.1 {

   db close

   set_file_format 2

   sqlite3 db test.db

   get_file_format

 } {2}

 ifcapable attach {

   do_test alter2-6.2 {

     file delete -force test2.db-journal

     file delete -force test2.db

     execsql {

       ATTACH 'test2.db' AS aux;

       CREATE TABLE aux.t1(a, b);

     }

     get_file_format test2.db

   } $default_file_format

 }

 do_test alter2-6.3 {

   execsql {

     CREATE TABLE t1(a, b);

   }

   get_file_format 

 } {2}

 #---------------------------------------------------------------------

 # Test that types and values for columns added with default values 

 # other than NULL work with SELECT statements.

 #

 do_test alter2-7.1 {

   execsql {

     DROP TABLE t1;

     CREATE TABLE t1(a);

     INSERT INTO t1 VALUES(1);

     INSERT INTO t1 VALUES(2);

     INSERT INTO t1 VALUES(3);

     INSERT INTO t1 VALUES(4);

     SELECT * FROM t1;

   }

 } {1 2 3 4}

 do_test alter2-7.2 {

   set sql {CREATE TABLE t1(a, b DEFAULT '123', c INTEGER DEFAULT '123')}

   alter_table t1 $sql 3

   execsql {

     SELECT * FROM t1 LIMIT 1;

   }

 } {1 123 123}

 do_test alter2-7.3 {

   execsql {

     SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;

   }

 } {1 integer 123 text 123 integer}

 do_test alter2-7.4 {

   execsql {

     SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;

   }

 } {1 integer 123 text 123 integer}

 do_test alter2-7.5 {

   set sql {CREATE TABLE t1(a, b DEFAULT -123.0, c VARCHAR(10) default 5)}

   alter_table t1 $sql 3

   execsql {

     SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;

   }

 } {1 integer -123 integer 5 text}

 #-----------------------------------------------------------------------

 # Test that UPDATE trigger tables work with default values, and that when

 # a row is updated the default values are correctly transfered to the 

 # new row.

 # 

 ifcapable trigger {

 db function set_val {set ::val}

   do_test alter2-8.1 {

     execsql {

       CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN

       SELECT set_val(

           old.b||' '||typeof(old.b)||' '||old.c||' '||typeof(old.c)||' '||

           new.b||' '||typeof(new.b)||' '||new.c||' '||typeof(new.c) 

       );

       END;

     }

     list

   } {}

 }

 do_test alter2-8.2 {

   execsql {

     UPDATE t1 SET c = 10 WHERE a = 1;

     SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;

   }

 } {1 integer -123 integer 10 text}

 ifcapable trigger {

   do_test alter2-8.3 {

     set ::val

   } {-123 integer 5 text -123 integer 10 text}

 }

 #-----------------------------------------------------------------------

 # Test that DELETE trigger tables work with default values, and that when

 # a row is updated the default values are correctly transfered to the 

 # new row.

 # 

 ifcapable trigger {

   do_test alter2-9.1 {

     execsql {

       CREATE TRIGGER trig2 BEFORE DELETE ON t1 BEGIN

       SELECT set_val(

           old.b||' '||typeof(old.b)||' '||old.c||' '||typeof(old.c)

       );

       END;

     }

     list

   } {}

   do_test alter2-9.2 {

     execsql {

       DELETE FROM t1 WHERE a = 2;

     }

     set ::val

   } {-123 integer 5 text}

 }

 #-----------------------------------------------------------------------

 # Test creating an index on a column added with a default value. 

 #

 ifcapable bloblit {

   do_test alter2-10.1 {

     execsql {

       CREATE TABLE t2(a);

       INSERT INTO t2 VALUES('a');

       INSERT INTO t2 VALUES('b');

       INSERT INTO t2 VALUES('c');

       INSERT INTO t2 VALUES('d');

     }

     alter_table t2 {CREATE TABLE t2(a, b DEFAULT X'ABCD', c DEFAULT NULL);} 3

     catchsql {

       SELECT * FROM sqlite_master;

     }

     execsql {

       SELECT quote(a), quote(b), quote(c) FROM t2 LIMIT 1;

     }

   } {'a' X'ABCD' NULL}

   do_test alter2-10.2 {

     execsql {

       CREATE INDEX i1 ON t2(b);

       SELECT a FROM t2 WHERE b = X'ABCD';

     }

   } {a b c d}

   do_test alter2-10.3 {

     execsql {

       DELETE FROM t2 WHERE a = 'c';

       SELECT a FROM t2 WHERE b = X'ABCD';

     }

   } {a b d}

   do_test alter2-10.4 {

     execsql {

       SELECT count(b) FROM t2 WHERE b = X'ABCD';

     }

   } {3}

 }

 finish_test