sqllogictest

<h2>Test-Script Format</h2>

Test scripts are line-oriented ASCII text files.  
No provision is made for Unicode; the purpose
of sqllogictest is to test the query and join logic of the database engine, not
its support for localization and internationalization.

Test scripts consist of zero or more records.  A record is a
single statement or query or a control record.  Each record
is separated from its neighbors by one or more blank line.  Records
are evaluated in order, starting from the beginning of the
script and working toward the end.

Lines of the test script that begin with the sharp character
("#", ASCII code 35)
are comment lines and are ignored.  Comment lines are not considered blank
lines and cannot be used to separate records.  Comments
typically occur at the beginning of a record, but they are
allowed to occur in the middle of a record.  Comments
that occurs in the middle of an SQL statement are stripped from the
statement prior to the statement being sent to the database engine for
evaluation.  Comments are logically removed from the script by a preprocessor.
Hence, when we speak of the "first line of a record" we
really mean the "first non-comment line of a record".

Most records are either a statement or a query.  A statement is an SQL
command that is to be evaluated but from which we do not expect to get
results (other than success or failure).  A statement might be a
CREATE TABLE or an INSERT or an UPDATE or a DROP INDEX.  A query is an
SQL command from which we expect to receive results.  The result set
might be empty.

A statement record begins with one of the following two lines:

In the results section, integer values are rendered as if by
printf("%d").  Floating point values are rendered as if by
printf("%.3f").  NULL values are rendered as "NULL".  Empty
strings are rendered as "(empty)".  Within non-empty strings,
all control characters and unprintable characters are rendered as "@".

<h3>Control Records</h3>

The test script might also contain control records.  A control record is
one of the following:

<blockquote>
<tt>halt</tt><br>
<tt>hash-threshold</td> &lt;max-result-set-size&gt;
</blockquote>

A "halt" record is intended for debugging use only.  A halt record merely
causes sqllogictest to ignore the rest of the test script.  A halt record
can be inserted after a query that is giving an anomalous result, causing
the database to be left in the state where it gives the unexpected answer.
After sqllogictest exist, manually debugging can then proceed.

The "hash-threshold" record sets a limit on the number of values that can
appear in a result set.  If the number of values exceeds this, then instead
of recording each individual value in the full test script, an MD5 hash of
all values is computed in stored.  This makes the full test scripts much
shorter, but at the cost of obscuring the results.  If the hash-threshold
is 0, then results are never hashed.  A hash-threshold of 10 or 20 is
recommended.  During debugging, it is advantage to set the hash-threshold
to zero so that all results can be seen.

<h2>Suggestions For Generating Test-Scripts</h2>

When sqllogictest runs a test script, it begins with a completely
empty database.  So the first few records of any test script will
typically be CREATE statements of various kinds and expecially
CREATE TABLE statements.  In order to maximize the portability of
scripts across database engines, it is suggested that test scripts stick 

  *  Generate a random WHERE clause.

  *  Generate a random string literal of some maximum length.

  *  Generate a random identifier which is not a keyword.

Segregate queries that use LIMIT and OFFSET into separate test scripts
which are only run on database engines that support LIMIT and OFFSET.

All queries should use either an ORDER BY clause so that
the order of values in the output is deterministic, or else

the "rowsort" or "valuesort" modifiers at the beginning
of the query record to ensure that the output appears in the same order
on all database engines.

A typical test script will begin with some CREATE statements followed by
some INSERT statements to add initial data.  This is followed by
thousands of randomly generate UPDATE, DELETE, and INSERT statements.
Several SELECT statements typical follow each UPDATE, DELETE, or INSERT

#
LIB = $(LDFLAGS)

# You should not need to change anything below this line
###############################################################################
#
OBJ = \
  md5.o \
  sqlite3.o

sqllogictest$(E):	sqllogictest.c sqllogictest.h $(OBJ)
	$(CC) -o sqllogictest$(E) sqllogictest.c $(OBJ)

md5.o:	md5.c
	$(CC) -c md5.c

sqlite3.o:	sqlite3.c sqlite3.h
	$(CC) -c sqlite3.c -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION

clean:
	rm -f $(OBJ)

