1 # 2004 November 10

     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 the ALTER TABLE statement.

    13 #

    14 # $Id: alter.test,v 1.30 2008/05/09 14:17:52 drh Exp $

    15 #

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file.

    21 ifcapable !altertable {

    22   finish_test

    23   return

    24 }

    25 

    26 #----------------------------------------------------------------------

    27 # Test organization:

    28 #

    29 # alter-1.1.* - alter-1.7.*: Basic tests of ALTER TABLE, including tables

    30 #     with implicit and explicit indices. These tests came from an earlier

    31 #     fork of SQLite that also supported ALTER TABLE.

    32 # alter-1.8.*: Tests for ALTER TABLE when the table resides in an 

    33 #     attached database.

    34 # alter-1.9.*: Tests for ALTER TABLE when their is whitespace between the

    35 #     table name and left parenthesis token. i.e: 

    36 #     "CREATE TABLE abc       (a, b, c);"

    37 # alter-2.*: Test error conditions and messages.

    38 # alter-3.*: Test ALTER TABLE on tables that have TRIGGERs attached to them.

    39 # alter-4.*: Test ALTER TABLE on tables that have AUTOINCREMENT fields.

    40 # ...

    41 # alter-12.*: Test ALTER TABLE on views.

    42 #

    43 

    44 # Create some tables to rename.  Be sure to include some TEMP tables

    45 # and some tables with odd names.

    46 #

    47 do_test alter-1.1 {

    48   ifcapable tempdb {

    49     set ::temp TEMP

    50   } else {

    51     set ::temp {}

    52   }

    53   execsql [subst -nocommands {

    54     CREATE TABLE t1(a,b);

    55     INSERT INTO t1 VALUES(1,2);

    56     CREATE TABLE [t1'x1](c UNIQUE, b PRIMARY KEY);

    57     INSERT INTO [t1'x1] VALUES(3,4);

    58     CREATE INDEX t1i1 ON T1(B);

    59     CREATE INDEX t1i2 ON t1(a,b);

    60     CREATE INDEX i3 ON [t1'x1](b,c);

    61     CREATE $::temp TABLE "temp table"(e,f,g UNIQUE);

    62     CREATE INDEX i2 ON [temp table](f);

    63     INSERT INTO [temp table] VALUES(5,6,7);

    64   }]

    65   execsql {

    66     SELECT 't1', * FROM t1;

    67     SELECT 't1''x1', * FROM "t1'x1";

    68     SELECT * FROM [temp table];

    69   }

    70 } {t1 1 2 t1'x1 3 4 5 6 7}

    71 do_test alter-1.2 {

    72   execsql [subst {

    73     CREATE $::temp TABLE objlist(type, name, tbl_name);

    74     INSERT INTO objlist SELECT type, name, tbl_name 

    75         FROM sqlite_master WHERE NAME!='objlist';

    76   }]

    77   ifcapable tempdb {

    78     execsql {

    79       INSERT INTO objlist SELECT type, name, tbl_name 

    80           FROM sqlite_temp_master WHERE NAME!='objlist';

    81     }

    82   }

    83 

    84   execsql {

    85     SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;

    86   }

    87 } [list \

    88      table t1                              t1             \

    89      index t1i1                            t1             \

    90      index t1i2                            t1             \

    91      table t1'x1                           t1'x1          \

    92      index i3                              t1'x1          \

    93      index {sqlite_autoindex_t1'x1_1}      t1'x1          \

    94      index {sqlite_autoindex_t1'x1_2}      t1'x1          \

    95      table {temp table}                    {temp table}   \

    96      index i2                              {temp table}   \

    97      index {sqlite_autoindex_temp table_1} {temp table}   \

    98   ]

    99 

   100 # Make some changes

   101 #

   102 integrity_check alter-1.3.0

   103 do_test alter-1.3 {

   104   execsql {

   105     ALTER TABLE [T1] RENAME to [-t1-];

   106     ALTER TABLE "t1'x1" RENAME TO T2;

   107     ALTER TABLE [temp table] RENAME to TempTab;

   108   }

   109 } {}

   110 integrity_check alter-1.3.1

   111 do_test alter-1.4 {

   112   execsql {

   113     SELECT 't1', * FROM [-t1-];

   114     SELECT 't2', * FROM t2;

   115     SELECT * FROM temptab;

   116   }

   117 } {t1 1 2 t2 3 4 5 6 7}

   118 do_test alter-1.5 {

   119   execsql {

   120     DELETE FROM objlist;

   121     INSERT INTO objlist SELECT type, name, tbl_name

   122         FROM sqlite_master WHERE NAME!='objlist';

   123   }

   124   catchsql {

   125     INSERT INTO objlist SELECT type, name, tbl_name 

   126         FROM sqlite_temp_master WHERE NAME!='objlist';

   127   }

   128   execsql {

   129     SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;

   130   }

   131 } [list \

   132      table -t1-                         -t1-        \

   133      index t1i1                         -t1-        \

   134      index t1i2                         -t1-        \

   135      table T2                           T2          \

   136      index i3                           T2          \

   137      index {sqlite_autoindex_T2_1}      T2          \

   138      index {sqlite_autoindex_T2_2}      T2          \

   139      table {TempTab}                    {TempTab}   \

   140      index i2                           {TempTab}   \

   141      index {sqlite_autoindex_TempTab_1} {TempTab}   \

   142   ]

   143 

   144 # Make sure the changes persist after restarting the database.

   145 # (The TEMP table will not persist, of course.)

   146 #

   147 ifcapable tempdb {

   148   do_test alter-1.6 {

   149     db close

   150     sqlite3 db test.db

   151     set DB [sqlite3_connection_pointer db]

   152     execsql {

   153       CREATE TEMP TABLE objlist(type, name, tbl_name);

   154       INSERT INTO objlist SELECT type, name, tbl_name FROM sqlite_master;

   155       INSERT INTO objlist 

   156           SELECT type, name, tbl_name FROM sqlite_temp_master 

   157           WHERE NAME!='objlist';

   158       SELECT type, name, tbl_name FROM objlist 

   159           ORDER BY tbl_name, type desc, name;

   160     }

   161   } [list \

   162        table -t1-                         -t1-           \

   163        index t1i1                         -t1-           \

   164        index t1i2                         -t1-           \

   165        table T2                           T2          \

   166        index i3                           T2          \

   167        index {sqlite_autoindex_T2_1}      T2          \

   168        index {sqlite_autoindex_T2_2}      T2          \

   169     ]

   170 } else {

   171   execsql {

   172     DROP TABLE TempTab;

   173   }

   174 }

   175 

   176 # Make sure the ALTER TABLE statements work with the

   177 # non-callback API

   178 #

   179 do_test alter-1.7 {

   180   stepsql $DB {

   181     ALTER TABLE [-t1-] RENAME to [*t1*];

   182     ALTER TABLE T2 RENAME TO [<t2>];

   183   }

   184   execsql {

   185     DELETE FROM objlist;

   186     INSERT INTO objlist SELECT type, name, tbl_name

   187         FROM sqlite_master WHERE NAME!='objlist';

   188   }

   189   catchsql {

   190     INSERT INTO objlist SELECT type, name, tbl_name 

   191         FROM sqlite_temp_master WHERE NAME!='objlist';

   192   }

   193   execsql {

   194     SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;

   195   }

   196 } [list \

   197      table *t1*                         *t1*           \

   198      index t1i1                         *t1*           \

   199      index t1i2                         *t1*           \

   200      table <t2>                         <t2>          \

   201      index i3                           <t2>          \

   202      index {sqlite_autoindex_<t2>_1}    <t2>          \

   203      index {sqlite_autoindex_<t2>_2}    <t2>          \

   204   ]

   205 

   206 # Check that ALTER TABLE works on attached databases.

   207 #

   208 ifcapable attach {

   209   do_test alter-1.8.1 {

   210     file delete -force test2.db

   211     file delete -force test2.db-journal

   212     execsql {

   213       ATTACH 'test2.db' AS aux;

   214     }

   215   } {}

   216   do_test alter-1.8.2 {

   217     execsql {

   218       CREATE TABLE t4(a PRIMARY KEY, b, c);

   219       CREATE TABLE aux.t4(a PRIMARY KEY, b, c);

   220       CREATE INDEX i4 ON t4(b);

   221       CREATE INDEX aux.i4 ON t4(b);

   222     }

   223   } {}

   224   do_test alter-1.8.3 {

   225     execsql {

   226       INSERT INTO t4 VALUES('main', 'main', 'main');

   227       INSERT INTO aux.t4 VALUES('aux', 'aux', 'aux');

   228       SELECT * FROM t4 WHERE a = 'main';

   229     }

   230   } {main main main}

   231   do_test alter-1.8.4 {

   232     execsql {

   233       ALTER TABLE t4 RENAME TO t5;

   234       SELECT * FROM t4 WHERE a = 'aux';

   235     }

   236   } {aux aux aux}

   237   do_test alter-1.8.5 {

   238     execsql {

   239       SELECT * FROM t5;

   240     }

   241   } {main main main}

   242   do_test alter-1.8.6 {

   243     execsql {

   244       SELECT * FROM t5 WHERE b = 'main';

   245     }

   246   } {main main main}

   247   do_test alter-1.8.7 {

   248     execsql {

   249       ALTER TABLE aux.t4 RENAME TO t5;

   250       SELECT * FROM aux.t5 WHERE b = 'aux';

   251     }

   252   } {aux aux aux}

   253 }

   254 

   255 do_test alter-1.9.1 {

   256   execsql {

   257     CREATE TABLE tbl1   (a, b, c);

   258     INSERT INTO tbl1 VALUES(1, 2, 3);

   259   }

   260 } {}

   261 do_test alter-1.9.2 {

   262   execsql {

   263     SELECT * FROM tbl1;

   264   }

   265 } {1 2 3}

   266 do_test alter-1.9.3 {

   267   execsql {

   268     ALTER TABLE tbl1 RENAME TO tbl2;

   269     SELECT * FROM tbl2;

   270   }

   271 } {1 2 3}

   272 do_test alter-1.9.4 {

   273   execsql {

   274     DROP TABLE tbl2;

   275   }

   276 } {}

   277 

   278 # Test error messages

   279 #

   280 do_test alter-2.1 {

   281   catchsql {

   282     ALTER TABLE none RENAME TO hi;

   283   }

   284 } {1 {no such table: none}}

   285 do_test alter-2.2 {

   286   execsql {

   287     CREATE TABLE t3(p,q,r);

   288   }

   289   catchsql {

   290     ALTER TABLE [<t2>] RENAME TO t3;

   291   }

   292 } {1 {there is already another table or index with this name: t3}}

   293 do_test alter-2.3 {

   294   catchsql {

   295     ALTER TABLE [<t2>] RENAME TO i3;

   296   }

   297 } {1 {there is already another table or index with this name: i3}}

   298 do_test alter-2.4 {

   299   catchsql {

   300     ALTER TABLE SqLiTe_master RENAME TO master;

   301   }

   302 } {1 {table sqlite_master may not be altered}}

   303 do_test alter-2.5 {

   304   catchsql {

   305     ALTER TABLE t3 RENAME TO sqlite_t3;

   306   }

   307 } {1 {object name reserved for internal use: sqlite_t3}}

   308 do_test alter-2.6 {

   309   catchsql {

   310     ALTER TABLE t3 ADD COLUMN (ALTER TABLE t3 ADD COLUMN);

   311   }

   312 } {1 {near "(": syntax error}}

   313 

   314 # If this compilation does not include triggers, omit the alter-3.* tests.

   315 ifcapable trigger {

   316 

   317 #-----------------------------------------------------------------------

   318 # Tests alter-3.* test ALTER TABLE on tables that have triggers.

   319 #

   320 # alter-3.1.*: ALTER TABLE with triggers.

   321 # alter-3.2.*: Test that the ON keyword cannot be used as a database,

   322 #     table or column name unquoted. This is done because part of the

   323 #     ALTER TABLE code (specifically the implementation of SQL function

   324 #     "sqlite_alter_trigger") will break in this case.

   325 # alter-3.3.*: ALTER TABLE with TEMP triggers (todo).

   326 #

   327 

   328 # An SQL user-function for triggers to fire, so that we know they

   329 # are working.

   330 proc trigfunc {args} {

   331   set ::TRIGGER $args

   332 }

   333 db func trigfunc trigfunc

   334 

   335 do_test alter-3.1.0 {

   336   execsql {

   337     CREATE TABLE t6(a, b, c);

   338     CREATE TRIGGER trig1 AFTER INSERT ON t6 BEGIN

   339       SELECT trigfunc('trig1', new.a, new.b, new.c);

   340     END;

   341   }

   342 } {}

   343 do_test alter-3.1.1 {

   344   execsql {

   345     INSERT INTO t6 VALUES(1, 2, 3);

   346   }

   347   set ::TRIGGER

   348 } {trig1 1 2 3}

   349 do_test alter-3.1.2 {

   350   execsql {

   351     ALTER TABLE t6 RENAME TO t7;

   352     INSERT INTO t7 VALUES(4, 5, 6);

   353   }

   354   set ::TRIGGER

   355 } {trig1 4 5 6}

   356 do_test alter-3.1.3 {

   357   execsql {

   358     DROP TRIGGER trig1;

   359   }

   360 } {}

   361 do_test alter-3.1.4 {

   362   execsql {

   363     CREATE TRIGGER trig2 AFTER INSERT ON main.t7 BEGIN

   364       SELECT trigfunc('trig2', new.a, new.b, new.c);

   365     END;

   366     INSERT INTO t7 VALUES(1, 2, 3);

   367   }

   368   set ::TRIGGER

   369 } {trig2 1 2 3}

   370 do_test alter-3.1.5 {

   371   execsql {

   372     ALTER TABLE t7 RENAME TO t8;

   373     INSERT INTO t8 VALUES(4, 5, 6);

   374   }

   375   set ::TRIGGER

   376 } {trig2 4 5 6}

   377 do_test alter-3.1.6 {

   378   execsql {

   379     DROP TRIGGER trig2;

   380   }

   381 } {}

   382 do_test alter-3.1.7 {

   383   execsql {

   384     CREATE TRIGGER trig3 AFTER INSERT ON main.'t8'BEGIN

   385       SELECT trigfunc('trig3', new.a, new.b, new.c);

   386     END;

   387     INSERT INTO t8 VALUES(1, 2, 3);

   388   }

   389   set ::TRIGGER

   390 } {trig3 1 2 3}

   391 do_test alter-3.1.8 {

   392   execsql {

   393     ALTER TABLE t8 RENAME TO t9;

   394     INSERT INTO t9 VALUES(4, 5, 6);

   395   }

   396   set ::TRIGGER

   397 } {trig3 4 5 6}

   398 

   399 # Make sure "ON" cannot be used as a database, table or column name without

   400 # quoting. Otherwise the sqlite_alter_trigger() function might not work.

   401 file delete -force test3.db

   402 file delete -force test3.db-journal

   403 ifcapable attach {

   404   do_test alter-3.2.1 {

   405     catchsql {

   406       ATTACH 'test3.db' AS ON;

   407     }

   408   } {1 {near "ON": syntax error}}

   409   do_test alter-3.2.2 {

   410     catchsql {

   411       ATTACH 'test3.db' AS 'ON';

   412     }

   413   } {0 {}}

   414   do_test alter-3.2.3 {

   415     catchsql {

   416       CREATE TABLE ON.t1(a, b, c); 

   417     }

   418   } {1 {near "ON": syntax error}}

   419   do_test alter-3.2.4 {

   420     catchsql {

   421       CREATE TABLE 'ON'.t1(a, b, c); 

   422     }

   423   } {0 {}}

   424   do_test alter-3.2.4 {

   425     catchsql {

   426       CREATE TABLE 'ON'.ON(a, b, c); 

   427     }

   428   } {1 {near "ON": syntax error}}

   429   do_test alter-3.2.5 {

   430     catchsql {

   431       CREATE TABLE 'ON'.'ON'(a, b, c); 

   432     }

   433   } {0 {}}

   434 }

   435 do_test alter-3.2.6 {

   436   catchsql {

   437     CREATE TABLE t10(a, ON, c);

   438   }

   439 } {1 {near "ON": syntax error}}

   440 do_test alter-3.2.7 {

   441   catchsql {

   442     CREATE TABLE t10(a, 'ON', c);

   443   }

   444 } {0 {}}

   445 do_test alter-3.2.8 {

   446   catchsql {

   447     CREATE TRIGGER trig4 AFTER INSERT ON ON BEGIN SELECT 1; END;

   448   }

   449 } {1 {near "ON": syntax error}}

   450 ifcapable attach {

   451   do_test alter-3.2.9 {

   452     catchsql {

   453       CREATE TRIGGER 'on'.trig4 AFTER INSERT ON 'ON' BEGIN SELECT 1; END;

   454     }

   455   } {0 {}}

   456 }

   457 do_test alter-3.2.10 {

   458   execsql {

   459     DROP TABLE t10;

   460   }

   461 } {}

   462 

   463 do_test alter-3.3.1 {

   464   execsql [subst {

   465     CREATE TABLE tbl1(a, b, c);

   466     CREATE $::temp TRIGGER trig1 AFTER INSERT ON tbl1 BEGIN

   467       SELECT trigfunc('trig1', new.a, new.b, new.c);

   468     END;

   469   }]

   470 } {}

   471 do_test alter-3.3.2 {

   472   execsql {

   473     INSERT INTO tbl1 VALUES('a', 'b', 'c');

   474   }

   475   set ::TRIGGER

   476 } {trig1 a b c}

   477 do_test alter-3.3.3 {

   478   execsql {

   479     ALTER TABLE tbl1 RENAME TO tbl2;

   480     INSERT INTO tbl2 VALUES('d', 'e', 'f');

   481   } 

   482   set ::TRIGGER

   483 } {trig1 d e f}

   484 do_test alter-3.3.4 {

   485   execsql [subst {

   486     CREATE $::temp TRIGGER trig2 AFTER UPDATE ON tbl2 BEGIN

   487       SELECT trigfunc('trig2', new.a, new.b, new.c);

   488     END;

   489   }] 

   490 } {}

   491 do_test alter-3.3.5 {

   492   execsql {

   493     ALTER TABLE tbl2 RENAME TO tbl3;

   494     INSERT INTO tbl3 VALUES('g', 'h', 'i');

   495   } 

   496   set ::TRIGGER

   497 } {trig1 g h i}

   498 do_test alter-3.3.6 {

   499   execsql {

   500     UPDATE tbl3 SET a = 'G' where a = 'g';

   501   } 

   502   set ::TRIGGER

   503 } {trig2 G h i}

   504 do_test alter-3.3.7 {

   505   execsql {

   506     DROP TABLE tbl3;

   507   }

   508 } {}

   509 ifcapable tempdb {

   510   do_test alter-3.3.8 {

   511     execsql {

   512       SELECT * FROM sqlite_temp_master WHERE type = 'trigger';

   513     }

   514   } {}

   515 }

   516 

   517 } ;# ifcapable trigger

   518 

   519 # If the build does not include AUTOINCREMENT fields, omit alter-4.*.

   520 ifcapable autoinc {

   521 

   522 do_test alter-4.1 {

   523   execsql {

   524     CREATE TABLE tbl1(a INTEGER PRIMARY KEY AUTOINCREMENT);

   525     INSERT INTO tbl1 VALUES(10);

   526   }

   527 } {}

   528 do_test alter-4.2 {

   529   execsql {

   530     INSERT INTO tbl1 VALUES(NULL);

   531     SELECT a FROM tbl1;

   532   }

   533 } {10 11}

   534 do_test alter-4.3 {

   535   execsql {

   536     ALTER TABLE tbl1 RENAME TO tbl2;

   537     DELETE FROM tbl2;

   538     INSERT INTO tbl2 VALUES(NULL);

   539     SELECT a FROM tbl2;

   540   }

   541 } {12}

   542 do_test alter-4.4 {

   543   execsql {

   544     DROP TABLE tbl2;

   545   }

   546 } {}

   547 

   548 } ;# ifcapable autoinc

   549 

   550 # Test that it is Ok to execute an ALTER TABLE immediately after

   551 # opening a database.

   552 do_test alter-5.1 {

   553   execsql {

   554     CREATE TABLE tbl1(a, b, c);

   555     INSERT INTO tbl1 VALUES('x', 'y', 'z');

   556   }

   557 } {}

   558 do_test alter-5.2 {

   559   sqlite3 db2 test.db

   560   execsql {

   561     ALTER TABLE tbl1 RENAME TO tbl2;

   562     SELECT * FROM tbl2;

   563   } db2

   564 } {x y z}

   565 do_test alter-5.3 {

   566   db2 close

   567 } {}

   568 

   569 foreach tblname [execsql {

   570   SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite%'

   571 }] {

   572   execsql "DROP TABLE \"$tblname\""

   573 }

   574 

   575 set ::tbl_name "abc\uABCDdef"

   576 do_test alter-6.1 {

   577   string length $::tbl_name

   578 } {7}

   579 do_test alter-6.2 {

   580   execsql "

   581     CREATE TABLE ${tbl_name}(a, b, c);

   582   "

   583   set ::oid [execsql {SELECT max(oid) FROM sqlite_master}]

   584   execsql "

   585     SELECT sql FROM sqlite_master WHERE oid = $::oid;

   586   "

   587 } "{CREATE TABLE ${::tbl_name}(a, b, c)}"

   588 execsql "

   589   SELECT * FROM ${::tbl_name}

   590 "

   591 set ::tbl_name2 "abcXdef"

   592 do_test alter-6.3 {

   593   execsql "

   594     ALTER TABLE $::tbl_name RENAME TO $::tbl_name2 

   595   "

   596   execsql "

   597     SELECT sql FROM sqlite_master WHERE oid = $::oid

   598   "

   599 } "{CREATE TABLE \"${::tbl_name2}\"(a, b, c)}"

   600 do_test alter-6.4 {

   601   execsql "

   602     ALTER TABLE $::tbl_name2 RENAME TO $::tbl_name

   603   "

   604   execsql "

   605     SELECT sql FROM sqlite_master WHERE oid = $::oid

   606   "

   607 } "{CREATE TABLE \"${::tbl_name}\"(a, b, c)}"

   608 set ::col_name ghi\1234\jkl

   609 do_test alter-6.5 {

   610   execsql "

   611     ALTER TABLE $::tbl_name ADD COLUMN $::col_name VARCHAR

   612   "

   613   execsql "

   614     SELECT sql FROM sqlite_master WHERE oid = $::oid

   615   "

   616 } "{CREATE TABLE \"${::tbl_name}\"(a, b, c, $::col_name VARCHAR)}"

   617 set ::col_name2 B\3421\A

   618 do_test alter-6.6 {

   619   db close

   620   sqlite3 db test.db

   621   execsql "

   622     ALTER TABLE $::tbl_name ADD COLUMN $::col_name2

   623   "

   624   execsql "

   625     SELECT sql FROM sqlite_master WHERE oid = $::oid

   626   "

   627 } "{CREATE TABLE \"${::tbl_name}\"(a, b, c, $::col_name VARCHAR, $::col_name2)}"

   628 do_test alter-6.7 {

   629   execsql "

   630     INSERT INTO ${::tbl_name} VALUES(1, 2, 3, 4, 5);

   631     SELECT $::col_name, $::col_name2 FROM $::tbl_name;

   632   "

   633 } {4 5}

   634 

   635 # Ticket #1665:  Make sure ALTER TABLE ADD COLUMN works on a table

   636 # that includes a COLLATE clause.

   637 #

   638 do_test alter-7.1 {

   639   execsql {

   640     CREATE TABLE t1(a TEXT COLLATE BINARY);

   641     ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;

   642     INSERT INTO t1 VALUES(1,'-2');

   643     INSERT INTO t1 VALUES(5.4e-08,'5.4e-08');

   644     SELECT typeof(a), a, typeof(b), b FROM t1;

   645   }

   646 } {text 1 integer -2 text 5.4e-08 real 5.4e-08}

   647 

   648 # Make sure that when a column is added by ALTER TABLE ADD COLUMN and has

   649 # a default value that the default value is used by aggregate functions.

   650 #

   651 do_test alter-8.1 {

   652   execsql {

   653     CREATE TABLE t2(a INTEGER);

   654     INSERT INTO t2 VALUES(1);

   655     INSERT INTO t2 VALUES(1);

   656     INSERT INTO t2 VALUES(2);

   657     ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;

   658     SELECT sum(b) FROM t2;

   659   }

   660 } {27}

   661 do_test alter-8.2 {

   662   execsql {

   663     SELECT a, sum(b) FROM t2 GROUP BY a;

   664   }

   665 } {1 18 2 9}

   666 

   667 #--------------------------------------------------------------------------

   668 # alter-9.X - Special test: Make sure the sqlite_rename_trigger() and

   669 # rename_table() functions do not crash when handed bad input.

   670 #

   671 ifcapable trigger {

   672   do_test alter-9.1 {

   673     execsql {SELECT SQLITE_RENAME_TRIGGER(0,0)}

   674   } {{}}

   675 }

   676 do_test alter-9.2 {

   677   execsql {

   678     SELECT SQLITE_RENAME_TABLE(0,0);

   679     SELECT SQLITE_RENAME_TABLE(10,20);

   680     SELECT SQLITE_RENAME_TABLE("foo", "foo");

   681   }

   682 } {{} {} {}}

   683 

   684 #------------------------------------------------------------------------

   685 # alter-10.X - Make sure ALTER TABLE works with multi-byte UTF-8 characters 

   686 # in the names.

   687 #

   688 do_test alter-10.1 {

   689   execsql "CREATE TABLE xyz(x UNIQUE)"

   690   execsql "ALTER TABLE xyz RENAME TO xyz\u1234abc"

   691   execsql {SELECT name FROM sqlite_master WHERE name LIKE 'xyz%'}

   692 } [list xyz\u1234abc]

   693 do_test alter-10.2 {

   694   execsql {SELECT name FROM sqlite_master WHERE name LIKE 'sqlite_autoindex%'}

   695 } [list sqlite_autoindex_xyz\u1234abc_1]

   696 do_test alter-10.3 {

   697   execsql "ALTER TABLE xyz\u1234abc RENAME TO xyzabc"

   698   execsql {SELECT name FROM sqlite_master WHERE name LIKE 'xyz%'}

   699 } [list xyzabc]

   700 do_test alter-10.4 {

   701   execsql {SELECT name FROM sqlite_master WHERE name LIKE 'sqlite_autoindex%'}

   702 } [list sqlite_autoindex_xyzabc_1]

   703 

   704 do_test alter-11.1 {

   705   sqlite3_exec db {CREATE TABLE t11(%c6%c6)}

   706   execsql {

   707     ALTER TABLE t11 ADD COLUMN abc;

   708   }

   709   catchsql {

   710     ALTER TABLE t11 ADD COLUMN abc;

   711   }

   712 } {1 {duplicate column name: abc}}

   713 set isutf16 [regexp 16 [db one {PRAGMA encoding}]]

   714 if {!$isutf16} {

   715   do_test alter-11.2 {

   716     execsql {INSERT INTO t11 VALUES(1,2)}

   717     sqlite3_exec db {SELECT %c6%c6 AS xyz, abc FROM t11}

   718   } {0 {xyz abc 1 2}}

   719 }

   720 do_test alter-11.3 {

   721   sqlite3_exec db {CREATE TABLE t11b("%81%82%83" text)}

   722   execsql {

   723     ALTER TABLE t11b ADD COLUMN abc;

   724   }

   725   catchsql {

   726     ALTER TABLE t11b ADD COLUMN abc;

   727   }

   728 } {1 {duplicate column name: abc}}

   729 if {!$isutf16} {

   730   do_test alter-11.4 {

   731     execsql {INSERT INTO t11b VALUES(3,4)}

   732     sqlite3_exec db {SELECT %81%82%83 AS xyz, abc FROM t11b}

   733   } {0 {xyz abc 3 4}}

   734   do_test alter-11.5 {

   735     sqlite3_exec db {SELECT [%81%82%83] AS xyz, abc FROM t11b}

   736   } {0 {xyz abc 3 4}}

   737   do_test alter-11.6 {

   738     sqlite3_exec db {SELECT "%81%82%83" AS xyz, abc FROM t11b}

   739   } {0 {xyz abc 3 4}}

   740 }

   741 do_test alter-11.7 {

   742   sqlite3_exec db {CREATE TABLE t11c(%81%82%83 text)}

   743   execsql {

   744     ALTER TABLE t11c ADD COLUMN abc;

   745   }

   746   catchsql {

   747     ALTER TABLE t11c ADD COLUMN abc;

   748   }

   749 } {1 {duplicate column name: abc}}

   750 if {!$isutf16} {

   751   do_test alter-11.8 {

   752     execsql {INSERT INTO t11c VALUES(5,6)}

   753     sqlite3_exec db {SELECT %81%82%83 AS xyz, abc FROM t11c}

   754   } {0 {xyz abc 5 6}}

   755   do_test alter-11.9 {

   756     sqlite3_exec db {SELECT [%81%82%83] AS xyz, abc FROM t11c}

   757   } {0 {xyz abc 5 6}}

   758   do_test alter-11.10 {

   759     sqlite3_exec db {SELECT "%81%82%83" AS xyz, abc FROM t11c}

   760   } {0 {xyz abc 5 6}}

   761 }

   762 

   763 do_test alter-12.1 {

   764   execsql {

   765     CREATE TABLE t12(a, b, c);

   766     CREATE VIEW v1 AS SELECT * FROM t12;

   767   }

   768 } {}

   769 do_test alter-12.2 {

   770   catchsql {

   771     ALTER TABLE v1 RENAME TO v2;

   772   }

   773 } {1 {view v1 may not be altered}}

   774 do_test alter-12.3 {

   775   execsql { SELECT * FROM v1; }

   776 } {}

   777 do_test alter-12.4 {

   778   db close

   779   sqlite3 db test.db

   780   execsql { SELECT * FROM v1; }

   781 } {}

   782 do_test alter-12.5 {

   783   catchsql { 

   784     ALTER TABLE v1 ADD COLUMN new_column;

   785   }

   786 } {1 {Cannot add a column to a view}}

   787 

   788 # Ticket #3102:

   789 # Verify that comments do not interfere with the table rename

   790 # algorithm.

   791 #

   792 do_test alter-13.1 {

   793   execsql {

   794     CREATE TABLE /* hi */ t3102a(x);

   795     CREATE TABLE t3102b -- comment

   796     (y);

   797     CREATE INDEX t3102c ON t3102a(x);

   798     SELECT name FROM sqlite_master WHERE name LIKE 't3102%' ORDER BY 1;

   799   }

   800 } {t3102a t3102b t3102c}

   801 do_test alter-13.2 {

   802   execsql {

   803     ALTER TABLE t3102a RENAME TO t3102a_rename;

   804     SELECT name FROM sqlite_master WHERE name LIKE 't3102%' ORDER BY 1;

   805   }

   806 } {t3102a_rename t3102b t3102c}

   807 do_test alter-13.3 {

   808   execsql {

   809     ALTER TABLE t3102b RENAME TO t3102b_rename;

   810     SELECT name FROM sqlite_master WHERE name LIKE 't3102%' ORDER BY 1;

   811   }

   812 } {t3102a_rename t3102b_rename t3102c}

   813 

   814 finish_test