1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/alter.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,814 @@
1.4 +# 2004 November 10
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#*************************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this script is testing the ALTER TABLE statement.
1.16 +#
1.17 +# $Id: alter.test,v 1.30 2008/05/09 14:17:52 drh Exp $
1.18 +#
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
1.24 +ifcapable !altertable {
1.25 + finish_test
1.26 + return
1.27 +}
1.28 +
1.29 +#----------------------------------------------------------------------
1.30 +# Test organization:
1.31 +#
1.32 +# alter-1.1.* - alter-1.7.*: Basic tests of ALTER TABLE, including tables
1.33 +# with implicit and explicit indices. These tests came from an earlier
1.34 +# fork of SQLite that also supported ALTER TABLE.
1.35 +# alter-1.8.*: Tests for ALTER TABLE when the table resides in an
1.36 +# attached database.
1.37 +# alter-1.9.*: Tests for ALTER TABLE when their is whitespace between the
1.38 +# table name and left parenthesis token. i.e:
1.39 +# "CREATE TABLE abc (a, b, c);"
1.40 +# alter-2.*: Test error conditions and messages.
1.41 +# alter-3.*: Test ALTER TABLE on tables that have TRIGGERs attached to them.
1.42 +# alter-4.*: Test ALTER TABLE on tables that have AUTOINCREMENT fields.
1.43 +# ...
1.44 +# alter-12.*: Test ALTER TABLE on views.
1.45 +#
1.46 +
1.47 +# Create some tables to rename. Be sure to include some TEMP tables
1.48 +# and some tables with odd names.
1.49 +#
1.50 +do_test alter-1.1 {
1.51 + ifcapable tempdb {
1.52 + set ::temp TEMP
1.53 + } else {
1.54 + set ::temp {}
1.55 + }
1.56 + execsql [subst -nocommands {
1.57 + CREATE TABLE t1(a,b);
1.58 + INSERT INTO t1 VALUES(1,2);
1.59 + CREATE TABLE [t1'x1](c UNIQUE, b PRIMARY KEY);
1.60 + INSERT INTO [t1'x1] VALUES(3,4);
1.61 + CREATE INDEX t1i1 ON T1(B);
1.62 + CREATE INDEX t1i2 ON t1(a,b);
1.63 + CREATE INDEX i3 ON [t1'x1](b,c);
1.64 + CREATE $::temp TABLE "temp table"(e,f,g UNIQUE);
1.65 + CREATE INDEX i2 ON [temp table](f);
1.66 + INSERT INTO [temp table] VALUES(5,6,7);
1.67 + }]
1.68 + execsql {
1.69 + SELECT 't1', * FROM t1;
1.70 + SELECT 't1''x1', * FROM "t1'x1";
1.71 + SELECT * FROM [temp table];
1.72 + }
1.73 +} {t1 1 2 t1'x1 3 4 5 6 7}
1.74 +do_test alter-1.2 {
1.75 + execsql [subst {
1.76 + CREATE $::temp TABLE objlist(type, name, tbl_name);
1.77 + INSERT INTO objlist SELECT type, name, tbl_name
1.78 + FROM sqlite_master WHERE NAME!='objlist';
1.79 + }]
1.80 + ifcapable tempdb {
1.81 + execsql {
1.82 + INSERT INTO objlist SELECT type, name, tbl_name
1.83 + FROM sqlite_temp_master WHERE NAME!='objlist';
1.84 + }
1.85 + }
1.86 +
1.87 + execsql {
1.88 + SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;
1.89 + }
1.90 +} [list \
1.91 + table t1 t1 \
1.92 + index t1i1 t1 \
1.93 + index t1i2 t1 \
1.94 + table t1'x1 t1'x1 \
1.95 + index i3 t1'x1 \
1.96 + index {sqlite_autoindex_t1'x1_1} t1'x1 \
1.97 + index {sqlite_autoindex_t1'x1_2} t1'x1 \
1.98 + table {temp table} {temp table} \
1.99 + index i2 {temp table} \
1.100 + index {sqlite_autoindex_temp table_1} {temp table} \
1.101 + ]
1.102 +
1.103 +# Make some changes
1.104 +#
1.105 +integrity_check alter-1.3.0
1.106 +do_test alter-1.3 {
1.107 + execsql {
1.108 + ALTER TABLE [T1] RENAME to [-t1-];
1.109 + ALTER TABLE "t1'x1" RENAME TO T2;
1.110 + ALTER TABLE [temp table] RENAME to TempTab;
1.111 + }
1.112 +} {}
1.113 +integrity_check alter-1.3.1
1.114 +do_test alter-1.4 {
1.115 + execsql {
1.116 + SELECT 't1', * FROM [-t1-];
1.117 + SELECT 't2', * FROM t2;
1.118 + SELECT * FROM temptab;
1.119 + }
1.120 +} {t1 1 2 t2 3 4 5 6 7}
1.121 +do_test alter-1.5 {
1.122 + execsql {
1.123 + DELETE FROM objlist;
1.124 + INSERT INTO objlist SELECT type, name, tbl_name
1.125 + FROM sqlite_master WHERE NAME!='objlist';
1.126 + }
1.127 + catchsql {
1.128 + INSERT INTO objlist SELECT type, name, tbl_name
1.129 + FROM sqlite_temp_master WHERE NAME!='objlist';
1.130 + }
1.131 + execsql {
1.132 + SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;
1.133 + }
1.134 +} [list \
1.135 + table -t1- -t1- \
1.136 + index t1i1 -t1- \
1.137 + index t1i2 -t1- \
1.138 + table T2 T2 \
1.139 + index i3 T2 \
1.140 + index {sqlite_autoindex_T2_1} T2 \
1.141 + index {sqlite_autoindex_T2_2} T2 \
1.142 + table {TempTab} {TempTab} \
1.143 + index i2 {TempTab} \
1.144 + index {sqlite_autoindex_TempTab_1} {TempTab} \
1.145 + ]
1.146 +
1.147 +# Make sure the changes persist after restarting the database.
1.148 +# (The TEMP table will not persist, of course.)
1.149 +#
1.150 +ifcapable tempdb {
1.151 + do_test alter-1.6 {
1.152 + db close
1.153 + sqlite3 db test.db
1.154 + set DB [sqlite3_connection_pointer db]
1.155 + execsql {
1.156 + CREATE TEMP TABLE objlist(type, name, tbl_name);
1.157 + INSERT INTO objlist SELECT type, name, tbl_name FROM sqlite_master;
1.158 + INSERT INTO objlist
1.159 + SELECT type, name, tbl_name FROM sqlite_temp_master
1.160 + WHERE NAME!='objlist';
1.161 + SELECT type, name, tbl_name FROM objlist
1.162 + ORDER BY tbl_name, type desc, name;
1.163 + }
1.164 + } [list \
1.165 + table -t1- -t1- \
1.166 + index t1i1 -t1- \
1.167 + index t1i2 -t1- \
1.168 + table T2 T2 \
1.169 + index i3 T2 \
1.170 + index {sqlite_autoindex_T2_1} T2 \
1.171 + index {sqlite_autoindex_T2_2} T2 \
1.172 + ]
1.173 +} else {
1.174 + execsql {
1.175 + DROP TABLE TempTab;
1.176 + }
1.177 +}
1.178 +
1.179 +# Make sure the ALTER TABLE statements work with the
1.180 +# non-callback API
1.181 +#
1.182 +do_test alter-1.7 {
1.183 + stepsql $DB {
1.184 + ALTER TABLE [-t1-] RENAME to [*t1*];
1.185 + ALTER TABLE T2 RENAME TO [<t2>];
1.186 + }
1.187 + execsql {
1.188 + DELETE FROM objlist;
1.189 + INSERT INTO objlist SELECT type, name, tbl_name
1.190 + FROM sqlite_master WHERE NAME!='objlist';
1.191 + }
1.192 + catchsql {
1.193 + INSERT INTO objlist SELECT type, name, tbl_name
1.194 + FROM sqlite_temp_master WHERE NAME!='objlist';
1.195 + }
1.196 + execsql {
1.197 + SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;
1.198 + }
1.199 +} [list \
1.200 + table *t1* *t1* \
1.201 + index t1i1 *t1* \
1.202 + index t1i2 *t1* \
1.203 + table <t2> <t2> \
1.204 + index i3 <t2> \
1.205 + index {sqlite_autoindex_<t2>_1} <t2> \
1.206 + index {sqlite_autoindex_<t2>_2} <t2> \
1.207 + ]
1.208 +
1.209 +# Check that ALTER TABLE works on attached databases.
1.210 +#
1.211 +ifcapable attach {
1.212 + do_test alter-1.8.1 {
1.213 + file delete -force test2.db
1.214 + file delete -force test2.db-journal
1.215 + execsql {
1.216 + ATTACH 'test2.db' AS aux;
1.217 + }
1.218 + } {}
1.219 + do_test alter-1.8.2 {
1.220 + execsql {
1.221 + CREATE TABLE t4(a PRIMARY KEY, b, c);
1.222 + CREATE TABLE aux.t4(a PRIMARY KEY, b, c);
1.223 + CREATE INDEX i4 ON t4(b);
1.224 + CREATE INDEX aux.i4 ON t4(b);
1.225 + }
1.226 + } {}
1.227 + do_test alter-1.8.3 {
1.228 + execsql {
1.229 + INSERT INTO t4 VALUES('main', 'main', 'main');
1.230 + INSERT INTO aux.t4 VALUES('aux', 'aux', 'aux');
1.231 + SELECT * FROM t4 WHERE a = 'main';
1.232 + }
1.233 + } {main main main}
1.234 + do_test alter-1.8.4 {
1.235 + execsql {
1.236 + ALTER TABLE t4 RENAME TO t5;
1.237 + SELECT * FROM t4 WHERE a = 'aux';
1.238 + }
1.239 + } {aux aux aux}
1.240 + do_test alter-1.8.5 {
1.241 + execsql {
1.242 + SELECT * FROM t5;
1.243 + }
1.244 + } {main main main}
1.245 + do_test alter-1.8.6 {
1.246 + execsql {
1.247 + SELECT * FROM t5 WHERE b = 'main';
1.248 + }
1.249 + } {main main main}
1.250 + do_test alter-1.8.7 {
1.251 + execsql {
1.252 + ALTER TABLE aux.t4 RENAME TO t5;
1.253 + SELECT * FROM aux.t5 WHERE b = 'aux';
1.254 + }
1.255 + } {aux aux aux}
1.256 +}
1.257 +
1.258 +do_test alter-1.9.1 {
1.259 + execsql {
1.260 + CREATE TABLE tbl1 (a, b, c);
1.261 + INSERT INTO tbl1 VALUES(1, 2, 3);
1.262 + }
1.263 +} {}
1.264 +do_test alter-1.9.2 {
1.265 + execsql {
1.266 + SELECT * FROM tbl1;
1.267 + }
1.268 +} {1 2 3}
1.269 +do_test alter-1.9.3 {
1.270 + execsql {
1.271 + ALTER TABLE tbl1 RENAME TO tbl2;
1.272 + SELECT * FROM tbl2;
1.273 + }
1.274 +} {1 2 3}
1.275 +do_test alter-1.9.4 {
1.276 + execsql {
1.277 + DROP TABLE tbl2;
1.278 + }
1.279 +} {}
1.280 +
1.281 +# Test error messages
1.282 +#
1.283 +do_test alter-2.1 {
1.284 + catchsql {
1.285 + ALTER TABLE none RENAME TO hi;
1.286 + }
1.287 +} {1 {no such table: none}}
1.288 +do_test alter-2.2 {
1.289 + execsql {
1.290 + CREATE TABLE t3(p,q,r);
1.291 + }
1.292 + catchsql {
1.293 + ALTER TABLE [<t2>] RENAME TO t3;
1.294 + }
1.295 +} {1 {there is already another table or index with this name: t3}}
1.296 +do_test alter-2.3 {
1.297 + catchsql {
1.298 + ALTER TABLE [<t2>] RENAME TO i3;
1.299 + }
1.300 +} {1 {there is already another table or index with this name: i3}}
1.301 +do_test alter-2.4 {
1.302 + catchsql {
1.303 + ALTER TABLE SqLiTe_master RENAME TO master;
1.304 + }
1.305 +} {1 {table sqlite_master may not be altered}}
1.306 +do_test alter-2.5 {
1.307 + catchsql {
1.308 + ALTER TABLE t3 RENAME TO sqlite_t3;
1.309 + }
1.310 +} {1 {object name reserved for internal use: sqlite_t3}}
1.311 +do_test alter-2.6 {
1.312 + catchsql {
1.313 + ALTER TABLE t3 ADD COLUMN (ALTER TABLE t3 ADD COLUMN);
1.314 + }
1.315 +} {1 {near "(": syntax error}}
1.316 +
1.317 +# If this compilation does not include triggers, omit the alter-3.* tests.
1.318 +ifcapable trigger {
1.319 +
1.320 +#-----------------------------------------------------------------------
1.321 +# Tests alter-3.* test ALTER TABLE on tables that have triggers.
1.322 +#
1.323 +# alter-3.1.*: ALTER TABLE with triggers.
1.324 +# alter-3.2.*: Test that the ON keyword cannot be used as a database,
1.325 +# table or column name unquoted. This is done because part of the
1.326 +# ALTER TABLE code (specifically the implementation of SQL function
1.327 +# "sqlite_alter_trigger") will break in this case.
1.328 +# alter-3.3.*: ALTER TABLE with TEMP triggers (todo).
1.329 +#
1.330 +
1.331 +# An SQL user-function for triggers to fire, so that we know they
1.332 +# are working.
1.333 +proc trigfunc {args} {
1.334 + set ::TRIGGER $args
1.335 +}
1.336 +db func trigfunc trigfunc
1.337 +
1.338 +do_test alter-3.1.0 {
1.339 + execsql {
1.340 + CREATE TABLE t6(a, b, c);
1.341 + CREATE TRIGGER trig1 AFTER INSERT ON t6 BEGIN
1.342 + SELECT trigfunc('trig1', new.a, new.b, new.c);
1.343 + END;
1.344 + }
1.345 +} {}
1.346 +do_test alter-3.1.1 {
1.347 + execsql {
1.348 + INSERT INTO t6 VALUES(1, 2, 3);
1.349 + }
1.350 + set ::TRIGGER
1.351 +} {trig1 1 2 3}
1.352 +do_test alter-3.1.2 {
1.353 + execsql {
1.354 + ALTER TABLE t6 RENAME TO t7;
1.355 + INSERT INTO t7 VALUES(4, 5, 6);
1.356 + }
1.357 + set ::TRIGGER
1.358 +} {trig1 4 5 6}
1.359 +do_test alter-3.1.3 {
1.360 + execsql {
1.361 + DROP TRIGGER trig1;
1.362 + }
1.363 +} {}
1.364 +do_test alter-3.1.4 {
1.365 + execsql {
1.366 + CREATE TRIGGER trig2 AFTER INSERT ON main.t7 BEGIN
1.367 + SELECT trigfunc('trig2', new.a, new.b, new.c);
1.368 + END;
1.369 + INSERT INTO t7 VALUES(1, 2, 3);
1.370 + }
1.371 + set ::TRIGGER
1.372 +} {trig2 1 2 3}
1.373 +do_test alter-3.1.5 {
1.374 + execsql {
1.375 + ALTER TABLE t7 RENAME TO t8;
1.376 + INSERT INTO t8 VALUES(4, 5, 6);
1.377 + }
1.378 + set ::TRIGGER
1.379 +} {trig2 4 5 6}
1.380 +do_test alter-3.1.6 {
1.381 + execsql {
1.382 + DROP TRIGGER trig2;
1.383 + }
1.384 +} {}
1.385 +do_test alter-3.1.7 {
1.386 + execsql {
1.387 + CREATE TRIGGER trig3 AFTER INSERT ON main.'t8'BEGIN
1.388 + SELECT trigfunc('trig3', new.a, new.b, new.c);
1.389 + END;
1.390 + INSERT INTO t8 VALUES(1, 2, 3);
1.391 + }
1.392 + set ::TRIGGER
1.393 +} {trig3 1 2 3}
1.394 +do_test alter-3.1.8 {
1.395 + execsql {
1.396 + ALTER TABLE t8 RENAME TO t9;
1.397 + INSERT INTO t9 VALUES(4, 5, 6);
1.398 + }
1.399 + set ::TRIGGER
1.400 +} {trig3 4 5 6}
1.401 +
1.402 +# Make sure "ON" cannot be used as a database, table or column name without
1.403 +# quoting. Otherwise the sqlite_alter_trigger() function might not work.
1.404 +file delete -force test3.db
1.405 +file delete -force test3.db-journal
1.406 +ifcapable attach {
1.407 + do_test alter-3.2.1 {
1.408 + catchsql {
1.409 + ATTACH 'test3.db' AS ON;
1.410 + }
1.411 + } {1 {near "ON": syntax error}}
1.412 + do_test alter-3.2.2 {
1.413 + catchsql {
1.414 + ATTACH 'test3.db' AS 'ON';
1.415 + }
1.416 + } {0 {}}
1.417 + do_test alter-3.2.3 {
1.418 + catchsql {
1.419 + CREATE TABLE ON.t1(a, b, c);
1.420 + }
1.421 + } {1 {near "ON": syntax error}}
1.422 + do_test alter-3.2.4 {
1.423 + catchsql {
1.424 + CREATE TABLE 'ON'.t1(a, b, c);
1.425 + }
1.426 + } {0 {}}
1.427 + do_test alter-3.2.4 {
1.428 + catchsql {
1.429 + CREATE TABLE 'ON'.ON(a, b, c);
1.430 + }
1.431 + } {1 {near "ON": syntax error}}
1.432 + do_test alter-3.2.5 {
1.433 + catchsql {
1.434 + CREATE TABLE 'ON'.'ON'(a, b, c);
1.435 + }
1.436 + } {0 {}}
1.437 +}
1.438 +do_test alter-3.2.6 {
1.439 + catchsql {
1.440 + CREATE TABLE t10(a, ON, c);
1.441 + }
1.442 +} {1 {near "ON": syntax error}}
1.443 +do_test alter-3.2.7 {
1.444 + catchsql {
1.445 + CREATE TABLE t10(a, 'ON', c);
1.446 + }
1.447 +} {0 {}}
1.448 +do_test alter-3.2.8 {
1.449 + catchsql {
1.450 + CREATE TRIGGER trig4 AFTER INSERT ON ON BEGIN SELECT 1; END;
1.451 + }
1.452 +} {1 {near "ON": syntax error}}
1.453 +ifcapable attach {
1.454 + do_test alter-3.2.9 {
1.455 + catchsql {
1.456 + CREATE TRIGGER 'on'.trig4 AFTER INSERT ON 'ON' BEGIN SELECT 1; END;
1.457 + }
1.458 + } {0 {}}
1.459 +}
1.460 +do_test alter-3.2.10 {
1.461 + execsql {
1.462 + DROP TABLE t10;
1.463 + }
1.464 +} {}
1.465 +
1.466 +do_test alter-3.3.1 {
1.467 + execsql [subst {
1.468 + CREATE TABLE tbl1(a, b, c);
1.469 + CREATE $::temp TRIGGER trig1 AFTER INSERT ON tbl1 BEGIN
1.470 + SELECT trigfunc('trig1', new.a, new.b, new.c);
1.471 + END;
1.472 + }]
1.473 +} {}
1.474 +do_test alter-3.3.2 {
1.475 + execsql {
1.476 + INSERT INTO tbl1 VALUES('a', 'b', 'c');
1.477 + }
1.478 + set ::TRIGGER
1.479 +} {trig1 a b c}
1.480 +do_test alter-3.3.3 {
1.481 + execsql {
1.482 + ALTER TABLE tbl1 RENAME TO tbl2;
1.483 + INSERT INTO tbl2 VALUES('d', 'e', 'f');
1.484 + }
1.485 + set ::TRIGGER
1.486 +} {trig1 d e f}
1.487 +do_test alter-3.3.4 {
1.488 + execsql [subst {
1.489 + CREATE $::temp TRIGGER trig2 AFTER UPDATE ON tbl2 BEGIN
1.490 + SELECT trigfunc('trig2', new.a, new.b, new.c);
1.491 + END;
1.492 + }]
1.493 +} {}
1.494 +do_test alter-3.3.5 {
1.495 + execsql {
1.496 + ALTER TABLE tbl2 RENAME TO tbl3;
1.497 + INSERT INTO tbl3 VALUES('g', 'h', 'i');
1.498 + }
1.499 + set ::TRIGGER
1.500 +} {trig1 g h i}
1.501 +do_test alter-3.3.6 {
1.502 + execsql {
1.503 + UPDATE tbl3 SET a = 'G' where a = 'g';
1.504 + }
1.505 + set ::TRIGGER
1.506 +} {trig2 G h i}
1.507 +do_test alter-3.3.7 {
1.508 + execsql {
1.509 + DROP TABLE tbl3;
1.510 + }
1.511 +} {}
1.512 +ifcapable tempdb {
1.513 + do_test alter-3.3.8 {
1.514 + execsql {
1.515 + SELECT * FROM sqlite_temp_master WHERE type = 'trigger';
1.516 + }
1.517 + } {}
1.518 +}
1.519 +
1.520 +} ;# ifcapable trigger
1.521 +
1.522 +# If the build does not include AUTOINCREMENT fields, omit alter-4.*.
1.523 +ifcapable autoinc {
1.524 +
1.525 +do_test alter-4.1 {
1.526 + execsql {
1.527 + CREATE TABLE tbl1(a INTEGER PRIMARY KEY AUTOINCREMENT);
1.528 + INSERT INTO tbl1 VALUES(10);
1.529 + }
1.530 +} {}
1.531 +do_test alter-4.2 {
1.532 + execsql {
1.533 + INSERT INTO tbl1 VALUES(NULL);
1.534 + SELECT a FROM tbl1;
1.535 + }
1.536 +} {10 11}
1.537 +do_test alter-4.3 {
1.538 + execsql {
1.539 + ALTER TABLE tbl1 RENAME TO tbl2;
1.540 + DELETE FROM tbl2;
1.541 + INSERT INTO tbl2 VALUES(NULL);
1.542 + SELECT a FROM tbl2;
1.543 + }
1.544 +} {12}
1.545 +do_test alter-4.4 {
1.546 + execsql {
1.547 + DROP TABLE tbl2;
1.548 + }
1.549 +} {}
1.550 +
1.551 +} ;# ifcapable autoinc
1.552 +
1.553 +# Test that it is Ok to execute an ALTER TABLE immediately after
1.554 +# opening a database.
1.555 +do_test alter-5.1 {
1.556 + execsql {
1.557 + CREATE TABLE tbl1(a, b, c);
1.558 + INSERT INTO tbl1 VALUES('x', 'y', 'z');
1.559 + }
1.560 +} {}
1.561 +do_test alter-5.2 {
1.562 + sqlite3 db2 test.db
1.563 + execsql {
1.564 + ALTER TABLE tbl1 RENAME TO tbl2;
1.565 + SELECT * FROM tbl2;
1.566 + } db2
1.567 +} {x y z}
1.568 +do_test alter-5.3 {
1.569 + db2 close
1.570 +} {}
1.571 +
1.572 +foreach tblname [execsql {
1.573 + SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite%'
1.574 +}] {
1.575 + execsql "DROP TABLE \"$tblname\""
1.576 +}
1.577 +
1.578 +set ::tbl_name "abc\uABCDdef"
1.579 +do_test alter-6.1 {
1.580 + string length $::tbl_name
1.581 +} {7}
1.582 +do_test alter-6.2 {
1.583 + execsql "
1.584 + CREATE TABLE ${tbl_name}(a, b, c);
1.585 + "
1.586 + set ::oid [execsql {SELECT max(oid) FROM sqlite_master}]
1.587 + execsql "
1.588 + SELECT sql FROM sqlite_master WHERE oid = $::oid;
1.589 + "
1.590 +} "{CREATE TABLE ${::tbl_name}(a, b, c)}"
1.591 +execsql "
1.592 + SELECT * FROM ${::tbl_name}
1.593 +"
1.594 +set ::tbl_name2 "abcXdef"
1.595 +do_test alter-6.3 {
1.596 + execsql "
1.597 + ALTER TABLE $::tbl_name RENAME TO $::tbl_name2
1.598 + "
1.599 + execsql "
1.600 + SELECT sql FROM sqlite_master WHERE oid = $::oid
1.601 + "
1.602 +} "{CREATE TABLE \"${::tbl_name2}\"(a, b, c)}"
1.603 +do_test alter-6.4 {
1.604 + execsql "
1.605 + ALTER TABLE $::tbl_name2 RENAME TO $::tbl_name
1.606 + "
1.607 + execsql "
1.608 + SELECT sql FROM sqlite_master WHERE oid = $::oid
1.609 + "
1.610 +} "{CREATE TABLE \"${::tbl_name}\"(a, b, c)}"
1.611 +set ::col_name ghi\1234\jkl
1.612 +do_test alter-6.5 {
1.613 + execsql "
1.614 + ALTER TABLE $::tbl_name ADD COLUMN $::col_name VARCHAR
1.615 + "
1.616 + execsql "
1.617 + SELECT sql FROM sqlite_master WHERE oid = $::oid
1.618 + "
1.619 +} "{CREATE TABLE \"${::tbl_name}\"(a, b, c, $::col_name VARCHAR)}"
1.620 +set ::col_name2 B\3421\A
1.621 +do_test alter-6.6 {
1.622 + db close
1.623 + sqlite3 db test.db
1.624 + execsql "
1.625 + ALTER TABLE $::tbl_name ADD COLUMN $::col_name2
1.626 + "
1.627 + execsql "
1.628 + SELECT sql FROM sqlite_master WHERE oid = $::oid
1.629 + "
1.630 +} "{CREATE TABLE \"${::tbl_name}\"(a, b, c, $::col_name VARCHAR, $::col_name2)}"
1.631 +do_test alter-6.7 {
1.632 + execsql "
1.633 + INSERT INTO ${::tbl_name} VALUES(1, 2, 3, 4, 5);
1.634 + SELECT $::col_name, $::col_name2 FROM $::tbl_name;
1.635 + "
1.636 +} {4 5}
1.637 +
1.638 +# Ticket #1665: Make sure ALTER TABLE ADD COLUMN works on a table
1.639 +# that includes a COLLATE clause.
1.640 +#
1.641 +do_test alter-7.1 {
1.642 + execsql {
1.643 + CREATE TABLE t1(a TEXT COLLATE BINARY);
1.644 + ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
1.645 + INSERT INTO t1 VALUES(1,'-2');
1.646 + INSERT INTO t1 VALUES(5.4e-08,'5.4e-08');
1.647 + SELECT typeof(a), a, typeof(b), b FROM t1;
1.648 + }
1.649 +} {text 1 integer -2 text 5.4e-08 real 5.4e-08}
1.650 +
1.651 +# Make sure that when a column is added by ALTER TABLE ADD COLUMN and has
1.652 +# a default value that the default value is used by aggregate functions.
1.653 +#
1.654 +do_test alter-8.1 {
1.655 + execsql {
1.656 + CREATE TABLE t2(a INTEGER);
1.657 + INSERT INTO t2 VALUES(1);
1.658 + INSERT INTO t2 VALUES(1);
1.659 + INSERT INTO t2 VALUES(2);
1.660 + ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
1.661 + SELECT sum(b) FROM t2;
1.662 + }
1.663 +} {27}
1.664 +do_test alter-8.2 {
1.665 + execsql {
1.666 + SELECT a, sum(b) FROM t2 GROUP BY a;
1.667 + }
1.668 +} {1 18 2 9}
1.669 +
1.670 +#--------------------------------------------------------------------------
1.671 +# alter-9.X - Special test: Make sure the sqlite_rename_trigger() and
1.672 +# rename_table() functions do not crash when handed bad input.
1.673 +#
1.674 +ifcapable trigger {
1.675 + do_test alter-9.1 {
1.676 + execsql {SELECT SQLITE_RENAME_TRIGGER(0,0)}
1.677 + } {{}}
1.678 +}
1.679 +do_test alter-9.2 {
1.680 + execsql {
1.681 + SELECT SQLITE_RENAME_TABLE(0,0);
1.682 + SELECT SQLITE_RENAME_TABLE(10,20);
1.683 + SELECT SQLITE_RENAME_TABLE("foo", "foo");
1.684 + }
1.685 +} {{} {} {}}
1.686 +
1.687 +#------------------------------------------------------------------------
1.688 +# alter-10.X - Make sure ALTER TABLE works with multi-byte UTF-8 characters
1.689 +# in the names.
1.690 +#
1.691 +do_test alter-10.1 {
1.692 + execsql "CREATE TABLE xyz(x UNIQUE)"
1.693 + execsql "ALTER TABLE xyz RENAME TO xyz\u1234abc"
1.694 + execsql {SELECT name FROM sqlite_master WHERE name LIKE 'xyz%'}
1.695 +} [list xyz\u1234abc]
1.696 +do_test alter-10.2 {
1.697 + execsql {SELECT name FROM sqlite_master WHERE name LIKE 'sqlite_autoindex%'}
1.698 +} [list sqlite_autoindex_xyz\u1234abc_1]
1.699 +do_test alter-10.3 {
1.700 + execsql "ALTER TABLE xyz\u1234abc RENAME TO xyzabc"
1.701 + execsql {SELECT name FROM sqlite_master WHERE name LIKE 'xyz%'}
1.702 +} [list xyzabc]
1.703 +do_test alter-10.4 {
1.704 + execsql {SELECT name FROM sqlite_master WHERE name LIKE 'sqlite_autoindex%'}
1.705 +} [list sqlite_autoindex_xyzabc_1]
1.706 +
1.707 +do_test alter-11.1 {
1.708 + sqlite3_exec db {CREATE TABLE t11(%c6%c6)}
1.709 + execsql {
1.710 + ALTER TABLE t11 ADD COLUMN abc;
1.711 + }
1.712 + catchsql {
1.713 + ALTER TABLE t11 ADD COLUMN abc;
1.714 + }
1.715 +} {1 {duplicate column name: abc}}
1.716 +set isutf16 [regexp 16 [db one {PRAGMA encoding}]]
1.717 +if {!$isutf16} {
1.718 + do_test alter-11.2 {
1.719 + execsql {INSERT INTO t11 VALUES(1,2)}
1.720 + sqlite3_exec db {SELECT %c6%c6 AS xyz, abc FROM t11}
1.721 + } {0 {xyz abc 1 2}}
1.722 +}
1.723 +do_test alter-11.3 {
1.724 + sqlite3_exec db {CREATE TABLE t11b("%81%82%83" text)}
1.725 + execsql {
1.726 + ALTER TABLE t11b ADD COLUMN abc;
1.727 + }
1.728 + catchsql {
1.729 + ALTER TABLE t11b ADD COLUMN abc;
1.730 + }
1.731 +} {1 {duplicate column name: abc}}
1.732 +if {!$isutf16} {
1.733 + do_test alter-11.4 {
1.734 + execsql {INSERT INTO t11b VALUES(3,4)}
1.735 + sqlite3_exec db {SELECT %81%82%83 AS xyz, abc FROM t11b}
1.736 + } {0 {xyz abc 3 4}}
1.737 + do_test alter-11.5 {
1.738 + sqlite3_exec db {SELECT [%81%82%83] AS xyz, abc FROM t11b}
1.739 + } {0 {xyz abc 3 4}}
1.740 + do_test alter-11.6 {
1.741 + sqlite3_exec db {SELECT "%81%82%83" AS xyz, abc FROM t11b}
1.742 + } {0 {xyz abc 3 4}}
1.743 +}
1.744 +do_test alter-11.7 {
1.745 + sqlite3_exec db {CREATE TABLE t11c(%81%82%83 text)}
1.746 + execsql {
1.747 + ALTER TABLE t11c ADD COLUMN abc;
1.748 + }
1.749 + catchsql {
1.750 + ALTER TABLE t11c ADD COLUMN abc;
1.751 + }
1.752 +} {1 {duplicate column name: abc}}
1.753 +if {!$isutf16} {
1.754 + do_test alter-11.8 {
1.755 + execsql {INSERT INTO t11c VALUES(5,6)}
1.756 + sqlite3_exec db {SELECT %81%82%83 AS xyz, abc FROM t11c}
1.757 + } {0 {xyz abc 5 6}}
1.758 + do_test alter-11.9 {
1.759 + sqlite3_exec db {SELECT [%81%82%83] AS xyz, abc FROM t11c}
1.760 + } {0 {xyz abc 5 6}}
1.761 + do_test alter-11.10 {
1.762 + sqlite3_exec db {SELECT "%81%82%83" AS xyz, abc FROM t11c}
1.763 + } {0 {xyz abc 5 6}}
1.764 +}
1.765 +
1.766 +do_test alter-12.1 {
1.767 + execsql {
1.768 + CREATE TABLE t12(a, b, c);
1.769 + CREATE VIEW v1 AS SELECT * FROM t12;
1.770 + }
1.771 +} {}
1.772 +do_test alter-12.2 {
1.773 + catchsql {
1.774 + ALTER TABLE v1 RENAME TO v2;
1.775 + }
1.776 +} {1 {view v1 may not be altered}}
1.777 +do_test alter-12.3 {
1.778 + execsql { SELECT * FROM v1; }
1.779 +} {}
1.780 +do_test alter-12.4 {
1.781 + db close
1.782 + sqlite3 db test.db
1.783 + execsql { SELECT * FROM v1; }
1.784 +} {}
1.785 +do_test alter-12.5 {
1.786 + catchsql {
1.787 + ALTER TABLE v1 ADD COLUMN new_column;
1.788 + }
1.789 +} {1 {Cannot add a column to a view}}
1.790 +
1.791 +# Ticket #3102:
1.792 +# Verify that comments do not interfere with the table rename
1.793 +# algorithm.
1.794 +#
1.795 +do_test alter-13.1 {
1.796 + execsql {
1.797 + CREATE TABLE /* hi */ t3102a(x);
1.798 + CREATE TABLE t3102b -- comment
1.799 + (y);
1.800 + CREATE INDEX t3102c ON t3102a(x);
1.801 + SELECT name FROM sqlite_master WHERE name LIKE 't3102%' ORDER BY 1;
1.802 + }
1.803 +} {t3102a t3102b t3102c}
1.804 +do_test alter-13.2 {
1.805 + execsql {
1.806 + ALTER TABLE t3102a RENAME TO t3102a_rename;
1.807 + SELECT name FROM sqlite_master WHERE name LIKE 't3102%' ORDER BY 1;
1.808 + }
1.809 +} {t3102a_rename t3102b t3102c}
1.810 +do_test alter-13.3 {
1.811 + execsql {
1.812 + ALTER TABLE t3102b RENAME TO t3102b_rename;
1.813 + SELECT name FROM sqlite_master WHERE name LIKE 't3102%' ORDER BY 1;
1.814 + }
1.815 +} {t3102a_rename t3102b_rename t3102c}
1.816 +
1.817 +finish_test