/*
** This code taken from the SQLite test library.  Originally found on
** the internet.  The original header comment follows this comment.
** The code is largerly unchanged, but there have been some modifications.
*/
/*
 * This code implements the MD5 message-digest algorithm.
 * The algorithm is due to Ron Rivest.  This code was
 * written by Colin Plumb in 1993, no copyright is claimed.
 * This code is in the public domain; do with it what you wish.
 *
 * Equivalent code is available from RSA Data Security, Inc.
 * This code has been tested against that, and is equivalent,
 * except that you don't need to include two pages of legalese
 * with every copy.
 *
 * To compute the message digest of a chunk of bytes, declare an
 * MD5Context structure, pass it to MD5Init, call MD5Update as
 * needed on buffers full of bytes, and then call MD5Final, which
 * will fill a supplied 16-byte array with the digest.
 */
#include <string.h>

/*
 * If compiled on a machine that doesn't have a 32-bit integer,
 * you just set "uint32" to the appropriate datatype for an
 * unsigned 32-bit integer.  For example:
 *
 *       cc -Duint32='unsigned long' md5.c
 *
 */
#ifndef uint32
#  define uint32 unsigned int
#endif

struct Context {
  int isInit;
  uint32 buf[4];
  uint32 bits[2];
  unsigned char in[64];
};
typedef struct Context MD5Context;

/*
 * Note: this code is harmless on little-endian machines.
 */
static void byteReverse (unsigned char *buf, unsigned longs){
        uint32 t;
        do {
                t = (uint32)((unsigned)buf[3]<<8 | buf[2]) << 16 |
                            ((unsigned)buf[1]<<8 | buf[0]);
                *(uint32 *)buf = t;
                buf += 4;
        } while (--longs);
}
/* The four core functions - F1 is optimized somewhat */

/* #define F1(x, y, z) (x & y | ~x & z) */
#define F1(x, y, z) (z ^ (x & (y ^ z)))
#define F2(x, y, z) F1(z, x, y)
#define F3(x, y, z) (x ^ y ^ z)
#define F4(x, y, z) (y ^ (x | ~z))

/* This is the central step in the MD5 algorithm. */
#define MD5STEP(f, w, x, y, z, data, s) \
        ( w += f(x, y, z) + data,  w = w<<s | w>>(32-s),  w += x )

/*
 * The core of the MD5 algorithm, this alters an existing MD5 hash to
 * reflect the addition of 16 longwords of new data.  MD5Update blocks
 * the data and converts bytes into longwords for this routine.
 */
