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

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Re-implement the core of the multi-threaded sorter tests in sort4.test using C. Run each test in sort4.test ten times, or repeat all tests for 300 seconds as part of the "multithread" permutation test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | threads
Files: files | file ages | folders
SHA1: 208b2b04d4d282bec4424ea7160a123ba549d118
User & Date: dan 2014-05-06 15:38:07
Context
2014-05-06
16:21
Add a little extra variety to the tests in sort4.test. check-in: 7de6aee6 user: dan tags: threads
15:38
Re-implement the core of the multi-threaded sorter tests in sort4.test using C. Run each test in sort4.test ten times, or repeat all tests for 300 seconds as part of the "multithread" permutation test. check-in: 208b2b04 user: dan tags: threads
2014-05-05
20:03
Add test file sort4.test, containing brute force tests for the multi-theaded sorter. check-in: 9cc364c4 user: dan tags: threads
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/test1.c.

6366
6367
6368
6369
6370
6371
6372









































































6373
6374
6375
6376
6377
6378
6379
....
6600
6601
6602
6603
6604
6605
6606

6607
6608
6609
6610
6611
6612
6613
    }
  }

  Tcl_ResetResult(interp);
  return TCL_OK;
}











































































/*
** Register commands with the TCL interpreter.
*/
int Sqlitetest1_Init(Tcl_Interp *interp){
  extern int sqlite3_search_count;
  extern int sqlite3_found_count;
................................................................................
#endif
     { "sqlite3_test_control", test_test_control },
#if SQLITE_OS_UNIX
     { "getrusage", test_getrusage },
#endif
     { "load_static_extension", tclLoadStaticExtensionCmd },
     { "sorter_test_fakeheap", sorter_test_fakeheap },

  };
  static int bitmask_size = sizeof(Bitmask)*8;
  int i;
  extern int sqlite3_sync_count, sqlite3_fullsync_count;
  extern int sqlite3_opentemp_count;
  extern int sqlite3_like_count;
  extern int sqlite3_xferopt_count;







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>







6366
6367
6368
6369
6370
6371
6372
6373
6374
6375
6376
6377
6378
6379
6380
6381
6382
6383
6384
6385
6386
6387
6388
6389
6390
6391
6392
6393
6394
6395
6396
6397
6398
6399
6400
6401
6402
6403
6404
6405
6406
6407
6408
6409
6410
6411
6412
6413
6414
6415
6416
6417
6418
6419
6420
6421
6422
6423
6424
6425
6426
6427
6428
6429
6430
6431
6432
6433
6434
6435
6436
6437
6438
6439
6440
6441
6442
6443
6444
6445
6446
6447
6448
6449
6450
6451
6452
....
6673
6674
6675
6676
6677
6678
6679
6680
6681
6682
6683
6684
6685
6686
6687
    }
  }

  Tcl_ResetResult(interp);
  return TCL_OK;
}

