SQLite

    zOp = "eq";
  }else if( pTerm->eOperator & (WO_LT|WO_LE|WO_GE|WO_GT) ){
    zOp = "range";
  }
  pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);

  if( zOp ){
    const char *zFmt = bOr ? "%z{{%s %s %s %lld}}" : "%z{%s %s %s %lld}";
    const char *zFmt = bOr ? "%z{{%s \"%w\" \"%w\" %lld}}" :
                             "%z{%s \"%w\" \"%w\" %lld}";
    zBuf = whereAppendPrintf(db, zFmt, zIn, 
        zOp, pTab->aCol[pTerm->u.leftColumn].zName, 
        (pColl ? pColl->zName : "BINARY"),
        pTerm->prereqRight
    );
  }else{
    zBuf = zIn;

      struct SrcList_item *pItem = &pWInfo->pTabList->a[ii];
      if( pItem->pSelect ) continue;
      pTab = pItem->pTab;
      nCol = pTab->nCol;

      /* Append the table name to the buffer. */
      zBuf = whereAppendPrintf(db, "%s", pTab->zName);
      zBuf = whereAppendPrintf(db, "\"%w\"", pTab->zName);

      /* Append the list of columns required to create a covering index */
      zBuf = whereAppendPrintf(db, "%z {cols", zBuf);
      if( 0==(pItem->colUsed & ((u64)1 << (sizeof(Bitmask)*8-1))) ){
        for(iCol=0; iCol<nCol; iCol++){
          if( iCol==(sizeof(Bitmask)*8-1) ) break;
          if( pItem->colUsed & ((u64)1 << iCol) ){
            const char *zName = pTab->aCol[iCol].zName;
            zBuf = whereAppendPrintf(db, "%z %s", zBuf, pTab->aCol[iCol].zName);
            zBuf = whereAppendPrintf(db, "%z \"%w\"", zBuf, zName);
          }
        }
      }
      zBuf = whereAppendPrintf(db, "%z}",zBuf);

      /* Append the contents of WHERE clause */
      zBuf = whereTraceWC(pParse, 1, pItem, zBuf, p->pWC);

      /* Append the ORDER BY clause, if any */
      if( pOrderBy ){
        int i;
        int bFirst = 1;
        for(i=0; i<pOrderBy->nExpr; i++){
          Expr *pExpr = pOrderBy->a[i].pExpr; 
          CollSeq *pColl = sqlite3ExprCollSeq(pParse, pExpr);

          pExpr = sqlite3ExprSkipCollate(pExpr);
          if( pExpr->op==TK_COLUMN && pExpr->iTable==pItem->iCursor ){
            if( pExpr->iColumn>=0 ){
              const char *zName = pTab->aCol[pExpr->iColumn].zName;
              zBuf = whereAppendPrintf(db, "%z%s%s %s %s", zBuf,
              zBuf = whereAppendPrintf(db, "%z%s\"%w\" \"%w\" %s", zBuf,
                  bFirst ? " {orderby " : " ", zName, pColl->zName,
                  (pOrderBy->a[i].sortOrder ? "DESC" : "ASC")
              );
              bFirst = 0;
            }
          }
        }

  CREATE TABLE t1(a, b, c);
  CREATE TABLE t2(x, y, z);
}

do_trace_test 1.1 {
  SELECT b, c, y, z FROM t1, t2 WHERE c=? AND z=?
} {
  {t1 {cols b c} {eq c BINARY 0}}
  {t2 {cols y z} {eq z BINARY 0}}
  {"t1" {cols "b" "c"} {eq "c" "BINARY" 0}}
  {"t2" {cols "y" "z"} {eq "z" "BINARY" 0}}
}

do_trace_test 1.2 {
  SELECT a FROM t1 WHERE b>10
} {
  {t1 {cols a b} {range b BINARY 0}}
  {"t1" {cols "a" "b"} {range "b" "BINARY" 0}}
}

do_trace_test 1.3 {
  SELECT b FROM t1 WHERE b IN (10, 20, 30)
} {
  {t1 {cols b} {eq b BINARY 0}}
  {"t1" {cols "b"} {eq "b" "BINARY" 0}}
}

do_trace_test 1.4 {
  SELECT * FROM t1, t2 WHERE x=a
} {
  {t1 {cols a b c} {eq a BINARY 2}} 
  {t2 {cols x y z} {eq x BINARY 1}}
  {"t1" {cols "a" "b" "c"} {eq "a" "BINARY" 2}} 
  {"t2" {cols "x" "y" "z"} {eq "x" "BINARY" 1}}
}

do_trace_test 1.5 {
  SELECT * FROM t1 WHERE a IN (1, 2, 3)
} {
  {t1 {cols a b c} {eq a BINARY 0}}
  {"t1" {cols "a" "b" "c"} {eq "a" "BINARY" 0}}
}

#-----------------------------------------------------------------------
# Cases involving OR clauses in the WHERE clause.
#
do_trace_test 2.1 {
  SELECT * FROM t1 WHERE a=? OR b=?
} {
  {t1 {cols a b c} {or {{eq a BINARY 0}} {{eq b BINARY 0}}}}
  {"t1" {cols "a" "b" "c"} {or {{eq "a" "BINARY" 0}} {{eq "b" "BINARY" 0}}}}
}

do_trace_test 2.2 {
  SELECT * FROM t1 WHERE a=? OR (b=? AND c=?)
} {
  {t1 {cols a b c} {or {{eq a BINARY 0}} {{eq b BINARY 0} {eq c BINARY 0}}}}
  {"t1" {cols "a" "b" "c"} {or {{eq "a" "BINARY" 0}} {{eq "b" "BINARY" 0} {eq "c" "BINARY" 0}}}}
}

do_trace_test 2.3 {
  SELECT * FROM t1 WHERE (a=? AND b=?) OR c=?
} {
  {t1 {cols a b c} {or {{eq c BINARY 0}} {{eq a BINARY 0} {eq b BINARY 0}}}}
  {"t1" {cols "a" "b" "c"} {or {{eq "c" "BINARY" 0}} {{eq "a" "BINARY" 0} {eq "b" "BINARY" 0}}}}
}

#-----------------------------------------------------------------------
# Cases involving ORDER BY.
#
do_trace_test 3.1 {
  SELECT * FROM t1 ORDER BY a;
} {{t1 {cols a b c} {orderby a BINARY ASC}}}
} {{"t1" {cols "a" "b" "c"} {orderby "a" "BINARY" ASC}}}

do_trace_test 3.2 {
  SELECT * FROM t1 WHERE a=? ORDER BY b;
} {{t1 {cols a b c} {eq a BINARY 0} {orderby b BINARY ASC}}}
} {{"t1" {cols "a" "b" "c"} {eq "a" "BINARY" 0} {orderby "b" "BINARY" ASC}}}

