SQLite

**    May you share freely, never taking more than you give.
**
*************************************************************************
**
** This program implements an SQLite database self-verification utility.
** Usage:
** 
**       dbselftest DATABASE ...
**
** This program reads the "selftest" table in DATABASE, in rowid order,
** and runs each of the tests described there, reporting results at the
** end.
**
** The intent of this program is to have a set of test database files that
** can be run using future versions of SQLite in order to verify that

#include <string.h>
#include <stdarg.h>
#include <stdio.h>
#include <stdlib.h>
#include "sqlite3.h"

static const char zHelp[] =
  "Usage: dbselftest [OPTIONS] DBFILE ...\n"
  "\n"
  "    --init         Create the selftest table\n"
  "    -q             Suppress most output.  Errors only\n"
  "    -v             Show extra output\n"
;

    hash_step(&cx, sqlite3_value_blob(argv[0]), nByte);
  }else{
    hash_step(&cx, sqlite3_value_text(argv[0]), nByte);
  }
  hash_finish(&cx, zOut);
  sqlite3_result_text(context, zOut, 40, SQLITE_TRANSIENT);
}

/*
** Run a prepared statement and compute the SHA1 hash on the
** result rows.
*/
static void sha1RunStatement(SHA1Context *pCtx, sqlite3_stmt *pStmt){
  int nCol = sqlite3_column_count(pStmt);
  const char *z = sqlite3_sql(pStmt);
  int n = (int)strlen(z);

  hash_step_vformat(pCtx,"S%d:",n);
  hash_step(pCtx,(unsigned char*)z,n);

  /* Compute a hash over the result of the query */
  while( SQLITE_ROW==sqlite3_step(pStmt) ){
    int i;
    hash_step(pCtx,(const unsigned char*)"R",1);
    for(i=0; i<nCol; i++){
      switch( sqlite3_column_type(pStmt,i) ){
        case SQLITE_NULL: {
          hash_step(pCtx, (const unsigned char*)"N",1);
          break;
        }
        case SQLITE_INTEGER: {
          sqlite3_uint64 u;
          int j;
          unsigned char x[9];
          sqlite3_int64 v = sqlite3_column_int64(pStmt,i);
          memcpy(&u, &v, 8);
          for(j=8; j>=1; j--){
            x[j] = u & 0xff;
            u >>= 8;
          }
          x[0] = 'I';
          hash_step(pCtx, x, 9);
          break;
        }
        case SQLITE_FLOAT: {
          sqlite3_uint64 u;
          int j;
          unsigned char x[9];
          double r = sqlite3_column_double(pStmt,i);
          memcpy(&u, &r, 8);
          for(j=8; j>=1; j--){
            x[j] = u & 0xff;
            u >>= 8;
          }
          x[0] = 'F';
          hash_step(pCtx,x,9);
          break;
        }
        case SQLITE_TEXT: {
          int n2 = sqlite3_column_bytes(pStmt, i);
          const unsigned char *z2 = sqlite3_column_text(pStmt, i);
          hash_step_vformat(pCtx,"T%d:",n2);
          hash_step(pCtx, z2, n2);
          break;
        }
        case SQLITE_BLOB: {
          int n2 = sqlite3_column_bytes(pStmt, i);
          const unsigned char *z2 = sqlite3_column_blob(pStmt, i);
          hash_step_vformat(pCtx,"B%d:",n2);
          hash_step(pCtx, z2, n2);
          break;
        }
      }
    }
  }
}

/*
** Run one or more statements of SQL.  Compute a SHA1 hash of the output.
*/
static int sha1Exec(
  sqlite3 *db,          /* Run against this database connection */
  const char *zSql,     /* The SQL to be run */
  char *zOut            /* Store the SHA1 hash as hexadecimal in this buffer */
){
  sqlite3_stmt *pStmt = 0;    /* A prepared statement */
  int rc;                     /* Result of an API call */
  SHA1Context cx;             /* The SHA1 hash context */

  hash_init(&cx);
  while( zSql[0] ){
    rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, &zSql);
    if( rc ){
      sqlite3_finalize(pStmt);
      return rc;
    }
    sha1RunStatement(&cx, pStmt);
    sqlite3_finalize(pStmt);
  }
  hash_finish(&cx, zOut);
  return SQLITE_OK;
}

