gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/hidden.test (about) 1 # 2015 November 18 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 # Test the __hidden__ hack. 13 # 14 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 set testprefix hidden 18 19 ifcapable !hiddencolumns { 20 finish_test 21 return 22 } 23 24 do_execsql_test 1.1 { 25 CREATE TABLE t1(__hidden__a, b); 26 INSERT INTO t1 VALUES('1'); 27 INSERT INTO t1(__hidden__a, b) VALUES('x', 'y'); 28 } {} 29 30 do_execsql_test 1.2 { 31 SELECT * FROM t1; 32 } {1 y} 33 34 do_execsql_test 1.3 { 35 SELECT __hidden__a, * FROM t1; 36 } {{} 1 x y} 37 38 foreach {tn view} { 39 1 { CREATE VIEW v1(a, b, __hidden__c) AS SELECT a, b, c FROM x1 } 40 2 { CREATE VIEW v1 AS SELECT a, b, c AS __hidden__c FROM x1 } 41 } { 42 do_execsql_test 2.$tn.1 { 43 DROP TABLE IF EXISTS x1; 44 CREATE TABLE x1(a, b, c); 45 INSERT INTO x1 VALUES(1, 2, 3); 46 } 47 48 catchsql { DROP VIEW v1 } 49 execsql $view 50 51 do_execsql_test 2.$tn.2 { 52 SELECT a, b, __hidden__c FROM v1; 53 } {1 2 3} 54 55 do_execsql_test 2.$tn.3 { 56 SELECT * FROM v1; 57 } {1 2} 58 59 do_execsql_test 2.$tn.4 { 60 CREATE TRIGGER tr1 INSTEAD OF INSERT ON v1 BEGIN 61 INSERT INTO x1 VALUES(new.a, new.b, new.__hidden__c); 62 END; 63 64 INSERT INTO v1 VALUES(4, 5); 65 SELECT * FROM x1; 66 } {1 2 3 4 5 {}} 67 68 do_execsql_test 2.$tn.5 { 69 INSERT INTO v1(a, b, __hidden__c) VALUES(7, 8, 9); 70 SELECT * FROM x1; 71 } {1 2 3 4 5 {} 7 8 9} 72 } 73 74 #------------------------------------------------------------------------- 75 # Test INSERT INTO ... SELECT ... statements that write to tables with 76 # hidden columns. 77 # 78 do_execsql_test 3.1 { 79 CREATE TABLE t4(a, __hidden__b, c); 80 INSERT INTO t4 SELECT 1, 2; 81 SELECT a, __hidden__b, c FROM t4; 82 } {1 {} 2} 83 84 do_execsql_test 3.2.1 { 85 CREATE TABLE t5(__hidden__a, b, c); 86 CREATE TABLE t6(__hidden__a, b, c); 87 INSERT INTO t6(__hidden__a, b, c) VALUES(1, 2, 3); 88 INSERT INTO t6(__hidden__a, b, c) VALUES(4, 5, 6); 89 INSERT INTO t6(__hidden__a, b, c) VALUES(7, 8, 9); 90 } 91 92 do_execsql_test 3.2.2 { 93 INSERT INTO t5 SELECT * FROM t6; 94 SELECT * FROM t5; 95 } {2 3 5 6 8 9} 96 97 do_execsql_test 3.2.3 { 98 SELECT __hidden__a FROM t5; 99 } {{} {} {}} 100 101 102 do_execsql_test 3.3.1 { 103 CREATE TABLE t5a(a, b, __hidden__c); 104 CREATE TABLE t6a(a, b, __hidden__c); 105 INSERT INTO t6a(a, b, __hidden__c) VALUES(1, 2, 3); 106 INSERT INTO t6a(a, b, __hidden__c) VALUES(4, 5, 6); 107 INSERT INTO t6a(a, b, __hidden__c) VALUES(7, 8, 9); 108 } 109 110 do_execsql_test 3.3.2 { 111 INSERT INTO t5a SELECT * FROM t6a; 112 SELECT * FROM t5a; 113 } {1 2 4 5 7 8} 114 115 do_execsql_test 3.3.3 { 116 SELECT __hidden__c FROM t5a; 117 } {{} {} {}} 118 119 do_execsql_test 3.4.1 { 120 CREATE TABLE t5b(a, __hidden__b, c); 121 CREATE TABLE t6b(a, b, __hidden__c); 122 INSERT INTO t6b(a, b, __hidden__c) VALUES(1, 2, 3); 123 INSERT INTO t6b(a, b, __hidden__c) VALUES(4, 5, 6); 124 INSERT INTO t6b(a, b, __hidden__c) VALUES(7, 8, 9); 125 } 126 127 do_execsql_test 3.4.2 { 128 INSERT INTO t5b SELECT * FROM t6b; 129 SELECT * FROM t5b; 130 } {1 2 4 5 7 8} 131 132 do_execsql_test 3.4.3 { 133 SELECT __hidden__b FROM t5b; 134 } {{} {} {}} 135 136 #------------------------------------------------------------------------- 137 # Test VACUUM 138 # 139 reset_db 140 do_execsql_test 4.1 { 141 CREATE TABLE t1(a, __hidden__b, c UNIQUE); 142 INSERT INTO t1(a, __hidden__b, c) VALUES(1, 2, 3); 143 INSERT INTO t1(a, __hidden__b, c) VALUES(4, 5, 6); 144 INSERT INTO t1(a, __hidden__b, c) VALUES(7, 8, 9); 145 DELETE FROM t1 WHERE __hidden__b = 5; 146 SELECT rowid, a, __hidden__b, c FROM t1; 147 } {1 1 2 3 3 7 8 9} 148 do_execsql_test 4.2 { 149 VACUUM; 150 SELECT rowid, a, __hidden__b, c FROM t1; 151 } {1 1 2 3 3 7 8 9} 152 153 finish_test