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