SQLite

} {
  SELECT * FROM t1 ORDER BY "index";
} {
  CREATE INDEX t1_idx_01a7214e ON t1('index');
  SCAN t1 USING COVERING INDEX t1_idx_01a7214e
}

ifcapable fts5 {
  do_setup_rec_test $tn.20.0 {
    CREATE VIRTUAL TABLE ft USING fts5(a);
    CREATE TABLE t1(x, y);
  } {
    SELECT * FROM ft, t1 WHERE a=x
  } {
    CREATE INDEX t1_idx_00000078 ON t1(x);
    SCAN ft VIRTUAL TABLE INDEX 0:
    SEARCH t1 USING INDEX t1_idx_00000078 (x=?)
  }
}

}

proc do_candidates_test {tn sql res} {
  set res [squish [string trim $res]]

  set expert [sqlite3_expert_new db]
  $expert sql $sql

do_candidates_test 6.1 {
  SELECT * FROM x1 WHERE b=? ORDER BY a;
} {
  CREATE INDEX x1_idx_0001267f ON x1(b, a); 
  CREATE INDEX x1_idx_00000062 ON x1(b);
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 7.0 {
  CREATE VIRTUAL TABLE ft USING fts5(a);
  CREATE TABLE t1(x, y);
}

do_candidates_test 7.1 {
  SELECT * FROM ft, t1 WHERE a=x
} {
  CREATE INDEX t1_idx_00000078 ON t1(x);
}

register_tcl_module db
proc vtab_command {method args} {
  global G

  switch -- $method {
    xConnect {
      return "CREATE TABLE t1(a, b, c);"
    }

    xBestIndex {
      return [list]
    }

    xFilter {
      return [list sql "SELECT rowid, * FROM t0"]
    }
  }

  return {}
}

do_execsql_test 7.2 {
  CREATE TABLE t0(a, b, c);
  INSERT INTO t0 VALUES(1, 2, 3), (11, 22, 33);
  CREATE VIRTUAL TABLE t2 USING tcl(vtab_command);
}

do_execsql_test 7.3 {
  SELECT * FROM t2
} {
  1 2 3
  11 22 33
}

do_candidates_test 7.4 {
  SELECT * FROM ft, t1 WHERE a=x
} {
  CREATE INDEX t1_idx_00000078 ON t1(x);
}

do_test 7.5 {
  set expert [sqlite3_expert_new db]
  list [catch { $expert sql "SELECT * FROM ft, t2 WHERE b=1" } msg] $msg
} {1 {no such table: t2}}
$expert destroy

finish_test

    pEnd = pFirst;
    pFirst = p->pWrite;
  }

  return rc;
}

/*
** This function tests if the schema of the main database of database handle
** db contains an object named zTab. Assuming no error occurs, output parameter
** (*pbContains) is set to true if zTab exists, or false if it does not.
**
** Or, if an error occurs, an SQLite error code is returned. The final value
** of (*pbContains) is undefined in this case.
*/
static int expertDbContainsObject(
  sqlite3 *db, 
  const char *zTab, 
  int *pbContains                 /* OUT: True if object exists */
){
  const char *zSql = "SELECT 1 FROM sqlite_schema WHERE name = ?";
  sqlite3_stmt *pSql = 0;
  int rc = SQLITE_OK;
  int ret = 0;

  rc = sqlite3_prepare_v2(db, zSql, -1, &pSql, 0);
  if( rc==SQLITE_OK ){
    sqlite3_bind_text(pSql, 1, zTab, -1, SQLITE_STATIC);
    if( SQLITE_ROW==sqlite3_step(pSql) ){
      ret = 1;
    }
    rc = sqlite3_finalize(pSql);
  }

  *pbContains = ret;
  return rc;
}

/*
** Execute SQL command zSql using database handle db. If no error occurs,
** set (*pzErr) to NULL and return SQLITE_OK. 
**
** If an error does occur, return an SQLite error code and set (*pzErr) to
** point to a buffer containing an English language error message. Except,
** if the error message begins with "no such module:", then ignore the
** error and return as if the SQL statement had succeeded.
**
** This is used to copy as much of the database schema as possible while 
** ignoring any errors related to missing virtual table modules.
*/
static int expertSchemaSql(sqlite3 *db, const char *zSql, char **pzErr){
  int rc = SQLITE_OK;
  char *zErr = 0;

  rc = sqlite3_exec(db, zSql, 0, 0, &zErr);
  if( rc!=SQLITE_OK && zErr ){
    int nErr = STRLEN(zErr);
    if( nErr>=15 && memcmp(zErr, "no such module:", 15)==0 ){
      sqlite3_free(zErr);
      rc = SQLITE_OK;
      zErr = 0;
    }
  }

  *pzErr = zErr;
  return rc;
}

static int idxCreateVtabSchema(sqlite3expert *p, char **pzErrmsg){
  int rc = idxRegisterVtab(p);
  sqlite3_stmt *pSchema = 0;

  /* For each table in the main db schema:
  **
  **   1) Add an entry to the p->pTable list, and
  **   2) Create the equivalent virtual table in dbv.
  */
  rc = idxPrepareStmt(p->db, &pSchema, pzErrmsg,
      "SELECT type, name, sql, 1, sql LIKE 'create virtual%' "
      "FROM sqlite_schema "
      "WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%%' "
      " UNION ALL "
      "SELECT type, name, sql, 2, 0 FROM sqlite_schema "
      "WHERE type = 'trigger'"
      "  AND tbl_name IN(SELECT name FROM sqlite_schema WHERE type = 'view') "
      "ORDER BY 4, 5 DESC, 1"
  );
  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSchema) ){
    const char *zType = (const char*)sqlite3_column_text(pSchema, 0);
    const char *zName = (const char*)sqlite3_column_text(pSchema, 1);
    const char *zSql = (const char*)sqlite3_column_text(pSchema, 2);
    int bVirtual = sqlite3_column_int(pSchema, 4);
    int bExists = 0;

    if( zType==0 || zName==0 ) continue;
    rc = expertDbContainsObject(p->dbv, zName, &bExists);
    if( rc || bExists ) continue;

    if( zType[0]=='v' || zType[1]=='r' || bVirtual ){
      /* A view. Or a trigger on a view. */
      if( zSql ) rc = expertSchemaSql(p->dbv, zSql, pzErrmsg);
    }else{
      IdxTable *pTab;
      rc = idxGetTableInfo(p->db, zName, &pTab, pzErrmsg);
      if( rc==SQLITE_OK ){
        int i;
        char *zInner = 0;
        char *zOuter = 0;

  }
#endif

  /* Copy the entire schema of database [db] into [dbm]. */
  if( rc==SQLITE_OK ){
    sqlite3_stmt *pSql = 0;
    rc = idxPrintfPrepareStmt(pNew->db, &pSql, pzErrmsg, 
        "SELECT sql, name "
        " FROM sqlite_schema WHERE name NOT LIKE 'sqlite_%%'"
        " ORDER BY rowid"
    );
    while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSql) ){
      const char *zSql = (const char*)sqlite3_column_text(pSql, 0);
      const char *zName = (const char*)sqlite3_column_text(pSql, 1);
      int bExists = 0;
      rc = expertDbContainsObject(pNew->dbm, zName, &bExists);
      if( rc==SQLITE_OK && zSql && bExists==0 ){
        rc = expertSchemaSql(pNew->dbm, zSql, pzErrmsg);
      }
    }
    idxFinalize(&rc, pSql);
  }

  /* Create the vtab schema */
  if( rc==SQLITE_OK ){
    rc = idxCreateVtabSchema(pNew, pzErrmsg);