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   }
 120   catchsql {
 121     ALTER TABLE t1 ADD c PRIMARY KEY;
 122   }
 123 } {1 {Cannot add a PRIMARY KEY column}}
 124 do_test alter3-2.2 {
 125   catchsql {
 126     ALTER TABLE t1 ADD c UNIQUE
 127   }
 128 } {1 {Cannot add a UNIQUE column}}
 129 do_test alter3-2.3 {
 130   catchsql {
 131     ALTER TABLE t1 ADD b VARCHAR(10)
 132   }
 133 } {1 {duplicate column name: b}}
 134 do_test alter3-2.3 {
 135   catchsql {
 136     ALTER TABLE t1 ADD c NOT NULL;
 137   }
 138 } {1 {Cannot add a NOT NULL column with default value NULL}}
 139 do_test alter3-2.4 {
 140   catchsql {
 141     ALTER TABLE t1 ADD c NOT NULL DEFAULT 10;
 142   }
 143 } {0 {}}
 144 ifcapable view {
 145   do_test alter3-2.5 {
 146     execsql {
 147       CREATE VIEW v1 AS SELECT * FROM t1;
 148     }
 149     catchsql {
 150       alter table v1 add column d;
 151     }
 152   } {1 {Cannot add a column to a view}}
 153 }
 154 do_test alter3-2.6 {
 155   catchsql {
 156     alter table t1 add column d DEFAULT CURRENT_TIME;
 157   }
 158 } {1 {Cannot add a column with non-constant default}}
 159 do_test alter3-2.99 {
 160   execsql {
 161     DROP TABLE t1;
 162   }
 163 } {}
 164
 165 do_test alter3-3.1 {
 166   execsql {
 167     CREATE TABLE t1(a, b);
 168     INSERT INTO t1 VALUES(1, 100);
 169     INSERT INTO t1 VALUES(2, 300);
 170     SELECT * FROM t1;
 171   }
 172 } {1 100 2 300}
 173 do_test alter3-3.1 {
 174   execsql {
 175     PRAGMA schema_version = 10;
 176   }
 177 } {}
 178 do_test alter3-3.2 {
 179   execsql {
 180     ALTER TABLE t1 ADD c;
 181     SELECT * FROM t1;
 182   }
 183 } {1 100 {} 2 300 {}}
 184 if {!$has_codec} {
 185   do_test alter3-3.3 {
 186     get_file_format
 187   } {3}
 188 }
 189 ifcapable schema_version {
 190   do_test alter3-3.4 {
 191     execsql {
 192       PRAGMA schema_version;
 193     }
 194   } {11}
 195 }
 196
 197 do_test alter3-4.1 {
 198   db close
 199   forcedelete test.db
 200   set ::DB [sqlite3 db test.db]
 201   sqlite3_db_config db LEGACY_FILE_FORMAT 1
 202   execsql {
 203     CREATE TABLE t1(a, b);
 204     INSERT INTO t1 VALUES(1, 100);
 205     INSERT INTO t1 VALUES(2, 300);
 206     SELECT * FROM t1;
 207   }
 208 } {1 100 2 300}
 209 do_test alter3-4.1 {
 210   execsql {
 211     PRAGMA schema_version = 20;
 212   }
 213 } {}
 214 do_test alter3-4.2 {
 215   execsql {
 216     ALTER TABLE t1 ADD c DEFAULT 'hello world';
 217     SELECT * FROM t1;
 218   }
 219 } {1 100 {hello world} 2 300 {hello world}}
 220 if {!$has_codec} {
 221   do_test alter3-4.3 {
 222     get_file_format
 223   } {3}
 224 }
 225 ifcapable schema_version {
 226   do_test alter3-4.4 {
 227     execsql {
 228       PRAGMA schema_version;
 229     }
 230   } {21}
 231 }
 232 do_test alter3-4.99 {
 233   execsql {
 234     DROP TABLE t1;
 235   }
 236 } {}
 237
 238 ifcapable attach {
 239   do_test alter3-5.1 {
 240     forcedelete test2.db
 241     forcedelete test2.db-journal
 242     execsql {
 243       CREATE TABLE t1(a, b);
 244       INSERT INTO t1 VALUES(1, 'one');
 245       INSERT INTO t1 VALUES(2, 'two');
 246       ATTACH 'test2.db' AS aux;
 247       CREATE TABLE aux.t1 AS SELECT * FROM t1;
 248       PRAGMA aux.schema_version = 30;
 249       SELECT sql FROM aux.sqlite_master;
 250     }
 251   } {{CREATE TABLE t1(a,b)}}
 252   do_test alter3-5.2 {
 253     execsql {
 254       ALTER TABLE aux.t1 ADD COLUMN c VARCHAR(128);
 255       SELECT sql FROM aux.sqlite_master;
 256     }
 257   } {{CREATE TABLE t1(a,b, c VARCHAR(128))}}
 258   do_test alter3-5.3 {
 259     execsql {
 260       SELECT * FROM aux.t1;
 261     }
 262   } {1 one {} 2 two {}}
 263   ifcapable schema_version {
 264     do_test alter3-5.4 {
 265       execsql {
 266         PRAGMA aux.schema_version;
 267       }
 268     } {31}
 269   }
 270   if {!$has_codec} {
 271     do_test alter3-5.5 {
 272       list [get_file_format test2.db] [get_file_format]
 273     } {3 3}
 274   }
 275   do_test alter3-5.6 {
 276     execsql {
 277       ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;
 278       SELECT sql FROM aux.sqlite_master;
 279     }
 280   } {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}
 281   do_test alter3-5.7 {
 282     execsql {
 283       SELECT * FROM aux.t1;
 284     }
 285   } {1 one {} 1000 2 two {} 1000}
 286   ifcapable schema_version {
 287     do_test alter3-5.8 {
 288       execsql {
 289         PRAGMA aux.schema_version;
 290       }
 291     } {32}
 292   }
 293   do_test alter3-5.9 {
 294     execsql {
 295       SELECT * FROM t1;
 296     }
 297   } {1 one 2 two}
 298   do_test alter3-5.99 {
 299     execsql {
 300       DROP TABLE aux.t1;
 301       DROP TABLE t1;
 302     }
 303   } {}
 304 }
 305
 306 #----------------------------------------------------------------
 307 # Test that the table schema is correctly reloaded when a column
 308 # is added to a table.
 309 #
 310 ifcapable trigger&&tempdb {
 311   do_test alter3-6.1 {
 312     execsql {
 313       CREATE TABLE t1(a, b);
 314       CREATE TABLE log(trig, a, b);
 315
 316       CREATE TRIGGER t1_a AFTER INSERT ON t1 BEGIN
 317         INSERT INTO log VALUES('a', new.a, new.b);
 318       END;
 319       CREATE TEMP TRIGGER t1_b AFTER INSERT ON t1 BEGIN
 320         INSERT INTO log VALUES('b', new.a, new.b);
 321       END;
 322
 323       INSERT INTO t1 VALUES(1, 2);
 324       SELECT * FROM log;
 325     }
 326   } {b 1 2 a 1 2}
 327   do_test alter3-6.2 {
 328     execsql {
 329       ALTER TABLE t1 ADD COLUMN c DEFAULT 'c';
 330       INSERT INTO t1(a, b) VALUES(3, 4);
 331       SELECT * FROM log;
 332     }
 333   } {b 1 2 a 1 2 b 3 4 a 3 4}
 334 }
 335
 336 if {!$has_codec} {
 337   ifcapable vacuum {
 338     do_test alter3-7.1 {
 339       execsql {
 340         VACUUM;
 341       }
 342       get_file_format
 343     } {1}
 344     do_test alter3-7.2 {
 345       execsql {
 346         CREATE TABLE abc(a, b, c);
 347         ALTER TABLE abc ADD d DEFAULT NULL;
 348       }
 349       get_file_format
 350     } {3}
 351     do_test alter3-7.3 {
 352       execsql {
 353         ALTER TABLE abc ADD e DEFAULT 10;
 354       }
 355       get_file_format
 356     } {3}
 357     do_test alter3-7.4 {
 358       execsql {
 359         ALTER TABLE abc ADD f DEFAULT NULL;
 360       }
 361       get_file_format
 362     } {3}
 363     do_test alter3-7.5 {
 364       execsql {
 365         VACUUM;
 366       }
 367       get_file_format
 368     } {1}
 369   }
 370 }
 371
 372 # Ticket #1183 - Make sure adding columns to large tables does not cause
 373 # memory corruption (as was the case before this bug was fixed).
 374 do_test alter3-8.1 {
 375   execsql {
 376     CREATE TABLE t4(c1);
 377   }
 378 } {}
 379 set ::sql ""
 380 do_test alter3-8.2 {
 381   set cols c1
 382   for {set i 2} {$i < 100} {incr i} {
 383     execsql "
 384       ALTER TABLE t4 ADD c$i
 385     "
 386     lappend cols c$i
 387   }
 388   set ::sql "CREATE TABLE t4([join $cols {, }])"
 389   list
 390 } {}
 391 do_test alter3-8.2 {
 392   execsql {
 393     SELECT sql FROM sqlite_master WHERE name = 't4';
 394   }
 395 } [list $::sql]
 396
 397 finish_test