| 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 |
} |