/ Check-in [94689e3b]
Login

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

Overview
Comment:Add the ".lint fkey-indexes" command to the command-line shell.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 94689e3bdac2eabbcf1a51d741c2604ed4bd8a40
User & Date: drh 2016-12-16 18:43:49
Context
2016-12-17
08:18
Fix a problem in the shell tools readfile() command causing blobs to be truncated at the first embedded 0x00 byte in release builds, or an assert() to fail in a debug build. check-in: 8dedd6ad user: dan tags: trunk
2016-12-16
21:29
Merge recent enhancements from trunk, and especially the pragma-as-vtab change. check-in: 4b1e7804 user: drh tags: apple-osx
21:15
Merge the pragma-as-vtab enhancement from trunk. check-in: 89250777 user: drh tags: est_count_pragma
18:43
Add the ".lint fkey-indexes" command to the command-line shell. check-in: 94689e3b user: drh tags: trunk
18:41
Fix minor issues with the ".lint" command implemention in the shell and the shell help text related to ".lint". Closed-Leaf check-in: 1268dc77 user: drh tags: fkey-missing-indexes
18:14
Built-in PRAGMA statements without side-effects can be invoked as table-valued functions by prefixing their name with "pragma_". check-in: d66ec5cf user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/shell.c.

  2195   2195     ".indexes ?TABLE?       Show names of all indexes\n"
  2196   2196     "                         If TABLE specified, only show indexes for tables\n"
  2197   2197     "                         matching LIKE pattern TABLE.\n"
  2198   2198   #ifdef SQLITE_ENABLE_IOTRACE
  2199   2199     ".iotrace FILE          Enable I/O diagnostic logging to FILE\n"
  2200   2200   #endif
  2201   2201     ".limit ?LIMIT? ?VAL?   Display or change the value of an SQLITE_LIMIT\n"
         2202  +  ".lint OPTIONS          Report potential schema issues. Options:\n"
         2203  +  "                         fkey-indexes     Find missing foreign key indexes\n"
  2202   2204   #ifndef SQLITE_OMIT_LOAD_EXTENSION
  2203   2205     ".load FILE ?ENTRY?     Load an extension library\n"
  2204   2206   #endif
  2205   2207     ".log FILE|off          Turn logging on or off.  FILE can be stderr/stdout\n"
  2206   2208     ".mode MODE ?TABLE?     Set output mode where MODE is one of:\n"
  2207   2209     "                         ascii    Columns/rows delimited by 0x1F and 0x1E\n"
  2208   2210     "                         csv      Comma-separated values\n"
