test/alter3.test

   1 # 2005 February 19
   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: alter3.test,v 1.11 2008/03/19 00:21:31 drh Exp $
  17 #
  18
  19 set testdir [file dirname $argv0]
  20
  21 source $testdir/tester.tcl
  22
  23 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
  24 ifcapable !altertable {
  25   finish_test
  26   return
  27 }
  28
  29 # Determine if there is a codec available on this test.
  30 #
  31 if {[catch {sqlite3 -has-codec} r] || $r} {
  32   set has_codec 1
  33 } else {
  34   set has_codec 0
  35 }
  36
  37
  38 # Test Organisation:
  39 # ------------------
  40 #
  41 # alter3-1.*: Test that ALTER TABLE correctly modifies the CREATE TABLE sql.
  42 # alter3-2.*: Test error messages.
  43 # alter3-3.*: Test adding columns with default value NULL.
  44 # alter3-4.*: Test adding columns with default values other than NULL.
  45 # alter3-5.*: Test adding columns to tables in ATTACHed databases.
  46 # alter3-6.*: Test that temp triggers are not accidentally dropped.
  47 # alter3-7.*: Test that VACUUM resets the file-format.
  48 #
  49
  50 # This procedure returns the value of the file-format in file 'test.db'.
  51 #
  52 proc get_file_format {{fname test.db}} {
  53   return [hexio_get_int [hexio_read $fname 44 4]]
  54 }
  55
  56 do_test alter3-1.1 {
  57   sqlite3_db_config db LEGACY_FILE_FORMAT 1
  58   execsql {
  59     CREATE TABLE abc(a, b, c);
  60     SELECT sql FROM sqlite_master;
  61   }
  62 } {{CREATE TABLE abc(a, b, c)}}
  63 do_test alter3-1.2 {
  64   execsql {ALTER TABLE abc ADD d INTEGER;}
  65   execsql {
  66     SELECT sql FROM sqlite_master;
  67   }
  68 } {{CREATE TABLE abc(a, b, c, d INTEGER)}}
  69 do_test alter3-1.3 {
  70   execsql {ALTER TABLE abc ADD e}
  71   execsql {
  72     SELECT sql FROM sqlite_master;
  73   }
  74 } {{CREATE TABLE abc(a, b, c, d INTEGER, e)}}
  75 do_test alter3-1.4 {
  76   execsql {
  77     CREATE TABLE main.t1(a, b);
  78     ALTER TABLE t1 ADD c;
  79     SELECT sql FROM sqlite_master WHERE tbl_name = 't1';
  80   }
  81 } {{CREATE TABLE t1(a, b, c)}}
  82 do_test alter3-1.5 {
  83   execsql {
  84     ALTER TABLE t1 ADD d CHECK (a>d);
  85     SELECT sql FROM sqlite_master WHERE tbl_name = 't1';
  86   }
  87 } {{CREATE TABLE t1(a, b, c, d CHECK (a>d))}}
  88 ifcapable foreignkey {
  89   do_test alter3-1.6 {
  90     execsql {
  91       CREATE TABLE t2(a, b, UNIQUE(a, b));
  92       ALTER TABLE t2 ADD c REFERENCES t1(c)  ;
  93       SELECT sql FROM sqlite_master WHERE tbl_name = 't2' AND type = 'table';
  94     }
  95   } {{CREATE TABLE t2(a, b, c REFERENCES t1(c), UNIQUE(a, b))}}
  96 }
  97 do_test alter3-1.7 {
  98   execsql {
  99     CREATE TABLE t3(a, b, UNIQUE(a, b));
 100     ALTER TABLE t3 ADD COLUMN c VARCHAR(10, 20);
 101     SELECT sql FROM sqlite_master WHERE tbl_name = 't3' AND type = 'table';
 102   }
 103 } {{CREATE TABLE t3(a, b, c VARCHAR(10, 20), UNIQUE(a, b))}}
 104 do_test alter3-1.99 {
 105   catchsql {
 106     # May not exist if foriegn-keys are omitted at compile time.
 107     DROP TABLE t2;
 108   }
 109   execsql {
 110     DROP TABLE abc;
 111     DROP TABLE t1;
 112     DROP TABLE t3;
 113   }
 114 } {}
 115
 116 do_test alter3-2.1 {
 117   execsql {
 118     CREATE TABLE t1(a, b);
 119     INSERT INTO t1 VALUES(1,2);
 120   }
 121   catchsql {
 122     ALTER TABLE t1 ADD c PRIMARY KEY;
 123   }
 124 } {1 {Cannot add a PRIMARY KEY column}}
 125 do_test alter3-2.2 {
 126   catchsql {
 127     ALTER TABLE t1 ADD c UNIQUE
 128   }
 129 } {1 {Cannot add a UNIQUE column}}
 130 do_test alter3-2.3 {
 131   catchsql {
 132     ALTER TABLE t1 ADD b VARCHAR(10)
 133   }
 134 } {1 {duplicate column name: b}}
 135 do_test alter3-2.3 {
 136   catchsql {
 137     ALTER TABLE t1 ADD c NOT NULL;
 138   }
 139 } {1 {Cannot add a NOT NULL column with default value NULL}}
 140 do_test alter3-2.4 {
 141   catchsql {
 142     ALTER TABLE t1 ADD c NOT NULL DEFAULT 10;
 143   }
 144 } {0 {}}
 145 ifcapable view {
 146   do_test alter3-2.5 {
 147     execsql {
 148       CREATE VIEW v1 AS SELECT * FROM t1;
 149     }
 150     catchsql {
 151       alter table v1 add column d;
 152     }
 153   } {1 {Cannot add a column to a view}}
 154 }
 155 do_test alter3-2.6 {
 156   catchsql {
 157     alter table t1 add column d DEFAULT CURRENT_TIME;
 158   }
 159 } {1 {Cannot add a column with non-constant default}}
 160 do_test alter3-2.99 {
 161   execsql {
 162     DROP TABLE t1;
 163   }
 164 } {}
 165
 166 do_test alter3-3.1 {
 167   execsql {
 168     CREATE TABLE t1(a, b);
 169     INSERT INTO t1 VALUES(1, 100);
 170     INSERT INTO t1 VALUES(2, 300);
 171     SELECT * FROM t1;
 172   }
 173 } {1 100 2 300}
 174 do_test alter3-3.1 {
 175   execsql {
 176     PRAGMA schema_version = 10;
 177   }
 178 } {}
 179 do_test alter3-3.2 {
 180   execsql {
 181     ALTER TABLE t1 ADD c;
 182     SELECT * FROM t1;
 183   }
 184 } {1 100 {} 2 300 {}}
 185 if {!$has_codec} {
 186   do_test alter3-3.3 {
 187     get_file_format
 188   } {3}
 189 }
 190 ifcapable schema_version {
 191   do_test alter3-3.4 {
 192     execsql {
 193       PRAGMA schema_version;
 194     }
 195   } {11}
 196 }
 197
 198 do_test alter3-4.1 {
 199   db close
 200   forcedelete test.db
 201   set ::DB [sqlite3 db test.db]
 202   sqlite3_db_config db LEGACY_FILE_FORMAT 1
 203   execsql {
 204     CREATE TABLE t1(a, b);
 205     INSERT INTO t1 VALUES(1, 100);
 206     INSERT INTO t1 VALUES(2, 300);
 207     SELECT * FROM t1;
 208   }
 209 } {1 100 2 300}
 210 do_test alter3-4.1 {
 211   execsql {
 212     PRAGMA schema_version = 20;
 213   }
 214 } {}
 215 do_test alter3-4.2 {
 216   execsql {
 217     ALTER TABLE t1 ADD c DEFAULT 'hello world';
 218     SELECT * FROM t1;
 219   }
 220 } {1 100 {hello world} 2 300 {hello world}}
 221 if {!$has_codec} {
 222   do_test alter3-4.3 {
 223     get_file_format
 224   } {3}
 225 }
 226 ifcapable schema_version {
 227   do_test alter3-4.4 {
 228     execsql {
 229       PRAGMA schema_version;
 230     }
 231   } {21}
 232 }
 233 do_test alter3-4.99 {
 234   execsql {
 235     DROP TABLE t1;
 236   }
 237 } {}
 238
 239 ifcapable attach {
 240   do_test alter3-5.1 {
 241     forcedelete test2.db
 242     forcedelete test2.db-journal
 243     execsql {
 244       CREATE TABLE t1(a, b);
 245       INSERT INTO t1 VALUES(1, 'one');
 246       INSERT INTO t1 VALUES(2, 'two');
 247       ATTACH 'test2.db' AS aux;
 248       CREATE TABLE aux.t1 AS SELECT * FROM t1;
 249       PRAGMA aux.schema_version = 30;
 250       SELECT sql FROM aux.sqlite_master;
 251     }
 252   } {{CREATE TABLE t1(a,b)}}
 253   do_test alter3-5.2 {
 254     execsql {
 255       ALTER TABLE aux.t1 ADD COLUMN c VARCHAR(128);
 256       SELECT sql FROM aux.sqlite_master;
 257     }
 258   } {{CREATE TABLE t1(a,b, c VARCHAR(128))}}
 259   do_test alter3-5.3 {
 260     execsql {
 261       SELECT * FROM aux.t1;
 262     }
 263   } {1 one {} 2 two {}}
 264   ifcapable schema_version {
 265     do_test alter3-5.4 {
 266       execsql {
 267         PRAGMA aux.schema_version;
 268       }
 269     } {31}
 270   }
 271   if {!$has_codec} {
 272     do_test alter3-5.5 {
 273       list [get_file_format test2.db] [get_file_format]
 274     } {3 3}
 275   }
 276   do_test alter3-5.6 {
 277     execsql {
 278       ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;
 279       SELECT sql FROM aux.sqlite_master;
 280     }
 281   } {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}
 282   do_test alter3-5.7 {
 283     execsql {
 284       SELECT * FROM aux.t1;
 285     }
 286   } {1 one {} 1000 2 two {} 1000}
 287   ifcapable schema_version {
 288     do_test alter3-5.8 {
 289       execsql {
 290         PRAGMA aux.schema_version;
 291       }
 292     } {32}
 293   }
 294   do_test alter3-5.9 {
 295     execsql {
 296       SELECT * FROM t1;
 297     }
 298   } {1 one 2 two}
 299   do_test alter3-5.99 {
 300     execsql {
 301       DROP TABLE aux.t1;
 302       DROP TABLE t1;
 303     }
 304   } {}
 305 }
 306
 307 #----------------------------------------------------------------
 308 # Test that the table schema is correctly reloaded when a column
 309 # is added to a table.
 310 #
 311 ifcapable trigger&&tempdb {
 312   do_test alter3-6.1 {
 313     execsql {
 314       CREATE TABLE t1(a, b);
 315       CREATE TABLE log(trig, a, b);
 316
 317       CREATE TRIGGER t1_a AFTER INSERT ON t1 BEGIN
 318         INSERT INTO log VALUES('a', new.a, new.b);
 319       END;
 320       CREATE TEMP TRIGGER t1_b AFTER INSERT ON t1 BEGIN
 321         INSERT INTO log VALUES('b', new.a, new.b);
 322       END;
 323
 324       INSERT INTO t1 VALUES(1, 2);
 325       SELECT * FROM log;
 326     }
 327   } {b 1 2 a 1 2}
 328   do_test alter3-6.2 {
 329     execsql {
 330       ALTER TABLE t1 ADD COLUMN c DEFAULT 'c';
 331       INSERT INTO t1(a, b) VALUES(3, 4);
 332       SELECT * FROM log;
 333     }
 334   } {b 1 2 a 1 2 b 3 4 a 3 4}
 335 }
 336
 337 if {!$has_codec} {
 338   ifcapable vacuum {
 339     do_test alter3-7.1 {
 340       execsql {
 341         VACUUM;
 342       }
 343       get_file_format
 344     } {1}
 345     do_test alter3-7.2 {
 346       execsql {
 347         CREATE TABLE abc(a, b, c);
 348         ALTER TABLE abc ADD d DEFAULT NULL;
 349       }
 350       get_file_format
 351     } {3}
 352     do_test alter3-7.3 {
 353       execsql {
 354         ALTER TABLE abc ADD e DEFAULT 10;
 355       }
 356       get_file_format
 357     } {3}
 358     do_test alter3-7.4 {
 359       execsql {
 360         ALTER TABLE abc ADD f DEFAULT NULL;
 361       }
 362       get_file_format
 363     } {3}
 364     do_test alter3-7.5 {
 365       execsql {
 366         VACUUM;
 367       }
 368       get_file_format
 369     } {1}
 370   }
 371 }
 372
 373 # Ticket #1183 - Make sure adding columns to large tables does not cause
 374 # memory corruption (as was the case before this bug was fixed).
 375 do_test alter3-8.1 {
 376   execsql {
 377     CREATE TABLE t4(c1);
 378   }
 379 } {}
 380 set ::sql ""
 381 do_test alter3-8.2 {
 382   set cols c1
 383   for {set i 2} {$i < 100} {incr i} {
 384     execsql "
 385       ALTER TABLE t4 ADD c$i
 386     "
 387     lappend cols c$i
 388   }
 389   set ::sql "CREATE TABLE t4([join $cols {, }])"
 390   list
 391 } {}
 392 do_test alter3-8.2 {
 393   execsql {
 394     SELECT sql FROM sqlite_master WHERE name = 't4';
 395   }
 396 } [list $::sql]
 397
 398 finish_test