WebSVN – wasCSharpSQLite – Blame – Rev 1

1

office

1

# 2010 September 24

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

#

12

# This file implements tests to verify that the "testable statements" in

13

# the lang_select.html document are correct.

14

#

15

16

set testdir [file dirname $argv0]

17

source $testdir/tester.tcl

18

19

#-------------------------------------------------------------------------

# te_* commands:

#

#

# te_read_sql DB SELECT-STATEMENT

24

# te_read_tbl DB TABLENAME

25

#

26

# These two commands are used to read a dataset from the database. A dataset

27

# consists of N rows of M named columns of values each, where each value has a

28

# type (null, integer, real, text or blob) and a value within the types domain.

29

# The tcl format for a "dataset" is a list of two elements:

30

#

31

# * A list of the column names.

32

# * A list of data rows. Each row is itself a list, where each element is

33

# the contents of a column of the row. Each of these is a list of two

34

# elements, the type name and the actual value.

35

#

36

# For example, the contents of table [t1] as a dataset is:

37

#

38

# CREATE TABLE t1(a, b);

39

# INSERT INTO t1 VALUES('abc', NULL);

40

# INSERT INTO t1 VALUES(43.1, 22);

41

#

42

# {a b} {{{TEXT abc} {NULL {}}} {{REAL 43.1} {INTEGER 22}}}

43

#

44

# The [te_read_tbl] command returns a dataset read from a table. The

45

# [te_read_sql] returns the dataset that results from executing a SELECT

# command.

#

#

# te_tbljoin ?SWITCHES? LHS-TABLE RHS-TABLE

50

# te_join ?SWITCHES? LHS-DATASET RHS-DATASET

51

#

52

# This command joins the two datasets and returns the resulting dataset. If

53

# there are no switches specified, then the results is the cartesian product

54

# of the two inputs. The [te_tbljoin] command reads the left and right-hand

55

# datasets from the specified tables. The [te_join] command is passed the

56

# datasets directly.

57

#

58

# Optional switches are as follows:

59

#

60

# -on SCRIPT

61

# -using COLUMN-LIST

62

# -left

63

#

64

# The -on option specifies a tcl script that is executed for each row in the

65

# cartesian product of the two datasets. The script has 4 arguments appended

66

# to it, in the following order:

67

#

68

# * The list of column-names from the left-hand dataset.

69

# * A single row from the left-hand dataset (one "data row" list as

70

# described above.

71

# * The list of column-names from the right-hand dataset.

72

# * A single row from the right-hand dataset.

73

#

74

# The script must return a boolean value - true if the combination of rows

75

# should be included in the output dataset, or false otherwise.

76

#

77

# The -using option specifies a list of the columns from the right-hand

78

# dataset that should be omitted from the output dataset.

79

#

80

# If the -left option is present, the join is done LEFT JOIN style.

81

# Specifically, an extra row is inserted if after the -on script is run there

82

# exist rows in the left-hand dataset that have no corresponding rows in

83

# the output. See the implementation for more specific comments.

84

#

85

#

86

# te_equals ?SWITCHES? COLNAME1 COLNAME2 <-on script args>

87

#

88

# The only supported switch is "-nocase". If it is present, then text values

89

# are compared in a case-independent fashion. Otherwise, they are compared

90

# as if using the SQLite BINARY collation sequence.

91

#

92

#

93

# te_and ONSCRIPT1 ONSCRIPT2...

#

#

#

# te_read_tbl DB TABLENAME

100

# te_read_sql DB SELECT-STATEMENT

101

#

102

# These two procs are used to extract datasets from the database, either

103

# by reading the contents of a named table (te_read_tbl), or by executing

104

# a SELECT statement (t3_read_sql).

105

#

106

# See the comment above, describing "te_* commands", for details of the

107

# return values.

108

#

109

proc te_read_tbl {db tbl} {

110

te_read_sql $db "SELECT * FROM '$tbl'"

111

}

112

proc te_read_sql {db sql} {

113

set S [sqlite3_prepare_v2 $db $sql -1 DUMMY]

114

115

set cols [list]

116

for {set i 0} {$i < [sqlite3_column_count $S]} {incr i} {

117

lappend cols [sqlite3_column_name $S $i]

}

set rows [list]

while {[sqlite3_step $S] == "SQLITE_ROW"} {

122

set r [list]

123

for {set i 0} {$i < [sqlite3_column_count $S]} {incr i} {

124

lappend r [list [sqlite3_column_type $S $i] [sqlite3_column_text $S $i]]

}

lappend rows $r

}

sqlite3_finalize $S

129

130

return [list $cols $rows]

}

#-------

# Usage: te_join <table-data1> <table-data2> <join spec>...

135

#

136

# Where a join-spec is an optional list of arguments as follows:

137

#

138

# ?-left?

139

# ?-using colname-list?

140

# ?-on on-expr-proc?

