SQLite

# 2014 June 17
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the FTS5 module.
#

source [file join [file dirname [info script]] fts5_common.tcl]
set testprefix fts5leftjoin

# If SQLITE_ENABLE_FTS5 is not defined, omit this file.
ifcapable !fts5 {
  finish_test
  return
}

do_execsql_test 1.0 {
  CREATE VIRTUAL TABLE vt USING fts5(x);
  INSERT INTO vt VALUES('abc');
  INSERT INTO vt VALUES('xyz');

  CREATE TABLE t1(a INTEGER PRIMARY KEY);
  INSERT INTO t1 VALUES(1), (2);
}

do_execsql_test 1.1 {
  SELECT * FROM t1 LEFT JOIN (
    SELECT rowid AS rrr, * FROM vt WHERE vt MATCH 'abc'
  ) ON t1.a = rrr
} {1 1 abc 2 {} {}}

do_execsql_test 1.2 {
  SELECT * FROM t1 LEFT JOIN vt ON (vt MATCH 'abc')
} {1 abc 2 abc}

finish_test

typedef struct CsvReader CsvReader;
struct CsvReader {
  FILE *in;              /* Read the CSV text from this input stream */
  char *z;               /* Accumulated text for a field */
  int n;                 /* Number of bytes in z */
  int nAlloc;            /* Space allocated for z[] */
  int nLine;             /* Current line number */
  int bNotFirst;         /* True if prior text has been seen */
  char cTerm;            /* Character that terminated the most recent field */
  size_t iIn;            /* Next unread character in the input buffer */
  size_t nIn;            /* Number of characters in the input buffer */
  char *zIn;             /* The input buffer */
  char zErr[CSV_MXERR];  /* Error message */
};

/* Initialize a CsvReader object */
static void csv_reader_init(CsvReader *p){
  p->in = 0;
  p->z = 0;
  p->n = 0;
  p->nAlloc = 0;
  p->nLine = 0;
  p->bNotFirst = 0;
  p->nIn = 0;
  p->zIn = 0;
  p->zErr[0] = 0;
}

/* Close and reset a CsvReader object */
static void csv_reader_reset(CsvReader *p){

        }
      }
      if( csv_append(p, (char)c) ) return 0;
      ppc = pc;
      pc = c;
    }
  }else{
    /* If this is the first field being parsed and it begins with the
    ** UTF-8 BOM  (0xEF BB BF) then skip the BOM */
    if( (c&0xff)==0xef && p->bNotFirst==0 ){
      csv_append(p, c);
      c = csv_getc(p);
      if( (c&0xff)==0xbb ){
        csv_append(p, c);
        c = csv_getc(p);
        if( (c&0xff)==0xbf ){
          p->bNotFirst = 1;
          p->n = 0;
          return csv_read_one_field(p);
        }
      }
    }
    while( c>',' || (c!=EOF && c!=',' && c!='\n') ){
      if( csv_append(p, (char)c) ) return 0;
      c = csv_getc(p);
    }
    if( c=='\n' ){
      p->nLine++;
      if( p->n>0 && p->z[p->n-1]=='\r' ) p->n--;
    }
    p->cTerm = (char)c;
  }
  if( p->z ) p->z[p->n] = 0;
  p->bNotFirst = 1;
  return p->z;
}


/* Forward references to the various virtual table methods implemented
** in this file. */
static int csvtabCreate(sqlite3*, void*, int, const char*const*, 

**
** This file implements routines used to report what compile-time options
** SQLite was built with.
*/

#ifndef SQLITE_OMIT_COMPILEOPTION_DIAGS

/*
** Include the configuration header output by 'configure' if we're using the
** autoconf-based build
*/
#if defined(_HAVE_SQLITE_CONFIG_H) && !defined(SQLITECONFIG_H)
#include "config.h"
#define SQLITECONFIG_H 1
#endif

/* These macros are provided to "stringify" the value of the define
** for those options in which the value is meaningful. */
#define CTIMEOPT_VAL_(opt) #opt
#define CTIMEOPT_VAL(opt) CTIMEOPT_VAL_(opt)

/*

  if( p->zDestTable ){
    free(p->zDestTable);
    p->zDestTable = 0;
  }
  if( zName==0 ) return;
  cQuote = quoteChar(zName);
  n = strlen30(zName);
  if( cQuote ) n += n+2;
  z = p->zDestTable = malloc( n+1 );
  if( z==0 ){
    raw_printf(stderr,"Error: out of memory\n");
    exit(1);
  }
  n = 0;
  if( cQuote ) z[n++] = cQuote;

struct ImportCtx {
  const char *zFile;  /* Name of the input file */
  FILE *in;           /* Read the CSV text from this input stream */
  char *z;            /* Accumulated text for a field */
  int n;              /* Number of bytes in z */
  int nAlloc;         /* Space allocated for z[] */
  int nLine;          /* Current line number */
  int bNotFirst;      /* True if one or more bytes already read */
  int cTerm;          /* Character that terminated the most recent field */
  int cColSep;        /* The column separator character.  (Usually ",") */
  int cRowSep;        /* The row separator character.  (Usually "\n") */
};