static void MD5Transform(uint32 buf[4], const uint32 in[16]){
        register uint32 a, b, c, d;

        a = buf[0];
        b = buf[1];
        c = buf[2];
        d = buf[3];

        MD5STEP(F1, a, b, c, d, in[ 0]+0xd76aa478,  7);
        MD5STEP(F1, d, a, b, c, in[ 1]+0xe8c7b756, 12);
        MD5STEP(F1, c, d, a, b, in[ 2]+0x242070db, 17);
        MD5STEP(F1, b, c, d, a, in[ 3]+0xc1bdceee, 22);
        MD5STEP(F1, a, b, c, d, in[ 4]+0xf57c0faf,  7);
        MD5STEP(F1, d, a, b, c, in[ 5]+0x4787c62a, 12);
        MD5STEP(F1, c, d, a, b, in[ 6]+0xa8304613, 17);
        MD5STEP(F1, b, c, d, a, in[ 7]+0xfd469501, 22);
        MD5STEP(F1, a, b, c, d, in[ 8]+0x698098d8,  7);
        MD5STEP(F1, d, a, b, c, in[ 9]+0x8b44f7af, 12);
        MD5STEP(F1, c, d, a, b, in[10]+0xffff5bb1, 17);
        MD5STEP(F1, b, c, d, a, in[11]+0x895cd7be, 22);
        MD5STEP(F1, a, b, c, d, in[12]+0x6b901122,  7);
        MD5STEP(F1, d, a, b, c, in[13]+0xfd987193, 12);
        MD5STEP(F1, c, d, a, b, in[14]+0xa679438e, 17);
        MD5STEP(F1, b, c, d, a, in[15]+0x49b40821, 22);

        MD5STEP(F2, a, b, c, d, in[ 1]+0xf61e2562,  5);
        MD5STEP(F2, d, a, b, c, in[ 6]+0xc040b340,  9);
        MD5STEP(F2, c, d, a, b, in[11]+0x265e5a51, 14);
        MD5STEP(F2, b, c, d, a, in[ 0]+0xe9b6c7aa, 20);
        MD5STEP(F2, a, b, c, d, in[ 5]+0xd62f105d,  5);
        MD5STEP(F2, d, a, b, c, in[10]+0x02441453,  9);
        MD5STEP(F2, c, d, a, b, in[15]+0xd8a1e681, 14);
        MD5STEP(F2, b, c, d, a, in[ 4]+0xe7d3fbc8, 20);
        MD5STEP(F2, a, b, c, d, in[ 9]+0x21e1cde6,  5);
        MD5STEP(F2, d, a, b, c, in[14]+0xc33707d6,  9);
        MD5STEP(F2, c, d, a, b, in[ 3]+0xf4d50d87, 14);
        MD5STEP(F2, b, c, d, a, in[ 8]+0x455a14ed, 20);
        MD5STEP(F2, a, b, c, d, in[13]+0xa9e3e905,  5);
        MD5STEP(F2, d, a, b, c, in[ 2]+0xfcefa3f8,  9);
        MD5STEP(F2, c, d, a, b, in[ 7]+0x676f02d9, 14);
        MD5STEP(F2, b, c, d, a, in[12]+0x8d2a4c8a, 20);

        MD5STEP(F3, a, b, c, d, in[ 5]+0xfffa3942,  4);
        MD5STEP(F3, d, a, b, c, in[ 8]+0x8771f681, 11);
        MD5STEP(F3, c, d, a, b, in[11]+0x6d9d6122, 16);
        MD5STEP(F3, b, c, d, a, in[14]+0xfde5380c, 23);
        MD5STEP(F3, a, b, c, d, in[ 1]+0xa4beea44,  4);
        MD5STEP(F3, d, a, b, c, in[ 4]+0x4bdecfa9, 11);
        MD5STEP(F3, c, d, a, b, in[ 7]+0xf6bb4b60, 16);
        MD5STEP(F3, b, c, d, a, in[10]+0xbebfbc70, 23);
        MD5STEP(F3, a, b, c, d, in[13]+0x289b7ec6,  4);
        MD5STEP(F3, d, a, b, c, in[ 0]+0xeaa127fa, 11);
        MD5STEP(F3, c, d, a, b, in[ 3]+0xd4ef3085, 16);
        MD5STEP(F3, b, c, d, a, in[ 6]+0x04881d05, 23);
        MD5STEP(F3, a, b, c, d, in[ 9]+0xd9d4d039,  4);
        MD5STEP(F3, d, a, b, c, in[12]+0xe6db99e5, 11);
        MD5STEP(F3, c, d, a, b, in[15]+0x1fa27cf8, 16);
        MD5STEP(F3, b, c, d, a, in[ 2]+0xc4ac5665, 23);

        MD5STEP(F4, a, b, c, d, in[ 0]+0xf4292244,  6);
        MD5STEP(F4, d, a, b, c, in[ 7]+0x432aff97, 10);
        MD5STEP(F4, c, d, a, b, in[14]+0xab9423a7, 15);
        MD5STEP(F4, b, c, d, a, in[ 5]+0xfc93a039, 21);
        MD5STEP(F4, a, b, c, d, in[12]+0x655b59c3,  6);
        MD5STEP(F4, d, a, b, c, in[ 3]+0x8f0ccc92, 10);
        MD5STEP(F4, c, d, a, b, in[10]+0xffeff47d, 15);
        MD5STEP(F4, b, c, d, a, in[ 1]+0x85845dd1, 21);
        MD5STEP(F4, a, b, c, d, in[ 8]+0x6fa87e4f,  6);
        MD5STEP(F4, d, a, b, c, in[15]+0xfe2ce6e0, 10);
        MD5STEP(F4, c, d, a, b, in[ 6]+0xa3014314, 15);
        MD5STEP(F4, b, c, d, a, in[13]+0x4e0811a1, 21);
        MD5STEP(F4, a, b, c, d, in[ 4]+0xf7537e82,  6);
        MD5STEP(F4, d, a, b, c, in[11]+0xbd3af235, 10);
        MD5STEP(F4, c, d, a, b, in[ 2]+0x2ad7d2bb, 15);
        MD5STEP(F4, b, c, d, a, in[ 9]+0xeb86d391, 21);

        buf[0] += a;
        buf[1] += b;
        buf[2] += c;
        buf[3] += d;
}

