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