/ Check-in [f02ee546]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add the "wordcount.c" test program.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: f02ee5463084cef45ae412c51211b66de7e86baf
User & Date: drh 2013-11-07 18:37:31
Context
2013-11-07
18:40
Increase the version number to 3.8.2. check-in: 9ad5b74c user: drh tags: trunk
18:37
Add the "wordcount.c" test program. check-in: f02ee546 user: drh tags: trunk
16:08
Add support for WITHOUT ROWID tables. This change also includes (1) standardization of the error message returned from run-time constraint errors, (2) improved EXPLAIN comments, (3) the SQLITE_ENABLE_EXPLAIN_COMMENTS option, (4) the SQLITE_ENABLE_MODULE_COMMENTS option, and (5) a bug fix (see [573cc27427]) in the handling of REPLACE on the rowid when secondary indices use FAIL or IGNORE. check-in: c80e229d user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to Makefile.in.

   928    928   	echo "static const char *zMainloop = " >> $@
   929    929   	$(NAWK) -f $(TOP)/tool/tostr.awk $(TOP)/tool/spaceanal.tcl >> $@
   930    930   	echo "; return zMainloop; }" >> $@
   931    931   
   932    932   sqlite3_analyzer$(TEXE): sqlite3_analyzer.c
   933    933   	$(LTLINK) sqlite3_analyzer.c -o $@ $(LIBTCL) $(TLIBS)
   934    934   
          935  +showdb$(TEXE):	$(TOP)/tool/showdb.c sqlite3.c
          936  +	$(LTLINK) -o $@ $(TOP)/tool/showdb.c sqlite3.c $(TLIBS)
          937  +
          938  +wordcount$(TEXE):	$(TOP)/test/wordcount.c sqlite3.c
          939  +	$(LTLINK) -o $@ $(TOP)/test/wordcount.c sqlite3.c $(TLIBS)
          940  +
   935    941   # Standard install and cleanup targets
   936    942   #
   937    943   lib_install:	libsqlite3.la
   938    944   	$(INSTALL) -d $(DESTDIR)$(libdir)
   939    945   	$(LTINSTALL) libsqlite3.la $(DESTDIR)$(libdir)
   940    946   	
   941    947   install:	sqlite3$(BEXE) lib_install sqlite3.h sqlite3.pc ${HAVE_TCL:1=tcl_install}

Changes to Makefile.msc.

  1327   1327   	echo static const char *zMainloop = >> $@
  1328   1328   	$(NAWK) -f $(TOP)\tool\tostr.awk $(TOP)\tool\spaceanal.tcl >> $@
  1329   1329   	echo ; return zMainloop; } >> $@
  1330   1330   
  1331   1331   sqlite3_analyzer.exe:	sqlite3_analyzer.c $(LIBRESOBJS)
  1332   1332   	$(LTLINK) -DBUILD_sqlite -DTCLSH=2 -I$(TCLINCDIR) sqlite3_analyzer.c \
  1333   1333   		/link $(LTLINKOPTS) $(LTLIBPATHS) $(LIBRESOBJS) $(LTLIBS) $(TLIBS)
         1334  +
         1335  +showdb.exe:	$(TOP)/tool/showdb.c sqlite3.c
         1336  +	$(LTLINK) -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION -o $@ \
         1337  +		$(TOP)/tool/showdb.c sqlite3.c
         1338  +
         1339  +wordcount.exe:	$(TOP)/test/wordcount.c sqlite3.c
         1340  +	$(LTLINK) -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION -o $@ \
         1341  +		$(TOP)/test/wordcount.c sqlite3.c
  1334   1342   
  1335   1343   clean:
  1336   1344   	del /Q *.lo *.ilk *.lib *.obj *.pdb sqlite3.exe libsqlite3.lib
  1337   1345   	del /Q *.cod *.da *.bb *.bbg gmon.out
  1338   1346   	del /Q sqlite3.h opcodes.c opcodes.h
  1339   1347   	del /Q lemon.exe lempar.c parse.*
  1340   1348   	del /Q mkkeywordhash.exe keywordhash.h