/*
 * Start MD5 accumulation.  Set bit count to 0 and buffer to mysterious
 * initialization constants.
 */
static void MD5Init(MD5Context *ctx){
        ctx->isInit = 1;
        ctx->buf[0] = 0x67452301;
        ctx->buf[1] = 0xefcdab89;
        ctx->buf[2] = 0x98badcfe;
        ctx->buf[3] = 0x10325476;
        ctx->bits[0] = 0;
        ctx->bits[1] = 0;
}

/*
 * Update context to reflect the concatenation of another buffer full
 * of bytes.
 */
static 
void MD5Update(MD5Context *pCtx, const unsigned char *buf, unsigned int len){
        struct Context *ctx = (struct Context *)pCtx;
        uint32 t;

        /* Update bitcount */

        t = ctx->bits[0];
        if ((ctx->bits[0] = t + ((uint32)len << 3)) < t)
                ctx->bits[1]++; /* Carry from low to high */
        ctx->bits[1] += len >> 29;

        t = (t >> 3) & 0x3f;    /* Bytes already in shsInfo->data */

        /* Handle any leading odd-sized chunks */

        if ( t ) {
                unsigned char *p = (unsigned char *)ctx->in + t;

                t = 64-t;
                if (len < t) {
                        memcpy(p, buf, len);
                        return;
                }
                memcpy(p, buf, t);
                byteReverse(ctx->in, 16);
                MD5Transform(ctx->buf, (uint32 *)ctx->in);
                buf += t;
                len -= t;
        }

        /* Process data in 64-byte chunks */

        while (len >= 64) {
                memcpy(ctx->in, buf, 64);
                byteReverse(ctx->in, 16);
                MD5Transform(ctx->buf, (uint32 *)ctx->in);
                buf += 64;
                len -= 64;
        }

        /* Handle any remaining bytes of data. */

        memcpy(ctx->in, buf, len);
}

/*
 * Final wrapup - pad to 64-byte boundary with the bit pattern 
 * 1 0* (64-bit count of bits processed, MSB-first)
 */
static void MD5Final(unsigned char digest[16], MD5Context *pCtx){
        struct Context *ctx = (struct Context *)pCtx;
        unsigned count;
        unsigned char *p;

        /* Compute number of bytes mod 64 */
        count = (ctx->bits[0] >> 3) & 0x3F;

        /* Set the first char of padding to 0x80.  This is safe since there is
           always at least one byte free */
        p = ctx->in + count;
        *p++ = 0x80;

        /* Bytes of padding needed to make 64 bytes */
        count = 64 - 1 - count;

        /* Pad out to 56 mod 64 */
        if (count < 8) {
                /* Two lots of padding:  Pad the first block to 64 bytes */
                memset(p, 0, count);
                byteReverse(ctx->in, 16);
                MD5Transform(ctx->buf, (uint32 *)ctx->in);

                /* Now fill the next block with 56 bytes */
                memset(ctx->in, 0, 56);
        } else {
                /* Pad block to 56 bytes */
                memset(p, 0, count-8);
        }
        byteReverse(ctx->in, 14);

        /* Append length in bits and transform */
        ((uint32 *)ctx->in)[ 14 ] = ctx->bits[0];
        ((uint32 *)ctx->in)[ 15 ] = ctx->bits[1];

        MD5Transform(ctx->buf, (uint32 *)ctx->in);
        byteReverse((unsigned char *)ctx->buf, 4);
        memcpy(digest, ctx->buf, 16);
        memset(ctx, 0, sizeof(ctx));    /* In case it is sensitive */
}

