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   execsql {

    58     CREATE TABLE abc(a, b, c);

    59     SELECT sql FROM sqlite_master;

    60   }

    61 } {{CREATE TABLE abc(a, b, c)}}

    62 do_test alter3-1.2 {

    63   execsql {ALTER TABLE abc ADD d INTEGER;}

    64   execsql {

    65     SELECT sql FROM sqlite_master;

    66   }

    67 } {{CREATE TABLE abc(a, b, c, d INTEGER)}}

    68 do_test alter3-1.3 {

    69   execsql {ALTER TABLE abc ADD e}

    70   execsql {

    71     SELECT sql FROM sqlite_master;

    72   }

    73 } {{CREATE TABLE abc(a, b, c, d INTEGER, e)}}

    74 do_test alter3-1.4 {

    75   execsql {

    76     CREATE TABLE main.t1(a, b);

    77     ALTER TABLE t1 ADD c;

    78     SELECT sql FROM sqlite_master WHERE tbl_name = 't1';

    79   }

    80 } {{CREATE TABLE t1(a, b, c)}}

    81 do_test alter3-1.5 {

    82   execsql {

    83     ALTER TABLE t1 ADD d CHECK (a>d);

    84     SELECT sql FROM sqlite_master WHERE tbl_name = 't1';

    85   }

    86 } {{CREATE TABLE t1(a, b, c, d CHECK (a>d))}}

    87 ifcapable foreignkey {

    88   do_test alter3-1.6 {

    89     execsql {

    90       CREATE TABLE t2(a, b, UNIQUE(a, b));

    91       ALTER TABLE t2 ADD c REFERENCES t1(c)  ;

    92       SELECT sql FROM sqlite_master WHERE tbl_name = 't2' AND type = 'table';

    93     }

    94   } {{CREATE TABLE t2(a, b, c REFERENCES t1(c), UNIQUE(a, b))}}

    95 }

    96 do_test alter3-1.7 {

    97   execsql {

    98     CREATE TABLE t3(a, b, UNIQUE(a, b));

    99     ALTER TABLE t3 ADD COLUMN c VARCHAR(10, 20);

   100     SELECT sql FROM sqlite_master WHERE tbl_name = 't3' AND type = 'table';

   101   }

   102 } {{CREATE TABLE t3(a, b, c VARCHAR(10, 20), UNIQUE(a, b))}}

   103 do_test alter3-1.99 {

   104   catchsql {

   105     # May not exist if foriegn-keys are omitted at compile time.

   106     DROP TABLE t2; 

   107   }

   108   execsql {

   109     DROP TABLE abc; 

   110     DROP TABLE t1; 

   111     DROP TABLE t3; 

   112   }

   113 } {}

   114 

   115 do_test alter3-2.1 {

   116   execsql {

   117     CREATE TABLE t1(a, b);

   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   file delete -force test.db

   199   set ::DB [sqlite3 db test.db]

   200   execsql {

   201     CREATE TABLE t1(a, b);

   202     INSERT INTO t1 VALUES(1, 100);

   203     INSERT INTO t1 VALUES(2, 300);

   204     SELECT * FROM t1;

   205   }

   206 } {1 100 2 300}

   207 do_test alter3-4.1 {

   208   execsql {

   209     PRAGMA schema_version = 20;

   210   }

   211 } {}

   212 do_test alter3-4.2 {

   213   execsql {

   214     ALTER TABLE t1 ADD c DEFAULT 'hello world';

   215     SELECT * FROM t1;

   216   }

   217 } {1 100 {hello world} 2 300 {hello world}}

   218 if {!$has_codec} {

   219   do_test alter3-4.3 {

   220     get_file_format

   221   } {3}

   222 }

   223 ifcapable schema_version {

   224   do_test alter3-4.4 {

   225     execsql {

   226       PRAGMA schema_version;

   227     }

   228   } {21}

   229 }

   230 do_test alter3-4.99 {

   231   execsql {

   232     DROP TABLE t1;

   233   }

   234 } {}

   235 

   236 ifcapable attach {

   237   do_test alter3-5.1 {

   238     file delete -force test2.db

   239     file delete -force test2.db-journal

   240     execsql {

   241       CREATE TABLE t1(a, b);

   242       INSERT INTO t1 VALUES(1, 'one');

   243       INSERT INTO t1 VALUES(2, 'two');

   244       ATTACH 'test2.db' AS aux;

   245       CREATE TABLE aux.t1 AS SELECT * FROM t1;

   246       PRAGMA aux.schema_version = 30;

   247       SELECT sql FROM aux.sqlite_master;

   248     } 

   249   } {{CREATE TABLE t1(a,b)}}

   250   do_test alter3-5.2 {

   251     execsql {

   252       ALTER TABLE aux.t1 ADD COLUMN c VARCHAR(128);

   253       SELECT sql FROM aux.sqlite_master;

   254     }

   255   } {{CREATE TABLE t1(a,b, c VARCHAR(128))}}

   256   do_test alter3-5.3 {

   257     execsql {

   258       SELECT * FROM aux.t1;

   259     }

   260   } {1 one {} 2 two {}}

   261   ifcapable schema_version {

   262     do_test alter3-5.4 {

   263       execsql {

   264         PRAGMA aux.schema_version;

   265       }

   266     } {31}

   267   }

   268   if {!$has_codec} {

   269     do_test alter3-5.5 {

   270       list [get_file_format test2.db] [get_file_format]

   271     } {2 3}

   272   }

   273   do_test alter3-5.6 {

   274     execsql {

   275       ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;

   276       SELECT sql FROM aux.sqlite_master;

   277     }

   278   } {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}

   279   do_test alter3-5.7 {

   280     execsql {

   281       SELECT * FROM aux.t1;

   282     }

   283   } {1 one {} 1000 2 two {} 1000}

   284   ifcapable schema_version {

   285     do_test alter3-5.8 {

   286       execsql {

   287         PRAGMA aux.schema_version;

   288       }

   289     } {32}

   290   }

   291   do_test alter3-5.9 {

   292     execsql {

   293       SELECT * FROM t1;

   294     }

   295   } {1 one 2 two}

   296   do_test alter3-5.99 {

   297     execsql {

   298       DROP TABLE aux.t1;

   299       DROP TABLE t1;

   300     }

   301   } {}

   302 }

   303 

   304 #----------------------------------------------------------------

   305 # Test that the table schema is correctly reloaded when a column

   306 # is added to a table.

   307 #

   308 ifcapable trigger&&tempdb {

   309   do_test alter3-6.1 {

   310     execsql {

   311       CREATE TABLE t1(a, b);

   312       CREATE TABLE log(trig, a, b);

   313 

   314       CREATE TRIGGER t1_a AFTER INSERT ON t1 BEGIN

   315         INSERT INTO log VALUES('a', new.a, new.b);

   316       END;

   317       CREATE TEMP TRIGGER t1_b AFTER INSERT ON t1 BEGIN

   318         INSERT INTO log VALUES('b', new.a, new.b);

   319       END;

   320   

   321       INSERT INTO t1 VALUES(1, 2);

   322       SELECT * FROM log;

   323     }

   324   } {b 1 2 a 1 2}

   325   do_test alter3-6.2 {

   326     execsql {

   327       ALTER TABLE t1 ADD COLUMN c DEFAULT 'c';

   328       INSERT INTO t1(a, b) VALUES(3, 4);

   329       SELECT * FROM log;

   330     }

   331   } {b 1 2 a 1 2 b 3 4 a 3 4}

   332 }

   333 

   334 if {!$has_codec} {

   335   ifcapable vacuum {

   336     do_test alter3-7.1 {

   337       execsql {

   338         VACUUM;

   339       }

   340       get_file_format

   341     } {1}

   342     do_test alter3-7.2 {

   343       execsql {

   344         CREATE TABLE abc(a, b, c);

   345         ALTER TABLE abc ADD d DEFAULT NULL;

   346       }

   347       get_file_format

   348     } {2}

   349     do_test alter3-7.3 {

   350       execsql {

   351         ALTER TABLE abc ADD e DEFAULT 10;

   352       }

   353       get_file_format

   354     } {3}

   355     do_test alter3-7.4 {

   356       execsql {

   357         ALTER TABLE abc ADD f DEFAULT NULL;

   358       }

   359       get_file_format

   360     } {3}

   361     do_test alter3-7.5 {

   362       execsql {

   363         VACUUM;

   364       }

   365       get_file_format

   366     } {1}

   367   }

   368 }

   369 

   370 # Ticket #1183 - Make sure adding columns to large tables does not cause

   371 # memory corruption (as was the case before this bug was fixed).

   372 do_test alter3-8.1 {

   373   execsql {

   374     CREATE TABLE t4(c1);

   375   }

   376 } {}

   377 set ::sql ""

   378 do_test alter3-8.2 {

   379   set cols c1

   380   for {set i 2} {$i < 100} {incr i} {

   381     execsql "

   382       ALTER TABLE t4 ADD c$i

   383     "

   384     lappend cols c$i

   385   }

   386   set ::sql "CREATE TABLE t4([join $cols {, }])"

   387   list 

   388 } {}

   389 do_test alter3-8.2 {

   390   execsql {

   391     SELECT sql FROM sqlite_master WHERE name = 't4';

   392   }

   393 } [list $::sql]

   394 

   395 finish_test