Changes to main.mk.

   616    616   TEST_EXTENSION = $(SHPREFIX)testloadext.$(SO)
   617    617   $(TEST_EXTENSION): $(TOP)/src/test_loadext.c
   618    618   	$(MKSHLIB) $(TOP)/src/test_loadext.c -o $(TEST_EXTENSION)
   619    619   
   620    620   extensiontest: testfixture$(EXE) $(TEST_EXTENSION)
   621    621   	./testfixture$(EXE) $(TOP)/test/loadext.test
   622    622   
   623         -showdb:	$(TOP)/tool/showdb.c sqlite3.c
   624         -	$(TCC) -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION -o showdb \
          623  +showdb$(EXE):	$(TOP)/tool/showdb.c sqlite3.c
          624  +	$(TCC) -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION -o showdb$(EXE) \
   625    625   		$(TOP)/tool/showdb.c sqlite3.c
          626  +
          627  +wordcount$(EXE):	$(TOP)/test/wordcount.c sqlite3.c
          628  +	$(TCC) -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION -o wordcount$(EXE) \
          629  +		$(TOP)/test/wordcount.c sqlite3.c
   626    630   
   627    631   # This target will fail if the SQLite amalgamation contains any exported
   628    632   # symbols that do not begin with "sqlite3_". It is run as part of the
   629    633   # releasetest.tcl script.
   630    634   #
   631    635   checksymbols: sqlite3.o
   632    636   	nm -g --defined-only sqlite3.o | grep -v " sqlite3_" ; test $$? -ne 0