/*
** Convert a digest into base-16.  digest should be declared as
** "unsigned char digest[16]" in the calling function.  The MD5
** digest is stored in the first 16 bytes.  zBuf should
** be "char zBuf[33]".
*/
static void DigestToBase16(unsigned char *digest, char *zBuf){
  static char const zEncode[] = "0123456789abcdef";
  int i, j;

  for(j=i=0; i<16; i++){
    int a = digest[i];
    zBuf[j++] = zEncode[(a>>4)&0xf];
    zBuf[j++] = zEncode[a & 0xf];
  }
  zBuf[j] = 0;
}

/*
** Status of an MD5 hash.
*/
static MD5Context ctx;
static int isInit = 0;
static char zResult[34] = "";

/*
** Add additional text to the current MD5 hash.
*/
void md5_add(const char *z){
  if( !isInit ){
    MD5Init(&ctx);
    isInit = 1;
  }
  MD5Update(&ctx, (unsigned char*)z, (unsigned)strlen(z));
}

/*
** Compute the final signature.  Reset the hash generator in preparation
** for the next round.
*/
const char *md5_finish(void){
  if( isInit ){
    unsigned char digest[16];
    MD5Final(digest, &ctx);
    isInit = 0;
    DigestToBase16(digest, zResult);
  }
  return zResult;
}

** On connect, it will attempt to "DROP" all existing tables 
** from the database name 'slt' to reset it to a known status.
**
** The DSN name and DB name are controlled by the defines
** SLT_DSN and SLT_DB.
**
*/
#ifndef OMIT_ODBC  /* Omit this module if OMIT_ODBC is defined */

#ifdef WIN32
#include <windows.h>
#endif
#define SQL_NOUNICODEMAP
#include <sql.h>
#include <sqlext.h>

  };
  sqllogictestRegisterEngine(&ODBC3DbEngine);
}

/*
**************** End of the ODBC3 database engine interface *****************
*****************************************************************************/
#endif /* OMIT_ODBC */

