/ Check-in [3ab05987]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Update .fkey_missing_indexes to use the built-in pragma vtabs.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | fkey-missing-indexes
Files: files | file ages | folders
SHA1: 3ab05987b0cc12af64bf32d885d04aff45d7a77c
User & Date: dan 2016-12-16 16:44:27
Context
2016-12-16
17:28
Change ".fkey_missing_indexes" to ".lint fkey-indexes". check-in: ee621ade user: dan tags: fkey-missing-indexes
16:44
Update .fkey_missing_indexes to use the built-in pragma vtabs. check-in: 3ab05987 user: dan tags: fkey-missing-indexes
16:13
Merge the pragma-as-vtab change into this branch. check-in: 4ba45e72 user: dan tags: fkey-missing-indexes
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/shell.c.

3254
3255
3256
3257
3258
3259
3260
3261
3262
3263
3264
3265
3266
3267
3268
3269
3270
3271
3272
3273
3274
3275
3276
3277
3278
3279
3280
3281
3282
3283
3284
3285
3286
3287
3288
3289
3290
3291
3292
3293
3294
3295
3296
3297
3298
3299
3300
3301
3302
3303
3304
3305
3306
3307
3308
3309
3310
3311
3312
3313
3314
3315
3316
3317
3318
3319
3320
3321
3322
3323
3324
3325
3326
3327
3328
3329
3330
3331
3332
3333
3334
3335
3336
3337
3338
3339
3340
3341
3342
3343
3344
3345
3346
3347
3348
3349
3350
3351
3352
3353
3354
3355
3356
3357
3358
3359
3360
3361
3362
3363
3364
3365
3366
3367
3368
3369
3370
3371
3372
3373
3374
3375
3376
3377
3378
3379
3380
3381
3382
3383
3384
3385
3386
3387
3388
3389
3390
3391
3392
3393
3394
3395
3396
3397
3398
3399
3400
3401
3402
3403
3404
3405
3406
3407
3408
3409
3410
3411
3412
3413
3414
3415
3416
3417
3418
3419
3420
3421
3422
3423
3424
3425
3426
3427
3428
3429
3430
3431
3432
3433
3434
3435
3436
3437
3438
3439
3440
3441
3442
3443
3444
3445
3446
3447
3448
3449
3450
3451
3452
3453
3454
3455
3456
3457
3458
3459
3460
3461
3462
3463
3464
3465
3466
3467
3468
3469
3470
3471
3472
3473
3474
3475
3476
3477
3478
3479
3480
3481
3482
3483
3484
3485
3486
3487
3488
3489
3490
3491
3492
3493
3494
3495
3496
3497
3498
3499
3500
3501
3502
3503
3504
3505
3506
3507
3508
3509
3510
3511
3512
3513
3514
3515
3516
3517
3518
3519
3520
3521
3522
3523
3524
3525
3526
3527
3528
3529
3530
3531
3532
3533
3534
3535
3536

3537
3538
3539
3540
3541
3542
3543
3544
3545
3546
....
3589
3590
3591
3592
3593
3594
3595
3596
3597
3598
3599
3600
3601


3602
3603
3604



































3605
3606
3607
3608
3609

3610
3611
3612
3613
3614
3615
3616
3617
3618
3619
3620
3621
3622
3623
3624
3625
3626
3627
3628

3629
3630
3631
3632
3633
3634
3635
3636
....
3639
3640
3641
3642
3643
3644
3645
3646
3647
3648
3649
3650
3651
3652
3653
3654
3655
3656
3657
3658
3659
3660
3661
3662
  return rc;
}


/**************************************************************************
** Beginning of implementation of .fkey_missing_indexes
*/
typedef struct PragmaVtab PragmaVtab;
typedef struct PragmaCursor PragmaCursor;
typedef struct PragmaConfig PragmaConfig;

/*
** Table structure for "pragma" eponymous virtual tables.
*/
struct PragmaVtab {
  sqlite3_vtab base;
  PragmaConfig *pConfig;
  sqlite3 *db;
};

/*
** Cursor structure for "pragma" eponymous virtual tables.
*/
struct PragmaCursor {
  sqlite3_vtab_cursor base;
  sqlite3_stmt *pSelect;
  sqlite3_stmt *pPragma;
  sqlite_int64 iRowid;
};

struct PragmaConfig {
  const char *zModule;
  const char *zSchema;
  const char *zPragma;
};

