PageRenderTime 49ms CodeModel.GetById 16ms RepoModel.GetById 0ms app.codeStats 0ms

/test/vtab1.test

https://bitbucket.org/aware/sqlite
Unknown | 1296 lines | 1208 code | 88 blank | 0 comment | 0 complexity | 477e147347dee39a46c1933c09fafc1b MD5 | raw file
  1. # 2006 June 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 regression tests for SQLite library. The
  12. # focus of this file is creating and dropping virtual tables.
  13. #
  14. # $Id:$
  15. set testdir [file dirname $argv0]
  16. source $testdir/tester.tcl
  17. set testprefix vtab1
  18. ifcapable !vtab||!schema_pragmas {
  19. finish_test
  20. return
  21. }
  22. #----------------------------------------------------------------------
  23. # Organization of tests in this file:
  24. #
  25. # vtab1-1.*: Error conditions and other issues surrounding creation/connection
  26. # of a virtual module.
  27. # vtab1-2.*: Test sqlite3_declare_vtab() and the xConnect/xDisconnect methods.
  28. # vtab1-3.*: Table scans and WHERE clauses.
  29. # vtab1-4.*: Table scans and ORDER BY clauses.
  30. # vtab1-5.*: Test queries that include joins. This brings the
  31. # sqlite3_index_info.estimatedCost variable into play.
  32. # vtab1-6.*: Test UPDATE/INSERT/DELETE on vtables.
  33. # vtab1-7.*: Test sqlite3_last_insert_rowid().
  34. #
  35. # This file uses the "echo" module (see src/test8.c). Refer to comments
  36. # in that file for the special behaviour of the Tcl $echo_module variable.
  37. #
  38. # TODO:
  39. # * How to test the sqlite3_index_constraint_usage.omit field?
  40. # * vtab1-5.*
  41. #
  42. # vtab1-14.*: Test 'IN' constraints - i.e. "SELECT * FROM t1 WHERE id IN(...)"
  43. #
  44. # vtab1-18.*: Check that the LIKE optimization is not applied when the lhs
  45. # is a virtual table column.
  46. #
  47. #----------------------------------------------------------------------
  48. # Test cases vtab1.1.*
  49. #
  50. # We cannot create a virtual table if the module has not been registered.
  51. #
  52. do_test vtab1-1.1.1 {
  53. explain {
  54. CREATE VIRTUAL TABLE t1 USING echo;
  55. }
  56. catchsql {
  57. CREATE VIRTUAL TABLE t1 USING echo;
  58. }
  59. } {1 {no such module: echo}}
  60. do_test vtab1-1.1.2 {
  61. catchsql {
  62. CREATE VIRTUAL TABLE IF NOT EXISTS t1 USING echo;
  63. }
  64. } {1 {no such module: echo}}
  65. do_test vtab1-1.2 {
  66. execsql {
  67. SELECT name FROM sqlite_master ORDER BY 1
  68. }
  69. } {}
  70. # Register the module
  71. register_echo_module [sqlite3_connection_pointer db]
  72. # Once a module has been registered, virtual tables using that module
  73. # may be created. However if a module xCreate() fails to call
  74. # sqlite3_declare_vtab() an error will be raised and the table not created.
  75. #
  76. # The "echo" module does not invoke sqlite3_declare_vtab() if it is
  77. # passed zero arguments.
  78. #
  79. do_test vtab1-1.3.1 {
  80. catchsql {
  81. CREATE VIRTUAL TABLE t1 USING echo;
  82. }
  83. } {1 {vtable constructor did not declare schema: t1}}
  84. do_test vtab1-1.3.2 {
  85. catchsql {
  86. CREATE VIRTUAL TABLE IF NOT EXISTS t1 USING echo;
  87. }
  88. } {1 {vtable constructor did not declare schema: t1}}
  89. do_test vtab1-1.4 {
  90. execsql {
  91. SELECT name FROM sqlite_master ORDER BY 1
  92. }
  93. } {}
  94. # The "echo" module xCreate method returns an error and does not create
  95. # the virtual table if it is passed an argument that does not correspond
  96. # to an existing real table in the same database.
  97. #
  98. do_test vtab1-1.5.1 {
  99. catchsql {
  100. CREATE VIRTUAL TABLE t1 USING echo(no_such_table);
  101. }
  102. } {1 {vtable constructor failed: t1}}
  103. do_test vtab1-1.5.2 {
  104. catchsql {
  105. CREATE VIRTUAL TABLE IF NOT EXISTS t1 USING echo(no_such_table);
  106. }
  107. } {1 {vtable constructor failed: t1}}
  108. do_test vtab1-1.6 {
  109. execsql {
  110. SELECT name FROM sqlite_master ORDER BY 1
  111. }
  112. } {}
  113. # Ticket #2156. Using the sqlite3_prepare_v2() API, make sure that
  114. # a CREATE VIRTUAL TABLE statement can be used multiple times.
  115. #
  116. do_test vtab1-1.2152.1 {
  117. set DB [sqlite3_connection_pointer db]
  118. set sql {CREATE VIRTUAL TABLE t2152a USING echo(t2152b)}
  119. set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL]
  120. sqlite3_step $STMT
  121. } SQLITE_ERROR
  122. do_test vtab-1.2152.2 {
  123. sqlite3_reset $STMT
  124. sqlite3_step $STMT
  125. } SQLITE_ERROR
  126. do_test vtab-1.2152.3 {
  127. sqlite3_reset $STMT
  128. db eval {CREATE TABLE t2152b(x,y)}
  129. sqlite3_step $STMT
  130. } SQLITE_DONE
  131. do_test vtab-1.2152.4 {
  132. sqlite3_finalize $STMT
  133. db eval {DROP TABLE t2152a; DROP TABLE t2152b}
  134. } {}
  135. # Test to make sure nothing goes wrong and no memory is leaked if we
  136. # select an illegal table-name (i.e a reserved name or the name of a
  137. # table that already exists).
  138. #
  139. do_test vtab1-1.7.1 {
  140. catchsql {
  141. CREATE VIRTUAL TABLE sqlite_master USING echo;
  142. }
  143. } {1 {object name reserved for internal use: sqlite_master}}
  144. do_test vtab1-1.7.2 {
  145. catchsql {
  146. CREATE VIRTUAL TABLE IF NOT EXISTS sqlite_master USING echo;
  147. }
  148. } {1 {object name reserved for internal use: sqlite_master}}
  149. do_test vtab1-1.8.1 {
  150. catchsql {
  151. CREATE TABLE treal(a, b, c);
  152. CREATE VIRTUAL TABLE treal USING echo(treal);
  153. }
  154. } {1 {table treal already exists}}
  155. do_test vtab1-1.8.2 {
  156. catchsql {
  157. CREATE VIRTUAL TABLE IF NOT EXISTS treal USING echo(treal);
  158. }
  159. } {0 {}}
  160. do_test vtab1-1.9 {
  161. execsql {
  162. DROP TABLE treal;
  163. SELECT name FROM sqlite_master ORDER BY 1
  164. }
  165. } {}
  166. do_test vtab1-1.10 {
  167. execsql {
  168. CREATE TABLE treal(a, b, c);
  169. CREATE VIRTUAL TABLE techo USING echo(treal);
  170. }
  171. db close
  172. sqlite3 db test.db
  173. catchsql {
  174. SELECT * FROM techo;
  175. }
  176. } {1 {no such module: echo}}
  177. do_test vtab1-1.11 {
  178. catchsql {
  179. INSERT INTO techo VALUES(1, 2, 3);
  180. }
  181. } {1 {no such module: echo}}
  182. do_test vtab1-1.12 {
  183. catchsql {
  184. UPDATE techo SET a = 10;
  185. }
  186. } {1 {no such module: echo}}
  187. do_test vtab1-1.13 {
  188. catchsql {
  189. DELETE FROM techo;
  190. }
  191. } {1 {no such module: echo}}
  192. do_test vtab1-1.14 {
  193. catchsql {
  194. PRAGMA table_info(techo)
  195. }
  196. } {1 {no such module: echo}}
  197. do_test vtab1-1.15 {
  198. catchsql {
  199. DROP TABLE techo;
  200. }
  201. } {1 {no such module: echo}}
  202. register_echo_module [sqlite3_connection_pointer db]
  203. register_echo_module [sqlite3_connection_pointer db]
  204. # Test an error message returned from a v-table constructor.
  205. #
  206. do_test vtab1-1.16 {
  207. execsql {
  208. DROP TABLE techo;
  209. CREATE TABLE logmsg(log);
  210. }
  211. catchsql {
  212. CREATE VIRTUAL TABLE techo USING echo(treal, logmsg);
  213. }
  214. } {1 {table 'logmsg' already exists}}
  215. do_test vtab1-1.17 {
  216. execsql {
  217. DROP TABLE treal;
  218. DROP TABLE logmsg;
  219. SELECT sql FROM sqlite_master;
  220. }
  221. } {}
  222. #----------------------------------------------------------------------
  223. # Test cases vtab1.2.*
  224. #
  225. # At this point, the database is completely empty. The echo module
  226. # has already been registered.
  227. # If a single argument is passed to the echo module during table
  228. # creation, it is assumed to be the name of a table in the same
  229. # database. The echo module attempts to set the schema of the
  230. # new virtual table to be the same as the existing database table.
  231. #
  232. do_test vtab1-2.1 {
  233. execsql {
  234. CREATE TABLE template(a, b, c);
  235. }
  236. execsql { PRAGMA table_info(template); }
  237. } [list \
  238. 0 a {} 0 {} 0 \
  239. 1 b {} 0 {} 0 \
  240. 2 c {} 0 {} 0 \
  241. ]
  242. do_test vtab1-2.2 {
  243. execsql {
  244. CREATE VIRTUAL TABLE t1 USING echo(template);
  245. }
  246. execsql { PRAGMA table_info(t1); }
  247. } [list \
  248. 0 a {} 0 {} 0 \
  249. 1 b {} 0 {} 0 \
  250. 2 c {} 0 {} 0 \
  251. ]
  252. # Test that the database can be unloaded. This should invoke the xDisconnect()
  253. # callback for the successfully create virtual table (t1).
  254. #
  255. do_test vtab1-2.3 {
  256. set echo_module [list]
  257. db close
  258. set echo_module
  259. } [list xDisconnect]
  260. # Re-open the database. This should not cause any virtual methods to
  261. # be called. The invocation of xConnect() is delayed until the virtual
  262. # table schema is first required by the compiler.
  263. #
  264. do_test vtab1-2.4 {
  265. set echo_module [list]
  266. sqlite3 db test.db
  267. db cache size 0
  268. set echo_module
  269. } {}
  270. # Try to query the virtual table schema. This should fail, as the
  271. # echo module has not been registered with this database connection.
  272. #
  273. do_test vtab1.2.6 {
  274. catchsql { PRAGMA table_info(t1); }
  275. } {1 {no such module: echo}}
  276. # Register the module
  277. register_echo_module [sqlite3_connection_pointer db]
  278. # Try to query the virtual table schema again. This time it should
  279. # invoke the xConnect method and succeed.
  280. #
  281. do_test vtab1.2.7 {
  282. execsql { PRAGMA table_info(t1); }
  283. } [list \
  284. 0 a {} 0 {} 0 \
  285. 1 b {} 0 {} 0 \
  286. 2 c {} 0 {} 0 \
  287. ]
  288. do_test vtab1.2.8 {
  289. set echo_module
  290. } {xConnect echo main t1 template}
  291. # Drop table t1. This should cause the xDestroy (but not xDisconnect) method
  292. # to be invoked.
  293. do_test vtab1-2.5 {
  294. set echo_module ""
  295. execsql {
  296. DROP TABLE t1;
  297. }
  298. set echo_module
  299. } {xDestroy}
  300. do_test vtab1-2.6 {
  301. execsql {
  302. PRAGMA table_info(t1);
  303. }
  304. } {}
  305. do_test vtab1-2.7 {
  306. execsql {
  307. SELECT sql FROM sqlite_master;
  308. }
  309. } [list {CREATE TABLE template(a, b, c)}]
  310. # Clean up other test artifacts:
  311. do_test vtab1-2.8 {
  312. execsql {
  313. DROP TABLE template;
  314. SELECT sql FROM sqlite_master;
  315. }
  316. } [list]
  317. #----------------------------------------------------------------------
  318. # Test case vtab1-3 test table scans and the echo module's
  319. # xBestIndex/xFilter handling of WHERE conditions.
  320. do_test vtab1-3.1 {
  321. set echo_module ""
  322. execsql {
  323. CREATE TABLE treal(a INTEGER, b INTEGER, c);
  324. CREATE INDEX treal_idx ON treal(b);
  325. CREATE VIRTUAL TABLE t1 USING echo(treal);
  326. }
  327. set echo_module
  328. } [list xCreate echo main t1 treal \
  329. xSync echo(treal) \
  330. xCommit echo(treal) \
  331. ]
  332. # Test that a SELECT on t1 doesn't crash. No rows are returned
  333. # because the underlying real table is currently empty.
  334. #
  335. do_test vtab1-3.2 {
  336. execsql {
  337. SELECT a, b, c FROM t1;
  338. }
  339. } {}
  340. # Put some data into the table treal. Then try a few simple SELECT
  341. # statements on t1.
  342. #
  343. do_test vtab1-3.3 {
  344. execsql {
  345. INSERT INTO treal VALUES(1, 2, 3);
  346. INSERT INTO treal VALUES(4, 5, 6);
  347. SELECT * FROM t1;
  348. }
  349. } {1 2 3 4 5 6}
  350. do_test vtab1-3.4 {
  351. execsql {
  352. SELECT a FROM t1;
  353. }
  354. } {1 4}
  355. do_test vtab1-3.5 {
  356. execsql {
  357. SELECT rowid FROM t1;
  358. }
  359. } {1 2}
  360. do_test vtab1-3.6 {
  361. set echo_module ""
  362. execsql {
  363. SELECT * FROM t1;
  364. }
  365. } {1 2 3 4 5 6}
  366. do_test vtab1-3.7 {
  367. execsql {
  368. SELECT rowid, * FROM t1;
  369. }
  370. } {1 1 2 3 2 4 5 6}
  371. do_test vtab1-3.8 {
  372. execsql {
  373. SELECT a AS d, b AS e, c AS f FROM t1;
  374. }
  375. } {1 2 3 4 5 6}
  376. # Execute some SELECT statements with WHERE clauses on the t1 table.
  377. # Then check the echo_module variable (written to by the module methods
  378. # in test8.c) to make sure the xBestIndex() and xFilter() methods were
  379. # called correctly.
  380. #
  381. do_test vtab1-3.8 {
  382. set echo_module ""
  383. execsql {
  384. SELECT * FROM t1;
  385. }
  386. set echo_module
  387. } [list xBestIndex {SELECT rowid, * FROM 'treal'} \
  388. xFilter {SELECT rowid, * FROM 'treal'} ]
  389. do_test vtab1-3.9 {
  390. set echo_module ""
  391. execsql {
  392. SELECT * FROM t1 WHERE b = 5;
  393. }
  394. } {4 5 6}
  395. do_test vtab1-3.10 {
  396. set echo_module
  397. } [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b = ?} \
  398. xFilter {SELECT rowid, * FROM 'treal' WHERE b = ?} 5 ]
  399. do_test vtab1-3.10 {
  400. set echo_module ""
  401. execsql {
  402. SELECT * FROM t1 WHERE b >= 5 AND b <= 10;
  403. }
  404. } {4 5 6}
  405. do_test vtab1-3.11 {
  406. set echo_module
  407. } [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} \
  408. xFilter {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} 5 10 ]
  409. do_test vtab1-3.12 {
  410. set echo_module ""
  411. execsql {
  412. SELECT * FROM t1 WHERE b BETWEEN 2 AND 10;
  413. }
  414. } {1 2 3 4 5 6}
  415. do_test vtab1-3.13 {
  416. set echo_module
  417. } [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} \
  418. xFilter {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} 2 10 ]
  419. # Add a function for the MATCH operator. Everything always matches!
  420. #proc test_match {lhs rhs} {
  421. # lappend ::echo_module MATCH $lhs $rhs
  422. # return 1
  423. #}
  424. #db function match test_match
  425. set echo_module ""
  426. do_test vtab1-3.12 {
  427. set echo_module ""
  428. catchsql {
  429. SELECT * FROM t1 WHERE a MATCH 'string';
  430. }
  431. } {1 {unable to use function MATCH in the requested context}}
  432. do_test vtab1-3.13 {
  433. set echo_module
  434. } [list xBestIndex {SELECT rowid, * FROM 'treal'} \
  435. xFilter {SELECT rowid, * FROM 'treal'}]
  436. ifcapable subquery {
  437. # The echo module uses a subquery internally to implement the MATCH operator.
  438. do_test vtab1-3.14 {
  439. set echo_module ""
  440. execsql {
  441. SELECT * FROM t1 WHERE b MATCH 'string';
  442. }
  443. } {}
  444. do_test vtab1-3.15 {
  445. set echo_module
  446. } [list xBestIndex \
  447. {SELECT rowid, * FROM 'treal' WHERE b LIKE (SELECT '%'||?||'%')} \
  448. xFilter \
  449. {SELECT rowid, * FROM 'treal' WHERE b LIKE (SELECT '%'||?||'%')} \
  450. string ]
  451. }; #ifcapable subquery
  452. #----------------------------------------------------------------------
  453. # Test case vtab1-3 test table scans and the echo module's
  454. # xBestIndex/xFilter handling of ORDER BY clauses.
  455. # This procedure executes the SQL. Then it checks to see if the OP_Sort
  456. # opcode was executed. If an OP_Sort did occur, then "sort" is appended
  457. # to the result. If no OP_Sort happened, then "nosort" is appended.
  458. #
  459. # This procedure is used to check to make sure sorting is or is not
  460. # occurring as expected.
  461. #
  462. proc cksort {sql} {
  463. set ::sqlite_sort_count 0
  464. set data [execsql $sql]
  465. if {$::sqlite_sort_count} {set x sort} {set x nosort}
  466. lappend data $x
  467. return $data
  468. }
  469. do_test vtab1-4.1 {
  470. set echo_module ""
  471. cksort {
  472. SELECT b FROM t1 ORDER BY b;
  473. }
  474. } {2 5 nosort}
  475. do_test vtab1-4.2 {
  476. set echo_module
  477. } [list xBestIndex {SELECT rowid, * FROM 'treal' ORDER BY b ASC} \
  478. xFilter {SELECT rowid, * FROM 'treal' ORDER BY b ASC} ]
  479. do_test vtab1-4.3 {
  480. set echo_module ""
  481. cksort {
  482. SELECT b FROM t1 ORDER BY b DESC;
  483. }
  484. } {5 2 nosort}
  485. do_test vtab1-4.4 {
  486. set echo_module
  487. } [list xBestIndex {SELECT rowid, * FROM 'treal' ORDER BY b DESC} \
  488. xFilter {SELECT rowid, * FROM 'treal' ORDER BY b DESC} ]
  489. do_test vtab1-4.3 {
  490. set echo_module ""
  491. cksort {
  492. SELECT b FROM t1 ORDER BY b||'';
  493. }
  494. } {2 5 sort}
  495. do_test vtab1-4.4 {
  496. set echo_module
  497. } [list xBestIndex {SELECT rowid, * FROM 'treal'} \
  498. xFilter {SELECT rowid, * FROM 'treal'} ]
  499. execsql {
  500. DROP TABLE t1;
  501. DROP TABLE treal;
  502. }
  503. #----------------------------------------------------------------------
  504. # Test cases vtab1-5 test SELECT queries that include joins on virtual
  505. # tables.
  506. proc filter {log} {
  507. set out [list]
  508. for {set ii 0} {$ii < [llength $log]} {incr ii} {
  509. if {[lindex $log $ii] eq "xFilter"} {
  510. lappend out xFilter
  511. lappend out [lindex $log [expr $ii+1]]
  512. }
  513. }
  514. return $out
  515. }
  516. do_test vtab1-5-1 {
  517. execsql {
  518. CREATE TABLE t1(a, b, c);
  519. CREATE TABLE t2(d, e, f);
  520. INSERT INTO t1 VALUES(1, 'red', 'green');
  521. INSERT INTO t1 VALUES(2, 'blue', 'black');
  522. INSERT INTO t2 VALUES(1, 'spades', 'clubs');
  523. INSERT INTO t2 VALUES(2, 'hearts', 'diamonds');
  524. CREATE VIRTUAL TABLE et1 USING echo(t1);
  525. CREATE VIRTUAL TABLE et2 USING echo(t2);
  526. }
  527. } {}
  528. do_test vtab1-5-2 {
  529. set echo_module ""
  530. execsql {
  531. SELECT * FROM et1, et2;
  532. }
  533. } [list \
  534. 1 red green 1 spades clubs \
  535. 1 red green 2 hearts diamonds \
  536. 2 blue black 1 spades clubs \
  537. 2 blue black 2 hearts diamonds \
  538. ]
  539. do_test vtab1-5-3 {
  540. filter $echo_module
  541. } [list \
  542. xFilter {SELECT rowid, * FROM 't1'} \
  543. xFilter {SELECT rowid, * FROM 't2'} \
  544. xFilter {SELECT rowid, * FROM 't2'} \
  545. ]
  546. do_test vtab1-5-4 {
  547. set echo_module ""
  548. execsql {
  549. SELECT * FROM et1, et2 WHERE et2.d = 2;
  550. }
  551. } [list \
  552. 1 red green 2 hearts diamonds \
  553. 2 blue black 2 hearts diamonds \
  554. ]
  555. do_test vtab1-5-5 {
  556. filter $echo_module
  557. } [list \
  558. xFilter {SELECT rowid, * FROM 't1'} \
  559. xFilter {SELECT rowid, * FROM 't2'} \
  560. xFilter {SELECT rowid, * FROM 't2'} \
  561. ]
  562. do_test vtab1-5-6 {
  563. execsql {
  564. CREATE INDEX i1 ON t2(d);
  565. }
  566. db close
  567. sqlite3 db test.db
  568. register_echo_module [sqlite3_connection_pointer db]
  569. set ::echo_module ""
  570. execsql {
  571. SELECT * FROM et1, et2 WHERE et2.d = 2;
  572. }
  573. } [list \
  574. 1 red green 2 hearts diamonds \
  575. 2 blue black 2 hearts diamonds \
  576. ]
  577. do_test vtab1-5-7 {
  578. filter $::echo_module
  579. } [list \
  580. xFilter {SELECT rowid, * FROM 't2' WHERE d = ?} \
  581. xFilter {SELECT rowid, * FROM 't1'} \
  582. ]
  583. execsql {
  584. DROP TABLE t1;
  585. DROP TABLE t2;
  586. DROP TABLE et1;
  587. DROP TABLE et2;
  588. }
  589. #----------------------------------------------------------------------
  590. # Test cases vtab1-6 test INSERT, UPDATE and DELETE operations
  591. # on virtual tables.
  592. do_test vtab1-6-1 {
  593. execsql { SELECT sql FROM sqlite_master }
  594. } {}
  595. do_test vtab1-6-2 {
  596. execsql {
  597. CREATE TABLE treal(a PRIMARY KEY, b, c);
  598. CREATE VIRTUAL TABLE techo USING echo(treal);
  599. SELECT name FROM sqlite_master WHERE type = 'table';
  600. }
  601. } {treal techo}
  602. do_test vtab1-6-3.1.1 {
  603. execsql {
  604. PRAGMA count_changes=ON;
  605. INSERT INTO techo VALUES(1, 2, 3);
  606. }
  607. } {1}
  608. do_test vtab1-6-3.1.2 {
  609. db changes
  610. } {1}
  611. do_test vtab1-6-3.2 {
  612. execsql {
  613. SELECT * FROM techo;
  614. }
  615. } {1 2 3}
  616. do_test vtab1-6-4.1 {
  617. execsql {
  618. UPDATE techo SET a = 5;
  619. }
  620. db changes
  621. } {1}
  622. do_test vtab1-6-4.2 {
  623. execsql {
  624. SELECT * FROM techo;
  625. }
  626. } {5 2 3}
  627. do_test vtab1-6-4.3 {
  628. execsql {
  629. UPDATE techo SET a=6 WHERE a<0;
  630. }
  631. db changes
  632. } {0}
  633. do_test vtab1-6-4.4 {
  634. execsql {
  635. SELECT * FROM techo;
  636. }
  637. } {5 2 3}
  638. do_test vtab1-6-5.1 {
  639. execsql {
  640. UPDATE techo set a = a||b||c;
  641. }
  642. db changes
  643. } {1}
  644. do_test vtab1-6-5.2 {
  645. execsql {
  646. SELECT * FROM techo;
  647. }
  648. } {523 2 3}
  649. do_test vtab1-6-6.1 {
  650. execsql {
  651. UPDATE techo set rowid = 10;
  652. }
  653. db changes
  654. } {1}
  655. do_test vtab1-6-6.2 {
  656. execsql {
  657. SELECT rowid FROM techo;
  658. }
  659. } {10}
  660. do_test vtab1-6-7.1.1 {
  661. execsql {
  662. INSERT INTO techo VALUES(11,12,13);
  663. }
  664. } {1}
  665. do_test vtab1-6-7.1.2 {
  666. db changes
  667. } {1}
  668. do_test vtab1-6-7.2 {
  669. execsql {
  670. SELECT * FROM techo ORDER BY a;
  671. }
  672. } {11 12 13 523 2 3}
  673. do_test vtab1-6-7.3 {
  674. execsql {
  675. UPDATE techo SET b=b+1000
  676. }
  677. db changes
  678. } {2}
  679. do_test vtab1-6-7.4 {
  680. execsql {
  681. SELECT * FROM techo ORDER BY a;
  682. }
  683. } {11 1012 13 523 1002 3}
  684. do_test vtab1-6-8.1 {
  685. execsql {
  686. DELETE FROM techo WHERE a=5;
  687. }
  688. db changes
  689. } {0}
  690. do_test vtab1-6-8.2 {
  691. execsql {
  692. SELECT * FROM techo ORDER BY a;
  693. }
  694. } {11 1012 13 523 1002 3}
  695. do_test vtab1-6-8.3 {
  696. execsql {
  697. DELETE FROM techo;
  698. }
  699. db changes
  700. } {2}
  701. do_test vtab1-6-8.4 {
  702. execsql {
  703. SELECT * FROM techo ORDER BY a;
  704. }
  705. } {}
  706. execsql {PRAGMA count_changes=OFF}
  707. forcedelete test2.db
  708. forcedelete test2.db-journal
  709. sqlite3 db2 test2.db
  710. execsql {
  711. CREATE TABLE techo(a PRIMARY KEY, b, c);
  712. } db2
  713. proc check_echo_table {tn} {
  714. set ::data1 [execsql {SELECT rowid, * FROM techo}]
  715. set ::data2 [execsql {SELECT rowid, * FROM techo} db2]
  716. do_test $tn {
  717. string equal $::data1 $::data2
  718. } 1
  719. }
  720. set tn 0
  721. foreach stmt [list \
  722. {INSERT INTO techo VALUES('abc', 'def', 'ghi')} \
  723. {INSERT INTO techo SELECT a||'.'||rowid, b, c FROM techo} \
  724. {INSERT INTO techo SELECT a||'x'||rowid, b, c FROM techo} \
  725. {INSERT INTO techo SELECT a||'y'||rowid, b, c FROM techo} \
  726. {DELETE FROM techo WHERE (oid % 3) = 0} \
  727. {UPDATE techo set rowid = 100 WHERE rowid = 1} \
  728. {INSERT INTO techo(a, b) VALUES('hello', 'world')} \
  729. {DELETE FROM techo} \
  730. ] {
  731. execsql $stmt
  732. execsql $stmt db2
  733. check_echo_table vtab1-6.8.[incr tn]
  734. }
  735. db2 close
  736. #----------------------------------------------------------------------
  737. # Test cases vtab1-7 tests that the value returned by
  738. # sqlite3_last_insert_rowid() is set correctly when rows are inserted
  739. # into virtual tables.
  740. do_test vtab1.7-1 {
  741. execsql {
  742. CREATE TABLE real_abc(a PRIMARY KEY, b, c);
  743. CREATE VIRTUAL TABLE echo_abc USING echo(real_abc);
  744. }
  745. } {}
  746. do_test vtab1.7-2 {
  747. execsql {
  748. INSERT INTO echo_abc VALUES(1, 2, 3);
  749. SELECT last_insert_rowid();
  750. }
  751. } {1}
  752. do_test vtab1.7-3 {
  753. execsql {
  754. INSERT INTO echo_abc(rowid) VALUES(31427);
  755. SELECT last_insert_rowid();
  756. }
  757. } {31427}
  758. do_test vtab1.7-4 {
  759. execsql {
  760. INSERT INTO echo_abc SELECT a||'.v2', b, c FROM echo_abc;
  761. SELECT last_insert_rowid();
  762. }
  763. } {31429}
  764. do_test vtab1.7-5 {
  765. execsql {
  766. SELECT rowid, a, b, c FROM echo_abc
  767. }
  768. } [list 1 1 2 3 \
  769. 31427 {} {} {} \
  770. 31428 1.v2 2 3 \
  771. 31429 {} {} {} \
  772. ]
  773. # Now test that DELETE and UPDATE operations do not modify the value.
  774. do_test vtab1.7-6 {
  775. execsql {
  776. UPDATE echo_abc SET c = 5 WHERE b = 2;
  777. SELECT last_insert_rowid();
  778. }
  779. } {31429}
  780. do_test vtab1.7-7 {
  781. execsql {
  782. UPDATE echo_abc SET rowid = 5 WHERE rowid = 1;
  783. SELECT last_insert_rowid();
  784. }
  785. } {31429}
  786. do_test vtab1.7-8 {
  787. execsql {
  788. DELETE FROM echo_abc WHERE b = 2;
  789. SELECT last_insert_rowid();
  790. }
  791. } {31429}
  792. do_test vtab1.7-9 {
  793. execsql {
  794. SELECT rowid, a, b, c FROM echo_abc
  795. }
  796. } [list 31427 {} {} {} \
  797. 31429 {} {} {} \
  798. ]
  799. do_test vtab1.7-10 {
  800. execsql {
  801. DELETE FROM echo_abc WHERE b = 2;
  802. SELECT last_insert_rowid();
  803. }
  804. } {31429}
  805. do_test vtab1.7-11 {
  806. execsql {
  807. SELECT rowid, a, b, c FROM real_abc
  808. }
  809. } [list 31427 {} {} {} \
  810. 31429 {} {} {} \
  811. ]
  812. do_test vtab1.7-12 {
  813. execsql {
  814. DELETE FROM echo_abc;
  815. SELECT last_insert_rowid();
  816. }
  817. } {31429}
  818. do_test vtab1.7-13 {
  819. execsql {
  820. SELECT rowid, a, b, c FROM real_abc
  821. }
  822. } {}
  823. ifcapable attach {
  824. do_test vtab1.8-1 {
  825. set echo_module ""
  826. execsql {
  827. ATTACH 'test2.db' AS aux;
  828. CREATE VIRTUAL TABLE aux.e2 USING echo(real_abc);
  829. }
  830. set echo_module
  831. } [list xCreate echo aux e2 real_abc \
  832. xSync echo(real_abc) \
  833. xCommit echo(real_abc) \
  834. ]
  835. }
  836. do_test vtab1.8-2 {
  837. catchsql {
  838. DROP TABLE aux.e2;
  839. }
  840. execsql {
  841. DROP TABLE treal;
  842. DROP TABLE techo;
  843. DROP TABLE echo_abc;
  844. DROP TABLE real_abc;
  845. }
  846. } {}
  847. do_test vtab1.9-1 {
  848. set echo_module ""
  849. execsql {
  850. CREATE TABLE r(a, b, c);
  851. CREATE VIRTUAL TABLE e USING echo(r, e_log);
  852. SELECT name FROM sqlite_master;
  853. }
  854. } {r e e_log}
  855. do_test vtab1.9-2 {
  856. execsql {
  857. DROP TABLE e;
  858. SELECT name FROM sqlite_master;
  859. }
  860. } {r}
  861. do_test vtab1.9-3 {
  862. set echo_module ""
  863. execsql {
  864. CREATE VIRTUAL TABLE e USING echo(r, e_log, virtual 1 2 3 varchar(32));
  865. }
  866. set echo_module
  867. } [list \
  868. xCreate echo main e r e_log {virtual 1 2 3 varchar(32)} \
  869. xSync echo(r) \
  870. xCommit echo(r) \
  871. ]
  872. do_test vtab1.10-1 {
  873. execsql {
  874. CREATE TABLE del(d);
  875. CREATE VIRTUAL TABLE e2 USING echo(del);
  876. }
  877. db close
  878. sqlite3 db test.db
  879. register_echo_module [sqlite3_connection_pointer db]
  880. execsql {
  881. DROP TABLE del;
  882. }
  883. catchsql {
  884. SELECT * FROM e2;
  885. }
  886. } {1 {vtable constructor failed: e2}}
  887. do_test vtab1.10-2 {
  888. set rc [catch {
  889. set ptr [sqlite3_connection_pointer db]
  890. sqlite3_declare_vtab $ptr {CREATE TABLE abc(a, b, c)}
  891. } msg]
  892. list $rc $msg
  893. } {1 {library routine called out of sequence}}
  894. do_test vtab1.10-3 {
  895. set ::echo_module_begin_fail r
  896. catchsql {
  897. INSERT INTO e VALUES(1, 2, 3);
  898. }
  899. } {1 {SQL logic error or missing database}}
  900. do_test vtab1.10-4 {
  901. catch {execsql {
  902. EXPLAIN SELECT * FROM e WHERE rowid = 2;
  903. EXPLAIN QUERY PLAN SELECT * FROM e WHERE rowid = 2 ORDER BY rowid;
  904. }}
  905. } {0}
  906. do_test vtab1.10-5 {
  907. set echo_module ""
  908. execsql {
  909. SELECT * FROM e WHERE rowid||'' MATCH 'pattern';
  910. }
  911. set echo_module
  912. } [list \
  913. xBestIndex {SELECT rowid, * FROM 'r'} \
  914. xFilter {SELECT rowid, * FROM 'r'} \
  915. ]
  916. proc match_func {args} {return ""}
  917. do_test vtab1.10-6 {
  918. set echo_module ""
  919. db function match match_func
  920. execsql {
  921. SELECT * FROM e WHERE match('pattern', rowid, 'pattern2');
  922. }
  923. set echo_module
  924. } [list \
  925. xBestIndex {SELECT rowid, * FROM 'r'} \
  926. xFilter {SELECT rowid, * FROM 'r'} \
  927. ]
  928. # Testing the xFindFunction interface
  929. #
  930. catch {rename ::echo_glob_overload {}}
  931. do_test vtab1.11-1 {
  932. execsql {
  933. INSERT INTO r(a,b,c) VALUES(1,'?',99);
  934. INSERT INTO r(a,b,c) VALUES(2,3,99);
  935. SELECT a GLOB b FROM e
  936. }
  937. } {1 0}
  938. proc ::echo_glob_overload {a b} {
  939. return [list $b $a]
  940. }
  941. do_test vtab1.11-2 {
  942. execsql {
  943. SELECT a like 'b' FROM e
  944. }
  945. } {0 0}
  946. do_test vtab1.11-3 {
  947. execsql {
  948. SELECT a glob '2' FROM e
  949. }
  950. } {{1 2} {2 2}}
  951. do_test vtab1.11-4 {
  952. execsql {
  953. SELECT glob('2',a) FROM e
  954. }
  955. } {0 1}
  956. do_test vtab1.11-5 {
  957. execsql {
  958. SELECT glob(a,'2') FROM e
  959. }
  960. } {{2 1} {2 2}}
  961. #----------------------------------------------------------------------
  962. # Test the outcome if a constraint is encountered half-way through
  963. # a multi-row INSERT that is inside a transaction
  964. #
  965. do_test vtab1.12-1 {
  966. execsql {
  967. CREATE TABLE b(a, b, c);
  968. CREATE TABLE c(a UNIQUE, b, c);
  969. INSERT INTO b VALUES(1, 'A', 'B');
  970. INSERT INTO b VALUES(2, 'C', 'D');
  971. INSERT INTO b VALUES(3, 'E', 'F');
  972. INSERT INTO c VALUES(3, 'G', 'H');
  973. CREATE VIRTUAL TABLE echo_c USING echo(c);
  974. }
  975. } {}
  976. # First test outside of a transaction.
  977. do_test vtab1.12-2 {
  978. catchsql { INSERT INTO echo_c SELECT * FROM b; }
  979. } {1 {echo-vtab-error: column a is not unique}}
  980. do_test vtab1.12-2.1 {
  981. sqlite3_errmsg db
  982. } {echo-vtab-error: column a is not unique}
  983. do_test vtab1.12-3 {
  984. execsql { SELECT * FROM c }
  985. } {3 G H}
  986. # Now the real test - wrapped in a transaction.
  987. do_test vtab1.12-4 {
  988. execsql {BEGIN}
  989. catchsql { INSERT INTO echo_c SELECT * FROM b; }
  990. } {1 {echo-vtab-error: column a is not unique}}
  991. do_test vtab1.12-5 {
  992. execsql { SELECT * FROM c }
  993. } {3 G H}
  994. do_test vtab1.12-6 {
  995. execsql { COMMIT }
  996. execsql { SELECT * FROM c }
  997. } {3 G H}
  998. # At one point (ticket #2759), a WHERE clause of the form "<column> IS NULL"
  999. # on a virtual table was causing an assert() to fail in the compiler.
  1000. #
  1001. # "IS NULL" clauses should not be passed through to the virtual table
  1002. # implementation. They are handled by SQLite after the vtab returns its
  1003. # data.
  1004. #
  1005. do_test vtab1.13-1 {
  1006. execsql {
  1007. SELECT * FROM echo_c WHERE a IS NULL
  1008. }
  1009. } {}
  1010. do_test vtab1.13-2 {
  1011. execsql {
  1012. INSERT INTO c VALUES(NULL, 15, 16);
  1013. SELECT * FROM echo_c WHERE a IS NULL
  1014. }
  1015. } {{} 15 16}
  1016. do_test vtab1.13-3 {
  1017. execsql {
  1018. INSERT INTO c VALUES(15, NULL, 16);
  1019. SELECT * FROM echo_c WHERE b IS NULL
  1020. }
  1021. } {15 {} 16}
  1022. do_test vtab1.13-3 {
  1023. execsql {
  1024. SELECT * FROM echo_c WHERE b IS NULL AND a = 15;
  1025. }
  1026. } {15 {} 16}
  1027. do_test vtab1-14.1 {
  1028. execsql { DELETE FROM c }
  1029. set echo_module ""
  1030. execsql { SELECT * FROM echo_c WHERE rowid IN (1, 2, 3) }
  1031. set echo_module
  1032. } [list xBestIndex {SELECT rowid, * FROM 'c'} xFilter {SELECT rowid, * FROM 'c'}]
  1033. do_test vtab1-14.2 {
  1034. set echo_module ""
  1035. execsql { SELECT * FROM echo_c WHERE rowid = 1 }
  1036. set echo_module
  1037. } [list xBestIndex {SELECT rowid, * FROM 'c' WHERE rowid = ?} xFilter {SELECT rowid, * FROM 'c' WHERE rowid = ?} 1]
  1038. do_test vtab1-14.3 {
  1039. set echo_module ""
  1040. execsql { SELECT * FROM echo_c WHERE a = 1 }
  1041. set echo_module
  1042. } [list xBestIndex {SELECT rowid, * FROM 'c' WHERE a = ?} xFilter {SELECT rowid, * FROM 'c' WHERE a = ?} 1]
  1043. do_test vtab1-14.4 {
  1044. set echo_module ""
  1045. execsql { SELECT * FROM echo_c WHERE a IN (1, 2) }
  1046. set echo_module
  1047. } [list xBestIndex {SELECT rowid, * FROM 'c'} xFilter {SELECT rowid, * FROM 'c'}]
  1048. do_test vtab1-15.1 {
  1049. execsql {
  1050. CREATE TABLE t1(a, b, c);
  1051. CREATE VIRTUAL TABLE echo_t1 USING echo(t1);
  1052. }
  1053. } {}
  1054. do_test vtab1-15.2 {
  1055. execsql {
  1056. INSERT INTO echo_t1(rowid) VALUES(45);
  1057. SELECT rowid, * FROM echo_t1;
  1058. }
  1059. } {45 {} {} {}}
  1060. do_test vtab1-15.3 {
  1061. execsql {
  1062. INSERT INTO echo_t1(rowid) VALUES(NULL);
  1063. SELECT rowid, * FROM echo_t1;
  1064. }
  1065. } {45 {} {} {} 46 {} {} {}}
  1066. do_test vtab1-15.4 {
  1067. catchsql {
  1068. INSERT INTO echo_t1(rowid) VALUES('new rowid');
  1069. }
  1070. } {1 {datatype mismatch}}
  1071. # The following tests - vtab1-16.* - are designed to test that setting
  1072. # sqlite3_vtab.zErrMsg variable can be used by the vtab interface to
  1073. # return an error message to the user.
  1074. #
  1075. do_test vtab1-16.1 {
  1076. execsql {
  1077. CREATE TABLE t2(a PRIMARY KEY, b, c);
  1078. INSERT INTO t2 VALUES(1, 2, 3);
  1079. INSERT INTO t2 VALUES(4, 5, 6);
  1080. CREATE VIRTUAL TABLE echo_t2 USING echo(t2);
  1081. }
  1082. } {}
  1083. set tn 2
  1084. foreach method [list \
  1085. xBestIndex \
  1086. xOpen \
  1087. xFilter \
  1088. xNext \
  1089. xColumn \
  1090. xRowid \
  1091. ] {
  1092. do_test vtab1-16.$tn {
  1093. set echo_module_fail($method,t2) "the $method method has failed"
  1094. catchsql { SELECT rowid, * FROM echo_t2 WHERE a >= 1 }
  1095. } "1 {echo-vtab-error: the $method method has failed}"
  1096. unset echo_module_fail($method,t2)
  1097. incr tn
  1098. }
  1099. foreach method [list \
  1100. xUpdate \
  1101. xBegin \
  1102. xSync \
  1103. ] {
  1104. do_test vtab1-16.$tn {
  1105. set echo_module_fail($method,t2) "the $method method has failed"
  1106. catchsql { INSERT INTO echo_t2 VALUES(7, 8, 9) }
  1107. } "1 {echo-vtab-error: the $method method has failed}"
  1108. unset echo_module_fail($method,t2)
  1109. incr tn
  1110. }
  1111. ifcapable altertable {
  1112. do_test vtab1-16.$tn {
  1113. set echo_module_fail(xRename,t2) "the xRename method has failed"
  1114. catchsql { ALTER TABLE echo_t2 RENAME TO another_name }
  1115. } "1 {echo-vtab-error: the xRename method has failed}"
  1116. unset echo_module_fail(xRename,t2)
  1117. incr tn
  1118. }
  1119. # The following test case exposes an instance in sqlite3_declare_vtab()
  1120. # an error message was set using a call similar to sqlite3_mprintf(zErr),
  1121. # where zErr is an arbitrary string. This is no good if the string contains
  1122. # characters that can be mistaken for printf() formatting directives.
  1123. #
  1124. do_test vtab1-17.1 {
  1125. execsql {
  1126. PRAGMA writable_schema = 1;
  1127. INSERT INTO sqlite_master VALUES(
  1128. 'table', 't3', 't3', 0, 'INSERT INTO "%s%s" VALUES(1)'
  1129. );
  1130. }
  1131. catchsql { CREATE VIRTUAL TABLE t4 USING echo(t3); }
  1132. } {1 {vtable constructor failed: t4}}
  1133. # This test verifies that ticket 48f29963 is fixed.
  1134. #
  1135. do_test vtab1-17.1 {
  1136. execsql {
  1137. CREATE TABLE t5(a, b);
  1138. CREATE VIRTUAL TABLE e5 USING echo_v2(t5);
  1139. BEGIN;
  1140. INSERT INTO e5 VALUES(1, 2);
  1141. DROP TABLE e5;
  1142. SAVEPOINT one;
  1143. ROLLBACK TO one;
  1144. COMMIT;
  1145. }
  1146. } {}
  1147. do_test vtab1-17.2 {
  1148. execsql { DELETE FROM sqlite_master WHERE sql LIKE 'insert%' }
  1149. } {}
  1150. #-------------------------------------------------------------------------
  1151. # The following tests - vtab1-18.* - test that the optimization of LIKE
  1152. # constraints in where.c plays well with virtual tables.
  1153. #
  1154. # 18.1.*: Case-insensitive LIKE.
  1155. # 18.2.*: Case-sensitive LIKE.
  1156. #
  1157. unset -nocomplain echo_module_begin_fail
  1158. do_execsql_test 18.1.0 {
  1159. CREATE TABLE t6(a, b TEXT);
  1160. CREATE INDEX i6 ON t6(b, a);
  1161. INSERT INTO t6 VALUES(1, 'Peter');
  1162. INSERT INTO t6 VALUES(2, 'Andrew');
  1163. INSERT INTO t6 VALUES(3, 'James');
  1164. INSERT INTO t6 VALUES(4, 'John');
  1165. INSERT INTO t6 VALUES(5, 'Phillip');
  1166. INSERT INTO t6 VALUES(6, 'Bartholomew');
  1167. CREATE VIRTUAL TABLE e6 USING echo(t6);
  1168. }
  1169. foreach {tn sql res filter} {
  1170. 1.1 "SELECT a FROM e6 WHERE b>'James'" {4 1 5}
  1171. {xFilter {SELECT rowid, * FROM 't6' WHERE b > ?} James}
  1172. 1.2 "SELECT a FROM e6 WHERE b>='J' AND b<'K'" {3 4}
  1173. {xFilter {SELECT rowid, * FROM 't6' WHERE b >= ? AND b < ?} J K}
  1174. 1.3 "SELECT a FROM e6 WHERE b LIKE 'J%'" {3 4}
  1175. {xFilter {SELECT rowid, * FROM 't6'}}
  1176. 1.4 "SELECT a FROM e6 WHERE b LIKE 'j%'" {3 4}
  1177. {xFilter {SELECT rowid, * FROM 't6'}}
  1178. } {
  1179. set echo_module {}
  1180. do_execsql_test 18.$tn.1 $sql $res
  1181. do_test 18.$tn.2 { lrange $::echo_module 2 end } $filter
  1182. }
  1183. do_execsql_test 18.2.0 { PRAGMA case_sensitive_like = ON }
  1184. foreach {tn sql res filter} {
  1185. 2.1 "SELECT a FROM e6 WHERE b LIKE 'J%'" {3 4}
  1186. {xFilter {SELECT rowid, * FROM 't6'}}
  1187. 2.2 "SELECT a FROM e6 WHERE b LIKE 'j%'" {}
  1188. {xFilter {SELECT rowid, * FROM 't6'}}
  1189. } {
  1190. set echo_module {}
  1191. do_execsql_test 18.$tn.1 $sql $res
  1192. do_test 18.$tn.2 { lrange $::echo_module 2 end } $filter
  1193. }
  1194. do_execsql_test 18.2.x { PRAGMA case_sensitive_like = OFF }
  1195. #-------------------------------------------------------------------------
  1196. # Test that an existing module may not be overridden.
  1197. #
  1198. do_test 19.1 {
  1199. sqlite3 db2 test.db
  1200. register_echo_module [sqlite3_connection_pointer db2]
  1201. } SQLITE_OK
  1202. do_test 19.2 {
  1203. register_echo_module [sqlite3_connection_pointer db2]
  1204. } SQLITE_MISUSE
  1205. do_test 19.3 {
  1206. db2 close
  1207. } {}
  1208. finish_test