/ Check-in [7df23aca]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add the experimental ".fkey_missing_indexes" command to the shell tool. To identify indexes that should be created on child keys if FK processing is to be enabled.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | fkey-missing-indexes
Files: files | file ages | folders
SHA1: 7df23aca1f7c7b769d614d740b3fda3073f46ba9
User & Date: dan 2016-12-14 19:28:27
Context
2016-12-15
06:01
Add the -groupbyparent option to the ".fkey_missing_indexes" command. check-in: 976c51b4 user: dan tags: fkey-missing-indexes
2016-12-14
19:28
Add the experimental ".fkey_missing_indexes" command to the shell tool. To identify indexes that should be created on child keys if FK processing is to be enabled. check-in: 7df23aca user: dan tags: fkey-missing-indexes
14:07
Refactor the Table.nRef field as Table.nTabRef for easier grepping. check-in: 9cae4c2e user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/shell.c.

  2181   2181     ".dump ?TABLE? ...      Dump the database in an SQL text format\n"
  2182   2182     "                         If TABLE specified, only dump tables matching\n"
  2183   2183     "                         LIKE pattern TABLE.\n"
  2184   2184     ".echo on|off           Turn command echo on or off\n"
  2185   2185     ".eqp on|off|full       Enable or disable automatic EXPLAIN QUERY PLAN\n"
  2186   2186     ".exit                  Exit this program\n"
  2187   2187     ".explain ?on|off|auto? Turn EXPLAIN output mode on or off or to automatic\n"
         2188  +  ".fkey_missing_indexes  Find indexes to make FK processing more efficient\n"
  2188   2189     ".fullschema ?--indent? Show schema and the content of sqlite_stat tables\n"
  2189   2190     ".headers on|off        Turn display of headers on or off\n"
  2190   2191     ".help                  Show this message\n"
  2191   2192     ".import FILE TABLE     Import data from FILE into TABLE\n"
  2192   2193   #ifndef SQLITE_OMIT_TEST_CONTROL
  2193   2194     ".imposter INDEX TABLE  Create imposter table TABLE on index INDEX\n"
  2194   2195   #endif
