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