SQLite

#!/bin/sh
#
# This script runs the wordcount program in different ways and generates
# an output useful for performance comparisons.
#

# Select the source text to be analyzed.
#
if test "x$1" = "x";
then echo "Usage: $0 FILENAME [ARGS...]"; exit 1;
fi

# Do test runs
#
rm -f wcdb1.db
./wordcount --tag A: --timer --summary wcdb1.db $* --insert
rm -f wcdb2.db
./wordcount --tag B: --timer --summary wcdb2.db $* --insert --without-rowid
rm -f wcdb1.db
./wordcount --tag C: --timer --summary wcdb1.db $* --replace
rm -f wcdb2.db
./wordcount --tag D: --timer --summary wcdb2.db $* --replace --without-rowid
rm -f wcdb1.db
./wordcount --tag E: --timer --summary wcdb1.db $* --select
rm -f wcdb2.db
./wordcount --tag F: --timer --summary wcdb2.db $* --select --without-rowid
./wordcount --tag G: --timer --summary wcdb1.db $* --query
./wordcount --tag H: --timer --summary wcdb1.db $* --query --without-rowid
./wordcount --tag I: --timer --summary wcdb1.db $* --delete
./wordcount --tag J: --timer --summary wcdb2.db $* --delete --without-rowid

# Clean up temporary files created.
#
rm -f wcdb1.db wcdb2.db

**     --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
**     --timer              Time the operation of this program
**     --tag NAME           Tag all output using NAME.  Use only stdout.
**
** 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
**

#include <string.h>
#include <ctype.h>
#include <stdlib.h>
#include <stdarg.h>
#include "sqlite3.h"
#define ISALPHA(X) isalpha((unsigned char)(X))

/* Output tag */
char *zTag = "--";

