xref: /sqlite-3.40.0/test/altertab3.test (revision 7ac2ee0a) 
1# 2019 January 23
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#
12
13set testdir [file dirname $argv0]
14source $testdir/tester.tcl
15set testprefix altertab3
16
17# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
18ifcapable !altertable {
19  finish_test
20  return
21}
22
23ifcapable windowfunc {
24do_execsql_test 1.0 {
25  CREATE TABLE t1(a, b);
26  CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
27    SELECT sum(b) OVER w FROM t1 WINDOW w AS (ORDER BY a);
28  END;
29}
30
31do_execsql_test 1.1 {
32  ALTER TABLE t1 RENAME a TO aaa;
33}
34
35do_execsql_test 1.2 {
36  SELECT sql FROM sqlite_master WHERE name='tr1'
37} {{CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
38    SELECT sum(b) OVER w FROM t1 WINDOW w AS (ORDER BY aaa);
39  END}}
40
41do_execsql_test 1.3 {
42  INSERT INTO t1 VALUES(1, 2);
43}
44} ;# windowfunc
45
46#-------------------------------------------------------------------------
47reset_db
48do_execsql_test 2.0 {
49  CREATE TABLE t1(a,b,c);
50  CREATE TABLE t2(a,b,c);
51  CREATE TRIGGER r1 AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN
52    SELECT a,b, a name FROM t1
53      INTERSECT
54    SELECT a,b,c FROM t1 WHERE b>='d' ORDER BY name;
55    SELECT new.c;
56  END;
57}
58
59do_execsql_test 2.1 {
60  ALTER TABLE t1 RENAME TO t1x;
61  SELECT sql FROM sqlite_master WHERE name = 'r1';
62} {{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.a NOT NULL BEGIN
63    SELECT a,b, a name FROM "t1x"
64      INTERSECT
65    SELECT a,b,c FROM "t1x" WHERE b>='d' ORDER BY name;
66    SELECT new.c;
67  END}}
68
69#-------------------------------------------------------------------------
70reset_db
71do_execsql_test 3.0 {
72  CREATE TABLE t1(a, b, c, d);
73  CREATE VIEW v1 AS SELECT * FROM t1 WHERE a=1 OR (b IN ());
74}
75
76do_execsql_test 3.1 {
77  ALTER TABLE t1 RENAME b TO bbb;
78}
79
80do_execsql_test 3.2 {
81  SELECT sql FROM sqlite_master WHERE name = 'v1'
82} {{CREATE VIEW v1 AS SELECT * FROM t1 WHERE a=1 OR (bbb IN ())}}
83
84#-------------------------------------------------------------------------
85reset_db
86do_execsql_test 4.0 {
87  CREATE TABLE t1(a, b);
88  CREATE TABLE t3(e, f);
89  CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
90    INSERT INTO t2 VALUES(new.a, new.b);
91  END;
92}
93
94do_catchsql_test 4.1.2 {
95  BEGIN;
96    ALTER TABLE t3 RENAME TO t4;
97} {1 {error in trigger tr1: no such table: main.t2}}
98do_execsql_test 4.1.2 {
99  COMMIT;
100}
101do_execsql_test 4.1.3 {
102  SELECT * FROM sqlite_master WHERE type='table' AND name!='t1';
103} {table t3 t3 3 {CREATE TABLE t3(e, f)}}
104
105
106do_catchsql_test 4.2.1 {
107  BEGIN;
108    ALTER TABLE t3 RENAME e TO eee;
109} {1 {error in trigger tr1: no such table: main.t2}}
110do_execsql_test 4.2.2 {
111  COMMIT;
112}
113do_execsql_test 4.2.3 {
114  SELECT * FROM sqlite_master WHERE type='table' AND name!='t1';
115} {table t3 t3 3 {CREATE TABLE t3(e, f)}}
116
117#-------------------------------------------------------------------------
118reset_db
119do_execsql_test 5.0 {
120  CREATE TABLE t1 (
121      c1 integer, c2, PRIMARY KEY(c1 collate rtrim),
122      UNIQUE(c2)
123  )
124}
125do_execsql_test 5.1 {
126  ALTER TABLE t1 RENAME c1 TO c3;
127}
128
129#-------------------------------------------------------------------------
130reset_db
131do_execsql_test 6.0 {
132  CREATE TEMPORARY TABLE Table0 (
133    Col0 INTEGER,
134    PRIMARY KEY(Col0 COLLATE RTRIM),
135    FOREIGN KEY (Col0) REFERENCES Table0
136  );
137}
138
139do_execsql_test 6.1 {
140  ALTER TABLE Table0 RENAME Col0 TO Col0;
141}
142
143finish_test
144
145
146