................................................................................
  3249   3250     sqlite3_free(z);
  3250   3251   #else
  3251   3252     rc = unlink(zFilename);
  3252   3253   #endif
  3253   3254     return rc;
  3254   3255   }
  3255   3256   
         3257  +
         3258  +/**************************************************************************
         3259  +** Beginning of implementation of .fkey_missing_indexes
         3260  +*/
         3261  +typedef struct PragmaVtab PragmaVtab;
         3262  +typedef struct PragmaCursor PragmaCursor;
         3263  +typedef struct PragmaConfig PragmaConfig;
         3264  +
         3265  +/*
         3266  +** Table structure for "pragma" eponymous virtual tables.
         3267  +*/
         3268  +struct PragmaVtab {
         3269  +  sqlite3_vtab base;
         3270  +  PragmaConfig *pConfig;
         3271  +  sqlite3 *db;
         3272  +};
         3273  +
         3274  +/*
         3275  +** Cursor structure for "pragma" eponymous virtual tables.
         3276  +*/
         3277  +struct PragmaCursor {
         3278  +  sqlite3_vtab_cursor base;
         3279  +  sqlite3_stmt *pSelect;
         3280  +  sqlite3_stmt *pPragma;
         3281  +  sqlite_int64 iRowid;
         3282  +};
         3283  +
         3284  +struct PragmaConfig {
         3285  +  const char *zModule;
         3286  +  const char *zSchema;
         3287  +  const char *zPragma;
         3288  +};
         3289  +
         3290  +/* 
         3291  +** Pragma virtual table module xConnect method.
         3292  +*/
         3293  +static int shellPragmaConnect(
         3294  +  sqlite3 *db,
         3295  +  void *pAux,
         3296  +  int argc, const char *const*argv,
         3297  +  sqlite3_vtab **ppVtab,
         3298  +  char **pzErr
         3299  +){
         3300  +  PragmaConfig *pConfig = (PragmaConfig*)pAux;
         3301  +  PragmaVtab *pTab = 0;
         3302  +  int rc;
         3303  +
         3304  +  rc = sqlite3_declare_vtab(db, pConfig->zSchema);
         3305  +  if( rc==SQLITE_OK ){
         3306  +    pTab = (PragmaVtab*)sqlite3_malloc(sizeof(PragmaVtab));
         3307  +    if( pTab==0 ){
         3308  +      rc = SQLITE_NOMEM;
         3309  +    }else{
         3310  +      memset(pTab, 0, sizeof(PragmaVtab));
         3311  +      pTab->db = db;
         3312  +      pTab->pConfig = pConfig;
         3313  +    }
         3314  +  }else{
         3315  +    *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(db));
         3316  +  }
         3317  +
         3318  +  *ppVtab = (sqlite3_vtab*)pTab;
         3319  +  return rc;
         3320  +}
         3321  +
         3322  +/* 
         3323  +** Pragma virtual table module xDisconnect method.
         3324  +*/
         3325  +static int shellPragmaDisconnect(sqlite3_vtab *pVtab){
         3326  +  PragmaVtab *pTab = (PragmaVtab*)pVtab;
         3327  +  sqlite3_free(pTab);
         3328  +  return SQLITE_OK;
         3329  +}
         3330  +
         3331  +/* 
         3332  +** Pragma virtual table module xBestIndex method.
         3333  +*/
         3334  +static int shellPragmaBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
         3335  +  return SQLITE_OK;
         3336  +}
         3337  +
         3338  +/* 
         3339  +** Pragma virtual table module xOpen method.
         3340  +*/
         3341  +static int shellPragmaOpen(sqlite3_vtab *pVtab, sqlite3_vtab_cursor **ppCursor){
         3342  +  PragmaCursor *pCsr;
         3343  +
         3344  +  pCsr = (PragmaCursor*)sqlite3_malloc(sizeof(PragmaCursor));
         3345  +  if( pCsr==0 ) return SQLITE_NOMEM;
         3346  +  memset(pCsr, 0, sizeof(PragmaCursor));
         3347  +  pCsr->base.pVtab = pVtab;
         3348  +
         3349  +  *ppCursor = (sqlite3_vtab_cursor*)pCsr;
         3350  +  return SQLITE_OK;
         3351  +}
         3352  +
         3353  +/* 
         3354  +** Pragma virtual table module xClose method.
         3355  +*/
         3356  +static int shellPragmaClose(sqlite3_vtab_cursor *cur){
         3357  +  return SQLITE_OK;
         3358  +}
         3359  +
         3360  +/* 
         3361  +** Pragma virtual table module xNext method.
         3362  +*/
         3363  +static int shellPragmaNext(sqlite3_vtab_cursor *pVtabCursor){
         3364  +  PragmaCursor *pCsr = (PragmaCursor*)pVtabCursor;
         3365  +  PragmaVtab *pTab = (PragmaVtab*)(pVtabCursor->pVtab);
         3366  +  sqlite3 *db = pTab->db;
         3367  +  int rc = SQLITE_OK;
         3368  +
         3369  +  /* Increment the xRowid value */
         3370  +  pCsr->iRowid++;
         3371  +
         3372  +  while( 1 ){
         3373  +    assert( rc==SQLITE_OK );
         3374  +
         3375  +    if( pCsr->pPragma ){
         3376  +      if( SQLITE_ROW!=sqlite3_step(pCsr->pPragma) ){
         3377  +        rc = sqlite3_finalize(pCsr->pPragma);
         3378  +        pCsr->pPragma = 0;
         3379  +      }
         3380  +    }
         3381  +
         3382  +    if( rc==SQLITE_OK && pCsr->pPragma==0 ){
         3383  +      if( SQLITE_ROW!=sqlite3_step(pCsr->pSelect) ){
         3384  +        rc = sqlite3_finalize(pCsr->pSelect);
         3385  +        pCsr->pSelect = 0;
         3386  +      }else{
         3387  +        const char *zName = (const char*)sqlite3_column_text(pCsr->pSelect, 0);
         3388  +        char *zSql = sqlite3_mprintf(pTab->pConfig->zPragma, zName);
         3389  +        if( zSql==0 ){
         3390  +          rc = SQLITE_NOMEM;
         3391  +        }else{
         3392  +          rc = sqlite3_prepare_v2(db, zSql, -1, &pCsr->pPragma, 0);
         3393  +          if( rc!=SQLITE_OK ){
         3394  +            pTab->base.zErrMsg = sqlite3_mprintf("%s", sqlite3_errmsg(db));
         3395  +          }else{
         3396  +            continue;
         3397  +          }
         3398  +        }
         3399  +      }
         3400  +    }
         3401  +
         3402  +    break;
         3403  +  }
         3404  +
         3405  +  return rc;
         3406  +}
         3407  +
         3408  +/* 
         3409  +** Pragma virtual table module xFilter method.
         3410  +*/
         3411  +static int shellPragmaFilter(
         3412  +  sqlite3_vtab_cursor *pVtabCursor, 
         3413  +  int idxNum, const char *idxStr,
         3414  +  int argc, sqlite3_value **argv
         3415  +){
         3416  +  PragmaCursor *pCsr = (PragmaCursor*)pVtabCursor;
         3417  +  PragmaVtab *pTab = (PragmaVtab*)(pVtabCursor->pVtab);
         3418  +  int rc;
         3419  +
         3420  +  sqlite3_finalize(pCsr->pSelect);
         3421  +  sqlite3_finalize(pCsr->pPragma);
         3422  +  pCsr->pSelect = 0;
         3423  +  pCsr->pPragma = 0;
         3424  +  pCsr->iRowid = 0;
         3425  +
         3426  +  rc = sqlite3_prepare_v2(pTab->db, 
         3427  +      "SELECT name FROM sqlite_master WHERE type = 'table' AND rootpage>0",
         3428  +      -1, &pCsr->pSelect, 0
         3429  +  );
         3430  +  if( rc!=SQLITE_OK ){
         3431  +    pTab->base.zErrMsg = sqlite3_mprintf("%s", sqlite3_errmsg(pTab->db));
         3432  +    return rc;
         3433  +  }
         3434  +
         3435  +  return shellPragmaNext(pVtabCursor);
         3436  +}
         3437  +
         3438  +/*
         3439  +** Pragma virtual table module xEof method.
         3440  +*/
         3441  +static int shellPragmaEof(sqlite3_vtab_cursor *pVtabCursor){
         3442  +  PragmaCursor *pCsr = (PragmaCursor*)pVtabCursor;
         3443  +  return (pCsr->pSelect==0);
         3444  +}
         3445  +
         3446  +/* 
         3447  +** Pragma virtual table module xColumn method.
         3448  +*/
         3449  +static int shellPragmaColumn(
         3450  +  sqlite3_vtab_cursor *pVtabCursor, 
         3451  +  sqlite3_context *ctx, 
         3452  +  int i
         3453  +){
         3454  +  PragmaCursor *pCsr = (PragmaCursor*)pVtabCursor;
         3455  +  switch( i ){
         3456  +    case 0: /* "database" */
         3457  +      sqlite3_result_text(ctx, "main", -1, SQLITE_STATIC);
         3458  +      break;
         3459  +
         3460  +    case 1: /* "child"/"table" */
         3461  +      sqlite3_result_value(ctx, sqlite3_column_value(pCsr->pSelect, 0));
         3462  +      break;
         3463  +
         3464  +    default:
         3465  +      sqlite3_result_value(ctx, sqlite3_column_value(pCsr->pPragma, i-2));
         3466  +      break;
         3467  +  }
         3468  +
         3469  +  return SQLITE_OK;
         3470  +}
         3471  +
         3472  +/* 
         3473  +** Pragma virtual table module xRowid method.
         3474  +*/
         3475  +static int shellPragmaRowid(sqlite3_vtab_cursor *pVtabCursor, sqlite_int64 *p){
         3476  +  PragmaCursor *pCsr = (PragmaCursor*)pVtabCursor;
         3477  +  *p = pCsr->iRowid;
         3478  +  return SQLITE_OK;
         3479  +}
         3480  +
         3481  +/*
         3482  +** Register the virtual table module with the supplied database handle.
         3483  +*/
         3484  +static int shellPragmaRegister(sqlite3 *db){
         3485  +  static sqlite3_module shellPragmaModule = {
         3486  +    0,                            /* iVersion */
         3487  +    shellPragmaConnect,           /* xCreate - create a table */
         3488  +    shellPragmaConnect,           /* xConnect - connect to an existing table */
         3489  +    shellPragmaBestIndex,         /* xBestIndex - Determine search strategy */
         3490  +    shellPragmaDisconnect,        /* xDisconnect - Disconnect from a table */
         3491  +    shellPragmaDisconnect,        /* xDestroy - Drop a table */
         3492  +    shellPragmaOpen,              /* xOpen - open a cursor */
         3493  +    shellPragmaClose,             /* xClose - close a cursor */
         3494  +    shellPragmaFilter,            /* xFilter - configure scan constraints */
         3495  +    shellPragmaNext,              /* xNext - advance a cursor */
         3496  +    shellPragmaEof,               /* xEof */
         3497  +    shellPragmaColumn,            /* xColumn - read data */
         3498  +    shellPragmaRowid,             /* xRowid - read data */
         3499  +    0,                            /* xUpdate - write data */
         3500  +    0,                            /* xBegin - begin transaction */
         3501  +    0,                            /* xSync - sync transaction */
         3502  +    0,                            /* xCommit - commit transaction */
         3503  +    0,                            /* xRollback - rollback transaction */
         3504  +    0,                            /* xFindFunction - function overloading */
         3505  +    0,                            /* xRename - rename the table */
         3506  +    0,                            /* xSavepoint */
         3507  +    0,                            /* xRelease */
         3508  +    0                             /* xRollbackTo */
         3509  +  };
         3510  +  int rc = SQLITE_OK;
         3511  +  int i;
         3512  +
         3513  +  static PragmaConfig aConfig[] = {
         3514  +    { "pragma_foreign_key_list",
         3515  +      "CREATE TABLE x(database, child, "
         3516  +        "id, seq, parent, child_col, parent_col, on_update, on_delete, match)",
         3517  +      "PRAGMA foreign_key_list = %Q"
         3518  +    },
         3519  +    { "pragma_table_info",
         3520  +      "CREATE TABLE x(database, tbl, "
         3521  +        "cid, name, type, not_null, dflt_value, pk)",
         3522  +      "PRAGMA table_info = %Q"
         3523  +    }
         3524  +  };
         3525  +
         3526  +  for(i=0; rc==SQLITE_OK && i<sizeof(aConfig)/sizeof(aConfig[0]); i++){
         3527  +    rc = sqlite3_create_module_v2(
         3528  +        db, aConfig[i].zModule, &shellPragmaModule, (void*)&aConfig[i], 0
         3529  +    );
         3530  +  }
         3531  +
         3532  +  return rc;
         3533  +}
         3534  +
         3535  +/*
         3536  +** The implementation of SQL scalar function fkey_collate_clause(). This
         3537  +** scalar function is always called with four arguments - the parent
         3538  +** table name, the parent column name, the child table name and the child
         3539  +** column name.
         3540  +**
         3541  +**   fkey_collate_clause('parent-tab', 'parent-col', 'child-tab', 'child-col')
         3542  +**
         3543  +** If either of the named tables or columns do not exist, this function
         3544  +** returns an empty string. An empty string is also returned if both tables 
         3545  +** and columns exist but have the same default collation sequence. Or,
         3546  +** if both exist but the default collation sequences are different, this
         3547  +** function returns the string " COLLATE <parent-collation>", where
         3548  +** <parent-collation> is the default collation sequence of the parent column.
         3549  +*/
         3550  +static void shellFkeyCollateClause(
         3551  +  sqlite3_context *pCtx, 
         3552  +  int nVal, 
         3553  +  sqlite3_value **apVal
         3554  +){
         3555  +  sqlite3 *db = sqlite3_context_db_handle(pCtx);
         3556  +  const char *zParent;
         3557  +  const char *zParentCol;
         3558  +  const char *zParentSeq;
         3559  +  const char *zChild;
         3560  +  const char *zChildCol;
         3561  +  const char *zChildSeq;
         3562  +  int rc;
         3563  +  
         3564  +  assert( nVal==4 );
         3565  +  zParent = (const char*)sqlite3_value_text(apVal[0]);
         3566  +  zParentCol = (const char*)sqlite3_value_text(apVal[1]);
         3567  +  zChild = (const char*)sqlite3_value_text(apVal[2]);
         3568  +  zChildCol = (const char*)sqlite3_value_text(apVal[3]);
         3569  +
         3570  +  sqlite3_result_text(pCtx, "", -1, SQLITE_STATIC);
         3571  +  rc = sqlite3_table_column_metadata(
         3572  +      db, "main", zParent, zParentCol, 0, &zParentSeq, 0, 0, 0
         3573  +  );
         3574  +  if( rc==SQLITE_OK ){
         3575  +    rc = sqlite3_table_column_metadata(
         3576  +        db, "main", zChild, zChildCol, 0, &zChildSeq, 0, 0, 0
         3577  +    );
         3578  +  }
         3579  +
         3580  +  if( rc==SQLITE_OK && sqlite3_stricmp(zParentSeq, zChildSeq) ){
         3581  +    char *z = sqlite3_mprintf(" COLLATE %s", zParentSeq);
         3582  +    sqlite3_result_text(pCtx, z, -1, SQLITE_TRANSIENT);
         3583  +    sqlite3_free(z);
         3584  +  }
         3585  +}
         3586  +
         3587  +
         3588  +/*
         3589  +** The implementation of dot-command ".fkey_missing_indexes".
         3590  +*/
         3591  +static int shellFkeyMissingIndexes(
         3592  +  ShellState *pState,             /* Current shell tool state */
         3593  +  char **azArg,                   /* Array of arguments passed to dot command */
         3594  +  int nArg                        /* Number of entries in azArg[] */
         3595  +){
         3596  +  sqlite3 *db = pState->db;
         3597  +  FILE *out = pState->out;
         3598  +  int bVerbose = 0;
         3599  +
         3600  +  int rc;
         3601  +  sqlite3_stmt *pSql = 0;
         3602  +  const char *zSql =
         3603  +    "SELECT "
         3604  +    "     'EXPLAIN QUERY PLAN SELECT rowid FROM ' || quote(child) || ' WHERE ' "
         3605  +    "  || group_concat(quote(child) || '.' || quote(child_col) || '=?' || "
         3606  +    "        fkey_collate_clause(parent, parent_col, child, child_col),' AND ')"
         3607  +    ", "
         3608  +    "     'SEARCH TABLE ' || child || ' USING COVERING INDEX*('"
         3609  +    "  || group_concat('*=?', ' AND ') || ')'"
         3610  +    ", "
         3611  +    "     child  || '(' || group_concat(child_col,  ', ') || ')'"
         3612  +    ", "
         3613  +    "     parent || '(' || group_concat(COALESCE(parent_col, "
         3614  +    "       (SELECT name FROM pragma_table_info WHERE tbl=parent AND pk=seq+1)"
         3615  +    "     )) || ')'"
         3616  +    ", "
         3617  +    "     'CREATE INDEX ' || quote(child ||'_'|| group_concat(child_col, '_'))"
         3618  +    "  || ' ON ' || quote(child) || '('"
         3619  +    "  || group_concat(quote(child_col) ||"
         3620  +    "        fkey_collate_clause(parent, parent_col, child, child_col), ', ')"
         3621  +    "  || ');'"
         3622  +
         3623  +    "FROM pragma_foreign_key_list AS o GROUP BY child, id"
         3624  +  ;
         3625  +
         3626  +  if( nArg>2 ){
         3627  +    raw_printf(stderr, "Usage: .fkey_lint ?verbose-flag?\n");
         3628  +  }
         3629  +  if( nArg==2 ) bVerbose = booleanValue(azArg[1]);
         3630  +
         3631  +  /* Register the pragma eponymous virtual tables */
         3632  +  rc = shellPragmaRegister(db);
         3633  +  
         3634  +  /* Register the fkey_collate_clause() SQL function */
         3635  +  if( rc==SQLITE_OK ){
         3636  +    rc = sqlite3_create_function(db, "fkey_collate_clause", 4, SQLITE_UTF8,
         3637  +        0, shellFkeyCollateClause, 0, 0
         3638  +    );
         3639  +  }
         3640  +
         3641  +
         3642  +  if( rc==SQLITE_OK ){
         3643  +    rc = sqlite3_prepare_v2(db, zSql, -1, &pSql, 0);
         3644  +  }
         3645  +
         3646  +  if( rc==SQLITE_OK ){
         3647  +    int rc2;
         3648  +    while( SQLITE_ROW==sqlite3_step(pSql) ){
         3649  +      int res = -1;
         3650  +      sqlite3_stmt *pExplain = 0;
         3651  +      const char *zEQP = (const char*)sqlite3_column_text(pSql, 0);
         3652  +      const char *zGlob = (const char*)sqlite3_column_text(pSql, 1);
         3653  +      const char *zFrom = (const char*)sqlite3_column_text(pSql, 2);
         3654  +      const char *zTarget = (const char*)sqlite3_column_text(pSql, 3);
         3655  +      const char *zCI = (const char*)sqlite3_column_text(pSql, 4);
         3656  +
         3657  +      rc = sqlite3_prepare_v2(db, zEQP, -1, &pExplain, 0);
         3658  +      if( rc!=SQLITE_OK ) break;
         3659  +      if( SQLITE_ROW==sqlite3_step(pExplain) ){
         3660  +        const char *zPlan = (const char*)sqlite3_column_text(pExplain, 3);
         3661  +        res = (0==sqlite3_strglob(zGlob, zPlan));
         3662  +      }
         3663  +      rc = sqlite3_finalize(pExplain);
         3664  +      if( rc!=SQLITE_OK ) break;
         3665  +
         3666  +      if( res<0 ){
         3667  +        raw_printf(stderr, "Error: internal error");
         3668  +        break;
         3669  +      }else if( res==0 ){
         3670  +        raw_printf(out, "%s --> %s\n", zCI, zTarget);
         3671  +      }else if( bVerbose ){
         3672  +        raw_printf(out, "/* no extra indexes required for %s -> %s */\n", 
         3673  +            zFrom, zTarget
         3674  +        );
         3675  +      }
         3676  +    }
         3677  +
         3678  +    if( rc!=SQLITE_OK ){
         3679  +      raw_printf(stderr, "%s\n", sqlite3_errmsg(db));
         3680  +    }
         3681  +
         3682  +    rc2 = sqlite3_finalize(pSql);
         3683  +    if( rc==SQLITE_OK && rc2!=SQLITE_OK ){
         3684  +      rc = rc2;
         3685  +      raw_printf(stderr, "%s\n", sqlite3_errmsg(db));
         3686  +    }
         3687  +  }else{
         3688  +    raw_printf(stderr, "%s\n", sqlite3_errmsg(db));
         3689  +  }
         3690  +
         3691  +  return rc;
         3692  +}
         3693  +/*
         3694  +** End of implementation of .fkey_missing_indexes
         3695  +**************************************************************************/
         3696  +
  3256   3697   /*
  3257   3698   ** If an input line begins with "." then invoke this routine to
  3258   3699   ** process that line.
  3259   3700   **
  3260   3701   ** Return 1 on error, 2 to exit, and 0 otherwise.
  3261   3702   */
  3262   3703   static int do_meta_command(char *zLine, ShellState *p){
................................................................................
  3561   4002         if( p->mode==MODE_Explain ) p->mode = p->normalMode;
  3562   4003         p->autoExplain = 0;
  3563   4004       }else if( val==99 ){
  3564   4005         if( p->mode==MODE_Explain ) p->mode = p->normalMode;
  3565   4006         p->autoExplain = 1;
  3566   4007       }
  3567   4008     }else
         4009  +
         4010  +  if( c=='f' && strncmp(azArg[0], "fkey_missing_indexes", n)==0 ){
         4011  +    shellFkeyMissingIndexes(p, azArg, nArg);
         4012  +  }else
  3568   4013   
  3569   4014     if( c=='f' && strncmp(azArg[0], "fullschema", n)==0 ){
  3570   4015       ShellState data;
  3571   4016       char *zErrMsg = 0;
  3572   4017       int doStats = 0;
  3573   4018       memcpy(&data, p, sizeof(data));
  3574   4019       data.showHeader = 0;

Added test/shell6.test.

            1  +# 2016 December 15
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +
           13  +set testdir [file dirname $argv0]
           14  +source $testdir/tester.tcl
           15  +set testprefix shell6
           16  +set CLI [test_find_cli]
           17  +db close
           18  +forcedelete test.db test.db-journal test.db-wal
           19  +
           20  +foreach {tn schema output} {
           21  +  1 {
           22  +    CREATE TABLE p1(a PRIMARY KEY, b);
           23  +    CREATE TABLE c1(x, y REFERENCES p1);
           24  +  } {
           25  +    CREATE INDEX 'c1_y' ON 'c1'('y'); --> p1(a)
           26  +  }
           27  +
           28  +  2 {
           29  +    CREATE TABLE p1(a PRIMARY KEY, b);
           30  +    CREATE TABLE c2(x REFERENCES p1, y REFERENCES p1);
           31  +  } {
           32  +    CREATE INDEX 'c2_y' ON 'c2'('y'); --> p1(a)
           33  +    CREATE INDEX 'c2_x' ON 'c2'('x'); --> p1(a)
           34  +  }
           35  +
           36  +  3 {
           37  +    CREATE TABLE 'p 1'(a, b, c, PRIMARY KEY(c, b));
           38  +    CREATE TABLE 'c 1'(x, y, z, FOREIGN KEY (z, y) REFERENCES 'p 1');
           39  +  } {
           40  +    CREATE INDEX 'c 1_z_y' ON 'c 1'('z', 'y'); --> p 1(c,b)
           41  +  }
           42  +
           43  +  4 {
           44  +    CREATE TABLE p1(a, 'b b b' PRIMARY KEY);
           45  +    CREATE TABLE c1('x y z' REFERENCES p1);
           46  +    CREATE INDEX i1 ON c1('x y z') WHERE "x y z" IS NOT NULL;
           47  +  } {
           48  +  }
           49  +
           50  +  5 {
           51  +    CREATE TABLE p1(a, 'b b b' PRIMARY KEY);
           52  +    CREATE TABLE c1('x y z' REFERENCES p1);
           53  +    CREATE INDEX i1 ON c1('x y z') WHERE "x y z" IS NOT 12;
           54  +  } {
           55  +    CREATE INDEX 'c1_x y z' ON 'c1'('x y z'); --> p1(b b b)
           56  +  }
           57  +
           58  +  6 {
           59  +    CREATE TABLE x1(a, b, c, UNIQUE(a, b));
           60  +    CREATE TABLE y1(a, b, c, FOREIGN KEY(b, a) REFERENCES x1(a, b));
           61  +    CREATE INDEX y1i ON y1(a, c, b);
           62  +  } {
           63  +    CREATE INDEX 'y1_b_a' ON 'y1'('b', 'a'); --> x1(a,b)
           64  +  }
           65  +
           66  +  6 {
           67  +    CREATE TABLE x1(a COLLATE nocase, b, UNIQUE(a));
           68  +    CREATE TABLE y1(a COLLATE rtrim REFERENCES x1(a));
           69  +  } {
           70  +    CREATE INDEX 'y1_a' ON 'y1'('a' COLLATE nocase); --> x1(a)
           71  +  }
           72  +
           73  +} {
           74  +  forcedelete test.db
           75  +  sqlite3 db test.db
           76  +  execsql $schema
           77  +
           78  +  set expected ""
           79  +  foreach line [split $output "\n"] {
           80  +    set line [string trim $line]
           81  +    if {$line!=""} {
           82  +      append expected "$line\n"
           83  +    }
           84  +  }
           85  +
           86  +  do_test 1.$tn.1 {
           87  +    set RES [catchcmd test.db .fkey_missing_indexes]
           88  +  } [list 0 [string trim $expected]]
           89  +
           90  +  do_test 1.$tn.2 {
           91  +    execsql [lindex $RES 1]
           92  +    catchcmd test.db .fkey_missing_indexes
           93  +  } {0 {}}
           94  +
           95  +  db close
           96  +}
           97  +
           98  +finish_test
           99  +
          100  +