/* Append a single byte to z[] */
static void import_append_char(ImportCtx *p, int c){

        break;
      }
      import_append_char(p, c);
      ppc = pc;
      pc = c;
    }
  }else{
    /* If this is the first field being parsed and it begins with the
    ** UTF-8 BOM  (0xEF BB BF) then skip the BOM */
    if( (c&0xff)==0xef && p->bNotFirst==0 ){
      import_append_char(p, c);
      c = fgetc(p->in);
      if( (c&0xff)==0xbb ){
        import_append_char(p, c);
        c = fgetc(p->in);
        if( (c&0xff)==0xbf ){
          p->bNotFirst = 1;
          p->n = 0;
          return csv_read_one_field(p);
        }
      }
    }
    while( c!=EOF && c!=cSep && c!=rSep ){
      import_append_char(p, c);
      c = fgetc(p->in);
    }
    if( c==rSep ){
      p->nLine++;
      if( p->n>0 && p->z[p->n-1]=='\r' ) p->n--;
    }
    p->cTerm = c;
  }
  if( p->z ) p->z[p->n] = 0;
  p->bNotFirst = 1;
  return p->z;
}

/* Read a single field of ASCII delimited text.
**
**   +  Input comes from p->in.
**   +  Store results in p->z of length p->n.  Space to hold p->z comes

*/
#include "sqlite3.h"

/*
** Include the configuration header output by 'configure' if we're using the
** autoconf-based build
*/
#if defined(_HAVE_SQLITE_CONFIG_H) && !defined(SQLITECONFIG_H)
#include "config.h"
#define SQLITECONFIG_H 1
#endif

#include "sqliteLimit.h"

/* Disable nuisance warnings on Borland compilers */
#if defined(__BORLANDC__)
#pragma warn -rch /* unreachable code */

typedef struct DbEvalContext DbEvalContext;
struct DbEvalContext {
  SqliteDb *pDb;                  /* Database handle */
  Tcl_Obj *pSql;                  /* Object holding string zSql */
  const char *zSql;               /* Remaining SQL to execute */
  SqlPreparedStmt *pPreStmt;      /* Current statement */
  int nCol;                       /* Number of columns returned by pStmt */
  int evalFlags;                  /* Flags used */
  Tcl_Obj *pArray;                /* Name of array variable */
  Tcl_Obj **apColName;            /* Array of column names */
};

#define SQLITE_EVAL_WITHOUTNULLS  0x00001  /* Unset array(*) for NULL */