................................................................................
  3248   3250     rc = _wunlink(z);
  3249   3251     sqlite3_free(z);
  3250   3252   #else
  3251   3253     rc = unlink(zFilename);
  3252   3254   #endif
  3253   3255     return rc;
  3254   3256   }
         3257  +
         3258  +
         3259  +/*
         3260  +** The implementation of SQL scalar function fkey_collate_clause(), used
         3261  +** by the ".lint fkey-indexes" command. This scalar function is always
         3262  +** called with four arguments - the parent table name, the parent column name,
         3263  +** the child table name and the child column name.
         3264  +**
         3265  +**   fkey_collate_clause('parent-tab', 'parent-col', 'child-tab', 'child-col')
         3266  +**
         3267  +** If either of the named tables or columns do not exist, this function
         3268  +** returns an empty string. An empty string is also returned if both tables 
         3269  +** and columns exist but have the same default collation sequence. Or,
         3270  +** if both exist but the default collation sequences are different, this
         3271  +** function returns the string " COLLATE <parent-collation>", where
         3272  +** <parent-collation> is the default collation sequence of the parent column.
         3273  +*/
         3274  +static void shellFkeyCollateClause(
         3275  +  sqlite3_context *pCtx, 
         3276  +  int nVal, 
         3277  +  sqlite3_value **apVal
         3278  +){
         3279  +  sqlite3 *db = sqlite3_context_db_handle(pCtx);
         3280  +  const char *zParent;
         3281  +  const char *zParentCol;
         3282  +  const char *zParentSeq;
         3283  +  const char *zChild;
         3284  +  const char *zChildCol;
         3285  +  const char *zChildSeq;
         3286  +  int rc;
         3287  +  
         3288  +  assert( nVal==4 );
         3289  +  zParent = (const char*)sqlite3_value_text(apVal[0]);
         3290  +  zParentCol = (const char*)sqlite3_value_text(apVal[1]);
         3291  +  zChild = (const char*)sqlite3_value_text(apVal[2]);
         3292  +  zChildCol = (const char*)sqlite3_value_text(apVal[3]);
         3293  +
         3294  +  sqlite3_result_text(pCtx, "", -1, SQLITE_STATIC);
         3295  +  rc = sqlite3_table_column_metadata(
         3296  +      db, "main", zParent, zParentCol, 0, &zParentSeq, 0, 0, 0
         3297  +  );
         3298  +  if( rc==SQLITE_OK ){
         3299  +    rc = sqlite3_table_column_metadata(
         3300  +        db, "main", zChild, zChildCol, 0, &zChildSeq, 0, 0, 0
         3301  +    );
         3302  +  }
         3303  +
         3304  +  if( rc==SQLITE_OK && sqlite3_stricmp(zParentSeq, zChildSeq) ){
         3305  +    char *z = sqlite3_mprintf(" COLLATE %s", zParentSeq);
         3306  +    sqlite3_result_text(pCtx, z, -1, SQLITE_TRANSIENT);
         3307  +    sqlite3_free(z);
         3308  +  }
         3309  +}
         3310  +
         3311  +
         3312  +/*
         3313  +** The implementation of dot-command ".lint fkey-indexes".
         3314  +*/
         3315  +static int lintFkeyIndexes(
         3316  +  ShellState *pState,             /* Current shell tool state */
         3317  +  char **azArg,                   /* Array of arguments passed to dot command */
         3318  +  int nArg                        /* Number of entries in azArg[] */
         3319  +){
         3320  +  sqlite3 *db = pState->db;       /* Database handle to query "main" db of */
         3321  +  FILE *out = pState->out;        /* Stream to write non-error output to */
         3322  +  int bVerbose = 0;               /* If -verbose is present */
         3323  +  int bGroupByParent = 0;         /* If -groupbyparent is present */
         3324  +  int i;                          /* To iterate through azArg[] */
         3325  +  const char *zIndent = "";       /* How much to indent CREATE INDEX by */
         3326  +  int rc;                         /* Return code */
         3327  +  sqlite3_stmt *pSql = 0;         /* Compiled version of SQL statement below */
         3328  +
         3329  +  /*
         3330  +  ** This SELECT statement returns one row for each foreign key constraint
         3331  +  ** in the schema of the main database. The column values are:
         3332  +  **
         3333  +  ** 0. The text of an SQL statement similar to:
         3334  +  **
         3335  +  **      "EXPLAIN QUERY PLAN SELECT rowid FROM child_table WHERE child_key=?"
         3336  +  **
         3337  +  **    This is the same SELECT that the foreign keys implementation needs
         3338  +  **    to run internally on child tables. If there is an index that can
         3339  +  **    be used to optimize this query, then it can also be used by the FK
         3340  +  **    implementation to optimize DELETE or UPDATE statements on the parent
         3341  +  **    table.
         3342  +  **
         3343  +  ** 1. A GLOB pattern suitable for sqlite3_strglob(). If the plan output by
         3344  +  **    the EXPLAIN QUERY PLAN command matches this pattern, then the schema
         3345  +  **    contains an index that can be used to optimize the query.
         3346  +  **
         3347  +  ** 2. Human readable text that describes the child table and columns. e.g.
         3348  +  **
         3349  +  **       "child_table(child_key1, child_key2)"
         3350  +  **
         3351  +  ** 3. Human readable text that describes the parent table and columns. e.g.
         3352  +  **
         3353  +  **       "parent_table(parent_key1, parent_key2)"
         3354  +  **
         3355  +  ** 4. A full CREATE INDEX statement for an index that could be used to
         3356  +  **    optimize DELETE or UPDATE statements on the parent table. e.g.
         3357  +  **
         3358  +  **       "CREATE INDEX child_table_child_key ON child_table(child_key)"
         3359  +  **
         3360  +  ** 5. The name of the parent table.
         3361  +  **
         3362  +  ** These six values are used by the C logic below to generate the report.
         3363  +  */
         3364  +  const char *zSql =
         3365  +  "SELECT "
         3366  +    "     'EXPLAIN QUERY PLAN SELECT rowid FROM ' || quote(s.name) || ' WHERE '"
         3367  +    "  || group_concat(quote(s.name) || '.' || quote(f.[from]) || '=?' "
         3368  +    "  || fkey_collate_clause(f.[table], f.[to], s.name, f.[from]),' AND ')"
         3369  +    ", "
         3370  +    "     'SEARCH TABLE ' || s.name || ' USING COVERING INDEX*('"
         3371  +    "  || group_concat('*=?', ' AND ') || ')'"
         3372  +    ", "
         3373  +    "     s.name  || '(' || group_concat(f.[from],  ', ') || ')'"
         3374  +    ", "
         3375  +    "     f.[table] || '(' || group_concat(COALESCE(f.[to], "
         3376  +    "       (SELECT name FROM pragma_table_info(f.[table]) WHERE pk=seq+1)"
         3377  +    "     )) || ')'"
         3378  +    ", "
         3379  +    "     'CREATE INDEX ' || quote(s.name ||'_'|| group_concat(f.[from], '_'))"
         3380  +    "  || ' ON ' || quote(s.name) || '('"
         3381  +    "  || group_concat(quote(f.[from]) ||"
         3382  +    "        fkey_collate_clause(f.[table], f.[to], s.name, f.[from]), ', ')"
         3383  +    "  || ');'"
         3384  +    ", "
         3385  +    "     f.[table] "
         3386  +
         3387  +    "FROM sqlite_master AS s, pragma_foreign_key_list(s.name) AS f "
         3388  +    "GROUP BY s.name, f.id "
         3389  +    "ORDER BY (CASE WHEN ? THEN f.[table] ELSE s.name END)"
         3390  +  ;
         3391  +
         3392  +  for(i=2; i<nArg; i++){
         3393  +    int n = strlen(azArg[i]);
         3394  +    if( n>1 && sqlite3_strnicmp("-verbose", azArg[i], n)==0 ){
         3395  +      bVerbose = 1;
         3396  +    }
         3397  +    else if( n>1 && sqlite3_strnicmp("-groupbyparent", azArg[i], n)==0 ){
         3398  +      bGroupByParent = 1;
         3399  +      zIndent = "    ";
         3400  +    }
         3401  +    else{
         3402  +      raw_printf(stderr, "Usage: %s %s ?-verbose? ?-groupbyparent?\n",
         3403  +          azArg[0], azArg[1]
         3404  +      );
         3405  +      return SQLITE_ERROR;
         3406  +    }
         3407  +  }
         3408  +  
         3409  +  /* Register the fkey_collate_clause() SQL function */
         3410  +  rc = sqlite3_create_function(db, "fkey_collate_clause", 4, SQLITE_UTF8,
         3411  +      0, shellFkeyCollateClause, 0, 0
         3412  +  );
         3413  +
         3414  +
         3415  +  if( rc==SQLITE_OK ){
         3416  +    rc = sqlite3_prepare_v2(db, zSql, -1, &pSql, 0);
         3417  +  }
         3418  +  if( rc==SQLITE_OK ){
         3419  +    sqlite3_bind_int(pSql, 1, bGroupByParent);
         3420  +  }
         3421  +
         3422  +  if( rc==SQLITE_OK ){
         3423  +    int rc2;
         3424  +    char *zPrev = 0;
         3425  +    while( SQLITE_ROW==sqlite3_step(pSql) ){
         3426  +      int res = -1;
         3427  +      sqlite3_stmt *pExplain = 0;
         3428  +      const char *zEQP = (const char*)sqlite3_column_text(pSql, 0);
         3429  +      const char *zGlob = (const char*)sqlite3_column_text(pSql, 1);
         3430  +      const char *zFrom = (const char*)sqlite3_column_text(pSql, 2);
         3431  +      const char *zTarget = (const char*)sqlite3_column_text(pSql, 3);
         3432  +      const char *zCI = (const char*)sqlite3_column_text(pSql, 4);
         3433  +      const char *zParent = (const char*)sqlite3_column_text(pSql, 5);
         3434  +
         3435  +      rc = sqlite3_prepare_v2(db, zEQP, -1, &pExplain, 0);
         3436  +      if( rc!=SQLITE_OK ) break;
         3437  +      if( SQLITE_ROW==sqlite3_step(pExplain) ){
         3438  +        const char *zPlan = (const char*)sqlite3_column_text(pExplain, 3);
         3439  +        res = (0==sqlite3_strglob(zGlob, zPlan));
         3440  +      }
         3441  +      rc = sqlite3_finalize(pExplain);
         3442  +      if( rc!=SQLITE_OK ) break;
         3443  +
         3444  +      if( res<0 ){
         3445  +        raw_printf(stderr, "Error: internal error");
         3446  +        break;
         3447  +      }else{
         3448  +        if( bGroupByParent 
         3449  +        && (bVerbose || res==0)
         3450  +        && (zPrev==0 || sqlite3_stricmp(zParent, zPrev)) 
         3451  +        ){
         3452  +          raw_printf(out, "-- Parent table %s\n", zParent);
         3453  +          sqlite3_free(zPrev);
         3454  +          zPrev = sqlite3_mprintf("%s", zParent);
         3455  +        }
         3456  +
         3457  +        if( res==0 ){
         3458  +          raw_printf(out, "%s%s --> %s\n", zIndent, zCI, zTarget);
         3459  +        }else if( bVerbose ){
         3460  +          raw_printf(out, "%s/* no extra indexes required for %s -> %s */\n", 
         3461  +              zIndent, zFrom, zTarget
         3462  +          );
         3463  +        }
         3464  +      }
         3465  +    }
         3466  +    sqlite3_free(zPrev);
         3467  +
         3468  +    if( rc!=SQLITE_OK ){
         3469  +      raw_printf(stderr, "%s\n", sqlite3_errmsg(db));
         3470  +    }
         3471  +
         3472  +    rc2 = sqlite3_finalize(pSql);
         3473  +    if( rc==SQLITE_OK && rc2!=SQLITE_OK ){
         3474  +      rc = rc2;
         3475  +      raw_printf(stderr, "%s\n", sqlite3_errmsg(db));
         3476  +    }
         3477  +  }else{
         3478  +    raw_printf(stderr, "%s\n", sqlite3_errmsg(db));
         3479  +  }
         3480  +
         3481  +  return rc;
         3482  +}
         3483  +
         3484  +/*
         3485  +** Implementation of ".lint" dot command.
         3486  +*/
         3487  +static int lintDotCommand(
         3488  +  ShellState *pState,             /* Current shell tool state */
         3489  +  char **azArg,                   /* Array of arguments passed to dot command */
         3490  +  int nArg                        /* Number of entries in azArg[] */
         3491  +){
         3492  +  int n;
         3493  +  n = (nArg>=2 ? strlen(azArg[1]) : 0);
         3494  +  if( n<1 || sqlite3_strnicmp(azArg[1], "fkey-indexes", n) ) goto usage;
         3495  +  return lintFkeyIndexes(pState, azArg, nArg);
         3496  +
         3497  + usage:
         3498  +  raw_printf(stderr, "Usage %s sub-command ?switches...?\n", azArg[0]);
         3499  +  raw_printf(stderr, "Where sub-commands are:\n");
         3500  +  raw_printf(stderr, "    fkey-indexes\n");
         3501  +  return SQLITE_ERROR;
         3502  +}
         3503  +
  3255   3504   
  3256   3505   /*
  3257   3506   ** If an input line begins with "." then invoke this routine to
  3258   3507   ** process that line.
  3259   3508   **
  3260   3509   ** Return 1 on error, 2 to exit, and 0 otherwise.
  3261   3510   */