Added 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  +**     --without-rowid      Use a WITHOUT ROWID table to store the words.
           17  +**     --insert             Use INSERT mode (the default)
           18  +**     --replace            Use REPLACE mode
           19  +**     --select             Use SELECT mode
           20  +**     --update             Use UPDATE mode
           21  +**     --nocase             Add the NOCASE collating sequence to the words.
           22  +**     --trace              Enable sqlite3_trace() output.
           23  +**
           24  +** Modes:
           25  +**
           26  +** Insert mode means:
           27  +**    (1) INSERT OR IGNORE INTO wordcount VALUES($new,1)
           28  +**    (2) UPDATE wordcount SET cnt=cnt+1 WHERE word=$new -- if (1) is a noop
           29  +**
           30  +** Update mode means:
           31  +**    (1) INSERT OR IGNORE INTO wordcount VALUES($new,0)
           32  +**    (2) UPDATE wordcount SET cnt=cnt+1 WHERE word=$new
           33  +**
           34  +** Replace mode means:
           35  +**    (1) REPLACE INTO wordcount
           36  +**        VALUES($new,ifnull((SELECT cnt FROM wordcount WHERE word=$new),0)+1);
           37  +**
           38  +** Select mode modes:
           39  +**    (1) SELECT 1 FROM wordcount WHERE word=$newword
           40  +**    (2) INSERT INTO wordcount VALUES($new,1) -- if (1) returns nothing
           41  +**    (3) UPDATE wordcount SET cnt=cnt+1 WHERE word=$new  --if (1) return TRUE
           42  +**
           43  +******************************************************************************
           44  +**
           45  +** Compile as follows:
           46  +**
           47  +**    gcc -I. wordcount.c sqlite3.c -ldl -lpthreads
           48  +**
           49  +** Or:
           50  +**
           51  +**    gcc -I. -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION \
           52  +**        wordcount.c sqlite3.c
           53  +*/
           54  +#include <stdio.h>
           55  +#include <string.h>
           56  +#include <ctype.h>
           57  +#include <stdlib.h>
           58  +#include <stdarg.h>
           59  +#include "sqlite3.h"
           60  +
           61  +/* Print an error message and exit */
           62  +static void fatal_error(const char *zMsg, ...){
           63  +  va_list ap;
           64  +  va_start(ap, zMsg);
           65  +  vfprintf(stderr, zMsg, ap);
           66  +  va_end(ap);
           67  +  exit(1);
           68  +}
           69  +
           70  +/* The sqlite3_trace() callback function */
           71  +static void traceCallback(void *NotUsed, const char *zSql){
           72  +  printf("%s;\n", zSql);
           73  +}
           74  +
           75  +/* Define operating modes */
           76  +#define MODE_INSERT     0
           77  +#define MODE_REPLACE    1
           78  +#define MODE_SELECT     2
           79  +#define MODE_UPDATE     3
           80  +
           81  +int main(int argc, char **argv){
           82  +  const char *zFileToRead = 0;  /* Input file.  NULL for stdin */
           83  +  const char *zDbName = 0;      /* Name of the database file to create */
           84  +  int useWithoutRowid = 0;      /* True for --without-rowid */
           85  +  int iMode = MODE_INSERT;      /* One of MODE_xxxxx */
           86  +  int useNocase = 0;            /* True for --nocase */
           87  +  int doTrace = 0;              /* True for --trace */
           88  +  int i, j;                     /* Loop counters */
           89  +  sqlite3 *db;                  /* The SQLite database connection */
           90  +  char *zSql;                   /* Constructed SQL statement */
           91  +  sqlite3_stmt *pInsert = 0;    /* The INSERT statement */
           92  +  sqlite3_stmt *pUpdate = 0;    /* The UPDATE statement */
           93  +  sqlite3_stmt *pSelect = 0;    /* The SELECT statement */
           94  +  FILE *in;                     /* The open input file */
           95  +  int rc;                       /* Return code from an SQLite interface */
           96  +  int iCur, iHiwtr;             /* Statistics values, current and "highwater" */
           97  +  char zInput[2000];            /* A single line of input */
           98  +
           99  +  /* Process command-line arguments */
          100  +  for(i=1; i<argc; i++){
          101  +    const char *z = argv[i];
          102  +    if( z[0]=='-' ){
          103  +      do{ z++; }while( z[0]=='-' );
          104  +      if( strcmp(z,"without-rowid")==0 ){
          105  +        useWithoutRowid = 1;
          106  +      }else if( strcmp(z,"replace")==0 ){
          107  +        iMode = MODE_REPLACE;
          108  +      }else if( strcmp(z,"select")==0 ){
          109  +        iMode = MODE_SELECT;
          110  +      }else if( strcmp(z,"insert")==0 ){
          111  +        iMode = MODE_INSERT;
          112  +      }else if( strcmp(z,"update")==0 ){
          113  +        iMode = MODE_UPDATE;
          114  +      }else if( strcmp(z,"nocase")==0 ){
          115  +        useNocase = 1;
          116  +      }else if( strcmp(z,"trace")==0 ){
          117  +        doTrace = 1;
          118  +      }else{
          119  +        fatal_error("unknown option: %s\n", argv[i]);
          120  +      }
          121  +    }else if( zDbName==0 ){
          122  +      zDbName = argv[i];
          123  +    }else if( zFileToRead==0 ){
          124  +      zFileToRead = argv[i];
          125  +    }else{
          126  +      fatal_error("surplus argument: %s\n", argv[i]);
          127  +    }
          128  +  }
          129  +  if( zDbName==0 ){
          130  +    fatal_error("Usage: %s [--options] DATABASE [INPUTFILE]\n", argv[0]);
          131  +  }
          132  +
          133  +  /* Open the database and the input file */
          134  +  if( sqlite3_open(zDbName, &db) ){
          135  +    fatal_error("Cannot open database file: %s\n", zDbName);
          136  +  }
          137  +  if( zFileToRead ){
          138  +    in = fopen(zFileToRead, "rb");
          139  +    if( in==0 ){
          140  +      fatal_error("Could not open input file \"%s\"\n", zFileToRead);
          141  +    }
          142  +  }else{
          143  +    in = stdin;
          144  +  }
          145  +
          146  +  /* Construct the "wordcount" table into which to put the words */
          147  +  if( doTrace ) sqlite3_trace(db, traceCallback, 0);
          148  +  if( sqlite3_exec(db, "BEGIN IMMEDIATE", 0, 0, 0) ){
          149  +    fatal_error("Could not start a transaction\n");
          150  +  }
          151  +  zSql = sqlite3_mprintf(
          152  +     "CREATE TABLE wordcount(\n"
          153  +     "  word TEXT PRIMARY KEY COLLATE %s,\n"
          154  +     "  cnt INTEGER\n"
          155  +     ")%s",
          156  +     useNocase ? "nocase" : "binary",
          157  +     useWithoutRowid ? " WITHOUT ROWID" : ""
          158  +  );
          159  +  if( zSql==0 ) fatal_error("out of memory\n");
          160  +  rc = sqlite3_exec(db, zSql, 0, 0, 0);
          161  +  if( rc ) fatal_error("Could not create the wordcount table: %s.\n",
          162  +                       sqlite3_errmsg(db));
          163  +  sqlite3_free(zSql);
          164  +
          165  +  /* Prepare SQL statements that will be needed */
          166  +  if( iMode==MODE_SELECT ){
          167  +    rc = sqlite3_prepare_v2(db,
          168  +          "SELECT 1 FROM wordcount WHERE word=?1",
          169  +          -1, &pSelect, 0);
          170  +    if( rc ) fatal_error("Could not prepare the SELECT statement: %s\n",
          171  +                          sqlite3_errmsg(db));
          172  +    rc = sqlite3_prepare_v2(db,
          173  +          "INSERT INTO wordcount(word,cnt) VALUES(?1,1)",
          174  +          -1, &pInsert, 0);
          175  +    if( rc ) fatal_error("Could not prepare the INSERT statement: %s\n",
          176  +                         sqlite3_errmsg(db));
          177  +  }
          178  +  if( iMode==MODE_SELECT || iMode==MODE_UPDATE || iMode==MODE_INSERT ){
          179  +    rc = sqlite3_prepare_v2(db,
          180  +          "UPDATE wordcount SET cnt=cnt+1 WHERE word=?1",
          181  +          -1, &pUpdate, 0);
          182  +    if( rc ) fatal_error("Could not prepare the UPDATE statement: %s\n",
          183  +                         sqlite3_errmsg(db));
          184  +  }
          185  +  if( iMode==MODE_INSERT ){
          186  +    rc = sqlite3_prepare_v2(db,
          187  +          "INSERT OR IGNORE INTO wordcount(word,cnt) VALUES(?1,1)",
          188  +          -1, &pInsert, 0);
          189  +    if( rc ) fatal_error("Could not prepare the INSERT statement: %s\n",
          190  +                         sqlite3_errmsg(db));
          191  +  }
          192  +  if( iMode==MODE_UPDATE ){
          193  +    rc = sqlite3_prepare_v2(db,
          194  +          "INSERT OR IGNORE INTO wordcount(word,cnt) VALUES(?1,0)",
          195  +          -1, &pInsert, 0);
          196  +    if( rc ) fatal_error("Could not prepare the INSERT statement: %s\n",
          197  +                         sqlite3_errmsg(db));
          198  +  }
          199  +  if( iMode==MODE_REPLACE ){
          200  +    rc = sqlite3_prepare_v2(db,
          201  +          "REPLACE INTO wordcount(word,cnt)"
          202  +          "VALUES(?1,coalesce((SELECT cnt FROM wordcount WHERE word=?1),0)+1)",
          203  +          -1, &pInsert, 0);
          204  +    if( rc ) fatal_error("Could not prepare the REPLACE statement: %s\n",
          205  +                          sqlite3_errmsg(db));
          206  +  }
          207  +
          208  +  /* Process the input file */
          209  +  while( fgets(zInput, sizeof(zInput), in) ){
          210  +    for(i=0; zInput[i]; i++){
          211  +      if( !isalpha(zInput[i]) ) continue;
          212  +      for(j=i+1; isalpha(zInput[j]); j++){}
          213  +
          214  +      /* Found a new word at zInput[i] that is j-i bytes long. 
          215  +      ** Process it into the wordcount table.  */
          216  +      if( iMode==MODE_SELECT ){
          217  +        sqlite3_bind_text(pSelect, 1, zInput+i, j-i, SQLITE_STATIC);
          218  +        rc = sqlite3_step(pSelect);
          219  +        sqlite3_reset(pSelect);
          220  +        if( rc==SQLITE_ROW ){
          221  +          sqlite3_bind_text(pUpdate, 1, zInput+i, j-i, SQLITE_STATIC);
          222  +          if( sqlite3_step(pUpdate)!=SQLITE_DONE ){
          223  +            fatal_error("UPDATE failed: %s\n", sqlite3_errmsg(db));
          224  +          }
          225  +          sqlite3_reset(pUpdate);
          226  +        }else if( rc==SQLITE_DONE ){
          227  +          sqlite3_bind_text(pInsert, 1, zInput+i, j-i, SQLITE_STATIC);
          228  +          if( sqlite3_step(pInsert)!=SQLITE_DONE ){
          229  +            fatal_error("Insert failed: %s\n", sqlite3_errmsg(db));
          230  +          }
          231  +          sqlite3_reset(pInsert);
          232  +        }else{
          233  +          fatal_error("SELECT failed: %s\n", sqlite3_errmsg(db));
          234  +        }
          235  +      }else{
          236  +        sqlite3_bind_text(pInsert, 1, zInput+i, j-i, SQLITE_STATIC);
          237  +        if( sqlite3_step(pInsert)!=SQLITE_DONE ){
          238  +          fatal_error("INSERT failed: %s\n", sqlite3_errmsg(db));
          239  +        }
          240  +        sqlite3_reset(pInsert);
          241  +        if( iMode==MODE_UPDATE
          242  +         || (iMode==MODE_INSERT && sqlite3_changes(db)==0)
          243  +        ){
          244  +          sqlite3_bind_text(pUpdate, 1, zInput+i, j-i, SQLITE_STATIC);
          245  +          if( sqlite3_step(pUpdate)!=SQLITE_DONE ){
          246  +            fatal_error("UPDATE failed: %s\n", sqlite3_errmsg(db));
          247  +          }
          248  +          sqlite3_reset(pUpdate);
          249  +        }
          250  +      }
          251  +      i = j-1;
          252  +    }
          253  +  }
          254  +  sqlite3_exec(db, "COMMIT", 0, 0, 0);
          255  +  if( zFileToRead ) fclose(in);
          256  +  sqlite3_finalize(pInsert);
          257  +  sqlite3_finalize(pUpdate);
          258  +  sqlite3_finalize(pSelect);
          259  +
          260  +  /* Database connection statistics printed after both prepared statements
          261  +  ** have been finalized */
          262  +  sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_USED, &iCur, &iHiwtr, 0);
          263  +  printf("-- Lookaside Slots Used:        %d (max %d)\n", iCur,iHiwtr);
          264  +  sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_HIT, &iCur, &iHiwtr, 0);
          265  +  printf("-- Successful lookasides:       %d\n", iHiwtr);
          266  +  sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE, &iCur, &iHiwtr, 0);
          267  +  printf("-- Lookaside size faults:       %d\n", iHiwtr);
          268  +  sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL, &iCur, &iHiwtr, 0);
          269  +  printf("-- Lookaside OOM faults:        %d\n", iHiwtr);
          270  +  sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_USED, &iCur, &iHiwtr, 0);
          271  +  printf("-- Pager Heap Usage:            %d bytes\n", iCur);
          272  +  sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_HIT, &iCur, &iHiwtr, 1);
          273  +  printf("-- Page cache hits:             %d\n", iCur);
          274  +  sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_MISS, &iCur, &iHiwtr, 1);
          275  +  printf("-- Page cache misses:           %d\n", iCur); 
          276  +  sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_WRITE, &iCur, &iHiwtr, 1);
          277  +  printf("-- Page cache writes:           %d\n", iCur); 
          278  +  sqlite3_db_status(db, SQLITE_DBSTATUS_SCHEMA_USED, &iCur, &iHiwtr, 0);
          279  +  printf("-- Schema Heap Usage:           %d bytes\n", iCur); 
          280  +  sqlite3_db_status(db, SQLITE_DBSTATUS_STMT_USED, &iCur, &iHiwtr, 0);
          281  +  printf("-- Statement Heap Usage:        %d bytes\n", iCur); 
          282  +
          283  +  sqlite3_close(db);
          284  +
          285  +  /* Global memory usage statistics printed after the database connection
          286  +  ** has closed.  Memory usage should be zero at this point. */
          287  +  sqlite3_status(SQLITE_STATUS_MEMORY_USED, &iCur, &iHiwtr, 0);
          288  +  printf("-- Memory Used (bytes):         %d (max %d)\n", iCur,iHiwtr);
          289  +  sqlite3_status(SQLITE_STATUS_MALLOC_COUNT, &iCur, &iHiwtr, 0);
          290  +  printf("-- Outstanding Allocations:     %d (max %d)\n", iCur,iHiwtr);
          291  +  sqlite3_status(SQLITE_STATUS_PAGECACHE_OVERFLOW, &iCur, &iHiwtr, 0);
          292  +  printf("-- Pcache Overflow Bytes:       %d (max %d)\n", iCur,iHiwtr);
          293  +  sqlite3_status(SQLITE_STATUS_SCRATCH_OVERFLOW, &iCur, &iHiwtr, 0);
          294  +  printf("-- Scratch Overflow Bytes:      %d (max %d)\n", iCur,iHiwtr);
          295  +  sqlite3_status(SQLITE_STATUS_MALLOC_SIZE, &iCur, &iHiwtr, 0);
          296  +  printf("-- Largest Allocation:          %d bytes\n",iHiwtr);
          297  +  sqlite3_status(SQLITE_STATUS_PAGECACHE_SIZE, &iCur, &iHiwtr, 0);
          298  +  printf("-- Largest Pcache Allocation:   %d bytes\n",iHiwtr);
          299  +  sqlite3_status(SQLITE_STATUS_SCRATCH_SIZE, &iCur, &iHiwtr, 0);
          300  +  printf("-- Largest Scratch Allocation:  %d bytes\n", iHiwtr);
          301  +
          302  +  return 0;
          303  +}