do_trace_test 3.3 {
  SELECT min(a) FROM t1;
} {{t1 {cols a} {orderby a BINARY ASC}}}
} {{"t1" {cols "a"} {orderby "a" "BINARY" ASC}}}

do_trace_test 3.4 {
  SELECT max(a) FROM t1;
} {{t1 {cols a} {orderby a BINARY DESC}}}
} {{"t1" {cols "a"} {orderby "a" "BINARY" DESC}}}

finish_test

set ::VERBOSE 0

proc usage {} {
  puts stderr "Usage: $::argv0 ?SWITCHES? DATABASE/SCHEMA"
  puts stderr "  Switches are:"
  puts stderr "  -select SQL     (recommend indexes for SQL statement)"
  puts stderr "  -verbose        (increase verbosity of output)"
  puts stderr "  -test           (run internal tests and then exit)"
  puts stderr ""
  exit
}

# Return the quoted version of identfier $id. Quotes are only added if 
# they are required by SQLite.
#
# This command currently assumes that quotes are required if the 
# identifier contains any ASCII-range characters that are not 
# alpha-numeric or underscores.
#
proc quote {id} {
  if {[requires_quote $id]} {
    set x [string map {\" \"\"} $id]
    return "\"$x\""
  }
  return $id
}
proc requires_quote {id} {
  foreach c [split $id {}] {
    if {[string is alnum $c]==0 && $c!="_"} {
      return 1
    }
  }
  return 0
}

# The argument passed to this command is a Tcl list of identifiers. The
# value returned is the same list, except with each item quoted and the
# elements comma-separated.
#
proc list_to_sql {L} {
  set ret [list]
  foreach l $L {
    lappend ret [quote $l]
  }
  join $ret ", "
}

proc process_cmdline_args {ctxvar argv} {
  upvar $ctxvar G
  set nArg [llength $argv]
  set G(database) [lindex $argv end]

  for {set i 0} {$i < [llength $argv]-1} {incr i} {

  set rangeset [concat $rangeset $range]

  set lCols [list]
  set idxname $tname

  foreach {c collate dir} $rangeset {
    append idxname "_$c"
    set coldef $c
    set coldef [quote $c]

    if {[string compare -nocase $collate $aColl($c)]!=0} {
      append idxname [string tolower $collate]
      append coldef " COLLATE $collate"
      append coldef " COLLATE [quote $collate]"
    }

    if {$dir=="DESC"} {
      append coldef " DESC"
      append idxname "desc"
    }
    lappend lCols $coldef
  }

  set create_index "CREATE INDEX $idxname ON ${tname}("
  set create_index "CREATE INDEX [quote $idxname] ON [quote $tname]("
  append create_index [join $lCols ", "]
  append create_index ");"

  set G(trial.$idxname) $create_index
}

proc expand_or_cons {L} {

      }
      set lRet $lNew
    } 
  }
  return $lRet
}