141

#

142

proc te_join {data1 data2 args} {

143

144

set testproc ""

145

set usinglist [list]

146

set isleft 0

147

for {set i 0} {$i < [llength $args]} {incr i} {

148

set a [lindex $args $i]

149

if { $a != "{}" } {

150

switch -- $a {

151

-on { set testproc [lindex $args [incr i]] }

152

-using { set usinglist [lindex $args [incr i]] }

153

-left { set isleft 1 }

154

default {

155

error "Unknown argument: $a"

}

}

}

}

set c1 [lindex $data1 0]

161

set c2 [lindex $data2 0]

162

set omitlist [list]

163

set nullrowlist [list]

set cret $c1

set cidx 0

foreach col $c2 {

set idx [lsearch $usinglist $col]

169

if {$idx>=0} {lappend omitlist $cidx}

170

if {$idx<0} {

171

lappend nullrowlist {NULL {}}

lappend cret $col

}

incr cidx

}

set omitlist [lsort -integer -decreasing $omitlist]

set rret [list]

foreach r1 [lindex $data1 1] {

181

set one 0

182

foreach r2 [lindex $data2 1] {

183

set ok 1

184

if {$testproc != ""} {

185

set ok [eval $testproc [list $c1 $r1 $c2 $r2]]

}

if {$ok} {

set one 1

foreach idx $omitlist {set r2 [lreplace $r2 $idx $idx]}

190

lappend rret [concat $r1 $r2]

}

}

if {$isleft && $one==0} {

195

lappend rret [concat $r1 $nullrowlist]

}

}

list $cret $rret

}

proc te_tbljoin {db t1 t2 args} {

203

te_join [te_read_tbl $db $t1] [te_read_tbl $db $t2] {*}$args

204

}

205

206

proc te_apply_affinity {affinity typevar valvar} {

207

upvar $typevar type

208

upvar $valvar val

209

210

switch -- $affinity {

211

integer {

212

if {[string is double $val]} { set type REAL }

213

if {[string is wideinteger $val]} { set type INTEGER }

214

if {$type == "REAL" && int($val)==$val} {

215

set type INTEGER

216

set val [expr {int($val)}]

}

}

text {

set type TEXT

}

none { }

default { error "invalid affinity: $affinity" }

}

}

#----------

# te_equals ?SWITCHES? c1 c2 cols1 row1 cols2 row2

230

#

231

proc te_equals {args} {

232

233

if {[llength $args]<6} {error "invalid arguments to te_equals"}

234

foreach {c1 c2 cols1 row1 cols2 row2} [lrange $args end-5 end] break

set nocase 0

set affinity none

for {set i 0} {$i < ([llength $args]-6)} {incr i} {

240

set a [lindex $args $i]

switch -- $a {

-nocase {

set nocase 1

}

-affinity {

set affinity [string tolower [lindex $args [incr i]]]

247

}

248

default {

249

error "invalid arguments to te_equals"

}

}

}

set idx2 [if {[string is integer $c2]} { set c2 } else { lsearch $cols2 $c2 }]

255

set idx1 [if {[string is integer $c1]} { set c1 } else { lsearch $cols1 $c1 }]

256

257

set t1 [lindex $row1 $idx1 0]

258

set t2 [lindex $row2 $idx2 0]

259

set v1 [lindex $row1 $idx1 1]

260

set v2 [lindex $row2 $idx2 1]

261

262

te_apply_affinity $affinity t1 v1

263

te_apply_affinity $affinity t2 v2

264

265

if {$t1 == "NULL" || $t2 == "NULL"} { return 0 }

266

if {$nocase && $t1 == "TEXT"} { set v1 [string tolower $v1] }

267

if {$nocase && $t2 == "TEXT"} { set v2 [string tolower $v2] }

268

269

270

set res [expr {$t1 == $t2 && [string equal $v1 $v2]}]

return $res

}

proc te_false {args} { return 0 }

275

proc te_true {args} { return 1 }

276

277

proc te_and {args} {

278

foreach a [lrange $args 0 end-4] {

279

set res [eval $a [lrange $args end-3 end]]

280

if {$res == 0} {return 0}

}

return 1

}

proc te_dataset_eq {testname got expected} {

287

uplevel #0 [list do_test $testname [list set {} $got] $expected]

288

}

289

proc te_dataset_eq_unordered {testname got expected} {

290

lset got 1 [lsort [lindex $got 1]]

291

lset expected 1 [lsort [lindex $expected 1]]

292

te_dataset_eq $testname $got $expected

293

}

294

295

proc te_dataset_ne {testname got unexpected} {

296

uplevel #0 [list do_test $testname [list string equal $got $unexpected] 0]

297

}

298

proc te_dataset_ne_unordered {testname got unexpected} {

299

lset got 1 [lsort [lindex $got 1]]

300

lset unexpected 1 [lsort [lindex $unexpected 1]]

301

te_dataset_ne $testname $got $unexpected

}