................................................................................
  4028   4277           sqlite3_limit(p->db, aLimit[iLimit].limitCode,
  4029   4278                         (int)integerValue(azArg[2]));
  4030   4279         }
  4031   4280         printf("%20s %d\n", aLimit[iLimit].zLimitName,
  4032   4281                sqlite3_limit(p->db, aLimit[iLimit].limitCode, -1));
  4033   4282       }
  4034   4283     }else
         4284  +
         4285  +  if( c=='l' && n>2 && strncmp(azArg[0], "lint", n)==0 ){
         4286  +    open_db(p, 0);
         4287  +    lintDotCommand(p, azArg, nArg);
         4288  +  }else
  4035   4289   
  4036   4290   #ifndef SQLITE_OMIT_LOAD_EXTENSION
  4037   4291     if( c=='l' && strncmp(azArg[0], "load", n)==0 ){
  4038   4292       const char *zFile, *zProc;
  4039   4293       char *zErrMsg = 0;
  4040   4294       if( nArg<2 ){
  4041   4295         raw_printf(stderr, "Usage: .load FILE ?ENTRYPOINT?\n");

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 [list .lint fkey-indexes]]
           88  +  } [list 0 [string trim $expected]]
           89  +
           90  +  do_test 1.$tn.2 {
           91  +    execsql [lindex $RES 1]
           92  +    catchcmd test.db [list .lint fkey-indexes]
           93  +  } {0 {}}
           94  +
           95  +  db close
           96  +}
           97  +
           98  +finish_test
           99  +
          100  +