#--------------------------------------------------------------------------
# Argument $tname is the name of a table in the main database opened by
# database handle [db]. $arrayvar is the name of an array variable in the
# caller's context. This command populates the array with an entry mapping 
# from column name to default collation sequence for each column of table
# $tname. For example, if a table is declared:
#
#   CREATE TABLE t1(a COLLATE nocase, b, c COLLATE binary)
#
# the mapping is populated with:
#
#   map(a) -> "nocase"
#   map(b) -> "binary"
#   map(c) -> "binary"
#
proc sqlidx_get_coll_map {tname arrayvar} {
  upvar $arrayvar aColl
  set colnames [list]
  set qname [quote $tname]
  db eval "PRAGMA table_info = $tname" x { lappend colnames $x(name) }
  db eval "CREATE INDEX schemalint_test ON ${tname}([join $colnames ,])"
  db eval "PRAGMA table_info = $qname" x { lappend colnames $x(name) }
  db eval "CREATE INDEX schemalint_test ON ${qname}([list_to_sql $colnames])"

  db eval "PRAGMA index_xinfo = schemalint_test" x { 
    set aColl($x(name)) $x(coll)
  }
  db eval "DROP INDEX schemalint_test"
}

proc find_trial_indexes {ctxvar} {

# The following is test code only.
#
proc sqlidx_one_test {tn schema select expected} {
#  if {$tn!=2} return
  sqlidx_init_context C

  sqlite3 db ""
  db collate "a b c" [list string compare]
  db eval $schema
  lappend C(lSelect) $select
  analyze_selects C
  find_trial_indexes C

  set idxlist [run_trials C]
  if {$idxlist != [list {*}$expected]} {
    puts stderr "Test $tn failed"
    puts stderr "Expected: $expected"
    puts stderr "Got: $idxlist"
    exit -1
  }

  db close

  upvar nTest nTest
  incr nTest
}

proc sqlidx_internal_tests {} {
  set nTest 0


  # No indexes for a query with no constraints.
  sqlidx_one_test 0 {
    CREATE TABLE t1(a, b, c);
  } {
    SELECT * FROM t1;
  } {

    CREATE TABLE t1(a COLLATE NOCase, b, c);
  } {
    SELECT * FROM t1 WHERE a=?
  } {
    {CREATE INDEX t1_a ON t1(a);}
  }

  # Tables with names that require quotes.
  #
  sqlidx_one_test 8.1 {
    CREATE TABLE "t t"(a, b, c);
  } {
    SELECT * FROM "t t" WHERE a=?
  } {
    {CREATE INDEX "t t_a" ON "t t"(a);}
  }
  sqlidx_one_test 8.2 {
    CREATE TABLE "t t"(a, b, c);
  } {
    SELECT * FROM "t t" WHERE b BETWEEN ? AND ?
  } {
    {CREATE INDEX "t t_b" ON "t t"(b);}
  }
  
  # Columns with names that require quotes.
  #
  sqlidx_one_test 9.1 {
    CREATE TABLE t3(a, "b b", c);
  } {
    SELECT * FROM t3 WHERE "b b" = ?
  } {
    {CREATE INDEX "t3_b b" ON t3("b b");}
  }
  sqlidx_one_test 9.2 {
    CREATE TABLE t3(a, "b b", c);
  } {
    SELECT * FROM t3 ORDER BY "b b"
  } {
    {CREATE INDEX "t3_b b" ON t3("b b");}
  }

  # Collations with names that require quotes.
  #
  sqlidx_one_test 10.1 {
    CREATE TABLE t4(a, b, c);
  } {
    SELECT * FROM t4 ORDER BY c COLLATE "a b c"
  } {
    {CREATE INDEX "t4_ca b c" ON t4(c COLLATE "a b c");}
  }
  sqlidx_one_test 10.2 {
    CREATE TABLE t4(a, b, c);
  } {
    SELECT * FROM t4 WHERE c = ? COLLATE "a b c"
  } {
    {CREATE INDEX "t4_ca b c" ON t4(c COLLATE "a b c");}
  }

  puts "All $nTest tests passed"
  exit
}
# End of internal test code.
#-------------------------------------------------------------------------

sqlidx_init_context D
process_cmdline_args D $argv
open_database D
analyze_selects D
find_trial_indexes D
foreach idx [run_trials D] { puts $idx }