/* 
** Pragma virtual table module xConnect method.
*/
static int shellPragmaConnect(
  sqlite3 *db,
  void *pAux,
  int argc, const char *const*argv,
  sqlite3_vtab **ppVtab,
  char **pzErr
){
  PragmaConfig *pConfig = (PragmaConfig*)pAux;
  PragmaVtab *pTab = 0;
  int rc;

  rc = sqlite3_declare_vtab(db, pConfig->zSchema);
  if( rc==SQLITE_OK ){
    pTab = (PragmaVtab*)sqlite3_malloc(sizeof(PragmaVtab));
    if( pTab==0 ){
      rc = SQLITE_NOMEM;
    }else{
      memset(pTab, 0, sizeof(PragmaVtab));
      pTab->db = db;
      pTab->pConfig = pConfig;
    }
  }else{
    *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(db));
  }

  *ppVtab = (sqlite3_vtab*)pTab;
  return rc;
}

/* 
** Pragma virtual table module xDisconnect method.
*/
static int shellPragmaDisconnect(sqlite3_vtab *pVtab){
  PragmaVtab *pTab = (PragmaVtab*)pVtab;
  sqlite3_free(pTab);
  return SQLITE_OK;
}

/* 
** Pragma virtual table module xBestIndex method.
*/
static int shellPragmaBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
  return SQLITE_OK;
}

/* 
** Pragma virtual table module xOpen method.
*/
static int shellPragmaOpen(sqlite3_vtab *pVtab, sqlite3_vtab_cursor **ppCursor){
  PragmaCursor *pCsr;

  pCsr = (PragmaCursor*)sqlite3_malloc(sizeof(PragmaCursor));
  if( pCsr==0 ) return SQLITE_NOMEM;
  memset(pCsr, 0, sizeof(PragmaCursor));
  pCsr->base.pVtab = pVtab;

  *ppCursor = (sqlite3_vtab_cursor*)pCsr;
  return SQLITE_OK;
}

/* 
** Pragma virtual table module xClose method.
*/
static int shellPragmaClose(sqlite3_vtab_cursor *cur){
  return SQLITE_OK;
}

/* 
** Pragma virtual table module xNext method.
*/
static int shellPragmaNext(sqlite3_vtab_cursor *pVtabCursor){
  PragmaCursor *pCsr = (PragmaCursor*)pVtabCursor;
  PragmaVtab *pTab = (PragmaVtab*)(pVtabCursor->pVtab);
  sqlite3 *db = pTab->db;
  int rc = SQLITE_OK;

  /* Increment the xRowid value */
  pCsr->iRowid++;

  while( 1 ){
    assert( rc==SQLITE_OK );

    if( pCsr->pPragma ){
      if( SQLITE_ROW!=sqlite3_step(pCsr->pPragma) ){
        rc = sqlite3_finalize(pCsr->pPragma);
        pCsr->pPragma = 0;
      }
    }

    if( rc==SQLITE_OK && pCsr->pPragma==0 ){
      if( SQLITE_ROW!=sqlite3_step(pCsr->pSelect) ){
        rc = sqlite3_finalize(pCsr->pSelect);
        pCsr->pSelect = 0;
      }else{
        const char *zName = (const char*)sqlite3_column_text(pCsr->pSelect, 0);
        char *zSql = sqlite3_mprintf(pTab->pConfig->zPragma, zName);
        if( zSql==0 ){
          rc = SQLITE_NOMEM;
        }else{
          rc = sqlite3_prepare_v2(db, zSql, -1, &pCsr->pPragma, 0);
          if( rc!=SQLITE_OK ){
            pTab->base.zErrMsg = sqlite3_mprintf("%s", sqlite3_errmsg(db));
          }else{
            continue;
          }
        }
      }
    }

    break;
  }

  return rc;
}

/* 
** Pragma virtual table module xFilter method.
*/
static int shellPragmaFilter(
  sqlite3_vtab_cursor *pVtabCursor, 
  int idxNum, const char *idxStr,
  int argc, sqlite3_value **argv
){
  PragmaCursor *pCsr = (PragmaCursor*)pVtabCursor;
  PragmaVtab *pTab = (PragmaVtab*)(pVtabCursor->pVtab);
  int rc;

  sqlite3_finalize(pCsr->pSelect);
  sqlite3_finalize(pCsr->pPragma);
  pCsr->pSelect = 0;
  pCsr->pPragma = 0;
  pCsr->iRowid = 0;

  rc = sqlite3_prepare_v2(pTab->db, 
      "SELECT name FROM sqlite_master WHERE type = 'table' AND rootpage>0",
      -1, &pCsr->pSelect, 0
  );
  if( rc!=SQLITE_OK ){
    pTab->base.zErrMsg = sqlite3_mprintf("%s", sqlite3_errmsg(pTab->db));
    return rc;
  }

  return shellPragmaNext(pVtabCursor);
}