/*
**     sorter_test_sort4_helper DB SQL1 NSTEP SQL2
**
** Compile SQL statement $SQL1 and step it $NSTEP times. For each row, 
** check that the leftmost and rightmost columns returned are both integers,
** and that both contain the same value.
**
** Then execute statement $SQL2. Check that the statement returns the same
** set of integers in the same order as in the previous step (using $SQL1).
*/
static int sorter_test_sort4_helper(
  void * clientData,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  const char *zSql1;
  const char *zSql2;
  int nStep; 
  int iStep; 
  int iCksum1 = 0; 
  int iCksum2 = 0; 
  int rc;
  int iB;
  sqlite3 *db;
  sqlite3_stmt *pStmt;
  
  if( objc!=5 ){
    Tcl_WrongNumArgs(interp, 1, objv, "DB SQL1 NSTEP SQL2");
    return TCL_ERROR;
  }

  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
  zSql1 = Tcl_GetString(objv[2]);
  if( Tcl_GetIntFromObj(interp, objv[3], &nStep) ) return TCL_ERROR;
  zSql2 = Tcl_GetString(objv[4]);

  rc = sqlite3_prepare_v2(db, zSql1, -1, &pStmt, 0);
  if( rc!=SQLITE_OK ) goto sql_error;

  iB = sqlite3_column_count(pStmt)-1;
  for(iStep=0; iStep<nStep && SQLITE_ROW==sqlite3_step(pStmt); iStep++){
    int a = sqlite3_column_int(pStmt, 0);
    if( a!=sqlite3_column_int(pStmt, iB) ){
      Tcl_AppendResult(interp, "data error: (a!=b)", 0);
      return TCL_ERROR;
    }

    iCksum1 += (iCksum1 << 3) + a;
  }
  rc = sqlite3_finalize(pStmt);
  if( rc!=SQLITE_OK ) goto sql_error;

  rc = sqlite3_prepare_v2(db, zSql2, -1, &pStmt, 0);
  if( rc!=SQLITE_OK ) goto sql_error;
  for(iStep=0; SQLITE_ROW==sqlite3_step(pStmt); iStep++){
    int a = sqlite3_column_int(pStmt, 0);
    iCksum2 += (iCksum2 << 3) + a;
  }
  rc = sqlite3_finalize(pStmt);
  if( rc!=SQLITE_OK ) goto sql_error;

  if( iCksum1!=iCksum2 ){
    Tcl_AppendResult(interp, "checksum mismatch", 0);
    return TCL_ERROR;
  }

  return TCL_OK;
 sql_error:
  Tcl_AppendResult(interp, "sql error: ", sqlite3_errmsg(db), 0);
  return TCL_ERROR;
}


/*
** Register commands with the TCL interpreter.
*/
int Sqlitetest1_Init(Tcl_Interp *interp){
  extern int sqlite3_search_count;
  extern int sqlite3_found_count;
................................................................................
#endif
     { "sqlite3_test_control", test_test_control },
#if SQLITE_OS_UNIX
     { "getrusage", test_getrusage },
#endif
     { "load_static_extension", tclLoadStaticExtensionCmd },
     { "sorter_test_fakeheap", sorter_test_fakeheap },
     { "sorter_test_sort4_helper", sorter_test_sort4_helper },
  };
  static int bitmask_size = sizeof(Bitmask)*8;
  int i;
  extern int sqlite3_sync_count, sqlite3_fullsync_count;
  extern int sqlite3_opentemp_count;
  extern int sqlite3_like_count;
  extern int sqlite3_xferopt_count;

Changes to test/permutations.test.

465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
  sqlite3_shutdown
  catch {sqlite3_config multithread}
  sqlite3_initialize
  autoinstall_test_functions
} -files {
  delete.test   delete2.test  insert.test  rollback.test  select1.test
  select2.test  trans.test    update.test  vacuum.test    types.test
  types2.test   types3.test
} -shutdown {
  catch {db close}
  sqlite3_shutdown
  catch {sqlite3_config serialized}
  sqlite3_initialize
  autoinstall_test_functions
}







|







465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
  sqlite3_shutdown
  catch {sqlite3_config multithread}
  sqlite3_initialize
  autoinstall_test_functions
} -files {
  delete.test   delete2.test  insert.test  rollback.test  select1.test
  select2.test  trans.test    update.test  vacuum.test    types.test
  types2.test   types3.test   sort4.test
} -shutdown {
  catch {db close}
  sqlite3_shutdown
  catch {sqlite3_config serialized}
  sqlite3_initialize
  autoinstall_test_functions
}

Changes to test/sort4.test.

14
15
16
17
18
19
20

21
22
23
24
25
26






27
28
29
30

















31
32
33
34
35
36
37
..
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90



91
92
93
94
95
96
97
...
115
116
117
118
119
120
121



122
123
124
125

126
127








128
129
130
131
132



133
134
135
136
137





138
139
140
141
142
143
144
145
# sorter.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix sort4


catch { db close }
sqlite3_shutdown
sqlite3_config_worker_threads 3
sqlite3_initialize
reset_db







#--------------------------------------------------------------------
# Set up a table "t1" containing $nRow rows. Each row contains also
# contains blob fields that total to at least $nPayload bytes of 
# content.

















