/ Check-in [976c51b4]
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:Add the -groupbyparent option to the ".fkey_missing_indexes" command.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | fkey-missing-indexes
Files: files | file ages | folders
SHA1: 976c51b4836dfba2ce9b246334a85bda08ac526f
User & Date: dan 2016-12-15 06:01:40
Context
2016-12-16
16:13
Merge the pragma-as-vtab change into this branch. check-in: 4ba45e72 user: dan tags: fkey-missing-indexes
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
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/shell.c.

3591
3592
3593
3594
3595
3596
3597
3598



3599
3600
3601
3602
3603
3604
3605
....
3615
3616
3617
3618
3619
3620
3621


3622
3623

3624
3625
3626










3627

3628
3629

3630
3631
3632
3633
3634
3635
3636
....
3638
3639
3640
3641
3642
3643
3644



3645
3646
3647

3648
3649
3650
3651
3652
3653
3654
3655

3656
3657
3658
3659
3660
3661
3662
3663
3664
3665
3666
3667
3668










3669
3670
3671
3672
3673
3674
3675
3676


3677
3678
3679
3680
3681
3682
3683
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;




  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) || '=?' || "
................................................................................
    "     )) || ')'"
    ", "
    "     '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), ', ')"
    "  || ');'"



    "FROM pragma_foreign_key_list AS o GROUP BY child, id"

  ;

  if( nArg>2 ){










    raw_printf(stderr, "Usage: .fkey_lint ?verbose-flag?\n");

  }
  if( nArg==2 ) bVerbose = booleanValue(azArg[1]);


  /* 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,
................................................................................
    );
  }


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




  if( rc==SQLITE_OK ){
    int rc2;

    while( SQLITE_ROW==sqlite3_step(pSql) ){
      int res = -1;
      sqlite3_stmt *pExplain = 0;
      const char *zEQP = (const char*)sqlite3_column_text(pSql, 0);
      const char *zGlob = (const char*)sqlite3_column_text(pSql, 1);
      const char *zFrom = (const char*)sqlite3_column_text(pSql, 2);
      const char *zTarget = (const char*)sqlite3_column_text(pSql, 3);
      const char *zCI = (const char*)sqlite3_column_text(pSql, 4);


      rc = sqlite3_prepare_v2(db, zEQP, -1, &pExplain, 0);
      if( rc!=SQLITE_OK ) break;
      if( SQLITE_ROW==sqlite3_step(pExplain) ){
        const char *zPlan = (const char*)sqlite3_column_text(pExplain, 3);
        res = (0==sqlite3_strglob(zGlob, zPlan));
      }
      rc = sqlite3_finalize(pExplain);
      if( rc!=SQLITE_OK ) break;

      if( res<0 ){
        raw_printf(stderr, "Error: internal error");
        break;










      }else if( res==0 ){
        raw_printf(out, "%s --> %s\n", zCI, zTarget);
      }else if( bVerbose ){
        raw_printf(out, "/* no extra indexes required for %s -> %s */\n", 
            zFrom, zTarget
        );
      }
    }



    if( rc!=SQLITE_OK ){
      raw_printf(stderr, "%s\n", sqlite3_errmsg(db));
    }

    rc2 = sqlite3_finalize(pSql);
    if( rc==SQLITE_OK && rc2!=SQLITE_OK ){







|
>
>
>







 







>
>

|
>


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







 







>
>
>



>








>













>
>
>
>
>
>
>
>
>
>
|
|
|
|
|
|
|
|
>
>







3591
3592
3593
3594
3595
3596
3597
3598
3599
3600
3601
3602
3603
3604
3605
3606
3607
3608
....
3618
3619
3620
3621
3622
3623
3624
3625
3626
3627
3628
3629
3630
3631

3632
3633
3634
3635
3636
3637
3638
3639
3640
3641
3642
3643
3644

3645
3646
3647
3648
3649
3650
3651
3652
....
3654
3655
3656
3657
3658
3659
3660
3661
3662
3663
3664
3665
3666
3667
3668
3669
3670
3671
3672
3673
3674
3675
3676
3677
3678
3679
3680
3681
3682
3683
3684
3685
3686
3687
3688
3689
3690
3691
3692
3693
3694
3695
3696
3697
3698
3699
3700
3701
3702
3703
3704
3705
3706
3707
3708
3709
3710
3711
3712
3713
3714
3715
3716
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) || '=?' || "
................................................................................
    "     )) || ')'"
    ", "
    "     '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;
    }
    else if( n>1 && sqlite3_strnicmp("-groupbyparent", azArg[i], n)==0 ){
      bGroupByParent = 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,
................................................................................
    );
  }


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

  if( rc==SQLITE_OK ){
    int rc2;
    char *zPrev = 0;
    while( SQLITE_ROW==sqlite3_step(pSql) ){
      int res = -1;
      sqlite3_stmt *pExplain = 0;
      const char *zEQP = (const char*)sqlite3_column_text(pSql, 0);
      const char *zGlob = (const char*)sqlite3_column_text(pSql, 1);
      const char *zFrom = (const char*)sqlite3_column_text(pSql, 2);
      const char *zTarget = (const char*)sqlite3_column_text(pSql, 3);
      const char *zCI = (const char*)sqlite3_column_text(pSql, 4);
      const char *zParent = (const char*)sqlite3_column_text(pSql, 5);

      rc = sqlite3_prepare_v2(db, zEQP, -1, &pExplain, 0);
      if( rc!=SQLITE_OK ) break;
      if( SQLITE_ROW==sqlite3_step(pExplain) ){
        const char *zPlan = (const char*)sqlite3_column_text(pExplain, 3);
        res = (0==sqlite3_strglob(zGlob, zPlan));
      }
      rc = sqlite3_finalize(pExplain);
      if( rc!=SQLITE_OK ) break;

      if( res<0 ){
        raw_printf(stderr, "Error: internal error");
        break;
      }else{
        if( bGroupByParent 
        && (bVerbose || res==0)
        && (zPrev==0 || sqlite3_stricmp(zParent, zPrev)) 
        ){
          raw_printf(out, "-- Parent table %s\n", zParent);
          sqlite3_free(zPrev);
          zPrev = sqlite3_mprintf("%s", zParent);
        }

        if( res==0 ){
          raw_printf(out, "%s%s --> %s\n", zIndent, zCI, zTarget);
        }else if( bVerbose ){
          raw_printf(out, "%s/* no extra indexes required for %s -> %s */\n", 
              zIndent, zFrom, zTarget
          );
        }
      }
    }
    sqlite3_free(zPrev);

    if( rc!=SQLITE_OK ){
      raw_printf(stderr, "%s\n", sqlite3_errmsg(db));
    }

    rc2 = sqlite3_finalize(pSql);
    if( rc==SQLITE_OK && rc2!=SQLITE_OK ){