**     CREATE TABLE t1(k PRIMARY KEY, c0 BLOB, c1 BLOB, v BLOB);
**     CREATE INDEX i0 ON t1(c0);
**     CREATE INDEX i1 ON t1(c1);
**
** where the number of "c" columns (and indexes) is determined by the nIdx
** argument. The example above is as would be returned for nIdx==2.
*/
static char *create_schema_sql(int nIdx){
  char *zSchema;
  int i;

  zSchema = sqlite4_mprintf(0, "CREATE TABLE t1(k PRIMARY KEY,");
  for(i=0; i<nIdx; i++){
    zSchema = sqlite4_mprintf(0, "%z c%d BLOB,", zSchema, i);
  }
  zSchema = sqlite4_mprintf(0, "%z v BLOB);", zSchema);



  for(i=0; i<nIdx; i++){
    zSchema = sqlite4_mprintf(
      0, "%z\nCREATE INDEX i%d ON t1 (c%d);", zSchema, i, i
    );
  }

................................................................................
  sqlite4_kvstore_control(db, "main", SQLITE4_KVCTRL_LSM_HANDLE, &pLsm);
  i = iSync;
  lsm_config(pLsm, LSM_CONFIG_SAFETY, &i);
  assert( i==iSync );

  install_rblob_function4(db);

  zCreateTbl = create_schema_sql(nIdx);
  zInsert = create_insert_sql(nIdx);

  /* Create the db schema and prepare the INSERT statement */
  EXPLODE(  sqlite4_exec(db, zCreateTbl, 0, 0)  );
  EXPLODE(  sqlite4_prepare(db, zInsert, -1, &pInsert, 0)  );

  /* Run the test */
................................................................................
  EXPLODE( sqlite3_exec(db, "PRAGMA journal_mode=WAL", 0, 0, 0) );
  zSync = sqlite4_mprintf(0, "PRAGMA synchronous=%d", iSync);
  EXPLODE( sqlite3_exec(db, zSync, 0, 0, 0) );
  sqlite4_free(0, zSync);

  install_rblob_function3(db);

  zCreateTbl = create_schema_sql(nIdx);
  zInsert = create_insert_sql(nIdx);

  /* Create the db schema and prepare the INSERT statement */
  EXPLODE(  sqlite3_exec(db, zCreateTbl, 0, 0, 0)  );
  EXPLODE(  sqlite3_prepare(db, zInsert, -1, &pInsert, 0)  );

  /* Run the test */

**     CREATE TABLE t1(k PRIMARY KEY, c0 BLOB, c1 BLOB, v BLOB);
**     CREATE INDEX i0 ON t1(c0);
**     CREATE INDEX i1 ON t1(c1);
**
** where the number of "c" columns (and indexes) is determined by the nIdx
** argument. The example above is as would be returned for nIdx==2.
*/
static char *create_schema_sql(int nIdx, int bWithoutRowid){
  char *zSchema;
  int i;

  zSchema = sqlite4_mprintf(0, "CREATE TABLE t1(k PRIMARY KEY,");
  for(i=0; i<nIdx; i++){
    zSchema = sqlite4_mprintf(0, "%z c%d BLOB,", zSchema, i);
  }
  zSchema = sqlite4_mprintf(0, "%z v BLOB)%s;", zSchema,
      (bWithoutRowid ? " WITHOUT ROWID" : "")
  );

  for(i=0; i<nIdx; i++){
    zSchema = sqlite4_mprintf(
      0, "%z\nCREATE INDEX i%d ON t1 (c%d);", zSchema, i, i
    );
  }

................................................................................
  sqlite4_kvstore_control(db, "main", SQLITE4_KVCTRL_LSM_HANDLE, &pLsm);
  i = iSync;
  lsm_config(pLsm, LSM_CONFIG_SAFETY, &i);
  assert( i==iSync );

  install_rblob_function4(db);

  zCreateTbl = create_schema_sql(nIdx, 0);
  zInsert = create_insert_sql(nIdx);

  /* Create the db schema and prepare the INSERT statement */
  EXPLODE(  sqlite4_exec(db, zCreateTbl, 0, 0)  );
  EXPLODE(  sqlite4_prepare(db, zInsert, -1, &pInsert, 0)  );

  /* Run the test */
................................................................................
  EXPLODE( sqlite3_exec(db, "PRAGMA journal_mode=WAL", 0, 0, 0) );
  zSync = sqlite4_mprintf(0, "PRAGMA synchronous=%d", iSync);
  EXPLODE( sqlite3_exec(db, zSync, 0, 0, 0) );
  sqlite4_free(0, zSync);

  install_rblob_function3(db);

  zCreateTbl = create_schema_sql(nIdx, 1);
  zInsert = create_insert_sql(nIdx);

  /* Create the db schema and prepare the INSERT statement */
  EXPLODE(  sqlite3_exec(db, zCreateTbl, 0, 0, 0)  );
  EXPLODE(  sqlite3_prepare(db, zInsert, -1, &pInsert, 0)  );

  /* Run the test */

        pHdr->iSubBlock = 0;
      }
    }
    if( rc==SQLITE4_OK && pCsr->nPg==1 ){
      rc = btExtendTree(pCsr);
    }
    if( rc==SQLITE4_OK ){


      if( bLeaf==0 || SQLITE4_NOTFOUND==(rc = btTryAppend(pCsr, nKV, apKV)) ){
        rc = btBalance(pCsr, bLeaf, nKV, apKV);
      }

    }
  }

  return rc;
}