/*
** Implementation of the sha1_query(SQL) function.
**
** This function compiles and runs the SQL statement(s) given in the
** argument. The results are hashed using SHA1 and that hash is returned.
**

  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  sqlite3 *db = sqlite3_context_db_handle(context);
  const char *zSql = (const char*)sqlite3_value_text(argv[0]);
  sqlite3_stmt *pStmt = 0;


  int rc;


  SHA1Context cx;
  char zOut[44];

  assert( argc==1 );
  if( zSql==0 ) return;
  hash_init(&cx);
  while( zSql[0] ){

    if( !sqlite3_stmt_readonly(pStmt) ){
      char *zMsg = sqlite3_mprintf("non-query: [%s]", sqlite3_sql(pStmt));
      sqlite3_finalize(pStmt);
      sqlite3_result_error(context, zMsg, -1);
      sqlite3_free(zMsg);
      return;
    }





    sha1RunStatement(&cx, pStmt);






















































    sqlite3_finalize(pStmt);
  }
  hash_finish(&cx, zOut);
  sqlite3_result_text(context, zOut, 40, SQLITE_TRANSIENT);
}
/* End of ext/misc/sha1.c
******************************************************************************/

    p->nAlloc += p->n+n + 100;
    p->z = sqlite3_realloc(p->z, p->nAlloc);
    if( z==0 ){
      printf("Could not allocate %d bytes\n", p->nAlloc);
      exit(1);
    }
  }
  memcpy(p->z+p->n, z, n+1);
  p->n += n;
}

/* This is an sqlite3_exec() callback that will capture all
** output in a Str.
**
** Columns are separated by ",".  Rows are separated by "|".
*/
static int execCallback(void *pStr, int argc, char **argv, char **colv){
  int i;
  Str *p = (Str*)pStr;
  if( p->n ) strAppend(p, "|");
  for(i=0; i<argc; i++){
    const char *z = (const char*)argv[i];
    if( z==0 ) z = "NULL";
    if( i>0 ) strAppend(p, ",");
    strAppend(p, z);
  }
  return 0;
}

/*
** Run an SQL statement constructing using sqlite3_vmprintf().
** Return the number of errors.
*/
static int runSql(sqlite3 *db, const char *zFormat, ...){
  char *zSql;
  char *zErr = 0;
  int rc;
  int nErr = 0;
  va_list ap;

  va_start(ap, zFormat);
  zSql = sqlite3_vmprintf(zFormat, ap);
  va_end(ap);
  if( zSql==0 ){
    printf("Out of memory\n");
    exit(1);
  }
  rc = sqlite3_exec(db, zSql, 0, 0, &zErr);
  if( rc || zErr ){
    printf("SQL error in [%s]: code=%d: %s\n", zSql, rc, zErr);
    nErr++;
  }
  sqlite3_free(zSql);
  return nErr;
}

/*
** Generate a prepared statement using a formatted string.
*/
static sqlite3_stmt *prepareSql(sqlite3 *db, const char *zFormat, ...){
  char *zSql;
  int rc;
  sqlite3_stmt *pStmt = 0;
  va_list ap;

  va_start(ap, zFormat);
  zSql = sqlite3_vmprintf(zFormat, ap);
  va_end(ap);
  if( zSql==0 ){
    printf("Out of memory\n");
    exit(1);
  }
  rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
  if( rc ){
    printf("SQL error in [%s]: code=%d: %s\n", zSql, rc, sqlite3_errmsg(db));
    sqlite3_finalize(pStmt);
    pStmt = 0;
  }
  sqlite3_free(zSql);
  return pStmt;
}

