Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add many new options to the wordcount test program: --delete, --pagesize, --cachesize, --commit, --nosync, and --journal. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
e938112d316ca31460f247cc104ca3ff |
User & Date: | drh 2013-11-07 23:23:27.610 |
Context
2013-11-08
| ||
00:16 | On the --summary output of wordcount, add the a PRAGMA integrity_check and a 64-bit checksum of the entire table. (check-in: 1d1d13b890 user: drh tags: trunk) | |
2013-11-07
| ||
23:23 | Add many new options to the wordcount test program: --delete, --pagesize, --cachesize, --commit, --nosync, and --journal. (check-in: e938112d31 user: drh tags: trunk) | |
21:32 | Fix a compiler warning introduced by the previous check-in. (check-in: 404bd98fb4 user: drh tags: trunk) | |
Changes
Changes to test/wordcount.c.
︙ | ︙ | |||
14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | ** Option: ** ** --without-rowid Use a WITHOUT ROWID table to store the words. ** --insert Use INSERT mode (the default) ** --replace Use REPLACE mode ** --select Use SELECT mode ** --update Use UPDATE mode ** --nocase Add the NOCASE collating sequence to the words. ** --trace Enable sqlite3_trace() output. ** --summary Show summary information on the collected data. ** --stats Show sqlite3_status() results at the end. ** ** Modes: ** ** Insert mode means: ** (1) INSERT OR IGNORE INTO wordcount VALUES($new,1) ** (2) UPDATE wordcount SET cnt=cnt+1 WHERE word=$new -- if (1) is a noop ** ** Update mode means: ** (1) INSERT OR IGNORE INTO wordcount VALUES($new,0) ** (2) UPDATE wordcount SET cnt=cnt+1 WHERE word=$new ** ** Replace mode means: ** (1) REPLACE INTO wordcount ** VALUES($new,ifnull((SELECT cnt FROM wordcount WHERE word=$new),0)+1); ** | > > > > > > | | > > > > > > > > > | 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | ** Option: ** ** --without-rowid Use a WITHOUT ROWID table to store the words. ** --insert Use INSERT mode (the default) ** --replace Use REPLACE mode ** --select Use SELECT mode ** --update Use UPDATE mode ** --delete Use DELETE mode ** --nocase Add the NOCASE collating sequence to the words. ** --trace Enable sqlite3_trace() output. ** --summary Show summary information on the collected data. ** --stats Show sqlite3_status() results at the end. ** --pagesize NNN Use a page size of NNN ** --cachesize NNN Use a cache size of NNN ** --commit NNN Commit after every NNN operations ** --nosync Use PRAGMA synchronous=OFF ** --journal MMMM Use PRAGMA journal_mode=MMMM ** ** Modes: ** ** Insert mode means: ** (1) INSERT OR IGNORE INTO wordcount VALUES($new,1) ** (2) UPDATE wordcount SET cnt=cnt+1 WHERE word=$new -- if (1) is a noop ** ** Update mode means: ** (1) INSERT OR IGNORE INTO wordcount VALUES($new,0) ** (2) UPDATE wordcount SET cnt=cnt+1 WHERE word=$new ** ** Replace mode means: ** (1) REPLACE INTO wordcount ** VALUES($new,ifnull((SELECT cnt FROM wordcount WHERE word=$new),0)+1); ** ** Select mode means: ** (1) SELECT 1 FROM wordcount WHERE word=$new ** (2) INSERT INTO wordcount VALUES($new,1) -- if (1) returns nothing ** (3) UPDATE wordcount SET cnt=cnt+1 WHERE word=$new --if (1) return TRUE ** ** Delete mode means: ** (1) DELETE FROM wordcount WHERE word=$new ** ** Note that delete mode is only useful for preexisting databases. The ** wordcount table is created using IF NOT EXISTS so this utility can be ** run multiple times on the same database file. The --without-rowid, ** --nocase, and --pagesize parameters are only effective when creating ** a new database and are harmless no-ops on preexisting databases. ** ****************************************************************************** ** ** Compile as follows: ** ** gcc -I. wordcount.c sqlite3.c -ldl -lpthreads ** ** Or: |
︙ | ︙ | |||
89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 | /* Define operating modes */ #define MODE_INSERT 0 #define MODE_REPLACE 1 #define MODE_SELECT 2 #define MODE_UPDATE 3 int main(int argc, char **argv){ const char *zFileToRead = 0; /* Input file. NULL for stdin */ const char *zDbName = 0; /* Name of the database file to create */ int useWithoutRowid = 0; /* True for --without-rowid */ int iMode = MODE_INSERT; /* One of MODE_xxxxx */ int useNocase = 0; /* True for --nocase */ int doTrace = 0; /* True for --trace */ int showStats = 0; /* True for --stats */ int showSummary = 0; /* True for --summary */ int i, j; /* Loop counters */ sqlite3 *db; /* The SQLite database connection */ char *zSql; /* Constructed SQL statement */ sqlite3_stmt *pInsert = 0; /* The INSERT statement */ sqlite3_stmt *pUpdate = 0; /* The UPDATE statement */ sqlite3_stmt *pSelect = 0; /* The SELECT statement */ FILE *in; /* The open input file */ int rc; /* Return code from an SQLite interface */ int iCur, iHiwtr; /* Statistics values, current and "highwater" */ char zInput[2000]; /* A single line of input */ /* Process command-line arguments */ for(i=1; i<argc; i++){ const char *z = argv[i]; if( z[0]=='-' ){ do{ z++; }while( z[0]=='-' ); if( strcmp(z,"without-rowid")==0 ){ useWithoutRowid = 1; }else if( strcmp(z,"replace")==0 ){ iMode = MODE_REPLACE; }else if( strcmp(z,"select")==0 ){ iMode = MODE_SELECT; }else if( strcmp(z,"insert")==0 ){ iMode = MODE_INSERT; }else if( strcmp(z,"update")==0 ){ iMode = MODE_UPDATE; }else if( strcmp(z,"nocase")==0 ){ useNocase = 1; }else if( strcmp(z,"trace")==0 ){ doTrace = 1; }else if( strcmp(z,"stats")==0 ){ showStats = 1; }else if( strcmp(z,"summary")==0 ){ showSummary = 1; }else{ fatal_error("unknown option: %s\n", argv[i]); } }else if( zDbName==0 ){ zDbName = argv[i]; }else if( zFileToRead==0 ){ zFileToRead = argv[i]; | > > > > > > > > > > > > > > > > > > > > > > > | 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 | /* Define operating modes */ #define MODE_INSERT 0 #define MODE_REPLACE 1 #define MODE_SELECT 2 #define MODE_UPDATE 3 #define MODE_DELETE 4 int main(int argc, char **argv){ const char *zFileToRead = 0; /* Input file. NULL for stdin */ const char *zDbName = 0; /* Name of the database file to create */ int useWithoutRowid = 0; /* True for --without-rowid */ int iMode = MODE_INSERT; /* One of MODE_xxxxx */ int useNocase = 0; /* True for --nocase */ int doTrace = 0; /* True for --trace */ int showStats = 0; /* True for --stats */ int showSummary = 0; /* True for --summary */ int cacheSize = 0; /* Desired cache size. 0 means default */ int pageSize = 0; /* Desired page size. 0 means default */ int commitInterval = 0; /* How often to commit. 0 means never */ int noSync = 0; /* True for --nosync */ const char *zJMode = 0; /* Journal mode */ int nOp = 0; /* Operation counter */ int i, j; /* Loop counters */ sqlite3 *db; /* The SQLite database connection */ char *zSql; /* Constructed SQL statement */ sqlite3_stmt *pInsert = 0; /* The INSERT statement */ sqlite3_stmt *pUpdate = 0; /* The UPDATE statement */ sqlite3_stmt *pSelect = 0; /* The SELECT statement */ sqlite3_stmt *pDelete = 0; /* The DELETE statement */ FILE *in; /* The open input file */ int rc; /* Return code from an SQLite interface */ int iCur, iHiwtr; /* Statistics values, current and "highwater" */ char zInput[2000]; /* A single line of input */ /* Process command-line arguments */ for(i=1; i<argc; i++){ const char *z = argv[i]; if( z[0]=='-' ){ do{ z++; }while( z[0]=='-' ); if( strcmp(z,"without-rowid")==0 ){ useWithoutRowid = 1; }else if( strcmp(z,"replace")==0 ){ iMode = MODE_REPLACE; }else if( strcmp(z,"select")==0 ){ iMode = MODE_SELECT; }else if( strcmp(z,"insert")==0 ){ iMode = MODE_INSERT; }else if( strcmp(z,"update")==0 ){ iMode = MODE_UPDATE; }else if( strcmp(z,"delete")==0 ){ iMode = MODE_DELETE; }else if( strcmp(z,"nocase")==0 ){ useNocase = 1; }else if( strcmp(z,"trace")==0 ){ doTrace = 1; }else if( strcmp(z,"nosync")==0 ){ noSync = 1; }else if( strcmp(z,"stats")==0 ){ showStats = 1; }else if( strcmp(z,"summary")==0 ){ showSummary = 1; }else if( strcmp(z,"cachesize")==0 && i<argc-1 ){ i++; cacheSize = atoi(argv[i]); }else if( strcmp(z,"pagesize")==0 && i<argc-1 ){ i++; pageSize = atoi(argv[i]); }else if( strcmp(z,"commit")==0 && i<argc-1 ){ i++; commitInterval = atoi(argv[i]); }else if( strcmp(z,"journal")==0 && i<argc-1 ){ zJMode = argv[++i]; }else{ fatal_error("unknown option: %s\n", argv[i]); } }else if( zDbName==0 ){ zDbName = argv[i]; }else if( zFileToRead==0 ){ zFileToRead = argv[i]; |
︙ | ︙ | |||
161 162 163 164 165 166 167 | if( in==0 ){ fatal_error("Could not open input file \"%s\"\n", zFileToRead); } }else{ in = stdin; } | | > > > > > > > > > > > > > > > > > > > | | 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 | if( in==0 ){ fatal_error("Could not open input file \"%s\"\n", zFileToRead); } }else{ in = stdin; } /* Set database connection options */ if( doTrace ) sqlite3_trace(db, traceCallback, 0); if( pageSize ){ zSql = sqlite3_mprintf("PRAGMA page_size=%d", pageSize); sqlite3_exec(db, zSql, 0, 0, 0); sqlite3_free(zSql); } if( cacheSize ){ zSql = sqlite3_mprintf("PRAGMA cache_size=%d", cacheSize); sqlite3_exec(db, zSql, 0, 0, 0); sqlite3_free(zSql); } if( noSync ) sqlite3_exec(db, "PRAGMA synchronous=OFF", 0, 0, 0); if( zJMode ){ zSql = sqlite3_mprintf("PRAGMA journal_mode=%s", zJMode); sqlite3_exec(db, zSql, 0, 0, 0); sqlite3_free(zSql); } /* Construct the "wordcount" table into which to put the words */ if( sqlite3_exec(db, "BEGIN IMMEDIATE", 0, 0, 0) ){ fatal_error("Could not start a transaction\n"); } zSql = sqlite3_mprintf( "CREATE TABLE IF NOT EXISTS wordcount(\n" " word TEXT PRIMARY KEY COLLATE %s,\n" " cnt INTEGER\n" ")%s", useNocase ? "nocase" : "binary", useWithoutRowid ? " WITHOUT ROWID" : "" ); if( zSql==0 ) fatal_error("out of memory\n"); |
︙ | ︙ | |||
222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 | rc = sqlite3_prepare_v2(db, "REPLACE INTO wordcount(word,cnt)" "VALUES(?1,coalesce((SELECT cnt FROM wordcount WHERE word=?1),0)+1)", -1, &pInsert, 0); if( rc ) fatal_error("Could not prepare the REPLACE statement: %s\n", sqlite3_errmsg(db)); } /* Process the input file */ while( fgets(zInput, sizeof(zInput), in) ){ for(i=0; zInput[i]; i++){ if( !isalpha(zInput[i]) ) continue; for(j=i+1; isalpha(zInput[j]); j++){} /* Found a new word at zInput[i] that is j-i bytes long. ** Process it into the wordcount table. */ | > > > > > > > | > > > > > > | 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 | rc = sqlite3_prepare_v2(db, "REPLACE INTO wordcount(word,cnt)" "VALUES(?1,coalesce((SELECT cnt FROM wordcount WHERE word=?1),0)+1)", -1, &pInsert, 0); if( rc ) fatal_error("Could not prepare the REPLACE statement: %s\n", sqlite3_errmsg(db)); } if( iMode==MODE_DELETE ){ rc = sqlite3_prepare_v2(db, "DELETE FROM wordcount WHERE word=?1", -1, &pDelete, 0); if( rc ) fatal_error("Could not prepare the DELETE statement: %s\n", sqlite3_errmsg(db)); } /* Process the input file */ while( fgets(zInput, sizeof(zInput), in) ){ for(i=0; zInput[i]; i++){ if( !isalpha(zInput[i]) ) continue; for(j=i+1; isalpha(zInput[j]); j++){} /* Found a new word at zInput[i] that is j-i bytes long. ** Process it into the wordcount table. */ if( iMode==MODE_DELETE ){ sqlite3_bind_text(pDelete, 1, zInput+i, j-i, SQLITE_STATIC); if( sqlite3_step(pDelete)!=SQLITE_DONE ){ fatal_error("DELETE failed: %s\n", sqlite3_errmsg(db)); } sqlite3_reset(pDelete); }else if( iMode==MODE_SELECT ){ sqlite3_bind_text(pSelect, 1, zInput+i, j-i, SQLITE_STATIC); rc = sqlite3_step(pSelect); sqlite3_reset(pSelect); if( rc==SQLITE_ROW ){ sqlite3_bind_text(pUpdate, 1, zInput+i, j-i, SQLITE_STATIC); if( sqlite3_step(pUpdate)!=SQLITE_DONE ){ fatal_error("UPDATE failed: %s\n", sqlite3_errmsg(db)); |
︙ | ︙ | |||
267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 | if( sqlite3_step(pUpdate)!=SQLITE_DONE ){ fatal_error("UPDATE failed: %s\n", sqlite3_errmsg(db)); } sqlite3_reset(pUpdate); } } i = j-1; } } sqlite3_exec(db, "COMMIT", 0, 0, 0); if( zFileToRead ) fclose(in); sqlite3_finalize(pInsert); sqlite3_finalize(pUpdate); sqlite3_finalize(pSelect); if( showSummary ){ sqlite3_exec(db, "SELECT '-- count(*): ', count(*) FROM wordcount;\n" "SELECT '-- sum(cnt): ', sum(cnt) FROM wordcount;\n" "SELECT '-- avg(cnt): ', avg(cnt) FROM wordcount;\n" "SELECT '-- sum(cnt=1):', sum(cnt=1) FROM wordcount;\n" | > > > > > > > | 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 | if( sqlite3_step(pUpdate)!=SQLITE_DONE ){ fatal_error("UPDATE failed: %s\n", sqlite3_errmsg(db)); } sqlite3_reset(pUpdate); } } i = j-1; /* Increment the operation counter. Do a COMMIT if it is time. */ nOp++; if( commitInterval>0 && (nOp%commitInterval)==0 ){ sqlite3_exec(db, "COMMIT; BEGIN IMMEDIATE", 0, 0, 0); } } } sqlite3_exec(db, "COMMIT", 0, 0, 0); if( zFileToRead ) fclose(in); sqlite3_finalize(pInsert); sqlite3_finalize(pUpdate); sqlite3_finalize(pSelect); sqlite3_finalize(pDelete); if( showSummary ){ sqlite3_exec(db, "SELECT '-- count(*): ', count(*) FROM wordcount;\n" "SELECT '-- sum(cnt): ', sum(cnt) FROM wordcount;\n" "SELECT '-- avg(cnt): ', avg(cnt) FROM wordcount;\n" "SELECT '-- sum(cnt=1):', sum(cnt=1) FROM wordcount;\n" |
︙ | ︙ |