/ Check-in [e938112d]
Login

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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e938112d316ca31460f247cc104ca3ff1d60b4da
User & Date: drh 2013-11-07 23:23:27
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: 1d1d13b8 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: e938112d user: drh tags: trunk
21:32
Fix a compiler warning introduced by the previous check-in. check-in: 404bd98f user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/wordcount.c.

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