/* Return the current wall-clock time */
static sqlite3_int64 realTime(void){
  static sqlite3_vfs *clockVfs = 0;
  sqlite3_int64 t;
  if( clockVfs==0 ) clockVfs = sqlite3_vfs_find(0);
  if( clockVfs->iVersion>=1 && clockVfs->xCurrentTimeInt64!=0 ){
    clockVfs->xCurrentTimeInt64(clockVfs, &t);

  printf("%s;\n", zSql);
}

/* An sqlite3_exec() callback that prints results on standard output,
** each column separated by a single space. */
static int printResult(void *NotUsed, int nArg, char **azArg, char **azNm){
  int i;
  printf("%s", zTag);
  for(i=0; i<nArg; i++){
    printf(" %s", azArg[i] ? azArg[i] : "(null)");
  }
  printf("\n");
  return 0;
}

  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" */
  FILE *pTimer = stderr;        /* Output channel for the timer */
  sqlite3_int64 sumCnt = 0;     /* Sum in QUERY mode */
  sqlite3_int64 startTime;
  char zInput[2000];            /* A single line of input */

  /* Process command-line arguments */
  for(i=1; i<argc; i++){
    const char *z = argv[i];

        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 if( strcmp(z,"tag")==0 && i<argc-1 ){
        zTag = argv[++i];
        pTimer = stdout;
      }else{
        fatal_error("unknown option: %s\n", argv[i]);
      }
    }else if( zDbName==0 ){
      zDbName = argv[i];
    }else if( zFileToRead==0 ){
      zFileToRead = argv[i];

  if( zFileToRead ) fclose(in);
  sqlite3_finalize(pInsert);
  sqlite3_finalize(pUpdate);
  sqlite3_finalize(pSelect);
  sqlite3_finalize(pDelete);

  if( iMode==MODE_QUERY ){
    printf("%s sum of cnt: %lld\n", zTag, sumCnt);
    rc = sqlite3_prepare_v2(db,"SELECT sum(cnt*cnt) FROM wordcount", -1,
                            &pSelect, 0);
    if( rc==SQLITE_OK && sqlite3_step(pSelect)==SQLITE_ROW ){
      printf("%s double-check: %lld\n", zTag, sqlite3_column_int64(pSelect, 0));
    }
    sqlite3_finalize(pSelect);
  }


  if( showTimer ){
    sqlite3_int64 elapseTime = realTime() - startTime;
    fprintf(pTimer, "%3d.%03d wordcount", (int)(elapseTime/1000),
                                   (int)(elapseTime%1000));
    for(i=1; i<argc; i++) if( i!=showTimer ) fprintf(pTimer, " %s", argv[i]);
    fprintf(pTimer, "\n");
  }

  if( showSummary ){
    sqlite3_create_function(db, "checksum", -1, SQLITE_UTF8, 0,
                            0, checksumStep, checksumFinalize);
    sqlite3_exec(db, 
      "SELECT 'count(*):  ', count(*) FROM wordcount;\n"

      printResult, 0, 0);
  }

  /* Database connection statistics printed after both prepared statements
  ** have been finalized */
  if( showStats ){
    sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_USED, &iCur, &iHiwtr, 0);
    printf("%s Lookaside Slots Used:        %d (max %d)\n", zTag, iCur,iHiwtr);
    sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_HIT, &iCur, &iHiwtr, 0);
    printf("%s Successful lookasides:       %d\n", zTag, iHiwtr);
    sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE, &iCur,&iHiwtr,0);
    printf("%s Lookaside size faults:       %d\n", zTag, iHiwtr);
    sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL, &iCur,&iHiwtr,0);
    printf("%s Lookaside OOM faults:        %d\n", zTag, iHiwtr);
    sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_USED, &iCur, &iHiwtr, 0);
    printf("%s Pager Heap Usage:            %d bytes\n", zTag, iCur);
    sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_HIT, &iCur, &iHiwtr, 1);
    printf("%s Page cache hits:             %d\n", zTag, iCur);
    sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_MISS, &iCur, &iHiwtr, 1);
    printf("%s Page cache misses:           %d\n", zTag, iCur); 
    sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_WRITE, &iCur, &iHiwtr, 1);
    printf("%s Page cache writes:           %d\n", zTag, iCur); 
    sqlite3_db_status(db, SQLITE_DBSTATUS_SCHEMA_USED, &iCur, &iHiwtr, 0);
    printf("%s Schema Heap Usage:           %d bytes\n", zTag, iCur); 
    sqlite3_db_status(db, SQLITE_DBSTATUS_STMT_USED, &iCur, &iHiwtr, 0);
    printf("%s Statement Heap Usage:        %d bytes\n", zTag, iCur); 
  }

  sqlite3_close(db);

  /* Global memory usage statistics printed after the database connection
  ** has closed.  Memory usage should be zero at this point. */
  if( showStats ){
    sqlite3_status(SQLITE_STATUS_MEMORY_USED, &iCur, &iHiwtr, 0);
    printf("%s Memory Used (bytes):         %d (max %d)\n", zTag,iCur,iHiwtr);
    sqlite3_status(SQLITE_STATUS_MALLOC_COUNT, &iCur, &iHiwtr, 0);
    printf("%s Outstanding Allocations:     %d (max %d)\n",zTag,iCur,iHiwtr);
    sqlite3_status(SQLITE_STATUS_PAGECACHE_OVERFLOW, &iCur, &iHiwtr, 0);
    printf("%s Pcache Overflow Bytes:       %d (max %d)\n",zTag,iCur,iHiwtr);
    sqlite3_status(SQLITE_STATUS_SCRATCH_OVERFLOW, &iCur, &iHiwtr, 0);
    printf("%s Scratch Overflow Bytes:      %d (max %d)\n",zTag,iCur,iHiwtr);
    sqlite3_status(SQLITE_STATUS_MALLOC_SIZE, &iCur, &iHiwtr, 0);
    printf("%s Largest Allocation:          %d bytes\n",zTag,iHiwtr);
    sqlite3_status(SQLITE_STATUS_PAGECACHE_SIZE, &iCur, &iHiwtr, 0);
    printf("%s Largest Pcache Allocation:   %d bytes\n",zTag,iHiwtr);
    sqlite3_status(SQLITE_STATUS_SCRATCH_SIZE, &iCur, &iHiwtr, 0);
    printf("%s Largest Scratch Allocation:  %d bytes\n",zTag,iHiwtr);
  }
  return 0;
}