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.2 {
  49   execsql {ALTER TABLE abc ADD d INTEGER;}
  50   execsql {
  51     SELECT sql FROM sqlite_temp_master;
  52   }
  53 } {{CREATE TABLE abc(a, b, c, d INTEGER)}}
  54 do_test alter4-1.3 {
  55   execsql {ALTER TABLE abc ADD e}
  56   execsql {
  57     SELECT sql FROM sqlite_temp_master;
  58   }
  59 } {{CREATE TABLE abc(a, b, c, d INTEGER, e)}}
  60 do_test alter4-1.4 {
  61   execsql {
  62     CREATE TABLE temp.t1(a, b);
  63     ALTER TABLE t1 ADD c;
  64     SELECT sql FROM sqlite_temp_master WHERE tbl_name = 't1';
  65   }
  66 } {{CREATE TABLE t1(a, b, c)}}
  67 do_test alter4-1.5 {
  68   execsql {
  69     ALTER TABLE t1 ADD d CHECK (a>d);
  70     SELECT sql FROM sqlite_temp_master WHERE tbl_name = 't1';
  71   }
  72 } {{CREATE TABLE t1(a, b, c, d CHECK (a>d))}}
  73 ifcapable foreignkey {
  74   do_test alter4-1.6 {
  75     execsql {
  76       CREATE TEMP TABLE t2(a, b, UNIQUE(a, b));
  77       ALTER TABLE t2 ADD c REFERENCES t1(c)  ;
  78       SELECT sql FROM sqlite_temp_master
  79        WHERE tbl_name = 't2' AND type = 'table';
  80     }
  81   } {{CREATE TABLE t2(a, b, c REFERENCES t1(c), UNIQUE(a, b))}}
  82 }
  83 do_test alter4-1.7 {
  84   execsql {
  85     CREATE TEMPORARY TABLE t3(a, b, UNIQUE(a, b));
  86     ALTER TABLE t3 ADD COLUMN c VARCHAR(10, 20);
  87     SELECT sql FROM sqlite_temp_master
  88      WHERE tbl_name = 't3' AND type = 'table';
  89   }
  90 } {{CREATE TABLE t3(a, b, c VARCHAR(10, 20), UNIQUE(a, b))}}
  91 do_test alter4-1.99 {
  92   catchsql {
  93     # May not exist if foriegn-keys are omitted at compile time.
  94     DROP TABLE t2;
  95   }
  96   execsql {
  97     DROP TABLE abc;
  98     DROP TABLE t1;
  99     DROP TABLE t3;
 100   }
 101 } {}
 102
 103 do_test alter4-2.1 {
 104   execsql {
 105     CREATE TABLE temp.t1(a, b);
 106   }
 107   catchsql {
 108     ALTER TABLE t1 ADD c PRIMARY KEY;
 109   }
 110 } {1 {Cannot add a PRIMARY KEY column}}
 111 do_test alter4-2.2 {
 112   catchsql {
 113     ALTER TABLE t1 ADD c UNIQUE
 114   }
 115 } {1 {Cannot add a UNIQUE column}}
 116 do_test alter4-2.3 {
 117   catchsql {
 118     ALTER TABLE t1 ADD b VARCHAR(10)
 119   }
 120 } {1 {duplicate column name: b}}
 121 do_test alter4-2.3 {
 122   catchsql {
 123     ALTER TABLE t1 ADD c NOT NULL;
 124   }
 125 } {1 {Cannot add a NOT NULL column with default value NULL}}
 126 do_test alter4-2.4 {
 127   catchsql {
 128     ALTER TABLE t1 ADD c NOT NULL DEFAULT 10;
 129   }
 130 } {0 {}}
 131 ifcapable view {
 132   do_test alter4-2.5 {
 133     execsql {
 134       CREATE TEMPORARY VIEW v1 AS SELECT * FROM t1;
 135     }
 136     catchsql {
 137       alter table v1 add column d;
 138     }
 139   } {1 {Cannot add a column to a view}}
 140 }
 141 do_test alter4-2.6 {
 142   catchsql {
 143     alter table t1 add column d DEFAULT CURRENT_TIME;
 144   }
 145 } {1 {Cannot add a column with non-constant default}}
 146 do_test alter4-2.7 {
 147   catchsql {
 148     alter table t1 add column d default (-5+1);
 149   }
 150 } {1 {Cannot add a column with non-constant default}}
 151 do_test alter4-2.99 {
 152   execsql {
 153     DROP TABLE t1;
 154   }
 155 } {}
 156
 157 do_test alter4-3.1 {
 158   execsql {
 159     CREATE TEMP TABLE t1(a, b);
 160     INSERT INTO t1 VALUES(1, 100);
 161     INSERT INTO t1 VALUES(2, 300);
 162     SELECT * FROM t1;
 163   }
 164 } {1 100 2 300}
 165 do_test alter4-3.1 {
 166   execsql {
 167     PRAGMA schema_version = 10;
 168   }
 169 } {}
 170 do_test alter4-3.2 {
 171   execsql {
 172     ALTER TABLE t1 ADD c;
 173     SELECT * FROM t1;
 174   }
 175 } {1 100 {} 2 300 {}}
 176 ifcapable schema_version {
 177   do_test alter4-3.4 {
 178     execsql {
 179       PRAGMA schema_version;
 180     }
 181   } {10}
 182 }
 183
 184 do_test alter4-4.1 {
 185   db close
 186   forcedelete test.db
 187   set ::DB [sqlite3 db test.db]
 188   execsql {
 189     CREATE TEMP TABLE t1(a, b);
 190     INSERT INTO t1 VALUES(1, 100);
 191     INSERT INTO t1 VALUES(2, 300);
 192     SELECT * FROM t1;
 193   }
 194 } {1 100 2 300}
 195 do_test alter4-4.1 {
 196   execsql {
 197     PRAGMA schema_version = 20;
 198   }
 199 } {}
 200 do_test alter4-4.2 {
 201   execsql {
 202     ALTER TABLE t1 ADD c DEFAULT 'hello world';
 203     SELECT * FROM t1;
 204   }
 205 } {1 100 {hello world} 2 300 {hello world}}
 206 ifcapable schema_version {
 207   do_test alter4-4.4 {
 208     execsql {
 209       PRAGMA schema_version;
 210     }
 211   } {20}
 212 }
 213 do_test alter4-4.99 {
 214   execsql {
 215     DROP TABLE t1;
 216   }
 217 } {}
 218
 219 ifcapable attach {
 220   do_test alter4-5.1 {
 221     forcedelete test2.db
 222     forcedelete test2.db-journal
 223     execsql {
 224       CREATE TEMP TABLE t1(a, b);
 225       INSERT INTO t1 VALUES(1, 'one');
 226       INSERT INTO t1 VALUES(2, 'two');
 227       ATTACH 'test2.db' AS aux;
 228       CREATE TABLE aux.t1 AS SELECT * FROM t1;
 229       PRAGMA aux.schema_version = 30;
 230       SELECT sql FROM aux.sqlite_master;
 231     }
 232   } {{CREATE TABLE t1(a,b)}}
 233   do_test alter4-5.2 {
 234     execsql {
 235       ALTER TABLE aux.t1 ADD COLUMN c VARCHAR(128);
 236       SELECT sql FROM aux.sqlite_master;
 237     }
 238   } {{CREATE TABLE t1(a,b, c VARCHAR(128))}}
 239   do_test alter4-5.3 {
 240     execsql {
 241       SELECT * FROM aux.t1;
 242     }
 243   } {1 one {} 2 two {}}
 244   ifcapable schema_version {
 245     do_test alter4-5.4 {
 246       execsql {
 247         PRAGMA aux.schema_version;
 248       }
 249     } {31}
 250   }
 251   do_test alter4-5.6 {
 252     execsql {
 253       ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;
 254       SELECT sql FROM aux.sqlite_master;
 255     }
 256   } {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}
 257   do_test alter4-5.7 {
 258     execsql {
 259       SELECT * FROM aux.t1;
 260     }
 261   } {1 one {} 1000 2 two {} 1000}
 262   ifcapable schema_version {
 263     do_test alter4-5.8 {
 264       execsql {
 265         PRAGMA aux.schema_version;
 266       }
 267     } {32}
 268   }
 269   do_test alter4-5.9 {
 270     execsql {
 271       SELECT * FROM t1;
 272     }
 273   } {1 one 2 two}
 274   do_test alter4-5.99 {
 275     execsql {
 276       DROP TABLE aux.t1;
 277       DROP TABLE t1;
 278     }
 279   } {}
 280 }
 281
 282 #----------------------------------------------------------------
 283 # Test that the table schema is correctly reloaded when a column
 284 # is added to a table.
 285 #
 286 ifcapable trigger&&tempdb {
 287   do_test alter4-6.1 {
 288     execsql {
 289       CREATE TEMP TABLE t1(a, b);
 290       CREATE TEMP TABLE log(trig, a, b);
 291
 292       CREATE TRIGGER t1_a AFTER INSERT ON t1 BEGIN
 293         INSERT INTO log VALUES('a', new.a, new.b);
 294       END;
 295       CREATE TEMP TRIGGER t1_b AFTER INSERT ON t1 BEGIN
 296         INSERT INTO log VALUES('b', new.a, new.b);
 297       END;
 298
 299       INSERT INTO t1 VALUES(1, 2);
 300       SELECT * FROM log;
 301     }
 302   } {b 1 2 a 1 2}
 303   do_test alter4-6.2 {
 304     execsql {
 305       ALTER TABLE t1 ADD COLUMN c DEFAULT 'c';
 306       INSERT INTO t1(a, b) VALUES(3, 4);
 307       SELECT * FROM log;
 308     }
 309   } {b 1 2 a 1 2 b 3 4 a 3 4}
 310 }
 311
 312 # Ticket #1183 - Make sure adding columns to large tables does not cause
 313 # memory corruption (as was the case before this bug was fixed).
 314 do_test alter4-8.1 {
 315   execsql {
 316     CREATE TEMP TABLE t4(c1);
 317   }
 318 } {}
 319 set ::sql ""
 320 do_test alter4-8.2 {
 321   set cols c1
 322   for {set i 2} {$i < 100} {incr i} {
 323     execsql "
 324       ALTER TABLE t4 ADD c$i
 325     "
 326     lappend cols c$i
 327   }
 328   set ::sql "CREATE TABLE t4([join $cols {, }])"
 329   list
 330 } {}
 331 do_test alter4-8.2 {
 332   execsql {
 333     SELECT sql FROM sqlite_temp_master WHERE name = 't4';
 334   }
 335 } [list $::sql]
 336
 337
 338 # Test that a default value equal to -1 multipied by the smallest possible
 339 # 64-bit integer is correctly converted to a real.
 340 do_execsql_test alter4-9.1 {
 341   CREATE TABLE t5(
 342     a INTEGER DEFAULT -9223372036854775808,
 343     b INTEGER DEFAULT (-(-9223372036854775808))
 344   );
 345   INSERT INTO t5 DEFAULT VALUES;
 346 }
 347
 348 do_execsql_test alter4-9.2 { SELECT typeof(a), a, typeof(b), b FROM t5; } {
 349   integer -9223372036854775808
 350   real     9.22337203685478e+18
 351 }
 352
 353 do_execsql_test alter4-9.3 {
 354   ALTER TABLE t5 ADD COLUMN c INTEGER DEFAULT (-(-9223372036854775808));
 355   SELECT typeof(c), c FROM t5;
 356 } {real 9.22337203685478e+18}
 357
 358 finish_test