1# 2017 July 14
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 script is testing the lsm1 virtual table module.
13#
14
15source [file join [file dirname [info script]] lsm1_common.tcl]
16set testprefix lsm1_simple
17return_if_no_lsm1
18load_lsm1_vtab db
19
20forcedelete testlsm.db
21
22do_execsql_test 100 {
23  CREATE VIRTUAL TABLE x1 USING lsm1(testlsm.db,a,UINT,b,c,d);
24  PRAGMA table_info(x1);
25} {
26  0 a UINT 1 {} 1
27  1 b {} 0 {} 0
28  2 c {} 0 {} 0
29  3 d {} 0 {} 0
30}
31
32do_execsql_test 110 {
33  INSERT INTO x1(a,b,c,d) VALUES(15, 11, 22, 33),(8,'banjo',x'333231',NULL),
34      (12,NULL,3.25,-559281390);
35  SELECT a, quote(b), quote(c), quote(d) FROM x1;
36} {8 'banjo' X'333231' NULL 12 NULL 3.25 -559281390 15 11 22 33}
37do_execsql_test 111 {
38  SELECT a, quote(lsm1_key), quote(lsm1_value) FROM x1;
39} {8 X'08' X'2162616E6A6F1633323105' 12 X'0C' X'05320000000000000A401FFB42ABE9DB' 15 X'0F' X'4284C6'}
40
41do_execsql_test 120 {
42  UPDATE x1 SET d = d+1.0 WHERE a=15;
43  SELECT a, quote(b), quote(c), quote(d) FROM x1;
44} {8 'banjo' X'333231' NULL 12 NULL 3.25 -559281390 15 11 22 34.0}
45
46do_execsql_test 130 {
47  UPDATE x1 SET a=123456789 WHERE a=12;
48  SELECT a, quote(b), quote(c), quote(d) FROM x1;
49} {8 'banjo' X'333231' NULL 15 11 22 34.0 123456789 NULL 3.25 -559281390}
50do_execsql_test 131 {
51  SELECT quote(lsm1_key), printf('0x%x',a) FROM x1 WHERE a > 100000000;
52} {X'FB075BCD15' 0x75bcd15}
53
54do_execsql_test 140 {
55  DELETE FROM x1 WHERE a=15;
56  SELECT a, quote(b), quote(c), quote(d) FROM x1;
57} {8 'banjo' X'333231' NULL 123456789 NULL 3.25 -559281390}
58
59do_test 150 {
60  lsort [glob testlsm.db*]
61} {testlsm.db testlsm.db-log testlsm.db-shm}
62
63db close
64do_test 160 {
65  lsort [glob testlsm.db*]
66} {testlsm.db}
67
68forcedelete testlsm.db
69forcedelete test.db
70sqlite3 db test.db
71load_lsm1_vtab db
72
73
74do_execsql_test 200 {
75  CREATE VIRTUAL TABLE x1 USING lsm1(testlsm.db,a,TEXT,b,c,d);
76  PRAGMA table_info(x1);
77} {
78  0 a TEXT 1 {} 1
79  1 b {} 0 {} 0
80  2 c {} 0 {} 0
81  3 d {} 0 {} 0
82}
83do_execsql_test 210 {
84  INSERT INTO x1(a,b,c,d) VALUES(15, 11, 22, 33),(8,'banjo',x'333231',NULL),
85      (12,NULL,3.25,-559281390);
86  SELECT quote(a), quote(b), quote(c), quote(d), '|' FROM x1;
87} {'12' NULL 3.25 -559281390 | '15' 11 22 33 | '8' 'banjo' X'333231' NULL |}
88do_execsql_test 211 {
89  SELECT quote(a), quote(lsm1_key), quote(lsm1_value), '|' FROM x1;
90} {'12' X'3132' X'05320000000000000A401FFB42ABE9DB' | '15' X'3135' X'4284C6' | '8' X'38' X'2162616E6A6F1633323105' |}
91do_execsql_test 212 {
92  SELECT quote(a), quote(lsm1_key), quote(lsm1_value) FROM x1 WHERE a='12';
93} {'12' X'3132' X'05320000000000000A401FFB42ABE9DB'}
94
95#-------------------------------------------------------------------------
96reset_db
97forcedelete testlsm.db
98load_lsm1_vtab db
99do_execsql_test 300 {
100  CREATE VIRTUAL TABLE x1 USING lsm1(testlsm.db,a,TEXT,b,c,d);
101}
102do_eqp_test 310 {
103  SELECT * FROM x1 WHERE a=?
104} {SCAN TABLE x1 VIRTUAL TABLE INDEX 0:}
105
106do_eqp_test 320 {
107  SELECT * FROM x1 WHERE a>?
108} {SCAN TABLE x1 VIRTUAL TABLE INDEX 2:}
109
110do_eqp_test 330 {
111  SELECT * FROM x1 WHERE a<?
112} {SCAN TABLE x1 VIRTUAL TABLE INDEX 3:}
113do_eqp_test 340 {
114  SELECT * FROM x1 WHERE a BETWEEN ? AND ?
115} {SCAN TABLE x1 VIRTUAL TABLE INDEX 1:}
116
117#-------------------------------------------------------------------------
118reset_db
119forcedelete testlsm.db
120load_lsm1_vtab db
121do_execsql_test 400 {
122  CREATE VIRTUAL TABLE x1 USING lsm1(testlsm.db,a,TEXT,b);
123  INSERT INTO x1 VALUES('one', 1);
124  INSERT INTO x1 VALUES('two', 2);
125  INSERT INTO x1 VALUES('three', 3);
126  INSERT INTO x1 VALUES('four', 4);
127  INSERT INTO x1 VALUES('five', 5);
128}
129do_execsql_test 410 {
130  SELECT b FROM x1 WHERE a = 'two'
131} {2}
132do_execsql_test 411 {
133  SELECT b FROM x1 WHERE a = 'one'
134} {1}
135do_execsql_test 412 {
136  SELECT b FROM x1 WHERE a = 'five'
137} {5}
138
139do_execsql_test 420 {
140  SELECT b FROM x1 WHERE a BETWEEN 'one' AND 'three';
141} {1 3}
142do_execsql_test 421 {
143  SELECT b FROM x1 WHERE a BETWEEN 'five' AND 'two';
144} {5 4 1 3 2}
145do_execsql_test 421 {
146  SELECT b FROM x1 WHERE a > 'five';
147} {4 1 3 2}
148do_execsql_test 421 {
149  SELECT b FROM x1 WHERE a <= 'three';
150} {3 1 4 5}
151
152finish_test
153