/*
** Construct the standard selftest configuration for the database.
*/
static int buildSelftestTable(sqlite3 *db){
  int rc;
  sqlite3_stmt *pStmt;
  int tno = 110;
  char *zSql;
  char zHash[50];

  rc = runSql(db,
     "CREATE TABLE IF NOT EXISTS selftest(\n"
     "  tno INTEGER PRIMARY KEY,  -- test number\n"
     "  op TEXT,                  -- what kind of test\n"
     "  sql TEXT,                 -- SQL text for the test\n"
     "  ans TEXT                  -- expected answer\n"
     ");"
     "INSERT INTO selftest"
     " VALUES(100,'memo','Hashes generated using --init',NULL);"
  );
  if( rc ) return 1;
  tno = 110;
  zSql = "SELECT type,name,tbl_name,sql FROM sqlite_master";
  sha1Exec(db, zSql, zHash);
  rc = runSql(db, 
      "INSERT INTO selftest(tno,op,sql,ans)"
      " VALUES(%d,'sha1',%Q,%Q)", tno, zSql, zHash);
  tno += 10;
  pStmt = prepareSql(db,
    "SELECT lower(name) FROM sqlite_master"
    " WHERE type='table' AND sql NOT GLOB 'CREATE VIRTUAL*'"
    "   AND name<>'selftest'"
    " ORDER BY 1");
  if( pStmt==0 ) return 1;
  while( SQLITE_ROW==sqlite3_step(pStmt) ){
    zSql = sqlite3_mprintf("SELECT * FROM \"%w\" NOT INDEXED",
                            sqlite3_column_text(pStmt, 0));
    if( zSql==0 ){
      printf("Of of memory\n");
      exit(1);
    }
    sha1Exec(db, zSql, zHash);
    rc = runSql(db,
      "INSERT INTO selftest(tno,op,sql,ans)"
      " VALUES(%d,'sha1',%Q,%Q)", tno, zSql, zHash);
    tno += 10;
    sqlite3_free(zSql);
    if( rc ) break;
  }
  sqlite3_finalize(pStmt);
  if( rc ) return 1;
  rc = runSql(db,
     "INSERT INTO selftest(tno,op,sql,ans)"
     " VALUES(%d,'run','PRAGMA integrity_check','ok');", tno);
  if( rc ) return 1;
  return rc;
}

/*
** Return true if the named table exists
*/
static int tableExists(sqlite3 *db, const char *zTab){
  return sqlite3_table_column_metadata(db, "main", zTab, 0, 0, 0, 0, 0, 0)
            == SQLITE_OK;
}

/*
** Default selftest table content, for use when there is no selftest table
*/
static char *azDefaultTest[] = {
   0, 0, 0, 0,
   "0", "memo", "Missing SELFTEST table - default checks only", "",
   "1", "run", "PRAGMA integrity_check", "ok"
};