#-------------------------------------------------------------------------

306

#

307

proc test_join {tn sqljoin tbljoinargs} {

308

set sql [te_read_sql db "SELECT * FROM $sqljoin"]

309

set te [te_tbljoin db {*}$tbljoinargs]

310

te_dataset_eq_unordered $tn $sql $te

}

drop_all_tables

do_execsql_test e_select-2.0 {

315

CREATE TABLE t1(a, b);

316

CREATE TABLE t2(a, b);

317

CREATE TABLE t3(b COLLATE nocase);

318

319

INSERT INTO t1 VALUES(2, 'B');

320

INSERT INTO t1 VALUES(1, 'A');

321

INSERT INTO t1 VALUES(4, 'D');

322

INSERT INTO t1 VALUES(NULL, NULL);

323

INSERT INTO t1 VALUES(3, NULL);

324

325

INSERT INTO t2 VALUES(1, 'A');

326

INSERT INTO t2 VALUES(2, NULL);

327

INSERT INTO t2 VALUES(5, 'E');

328

INSERT INTO t2 VALUES(NULL, NULL);

329

INSERT INTO t2 VALUES(3, 'C');

330

331

INSERT INTO t3 VALUES('a');

332

INSERT INTO t3 VALUES('c');

333

INSERT INTO t3 VALUES('b');

334

} {}

335

336

foreach {tn indexes} {

337

e_select-2.1.1 { }

338

e_select-2.1.2 { CREATE INDEX i1 ON t1(a) }

339

e_select-2.1.3 { CREATE INDEX i1 ON t2(a) }

340

e_select-2.1.4 { CREATE INDEX i1 ON t3(b) }

341

} {

342

343

catchsql { DROP INDEX i1 }

344

catchsql { DROP INDEX i2 }

345

catchsql { DROP INDEX i3 }

346

execsql $indexes

347

348

# EVIDENCE-OF: R-46122-14930 If the join-op is "CROSS JOIN", "INNER

349

# JOIN", "JOIN" or a comma (",") and there is no ON or USING clause,

350

# then the result of the join is simply the cartesian product of the

351

# left and right-hand datasets.

352

#

353

# EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER

354

# JOIN", "JOIN" and "," join operators.

355

#

356

# EVIDENCE-OF: R-07544-24155 The "CROSS JOIN" join operator produces the

357

# same data as the "INNER JOIN", "JOIN" and "," operators

358

#

359

test_join $tn.1.1 "t1, t2" {t1 t2}

360

test_join $tn.1.2 "t1 INNER JOIN t2" {t1 t2}

361

test_join $tn.1.3 "t1 CROSS JOIN t2" {t1 t2}

362

test_join $tn.1.4 "t1 JOIN t2" {t1 t2}

363

test_join $tn.1.5 "t2, t3" {t2 t3}

364

test_join $tn.1.6 "t2 INNER JOIN t3" {t2 t3}

365

test_join $tn.1.7 "t2 CROSS JOIN t3" {t2 t3}

366

test_join $tn.1.8 "t2 JOIN t3" {t2 t3}

367

test_join $tn.1.9 "t2, t2 AS x" {t2 t2}

368

test_join $tn.1.10 "t2 INNER JOIN t2 AS x" {t2 t2}

369

test_join $tn.1.11 "t2 CROSS JOIN t2 AS x" {t2 t2}

370

test_join $tn.1.12 "t2 JOIN t2 AS x" {t2 t2}

371

372

# EVIDENCE-OF: R-22775-56496 If there is an ON clause specified, then

373

# the ON expression is evaluated for each row of the cartesian product

374

# as a boolean expression. All rows for which the expression evaluates

375

# to false are excluded from the dataset.

376

#

377

test_join $tn.2.1 "t1, t2 ON (t1.a=t2.a)" {t1 t2 -on {te_equals a a}}

378

test_join $tn.2.2 "t2, t1 ON (t1.a=t2.a)" {t2 t1 -on {te_equals a a}}

379

test_join $tn.2.3 "t2, t1 ON (1)" {t2 t1 -on te_true}

380

test_join $tn.2.4 "t2, t1 ON (NULL)" {t2 t1 -on te_false}

381

test_join $tn.2.5 "t2, t1 ON (1.1-1.1)" {t2 t1 -on te_false}

382

test_join $tn.2.6 "t1, t2 ON (1.1-1.0)" {t1 t2 -on te_true}

383

384

385

test_join $tn.3 "t1 LEFT JOIN t2 ON (t1.a=t2.a)" {t1 t2 -left -on {te_equals a a}}

386

test_join $tn.4 "t1 LEFT JOIN t2 USING (a)" {

387

t1 t2 -left -using a -on {te_equals a a}

388

}

389

test_join $tn.5 "t1 CROSS JOIN t2 USING(b, a)" {

390