/*
** Pragma virtual table module xEof method.
*/
static int shellPragmaEof(sqlite3_vtab_cursor *pVtabCursor){
  PragmaCursor *pCsr = (PragmaCursor*)pVtabCursor;
  return (pCsr->pSelect==0);
}

/* 
** Pragma virtual table module xColumn method.
*/
static int shellPragmaColumn(
  sqlite3_vtab_cursor *pVtabCursor, 
  sqlite3_context *ctx, 
  int i
){
  PragmaCursor *pCsr = (PragmaCursor*)pVtabCursor;
  switch( i ){
    case 0: /* "database" */
      sqlite3_result_text(ctx, "main", -1, SQLITE_STATIC);
      break;

    case 1: /* "child"/"table" */
      sqlite3_result_value(ctx, sqlite3_column_value(pCsr->pSelect, 0));
      break;

    default:
      sqlite3_result_value(ctx, sqlite3_column_value(pCsr->pPragma, i-2));
      break;
  }

  return SQLITE_OK;
}

/* 
** Pragma virtual table module xRowid method.
*/
static int shellPragmaRowid(sqlite3_vtab_cursor *pVtabCursor, sqlite_int64 *p){
  PragmaCursor *pCsr = (PragmaCursor*)pVtabCursor;
  *p = pCsr->iRowid;
  return SQLITE_OK;
}

/*
** Register the virtual table module with the supplied database handle.
*/
static int shellPragmaRegister(sqlite3 *db){
  static sqlite3_module shellPragmaModule = {
    0,                            /* iVersion */
    shellPragmaConnect,           /* xCreate - create a table */
    shellPragmaConnect,           /* xConnect - connect to an existing table */
    shellPragmaBestIndex,         /* xBestIndex - Determine search strategy */
    shellPragmaDisconnect,        /* xDisconnect - Disconnect from a table */
    shellPragmaDisconnect,        /* xDestroy - Drop a table */
    shellPragmaOpen,              /* xOpen - open a cursor */
    shellPragmaClose,             /* xClose - close a cursor */
    shellPragmaFilter,            /* xFilter - configure scan constraints */
    shellPragmaNext,              /* xNext - advance a cursor */
    shellPragmaEof,               /* xEof */
    shellPragmaColumn,            /* xColumn - read data */
    shellPragmaRowid,             /* xRowid - read data */
    0,                            /* xUpdate - write data */
    0,                            /* xBegin - begin transaction */
    0,                            /* xSync - sync transaction */
    0,                            /* xCommit - commit transaction */
    0,                            /* xRollback - rollback transaction */
    0,                            /* xFindFunction - function overloading */
    0,                            /* xRename - rename the table */
    0,                            /* xSavepoint */
    0,                            /* xRelease */
    0                             /* xRollbackTo */
  };
  int rc = SQLITE_OK;
  int i;

  static PragmaConfig aConfig[] = {
    { "pragma_foreign_key_list",
      "CREATE TABLE x(database, child, "
        "id, seq, parent, child_col, parent_col, on_update, on_delete, match)",
      "PRAGMA foreign_key_list = %Q"
    },
    { "pragma_table_info",
      "CREATE TABLE x(database, tbl, "
        "cid, name, type, not_null, dflt_value, pk)",
      "PRAGMA table_info = %Q"
    }
  };

  for(i=0; rc==SQLITE_OK && i<sizeof(aConfig)/sizeof(aConfig[0]); i++){
    rc = sqlite3_create_module_v2(
        db, aConfig[i].zModule, &shellPragmaModule, (void*)&aConfig[i], 0
    );
  }

  return rc;
}

