1 # 2005 February 18

     2 #

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

     4 # a legal notice, here is a blessing:

     5 #

     6 #    May you do good and not evil.

     7 #    May you find forgiveness for yourself and forgive others.

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

     9 #

    10 #*************************************************************************

    11 # This file implements regression tests for SQLite library.  The

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

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

    14 # "ALTER TABLE ... ADD COLUMN".

    15 #

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

    17 #

    18 

    19 set testdir [file dirname $argv0]

    20 source $testdir/tester.tcl

    21 

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

    23 ifcapable {!pragma} return

    24 

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

    26 #

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

    28 

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

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

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

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

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

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

    35 #

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

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

    38 #

    39 

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

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

    42 # 

    43 proc set_file_format {newval} {

    44   hexio_write test.db 44 [hexio_render_int32 $newval]

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

    46   incr schemacookie

    47   hexio_write test.db 40 [hexio_render_int32 $schemacookie]

    48   return {}

    49 }

    50 

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

    52 # 

    53 proc get_file_format {{fname test.db}} {

    54   return [hexio_get_int [hexio_read $fname 44 4]]

    55 }

    56 

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

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

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

    60 # NULL, or 3 otherwise. 

    61 #

    62 proc alter_table {tbl sql {file_format 2}} {

    63   sqlite3 dbat test.db

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

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

    66   dbat eval [subst {

    67     PRAGMA writable_schema = 1;

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

    69     PRAGMA writable_schema = 0;

    70   }]

    71   dbat close

    72   set_file_format 2

    73 }

    74 

    75 #-----------------------------------------------------------------------

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

    77 #

    78 do_test alter2-1.1 {

    79   execsql {

    80     CREATE TABLE abc(a, b);

    81     INSERT INTO abc VALUES(1, 2);

    82     INSERT INTO abc VALUES(3, 4);

    83     INSERT INTO abc VALUES(5, 6);

    84   }

    85 } {}

    86 do_test alter2-1.2 {

    87   # ALTER TABLE abc ADD COLUMN c;

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

    89 } {}

    90 do_test alter2-1.3 {

    91   execsql {

    92     SELECT * FROM abc;

    93   }

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

    95 do_test alter2-1.4 {

    96   execsql {

    97     UPDATE abc SET c = 10 WHERE a = 1;

    98     SELECT * FROM abc;

    99   }

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

   101 do_test alter2-1.5 {

   102   execsql {

   103     CREATE INDEX abc_i ON abc(c);

   104   }

   105 } {}

   106 do_test alter2-1.6 {

   107   execsql {

   108     SELECT c FROM abc ORDER BY c;

   109   }

   110 } {{} {} 10}

   111 do_test alter2-1.7 {

   112   execsql {

   113     SELECT * FROM abc WHERE c = 10;

   114   }

   115 } {1 2 10}

   116 do_test alter2-1.8 {

   117   execsql {

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

   119   }

   120 } {8 {} 1 10}

   121 do_test alter2-1.9 {

   122   # ALTER TABLE abc ADD COLUMN d;

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

   124   execsql { SELECT * FROM abc; }

   125   execsql {

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

   127     SELECT * FROM abc;

   128   }

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

   130 do_test alter2-1.10 {

   131   execsql {

   132     SELECT typeof(d) FROM abc;

   133   }

   134 } {null integer null}

   135 do_test alter2-1.99 {

   136   execsql {

   137     DROP TABLE abc;

   138   }

   139 } {}

   140 

   141 #-----------------------------------------------------------------------

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

   143 #

   144 ifcapable view {

   145   do_test alter2-2.1 {

   146     execsql {

   147       CREATE TABLE abc2(a, b, c);

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

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

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

   151       CREATE VIEW abc2_v AS SELECT * FROM abc2;

   152       SELECT * FROM abc2_v;

   153     }

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

   155   do_test alter2-2.2 {

   156     # ALTER TABLE abc ADD COLUMN d;

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

   158     execsql {

   159       SELECT * FROM abc2_v;

   160     }

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

   162   do_test alter2-2.3 {

   163     execsql {

   164       DROP TABLE abc2;

   165       DROP VIEW abc2_v;

   166     }

   167   } {}

   168 }

   169 

   170 #-----------------------------------------------------------------------

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

   172 # trigger pseudo-table.

   173 #

   174 ifcapable trigger {

   175   do_test alter2-3.1 {

   176     execsql {

   177       CREATE TABLE abc3(a, b);

   178       CREATE TABLE blog(o, n);

   179       CREATE TRIGGER abc3_t AFTER UPDATE OF b ON abc3 BEGIN

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

   181       END;

   182     }

   183   } {}

   184   do_test alter2-3.2 {

   185     execsql {

   186       INSERT INTO abc3 VALUES(1, 4);

   187       UPDATE abc3 SET b = 2 WHERE b = 4;

   188       SELECT * FROM blog;

   189     }

   190   } {4 2}

   191   do_test alter2-3.3 {

   192     execsql {

   193       INSERT INTO abc3 VALUES(3, 4);

   194       INSERT INTO abc3 VALUES(5, 6);

   195     }

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

   197     execsql {

   198       SELECT * FROM abc3;

   199     }

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

   201   do_test alter2-3.4 {

   202     execsql {

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

   204       SELECT * FROM abc3;

   205     }

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

   207   do_test alter2-3.5 {

   208     execsql {

   209       SELECT * FROM blog;

   210     }

   211   } {4 2 2 4 4 8}

   212 

   213   do_test alter2-3.6 {

   214     execsql {

   215       CREATE TABLE clog(o, n);

   216       CREATE TRIGGER abc3_t2 AFTER UPDATE OF c ON abc3 BEGIN

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

   218       END;

   219       UPDATE abc3 SET c = a*2;

   220       SELECT * FROM clog;

   221     }

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

   223 }

   224 

   225 #---------------------------------------------------------------------

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

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

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

   229 # file format requires incrementing the schema cookie.

   230 #

   231 do_test alter2-4.1 {

   232   db close

   233   set_file_format 5

   234   sqlite3 db test.db

   235 } {}

   236 do_test alter2-4.2 {

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

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

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

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

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

   242   # test case.

   243   #

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

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

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

   247   # sqlite3_step().

   248   #

   249   catchsql { SELECT * FROM sqlite_master; }

   250   catchsql { SELECT * FROM sqlite_master; }

   251 } {1 {unsupported file format}}

   252 do_test alter2-4.3 {

   253   sqlite3_errcode db

   254 } {SQLITE_ERROR}

   255 do_test alter2-4.4 {

   256   set ::DB [sqlite3_connection_pointer db]

   257   catchsql {

   258     SELECT * FROM sqlite_master;

   259   }

   260 } {1 {unsupported file format}}

   261 do_test alter2-4.5 {

   262   sqlite3_errcode db

   263 } {SQLITE_ERROR}

   264 

   265 #---------------------------------------------------------------------

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

   267 # resets the file format to 1.

   268 #

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

   270 ifcapable vacuum {

   271   do_test alter2-5.1 {

   272     set_file_format 2

   273     db close

   274     sqlite3 db test.db

   275     execsql {SELECT 1 FROM sqlite_master LIMIT 1;}

   276     get_file_format

   277   } {2}

   278   do_test alter2-5.2 {

   279     execsql {    

   280       VACUUM;

   281     }

   282   } {}

   283   do_test alter2-5.3 {

   284     get_file_format

   285   } $default_file_format

   286 }

   287  

   288 #---------------------------------------------------------------------

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

   290 # databases are still created with file-format 1.

   291 #

   292 do_test alter2-6.1 {

   293   db close

   294   set_file_format 2

   295   sqlite3 db test.db

   296   get_file_format

   297 } {2}

   298 ifcapable attach {

   299   do_test alter2-6.2 {

   300     file delete -force test2.db-journal

   301     file delete -force test2.db

   302     execsql {

   303       ATTACH 'test2.db' AS aux;

   304       CREATE TABLE aux.t1(a, b);

   305     }

   306     get_file_format test2.db

   307   } $default_file_format

   308 }

   309 do_test alter2-6.3 {

   310   execsql {

   311     CREATE TABLE t1(a, b);

   312   }

   313   get_file_format 

   314 } {2}

   315 

   316 #---------------------------------------------------------------------

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

   318 # other than NULL work with SELECT statements.

   319 #

   320 do_test alter2-7.1 {

   321   execsql {

   322     DROP TABLE t1;

   323     CREATE TABLE t1(a);

   324     INSERT INTO t1 VALUES(1);

   325     INSERT INTO t1 VALUES(2);

   326     INSERT INTO t1 VALUES(3);

   327     INSERT INTO t1 VALUES(4);

   328     SELECT * FROM t1;

   329   }

   330 } {1 2 3 4}

   331 do_test alter2-7.2 {

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

   333   alter_table t1 $sql 3

   334   execsql {

   335     SELECT * FROM t1 LIMIT 1;

   336   }

   337 } {1 123 123}

   338 do_test alter2-7.3 {

   339   execsql {

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

   341   }

   342 } {1 integer 123 text 123 integer}

   343 do_test alter2-7.4 {

   344   execsql {

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

   346   }

   347 } {1 integer 123 text 123 integer}

   348 do_test alter2-7.5 {

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

   350   alter_table t1 $sql 3

   351   execsql {

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

   353   }

   354 } {1 integer -123 integer 5 text}

   355 

   356 #-----------------------------------------------------------------------

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

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

   359 # new row.

   360 # 

   361 ifcapable trigger {

   362 db function set_val {set ::val}

   363   do_test alter2-8.1 {

   364     execsql {

   365       CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN

   366       SELECT set_val(

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

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

   369       );

   370       END;

   371     }

   372     list

   373   } {}

   374 }

   375 do_test alter2-8.2 {

   376   execsql {

   377     UPDATE t1 SET c = 10 WHERE a = 1;

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

   379   }

   380 } {1 integer -123 integer 10 text}

   381 ifcapable trigger {

   382   do_test alter2-8.3 {

   383     set ::val

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

   385 }

   386 

   387 #-----------------------------------------------------------------------

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

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

   390 # new row.

   391 # 

   392 ifcapable trigger {

   393   do_test alter2-9.1 {

   394     execsql {

   395       CREATE TRIGGER trig2 BEFORE DELETE ON t1 BEGIN

   396       SELECT set_val(

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

   398       );

   399       END;

   400     }

   401     list

   402   } {}

   403   do_test alter2-9.2 {

   404     execsql {

   405       DELETE FROM t1 WHERE a = 2;

   406     }

   407     set ::val

   408   } {-123 integer 5 text}

   409 }

   410 

   411 #-----------------------------------------------------------------------

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

   413 #

   414 ifcapable bloblit {

   415   do_test alter2-10.1 {

   416     execsql {

   417       CREATE TABLE t2(a);

   418       INSERT INTO t2 VALUES('a');

   419       INSERT INTO t2 VALUES('b');

   420       INSERT INTO t2 VALUES('c');

   421       INSERT INTO t2 VALUES('d');

   422     }

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

   424     catchsql {

   425       SELECT * FROM sqlite_master;

   426     }

   427     execsql {

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

   429     }

   430   } {'a' X'ABCD' NULL}

   431   do_test alter2-10.2 {

   432     execsql {

   433       CREATE INDEX i1 ON t2(b);

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

   435     }

   436   } {a b c d}

   437   do_test alter2-10.3 {

   438     execsql {

   439       DELETE FROM t2 WHERE a = 'c';

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

   441     }

   442   } {a b d}

   443   do_test alter2-10.4 {

   444     execsql {

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

   446     }

   447   } {3}

   448 }

   449 

   450 finish_test