sl@0: # 2005 February 18
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #*************************************************************************
sl@0: # This file implements regression tests for SQLite library.  The
sl@0: # focus of this script is testing that SQLite can handle a subtle 
sl@0: # file format change that may be used in the future to implement
sl@0: # "ALTER TABLE ... ADD COLUMN".
sl@0: #
sl@0: # $Id: alter2.test,v 1.13 2008/03/19 00:21:31 drh Exp $
sl@0: #
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: # We have to have pragmas in order to do this test
sl@0: ifcapable {!pragma} return
sl@0: 
sl@0: # These tests do not work if there is a codec. 
sl@0: #
sl@0: #if {[catch {sqlite3 -has_codec} r] || $r} return
sl@0: 
sl@0: # The file format change affects the way row-records stored in tables (but 
sl@0: # not indices) are interpreted. Before version 3.1.3, a row-record for a 
sl@0: # table with N columns was guaranteed to contain exactly N fields. As
sl@0: # of version 3.1.3, the record may contain up to N fields. In this case
sl@0: # the M fields that are present are the values for the left-most M 
sl@0: # columns. The (N-M) rightmost columns contain NULL.
sl@0: #
sl@0: # If any records in the database contain less fields than their table
sl@0: # has columns, then the file-format meta value should be set to (at least) 2. 
sl@0: #
sl@0: 
sl@0: # This procedure sets the value of the file-format in file 'test.db'
sl@0: # to $newval. Also, the schema cookie is incremented.
sl@0: # 
sl@0: proc set_file_format {newval} {
sl@0:   hexio_write test.db 44 [hexio_render_int32 $newval]
sl@0:   set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
sl@0:   incr schemacookie
sl@0:   hexio_write test.db 40 [hexio_render_int32 $schemacookie]
sl@0:   return {}
sl@0: }
sl@0: 
sl@0: # This procedure returns the value of the file-format in file 'test.db'.
sl@0: # 
sl@0: proc get_file_format {{fname test.db}} {
sl@0:   return [hexio_get_int [hexio_read $fname 44 4]]
sl@0: }
sl@0: 
sl@0: # This procedure sets the SQL statement stored for table $tbl in the
sl@0: # sqlite_master table of file 'test.db' to $sql. Also set the file format
sl@0: # to the supplied value. This is 2 if the added column has a default that is
sl@0: # NULL, or 3 otherwise. 
sl@0: #
sl@0: proc alter_table {tbl sql {file_format 2}} {
sl@0:   sqlite3 dbat test.db
sl@0:   set s [string map {' ''} $sql]
sl@0:   set t [string map {' ''} $tbl]
sl@0:   dbat eval [subst {
sl@0:     PRAGMA writable_schema = 1;
sl@0:     UPDATE sqlite_master SET sql = '$s' WHERE name = '$t' AND type = 'table';
sl@0:     PRAGMA writable_schema = 0;
sl@0:   }]
sl@0:   dbat close
sl@0:   set_file_format 2
sl@0: }
sl@0: 
sl@0: #-----------------------------------------------------------------------
sl@0: # Some basic tests to make sure short rows are handled.
sl@0: #
sl@0: do_test alter2-1.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE abc(a, b);
sl@0:     INSERT INTO abc VALUES(1, 2);
sl@0:     INSERT INTO abc VALUES(3, 4);
sl@0:     INSERT INTO abc VALUES(5, 6);
sl@0:   }
sl@0: } {}
sl@0: do_test alter2-1.2 {
sl@0:   # ALTER TABLE abc ADD COLUMN c;
sl@0:   alter_table abc {CREATE TABLE abc(a, b, c);}
sl@0: } {}
sl@0: do_test alter2-1.3 {
sl@0:   execsql {
sl@0:     SELECT * FROM abc;
sl@0:   }
sl@0: } {1 2 {} 3 4 {} 5 6 {}}
sl@0: do_test alter2-1.4 {
sl@0:   execsql {
sl@0:     UPDATE abc SET c = 10 WHERE a = 1;
sl@0:     SELECT * FROM abc;
sl@0:   }
sl@0: } {1 2 10 3 4 {} 5 6 {}}
sl@0: do_test alter2-1.5 {
sl@0:   execsql {
sl@0:     CREATE INDEX abc_i ON abc(c);
sl@0:   }
sl@0: } {}
sl@0: do_test alter2-1.6 {
sl@0:   execsql {
sl@0:     SELECT c FROM abc ORDER BY c;
sl@0:   }
sl@0: } {{} {} 10}
sl@0: do_test alter2-1.7 {
sl@0:   execsql {
sl@0:     SELECT * FROM abc WHERE c = 10;
sl@0:   }
sl@0: } {1 2 10}
sl@0: do_test alter2-1.8 {
sl@0:   execsql {
sl@0:     SELECT sum(a), c FROM abc GROUP BY c;
sl@0:   }
sl@0: } {8 {} 1 10}
sl@0: do_test alter2-1.9 {
sl@0:   # ALTER TABLE abc ADD COLUMN d;
sl@0:   alter_table abc {CREATE TABLE abc(a, b, c, d);}
sl@0:   execsql { SELECT * FROM abc; }
sl@0:   execsql {
sl@0:     UPDATE abc SET d = 11 WHERE c IS NULL AND a<4;
sl@0:     SELECT * FROM abc;
sl@0:   }
sl@0: } {1 2 10 {} 3 4 {} 11 5 6 {} {}}
sl@0: do_test alter2-1.10 {
sl@0:   execsql {
sl@0:     SELECT typeof(d) FROM abc;
sl@0:   }
sl@0: } {null integer null}
sl@0: do_test alter2-1.99 {
sl@0:   execsql {
sl@0:     DROP TABLE abc;
sl@0:   }
sl@0: } {}
sl@0: 
sl@0: #-----------------------------------------------------------------------
sl@0: # Test that views work when the underlying table structure is changed.
sl@0: #
sl@0: ifcapable view {
sl@0:   do_test alter2-2.1 {
sl@0:     execsql {
sl@0:       CREATE TABLE abc2(a, b, c);
sl@0:       INSERT INTO abc2 VALUES(1, 2, 10);
sl@0:       INSERT INTO abc2 VALUES(3, 4, NULL);
sl@0:       INSERT INTO abc2 VALUES(5, 6, NULL);
sl@0:       CREATE VIEW abc2_v AS SELECT * FROM abc2;
sl@0:       SELECT * FROM abc2_v;
sl@0:     }
sl@0:   } {1 2 10 3 4 {} 5 6 {}}
sl@0:   do_test alter2-2.2 {
sl@0:     # ALTER TABLE abc ADD COLUMN d;
sl@0:     alter_table abc2 {CREATE TABLE abc2(a, b, c, d);}
sl@0:     execsql {
sl@0:       SELECT * FROM abc2_v;
sl@0:     }
sl@0:   } {1 2 10 {} 3 4 {} {} 5 6 {} {}}
sl@0:   do_test alter2-2.3 {
sl@0:     execsql {
sl@0:       DROP TABLE abc2;
sl@0:       DROP VIEW abc2_v;
sl@0:     }
sl@0:   } {}
sl@0: }
sl@0: 
sl@0: #-----------------------------------------------------------------------
sl@0: # Test that triggers work when a short row is copied to the old.*
sl@0: # trigger pseudo-table.
sl@0: #
sl@0: ifcapable trigger {
sl@0:   do_test alter2-3.1 {
sl@0:     execsql {
sl@0:       CREATE TABLE abc3(a, b);
sl@0:       CREATE TABLE blog(o, n);
sl@0:       CREATE TRIGGER abc3_t AFTER UPDATE OF b ON abc3 BEGIN
sl@0:         INSERT INTO blog VALUES(old.b, new.b);
sl@0:       END;
sl@0:     }
sl@0:   } {}
sl@0:   do_test alter2-3.2 {
sl@0:     execsql {
sl@0:       INSERT INTO abc3 VALUES(1, 4);
sl@0:       UPDATE abc3 SET b = 2 WHERE b = 4;
sl@0:       SELECT * FROM blog;
sl@0:     }
sl@0:   } {4 2}
sl@0:   do_test alter2-3.3 {
sl@0:     execsql {
sl@0:       INSERT INTO abc3 VALUES(3, 4);
sl@0:       INSERT INTO abc3 VALUES(5, 6);
sl@0:     }
sl@0:     alter_table abc3 {CREATE TABLE abc3(a, b, c);}
sl@0:     execsql {
sl@0:       SELECT * FROM abc3;
sl@0:     }
sl@0:   } {1 2 {} 3 4 {} 5 6 {}}
sl@0:   do_test alter2-3.4 {
sl@0:     execsql {
sl@0:       UPDATE abc3 SET b = b*2 WHERE a<4;
sl@0:       SELECT * FROM abc3;
sl@0:     }
sl@0:   } {1 4 {} 3 8 {} 5 6 {}}
sl@0:   do_test alter2-3.5 {
sl@0:     execsql {
sl@0:       SELECT * FROM blog;
sl@0:     }
sl@0:   } {4 2 2 4 4 8}
sl@0: 
sl@0:   do_test alter2-3.6 {
sl@0:     execsql {
sl@0:       CREATE TABLE clog(o, n);
sl@0:       CREATE TRIGGER abc3_t2 AFTER UPDATE OF c ON abc3 BEGIN
sl@0:         INSERT INTO clog VALUES(old.c, new.c);
sl@0:       END;
sl@0:       UPDATE abc3 SET c = a*2;
sl@0:       SELECT * FROM clog;
sl@0:     }
sl@0:   } {{} 2 {} 6 {} 10}
sl@0: }
sl@0: 
sl@0: #---------------------------------------------------------------------
sl@0: # Check that an error occurs if the database is upgraded to a file
sl@0: # format that SQLite does not support (in this case 5). Note: The 
sl@0: # file format is checked each time the schema is read, so changing the
sl@0: # file format requires incrementing the schema cookie.
sl@0: #
sl@0: do_test alter2-4.1 {
sl@0:   db close
sl@0:   set_file_format 5
sl@0:   sqlite3 db test.db
sl@0: } {}
sl@0: do_test alter2-4.2 {
sl@0:   # We have to run two queries here because the Tcl interface uses
sl@0:   # sqlite3_prepare_v2(). In this case, the first query encounters an 
sl@0:   # SQLITE_SCHEMA error. Then, when trying to recompile the statement, the
sl@0:   # "unsupported file format" error is encountered. So the error code
sl@0:   # returned is SQLITE_SCHEMA, not SQLITE_ERROR as required by the following
sl@0:   # test case.
sl@0:   #
sl@0:   # When the query is attempted a second time, the same error message is
sl@0:   # returned but the error code is SQLITE_ERROR, because the unsupported
sl@0:   # file format was detected during a call to sqlite3_prepare(), not
sl@0:   # sqlite3_step().
sl@0:   #
sl@0:   catchsql { SELECT * FROM sqlite_master; }
sl@0:   catchsql { SELECT * FROM sqlite_master; }
sl@0: } {1 {unsupported file format}}
sl@0: do_test alter2-4.3 {
sl@0:   sqlite3_errcode db
sl@0: } {SQLITE_ERROR}
sl@0: do_test alter2-4.4 {
sl@0:   set ::DB [sqlite3_connection_pointer db]
sl@0:   catchsql {
sl@0:     SELECT * FROM sqlite_master;
sl@0:   }
sl@0: } {1 {unsupported file format}}
sl@0: do_test alter2-4.5 {
sl@0:   sqlite3_errcode db
sl@0: } {SQLITE_ERROR}
sl@0: 
sl@0: #---------------------------------------------------------------------
sl@0: # Check that executing VACUUM on a file with file-format version 2
sl@0: # resets the file format to 1.
sl@0: #
sl@0: set default_file_format [expr $SQLITE_DEFAULT_FILE_FORMAT==4 ? 4 : 1]
sl@0: ifcapable vacuum {
sl@0:   do_test alter2-5.1 {
sl@0:     set_file_format 2
sl@0:     db close
sl@0:     sqlite3 db test.db
sl@0:     execsql {SELECT 1 FROM sqlite_master LIMIT 1;}
sl@0:     get_file_format
sl@0:   } {2}
sl@0:   do_test alter2-5.2 {
sl@0:     execsql {    
sl@0:       VACUUM;
sl@0:     }
sl@0:   } {}
sl@0:   do_test alter2-5.3 {
sl@0:     get_file_format
sl@0:   } $default_file_format
sl@0: }
sl@0:  
sl@0: #---------------------------------------------------------------------
sl@0: # Test that when a database with file-format 2 is opened, new 
sl@0: # databases are still created with file-format 1.
sl@0: #
sl@0: do_test alter2-6.1 {
sl@0:   db close
sl@0:   set_file_format 2
sl@0:   sqlite3 db test.db
sl@0:   get_file_format
sl@0: } {2}
sl@0: ifcapable attach {
sl@0:   do_test alter2-6.2 {
sl@0:     file delete -force test2.db-journal
sl@0:     file delete -force test2.db
sl@0:     execsql {
sl@0:       ATTACH 'test2.db' AS aux;
sl@0:       CREATE TABLE aux.t1(a, b);
sl@0:     }
sl@0:     get_file_format test2.db
sl@0:   } $default_file_format
sl@0: }
sl@0: do_test alter2-6.3 {
sl@0:   execsql {
sl@0:     CREATE TABLE t1(a, b);
sl@0:   }
sl@0:   get_file_format 
sl@0: } {2}
sl@0: 
sl@0: #---------------------------------------------------------------------
sl@0: # Test that types and values for columns added with default values 
sl@0: # other than NULL work with SELECT statements.
sl@0: #
sl@0: do_test alter2-7.1 {
sl@0:   execsql {
sl@0:     DROP TABLE t1;
sl@0:     CREATE TABLE t1(a);
sl@0:     INSERT INTO t1 VALUES(1);
sl@0:     INSERT INTO t1 VALUES(2);
sl@0:     INSERT INTO t1 VALUES(3);
sl@0:     INSERT INTO t1 VALUES(4);
sl@0:     SELECT * FROM t1;
sl@0:   }
sl@0: } {1 2 3 4}
sl@0: do_test alter2-7.2 {
sl@0:   set sql {CREATE TABLE t1(a, b DEFAULT '123', c INTEGER DEFAULT '123')}
sl@0:   alter_table t1 $sql 3
sl@0:   execsql {
sl@0:     SELECT * FROM t1 LIMIT 1;
sl@0:   }
sl@0: } {1 123 123}
sl@0: do_test alter2-7.3 {
sl@0:   execsql {
sl@0:     SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;
sl@0:   }
sl@0: } {1 integer 123 text 123 integer}
sl@0: do_test alter2-7.4 {
sl@0:   execsql {
sl@0:     SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;
sl@0:   }
sl@0: } {1 integer 123 text 123 integer}
sl@0: do_test alter2-7.5 {
sl@0:   set sql {CREATE TABLE t1(a, b DEFAULT -123.0, c VARCHAR(10) default 5)}
sl@0:   alter_table t1 $sql 3
sl@0:   execsql {
sl@0:     SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;
sl@0:   }
sl@0: } {1 integer -123 integer 5 text}
sl@0: 
sl@0: #-----------------------------------------------------------------------
sl@0: # Test that UPDATE trigger tables work with default values, and that when
sl@0: # a row is updated the default values are correctly transfered to the 
sl@0: # new row.
sl@0: # 
sl@0: ifcapable trigger {
sl@0: db function set_val {set ::val}
sl@0:   do_test alter2-8.1 {
sl@0:     execsql {
sl@0:       CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN
sl@0:       SELECT set_val(
sl@0:           old.b||' '||typeof(old.b)||' '||old.c||' '||typeof(old.c)||' '||
sl@0:           new.b||' '||typeof(new.b)||' '||new.c||' '||typeof(new.c) 
sl@0:       );
sl@0:       END;
sl@0:     }
sl@0:     list
sl@0:   } {}
sl@0: }
sl@0: do_test alter2-8.2 {
sl@0:   execsql {
sl@0:     UPDATE t1 SET c = 10 WHERE a = 1;
sl@0:     SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;
sl@0:   }
sl@0: } {1 integer -123 integer 10 text}
sl@0: ifcapable trigger {
sl@0:   do_test alter2-8.3 {
sl@0:     set ::val
sl@0:   } {-123 integer 5 text -123 integer 10 text}
sl@0: }
sl@0: 
sl@0: #-----------------------------------------------------------------------
sl@0: # Test that DELETE trigger tables work with default values, and that when
sl@0: # a row is updated the default values are correctly transfered to the 
sl@0: # new row.
sl@0: # 
sl@0: ifcapable trigger {
sl@0:   do_test alter2-9.1 {
sl@0:     execsql {
sl@0:       CREATE TRIGGER trig2 BEFORE DELETE ON t1 BEGIN
sl@0:       SELECT set_val(
sl@0:           old.b||' '||typeof(old.b)||' '||old.c||' '||typeof(old.c)
sl@0:       );
sl@0:       END;
sl@0:     }
sl@0:     list
sl@0:   } {}
sl@0:   do_test alter2-9.2 {
sl@0:     execsql {
sl@0:       DELETE FROM t1 WHERE a = 2;
sl@0:     }
sl@0:     set ::val
sl@0:   } {-123 integer 5 text}
sl@0: }
sl@0: 
sl@0: #-----------------------------------------------------------------------
sl@0: # Test creating an index on a column added with a default value. 
sl@0: #
sl@0: ifcapable bloblit {
sl@0:   do_test alter2-10.1 {
sl@0:     execsql {
sl@0:       CREATE TABLE t2(a);
sl@0:       INSERT INTO t2 VALUES('a');
sl@0:       INSERT INTO t2 VALUES('b');
sl@0:       INSERT INTO t2 VALUES('c');
sl@0:       INSERT INTO t2 VALUES('d');
sl@0:     }
sl@0:     alter_table t2 {CREATE TABLE t2(a, b DEFAULT X'ABCD', c DEFAULT NULL);} 3
sl@0:     catchsql {
sl@0:       SELECT * FROM sqlite_master;
sl@0:     }
sl@0:     execsql {
sl@0:       SELECT quote(a), quote(b), quote(c) FROM t2 LIMIT 1;
sl@0:     }
sl@0:   } {'a' X'ABCD' NULL}
sl@0:   do_test alter2-10.2 {
sl@0:     execsql {
sl@0:       CREATE INDEX i1 ON t2(b);
sl@0:       SELECT a FROM t2 WHERE b = X'ABCD';
sl@0:     }
sl@0:   } {a b c d}
sl@0:   do_test alter2-10.3 {
sl@0:     execsql {
sl@0:       DELETE FROM t2 WHERE a = 'c';
sl@0:       SELECT a FROM t2 WHERE b = X'ABCD';
sl@0:     }
sl@0:   } {a b d}
sl@0:   do_test alter2-10.4 {
sl@0:     execsql {
sl@0:       SELECT count(b) FROM t2 WHERE b = X'ABCD';
sl@0:     }
sl@0:   } {3}
sl@0: }
sl@0: 
sl@0: finish_test