/*
** The implementation of SQL scalar function fkey_collate_clause(). This

** scalar function is always called with four arguments - the parent
** table name, the parent column name, the child table name and the child
** column name.
**
**   fkey_collate_clause('parent-tab', 'parent-col', 'child-tab', 'child-col')
**
** If either of the named tables or columns do not exist, this function
** returns an empty string. An empty string is also returned if both tables 
** and columns exist but have the same default collation sequence. Or,
** if both exist but the default collation sequences are different, this
................................................................................
** The implementation of dot-command ".fkey_missing_indexes".
*/
static int shellFkeyMissingIndexes(
  ShellState *pState,             /* Current shell tool state */
  char **azArg,                   /* Array of arguments passed to dot command */
  int nArg                        /* Number of entries in azArg[] */
){
  sqlite3 *db = pState->db;
  FILE *out = pState->out;
  int bVerbose = 0;               /* If -verbose is present */
  int bGroupByParent = 0;         /* If -groupbyparent is present */
  int i;
  const char *zIndent = "";



  int rc;
  sqlite3_stmt *pSql = 0;



































  const char *zSql =
    "SELECT "
    "     'EXPLAIN QUERY PLAN SELECT rowid FROM ' || quote(child) || ' WHERE ' "
    "  || group_concat(quote(child) || '.' || quote(child_col) || '=?' || "
    "        fkey_collate_clause(parent, parent_col, child, child_col),' AND ')"

    ", "
    "     'SEARCH TABLE ' || child || ' USING COVERING INDEX*('"
    "  || group_concat('*=?', ' AND ') || ')'"
    ", "
    "     child  || '(' || group_concat(child_col,  ', ') || ')'"
    ", "
    "     parent || '(' || group_concat(COALESCE(parent_col, "
    "       (SELECT name FROM pragma_table_info WHERE tbl=parent AND pk=seq+1)"
    "     )) || ')'"
    ", "
    "     'CREATE INDEX ' || quote(child ||'_'|| group_concat(child_col, '_'))"
    "  || ' ON ' || quote(child) || '('"
    "  || group_concat(quote(child_col) ||"
    "        fkey_collate_clause(parent, parent_col, child, child_col), ', ')"
    "  || ');'"
    ", "
    "     parent "

    "FROM pragma_foreign_key_list GROUP BY child, id "

    "ORDER BY (CASE WHEN ? THEN parent ELSE CHILD END)"
  ;

  for(i=1; i<nArg; i++){
    int n = strlen(azArg[i]);
    if( n>1 && sqlite3_strnicmp("-verbose", azArg[i], n)==0 ){
      bVerbose = 1;
    }
................................................................................
      zIndent = "    ";
    }
    else{
      raw_printf(stderr, "Usage: .fkey_lint ?-verbose? ?-groupbyparent?\n");
      return SQLITE_ERROR;
    }
  }

  /* Register the pragma eponymous virtual tables */
  rc = shellPragmaRegister(db);
  
  /* Register the fkey_collate_clause() SQL function */
  if( rc==SQLITE_OK ){
    rc = sqlite3_create_function(db, "fkey_collate_clause", 4, SQLITE_UTF8,
        0, shellFkeyCollateClause, 0, 0
    );
  }


  if( rc==SQLITE_OK ){
    rc = sqlite3_prepare_v2(db, zSql, -1, &pSql, 0);
  }
  if( rc==SQLITE_OK ){
    sqlite3_bind_int(pSql, 1, bGroupByParent);







<
<
<


<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
|
>
|
|
<







 







|
|


|
|
>
>

<
<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

|
|
|
<
>

|


|

|
|


|
|
|
|


|

|
>
|







 







|
<
<
<

<
|
|
|
<







3254
3255
3256
3257
3258
3259
3260



3261
3262














































































































































































































































































3263
3264
3265
3266

3267
3268
3269
3270
3271
3272
3273
....
3316
3317
3318
3319
3320
3321
3322
3323
3324
3325
3326
3327
3328
3329
3330
3331


3332
3333
3334
3335
3336
3337
3338
3339
3340
3341
3342
3343
3344
3345
3346
3347
3348
3349
3350
3351
3352
3353
3354
3355
3356
3357
3358
3359
3360
3361
3362
3363
3364
3365
3366
3367
3368
3369
3370

3371
3372
3373
3374
3375
3376
3377
3378
3379
3380
3381
3382
3383
3384
3385
3386
3387
3388
3389
3390
3391
3392
3393
3394
3395
3396
3397
3398
3399
....
3402
3403
3404
3405
3406
3407
3408
3409



3410

3411
3412
3413

3414
3415
3416
3417
3418
3419
3420
  return rc;
}


/**************************************************************************
** Beginning of implementation of .fkey_missing_indexes
*/




