test/wordcount.c

   1 /*
   2 ** This C program extracts all "words" from an input document and adds them
   3 ** to an SQLite database.  A "word" is any contiguous sequence of alphabetic
   4 ** characters.  All digits, punctuation, and whitespace characters are
   5 ** word separators.  The database stores a single entry for each distinct
   6 ** word together with a count of the number of occurrences of that word.
   7 ** A fresh database is created automatically on each run.
   8 **
   9 **     wordcount DATABASE INPUTFILE
  10 **
  11 ** The INPUTFILE name can be omitted, in which case input it taken from
  12 ** standard input.
  13 **
  14 ** Option:
  15 **
  16 **
  17 ** Modes:
  18 **
  19 ** Insert mode means:
  20 **    (1) INSERT OR IGNORE INTO wordcount VALUES($new,1)
  21 **    (2) UPDATE wordcount SET cnt=cnt+1 WHERE word=$new -- if (1) is a noop
  22 **
  23 ** Update mode means:
  24 **    (1) INSERT OR IGNORE INTO wordcount VALUES($new,0)
  25 **    (2) UPDATE wordcount SET cnt=cnt+1 WHERE word=$new
  26 **
  27 ** Replace mode means:
  28 **    (1) REPLACE INTO wordcount
  29 **        VALUES($new,ifnull((SELECT cnt FROM wordcount WHERE word=$new),0)+1);
  30 **
  31 ** Select mode means:
  32 **    (1) SELECT 1 FROM wordcount WHERE word=$new
  33 **    (2) INSERT INTO wordcount VALUES($new,1) -- if (1) returns nothing
  34 **    (3) UPDATE wordcount SET cnt=cnt+1 WHERE word=$new  --if (1) return TRUE
  35 **
  36 ** Delete mode means:
  37 **    (1) DELETE FROM wordcount WHERE word=$new
  38 **
  39 ** Query mode means:
  40 **    (1) SELECT cnt FROM wordcount WHERE word=$new
  41 **
  42 ** Note that delete mode and query mode are only useful for preexisting
  43 ** databases.  The wordcount table is created using IF NOT EXISTS so this
  44 ** utility can be run multiple times on the same database file.  The
  45 ** --without-rowid, --nocase, and --pagesize parameters are only effective
  46 ** when creating a new database and are harmless no-ops on preexisting
  47 ** databases.
  48 **
  49 ******************************************************************************
  50 **
  51 ** Compile as follows:
  52 **
  53 **    gcc -I. wordcount.c sqlite3.c -ldl -lpthreads
  54 **
  55 ** Or:
  56 **
  57 **    gcc -I. -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION \
  58 **        wordcount.c sqlite3.c
  59 */
  60 #include <stdio.h>
  61 #include <string.h>
  62 #include <ctype.h>
  63 #include <stdlib.h>
  64 #include <stdarg.h>
  65 #include "sqlite3.h"
  66 #ifndef _WIN32
  67 # include <unistd.h>
  68 #else
  69 # include <io.h>
  70 #endif
  71 #define ISALPHA(X) isalpha((unsigned char)(X))
  72
  73 const char zHelp[] =
  74 "Usage: wordcount [OPTIONS] DATABASE [INPUT]\n"
  75 " --all                Repeat the test for all test modes\n"
  76 " --cachesize NNN      Use a cache size of NNN\n"
  77 " --commit NNN         Commit after every NNN operations\n"
  78 " --delete             Use DELETE mode\n"
  79 " --insert             Use INSERT mode (the default)\n"
  80 " --journal MMMM       Use PRAGMA journal_mode=MMMM\n"
  81 " --nocase             Add the NOCASE collating sequence to the words.\n"
  82 " --nosync             Use PRAGMA synchronous=OFF\n"
  83 " --pagesize NNN       Use a page size of NNN\n"
  84 " --query              Use QUERY mode\n"
  85 " --replace            Use REPLACE mode\n"
  86 " --select             Use SELECT mode\n"
  87 " --stats              Show sqlite3_status() results at the end.\n"
  88 " --summary            Show summary information on the collected data.\n"
  89 " --tag NAME           Tag all output using NAME.  Use only stdout.\n"
  90 " --timer              Time the operation of this program\n"
  91 " --trace              Enable sqlite3_trace() output.\n"
  92 " --update             Use UPDATE mode\n"
  93 " --without-rowid      Use a WITHOUT ROWID table to store the words.\n"
  94 ;
  95
  96 /* Output tag */
  97 char *zTag = "--";
  98
  99 /* Return the current wall-clock time */
 100 static sqlite3_int64 realTime(void){
 101   static sqlite3_vfs *clockVfs = 0;
 102   sqlite3_int64 t;
 103   if( clockVfs==0 ) clockVfs = sqlite3_vfs_find(0);
 104   if( clockVfs->iVersion>=1 && clockVfs->xCurrentTimeInt64!=0 ){
 105     clockVfs->xCurrentTimeInt64(clockVfs, &t);
 106   }else{
 107     double r;
 108     clockVfs->xCurrentTime(clockVfs, &r);
 109     t = (sqlite3_int64)(r*86400000.0);
 110   }
 111   return t;
 112 }
 113
 114 /* Print an error message and exit */
 115 static void fatal_error(const char *zMsg, ...){
 116   va_list ap;
 117   va_start(ap, zMsg);
 118   vfprintf(stderr, zMsg, ap);
 119   va_end(ap);
 120   exit(1);
 121 }
 122
 123 /* Print a usage message and quit */
 124 static void usage(void){
 125   printf("%s",zHelp);
 126   exit(0);
 127 }
 128
 129 /* The sqlite3_trace() callback function */
 130 static void traceCallback(void *NotUsed, const char *zSql){
 131   printf("%s;\n", zSql);
 132 }
 133
 134 /* An sqlite3_exec() callback that prints results on standard output,
 135 ** each column separated by a single space. */
 136 static int printResult(void *NotUsed, int nArg, char **azArg, char **azNm){
 137   int i;
 138   printf("%s", zTag);
 139   for(i=0; i<nArg; i++){
 140     printf(" %s", azArg[i] ? azArg[i] : "(null)");
 141   }
 142   printf("\n");
 143   return 0;
 144 }
 145
 146
 147 /*
 148 ** Add one character to a hash
 149 */
 150 static void addCharToHash(unsigned int *a, unsigned char x){
 151   if( a[0]<4 ){
 152     a[1] = (a[1]<<8) | x;
 153     a[0]++;
 154   }else{
 155     a[2] = (a[2]<<8) | x;
 156     a[0]++;
 157     if( a[0]==8 ){
 158       a[3] += a[1] + a[4];
 159       a[4] += a[2] + a[3];
 160       a[0] = a[1] = a[2] = 0;
 161     }
 162   }
 163 }
 164
 165 /*
 166 ** Compute the final hash value.
 167 */
 168 static void finalHash(unsigned int *a, char *z){
 169   a[3] += a[1] + a[4] + a[0];
 170   a[4] += a[2] + a[3];
 171   sqlite3_snprintf(17, z, "%08x%08x", a[3], a[4]);
 172 }
 173
 174
 175 /*
 176 ** Implementation of a checksum() aggregate SQL function
 177 */
 178 static void checksumStep(
 179   sqlite3_context *context,
 180   int argc,
 181   sqlite3_value **argv
 182 ){
 183   const unsigned char *zVal;
 184   int nVal, i, j;
 185   unsigned int *a;
 186   a = (unsigned*)sqlite3_aggregate_context(context, sizeof(unsigned int)*5);
 187
 188   if( a ){
 189     for(i=0; i<argc; i++){
 190       nVal = sqlite3_value_bytes(argv[i]);
 191       zVal = (const unsigned char*)sqlite3_value_text(argv[i]);
 192       if( zVal ) for(j=0; j<nVal; j++) addCharToHash(a, zVal[j]);
 193       addCharToHash(a, '|');
 194     }
 195     addCharToHash(a, '\n');
 196   }
 197 }
 198 static void checksumFinalize(sqlite3_context *context){
 199   unsigned int *a;
 200   char zResult[24];
 201   a = sqlite3_aggregate_context(context, 0);
 202   if( a ){
 203     finalHash(a, zResult);
 204     sqlite3_result_text(context, zResult, -1, SQLITE_TRANSIENT);
 205   }
 206 }
 207
 208 /* Define operating modes */
 209 #define MODE_INSERT     0
 210 #define MODE_REPLACE    1
 211 #define MODE_SELECT     2
 212 #define MODE_UPDATE     3
 213 #define MODE_DELETE     4
 214 #define MODE_QUERY      5
 215 #define MODE_COUNT      6
 216 #define MODE_ALL      (-1)
 217
 218 /* Mode names */
 219 static const char *azMode[] = {
 220   "--insert",
 221   "--replace",
 222   "--select",
 223   "--update",
 224   "--delete",
 225   "--query"
 226 };
 227
 228 /*
 229 ** Determine if another iteration of the test is required.  Return true
 230 ** if so.  Return zero if all iterations have finished.
 231 */
 232 static int allLoop(
 233   int iMode,                /* The selected test mode */
 234   int *piLoopCnt,           /* Iteration loop counter */
 235   int *piMode2,             /* The test mode to use on the next iteration */
 236   int *pUseWithoutRowid     /* Whether or not to use --without-rowid */
 237 ){
 238   int i;
 239   if( iMode!=MODE_ALL ){
 240     if( *piLoopCnt ) return 0;
 241     *piMode2 = iMode;
 242     *piLoopCnt = 1;
 243     return 1;
 244   }
 245   if( (*piLoopCnt)>=MODE_COUNT*2 ) return 0;
 246   i = (*piLoopCnt)++;
 247   *pUseWithoutRowid = i&1;
 248   *piMode2 = i>>1;
 249   return 1;
 250 }
 251
 252 int main(int argc, char **argv){
 253   const char *zFileToRead = 0;  /* Input file.  NULL for stdin */
 254   const char *zDbName = 0;      /* Name of the database file to create */
 255   int useWithoutRowid = 0;      /* True for --without-rowid */
 256   int iMode = MODE_INSERT;      /* One of MODE_xxxxx */
 257   int iMode2;                   /* Mode to use for current --all iteration */
 258   int iLoopCnt = 0;             /* Which iteration when running --all */
 259   int useNocase = 0;            /* True for --nocase */
 260   int doTrace = 0;              /* True for --trace */
 261   int showStats = 0;            /* True for --stats */
 262   int showSummary = 0;          /* True for --summary */
 263   int showTimer = 0;            /* True for --timer */
 264   int cacheSize = 0;            /* Desired cache size.  0 means default */
 265   int pageSize = 0;             /* Desired page size.  0 means default */
 266   int commitInterval = 0;       /* How often to commit.  0 means never */
 267   int noSync = 0;               /* True for --nosync */
 268   const char *zJMode = 0;       /* Journal mode */
 269   int nOp = 0;                  /* Operation counter */
 270   int i, j;                     /* Loop counters */
 271   sqlite3 *db;                  /* The SQLite database connection */
 272   char *zSql;                   /* Constructed SQL statement */
 273   sqlite3_stmt *pInsert = 0;    /* The INSERT statement */
 274   sqlite3_stmt *pUpdate = 0;    /* The UPDATE statement */
 275   sqlite3_stmt *pSelect = 0;    /* The SELECT statement */
 276   sqlite3_stmt *pDelete = 0;    /* The DELETE statement */
 277   FILE *in;                     /* The open input file */
 278   int rc;                       /* Return code from an SQLite interface */
 279   int iCur, iHiwtr;             /* Statistics values, current and "highwater" */
 280   FILE *pTimer = stderr;        /* Output channel for the timer */
 281   sqlite3_int64 sumCnt = 0;     /* Sum in QUERY mode */
 282   sqlite3_int64 startTime;      /* Time of start */
 283   sqlite3_int64 totalTime = 0;  /* Total time */
 284   char zInput[2000];            /* A single line of input */
 285
 286   /* Process command-line arguments */
 287   for(i=1; i<argc; i++){
 288     const char *z = argv[i];
 289     if( z[0]=='-' ){
 290       do{ z++; }while( z[0]=='-' );
 291       if( strcmp(z,"without-rowid")==0 ){
 292         useWithoutRowid = 1;
 293       }else if( strcmp(z,"replace")==0 ){
 294         iMode = MODE_REPLACE;
 295       }else if( strcmp(z,"select")==0 ){
 296         iMode = MODE_SELECT;
 297       }else if( strcmp(z,"insert")==0 ){
 298         iMode = MODE_INSERT;
 299       }else if( strcmp(z,"update")==0 ){
 300         iMode = MODE_UPDATE;
 301       }else if( strcmp(z,"delete")==0 ){
 302         iMode = MODE_DELETE;
 303       }else if( strcmp(z,"query")==0 ){
 304         iMode = MODE_QUERY;
 305       }else if( strcmp(z,"all")==0 ){
 306         iMode = MODE_ALL;
 307         showTimer = -99;
 308       }else if( strcmp(z,"nocase")==0 ){
 309         useNocase = 1;
 310       }else if( strcmp(z,"trace")==0 ){
 311         doTrace = 1;
 312       }else if( strcmp(z,"nosync")==0 ){
 313         noSync = 1;
 314       }else if( strcmp(z,"stats")==0 ){
 315         showStats = 1;
 316       }else if( strcmp(z,"summary")==0 ){
 317         showSummary = 1;
 318       }else if( strcmp(z,"timer")==0 ){
 319         showTimer = i;
 320       }else if( strcmp(z,"cachesize")==0 && i<argc-1 ){
 321         i++;
 322         cacheSize = atoi(argv[i]);
 323       }else if( strcmp(z,"pagesize")==0 && i<argc-1 ){
 324         i++;
 325         pageSize = atoi(argv[i]);
 326       }else if( strcmp(z,"commit")==0 && i<argc-1 ){
 327         i++;
 328         commitInterval = atoi(argv[i]);
 329       }else if( strcmp(z,"journal")==0 && i<argc-1 ){
 330         zJMode = argv[++i];
 331       }else if( strcmp(z,"tag")==0 && i<argc-1 ){
 332         zTag = argv[++i];
 333         pTimer = stdout;
 334       }else if( strcmp(z, "help")==0 || strcmp(z,"?")==0 ){
 335         usage();
 336       }else{
 337         fatal_error("unknown option: \"%s\"\n"
 338                     "Use --help for a list of options\n",
 339                     argv[i]);
 340       }
 341     }else if( zDbName==0 ){
 342       zDbName = argv[i];
 343     }else if( zFileToRead==0 ){
 344       zFileToRead = argv[i];
 345     }else{
 346       fatal_error("surplus argument: \"%s\"\n", argv[i]);
 347     }
 348   }
 349   if( zDbName==0 ){
 350     usage();
 351   }
 352   startTime = realTime();
 353
 354   /* Open the database and the input file */
 355   if( zDbName[0] && strcmp(zDbName,":memory:")!=0 ){
 356     unlink(zDbName);
 357   }
 358   if( sqlite3_open(zDbName, &db) ){
 359     fatal_error("Cannot open database file: %s\n", zDbName);
 360   }
 361   if( zFileToRead ){
 362     in = fopen(zFileToRead, "rb");
 363     if( in==0 ){
 364       fatal_error("Could not open input file \"%s\"\n", zFileToRead);
 365     }
 366   }else{
 367     if( iMode==MODE_ALL ){
 368       fatal_error("The --all mode cannot be used with stdin\n");
 369     }
 370     in = stdin;
 371   }
 372
 373   /* Set database connection options */
 374   if( doTrace ) sqlite3_trace(db, traceCallback, 0);
 375   if( pageSize ){
 376     zSql = sqlite3_mprintf("PRAGMA page_size=%d", pageSize);
 377     sqlite3_exec(db, zSql, 0, 0, 0);
 378     sqlite3_free(zSql);
 379   }
 380   if( cacheSize ){
 381     zSql = sqlite3_mprintf("PRAGMA cache_size=%d", cacheSize);
 382     sqlite3_exec(db, zSql, 0, 0, 0);
 383     sqlite3_free(zSql);
 384   }
 385   if( noSync ) sqlite3_exec(db, "PRAGMA synchronous=OFF", 0, 0, 0);
 386   if( zJMode ){
 387     zSql = sqlite3_mprintf("PRAGMA journal_mode=%s", zJMode);
 388     sqlite3_exec(db, zSql, 0, 0, 0);
 389     sqlite3_free(zSql);
 390   }
 391
 392   iLoopCnt = 0;
 393   while( allLoop(iMode, &iLoopCnt, &iMode2, &useWithoutRowid) ){
 394     /* Delete prior content in --all mode */
 395     if( iMode==MODE_ALL ){
 396       if( sqlite3_exec(db, "DROP TABLE IF EXISTS wordcount; VACUUM;",0,0,0) ){
 397         fatal_error("Could not clean up prior iteration\n");
 398       }
 399       startTime = realTime();
 400       rewind(in);
 401     }
 402
 403     /* Construct the "wordcount" table into which to put the words */
 404     if( sqlite3_exec(db, "BEGIN IMMEDIATE", 0, 0, 0) ){
 405       fatal_error("Could not start a transaction\n");
 406     }
 407     zSql = sqlite3_mprintf(
 408        "CREATE TABLE IF NOT EXISTS wordcount(\n"
 409        "  word TEXT PRIMARY KEY COLLATE %s,\n"
 410        "  cnt INTEGER\n"
 411        ")%s",
 412        useNocase ? "nocase" : "binary",
 413        useWithoutRowid ? " WITHOUT ROWID" : ""
 414     );
 415     if( zSql==0 ) fatal_error("out of memory\n");
 416     rc = sqlite3_exec(db, zSql, 0, 0, 0);
 417     if( rc ) fatal_error("Could not create the wordcount table: %s.\n",
 418                          sqlite3_errmsg(db));
 419     sqlite3_free(zSql);
 420
 421     /* Prepare SQL statements that will be needed */
 422     if( iMode2==MODE_QUERY ){
 423       rc = sqlite3_prepare_v2(db,
 424             "SELECT cnt FROM wordcount WHERE word=?1",
 425             -1, &pSelect, 0);
 426       if( rc ) fatal_error("Could not prepare the SELECT statement: %s\n",
 427                             sqlite3_errmsg(db));
 428     }
 429     if( iMode2==MODE_SELECT ){
 430       rc = sqlite3_prepare_v2(db,
 431             "SELECT 1 FROM wordcount WHERE word=?1",
 432             -1, &pSelect, 0);
 433       if( rc ) fatal_error("Could not prepare the SELECT statement: %s\n",
 434                             sqlite3_errmsg(db));
 435       rc = sqlite3_prepare_v2(db,
 436             "INSERT INTO wordcount(word,cnt) VALUES(?1,1)",
 437             -1, &pInsert, 0);
 438       if( rc ) fatal_error("Could not prepare the INSERT statement: %s\n",
 439                            sqlite3_errmsg(db));
 440     }
 441     if( iMode2==MODE_SELECT || iMode2==MODE_UPDATE || iMode2==MODE_INSERT ){
 442       rc = sqlite3_prepare_v2(db,
 443             "UPDATE wordcount SET cnt=cnt+1 WHERE word=?1",
 444             -1, &pUpdate, 0);
 445       if( rc ) fatal_error("Could not prepare the UPDATE statement: %s\n",
 446                            sqlite3_errmsg(db));
 447     }
 448     if( iMode2==MODE_INSERT ){
 449       rc = sqlite3_prepare_v2(db,
 450             "INSERT OR IGNORE INTO wordcount(word,cnt) VALUES(?1,1)",
 451             -1, &pInsert, 0);
 452       if( rc ) fatal_error("Could not prepare the INSERT statement: %s\n",
 453                            sqlite3_errmsg(db));
 454     }
 455     if( iMode2==MODE_UPDATE ){
 456       rc = sqlite3_prepare_v2(db,
 457             "INSERT OR IGNORE INTO wordcount(word,cnt) VALUES(?1,0)",
 458             -1, &pInsert, 0);
 459       if( rc ) fatal_error("Could not prepare the INSERT statement: %s\n",
 460                            sqlite3_errmsg(db));
 461     }
 462     if( iMode2==MODE_REPLACE ){
 463       rc = sqlite3_prepare_v2(db,
 464           "REPLACE INTO wordcount(word,cnt)"
 465           "VALUES(?1,coalesce((SELECT cnt FROM wordcount WHERE word=?1),0)+1)",
 466           -1, &pInsert, 0);
 467       if( rc ) fatal_error("Could not prepare the REPLACE statement: %s\n",
 468                             sqlite3_errmsg(db));
 469     }
 470     if( iMode2==MODE_DELETE ){
 471       rc = sqlite3_prepare_v2(db,
 472             "DELETE FROM wordcount WHERE word=?1",
 473             -1, &pDelete, 0);
 474       if( rc ) fatal_error("Could not prepare the DELETE statement: %s\n",
 475                            sqlite3_errmsg(db));
 476     }
 477
 478     /* Process the input file */
 479     while( fgets(zInput, sizeof(zInput), in) ){
 480       for(i=0; zInput[i]; i++){
 481         if( !ISALPHA(zInput[i]) ) continue;
 482         for(j=i+1; ISALPHA(zInput[j]); j++){}
 483
 484         /* Found a new word at zInput[i] that is j-i bytes long.
 485         ** Process it into the wordcount table.  */
 486         if( iMode2==MODE_DELETE ){
 487           sqlite3_bind_text(pDelete, 1, zInput+i, j-i, SQLITE_STATIC);
 488           if( sqlite3_step(pDelete)!=SQLITE_DONE ){
 489             fatal_error("DELETE failed: %s\n", sqlite3_errmsg(db));
 490           }
 491           sqlite3_reset(pDelete);
 492         }else if( iMode2==MODE_SELECT ){
 493           sqlite3_bind_text(pSelect, 1, zInput+i, j-i, SQLITE_STATIC);
 494           rc = sqlite3_step(pSelect);
 495           sqlite3_reset(pSelect);
 496           if( rc==SQLITE_ROW ){
 497             sqlite3_bind_text(pUpdate, 1, zInput+i, j-i, SQLITE_STATIC);
 498             if( sqlite3_step(pUpdate)!=SQLITE_DONE ){
 499               fatal_error("UPDATE failed: %s\n", sqlite3_errmsg(db));
 500             }
 501             sqlite3_reset(pUpdate);
 502           }else if( rc==SQLITE_DONE ){
 503             sqlite3_bind_text(pInsert, 1, zInput+i, j-i, SQLITE_STATIC);
 504             if( sqlite3_step(pInsert)!=SQLITE_DONE ){
 505               fatal_error("Insert failed: %s\n", sqlite3_errmsg(db));
 506             }
 507             sqlite3_reset(pInsert);
 508           }else{
 509             fatal_error("SELECT failed: %s\n", sqlite3_errmsg(db));
 510           }
 511         }else if( iMode2==MODE_QUERY ){
 512           sqlite3_bind_text(pSelect, 1, zInput+i, j-i, SQLITE_STATIC);
 513           if( sqlite3_step(pSelect)==SQLITE_ROW ){
 514             sumCnt += sqlite3_column_int64(pSelect, 0);
 515           }
 516           sqlite3_reset(pSelect);
 517         }else{
 518           sqlite3_bind_text(pInsert, 1, zInput+i, j-i, SQLITE_STATIC);
 519           if( sqlite3_step(pInsert)!=SQLITE_DONE ){
 520             fatal_error("INSERT failed: %s\n", sqlite3_errmsg(db));
 521           }
 522           sqlite3_reset(pInsert);
 523           if( iMode2==MODE_UPDATE
 524            || (iMode2==MODE_INSERT && sqlite3_changes(db)==0)
 525           ){
 526             sqlite3_bind_text(pUpdate, 1, zInput+i, j-i, SQLITE_STATIC);
 527             if( sqlite3_step(pUpdate)!=SQLITE_DONE ){
 528               fatal_error("UPDATE failed: %s\n", sqlite3_errmsg(db));
 529             }
 530             sqlite3_reset(pUpdate);
 531           }
 532         }
 533         i = j-1;
 534
 535         /* Increment the operation counter.  Do a COMMIT if it is time. */
 536         nOp++;
 537         if( commitInterval>0 && (nOp%commitInterval)==0 ){
 538           sqlite3_exec(db, "COMMIT; BEGIN IMMEDIATE", 0, 0, 0);
 539         }
 540       }
 541     }
 542     sqlite3_exec(db, "COMMIT", 0, 0, 0);
 543     sqlite3_finalize(pInsert);  pInsert = 0;
 544     sqlite3_finalize(pUpdate);  pUpdate = 0;
 545     sqlite3_finalize(pSelect);  pSelect = 0;
 546     sqlite3_finalize(pDelete);  pDelete = 0;
 547
 548     if( iMode2==MODE_QUERY && iMode!=MODE_ALL ){
 549       printf("%s sum of cnt: %lld\n", zTag, sumCnt);
 550       rc = sqlite3_prepare_v2(db,"SELECT sum(cnt*cnt) FROM wordcount", -1,
 551                               &pSelect, 0);
 552       if( rc==SQLITE_OK && sqlite3_step(pSelect)==SQLITE_ROW ){
 553         printf("%s double-check: %lld\n", zTag,sqlite3_column_int64(pSelect,0));
 554       }
 555       sqlite3_finalize(pSelect);
 556     }
 557
 558
 559     if( showTimer ){
 560       sqlite3_int64 elapseTime = realTime() - startTime;
 561       totalTime += elapseTime;
 562       fprintf(pTimer, "%3d.%03d wordcount", (int)(elapseTime/1000),
 563                                    (int)(elapseTime%1000));
 564       if( iMode==MODE_ALL ){
 565         fprintf(pTimer, " %s%s\n", azMode[iMode2],
 566                 useWithoutRowid? " --without-rowid" : "");
 567       }else{
 568         for(i=1; i<argc; i++) if( i!=showTimer ) fprintf(pTimer," %s",argv[i]);
 569         fprintf(pTimer, "\n");
 570       }
 571     }
 572
 573     if( showSummary ){
 574       sqlite3_create_function(db, "checksum", -1, SQLITE_UTF8, 0,
 575                               0, checksumStep, checksumFinalize);
 576       sqlite3_exec(db,
 577         "SELECT 'count(*):  ', count(*) FROM wordcount;\n"
 578         "SELECT 'sum(cnt):  ', sum(cnt) FROM wordcount;\n"
 579         "SELECT 'max(cnt):  ', max(cnt) FROM wordcount;\n"
 580         "SELECT 'avg(cnt):  ', avg(cnt) FROM wordcount;\n"
 581         "SELECT 'sum(cnt=1):', sum(cnt=1) FROM wordcount;\n"
 582         "SELECT 'top 10:    ', group_concat(word, ', ') FROM "
 583            "(SELECT word FROM wordcount ORDER BY cnt DESC, word LIMIT 10);\n"
 584         "SELECT 'checksum:  ', checksum(word, cnt) FROM "
 585            "(SELECT word, cnt FROM wordcount ORDER BY word);\n"
 586         "PRAGMA integrity_check;\n",
 587         printResult, 0, 0);
 588     }
 589   } /* End the --all loop */
 590
 591   /* Close the input file after the last read */
 592   if( zFileToRead ) fclose(in);
 593
 594   /* In --all mode, so the total time */
 595   if( iMode==MODE_ALL && showTimer ){
 596     fprintf(pTimer, "%3d.%03d wordcount --all\n", (int)(totalTime/1000),
 597                                    (int)(totalTime%1000));
 598   }
 599
 600   /* Database connection statistics printed after both prepared statements
 601   ** have been finalized */
 602   if( showStats ){
 603     sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_USED, &iCur, &iHiwtr, 0);
 604     printf("%s Lookaside Slots Used:        %d (max %d)\n", zTag, iCur,iHiwtr);
 605     sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_HIT, &iCur, &iHiwtr, 0);
 606     printf("%s Successful lookasides:       %d\n", zTag, iHiwtr);
 607     sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE, &iCur,&iHiwtr,0);
 608     printf("%s Lookaside size faults:       %d\n", zTag, iHiwtr);
 609     sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL, &iCur,&iHiwtr,0);
 610     printf("%s Lookaside OOM faults:        %d\n", zTag, iHiwtr);
 611     sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_USED, &iCur, &iHiwtr, 0);
 612     printf("%s Pager Heap Usage:            %d bytes\n", zTag, iCur);
 613     sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_HIT, &iCur, &iHiwtr, 1);
 614     printf("%s Page cache hits:             %d\n", zTag, iCur);
 615     sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_MISS, &iCur, &iHiwtr, 1);
 616     printf("%s Page cache misses:           %d\n", zTag, iCur);
 617     sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_WRITE, &iCur, &iHiwtr, 1);
 618     printf("%s Page cache writes:           %d\n", zTag, iCur);
 619     sqlite3_db_status(db, SQLITE_DBSTATUS_SCHEMA_USED, &iCur, &iHiwtr, 0);
 620     printf("%s Schema Heap Usage:           %d bytes\n", zTag, iCur);
 621     sqlite3_db_status(db, SQLITE_DBSTATUS_STMT_USED, &iCur, &iHiwtr, 0);
 622     printf("%s Statement Heap Usage:        %d bytes\n", zTag, iCur);
 623   }
 624
 625   sqlite3_close(db);
 626
 627   /* Global memory usage statistics printed after the database connection
 628   ** has closed.  Memory usage should be zero at this point. */
 629   if( showStats ){
 630     sqlite3_status(SQLITE_STATUS_MEMORY_USED, &iCur, &iHiwtr, 0);
 631     printf("%s Memory Used (bytes):         %d (max %d)\n", zTag,iCur,iHiwtr);
 632     sqlite3_status(SQLITE_STATUS_MALLOC_COUNT, &iCur, &iHiwtr, 0);
 633     printf("%s Outstanding Allocations:     %d (max %d)\n",zTag,iCur,iHiwtr);
 634     sqlite3_status(SQLITE_STATUS_PAGECACHE_OVERFLOW, &iCur, &iHiwtr, 0);
 635     printf("%s Pcache Overflow Bytes:       %d (max %d)\n",zTag,iCur,iHiwtr);
 636     sqlite3_status(SQLITE_STATUS_SCRATCH_OVERFLOW, &iCur, &iHiwtr, 0);
 637     printf("%s Scratch Overflow Bytes:      %d (max %d)\n",zTag,iCur,iHiwtr);
 638     sqlite3_status(SQLITE_STATUS_MALLOC_SIZE, &iCur, &iHiwtr, 0);
 639     printf("%s Largest Allocation:          %d bytes\n",zTag,iHiwtr);
 640     sqlite3_status(SQLITE_STATUS_PAGECACHE_SIZE, &iCur, &iHiwtr, 0);
 641     printf("%s Largest Pcache Allocation:   %d bytes\n",zTag,iHiwtr);
 642     sqlite3_status(SQLITE_STATUS_SCRATCH_SIZE, &iCur, &iHiwtr, 0);
 643     printf("%s Largest Scratch Allocation:  %d bytes\n",zTag,iHiwtr);
 644   }
 645   return 0;
 646 }