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