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