/*














































































































































































































































































** The implementation of SQL scalar function fkey_collate_clause(), used
** by the ".fkey_missing_indexes" command. This scalar function is always
** called with four arguments - the parent table name, the parent column name,
** the child table name and the child column name.

**
**   fkey_collate_clause('parent-tab', 'parent-col', 'child-tab', 'child-col')
**
** If either of the named tables or columns do not exist, this function
** returns an empty string. An empty string is also returned if both tables 
** and columns exist but have the same default collation sequence. Or,
** if both exist but the default collation sequences are different, this
................................................................................
** The implementation of dot-command ".fkey_missing_indexes".
*/
static int shellFkeyMissingIndexes(
  ShellState *pState,             /* Current shell tool state */
  char **azArg,                   /* Array of arguments passed to dot command */
  int nArg                        /* Number of entries in azArg[] */
){
  sqlite3 *db = pState->db;       /* Database handle to query "main" db of */
  FILE *out = pState->out;        /* Stream to write non-error output to */
  int bVerbose = 0;               /* If -verbose is present */
  int bGroupByParent = 0;         /* If -groupbyparent is present */
  int i;                          /* To iterate through azArg[] */
  const char *zIndent = "";       /* How much to indent CREATE INDEX by */
  int rc;                         /* Return code */
  sqlite3_stmt *pSql = 0;         /* Compiled version of SQL statement below */



  /*
  ** This SELECT statement returns one row for each foreign key constraint
  ** in the schema of the main database. The column values are:
  **
  ** 0. The text of an SQL statement similar to:
  **
  **      "EXPLAIN QUERY PLAN SELECT rowid FROM child_table WHERE child_key=?"
  **
  **    This is the same SELECT that the foreign keys implementation needs
  **    to run internally on child tables. If there is an index that can
  **    be used to optimize this query, then it can also be used by the FK
  **    implementation to optimize DELETE or UPDATE statements on the parent
  **    table.
  **
  ** 1. A GLOB pattern suitable for sqlite3_strglob(). If the plan output by
  **    the EXPLAIN QUERY PLAN command matches this pattern, then the schema
  **    contains an index that can be used to optimize the query.
  **
  ** 2. Human readable text that describes the child table and columns. e.g.
  **
  **       "child_table(child_key1, child_key2)"
  **
  ** 3. Human readable text that describes the parent table and columns. e.g.
  **
  **       "parent_table(parent_key1, parent_key2)"
  **
  ** 4. A full CREATE INDEX statement for an index that could be used to
  **    optimize DELETE or UPDATE statements on the parent table. e.g.
  **
  **       "CREATE INDEX child_table_child_key ON child_table(child_key)"
  **
  ** 5. The name of the parent table.
  **
  ** These six values are used by the C logic below to generate the report.
  */
  const char *zSql =
  "SELECT "
    "     'EXPLAIN QUERY PLAN SELECT rowid FROM ' || quote(s.name) || ' WHERE '"
    "  || group_concat(quote(s.name) || '.' || quote(f.[from]) || '=?' "

    "  || fkey_collate_clause(f.[table], f.[to], s.name, f.[from]),' AND ')"
    ", "
    "     'SEARCH TABLE ' || s.name || ' USING COVERING INDEX*('"
    "  || group_concat('*=?', ' AND ') || ')'"
    ", "
    "     s.name  || '(' || group_concat(f.[from],  ', ') || ')'"
    ", "
    "     f.[table] || '(' || group_concat(COALESCE(f.[to], "
    "       (SELECT name FROM pragma_table_info(f.[table]) WHERE pk=seq+1)"
    "     )) || ')'"
    ", "
    "     'CREATE INDEX ' || quote(s.name ||'_'|| group_concat(f.[from], '_'))"
    "  || ' ON ' || quote(s.name) || '('"
    "  || group_concat(quote(f.[from]) ||"
    "        fkey_collate_clause(f.[table], f.[to], s.name, f.[from]), ', ')"
    "  || ');'"
    ", "
    "     f.[table] "

    "FROM sqlite_master AS s, pragma_foreign_key_list(s.name) AS f "
    "GROUP BY s.name, f.id "
    "ORDER BY (CASE WHEN ? THEN f.[table] ELSE s.name END)"
  ;

  for(i=1; i<nArg; i++){
    int n = strlen(azArg[i]);
    if( n>1 && sqlite3_strnicmp("-verbose", azArg[i], n)==0 ){
      bVerbose = 1;
    }
................................................................................
      zIndent = "    ";
    }
    else{
      raw_printf(stderr, "Usage: .fkey_lint ?-verbose? ?-groupbyparent?\n");
      return SQLITE_ERROR;
    }
  }
  



  /* Register the fkey_collate_clause() SQL function */

  rc = sqlite3_create_function(db, "fkey_collate_clause", 4, SQLITE_UTF8,
      0, shellFkeyCollateClause, 0, 0
  );



  if( rc==SQLITE_OK ){
    rc = sqlite3_prepare_v2(db, zSql, -1, &pSql, 0);
  }
  if( rc==SQLITE_OK ){
    sqlite3_bind_int(pSql, 1, bGroupByParent);