/*
** This is the main routine.  This routine runs first.  It processes
** command-line arguments then runs the test.
*/
int main(int argc, char **argv){
  int verifyMode = 0;                  /* True if in -verify mode */
  const char *zScriptFile = 0;         /* Input script filename */
  const char *zDbEngine = "SQLite";    /* Name of database engine */
  const char *zConnection = 0;         /* Connection string on DB engine */
  const DbEngine *pEngine = 0;         /* Pointer to DbEngine object */
  int i;                               /* Loop counter */
  char *zScript;                       /* Content of the script */
  long nScript;                        /* Size of the script in bytes */
  void *pConn;                         /* Connection to the database engine */
  int rc;                              /* Result code from subroutine call */
  int nErr = 0;                        /* Number of errors */
  int nCmd = 0;                        /* Number of SQL statements processed */
  int nResult;                         /* Number of query results */
  char **azResult;                     /* Query result vector */
  Script sScript;                      /* Script parsing status */
  FILE *in;                            /* For reading script */
  int hashThreshold = 0;               /* Hash result if this long or longer */
  

  /* Add calls to the registration procedures for new database engine
  ** interfaces here
  */
  registerSqlite();
#ifndef OMIT_ODBC
  registerODBC3();
#endif

  /* Report an error if no registered engines
  */
  if( nEngine == 0 ){
    fprintf(stderr, "%s: no registered database engines\n", argv[0]);
    usage(argv[0]);
  }

  /* Default to first registered engine 
  */
  if( zDbEngine == NULL ){
    zDbEngine = apEngine[0]->zName;
  }

  /* Scan the command-line and process arguments
  */
  for(i=1; i<argc; i++){
    int n = (int)strlen(argv[i]);
    if( strncmp(argv[i], "-verify",n)==0 ){
      verifyMode = 1;
#if 0  /* Obsolete code */
    }else if( strncmp(argv[i], "-engine",n)==0 ){
      zDbEngine = argv[++i];
    }else if( strncmp(argv[i], "-connection",n)==0 ){
      zConnection = argv[++i];
#endif
    }else if( strncmp(argv[i], "-odbc",n)==0 ){
      zDbEngine = "ODBC3";
      zConnection = argv[++i];
    }else if( zScriptFile==0 ){
      zScriptFile = argv[i];
    }else{
      fprintf(stderr, "%s: unknown argument: %s\n", argv[0], argv[i]);
      usage(argv[0]);
    }
  }

        nColumn = 1;
        qsort(azResult, nResult, sizeof(azResult[0]), rowCompare);
      }else{
        fprintf(stderr, "%s:%d: unknown sort method: '%s'\n",
                zScriptFile, sScript.startLine, sScript.azToken[2]);
        nErr++;
      }

      /* Hash the results if we are over the hash threshold */
      if( hashThreshold>0 && nResult>hashThreshold ){
        for(i=0; i<nResult; i++){
          md5_add(azResult[i]);
          md5_add("\n");
        }
      }

      if( verifyMode ){
        /* In verify mode, first skip over the ---- line if we are still
        ** pointing at it. */
        if( strcmp(sScript.zLine, "----")==0 ) nextLine(&sScript);

        /* Compare subsequent lines of the script against the results
        ** from the query.  Report an error if any differences are found.
        */
        if( hashThreshold==0 || nResult<=hashThreshold ){
        for(i=0; i<nResult && sScript.zLine[0]; nextLine(&sScript), i++){
          if( strcmp(sScript.zLine, azResult[i])!=0 ){
            fprintf(stderr,"%s:%d: wrong result\n", zScriptFile,
                    sScript.nLine);
            nErr++;
            break;
          }
          }
        }else{
          if( strcmp(sScript.zLine, md5_finish())!=0 ){
            fprintf(stderr, "%s:%d: wrong result hash\n",
                    zScriptFile, sScript.nLine);
            nErr++;
          }
        }
      }else{
        /* In completion mode, first make sure we have output an ---- line.
        ** Output such a line now if we have not already done so.
        */
        if( strcmp(sScript.zLine, "----")!=0 ){
          printf("----\n");
        }

        /* Output the results obtained by running the query
        */
        if( hashThreshold==0 || nResult<=hashThreshold ){
        for(i=0; i<nResult; i++){
          printf("%s\n", azResult[i]);
        }
        }else{
          printf("%s\n", md5_finish());
        }
        printf("\n");

        /* Skip over any existing results.  They will be ignored.
        */
        sScript.copyFlag = 0;
        while( sScript.zLine[0]!=0 && sScript.iCur<sScript.iEnd ){
          nextLine(&sScript);
        }
        sScript.copyFlag = 1;
      }

      /* Free the query results */
      pEngine->xFreeResults(pConn, azResult, nResult);
    }else if( strcmp(sScript.azToken[0],"hash-threshold")==0 ){
      /* Set the maximum number of result values that will be accepted
      ** for a query.  If the number of result values exceeds this number,
      ** then an MD5 hash is computed of all values, and the resulting hash
      ** is the only result.
      **
      ** If the threshold is 0, then hashing is never used.
      */
      hashThreshold = atoi(sScript.azToken[1]);
    }else if( strcmp(sScript.azToken[0],"halt")==0 ){
      /* Used for debugging.  Stop reading the test script and shut down.
      ** A "halt" record can be inserted in the middle of a test script in
      ** to run the script up to a particular point that is giving a
      ** faulty result, then terminate at that point for analysis.
      */
      fprintf(stderr, "%s:%d: halt\n", zScriptFile, sScript.startLine);
      break;
    }else{
      /* An unrecognized record type is an error */
      fprintf(stderr, "%s:%d: unknown record type: '%s'\n",
              zScriptFile, sScript.startLine, sScript.azToken[0]);
      nErr++;
      break;
    }
  }


  /* Shutdown the database connection.
  */
  rc = pEngine->xDisconnect(pConn);

};

/*
** Each database engine interface invokes the following routine
** to register itself with the main sqllogictest driver.
*/
void sqllogictestRegisterEngine(const DbEngine*);


/*
** MD5 hashing routines.
*/
void md5_add(const char *z);
const char *md5_finish(void);