/ Check-in [d9e4f957]
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

 ```17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 ``` ```set testdir [file dirname \$argv0] source \$testdir/tester.tcl load_static_extension db eval do_execsql_test btree02-100 { CREATE TABLE t1(a TEXT, ax INTEGER, b INT, PRIMARY KEY(a,ax)) WITHOUT ROWID; WITH RECURSIVE c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<10) INSERT INTO t1(a,ax,b) SELECT printf('%02x',i), random(), i FROM c; CREATE INDEX t1a ON t1(a); CREATE TABLE t2(x,y); CREATE TABLE t3(cnt); WITH RECURSIVE c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<4) INSERT INTO t3(cnt) SELECT i FROM c; SELECT count(*) FROM t1; } {10} do_test btree02-110 { db eval BEGIN set i 0 db eval {SELECT a, ax, b, cnt FROM t1 CROSS JOIN t3 WHERE b IS NOT NULL} { if {\$a==""} {set a 0} if {\$b==""} {set b 0} db eval {INSERT INTO t2(x,y) VALUES(\$b,\$cnt)} # puts "a,b,cnt = (\$a,\$b,\$cnt)" incr i if {\$i%2==1} { set bx [expr {\$b+1000}] # puts "INSERT (\$a),\$bx" db eval {INSERT INTO t1(a,ax,b) VALUES(printf('(%s)',\$a),random(),\$bx)} } else { # puts "DELETE a=\$a" db eval {DELETE FROM t1 WHERE a=\$a} } db eval {COMMIT; BEGIN} } db one {COMMIT; SELECT count(*) FROM t1;} } {27} finish_test ``` ``` | > > > > > > | | | | > > | ``` ```17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 ``` ```set testdir [file dirname \$argv0] source \$testdir/tester.tcl load_static_extension db eval do_execsql_test btree02-100 { CREATE TABLE t1(a TEXT, ax INTEGER, b INT, PRIMARY KEY(a,ax)) WITHOUT ROWID; WITH RECURSIVE c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<10) INSERT INTO t1(a,ax,b) SELECT printf('%02x',i+160), random(), i FROM c; CREATE INDEX t1a ON t1(a); CREATE TABLE t2(x,y); CREATE TABLE t3(cnt); WITH RECURSIVE c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<4) INSERT INTO t3(cnt) SELECT i FROM c; SELECT count(*) FROM t1; } {10} proc showt1 {} { puts -nonewline "t1: " puts [db eval {SELECT printf('(%s,%s)',quote(a),quote(b)) FROM t1}] } do_test btree02-110 { db eval BEGIN set i 0 # showt1 db eval {SELECT a, ax, b, cnt FROM t1 CROSS JOIN t3 WHERE b IS NOT NULL} { if {\$a==""} continue db eval {INSERT INTO t2(x,y) VALUES(\$b,\$cnt)} # puts "a,b,cnt = (\$a,\$b,\$cnt)" incr i if {\$i%2==1} { set bx [expr {\$b+1000}] # puts "INSERT (\$a),\$bx" db eval {INSERT INTO t1(a,ax,b) VALUES(printf('(%s)',\$a),random(),\$bx)} # showt1 } else { # puts "DELETE a=\$a" db eval {DELETE FROM t1 WHERE a=\$a} # showt1 } db eval {COMMIT; BEGIN} } db one {COMMIT; SELECT count(*) FROM t1;} } {10} finish_test ```