#! /bin/env tclsh
proc suite_main {} {
variable setup1
package require {ycl list}
namespace import [yclprefix]::list::sl
package require {ycl test}
[yclprefix]::test::init
package require {ycl test data}
namespace upvar [yclprefix]::test cleanup1 cleanup1
package require sqlite3
package require {ycl sql sqlite}
namespace import [yclprefix]::sql::sqlite::gen
variable setup1 {
catch {
rename eav1 {}
}
catch {
rename eav2 {}
}
catch {
rename accounting {}
}
[yclprefix] test data eav1 eav1
[yclprefix] test data eav2 eav2
[yclprefix] test data accounting accounting
[yclprefix] test data sqlsyntax sqlsyntax
sqlite3 simple :memory:
simple eval {
create table t (english , spanish)
; insert into t values
('run' , 'correr')
}
}
variable cleanup1 [string cat [set [yclprefix]::test::cleanup1] {
rename simple {}
}]
test basic {} -setup $setup1 -body {
set res [gen select {
table t
report {
english
}
}]
dict with res {}
set res {}
dict with vars [list simple eval $sql values {
lappend res $values(english)
}]
return $res
} -cleanup [string cat $cleanup1 \n {
}] -result run
test basic_concise {} -setup $setup1 -body {
set res [gen select {
t
{
english
spanish
}
}]
dict with res {}
set res {}
dict with vars [list simple eval $sql values {
lappend res $values(english)
lappend res $values(spanish)
}]
return $res
} -result {run correr}
test bindmap {
A table with no arguments becomes the default table
} -setup $setup1 -body {
set x first_name_id
set res [gen select {
table a {
as id id1 context context1 \
what what1 attribute attribute1 value $x
table s {
as id sid
as value svalue
== [col $x] [col sid]
table v {
as id vid
as value vvalue
== [col svalue] [col vid]
}
}
== [col vvalue] [str William]
}
report {
id1
context1
what1
attribute1
$x
vvalue
}
}]
dict with res {}
set res {}
dict with vars {}
puts [list doop: $sql]
eav2 eval $sql values {
foreach vname {id1 context1 what1 attribute1 first_name_id
vvalue} {
lappend res $values($vname)
}
}
return $res
} -cleanup $cleanup1 -result {24 0 23 9 -1 William}
test expr_greater {
A table with no arguments becomes the default table
} -setup $setup1 -body {
set res [gen select {
table transaction {
> [col amount] [num 30]
}
report {
amount
}
}]
dict with res {}
set res {}
dict with vars {}
accounting eval $sql values {
lappend res $values(amount)
}
return $res
} -cleanup $cleanup1 -result {37.0 37.0}
test expr_like {
A table with no arguments becomes the default table
Also tests values of type var
} -setup $setup1 -body {
set x pers%
set res [gen select {
table v {
like [col value] [var x]
}
report {
value
}
}]
dict with res {}
set res {}
dict with vars {}
eav2 eval $sql values {
lappend res $values(value)
}
return $res
} -cleanup $cleanup1 -result {person}
test expr_multi {
A table with no arguments becomes the default table
Also tests values of type var
} -setup $setup1 -body {
set x %me
set y t%
set res [gen select {
table v {
like [col value] [var x]
like [col value] [var y]
or {
like [col value] [str ev%]
like [col value] [str %nt]
}
}
report {
value
}
}]
dict with res {}
set res {}
dict with vars {}
eav2 eval $sql values {
lappend res $values(value)
}
return $res
} -cleanup $cleanup1 -result {time event}
test expr_func {
A table with no arguments becomes the default table
Also tests values of type var
} -setup $setup1 -body {
set x %me
set y t%
set res [gen select {
table v {
like [col value] [var x]
like [col value] [var y]
or {
like [col value] [str ev%]
like [col value] [str %nt]
}
}
report {
value
}
}]
dict with res {}
set res {}
dict with vars {}
puts [list huh $sql]
eav2 eval $sql values {
lappend res $values(value)
}
return $res
} -cleanup $cleanup1 -result {time event}
test join_left {} -setup $setup1 -body {
set res [gen select {
table a {
as id ida value valuea
left table v {
as id idv value valuev
== valuea col idv col
}
!= valuev col {} str
or {
== idv col 2 num
== idv col 4 num
}
}
report {
ida
valuev
}
}]
dict with res {}
set res {}
dict with vars {}
puts [list wheee $sql]
eav2 eval $sql values {
lappend res [list $values(ida) $values(valuev)]
}
return $res
} -cleanup $cleanup1 -result [sl {
}]
test test_union {
} -setup $setup1 -body {
set x %me
set y t%
set res [gen select {
table v {
like value col x var
like value col y var
or (
like value col ev% str
like value col %nt str
)
union table v {
like value col pers% str
}
}
report {
value
}
}]
dict with res {}
set res {}
dict with vars {}
eav2 eval $sql values {
lappend res $values(value)
}
return $res
} -cleanup $cleanup1 -result {time event person}
test recursive {
A table with no arguments becomes the default table
Also tests values of type var
} -setup $setup1 -body {
set sql {
with recursive
t1(value , next) as (
select value , next from s
where id = -1
union all
select s1.value, s1.next from s as s1, t1
where t1.next == s1.id)
select value from t1
}
eav2 eval $sql values {
lappend res $values(value)
}
return $res
} -cleanup $cleanup1 -result {24 26}
test sql {
} -setup $setup1 -body {
eav2 eval {select (select sum(value) + 88 from s), 3 from v } values {
lappend res [array get values]
}
return $res
} -cleanup $cleanup1 -result {24 26}
cleanupTests
}