test/alter2.test

   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.14 2009/04/07 14:14:22 danielk1977 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 # Do not use a codec for tests in this file, as the database file is
  26 # manipulated directly using tcl scripts. See proc [set_file_format].
  27 #
  28 do_not_use_codec
  29
  30 # The file format change affects the way row-records stored in tables (but
  31 # not indices) are interpreted. Before version 3.1.3, a row-record for a
  32 # table with N columns was guaranteed to contain exactly N fields. As
  33 # of version 3.1.3, the record may contain up to N fields. In this case
  34 # the M fields that are present are the values for the left-most M
  35 # columns. The (N-M) rightmost columns contain NULL.
  36 #
  37 # If any records in the database contain less fields than their table
  38 # has columns, then the file-format meta value should be set to (at least) 2.
  39 #
  40
  41 # This procedure sets the value of the file-format in file 'test.db'
  42 # to $newval. Also, the schema cookie is incremented.
  43 #
  44 proc set_file_format {newval} {
  45   hexio_write test.db 44 [hexio_render_int32 $newval]
  46   set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
  47   incr schemacookie
  48   hexio_write test.db 40 [hexio_render_int32 $schemacookie]
  49   return {}
  50 }
  51
  52 # This procedure returns the value of the file-format in file 'test.db'.
  53 #
  54 proc get_file_format {{fname test.db}} {
  55   return [hexio_get_int [hexio_read $fname 44 4]]
  56 }
  57
  58 # This procedure sets the SQL statement stored for table $tbl in the
  59 # sqlite_master table of file 'test.db' to $sql. Also set the file format
  60 # to the supplied value. This is 2 if the added column has a default that is
  61 # NULL, or 3 otherwise.
  62 #
  63 proc alter_table {tbl sql {file_format 2}} {
  64   sqlite3 dbat test.db
  65   set s [string map {' ''} $sql]
  66   set t [string map {' ''} $tbl]
  67   dbat eval [subst {
  68     PRAGMA writable_schema = 1;
  69     UPDATE sqlite_master SET sql = '$s' WHERE name = '$t' AND type = 'table';
  70     PRAGMA writable_schema = 0;
  71   }]
  72   dbat close
  73   set_file_format 2
  74 }
  75
  76 # Create bogus application-defined functions for functions used
  77 # internally by ALTER TABLE, to ensure that ALTER TABLE falls back
  78 # to the built-in functions.
  79 #
  80 proc failing_app_func {args} {error "bad function"}
  81 do_test alter2-1.0 {
  82   db func substr failing_app_func
  83   db func like failing_app_func
  84   db func sqlite_rename_table failing_app_func
  85   db func sqlite_rename_trigger failing_app_func
  86   db func sqlite_rename_parent failing_app_func
  87   catchsql {SELECT substr('abcdefg',1,3)}
  88 } {1 {bad function}}
  89
  90
  91 #-----------------------------------------------------------------------
  92 # Some basic tests to make sure short rows are handled.
  93 #
  94 do_test alter2-1.1 {
  95   execsql {
  96     CREATE TABLE abc(a, b);
  97     INSERT INTO abc VALUES(1, 2);
  98     INSERT INTO abc VALUES(3, 4);
  99     INSERT INTO abc VALUES(5, 6);
 100   }
 101 } {}
 102 do_test alter2-1.2 {
 103   # ALTER TABLE abc ADD COLUMN c;
 104   alter_table abc {CREATE TABLE abc(a, b, c);}
 105 } {}
 106 do_test alter2-1.3 {
 107   execsql {
 108     SELECT * FROM abc;
 109   }
 110 } {1 2 {} 3 4 {} 5 6 {}}
 111 do_test alter2-1.4 {
 112   execsql {
 113     UPDATE abc SET c = 10 WHERE a = 1;
 114     SELECT * FROM abc;
 115   }
 116 } {1 2 10 3 4 {} 5 6 {}}
 117 do_test alter2-1.5 {
 118   execsql {
 119     CREATE INDEX abc_i ON abc(c);
 120   }
 121 } {}
 122 do_test alter2-1.6 {
 123   execsql {
 124     SELECT c FROM abc ORDER BY c;
 125   }
 126 } {{} {} 10}
 127 do_test alter2-1.7 {
 128   execsql {
 129     SELECT * FROM abc WHERE c = 10;
 130   }
 131 } {1 2 10}
 132 do_test alter2-1.8 {
 133   execsql {
 134     SELECT sum(a), c FROM abc GROUP BY c;
 135   }
 136 } {8 {} 1 10}
 137 do_test alter2-1.9 {
 138   # ALTER TABLE abc ADD COLUMN d;
 139   alter_table abc {CREATE TABLE abc(a, b, c, d);}
 140   if {[permutation] == "prepare"} { db cache flush }
 141   execsql { SELECT * FROM abc; }
 142   execsql {
 143     UPDATE abc SET d = 11 WHERE c IS NULL AND a<4;
 144     SELECT * FROM abc;
 145   }
 146 } {1 2 10 {} 3 4 {} 11 5 6 {} {}}
 147 do_test alter2-1.10 {
 148   execsql {
 149     SELECT typeof(d) FROM abc;
 150   }
 151 } {null integer null}
 152 do_test alter2-1.99 {
 153   execsql {
 154     DROP TABLE abc;
 155   }
 156 } {}
 157
 158 #-----------------------------------------------------------------------
 159 # Test that views work when the underlying table structure is changed.
 160 #
 161 ifcapable view {
 162   do_test alter2-2.1 {
 163     execsql {
 164       CREATE TABLE abc2(a, b, c);
 165       INSERT INTO abc2 VALUES(1, 2, 10);
 166       INSERT INTO abc2 VALUES(3, 4, NULL);
 167       INSERT INTO abc2 VALUES(5, 6, NULL);
 168       CREATE VIEW abc2_v AS SELECT * FROM abc2;
 169       SELECT * FROM abc2_v;
 170     }
 171   } {1 2 10 3 4 {} 5 6 {}}
 172   do_test alter2-2.2 {
 173     # ALTER TABLE abc ADD COLUMN d;
 174     alter_table abc2 {CREATE TABLE abc2(a, b, c, d);}
 175     execsql {
 176       SELECT * FROM abc2_v;
 177     }
 178   } {1 2 10 {} 3 4 {} {} 5 6 {} {}}
 179   do_test alter2-2.3 {
 180     execsql {
 181       DROP TABLE abc2;
 182       DROP VIEW abc2_v;
 183     }
 184   } {}
 185 }
 186
 187 #-----------------------------------------------------------------------
 188 # Test that triggers work when a short row is copied to the old.*
 189 # trigger pseudo-table.
 190 #
 191 ifcapable trigger {
 192   do_test alter2-3.1 {
 193     execsql {
 194       CREATE TABLE abc3(a, b);
 195       CREATE TABLE blog(o, n);
 196       CREATE TRIGGER abc3_t AFTER UPDATE OF b ON abc3 BEGIN
 197         INSERT INTO blog VALUES(old.b, new.b);
 198       END;
 199     }
 200   } {}
 201   do_test alter2-3.2 {
 202     execsql {
 203       INSERT INTO abc3 VALUES(1, 4);
 204       UPDATE abc3 SET b = 2 WHERE b = 4;
 205       SELECT * FROM blog;
 206     }
 207   } {4 2}
 208   do_test alter2-3.3 {
 209     execsql {
 210       INSERT INTO abc3 VALUES(3, 4);
 211       INSERT INTO abc3 VALUES(5, 6);
 212     }
 213     alter_table abc3 {CREATE TABLE abc3(a, b, c);}
 214     execsql {
 215       SELECT * FROM abc3;
 216     }
 217   } {1 2 {} 3 4 {} 5 6 {}}
 218   do_test alter2-3.4 {
 219     execsql {
 220       UPDATE abc3 SET b = b*2 WHERE a<4;
 221       SELECT * FROM abc3;
 222     }
 223   } {1 4 {} 3 8 {} 5 6 {}}
 224   do_test alter2-3.5 {
 225     execsql {
 226       SELECT * FROM blog;
 227     }
 228   } {4 2 2 4 4 8}
 229
 230   do_test alter2-3.6 {
 231     execsql {
 232       CREATE TABLE clog(o, n);
 233       CREATE TRIGGER abc3_t2 AFTER UPDATE OF c ON abc3 BEGIN
 234         INSERT INTO clog VALUES(old.c, new.c);
 235       END;
 236       UPDATE abc3 SET c = a*2;
 237       SELECT * FROM clog;
 238     }
 239   } {{} 2 {} 6 {} 10}
 240 } else {
 241   execsql { CREATE TABLE abc3(a, b); }
 242 }
 243
 244 #---------------------------------------------------------------------
 245 # Check that an error occurs if the database is upgraded to a file
 246 # format that SQLite does not support (in this case 5). Note: The
 247 # file format is checked each time the schema is read, so changing the
 248 # file format requires incrementing the schema cookie.
 249 #
 250 do_test alter2-4.1 {
 251   db close
 252   set_file_format 5
 253   catch { sqlite3 db test.db }
 254   set {} {}
 255 } {}
 256 do_test alter2-4.2 {
 257   # We have to run two queries here because the Tcl interface uses
 258   # sqlite3_prepare_v2(). In this case, the first query encounters an
 259   # SQLITE_SCHEMA error. Then, when trying to recompile the statement, the
 260   # "unsupported file format" error is encountered. So the error code
 261   # returned is SQLITE_SCHEMA, not SQLITE_ERROR as required by the following
 262   # test case.
 263   #
 264   # When the query is attempted a second time, the same error message is
 265   # returned but the error code is SQLITE_ERROR, because the unsupported
 266   # file format was detected during a call to sqlite3_prepare(), not
 267   # sqlite3_step().
 268   #
 269   catchsql { SELECT * FROM sqlite_master; }
 270   catchsql { SELECT * FROM sqlite_master; }
 271 } {1 {unsupported file format}}
 272 do_test alter2-4.3 {
 273   sqlite3_errcode db
 274 } {SQLITE_ERROR}
 275 do_test alter2-4.4 {
 276   set ::DB [sqlite3_connection_pointer db]
 277   catchsql {
 278     SELECT * FROM sqlite_master;
 279   }
 280 } {1 {unsupported file format}}
 281 do_test alter2-4.5 {
 282   sqlite3_errcode db
 283 } {SQLITE_ERROR}
 284
 285 #---------------------------------------------------------------------
 286 # Check that executing VACUUM on a file with file-format version 2
 287 # resets the file format to 1.
 288 #
 289 set default_file_format [expr $SQLITE_DEFAULT_FILE_FORMAT==4 ? 4 : 1]
 290 ifcapable vacuum {
 291   do_test alter2-5.1 {
 292     set_file_format 2
 293     db close
 294     sqlite3 db test.db
 295     execsql {SELECT 1 FROM sqlite_master LIMIT 1;}
 296     get_file_format
 297   } {2}
 298   do_test alter2-5.2 {
 299     execsql { VACUUM }
 300   } {}
 301   do_test alter2-5.3 {
 302     get_file_format
 303   } $default_file_format
 304 }
 305
 306 #---------------------------------------------------------------------
 307 # Test that when a database with file-format 2 is opened, new
 308 # databases are still created with file-format 1.
 309 #
 310 do_test alter2-6.1 {
 311   db close
 312   set_file_format 2
 313   sqlite3 db test.db
 314   get_file_format
 315 } {2}
 316 ifcapable attach {
 317   do_test alter2-6.2 {
 318     forcedelete test2.db-journal
 319     forcedelete test2.db
 320     execsql {
 321       ATTACH 'test2.db' AS aux;
 322       CREATE TABLE aux.t1(a, b);
 323     }
 324     get_file_format test2.db
 325   } $default_file_format
 326 }
 327 do_test alter2-6.3 {
 328   execsql {
 329     CREATE TABLE t1(a, b);
 330   }
 331   get_file_format
 332 } {2}
 333
 334 #---------------------------------------------------------------------
 335 # Test that types and values for columns added with default values
 336 # other than NULL work with SELECT statements.
 337 #
 338 do_test alter2-7.1 {
 339   execsql {
 340     DROP TABLE t1;
 341     CREATE TABLE t1(a);
 342     INSERT INTO t1 VALUES(1);
 343     INSERT INTO t1 VALUES(2);
 344     INSERT INTO t1 VALUES(3);
 345     INSERT INTO t1 VALUES(4);
 346     SELECT * FROM t1;
 347   }
 348 } {1 2 3 4}
 349 do_test alter2-7.2 {
 350   set sql {CREATE TABLE t1(a, b DEFAULT '123', c INTEGER DEFAULT '123')}
 351   alter_table t1 $sql 3
 352   execsql {
 353     SELECT * FROM t1 LIMIT 1;
 354   }
 355 } {1 123 123}
 356 do_test alter2-7.3 {
 357   execsql {
 358     SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;
 359   }
 360 } {1 integer 123 text 123 integer}
 361 do_test alter2-7.4 {
 362   execsql {
 363     SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;
 364   }
 365 } {1 integer 123 text 123 integer}
 366 do_test alter2-7.5 {
 367   set sql {CREATE TABLE t1(a, b DEFAULT -123.0, c VARCHAR(10) default 5)}
 368   alter_table t1 $sql 3
 369   execsql {
 370     SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;
 371   }
 372 } {1 integer -123 integer 5 text}
 373
 374 #-----------------------------------------------------------------------
 375 # Test that UPDATE trigger tables work with default values, and that when
 376 # a row is updated the default values are correctly transfered to the
 377 # new row.
 378 #
 379 ifcapable trigger {
 380 db function set_val {set ::val}
 381   do_test alter2-8.1 {
 382     execsql {
 383       CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN
 384       SELECT set_val(
 385           old.b||' '||typeof(old.b)||' '||old.c||' '||typeof(old.c)||' '||
 386           new.b||' '||typeof(new.b)||' '||new.c||' '||typeof(new.c)
 387       );
 388       END;
 389     }
 390     list
 391   } {}
 392 }
 393 do_test alter2-8.2 {
 394   execsql {
 395     UPDATE t1 SET c = 10 WHERE a = 1;
 396     SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1;
 397   }
 398 } {1 integer -123 integer 10 text}
 399 ifcapable trigger {
 400   do_test alter2-8.3 {
 401     set ::val
 402   } {-123 integer 5 text -123 integer 10 text}
 403 }
 404
 405 #-----------------------------------------------------------------------
 406 # Test that DELETE trigger tables work with default values, and that when
 407 # a row is updated the default values are correctly transfered to the
 408 # new row.
 409 #
 410 ifcapable trigger {
 411   do_test alter2-9.1 {
 412     execsql {
 413       CREATE TRIGGER trig2 BEFORE DELETE ON t1 BEGIN
 414       SELECT set_val(
 415           old.b||' '||typeof(old.b)||' '||old.c||' '||typeof(old.c)
 416       );
 417       END;
 418     }
 419     list
 420   } {}
 421   do_test alter2-9.2 {
 422     execsql {
 423       DELETE FROM t1 WHERE a = 2;
 424     }
 425     set ::val
 426   } {-123 integer 5 text}
 427 }
 428
 429 #-----------------------------------------------------------------------
 430 # Test creating an index on a column added with a default value.
 431 #
 432 ifcapable bloblit {
 433   do_test alter2-10.1 {
 434     execsql {
 435       CREATE TABLE t2(a);
 436       INSERT INTO t2 VALUES('a');
 437       INSERT INTO t2 VALUES('b');
 438       INSERT INTO t2 VALUES('c');
 439       INSERT INTO t2 VALUES('d');
 440     }
 441     alter_table t2 {CREATE TABLE t2(a, b DEFAULT X'ABCD', c DEFAULT NULL);} 3
 442     catchsql {
 443       SELECT * FROM sqlite_master;
 444     }
 445     execsql {
 446       SELECT quote(a), quote(b), quote(c) FROM t2 LIMIT 1;
 447     }
 448   } {'a' X'ABCD' NULL}
 449   do_test alter2-10.2 {
 450     execsql {
 451       CREATE INDEX i1 ON t2(b);
 452       SELECT a FROM t2 WHERE b = X'ABCD';
 453     }
 454   } {a b c d}
 455   do_test alter2-10.3 {
 456     execsql {
 457       DELETE FROM t2 WHERE a = 'c';
 458       SELECT a FROM t2 WHERE b = X'ABCD';
 459     }
 460   } {a b d}
 461   do_test alter2-10.4 {
 462     execsql {
 463       SELECT count(b) FROM t2 WHERE b = X'ABCD';
 464     }
 465   } {3}
 466 }
 467
 468 finish_test