xref: /sqlite-3.40.0/test/vtabJ.test (revision 4d906f1b) 
1# 2017-08-10
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 tests of writing to WITHOUT ROWID virtual tables
12# using the tclvar eponymous virtual table.
13#
14
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17set testprefix vtabJ
18
19ifcapable !vtab {
20  finish_test
21  return
22}
23
24register_tclvar_module db
25
26unset -nocomplain vtabJ
27do_test 100 {
28  set vtabJ(1) this
29  set vtabJ(two) is
30  set vtabJ(3) {a test}
31  db eval {
32    SELECT fullname, value FROM tclvar WHERE name='vtabJ' ORDER BY fullname;
33  }
34} {vtabJ(1) this vtabJ(3) {a test} vtabJ(two) is}
35
36do_execsql_test 110 {
37  INSERT INTO tclvar(fullname, value)
38    VALUES('vtabJ(4)',4),('vtabJ(five)',555);
39  SELECT fullname, value FROM tclvar WHERE name='vtabJ' ORDER BY fullname;
40} {vtabJ(1) this vtabJ(3) {a test} vtabJ(4) 4 vtabJ(five) 555 vtabJ(two) is}
41do_test 111 {
42  set res {}
43  foreach vname [lsort [array names vtabJ]] {
44    lappend res vtabJ($vname) $vtabJ($vname)
45  }
46  set res
47} {vtabJ(1) this vtabJ(3) {a test} vtabJ(4) 4 vtabJ(five) 555 vtabJ(two) is}
48
49do_test 120 {
50  db eval {
51    INSERT INTO tclvar(fullname, value) VALUES('vtabJ(4)',444);
52  }
53  set vtabJ(4)
54} {444}
55
56do_test 130 {
57  db eval {
58    INSERT INTO tclvar(fullname, value) VALUES('vtabJ(4)',NULL);
59  }
60  info exists vtabJ(4)
61} {0}
62
63do_test 140 {
64  db eval {
65    UPDATE tclvar SET value=55 WHERE fullname='vtabJ(five)';
66  }
67  set vtabJ(five)
68} {55}
69
70do_test 150 {
71  db eval {
72    UPDATE tclvar SET fullname='vtabJ(5)' WHERE fullname='vtabJ(five)';
73  }
74  set vtabJ(5)
75} {55}
76do_test 151 {
77  info exists vtabJ(five)
78} {0}
79do_test 152 {
80  set res {}
81  foreach vname [lsort [array names vtabJ]] {
82    lappend res vtabJ($vname) $vtabJ($vname)
83  }
84  set res
85} {vtabJ(1) this vtabJ(3) {a test} vtabJ(5) 55 vtabJ(two) is}
86
87do_execsql_test 160 {
88  SELECT fullname FROM tclvar WHERE arrayname='two'
89} {vtabJ(two)}
90do_execsql_test 161 {
91  DELETE FROM tclvar WHERE arrayname='two';
92  SELECT fullname, value FROM tclvar WHERE name='vtabJ' ORDER BY fullname;
93} {vtabJ(1) this vtabJ(3) {a test} vtabJ(5) 55}
94do_test 162 {
95  set res {}
96  foreach vname [lsort [array names vtabJ]] {
97    lappend res vtabJ($vname) $vtabJ($vname)
98  }
99  set res
100} {vtabJ(1) this vtabJ(3) {a test} vtabJ(5) 55}
101
102# Try to trick the module into updating the same variable twice for a
103# single UPDATE statement.
104#
105do_execsql_test 171 {
106  INSERT INTO tclvar(fullname, value) VALUES('xx', 'a');
107  SELECT name, value FROM tclvar where name = 'xx';
108} {xx a}
109do_execsql_test 172 {
110  UPDATE tclvar SET value = value || 't'
111  WHERE name = 'xx' OR name = 'x'||'x';
112  SELECT name, value FROM tclvar where name = 'xx';
113} {xx at}
114do_execsql_test 173 {
115  UPDATE tclvar SET value = value || 't'
116  WHERE name = 'xx' OR name BETWEEN 'xx' AND 'xx';
117  SELECT name, value FROM tclvar where name = 'xx';
118} {xx att}
119
120do_execsql_test 181 {
121  DELETE FROM tclvar WHERE name BETWEEN 'xx' AND 'xx' OR name='xx';
122  SELECT name, value FROM tclvar where name = 'xx';
123} {}
124
125#-------------------------------------------------------------------------
126
127do_execsql_test 200 {
128  CREATE TABLE var(k TEXT, v TEXT);
129  INSERT INTO var VALUES('testvar1', 10);
130  INSERT INTO var VALUES('testvar2', 20);
131  INSERT INTO var VALUES('testvar3', 30);
132}
133
134do_test 210 {
135  foreach {testvar1 testvar2 testvar3} {1 2 3} {}
136  execsql {
137    UPDATE tclvar SET value = var.v FROM var WHERE name = var.k;
138  }
139  list $testvar1 $testvar2 $testvar3
140} {10 20 30}
141
142do_test 220 {
143  execsql {
144    CREATE TABLE nam(k TEXT, v TEXT);
145    INSERT INTO nam VALUES('testvar1', 'tv1');
146    INSERT INTO nam VALUES('testvar2', 'tv2');
147    INSERT INTO nam VALUES('testvar3', 'tv3');
148    UPDATE tclvar SET fullname = nam.v FROM nam WHERE name = nam.k;
149  }
150  list $tv1 $tv2 $tv3
151} {10 20 30}
152
153
154finish_test
155