test/alter4.test

   1 # 2009 February 2
   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: alter4.test,v 1.1 2009/02/02 18:03:22 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
  30 # Test Organisation:
  31 # ------------------
  32 #
  33 # alter4-1.*: Test that ALTER TABLE correctly modifies the CREATE TABLE sql.
  34 # alter4-2.*: Test error messages.
  35 # alter4-3.*: Test adding columns with default value NULL.
  36 # alter4-4.*: Test adding columns with default values other than NULL.
  37 # alter4-5.*: Test adding columns to tables in ATTACHed databases.
  38 # alter4-6.*: Test that temp triggers are not accidentally dropped.
  39 # alter4-7.*: Test that VACUUM resets the file-format.
  40 #
  41
  42 do_test alter4-1.1 {
  43   execsql {
  44     CREATE TEMP TABLE abc(a, b, c);
  45     SELECT sql FROM sqlite_temp_master;
  46   }
  47 } {{CREATE TABLE abc(a, b, c)}}
  48 do_test alter4-1.1b {
  49   execsql {
  50     SELECT sql FROM temp.sqlite_master;
  51   }
  52 } {{CREATE TABLE abc(a, b, c)}}
  53 do_test alter4-1.2 {
  54   execsql {ALTER TABLE abc ADD d INTEGER;}
  55   execsql {
  56     SELECT sql FROM sqlite_temp_master;
  57   }
  58 } {{CREATE TABLE abc(a, b, c, d INTEGER)}}
  59 do_test alter4-1.2b {
  60   execsql {
  61     SELECT sql FROM temp.sqlite_master;
  62   }
  63 } {{CREATE TABLE abc(a, b, c, d INTEGER)}}
  64 do_test alter4-1.3 {
  65   execsql {ALTER TABLE abc ADD e}
  66   execsql {
  67     SELECT sql FROM sqlite_temp_master;
  68   }
  69 } {{CREATE TABLE abc(a, b, c, d INTEGER, e)}}
  70 do_test alter4-1.3b {
  71   execsql {
  72     SELECT sql FROM temp.sqlite_master;
  73   }
  74 } {{CREATE TABLE abc(a, b, c, d INTEGER, e)}}
  75 do_test alter4-1.4 {
  76   execsql {
  77     CREATE TABLE temp.t1(a, b);
  78     ALTER TABLE t1 ADD c;
  79     SELECT sql FROM sqlite_temp_master WHERE tbl_name = 't1';
  80   }
  81 } {{CREATE TABLE t1(a, b, c)}}
  82 do_test alter4-1.4b {
  83   execsql {
  84     SELECT sql FROM temp.sqlite_master WHERE tbl_name = 't1';
  85   }
  86 } {{CREATE TABLE t1(a, b, c)}}
  87 do_test alter4-1.5 {
  88   execsql {
  89     ALTER TABLE t1 ADD d CHECK (a>d);
  90     SELECT sql FROM sqlite_temp_master WHERE tbl_name = 't1';
  91   }
  92 } {{CREATE TABLE t1(a, b, c, d CHECK (a>d))}}
  93 ifcapable foreignkey {
  94   do_test alter4-1.6 {
  95     execsql {
  96       CREATE TEMP TABLE t2(a, b, UNIQUE(a, b));
  97       ALTER TABLE t2 ADD c REFERENCES t1(c)  ;
  98       SELECT sql FROM sqlite_temp_master
  99        WHERE tbl_name = 't2' AND type = 'table';
 100     }
 101   } {{CREATE TABLE t2(a, b, c REFERENCES t1(c), UNIQUE(a, b))}}
 102 }
 103 do_test alter4-1.7 {
 104   execsql {
 105     CREATE TEMPORARY TABLE t3(a, b, UNIQUE(a, b));
 106     ALTER TABLE t3 ADD COLUMN c VARCHAR(10, 20);
 107     SELECT sql FROM sqlite_temp_master
 108      WHERE tbl_name = 't3' AND type = 'table';
 109   }
 110 } {{CREATE TABLE t3(a, b, c VARCHAR(10, 20), UNIQUE(a, b))}}
 111 do_test alter4-1.99 {
 112   catchsql {
 113     # May not exist if foriegn-keys are omitted at compile time.
 114     DROP TABLE t2;
 115   }
 116   execsql {
 117     DROP TABLE abc;
 118     DROP TABLE t1;
 119     DROP TABLE t3;
 120   }
 121 } {}
 122
 123 do_test alter4-2.1 {
 124   execsql {
 125     CREATE TABLE temp.t1(a, b);
 126     INSERT INTO t1 VALUES(1,2);
 127   }
 128   catchsql {
 129     ALTER TABLE t1 ADD c PRIMARY KEY;
 130   }
 131 } {1 {Cannot add a PRIMARY KEY column}}
 132 do_test alter4-2.2 {
 133   catchsql {
 134     ALTER TABLE t1 ADD c UNIQUE
 135   }
 136 } {1 {Cannot add a UNIQUE column}}
 137 do_test alter4-2.3 {
 138   catchsql {
 139     ALTER TABLE t1 ADD b VARCHAR(10)
 140   }
 141 } {1 {duplicate column name: b}}
 142 do_test alter4-2.3 {
 143   catchsql {
 144     ALTER TABLE t1 ADD c NOT NULL;
 145   }
 146 } {1 {Cannot add a NOT NULL column with default value NULL}}
 147 do_test alter4-2.4 {
 148   catchsql {
 149     ALTER TABLE t1 ADD c NOT NULL DEFAULT 10;
 150   }
 151 } {0 {}}
 152 ifcapable view {
 153   do_test alter4-2.5 {
 154     execsql {
 155       CREATE TEMPORARY VIEW v1 AS SELECT * FROM t1;
 156     }
 157     catchsql {
 158       alter table v1 add column d;
 159     }
 160   } {1 {Cannot add a column to a view}}
 161 }
 162 do_test alter4-2.6 {
 163   catchsql {
 164     alter table t1 add column d DEFAULT CURRENT_TIME;
 165   }
 166 } {1 {Cannot add a column with non-constant default}}
 167 do_test alter4-2.7 {
 168   catchsql {
 169     alter table t1 add column d default (-5+1);
 170   }
 171 } {1 {Cannot add a column with non-constant default}}
 172 do_test alter4-2.99 {
 173   execsql {
 174     DROP TABLE t1;
 175   }
 176 } {}
 177
 178 do_test alter4-3.1 {
 179   execsql {
 180     CREATE TEMP TABLE t1(a, b);
 181     INSERT INTO t1 VALUES(1, 100);
 182     INSERT INTO t1 VALUES(2, 300);
 183     SELECT * FROM t1;
 184   }
 185 } {1 100 2 300}
 186 do_test alter4-3.1 {
 187   execsql {
 188     PRAGMA schema_version = 10;
 189   }
 190 } {}
 191 do_test alter4-3.2 {
 192   execsql {
 193     ALTER TABLE t1 ADD c;
 194     SELECT * FROM t1;
 195   }
 196 } {1 100 {} 2 300 {}}
 197 ifcapable schema_version {
 198   do_test alter4-3.4 {
 199     execsql {
 200       PRAGMA schema_version;
 201     }
 202   } {10}
 203 }
 204
 205 do_test alter4-4.1 {
 206   db close
 207   forcedelete test.db
 208   set ::DB [sqlite3 db test.db]
 209   execsql {
 210     CREATE TEMP TABLE t1(a, b);
 211     INSERT INTO t1 VALUES(1, 100);
 212     INSERT INTO t1 VALUES(2, 300);
 213     SELECT * FROM t1;
 214   }
 215 } {1 100 2 300}
 216 do_test alter4-4.1 {
 217   execsql {
 218     PRAGMA schema_version = 20;
 219   }
 220 } {}
 221 do_test alter4-4.2 {
 222   execsql {
 223     ALTER TABLE t1 ADD c DEFAULT 'hello world';
 224     SELECT * FROM t1;
 225   }
 226 } {1 100 {hello world} 2 300 {hello world}}
 227 ifcapable schema_version {
 228   do_test alter4-4.4 {
 229     execsql {
 230       PRAGMA schema_version;
 231     }
 232   } {20}
 233 }
 234 do_test alter4-4.99 {
 235   execsql {
 236     DROP TABLE t1;
 237   }
 238 } {}
 239
 240 ifcapable attach {
 241   do_test alter4-5.1 {
 242     forcedelete test2.db
 243     forcedelete test2.db-journal
 244     execsql {
 245       CREATE TEMP TABLE t1(a, b);
 246       INSERT INTO t1 VALUES(1, 'one');
 247       INSERT INTO t1 VALUES(2, 'two');
 248       ATTACH 'test2.db' AS aux;
 249       CREATE TABLE aux.t1 AS SELECT * FROM t1;
 250       PRAGMA aux.schema_version = 30;
 251       SELECT sql FROM aux.sqlite_master;
 252     }
 253   } {{CREATE TABLE t1(a,b)}}
 254   do_test alter4-5.2 {
 255     execsql {
 256       ALTER TABLE aux.t1 ADD COLUMN c VARCHAR(128);
 257       SELECT sql FROM aux.sqlite_master;
 258     }
 259   } {{CREATE TABLE t1(a,b, c VARCHAR(128))}}
 260   do_test alter4-5.3 {
 261     execsql {
 262       SELECT * FROM aux.t1;
 263     }
 264   } {1 one {} 2 two {}}
 265   ifcapable schema_version {
 266     do_test alter4-5.4 {
 267       execsql {
 268         PRAGMA aux.schema_version;
 269       }
 270     } {31}
 271   }
 272   do_test alter4-5.6 {
 273     execsql {
 274       ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;
 275       SELECT sql FROM aux.sqlite_master;
 276     }
 277   } {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}
 278   do_test alter4-5.7 {
 279     execsql {
 280       SELECT * FROM aux.t1;
 281     }
 282   } {1 one {} 1000 2 two {} 1000}
 283   ifcapable schema_version {
 284     do_test alter4-5.8 {
 285       execsql {
 286         PRAGMA aux.schema_version;
 287       }
 288     } {32}
 289   }
 290   do_test alter4-5.9 {
 291     execsql {
 292       SELECT * FROM t1;
 293     }
 294   } {1 one 2 two}
 295   do_test alter4-5.99 {
 296     execsql {
 297       DROP TABLE aux.t1;
 298       DROP TABLE t1;
 299     }
 300   } {}
 301 }
 302
 303 #----------------------------------------------------------------
 304 # Test that the table schema is correctly reloaded when a column
 305 # is added to a table.
 306 #
 307 ifcapable trigger&&tempdb {
 308   do_test alter4-6.1 {
 309     execsql {
 310       CREATE TEMP TABLE t1(a, b);
 311       CREATE TEMP TABLE log(trig, a, b);
 312
 313       CREATE TRIGGER t1_a AFTER INSERT ON t1 BEGIN
 314         INSERT INTO log VALUES('a', new.a, new.b);
 315       END;
 316       CREATE TEMP TRIGGER t1_b AFTER INSERT ON t1 BEGIN
 317         INSERT INTO log VALUES('b', new.a, new.b);
 318       END;
 319
 320       INSERT INTO t1 VALUES(1, 2);
 321       SELECT * FROM log ORDER BY trig, a, b;
 322     }
 323   } {a 1 2 b 1 2}
 324   do_test alter4-6.2 {
 325     execsql {
 326       ALTER TABLE t1 ADD COLUMN c DEFAULT 'c';
 327       INSERT INTO t1(a, b) VALUES(3, 4);
 328       SELECT * FROM log ORDER BY trig, a, b;
 329     }
 330   } {a 1 2 a 3 4 b 1 2 b 3 4}
 331 }
 332
 333 # Ticket #1183 - Make sure adding columns to large tables does not cause
 334 # memory corruption (as was the case before this bug was fixed).
 335 do_test alter4-8.1 {
 336   execsql {
 337     CREATE TEMP TABLE t4(c1);
 338   }
 339 } {}
 340 set ::sql ""
 341 do_test alter4-8.2 {
 342   set cols c1
 343   for {set i 2} {$i < 100} {incr i} {
 344     execsql "
 345       ALTER TABLE t4 ADD c$i
 346     "
 347     lappend cols c$i
 348   }
 349   set ::sql "CREATE TABLE t4([join $cols {, }])"
 350   list
 351 } {}
 352 do_test alter4-8.2 {
 353   execsql {
 354     SELECT sql FROM sqlite_temp_master WHERE name = 't4';
 355   }
 356 } [list $::sql]
 357
 358
 359 # Test that a default value equal to -1 multipied by the smallest possible
 360 # 64-bit integer is correctly converted to a real.
 361 do_execsql_test alter4-9.1 {
 362   CREATE TABLE t5(
 363     a INTEGER DEFAULT -9223372036854775808,
 364     b INTEGER DEFAULT (-(-9223372036854775808))
 365   );
 366   INSERT INTO t5 DEFAULT VALUES;
 367 }
 368
 369 do_execsql_test alter4-9.2 { SELECT typeof(a), a, typeof(b), b FROM t5; } {
 370   integer -9223372036854775808
 371   real     9.22337203685478e+18
 372 }
 373
 374 do_execsql_test alter4-9.3 {
 375   ALTER TABLE t5 ADD COLUMN c INTEGER DEFAULT (-(-9223372036854775808));
 376   SELECT typeof(c), c FROM t5;
 377 } {real 9.22337203685478e+18}
 378
 379 # Confirm that doing an ALTER TABLE on a legacy format database
 380 # does not corrupt DESC indexes.
 381 #
 382 # Ticket https://www.sqlite.org/src/tktview/f68bf68513a1c
 383 #
 384 do_test alter4-10.1 {
 385   db close
 386   sqlite3 db :memory:
 387   sqlite3_db_config db LEGACY_FILE_FORMAT 1
 388   db eval {
 389     CREATE TABLE t1(a,b,c);
 390     CREATE INDEX t1a ON t1(a DESC);
 391     INSERT INTO t1 VALUES(1,2,3);
 392     INSERT INTO t1 VALUES(2,3,4);
 393     ALTER TABLE t1 ADD COLUMN d;
 394     PRAGMA integrity_check;
 395   }
 396 } {ok}
 397
 398 reset_db
 399 do_execsql_test alter4-11.0 {
 400   CREATE TABLE t1(c INTEGER PRIMARY KEY, d);
 401   INSERT INTO t1(c,d) VALUES(1,2);
 402   PRAGMA foreign_keys = on;
 403   ALTER TABLE t1 ADD COLUMN e;
 404 }
 405
 406 do_execsql_test alter4-11.1 {
 407   ALTER TABLE t1 ADD COLUMN f REFERENCES t1;
 408 }
 409
 410 do_catchsql_test alter4-11.2 {
 411   ALTER TABLE t1 ADD COLUMN g REFERENCES t1 DEFAULT 4;
 412 } {1 {Cannot add a REFERENCES column with non-NULL default value}}
 413
 414 do_catchsql_test alter4-11.3 {
 415   ALTER TABLE t2 ADD COLUMN g;
 416 } {1 {no such table: t2}}
 417
 418 ifcapable fts5 {
 419   do_execsql_test alter4-11.4 {
 420     CREATE VIRTUAL TABLE fff USING fts5(f);
 421   }
 422   do_catchsql_test alter4-11.2 {
 423     ALTER TABLE fff ADD COLUMN g;
 424   } {1 {virtual tables may not be altered}}
 425 }
 426
 427 finish_test