1# 2017-07-15 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# This file implements regression tests for SQLite library. The 12# focus of this file is percentile.c extension 13# 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17set testprefix unionvtab 18 19load_static_extension db unionvtab 20 21#------------------------------------------------------------------------- 22# Warm body tests. 23# 24forcedelete test.db2 25do_execsql_test 1.0 { 26 ATTACH 'test.db2' AS aux; 27 28 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT); 29 CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT); 30 CREATE TABLE aux.t3(a INTEGER PRIMARY KEY, b TEXT); 31 32 33 INSERT INTO t1 VALUES(1, 'one'), (2, 'two'), (3, 'three'); 34 INSERT INTO t2 VALUES(10, 'ten'), (11, 'eleven'), (12, 'twelve'); 35 INSERT INTO t3 VALUES(20, 'twenty'), (21, 'twenty-one'), (22, 'twenty-two'); 36} 37 38do_execsql_test 1.1 { 39 CREATE VIRTUAL TABLE temp.uuu USING unionvtab( 40 "VALUES(NULL, 't1', 1, 9), ('main', 't2', 10, 19), ('aux', 't3', 20, 29)" 41 ); 42 SELECT * FROM uuu; 43} { 44 1 one 2 two 3 three 45 10 ten 11 eleven 12 twelve 46 20 twenty 21 twenty-one 22 twenty-two 47} 48 49do_execsql_test 1.2 { 50 PRAGMA table_info(uuu); 51} { 52 0 a INTEGER 0 {} 0 53 1 b TEXT 0 {} 0 54} 55 56do_execsql_test 1.3 { 57 SELECT * FROM uuu WHERE rowid = 3; 58 SELECT * FROM uuu WHERE rowid = 11; 59} {3 three 11 eleven} 60 61do_execsql_test 1.4 { 62 SELECT * FROM uuu WHERE rowid IN (12, 10, 2); 63} {2 two 10 ten 12 twelve} 64 65do_execsql_test 1.5 { 66 SELECT * FROM uuu WHERE rowid BETWEEN 3 AND 11; 67} {3 three 10 ten 11 eleven} 68 69do_execsql_test 1.6 { 70 SELECT * FROM uuu WHERE rowid BETWEEN 11 AND 15; 71} {11 eleven 12 twelve} 72 73do_execsql_test 1.7 { 74 SELECT * FROM uuu WHERE rowid BETWEEN -46 AND 1500; 75} { 76 1 one 2 two 3 three 77 10 ten 11 eleven 12 twelve 78 20 twenty 21 twenty-one 22 twenty-two 79} 80 81#------------------------------------------------------------------------- 82# Error conditions. 83# 84# 2.1.*: Attempt to create a unionvtab table outside of the TEMP schema. 85# 2.2.*: Tables that do not exist. 86# 2.3.*: Non WITHOUT ROWID tables. 87# 2.4.*: Tables with mismatched schemas. 88# 89do_catchsql_test 2.1.1 { 90 CREATE VIRTUAL TABLE u1 USING unionvtab("VALUES(NULL, 't1', 1, 100)"); 91} {1 {unionvtab tables must be created in TEMP schema}} 92do_catchsql_test 2.1.2 { 93 CREATE VIRTUAL TABLE main.u1 USING unionvtab("VALUES('', 't1', 1, 100)"); 94} {1 {unionvtab tables must be created in TEMP schema}} 95do_catchsql_test 2.1.3 { 96 CREATE VIRTUAL TABLE aux.u1 USING unionvtab("VALUES('', 't1', 1, 100)"); 97} {1 {unionvtab tables must be created in TEMP schema}} 98 99do_catchsql_test 2.2.1 { 100 CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 't555', 1, 100)"); 101} {1 {no such rowid table: t555}} 102do_catchsql_test 2.2.2 { 103 CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('aux', 't555', 1, 100)"); 104} {1 {no such rowid table: aux.t555}} 105do_catchsql_test 2.2.3 { 106 CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('xua', 't555', 1, 100)"); 107} {1 {no such rowid table: xua.t555}} 108 109do_execsql_test 2.4.0 { 110 CREATE TABLE x1(a BLOB, b); 111 CREATE TABLE x2(a BLOB, b); 112 CREATE TEMP TABLE x3(a BLOB, b); 113 114 CREATE TABLE aux.y1(one, two, three INTEGER PRIMARY KEY); 115 CREATE TEMP TABLE y2(one, two, three INTEGER PRIMARY KEY); 116 CREATE TABLE y3(one, two, three INTEGER PRIMARY KEY); 117} 118 119foreach {tn dbs res} { 120 1 {x1 x2 x3} {0 {}} 121 2 {y1 y2 y3} {0 {}} 122 3 {x1 y2 y3} {1 {source table schema mismatch}} 123 4 {x1 y2 x3} {1 {source table schema mismatch}} 124 5 {x1 x2 y3} {1 {source table schema mismatch}} 125} { 126 set L [list] 127 set iMin 0 128 foreach e $dbs { 129 set E [split $e .] 130 if {[llength $E]>1} { 131 lappend L "('[lindex $E 0]', '[lindex $E 1]', $iMin, $iMin)" 132 } else { 133 lappend L "(NULL, '$e', $iMin, $iMin)" 134 } 135 incr iMin 136 } 137 138 set sql "CREATE VIRTUAL TABLE temp.a1 USING unionvtab(\"VALUES [join $L ,]\")" 139 do_catchsql_test 2.4.$tn " 140 DROP TABLE IF EXISTS temp.a1; 141 CREATE VIRTUAL TABLE temp.a1 USING unionvtab(\"VALUES [join $L ,]\"); 142 " $res 143} 144 145 146finish_test 147 148