int main(int argc, char **argv){
  int eVolume = VOLUME_LOW;    /* How much output to display */
  const char **azDb = 0;       /* Name of the database file */
  int nDb = 0;                 /* Number of database files to check */
  int doInit = 0;              /* True if --init is present */
  sqlite3 *db = 0;             /* Open database connection */
  int rc;                      /* Return code from API calls */
  char *zErrMsg = 0;           /* An error message return */
  char **azTest;               /* Content of the selftest table */
  int nRow = 0, nCol = 0;      /* Rows and columns in azTest[] */
  int i;                       /* Loop counter */
  int nErr = 0;                /* Number of errors */
  int iDb;                     /* Loop counter for databases */
  Str str;                     /* Result accumulator */
  int nTest = 0;               /* Number of tests run */

  for(i=1; i<argc; i++){
    const char *z = argv[i];
    if( z[0]=='-' ){
      if( z[1]=='-' ) z++;
      if( strcmp(z, "-help")==0 ){
        printf("%s", zHelp);

        if( eVolume<VOLUME_MAX) eVolume++;
      }else
      {
        printf("unknown option: \"%s\"\nUse --help for more information\n",
               argv[i]);
        return 1;
      }



    }else{
      nDb++;


      azDb = sqlite3_realloc(azDb, nDb*sizeof(azDb[0]));
      if( azDb==0 ){
        printf("out of memory\n");
        exit(1);
      }
      azDb[nDb-1] = argv[i];



    }





  }
  if( nDb==0 ){



















    printf("No databases specified.  Use --help for more info\n");







    return 1;
  }
  if( eVolume>=VOLUME_LOW ){
    printf("SQLite %s\n", sqlite3_sourceid());
  }
  memset(&str, 0, sizeof(str));
  strAppend(&str, "\n");
  for(iDb=0; iDb<nDb; iDb++, sqlite3_close(db)){
    rc = sqlite3_open_v2(azDb[iDb], &db, 



          doInit ? SQLITE_OPEN_READWRITE : SQLITE_OPEN_READONLY, 0);
    if( rc ){
      printf("Cannot open \"%s\": %s\n", azDb[iDb], sqlite3_errmsg(db));

      return 1;
    }


    rc = sqlite3_create_function(db, "sha1", 1, SQLITE_UTF8, 0,
                                 sha1Func, 0, 0);


    if( rc==SQLITE_OK ){



      rc = sqlite3_create_function(db, "sha1_query", 1, SQLITE_UTF8, 0,


                                   sha1QueryFunc, 0, 0);
    }
    if( rc ){


      printf("Initialization error: %s\n", sqlite3_errmsg(db));

      sqlite3_close(db);










      return 1;
    }
    if( doInit && !tableExists(db, "selftest") ){
       buildSelftestTable(db);
    }
    if( !tableExists(db, "selftest") ){
      azTest = azDefaultTest;
      nCol = 4;
      nRow = 2;
    }else{
      rc = sqlite3_get_table(db, 
          "SELECT tno,op,sql,ans FROM selftest ORDER BY tno",
          &azTest, &nRow, &nCol, &zErrMsg);
      if( rc || zErrMsg ){
        printf("Error querying selftest: %s\n", zErrMsg);
        sqlite3_free_table(azTest);
        continue;
      }
    }
    for(i=1; i<=nRow; i++){
      int tno = atoi(azTest[i*nCol]);
      const char *zOp = azTest[i*nCol+1];
      const char *zSql = azTest[i*nCol+2];
      const char *zAns = azTest[i*nCol+3];
  
      if( eVolume>=VOLUME_ECHO ){
        char *zQuote = sqlite3_mprintf("%q", zSql);
        printf("%d: %s %s\n", tno, zOp, zSql);
        sqlite3_free(zQuote);
      }
      if( strcmp(zOp,"memo")==0 ){
        if( eVolume>=VOLUME_LOW ){
          printf("%s: %s\n", azDb[iDb], zSql);
        }
      }else
      if( strcmp(zOp,"sha1")==0 ){
        char zOut[44];
        rc = sha1Exec(db, zSql, zOut);
        nTest++;
        if( eVolume>=VOLUME_VERBOSE ){
          printf("Result: %s\n", zOut);
        }
        if( rc ){
          nErr++;
          if( eVolume>=VOLUME_ERROR_ONLY ){
            printf("%d: error-code-%d: %s\n", tno, rc, sqlite3_errmsg(db));
          }
        }else if( strcmp(zAns,zOut)!=0 ){
          nErr++;
          if( eVolume>=VOLUME_ERROR_ONLY ){
            printf("%d: Expected: [%s]\n", tno, zAns);
            printf("%d:      Got: [%s]\n", tno, zOut);
          }
        }
      }else
      if( strcmp(zOp,"run")==0 ){
        str.n = 0;
        str.z[0] = 0;
        zErrMsg = 0;
        rc = sqlite3_exec(db, zSql, execCallback, &str, &zErrMsg);
        nTest++;
        if( eVolume>=VOLUME_VERBOSE ){
          printf("Result: %s\n", str.z);
        }
        if( rc || zErrMsg ){
          nErr++;
          if( eVolume>=VOLUME_ERROR_ONLY ){
            printf("%d: error-code-%d: %s\n", tno, rc, zErrMsg);
          }
          sqlite3_free(zErrMsg);
        }else if( strcmp(zAns,str.z)!=0 ){
          nErr++;
          if( eVolume>=VOLUME_ERROR_ONLY ){
            printf("%d: Expected: [%s]\n", tno, zAns);
            printf("%d:      Got: [%s]\n", tno, str.z);
          }
        }
      }else
      {
        printf("Unknown operation \"%s\" on selftest line %d\n", zOp, tno);
        return 1;
      }
    }
    if( azTest!=azDefaultTest ) sqlite3_free_table(azTest);
  }
  if( eVolume>=VOLUME_LOW || (nErr>0 && eVolume>=VOLUME_ERROR_ONLY) ){
    printf("%d errors out of %d tests on %d databases\n", nErr, nTest, nDb);
  }
  return nErr;
}