static int btDeleteFromPage(BtCursor *pCsr, int nDel){

        pHdr->iSubBlock = 0;
      }
    }
    if( rc==SQLITE4_OK && pCsr->nPg==1 ){
      rc = btExtendTree(pCsr);
    }
    if( rc==SQLITE4_OK ){
      rc = btBalance(pCsr, bLeaf, nKV, apKV);
#if 0
      if( bLeaf==0 || SQLITE4_NOTFOUND==(rc = btTryAppend(pCsr, nKV, apKV)) ){
        rc = btBalance(pCsr, bLeaf, nKV, apKV);
      }
#endif
    }
  }

  return rc;
}

static int btDeleteFromPage(BtCursor *pCsr, int nDel){

    pPg = pFS->pLruFirst;
    while( pPg ){
      Page *pNext = pPg->pLruNext;
      if( pPg->flags & PAGE_FREE ) lsmFree(pEnv, pPg->aData);
      lsmFree(pEnv, pPg);
      pPg = pNext;
    }









    if( pFS->fdDb ) lsmEnvClose(pFS->pEnv, pFS->fdDb );
    if( pFS->fdLog ) lsmEnvClose(pFS->pEnv, pFS->fdLog );
    lsmFree(pEnv, pFS->pLsmFile);
    lsmFree(pEnv, pFS->apHash);
    lsmFree(pEnv, pFS->aIBuffer);
    lsmFree(pEnv, pFS->aOBuffer);

    pPg = pFS->pLruFirst;
    while( pPg ){
      Page *pNext = pPg->pLruNext;
      if( pPg->flags & PAGE_FREE ) lsmFree(pEnv, pPg->aData);
      lsmFree(pEnv, pPg);
      pPg = pNext;
    }

    pPg = pFS->pFree;
    while( pPg ){
      Page *pNext = pPg->pFreeNext;
      if( pPg->flags & PAGE_FREE ) lsmFree(pEnv, pPg->aData);
      lsmFree(pEnv, pPg);
      pPg = pNext;
    }

    if( pFS->fdDb ) lsmEnvClose(pFS->pEnv, pFS->fdDb );
    if( pFS->fdLog ) lsmEnvClose(pFS->pEnv, pFS->fdLog );
    lsmFree(pEnv, pFS->pLsmFile);
    lsmFree(pEnv, pFS->apHash);
    lsmFree(pEnv, pFS->aIBuffer);
    lsmFree(pEnv, pFS->aOBuffer);

# 2013 June 12
#
# 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.
#
#***********************************************************************
# 
# Test cases for SELECT statements.


#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix selectF

# Evaluate the SQL query passed as the only argument using database
# connection [db]. Return any results as a list of lists - one list
# element for each row. i.e.
#

#   "SELECT 1, 2 UNION SELECT 3, 4" -> "{1 2} {3 4}"


#
proc eval_to_list {bOrdered sql} {
  set sqlresult [list]
  db eval $sql a {
    set row [list]
    foreach c $a(*) { lappend row $a($c) }
    lappend sqlresult $row

  }
  if {!$bOrdered} {set sqlresult [lsort $sqlresult]}
  set sqlresult
}

# Execute SELECT query $zSql using [eval_to_list] and compare the results
# to $lRes. If argument $bOrdered is false, this indicates that the order
# of results is non-deterministic. In this case the results are passed
# to [lsort] before they are compared to $lRes.





#
proc do_select_test {testname bOrdered zSql lRes} {
  uplevel [list do_test $testname [list eval_to_list $bOrdered $zSql] $lRes]
}

# The SELECT tests in this file all use the following command. See the
# example below for details.
#
proc do_select_test_set {tn data indexes tests} {
  db_delete_and_reopen
  execsql $data
  db_save_and_close

  if {$tn=="*"} {
    foreach {tn2 bOrder sql res} $tests {
      puts "$tn2 $bOrder \"$sql\" {[eval_to_list $bOrder $sql]}"
    }
  } else {
    foreach {tn1 sql} $indexes {
      db_restore_and_reopen
      foreach {tn2 bOrder sql res} $tests {
        do_select_test $tn.$tn1.$tn2 $bOrder $sql $res
      }
    }
  }
}

#--------------------------------------------------------------------
# Warm-body tests. This block serves as an example of how to use
# the [do_select_test_set] command.
#
do_select_test_set 1 {
  CREATE TABLE t1(a, b, c);
  INSERT INTO t1 VALUES(2, 4,  6);
  INSERT INTO t1 VALUES(1, 2,  3);
  INSERT INTO t1 VALUES(4, 8,  12);
  INSERT INTO t1 VALUES(3, 6,  9);
  INSERT INTO t1 VALUES(6, 12, 18);
  INSERT INTO t1 VALUES(5, 10, 15);
} {
  1 { }
  2 { CREATE INDEX ON t1(a, b, c) }
  3 { CREATE INDEX ON t1(c, b, a) }
  4 { 
    CREATE INDEX ON t1(a);
    CREATE INDEX ON t1(b);
    CREATE INDEX ON t1(c);
  }
} {
1 0 "SELECT * FROM t1 WHERE a = 2" {{2 4 6}}
2 0 "SELECT * FROM t1 WHERE a < 4" {{1 2 3} {2 4 6} {3 6 9}}
3 0 "SELECT * FROM t1 WHERE a = b/2 AND c%2" {{1 2 3} {3 6 9} {5 10 15}}
4 0 "SELECT (a/2) FROM t1 GROUP BY (a/2)" {0 1 2 3}
5 0 "SELECT a+b FROM t1 GROUP BY (a+b)" {12 15 18 3 6 9}
6 1 "SELECT a+b FROM t1 GROUP BY (a+b) ORDER BY 1" {3 6 9 12 15 18}
7 0 "SELECT a*b FROM t1 WHERE (c%2)" {18 2 50}
8 0 "SELECT count(*) FROM t1, t1 AS t2 WHERE t1.a=t2.a" {6}

9 0 "SELECT * FROM t1 WHERE a=1 AND b=2" {{1 2 3}}
10 0 "SELECT * FROM t1 WHERE a>3 AND b<12" {{4 8 12} {5 10 15}}



11 1 "SELECT a FROM t1 ORDER BY a" {1 2 3 4 5 6}
12 1 "SELECT a FROM t1 ORDER BY +a" {1 2 3 4 5 6}
13 1 "SELECT a FROM t1 ORDER BY b DESC" {6 5 4 3 2 1}

}

finish_test

# 2014-03-03
#
# 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 verifies that an OP_Copy operation is used instead of OP_SCopy
# in a compound select in a case where the source register might be changed
# before the copy is used.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix selectF

do_execsql_test 1 {
  BEGIN TRANSACTION;
  CREATE TABLE t1(a, b, c);

  INSERT INTO "t1" VALUES(1,'one','I');
  CREATE TABLE t2(d, e, f);
  INSERT INTO "t2" VALUES(5,'ten','XX');
  INSERT INTO "t2" VALUES(6,NULL,NULL);



  CREATE INDEX i1 ON t1(b, a);



  COMMIT;
}








#explain_i {
#  SELECT * FROM t2
#  UNION ALL 
#  SELECT * FROM t1 WHERE a<5 
#  ORDER BY 2, 1
#}









do_execsql_test 2 {






































  SELECT * FROM t2







  UNION ALL 
  SELECT * FROM t1 WHERE a<5 

  ORDER BY 2, 1
} {6 {} {} 1 one I 5 ten XX}





  

finish_test