#
proc populate_table {nRow nPayload} {
  set nCol 0

  set n 0
  for {set nCol 0} {$n < $nPayload} {incr nCol} {
    incr n [expr (4 << $nCol)]
................................................................................
      execsql $insert
    }
  }
}

# Helper for [do_sorter_test]
#
proc sorter_test {nRow nRead {nPayload 100} {cache_size 10}} {
  db eval "PRAGMA cache_size = $cache_size"
  set res [list]

  set nLoad [expr ($nRow > $nRead) ? $nRead : $nRow]

  set nPayload [expr (($nPayload+3)/4) * 4]
  set cols [list]
  foreach {mask col} { 
    0x04  c0 0x08  c1 0x10  c2 0x20  c3 
    0x40  c4 0x80  c5 0x100 c6 0x200 c7 
  } {
    if {$nPayload & $mask} { lappend cols $col }
  }

  set n 0
  db eval "SELECT a, [join $cols ,], b FROM t1 WHERE rowid<=$nRow ORDER BY a" {
    if {$a!=$b} { error "a!=b (a=$a b=$b)" }
    lappend res $a
    incr n
    if {$n==$nLoad} break
  }


  set sql {SELECT a FROM t1 WHERE rowid<=$nRow ORDER BY a LIMIT $nRead}
  if {$res != [db eval $sql]} {
    puts $res
    puts [db eval {SELECT a FROM t1 WHERE rowid<=$nLoad ORDER BY a}]
    error "data no good"
  }




  set {} {} 
}

# Usage:
#
#   do_sorter_test <testname> <args>...
#
................................................................................
      unset a(-cachesize)
      set optlist "[join [array names a] ,] or -cachesize"
      error "Unknown option $s, expected $optlist"
    }
    set a($s) $val
  }




  for {set i 0} {$i < $a(-repeats)} {incr i} {
    set cmd [list sorter_test $a(-rows) $a(-read) $a(-payload) $a(-cachesize)]
    do_test $tn.$i $cmd {}
  }

}









do_test 1 {
  execsql "PRAGMA page_size = 4096"
  populate_table 100000 500
} {}




do_sorter_test 2 -repeats 10 -rows 1000   -read 100
do_sorter_test 3 -repeats 10 -rows 100000 -read 1000
do_sorter_test 4 -repeats 10 -rows 100000 -read 1000 -payload 500
do_sorter_test 5 -repeats 10 -rows 100000 -read 100000 -payload 8
do_sorter_test 6 -repeats 10 -rows 100000 -read 10 -payload 8






catch { db close }
sqlite3_shutdown
sqlite3_config_worker_threads 0
sqlite3_initialize
finish_test









>






>
>
>
>
>
>


|
<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|
<













|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
>
>







 







>
>
>
|
|
<
|
>


>
>
>
>
>
>
>
>





>
>
>
|
|
|
|
|
>
>
>
>
>







<
14
15
16
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
..
76
77
78
79
80
81
82
83

84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
...
140
141
142
143
144
145
146
147
148
149
150
151

152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188

# sorter.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix sort4

# Configure the sorter to use 3 background threads.
catch { db close }
sqlite3_shutdown
sqlite3_config_worker_threads 3
sqlite3_initialize
reset_db

# Minimum number of seconds to run for. If the value is 0, each test
# is run exactly once. Otherwise, tests are repeated until the timeout
# expires.
set SORT4TIMEOUT 0
if {[permutation] == "multithread"} { set SORT4TIMEOUT 300 }

#--------------------------------------------------------------------
# Set up a table "t1" containing $nRow rows. Each row contains also
# contains blob fields that collectively contain at least $nPayload 

