?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Snapshot of upstream SQLite 3.45.3
[sqlcipher.git] / test / speedtest1.c
blob4f32580987915457d4706c920c88b2b016be1a00
1 /*
2 ** A program for performance testing.
3 **
4 ** The available command-line options are described below:
5 */
6 static const char zHelp[] =
7 "Usage: %s [--options] DATABASE\n"
8 "Options:\n"
9 " --autovacuum Enable AUTOVACUUM mode\n"
10 " --big-transactions Add BEGIN/END around all large tests\n"
11 " --cachesize N Set PRAGMA cache_size=N. Note: N is pages, not bytes\n"
12 " --checkpoint Run PRAGMA wal_checkpoint after each test case\n"
13 " --exclusive Enable locking_mode=EXCLUSIVE\n"
14 " --explain Like --sqlonly but with added EXPLAIN keywords\n"
15 " --fullfsync Enable fullfsync=TRUE\n"
16 " --heap SZ MIN Memory allocator uses SZ bytes & min allocation MIN\n"
17 " --incrvacuum Enable incremenatal vacuum mode\n"
18 " --journal M Set the journal_mode to M\n"
19 " --key KEY Set the encryption key to KEY\n"
20 " --lookaside N SZ Configure lookaside for N slots of SZ bytes each\n"
21 " --memdb Use an in-memory database\n"
22 " --mmap SZ MMAP the first SZ bytes of the database file\n"
23 " --multithread Set multithreaded mode\n"
24 " --nolongdouble Disable the use of long double\n"
25 " --nomemstat Disable memory statistics\n"
26 " --nomutex Open db with SQLITE_OPEN_NOMUTEX\n"
27 " --nosync Set PRAGMA synchronous=OFF\n"
28 " --notnull Add NOT NULL constraints to table columns\n"
29 " --output FILE Store SQL output in FILE\n"
30 " --pagesize N Set the page size to N\n"
31 " --pcache N SZ Configure N pages of pagecache each of size SZ bytes\n"
32 " --primarykey Use PRIMARY KEY instead of UNIQUE where appropriate\n"
33 " --repeat N Repeat each SELECT N times (default: 1)\n"
34 " --reprepare Reprepare each statement upon every invocation\n"
35 " --reserve N Reserve N bytes on each database page\n"
36 " --script FILE Write an SQL script for the test into FILE\n"
37 " --serialized Set serialized threading mode\n"
38 " --singlethread Set single-threaded mode - disables all mutexing\n"
39 " --sqlonly No-op. Only show the SQL that would have been run.\n"
40 " --shrink-memory Invoke sqlite3_db_release_memory() frequently.\n"
41 " --size N Relative test size. Default=100\n"
42 " --strict Use STRICT table where appropriate\n"
43 " --stats Show statistics at the end\n"
44 " --stmtscanstatus Activate SQLITE_DBCONFIG_STMT_SCANSTATUS\n"
45 " --temp N N from 0 to 9. 0: no temp table. 9: all temp tables\n"
46 " --testset T Run test-set T (main, cte, rtree, orm, fp, debug)\n"
47 " --trace Turn on SQL tracing\n"
48 " --threads N Use up to N threads for sorting\n"
49 " --utf16be Set text encoding to UTF-16BE\n"
50 " --utf16le Set text encoding to UTF-16LE\n"
51 " --verify Run additional verification steps\n"
52 " --vfs NAME Use the given (preinstalled) VFS\n"
53 " --without-rowid Use WITHOUT ROWID where appropriate\n"
54 ;
55
56 #include "sqlite3.h"
57 #include <assert.h>
58 #include <stdio.h>
59 #include <stdlib.h>
60 #include <stdarg.h>
61 #include <string.h>
62 #include <ctype.h>
63 #ifndef _WIN32
64 # include <unistd.h>
65 #else
66 # include <io.h>
67 #endif
68 #define ISSPACE(X) isspace((unsigned char)(X))
69 #define ISDIGIT(X) isdigit((unsigned char)(X))
70
71 #if SQLITE_VERSION_NUMBER<3005000
72 # define sqlite3_int64 sqlite_int64
73 #endif
74
75 typedef sqlite3_uint64 u64;
76
77 /*
78 ** State structure for a Hash hash in progress
79 */
80 typedef struct HashContext HashContext;
81 struct HashContext {
82 unsigned char isInit; /* True if initialized */
83 unsigned char i, j; /* State variables */
84 unsigned char s[256]; /* State variables */
85 unsigned char r[32]; /* Result */
86 };
87
88
89 /* All global state is held in this structure */
90 static struct Global {
91 sqlite3 *db; /* The open database connection */
92 sqlite3_stmt *pStmt; /* Current SQL statement */
93 sqlite3_int64 iStart; /* Start-time for the current test */
94 sqlite3_int64 iTotal; /* Total time */
95 int bWithoutRowid; /* True for --without-rowid */
96 int bReprepare; /* True to reprepare the SQL on each rerun */
97 int bSqlOnly; /* True to print the SQL once only */
98 int bExplain; /* Print SQL with EXPLAIN prefix */
99 int bVerify; /* Try to verify that results are correct */
100 int bMemShrink; /* Call sqlite3_db_release_memory() often */
101 int eTemp; /* 0: no TEMP. 9: always TEMP. */
102 int szTest; /* Scale factor for test iterations */
103 int nRepeat; /* Repeat selects this many times */
104 int doCheckpoint; /* Run PRAGMA wal_checkpoint after each trans */
105 int nReserve; /* Reserve bytes */
106 int stmtScanStatus; /* True to activate Stmt ScanStatus reporting */
107 int doBigTransactions; /* Enable transactions on tests 410 and 510 */
108 const char *zWR; /* Might be WITHOUT ROWID */
109 const char *zNN; /* Might be NOT NULL */
110 const char *zPK; /* Might be UNIQUE or PRIMARY KEY */
111 unsigned int x, y; /* Pseudo-random number generator state */
112 u64 nResByte; /* Total number of result bytes */
113 int nResult; /* Size of the current result */
114 char zResult[3000]; /* Text of the current result */
115 FILE *pScript; /* Write an SQL script into this file */
116 #ifndef SPEEDTEST_OMIT_HASH
117 FILE *hashFile; /* Store all hash results in this file */
118 HashContext hash; /* Hash of all output */
119 #endif
120 } g;
121
122 /* Return " TEMP" or "", as appropriate for creating a table.
123 */
124 static const char *isTemp(int N){
125 return g.eTemp>=N ? " TEMP" : "";
126 }
127
128 /* Print an error message and exit */
129 static void fatal_error(const char *zMsg, ...){
130 va_list ap;
131 va_start(ap, zMsg);
132 vfprintf(stderr, zMsg, ap);
133 va_end(ap);
134 exit(1);
135 }
136
137 #ifndef SPEEDTEST_OMIT_HASH
138 /****************************************************************************
139 ** Hash algorithm used to verify that compilation is not miscompiled
140 ** in such a was as to generate an incorrect result.
141 */
142
143 /*
144 ** Initialize a new hash. iSize determines the size of the hash
145 ** in bits and should be one of 224, 256, 384, or 512. Or iSize
146 ** can be zero to use the default hash size of 256 bits.
147 */
148 static void HashInit(void){
149 unsigned int k;
150 g.hash.i = 0;
151 g.hash.j = 0;
152 for(k=0; k<256; k++) g.hash.s[k] = k;
153 }
154
155 /*
156 ** Make consecutive calls to the HashUpdate function to add new content
157 ** to the hash
158 */
159 static void HashUpdate(
160 const unsigned char *aData,
161 unsigned int nData
162 ){
163 unsigned char t;
164 unsigned char i = g.hash.i;
165 unsigned char j = g.hash.j;
166 unsigned int k;
167 if( g.hashFile ) fwrite(aData, 1, nData, g.hashFile);
168 for(k=0; k<nData; k++){
169 j += g.hash.s[i] + aData[k];
170 t = g.hash.s[j];
171 g.hash.s[j] = g.hash.s[i];
172 g.hash.s[i] = t;
173 i++;
174 }
175 g.hash.i = i;
176 g.hash.j = j;
177 }
178
179 /*
180 ** After all content has been added, invoke HashFinal() to compute
181 ** the final hash. The hash result is stored in g.hash.r[].
182 */
183 static void HashFinal(void){
184 unsigned int k;
185 unsigned char t, i, j;
186 i = g.hash.i;
187 j = g.hash.j;
188 for(k=0; k<32; k++){
189 i++;
190 t = g.hash.s[i];
191 j += t;
192 g.hash.s[i] = g.hash.s[j];
193 g.hash.s[j] = t;
194 t += g.hash.s[i];
195 g.hash.r[k] = g.hash.s[t];
196 }
197 }
198
199 /* End of the Hash hashing logic
200 *****************************************************************************/
201 #endif /* SPEEDTEST_OMIT_HASH */
202
203 /*
204 ** Return the value of a hexadecimal digit. Return -1 if the input
205 ** is not a hex digit.
206 */
207 static int hexDigitValue(char c){
208 if( c>='0' && c<='9' ) return c - '0';
209 if( c>='a' && c<='f' ) return c - 'a' + 10;
210 if( c>='A' && c<='F' ) return c - 'A' + 10;
211 return -1;
212 }
213
214 /* Provide an alternative to sqlite3_stricmp() in older versions of
215 ** SQLite */
216 #if SQLITE_VERSION_NUMBER<3007011
217 # define sqlite3_stricmp strcmp
218 #endif
219
220 /*
221 ** Interpret zArg as an integer value, possibly with suffixes.
222 */
223 static int integerValue(const char *zArg){
224 sqlite3_int64 v = 0;
225 static const struct { char *zSuffix; int iMult; } aMult[] = {
226 { "KiB", 1024 },
227 { "MiB", 1024*1024 },
228 { "GiB", 1024*1024*1024 },
229 { "KB", 1000 },
230 { "MB", 1000000 },
231 { "GB", 1000000000 },
232 { "K", 1000 },
233 { "M", 1000000 },
234 { "G", 1000000000 },
235 };
236 int i;
237 int isNeg = 0;
238 if( zArg[0]=='-' ){
239 isNeg = 1;
240 zArg++;
241 }else if( zArg[0]=='+' ){
242 zArg++;
243 }
244 if( zArg[0]=='0' && zArg[1]=='x' ){
245 int x;
246 zArg += 2;
247 while( (x = hexDigitValue(zArg[0]))>=0 ){
248 v = (v<<4) + x;
249 zArg++;
250 }
251 }else{
252 while( isdigit(zArg[0]) ){
253 v = v*10 + zArg[0] - '0';
254 zArg++;
255 }
256 }
257 for(i=0; i<sizeof(aMult)/sizeof(aMult[0]); i++){
258 if( sqlite3_stricmp(aMult[i].zSuffix, zArg)==0 ){
259 v *= aMult[i].iMult;
260 break;
261 }
262 }
263 if( v>0x7fffffff ) fatal_error("parameter too large - max 2147483648");
264 return (int)(isNeg? -v : v);
265 }
266
267 /* Return the current wall-clock time, in milliseconds */
268 sqlite3_int64 speedtest1_timestamp(void){
269 #if SQLITE_VERSION_NUMBER<3005000
270 return 0;
271 #else
272 static sqlite3_vfs *clockVfs = 0;
273 sqlite3_int64 t;
274 if( clockVfs==0 ) clockVfs = sqlite3_vfs_find(0);
275 #if SQLITE_VERSION_NUMBER>=3007000
276 if( clockVfs->iVersion>=2 && clockVfs->xCurrentTimeInt64!=0 ){
277 clockVfs->xCurrentTimeInt64(clockVfs, &t);
278 }else
279 #endif
280 {
281 double r;
282 clockVfs->xCurrentTime(clockVfs, &r);
283 t = (sqlite3_int64)(r*86400000.0);
284 }
285 return t;
286 #endif
287 }
288
289 /* Return a pseudo-random unsigned integer */
290 unsigned int speedtest1_random(void){
291 g.x = (g.x>>1) ^ ((1+~(g.x&1)) & 0xd0000001);
292 g.y = g.y*1103515245 + 12345;
293 return g.x ^ g.y;
294 }
295
296 /* Map the value in within the range of 1...limit into another
297 ** number in a way that is chatic and invertable.
298 */
299 unsigned swizzle(unsigned in, unsigned limit){
300 unsigned out = 0;
301 while( limit ){
302 out = (out<<1) | (in&1);
303 in >>= 1;
304 limit >>= 1;
305 }
306 return out;
307 }
308
309 /* Round up a number so that it is a power of two minus one
310 */
311 unsigned roundup_allones(unsigned limit){
312 unsigned m = 1;
313 while( m<limit ) m = (m<<1)+1;
314 return m;
315 }
316
317 /* The speedtest1_numbername procedure below converts its argment (an integer)
318 ** into a string which is the English-language name for that number.
319 ** The returned string should be freed with sqlite3_free().
320 **
321 ** Example:
322 **
323 ** speedtest1_numbername(123) -> "one hundred twenty three"
324 */
325 int speedtest1_numbername(unsigned int n, char *zOut, int nOut){
326 static const char *ones[] = { "zero", "one", "two", "three", "four", "five",
327 "six", "seven", "eight", "nine", "ten", "eleven", "twelve",
328 "thirteen", "fourteen", "fifteen", "sixteen", "seventeen",
329 "eighteen", "nineteen" };
330 static const char *tens[] = { "", "ten", "twenty", "thirty", "forty",
331 "fifty", "sixty", "seventy", "eighty", "ninety" };
332 int i = 0;
333
334 if( n>=1000000000 ){
335 i += speedtest1_numbername(n/1000000000, zOut+i, nOut-i);
336 sqlite3_snprintf(nOut-i, zOut+i, " billion");
337 i += (int)strlen(zOut+i);
338 n = n % 1000000000;
339 }
340 if( n>=1000000 ){
341 if( i && i<nOut-1 ) zOut[i++] = ' ';
342 i += speedtest1_numbername(n/1000000, zOut+i, nOut-i);
343 sqlite3_snprintf(nOut-i, zOut+i, " million");
344 i += (int)strlen(zOut+i);
345 n = n % 1000000;
346 }
347 if( n>=1000 ){
348 if( i && i<nOut-1 ) zOut[i++] = ' ';
349 i += speedtest1_numbername(n/1000, zOut+i, nOut-i);
350 sqlite3_snprintf(nOut-i, zOut+i, " thousand");
351 i += (int)strlen(zOut+i);
352 n = n % 1000;
353 }
354 if( n>=100 ){
355 if( i && i<nOut-1 ) zOut[i++] = ' ';
356 sqlite3_snprintf(nOut-i, zOut+i, "%s hundred", ones[n/100]);
357 i += (int)strlen(zOut+i);
358 n = n % 100;
359 }
360 if( n>=20 ){
361 if( i && i<nOut-1 ) zOut[i++] = ' ';
362 sqlite3_snprintf(nOut-i, zOut+i, "%s", tens[n/10]);
363 i += (int)strlen(zOut+i);
364 n = n % 10;
365 }
366 if( n>0 ){
367 if( i && i<nOut-1 ) zOut[i++] = ' ';
368 sqlite3_snprintf(nOut-i, zOut+i, "%s", ones[n]);
369 i += (int)strlen(zOut+i);
370 }
371 if( i==0 ){
372 sqlite3_snprintf(nOut-i, zOut+i, "zero");
373 i += (int)strlen(zOut+i);
374 }
375 return i;
376 }
377
378
379 /* Start a new test case */
380 #define NAMEWIDTH 60
381 static const char zDots[] =
382 ".......................................................................";
383 static int iTestNumber = 0; /* Current test # for begin/end_test(). */
384 void speedtest1_begin_test(int iTestNum, const char *zTestName, ...){
385 int n = (int)strlen(zTestName);
386 char *zName;
387 va_list ap;
388 iTestNumber = iTestNum;
389 va_start(ap, zTestName);
390 zName = sqlite3_vmprintf(zTestName, ap);
391 va_end(ap);
392 n = (int)strlen(zName);
393 if( n>NAMEWIDTH ){
394 zName[NAMEWIDTH] = 0;
395 n = NAMEWIDTH;
396 }
397 if( g.pScript ){
398 fprintf(g.pScript,"-- begin test %d %.*s\n", iTestNumber, n, zName)
399 /* maintenance reminder: ^^^ code in ext/wasm expects %d to be
400 ** field #4 (as in: cut -d' ' -f4). */;
401 }
402 if( g.bSqlOnly ){
403 printf("/* %4d - %s%.*s */\n", iTestNum, zName, NAMEWIDTH-n, zDots);
404 }else{
405 printf("%4d - %s%.*s ", iTestNum, zName, NAMEWIDTH-n, zDots);
406 fflush(stdout);
407 }
408 sqlite3_free(zName);
409 g.nResult = 0;
410 g.iStart = speedtest1_timestamp();
411 g.x = 0xad131d0b;
412 g.y = 0x44f9eac8;
413 }
414
415 /* Forward reference */
416 void speedtest1_exec(const char*,...);
417
418 /* Complete a test case */
419 void speedtest1_end_test(void){
420 sqlite3_int64 iElapseTime = speedtest1_timestamp() - g.iStart;
421 if( g.doCheckpoint ) speedtest1_exec("PRAGMA wal_checkpoint;");
422 assert( iTestNumber > 0 );
423 if( g.pScript ){
424 fprintf(g.pScript,"-- end test %d\n", iTestNumber);
425 }
426 if( !g.bSqlOnly ){
427 g.iTotal += iElapseTime;
428 printf("%4d.%03ds\n", (int)(iElapseTime/1000), (int)(iElapseTime%1000));
429 }
430 if( g.pStmt ){
431 sqlite3_finalize(g.pStmt);
432 g.pStmt = 0;
433 }
434 iTestNumber = 0;
435 }
436
437 /* Report end of testing */
438 void speedtest1_final(void){
439 if( !g.bSqlOnly ){
440 printf(" TOTAL%.*s %4d.%03ds\n", NAMEWIDTH-5, zDots,
441 (int)(g.iTotal/1000), (int)(g.iTotal%1000));
442 }
443 if( g.bVerify ){
444 #ifndef SPEEDTEST_OMIT_HASH
445 int i;
446 #endif
447 printf("Verification Hash: %llu ", g.nResByte);
448 #ifndef SPEEDTEST_OMIT_HASH
449 HashUpdate((const unsigned char*)"\n", 1);
450 HashFinal();
451 for(i=0; i<24; i++){
452 printf("%02x", g.hash.r[i]);
453 }
454 if( g.hashFile && g.hashFile!=stdout ) fclose(g.hashFile);
455 #endif
456 printf("\n");
457 }
458 }
459
460 /* Print an SQL statement to standard output */
461 static void printSql(const char *zSql){
462 int n = (int)strlen(zSql);
463 while( n>0 && (zSql[n-1]==';' || ISSPACE(zSql[n-1])) ){ n--; }
464 if( g.bExplain ) printf("EXPLAIN ");
465 printf("%.*s;\n", n, zSql);
466 if( g.bExplain
467 #if SQLITE_VERSION_NUMBER>=3007017
468 && ( sqlite3_strglob("CREATE *", zSql)==0
469 || sqlite3_strglob("DROP *", zSql)==0
470 || sqlite3_strglob("ALTER *", zSql)==0
471 )
472 #endif
473 ){
474 printf("%.*s;\n", n, zSql);
475 }
476 }
477
478 /* Shrink memory used, if appropriate and if the SQLite version is capable
479 ** of doing so.
480 */
481 void speedtest1_shrink_memory(void){
482 #if SQLITE_VERSION_NUMBER>=3007010
483 if( g.bMemShrink ) sqlite3_db_release_memory(g.db);
484 #endif
485 }
486
487 /* Run SQL */
488 void speedtest1_exec(const char *zFormat, ...){
489 va_list ap;
490 char *zSql;
491 va_start(ap, zFormat);
492 zSql = sqlite3_vmprintf(zFormat, ap);
493 va_end(ap);
494 if( g.bSqlOnly ){
495 printSql(zSql);
496 }else{
497 char *zErrMsg = 0;
498 int rc;
499 if( g.pScript ){
500 fprintf(g.pScript,"%s;\n",zSql);
501 }
502 rc = sqlite3_exec(g.db, zSql, 0, 0, &zErrMsg);
503 if( zErrMsg ) fatal_error("SQL error: %s\n%s\n", zErrMsg, zSql);
504 if( rc!=SQLITE_OK ) fatal_error("exec error: %s\n", sqlite3_errmsg(g.db));
505 }
506 sqlite3_free(zSql);
507 speedtest1_shrink_memory();
508 }
509
510 /* Run SQL and return the first column of the first row as a string. The
511 ** returned string is obtained from sqlite_malloc() and must be freed by
512 ** the caller.
513 */
514 char *speedtest1_once(const char *zFormat, ...){
515 va_list ap;
516 char *zSql;
517 sqlite3_stmt *pStmt;
518 char *zResult = 0;
519 va_start(ap, zFormat);
520 zSql = sqlite3_vmprintf(zFormat, ap);
521 va_end(ap);
522 if( g.bSqlOnly ){
523 printSql(zSql);
524 }else{
525 int rc = sqlite3_prepare_v2(g.db, zSql, -1, &pStmt, 0);
526 if( rc ){
527 fatal_error("SQL error: %s\n", sqlite3_errmsg(g.db));
528 }
529 if( g.pScript ){
530 char *z = sqlite3_expanded_sql(pStmt);
531 fprintf(g.pScript,"%s\n",z);
532 sqlite3_free(z);
533 }
534 if( sqlite3_step(pStmt)==SQLITE_ROW ){
535 const char *z = (const char*)sqlite3_column_text(pStmt, 0);
536 if( z ) zResult = sqlite3_mprintf("%s", z);
537 }
538 sqlite3_finalize(pStmt);
539 }
540 sqlite3_free(zSql);
541 speedtest1_shrink_memory();
542 return zResult;
543 }
544
545 /* Prepare an SQL statement */
546 void speedtest1_prepare(const char *zFormat, ...){
547 va_list ap;
548 char *zSql;
549 va_start(ap, zFormat);
550 zSql = sqlite3_vmprintf(zFormat, ap);
551 va_end(ap);
552 if( g.bSqlOnly ){
553 printSql(zSql);
554 }else{
555 int rc;
556 if( g.pStmt ) sqlite3_finalize(g.pStmt);
557 rc = sqlite3_prepare_v2(g.db, zSql, -1, &g.pStmt, 0);
558 if( rc ){
559 fatal_error("SQL error: %s\n", sqlite3_errmsg(g.db));
560 }
561 }
562 sqlite3_free(zSql);
563 }
564
565 /* Run an SQL statement previously prepared */
566 void speedtest1_run(void){
567 int i, n, len;
568 if( g.bSqlOnly ) return;
569 assert( g.pStmt );
570 g.nResult = 0;
571 if( g.pScript ){
572 char *z = sqlite3_expanded_sql(g.pStmt);
573 fprintf(g.pScript,"%s\n",z);
574 sqlite3_free(z);
575 }
576 while( sqlite3_step(g.pStmt)==SQLITE_ROW ){
577 n = sqlite3_column_count(g.pStmt);
578 for(i=0; i<n; i++){
579 const char *z = (const char*)sqlite3_column_text(g.pStmt, i);
580 if( z==0 ) z = "nil";
581 len = (int)strlen(z);
582 #ifndef SPEEDTEST_OMIT_HASH
583 if( g.bVerify ){
584 int eType = sqlite3_column_type(g.pStmt, i);
585 unsigned char zPrefix[2];
586 zPrefix[0] = '\n';
587 zPrefix[1] = "-IFTBN"[eType];
588 if( g.nResByte ){
589 HashUpdate(zPrefix, 2);
590 }else{
591 HashUpdate(zPrefix+1, 1);
592 }
593 if( eType==SQLITE_FLOAT ){
594 /* Omit the value of floating-point results from the verification
595 ** hash. The only thing we record is the fact that the result was
596 ** a floating-point value. */
597 g.nResByte += 2;
598 }else if( eType==SQLITE_BLOB ){
599 int nBlob = sqlite3_column_bytes(g.pStmt, i);
600 int iBlob;
601 unsigned char zChar[2];
602 const unsigned char *aBlob = sqlite3_column_blob(g.pStmt, i);
603 for(iBlob=0; iBlob<nBlob; iBlob++){
604 zChar[0] = "0123456789abcdef"[aBlob[iBlob]>>4];
605 zChar[1] = "0123456789abcdef"[aBlob[iBlob]&15];
606 HashUpdate(zChar,2);
607 }
608 g.nResByte += nBlob*2 + 2;
609 }else{
610 HashUpdate((unsigned char*)z, len);
611 g.nResByte += len + 2;
612 }
613 }
614 #endif
615 if( g.nResult+len<sizeof(g.zResult)-2 ){
616 if( g.nResult>0 ) g.zResult[g.nResult++] = ' ';
617 memcpy(g.zResult + g.nResult, z, len+1);
618 g.nResult += len;
619 }
620 }
621 }
622 #if SQLITE_VERSION_NUMBER>=3006001
623 if( g.bReprepare ){
624 sqlite3_stmt *pNew;
625 sqlite3_prepare_v2(g.db, sqlite3_sql(g.pStmt), -1, &pNew, 0);
626 sqlite3_finalize(g.pStmt);
627 g.pStmt = pNew;
628 }else
629 #endif
630 {
631 sqlite3_reset(g.pStmt);
632 }
633 speedtest1_shrink_memory();
634 }
635
636 #ifndef SQLITE_OMIT_DEPRECATED
637 /* The sqlite3_trace() callback function */
638 static void traceCallback(void *NotUsed, const char *zSql){
639 int n = (int)strlen(zSql);
640 while( n>0 && (zSql[n-1]==';' || ISSPACE(zSql[n-1])) ) n--;
641 fprintf(stderr,"%.*s;\n", n, zSql);
642 }
643 #endif /* SQLITE_OMIT_DEPRECATED */
644
645 /* Substitute random() function that gives the same random
646 ** sequence on each run, for repeatability. */
647 static void randomFunc(
648 sqlite3_context *context,
649 int NotUsed,
650 sqlite3_value **NotUsed2
651 ){
652 sqlite3_result_int64(context, (sqlite3_int64)speedtest1_random());
653 }
654
655 /* Estimate the square root of an integer */
656 static int est_square_root(int x){
657 int y0 = x/2;
658 int y1;
659 int n;
660 for(n=0; y0>0 && n<10; n++){
661 y1 = (y0 + x/y0)/2;
662 if( y1==y0 ) break;
663 y0 = y1;
664 }
665 return y0;
666 }
667
668
669 #if SQLITE_VERSION_NUMBER<3005004
670 /*
671 ** An implementation of group_concat(). Used only when testing older
672 ** versions of SQLite that lack the built-in group_concat().
673 */
674 struct groupConcat {
675 char *z;
676 int nAlloc;
677 int nUsed;
678 };
679 static void groupAppend(struct groupConcat *p, const char *z, int n){
680 if( p->nUsed+n >= p->nAlloc ){
681 int n2 = (p->nAlloc+n+1)*2;
682 char *z2 = sqlite3_realloc(p->z, n2);
683 if( z2==0 ) return;
684 p->z = z2;
685 p->nAlloc = n2;
686 }
687 memcpy(p->z+p->nUsed, z, n);
688 p->nUsed += n;
689 }
690 static void groupStep(
691 sqlite3_context *context,
692 int argc,
693 sqlite3_value **argv
694 ){
695 const char *zVal;
696 struct groupConcat *p;
697 const char *zSep;
698 int nVal, nSep;
699 assert( argc==1 || argc==2 );
700 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
701 p= (struct groupConcat*)sqlite3_aggregate_context(context, sizeof(*p));
702
703 if( p ){
704 int firstTerm = p->nUsed==0;
705 if( !firstTerm ){
706 if( argc==2 ){
707 zSep = (char*)sqlite3_value_text(argv[1]);
708 nSep = sqlite3_value_bytes(argv[1]);
709 }else{
710 zSep = ",";
711 nSep = 1;
712 }
713 if( nSep ) groupAppend(p, zSep, nSep);
714 }
715 zVal = (char*)sqlite3_value_text(argv[0]);
716 nVal = sqlite3_value_bytes(argv[0]);
717 if( zVal ) groupAppend(p, zVal, nVal);
718 }
719 }
720 static void groupFinal(sqlite3_context *context){
721 struct groupConcat *p;
722 p = sqlite3_aggregate_context(context, 0);
723 if( p && p->z ){
724 p->z[p->nUsed] = 0;
725 sqlite3_result_text(context, p->z, p->nUsed, sqlite3_free);
726 }
727 }
728 #endif
729
730 /*
731 ** The main and default testset
732 */
733 void testset_main(void){
734 int i; /* Loop counter */
735 int n; /* iteration count */
736 int sz; /* Size of the tables */
737 int maxb; /* Maximum swizzled value */
738 unsigned x1 = 0, x2 = 0; /* Parameters */
739 int len = 0; /* Length of the zNum[] string */
740 char zNum[2000]; /* A number name */
741
742 sz = n = g.szTest*500;
743 zNum[0] = 0;
744 maxb = roundup_allones(sz);
745 speedtest1_begin_test(100, "%d INSERTs into table with no index", n);
746 speedtest1_exec("BEGIN");
747 speedtest1_exec("CREATE%s TABLE z1(a INTEGER %s, b INTEGER %s, c TEXT %s);",
748 isTemp(9), g.zNN, g.zNN, g.zNN);
749 speedtest1_prepare("INSERT INTO z1 VALUES(?1,?2,?3); -- %d times", n);
750 for(i=1; i<=n; i++){
751 x1 = swizzle(i,maxb);
752 speedtest1_numbername(x1, zNum, sizeof(zNum));
753 sqlite3_bind_int64(g.pStmt, 1, (sqlite3_int64)x1);
754 sqlite3_bind_int(g.pStmt, 2, i);
755 sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
756 speedtest1_run();
757 }
758 speedtest1_exec("COMMIT");
759 speedtest1_end_test();
760
761
762 n = sz;
763 speedtest1_begin_test(110, "%d ordered INSERTS with one index/PK", n);
764 speedtest1_exec("BEGIN");
765 speedtest1_exec(
766 "CREATE%s TABLE z2(a INTEGER %s %s, b INTEGER %s, c TEXT %s) %s",
767 isTemp(5), g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);
768 speedtest1_prepare("INSERT INTO z2 VALUES(?1,?2,?3); -- %d times", n);
769 for(i=1; i<=n; i++){
770 x1 = swizzle(i,maxb);
771 speedtest1_numbername(x1, zNum, sizeof(zNum));
772 sqlite3_bind_int(g.pStmt, 1, i);
773 sqlite3_bind_int64(g.pStmt, 2, (sqlite3_int64)x1);
774 sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
775 speedtest1_run();
776 }
777 speedtest1_exec("COMMIT");
778 speedtest1_end_test();
779
780
781 n = sz;
782 speedtest1_begin_test(120, "%d unordered INSERTS with one index/PK", n);
783 speedtest1_exec("BEGIN");
784 speedtest1_exec(
785 "CREATE%s TABLE t3(a INTEGER %s %s, b INTEGER %s, c TEXT %s) %s",
786 isTemp(3), g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);
787 speedtest1_prepare("INSERT INTO t3 VALUES(?1,?2,?3); -- %d times", n);
788 for(i=1; i<=n; i++){
789 x1 = swizzle(i,maxb);
790 speedtest1_numbername(x1, zNum, sizeof(zNum));
791 sqlite3_bind_int(g.pStmt, 2, i);
792 sqlite3_bind_int64(g.pStmt, 1, (sqlite3_int64)x1);
793 sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
794 speedtest1_run();
795 }
796 speedtest1_exec("COMMIT");
797 speedtest1_end_test();
798
799 #if SQLITE_VERSION_NUMBER<3005004
800 sqlite3_create_function(g.db, "group_concat", 1, SQLITE_UTF8, 0,
801 0, groupStep, groupFinal);
802 #endif
803
804 n = 25;
805 speedtest1_begin_test(130, "%d SELECTS, numeric BETWEEN, unindexed", n);
806 speedtest1_exec("BEGIN");
807 speedtest1_prepare(
808 "SELECT count(*), avg(b), sum(length(c)), group_concat(c) FROM z1\n"
809 " WHERE b BETWEEN ?1 AND ?2; -- %d times", n
810 );
811 for(i=1; i<=n; i++){
812 if( (i-1)%g.nRepeat==0 ){
813 x1 = speedtest1_random()%maxb;
814 x2 = speedtest1_random()%10 + sz/5000 + x1;
815 }
816 sqlite3_bind_int(g.pStmt, 1, x1);
817 sqlite3_bind_int(g.pStmt, 2, x2);
818 speedtest1_run();
819 }
820 speedtest1_exec("COMMIT");
821 speedtest1_end_test();
822
823
824 n = 10;
825 speedtest1_begin_test(140, "%d SELECTS, LIKE, unindexed", n);
826 speedtest1_exec("BEGIN");
827 speedtest1_prepare(
828 "SELECT count(*), avg(b), sum(length(c)), group_concat(c) FROM z1\n"
829 " WHERE c LIKE ?1; -- %d times", n
830 );
831 for(i=1; i<=n; i++){
832 if( (i-1)%g.nRepeat==0 ){
833 x1 = speedtest1_random()%maxb;
834 zNum[0] = '%';
835 len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
836 zNum[len] = '%';
837 zNum[len+1] = 0;
838 }
839 sqlite3_bind_text(g.pStmt, 1, zNum, len+1, SQLITE_STATIC);
840 speedtest1_run();
841 }
842 speedtest1_exec("COMMIT");
843 speedtest1_end_test();
844
845
846 n = 10;
847 speedtest1_begin_test(142, "%d SELECTS w/ORDER BY, unindexed", n);
848 speedtest1_exec("BEGIN");
849 speedtest1_prepare(
850 "SELECT a, b, c FROM z1 WHERE c LIKE ?1\n"
851 " ORDER BY a; -- %d times", n
852 );
853 for(i=1; i<=n; i++){
854 if( (i-1)%g.nRepeat==0 ){
855 x1 = speedtest1_random()%maxb;
856 zNum[0] = '%';
857 len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
858 zNum[len] = '%';
859 zNum[len+1] = 0;
860 }
861 sqlite3_bind_text(g.pStmt, 1, zNum, len+1, SQLITE_STATIC);
862 speedtest1_run();
863 }
864 speedtest1_exec("COMMIT");
865 speedtest1_end_test();
866
867 n = 10; /* g.szTest/5; */
868 speedtest1_begin_test(145, "%d SELECTS w/ORDER BY and LIMIT, unindexed", n);
869 speedtest1_exec("BEGIN");
870 speedtest1_prepare(
871 "SELECT a, b, c FROM z1 WHERE c LIKE ?1\n"
872 " ORDER BY a LIMIT 10; -- %d times", n
873 );
874 for(i=1; i<=n; i++){
875 if( (i-1)%g.nRepeat==0 ){
876 x1 = speedtest1_random()%maxb;
877 zNum[0] = '%';
878 len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
879 zNum[len] = '%';
880 zNum[len+1] = 0;
881 }
882 sqlite3_bind_text(g.pStmt, 1, zNum, len+1, SQLITE_STATIC);
883 speedtest1_run();
884 }
885 speedtest1_exec("COMMIT");
886 speedtest1_end_test();
887
888
889 speedtest1_begin_test(150, "CREATE INDEX five times");
890 speedtest1_exec("BEGIN;");
891 speedtest1_exec("CREATE UNIQUE INDEX t1b ON z1(b);");
892 speedtest1_exec("CREATE INDEX t1c ON z1(c);");
893 speedtest1_exec("CREATE UNIQUE INDEX t2b ON z2(b);");
894 speedtest1_exec("CREATE INDEX t2c ON z2(c DESC);");
895 speedtest1_exec("CREATE INDEX t3bc ON t3(b,c);");
896 speedtest1_exec("COMMIT;");
897 speedtest1_end_test();
898
899
900 n = sz/5;
901 speedtest1_begin_test(160, "%d SELECTS, numeric BETWEEN, indexed", n);
902 speedtest1_exec("BEGIN");
903 speedtest1_prepare(
904 "SELECT count(*), avg(b), sum(length(c)), group_concat(a) FROM z1\n"
905 " WHERE b BETWEEN ?1 AND ?2; -- %d times", n
906 );
907 for(i=1; i<=n; i++){
908 if( (i-1)%g.nRepeat==0 ){
909 x1 = speedtest1_random()%maxb;
910 x2 = speedtest1_random()%10 + sz/5000 + x1;
911 }
912 sqlite3_bind_int(g.pStmt, 1, x1);
913 sqlite3_bind_int(g.pStmt, 2, x2);
914 speedtest1_run();
915 }
916 speedtest1_exec("COMMIT");
917 speedtest1_end_test();
918
919
920 n = sz/5;
921 speedtest1_begin_test(161, "%d SELECTS, numeric BETWEEN, PK", n);
922 speedtest1_exec("BEGIN");
923 speedtest1_prepare(
924 "SELECT count(*), avg(b), sum(length(c)), group_concat(a) FROM z2\n"
925 " WHERE a BETWEEN ?1 AND ?2; -- %d times", n
926 );
927 for(i=1; i<=n; i++){
928 if( (i-1)%g.nRepeat==0 ){
929 x1 = speedtest1_random()%maxb;
930 x2 = speedtest1_random()%10 + sz/5000 + x1;
931 }
932 sqlite3_bind_int(g.pStmt, 1, x1);
933 sqlite3_bind_int(g.pStmt, 2, x2);
934 speedtest1_run();
935 }
936 speedtest1_exec("COMMIT");
937 speedtest1_end_test();
938
939
940 n = sz/5;
941 speedtest1_begin_test(170, "%d SELECTS, text BETWEEN, indexed", n);
942 speedtest1_exec("BEGIN");
943 speedtest1_prepare(
944 "SELECT count(*), avg(b), sum(length(c)), group_concat(a) FROM z1\n"
945 " WHERE c BETWEEN ?1 AND (?1||'~'); -- %d times", n
946 );
947 for(i=1; i<=n; i++){
948 if( (i-1)%g.nRepeat==0 ){
949 x1 = swizzle(i, maxb);
950 len = speedtest1_numbername(x1, zNum, sizeof(zNum)-1);
951 }
952 sqlite3_bind_text(g.pStmt, 1, zNum, len, SQLITE_STATIC);
953 speedtest1_run();
954 }
955 speedtest1_exec("COMMIT");
956 speedtest1_end_test();
957
958 n = sz;
959 speedtest1_begin_test(180, "%d INSERTS with three indexes", n);
960 speedtest1_exec("BEGIN");
961 speedtest1_exec(
962 "CREATE%s TABLE t4(\n"
963 " a INTEGER %s %s,\n"
964 " b INTEGER %s,\n"
965 " c TEXT %s\n"
966 ") %s",
967 isTemp(1), g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);
968 speedtest1_exec("CREATE INDEX t4b ON t4(b)");
969 speedtest1_exec("CREATE INDEX t4c ON t4(c)");
970 speedtest1_exec("INSERT INTO t4 SELECT * FROM z1");
971 speedtest1_exec("COMMIT");
972 speedtest1_end_test();
973
974 n = sz;
975 speedtest1_begin_test(190, "DELETE and REFILL one table", n);
976 speedtest1_exec("DELETE FROM z2;");
977 speedtest1_exec("INSERT INTO z2 SELECT * FROM z1;");
978 speedtest1_end_test();
979
980
981 speedtest1_begin_test(200, "VACUUM");
982 speedtest1_exec("VACUUM");
983 speedtest1_end_test();
984
985
986 speedtest1_begin_test(210, "ALTER TABLE ADD COLUMN, and query");
987 speedtest1_exec("ALTER TABLE z2 ADD COLUMN d INT DEFAULT 123");
988 speedtest1_exec("SELECT sum(d) FROM z2");
989 speedtest1_end_test();
990
991
992 n = sz/5;
993 speedtest1_begin_test(230, "%d UPDATES, numeric BETWEEN, indexed", n);
994 speedtest1_exec("BEGIN");
995 speedtest1_prepare(
996 "UPDATE z2 SET d=b*2 WHERE b BETWEEN ?1 AND ?2; -- %d times", n
997 );
998 for(i=1; i<=n; i++){
999 x1 = speedtest1_random()%maxb;
1000 x2 = speedtest1_random()%10 + sz/5000 + x1;
1001 sqlite3_bind_int(g.pStmt, 1, x1);
1002 sqlite3_bind_int(g.pStmt, 2, x2);
1003 speedtest1_run();
1004 }
1005 speedtest1_exec("COMMIT");
1006 speedtest1_end_test();
1007
1008
1009 n = sz;
1010 speedtest1_begin_test(240, "%d UPDATES of individual rows", n);
1011 speedtest1_exec("BEGIN");
1012 speedtest1_prepare(
1013 "UPDATE z2 SET d=b*3 WHERE a=?1; -- %d times", n
1014 );
1015 for(i=1; i<=n; i++){
1016 x1 = speedtest1_random()%sz + 1;
1017 sqlite3_bind_int(g.pStmt, 1, x1);
1018 speedtest1_run();
1019 }
1020 speedtest1_exec("COMMIT");
1021 speedtest1_end_test();
1022
1023 speedtest1_begin_test(250, "One big UPDATE of the whole %d-row table", sz);
1024 speedtest1_exec("UPDATE z2 SET d=b*4");
1025 speedtest1_end_test();
1026
1027
1028 speedtest1_begin_test(260, "Query added column after filling");
1029 speedtest1_exec("SELECT sum(d) FROM z2");
1030 speedtest1_end_test();
1031
1032
1033
1034 n = sz/5;
1035 speedtest1_begin_test(270, "%d DELETEs, numeric BETWEEN, indexed", n);
1036 speedtest1_exec("BEGIN");
1037 speedtest1_prepare(
1038 "DELETE FROM z2 WHERE b BETWEEN ?1 AND ?2; -- %d times", n
1039 );
1040 for(i=1; i<=n; i++){
1041 x1 = speedtest1_random()%maxb + 1;
1042 x2 = speedtest1_random()%10 + sz/5000 + x1;
1043 sqlite3_bind_int(g.pStmt, 1, x1);
1044 sqlite3_bind_int(g.pStmt, 2, x2);
1045 speedtest1_run();
1046 }
1047 speedtest1_exec("COMMIT");
1048 speedtest1_end_test();
1049
1050
1051 n = sz;
1052 speedtest1_begin_test(280, "%d DELETEs of individual rows", n);
1053 speedtest1_exec("BEGIN");
1054 speedtest1_prepare(
1055 "DELETE FROM t3 WHERE a=?1; -- %d times", n
1056 );
1057 for(i=1; i<=n; i++){
1058 x1 = speedtest1_random()%sz + 1;
1059 sqlite3_bind_int(g.pStmt, 1, x1);
1060 speedtest1_run();
1061 }
1062 speedtest1_exec("COMMIT");
1063 speedtest1_end_test();
1064
1065
1066 speedtest1_begin_test(290, "Refill two %d-row tables using REPLACE", sz);
1067 speedtest1_exec("REPLACE INTO z2(a,b,c) SELECT a,b,c FROM z1");
1068 speedtest1_exec("REPLACE INTO t3(a,b,c) SELECT a,b,c FROM z1");
1069 speedtest1_end_test();
1070
1071 speedtest1_begin_test(300, "Refill a %d-row table using (b&1)==(a&1)", sz);
1072 speedtest1_exec("DELETE FROM z2;");
1073 speedtest1_exec("INSERT INTO z2(a,b,c)\n"
1074 " SELECT a,b,c FROM z1 WHERE (b&1)==(a&1);");
1075 speedtest1_exec("INSERT INTO z2(a,b,c)\n"
1076 " SELECT a,b,c FROM z1 WHERE (b&1)<>(a&1);");
1077 speedtest1_end_test();
1078
1079
1080 n = sz/5;
1081 speedtest1_begin_test(310, "%d four-ways joins", n);
1082 speedtest1_exec("BEGIN");
1083 speedtest1_prepare(
1084 "SELECT z1.c FROM z1, z2, t3, t4\n"
1085 " WHERE t4.a BETWEEN ?1 AND ?2\n"
1086 " AND t3.a=t4.b\n"
1087 " AND z2.a=t3.b\n"
1088 " AND z1.c=z2.c;"
1089 );
1090 for(i=1; i<=n; i++){
1091 x1 = speedtest1_random()%sz + 1;
1092 x2 = speedtest1_random()%10 + x1 + 4;
1093 sqlite3_bind_int(g.pStmt, 1, x1);
1094 sqlite3_bind_int(g.pStmt, 2, x2);
1095 speedtest1_run();
1096 }
1097 speedtest1_exec("COMMIT");
1098 speedtest1_end_test();
1099
1100 speedtest1_begin_test(320, "subquery in result set", n);
1101 speedtest1_prepare(
1102 "SELECT sum(a), max(c),\n"
1103 " avg((SELECT a FROM z2 WHERE 5+z2.b=z1.b) AND rowid<?1), max(c)\n"
1104 " FROM z1 WHERE rowid<?1;"
1105 );
1106 sqlite3_bind_int(g.pStmt, 1, est_square_root(g.szTest)*50);
1107 speedtest1_run();
1108 speedtest1_end_test();
1109
1110 sz = n = g.szTest*700;
1111 zNum[0] = 0;
1112 maxb = roundup_allones(sz/3);
1113 speedtest1_begin_test(400, "%d REPLACE ops on an IPK", n);
1114 speedtest1_exec("BEGIN");
1115 speedtest1_exec("CREATE%s TABLE t5(a INTEGER PRIMARY KEY, b %s);",
1116 isTemp(9), g.zNN);
1117 speedtest1_prepare("REPLACE INTO t5 VALUES(?1,?2); -- %d times",n);
1118 for(i=1; i<=n; i++){
1119 x1 = swizzle(i,maxb);
1120 speedtest1_numbername(i, zNum, sizeof(zNum));
1121 sqlite3_bind_int(g.pStmt, 1, (sqlite3_int64)x1);
1122 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
1123 speedtest1_run();
1124 }
1125 speedtest1_exec("COMMIT");
1126 speedtest1_end_test();
1127 speedtest1_begin_test(410, "%d SELECTS on an IPK", n);
1128 if( g.doBigTransactions ){
1129 /* Historical note: tests 410 and 510 have historically not used
1130 ** explicit transactions. The --big-transactions flag was added
1131 ** 2022-09-08 to support the WASM/OPFS build, as the run-times
1132 ** approach 1 minute for each of these tests if they're not in an
1133 ** explicit transaction. The run-time effect of --big-transaciions
1134 ** on native builds is negligible. */
1135 speedtest1_exec("BEGIN");
1136 }
1137 speedtest1_prepare("SELECT b FROM t5 WHERE a=?1; -- %d times",n);
1138 for(i=1; i<=n; i++){
1139 x1 = swizzle(i,maxb);
1140 sqlite3_bind_int(g.pStmt, 1, (sqlite3_int64)x1);
1141 speedtest1_run();
1142 }
1143 if( g.doBigTransactions ){
1144 speedtest1_exec("COMMIT");
1145 }
1146 speedtest1_end_test();
1147
1148 sz = n = g.szTest*700;
1149 zNum[0] = 0;
1150 maxb = roundup_allones(sz/3);
1151 speedtest1_begin_test(500, "%d REPLACE on TEXT PK", n);
1152 speedtest1_exec("BEGIN");
1153 speedtest1_exec("CREATE%s TABLE t6(a TEXT PRIMARY KEY, b %s)%s;",
1154 isTemp(9), g.zNN,
1155 sqlite3_libversion_number()>=3008002 ? "WITHOUT ROWID" : "");
1156 speedtest1_prepare("REPLACE INTO t6 VALUES(?1,?2); -- %d times",n);
1157 for(i=1; i<=n; i++){
1158 x1 = swizzle(i,maxb);
1159 speedtest1_numbername(x1, zNum, sizeof(zNum));
1160 sqlite3_bind_int(g.pStmt, 2, i);
1161 sqlite3_bind_text(g.pStmt, 1, zNum, -1, SQLITE_STATIC);
1162 speedtest1_run();
1163 }
1164 speedtest1_exec("COMMIT");
1165 speedtest1_end_test();
1166 speedtest1_begin_test(510, "%d SELECTS on a TEXT PK", n);
1167 if( g.doBigTransactions ){
1168 /* See notes for test 410. */
1169 speedtest1_exec("BEGIN");
1170 }
1171 speedtest1_prepare("SELECT b FROM t6 WHERE a=?1; -- %d times",n);
1172 for(i=1; i<=n; i++){
1173 x1 = swizzle(i,maxb);
1174 speedtest1_numbername(x1, zNum, sizeof(zNum));
1175 sqlite3_bind_text(g.pStmt, 1, zNum, -1, SQLITE_STATIC);
1176 speedtest1_run();
1177 }
1178 if( g.doBigTransactions ){
1179 speedtest1_exec("COMMIT");
1180 }
1181 speedtest1_end_test();
1182 speedtest1_begin_test(520, "%d SELECT DISTINCT", n);
1183 speedtest1_exec("SELECT DISTINCT b FROM t5;");
1184 speedtest1_exec("SELECT DISTINCT b FROM t6;");
1185 speedtest1_end_test();
1186
1187
1188 speedtest1_begin_test(980, "PRAGMA integrity_check");
1189 speedtest1_exec("PRAGMA integrity_check");
1190 speedtest1_end_test();
1191
1192
1193 speedtest1_begin_test(990, "ANALYZE");
1194 speedtest1_exec("ANALYZE");
1195 speedtest1_end_test();
1196 }
1197
1198 /*
1199 ** A testset for common table expressions. This exercises code
1200 ** for views, subqueries, co-routines, etc.
1201 */
1202 void testset_cte(void){
1203 static const char *azPuzzle[] = {
1204 /* Easy */
1205 "534...9.."
1206 "67.195..."
1207 ".98....6."
1208 "8...6...3"
1209 "4..8.3..1"
1210 "....2...6"
1211 ".6....28."
1212 "...419..5"
1213 "...28..79",
1214
1215 /* Medium */
1216 "53....9.."
1217 "6..195..."
1218 ".98....6."
1219 "8...6...3"
1220 "4..8.3..1"
1221 "....2...6"
1222 ".6....28."
1223 "...419..5"
1224 "....8..79",
1225
1226 /* Hard */
1227 "53......."
1228 "6..195..."
1229 ".98....6."
1230 "8...6...3"
1231 "4..8.3..1"
1232 "....2...6"
1233 ".6....28."
1234 "...419..5"
1235 "....8..79",
1236 };
1237 const char *zPuz;
1238 double rSpacing;
1239 int nElem;
1240
1241 if( g.szTest<25 ){
1242 zPuz = azPuzzle[0];
1243 }else if( g.szTest<70 ){
1244 zPuz = azPuzzle[1];
1245 }else{
1246 zPuz = azPuzzle[2];
1247 }
1248 speedtest1_begin_test(100, "Sudoku with recursive 'digits'");
1249 speedtest1_prepare(
1250 "WITH RECURSIVE\n"
1251 " input(sud) AS (VALUES(?1)),\n"
1252 " digits(z,lp) AS (\n"
1253 " VALUES('1', 1)\n"
1254 " UNION ALL\n"
1255 " SELECT CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9\n"
1256 " ),\n"
1257 " x(s, ind) AS (\n"
1258 " SELECT sud, instr(sud, '.') FROM input\n"
1259 " UNION ALL\n"
1260 " SELECT\n"
1261 " substr(s, 1, ind-1) || z || substr(s, ind+1),\n"
1262 " instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )\n"
1263 " FROM x, digits AS z\n"
1264 " WHERE ind>0\n"
1265 " AND NOT EXISTS (\n"
1266 " SELECT 1\n"
1267 " FROM digits AS lp\n"
1268 " WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)\n"
1269 " OR z.z = substr(s, ((ind-1)%%9) + (lp-1)*9 + 1, 1)\n"
1270 " OR z.z = substr(s, (((ind-1)/3) %% 3) * 3\n"
1271 " + ((ind-1)/27) * 27 + lp\n"
1272 " + ((lp-1) / 3) * 6, 1)\n"
1273 " )\n"
1274 " )\n"
1275 "SELECT s FROM x WHERE ind=0;"
1276 );
1277 sqlite3_bind_text(g.pStmt, 1, zPuz, -1, SQLITE_STATIC);
1278 speedtest1_run();
1279 speedtest1_end_test();
1280
1281 speedtest1_begin_test(200, "Sudoku with VALUES 'digits'");
1282 speedtest1_prepare(
1283 "WITH RECURSIVE\n"
1284 " input(sud) AS (VALUES(?1)),\n"
1285 " digits(z,lp) AS (VALUES('1',1),('2',2),('3',3),('4',4),('5',5),\n"
1286 " ('6',6),('7',7),('8',8),('9',9)),\n"
1287 " x(s, ind) AS (\n"
1288 " SELECT sud, instr(sud, '.') FROM input\n"
1289 " UNION ALL\n"
1290 " SELECT\n"
1291 " substr(s, 1, ind-1) || z || substr(s, ind+1),\n"
1292 " instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )\n"
1293 " FROM x, digits AS z\n"
1294 " WHERE ind>0\n"
1295 " AND NOT EXISTS (\n"
1296 " SELECT 1\n"
1297 " FROM digits AS lp\n"
1298 " WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)\n"
1299 " OR z.z = substr(s, ((ind-1)%%9) + (lp-1)*9 + 1, 1)\n"
1300 " OR z.z = substr(s, (((ind-1)/3) %% 3) * 3\n"
1301 " + ((ind-1)/27) * 27 + lp\n"
1302 " + ((lp-1) / 3) * 6, 1)\n"
1303 " )\n"
1304 " )\n"
1305 "SELECT s FROM x WHERE ind=0;"
1306 );
1307 sqlite3_bind_text(g.pStmt, 1, zPuz, -1, SQLITE_STATIC);
1308 speedtest1_run();
1309 speedtest1_end_test();
1310
1311 rSpacing = 5.0/g.szTest;
1312 speedtest1_begin_test(300, "Mandelbrot Set with spacing=%f", rSpacing);
1313 speedtest1_prepare(
1314 "WITH RECURSIVE \n"
1315 " xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+?1 FROM xaxis WHERE x<1.2),\n"
1316 " yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+?2 FROM yaxis WHERE y<1.0),\n"
1317 " m(iter, cx, cy, x, y) AS (\n"
1318 " SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis\n"
1319 " UNION ALL\n"
1320 " SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m \n"
1321 " WHERE (x*x + y*y) < 4.0 AND iter<28\n"
1322 " ),\n"
1323 " m2(iter, cx, cy) AS (\n"
1324 " SELECT max(iter), cx, cy FROM m GROUP BY cx, cy\n"
1325 " ),\n"
1326 " a(t) AS (\n"
1327 " SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') \n"
1328 " FROM m2 GROUP BY cy\n"
1329 " )\n"
1330 "SELECT group_concat(rtrim(t),x'0a') FROM a;"
1331 );
1332 sqlite3_bind_double(g.pStmt, 1, rSpacing*.05);
1333 sqlite3_bind_double(g.pStmt, 2, rSpacing);
1334 speedtest1_run();
1335 speedtest1_end_test();
1336
1337 nElem = 10000*g.szTest;
1338 speedtest1_begin_test(400, "EXCEPT operator on %d-element tables", nElem);
1339 speedtest1_prepare(
1340 "WITH RECURSIVE \n"
1341 " z1(x) AS (VALUES(2) UNION ALL SELECT x+2 FROM z1 WHERE x<%d),\n"
1342 " z2(y) AS (VALUES(3) UNION ALL SELECT y+3 FROM z2 WHERE y<%d)\n"
1343 "SELECT count(x), avg(x) FROM (\n"
1344 " SELECT x FROM z1 EXCEPT SELECT y FROM z2 ORDER BY 1\n"
1345 ");",
1346 nElem, nElem
1347 );
1348 speedtest1_run();
1349 speedtest1_end_test();
1350 }
1351
1352 /*
1353 ** Compute a pseudo-random floating point ascii number.
1354 */
1355 void speedtest1_random_ascii_fp(char *zFP){
1356 int x = speedtest1_random();
1357 int y = speedtest1_random();
1358 int z;
1359 z = y%10;
1360 if( z<0 ) z = -z;
1361 y /= 10;
1362 sqlite3_snprintf(100,zFP,"%d.%de%d",y,z,x%200);
1363 }
1364
1365 /*
1366 ** A testset for floating-point numbers.
1367 */
1368 void testset_fp(void){
1369 int n;
1370 int i;
1371 char zFP1[100];
1372 char zFP2[100];
1373
1374 n = g.szTest*5000;
1375 speedtest1_begin_test(100, "Fill a table with %d FP values", n*2);
1376 speedtest1_exec("BEGIN");
1377 speedtest1_exec("CREATE%s TABLE z1(a REAL %s, b REAL %s);",
1378 isTemp(1), g.zNN, g.zNN);
1379 speedtest1_prepare("INSERT INTO z1 VALUES(?1,?2); -- %d times", n);
1380 for(i=1; i<=n; i++){
1381 speedtest1_random_ascii_fp(zFP1);
1382 speedtest1_random_ascii_fp(zFP2);
1383 sqlite3_bind_text(g.pStmt, 1, zFP1, -1, SQLITE_STATIC);
1384 sqlite3_bind_text(g.pStmt, 2, zFP2, -1, SQLITE_STATIC);
1385 speedtest1_run();
1386 }
1387 speedtest1_exec("COMMIT");
1388 speedtest1_end_test();
1389
1390 n = g.szTest/25 + 2;
1391 speedtest1_begin_test(110, "%d range queries", n);
1392 speedtest1_prepare("SELECT sum(b) FROM z1 WHERE a BETWEEN ?1 AND ?2");
1393 for(i=1; i<=n; i++){
1394 speedtest1_random_ascii_fp(zFP1);
1395 speedtest1_random_ascii_fp(zFP2);
1396 sqlite3_bind_text(g.pStmt, 1, zFP1, -1, SQLITE_STATIC);
1397 sqlite3_bind_text(g.pStmt, 2, zFP2, -1, SQLITE_STATIC);
1398 speedtest1_run();
1399 }
1400 speedtest1_end_test();
1401
1402 speedtest1_begin_test(120, "CREATE INDEX three times");
1403 speedtest1_exec("BEGIN;");
1404 speedtest1_exec("CREATE INDEX t1a ON z1(a);");
1405 speedtest1_exec("CREATE INDEX t1b ON z1(b);");
1406 speedtest1_exec("CREATE INDEX t1ab ON z1(a,b);");
1407 speedtest1_exec("COMMIT;");
1408 speedtest1_end_test();
1409
1410 n = g.szTest/3 + 2;
1411 speedtest1_begin_test(130, "%d indexed range queries", n);
1412 speedtest1_prepare("SELECT sum(b) FROM z1 WHERE a BETWEEN ?1 AND ?2");
1413 for(i=1; i<=n; i++){
1414 speedtest1_random_ascii_fp(zFP1);
1415 speedtest1_random_ascii_fp(zFP2);
1416 sqlite3_bind_text(g.pStmt, 1, zFP1, -1, SQLITE_STATIC);
1417 sqlite3_bind_text(g.pStmt, 2, zFP2, -1, SQLITE_STATIC);
1418 speedtest1_run();
1419 }
1420 speedtest1_end_test();
1421
1422 n = g.szTest*5000;
1423 speedtest1_begin_test(140, "%d calls to round()", n);
1424 speedtest1_exec("SELECT sum(round(a,2)+round(b,4)) FROM z1;");
1425 speedtest1_end_test();
1426
1427
1428 speedtest1_begin_test(150, "%d printf() calls", n*4);
1429 speedtest1_exec(
1430 "WITH c(fmt) AS (VALUES('%%g'),('%%e'),('%%!g'),('%%.20f'))"
1431 "SELECT sum(printf(fmt,a)) FROM z1, c"
1432 );
1433 speedtest1_end_test();
1434 }
1435
1436 #ifdef SQLITE_ENABLE_RTREE
1437 /* Generate two numbers between 1 and mx. The first number is less than
1438 ** the second. Usually the numbers are near each other but can sometimes
1439 ** be far apart.
1440 */
1441 static void twoCoords(
1442 int p1, int p2, /* Parameters adjusting sizes */
1443 unsigned mx, /* Range of 1..mx */
1444 unsigned *pX0, unsigned *pX1 /* OUT: write results here */
1445 ){
1446 unsigned d, x0, x1, span;
1447
1448 span = mx/100 + 1;
1449 if( speedtest1_random()%3==0 ) span *= p1;
1450 if( speedtest1_random()%p2==0 ) span = mx/2;
1451 d = speedtest1_random()%span + 1;
1452 x0 = speedtest1_random()%(mx-d) + 1;
1453 x1 = x0 + d;
1454 *pX0 = x0;
1455 *pX1 = x1;
1456 }
1457 #endif
1458
1459 #ifdef SQLITE_ENABLE_RTREE
1460 /* The following routine is an R-Tree geometry callback. It returns
1461 ** true if the object overlaps a slice on the Y coordinate between the
1462 ** two values given as arguments. In other words
1463 **
1464 ** SELECT count(*) FROM rt1 WHERE id MATCH xslice(10,20);
1465 **
1466 ** Is the same as saying:
1467 **
1468 ** SELECT count(*) FROM rt1 WHERE y1>=10 AND y0<=20;
1469 */
1470 static int xsliceGeometryCallback(
1471 sqlite3_rtree_geometry *p,
1472 int nCoord,
1473 double *aCoord,
1474 int *pRes
1475 ){
1476 *pRes = aCoord[3]>=p->aParam[0] && aCoord[2]<=p->aParam[1];
1477 return SQLITE_OK;
1478 }
1479 #endif /* SQLITE_ENABLE_RTREE */
1480
1481 #ifdef SQLITE_ENABLE_RTREE
1482 /*
1483 ** A testset for the R-Tree virtual table
1484 */
1485 void testset_rtree(int p1, int p2){
1486 unsigned i, n;
1487 unsigned mxCoord;
1488 unsigned x0, x1, y0, y1, z0, z1;
1489 unsigned iStep;
1490 unsigned mxRowid;
1491 int *aCheck = sqlite3_malloc( sizeof(int)*g.szTest*500 );
1492
1493 mxCoord = 15000;
1494 mxRowid = n = g.szTest*500;
1495 speedtest1_begin_test(100, "%d INSERTs into an r-tree", n);
1496 speedtest1_exec("BEGIN");
1497 speedtest1_exec("CREATE VIRTUAL TABLE rt1 USING rtree(id,x0,x1,y0,y1,z0,z1)");
1498 speedtest1_prepare("INSERT INTO rt1(id,x0,x1,y0,y1,z0,z1)"
1499 "VALUES(?1,?2,?3,?4,?5,?6,?7)");
1500 for(i=1; i<=n; i++){
1501 twoCoords(p1, p2, mxCoord, &x0, &x1);
1502 twoCoords(p1, p2, mxCoord, &y0, &y1);
1503 twoCoords(p1, p2, mxCoord, &z0, &z1);
1504 sqlite3_bind_int(g.pStmt, 1, i);
1505 sqlite3_bind_int(g.pStmt, 2, x0);
1506 sqlite3_bind_int(g.pStmt, 3, x1);
1507 sqlite3_bind_int(g.pStmt, 4, y0);
1508 sqlite3_bind_int(g.pStmt, 5, y1);
1509 sqlite3_bind_int(g.pStmt, 6, z0);
1510 sqlite3_bind_int(g.pStmt, 7, z1);
1511 speedtest1_run();
1512 }
1513 speedtest1_exec("COMMIT");
1514 speedtest1_end_test();
1515
1516 speedtest1_begin_test(101, "Copy from rtree to a regular table");
1517 speedtest1_exec("CREATE TABLE z1(id INTEGER PRIMARY KEY,x0,x1,y0,y1,z0,z1)");
1518 speedtest1_exec("INSERT INTO z1 SELECT * FROM rt1");
1519 speedtest1_end_test();
1520
1521 n = g.szTest*200;
1522 speedtest1_begin_test(110, "%d one-dimensional intersect slice queries", n);
1523 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x0>=?1 AND x1<=?2");
1524 iStep = mxCoord/n;
1525 for(i=0; i<n; i++){
1526 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1527 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1528 speedtest1_run();
1529 aCheck[i] = atoi(g.zResult);
1530 }
1531 speedtest1_end_test();
1532
1533 if( g.bVerify ){
1534 n = g.szTest*200;
1535 speedtest1_begin_test(111, "Verify result from 1-D intersect slice queries");
1536 speedtest1_prepare("SELECT count(*) FROM z1 WHERE x0>=?1 AND x1<=?2");
1537 iStep = mxCoord/n;
1538 for(i=0; i<n; i++){
1539 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1540 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1541 speedtest1_run();
1542 if( aCheck[i]!=atoi(g.zResult) ){
1543 fatal_error("Count disagree step %d: %d..%d. %d vs %d",
1544 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
1545 }
1546 }
1547 speedtest1_end_test();
1548 }
1549
1550 n = g.szTest*200;
1551 speedtest1_begin_test(120, "%d one-dimensional overlap slice queries", n);
1552 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE y1>=?1 AND y0<=?2");
1553 iStep = mxCoord/n;
1554 for(i=0; i<n; i++){
1555 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1556 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1557 speedtest1_run();
1558 aCheck[i] = atoi(g.zResult);
1559 }
1560 speedtest1_end_test();
1561
1562 if( g.bVerify ){
1563 n = g.szTest*200;
1564 speedtest1_begin_test(121, "Verify result from 1-D overlap slice queries");
1565 speedtest1_prepare("SELECT count(*) FROM z1 WHERE y1>=?1 AND y0<=?2");
1566 iStep = mxCoord/n;
1567 for(i=0; i<n; i++){
1568 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1569 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1570 speedtest1_run();
1571 if( aCheck[i]!=atoi(g.zResult) ){
1572 fatal_error("Count disagree step %d: %d..%d. %d vs %d",
1573 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
1574 }
1575 }
1576 speedtest1_end_test();
1577 }
1578
1579
1580 n = g.szTest*200;
1581 speedtest1_begin_test(125, "%d custom geometry callback queries", n);
1582 sqlite3_rtree_geometry_callback(g.db, "xslice", xsliceGeometryCallback, 0);
1583 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE id MATCH xslice(?1,?2)");
1584 iStep = mxCoord/n;
1585 for(i=0; i<n; i++){
1586 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1587 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1588 speedtest1_run();
1589 if( aCheck[i]!=atoi(g.zResult) ){
1590 fatal_error("Count disagree step %d: %d..%d. %d vs %d",
1591 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
1592 }
1593 }
1594 speedtest1_end_test();
1595
1596 n = g.szTest*400;
1597 speedtest1_begin_test(130, "%d three-dimensional intersect box queries", n);
1598 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x1>=?1 AND x0<=?2"
1599 " AND y1>=?1 AND y0<=?2 AND z1>=?1 AND z0<=?2");
1600 iStep = mxCoord/n;
1601 for(i=0; i<n; i++){
1602 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1603 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1604 speedtest1_run();
1605 aCheck[i] = atoi(g.zResult);
1606 }
1607 speedtest1_end_test();
1608
1609 n = g.szTest*500;
1610 speedtest1_begin_test(140, "%d rowid queries", n);
1611 speedtest1_prepare("SELECT * FROM rt1 WHERE id=?1");
1612 for(i=1; i<=n; i++){
1613 sqlite3_bind_int(g.pStmt, 1, i);
1614 speedtest1_run();
1615 }
1616 speedtest1_end_test();
1617
1618 n = g.szTest*50;
1619 speedtest1_begin_test(150, "%d UPDATEs using rowid", n);
1620 speedtest1_prepare("UPDATE rt1 SET x0=x0+100, x1=x1+100 WHERE id=?1");
1621 for(i=1; i<=n; i++){
1622 sqlite3_bind_int(g.pStmt, 1, (i*251)%mxRowid + 1);
1623 speedtest1_run();
1624 }
1625 speedtest1_end_test();
1626
1627 n = g.szTest*5;
1628 speedtest1_begin_test(155, "%d UPDATEs using one-dimensional overlap", n);
1629 speedtest1_prepare("UPDATE rt1 SET x0=x0-100, x1=x1-100"
1630 " WHERE y1>=?1 AND y0<=?1+5");
1631 iStep = mxCoord/n;
1632 for(i=0; i<n; i++){
1633 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1634 speedtest1_run();
1635 aCheck[i] = atoi(g.zResult);
1636 }
1637 speedtest1_end_test();
1638
1639 n = g.szTest*50;
1640 speedtest1_begin_test(160, "%d DELETEs using rowid", n);
1641 speedtest1_prepare("DELETE FROM rt1 WHERE id=?1");
1642 for(i=1; i<=n; i++){
1643 sqlite3_bind_int(g.pStmt, 1, (i*257)%mxRowid + 1);
1644 speedtest1_run();
1645 }
1646 speedtest1_end_test();
1647
1648
1649 n = g.szTest*5;
1650 speedtest1_begin_test(165, "%d DELETEs using one-dimensional overlap", n);
1651 speedtest1_prepare("DELETE FROM rt1 WHERE y1>=?1 AND y0<=?1+5");
1652 iStep = mxCoord/n;
1653 for(i=0; i<n; i++){
1654 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1655 speedtest1_run();
1656 aCheck[i] = atoi(g.zResult);
1657 }
1658 speedtest1_end_test();
1659
1660 speedtest1_begin_test(170, "Restore deleted entries using INSERT OR IGNORE");
1661 speedtest1_exec("INSERT OR IGNORE INTO rt1 SELECT * FROM z1");
1662 speedtest1_end_test();
1663 }
1664 #endif /* SQLITE_ENABLE_RTREE */
1665
1666 /*
1667 ** A testset that does key/value storage on tables with many columns.
1668 ** This is the kind of workload generated by ORMs such as CoreData.
1669 */
1670 void testset_orm(void){
1671 unsigned i, j, n;
1672 unsigned nRow;
1673 unsigned x1, len;
1674 char zNum[2000]; /* A number name */
1675 static const char zType[] = /* Types for all non-PK columns, in order */
1676 "IBBIIITIVVITBTBFBFITTFBTBVBVIFTBBFITFFVBIFIVBVVVBTVTIBBFFIVIBTB"
1677 "TVTTFTVTVFFIITIFBITFTTFFFVBIIBTTITFTFFVVVFIIITVBBVFFTVVB";
1678
1679 nRow = n = g.szTest*250;
1680 speedtest1_begin_test(100, "Fill %d rows", n);
1681 speedtest1_exec(
1682 "BEGIN;"
1683 "CREATE TABLE ZLOOKSLIKECOREDATA ("
1684 " ZPK INTEGER PRIMARY KEY,"
1685 " ZTERMFITTINGHOUSINGCOMMAND INTEGER,"
1686 " ZBRIEFGOBYDODGERHEIGHT BLOB,"
1687 " ZCAPABLETRIPDOORALMOND BLOB,"
1688 " ZDEPOSITPAIRCOLLEGECOMET INTEGER,"
1689 " ZFRAMEENTERSIMPLEMOUTH INTEGER,"
1690 " ZHOPEFULGATEHOLECHALK INTEGER,"
1691 " ZSLEEPYUSERGRANDBOWL TIMESTAMP,"
1692 " ZDEWPEACHCAREERCELERY INTEGER,"
1693 " ZHANGERLITHIUMDINNERMEET VARCHAR,"
1694 " ZCLUBRELEASELIZARDADVICE VARCHAR,"
1695 " ZCHARGECLICKHUMANEHIRE INTEGER,"
1696 " ZFINGERDUEPIZZAOPTION TIMESTAMP,"
1697 " ZFLYINGDOCTORTABLEMELODY BLOB,"
1698 " ZLONGFINLEAVEIMAGEOIL TIMESTAMP,"
1699 " ZFAMILYVISUALOWNERMATTER BLOB,"
1700 " ZGOLDYOUNGINITIALNOSE FLOAT,"
1701 " ZCAUSESALAMITERMCYAN BLOB,"
1702 " ZSPREADMOTORBISCUITBACON FLOAT,"
1703 " ZGIFTICEFISHGLUEHAIR INTEGER,"
1704 " ZNOTICEPEARPOLICYJUICE TIMESTAMP,"
1705 " ZBANKBUFFALORECOVERORBIT TIMESTAMP,"
1706 " ZLONGDIETESSAYNATURE FLOAT,"
1707 " ZACTIONRANGEELEGANTNEUTRON BLOB,"
1708 " ZCADETBRIGHTPLANETBANK TIMESTAMP,"
1709 " ZAIRFORGIVEHEADFROG BLOB,"
1710 " ZSHARKJUSTFRUITMOVIE VARCHAR,"
1711 " ZFARMERMORNINGMIRRORCONCERN BLOB,"
1712 " ZWOODPOETRYCOBBLERBENCH VARCHAR,"
1713 " ZHAFNIUMSCRIPTSALADMOTOR INTEGER,"
1714 " ZPROBLEMCLUBPOPOVERJELLY FLOAT,"
1715 " ZEIGHTLEADERWORKERMOST TIMESTAMP,"
1716 " ZGLASSRESERVEBARIUMMEAL BLOB,"
1717 " ZCLAMBITARUGULAFAJITA BLOB,"
1718 " ZDECADEJOYOUSWAVEHABIT FLOAT,"
1719 " ZCOMPANYSUMMERFIBERELF INTEGER,"
1720 " ZTREATTESTQUILLCHARGE TIMESTAMP,"
1721 " ZBROWBALANCEKEYCHOWDER FLOAT,"
1722 " ZPEACHCOPPERDINNERLAKE FLOAT,"
1723 " ZDRYWALLBEYONDBROWNBOWL VARCHAR,"
1724 " ZBELLYCRASHITEMLACK BLOB,"
1725 " ZTENNISCYCLEBILLOFFICER INTEGER,"
1726 " ZMALLEQUIPTHANKSGLUE FLOAT,"
1727 " ZMISSREPLYHUMANLIVING INTEGER,"
1728 " ZKIWIVISUALPRIDEAPPLE VARCHAR,"
1729 " ZWISHHITSKINMOTOR BLOB,"
1730 " ZCALMRACCOONPROGRAMDEBIT VARCHAR,"
1731 " ZSHINYASSISTLIVINGCRAB VARCHAR,"
1732 " ZRESOLVEWRISTWRAPAPPLE VARCHAR,"
1733 " ZAPPEALSIMPLESECONDHOUSING BLOB,"
1734 " ZCORNERANCHORTAPEDIVER TIMESTAMP,"
1735 " ZMEMORYREQUESTSOURCEBIG VARCHAR,"
1736 " ZTRYFACTKEEPMILK TIMESTAMP,"
1737 " ZDIVERPAINTLEATHEREASY INTEGER,"
1738 " ZSORTMISTYQUOTECABBAGE BLOB,"
1739 " ZTUNEGASBUFFALOCAPITAL BLOB,"
1740 " ZFILLSTOPLAWJOYFUL FLOAT,"
1741 " ZSTEELCAREFULPLATENUMBER FLOAT,"
1742 " ZGIVEVIVIDDIVINEMEANING INTEGER,"
1743 " ZTREATPACKFUTURECONVERT VARCHAR,"
1744 " ZCALMLYGEMFINISHEFFECT INTEGER,"
1745 " ZCABBAGESOCKEASEMINUTE BLOB,"
1746 " ZPLANETFAMILYPUREMEMORY TIMESTAMP,"
1747 " ZMERRYCRACKTRAINLEADER BLOB,"
1748 " ZMINORWAYPAPERCLASSY TIMESTAMP,"
1749 " ZEAGLELINEMINEMAIL VARCHAR,"
1750 " ZRESORTYARDGREENLET TIMESTAMP,"
1751 " ZYARDOREGANOVIVIDJEWEL TIMESTAMP,"
1752 " ZPURECAKEVIVIDNEATLY FLOAT,"
1753 " ZASKCONTACTMONITORFUN TIMESTAMP,"
1754 " ZMOVEWHOGAMMAINCH VARCHAR,"
1755 " ZLETTUCEBIRDMEETDEBATE TIMESTAMP,"
1756 " ZGENENATURALHEARINGKITE VARCHAR,"
1757 " ZMUFFINDRYERDRAWFORTUNE FLOAT,"
1758 " ZGRAYSURVEYWIRELOVE FLOAT,"
1759 " ZPLIERSPRINTASKOREGANO INTEGER,"
1760 " ZTRAVELDRIVERCONTESTLILY INTEGER,"
1761 " ZHUMORSPICESANDKIDNEY TIMESTAMP,"
1762 " ZARSENICSAMPLEWAITMUON INTEGER,"
1763 " ZLACEADDRESSGROUNDCAREFUL FLOAT,"
1764 " ZBAMBOOMESSWASABIEVENING BLOB,"
1765 " ZONERELEASEAVERAGENURSE INTEGER,"
1766 " ZRADIANTWHENTRYCARD TIMESTAMP,"
1767 " ZREWARDINSIDEMANGOINTENSE FLOAT,"
1768 " ZNEATSTEWPARTIRON TIMESTAMP,"
1769 " ZOUTSIDEPEAHENCOUNTICE TIMESTAMP,"
1770 " ZCREAMEVENINGLIPBRANCH FLOAT,"
1771 " ZWHALEMATHAVOCADOCOPPER FLOAT,"
1772 " ZLIFEUSELEAFYBELL FLOAT,"
1773 " ZWEALTHLINENGLEEFULDAY VARCHAR,"
1774 " ZFACEINVITETALKGOLD BLOB,"
1775 " ZWESTAMOUNTAFFECTHEARING INTEGER,"
1776 " ZDELAYOUTCOMEHORNAGENCY INTEGER,"
1777 " ZBIGTHINKCONVERTECONOMY BLOB,"
1778 " ZBASEGOUDAREGULARFORGIVE TIMESTAMP,"
1779 " ZPATTERNCLORINEGRANDCOLBY TIMESTAMP,"
1780 " ZCYANBASEFEEDADROIT INTEGER,"
1781 " ZCARRYFLOORMINNOWDRAGON TIMESTAMP,"
1782 " ZIMAGEPENCILOTHERBOTTOM FLOAT,"
1783 " ZXENONFLIGHTPALEAPPLE TIMESTAMP,"
1784 " ZHERRINGJOKEFEATUREHOPEFUL FLOAT,"
1785 " ZCAPYEARLYRIVETBRUSH FLOAT,"
1786 " ZAGEREEDFROGBASKET VARCHAR,"
1787 " ZUSUALBODYHALIBUTDIAMOND VARCHAR,"
1788 " ZFOOTTAPWORDENTRY VARCHAR,"
1789 " ZDISHKEEPBLESTMONITOR FLOAT,"
1790 " ZBROADABLESOLIDCASUAL INTEGER,"
1791 " ZSQUAREGLEEFULCHILDLIGHT INTEGER,"
1792 " ZHOLIDAYHEADPONYDETAIL INTEGER,"
1793 " ZGENERALRESORTSKYOPEN TIMESTAMP,"
1794 " ZGLADSPRAYKIDNEYGUPPY VARCHAR,"
1795 " ZSWIMHEAVYMENTIONKIND BLOB,"
1796 " ZMESSYSULFURDREAMFESTIVE BLOB,"
1797 " ZSKYSKYCLASSICBRIEF VARCHAR,"
1798 " ZDILLASKHOKILEMON FLOAT,"
1799 " ZJUNIORSHOWPRESSNOVA FLOAT,"
1800 " ZSIZETOEAWARDFRESH TIMESTAMP,"
1801 " ZKEYFAILAPRICOTMETAL VARCHAR,"
1802 " ZHANDYREPAIRPROTONAIRPORT VARCHAR,"
1803 " ZPOSTPROTEINHANDLEACTOR BLOB"
1804 ");"
1805 );
1806 speedtest1_prepare(
1807 "INSERT INTO ZLOOKSLIKECOREDATA(ZPK,ZAIRFORGIVEHEADFROG,"
1808 "ZGIFTICEFISHGLUEHAIR,ZDELAYOUTCOMEHORNAGENCY,ZSLEEPYUSERGRANDBOWL,"
1809 "ZGLASSRESERVEBARIUMMEAL,ZBRIEFGOBYDODGERHEIGHT,"
1810 "ZBAMBOOMESSWASABIEVENING,ZFARMERMORNINGMIRRORCONCERN,"
1811 "ZTREATPACKFUTURECONVERT,ZCAUSESALAMITERMCYAN,ZCALMRACCOONPROGRAMDEBIT,"
1812 "ZHOLIDAYHEADPONYDETAIL,ZWOODPOETRYCOBBLERBENCH,ZHAFNIUMSCRIPTSALADMOTOR,"
1813 "ZUSUALBODYHALIBUTDIAMOND,ZOUTSIDEPEAHENCOUNTICE,ZDIVERPAINTLEATHEREASY,"
1814 "ZWESTAMOUNTAFFECTHEARING,ZSIZETOEAWARDFRESH,ZDEWPEACHCAREERCELERY,"
1815 "ZSTEELCAREFULPLATENUMBER,ZCYANBASEFEEDADROIT,ZCALMLYGEMFINISHEFFECT,"
1816 "ZHANDYREPAIRPROTONAIRPORT,ZGENENATURALHEARINGKITE,ZBROADABLESOLIDCASUAL,"
1817 "ZPOSTPROTEINHANDLEACTOR,ZLACEADDRESSGROUNDCAREFUL,ZIMAGEPENCILOTHERBOTTOM,"
1818 "ZPROBLEMCLUBPOPOVERJELLY,ZPATTERNCLORINEGRANDCOLBY,ZNEATSTEWPARTIRON,"
1819 "ZAPPEALSIMPLESECONDHOUSING,ZMOVEWHOGAMMAINCH,ZTENNISCYCLEBILLOFFICER,"
1820 "ZSHARKJUSTFRUITMOVIE,ZKEYFAILAPRICOTMETAL,ZCOMPANYSUMMERFIBERELF,"
1821 "ZTERMFITTINGHOUSINGCOMMAND,ZRESORTYARDGREENLET,ZCABBAGESOCKEASEMINUTE,"
1822 "ZSQUAREGLEEFULCHILDLIGHT,ZONERELEASEAVERAGENURSE,ZBIGTHINKCONVERTECONOMY,"
1823 "ZPLIERSPRINTASKOREGANO,ZDECADEJOYOUSWAVEHABIT,ZDRYWALLBEYONDBROWNBOWL,"
1824 "ZCLUBRELEASELIZARDADVICE,ZWHALEMATHAVOCADOCOPPER,ZBELLYCRASHITEMLACK,"
1825 "ZLETTUCEBIRDMEETDEBATE,ZCAPABLETRIPDOORALMOND,ZRADIANTWHENTRYCARD,"
1826 "ZCAPYEARLYRIVETBRUSH,ZAGEREEDFROGBASKET,ZSWIMHEAVYMENTIONKIND,"
1827 "ZTRAVELDRIVERCONTESTLILY,ZGLADSPRAYKIDNEYGUPPY,ZBANKBUFFALORECOVERORBIT,"
1828 "ZFINGERDUEPIZZAOPTION,ZCLAMBITARUGULAFAJITA,ZLONGFINLEAVEIMAGEOIL,"
1829 "ZLONGDIETESSAYNATURE,ZJUNIORSHOWPRESSNOVA,ZHOPEFULGATEHOLECHALK,"
1830 "ZDEPOSITPAIRCOLLEGECOMET,ZWEALTHLINENGLEEFULDAY,ZFILLSTOPLAWJOYFUL,"
1831 "ZTUNEGASBUFFALOCAPITAL,ZGRAYSURVEYWIRELOVE,ZCORNERANCHORTAPEDIVER,"
1832 "ZREWARDINSIDEMANGOINTENSE,ZCADETBRIGHTPLANETBANK,ZPLANETFAMILYPUREMEMORY,"
1833 "ZTREATTESTQUILLCHARGE,ZCREAMEVENINGLIPBRANCH,ZSKYSKYCLASSICBRIEF,"
1834 "ZARSENICSAMPLEWAITMUON,ZBROWBALANCEKEYCHOWDER,ZFLYINGDOCTORTABLEMELODY,"
1835 "ZHANGERLITHIUMDINNERMEET,ZNOTICEPEARPOLICYJUICE,ZSHINYASSISTLIVINGCRAB,"
1836 "ZLIFEUSELEAFYBELL,ZFACEINVITETALKGOLD,ZGENERALRESORTSKYOPEN,"
1837 "ZPURECAKEVIVIDNEATLY,ZKIWIVISUALPRIDEAPPLE,ZMESSYSULFURDREAMFESTIVE,"
1838 "ZCHARGECLICKHUMANEHIRE,ZHERRINGJOKEFEATUREHOPEFUL,ZYARDOREGANOVIVIDJEWEL,"
1839 "ZFOOTTAPWORDENTRY,ZWISHHITSKINMOTOR,ZBASEGOUDAREGULARFORGIVE,"
1840 "ZMUFFINDRYERDRAWFORTUNE,ZACTIONRANGEELEGANTNEUTRON,ZTRYFACTKEEPMILK,"
1841 "ZPEACHCOPPERDINNERLAKE,ZFRAMEENTERSIMPLEMOUTH,ZMERRYCRACKTRAINLEADER,"
1842 "ZMEMORYREQUESTSOURCEBIG,ZCARRYFLOORMINNOWDRAGON,ZMINORWAYPAPERCLASSY,"
1843 "ZDILLASKHOKILEMON,ZRESOLVEWRISTWRAPAPPLE,ZASKCONTACTMONITORFUN,"
1844 "ZGIVEVIVIDDIVINEMEANING,ZEIGHTLEADERWORKERMOST,ZMISSREPLYHUMANLIVING,"
1845 "ZXENONFLIGHTPALEAPPLE,ZSORTMISTYQUOTECABBAGE,ZEAGLELINEMINEMAIL,"
1846 "ZFAMILYVISUALOWNERMATTER,ZSPREADMOTORBISCUITBACON,ZDISHKEEPBLESTMONITOR,"
1847 "ZMALLEQUIPTHANKSGLUE,ZGOLDYOUNGINITIALNOSE,ZHUMORSPICESANDKIDNEY)"
1848 "VALUES(?1,?26,?20,?93,?8,?33,?3,?81,?28,?60,?18,?47,?109,?29,?30,?104,?86,"
1849 "?54,?92,?117,?9,?58,?97,?61,?119,?73,?107,?120,?80,?99,?31,?96,?85,?50,?71,"
1850 "?42,?27,?118,?36,?2,?67,?62,?108,?82,?94,?76,?35,?40,?11,?88,?41,?72,?4,"
1851 "?83,?102,?103,?112,?77,?111,?22,?13,?34,?15,?23,?116,?7,?5,?90,?57,?56,"
1852 "?75,?51,?84,?25,?63,?37,?87,?114,?79,?38,?14,?10,?21,?48,?89,?91,?110,"
1853 "?69,?45,?113,?12,?101,?68,?105,?46,?95,?74,?24,?53,?39,?6,?64,?52,?98,"
1854 "?65,?115,?49,?70,?59,?32,?44,?100,?55,?66,?16,?19,?106,?43,?17,?78);"
1855 );
1856 for(i=0; i<n; i++){
1857 x1 = speedtest1_random();
1858 speedtest1_numbername(x1%1000, zNum, sizeof(zNum));
1859 len = (int)strlen(zNum);
1860 sqlite3_bind_int(g.pStmt, 1, i^0xf);
1861 for(j=0; zType[j]; j++){
1862 switch( zType[j] ){
1863 case 'I':
1864 case 'T':
1865 sqlite3_bind_int64(g.pStmt, j+2, x1);
1866 break;
1867 case 'F':
1868 sqlite3_bind_double(g.pStmt, j+2, (double)x1);
1869 break;
1870 case 'V':
1871 case 'B':
1872 sqlite3_bind_text64(g.pStmt, j+2, zNum, len,
1873 SQLITE_STATIC, SQLITE_UTF8);
1874 break;
1875 }
1876 }
1877 speedtest1_run();
1878 }
1879 speedtest1_exec("COMMIT;");
1880 speedtest1_end_test();
1881
1882 n = g.szTest*250;
1883 speedtest1_begin_test(110, "Query %d rows by rowid", n);
1884 speedtest1_prepare(
1885 "SELECT ZCYANBASEFEEDADROIT,ZJUNIORSHOWPRESSNOVA,ZCAUSESALAMITERMCYAN,"
1886 "ZHOPEFULGATEHOLECHALK,ZHUMORSPICESANDKIDNEY,ZSWIMHEAVYMENTIONKIND,"
1887 "ZMOVEWHOGAMMAINCH,ZAPPEALSIMPLESECONDHOUSING,ZHAFNIUMSCRIPTSALADMOTOR,"
1888 "ZNEATSTEWPARTIRON,ZLONGFINLEAVEIMAGEOIL,ZDEWPEACHCAREERCELERY,"
1889 "ZXENONFLIGHTPALEAPPLE,ZCALMRACCOONPROGRAMDEBIT,ZUSUALBODYHALIBUTDIAMOND,"
1890 "ZTRYFACTKEEPMILK,ZWEALTHLINENGLEEFULDAY,ZLONGDIETESSAYNATURE,"
1891 "ZLIFEUSELEAFYBELL,ZTREATPACKFUTURECONVERT,ZMEMORYREQUESTSOURCEBIG,"
1892 "ZYARDOREGANOVIVIDJEWEL,ZDEPOSITPAIRCOLLEGECOMET,ZSLEEPYUSERGRANDBOWL,"
1893 "ZBRIEFGOBYDODGERHEIGHT,ZCLUBRELEASELIZARDADVICE,ZCAPABLETRIPDOORALMOND,"
1894 "ZDRYWALLBEYONDBROWNBOWL,ZASKCONTACTMONITORFUN,ZKIWIVISUALPRIDEAPPLE,"
1895 "ZNOTICEPEARPOLICYJUICE,ZPEACHCOPPERDINNERLAKE,ZSTEELCAREFULPLATENUMBER,"
1896 "ZGLADSPRAYKIDNEYGUPPY,ZCOMPANYSUMMERFIBERELF,ZTENNISCYCLEBILLOFFICER,"
1897 "ZIMAGEPENCILOTHERBOTTOM,ZWESTAMOUNTAFFECTHEARING,ZDIVERPAINTLEATHEREASY,"
1898 "ZSKYSKYCLASSICBRIEF,ZMESSYSULFURDREAMFESTIVE,ZMERRYCRACKTRAINLEADER,"
1899 "ZBROADABLESOLIDCASUAL,ZGLASSRESERVEBARIUMMEAL,ZTUNEGASBUFFALOCAPITAL,"
1900 "ZBANKBUFFALORECOVERORBIT,ZTREATTESTQUILLCHARGE,ZBAMBOOMESSWASABIEVENING,"
1901 "ZREWARDINSIDEMANGOINTENSE,ZEAGLELINEMINEMAIL,ZCALMLYGEMFINISHEFFECT,"
1902 "ZKEYFAILAPRICOTMETAL,ZFINGERDUEPIZZAOPTION,ZCADETBRIGHTPLANETBANK,"
1903 "ZGOLDYOUNGINITIALNOSE,ZMISSREPLYHUMANLIVING,ZEIGHTLEADERWORKERMOST,"
1904 "ZFRAMEENTERSIMPLEMOUTH,ZBIGTHINKCONVERTECONOMY,ZFACEINVITETALKGOLD,"
1905 "ZPOSTPROTEINHANDLEACTOR,ZHERRINGJOKEFEATUREHOPEFUL,ZCABBAGESOCKEASEMINUTE,"
1906 "ZMUFFINDRYERDRAWFORTUNE,ZPROBLEMCLUBPOPOVERJELLY,ZGIVEVIVIDDIVINEMEANING,"
1907 "ZGENENATURALHEARINGKITE,ZGENERALRESORTSKYOPEN,ZLETTUCEBIRDMEETDEBATE,"
1908 "ZBASEGOUDAREGULARFORGIVE,ZCHARGECLICKHUMANEHIRE,ZPLANETFAMILYPUREMEMORY,"
1909 "ZMINORWAYPAPERCLASSY,ZCAPYEARLYRIVETBRUSH,ZSIZETOEAWARDFRESH,"
1910 "ZARSENICSAMPLEWAITMUON,ZSQUAREGLEEFULCHILDLIGHT,ZSHINYASSISTLIVINGCRAB,"
1911 "ZCORNERANCHORTAPEDIVER,ZDECADEJOYOUSWAVEHABIT,ZTRAVELDRIVERCONTESTLILY,"
1912 "ZFLYINGDOCTORTABLEMELODY,ZSHARKJUSTFRUITMOVIE,ZFAMILYVISUALOWNERMATTER,"
1913 "ZFARMERMORNINGMIRRORCONCERN,ZGIFTICEFISHGLUEHAIR,ZOUTSIDEPEAHENCOUNTICE,"
1914 "ZSPREADMOTORBISCUITBACON,ZWISHHITSKINMOTOR,ZHOLIDAYHEADPONYDETAIL,"
1915 "ZWOODPOETRYCOBBLERBENCH,ZAIRFORGIVEHEADFROG,ZBROWBALANCEKEYCHOWDER,"
1916 "ZDISHKEEPBLESTMONITOR,ZCLAMBITARUGULAFAJITA,ZPLIERSPRINTASKOREGANO,"
1917 "ZRADIANTWHENTRYCARD,ZDELAYOUTCOMEHORNAGENCY,ZPURECAKEVIVIDNEATLY,"
1918 "ZPATTERNCLORINEGRANDCOLBY,ZHANDYREPAIRPROTONAIRPORT,ZAGEREEDFROGBASKET,"
1919 "ZSORTMISTYQUOTECABBAGE,ZFOOTTAPWORDENTRY,ZRESOLVEWRISTWRAPAPPLE,"
1920 "ZDILLASKHOKILEMON,ZFILLSTOPLAWJOYFUL,ZACTIONRANGEELEGANTNEUTRON,"
1921 "ZRESORTYARDGREENLET,ZCREAMEVENINGLIPBRANCH,ZWHALEMATHAVOCADOCOPPER,"
1922 "ZGRAYSURVEYWIRELOVE,ZBELLYCRASHITEMLACK,ZHANGERLITHIUMDINNERMEET,"
1923 "ZCARRYFLOORMINNOWDRAGON,ZMALLEQUIPTHANKSGLUE,ZTERMFITTINGHOUSINGCOMMAND,"
1924 "ZONERELEASEAVERAGENURSE,ZLACEADDRESSGROUNDCAREFUL"
1925 " FROM ZLOOKSLIKECOREDATA WHERE ZPK=?1;"
1926 );
1927 for(i=0; i<n; i++){
1928 x1 = speedtest1_random()%nRow;
1929 sqlite3_bind_int(g.pStmt, 1, x1);
1930 speedtest1_run();
1931 }
1932 speedtest1_end_test();
1933 }
1934
1935 /*
1936 */
1937 void testset_trigger(void){
1938 int jj, ii;
1939 char zNum[2000]; /* A number name */
1940
1941 const int NROW = 500*g.szTest;
1942 const int NROW2 = 100*g.szTest;
1943
1944 speedtest1_exec(
1945 "BEGIN;"
1946 "CREATE TABLE z1(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
1947 "CREATE TABLE z2(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
1948 "CREATE TABLE t3(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
1949 "CREATE VIEW v1 AS SELECT rowid, i, t FROM z1;"
1950 "CREATE VIEW v2 AS SELECT rowid, i, t FROM z2;"
1951 "CREATE VIEW v3 AS SELECT rowid, i, t FROM t3;"
1952 );
1953 for(jj=1; jj<=3; jj++){
1954 speedtest1_prepare("INSERT INTO t%d VALUES(NULL,?1,?2)", jj);
1955 for(ii=0; ii<NROW; ii++){
1956 int x1 = speedtest1_random() % NROW;
1957 speedtest1_numbername(x1, zNum, sizeof(zNum));
1958 sqlite3_bind_int(g.pStmt, 1, x1);
1959 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
1960 speedtest1_run();
1961 }
1962 }
1963 speedtest1_exec(
1964 "CREATE INDEX i1 ON z1(t);"
1965 "CREATE INDEX i2 ON z2(t);"
1966 "CREATE INDEX i3 ON t3(t);"
1967 "COMMIT;"
1968 );
1969
1970 speedtest1_begin_test(100, "speed4p-join1");
1971 speedtest1_prepare(
1972 "SELECT * FROM z1, z2, t3 WHERE z1.oid = z2.oid AND z2.oid = t3.oid"
1973 );
1974 speedtest1_run();
1975 speedtest1_end_test();
1976
1977 speedtest1_begin_test(110, "speed4p-join2");
1978 speedtest1_prepare(
1979 "SELECT * FROM z1, z2, t3 WHERE z1.t = z2.t AND z2.t = t3.t"
1980 );
1981 speedtest1_run();
1982 speedtest1_end_test();
1983
1984 speedtest1_begin_test(120, "speed4p-view1");
1985 for(jj=1; jj<=3; jj++){
1986 speedtest1_prepare("SELECT * FROM v%d WHERE rowid = ?", jj);
1987 for(ii=0; ii<NROW2; ii+=3){
1988 sqlite3_bind_int(g.pStmt, 1, ii*3);
1989 speedtest1_run();
1990 }
1991 }
1992 speedtest1_end_test();
1993
1994 speedtest1_begin_test(130, "speed4p-table1");
1995 for(jj=1; jj<=3; jj++){
1996 speedtest1_prepare("SELECT * FROM t%d WHERE rowid = ?", jj);
1997 for(ii=0; ii<NROW2; ii+=3){
1998 sqlite3_bind_int(g.pStmt, 1, ii*3);
1999 speedtest1_run();
2000 }
2001 }
2002 speedtest1_end_test();
2003
2004 speedtest1_begin_test(140, "speed4p-table1");
2005 for(jj=1; jj<=3; jj++){
2006 speedtest1_prepare("SELECT * FROM t%d WHERE rowid = ?", jj);
2007 for(ii=0; ii<NROW2; ii+=3){
2008 sqlite3_bind_int(g.pStmt, 1, ii*3);
2009 speedtest1_run();
2010 }
2011 }
2012 speedtest1_end_test();
2013
2014 speedtest1_begin_test(150, "speed4p-subselect1");
2015 speedtest1_prepare("SELECT "
2016 "(SELECT t FROM z1 WHERE rowid = ?1),"
2017 "(SELECT t FROM z2 WHERE rowid = ?1),"
2018 "(SELECT t FROM t3 WHERE rowid = ?1)"
2019 );
2020 for(jj=0; jj<NROW2; jj++){
2021 sqlite3_bind_int(g.pStmt, 1, jj*3);
2022 speedtest1_run();
2023 }
2024 speedtest1_end_test();
2025
2026 speedtest1_begin_test(160, "speed4p-rowid-update");
2027 speedtest1_exec("BEGIN");
2028 speedtest1_prepare("UPDATE z1 SET i=i+1 WHERE rowid=?1");
2029 for(jj=0; jj<NROW2; jj++){
2030 sqlite3_bind_int(g.pStmt, 1, jj);
2031 speedtest1_run();
2032 }
2033 speedtest1_exec("COMMIT");
2034 speedtest1_end_test();
2035
2036 speedtest1_exec("CREATE TABLE t5(t TEXT PRIMARY KEY, i INTEGER);");
2037 speedtest1_begin_test(170, "speed4p-insert-ignore");
2038 speedtest1_exec("INSERT OR IGNORE INTO t5 SELECT t, i FROM z1");
2039 speedtest1_end_test();
2040
2041 speedtest1_exec(
2042 "CREATE TABLE log(op TEXT, r INTEGER, i INTEGER, t TEXT);"
2043 "CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
2044 "CREATE TRIGGER t4_trigger1 AFTER INSERT ON t4 BEGIN"
2045 " INSERT INTO log VALUES('INSERT INTO t4', new.rowid, new.i, new.t);"
2046 "END;"
2047 "CREATE TRIGGER t4_trigger2 AFTER UPDATE ON t4 BEGIN"
2048 " INSERT INTO log VALUES('UPDATE OF t4', new.rowid, new.i, new.t);"
2049 "END;"
2050 "CREATE TRIGGER t4_trigger3 AFTER DELETE ON t4 BEGIN"
2051 " INSERT INTO log VALUES('DELETE OF t4', old.rowid, old.i, old.t);"
2052 "END;"
2053 "BEGIN;"
2054 );
2055
2056 speedtest1_begin_test(180, "speed4p-trigger1");
2057 speedtest1_prepare("INSERT INTO t4 VALUES(NULL, ?1, ?2)");
2058 for(jj=0; jj<NROW2; jj++){
2059 speedtest1_numbername(jj, zNum, sizeof(zNum));
2060 sqlite3_bind_int(g.pStmt, 1, jj);
2061 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
2062 speedtest1_run();
2063 }
2064 speedtest1_end_test();
2065
2066 /*
2067 ** Note: Of the queries, only half actually update a row. This property
2068 ** was copied over from speed4p.test, where it was probably introduced
2069 ** inadvertantly.
2070 */
2071 speedtest1_begin_test(190, "speed4p-trigger2");
2072 speedtest1_prepare("UPDATE t4 SET i = ?1, t = ?2 WHERE rowid = ?3");
2073 for(jj=1; jj<=NROW2*2; jj+=2){
2074 speedtest1_numbername(jj*2, zNum, sizeof(zNum));
2075 sqlite3_bind_int(g.pStmt, 1, jj*2);
2076 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
2077 sqlite3_bind_int(g.pStmt, 3, jj);
2078 speedtest1_run();
2079 }
2080 speedtest1_end_test();
2081
2082 /*
2083 ** Note: Same again.
2084 */
2085 speedtest1_begin_test(200, "speed4p-trigger3");
2086 speedtest1_prepare("DELETE FROM t4 WHERE rowid = ?1");
2087 for(jj=1; jj<=NROW2*2; jj+=2){
2088 sqlite3_bind_int(g.pStmt, 1, jj*2);
2089 speedtest1_run();
2090 }
2091 speedtest1_end_test();
2092 speedtest1_exec("COMMIT");
2093
2094 /*
2095 ** The following block contains the same tests as the above block that
2096 ** tests triggers, with one crucial difference: no triggers are defined.
2097 ** So the difference in speed between these tests and the preceding ones
2098 ** is the amount of time taken to compile and execute the trigger programs.
2099 */
2100 speedtest1_exec(
2101 "DROP TABLE t4;"
2102 "DROP TABLE log;"
2103 "VACUUM;"
2104 "CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
2105 "BEGIN;"
2106 );
2107 speedtest1_begin_test(210, "speed4p-notrigger1");
2108 speedtest1_prepare("INSERT INTO t4 VALUES(NULL, ?1, ?2)");
2109 for(jj=0; jj<NROW2; jj++){
2110 speedtest1_numbername(jj, zNum, sizeof(zNum));
2111 sqlite3_bind_int(g.pStmt, 1, jj);
2112 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
2113 speedtest1_run();
2114 }
2115 speedtest1_end_test();
2116 speedtest1_begin_test(210, "speed4p-notrigger2");
2117 speedtest1_prepare("UPDATE t4 SET i = ?1, t = ?2 WHERE rowid = ?3");
2118 for(jj=1; jj<=NROW2*2; jj+=2){
2119 speedtest1_numbername(jj*2, zNum, sizeof(zNum));
2120 sqlite3_bind_int(g.pStmt, 1, jj*2);
2121 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
2122 sqlite3_bind_int(g.pStmt, 3, jj);
2123 speedtest1_run();
2124 }
2125 speedtest1_end_test();
2126 speedtest1_begin_test(220, "speed4p-notrigger3");
2127 speedtest1_prepare("DELETE FROM t4 WHERE rowid = ?1");
2128 for(jj=1; jj<=NROW2*2; jj+=2){
2129 sqlite3_bind_int(g.pStmt, 1, jj*2);
2130 speedtest1_run();
2131 }
2132 speedtest1_end_test();
2133 speedtest1_exec("COMMIT");
2134 }
2135
2136 /*
2137 ** A testset used for debugging speedtest1 itself.
2138 */
2139 void testset_debug1(void){
2140 unsigned i, n;
2141 unsigned x1, x2;
2142 char zNum[2000]; /* A number name */
2143
2144 n = g.szTest;
2145 for(i=1; i<=n; i++){
2146 x1 = swizzle(i, n);
2147 x2 = swizzle(x1, n);
2148 speedtest1_numbername(x1, zNum, sizeof(zNum));
2149 printf("%5d %5d %5d %s\n", i, x1, x2, zNum);
2150 }
2151 }
2152
2153 #ifdef __linux__
2154 #include <sys/types.h>
2155 #include <unistd.h>
2156
2157 /*
2158 ** Attempt to display I/O stats on Linux using /proc/PID/io
2159 */
2160 static void displayLinuxIoStats(FILE *out){
2161 FILE *in;
2162 char z[200];
2163 sqlite3_snprintf(sizeof(z), z, "/proc/%d/io", getpid());
2164 in = fopen(z, "rb");
2165 if( in==0 ) return;
2166 while( fgets(z, sizeof(z), in)!=0 ){
2167 static const struct {
2168 const char *zPattern;
2169 const char *zDesc;
2170 } aTrans[] = {
2171 { "rchar: ", "Bytes received by read():" },
2172 { "wchar: ", "Bytes sent to write():" },
2173 { "syscr: ", "Read() system calls:" },
2174 { "syscw: ", "Write() system calls:" },
2175 { "read_bytes: ", "Bytes rcvd from storage:" },
2176 { "write_bytes: ", "Bytes sent to storage:" },
2177 { "cancelled_write_bytes: ", "Cancelled write bytes:" },
2178 };
2179 int i;
2180 for(i=0; i<sizeof(aTrans)/sizeof(aTrans[0]); i++){
2181 int n = (int)strlen(aTrans[i].zPattern);
2182 if( strncmp(aTrans[i].zPattern, z, n)==0 ){
2183 fprintf(out, "-- %-28s %s", aTrans[i].zDesc, &z[n]);
2184 break;
2185 }
2186 }
2187 }
2188 fclose(in);
2189 }
2190 #endif
2191
2192 #if SQLITE_VERSION_NUMBER<3006018
2193 # define sqlite3_sourceid(X) "(before 3.6.18)"
2194 #endif
2195
2196 #if SQLITE_CKSUMVFS_STATIC
2197 int sqlite3_register_cksumvfs(const char*);
2198 #endif
2199
2200 static int xCompileOptions(void *pCtx, int nVal, char **azVal, char **azCol){
2201 printf("-- Compile option: %s\n", azVal[0]);
2202 return SQLITE_OK;
2203 }
2204 int main(int argc, char **argv){
2205 int doAutovac = 0; /* True for --autovacuum */
2206 int cacheSize = 0; /* Desired cache size. 0 means default */
2207 int doExclusive = 0; /* True for --exclusive */
2208 int doFullFSync = 0; /* True for --fullfsync */
2209 int nHeap = 0, mnHeap = 0; /* Heap size from --heap */
2210 int doIncrvac = 0; /* True for --incrvacuum */
2211 const char *zJMode = 0; /* Journal mode */
2212 const char *zKey = 0; /* Encryption key */
2213 int nLook = -1, szLook = 0; /* --lookaside configuration */
2214 int noSync = 0; /* True for --nosync */
2215 int pageSize = 0; /* Desired page size. 0 means default */
2216 int nPCache = 0, szPCache = 0;/* --pcache configuration */
2217 int doPCache = 0; /* True if --pcache is seen */
2218 int showStats = 0; /* True for --stats */
2219 int nThread = 0; /* --threads value */
2220 int mmapSize = 0; /* How big of a memory map to use */
2221 int memDb = 0; /* --memdb. Use an in-memory database */
2222 int openFlags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE
2223 ; /* SQLITE_OPEN_xxx flags. */
2224 char *zTSet = "main"; /* Which --testset torun */
2225 const char * zVfs = 0; /* --vfs NAME */
2226 int doTrace = 0; /* True for --trace */
2227 const char *zEncoding = 0; /* --utf16be or --utf16le */
2228 const char *zDbName = 0; /* Name of the test database */
2229
2230 void *pHeap = 0; /* Allocated heap space */
2231 void *pLook = 0; /* Allocated lookaside space */
2232 void *pPCache = 0; /* Allocated storage for pcache */
2233 int iCur, iHi; /* Stats values, current and "highwater" */
2234 int i; /* Loop counter */
2235 int rc; /* API return code */
2236
2237 #ifdef SQLITE_SPEEDTEST1_WASM
2238 /* Resetting all state is important for the WASM build, which may
2239 ** call main() multiple times. */
2240 memset(&g, 0, sizeof(g));
2241 iTestNumber = 0;
2242 #endif
2243 #ifdef SQLITE_CKSUMVFS_STATIC
2244 sqlite3_register_cksumvfs(0);
2245 #endif
2246 /*
2247 ** Confirms that argc has at least N arguments following argv[i]. */
2248 #define ARGC_VALUE_CHECK(N) \
2249 if( i>=argc-(N) ) fatal_error("missing argument on %s\n", argv[i])
2250 /* Display the version of SQLite being tested */
2251 printf("-- Speedtest1 for SQLite %s %.48s\n",
2252 sqlite3_libversion(), sqlite3_sourceid());
2253
2254 /* Process command-line arguments */
2255 g.zWR = "";
2256 g.zNN = "";
2257 g.zPK = "UNIQUE";
2258 g.szTest = 100;
2259 g.nRepeat = 1;
2260 for(i=1; i<argc; i++){
2261 const char *z = argv[i];
2262 if( z[0]=='-' ){
2263 do{ z++; }while( z[0]=='-' );
2264 if( strcmp(z,"autovacuum")==0 ){
2265 doAutovac = 1;
2266 }else if( strcmp(z,"big-transactions")==0 ){
2267 g.doBigTransactions = 1;
2268 }else if( strcmp(z,"cachesize")==0 ){
2269 ARGC_VALUE_CHECK(1);
2270 cacheSize = integerValue(argv[++i]);
2271 }else if( strcmp(z,"exclusive")==0 ){
2272 doExclusive = 1;
2273 }else if( strcmp(z,"fullfsync")==0 ){
2274 doFullFSync = 1;
2275 }else if( strcmp(z,"checkpoint")==0 ){
2276 g.doCheckpoint = 1;
2277 }else if( strcmp(z,"explain")==0 ){
2278 g.bSqlOnly = 1;
2279 g.bExplain = 1;
2280 }else if( strcmp(z,"heap")==0 ){
2281 ARGC_VALUE_CHECK(2);
2282 nHeap = integerValue(argv[i+1]);
2283 mnHeap = integerValue(argv[i+2]);
2284 i += 2;
2285 }else if( strcmp(z,"incrvacuum")==0 ){
2286 doIncrvac = 1;
2287 }else if( strcmp(z,"journal")==0 ){
2288 ARGC_VALUE_CHECK(1);
2289 zJMode = argv[++i];
2290 }else if( strcmp(z,"key")==0 ){
2291 ARGC_VALUE_CHECK(1);
2292 zKey = argv[++i];
2293 }else if( strcmp(z,"lookaside")==0 ){
2294 ARGC_VALUE_CHECK(2);
2295 nLook = integerValue(argv[i+1]);
2296 szLook = integerValue(argv[i+2]);
2297 i += 2;
2298 }else if( strcmp(z,"memdb")==0 ){
2299 memDb = 1;
2300 #if SQLITE_VERSION_NUMBER>=3006000
2301 }else if( strcmp(z,"multithread")==0 ){
2302 sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
2303 }else if( strcmp(z,"nomemstat")==0 ){
2304 sqlite3_config(SQLITE_CONFIG_MEMSTATUS, 0);
2305 #endif
2306 #if SQLITE_VERSION_NUMBER>=3007017
2307 }else if( strcmp(z, "mmap")==0 ){
2308 ARGC_VALUE_CHECK(1);
2309 mmapSize = integerValue(argv[++i]);
2310 #endif
2311 }else if( strcmp(z,"nolongdouble")==0 ){
2312 #ifdef SQLITE_TESTCTRL_USELONGDOUBLE
2313 sqlite3_test_control(SQLITE_TESTCTRL_USELONGDOUBLE, 0);
2314 #endif
2315 }else if( strcmp(z,"nomutex")==0 ){
2316 openFlags |= SQLITE_OPEN_NOMUTEX;
2317 }else if( strcmp(z,"nosync")==0 ){
2318 noSync = 1;
2319 }else if( strcmp(z,"notnull")==0 ){
2320 g.zNN = "NOT NULL";
2321 }else if( strcmp(z,"output")==0 ){
2322 #ifdef SPEEDTEST_OMIT_HASH
2323 fatal_error("The --output option is not supported with"
2324 " -DSPEEDTEST_OMIT_HASH\n");
2325 #else
2326 ARGC_VALUE_CHECK(1);
2327 i++;
2328 if( strcmp(argv[i],"-")==0 ){
2329 g.hashFile = stdout;
2330 }else{
2331 g.hashFile = fopen(argv[i], "wb");
2332 if( g.hashFile==0 ){
2333 fatal_error("cannot open \"%s\" for writing\n", argv[i]);
2334 }
2335 }
2336 #endif
2337 }else if( strcmp(z,"pagesize")==0 ){
2338 ARGC_VALUE_CHECK(1);
2339 pageSize = integerValue(argv[++i]);
2340 }else if( strcmp(z,"pcache")==0 ){
2341 ARGC_VALUE_CHECK(2);
2342 nPCache = integerValue(argv[i+1]);
2343 szPCache = integerValue(argv[i+2]);
2344 doPCache = 1;
2345 i += 2;
2346 }else if( strcmp(z,"primarykey")==0 ){
2347 g.zPK = "PRIMARY KEY";
2348 }else if( strcmp(z,"repeat")==0 ){
2349 ARGC_VALUE_CHECK(1);
2350 g.nRepeat = integerValue(argv[++i]);
2351 }else if( strcmp(z,"reprepare")==0 ){
2352 g.bReprepare = 1;
2353 #if SQLITE_VERSION_NUMBER>=3006000
2354 }else if( strcmp(z,"serialized")==0 ){
2355 sqlite3_config(SQLITE_CONFIG_SERIALIZED);
2356 }else if( strcmp(z,"singlethread")==0 ){
2357 sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);
2358 #endif
2359 }else if( strcmp(z,"script")==0 ){
2360 ARGC_VALUE_CHECK(1);
2361 if( g.pScript ) fclose(g.pScript);
2362 g.pScript = fopen(argv[++i], "wb");
2363 if( g.pScript==0 ){
2364 fatal_error("unable to open output file \"%s\"\n", argv[i]);
2365 }
2366 }else if( strcmp(z,"sqlonly")==0 ){
2367 g.bSqlOnly = 1;
2368 }else if( strcmp(z,"shrink-memory")==0 ){
2369 g.bMemShrink = 1;
2370 }else if( strcmp(z,"size")==0 ){
2371 ARGC_VALUE_CHECK(1);
2372 g.szTest = integerValue(argv[++i]);
2373 }else if( strcmp(z,"stats")==0 ){
2374 showStats = 1;
2375 }else if( strcmp(z,"temp")==0 ){
2376 ARGC_VALUE_CHECK(1);
2377 i++;
2378 if( argv[i][0]<'0' || argv[i][0]>'9' || argv[i][1]!=0 ){
2379 fatal_error("argument to --temp should be integer between 0 and 9");
2380 }
2381 g.eTemp = argv[i][0] - '0';
2382 }else if( strcmp(z,"testset")==0 ){
2383 ARGC_VALUE_CHECK(1);
2384 zTSet = argv[++i];
2385 }else if( strcmp(z,"trace")==0 ){
2386 doTrace = 1;
2387 }else if( strcmp(z,"threads")==0 ){
2388 ARGC_VALUE_CHECK(1);
2389 nThread = integerValue(argv[++i]);
2390 }else if( strcmp(z,"utf16le")==0 ){
2391 zEncoding = "utf16le";
2392 }else if( strcmp(z,"utf16be")==0 ){
2393 zEncoding = "utf16be";
2394 }else if( strcmp(z,"verify")==0 ){
2395 g.bVerify = 1;
2396 #ifndef SPEEDTEST_OMIT_HASH
2397 HashInit();
2398 #endif
2399 }else if( strcmp(z,"vfs")==0 ){
2400 ARGC_VALUE_CHECK(1);
2401 zVfs = argv[++i];
2402 }else if( strcmp(z,"reserve")==0 ){
2403 ARGC_VALUE_CHECK(1);
2404 g.nReserve = atoi(argv[++i]);
2405 }else if( strcmp(z,"stmtscanstatus")==0 ){
2406 g.stmtScanStatus = 1;
2407 }else if( strcmp(z,"without-rowid")==0 ){
2408 if( strstr(g.zWR,"WITHOUT")!=0 ){
2409 /* no-op */
2410 }else if( strstr(g.zWR,"STRICT")!=0 ){
2411 g.zWR = "WITHOUT ROWID,STRICT";
2412 }else{
2413 g.zWR = "WITHOUT ROWID";
2414 }
2415 g.zPK = "PRIMARY KEY";
2416 }else if( strcmp(z,"strict")==0 ){
2417 if( strstr(g.zWR,"STRICT")!=0 ){
2418 /* no-op */
2419 }else if( strstr(g.zWR,"WITHOUT")!=0 ){
2420 g.zWR = "WITHOUT ROWID,STRICT";
2421 }else{
2422 g.zWR = "STRICT";
2423 }
2424 }else if( strcmp(z, "help")==0 || strcmp(z,"?")==0 ){
2425 printf(zHelp, argv[0]);
2426 exit(0);
2427 }else{
2428 fatal_error("unknown option: %s\nUse \"%s -?\" for help\n",
2429 argv[i], argv[0]);
2430 }
2431 }else if( zDbName==0 ){
2432 zDbName = argv[i];
2433 }else{
2434 fatal_error("surplus argument: %s\nUse \"%s -?\" for help\n",
2435 argv[i], argv[0]);
2436 }
2437 }
2438 #undef ARGC_VALUE_CHECK
2439 #if SQLITE_VERSION_NUMBER>=3006001
2440 if( nHeap>0 ){
2441 pHeap = malloc( nHeap );
2442 if( pHeap==0 ) fatal_error("cannot allocate %d-byte heap\n", nHeap);
2443 rc = sqlite3_config(SQLITE_CONFIG_HEAP, pHeap, nHeap, mnHeap);
2444 if( rc ) fatal_error("heap configuration failed: %d\n", rc);
2445 }
2446 if( doPCache ){
2447 if( nPCache>0 && szPCache>0 ){
2448 pPCache = malloc( nPCache*(sqlite3_int64)szPCache );
2449 if( pPCache==0 ) fatal_error("cannot allocate %lld-byte pcache\n",
2450 nPCache*(sqlite3_int64)szPCache);
2451 }
2452 rc = sqlite3_config(SQLITE_CONFIG_PAGECACHE, pPCache, szPCache, nPCache);
2453 if( rc ) fatal_error("pcache configuration failed: %d\n", rc);
2454 }
2455 if( nLook>=0 ){
2456 sqlite3_config(SQLITE_CONFIG_LOOKASIDE, 0, 0);
2457 }
2458 #endif
2459 sqlite3_initialize();
2460
2461 if( zDbName!=0 ){
2462 sqlite3_vfs *pVfs = sqlite3_vfs_find(zVfs);
2463 /* For some VFSes, e.g. opfs, unlink() is not sufficient. Use the
2464 ** selected (or default) VFS's xDelete method to delete the
2465 ** database. This is specifically important for the "opfs" VFS
2466 ** when running from a WASM build of speedtest1, so that the db
2467 ** can be cleaned up properly. For historical compatibility, we'll
2468 ** also simply unlink(). */
2469 if( pVfs!=0 ){
2470 pVfs->xDelete(pVfs, zDbName, 1);
2471 }
2472 unlink(zDbName);
2473 }
2474
2475 /* Open the database and the input file */
2476 if( sqlite3_open_v2(memDb ? ":memory:" : zDbName, &g.db,
2477 openFlags, zVfs) ){
2478 fatal_error("Cannot open database file: %s\n", zDbName);
2479 }
2480 #if SQLITE_VERSION_NUMBER>=3006001
2481 if( nLook>0 && szLook>0 ){
2482 pLook = malloc( nLook*szLook );
2483 rc = sqlite3_db_config(g.db, SQLITE_DBCONFIG_LOOKASIDE,pLook,szLook,nLook);
2484 if( rc ) fatal_error("lookaside configuration failed: %d\n", rc);
2485 }
2486 #endif
2487 if( g.nReserve>0 ){
2488 sqlite3_file_control(g.db, 0, SQLITE_FCNTL_RESERVE_BYTES, &g.nReserve);
2489 }
2490 if( g.stmtScanStatus ){
2491 sqlite3_db_config(g.db, SQLITE_DBCONFIG_STMT_SCANSTATUS, 1, 0);
2492 }
2493
2494 /* Set database connection options */
2495 sqlite3_create_function(g.db, "random", 0, SQLITE_UTF8, 0, randomFunc, 0, 0);
2496 #ifndef SQLITE_OMIT_DEPRECATED
2497 if( doTrace ) sqlite3_trace(g.db, traceCallback, 0);
2498 #endif
2499 if( memDb>0 ){
2500 speedtest1_exec("PRAGMA temp_store=memory");
2501 }
2502 if( mmapSize>0 ){
2503 speedtest1_exec("PRAGMA mmap_size=%d", mmapSize);
2504 }
2505 speedtest1_exec("PRAGMA threads=%d", nThread);
2506 if( zKey ){
2507 speedtest1_exec("PRAGMA key('%s')", zKey);
2508 }
2509 if( zEncoding ){
2510 speedtest1_exec("PRAGMA encoding=%s", zEncoding);
2511 }
2512 if( doAutovac ){
2513 speedtest1_exec("PRAGMA auto_vacuum=FULL");
2514 }else if( doIncrvac ){
2515 speedtest1_exec("PRAGMA auto_vacuum=INCREMENTAL");
2516 }
2517 if( pageSize ){
2518 speedtest1_exec("PRAGMA page_size=%d", pageSize);
2519 }
2520 if( cacheSize ){
2521 speedtest1_exec("PRAGMA cache_size=%d", cacheSize);
2522 }
2523 if( noSync ){
2524 speedtest1_exec("PRAGMA synchronous=OFF");
2525 }else if( doFullFSync ){
2526 speedtest1_exec("PRAGMA fullfsync=ON");
2527 }
2528 if( doExclusive ){
2529 speedtest1_exec("PRAGMA locking_mode=EXCLUSIVE");
2530 }
2531 if( zJMode ){
2532 speedtest1_exec("PRAGMA journal_mode=%s", zJMode);
2533 }
2534
2535 if( g.bExplain ) printf(".explain\n.echo on\n");
2536 do{
2537 char *zThisTest = zTSet;
2538 char *zComma = strchr(zThisTest,',');
2539 if( zComma ){
2540 *zComma = 0;
2541 zTSet = zComma+1;
2542 }else{
2543 zTSet = "";
2544 }
2545 if( g.iTotal>0 || zComma!=0 ){
2546 printf(" Begin testset \"%s\"\n", zThisTest);
2547 }
2548 if( strcmp(zThisTest,"main")==0 ){
2549 testset_main();
2550 }else if( strcmp(zThisTest,"debug1")==0 ){
2551 testset_debug1();
2552 }else if( strcmp(zThisTest,"orm")==0 ){
2553 testset_orm();
2554 }else if( strcmp(zThisTest,"cte")==0 ){
2555 testset_cte();
2556 }else if( strcmp(zThisTest,"fp")==0 ){
2557 testset_fp();
2558 }else if( strcmp(zThisTest,"trigger")==0 ){
2559 testset_trigger();
2560 }else if( strcmp(zThisTest,"rtree")==0 ){
2561 #ifdef SQLITE_ENABLE_RTREE
2562 testset_rtree(6, 147);
2563 #else
2564 fatal_error("compile with -DSQLITE_ENABLE_RTREE to enable "
2565 "the R-Tree tests\n");
2566 #endif
2567 }else{
2568 fatal_error("unknown testset: \"%s\"\n"
2569 "Choices: cte debug1 fp main orm rtree trigger\n",
2570 zThisTest);
2571 }
2572 if( zTSet[0] ){
2573 char *zSql, *zObj;
2574 speedtest1_begin_test(999, "Reset the database");
2575 while( 1 ){
2576 zObj = speedtest1_once(
2577 "SELECT name FROM main.sqlite_master"
2578 " WHERE sql LIKE 'CREATE %%TABLE%%'");
2579 if( zObj==0 ) break;
2580 zSql = sqlite3_mprintf("DROP TABLE main.\"%w\"", zObj);
2581 speedtest1_exec(zSql);
2582 sqlite3_free(zSql);
2583 sqlite3_free(zObj);
2584 }
2585 while( 1 ){
2586 zObj = speedtest1_once(
2587 "SELECT name FROM temp.sqlite_master"
2588 " WHERE sql LIKE 'CREATE %%TABLE%%'");
2589 if( zObj==0 ) break;
2590 zSql = sqlite3_mprintf("DROP TABLE main.\"%w\"", zObj);
2591 speedtest1_exec(zSql);
2592 sqlite3_free(zSql);
2593 sqlite3_free(zObj);
2594 }
2595 speedtest1_end_test();
2596 }
2597 }while( zTSet[0] );
2598 speedtest1_final();
2599
2600 if( showStats ){
2601 sqlite3_exec(g.db, "PRAGMA compile_options", xCompileOptions, 0, 0);
2602 }
2603
2604 /* Database connection statistics printed after both prepared statements
2605 ** have been finalized */
2606 #if SQLITE_VERSION_NUMBER>=3007009
2607 if( showStats ){
2608 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_USED, &iCur, &iHi, 0);
2609 printf("-- Lookaside Slots Used: %d (max %d)\n", iCur,iHi);
2610 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_HIT, &iCur, &iHi, 0);
2611 printf("-- Successful lookasides: %d\n", iHi);
2612 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE, &iCur,&iHi,0);
2613 printf("-- Lookaside size faults: %d\n", iHi);
2614 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL, &iCur,&iHi,0);
2615 printf("-- Lookaside OOM faults: %d\n", iHi);
2616 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_USED, &iCur, &iHi, 0);
2617 printf("-- Pager Heap Usage: %d bytes\n", iCur);
2618 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_HIT, &iCur, &iHi, 1);
2619 printf("-- Page cache hits: %d\n", iCur);
2620 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_MISS, &iCur, &iHi, 1);
2621 printf("-- Page cache misses: %d\n", iCur);
2622 #if SQLITE_VERSION_NUMBER>=3007012
2623 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_WRITE, &iCur, &iHi, 1);
2624 printf("-- Page cache writes: %d\n", iCur);
2625 #endif
2626 sqlite3_db_status(g.db, SQLITE_DBSTATUS_SCHEMA_USED, &iCur, &iHi, 0);
2627 printf("-- Schema Heap Usage: %d bytes\n", iCur);
2628 sqlite3_db_status(g.db, SQLITE_DBSTATUS_STMT_USED, &iCur, &iHi, 0);
2629 printf("-- Statement Heap Usage: %d bytes\n", iCur);
2630 }
2631 #endif
2632
2633 sqlite3_close(g.db);
2634
2635 #if SQLITE_VERSION_NUMBER>=3006001
2636 /* Global memory usage statistics printed after the database connection
2637 ** has closed. Memory usage should be zero at this point. */
2638 if( showStats ){
2639 sqlite3_status(SQLITE_STATUS_MEMORY_USED, &iCur, &iHi, 0);
2640 printf("-- Memory Used (bytes): %d (max %d)\n", iCur,iHi);
2641 #if SQLITE_VERSION_NUMBER>=3007000
2642 sqlite3_status(SQLITE_STATUS_MALLOC_COUNT, &iCur, &iHi, 0);
2643 printf("-- Outstanding Allocations: %d (max %d)\n", iCur,iHi);
2644 #endif
2645 sqlite3_status(SQLITE_STATUS_PAGECACHE_OVERFLOW, &iCur, &iHi, 0);
2646 printf("-- Pcache Overflow Bytes: %d (max %d)\n", iCur,iHi);
2647 sqlite3_status(SQLITE_STATUS_MALLOC_SIZE, &iCur, &iHi, 0);
2648 printf("-- Largest Allocation: %d bytes\n",iHi);
2649 sqlite3_status(SQLITE_STATUS_PAGECACHE_SIZE, &iCur, &iHi, 0);
2650 printf("-- Largest Pcache Allocation: %d bytes\n",iHi);
2651 }
2652 #endif
2653
2654 #ifdef __linux__
2655 if( showStats ){
2656 displayLinuxIoStats(stdout);
2657 }
2658 #endif
2659 if( g.pScript ){
2660 fclose(g.pScript);
2661 }
2662
2663 /* Release memory */
2664 free( pLook );
2665 free( pPCache );
2666 free( pHeap );
2667 return 0;
2668 }
2669
2670 #ifdef SQLITE_SPEEDTEST1_WASM
2671 /*
2672 ** A workaround for some inconsistent behaviour with how
2673 ** main() does (or does not) get exported to WASM.
2674 */
2675 int wasm_main(int argc, char **argv){
2676 return main(argc, argv);
2677 }
2678 #endif
SQLCipher is an SQLite extension that provides 256 bit AES encryption of database files.