/*
** Release any cache of column names currently held as part of
** the DbEvalContext structure passed as the first argument.
*/
static void dbReleaseColumnNames(DbEvalContext *p){
  if( p->apColName ){

**
**     set ${pArray}(*) {a b c}
*/
static void dbEvalInit(
  DbEvalContext *p,               /* Pointer to structure to initialize */
  SqliteDb *pDb,                  /* Database handle */
  Tcl_Obj *pSql,                  /* Object containing SQL script */
  Tcl_Obj *pArray,                /* Name of Tcl array to set (*) element of */
  int evalFlags                   /* Flags controlling evaluation */
){
  memset(p, 0, sizeof(DbEvalContext));
  p->pDb = pDb;
  p->zSql = Tcl_GetString(pSql);
  p->pSql = pSql;
  Tcl_IncrRefCount(pSql);
  if( pArray ){
    p->pArray = pArray;
    Tcl_IncrRefCount(pArray);
  }
  p->evalFlags = evalFlags;
}

/*
** Obtain information about the row that the DbEvalContext passed as the
** first argument currently points to.
*/
static void dbEvalRowInfo(

  while( (rc==TCL_OK || rc==TCL_CONTINUE) && TCL_OK==(rc = dbEvalStep(p)) ){
    int i;
    int nCol;
    Tcl_Obj **apColName;
    dbEvalRowInfo(p, &nCol, &apColName);
    for(i=0; i<nCol; i++){

      if( pArray==0 ){
        Tcl_ObjSetVar2(interp, apColName[i], 0, dbEvalColumnValue(p,i), 0);
      }else if( (p->evalFlags & SQLITE_EVAL_WITHOUTNULLS)!=0
             && sqlite3_column_type(p->pPreStmt->pStmt, i)==SQLITE_NULL 
      ){
        Tcl_UnsetVar2(interp, Tcl_GetString(pArray), 
                      Tcl_GetString(apColName[i]), 0);
      }else{
        Tcl_ObjSetVar2(interp, pArray, apColName[i], dbEvalColumnValue(p,i), 0);
      }
    }

    /* The required interpreter variables are now populated with the data
    ** from the current row. If using NRE, schedule callbacks to evaluate
    ** script pScript, then to invoke this function again to fetch the next
    ** row (or clean up if there is no next row or the script throws an

    Tcl_Obj *pResult = 0;
    DbEvalContext sEval;
    if( objc!=3 ){
      Tcl_WrongNumArgs(interp, 2, objv, "SQL");
      return TCL_ERROR;
    }

    dbEvalInit(&sEval, pDb, objv[2], 0, 0);
    rc = dbEvalStep(&sEval);
    if( choice==DB_ONECOLUMN ){
      if( rc==TCL_OK ){
        pResult = dbEvalColumnValue(&sEval, 0);
      }else if( rc==TCL_BREAK ){
        Tcl_ResetResult(interp);
      }
    }else if( rc==TCL_BREAK || rc==TCL_OK ){
      pResult = Tcl_NewBooleanObj(rc==TCL_OK);
    }
    dbEvalFinalize(&sEval);
    if( pResult ) Tcl_SetObjResult(interp, pResult);

    if( rc==TCL_BREAK ){
      rc = TCL_OK;
    }
    break;
  }

  /*
  **    $db eval ?options? $sql ?array? ?{  ...code... }?
  **
  ** The SQL statement in $sql is evaluated.  For each row, the values are
  ** placed in elements of the array named "array" and ...code... is executed.
  ** If "array" and "code" are omitted, then no callback is every invoked.
  ** If "array" is an empty string, then the values are placed in variables
  ** that have the same name as the fields extracted by the query.
  */
  case DB_EVAL: {
    int evalFlags = 0;
    const char *zOpt;
    while( objc>3 && (zOpt = Tcl_GetString(objv[2]))!=0 && zOpt[0]=='-' ){
      if( strcmp(zOpt, "-withoutnulls")==0 ){
        evalFlags |= SQLITE_EVAL_WITHOUTNULLS;
      }
      else{
        Tcl_AppendResult(interp, "unknown option: \"", zOpt, "\"", (void*)0);
        return TCL_ERROR;
      }
      objc--;
      objv++;
    }
    if( objc<3 || objc>5 ){
      Tcl_WrongNumArgs(interp, 2, objv, 
          "?OPTIONS? SQL ?ARRAY-NAME? ?SCRIPT?");
      return TCL_ERROR;
    }

    if( objc==3 ){
      DbEvalContext sEval;
      Tcl_Obj *pRet = Tcl_NewObj();
      Tcl_IncrRefCount(pRet);
      dbEvalInit(&sEval, pDb, objv[2], 0, 0);
      while( TCL_OK==(rc = dbEvalStep(&sEval)) ){
        int i;
        int nCol;
        dbEvalRowInfo(&sEval, &nCol, 0);
        for(i=0; i<nCol; i++){
          Tcl_ListObjAppendElement(interp, pRet, dbEvalColumnValue(&sEval, i));
        }
      }
      dbEvalFinalize(&sEval);
      if( rc==TCL_BREAK ){
        Tcl_SetObjResult(interp, pRet);
        rc = TCL_OK;
      }
      Tcl_DecrRefCount(pRet);
    }else{
      ClientData cd2[2];
      DbEvalContext *p;
      Tcl_Obj *pArray = 0;
      Tcl_Obj *pScript;

      if( objc>=5 && *(char *)Tcl_GetString(objv[3]) ){
        pArray = objv[3];
      }
      pScript = objv[objc-1];
      Tcl_IncrRefCount(pScript);

      p = (DbEvalContext *)Tcl_Alloc(sizeof(DbEvalContext));
      dbEvalInit(p, pDb, objv[2], pArray, evalFlags);

      cd2[0] = (void *)p;
      cd2[1] = (void *)pScript;
      rc = DbEvalNextCmd(cd2, interp, TCL_OK);
    }
    break;
  }

          WHERETRACE(0x002,
              ("---- sort cost=%-3d (%d/%d) increases cost %3d to %-3d\n",
               aSortCost[isOrdered], (nOrderBy-isOrdered), nOrderBy, 
               rUnsorted, rCost));
        }else{
          rCost = rUnsorted;
          rUnsorted -= 2;  /* TUNING:  Slight bias in favor of no-sort plans */
        }

        /* Check to see if pWLoop should be added to the set of
        ** mxChoice best-so-far paths.
        **
        ** First look for an existing path among best-so-far paths
        ** that covers the same set of loops and has the same isOrdered

    pLeft = pExpr->x.pList->a[1].pExpr;
    prereqExpr = sqlite3WhereExprUsage(pMaskSet, pRight);
    prereqColumn = sqlite3WhereExprUsage(pMaskSet, pLeft);
    if( (prereqExpr & prereqColumn)==0 ){
      Expr *pNewExpr;
      pNewExpr = sqlite3PExpr(pParse, TK_MATCH, 
                              0, sqlite3ExprDup(db, pRight, 0));
      if( ExprHasProperty(pExpr, EP_FromJoin) && pNewExpr ){
        ExprSetProperty(pNewExpr, EP_FromJoin);
      }
      idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC);
      testcase( idxNew==0 );
      pNewTerm = &pWC->a[idxNew];
      pNewTerm->prereqRight = prereqExpr;
      pNewTerm->leftCursor = pLeft->iTable;
      pNewTerm->u.leftColumn = pLeft->iColumn;
      pNewTerm->eOperator = WO_MATCH;

do_execsql_test 2.2 { SELECT * FROM ft2, ft3 WHERE y MATCH x; } {abc abc}
do_execsql_test 2.3 { SELECT * FROM ft3, ft2 WHERE x MATCH y; } {abc abc}
do_execsql_test 2.4 { SELECT * FROM ft3, ft2 WHERE y MATCH x; } {abc abc}

do_catchsql_test 2.5 { 
  SELECT * FROM ft3, ft2 WHERE y MATCH x AND x MATCH y; 
} {1 {unable to use function MATCH in the requested context}}

do_execsql_test 3.0 {
  CREATE VIRTUAL TABLE vt USING fts3(x);
  INSERT INTO vt VALUES('abc');
  INSERT INTO vt VALUES('xyz');

  CREATE TABLE tt(a INTEGER PRIMARY KEY);
  INSERT INTO tt VALUES(1), (2);
}

do_execsql_test 3.1 {
  SELECT * FROM tt LEFT JOIN (
    SELECT rowid AS rrr, * FROM vt WHERE vt MATCH 'abc'
  ) ON tt.a = rrr
} {1 1 abc 2 {} {}}

do_execsql_test 3.2 {
  SELECT * FROM tt LEFT JOIN vt ON (vt MATCH 'abc')
} {1 abc 2 abc}


finish_test

  catchcmd "test.db" ".restore FOO BAR"
} {1 {Error: unknown database FOO}}
do_test shell1-3.20.4 {
  # too many arguments
  catchcmd "test.db" ".restore FOO BAR BAD"
} {1 {Usage: .restore ?DB? FILE}}

ifcapable vtab {
# .schema ?TABLE?        Show the CREATE statements
#                          If TABLE specified, only show tables matching
#                          LIKE pattern TABLE.
do_test shell1-3.21.1 {
  catchcmd "test.db" ".schema"
} {0 {}}
do_test shell1-3.21.2 {

     CREATE VIEW v1 AS SELECT y+1 FROM v2;
  }
  catchcmd "test.db" ".schema"
} {0 {CREATE TABLE t1(x);
CREATE VIEW v2 AS SELECT x+1 AS y FROM t1;
CREATE VIEW v1 AS SELECT y+1 FROM v2;}}
db eval {DROP VIEW v1; DROP VIEW v2; DROP TABLE t1;}
}

# .separator STRING  Change column separator used by output and .import
do_test shell1-3.22.1 {
  catchcmd "test.db" ".separator"
} {1 {Usage: .separator COL ?ROW?}}
do_test shell1-3.22.2 {
  catchcmd "test.db" ".separator FOO"

  set res [catchcmd "test.db" {.import shell5.csv t1
SELECT COUNT(*) FROM t1;}]
} {0 7}

do_test shell5-1.4.10.2 {
  catchcmd "test.db" {SELECT b FROM t1 WHERE a='7';}
} {0 {Now is the time for all good men to come to the aid of their country.}}

# import file with 2 rows, 2 columns and an initial BOM
#
do_test shell5-1.4.11 {
  set in [open shell5.csv wb]
  puts $in "\xef\xbb\xbf2|3"
  puts $in "4|5"
  close $in
  set res [catchcmd "test.db" {CREATE TABLE t2(x INT, y INT);
.import shell5.csv t2
.mode quote
.header on
SELECT * FROM t2;}]
 string map {\n | \n\r |} $res
} {0 {'x','y'|2,3|4,5}}

# import file with 2 rows, 2 columns or text with an initial BOM
#
do_test shell5-1.4.12 {
  set in [open shell5.csv wb]
  puts $in "\xef\xbb\xbf\"two\"|3"
  puts $in "4|5"
  close $in
  set res [catchcmd "test.db" {DELETE FROM t2;
.import shell5.csv t2
.mode quote
.header on
SELECT * FROM t2;}]
 string map {\n | \n\r |} $res
} {0 {'x','y'|'two',3|4,5}}

# check importing very long field
do_test shell5-1.5.1 {
  set str [string repeat X 999]
  set in [open shell5.csv w]
  puts $in "8|$str"
  close $in

  for {set i 1} {$i<$cols} {incr i} {
    append data "$i|"
  }
  append data "$cols"
  set in [open shell5.csv w]
  puts $in $data
  close $in
  set res [catchcmd "test.db" {DROP TABLE IF EXISTS t2;
.import shell5.csv t2
SELECT COUNT(*) FROM t2;}]
} {0 1}

# try importing a large number of rows
set rows 9999
do_test shell5-1.7.1 {
  set in [open shell5.csv w]

    set v [catch {db complete} msg]
    lappend v $msg
  } {1 {wrong # args: should be "db complete SQL"}}
}
do_test tcl-1.14 {
  set v [catch {db eval} msg]
  lappend v $msg
} {1 {wrong # args: should be "db eval ?OPTIONS? SQL ?ARRAY-NAME? ?SCRIPT?"}}
do_test tcl-1.15 {
  set v [catch {db function} msg]
  lappend v $msg
} {1 {wrong # args: should be "db function NAME ?SWITCHES? SCRIPT"}}
do_test tcl-1.16 {
  set v [catch {db last_insert_rowid xyz} msg]
  lappend v $msg

  db exists {SELECT a FROM t1 WHERE a>2}
} {1}
do_test tcl-15.5 {
  db exists {SELECT a FROM t1 WHERE a>3}
} {0}


# 2017-06-26: The --withoutnulls flag to "db eval".
#
# In the "db eval --withoutnulls SQL ARRAY" form, NULL results cause the
# corresponding array entry to be unset.  The default behavior (without
# the -withoutnulls flags) is for the corresponding array value to get
# the [db nullvalue] string.
#
catch {db close}
forcedelete test.db
sqlite3 db test.db
do_execsql_test tcl-16.100 {
  CREATE TABLE t1(a,b);
  INSERT INTO t1 VALUES(1,2),(2,NULL),(3,'xyz');
}
do_test tcl-16.101 {
  set res {}
  unset -nocomplain x
  db eval {SELECT * FROM t1} x {
    lappend res $x(a) [array names x]
  }
  set res
} {1 {a b *} 2 {a b *} 3 {a b *}}
do_test tcl-16.102 {
  set res [catch {
    db eval -unknown {SELECT * FROM t1} x {
      lappend res $x(a) [array names x]
    }
  } rc]
  lappend res $rc
} {1 {unknown option: "-unknown"}}
do_test tcl-16.103 {
  set res {}
  unset -nocomplain x
  db eval -withoutnulls {SELECT * FROM t1} x {
    lappend res $x(a) [array names x]
  }
  set res
} {1 {a b *} 2 {a *} 3 {a b *}}





finish_test