#
# The author or author's hereby grant to the public domain a non-exclusive,
# fully paid-up, perpetual, license in the software and all related
# intellectual property to make, have made, use, have used, reproduce,
# prepare derivative works, distribute, perform and display the work.
#
#*************************************************************************
# This file implements regression tests for SQLite library. The
# focus of this script is testing the ALTER TABLE statement.
#
# $Id: alter.test,v 1.2 2004/11/12 15:53:37 danielk1977 Exp $
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
ifcapable !altertable {
finish_test
return
}
# Create some tables to rename. Be sure to include some TEMP tables
# and some tables with odd names.
#
do_test alter-1.1 {
execsql {
CREATE TABLE t1(a,b);
INSERT INTO t1 VALUES(1,2);
CREATE TABLE [t1'x1](c UNIQUE, b PRIMARY KEY);
INSERT INTO [t1'x1] VALUES(3,4);
CREATE INDEX t1i1 ON T1(B);
CREATE INDEX t1i2 ON t1(a,b);
CREATE INDEX i3 ON [t1'x1](b,c);
CREATE TEMP TABLE "temp table"(e,f,g UNIQUE);
CREATE INDEX i2 ON [temp table](f);
INSERT INTO [temp table] VALUES(5,6,7);
}
execsql {
SELECT 't1', * FROM t1
UNION ALL
SELECT 't1''x1', * FROM "t1'x1"
UNION ALL
SELECT * FROM [temp table]
}
} {t1 1 2 t1'x1 3 4 5 6 7}
do_test alter-1.2 {
execsql {
SELECT type, name, tbl_name FROM sqlite_master
UNION ALL
SELECT type, name, tbl_name FROM sqlite_temp_master
ORDER BY tbl_name, type desc, name
}
} [list \
table t1 t1 \
index t1i1 t1 \
index t1i2 t1 \
table t1'x1 t1'x1 \
index i3 t1'x1 \
index {sqlite_autoindex_t1'x1_1} t1'x1 \
index {sqlite_autoindex_t1'x1_2} t1'x1 \
table {temp table} {temp table} \
index i2 {temp table} \
index {sqlite_autoindex_temp table_1} {temp table} \
]
# Make some changes
#
do_test alter-1.3 {
execsql {
ALTER TABLE [T1] RENAME to [-t1-];
ALTER TABLE "t1'x1" RENAME TO T2;
ALTER TABLE [temp table] RENAME to TempTab;
}
} {}
integrity_check alter-1.3.1
do_test alter-1.4 {
execsql {
SELECT 't1', * FROM [-t1-]
UNION ALL
SELECT 't2', * FROM t2
UNION ALL
SELECT * FROM temptab
}
} {t1 1 2 t2 3 4 5 6 7}
do_test alter-1.5 {
execsql {
SELECT type, name, tbl_name FROM sqlite_master
UNION ALL
SELECT type, name, tbl_name FROM sqlite_temp_master
ORDER BY tbl_name, type desc, name
}
} [list \
table -t1- -t1- \
index t1i1 -t1- \
index t1i2 -t1- \
table T2 T2 \
index i3 T2 \
index {sqlite_autoindex_T2_1} T2 \
index {sqlite_autoindex_T2_2} T2 \
table {TempTab} {TempTab} \
index i2 {TempTab} \
index {sqlite_autoindex_TempTab_1} {TempTab} \
]
# Make sure the changes persist after restarting the database.
# (The TEMP table will not persist, of course.)
#
do_test alter-1.6 {
db close
set DB [sqlite3 db test.db]
execsql {
SELECT type, name, tbl_name FROM sqlite_master
UNION ALL
SELECT type, name, tbl_name FROM sqlite_temp_master
ORDER BY tbl_name, type desc, name
}
} [list \
table -t1- -t1- \
index t1i1 -t1- \
index t1i2 -t1- \
table T2 T2 \
index i3 T2 \
index {sqlite_autoindex_T2_1} T2 \
index {sqlite_autoindex_T2_2} T2 \
]
# Make sure the ALTER TABLE statements work with the
# non-callback API
#
do_test alter-1.7 {
stepsql $DB {
ALTER TABLE [-t1-] RENAME to [*t1*];
ALTER TABLE T2 RENAME TO [<t2>];
}
execsql {
SELECT type, name, tbl_name FROM sqlite_master
UNION ALL
SELECT type, name, tbl_name FROM sqlite_temp_master
ORDER BY tbl_name, type desc, name
}
} [list \
table *t1* *t1* \
index t1i1 *t1* \
index t1i2 *t1* \
table <t2> <t2> \
index i3 <t2> \
index {sqlite_autoindex_<t2>_1} <t2> \
index {sqlite_autoindex_<t2>_2} <t2> \
]
# Check that ALTER TABLE works on attached databases.
#
do_test alter-1.8.1 {
file delete -force test2.db
file delete -force test2.db-journal
execsql {
ATTACH 'test2.db' AS aux;
}
} {}
do_test alter-1.8.2 {
execsql {
CREATE TABLE t4(a PRIMARY KEY, b, c);
CREATE TABLE aux.t4(a PRIMARY KEY, b, c);
CREATE INDEX i4 ON t4(b);
CREATE INDEX aux.i4 ON aux.t4(b);
}
} {}
do_test alter-1.8.3 {
execsql {
INSERT INTO t4 VALUES('main', 'main', 'main');
INSERT INTO aux.t4 VALUES('aux', 'aux', 'aux');
SELECT * FROM t4 WHERE a = 'main';
}
} {main main main}
do_test alter-1.8.4 {
execsql {
ALTER TABLE t4 RENAME TO t5;
SELECT * FROM t4 WHERE a = 'aux';
}
} {aux aux aux}
do_test alter-1.8.5 {
execsql {
SELECT * FROM t5;
}
} {main main main}
do_test alter-1.8.6 {
execsql {
SELECT * FROM t5 WHERE b = 'main';
}
} {main main main}
do_test alter-1.8.7 {
execsql {
ALTER TABLE aux.t4 RENAME TO t5;
SELECT * FROM aux.t5 WHERE b = 'aux';
}
} {aux aux aux}
# Test error messages
#
do_test alter-2.1 {
catchsql {
ALTER TABLE none RENAME TO hi;
}
} {1 {no such table: none}}
do_test alter-2.2 {
execsql {
CREATE TABLE t3(p,q,r);
}
catchsql {
ALTER TABLE [<t2>] RENAME TO t3;
}
} {1 {there is already another table or index with this name: t3}}
do_test alter-2.3 {
catchsql {
ALTER TABLE [<t2>] RENAME TO i3;
}
} {1 {there is already another table or index with this name: i3}}
finish_test