# bytes of content. The table schema is as follows:
#
#   CREATE TABLE t1(a INTEGER, <extra-columns>, b INTEGER);
#
# For each row, the values of columns "a" and "b" are set to the same
# pseudo-randomly selected integer. The "extra-columns", of which there
# are at most eight, are named c0, c1, c2 etc. Column c0 contains a 4
# byte string. Column c1 an 8 byte string. Field c2 16 bytes, and so on.
#
# This table is intended to be used for testing queries of the form: 
#
#   SELECT a, <cols>, b FROM t1 ORDER BY a;
#
# The test code checks that rows are returned in order, and that the 
# values of "a" and "b" are the same for each row (the idea being that
# if field "b" at the end of the sorter record has not been corrupted, 
# the rest of the record is probably Ok as well).
#
proc populate_table {nRow nPayload} {
  set nCol 0

  set n 0
  for {set nCol 0} {$n < $nPayload} {incr nCol} {
    incr n [expr (4 << $nCol)]
................................................................................
      execsql $insert
    }
  }
}

# Helper for [do_sorter_test]
#
proc sorter_test {nRow nRead nPayload} {

  set res [list]

  set nLoad [expr ($nRow > $nRead) ? $nRead : $nRow]

  set nPayload [expr (($nPayload+3)/4) * 4]
  set cols [list]
  foreach {mask col} { 
    0x04  c0 0x08  c1 0x10  c2 0x20  c3 
    0x40  c4 0x80  c5 0x100 c6 0x200 c7 
  } {
    if {$nPayload & $mask} { lappend cols $col }
  }

  # Create two SELECT statements. Statement $sql1 uses the sorter to sort
  # $nRow records of a bit over $nPayload bytes each read from the "t1"
  # table created by [populate_table] proc above. Rows are sorted in order
  # of the integer field in each "t1" record.
  #
  # The second SQL statement sorts the same set of rows as the first, but
  # uses a LIMIT clause, causing SQLite to use a temp table instead of the
  # sorter for sorting.
  #
  set sql1 "SELECT a, [join $cols ,], b FROM t1 WHERE rowid<=$nRow ORDER BY a"
  set sql2 "SELECT a FROM t1 WHERE rowid<=$nRow ORDER BY a LIMIT $nRead"

  # Pass the two SQL statements to a helper command written in C. This
  # command steps statement $sql1 $nRead times and compares the integer
  # values in the rows returned with the results of executing $sql2. If
  # the comparison fails (indicating some bug in the sorter), a Tcl
  # exception is thrown.
  #
  sorter_test_sort4_helper db $sql1 $nRead $sql2
  set {} {} 
}

# Usage:
#
#   do_sorter_test <testname> <args>...
#
................................................................................
      unset a(-cachesize)
      set optlist "[join [array names a] ,] or -cachesize"
      error "Unknown option $s, expected $optlist"
    }
    set a($s) $val
  }

  db eval "PRAGMA cache_size = $a(-cachesize)"

  do_test $tn [subst -nocommands {
    for {set i 0} {[set i] < $a(-repeats)} {incr i} {
      sorter_test $a(-rows) $a(-read) $a(-payload)

    }
  }] {}
}

proc clock_seconds {} {
  db one {SELECT strftime('%s')}
}

#-------------------------------------------------------------------------
# Begin tests here.

# Create a test database.
do_test 1 {
  execsql "PRAGMA page_size = 4096"
  populate_table 100000 500
} {}

set iTimeLimit [expr [clock_seconds] + $SORT4TIMEOUT]

for {set tn 2} {1} {incr tn} {
  do_sorter_test $tn.2 -repeats 10 -rows 1000   -read 100
  do_sorter_test $tn.3 -repeats 10 -rows 100000 -read 1000
  do_sorter_test $tn.4 -repeats 10 -rows 100000 -read 1000 -payload 500
  do_sorter_test $tn.5 -repeats 10 -rows 100000 -read 100000 -payload 8
  do_sorter_test $tn.6 -repeats 10 -rows 100000 -read 10 -payload 8

  set iNow [clock_seconds]
  if {$iNow>=$iTimeLimit} break
  do_test "$testprefix-([expr $iTimeLimit-$iNow] seconds remain)" {} {}
}

catch { db close }
sqlite3_shutdown
sqlite3_config_worker_threads 0
sqlite3_initialize
finish_test