1 /*
2  * Copyright 2014 The Emscripten Authors.  All rights reserved.
3  * Emscripten is available under two separate licenses, the MIT license and the
4  * University of Illinois/NCSA Open Source License.  Both these licenses can be
5  * found in the LICENSE file.
6  */
7 
8 /*
9 ** A program for performance testing.
10 **
11 ** The available command-line options are described below:
12 */
13 static const char zHelp[] =
14   "Usage: %s [--options] DATABASE\n"
15   "Options:\n"
16   "  --autovacuum        Enable AUTOVACUUM mode\n"
17   "  --cachesize N       Set the cache size to N\n"
18   "  --exclusive         Enable locking_mode=EXCLUSIVE\n"
19   "  --explain           Like --sqlonly but with added EXPLAIN keywords\n"
20   "  --heap SZ MIN       Memory allocator uses SZ bytes & min allocation MIN\n"
21   "  --incrvacuum        Enable incremenatal vacuum mode\n"
22   "  --journalmode M     Set the journal_mode to MODE\n"
23   "  --key KEY           Set the encryption key to KEY\n"
24   "  --lookaside N SZ    Configure lookaside for N slots of SZ bytes each\n"
25   "  --nosync            Set PRAGMA synchronous=OFF\n"
26   "  --notnull           Add NOT NULL constraints to table columns\n"
27   "  --pagesize N        Set the page size to N\n"
28   "  --pcache N SZ       Configure N pages of pagecache each of size SZ bytes\n"
29   "  --primarykey        Use PRIMARY KEY instead of UNIQUE where appropriate\n"
30   "  --reprepare         Reprepare each statement upon every invocation\n"
31   "  --scratch N SZ      Configure scratch memory for N slots of SZ bytes each\n"
32   "  --sqlonly           No-op.  Only show the SQL that would have been run.\n"
33   "  --size N            Relative test size.  Default=100\n"
34   "  --stats             Show statistics at the end\n"
35   "  --testset T         Run test-set T\n"
36   "  --trace             Turn on SQL tracing\n"
37   "  --utf16be           Set text encoding to UTF-16BE\n"
38   "  --utf16le           Set text encoding to UTF-16LE\n"
39   "  --verify            Run additional verification steps.\n"
40   "  --without-rowid     Use WITHOUT ROWID where appropriate\n"
41 ;
42 
43 
44 #include "sqlite3.h"
45 #include <assert.h>
46 #include <stdio.h>
47 #include <stdlib.h>
48 #include <stdarg.h>
49 #include <string.h>
50 #include <ctype.h>
51 
52 /* All global state is held in this structure */
53 static struct Global {
54   sqlite3 *db;               /* The open database connection */
55   sqlite3_stmt *pStmt;       /* Current SQL statement */
56   sqlite3_int64 iStart;      /* Start-time for the current test */
57   sqlite3_int64 iTotal;      /* Total time */
58   int bWithoutRowid;         /* True for --without-rowid */
59   int bReprepare;            /* True to reprepare the SQL on each rerun */
60   int bSqlOnly;              /* True to print the SQL once only */
61   int bExplain;              /* Print SQL with EXPLAIN prefix */
62   int bVerify;               /* Try to verify that results are correct */
63   int szTest;                /* Scale factor for test iterations */
64   const char *zWR;           /* Might be WITHOUT ROWID */
65   const char *zNN;           /* Might be NOT NULL */
66   const char *zPK;           /* Might be UNIQUE or PRIMARY KEY */
67   unsigned int x, y;         /* Pseudo-random number generator state */
68   int nResult;               /* Size of the current result */
69   char zResult[3000];        /* Text of the current result */
70 } g;
71 
72 
73 /* Print an error message and exit */
fatal_error(const char * zMsg,...)74 static void fatal_error(const char *zMsg, ...){
75   va_list ap;
76   va_start(ap, zMsg);
77   vfprintf(stderr, zMsg, ap);
78   va_end(ap);
79   exit(1);
80 }
81 
82 /*
83 ** Return the value of a hexadecimal digit.  Return -1 if the input
84 ** is not a hex digit.
85 */
hexDigitValue(char c)86 static int hexDigitValue(char c){
87   if( c>='0' && c<='9' ) return c - '0';
88   if( c>='a' && c<='f' ) return c - 'a' + 10;
89   if( c>='A' && c<='F' ) return c - 'A' + 10;
90   return -1;
91 }
92 
93 /* Provide an alternative to sqlite3_stricmp() in older versions of
94 ** SQLite */
95 #if SQLITE_VERSION_NUMBER<3007011
96 # define sqlite3_stricmp strcmp
97 #endif
98 
99 /*
100 ** Interpret zArg as an integer value, possibly with suffixes.
101 */
integerValue(const char * zArg)102 static int integerValue(const char *zArg){
103   sqlite3_int64 v = 0;
104   static const struct { char *zSuffix; int iMult; } aMult[] = {
105     { "KiB", 1024 },
106     { "MiB", 1024*1024 },
107     { "GiB", 1024*1024*1024 },
108     { "KB",  1000 },
109     { "MB",  1000000 },
110     { "GB",  1000000000 },
111     { "K",   1000 },
112     { "M",   1000000 },
113     { "G",   1000000000 },
114   };
115   int i;
116   int isNeg = 0;
117   if( zArg[0]=='-' ){
118     isNeg = 1;
119     zArg++;
120   }else if( zArg[0]=='+' ){
121     zArg++;
122   }
123   if( zArg[0]=='0' && zArg[1]=='x' ){
124     int x;
125     zArg += 2;
126     while( (x = hexDigitValue(zArg[0]))>=0 ){
127       v = (v<<4) + x;
128       zArg++;
129     }
130   }else{
131     while( isdigit(zArg[0]) ){
132       v = v*10 + zArg[0] - '0';
133       zArg++;
134     }
135   }
136   for(i=0; i<sizeof(aMult)/sizeof(aMult[0]); i++){
137     if( sqlite3_stricmp(aMult[i].zSuffix, zArg)==0 ){
138       v *= aMult[i].iMult;
139       break;
140     }
141   }
142   if( v>0x7fffffff ) fatal_error("parameter too large - max 2147483648");
143   return (int)(isNeg? -v : v);
144 }
145 
146 /* Return the current wall-clock time, in milliseconds */
speedtest1_timestamp(void)147 sqlite3_int64 speedtest1_timestamp(void){
148   static sqlite3_vfs *clockVfs = 0;
149   sqlite3_int64 t;
150   if( clockVfs==0 ) clockVfs = sqlite3_vfs_find(0);
151 #if SQLITE_VERSION_NUMBER>=3007000
152   if( clockVfs->iVersion>=2 && clockVfs->xCurrentTimeInt64!=0 ){
153     clockVfs->xCurrentTimeInt64(clockVfs, &t);
154   }else
155 #endif
156   {
157     double r;
158     clockVfs->xCurrentTime(clockVfs, &r);
159     t = (sqlite3_int64)(r*86400000.0);
160   }
161   return t;
162 }
163 
164 /* Return a pseudo-random unsigned integer */
speedtest1_random(void)165 unsigned int speedtest1_random(void){
166   g.x = (g.x>>1) ^ ((1+~(g.x&1)) & 0xd0000001);
167   g.y = g.y*1103515245 + 12345;
168   return g.x ^ g.y;
169 }
170 
171 /* Map the value in within the range of 1...limit into another
172 ** number in a way that is chatic and invertable.
173 */
swizzle(unsigned in,unsigned limit)174 unsigned swizzle(unsigned in, unsigned limit){
175   unsigned out = 0;
176   while( limit ){
177     out = (out<<1) | (in&1);
178     in >>= 1;
179     limit >>= 1;
180   }
181   return out;
182 }
183 
184 /* Round up a number so that it is a power of two minus one
185 */
roundup_allones(unsigned limit)186 unsigned roundup_allones(unsigned limit){
187   unsigned m = 1;
188   while( m<limit ) m = (m<<1)+1;
189   return m;
190 }
191 
192 /* The speedtest1_numbername procedure below converts its argment (an integer)
193 ** into a string which is the English-language name for that number.
194 ** The returned string should be freed with sqlite3_free().
195 **
196 ** Example:
197 **
198 **     speedtest1_numbername(123)   ->  "one hundred twenty three"
199 */
speedtest1_numbername(unsigned int n,char * zOut,int nOut)200 int speedtest1_numbername(unsigned int n, char *zOut, int nOut){
201   static const char *ones[] = {  "zero", "one", "two", "three", "four", "five",
202                   "six", "seven", "eight", "nine", "ten", "eleven", "twelve",
203                   "thirteen", "fourteen", "fifteen", "sixteen", "seventeen",
204                   "eighteen", "nineteen" };
205   static const char *tens[] = { "", "ten", "twenty", "thirty", "forty",
206                  "fifty", "sixty", "seventy", "eighty", "ninety" };
207   int i = 0;
208 
209   if( n>=1000000000 ){
210     i += speedtest1_numbername(n/1000000000, zOut+i, nOut-i);
211     sqlite3_snprintf(nOut-i, zOut+i, " billion");
212     i += (int)strlen(zOut+i);
213     n = n % 1000000000;
214   }
215   if( n>=1000000 ){
216     if( i && i<nOut-1 ) zOut[i++] = ' ';
217     i += speedtest1_numbername(n/1000000, zOut+i, nOut-i);
218     sqlite3_snprintf(nOut-i, zOut+i, " million");
219     i += (int)strlen(zOut+i);
220     n = n % 1000000;
221   }
222   if( n>=1000 ){
223     if( i && i<nOut-1 ) zOut[i++] = ' ';
224     i += speedtest1_numbername(n/1000, zOut+i, nOut-i);
225     sqlite3_snprintf(nOut-i, zOut+i, " thousand");
226     i += (int)strlen(zOut+i);
227     n = n % 1000;
228   }
229   if( n>=100 ){
230     if( i && i<nOut-1 ) zOut[i++] = ' ';
231     sqlite3_snprintf(nOut-i, zOut+i, "%s hundred", ones[n/100]);
232     i += (int)strlen(zOut+i);
233     n = n % 100;
234   }
235   if( n>=20 ){
236     if( i && i<nOut-1 ) zOut[i++] = ' ';
237     sqlite3_snprintf(nOut-i, zOut+i, "%s", tens[n/10]);
238     i += (int)strlen(zOut+i);
239     n = n % 10;
240   }
241   if( n>0 ){
242     if( i && i<nOut-1 ) zOut[i++] = ' ';
243     sqlite3_snprintf(nOut-i, zOut+i, "%s", ones[n]);
244     i += (int)strlen(zOut+i);
245   }
246   if( i==0 ){
247     sqlite3_snprintf(nOut-i, zOut+i, "zero");
248     i += (int)strlen(zOut+i);
249   }
250   return i;
251 }
252 
253 
254 /* Start a new test case */
255 #define NAMEWIDTH 60
256 static const char zDots[] =
257   ".......................................................................";
speedtest1_begin_test(int iTestNum,const char * zTestName,...)258 void speedtest1_begin_test(int iTestNum, const char *zTestName, ...){
259   int n = (int)strlen(zTestName);
260   char *zName;
261   va_list ap;
262   va_start(ap, zTestName);
263   zName = sqlite3_vmprintf(zTestName, ap);
264   va_end(ap);
265   n = (int)strlen(zName);
266   if( n>NAMEWIDTH ){
267     zName[NAMEWIDTH] = 0;
268     n = NAMEWIDTH;
269   }
270   if( g.bSqlOnly ){
271     printf("/* %4d - %s%.*s */\n", iTestNum, zName, NAMEWIDTH-n, zDots);
272   }else{
273     printf("%4d - %s%.*s ", iTestNum, zName, NAMEWIDTH-n, zDots);
274     fflush(stdout);
275   }
276   sqlite3_free(zName);
277   g.nResult = 0;
278   g.iStart = speedtest1_timestamp();
279   g.x = 0xad131d0b;
280   g.y = 0x44f9eac8;
281 }
282 
283 /* Complete a test case */
speedtest1_end_test(void)284 void speedtest1_end_test(void){
285   sqlite3_int64 iElapseTime = speedtest1_timestamp() - g.iStart;
286   if( !g.bSqlOnly ){
287     g.iTotal += iElapseTime;
288     printf("%4d.%03ds\n", (int)(iElapseTime/1000), (int)(iElapseTime%1000));
289   }
290   if( g.pStmt ){
291     sqlite3_finalize(g.pStmt);
292     g.pStmt = 0;
293   }
294 }
295 
296 /* Report end of testing */
speedtest1_final(void)297 void speedtest1_final(void){
298   if( !g.bSqlOnly ){
299     printf("       TOTAL%.*s %4d.%03ds\n", NAMEWIDTH-5, zDots,
300            (int)(g.iTotal/1000), (int)(g.iTotal%1000));
301   }
302 }
303 
304 /* Print an SQL statement to standard output */
printSql(const char * zSql)305 static void printSql(const char *zSql){
306   int n = (int)strlen(zSql);
307   while( n>0 && (zSql[n-1]==';' || isspace(zSql[n-1])) ){ n--; }
308   if( g.bExplain ) printf("EXPLAIN ");
309   printf("%.*s;\n", n, zSql);
310   if( g.bExplain
311 #if SQLITE_VERSION_NUMBER>=3007010
312    && ( sqlite3_strglob("CREATE *", zSql)==0
313      || sqlite3_strglob("DROP *", zSql)==0
314      || sqlite3_strglob("ALTER *", zSql)==0
315       )
316 #endif
317   ){
318     printf("%.*s;\n", n, zSql);
319   }
320 }
321 
322 /* Run SQL */
speedtest1_exec(const char * zFormat,...)323 void speedtest1_exec(const char *zFormat, ...){
324   va_list ap;
325   char *zSql;
326   va_start(ap, zFormat);
327   zSql = sqlite3_vmprintf(zFormat, ap);
328   va_end(ap);
329   if( g.bSqlOnly ){
330     printSql(zSql);
331   }else{
332     char *zErrMsg = 0;
333     int rc = sqlite3_exec(g.db, zSql, 0, 0, &zErrMsg);
334     if( zErrMsg ) fatal_error("SQL error: %s\n%s\n", zErrMsg, zSql);
335     if( rc!=SQLITE_OK ) fatal_error("exec error: %s\n", sqlite3_errmsg(g.db));
336   }
337   sqlite3_free(zSql);
338 }
339 
340 /* Prepare an SQL statement */
speedtest1_prepare(const char * zFormat,...)341 void speedtest1_prepare(const char *zFormat, ...){
342   va_list ap;
343   char *zSql;
344   va_start(ap, zFormat);
345   zSql = sqlite3_vmprintf(zFormat, ap);
346   va_end(ap);
347   if( g.bSqlOnly ){
348     printSql(zSql);
349   }else{
350     int rc;
351     if( g.pStmt ) sqlite3_finalize(g.pStmt);
352     rc = sqlite3_prepare_v2(g.db, zSql, -1, &g.pStmt, 0);
353     if( rc ){
354       fatal_error("SQL error: %s\n", sqlite3_errmsg(g.db));
355     }
356   }
357   sqlite3_free(zSql);
358 }
359 
360 /* Run an SQL statement previously prepared */
speedtest1_execute(void)361 void speedtest1_execute(void){
362   int i, n, len;
363   if( g.bSqlOnly ) return;
364   assert( g.pStmt );
365   g.nResult = 0;
366   while( sqlite3_step(g.pStmt)==SQLITE_ROW ){
367     n = sqlite3_column_count(g.pStmt);
368     for(i=0; i<n; i++){
369       const char *z = (const char*)sqlite3_column_text(g.pStmt, i);
370       if( z==0 ) z = "nil";
371       len = (int)strlen(z);
372       if( g.nResult+len<sizeof(g.zResult)-2 ){
373         if( g.nResult>0 ) g.zResult[g.nResult++] = ' ';
374         memcpy(g.zResult + g.nResult, z, len+1);
375         g.nResult += len;
376       }
377     }
378   }
379   if( g.bReprepare ){
380     sqlite3_stmt *pNew;
381     sqlite3_prepare_v2(g.db, sqlite3_sql(g.pStmt), -1, &pNew, 0);
382     sqlite3_finalize(g.pStmt);
383     g.pStmt = pNew;
384   }else{
385     sqlite3_reset(g.pStmt);
386   }
387 }
388 
389 /* The sqlite3_trace() callback function */
traceCallback(void * NotUsed,const char * zSql)390 static void traceCallback(void *NotUsed, const char *zSql){
391   int n = (int)strlen(zSql);
392   while( n>0 && (zSql[n-1]==';' || isspace(zSql[n-1])) ) n--;
393   fprintf(stderr,"%.*s;\n", n, zSql);
394 }
395 
396 /* Substitute random() function that gives the same random
397 ** sequence on each run, for repeatability. */
randomFunc1(sqlite3_context * context,int NotUsed,sqlite3_value ** NotUsed2)398 static void randomFunc1(
399   sqlite3_context *context,
400   int NotUsed,
401   sqlite3_value **NotUsed2
402 ){
403   sqlite3_result_int64(context, (sqlite3_int64)speedtest1_random());
404 }
405 
406 /* Estimate the square root of an integer */
est_square_root(int x)407 static int est_square_root(int x){
408   int y0 = x/2;
409   int y1;
410   int n;
411   for(n=0; y0>0 && n<10; n++){
412     y1 = (y0 + x/y0)/2;
413     if( y1==y0 ) break;
414     y0 = y1;
415   }
416   return y0;
417 }
418 
419 /*
420 ** The main and default testset
421 */
testset_main(void)422 void testset_main(void){
423   int i;                        /* Loop counter */
424   int n;                        /* iteration count */
425   int sz;                       /* Size of the tables */
426   int maxb;                     /* Maximum swizzled value */
427   unsigned x1, x2;              /* Parameters */
428   int len;                      /* Length of the zNum[] string */
429   char zNum[2000];              /* A number name */
430 
431   sz = n = g.szTest*500;
432   maxb = roundup_allones(sz);
433   speedtest1_begin_test(100, "%d INSERTs into table with no index", n);
434   speedtest1_exec("BEGIN");
435   speedtest1_exec("CREATE TABLE t1(a INTEGER %s, b INTEGER %s, c TEXT %s);",
436                   g.zNN, g.zNN, g.zNN);
437   speedtest1_prepare("INSERT INTO t1 VALUES(?1,?2,?3); --  %d times", n);
438   for(i=1; i<=n; i++){
439     x1 = swizzle(i,maxb);
440     speedtest1_numbername(x1, zNum, sizeof(zNum));
441     sqlite3_bind_int64(g.pStmt, 1, (sqlite3_int64)x1);
442     sqlite3_bind_int(g.pStmt, 2, i);
443     sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
444     speedtest1_execute();
445   }
446   speedtest1_exec("COMMIT");
447   speedtest1_end_test();
448 
449 
450   n = sz;
451   speedtest1_begin_test(110, "%d ordered INSERTS with one index/PK", n);
452   speedtest1_exec("BEGIN");
453   speedtest1_exec("CREATE TABLE t2(a INTEGER %s %s, b INTEGER %s, c TEXT %s) %s",
454                    g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);
455   speedtest1_prepare("INSERT INTO t2 VALUES(?1,?2,?3); -- %d times", n);
456   for(i=1; i<=n; i++){
457     x1 = swizzle(i,maxb);
458     speedtest1_numbername(x1, zNum, sizeof(zNum));
459     sqlite3_bind_int(g.pStmt, 1, i);
460     sqlite3_bind_int64(g.pStmt, 2, (sqlite3_int64)x1);
461     sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
462     speedtest1_execute();
463   }
464   speedtest1_exec("COMMIT");
465   speedtest1_end_test();
466 
467 
468   n = sz;
469   speedtest1_begin_test(120, "%d unordered INSERTS with one index/PK", n);
470   speedtest1_exec("BEGIN");
471   speedtest1_exec("CREATE TABLE t3(a INTEGER %s %s, b INTEGER %s, c TEXT %s) %s",
472                    g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);
473   speedtest1_prepare("INSERT INTO t3 VALUES(?1,?2,?3); -- %d times", n);
474   for(i=1; i<=n; i++){
475     x1 = swizzle(i,maxb);
476     speedtest1_numbername(x1, zNum, sizeof(zNum));
477     sqlite3_bind_int(g.pStmt, 2, i);
478     sqlite3_bind_int64(g.pStmt, 1, (sqlite3_int64)x1);
479     sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
480     speedtest1_execute();
481   }
482   speedtest1_exec("COMMIT");
483   speedtest1_end_test();
484 
485 
486   n = 25;
487   speedtest1_begin_test(130, "%d SELECTS, numeric BETWEEN, unindexed", n);
488   speedtest1_exec("BEGIN");
489   speedtest1_prepare(
490     "SELECT count(*), avg(b), sum(length(c)) FROM t1\n"
491     " WHERE b BETWEEN ?1 AND ?2; -- %d times", n
492   );
493   for(i=1; i<=n; i++){
494     x1 = speedtest1_random()%maxb;
495     x2 = speedtest1_random()%10 + sz/5000 + x1;
496     sqlite3_bind_int(g.pStmt, 1, x1);
497     sqlite3_bind_int(g.pStmt, 2, x2);
498     speedtest1_execute();
499   }
500   speedtest1_exec("COMMIT");
501   speedtest1_end_test();
502 
503 
504   n = 10;
505   speedtest1_begin_test(140, "%d SELECTS, LIKE, unindexed", n);
506   speedtest1_exec("BEGIN");
507   speedtest1_prepare(
508     "SELECT count(*), avg(b), sum(length(c)) FROM t1\n"
509     " WHERE c LIKE ?1; -- %d times", n
510   );
511   for(i=1; i<=n; i++){
512     x1 = speedtest1_random()%maxb;
513     zNum[0] = '%';
514     len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
515     zNum[len] = '%';
516     zNum[len+1] = 0;
517     sqlite3_bind_text(g.pStmt, 1, zNum, len, SQLITE_STATIC);
518     speedtest1_execute();
519   }
520   speedtest1_exec("COMMIT");
521   speedtest1_end_test();
522 
523 
524   n = 10;
525   speedtest1_begin_test(142, "%d SELECTS w/ORDER BY, unindexed", n);
526   speedtest1_exec("BEGIN");
527   speedtest1_prepare(
528     "SELECT a, b, c FROM t1 WHERE c LIKE ?1\n"
529     " ORDER BY a; -- %d times", n
530   );
531   for(i=1; i<=n; i++){
532     x1 = speedtest1_random()%maxb;
533     zNum[0] = '%';
534     len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
535     zNum[len] = '%';
536     zNum[len+1] = 0;
537     sqlite3_bind_text(g.pStmt, 1, zNum, len, SQLITE_STATIC);
538     speedtest1_execute();
539   }
540   speedtest1_exec("COMMIT");
541   speedtest1_end_test();
542 
543   n = 10; //g.szTest/5;
544   speedtest1_begin_test(145, "%d SELECTS w/ORDER BY and LIMIT, unindexed", n);
545   speedtest1_exec("BEGIN");
546   speedtest1_prepare(
547     "SELECT a, b, c FROM t1 WHERE c LIKE ?1\n"
548     " ORDER BY a LIMIT 10; -- %d times", n
549   );
550   for(i=1; i<=n; i++){
551     x1 = speedtest1_random()%maxb;
552     zNum[0] = '%';
553     len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
554     zNum[len] = '%';
555     zNum[len+1] = 0;
556     sqlite3_bind_text(g.pStmt, 1, zNum, len, SQLITE_STATIC);
557     speedtest1_execute();
558   }
559   speedtest1_exec("COMMIT");
560   speedtest1_end_test();
561 
562 
563   speedtest1_begin_test(150, "CREATE INDEX five times");
564   speedtest1_exec("BEGIN;");
565   speedtest1_exec("CREATE UNIQUE INDEX t1b ON t1(b);");
566   speedtest1_exec("CREATE INDEX t1c ON t1(c);");
567   speedtest1_exec("CREATE UNIQUE INDEX t2b ON t2(b);");
568   speedtest1_exec("CREATE INDEX t2c ON t2(c DESC);");
569   speedtest1_exec("CREATE INDEX t3bc ON t3(b,c);");
570   speedtest1_exec("COMMIT;");
571   speedtest1_end_test();
572 
573 
574   n = sz/5;
575   speedtest1_begin_test(160, "%d SELECTS, numeric BETWEEN, indexed", n);
576   speedtest1_exec("BEGIN");
577   speedtest1_prepare(
578     "SELECT count(*), avg(b), sum(length(c)) FROM t1\n"
579     " WHERE b BETWEEN ?1 AND ?2; -- %d times", n
580   );
581   for(i=1; i<=n; i++){
582     x1 = speedtest1_random()%maxb;
583     x2 = speedtest1_random()%10 + sz/5000 + x1;
584     sqlite3_bind_int(g.pStmt, 1, x1);
585     sqlite3_bind_int(g.pStmt, 2, x2);
586     speedtest1_execute();
587   }
588   speedtest1_exec("COMMIT");
589   speedtest1_end_test();
590 
591 
592   n = sz/5;
593   speedtest1_begin_test(161, "%d SELECTS, numeric BETWEEN, PK", n);
594   speedtest1_exec("BEGIN");
595   speedtest1_prepare(
596     "SELECT count(*), avg(b), sum(length(c)) FROM t2\n"
597     " WHERE a BETWEEN ?1 AND ?2; -- %d times", n
598   );
599   for(i=1; i<=n; i++){
600     x1 = speedtest1_random()%maxb;
601     x2 = speedtest1_random()%10 + sz/5000 + x1;
602     sqlite3_bind_int(g.pStmt, 1, x1);
603     sqlite3_bind_int(g.pStmt, 2, x2);
604     speedtest1_execute();
605   }
606   speedtest1_exec("COMMIT");
607   speedtest1_end_test();
608 
609 
610   n = sz/5;
611   speedtest1_begin_test(170, "%d SELECTS, text BETWEEN, indexed", n);
612   speedtest1_exec("BEGIN");
613   speedtest1_prepare(
614     "SELECT count(*), avg(b), sum(length(c)) FROM t1\n"
615     " WHERE c BETWEEN ?1 AND (?1||'~'); -- %d times", n
616   );
617   for(i=1; i<=n; i++){
618     x1 = swizzle(i, maxb);
619     len = speedtest1_numbername(x1, zNum, sizeof(zNum)-1);
620     sqlite3_bind_text(g.pStmt, 1, zNum, len, SQLITE_STATIC);
621     speedtest1_execute();
622   }
623   speedtest1_exec("COMMIT");
624   speedtest1_end_test();
625 
626   n = sz;
627   speedtest1_begin_test(180, "%d INSERTS with three indexes", n);
628   speedtest1_exec("BEGIN");
629   speedtest1_exec(
630     "CREATE TABLE t4(\n"
631     "  a INTEGER %s %s,\n"
632     "  b INTEGER %s,\n"
633     "  c TEXT %s\n"
634     ") %s",
635     g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);
636   speedtest1_exec("CREATE INDEX t4b ON t4(b)");
637   speedtest1_exec("CREATE INDEX t4c ON t4(c)");
638   speedtest1_exec("INSERT INTO t4 SELECT * FROM t1");
639   speedtest1_exec("COMMIT");
640   speedtest1_end_test();
641 
642   n = sz;
643   speedtest1_begin_test(190, "DELETE and REFILL one table", n);
644   speedtest1_exec("DELETE FROM t2;");
645   speedtest1_exec("INSERT INTO t2 SELECT * FROM t1;");
646   speedtest1_end_test();
647 
648 //  #include <emscripten.h>
649   speedtest1_begin_test(200, "VACUUM");
650 //  EM_ASM( alert('pre') );
651   speedtest1_exec("VACUUM");
652 //  EM_ASM( alert('post') );
653   speedtest1_end_test();
654 
655 
656   speedtest1_begin_test(210, "ALTER TABLE ADD COLUMN, and query");
657   speedtest1_exec("ALTER TABLE t2 ADD COLUMN d DEFAULT 123");
658   speedtest1_exec("SELECT sum(d) FROM t2");
659   speedtest1_end_test();
660 
661 
662   n = sz/5;
663   speedtest1_begin_test(230, "%d UPDATES, numeric BETWEEN, indexed", n);
664   speedtest1_exec("BEGIN");
665   speedtest1_prepare(
666     "UPDATE t2 SET d=b*2 WHERE b BETWEEN ?1 AND ?2; -- %d times", n
667   );
668   for(i=1; i<=n; i++){
669     x1 = speedtest1_random()%maxb;
670     x2 = speedtest1_random()%10 + sz/5000 + x1;
671     sqlite3_bind_int(g.pStmt, 1, x1);
672     sqlite3_bind_int(g.pStmt, 2, x2);
673     speedtest1_execute();
674   }
675   speedtest1_exec("COMMIT");
676   speedtest1_end_test();
677 
678 
679   n = sz;
680   speedtest1_begin_test(240, "%d UPDATES of individual rows", n);
681   speedtest1_exec("BEGIN");
682   speedtest1_prepare(
683     "UPDATE t2 SET d=b*3 WHERE a=?1; -- %d times", n
684   );
685   for(i=1; i<=n; i++){
686     x1 = speedtest1_random()%sz + 1;
687     sqlite3_bind_int(g.pStmt, 1, x1);
688     speedtest1_execute();
689   }
690   speedtest1_exec("COMMIT");
691   speedtest1_end_test();
692 
693   speedtest1_begin_test(250, "One big UPDATE of the whole %d-row table", sz);
694   speedtest1_exec("UPDATE t2 SET d=b*4");
695   speedtest1_end_test();
696 
697 
698   speedtest1_begin_test(260, "Query added column after filling");
699   speedtest1_exec("SELECT sum(d) FROM t2");
700   speedtest1_end_test();
701 
702 
703 
704   n = sz/5;
705   speedtest1_begin_test(270, "%d DELETEs, numeric BETWEEN, indexed", n);
706   speedtest1_exec("BEGIN");
707   speedtest1_prepare(
708     "DELETE FROM t2 WHERE b BETWEEN ?1 AND ?2; -- %d times", n
709   );
710   for(i=1; i<=n; i++){
711     x1 = speedtest1_random()%maxb + 1;
712     x2 = speedtest1_random()%10 + sz/5000 + x1;
713     sqlite3_bind_int(g.pStmt, 1, x1);
714     sqlite3_bind_int(g.pStmt, 2, x2);
715     speedtest1_execute();
716   }
717   speedtest1_exec("COMMIT");
718   speedtest1_end_test();
719 
720 
721   n = sz;
722   speedtest1_begin_test(280, "%d DELETEs of individual rows", n);
723   speedtest1_exec("BEGIN");
724   speedtest1_prepare(
725     "DELETE FROM t3 WHERE a=?1; -- %d times", n
726   );
727   for(i=1; i<=n; i++){
728     x1 = speedtest1_random()%sz + 1;
729     sqlite3_bind_int(g.pStmt, 1, x1);
730     speedtest1_execute();
731   }
732   speedtest1_exec("COMMIT");
733   speedtest1_end_test();
734 
735 
736   speedtest1_begin_test(290, "Refill two %d-row tables using REPLACE", sz);
737   speedtest1_exec("REPLACE INTO t2(a,b,c) SELECT a,b,c FROM t1");
738   speedtest1_exec("REPLACE INTO t3(a,b,c) SELECT a,b,c FROM t1");
739   speedtest1_end_test();
740 
741   speedtest1_begin_test(300, "Refill a %d-row table using (b&1)==(a&1)", sz);
742   speedtest1_exec("DELETE FROM t2;");
743   speedtest1_exec("INSERT INTO t2(a,b,c)\n"
744                   " SELECT a,b,c FROM t1  WHERE (b&1)==(a&1);");
745   speedtest1_exec("INSERT INTO t2(a,b,c)\n"
746                   " SELECT a,b,c FROM t1  WHERE (b&1)<>(a&1);");
747   speedtest1_end_test();
748 
749 
750   n = sz/5;
751   speedtest1_begin_test(310, "%d four-ways joins", n);
752   speedtest1_exec("BEGIN");
753   speedtest1_prepare(
754     "SELECT t1.c FROM t1, t2, t3, t4\n"
755     " WHERE t4.a BETWEEN ?1 AND ?2\n"
756     "   AND t3.a=t4.b\n"
757     "   AND t2.a=t3.b\n"
758     "   AND t1.c=t2.c"
759   );
760   for(i=1; i<=n; i++){
761     x1 = speedtest1_random()%sz + 1;
762     x2 = speedtest1_random()%10 + x1 + 4;
763     sqlite3_bind_int(g.pStmt, 1, x1);
764     sqlite3_bind_int(g.pStmt, 2, x2);
765     speedtest1_execute();
766   }
767   speedtest1_exec("COMMIT");
768   speedtest1_end_test();
769 
770   speedtest1_begin_test(320, "subquery in result set", n);
771   speedtest1_prepare(
772     "SELECT sum(a), max(c),\n"
773     "       avg((SELECT a FROM t2 WHERE 5+t2.b=t1.b) AND rowid<?1), max(c)\n"
774     " FROM t1 WHERE rowid<?1;"
775   );
776   sqlite3_bind_int(g.pStmt, 1, est_square_root(g.szTest)*50);
777   speedtest1_execute();
778   speedtest1_end_test();
779 
780   speedtest1_begin_test(980, "PRAGMA integrity_check");
781   speedtest1_exec("PRAGMA integrity_check");
782   speedtest1_end_test();
783 
784 
785   speedtest1_begin_test(990, "ANALYZE");
786   speedtest1_exec("ANALYZE");
787   speedtest1_end_test();
788 }
789 
790 /*
791 ** A testset for common table expressions.  This exercises code
792 ** for views, subqueries, co-routines, etc.
793 */
testset_cte(void)794 void testset_cte(void){
795   static const char *azPuzzle[] = {
796     /* Easy */
797     "534...9.."
798     "67.195..."
799     ".98....6."
800     "8...6...3"
801     "4..8.3..1"
802     "....2...6"
803     ".6....28."
804     "...419..5"
805     "...28..79",
806 
807     /* Medium */
808     "53....9.."
809     "6..195..."
810     ".98....6."
811     "8...6...3"
812     "4..8.3..1"
813     "....2...6"
814     ".6....28."
815     "...419..5"
816     "....8..79",
817 
818     /* Hard */
819     "53......."
820     "6..195..."
821     ".98....6."
822     "8...6...3"
823     "4..8.3..1"
824     "....2...6"
825     ".6....28."
826     "...419..5"
827     "....8..79",
828   };
829   const char *zPuz;
830   double rSpacing;
831   int nElem;
832 
833   if( g.szTest<25 ){
834     zPuz = azPuzzle[0];
835   }else if( g.szTest<70 ){
836     zPuz = azPuzzle[1];
837   }else{
838     zPuz = azPuzzle[2];
839   }
840   speedtest1_begin_test(100, "Sudoku with recursive 'digits'");
841   speedtest1_prepare(
842     "WITH RECURSIVE\n"
843     "  input(sud) AS (VALUES(?1)),\n"
844     "  digits(z,lp) AS (\n"
845     "    VALUES('1', 1)\n"
846     "    UNION ALL\n"
847     "    SELECT CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9\n"
848     "  ),\n"
849     "  x(s, ind) AS (\n"
850     "    SELECT sud, instr(sud, '.') FROM input\n"
851     "    UNION ALL\n"
852     "    SELECT\n"
853     "      substr(s, 1, ind-1) || z || substr(s, ind+1),\n"
854     "      instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )\n"
855     "     FROM x, digits AS z\n"
856     "    WHERE ind>0\n"
857     "      AND NOT EXISTS (\n"
858     "            SELECT 1\n"
859     "              FROM digits AS lp\n"
860     "             WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)\n"
861     "                OR z.z = substr(s, ((ind-1)%%9) + (lp-1)*9 + 1, 1)\n"
862     "                OR z.z = substr(s, (((ind-1)/3) %% 3) * 3\n"
863     "                        + ((ind-1)/27) * 27 + lp\n"
864     "                        + ((lp-1) / 3) * 6, 1)\n"
865     "         )\n"
866     "  )\n"
867     "SELECT s FROM x WHERE ind=0;"
868   );
869   sqlite3_bind_text(g.pStmt, 1, zPuz, -1, SQLITE_STATIC);
870   speedtest1_execute();
871   speedtest1_end_test();
872 
873   speedtest1_begin_test(200, "Sudoku with VALUES 'digits'");
874   speedtest1_prepare(
875     "WITH RECURSIVE\n"
876     "  input(sud) AS (VALUES(?1)),\n"
877     "  digits(z,lp) AS (VALUES('1',1),('2',2),('3',3),('4',4),('5',5),\n"
878     "                         ('6',6),('7',7),('8',8),('9',9)),\n"
879     "  x(s, ind) AS (\n"
880     "    SELECT sud, instr(sud, '.') FROM input\n"
881     "    UNION ALL\n"
882     "    SELECT\n"
883     "      substr(s, 1, ind-1) || z || substr(s, ind+1),\n"
884     "      instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )\n"
885     "     FROM x, digits AS z\n"
886     "    WHERE ind>0\n"
887     "      AND NOT EXISTS (\n"
888     "            SELECT 1\n"
889     "              FROM digits AS lp\n"
890     "             WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)\n"
891     "                OR z.z = substr(s, ((ind-1)%%9) + (lp-1)*9 + 1, 1)\n"
892     "                OR z.z = substr(s, (((ind-1)/3) %% 3) * 3\n"
893     "                        + ((ind-1)/27) * 27 + lp\n"
894     "                        + ((lp-1) / 3) * 6, 1)\n"
895     "         )\n"
896     "  )\n"
897     "SELECT s FROM x WHERE ind=0;"
898   );
899   sqlite3_bind_text(g.pStmt, 1, zPuz, -1, SQLITE_STATIC);
900   speedtest1_execute();
901   speedtest1_end_test();
902 
903   rSpacing = 5.0/g.szTest;
904   speedtest1_begin_test(300, "Mandelbrot Set with spacing=%f", rSpacing);
905   speedtest1_prepare(
906    "WITH RECURSIVE \n"
907    "  xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+?1 FROM xaxis WHERE x<1.2),\n"
908    "  yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+?2 FROM yaxis WHERE y<1.0),\n"
909    "  m(iter, cx, cy, x, y) AS (\n"
910    "    SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis\n"
911    "    UNION ALL\n"
912    "    SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m \n"
913    "     WHERE (x*x + y*y) < 4.0 AND iter<28\n"
914    "  ),\n"
915    "  m2(iter, cx, cy) AS (\n"
916    "    SELECT max(iter), cx, cy FROM m GROUP BY cx, cy\n"
917    "  ),\n"
918    "  a(t) AS (\n"
919    "    SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') \n"
920    "    FROM m2 GROUP BY cy\n"
921    "  )\n"
922    "SELECT group_concat(rtrim(t),x'0a') FROM a;"
923   );
924   sqlite3_bind_double(g.pStmt, 1, rSpacing*.05);
925   sqlite3_bind_double(g.pStmt, 2, rSpacing);
926   speedtest1_execute();
927   speedtest1_end_test();
928 
929   nElem = 10000*g.szTest;
930   speedtest1_begin_test(400, "EXCEPT operator on %d-element tables", nElem);
931   speedtest1_prepare(
932     "WITH RECURSIVE \n"
933     "  t1(x) AS (VALUES(2) UNION ALL SELECT x+2 FROM t1 WHERE x<%d),\n"
934     "  t2(y) AS (VALUES(3) UNION ALL SELECT y+3 FROM t2 WHERE y<%d)\n"
935     "SELECT count(x), avg(x) FROM (\n"
936     "  SELECT x FROM t1 EXCEPT SELECT y FROM t2 ORDER BY 1\n"
937     ");",
938     nElem, nElem
939   );
940   speedtest1_execute();
941   speedtest1_end_test();
942 
943 }
944 
945 /* Generate two numbers between 1 and mx.  The first number is less than
946 ** the second.  Usually the numbers are near each other but can sometimes
947 ** be far apart.
948 */
twoCoords(int p1,int p2,unsigned mx,unsigned * pX0,unsigned * pX1)949 static void twoCoords(
950   int p1, int p2,                   /* Parameters adjusting sizes */
951   unsigned mx,                      /* Range of 1..mx */
952   unsigned *pX0, unsigned *pX1      /* OUT: write results here */
953 ){
954   unsigned d, x0, x1, span;
955 
956   span = mx/100 + 1;
957   if( speedtest1_random()%3==0 ) span *= p1;
958   if( speedtest1_random()%p2==0 ) span = mx/2;
959   d = speedtest1_random()%span + 1;
960   x0 = speedtest1_random()%(mx-d) + 1;
961   x1 = x0 + d;
962   *pX0 = x0;
963   *pX1 = x1;
964 }
965 
966 /* The following routine is an R-Tree geometry callback.  It returns
967 ** true if the object overlaps a slice on the Y coordinate between the
968 ** two values given as arguments.  In other words
969 **
970 **     SELECT count(*) FROM rt1 WHERE id MATCH xslice(10,20);
971 **
972 ** Is the same as saying:
973 **
974 **     SELECT count(*) FROM rt1 WHERE y1>=10 AND y0<=20;
975 */
xsliceGeometryCallback(sqlite3_rtree_geometry * p,int nCoord,double * aCoord,int * pRes)976 static int xsliceGeometryCallback(
977   sqlite3_rtree_geometry *p,
978   int nCoord,
979   double *aCoord,
980   int *pRes
981 ){
982   *pRes = aCoord[3]>=p->aParam[0] && aCoord[2]<=p->aParam[1];
983   return SQLITE_OK;
984 }
985 
986 /*
987 ** A testset for the R-Tree virtual table
988 */
testset_rtree(int p1,int p2)989 void testset_rtree(int p1, int p2){
990   unsigned i, n;
991   unsigned mxCoord;
992   unsigned x0, x1, y0, y1, z0, z1;
993   unsigned iStep;
994   int *aCheck = sqlite3_malloc( sizeof(int)*g.szTest*100 );
995 
996   mxCoord = 15000;
997   n = g.szTest*100;
998   speedtest1_begin_test(100, "%d INSERTs into an r-tree", n);
999   speedtest1_exec("BEGIN");
1000   speedtest1_exec("CREATE VIRTUAL TABLE rt1 USING rtree(id,x0,x1,y0,y1,z0,z1)");
1001   speedtest1_prepare("INSERT INTO rt1(id,x0,x1,y0,y1,z0,z1)"
1002                      "VALUES(?1,?2,?3,?4,?5,?6,?7)");
1003   for(i=1; i<=n; i++){
1004     twoCoords(p1, p2, mxCoord, &x0, &x1);
1005     twoCoords(p1, p2, mxCoord, &y0, &y1);
1006     twoCoords(p1, p2, mxCoord, &z0, &z1);
1007     sqlite3_bind_int(g.pStmt, 1, i);
1008     sqlite3_bind_int(g.pStmt, 2, x0);
1009     sqlite3_bind_int(g.pStmt, 3, x1);
1010     sqlite3_bind_int(g.pStmt, 4, y0);
1011     sqlite3_bind_int(g.pStmt, 5, y1);
1012     sqlite3_bind_int(g.pStmt, 6, z0);
1013     sqlite3_bind_int(g.pStmt, 7, z1);
1014     speedtest1_execute();
1015   }
1016   speedtest1_exec("COMMIT");
1017   speedtest1_end_test();
1018 
1019   speedtest1_begin_test(101, "Copy from rtree to a regular table");
1020   speedtest1_exec("CREATE TABLE t1(id INTEGER PRIMARY KEY,x0,x1,y0,y1,z0,z1)");
1021   speedtest1_exec("INSERT INTO t1 SELECT * FROM rt1");
1022   speedtest1_end_test();
1023 
1024   n = g.szTest*20;
1025   speedtest1_begin_test(110, "%d one-dimensional intersect slice queries", n);
1026   speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x0>=?1 AND x1<=?2");
1027   iStep = mxCoord/n;
1028   for(i=0; i<n; i++){
1029     sqlite3_bind_int(g.pStmt, 1, i*iStep);
1030     sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1031     speedtest1_execute();
1032     aCheck[i] = atoi(g.zResult);
1033   }
1034   speedtest1_end_test();
1035 
1036   if( g.bVerify ){
1037     n = g.szTest*20;
1038     speedtest1_begin_test(111, "Verify result from 1-D intersect slice queries");
1039     speedtest1_prepare("SELECT count(*) FROM t1 WHERE x0>=?1 AND x1<=?2");
1040     iStep = mxCoord/n;
1041     for(i=0; i<n; i++){
1042       sqlite3_bind_int(g.pStmt, 1, i*iStep);
1043       sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1044       speedtest1_execute();
1045       if( aCheck[i]!=atoi(g.zResult) ){
1046         fatal_error("Count disagree step %d: %d..%d.  %d vs %d",
1047                     i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
1048       }
1049     }
1050     speedtest1_end_test();
1051   }
1052 
1053   n = g.szTest*20;
1054   speedtest1_begin_test(120, "%d one-dimensional overlap slice queries", n);
1055   speedtest1_prepare("SELECT count(*) FROM rt1 WHERE y1>=?1 AND y0<=?2");
1056   iStep = mxCoord/n;
1057   for(i=0; i<n; i++){
1058     sqlite3_bind_int(g.pStmt, 1, i*iStep);
1059     sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1060     speedtest1_execute();
1061     aCheck[i] = atoi(g.zResult);
1062   }
1063   speedtest1_end_test();
1064 
1065   if( g.bVerify ){
1066     n = g.szTest*20;
1067     speedtest1_begin_test(121, "Verify result from 1-D overlap slice queries");
1068     speedtest1_prepare("SELECT count(*) FROM t1 WHERE y1>=?1 AND y0<=?2");
1069     iStep = mxCoord/n;
1070     for(i=0; i<n; i++){
1071       sqlite3_bind_int(g.pStmt, 1, i*iStep);
1072       sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1073       speedtest1_execute();
1074       if( aCheck[i]!=atoi(g.zResult) ){
1075         fatal_error("Count disagree step %d: %d..%d.  %d vs %d",
1076                     i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
1077       }
1078     }
1079     speedtest1_end_test();
1080   }
1081 
1082 
1083   n = g.szTest*20;
1084   speedtest1_begin_test(125, "%d custom geometry callback queries", n);
1085   //sqlite3_rtree_geometry_callback(g.db, "xslice", xsliceGeometryCallback, 0);
1086   speedtest1_prepare("SELECT count(*) FROM rt1 WHERE id MATCH xslice(?1,?2)");
1087   iStep = mxCoord/n;
1088   for(i=0; i<n; i++){
1089     sqlite3_bind_int(g.pStmt, 1, i*iStep);
1090     sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1091     speedtest1_execute();
1092     if( aCheck[i]!=atoi(g.zResult) ){
1093       fatal_error("Count disagree step %d: %d..%d.  %d vs %d",
1094                   i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
1095     }
1096   }
1097   speedtest1_end_test();
1098 
1099   n = g.szTest*80;
1100   speedtest1_begin_test(130, "%d three-dimensional intersect box queries", n);
1101   speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x1>=?1 AND x0<=?2"
1102                      " AND y1>=?1 AND y0<=?2 AND z1>=?1 AND z0<=?2");
1103   iStep = mxCoord/n;
1104   for(i=0; i<n; i++){
1105     sqlite3_bind_int(g.pStmt, 1, i*iStep);
1106     sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1107     speedtest1_execute();
1108     aCheck[i] = atoi(g.zResult);
1109   }
1110   speedtest1_end_test();
1111 
1112   n = g.szTest*100;
1113   speedtest1_begin_test(140, "%d rowid queries", n);
1114   speedtest1_prepare("SELECT * FROM rt1 WHERE id=?1");
1115   for(i=1; i<=n; i++){
1116     sqlite3_bind_int(g.pStmt, 1, i);
1117     speedtest1_execute();
1118   }
1119   speedtest1_end_test();
1120 }
1121 
1122 /*
1123 ** A testset used for debugging speedtest1 itself.
1124 */
testset_debug1(void)1125 void testset_debug1(void){
1126   unsigned i, n;
1127   unsigned x1, x2;
1128   char zNum[2000];              /* A number name */
1129 
1130   n = g.szTest;
1131   for(i=1; i<=n; i++){
1132     x1 = swizzle(i, n);
1133     x2 = swizzle(x1, n);
1134     speedtest1_numbername(x1, zNum, sizeof(zNum));
1135     printf("%5d %5d %5d %s\n", i, x1, x2, zNum);
1136   }
1137 }
1138 
main(int argc,char ** argv)1139 int main(int argc, char **argv){
1140   int doAutovac = 0;            /* True for --autovacuum */
1141   int cacheSize = 0;            /* Desired cache size.  0 means default */
1142   int doExclusive = 0;          /* True for --exclusive */
1143   int nHeap = 0, mnHeap = 0;    /* Heap size from --heap */
1144   int doIncrvac = 0;            /* True for --incrvacuum */
1145   const char *zJMode = 0;       /* Journal mode */
1146   const char *zKey = 0;         /* Encryption key */
1147   int nLook = 0, szLook = 0;    /* --lookaside configuration */
1148   int noSync = 0;               /* True for --nosync */
1149   int pageSize = 0;             /* Desired page size.  0 means default */
1150   int nPCache = 0, szPCache = 0;/* --pcache configuration */
1151   int nScratch = 0, szScratch=0;/* --scratch configuration */
1152   int showStats = 0;            /* True for --stats */
1153   const char *zTSet = "main";   /* Which --testset torun */
1154   int doTrace = 0;              /* True for --trace */
1155   const char *zEncoding = 0;    /* --utf16be or --utf16le */
1156   const char *zDbName = 0;      /* Name of the test database */
1157 
1158   void *pHeap = 0;              /* Allocated heap space */
1159   void *pLook = 0;              /* Allocated lookaside space */
1160   void *pPCache = 0;            /* Allocated storage for pcache */
1161   void *pScratch = 0;           /* Allocated storage for scratch */
1162   int iCur, iHi;                /* Stats values, current and "highwater" */
1163   int i;                        /* Loop counter */
1164   int rc;                       /* API return code */
1165 
1166   /* Process command-line arguments */
1167   g.zWR = "";
1168   g.zNN = "";
1169   g.zPK = "UNIQUE";
1170   g.szTest = 100;
1171 
1172   /* Emscripten commandline argument processing - first arg is ours */
1173   int arg = argc > 1 ? argv[1][0] - '0' : 3;
1174   switch(arg) {
1175     case 0: return 0; break;
1176     case 1: arg = 5; break;
1177     case 2: arg = 15; break;
1178     case 3: arg = 40; break;
1179     case 4: arg = 80; break;
1180     case 5: arg = 160; break;
1181     default:
1182       printf("error: %d\\n", arg);
1183       return -1;
1184   }
1185   g.szTest = arg;
1186 
1187   /* Back to sqlite - after the first is theirs*/
1188   for(i=2; i<argc; i++){
1189     const char *z = argv[i];
1190     if( z[0]=='-' ){
1191       do{ z++; }while( z[0]=='-' );
1192       if( strcmp(z,"autovacuum")==0 ){
1193         doAutovac = 1;
1194       }else if( strcmp(z,"cachesize")==0 ){
1195         if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
1196         i++;
1197         cacheSize = integerValue(argv[i]);
1198       }else if( strcmp(z,"exclusive")==0 ){
1199         doExclusive = 1;
1200       }else if( strcmp(z,"explain")==0 ){
1201         g.bSqlOnly = 1;
1202         g.bExplain = 1;
1203       }else if( strcmp(z,"heap")==0 ){
1204         if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]);
1205         nHeap = integerValue(argv[i+1]);
1206         mnHeap = integerValue(argv[i+2]);
1207         i += 2;
1208       }else if( strcmp(z,"incrvacuum")==0 ){
1209         doIncrvac = 1;
1210       }else if( strcmp(z,"journal")==0 ){
1211         if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
1212         zJMode = argv[++i];
1213       }else if( strcmp(z,"key")==0 ){
1214         if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
1215         zKey = argv[++i];
1216       }else if( strcmp(z,"lookaside")==0 ){
1217         if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]);
1218         nLook = integerValue(argv[i+1]);
1219         szLook = integerValue(argv[i+2]);
1220         i += 2;
1221       }else if( strcmp(z,"nosync")==0 ){
1222         noSync = 1;
1223       }else if( strcmp(z,"notnull")==0 ){
1224         g.zNN = "NOT NULL";
1225       }else if( strcmp(z,"pagesize")==0 ){
1226         if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
1227         pageSize = integerValue(argv[++i]);
1228       }else if( strcmp(z,"pcache")==0 ){
1229         if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]);
1230         nPCache = integerValue(argv[i+1]);
1231         szPCache = integerValue(argv[i+2]);
1232         i += 2;
1233       }else if( strcmp(z,"primarykey")==0 ){
1234         g.zPK = "PRIMARY KEY";
1235       }else if( strcmp(z,"reprepare")==0 ){
1236         g.bReprepare = 1;
1237       }else if( strcmp(z,"scratch")==0 ){
1238         if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]);
1239         nScratch = integerValue(argv[i+1]);
1240         szScratch = integerValue(argv[i+2]);
1241         i += 2;
1242       }else if( strcmp(z,"sqlonly")==0 ){
1243         g.bSqlOnly = 1;
1244       }else if( strcmp(z,"size")==0 ){
1245         if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
1246         g.szTest = integerValue(argv[++i]);
1247       }else if( strcmp(z,"stats")==0 ){
1248         showStats = 1;
1249       }else if( strcmp(z,"testset")==0 ){
1250         if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
1251         zTSet = argv[++i];
1252       }else if( strcmp(z,"trace")==0 ){
1253         doTrace = 1;
1254       }else if( strcmp(z,"utf16le")==0 ){
1255         zEncoding = "utf16le";
1256       }else if( strcmp(z,"utf16be")==0 ){
1257         zEncoding = "utf16be";
1258       }else if( strcmp(z,"verify")==0 ){
1259         g.bVerify = 1;
1260       }else if( strcmp(z,"without-rowid")==0 ){
1261         g.zWR = "WITHOUT ROWID";
1262         g.zPK = "PRIMARY KEY";
1263       }else if( strcmp(z, "help")==0 || strcmp(z,"?")==0 ){
1264         printf(zHelp, argv[0]);
1265         exit(0);
1266       }else{
1267         fatal_error("unknown option: %s\nUse \"%s -?\" for help\n",
1268                     argv[i], argv[0]);
1269       }
1270     }else if( zDbName==0 ){
1271       zDbName = argv[i];
1272     }else{
1273       fatal_error("surplus argument: %s\nUse \"%s -?\" for help\n",
1274                   argv[i], argv[0]);
1275     }
1276   }
1277 #if 0
1278   if( zDbName==0 ){
1279     fatal_error(zHelp, argv[0]);
1280   }
1281 #endif
1282   if( nHeap>0 ){
1283     pHeap = malloc( nHeap );
1284     if( pHeap==0 ) fatal_error("cannot allocate %d-byte heap\n", nHeap);
1285     rc = sqlite3_config(SQLITE_CONFIG_HEAP, pHeap, nHeap, mnHeap);
1286     if( rc ) fatal_error("heap configuration failed: %d\n", rc);
1287   }
1288   if( nPCache>0 && szPCache>0 ){
1289     pPCache = malloc( nPCache*(sqlite3_int64)szPCache );
1290     if( pPCache==0 ) fatal_error("cannot allocate %lld-byte pcache\n",
1291                                  nPCache*(sqlite3_int64)szPCache);
1292     rc = sqlite3_config(SQLITE_CONFIG_PAGECACHE, pPCache, szPCache, nPCache);
1293     if( rc ) fatal_error("pcache configuration failed: %d\n", rc);
1294   }
1295   if( nScratch>0 && szScratch>0 ){
1296     pScratch = malloc( nScratch*(sqlite3_int64)szScratch );
1297     if( pScratch==0 ) fatal_error("cannot allocate %lld-byte scratch\n",
1298                                  nScratch*(sqlite3_int64)szScratch);
1299     rc = sqlite3_config(SQLITE_CONFIG_SCRATCH, pScratch, szScratch, nScratch);
1300     if( rc ) fatal_error("scratch configuration failed: %d\n", rc);
1301   }
1302   if( nLook>0 ){
1303     sqlite3_config(SQLITE_CONFIG_LOOKASIDE, 0, 0);
1304   }
1305 
1306   /* Open the database and the input file */
1307   if( sqlite3_open(":memory:", &g.db) ){
1308     fatal_error("Cannot open database file: %s\n", zDbName);
1309   }
1310   if( nLook>0 && szLook>0 ){
1311     pLook = malloc( nLook*szLook );
1312     rc = sqlite3_db_config(g.db, SQLITE_DBCONFIG_LOOKASIDE, pLook, szLook,nLook);
1313     if( rc ) fatal_error("lookaside configuration failed: %d\n", rc);
1314   }
1315 
1316   /* Set database connection options */
1317   sqlite3_create_function(g.db, "random", 0, SQLITE_UTF8, 0, randomFunc1, 0, 0);
1318   if( doTrace ) sqlite3_trace(g.db, traceCallback, 0);
1319   if( zKey ){
1320     speedtest1_exec("PRAGMA key('%s')", zKey);
1321   }
1322   if( zEncoding ){
1323     speedtest1_exec("PRAGMA encoding=%s", zEncoding);
1324   }
1325   if( doAutovac ){
1326     speedtest1_exec("PRAGMA auto_vacuum=FULL");
1327   }else if( doIncrvac ){
1328     speedtest1_exec("PRAGMA auto_vacuum=INCREMENTAL");
1329   }
1330   if( pageSize ){
1331     speedtest1_exec("PRAGMA page_size=%d", pageSize);
1332   }
1333   if( cacheSize ){
1334     speedtest1_exec("PRAGMA cache_size=%d", cacheSize);
1335   }
1336   if( noSync ) speedtest1_exec("PRAGMA synchronous=OFF");
1337   if( doExclusive ){
1338     speedtest1_exec("PRAGMA locking_mode=EXCLUSIVE");
1339   }
1340   if( zJMode ){
1341     speedtest1_exec("PRAGMA journal_mode=%s", zJMode);
1342   }
1343 
1344   if( g.bExplain ) printf(".explain\n.echo on\n");
1345   if( strcmp(zTSet,"main")==0 ){
1346     testset_main();
1347   }else if( strcmp(zTSet,"debug1")==0 ){
1348     testset_debug1();
1349   }else if( strcmp(zTSet,"cte")==0 ){
1350     testset_cte();
1351   }else if( strcmp(zTSet,"rtree")==0 ){
1352     testset_rtree(6, 147);
1353   }else{
1354     fatal_error("unknown testset: \"%s\"\nChoices: main debug1 cte rtree\n",
1355                  zTSet);
1356   }
1357   speedtest1_final();
1358 
1359   /* Database connection statistics printed after both prepared statements
1360   ** have been finalized */
1361 #if SQLITE_VERSION_NUMBER>=3007009
1362   if( showStats ){
1363     sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_USED, &iCur, &iHi, 0);
1364     printf("-- Lookaside Slots Used:        %d (max %d)\n", iCur,iHi);
1365     sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_HIT, &iCur, &iHi, 0);
1366     printf("-- Successful lookasides:       %d\n", iHi);
1367     sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE, &iCur,&iHi,0);
1368     printf("-- Lookaside size faults:       %d\n", iHi);
1369     sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL, &iCur,&iHi,0);
1370     printf("-- Lookaside OOM faults:        %d\n", iHi);
1371     sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_USED, &iCur, &iHi, 0);
1372     printf("-- Pager Heap Usage:            %d bytes\n", iCur);
1373     sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_HIT, &iCur, &iHi, 1);
1374     printf("-- Page cache hits:             %d\n", iCur);
1375     sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_MISS, &iCur, &iHi, 1);
1376     printf("-- Page cache misses:           %d\n", iCur);
1377 #if SQLITE_VERSION_NUMBER>=3007012
1378     sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_WRITE, &iCur, &iHi, 1);
1379     printf("-- Page cache writes:           %d\n", iCur);
1380 #endif
1381     sqlite3_db_status(g.db, SQLITE_DBSTATUS_SCHEMA_USED, &iCur, &iHi, 0);
1382     printf("-- Schema Heap Usage:           %d bytes\n", iCur);
1383     sqlite3_db_status(g.db, SQLITE_DBSTATUS_STMT_USED, &iCur, &iHi, 0);
1384     printf("-- Statement Heap Usage:        %d bytes\n", iCur);
1385   }
1386 #endif
1387 
1388   sqlite3_close(g.db);
1389 
1390   /* Global memory usage statistics printed after the database connection
1391   ** has closed.  Memory usage should be zero at this point. */
1392   if( showStats ){
1393     sqlite3_status(SQLITE_STATUS_MEMORY_USED, &iCur, &iHi, 0);
1394     printf("-- Memory Used (bytes):         %d (max %d)\n", iCur,iHi);
1395 #if SQLITE_VERSION_NUMBER>=3007000
1396     sqlite3_status(SQLITE_STATUS_MALLOC_COUNT, &iCur, &iHi, 0);
1397     printf("-- Outstanding Allocations:     %d (max %d)\n", iCur,iHi);
1398 #endif
1399     sqlite3_status(SQLITE_STATUS_PAGECACHE_OVERFLOW, &iCur, &iHi, 0);
1400     printf("-- Pcache Overflow Bytes:       %d (max %d)\n", iCur,iHi);
1401     sqlite3_status(SQLITE_STATUS_SCRATCH_OVERFLOW, &iCur, &iHi, 0);
1402     printf("-- Scratch Overflow Bytes:      %d (max %d)\n", iCur,iHi);
1403     sqlite3_status(SQLITE_STATUS_MALLOC_SIZE, &iCur, &iHi, 0);
1404     printf("-- Largest Allocation:          %d bytes\n",iHi);
1405     sqlite3_status(SQLITE_STATUS_PAGECACHE_SIZE, &iCur, &iHi, 0);
1406     printf("-- Largest Pcache Allocation:   %d bytes\n",iHi);
1407     sqlite3_status(SQLITE_STATUS_SCRATCH_SIZE, &iCur, &iHi, 0);
1408     printf("-- Largest Scratch Allocation:  %d bytes\n", iHi);
1409   }
1410 
1411   /* Release memory */
1412   free( pLook );
1413   free( pPCache );
1414   free( pScratch );
1415   free( pHeap );
1416   return 0;
1417 }
1418