Debian Codegraph @

test/speedtest1.c

raw
1 /*
2 ** A program for performance testing.
3 **
4 ** To build this program against an historical version of SQLite for comparison
5 ** testing:
6 **
7 ** Unix:
8 **
9 ** ./configure --all
10 ** make clean speedtest1
11 ** mv speedtest1 speedtest1-current
12 ** cp $HISTORICAL_SQLITE3_C_H .
13 ** touch sqlite3.c sqlite3.h .target_source
14 ** make speedtest1
15 ** mv speedtest1 speedtest1-baseline
16 **
17 ** Windows:
18 **
19 ** nmake /f Makefile.msc clean speedtest1.exe
20 ** mv speedtest1.exe speedtest1-current.exe
21 ** cp $HISTORICAL_SQLITE_C_H .
22 ** touch sqlite3.c sqlite3.h .target_source
23 ** nmake /f Makefile.msc speedtest1.exe
24 ** mv speedtest1.exe speedtest1-baseline.exe
25 **
26 ** The available command-line options are described below:
27 */
28 static const char zHelp[] =
29 "Usage: %s [--options] DATABASE\n"
30 "Options:\n"
31 " --autovacuum Enable AUTOVACUUM mode\n"
32 " --big-transactions Add BEGIN/END around all large tests\n"
33 " --cachesize N Set PRAGMA cache_size=N. Note: N is pages, not bytes\n"
34 " --checkpoint Run PRAGMA wal_checkpoint after each test case\n"
35 " --exclusive Enable locking_mode=EXCLUSIVE\n"
36 " --explain Like --sqlonly but with added EXPLAIN keywords\n"
37 " --fullfsync Enable fullfsync=TRUE\n"
38 " --hard-heap-limit N The hard limit on the maximum heap size\n"
39 " --heap SZ MIN Memory allocator uses SZ bytes & min allocation MIN\n"
40 " --incrvacuum Enable incremenatal vacuum mode\n"
41 " --journal M Set the journal_mode to M\n"
42 " --key KEY Set the encryption key to KEY\n"
43 " --lookaside N SZ Configure lookaside for N slots of SZ bytes each\n"
44 " --memdb Use an in-memory database\n"
45 " --mmap SZ MMAP the first SZ bytes of the database file\n"
46 " --multithread Set multithreaded mode\n"
47 " --nomemstat Disable memory statistics\n"
48 " --nomutex Open db with SQLITE_OPEN_NOMUTEX\n"
49 " --nosync Set PRAGMA synchronous=OFF\n"
50 " --notnull Add NOT NULL constraints to table columns\n"
51 " --output FILE Store SQL output in FILE\n"
52 " --pagesize N Set the page size to N\n"
53 " --pcache N SZ Configure N pages of pagecache each of size SZ bytes\n"
54 " --primarykey Use PRIMARY KEY instead of UNIQUE where appropriate\n"
55 " --repeat N Repeat each SELECT N times (default: 1)\n"
56 " --reprepare Reprepare each statement upon every invocation\n"
57 " --reserve N Reserve N bytes on each database page\n"
58 " --script FILE Write an SQL script for the test into FILE\n"
59 " --serialized Set serialized threading mode\n"
60 " --singlethread Set single-threaded mode - disables all mutexing\n"
61 " --sqlonly No-op. Only show the SQL that would have been run.\n"
62 " --shrink-memory Invoke sqlite3_db_release_memory() frequently.\n"
63 " --size N Relative test size. Default=100\n"
64 " --soft-heap-limit N The soft limit on the maximum heap size\n"
65 " --strict Use STRICT table where appropriate\n"
66 " --stats Show statistics at the end\n"
67 " --stmtscanstatus Activate SQLITE_DBCONFIG_STMT_SCANSTATUS\n"
68 " --temp N N from 0 to 9. 0: no temp table. 9: all temp tables\n"
69 " --testset T Run test-set T (main, cte, rtree, orm, fp, json,\n"
70 " star, app, debug). Can be a comma-separated list\n"
71 " of values, with /SCALE suffixes or macro \"mix1\"\n"
72 " --trace Turn on SQL tracing\n"
73 " --threads N Use up to N threads for sorting\n"
74 " --utf16be Set text encoding to UTF-16BE\n"
75 " --utf16le Set text encoding to UTF-16LE\n"
76 " --verify Run additional verification steps\n"
77 " --vfs NAME Use the given (preinstalled) VFS\n"
78 " --without-rowid Use WITHOUT ROWID where appropriate\n"
79 ;
80
81 #include "sqlite3.h"
82 #include <assert.h>
83 #include <stdio.h>
84 #include <stdlib.h>
85 #include <stdarg.h>
86 #include <string.h>
87 #include <ctype.h>
88 #ifndef _WIN32
89 # include <unistd.h>
90 #else
91 # include <io.h>
92 #endif
93 #define ISSPACE(X) isspace((unsigned char)(X))
94 #define ISDIGIT(X) isdigit((unsigned char)(X))
95
96 #if SQLITE_VERSION_NUMBER<3005000
97 # define sqlite3_int64 sqlite_int64
98 #endif
99
100 typedef sqlite3_uint64 u64;
101
102 /*
103 ** State structure for a Hash hash in progress
104 */
105 typedef struct HashContext HashContext;
106 struct HashContext {
107 unsigned char isInit; /* True if initialized */
108 unsigned char i, j; /* State variables */
109 unsigned char s[256]; /* State variables */
110 unsigned char r[32]; /* Result */
111 };
112
113
114 /* All global state is held in this structure */
115 static struct Global {
116 sqlite3 *db; /* The open database connection */
117 const char *zDbName; /* Name of the database file */
118 const char *zVfs; /* --vfs NAME */
119 sqlite3_stmt *pStmt; /* Current SQL statement */
120 sqlite3_int64 iStart; /* Start-time for the current test */
121 sqlite3_int64 iTotal; /* Total time */
122 int bWithoutRowid; /* True for --without-rowid */
123 int bReprepare; /* True to reprepare the SQL on each rerun */
124 int bSqlOnly; /* True to print the SQL once only */
125 int bExplain; /* Print SQL with EXPLAIN prefix */
126 int bVerify; /* Try to verify that results are correct */
127 int bMemShrink; /* Call sqlite3_db_release_memory() often */
128 int eTemp; /* 0: no TEMP. 9: always TEMP. */
129 int szTest; /* Scale factor for test iterations */
130 int szBase; /* Base size prior to testset scaling */
131 int nRepeat; /* Repeat selects this many times */
132 int doCheckpoint; /* Run PRAGMA wal_checkpoint after each trans */
133 int nReserve; /* Reserve bytes */
134 int stmtScanStatus; /* True to activate Stmt ScanStatus reporting */
135 int doBigTransactions; /* Enable transactions on tests 410 and 510 */
136 const char *zWR; /* Might be WITHOUT ROWID */
137 const char *zNN; /* Might be NOT NULL */
138 const char *zPK; /* Might be UNIQUE or PRIMARY KEY */
139 unsigned int x, y; /* Pseudo-random number generator state */
140 u64 nResByte; /* Total number of result bytes */
141 int nResult; /* Size of the current result */
142 char zResult[3000]; /* Text of the current result */
143 FILE *pScript; /* Write an SQL script into this file */
144 #ifndef SPEEDTEST_OMIT_HASH
145 FILE *hashFile; /* Store all hash results in this file */
146 HashContext hash; /* Hash of all output */
147 #endif
148 } g;
149
150 /* Return " TEMP" or "", as appropriate for creating a table.
151 */
152 static const char *isTemp(int N){
153 return g.eTemp>=N ? " TEMP" : "";
154 }
155
156 /* Print an error message and exit */
157 static void fatal_error(const char *zMsg, ...){
158 va_list ap;
159 va_start(ap, zMsg);
160 vfprintf(stderr, zMsg, ap);
161 va_end(ap);
162 #ifdef SQLITE_SPEEDTEST1_WASM
163 /* Emscripten complains when exit() is called and anything is left
164 in the I/O buffers. */
165 fflush(stdout);
166 fflush(stderr);
167 #endif
168 exit(1);
169 }
170
171 #ifndef SPEEDTEST_OMIT_HASH
172 /****************************************************************************
173 ** Hash algorithm used to verify that compilation is not miscompiled
174 ** in such a was as to generate an incorrect result.
175 */
176
177 /*
178 ** Initialize a new hash. iSize determines the size of the hash
179 ** in bits and should be one of 224, 256, 384, or 512. Or iSize
180 ** can be zero to use the default hash size of 256 bits.
181 */
182 static void HashInit(void){
183 unsigned int k;
184 g.hash.i = 0;
185 g.hash.j = 0;
186 for(k=0; k<256; k++) g.hash.s[k] = k;
187 }
188
189 /*
190 ** Make consecutive calls to the HashUpdate function to add new content
191 ** to the hash
192 */
193 static void HashUpdate(
194 const unsigned char *aData,
195 unsigned int nData
196 ){
197 unsigned char t;
198 unsigned char i = g.hash.i;
199 unsigned char j = g.hash.j;
200 unsigned int k;
201 if( g.hashFile ) fwrite(aData, 1, nData, g.hashFile);
202 for(k=0; k<nData; k++){
203 j += g.hash.s[i] + aData[k];
204 t = g.hash.s[j];
205 g.hash.s[j] = g.hash.s[i];
206 g.hash.s[i] = t;
207 i++;
208 }
209 g.hash.i = i;
210 g.hash.j = j;
211 }
212
213 /*
214 ** After all content has been added, invoke HashFinal() to compute
215 ** the final hash. The hash result is stored in g.hash.r[].
216 */
217 static void HashFinal(void){
218 unsigned int k;
219 unsigned char t, i, j;
220 i = g.hash.i;
221 j = g.hash.j;
222 for(k=0; k<32; k++){
223 i++;
224 t = g.hash.s[i];
225 j += t;
226 g.hash.s[i] = g.hash.s[j];
227 g.hash.s[j] = t;
228 t += g.hash.s[i];
229 g.hash.r[k] = g.hash.s[t];
230 }
231 }
232
233 /* End of the Hash hashing logic
234 *****************************************************************************/
235 #endif /* SPEEDTEST_OMIT_HASH */
236
237 /*
238 ** Return the value of a hexadecimal digit. Return -1 if the input
239 ** is not a hex digit.
240 */
241 static int hexDigitValue(char c){
242 if( c>='0' && c<='9' ) return c - '0';
243 if( c>='a' && c<='f' ) return c - 'a' + 10;
244 if( c>='A' && c<='F' ) return c - 'A' + 10;
245 return -1;
246 }
247
248 /* Provide an alternative to sqlite3_stricmp() in older versions of
249 ** SQLite */
250 #if SQLITE_VERSION_NUMBER<3007011
251 # define sqlite3_stricmp strcmp
252 #endif
253
254 /*
255 ** Interpret zArg as an integer value, possibly with suffixes.
256 */
257 static int integerValue(const char *zArg){
258 sqlite3_int64 v = 0;
259 static const struct { char *zSuffix; int iMult; } aMult[] = {
260 { "KiB", 1024 },
261 { "MiB", 1024*1024 },
262 { "GiB", 1024*1024*1024 },
263 { "KB", 1000 },
264 { "MB", 1000000 },
265 { "GB", 1000000000 },
266 { "K", 1000 },
267 { "M", 1000000 },
268 { "G", 1000000000 },
269 };
270 int i;
271 int isNeg = 0;
272 if( zArg[0]=='-' ){
273 isNeg = 1;
274 zArg++;
275 }else if( zArg[0]=='+' ){
276 zArg++;
277 }
278 if( zArg[0]=='0' && zArg[1]=='x' ){
279 int x;
280 zArg += 2;
281 while( (x = hexDigitValue(zArg[0]))>=0 ){
282 v = (v<<4) + x;
283 zArg++;
284 }
285 }else{
286 while( isdigit(zArg[0]) ){
287 v = v*10 + zArg[0] - '0';
288 zArg++;
289 }
290 }
291 for(i=0; i<sizeof(aMult)/sizeof(aMult[0]); i++){
292 if( sqlite3_stricmp(aMult[i].zSuffix, zArg)==0 ){
293 v *= aMult[i].iMult;
294 break;
295 }
296 }
297 if( v>0x7fffffff ) fatal_error("parameter too large - max 2147483648");
298 return (int)(isNeg? -v : v);
299 }
300
301 /* Return the current wall-clock time, in milliseconds */
302 sqlite3_int64 speedtest1_timestamp(void){
303 #if SQLITE_VERSION_NUMBER<3005000
304 return 0;
305 #else
306 static sqlite3_vfs *clockVfs = 0;
307 sqlite3_int64 t;
308 if( clockVfs==0 ) clockVfs = sqlite3_vfs_find(0);
309 #if SQLITE_VERSION_NUMBER>=3007000
310 if( clockVfs->iVersion>=2 && clockVfs->xCurrentTimeInt64!=0 ){
311 clockVfs->xCurrentTimeInt64(clockVfs, &t);
312 }else
313 #endif
314 {
315 double r;
316 clockVfs->xCurrentTime(clockVfs, &r);
317 t = (sqlite3_int64)(r*86400000.0);
318 }
319 return t;
320 #endif
321 }
322
323 /* Return a pseudo-random unsigned integer */
324 unsigned int speedtest1_random(void){
325 g.x = (g.x>>1) ^ ((1+~(g.x&1)) & 0xd0000001);
326 g.y = g.y*1103515245 + 12345;
327 return g.x ^ g.y;
328 }
329
330 /* Map the value in within the range of 1...limit into another
331 ** number in a way that is chatic and invertable.
332 */
333 unsigned swizzle(unsigned in, unsigned limit){
334 unsigned out = 0;
335 while( limit ){
336 out = (out<<1) | (in&1);
337 in >>= 1;
338 limit >>= 1;
339 }
340 return out;
341 }
342
343 /* Round up a number so that it is a power of two minus one
344 */
345 unsigned roundup_allones(unsigned limit){
346 unsigned m = 1;
347 while( m<limit ) m = (m<<1)+1;
348 return m;
349 }
350
351 /* The speedtest1_numbername procedure below converts its argment (an integer)
352 ** into a string which is the English-language name for that number.
353 ** The returned string should be freed with sqlite3_free().
354 **
355 ** Example:
356 **
357 ** speedtest1_numbername(123) -> "one hundred twenty three"
358 */
359 int speedtest1_numbername(unsigned int n, char *zOut, int nOut){
360 static const char *ones[] = { "zero", "one", "two", "three", "four", "five",
361 "six", "seven", "eight", "nine", "ten", "eleven", "twelve",
362 "thirteen", "fourteen", "fifteen", "sixteen", "seventeen",
363 "eighteen", "nineteen" };
364 static const char *tens[] = { "", "ten", "twenty", "thirty", "forty",
365 "fifty", "sixty", "seventy", "eighty", "ninety" };
366 int i = 0;
367
368 if( n>=1000000000 ){
369 i += speedtest1_numbername(n/1000000000, zOut+i, nOut-i);
370 sqlite3_snprintf(nOut-i, zOut+i, " billion");
371 i += (int)strlen(zOut+i);
372 n = n % 1000000000;
373 }
374 if( n>=1000000 ){
375 if( i && i<nOut-1 ) zOut[i++] = ' ';
376 i += speedtest1_numbername(n/1000000, zOut+i, nOut-i);
377 sqlite3_snprintf(nOut-i, zOut+i, " million");
378 i += (int)strlen(zOut+i);
379 n = n % 1000000;
380 }
381 if( n>=1000 ){
382 if( i && i<nOut-1 ) zOut[i++] = ' ';
383 i += speedtest1_numbername(n/1000, zOut+i, nOut-i);
384 sqlite3_snprintf(nOut-i, zOut+i, " thousand");
385 i += (int)strlen(zOut+i);
386 n = n % 1000;
387 }
388 if( n>=100 ){
389 if( i && i<nOut-1 ) zOut[i++] = ' ';
390 sqlite3_snprintf(nOut-i, zOut+i, "%s hundred", ones[n/100]);
391 i += (int)strlen(zOut+i);
392 n = n % 100;
393 }
394 if( n>=20 ){
395 if( i && i<nOut-1 ) zOut[i++] = ' ';
396 sqlite3_snprintf(nOut-i, zOut+i, "%s", tens[n/10]);
397 i += (int)strlen(zOut+i);
398 n = n % 10;
399 }
400 if( n>0 ){
401 if( i && i<nOut-1 ) zOut[i++] = ' ';
402 sqlite3_snprintf(nOut-i, zOut+i, "%s", ones[n]);
403 i += (int)strlen(zOut+i);
404 }
405 if( i==0 ){
406 sqlite3_snprintf(nOut-i, zOut+i, "zero");
407 i += (int)strlen(zOut+i);
408 }
409 return i;
410 }
411
412
413 /* Start a new test case */
414 #define NAMEWIDTH 60
415 static const char zDots[] =
416 ".......................................................................";
417 static int iTestNumber = 0; /* Current test # for begin/end_test(). */
418 void speedtest1_begin_test(int iTestNum, const char *zTestName, ...){
419 int n = (int)strlen(zTestName);
420 char *zName;
421 va_list ap;
422 iTestNumber = iTestNum;
423 va_start(ap, zTestName);
424 zName = sqlite3_vmprintf(zTestName, ap);
425 va_end(ap);
426 n = (int)strlen(zName);
427 if( n>NAMEWIDTH ){
428 zName[NAMEWIDTH] = 0;
429 n = NAMEWIDTH;
430 }
431 if( g.pScript ){
432 fprintf(g.pScript,"-- begin test %d %.*s\n", iTestNumber, n, zName)
433 /* maintenance reminder: ^^^ code in ext/wasm expects %d to be
434 ** field #4 (as in: cut -d' ' -f4). */;
435 }
436 if( g.bSqlOnly ){
437 printf("/* %4d - %s%.*s */\n", iTestNum, zName, NAMEWIDTH-n, zDots);
438 }else{
439 printf("%4d - %s%.*s ", iTestNum, zName, NAMEWIDTH-n, zDots);
440 fflush(stdout);
441 }
442 sqlite3_free(zName);
443 g.nResult = 0;
444 g.iStart = speedtest1_timestamp();
445 g.x = 0xad131d0b;
446 g.y = 0x44f9eac8;
447 }
448
449 /* Forward reference */
450 void speedtest1_exec(const char*,...);
451
452 /* Complete a test case */
453 void speedtest1_end_test(void){
454 sqlite3_int64 iElapseTime = speedtest1_timestamp() - g.iStart;
455 if( g.doCheckpoint ) speedtest1_exec("PRAGMA wal_checkpoint;");
456 assert( iTestNumber > 0 );
457 if( g.pScript ){
458 fprintf(g.pScript,"-- end test %d\n", iTestNumber);
459 }
460 if( !g.bSqlOnly ){
461 g.iTotal += iElapseTime;
462 printf("%4d.%03ds\n", (int)(iElapseTime/1000), (int)(iElapseTime%1000));
463 }
464 if( g.pStmt ){
465 sqlite3_finalize(g.pStmt);
466 g.pStmt = 0;
467 }
468 iTestNumber = 0;
469 }
470
471 /* Report end of testing */
472 void speedtest1_final(void){
473 if( !g.bSqlOnly ){
474 printf(" TOTAL%.*s %4d.%03ds\n", NAMEWIDTH-5, zDots,
475 (int)(g.iTotal/1000), (int)(g.iTotal%1000));
476 }
477 if( g.bVerify ){
478 #ifndef SPEEDTEST_OMIT_HASH
479 int i;
480 #endif
481 printf("Verification Hash: %llu ", g.nResByte);
482 #ifndef SPEEDTEST_OMIT_HASH
483 HashUpdate((const unsigned char*)"\n", 1);
484 HashFinal();
485 for(i=0; i<24; i++){
486 printf("%02x", g.hash.r[i]);
487 }
488 if( g.hashFile && g.hashFile!=stdout ) fclose(g.hashFile);
489 #endif
490 printf("\n");
491 }
492 }
493
494 /* Print an SQL statement to standard output */
495 static void printSql(const char *zSql){
496 int n = (int)strlen(zSql);
497 while( n>0 && (zSql[n-1]==';' || ISSPACE(zSql[n-1])) ){ n--; }
498 if( g.bExplain ) printf("EXPLAIN ");
499 printf("%.*s;\n", n, zSql);
500 if( g.bExplain
501 #if SQLITE_VERSION_NUMBER>=3007017
502 && ( sqlite3_strglob("CREATE *", zSql)==0
503 || sqlite3_strglob("DROP *", zSql)==0
504 || sqlite3_strglob("ALTER *", zSql)==0
505 )
506 #endif
507 ){
508 printf("%.*s;\n", n, zSql);
509 }
510 }
511
512 /* Shrink memory used, if appropriate and if the SQLite version is capable
513 ** of doing so.
514 */
515 void speedtest1_shrink_memory(void){
516 #if SQLITE_VERSION_NUMBER>=3007010
517 if( g.bMemShrink ) sqlite3_db_release_memory(g.db);
518 #endif
519 }
520
521 /* Run SQL */
522 void speedtest1_exec(const char *zFormat, ...){
523 va_list ap;
524 char *zSql;
525 va_start(ap, zFormat);
526 zSql = sqlite3_vmprintf(zFormat, ap);
527 va_end(ap);
528 if( g.bSqlOnly ){
529 printSql(zSql);
530 }else{
531 char *zErrMsg = 0;
532 int rc;
533 if( g.pScript ){
534 fprintf(g.pScript,"%s;\n",zSql);
535 }
536 rc = sqlite3_exec(g.db, zSql, 0, 0, &zErrMsg);
537 if( zErrMsg ) fatal_error("SQL error: %s\n%s\n", zErrMsg, zSql);
538 if( rc!=SQLITE_OK ) fatal_error("exec error: %s\n", sqlite3_errmsg(g.db));
539 }
540 sqlite3_free(zSql);
541 speedtest1_shrink_memory();
542 }
543
544 /* Run SQL and return the first column of the first row as a string. The
545 ** returned string is obtained from sqlite_malloc() and must be freed by
546 ** the caller.
547 */
548 char *speedtest1_once(const char *zFormat, ...){
549 va_list ap;
550 char *zSql;
551 sqlite3_stmt *pStmt;
552 char *zResult = 0;
553 int rc;
554 va_start(ap, zFormat);
555 zSql = sqlite3_vmprintf(zFormat, ap);
556 va_end(ap);
557 if( g.bSqlOnly ){
558 printSql(zSql);
559 }else{
560 int rc = sqlite3_prepare_v2(g.db, zSql, -1, &pStmt, 0);
561 if( rc ){
562 fatal_error("SQL error: %s\n", sqlite3_errmsg(g.db));
563 }
564 if( g.pScript ){
565 char *z = sqlite3_expanded_sql(pStmt);
566 fprintf(g.pScript,"%s\n",z);
567 sqlite3_free(z);
568 }
569 if( sqlite3_step(pStmt)==SQLITE_ROW ){
570 const char *z = (const char*)sqlite3_column_text(pStmt, 0);
571 if( z ) zResult = sqlite3_mprintf("%s", z);
572 }
573 rc = sqlite3_reset(pStmt);
574 if( rc!=SQLITE_OK ){
575 fatal_error("%s\nError code %d: %s\n",
576 sqlite3_sql(pStmt), rc, sqlite3_errmsg(g.db));
577 }
578 sqlite3_finalize(pStmt);
579 }
580 sqlite3_free(zSql);
581 speedtest1_shrink_memory();
582 return zResult;
583 }
584
585 /* Prepare an SQL statement */
586 void speedtest1_prepare(const char *zFormat, ...){
587 va_list ap;
588 char *zSql;
589 va_start(ap, zFormat);
590 zSql = sqlite3_vmprintf(zFormat, ap);
591 va_end(ap);
592 if( g.bSqlOnly ){
593 printSql(zSql);
594 }else{
595 int rc;
596 if( g.pStmt ) sqlite3_finalize(g.pStmt);
597 rc = sqlite3_prepare_v2(g.db, zSql, -1, &g.pStmt, 0);
598 if( rc ){
599 fatal_error("SQL error: %s\n", sqlite3_errmsg(g.db));
600 }
601 }
602 sqlite3_free(zSql);
603 }
604
605 /* Run an SQL statement previously prepared */
606 void speedtest1_run(void){
607 int i, n, len, rc;
608 if( g.bSqlOnly ) return;
609 assert( g.pStmt );
610 g.nResult = 0;
611 if( g.pScript ){
612 char *z = sqlite3_expanded_sql(g.pStmt);
613 fprintf(g.pScript,"%s\n",z);
614 sqlite3_free(z);
615 }
616 while( sqlite3_step(g.pStmt)==SQLITE_ROW ){
617 n = sqlite3_column_count(g.pStmt);
618 for(i=0; i<n; i++){
619 const char *z = (const char*)sqlite3_column_text(g.pStmt, i);
620 if( z==0 ) z = "nil";
621 len = (int)strlen(z);
622 #ifndef SPEEDTEST_OMIT_HASH
623 if( g.bVerify ){
624 int eType = sqlite3_column_type(g.pStmt, i);
625 unsigned char zPrefix[2];
626 zPrefix[0] = '\n';
627 zPrefix[1] = "-IFTBN"[eType];
628 if( g.nResByte ){
629 HashUpdate(zPrefix, 2);
630 }else{
631 HashUpdate(zPrefix+1, 1);
632 }
633 if( eType==SQLITE_FLOAT ){
634 /* Omit the value of floating-point results from the verification
635 ** hash. The only thing we record is the fact that the result was
636 ** a floating-point value. */
637 g.nResByte += 2;
638 }else if( eType==SQLITE_BLOB ){
639 int nBlob = sqlite3_column_bytes(g.pStmt, i);
640 int iBlob;
641 unsigned char zChar[2];
642 const unsigned char *aBlob = sqlite3_column_blob(g.pStmt, i);
643 for(iBlob=0; iBlob<nBlob; iBlob++){
644 zChar[0] = "0123456789abcdef"[aBlob[iBlob]>>4];
645 zChar[1] = "0123456789abcdef"[aBlob[iBlob]&15];
646 HashUpdate(zChar,2);
647 }
648 g.nResByte += nBlob*2 + 2;
649 }else{
650 HashUpdate((unsigned char*)z, len);
651 g.nResByte += len + 2;
652 }
653 }
654 #endif
655 if( g.nResult+len<sizeof(g.zResult)-2 ){
656 if( g.nResult>0 ) g.zResult[g.nResult++] = ' ';
657 memcpy(g.zResult + g.nResult, z, len+1);
658 g.nResult += len;
659 }
660 }
661 }
662 #if SQLITE_VERSION_NUMBER>=3006001
663 if( g.bReprepare ){
664 sqlite3_stmt *pNew;
665 sqlite3_prepare_v2(g.db, sqlite3_sql(g.pStmt), -1, &pNew, 0);
666 rc = sqlite3_finalize(g.pStmt);
667 if( rc!=SQLITE_OK ){
668 fatal_error("%s\nError code %d: %s\n",
669 sqlite3_sql(pNew), rc, sqlite3_errmsg(g.db));
670 }
671 g.pStmt = pNew;
672 }else
673 #endif
674 {
675 rc = sqlite3_reset(g.pStmt);
676 if( rc!=SQLITE_OK ){
677 fatal_error("%s\nError code %d: %s\n",
678 sqlite3_sql(g.pStmt), rc, sqlite3_errmsg(g.db));
679 }
680 }
681 speedtest1_shrink_memory();
682 }
683
684 #ifndef SQLITE_OMIT_DEPRECATED
685 /* The sqlite3_trace() callback function */
686 static void traceCallback(void *NotUsed, const char *zSql){
687 int n = (int)strlen(zSql);
688 while( n>0 && (zSql[n-1]==';' || ISSPACE(zSql[n-1])) ) n--;
689 fprintf(stderr,"%.*s;\n", n, zSql);
690 }
691 #endif /* SQLITE_OMIT_DEPRECATED */
692
693 /* Substitute random() function that gives the same random
694 ** sequence on each run, for repeatability. */
695 static void randomFunc(
696 sqlite3_context *context,
697 int NotUsed,
698 sqlite3_value **NotUsed2
699 ){
700 sqlite3_result_int64(context, (sqlite3_int64)speedtest1_random());
701 }
702
703 /* Estimate the square root of an integer */
704 static int est_square_root(int x){
705 int y0 = x/2;
706 int y1;
707 int n;
708 for(n=0; y0>0 && n<10; n++){
709 y1 = (y0 + x/y0)/2;
710 if( y1==y0 ) break;
711 y0 = y1;
712 }
713 return y0;
714 }
715
716
717 #if SQLITE_VERSION_NUMBER<3005004
718 /*
719 ** An implementation of group_concat(). Used only when testing older
720 ** versions of SQLite that lack the built-in group_concat().
721 */
722 struct groupConcat {
723 char *z;
724 int nAlloc;
725 int nUsed;
726 };
727 static void groupAppend(struct groupConcat *p, const char *z, int n){
728 if( p->nUsed+n >= p->nAlloc ){
729 int n2 = (p->nAlloc+n+1)*2;
730 char *z2 = sqlite3_realloc(p->z, n2);
731 if( z2==0 ) return;
732 p->z = z2;
733 p->nAlloc = n2;
734 }
735 memcpy(p->z+p->nUsed, z, n);
736 p->nUsed += n;
737 }
738 static void groupStep(
739 sqlite3_context *context,
740 int argc,
741 sqlite3_value **argv
742 ){
743 const char *zVal;
744 struct groupConcat *p;
745 const char *zSep;
746 int nVal, nSep;
747 assert( argc==1 || argc==2 );
748 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
749 p= (struct groupConcat*)sqlite3_aggregate_context(context, sizeof(*p));
750
751 if( p ){
752 int firstTerm = p->nUsed==0;
753 if( !firstTerm ){
754 if( argc==2 ){
755 zSep = (char*)sqlite3_value_text(argv[1]);
756 nSep = sqlite3_value_bytes(argv[1]);
757 }else{
758 zSep = ",";
759 nSep = 1;
760 }
761 if( nSep ) groupAppend(p, zSep, nSep);
762 }
763 zVal = (char*)sqlite3_value_text(argv[0]);
764 nVal = sqlite3_value_bytes(argv[0]);
765 if( zVal ) groupAppend(p, zVal, nVal);
766 }
767 }
768 static void groupFinal(sqlite3_context *context){
769 struct groupConcat *p;
770 p = sqlite3_aggregate_context(context, 0);
771 if( p && p->z ){
772 p->z[p->nUsed] = 0;
773 sqlite3_result_text(context, p->z, p->nUsed, sqlite3_free);
774 }
775 }
776 #endif
777
778 /*
779 ** The main and default testset
780 */
781 void testset_main(void){
782 int i; /* Loop counter */
783 int n; /* iteration count */
784 int sz; /* Size of the tables */
785 int maxb; /* Maximum swizzled value */
786 unsigned x1 = 0, x2 = 0; /* Parameters */
787 int len = 0; /* Length of the zNum[] string */
788 char zNum[2000]; /* A number name */
789
790 sz = n = g.szTest*500;
791 zNum[0] = 0;
792 maxb = roundup_allones(sz);
793 speedtest1_begin_test(100, "%d INSERTs into table with no index", n);
794 speedtest1_exec("BEGIN");
795 speedtest1_exec("CREATE%s TABLE z1(a INTEGER %s, b INTEGER %s, c TEXT %s);",
796 isTemp(9), g.zNN, g.zNN, g.zNN);
797 speedtest1_prepare("INSERT INTO z1 VALUES(?1,?2,?3); -- %d times", n);
798 for(i=1; i<=n; i++){
799 x1 = swizzle(i,maxb);
800 speedtest1_numbername(x1, zNum, sizeof(zNum));
801 sqlite3_bind_int64(g.pStmt, 1, (sqlite3_int64)x1);
802 sqlite3_bind_int(g.pStmt, 2, i);
803 sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
804 speedtest1_run();
805 }
806 speedtest1_exec("COMMIT");
807 speedtest1_end_test();
808
809
810 n = sz;
811 speedtest1_begin_test(110, "%d ordered INSERTS with one index/PK", n);
812 speedtest1_exec("BEGIN");
813 speedtest1_exec(
814 "CREATE%s TABLE z2(a INTEGER %s %s, b INTEGER %s, c TEXT %s) %s",
815 isTemp(5), g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);
816 speedtest1_prepare("INSERT INTO z2 VALUES(?1,?2,?3); -- %d times", n);
817 for(i=1; i<=n; i++){
818 x1 = swizzle(i,maxb);
819 speedtest1_numbername(x1, zNum, sizeof(zNum));
820 sqlite3_bind_int(g.pStmt, 1, i);
821 sqlite3_bind_int64(g.pStmt, 2, (sqlite3_int64)x1);
822 sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
823 speedtest1_run();
824 }
825 speedtest1_exec("COMMIT");
826 speedtest1_end_test();
827
828
829 n = sz;
830 speedtest1_begin_test(120, "%d unordered INSERTS with one index/PK", n);
831 speedtest1_exec("BEGIN");
832 speedtest1_exec(
833 "CREATE%s TABLE t3(a INTEGER %s %s, b INTEGER %s, c TEXT %s) %s",
834 isTemp(3), g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);
835 speedtest1_prepare("INSERT INTO t3 VALUES(?1,?2,?3); -- %d times", n);
836 for(i=1; i<=n; i++){
837 x1 = swizzle(i,maxb);
838 speedtest1_numbername(x1, zNum, sizeof(zNum));
839 sqlite3_bind_int(g.pStmt, 2, i);
840 sqlite3_bind_int64(g.pStmt, 1, (sqlite3_int64)x1);
841 sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
842 speedtest1_run();
843 }
844 speedtest1_exec("COMMIT");
845 speedtest1_end_test();
846
847 #if SQLITE_VERSION_NUMBER<3005004
848 sqlite3_create_function(g.db, "group_concat", 1, SQLITE_UTF8, 0,
849 0, groupStep, groupFinal);
850 #endif
851
852 n = 25;
853 speedtest1_begin_test(130, "%d SELECTS, numeric BETWEEN, unindexed", n);
854 speedtest1_exec("BEGIN");
855 speedtest1_prepare(
856 "SELECT count(*), avg(b), sum(length(c)), group_concat(c) FROM z1\n"
857 " WHERE b BETWEEN ?1 AND ?2; -- %d times", n
858 );
859 for(i=1; i<=n; i++){
860 if( (i-1)%g.nRepeat==0 ){
861 x1 = speedtest1_random()%maxb;
862 x2 = speedtest1_random()%10 + sz/5000 + x1;
863 }
864 sqlite3_bind_int(g.pStmt, 1, x1);
865 sqlite3_bind_int(g.pStmt, 2, x2);
866 speedtest1_run();
867 }
868 speedtest1_exec("COMMIT");
869 speedtest1_end_test();
870
871
872 n = 10;
873 speedtest1_begin_test(140, "%d SELECTS, LIKE, unindexed", n);
874 speedtest1_exec("BEGIN");
875 speedtest1_prepare(
876 "SELECT count(*), avg(b), sum(length(c)), group_concat(c) FROM z1\n"
877 " WHERE c LIKE ?1; -- %d times", n
878 );
879 for(i=1; i<=n; i++){
880 if( (i-1)%g.nRepeat==0 ){
881 x1 = speedtest1_random()%maxb;
882 zNum[0] = '%';
883 len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
884 zNum[len] = '%';
885 zNum[len+1] = 0;
886 }
887 sqlite3_bind_text(g.pStmt, 1, zNum, len+1, SQLITE_STATIC);
888 speedtest1_run();
889 }
890 speedtest1_exec("COMMIT");
891 speedtest1_end_test();
892
893
894 n = 10;
895 speedtest1_begin_test(142, "%d SELECTS w/ORDER BY, unindexed", n);
896 speedtest1_exec("BEGIN");
897 speedtest1_prepare(
898 "SELECT a, b, c FROM z1 WHERE c LIKE ?1\n"
899 " ORDER BY a; -- %d times", n
900 );
901 for(i=1; i<=n; i++){
902 if( (i-1)%g.nRepeat==0 ){
903 x1 = speedtest1_random()%maxb;
904 zNum[0] = '%';
905 len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
906 zNum[len] = '%';
907 zNum[len+1] = 0;
908 }
909 sqlite3_bind_text(g.pStmt, 1, zNum, len+1, SQLITE_STATIC);
910 speedtest1_run();
911 }
912 speedtest1_exec("COMMIT");
913 speedtest1_end_test();
914
915 n = 10; /* g.szTest/5; */
916 speedtest1_begin_test(145, "%d SELECTS w/ORDER BY and LIMIT, unindexed", n);
917 speedtest1_exec("BEGIN");
918 speedtest1_prepare(
919 "SELECT a, b, c FROM z1 WHERE c LIKE ?1\n"
920 " ORDER BY a LIMIT 10; -- %d times", n
921 );
922 for(i=1; i<=n; i++){
923 if( (i-1)%g.nRepeat==0 ){
924 x1 = speedtest1_random()%maxb;
925 zNum[0] = '%';
926 len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
927 zNum[len] = '%';
928 zNum[len+1] = 0;
929 }
930 sqlite3_bind_text(g.pStmt, 1, zNum, len+1, SQLITE_STATIC);
931 speedtest1_run();
932 }
933 speedtest1_exec("COMMIT");
934 speedtest1_end_test();
935
936
937 speedtest1_begin_test(150, "CREATE INDEX five times");
938 speedtest1_exec("BEGIN;");
939 speedtest1_exec("CREATE UNIQUE INDEX t1b ON z1(b);");
940 speedtest1_exec("CREATE INDEX t1c ON z1(c);");
941 speedtest1_exec("CREATE UNIQUE INDEX t2b ON z2(b);");
942 speedtest1_exec("CREATE INDEX t2c ON z2(c DESC);");
943 speedtest1_exec("CREATE INDEX t3bc ON t3(b,c);");
944 speedtest1_exec("COMMIT;");
945 speedtest1_end_test();
946
947
948 n = sz/5;
949 speedtest1_begin_test(160, "%d SELECTS, numeric BETWEEN, indexed", n);
950 speedtest1_exec("BEGIN");
951 speedtest1_prepare(
952 "SELECT count(*), avg(b), sum(length(c)), group_concat(a) FROM z1\n"
953 " WHERE b BETWEEN ?1 AND ?2; -- %d times", n
954 );
955 for(i=1; i<=n; i++){
956 if( (i-1)%g.nRepeat==0 ){
957 x1 = speedtest1_random()%maxb;
958 x2 = speedtest1_random()%10 + sz/5000 + x1;
959 }
960 sqlite3_bind_int(g.pStmt, 1, x1);
961 sqlite3_bind_int(g.pStmt, 2, x2);
962 speedtest1_run();
963 }
964 speedtest1_exec("COMMIT");
965 speedtest1_end_test();
966
967
968 n = sz/5;
969 speedtest1_begin_test(161, "%d SELECTS, numeric BETWEEN, PK", n);
970 speedtest1_exec("BEGIN");
971 speedtest1_prepare(
972 "SELECT count(*), avg(b), sum(length(c)), group_concat(a) FROM z2\n"
973 " WHERE a BETWEEN ?1 AND ?2; -- %d times", n
974 );
975 for(i=1; i<=n; i++){
976 if( (i-1)%g.nRepeat==0 ){
977 x1 = speedtest1_random()%maxb;
978 x2 = speedtest1_random()%10 + sz/5000 + x1;
979 }
980 sqlite3_bind_int(g.pStmt, 1, x1);
981 sqlite3_bind_int(g.pStmt, 2, x2);
982 speedtest1_run();
983 }
984 speedtest1_exec("COMMIT");
985 speedtest1_end_test();
986
987
988 n = sz/5;
989 speedtest1_begin_test(170, "%d SELECTS, text BETWEEN, indexed", n);
990 speedtest1_exec("BEGIN");
991 speedtest1_prepare(
992 "SELECT count(*), avg(b), sum(length(c)), group_concat(a) FROM z1\n"
993 " WHERE c BETWEEN ?1 AND (?1||'~'); -- %d times", n
994 );
995 for(i=1; i<=n; i++){
996 if( (i-1)%g.nRepeat==0 ){
997 x1 = swizzle(i, maxb);
998 len = speedtest1_numbername(x1, zNum, sizeof(zNum)-1);
999 }
1000 sqlite3_bind_text(g.pStmt, 1, zNum, len, SQLITE_STATIC);
1001 speedtest1_run();
1002 }
1003 speedtest1_exec("COMMIT");
1004 speedtest1_end_test();
1005
1006 n = sz;
1007 speedtest1_begin_test(180, "%d INSERTS with three indexes", n);
1008 speedtest1_exec("BEGIN");
1009 speedtest1_exec(
1010 "CREATE%s TABLE t4(\n"
1011 " a INTEGER %s %s,\n"
1012 " b INTEGER %s,\n"
1013 " c TEXT %s\n"
1014 ") %s",
1015 isTemp(1), g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);
1016 speedtest1_exec("CREATE INDEX t4b ON t4(b)");
1017 speedtest1_exec("CREATE INDEX t4c ON t4(c)");
1018 speedtest1_exec("INSERT INTO t4 SELECT * FROM z1");
1019 speedtest1_exec("COMMIT");
1020 speedtest1_end_test();
1021
1022 n = sz;
1023 speedtest1_begin_test(190, "DELETE and REFILL one table", n);
1024 speedtest1_exec("DELETE FROM z2;");
1025 speedtest1_exec("INSERT INTO z2 SELECT * FROM z1;");
1026 speedtest1_end_test();
1027
1028
1029 speedtest1_begin_test(200, "VACUUM");
1030 speedtest1_exec("VACUUM");
1031 speedtest1_end_test();
1032
1033
1034 speedtest1_begin_test(210, "ALTER TABLE ADD COLUMN, and query");
1035 speedtest1_exec("ALTER TABLE z2 ADD COLUMN d INT DEFAULT 123");
1036 speedtest1_exec("SELECT sum(d) FROM z2");
1037 speedtest1_end_test();
1038
1039
1040 n = sz/5;
1041 speedtest1_begin_test(230, "%d UPDATES, numeric BETWEEN, indexed", n);
1042 speedtest1_exec("BEGIN");
1043 speedtest1_prepare(
1044 "UPDATE z2 SET d=b*2 WHERE b BETWEEN ?1 AND ?2; -- %d times", n
1045 );
1046 for(i=1; i<=n; i++){
1047 x1 = speedtest1_random()%maxb;
1048 x2 = speedtest1_random()%10 + sz/5000 + x1;
1049 sqlite3_bind_int(g.pStmt, 1, x1);
1050 sqlite3_bind_int(g.pStmt, 2, x2);
1051 speedtest1_run();
1052 }
1053 speedtest1_exec("COMMIT");
1054 speedtest1_end_test();
1055
1056
1057 n = sz;
1058 speedtest1_begin_test(240, "%d UPDATES of individual rows", n);
1059 speedtest1_exec("BEGIN");
1060 speedtest1_prepare(
1061 "UPDATE z2 SET d=b*3 WHERE a=?1; -- %d times", n
1062 );
1063 for(i=1; i<=n; i++){
1064 x1 = speedtest1_random()%sz + 1;
1065 sqlite3_bind_int(g.pStmt, 1, x1);
1066 speedtest1_run();
1067 }
1068 speedtest1_exec("COMMIT");
1069 speedtest1_end_test();
1070
1071 speedtest1_begin_test(250, "One big UPDATE of the whole %d-row table", sz);
1072 speedtest1_exec("UPDATE z2 SET d=b*4");
1073 speedtest1_end_test();
1074
1075
1076 speedtest1_begin_test(260, "Query added column after filling");
1077 speedtest1_exec("SELECT sum(d) FROM z2");
1078 speedtest1_end_test();
1079
1080
1081
1082 n = sz/5;
1083 speedtest1_begin_test(270, "%d DELETEs, numeric BETWEEN, indexed", n);
1084 speedtest1_exec("BEGIN");
1085 speedtest1_prepare(
1086 "DELETE FROM z2 WHERE b BETWEEN ?1 AND ?2; -- %d times", n
1087 );
1088 for(i=1; i<=n; i++){
1089 x1 = speedtest1_random()%maxb + 1;
1090 x2 = speedtest1_random()%10 + sz/5000 + x1;
1091 sqlite3_bind_int(g.pStmt, 1, x1);
1092 sqlite3_bind_int(g.pStmt, 2, x2);
1093 speedtest1_run();
1094 }
1095 speedtest1_exec("COMMIT");
1096 speedtest1_end_test();
1097
1098
1099 n = sz;
1100 speedtest1_begin_test(280, "%d DELETEs of individual rows", n);
1101 speedtest1_exec("BEGIN");
1102 speedtest1_prepare(
1103 "DELETE FROM t3 WHERE a=?1; -- %d times", n
1104 );
1105 for(i=1; i<=n; i++){
1106 x1 = speedtest1_random()%sz + 1;
1107 sqlite3_bind_int(g.pStmt, 1, x1);
1108 speedtest1_run();
1109 }
1110 speedtest1_exec("COMMIT");
1111 speedtest1_end_test();
1112
1113
1114 speedtest1_begin_test(290, "Refill two %d-row tables using REPLACE", sz);
1115 speedtest1_exec("REPLACE INTO z2(a,b,c) SELECT a,b,c FROM z1");
1116 speedtest1_exec("REPLACE INTO t3(a,b,c) SELECT a,b,c FROM z1");
1117 speedtest1_end_test();
1118
1119 speedtest1_begin_test(300, "Refill a %d-row table using (b&1)==(a&1)", sz);
1120 speedtest1_exec("DELETE FROM z2;");
1121 speedtest1_exec("INSERT INTO z2(a,b,c)\n"
1122 " SELECT a,b,c FROM z1 WHERE (b&1)==(a&1);");
1123 speedtest1_exec("INSERT INTO z2(a,b,c)\n"
1124 " SELECT a,b,c FROM z1 WHERE (b&1)<>(a&1);");
1125 speedtest1_end_test();
1126
1127
1128 n = sz/5;
1129 speedtest1_begin_test(310, "%d four-ways joins", n);
1130 speedtest1_exec("BEGIN");
1131 speedtest1_prepare(
1132 "SELECT z1.c FROM z1, z2, t3, t4\n"
1133 " WHERE t4.a BETWEEN ?1 AND ?2\n"
1134 " AND t3.a=t4.b\n"
1135 " AND z2.a=t3.b\n"
1136 " AND z1.c=z2.c;"
1137 );
1138 for(i=1; i<=n; i++){
1139 x1 = speedtest1_random()%sz + 1;
1140 x2 = speedtest1_random()%10 + x1 + 4;
1141 sqlite3_bind_int(g.pStmt, 1, x1);
1142 sqlite3_bind_int(g.pStmt, 2, x2);
1143 speedtest1_run();
1144 }
1145 speedtest1_exec("COMMIT");
1146 speedtest1_end_test();
1147
1148 speedtest1_begin_test(320, "subquery in result set", n);
1149 speedtest1_prepare(
1150 "SELECT sum(a), max(c),\n"
1151 " avg((SELECT a FROM z2 WHERE 5+z2.b=z1.b) AND rowid<?1), max(c)\n"
1152 " FROM z1 WHERE rowid<?1;"
1153 );
1154 sqlite3_bind_int(g.pStmt, 1, est_square_root(g.szTest)*50);
1155 speedtest1_run();
1156 speedtest1_end_test();
1157
1158 sz = n = g.szTest*700;
1159 zNum[0] = 0;
1160 maxb = roundup_allones(sz/3);
1161 speedtest1_begin_test(400, "%d REPLACE ops on an IPK", n);
1162 speedtest1_exec("BEGIN");
1163 speedtest1_exec("CREATE%s TABLE t5(a INTEGER PRIMARY KEY, b %s);",
1164 isTemp(9), g.zNN);
1165 speedtest1_prepare("REPLACE INTO t5 VALUES(?1,?2); -- %d times",n);
1166 for(i=1; i<=n; i++){
1167 x1 = swizzle(i,maxb);
1168 speedtest1_numbername(i, zNum, sizeof(zNum));
1169 sqlite3_bind_int(g.pStmt, 1, (sqlite3_int64)x1);
1170 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
1171 speedtest1_run();
1172 }
1173 speedtest1_exec("COMMIT");
1174 speedtest1_end_test();
1175 speedtest1_begin_test(410, "%d SELECTS on an IPK", n);
1176 if( g.doBigTransactions ){
1177 /* Historical note: tests 410 and 510 have historically not used
1178 ** explicit transactions. The --big-transactions flag was added
1179 ** 2022-09-08 to support the WASM/OPFS build, as the run-times
1180 ** approach 1 minute for each of these tests if they're not in an
1181 ** explicit transaction. The run-time effect of --big-transaciions
1182 ** on native builds is negligible. */
1183 speedtest1_exec("BEGIN");
1184 }
1185 speedtest1_prepare("SELECT b FROM t5 WHERE a=?1; -- %d times",n);
1186 for(i=1; i<=n; i++){
1187 x1 = swizzle(i,maxb);
1188 sqlite3_bind_int(g.pStmt, 1, (sqlite3_int64)x1);
1189 speedtest1_run();
1190 }
1191 if( g.doBigTransactions ){
1192 speedtest1_exec("COMMIT");
1193 }
1194 speedtest1_end_test();
1195
1196 sz = n = g.szTest*700;
1197 zNum[0] = 0;
1198 maxb = roundup_allones(sz/3);
1199 speedtest1_begin_test(500, "%d REPLACE on TEXT PK", n);
1200 speedtest1_exec("BEGIN");
1201 speedtest1_exec("CREATE%s TABLE t6(a TEXT PRIMARY KEY, b %s)%s;",
1202 isTemp(9), g.zNN,
1203 sqlite3_libversion_number()>=3008002 ? "WITHOUT ROWID" : "");
1204 speedtest1_prepare("REPLACE INTO t6 VALUES(?1,?2); -- %d times",n);
1205 for(i=1; i<=n; i++){
1206 x1 = swizzle(i,maxb);
1207 speedtest1_numbername(x1, zNum, sizeof(zNum));
1208 sqlite3_bind_int(g.pStmt, 2, i);
1209 sqlite3_bind_text(g.pStmt, 1, zNum, -1, SQLITE_STATIC);
1210 speedtest1_run();
1211 }
1212 speedtest1_exec("COMMIT");
1213 speedtest1_end_test();
1214 speedtest1_begin_test(510, "%d SELECTS on a TEXT PK", n);
1215 if( g.doBigTransactions ){
1216 /* See notes for test 410. */
1217 speedtest1_exec("BEGIN");
1218 }
1219 speedtest1_prepare("SELECT b FROM t6 WHERE a=?1; -- %d times",n);
1220 for(i=1; i<=n; i++){
1221 x1 = swizzle(i,maxb);
1222 speedtest1_numbername(x1, zNum, sizeof(zNum));
1223 sqlite3_bind_text(g.pStmt, 1, zNum, -1, SQLITE_STATIC);
1224 speedtest1_run();
1225 }
1226 if( g.doBigTransactions ){
1227 speedtest1_exec("COMMIT");
1228 }
1229 speedtest1_end_test();
1230 speedtest1_begin_test(520, "%d SELECT DISTINCT", n);
1231 speedtest1_exec("SELECT DISTINCT b FROM t5;");
1232 speedtest1_exec("SELECT DISTINCT b FROM t6;");
1233 speedtest1_end_test();
1234
1235
1236 speedtest1_begin_test(980, "PRAGMA integrity_check");
1237 speedtest1_exec("PRAGMA integrity_check");
1238 speedtest1_end_test();
1239
1240
1241 speedtest1_begin_test(990, "ANALYZE");
1242 speedtest1_exec("ANALYZE");
1243 speedtest1_end_test();
1244 }
1245
1246 /*
1247 ** A testset for common table expressions. This exercises code
1248 ** for views, subqueries, co-routines, etc.
1249 */
1250 void testset_cte(void){
1251 static const char *azPuzzle[] = {
1252 /* Easy */
1253 "534...9.."
1254 "67.195..."
1255 ".98....6."
1256 "8...6...3"
1257 "4..8.3..1"
1258 "....2...6"
1259 ".6....28."
1260 "...419..5"
1261 "...28..79",
1262
1263 /* Medium */
1264 "53....9.."
1265 "6..195..."
1266 ".98....6."
1267 "8...6...3"
1268 "4..8.3..1"
1269 "....2...6"
1270 ".6....28."
1271 "...419..5"
1272 "....8..79",
1273
1274 /* Hard */
1275 "53......."
1276 "6..195..."
1277 ".98....6."
1278 "8...6...3"
1279 "4..8.3..1"
1280 "....2...6"
1281 ".6....28."
1282 "...419..5"
1283 "....8..79",
1284 };
1285 const char *zPuz;
1286 double rSpacing;
1287 int nElem;
1288
1289 if( g.szTest<25 ){
1290 zPuz = azPuzzle[0];
1291 }else if( g.szTest<70 ){
1292 zPuz = azPuzzle[1];
1293 }else{
1294 zPuz = azPuzzle[2];
1295 }
1296 speedtest1_begin_test(100, "Sudoku with recursive 'digits'");
1297 speedtest1_prepare(
1298 "WITH RECURSIVE\n"
1299 " input(sud) AS (VALUES(?1)),\n"
1300 " digits(z,lp) AS (\n"
1301 " VALUES('1', 1)\n"
1302 " UNION ALL\n"
1303 " SELECT CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9\n"
1304 " ),\n"
1305 " x(s, ind) AS (\n"
1306 " SELECT sud, instr(sud, '.') FROM input\n"
1307 " UNION ALL\n"
1308 " SELECT\n"
1309 " substr(s, 1, ind-1) || z || substr(s, ind+1),\n"
1310 " instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )\n"
1311 " FROM x, digits AS z\n"
1312 " WHERE ind>0\n"
1313 " AND NOT EXISTS (\n"
1314 " SELECT 1\n"
1315 " FROM digits AS lp\n"
1316 " WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)\n"
1317 " OR z.z = substr(s, ((ind-1)%%9) + (lp-1)*9 + 1, 1)\n"
1318 " OR z.z = substr(s, (((ind-1)/3) %% 3) * 3\n"
1319 " + ((ind-1)/27) * 27 + lp\n"
1320 " + ((lp-1) / 3) * 6, 1)\n"
1321 " )\n"
1322 " )\n"
1323 "SELECT s FROM x WHERE ind=0;"
1324 );
1325 sqlite3_bind_text(g.pStmt, 1, zPuz, -1, SQLITE_STATIC);
1326 speedtest1_run();
1327 speedtest1_end_test();
1328
1329 speedtest1_begin_test(200, "Sudoku with VALUES 'digits'");
1330 speedtest1_prepare(
1331 "WITH RECURSIVE\n"
1332 " input(sud) AS (VALUES(?1)),\n"
1333 " digits(z,lp) AS (VALUES('1',1),('2',2),('3',3),('4',4),('5',5),\n"
1334 " ('6',6),('7',7),('8',8),('9',9)),\n"
1335 " x(s, ind) AS (\n"
1336 " SELECT sud, instr(sud, '.') FROM input\n"
1337 " UNION ALL\n"
1338 " SELECT\n"
1339 " substr(s, 1, ind-1) || z || substr(s, ind+1),\n"
1340 " instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )\n"
1341 " FROM x, digits AS z\n"
1342 " WHERE ind>0\n"
1343 " AND NOT EXISTS (\n"
1344 " SELECT 1\n"
1345 " FROM digits AS lp\n"
1346 " WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)\n"
1347 " OR z.z = substr(s, ((ind-1)%%9) + (lp-1)*9 + 1, 1)\n"
1348 " OR z.z = substr(s, (((ind-1)/3) %% 3) * 3\n"
1349 " + ((ind-1)/27) * 27 + lp\n"
1350 " + ((lp-1) / 3) * 6, 1)\n"
1351 " )\n"
1352 " )\n"
1353 "SELECT s FROM x WHERE ind=0;"
1354 );
1355 sqlite3_bind_text(g.pStmt, 1, zPuz, -1, SQLITE_STATIC);
1356 speedtest1_run();
1357 speedtest1_end_test();
1358
1359 rSpacing = 5.0/g.szTest;
1360 speedtest1_begin_test(300, "Mandelbrot Set with spacing=%f", rSpacing);
1361 speedtest1_prepare(
1362 "WITH RECURSIVE \n"
1363 " xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+?1 FROM xaxis WHERE x<1.2),\n"
1364 " yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+?2 FROM yaxis WHERE y<1.0),\n"
1365 " m(iter, cx, cy, x, y) AS (\n"
1366 " SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis\n"
1367 " UNION ALL\n"
1368 " SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m \n"
1369 " WHERE (x*x + y*y) < 4.0 AND iter<28\n"
1370 " ),\n"
1371 " m2(iter, cx, cy) AS (\n"
1372 " SELECT max(iter), cx, cy FROM m GROUP BY cx, cy\n"
1373 " ),\n"
1374 " a(t) AS (\n"
1375 " SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') \n"
1376 " FROM m2 GROUP BY cy\n"
1377 " )\n"
1378 "SELECT group_concat(rtrim(t),x'0a') FROM a;"
1379 );
1380 sqlite3_bind_double(g.pStmt, 1, rSpacing*.05);
1381 sqlite3_bind_double(g.pStmt, 2, rSpacing);
1382 speedtest1_run();
1383 speedtest1_end_test();
1384
1385 nElem = 10000*g.szTest;
1386 speedtest1_begin_test(400, "EXCEPT operator on %d-element tables", nElem);
1387 speedtest1_prepare(
1388 "WITH RECURSIVE \n"
1389 " z1(x) AS (VALUES(2) UNION ALL SELECT x+2 FROM z1 WHERE x<%d),\n"
1390 " z2(y) AS (VALUES(3) UNION ALL SELECT y+3 FROM z2 WHERE y<%d)\n"
1391 "SELECT count(x), avg(x) FROM (\n"
1392 " SELECT x FROM z1 EXCEPT SELECT y FROM z2 ORDER BY 1\n"
1393 ");",
1394 nElem, nElem
1395 );
1396 speedtest1_run();
1397 speedtest1_end_test();
1398 }
1399
1400 /*
1401 ** Compute a pseudo-random floating point ascii number.
1402 */
1403 void speedtest1_random_ascii_fp(char *zFP){
1404 int x = speedtest1_random();
1405 int y = speedtest1_random();
1406 int z;
1407 z = y%10;
1408 if( z<0 ) z = -z;
1409 y /= 10;
1410 sqlite3_snprintf(100,zFP,"%d.%de%d",y,z,x%200);
1411 }
1412
1413 /*
1414 ** A testset for floating-point numbers.
1415 */
1416 void testset_fp(void){
1417 int n;
1418 int i;
1419 char zFP1[100];
1420 char zFP2[100];
1421
1422 n = g.szTest*5000;
1423 speedtest1_begin_test(100, "Fill a table with %d FP values", n*2);
1424 speedtest1_exec("BEGIN");
1425 speedtest1_exec("CREATE%s TABLE z1(a REAL %s, b REAL %s);",
1426 isTemp(1), g.zNN, g.zNN);
1427 speedtest1_prepare("INSERT INTO z1 VALUES(?1,?2); -- %d times", n);
1428 for(i=1; i<=n; i++){
1429 speedtest1_random_ascii_fp(zFP1);
1430 speedtest1_random_ascii_fp(zFP2);
1431 sqlite3_bind_text(g.pStmt, 1, zFP1, -1, SQLITE_STATIC);
1432 sqlite3_bind_text(g.pStmt, 2, zFP2, -1, SQLITE_STATIC);
1433 speedtest1_run();
1434 }
1435 speedtest1_exec("COMMIT");
1436 speedtest1_end_test();
1437
1438 n = g.szTest/25 + 2;
1439 speedtest1_begin_test(110, "%d range queries", n);
1440 speedtest1_prepare("SELECT sum(b) FROM z1 WHERE a BETWEEN ?1 AND ?2");
1441 for(i=1; i<=n; i++){
1442 speedtest1_random_ascii_fp(zFP1);
1443 speedtest1_random_ascii_fp(zFP2);
1444 sqlite3_bind_text(g.pStmt, 1, zFP1, -1, SQLITE_STATIC);
1445 sqlite3_bind_text(g.pStmt, 2, zFP2, -1, SQLITE_STATIC);
1446 speedtest1_run();
1447 }
1448 speedtest1_end_test();
1449
1450 speedtest1_begin_test(120, "CREATE INDEX three times");
1451 speedtest1_exec("BEGIN;");
1452 speedtest1_exec("CREATE INDEX t1a ON z1(a);");
1453 speedtest1_exec("CREATE INDEX t1b ON z1(b);");
1454 speedtest1_exec("CREATE INDEX t1ab ON z1(a,b);");
1455 speedtest1_exec("COMMIT;");
1456 speedtest1_end_test();
1457
1458 n = g.szTest/3 + 2;
1459 speedtest1_begin_test(130, "%d indexed range queries", n);
1460 speedtest1_prepare("SELECT sum(b) FROM z1 WHERE a BETWEEN ?1 AND ?2");
1461 for(i=1; i<=n; i++){
1462 speedtest1_random_ascii_fp(zFP1);
1463 speedtest1_random_ascii_fp(zFP2);
1464 sqlite3_bind_text(g.pStmt, 1, zFP1, -1, SQLITE_STATIC);
1465 sqlite3_bind_text(g.pStmt, 2, zFP2, -1, SQLITE_STATIC);
1466 speedtest1_run();
1467 }
1468 speedtest1_end_test();
1469
1470 n = g.szTest*5000;
1471 speedtest1_begin_test(140, "%d calls to round()", n);
1472 speedtest1_exec("SELECT sum(round(a,2)+round(b,4)) FROM z1;");
1473 speedtest1_end_test();
1474
1475
1476 speedtest1_begin_test(150, "%d printf() calls", n*4);
1477 speedtest1_exec(
1478 "WITH c(fmt) AS (VALUES('%%g'),('%%e'),('%%!g'),('%%.20f'))"
1479 "SELECT sum(printf(fmt,a)) FROM z1, c"
1480 );
1481 speedtest1_end_test();
1482 }
1483
1484 /*
1485 ** A testset for star-schema queries.
1486 */
1487 void testset_star(void){
1488 int n;
1489 int i;
1490 n = g.szTest*50;
1491 speedtest1_begin_test(100, "Create a fact table with %d entries", n);
1492 speedtest1_exec(
1493 "CREATE TABLE facttab("
1494 " attr01 INT,"
1495 " attr02 INT,"
1496 " attr03 INT,"
1497 " data01 TEXT,"
1498 " attr04 INT,"
1499 " attr05 INT,"
1500 " attr06 INT,"
1501 " attr07 INT,"
1502 " attr08 INT,"
1503 " factid INTEGER PRIMARY KEY,"
1504 " data02 TEXT"
1505 ");"
1506 );
1507 speedtest1_exec(
1508 "WITH RECURSIVE counter(nnn) AS"
1509 "(VALUES(1) UNION ALL SELECT nnn+1 FROM counter WHERE nnn<%d)"
1510 "INSERT INTO facttab(attr01,attr02,attr03,attr04,attr05,"
1511 "attr06,attr07,attr08,data01,data02)"
1512 "SELECT random()%%12, random()%%13, random()%%14, random()%%15,"
1513 "random()%%16, random()%%17, random()%%18, random()%%19,"
1514 "concat('data-',nnn), format('%%x',random()) FROM counter;",
1515 n
1516 );
1517 speedtest1_end_test();
1518
1519 speedtest1_begin_test(110, "Create indexes on all attributes columns");
1520 for(i=1; i<=8; i++){
1521 speedtest1_exec(
1522 "CREATE INDEX fact_attr%02d ON facttab(attr%02d)", i, i
1523 );
1524 }
1525 speedtest1_end_test();
1526
1527 speedtest1_begin_test(120, "Create dimension tables");
1528 for(i=1; i<=8; i++){
1529 speedtest1_exec(
1530 "CREATE TABLE dimension%02d("
1531 "beta%02d INT, "
1532 "content%02d TEXT, "
1533 "rate%02d REAL)",
1534 i, i, i, i
1535 );
1536 speedtest1_exec(
1537 "WITH RECURSIVE ctr(nn) AS"
1538 " (VALUES(1) UNION ALL SELECT nn+1 FROM ctr WHERE nn<%d)"
1539 " INSERT INTO dimension%02d"
1540 " SELECT nn%%(%d), concat('content-%02d-',nn),"
1541 " (random()%%10000)*0.125 FROM ctr;",
1542 4*(i+1), i, 2*(i+1), i
1543 );
1544 if( i&2 ){
1545 speedtest1_exec(
1546 "CREATE INDEX dim%02d ON dimension%02d(beta%02d);",
1547 i, i, i
1548 );
1549 }else{
1550 speedtest1_exec(
1551 "CREATE INDEX dim%02d ON dimension%02d(beta%02d,content%02d);",
1552 i, i, i, i
1553 );
1554 }
1555 }
1556 speedtest1_end_test();
1557
1558 speedtest1_begin_test(130, "Star query over the entire fact table");
1559 speedtest1_exec(
1560 "SELECT count(*), max(content04), min(content03), sum(rate04), avg(rate05)"
1561 " FROM facttab, dimension01, dimension02, dimension03, dimension04,"
1562 " dimension05, dimension06, dimension07, dimension08"
1563 " WHERE attr01=beta01"
1564 " AND attr02=beta02"
1565 " AND attr03=beta03"
1566 " AND attr04=beta04"
1567 " AND attr05=beta05"
1568 " AND attr06=beta06"
1569 " AND attr07=beta07"
1570 " AND attr08=beta08"
1571 ";"
1572 );
1573 speedtest1_end_test();
1574
1575 speedtest1_begin_test(130, "Star query with LEFT JOINs");
1576 speedtest1_exec(
1577 "SELECT count(*), max(content04), min(content03), sum(rate04), avg(rate05)"
1578 " FROM facttab LEFT JOIN dimension01 ON attr01=beta01"
1579 " LEFT JOIN dimension02 ON attr02=beta02"
1580 " JOIN dimension03 ON attr03=beta03"
1581 " JOIN dimension04 ON attr04=beta04"
1582 " JOIN dimension05 ON attr05=beta05"
1583 " LEFT JOIN dimension06 ON attr06=beta06"
1584 " JOIN dimension07 ON attr07=beta07"
1585 " JOIN dimension08 ON attr08=beta08"
1586 " WHERE facttab.data01 LIKE 'data-9%%'"
1587 ";"
1588 );
1589 speedtest1_end_test();
1590 }
1591
1592 /*
1593 ** Tests that simulate an application opening and closing an SQLite database
1594 ** frequently. Fossil is used as the model. The focus here is on rapidly
1595 ** parsing the database schema and rapidly generating prepared statements,
1596 ** in other words, rapid start-up of Fossil-like applications.
1597 **
1598 ** The same database has no data, so the performance of sqlite3_step() is
1599 ** not significant to this testset.
1600 */
1601 static void testset_app(void){
1602 int i, n;
1603 speedtest1_begin_test(100, "Generate a Fossil-like database schema");
1604 speedtest1_exec(
1605 "BEGIN;"
1606 "CREATE TABLE blob(\n"
1607 " rid INTEGER PRIMARY KEY,\n"
1608 " rcvid INTEGER,\n"
1609 " size INTEGER,\n"
1610 " uuid TEXT UNIQUE NOT NULL,\n"
1611 " content BLOB,\n"
1612 " CHECK( length(uuid)>=40 AND rid>0 )\n"
1613 ");\n"
1614 "CREATE TABLE delta(\n"
1615 " rid INTEGER PRIMARY KEY,\n"
1616 " srcid INTEGER NOT NULL REFERENCES blob\n"
1617 ");\n"
1618 "CREATE TABLE rcvfrom(\n"
1619 " rcvid INTEGER PRIMARY KEY,\n"
1620 " uid INTEGER REFERENCES user,\n"
1621 " mtime DATETIME,\n"
1622 " nonce TEXT UNIQUE,\n"
1623 " ipaddr TEXT\n"
1624 ");\n"
1625 "CREATE TABLE private(rid INTEGER PRIMARY KEY);\n"
1626 "CREATE TABLE accesslog(\n"
1627 " uname TEXT,\n"
1628 " ipaddr TEXT,\n"
1629 " success BOOLEAN,\n"
1630 " mtime TIMESTAMP\n"
1631 ");\n"
1632 "CREATE TABLE user(\n"
1633 " uid INTEGER PRIMARY KEY,\n"
1634 " login TEXT UNIQUE,\n"
1635 " pw TEXT,\n"
1636 " cap TEXT,\n"
1637 " cookie TEXT,\n"
1638 " ipaddr TEXT,\n"
1639 " cexpire DATETIME,\n"
1640 " info TEXT,\n"
1641 " mtime DATE,\n"
1642 " photo BLOB\n"
1643 ", jx TEXT DEFAULT '{}');\n"
1644 "CREATE TABLE reportfmt(\n"
1645 " rn INTEGER PRIMARY KEY,\n"
1646 " owner TEXT,\n"
1647 " title TEXT UNIQUE,\n"
1648 " mtime INTEGER,\n"
1649 " cols TEXT,\n"
1650 " sqlcode TEXT\n"
1651 ", jx TEXT DEFAULT '{}');\n"
1652 "CREATE TABLE config(\n"
1653 " name TEXT PRIMARY KEY NOT NULL,\n"
1654 " value CLOB, mtime INTEGER,\n"
1655 " CHECK( typeof(name)='text' AND length(name)>=1 )\n"
1656 ") WITHOUT ROWID;\n"
1657 "CREATE TABLE shun(uuid PRIMARY KEY, mtime INTEGER, scom TEXT)\n"
1658 " WITHOUT ROWID;\n"
1659 "CREATE TABLE concealed(\n"
1660 " hash TEXT PRIMARY KEY,\n"
1661 " content TEXT\n"
1662 ", mtime INTEGER) WITHOUT ROWID;\n"
1663 "CREATE TABLE admin_log(\n"
1664 " id INTEGER PRIMARY KEY,\n"
1665 " time INTEGER, -- Seconds since 1970\n"
1666 " page TEXT, -- path of page\n"
1667 " who TEXT, -- User who made the change\n"
1668 " what TEXT -- What changed\n"
1669 ");\n"
1670 "CREATE TABLE unversioned(\n"
1671 " name TEXT PRIMARY KEY,\n"
1672 " rcvid INTEGER,\n"
1673 " mtime DATETIME,\n"
1674 " hash TEXT,\n"
1675 " sz INTEGER,\n"
1676 " encoding INT,\n"
1677 " content BLOB\n"
1678 ") WITHOUT ROWID;\n"
1679 "CREATE TABLE subscriber(\n"
1680 " subscriberId INTEGER PRIMARY KEY,\n"
1681 " subscriberCode BLOB DEFAULT (randomblob(32)) UNIQUE,\n"
1682 " semail TEXT UNIQUE COLLATE nocase,\n"
1683 " suname TEXT,\n"
1684 " sverified BOOLEAN DEFAULT true,\n"
1685 " sdonotcall BOOLEAN,\n"
1686 " sdigest BOOLEAN,\n"
1687 " ssub TEXT,\n"
1688 " sctime INTDATE,\n"
1689 " mtime INTDATE,\n"
1690 " smip TEXT\n"
1691 ", lastContact INT);\n"
1692 "CREATE TABLE pending_alert(\n"
1693 " eventid TEXT PRIMARY KEY,\n"
1694 " sentSep BOOLEAN DEFAULT false,\n"
1695 " sentDigest BOOLEAN DEFAULT false\n"
1696 ", sentMod BOOLEAN DEFAULT false) WITHOUT ROWID;\n"
1697 "CREATE TABLE filename(\n"
1698 " fnid INTEGER PRIMARY KEY,\n"
1699 " name TEXT UNIQUE\n"
1700 ") STRICT;\n"
1701 "CREATE TABLE mlink(\n"
1702 " mid INTEGER,\n"
1703 " fid INTEGER,\n"
1704 " pmid INTEGER,\n"
1705 " pid INTEGER,\n"
1706 " fnid INTEGER REFERENCES filename,\n"
1707 " pfnid INTEGER,\n"
1708 " mperm INTEGER,\n"
1709 " isaux INT DEFAULT 0\n"
1710 ") STRICT;\n"
1711 "CREATE TABLE plink(\n"
1712 " pid INTEGER REFERENCES blob,\n"
1713 " cid INTEGER REFERENCES blob,\n"
1714 " isprim INT,\n"
1715 " mtime REAL,\n"
1716 " baseid INTEGER REFERENCES blob,\n"
1717 " UNIQUE(pid, cid)\n"
1718 ") STRICT;\n"
1719 "CREATE TABLE leaf(rid INTEGER PRIMARY KEY);\n"
1720 "CREATE TABLE event(\n"
1721 " type TEXT,\n"
1722 " mtime REAL,\n"
1723 " objid INTEGER PRIMARY KEY,\n"
1724 " tagid INTEGER,\n"
1725 " uid INTEGER REFERENCES user,\n"
1726 " bgcolor TEXT,\n"
1727 " euser TEXT,\n"
1728 " user TEXT,\n"
1729 " ecomment TEXT,\n"
1730 " comment TEXT,\n"
1731 " brief TEXT,\n"
1732 " omtime REAL\n"
1733 ") STRICT;\n"
1734 "CREATE TABLE phantom(\n"
1735 " rid INTEGER PRIMARY KEY\n"
1736 ");\n"
1737 "CREATE TABLE orphan(\n"
1738 " rid INTEGER PRIMARY KEY,\n"
1739 " baseline INTEGER\n"
1740 ") STRICT;\n"
1741 "CREATE TABLE unclustered(\n"
1742 " rid INTEGER PRIMARY KEY\n"
1743 ");\n"
1744 "CREATE TABLE unsent(\n"
1745 " rid INTEGER PRIMARY KEY\n"
1746 ");\n"
1747 "CREATE TABLE tag(\n"
1748 " tagid INTEGER PRIMARY KEY,\n"
1749 " tagname TEXT UNIQUE\n"
1750 ") STRICT;\n"
1751 "CREATE TABLE tagxref(\n"
1752 " tagid INTEGER REFERENCES tag,\n"
1753 " tagtype INTEGER,\n"
1754 " srcid INTEGER REFERENCES blob,\n"
1755 " origid INTEGER REFERENCES blob,\n"
1756 " value TEXT,\n"
1757 " mtime REAL,\n"
1758 " rid INTEGER REFERENCES blob,\n"
1759 " UNIQUE(rid, tagid)\n"
1760 ") STRICT;\n"
1761 "CREATE TABLE backlink(\n"
1762 " target TEXT,\n"
1763 " srctype INT,\n"
1764 " srcid INT,\n"
1765 " mtime REAL,\n"
1766 " UNIQUE(target, srctype, srcid)\n"
1767 ") STRICT;\n"
1768 "CREATE TABLE attachment(\n"
1769 " attachid INTEGER PRIMARY KEY,\n"
1770 " isLatest INT DEFAULT 0,\n"
1771 " mtime REAL,\n"
1772 " src TEXT,\n"
1773 " target TEXT,\n"
1774 " filename TEXT,\n"
1775 " comment TEXT,\n"
1776 " user TEXT\n"
1777 ") STRICT;\n"
1778 "CREATE TABLE cherrypick(\n"
1779 " parentid INT,\n"
1780 " childid INT,\n"
1781 " isExclude INT DEFAULT false,\n"
1782 " PRIMARY KEY(parentid, childid)\n"
1783 ") WITHOUT ROWID, STRICT;\n"
1784 "CREATE TABLE vcache(\n"
1785 " vid INTEGER, -- check-in ID\n"
1786 " fname TEXT, -- filename\n"
1787 " rid INTEGER, -- artifact ID\n"
1788 " PRIMARY KEY(vid,fname)\n"
1789 ") WITHOUT ROWID;\n"
1790 "CREATE TABLE synclog(\n"
1791 " sfrom TEXT,\n"
1792 " sto TEXT,\n"
1793 " stime INT NOT NULL,\n"
1794 " stype TEXT,\n"
1795 " PRIMARY KEY(sfrom,sto)\n"
1796 ") WITHOUT ROWID;\n"
1797 "CREATE TABLE chat(\n"
1798 " msgid INTEGER PRIMARY KEY AUTOINCREMENT,\n"
1799 " mtime JULIANDAY,\n"
1800 " lmtime TEXT,\n"
1801 " xfrom TEXT,\n"
1802 " xmsg TEXT,\n"
1803 " fname TEXT,\n"
1804 " fmime TEXT,\n"
1805 " mdel INT,\n"
1806 " file BLOB\n"
1807 ");\n"
1808 "CREATE TABLE ftsdocs(\n"
1809 " rowid INTEGER PRIMARY KEY,\n"
1810 " type CHAR(1),\n"
1811 " rid INTEGER,\n"
1812 " name TEXT,\n"
1813 " idxed BOOLEAN,\n"
1814 " label TEXT,\n"
1815 " url TEXT,\n"
1816 " mtime DATE,\n"
1817 " bx TEXT,\n"
1818 " UNIQUE(type,rid)\n"
1819 ");\n"
1820 "CREATE TABLE ticket(\n"
1821 " -- Do not change any column that begins with tkt_\n"
1822 " tkt_id INTEGER PRIMARY KEY,\n"
1823 " tkt_uuid TEXT UNIQUE,\n"
1824 " tkt_mtime DATE,\n"
1825 " tkt_ctime DATE,\n"
1826 " -- Add as many fields as required below this line\n"
1827 " type TEXT,\n"
1828 " status TEXT,\n"
1829 " subsystem TEXT,\n"
1830 " priority TEXT,\n"
1831 " severity TEXT,\n"
1832 " foundin TEXT,\n"
1833 " private_contact TEXT,\n"
1834 " resolution TEXT,\n"
1835 " title TEXT,\n"
1836 " comment TEXT\n"
1837 ");\n"
1838 "CREATE TABLE ticketchng(\n"
1839 " -- Do not change any column that begins with tkt_\n"
1840 " tkt_id INTEGER REFERENCES ticket,\n"
1841 " tkt_rid INTEGER REFERENCES blob,\n"
1842 " tkt_mtime DATE,\n"
1843 " tkt_user TEXT,\n"
1844 " -- Add as many fields as required below this line\n"
1845 " login TEXT,\n"
1846 " username TEXT,\n"
1847 " mimetype TEXT,\n"
1848 " icomment TEXT\n"
1849 ");\n"
1850 "CREATE TABLE forumpost(\n"
1851 " fpid INTEGER PRIMARY KEY,\n"
1852 " froot INT,\n"
1853 " fprev INT,\n"
1854 " firt INT,\n"
1855 " fmtime REAL\n"
1856 ");\n"
1857 "CREATE INDEX delta_i1 ON delta(srcid);\n"
1858 "CREATE INDEX blob_rcvid ON blob(rcvid);\n"
1859 "CREATE INDEX subscriberUname\n"
1860 " ON subscriber(suname) WHERE suname IS NOT NULL;\n"
1861 "CREATE INDEX mlink_i1 ON mlink(mid);\n"
1862 "CREATE INDEX mlink_i2 ON mlink(fnid);\n"
1863 "CREATE INDEX mlink_i3 ON mlink(fid);\n"
1864 "CREATE INDEX mlink_i4 ON mlink(pid);\n"
1865 "CREATE INDEX plink_i2 ON plink(cid,pid);\n"
1866 "CREATE INDEX event_i1 ON event(mtime);\n"
1867 "CREATE INDEX orphan_baseline ON orphan(baseline);\n"
1868 "CREATE INDEX tagxref_i1 ON tagxref(tagid, mtime);\n"
1869 "CREATE INDEX backlink_src ON backlink(srcid, srctype);\n"
1870 "CREATE INDEX attachment_idx1 ON attachment(target, filename, mtime);\n"
1871 "CREATE INDEX attachment_idx2 ON attachment(src);\n"
1872 "CREATE INDEX cherrypick_cid ON cherrypick(childid);\n"
1873 "CREATE INDEX ftsdocIdxed ON ftsdocs(type,rid,name) WHERE idxed==0;\n"
1874 "CREATE INDEX ftsdocName ON ftsdocs(name) WHERE type='w';\n"
1875 "CREATE INDEX ticketchng_idx1 ON ticketchng(tkt_id, tkt_mtime);\n"
1876 "CREATE INDEX forumthread ON forumpost(froot,fmtime);\n"
1877 "CREATE VIEW artifact(rid,rcvid,size,atype,srcid,hash,content) AS\n"
1878 " SELECT blob.rid,rcvid,size,1,srcid,uuid,content\n"
1879 " FROM blob LEFT JOIN delta ON (blob.rid=delta.rid);\n"
1880 "CREATE VIEW ftscontent AS\n"
1881 " SELECT rowid, type, rid, name, idxed, label, url, mtime,\n"
1882 " title(type,rid,name) AS 'title', body(type,rid,name) AS 'body'\n"
1883 " FROM ftsdocs;\n"
1884 );
1885 if( sqlite3_compileoption_used("ENABLE_FTS5") ){
1886 speedtest1_exec(
1887 "CREATE VIRTUAL TABLE ftsidx\n"
1888 " USING fts5(content=\"ftscontent\", title, body);\n"
1889 "CREATE VIRTUAL TABLE chatfts1 USING fts5(\n"
1890 " xmsg, content=chat, content_rowid=msgid,tokenize=porter);\n"
1891 );
1892 }else{
1893 speedtest1_exec(
1894 "CREATE TABLE ftsidx_data(id INTEGER PRIMARY KEY, block BLOB);\n"
1895 "CREATE TABLE ftsidx_idx(segid, term, pgno, PRIMARY KEY(segid, term))\n"
1896 " WITHOUT ROWID;\n"
1897 "CREATE TABLE ftsidx_docsize(id INTEGER PRIMARY KEY, sz BLOB);\n"
1898 "CREATE TABLE ftsidx_config(k PRIMARY KEY, v) WITHOUT ROWID;\n"
1899 "CREATE TABLE chatfts1_data(id INTEGER PRIMARY KEY, block BLOB);\n"
1900 "CREATE TABLE chatfts1_idx(segid, term, pgno, PRIMARY KEY(segid, term))\n"
1901 " WITHOUT ROWID;\n"
1902 "CREATE TABLE chatfts1_docsize(id INTEGER PRIMARY KEY, sz BLOB);\n"
1903 "CREATE TABLE chatfts1_config(k PRIMARY KEY, v) WITHOUT ROWID;\n"
1904 );
1905 }
1906 speedtest1_exec(
1907 "ANALYZE sqlite_schema;\n"
1908 "INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES\n"
1909 " ('ftsidx_config','ftsidx_config','1 1'),\n"
1910 " ('ftsidx_idx','ftsidx_idx','4215 401 1'),\n"
1911 " ('user','sqlite_autoindex_user_1','25 1'),\n"
1912 " ('phantom',NULL,'26'),\n"
1913 " ('reportfmt','sqlite_autoindex_reportfmt_1','9 1'),\n"
1914 " ('rcvfrom','sqlite_autoindex_rcvfrom_1','18445 401'),\n"
1915 " ('private',NULL,'99'),\n"
1916 " ('mlink','mlink_i4','116678 401'),\n"
1917 " ('mlink','mlink_i3','121212 2'),\n"
1918 " ('mlink','mlink_i2','106372 401'),\n"
1919 " ('mlink','mlink_i1','99298 5'),\n"
1920 " ('ftsidx_data',NULL,'3795'),\n"
1921 " ('leaf',NULL,'1559'),\n"
1922 " ('delta','delta_i1','66340 1'),\n"
1923 " ('unversioned','unversioned','3 1'),\n"
1924 " ('pending_alert','pending_alert','3 1'),\n"
1925 " ('cherrypick','cherrypick_cid','680 2'),\n"
1926 " ('cherrypick','cherrypick','628 1 1'),\n"
1927 " ('config','config','128 1'),\n"
1928 " ('ftsidx_docsize',NULL,'33848'),\n"
1929 " ('event','event_i1','36096 1'),\n"
1930 " ('plink','plink_i2','38236 1 1'),\n"
1931 " ('plink','sqlite_autoindex_plink_1','38357 1 1'),\n"
1932 " ('shun','shun','10 1'),\n"
1933 " ('concealed','concealed','110 1'),\n"
1934 " ('vcache','vcache','1888 401 1'),\n"
1935 " ('ftsdocs','ftsdocName','19 1'),\n"
1936 " ('ftsdocs','ftsdocIdxed','168 84 1 1'),\n"
1937 " ('ftsdocs','sqlite_autoindex_ftsdocs_1','37312 401 1'),\n"
1938 " ('subscriber','subscriberUname','5 1'),\n"
1939 " ('subscriber','sqlite_autoindex_subscriber_2','37 1'),\n"
1940 " ('subscriber','sqlite_autoindex_subscriber_1','37 1'),\n"
1941 " ('tag','sqlite_autoindex_tag_1','2990 1'),\n"
1942 " ('filename','sqlite_autoindex_filename_1','3168 1'),\n"
1943 " ('chat',NULL,'56124'),\n"
1944 " ('tagxref','tagxref_i1','40992 401 2'),\n"
1945 " ('tagxref','sqlite_autoindex_tagxref_1','79233 3 1'),\n"
1946 " ('attachment','attachment_idx2','11 1'),\n"
1947 " ('attachment','attachment_idx1','11 2 2 1'),\n"
1948 " ('blob','blob_rcvid','128240 201'),\n"
1949 " ('blob','sqlite_autoindex_blob_1','126480 1'),\n"
1950 " ('synclog','synclog','12 3 1'),\n"
1951 " ('backlink','backlink_src','2160 2 2'),\n"
1952 " ('backlink','sqlite_autoindex_backlink_1','2340 2 2 1'),\n"
1953 " ('accesslog',NULL,'38'),\n"
1954 " ('chatfts1_config','chatfts1_config','1 1'),\n"
1955 " ('chatfts1_idx','chatfts1_idx','688 230 1'),\n"
1956 " ('ticket','sqlite_autoindex_ticket_1','794 1'),\n"
1957 " ('ticketchng','ticketchng_idx1','2089 3 1'),\n"
1958 " ('forumpost','forumthread','4 4 1'),\n"
1959 " ('unclustered',NULL,'12');\n"
1960 "COMMIT;"
1961 );
1962 speedtest1_end_test();
1963
1964 n = g.szTest*3;
1965 speedtest1_begin_test(110, "Open and use the database %d times", n);
1966 for(i=0; i<n; i++){
1967 sqlite3 *dbMain = g.db;
1968 sqlite3 *dbAux = 0;
1969 if( g.zDbName && g.zDbName[0] ){
1970 if( sqlite3_open_v2(g.zDbName, &dbAux, SQLITE_OPEN_READWRITE, g.zVfs) ){
1971 fatal_error("Cannot open database file: %s\n", g.zDbName);
1972 }
1973 g.db = dbAux;
1974 }
1975 speedtest1_exec(
1976 "SELECT name FROM pragma_table_list /*scan*/"
1977 " WHERE schema='repository' AND type IN ('table','virtual')"
1978 " AND name NOT IN ('admin_log', 'blob','delta','rcvfrom','user','alias',"
1979 "'config','shun','private','reportfmt',"
1980 "'concealed','accesslog','modreq',"
1981 "'purgeevent','purgeitem','unversioned',"
1982 "'subscriber','pending_alert','chat')"
1983 " AND name NOT GLOB 'sqlite_*'"
1984 " AND name NOT GLOB 'fx_*';"
1985 "SELECT 1 FROM pragma_table_xinfo('ticket') WHERE name = 'mimetype';"
1986 );
1987 speedtest1_exec(
1988 "SELECT"
1989 " name,"
1990 " value,"
1991 " unixepoch()/86400-value,"
1992 " date(value*86400,'unixepoch')"
1993 " FROM config"
1994 " WHERE name in ('email-renew-warning','email-renew-cutoff');"
1995 "SELECT count(*) FROM pending_alert WHERE NOT sentDigest;"
1996 );
1997 speedtest1_exec(
1998 "WITH priors(rid,who) AS ("
1999 " SELECT firt, coalesce(euser,user)"
2000 " FROM forumpost LEFT JOIN event ON fpid=objid"
2001 " WHERE fpid=12345"
2002 " UNION ALL"
2003 " SELECT firt, coalesce(euser,user)"
2004 " FROM priors, forumpost LEFT JOIN event ON fpid=objid"
2005 " WHERE fpid=rid"
2006 ")"
2007 "SELECT ','||group_concat(DISTINCT 'u'||who)||"
2008 "','||group_concat(rid) FROM priors;"
2009 );
2010 speedtest1_exec(
2011 "CREATE TEMP TABLE IF NOT EXISTS ok(rid INTEGER PRIMARY KEY);\n"
2012 );
2013 speedtest1_exec(
2014 "WITH RECURSIVE\n"
2015 " parent(pid,cid,isCP) AS (\n"
2016 " SELECT plink.pid, plink.cid, 0 AS xisCP FROM plink\n"
2017 " UNION ALL\n"
2018 " SELECT parentid, childid, 1 FROM cherrypick WHERE NOT isExclude\n"
2019 " ),\n"
2020 " ancestor(rid, mtime, isCP) AS (\n"
2021 " SELECT 123, mtime, 0 FROM event WHERE objid=$object\n"
2022 " UNION\n"
2023 " SELECT parent.pid, event.mtime, parent.isCP\n"
2024 " FROM ancestor, parent, event\n"
2025 " WHERE parent.cid=ancestor.rid\n"
2026 " AND event.objid=parent.pid\n"
2027 " AND NOT ancestor.isCP\n"
2028 " AND (event.mtime>=$date OR parent.pid=$pid)\n"
2029 " ORDER BY mtime DESC LIMIT 10\n"
2030 " )\n"
2031 " INSERT OR IGNORE INTO ok SELECT rid FROM ancestor;"
2032 );
2033 sqlite3_close(dbAux);
2034 g.db = dbMain;
2035 }
2036 speedtest1_end_test();
2037 }
2038
2039 #ifdef SQLITE_ENABLE_RTREE
2040 /* Generate two numbers between 1 and mx. The first number is less than
2041 ** the second. Usually the numbers are near each other but can sometimes
2042 ** be far apart.
2043 */
2044 static void twoCoords(
2045 int p1, int p2, /* Parameters adjusting sizes */
2046 unsigned mx, /* Range of 1..mx */
2047 unsigned *pX0, unsigned *pX1 /* OUT: write results here */
2048 ){
2049 unsigned d, x0, x1, span;
2050
2051 span = mx/100 + 1;
2052 if( speedtest1_random()%3==0 ) span *= p1;
2053 if( speedtest1_random()%p2==0 ) span = mx/2;
2054 d = speedtest1_random()%span + 1;
2055 x0 = speedtest1_random()%(mx-d) + 1;
2056 x1 = x0 + d;
2057 *pX0 = x0;
2058 *pX1 = x1;
2059 }
2060 #endif
2061
2062 #ifdef SQLITE_ENABLE_RTREE
2063 /* The following routine is an R-Tree geometry callback. It returns
2064 ** true if the object overlaps a slice on the Y coordinate between the
2065 ** two values given as arguments. In other words
2066 **
2067 ** SELECT count(*) FROM rt1 WHERE id MATCH xslice(10,20);
2068 **
2069 ** Is the same as saying:
2070 **
2071 ** SELECT count(*) FROM rt1 WHERE y1>=10 AND y0<=20;
2072 */
2073 static int xsliceGeometryCallback(
2074 sqlite3_rtree_geometry *p,
2075 int nCoord,
2076 double *aCoord,
2077 int *pRes
2078 ){
2079 *pRes = aCoord[3]>=p->aParam[0] && aCoord[2]<=p->aParam[1];
2080 return SQLITE_OK;
2081 }
2082 #endif /* SQLITE_ENABLE_RTREE */
2083
2084 #ifdef SQLITE_ENABLE_RTREE
2085 /*
2086 ** A testset for the R-Tree virtual table
2087 */
2088 void testset_rtree(int p1, int p2){
2089 unsigned i, n;
2090 unsigned mxCoord;
2091 unsigned x0, x1, y0, y1, z0, z1;
2092 unsigned iStep;
2093 unsigned mxRowid;
2094 int *aCheck = sqlite3_malloc( sizeof(int)*g.szTest*500 );
2095
2096 mxCoord = 15000;
2097 mxRowid = n = g.szTest*500;
2098 speedtest1_begin_test(100, "%d INSERTs into an r-tree", n);
2099 speedtest1_exec("BEGIN");
2100 speedtest1_exec("CREATE VIRTUAL TABLE rt1 USING rtree(id,x0,x1,y0,y1,z0,z1)");
2101 speedtest1_prepare("INSERT INTO rt1(id,x0,x1,y0,y1,z0,z1)"
2102 "VALUES(?1,?2,?3,?4,?5,?6,?7)");
2103 for(i=1; i<=n; i++){
2104 twoCoords(p1, p2, mxCoord, &x0, &x1);
2105 twoCoords(p1, p2, mxCoord, &y0, &y1);
2106 twoCoords(p1, p2, mxCoord, &z0, &z1);
2107 sqlite3_bind_int(g.pStmt, 1, i);
2108 sqlite3_bind_int(g.pStmt, 2, x0);
2109 sqlite3_bind_int(g.pStmt, 3, x1);
2110 sqlite3_bind_int(g.pStmt, 4, y0);
2111 sqlite3_bind_int(g.pStmt, 5, y1);
2112 sqlite3_bind_int(g.pStmt, 6, z0);
2113 sqlite3_bind_int(g.pStmt, 7, z1);
2114 speedtest1_run();
2115 }
2116 speedtest1_exec("COMMIT");
2117 speedtest1_end_test();
2118
2119 speedtest1_begin_test(101, "Copy from rtree to a regular table");
2120 speedtest1_exec("CREATE TABLE z1(id INTEGER PRIMARY KEY,x0,x1,y0,y1,z0,z1)");
2121 speedtest1_exec("INSERT INTO z1 SELECT * FROM rt1");
2122 speedtest1_end_test();
2123
2124 n = g.szTest*200;
2125 speedtest1_begin_test(110, "%d one-dimensional intersect slice queries", n);
2126 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x0>=?1 AND x1<=?2");
2127 iStep = mxCoord/n;
2128 for(i=0; i<n; i++){
2129 sqlite3_bind_int(g.pStmt, 1, i*iStep);
2130 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
2131 speedtest1_run();
2132 aCheck[i] = atoi(g.zResult);
2133 }
2134 speedtest1_end_test();
2135
2136 if( g.bVerify ){
2137 n = g.szTest*200;
2138 speedtest1_begin_test(111, "Verify result from 1-D intersect slice queries");
2139 speedtest1_prepare("SELECT count(*) FROM z1 WHERE x0>=?1 AND x1<=?2");
2140 iStep = mxCoord/n;
2141 for(i=0; i<n; i++){
2142 sqlite3_bind_int(g.pStmt, 1, i*iStep);
2143 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
2144 speedtest1_run();
2145 if( aCheck[i]!=atoi(g.zResult) ){
2146 fatal_error("Count disagree step %d: %d..%d. %d vs %d",
2147 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
2148 }
2149 }
2150 speedtest1_end_test();
2151 }
2152
2153 n = g.szTest*200;
2154 speedtest1_begin_test(120, "%d one-dimensional overlap slice queries", n);
2155 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE y1>=?1 AND y0<=?2");
2156 iStep = mxCoord/n;
2157 for(i=0; i<n; i++){
2158 sqlite3_bind_int(g.pStmt, 1, i*iStep);
2159 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
2160 speedtest1_run();
2161 aCheck[i] = atoi(g.zResult);
2162 }
2163 speedtest1_end_test();
2164
2165 if( g.bVerify ){
2166 n = g.szTest*200;
2167 speedtest1_begin_test(121, "Verify result from 1-D overlap slice queries");
2168 speedtest1_prepare("SELECT count(*) FROM z1 WHERE y1>=?1 AND y0<=?2");
2169 iStep = mxCoord/n;
2170 for(i=0; i<n; i++){
2171 sqlite3_bind_int(g.pStmt, 1, i*iStep);
2172 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
2173 speedtest1_run();
2174 if( aCheck[i]!=atoi(g.zResult) ){
2175 fatal_error("Count disagree step %d: %d..%d. %d vs %d",
2176 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
2177 }
2178 }
2179 speedtest1_end_test();
2180 }
2181
2182 n = g.szTest*200;
2183 speedtest1_begin_test(125, "%d custom geometry callback queries", n);
2184 sqlite3_rtree_geometry_callback(g.db, "xslice", xsliceGeometryCallback, 0);
2185 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE id MATCH xslice(?1,?2)");
2186 iStep = mxCoord/n;
2187 for(i=0; i<n; i++){
2188 sqlite3_bind_int(g.pStmt, 1, i*iStep);
2189 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
2190 speedtest1_run();
2191 if( aCheck[i]!=atoi(g.zResult) ){
2192 fatal_error("Count disagree step %d: %d..%d. %d vs %d",
2193 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
2194 }
2195 }
2196 speedtest1_end_test();
2197
2198 n = g.szTest*400;
2199 speedtest1_begin_test(130, "%d three-dimensional intersect box queries", n);
2200 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x1>=?1 AND x0<=?2"
2201 " AND y1>=?1 AND y0<=?2 AND z1>=?1 AND z0<=?2");
2202 iStep = mxCoord/n;
2203 for(i=0; i<n; i++){
2204 sqlite3_bind_int(g.pStmt, 1, i*iStep);
2205 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
2206 speedtest1_run();
2207 aCheck[i] = atoi(g.zResult);
2208 }
2209 speedtest1_end_test();
2210
2211 n = g.szTest*500;
2212 speedtest1_begin_test(140, "%d rowid queries", n);
2213 speedtest1_prepare("SELECT * FROM rt1 WHERE id=?1");
2214 for(i=1; i<=n; i++){
2215 sqlite3_bind_int(g.pStmt, 1, i);
2216 speedtest1_run();
2217 }
2218 speedtest1_end_test();
2219
2220 n = g.szTest*50;
2221 speedtest1_begin_test(150, "%d UPDATEs using rowid", n);
2222 speedtest1_prepare("UPDATE rt1 SET x0=x0+100, x1=x1+100 WHERE id=?1");
2223 for(i=1; i<=n; i++){
2224 sqlite3_bind_int(g.pStmt, 1, (i*251)%mxRowid + 1);
2225 speedtest1_run();
2226 }
2227 speedtest1_end_test();
2228
2229 n = g.szTest*5;
2230 speedtest1_begin_test(155, "%d UPDATEs using one-dimensional overlap", n);
2231 speedtest1_prepare("UPDATE rt1 SET x0=x0-100, x1=x1-100"
2232 " WHERE y1>=?1 AND y0<=?1+5");
2233 iStep = mxCoord/n;
2234 for(i=0; i<n; i++){
2235 sqlite3_bind_int(g.pStmt, 1, i*iStep);
2236 speedtest1_run();
2237 aCheck[i] = atoi(g.zResult);
2238 }
2239 speedtest1_end_test();
2240
2241 n = g.szTest*50;
2242 speedtest1_begin_test(160, "%d DELETEs using rowid", n);
2243 speedtest1_prepare("DELETE FROM rt1 WHERE id=?1");
2244 for(i=1; i<=n; i++){
2245 sqlite3_bind_int(g.pStmt, 1, (i*257)%mxRowid + 1);
2246 speedtest1_run();
2247 }
2248 speedtest1_end_test();
2249
2250
2251 n = g.szTest*5;
2252 speedtest1_begin_test(165, "%d DELETEs using one-dimensional overlap", n);
2253 speedtest1_prepare("DELETE FROM rt1 WHERE y1>=?1 AND y0<=?1+5");
2254 iStep = mxCoord/n;
2255 for(i=0; i<n; i++){
2256 sqlite3_bind_int(g.pStmt, 1, i*iStep);
2257 speedtest1_run();
2258 aCheck[i] = atoi(g.zResult);
2259 }
2260 speedtest1_end_test();
2261
2262 speedtest1_begin_test(170, "Restore deleted entries using INSERT OR IGNORE");
2263 speedtest1_exec("INSERT OR IGNORE INTO rt1 SELECT * FROM z1");
2264 speedtest1_end_test();
2265 }
2266 #endif /* SQLITE_ENABLE_RTREE */
2267
2268 /*
2269 ** A testset that does key/value storage on tables with many columns.
2270 ** This is the kind of workload generated by ORMs such as CoreData.
2271 */
2272 void testset_orm(void){
2273 unsigned i, j, n;
2274 unsigned nRow;
2275 unsigned x1, len;
2276 char zNum[2000]; /* A number name */
2277 static const char zType[] = /* Types for all non-PK columns, in order */
2278 "IBBIIITIVVITBTBFBFITTFBTBVBVIFTBBFITFFVBIFIVBVVVBTVTIBBFFIVIBTB"
2279 "TVTTFTVTVFFIITIFBITFTTFFFVBIIBTTITFTFFVVVFIIITVBBVFFTVVB";
2280
2281 nRow = n = g.szTest*250;
2282 speedtest1_begin_test(100, "Fill %d rows", n);
2283 speedtest1_exec(
2284 "BEGIN;"
2285 "CREATE TABLE ZLOOKSLIKECOREDATA ("
2286 " ZPK INTEGER PRIMARY KEY,"
2287 " ZTERMFITTINGHOUSINGCOMMAND INTEGER,"
2288 " ZBRIEFGOBYDODGERHEIGHT BLOB,"
2289 " ZCAPABLETRIPDOORALMOND BLOB,"
2290 " ZDEPOSITPAIRCOLLEGECOMET INTEGER,"
2291 " ZFRAMEENTERSIMPLEMOUTH INTEGER,"
2292 " ZHOPEFULGATEHOLECHALK INTEGER,"
2293 " ZSLEEPYUSERGRANDBOWL TIMESTAMP,"
2294 " ZDEWPEACHCAREERCELERY INTEGER,"
2295 " ZHANGERLITHIUMDINNERMEET VARCHAR,"
2296 " ZCLUBRELEASELIZARDADVICE VARCHAR,"
2297 " ZCHARGECLICKHUMANEHIRE INTEGER,"
2298 " ZFINGERDUEPIZZAOPTION TIMESTAMP,"
2299 " ZFLYINGDOCTORTABLEMELODY BLOB,"
2300 " ZLONGFINLEAVEIMAGEOIL TIMESTAMP,"
2301 " ZFAMILYVISUALOWNERMATTER BLOB,"
2302 " ZGOLDYOUNGINITIALNOSE FLOAT,"
2303 " ZCAUSESALAMITERMCYAN BLOB,"
2304 " ZSPREADMOTORBISCUITBACON FLOAT,"
2305 " ZGIFTICEFISHGLUEHAIR INTEGER,"
2306 " ZNOTICEPEARPOLICYJUICE TIMESTAMP,"
2307 " ZBANKBUFFALORECOVERORBIT TIMESTAMP,"
2308 " ZLONGDIETESSAYNATURE FLOAT,"
2309 " ZACTIONRANGEELEGANTNEUTRON BLOB,"
2310 " ZCADETBRIGHTPLANETBANK TIMESTAMP,"
2311 " ZAIRFORGIVEHEADFROG BLOB,"
2312 " ZSHARKJUSTFRUITMOVIE VARCHAR,"
2313 " ZFARMERMORNINGMIRRORCONCERN BLOB,"
2314 " ZWOODPOETRYCOBBLERBENCH VARCHAR,"
2315 " ZHAFNIUMSCRIPTSALADMOTOR INTEGER,"
2316 " ZPROBLEMCLUBPOPOVERJELLY FLOAT,"
2317 " ZEIGHTLEADERWORKERMOST TIMESTAMP,"
2318 " ZGLASSRESERVEBARIUMMEAL BLOB,"
2319 " ZCLAMBITARUGULAFAJITA BLOB,"
2320 " ZDECADEJOYOUSWAVEHABIT FLOAT,"
2321 " ZCOMPANYSUMMERFIBERELF INTEGER,"
2322 " ZTREATTESTQUILLCHARGE TIMESTAMP,"
2323 " ZBROWBALANCEKEYCHOWDER FLOAT,"
2324 " ZPEACHCOPPERDINNERLAKE FLOAT,"
2325 " ZDRYWALLBEYONDBROWNBOWL VARCHAR,"
2326 " ZBELLYCRASHITEMLACK BLOB,"
2327 " ZTENNISCYCLEBILLOFFICER INTEGER,"
2328 " ZMALLEQUIPTHANKSGLUE FLOAT,"
2329 " ZMISSREPLYHUMANLIVING INTEGER,"
2330 " ZKIWIVISUALPRIDEAPPLE VARCHAR,"
2331 " ZWISHHITSKINMOTOR BLOB,"
2332 " ZCALMRACCOONPROGRAMDEBIT VARCHAR,"
2333 " ZSHINYASSISTLIVINGCRAB VARCHAR,"
2334 " ZRESOLVEWRISTWRAPAPPLE VARCHAR,"
2335 " ZAPPEALSIMPLESECONDHOUSING BLOB,"
2336 " ZCORNERANCHORTAPEDIVER TIMESTAMP,"
2337 " ZMEMORYREQUESTSOURCEBIG VARCHAR,"
2338 " ZTRYFACTKEEPMILK TIMESTAMP,"
2339 " ZDIVERPAINTLEATHEREASY INTEGER,"
2340 " ZSORTMISTYQUOTECABBAGE BLOB,"
2341 " ZTUNEGASBUFFALOCAPITAL BLOB,"
2342 " ZFILLSTOPLAWJOYFUL FLOAT,"
2343 " ZSTEELCAREFULPLATENUMBER FLOAT,"
2344 " ZGIVEVIVIDDIVINEMEANING INTEGER,"
2345 " ZTREATPACKFUTURECONVERT VARCHAR,"
2346 " ZCALMLYGEMFINISHEFFECT INTEGER,"
2347 " ZCABBAGESOCKEASEMINUTE BLOB,"
2348 " ZPLANETFAMILYPUREMEMORY TIMESTAMP,"
2349 " ZMERRYCRACKTRAINLEADER BLOB,"
2350 " ZMINORWAYPAPERCLASSY TIMESTAMP,"
2351 " ZEAGLELINEMINEMAIL VARCHAR,"
2352 " ZRESORTYARDGREENLET TIMESTAMP,"
2353 " ZYARDOREGANOVIVIDJEWEL TIMESTAMP,"
2354 " ZPURECAKEVIVIDNEATLY FLOAT,"
2355 " ZASKCONTACTMONITORFUN TIMESTAMP,"
2356 " ZMOVEWHOGAMMAINCH VARCHAR,"
2357 " ZLETTUCEBIRDMEETDEBATE TIMESTAMP,"
2358 " ZGENENATURALHEARINGKITE VARCHAR,"
2359 " ZMUFFINDRYERDRAWFORTUNE FLOAT,"
2360 " ZGRAYSURVEYWIRELOVE FLOAT,"
2361 " ZPLIERSPRINTASKOREGANO INTEGER,"
2362 " ZTRAVELDRIVERCONTESTLILY INTEGER,"
2363 " ZHUMORSPICESANDKIDNEY TIMESTAMP,"
2364 " ZARSENICSAMPLEWAITMUON INTEGER,"
2365 " ZLACEADDRESSGROUNDCAREFUL FLOAT,"
2366 " ZBAMBOOMESSWASABIEVENING BLOB,"
2367 " ZONERELEASEAVERAGENURSE INTEGER,"
2368 " ZRADIANTWHENTRYCARD TIMESTAMP,"
2369 " ZREWARDINSIDEMANGOINTENSE FLOAT,"
2370 " ZNEATSTEWPARTIRON TIMESTAMP,"
2371 " ZOUTSIDEPEAHENCOUNTICE TIMESTAMP,"
2372 " ZCREAMEVENINGLIPBRANCH FLOAT,"
2373 " ZWHALEMATHAVOCADOCOPPER FLOAT,"
2374 " ZLIFEUSELEAFYBELL FLOAT,"
2375 " ZWEALTHLINENGLEEFULDAY VARCHAR,"
2376 " ZFACEINVITETALKGOLD BLOB,"
2377 " ZWESTAMOUNTAFFECTHEARING INTEGER,"
2378 " ZDELAYOUTCOMEHORNAGENCY INTEGER,"
2379 " ZBIGTHINKCONVERTECONOMY BLOB,"
2380 " ZBASEGOUDAREGULARFORGIVE TIMESTAMP,"
2381 " ZPATTERNCLORINEGRANDCOLBY TIMESTAMP,"
2382 " ZCYANBASEFEEDADROIT INTEGER,"
2383 " ZCARRYFLOORMINNOWDRAGON TIMESTAMP,"
2384 " ZIMAGEPENCILOTHERBOTTOM FLOAT,"
2385 " ZXENONFLIGHTPALEAPPLE TIMESTAMP,"
2386 " ZHERRINGJOKEFEATUREHOPEFUL FLOAT,"
2387 " ZCAPYEARLYRIVETBRUSH FLOAT,"
2388 " ZAGEREEDFROGBASKET VARCHAR,"
2389 " ZUSUALBODYHALIBUTDIAMOND VARCHAR,"
2390 " ZFOOTTAPWORDENTRY VARCHAR,"
2391 " ZDISHKEEPBLESTMONITOR FLOAT,"
2392 " ZBROADABLESOLIDCASUAL INTEGER,"
2393 " ZSQUAREGLEEFULCHILDLIGHT INTEGER,"
2394 " ZHOLIDAYHEADPONYDETAIL INTEGER,"
2395 " ZGENERALRESORTSKYOPEN TIMESTAMP,"
2396 " ZGLADSPRAYKIDNEYGUPPY VARCHAR,"
2397 " ZSWIMHEAVYMENTIONKIND BLOB,"
2398 " ZMESSYSULFURDREAMFESTIVE BLOB,"
2399 " ZSKYSKYCLASSICBRIEF VARCHAR,"
2400 " ZDILLASKHOKILEMON FLOAT,"
2401 " ZJUNIORSHOWPRESSNOVA FLOAT,"
2402 " ZSIZETOEAWARDFRESH TIMESTAMP,"
2403 " ZKEYFAILAPRICOTMETAL VARCHAR,"
2404 " ZHANDYREPAIRPROTONAIRPORT VARCHAR,"
2405 " ZPOSTPROTEINHANDLEACTOR BLOB"
2406 ");"
2407 );
2408 speedtest1_prepare(
2409 "INSERT INTO ZLOOKSLIKECOREDATA(ZPK,ZAIRFORGIVEHEADFROG,"
2410 "ZGIFTICEFISHGLUEHAIR,ZDELAYOUTCOMEHORNAGENCY,ZSLEEPYUSERGRANDBOWL,"
2411 "ZGLASSRESERVEBARIUMMEAL,ZBRIEFGOBYDODGERHEIGHT,"
2412 "ZBAMBOOMESSWASABIEVENING,ZFARMERMORNINGMIRRORCONCERN,"
2413 "ZTREATPACKFUTURECONVERT,ZCAUSESALAMITERMCYAN,ZCALMRACCOONPROGRAMDEBIT,"
2414 "ZHOLIDAYHEADPONYDETAIL,ZWOODPOETRYCOBBLERBENCH,ZHAFNIUMSCRIPTSALADMOTOR,"
2415 "ZUSUALBODYHALIBUTDIAMOND,ZOUTSIDEPEAHENCOUNTICE,ZDIVERPAINTLEATHEREASY,"
2416 "ZWESTAMOUNTAFFECTHEARING,ZSIZETOEAWARDFRESH,ZDEWPEACHCAREERCELERY,"
2417 "ZSTEELCAREFULPLATENUMBER,ZCYANBASEFEEDADROIT,ZCALMLYGEMFINISHEFFECT,"
2418 "ZHANDYREPAIRPROTONAIRPORT,ZGENENATURALHEARINGKITE,ZBROADABLESOLIDCASUAL,"
2419 "ZPOSTPROTEINHANDLEACTOR,ZLACEADDRESSGROUNDCAREFUL,ZIMAGEPENCILOTHERBOTTOM,"
2420 "ZPROBLEMCLUBPOPOVERJELLY,ZPATTERNCLORINEGRANDCOLBY,ZNEATSTEWPARTIRON,"
2421 "ZAPPEALSIMPLESECONDHOUSING,ZMOVEWHOGAMMAINCH,ZTENNISCYCLEBILLOFFICER,"
2422 "ZSHARKJUSTFRUITMOVIE,ZKEYFAILAPRICOTMETAL,ZCOMPANYSUMMERFIBERELF,"
2423 "ZTERMFITTINGHOUSINGCOMMAND,ZRESORTYARDGREENLET,ZCABBAGESOCKEASEMINUTE,"
2424 "ZSQUAREGLEEFULCHILDLIGHT,ZONERELEASEAVERAGENURSE,ZBIGTHINKCONVERTECONOMY,"
2425 "ZPLIERSPRINTASKOREGANO,ZDECADEJOYOUSWAVEHABIT,ZDRYWALLBEYONDBROWNBOWL,"
2426 "ZCLUBRELEASELIZARDADVICE,ZWHALEMATHAVOCADOCOPPER,ZBELLYCRASHITEMLACK,"
2427 "ZLETTUCEBIRDMEETDEBATE,ZCAPABLETRIPDOORALMOND,ZRADIANTWHENTRYCARD,"
2428 "ZCAPYEARLYRIVETBRUSH,ZAGEREEDFROGBASKET,ZSWIMHEAVYMENTIONKIND,"
2429 "ZTRAVELDRIVERCONTESTLILY,ZGLADSPRAYKIDNEYGUPPY,ZBANKBUFFALORECOVERORBIT,"
2430 "ZFINGERDUEPIZZAOPTION,ZCLAMBITARUGULAFAJITA,ZLONGFINLEAVEIMAGEOIL,"
2431 "ZLONGDIETESSAYNATURE,ZJUNIORSHOWPRESSNOVA,ZHOPEFULGATEHOLECHALK,"
2432 "ZDEPOSITPAIRCOLLEGECOMET,ZWEALTHLINENGLEEFULDAY,ZFILLSTOPLAWJOYFUL,"
2433 "ZTUNEGASBUFFALOCAPITAL,ZGRAYSURVEYWIRELOVE,ZCORNERANCHORTAPEDIVER,"
2434 "ZREWARDINSIDEMANGOINTENSE,ZCADETBRIGHTPLANETBANK,ZPLANETFAMILYPUREMEMORY,"
2435 "ZTREATTESTQUILLCHARGE,ZCREAMEVENINGLIPBRANCH,ZSKYSKYCLASSICBRIEF,"
2436 "ZARSENICSAMPLEWAITMUON,ZBROWBALANCEKEYCHOWDER,ZFLYINGDOCTORTABLEMELODY,"
2437 "ZHANGERLITHIUMDINNERMEET,ZNOTICEPEARPOLICYJUICE,ZSHINYASSISTLIVINGCRAB,"
2438 "ZLIFEUSELEAFYBELL,ZFACEINVITETALKGOLD,ZGENERALRESORTSKYOPEN,"
2439 "ZPURECAKEVIVIDNEATLY,ZKIWIVISUALPRIDEAPPLE,ZMESSYSULFURDREAMFESTIVE,"
2440 "ZCHARGECLICKHUMANEHIRE,ZHERRINGJOKEFEATUREHOPEFUL,ZYARDOREGANOVIVIDJEWEL,"
2441 "ZFOOTTAPWORDENTRY,ZWISHHITSKINMOTOR,ZBASEGOUDAREGULARFORGIVE,"
2442 "ZMUFFINDRYERDRAWFORTUNE,ZACTIONRANGEELEGANTNEUTRON,ZTRYFACTKEEPMILK,"
2443 "ZPEACHCOPPERDINNERLAKE,ZFRAMEENTERSIMPLEMOUTH,ZMERRYCRACKTRAINLEADER,"
2444 "ZMEMORYREQUESTSOURCEBIG,ZCARRYFLOORMINNOWDRAGON,ZMINORWAYPAPERCLASSY,"
2445 "ZDILLASKHOKILEMON,ZRESOLVEWRISTWRAPAPPLE,ZASKCONTACTMONITORFUN,"
2446 "ZGIVEVIVIDDIVINEMEANING,ZEIGHTLEADERWORKERMOST,ZMISSREPLYHUMANLIVING,"
2447 "ZXENONFLIGHTPALEAPPLE,ZSORTMISTYQUOTECABBAGE,ZEAGLELINEMINEMAIL,"
2448 "ZFAMILYVISUALOWNERMATTER,ZSPREADMOTORBISCUITBACON,ZDISHKEEPBLESTMONITOR,"
2449 "ZMALLEQUIPTHANKSGLUE,ZGOLDYOUNGINITIALNOSE,ZHUMORSPICESANDKIDNEY)"
2450 "VALUES(?1,?26,?20,?93,?8,?33,?3,?81,?28,?60,?18,?47,?109,?29,?30,?104,?86,"
2451 "?54,?92,?117,?9,?58,?97,?61,?119,?73,?107,?120,?80,?99,?31,?96,?85,?50,?71,"
2452 "?42,?27,?118,?36,?2,?67,?62,?108,?82,?94,?76,?35,?40,?11,?88,?41,?72,?4,"
2453 "?83,?102,?103,?112,?77,?111,?22,?13,?34,?15,?23,?116,?7,?5,?90,?57,?56,"
2454 "?75,?51,?84,?25,?63,?37,?87,?114,?79,?38,?14,?10,?21,?48,?89,?91,?110,"
2455 "?69,?45,?113,?12,?101,?68,?105,?46,?95,?74,?24,?53,?39,?6,?64,?52,?98,"
2456 "?65,?115,?49,?70,?59,?32,?44,?100,?55,?66,?16,?19,?106,?43,?17,?78);"
2457 );
2458 for(i=0; i<n; i++){
2459 x1 = speedtest1_random();
2460 speedtest1_numbername(x1%1000, zNum, sizeof(zNum));
2461 len = (int)strlen(zNum);
2462 sqlite3_bind_int(g.pStmt, 1, i^0xf);
2463 for(j=0; zType[j]; j++){
2464 switch( zType[j] ){
2465 case 'I':
2466 case 'T':
2467 sqlite3_bind_int64(g.pStmt, j+2, x1);
2468 break;
2469 case 'F':
2470 sqlite3_bind_double(g.pStmt, j+2, (double)x1);
2471 break;
2472 case 'V':
2473 case 'B':
2474 sqlite3_bind_text64(g.pStmt, j+2, zNum, len,
2475 SQLITE_STATIC, SQLITE_UTF8);
2476 break;
2477 }
2478 }
2479 speedtest1_run();
2480 }
2481 speedtest1_exec("COMMIT;");
2482 speedtest1_end_test();
2483
2484 n = g.szTest*250;
2485 speedtest1_begin_test(110, "Query %d rows by rowid", n);
2486 speedtest1_prepare(
2487 "SELECT ZCYANBASEFEEDADROIT,ZJUNIORSHOWPRESSNOVA,ZCAUSESALAMITERMCYAN,"
2488 "ZHOPEFULGATEHOLECHALK,ZHUMORSPICESANDKIDNEY,ZSWIMHEAVYMENTIONKIND,"
2489 "ZMOVEWHOGAMMAINCH,ZAPPEALSIMPLESECONDHOUSING,ZHAFNIUMSCRIPTSALADMOTOR,"
2490 "ZNEATSTEWPARTIRON,ZLONGFINLEAVEIMAGEOIL,ZDEWPEACHCAREERCELERY,"
2491 "ZXENONFLIGHTPALEAPPLE,ZCALMRACCOONPROGRAMDEBIT,ZUSUALBODYHALIBUTDIAMOND,"
2492 "ZTRYFACTKEEPMILK,ZWEALTHLINENGLEEFULDAY,ZLONGDIETESSAYNATURE,"
2493 "ZLIFEUSELEAFYBELL,ZTREATPACKFUTURECONVERT,ZMEMORYREQUESTSOURCEBIG,"
2494 "ZYARDOREGANOVIVIDJEWEL,ZDEPOSITPAIRCOLLEGECOMET,ZSLEEPYUSERGRANDBOWL,"
2495 "ZBRIEFGOBYDODGERHEIGHT,ZCLUBRELEASELIZARDADVICE,ZCAPABLETRIPDOORALMOND,"
2496 "ZDRYWALLBEYONDBROWNBOWL,ZASKCONTACTMONITORFUN,ZKIWIVISUALPRIDEAPPLE,"
2497 "ZNOTICEPEARPOLICYJUICE,ZPEACHCOPPERDINNERLAKE,ZSTEELCAREFULPLATENUMBER,"
2498 "ZGLADSPRAYKIDNEYGUPPY,ZCOMPANYSUMMERFIBERELF,ZTENNISCYCLEBILLOFFICER,"
2499 "ZIMAGEPENCILOTHERBOTTOM,ZWESTAMOUNTAFFECTHEARING,ZDIVERPAINTLEATHEREASY,"
2500 "ZSKYSKYCLASSICBRIEF,ZMESSYSULFURDREAMFESTIVE,ZMERRYCRACKTRAINLEADER,"
2501 "ZBROADABLESOLIDCASUAL,ZGLASSRESERVEBARIUMMEAL,ZTUNEGASBUFFALOCAPITAL,"
2502 "ZBANKBUFFALORECOVERORBIT,ZTREATTESTQUILLCHARGE,ZBAMBOOMESSWASABIEVENING,"
2503 "ZREWARDINSIDEMANGOINTENSE,ZEAGLELINEMINEMAIL,ZCALMLYGEMFINISHEFFECT,"
2504 "ZKEYFAILAPRICOTMETAL,ZFINGERDUEPIZZAOPTION,ZCADETBRIGHTPLANETBANK,"
2505 "ZGOLDYOUNGINITIALNOSE,ZMISSREPLYHUMANLIVING,ZEIGHTLEADERWORKERMOST,"
2506 "ZFRAMEENTERSIMPLEMOUTH,ZBIGTHINKCONVERTECONOMY,ZFACEINVITETALKGOLD,"
2507 "ZPOSTPROTEINHANDLEACTOR,ZHERRINGJOKEFEATUREHOPEFUL,ZCABBAGESOCKEASEMINUTE,"
2508 "ZMUFFINDRYERDRAWFORTUNE,ZPROBLEMCLUBPOPOVERJELLY,ZGIVEVIVIDDIVINEMEANING,"
2509 "ZGENENATURALHEARINGKITE,ZGENERALRESORTSKYOPEN,ZLETTUCEBIRDMEETDEBATE,"
2510 "ZBASEGOUDAREGULARFORGIVE,ZCHARGECLICKHUMANEHIRE,ZPLANETFAMILYPUREMEMORY,"
2511 "ZMINORWAYPAPERCLASSY,ZCAPYEARLYRIVETBRUSH,ZSIZETOEAWARDFRESH,"
2512 "ZARSENICSAMPLEWAITMUON,ZSQUAREGLEEFULCHILDLIGHT,ZSHINYASSISTLIVINGCRAB,"
2513 "ZCORNERANCHORTAPEDIVER,ZDECADEJOYOUSWAVEHABIT,ZTRAVELDRIVERCONTESTLILY,"
2514 "ZFLYINGDOCTORTABLEMELODY,ZSHARKJUSTFRUITMOVIE,ZFAMILYVISUALOWNERMATTER,"
2515 "ZFARMERMORNINGMIRRORCONCERN,ZGIFTICEFISHGLUEHAIR,ZOUTSIDEPEAHENCOUNTICE,"
2516 "ZSPREADMOTORBISCUITBACON,ZWISHHITSKINMOTOR,ZHOLIDAYHEADPONYDETAIL,"
2517 "ZWOODPOETRYCOBBLERBENCH,ZAIRFORGIVEHEADFROG,ZBROWBALANCEKEYCHOWDER,"
2518 "ZDISHKEEPBLESTMONITOR,ZCLAMBITARUGULAFAJITA,ZPLIERSPRINTASKOREGANO,"
2519 "ZRADIANTWHENTRYCARD,ZDELAYOUTCOMEHORNAGENCY,ZPURECAKEVIVIDNEATLY,"
2520 "ZPATTERNCLORINEGRANDCOLBY,ZHANDYREPAIRPROTONAIRPORT,ZAGEREEDFROGBASKET,"
2521 "ZSORTMISTYQUOTECABBAGE,ZFOOTTAPWORDENTRY,ZRESOLVEWRISTWRAPAPPLE,"
2522 "ZDILLASKHOKILEMON,ZFILLSTOPLAWJOYFUL,ZACTIONRANGEELEGANTNEUTRON,"
2523 "ZRESORTYARDGREENLET,ZCREAMEVENINGLIPBRANCH,ZWHALEMATHAVOCADOCOPPER,"
2524 "ZGRAYSURVEYWIRELOVE,ZBELLYCRASHITEMLACK,ZHANGERLITHIUMDINNERMEET,"
2525 "ZCARRYFLOORMINNOWDRAGON,ZMALLEQUIPTHANKSGLUE,ZTERMFITTINGHOUSINGCOMMAND,"
2526 "ZONERELEASEAVERAGENURSE,ZLACEADDRESSGROUNDCAREFUL"
2527 " FROM ZLOOKSLIKECOREDATA WHERE ZPK=?1;"
2528 );
2529 for(i=0; i<n; i++){
2530 x1 = speedtest1_random()%nRow;
2531 sqlite3_bind_int(g.pStmt, 1, x1);
2532 speedtest1_run();
2533 }
2534 speedtest1_end_test();
2535 }
2536
2537 /*
2538 */
2539 void testset_trigger(void){
2540 int jj, ii;
2541 char zNum[2000]; /* A number name */
2542
2543 const int NROW = 500*g.szTest;
2544 const int NROW2 = 100*g.szTest;
2545
2546 speedtest1_exec(
2547 "BEGIN;"
2548 "CREATE TABLE z1(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
2549 "CREATE TABLE z2(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
2550 "CREATE TABLE t3(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
2551 "CREATE VIEW v1 AS SELECT rowid, i, t FROM z1;"
2552 "CREATE VIEW v2 AS SELECT rowid, i, t FROM z2;"
2553 "CREATE VIEW v3 AS SELECT rowid, i, t FROM t3;"
2554 );
2555 for(jj=1; jj<=3; jj++){
2556 speedtest1_prepare("INSERT INTO t%d VALUES(NULL,?1,?2)", jj);
2557 for(ii=0; ii<NROW; ii++){
2558 int x1 = speedtest1_random() % NROW;
2559 speedtest1_numbername(x1, zNum, sizeof(zNum));
2560 sqlite3_bind_int(g.pStmt, 1, x1);
2561 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
2562 speedtest1_run();
2563 }
2564 }
2565 speedtest1_exec(
2566 "CREATE INDEX i1 ON z1(t);"
2567 "CREATE INDEX i2 ON z2(t);"
2568 "CREATE INDEX i3 ON t3(t);"
2569 "COMMIT;"
2570 );
2571
2572 speedtest1_begin_test(100, "speed4p-join1");
2573 speedtest1_prepare(
2574 "SELECT * FROM z1, z2, t3 WHERE z1.oid = z2.oid AND z2.oid = t3.oid"
2575 );
2576 speedtest1_run();
2577 speedtest1_end_test();
2578
2579 speedtest1_begin_test(110, "speed4p-join2");
2580 speedtest1_prepare(
2581 "SELECT * FROM z1, z2, t3 WHERE z1.t = z2.t AND z2.t = t3.t"
2582 );
2583 speedtest1_run();
2584 speedtest1_end_test();
2585
2586 speedtest1_begin_test(120, "speed4p-view1");
2587 for(jj=1; jj<=3; jj++){
2588 speedtest1_prepare("SELECT * FROM v%d WHERE rowid = ?", jj);
2589 for(ii=0; ii<NROW2; ii+=3){
2590 sqlite3_bind_int(g.pStmt, 1, ii*3);
2591 speedtest1_run();
2592 }
2593 }
2594 speedtest1_end_test();
2595
2596 speedtest1_begin_test(130, "speed4p-table1");
2597 for(jj=1; jj<=3; jj++){
2598 speedtest1_prepare("SELECT * FROM t%d WHERE rowid = ?", jj);
2599 for(ii=0; ii<NROW2; ii+=3){
2600 sqlite3_bind_int(g.pStmt, 1, ii*3);
2601 speedtest1_run();
2602 }
2603 }
2604 speedtest1_end_test();
2605
2606 speedtest1_begin_test(140, "speed4p-table1");
2607 for(jj=1; jj<=3; jj++){
2608 speedtest1_prepare("SELECT * FROM t%d WHERE rowid = ?", jj);
2609 for(ii=0; ii<NROW2; ii+=3){
2610 sqlite3_bind_int(g.pStmt, 1, ii*3);
2611 speedtest1_run();
2612 }
2613 }
2614 speedtest1_end_test();
2615
2616 speedtest1_begin_test(150, "speed4p-subselect1");
2617 speedtest1_prepare("SELECT "
2618 "(SELECT t FROM z1 WHERE rowid = ?1),"
2619 "(SELECT t FROM z2 WHERE rowid = ?1),"
2620 "(SELECT t FROM t3 WHERE rowid = ?1)"
2621 );
2622 for(jj=0; jj<NROW2; jj++){
2623 sqlite3_bind_int(g.pStmt, 1, jj*3);
2624 speedtest1_run();
2625 }
2626 speedtest1_end_test();
2627
2628 speedtest1_begin_test(160, "speed4p-rowid-update");
2629 speedtest1_exec("BEGIN");
2630 speedtest1_prepare("UPDATE z1 SET i=i+1 WHERE rowid=?1");
2631 for(jj=0; jj<NROW2; jj++){
2632 sqlite3_bind_int(g.pStmt, 1, jj);
2633 speedtest1_run();
2634 }
2635 speedtest1_exec("COMMIT");
2636 speedtest1_end_test();
2637
2638 speedtest1_exec("CREATE TABLE t5(t TEXT PRIMARY KEY, i INTEGER);");
2639 speedtest1_begin_test(170, "speed4p-insert-ignore");
2640 speedtest1_exec("INSERT OR IGNORE INTO t5 SELECT t, i FROM z1");
2641 speedtest1_end_test();
2642
2643 speedtest1_exec(
2644 "CREATE TABLE log(op TEXT, r INTEGER, i INTEGER, t TEXT);"
2645 "CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
2646 "CREATE TRIGGER t4_trigger1 AFTER INSERT ON t4 BEGIN"
2647 " INSERT INTO log VALUES('INSERT INTO t4', new.rowid, new.i, new.t);"
2648 "END;"
2649 "CREATE TRIGGER t4_trigger2 AFTER UPDATE ON t4 BEGIN"
2650 " INSERT INTO log VALUES('UPDATE OF t4', new.rowid, new.i, new.t);"
2651 "END;"
2652 "CREATE TRIGGER t4_trigger3 AFTER DELETE ON t4 BEGIN"
2653 " INSERT INTO log VALUES('DELETE OF t4', old.rowid, old.i, old.t);"
2654 "END;"
2655 "BEGIN;"
2656 );
2657
2658 speedtest1_begin_test(180, "speed4p-trigger1");
2659 speedtest1_prepare("INSERT INTO t4 VALUES(NULL, ?1, ?2)");
2660 for(jj=0; jj<NROW2; jj++){
2661 speedtest1_numbername(jj, zNum, sizeof(zNum));
2662 sqlite3_bind_int(g.pStmt, 1, jj);
2663 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
2664 speedtest1_run();
2665 }
2666 speedtest1_end_test();
2667
2668 /*
2669 ** Note: Of the queries, only half actually update a row. This property
2670 ** was copied over from speed4p.test, where it was probably introduced
2671 ** inadvertantly.
2672 */
2673 speedtest1_begin_test(190, "speed4p-trigger2");
2674 speedtest1_prepare("UPDATE t4 SET i = ?1, t = ?2 WHERE rowid = ?3");
2675 for(jj=1; jj<=NROW2*2; jj+=2){
2676 speedtest1_numbername(jj*2, zNum, sizeof(zNum));
2677 sqlite3_bind_int(g.pStmt, 1, jj*2);
2678 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
2679 sqlite3_bind_int(g.pStmt, 3, jj);
2680 speedtest1_run();
2681 }
2682 speedtest1_end_test();
2683
2684 /*
2685 ** Note: Same again.
2686 */
2687 speedtest1_begin_test(200, "speed4p-trigger3");
2688 speedtest1_prepare("DELETE FROM t4 WHERE rowid = ?1");
2689 for(jj=1; jj<=NROW2*2; jj+=2){
2690 sqlite3_bind_int(g.pStmt, 1, jj*2);
2691 speedtest1_run();
2692 }
2693 speedtest1_end_test();
2694 speedtest1_exec("COMMIT");
2695
2696 /*
2697 ** The following block contains the same tests as the above block that
2698 ** tests triggers, with one crucial difference: no triggers are defined.
2699 ** So the difference in speed between these tests and the preceding ones
2700 ** is the amount of time taken to compile and execute the trigger programs.
2701 */
2702 speedtest1_exec(
2703 "DROP TABLE t4;"
2704 "DROP TABLE log;"
2705 "VACUUM;"
2706 "CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
2707 "BEGIN;"
2708 );
2709 speedtest1_begin_test(210, "speed4p-notrigger1");
2710 speedtest1_prepare("INSERT INTO t4 VALUES(NULL, ?1, ?2)");
2711 for(jj=0; jj<NROW2; jj++){
2712 speedtest1_numbername(jj, zNum, sizeof(zNum));
2713 sqlite3_bind_int(g.pStmt, 1, jj);
2714 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
2715 speedtest1_run();
2716 }
2717 speedtest1_end_test();
2718 speedtest1_begin_test(210, "speed4p-notrigger2");
2719 speedtest1_prepare("UPDATE t4 SET i = ?1, t = ?2 WHERE rowid = ?3");
2720 for(jj=1; jj<=NROW2*2; jj+=2){
2721 speedtest1_numbername(jj*2, zNum, sizeof(zNum));
2722 sqlite3_bind_int(g.pStmt, 1, jj*2);
2723 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
2724 sqlite3_bind_int(g.pStmt, 3, jj);
2725 speedtest1_run();
2726 }
2727 speedtest1_end_test();
2728 speedtest1_begin_test(220, "speed4p-notrigger3");
2729 speedtest1_prepare("DELETE FROM t4 WHERE rowid = ?1");
2730 for(jj=1; jj<=NROW2*2; jj+=2){
2731 sqlite3_bind_int(g.pStmt, 1, jj*2);
2732 speedtest1_run();
2733 }
2734 speedtest1_end_test();
2735 speedtest1_exec("COMMIT");
2736 }
2737
2738 /*
2739 ** A testset used for debugging speedtest1 itself.
2740 */
2741 void testset_debug1(void){
2742 unsigned i, n;
2743 unsigned x1, x2;
2744 char zNum[2000]; /* A number name */
2745
2746 n = g.szTest;
2747 for(i=1; i<=n; i++){
2748 x1 = swizzle(i, n);
2749 x2 = swizzle(x1, n);
2750 speedtest1_numbername(x1, zNum, sizeof(zNum));
2751 printf("%5d %5d %5d %s\n", i, x1, x2, zNum);
2752 }
2753 }
2754
2755 /*
2756 ** Performance tests for JSON.
2757 */
2758 void testset_json(void){
2759 unsigned int r = 0x12345678;
2760 sqlite3_test_control(SQLITE_TESTCTRL_PRNG_SEED, r, g.db);
2761 speedtest1_begin_test(100, "table J1 is %d rows of JSONB",
2762 g.szTest*5);
2763 speedtest1_exec(
2764 "CREATE TABLE j1(x JSONB);\n"
2765 "WITH RECURSIVE\n"
2766 " jval(n,j) AS (\n"
2767 " VALUES(0,'{}'),(1,'[]'),(2,'true'),(3,'false'),(4,'null'),\n"
2768 " (5,'{x:1,y:2}'),(6,'0.0'),(7,'3.14159'),(8,'-99.9'),\n"
2769 " (9,'[1,2,\"\\n\\u2192\\\"\\u2190\",4]')\n"
2770 " ),\n"
2771 " c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<26*26-1),\n"
2772 " array1(y) AS MATERIALIZED (\n"
2773 " SELECT jsonb_group_array(\n"
2774 " jsonb_object('x',x,\n"
2775 " 'y',jsonb(coalesce(j,random()%%10000)),\n"
2776 " 'z',hex(randomblob(50)))\n"
2777 " )\n"
2778 " FROM c LEFT JOIN jval ON (x%%20)=n\n"
2779 " ),\n"
2780 " object1(z) AS MATERIALIZED (\n"
2781 " SELECT jsonb_group_object(char(0x61+x%%26,0x61+(x/26)%%26),\n"
2782 " jsonb( coalesce(j,random()%%10000)))\n"
2783 " FROM c LEFT JOIN jval ON (x%%20)=n\n"
2784 " ),\n"
2785 " c2(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c2 WHERE n<%d)\n"
2786 "INSERT INTO j1(x)\n"
2787 " SELECT jsonb_object('a',n,'b',n+10000,'c',jsonb(y),'d',jsonb(z),\n"
2788 " 'e',n+20000,'f',n+30000)\n"
2789 " FROM array1, object1, c2;",
2790 g.szTest*5
2791 );
2792 speedtest1_end_test();
2793
2794 speedtest1_begin_test(110, "table J2 is %d rows from J1 converted to text", g.szTest);
2795 speedtest1_exec(
2796 "CREATE TABLE j2(x JSON TEXT);\n"
2797 "INSERT INTO j2(x) SELECT json(x) FROM j1 LIMIT %d", g.szTest
2798 );
2799 speedtest1_end_test();
2800
2801 speedtest1_begin_test(120, "create indexes on JSON expressions on J1");
2802 speedtest1_exec(
2803 "BEGIN;\n"
2804 "CREATE INDEX j1x1 ON j1(x->>'a');\n"
2805 "CREATE INDEX j1x2 ON j1(x->>'b');\n"
2806 "CREATE INDEX j1x3 ON j1(x->>'f');\n"
2807 "COMMIT;\n"
2808 );
2809 speedtest1_end_test();
2810
2811 speedtest1_begin_test(130, "create indexes on JSON expressions on J2");
2812 speedtest1_exec(
2813 "BEGIN;\n"
2814 "CREATE INDEX j2x1 ON j2(x->>'a');\n"
2815 "CREATE INDEX j2x2 ON j2(x->>'b');\n"
2816 "CREATE INDEX j2x3 ON j2(x->>'f');\n"
2817 "COMMIT;\n"
2818 );
2819 speedtest1_end_test();
2820
2821 speedtest1_begin_test(140, "queries against J1");
2822 speedtest1_exec(
2823 "WITH c(n) AS (VALUES(0) UNION ALL SELECT n+1 FROM c WHERE n<7)\n"
2824 " SELECT sum(x->>format('$.c[%%d].x',n)) FROM c, j1;\n"
2825
2826 "WITH c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<5)\n"
2827 " SELECT sum(x->>format('$.\"c\"[#-%%d].y',n)) FROM c, j1;\n"
2828
2829 "SELECT sum(x->>'$.d.ez' + x->>'$.d.\"xz\"' + x->>'a' + x->>'$.c[10].y') FROM j1;\n"
2830
2831 "SELECT x->>'$.d.tz[2]', x->'$.d.tz' FROM j1;\n"
2832 );
2833 speedtest1_end_test();
2834
2835 speedtest1_begin_test(141, "queries involving json_type()");
2836 speedtest1_exec(
2837 "WITH c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<20)\n"
2838 " SELECT json_type(x,format('$.c[#-%%d].y',n)), count(*)\n"
2839 " FROM c, j1\n"
2840 " WHERE j1.rowid=1\n"
2841 " GROUP BY 1 ORDER BY 2;"
2842 );
2843 speedtest1_end_test();
2844
2845
2846 speedtest1_begin_test(150, "json_insert()/set()/remove() on every row of J1");
2847 speedtest1_exec(
2848 "BEGIN;\n"
2849 "UPDATE j1 SET x=jsonb_insert(x,'$.g',(x->>'f')+1,'$.h',3.14159,'$.i','hello',\n"
2850 " '$.j',json('{x:99}'),'$.k','{y:98}');\n"
2851 "UPDATE j1 SET x=jsonb_set(x,'$.e',(x->>'f')-1);\n"
2852 "UPDATE j1 SET x=jsonb_remove(x,'$.d');\n"
2853 "COMMIT;\n"
2854 );
2855 speedtest1_end_test();
2856
2857 speedtest1_begin_test(160, "json_insert()/set()/remove() on every row of J2");
2858 speedtest1_exec(
2859 "BEGIN;\n"
2860 "UPDATE j2 SET x=json_insert(x,'$.g',(x->>'f')+1);\n"
2861 "UPDATE j2 SET x=json_set(x,'$.e',(x->>'f')-1);\n"
2862 "UPDATE j2 SET x=json_remove(x,'$.d');\n"
2863 "COMMIT;\n"
2864 );
2865 speedtest1_end_test();
2866
2867 }
2868
2869 /*
2870 ** This testset focuses on the speed of parsing numeric literals (integers
2871 ** and real numbers). This was added to test the impact of allowing "_"
2872 ** characters to appear in numeric SQL literals to make them easier to read.
2873 ** For example, "SELECT 1_000_000;" instead of "SELECT 1000000;".
2874 */
2875 void testset_parsenumber(void){
2876 const char *zSql1 = "SELECT 1, 12, 123, 1234, 12345, 123456";
2877 const char *zSql2 = "SELECT 8227256643844975616, 7932208612563860480, "
2878 "2010730661871032832, 9138463067404021760, "
2879 "2557616153664746496, 2557616153664746496";
2880 const char *zSql3 = "SELECT 1.0, 1.2, 1.23, 123.4, 1.2345, 1.23456";
2881 const char *zSql4 = "SELECT 8.227256643844975616, 7.932208612563860480, "
2882 "2.010730661871032832, 9.138463067404021760, "
2883 "2.557616153664746496, 2.557616153664746496";
2884
2885 const int NROW = 100*g.szTest;
2886 int ii;
2887
2888 speedtest1_begin_test(100, "parsing %d small integers", NROW);
2889 for(ii=0; ii<NROW; ii++){
2890 sqlite3_exec(g.db, zSql1, 0, 0, 0);
2891 }
2892 speedtest1_end_test();
2893
2894 speedtest1_begin_test(110, "parsing %d large integers", NROW);
2895 for(ii=0; ii<NROW; ii++){
2896 sqlite3_exec(g.db, zSql2, 0, 0, 0);
2897 }
2898 speedtest1_end_test();
2899
2900 speedtest1_begin_test(200, "parsing %d small reals", NROW);
2901 for(ii=0; ii<NROW; ii++){
2902 sqlite3_exec(g.db, zSql3, 0, 0, 0);
2903 }
2904 speedtest1_end_test();
2905
2906 speedtest1_begin_test(210, "parsing %d large reals", NROW);
2907 for(ii=0; ii<NROW; ii++){
2908 sqlite3_exec(g.db, zSql4, 0, 0, 0);
2909 }
2910 speedtest1_end_test();
2911 }
2912
2913 #ifdef __linux__
2914 #include <sys/types.h>
2915 #include <unistd.h>
2916
2917 /*
2918 ** Attempt to display I/O stats on Linux using /proc/PID/io
2919 */
2920 static void displayLinuxIoStats(FILE *out){
2921 FILE *in;
2922 char z[200];
2923 sqlite3_snprintf(sizeof(z), z, "/proc/%d/io", getpid());
2924 in = fopen(z, "rb");
2925 if( in==0 ) return;
2926 while( fgets(z, sizeof(z), in)!=0 ){
2927 static const struct {
2928 const char *zPattern;
2929 const char *zDesc;
2930 } aTrans[] = {
2931 { "rchar: ", "Bytes received by read():" },
2932 { "wchar: ", "Bytes sent to write():" },
2933 { "syscr: ", "Read() system calls:" },
2934 { "syscw: ", "Write() system calls:" },
2935 { "read_bytes: ", "Bytes rcvd from storage:" },
2936 { "write_bytes: ", "Bytes sent to storage:" },
2937 { "cancelled_write_bytes: ", "Cancelled write bytes:" },
2938 };
2939 int i;
2940 for(i=0; i<sizeof(aTrans)/sizeof(aTrans[0]); i++){
2941 int n = (int)strlen(aTrans[i].zPattern);
2942 if( strncmp(aTrans[i].zPattern, z, n)==0 ){
2943 fprintf(out, "-- %-28s %s", aTrans[i].zDesc, &z[n]);
2944 break;
2945 }
2946 }
2947 }
2948 fclose(in);
2949 }
2950 #endif
2951
2952 #if SQLITE_VERSION_NUMBER<3006018
2953 # define sqlite3_sourceid(X) "(before 3.6.18)"
2954 #endif
2955
2956 #if SQLITE_CKSUMVFS_STATIC
2957 int sqlite3_register_cksumvfs(const char*);
2958 #endif
2959
2960 static int xCompileOptions(void *pCtx, int nVal, char **azVal, char **azCol){
2961 printf("-- Compile option: %s\n", azVal[0]);
2962 return SQLITE_OK;
2963 }
2964 int main(int argc, char **argv){
2965 int doAutovac = 0; /* True for --autovacuum */
2966 int cacheSize = 0; /* Desired cache size. 0 means default */
2967 int doExclusive = 0; /* True for --exclusive */
2968 int doFullFSync = 0; /* True for --fullfsync */
2969 int nHeap = 0, mnHeap = 0; /* Heap size from --heap */
2970 int doIncrvac = 0; /* True for --incrvacuum */
2971 const char *zJMode = 0; /* Journal mode */
2972 const char *zKey = 0; /* Encryption key */
2973 int nHardHeapLmt = 0; /* The hard heap limit */
2974 int nSoftHeapLmt = 0; /* The soft heap limit */
2975 int nLook = -1, szLook = 0; /* --lookaside configuration */
2976 int noSync = 0; /* True for --nosync */
2977 int pageSize = 0; /* Desired page size. 0 means default */
2978 int nPCache = 0, szPCache = 0;/* --pcache configuration */
2979 int doPCache = 0; /* True if --pcache is seen */
2980 int showStats = 0; /* True for --stats */
2981 int nThread = 0; /* --threads value */
2982 int mmapSize = 0; /* How big of a memory map to use */
2983 int memDb = 0; /* --memdb. Use an in-memory database */
2984 int openFlags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE
2985 ; /* SQLITE_OPEN_xxx flags. */
2986 char *zTSet = "mix1"; /* Which --testset torun */
2987 int doTrace = 0; /* True for --trace */
2988 const char *zEncoding = 0; /* --utf16be or --utf16le */
2989
2990 void *pHeap = 0; /* Allocated heap space */
2991 void *pLook = 0; /* Allocated lookaside space */
2992 void *pPCache = 0; /* Allocated storage for pcache */
2993 int iCur, iHi; /* Stats values, current and "highwater" */
2994 int i; /* Loop counter */
2995 int rc; /* API return code */
2996
2997 /* "mix1" is a macro testset: */
2998 static char zMix1Tests[] =
2999 "main,orm/25,cte/20,json,fp/3,parsenumber/25,rtree/10,star"
3000 #if !defined(SQLITE_SPEEDTEST1_WASM)
3001 ",app"
3002 /* This test misbehaves in WASM builds: sqlite3_open_v2() is
3003 failing to find the db file for reasons not yet understood. */
3004 #endif
3005 ;
3006
3007 #ifdef SQLITE_SPEEDTEST1_WASM
3008 /* Resetting all state is important for the WASM build, which may
3009 ** call main() multiple times. */
3010 memset(&g, 0, sizeof(g));
3011 iTestNumber = 0;
3012 #endif
3013 #ifdef SQLITE_CKSUMVFS_STATIC
3014 sqlite3_register_cksumvfs(0);
3015 #endif
3016 /*
3017 ** Confirms that argc has at least N arguments following argv[i]. */
3018 #define ARGC_VALUE_CHECK(N) \
3019 if( i>=argc-(N) ) fatal_error("missing argument on %s\n", argv[i])
3020 /* Display the version of SQLite being tested */
3021 printf("-- Speedtest1 for SQLite %s %.48s\n",
3022 sqlite3_libversion(), sqlite3_sourceid());
3023
3024 /* Process command-line arguments */
3025 g.zDbName = 0;
3026 g.zVfs = 0;
3027 g.zWR = "";
3028 g.zNN = "";
3029 g.zPK = "UNIQUE";
3030 g.szTest = 100;
3031 g.szBase = 100;
3032 g.nRepeat = 1;
3033 for(i=1; i<argc; i++){
3034 const char *z = argv[i];
3035 if( z[0]=='-' ){
3036 do{ z++; }while( z[0]=='-' );
3037 if( strcmp(z,"autovacuum")==0 ){
3038 doAutovac = 1;
3039 }else if( strcmp(z,"big-transactions")==0 ){
3040 g.doBigTransactions = 1;
3041 }else if( strcmp(z,"cachesize")==0 ){
3042 ARGC_VALUE_CHECK(1);
3043 cacheSize = integerValue(argv[++i]);
3044 }else if( strcmp(z,"exclusive")==0 ){
3045 doExclusive = 1;
3046 }else if( strcmp(z,"fullfsync")==0 ){
3047 doFullFSync = 1;
3048 }else if( strcmp(z,"checkpoint")==0 ){
3049 g.doCheckpoint = 1;
3050 }else if( strcmp(z,"explain")==0 ){
3051 g.bSqlOnly = 1;
3052 g.bExplain = 1;
3053 }else if( strcmp(z,"hard-heap-limit")==0 ){
3054 ARGC_VALUE_CHECK(1);
3055 nHardHeapLmt = integerValue(argv[i+1]);
3056 i += 1;
3057 }else if( strcmp(z,"heap")==0 ){
3058 ARGC_VALUE_CHECK(2);
3059 nHeap = integerValue(argv[i+1]);
3060 mnHeap = integerValue(argv[i+2]);
3061 i += 2;
3062 }else if( strcmp(z,"incrvacuum")==0 ){
3063 doIncrvac = 1;
3064 }else if( strcmp(z,"journal")==0 ){
3065 ARGC_VALUE_CHECK(1);
3066 zJMode = argv[++i];
3067 }else if( strcmp(z,"key")==0 ){
3068 ARGC_VALUE_CHECK(1);
3069 zKey = argv[++i];
3070 }else if( strcmp(z,"lookaside")==0 ){
3071 ARGC_VALUE_CHECK(2);
3072 nLook = integerValue(argv[i+1]);
3073 szLook = integerValue(argv[i+2]);
3074 i += 2;
3075 }else if( strcmp(z,"memdb")==0 ){
3076 memDb = 1;
3077 #if SQLITE_VERSION_NUMBER>=3006000
3078 }else if( strcmp(z,"multithread")==0 ){
3079 sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
3080 }else if( strcmp(z,"nomemstat")==0 ){
3081 sqlite3_config(SQLITE_CONFIG_MEMSTATUS, 0);
3082 #endif
3083 #if SQLITE_VERSION_NUMBER>=3007017
3084 }else if( strcmp(z, "mmap")==0 ){
3085 ARGC_VALUE_CHECK(1);
3086 mmapSize = integerValue(argv[++i]);
3087 #endif
3088 }else if( strcmp(z,"nomutex")==0 ){
3089 openFlags |= SQLITE_OPEN_NOMUTEX;
3090 }else if( strcmp(z,"nosync")==0 ){
3091 noSync = 1;
3092 }else if( strcmp(z,"notnull")==0 ){
3093 g.zNN = "NOT NULL";
3094 }else if( strcmp(z,"output")==0 ){
3095 #ifdef SPEEDTEST_OMIT_HASH
3096 fatal_error("The --output option is not supported with"
3097 " -DSPEEDTEST_OMIT_HASH\n");
3098 #else
3099 ARGC_VALUE_CHECK(1);
3100 i++;
3101 if( strcmp(argv[i],"-")==0 ){
3102 g.hashFile = stdout;
3103 }else{
3104 g.hashFile = fopen(argv[i], "wb");
3105 if( g.hashFile==0 ){
3106 fatal_error("cannot open \"%s\" for writing\n", argv[i]);
3107 }
3108 }
3109 #endif
3110 }else if( strcmp(z,"pagesize")==0 ){
3111 ARGC_VALUE_CHECK(1);
3112 pageSize = integerValue(argv[++i]);
3113 }else if( strcmp(z,"pcache")==0 ){
3114 ARGC_VALUE_CHECK(2);
3115 nPCache = integerValue(argv[i+1]);
3116 szPCache = integerValue(argv[i+2]);
3117 doPCache = 1;
3118 i += 2;
3119 }else if( strcmp(z,"primarykey")==0 ){
3120 g.zPK = "PRIMARY KEY";
3121 }else if( strcmp(z,"repeat")==0 ){
3122 ARGC_VALUE_CHECK(1);
3123 g.nRepeat = integerValue(argv[++i]);
3124 }else if( strcmp(z,"reprepare")==0 ){
3125 g.bReprepare = 1;
3126 #if SQLITE_VERSION_NUMBER>=3006000
3127 }else if( strcmp(z,"serialized")==0 ){
3128 sqlite3_config(SQLITE_CONFIG_SERIALIZED);
3129 }else if( strcmp(z,"singlethread")==0 ){
3130 sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);
3131 #endif
3132 }else if( strcmp(z,"script")==0 ){
3133 ARGC_VALUE_CHECK(1);
3134 if( g.pScript ) fclose(g.pScript);
3135 g.pScript = fopen(argv[++i], "wb");
3136 if( g.pScript==0 ){
3137 fatal_error("unable to open output file \"%s\"\n", argv[i]);
3138 }
3139 }else if( strcmp(z,"sqlonly")==0 ){
3140 g.bSqlOnly = 1;
3141 }else if( strcmp(z,"shrink-memory")==0 ){
3142 g.bMemShrink = 1;
3143 }else if( strcmp(z,"size")==0 ){
3144 ARGC_VALUE_CHECK(1);
3145 g.szTest = g.szBase = integerValue(argv[++i]);
3146 }else if( strcmp(z,"soft-heap-limit")==0 ){
3147 ARGC_VALUE_CHECK(1);
3148 nSoftHeapLmt = integerValue(argv[i+1]);
3149 i += 1;
3150 }else if( strcmp(z,"stats")==0 ){
3151 showStats = 1;
3152 }else if( strcmp(z,"temp")==0 ){
3153 ARGC_VALUE_CHECK(1);
3154 i++;
3155 if( argv[i][0]<'0' || argv[i][0]>'9' || argv[i][1]!=0 ){
3156 fatal_error("argument to --temp should be integer between 0 and 9");
3157 }
3158 g.eTemp = argv[i][0] - '0';
3159 }else if( strcmp(z,"testset")==0 ){
3160 ARGC_VALUE_CHECK(1);
3161 zTSet = argv[++i];
3162 }else if( strcmp(z,"trace")==0 ){
3163 doTrace = 1;
3164 }else if( strcmp(z,"threads")==0 ){
3165 ARGC_VALUE_CHECK(1);
3166 nThread = integerValue(argv[++i]);
3167 }else if( strcmp(z,"utf16le")==0 ){
3168 zEncoding = "utf16le";
3169 }else if( strcmp(z,"utf16be")==0 ){
3170 zEncoding = "utf16be";
3171 }else if( strcmp(z,"verify")==0 ){
3172 g.bVerify = 1;
3173 #ifndef SPEEDTEST_OMIT_HASH
3174 HashInit();
3175 #endif
3176 }else if( strcmp(z,"vfs")==0 ){
3177 ARGC_VALUE_CHECK(1);
3178 g.zVfs = argv[++i];
3179 }else if( strcmp(z,"reserve")==0 ){
3180 ARGC_VALUE_CHECK(1);
3181 g.nReserve = atoi(argv[++i]);
3182 }else if( strcmp(z,"stmtscanstatus")==0 ){
3183 g.stmtScanStatus = 1;
3184 }else if( strcmp(z,"without-rowid")==0 ){
3185 if( strstr(g.zWR,"WITHOUT")!=0 ){
3186 /* no-op */
3187 }else if( strstr(g.zWR,"STRICT")!=0 ){
3188 g.zWR = "WITHOUT ROWID,STRICT";
3189 }else{
3190 g.zWR = "WITHOUT ROWID";
3191 }
3192 g.zPK = "PRIMARY KEY";
3193 }else if( strcmp(z,"strict")==0 ){
3194 if( strstr(g.zWR,"STRICT")!=0 ){
3195 /* no-op */
3196 }else if( strstr(g.zWR,"WITHOUT")!=0 ){
3197 g.zWR = "WITHOUT ROWID,STRICT";
3198 }else{
3199 g.zWR = "STRICT";
3200 }
3201 }else if( strcmp(z, "help")==0 || strcmp(z,"?")==0 ){
3202 printf(zHelp, argv[0]);
3203 exit(0);
3204 }else{
3205 fatal_error("unknown option: %s\nUse \"%s -?\" for help\n",
3206 argv[i], argv[0]);
3207 }
3208 }else if( g.zDbName==0 ){
3209 g.zDbName = argv[i];
3210 }else{
3211 fatal_error("surplus argument: %s\nUse \"%s -?\" for help\n",
3212 argv[i], argv[0]);
3213 }
3214 }
3215 #undef ARGC_VALUE_CHECK
3216 #if SQLITE_VERSION_NUMBER>=3006001
3217 if( nHeap>0 ){
3218 pHeap = malloc( nHeap );
3219 if( pHeap==0 ) fatal_error("cannot allocate %d-byte heap\n", nHeap);
3220 rc = sqlite3_config(SQLITE_CONFIG_HEAP, pHeap, nHeap, mnHeap);
3221 if( rc ) fatal_error("heap configuration failed: %d\n", rc);
3222 }
3223 if( doPCache ){
3224 if( nPCache>0 && szPCache>0 ){
3225 pPCache = malloc( nPCache*(sqlite3_int64)szPCache );
3226 if( pPCache==0 ) fatal_error("cannot allocate %lld-byte pcache\n",
3227 nPCache*(sqlite3_int64)szPCache);
3228 }
3229 rc = sqlite3_config(SQLITE_CONFIG_PAGECACHE, pPCache, szPCache, nPCache);
3230 if( rc ) fatal_error("pcache configuration failed: %d\n", rc);
3231 }
3232 if( nLook>=0 ){
3233 sqlite3_config(SQLITE_CONFIG_LOOKASIDE, 0, 0);
3234 }
3235 #endif
3236 sqlite3_initialize();
3237
3238 if( g.zDbName!=0 ){
3239 sqlite3_vfs *pVfs = sqlite3_vfs_find(g.zVfs);
3240 /* For some VFSes, e.g. opfs, unlink() is not sufficient. Use the
3241 ** selected (or default) VFS's xDelete method to delete the
3242 ** database. This is specifically important for the "opfs" VFS
3243 ** when running from a WASM build of speedtest1, so that the db
3244 ** can be cleaned up properly. For historical compatibility, we'll
3245 ** also simply unlink(). */
3246 if( pVfs!=0 ){
3247 pVfs->xDelete(pVfs, g.zDbName, 1);
3248 }
3249 unlink(g.zDbName);
3250 }
3251
3252 /* Open the database and the input file */
3253 if( sqlite3_open_v2(memDb ? ":memory:" : g.zDbName, &g.db,
3254 openFlags, g.zVfs) ){
3255 fatal_error("Cannot open database file: %s\n", g.zDbName);
3256 }
3257 #if SQLITE_VERSION_NUMBER>=3006001
3258 if( nLook>0 && szLook>0 ){
3259 pLook = malloc( nLook*szLook );
3260 rc = sqlite3_db_config(g.db, SQLITE_DBCONFIG_LOOKASIDE,pLook,szLook,nLook);
3261 if( rc ) fatal_error("lookaside configuration failed: %d\n", rc);
3262 }
3263 #endif
3264 if( g.nReserve>0 ){
3265 sqlite3_file_control(g.db, 0, SQLITE_FCNTL_RESERVE_BYTES, &g.nReserve);
3266 }
3267 if( g.stmtScanStatus ){
3268 sqlite3_db_config(g.db, SQLITE_DBCONFIG_STMT_SCANSTATUS, 1, 0);
3269 }
3270
3271 /* Set database connection options */
3272 sqlite3_create_function(g.db, "random", 0, SQLITE_UTF8, 0, randomFunc, 0, 0);
3273 #ifndef SQLITE_OMIT_DEPRECATED
3274 if( doTrace ) sqlite3_trace(g.db, traceCallback, 0);
3275 #endif
3276 if( memDb>0 ){
3277 speedtest1_exec("PRAGMA temp_store=memory");
3278 }
3279 if( mmapSize>0 ){
3280 speedtest1_exec("PRAGMA mmap_size=%d", mmapSize);
3281 }
3282 speedtest1_exec("PRAGMA threads=%d", nThread);
3283 if( zKey ){
3284 speedtest1_exec("PRAGMA key('%s')", zKey);
3285 }
3286 if( zEncoding ){
3287 speedtest1_exec("PRAGMA encoding=%s", zEncoding);
3288 }
3289 if( doAutovac ){
3290 speedtest1_exec("PRAGMA auto_vacuum=FULL");
3291 }else if( doIncrvac ){
3292 speedtest1_exec("PRAGMA auto_vacuum=INCREMENTAL");
3293 }
3294 if( pageSize ){
3295 speedtest1_exec("PRAGMA page_size=%d", pageSize);
3296 }
3297 if( cacheSize ){
3298 speedtest1_exec("PRAGMA cache_size=%d", cacheSize);
3299 }
3300 if( noSync ){
3301 speedtest1_exec("PRAGMA synchronous=OFF");
3302 }else if( doFullFSync ){
3303 speedtest1_exec("PRAGMA fullfsync=ON");
3304 }
3305 if( doExclusive ){
3306 speedtest1_exec("PRAGMA locking_mode=EXCLUSIVE");
3307 }
3308 if( zJMode ){
3309 speedtest1_exec("PRAGMA journal_mode=%s", zJMode);
3310 }
3311 if( nHardHeapLmt>0 ){
3312 speedtest1_exec("PRAGMA hard_heap_limit=%d", nHardHeapLmt);
3313 }
3314 if( nSoftHeapLmt>0 ){
3315 speedtest1_exec("PRAGMA soft_heap_limit=%d", nSoftHeapLmt);
3316 }
3317 if( zJMode ){
3318 speedtest1_exec("PRAGMA journal_mode=%s", zJMode);
3319 }
3320
3321 if( g.bExplain ) printf(".explain\n.echo on\n");
3322 if( strcmp(zTSet,"mix1")==0 ) zTSet = zMix1Tests;
3323 do{
3324 char *zThisTest = zTSet;
3325 char *zSep;
3326 char *zComma = strchr(zThisTest,',');
3327 if( zComma ){
3328 *zComma = 0;
3329 zTSet = zComma+1;
3330 }else{
3331 zTSet = "";
3332 }
3333 zSep = strchr(zThisTest, '/');
3334 if( zSep ){
3335 int kk;
3336 for(kk=1; zSep[kk] && ISDIGIT(zSep[kk]); kk++){}
3337 if( kk==1 || zSep[kk]!=0 ){
3338 fatal_error("bad modifier on testset name: \"%s\"", zThisTest);
3339 }
3340 g.szTest = g.szBase*integerValue(zSep+1)/100;
3341 if( g.szTest<=0 ) g.szTest = 1;
3342 zSep[0] = 0;
3343 }else{
3344 g.szTest = g.szBase;
3345 }
3346 if( g.iTotal>0 || zComma==0 ){
3347 printf(" Begin testset \"%s\"\n", zThisTest);
3348 }
3349 if( strcmp(zThisTest,"main")==0 ){
3350 testset_main();
3351 }else if( strcmp(zThisTest,"debug1")==0 ){
3352 testset_debug1();
3353 }else if( strcmp(zThisTest,"orm")==0 ){
3354 testset_orm();
3355 }else if( strcmp(zThisTest,"cte")==0 ){
3356 testset_cte();
3357 }else if( strcmp(zThisTest,"star")==0 ){
3358 testset_star();
3359 }else if( strcmp(zThisTest,"app")==0 ){
3360 testset_app();
3361 }else if( strcmp(zThisTest,"fp")==0 ){
3362 testset_fp();
3363 }else if( strcmp(zThisTest,"json")==0 ){
3364 testset_json();
3365 }else if( strcmp(zThisTest,"trigger")==0 ){
3366 testset_trigger();
3367 }else if( strcmp(zThisTest,"parsenumber")==0 ){
3368 testset_parsenumber();
3369 }else if( strcmp(zThisTest,"rtree")==0 ){
3370 #ifdef SQLITE_ENABLE_RTREE
3371 testset_rtree(6, 147);
3372 #else
3373 fatal_error("compile with -DSQLITE_ENABLE_RTREE to enable "
3374 "the R-Tree tests\n");
3375 #endif
3376 }else{
3377 fatal_error("unknown testset: \"%s\"\n"
3378 "Choices: cte debug1 fp main orm rtree trigger\n",
3379 zThisTest);
3380 }
3381 if( zTSet[0] ){
3382 char *zSql, *zObj;
3383 speedtest1_begin_test(999, "Reset the database");
3384 while( 1 ){
3385 zObj = speedtest1_once(
3386 "SELECT name FROM main.sqlite_master"
3387 " WHERE sql LIKE 'CREATE %%TABLE%%'");
3388 if( zObj==0 ) break;
3389 zSql = sqlite3_mprintf("DROP TABLE main.\"%w\"", zObj);
3390 speedtest1_exec(zSql);
3391 sqlite3_free(zSql);
3392 sqlite3_free(zObj);
3393 }
3394 while( 1 ){
3395 zObj = speedtest1_once(
3396 "SELECT name FROM temp.sqlite_master"
3397 " WHERE sql LIKE 'CREATE %%TABLE%%'");
3398 if( zObj==0 ) break;
3399 zSql = sqlite3_mprintf("DROP TABLE main.\"%w\"", zObj);
3400 speedtest1_exec(zSql);
3401 sqlite3_free(zSql);
3402 sqlite3_free(zObj);
3403 }
3404 speedtest1_end_test();
3405 }
3406 }while( zTSet[0] );
3407 speedtest1_final();
3408
3409 if( showStats ){
3410 sqlite3_exec(g.db, "PRAGMA compile_options", xCompileOptions, 0, 0);
3411 }
3412
3413 /* Database connection statistics printed after both prepared statements
3414 ** have been finalized */
3415 #if SQLITE_VERSION_NUMBER>=3007009
3416 if( showStats ){
3417 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_USED, &iCur, &iHi, 0);
3418 printf("-- Lookaside Slots Used: %d (max %d)\n", iCur,iHi);
3419 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_HIT, &iCur, &iHi, 0);
3420 printf("-- Successful lookasides: %d\n", iHi);
3421 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE, &iCur,&iHi,0);
3422 printf("-- Lookaside size faults: %d\n", iHi);
3423 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL, &iCur,&iHi,0);
3424 printf("-- Lookaside OOM faults: %d\n", iHi);
3425 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_USED, &iCur, &iHi, 0);
3426 printf("-- Pager Heap Usage: %d bytes\n", iCur);
3427 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_HIT, &iCur, &iHi, 1);
3428 printf("-- Page cache hits: %d\n", iCur);
3429 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_MISS, &iCur, &iHi, 1);
3430 printf("-- Page cache misses: %d\n", iCur);
3431 #if SQLITE_VERSION_NUMBER>=3007012
3432 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_WRITE, &iCur, &iHi, 1);
3433 printf("-- Page cache writes: %d\n", iCur);
3434 #endif
3435 sqlite3_db_status(g.db, SQLITE_DBSTATUS_SCHEMA_USED, &iCur, &iHi, 0);
3436 printf("-- Schema Heap Usage: %d bytes\n", iCur);
3437 sqlite3_db_status(g.db, SQLITE_DBSTATUS_STMT_USED, &iCur, &iHi, 0);
3438 printf("-- Statement Heap Usage: %d bytes\n", iCur);
3439 }
3440 #endif
3441
3442 sqlite3_close(g.db);
3443
3444 #if SQLITE_VERSION_NUMBER>=3006001
3445 /* Global memory usage statistics printed after the database connection
3446 ** has closed. Memory usage should be zero at this point. */
3447 if( showStats ){
3448 sqlite3_status(SQLITE_STATUS_MEMORY_USED, &iCur, &iHi, 0);
3449 printf("-- Memory Used (bytes): %d (max %d)\n", iCur,iHi);
3450 #if SQLITE_VERSION_NUMBER>=3007000
3451 sqlite3_status(SQLITE_STATUS_MALLOC_COUNT, &iCur, &iHi, 0);
3452 printf("-- Outstanding Allocations: %d (max %d)\n", iCur,iHi);
3453 #endif
3454 sqlite3_status(SQLITE_STATUS_PAGECACHE_OVERFLOW, &iCur, &iHi, 0);
3455 printf("-- Pcache Overflow Bytes: %d (max %d)\n", iCur,iHi);
3456 sqlite3_status(SQLITE_STATUS_MALLOC_SIZE, &iCur, &iHi, 0);
3457 printf("-- Largest Allocation: %d bytes\n",iHi);
3458 sqlite3_status(SQLITE_STATUS_PAGECACHE_SIZE, &iCur, &iHi, 0);
3459 printf("-- Largest Pcache Allocation: %d bytes\n",iHi);
3460 }
3461 #endif
3462
3463 #ifdef __linux__
3464 if( showStats ){
3465 displayLinuxIoStats(stdout);
3466 }
3467 #endif
3468 if( g.pScript ){
3469 fclose(g.pScript);
3470 }
3471
3472 /* Release memory */
3473 free( pLook );
3474 free( pPCache );
3475 free( pHeap );
3476 return 0;
3477 }
3478
3479 #ifdef SQLITE_SPEEDTEST1_WASM
3480 /*
3481 ** A workaround for some inconsistent behaviour with how
3482 ** main() does (or does not) get exported to WASM.
3483 */
3484 int wasm_main(int argc, char **argv){
3485 return main(argc, argv);
3486 }
3487 #endif