AryaWu/sqlite

1# 2015 November 182#3# The author disclaims copyright to this source code.  In place of4# 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 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set testprefix hidden18 19ifcapable !hiddencolumns {20  finish_test21  return22}23 24do_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 30do_execsql_test 1.2 {31  SELECT * FROM t1;32} {1 y}33 34do_execsql_test 1.3 {35  SELECT __hidden__a, * FROM t1;36} {{} 1 x y}37 38foreach {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 $view50 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 BEGIN61      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 with76# hidden columns.77#78do_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 84do_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 92do_execsql_test 3.2.2 {93  INSERT INTO t5 SELECT * FROM t6;94  SELECT * FROM t5;95} {2 3   5 6   8 9}96 97do_execsql_test 3.2.3 {98  SELECT __hidden__a FROM t5;99} {{} {} {}}100 101 102do_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 110do_execsql_test 3.3.2 {111  INSERT INTO t5a SELECT * FROM t6a;112  SELECT * FROM t5a;113} {1 2   4 5   7 8}114 115do_execsql_test 3.3.3 {116  SELECT __hidden__c FROM t5a;117} {{} {} {}}118 119do_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 127do_execsql_test 3.4.2 {128  INSERT INTO t5b SELECT * FROM t6b;129  SELECT * FROM t5b;130} {1 2   4 5   7 8}131 132do_execsql_test 3.4.3 {133  SELECT __hidden__b FROM t5b;134} {{} {} {}}135 136#-------------------------------------------------------------------------137# Test VACUUM138#139reset_db140do_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}148do_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 153finish_test154