/ Check-in [48826b22]
Login

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

Overview
Comment:Fix the ".lint fkey" shell command for cases where the child key is also an INTEGER PRIMARY KEY.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 48826b222c110a90996a84605318ea6b1e502b8c5129f4d561f8350dbdbcd264
User & Date: dan 2017-04-06 14:56:26
Context
2017-04-07
20:20
Use replace() instead of char() to quote newline and return characters in strings in the output of .dump, to avoid excess expression complexity. check-in: 73073529 user: drh tags: trunk
19:41
Proof of concept for the ability to use the expression columns in an index on expressions in place of equivalent expressions in the result set or in the WHERE clause. This check-in compiles but is mostly untested. check-in: a52ef2ad user: drh tags: covering-index-on-expr
11:45
Use replace() instead of char() to quote newline and return characters in strings in the output of .dump, to avoid excess expression complexity. Closed-Leaf check-in: 4c2b5729 user: drh tags: shell-fix
2017-04-06
14:56
Fix the ".lint fkey" shell command for cases where the child key is also an INTEGER PRIMARY KEY. check-in: 48826b22 user: dan tags: trunk
12:06
Fix the ".lint fkey" shell tool command so that it works for foreign keys that refer implicitly to primary key columns with non-BINARY default collation sequences. check-in: 327eff25 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/shell.c.

4350
4351
4352
4353
4354
4355
4356

4357
4358
4359
4360
4361
4362
4363
....
4398
4399
4400
4401
4402
4403
4404

4405


4406
4407
4408
4409
4410
4411
4412
    ", "
    "     f.[table] "
    "FROM sqlite_master AS s, pragma_foreign_key_list(s.name) AS f "
    "LEFT JOIN pragma_table_info AS p ON (pk-1=seq AND p.arg=f.[table]) "
    "GROUP BY s.name, f.id "
    "ORDER BY (CASE WHEN ? THEN f.[table] ELSE s.name END)"
  ;


  for(i=2; i<nArg; i++){
    int n = (int)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 ){
................................................................................
      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;







>







 







>
|
>
>







4350
4351
4352
4353
4354
4355
4356
4357
4358
4359
4360
4361
4362
4363
4364
....
4399
4400
4401
4402
4403
4404
4405
4406
4407
4408
4409
4410
4411
4412
4413
4414
4415
4416
    ", "
    "     f.[table] "
    "FROM sqlite_master AS s, pragma_foreign_key_list(s.name) AS f "
    "LEFT JOIN pragma_table_info AS p ON (pk-1=seq AND p.arg=f.[table]) "
    "GROUP BY s.name, f.id "
    "ORDER BY (CASE WHEN ? THEN f.[table] ELSE s.name END)"
  ;
  const char *zGlobIPK = "SEARCH TABLE * USING INTEGER PRIMARY KEY (rowid=?)";

  for(i=2; i<nArg; i++){
    int n = (int)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 ){
................................................................................
      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)
           || 0==sqlite3_strglob(zGlobIPK, zPlan)
        );
      }
      rc = sqlite3_finalize(pExplain);
      if( rc!=SQLITE_OK ) break;

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

Changes to test/shell6.test.

82
83
84
85
86
87
88






89
90
91
92
93
94
95

  8 {
    CREATE TABLE x1(a, b COLLATE nocase, c COLLATE rtrim, PRIMARY KEY(c, b, a));
    CREATE TABLE y1(d, e, f, FOREIGN KEY(d, e, f) REFERENCES x1);
  } {
    CREATE INDEX 'y1_d_e_f' ON 'y1'('d' COLLATE rtrim, 'e' COLLATE nocase, 'f'); --> x1(c,b,a)
  }







} {
  forcedelete test.db
  sqlite3 db test.db
  execsql $schema

  set expected ""







>
>
>
>
>
>







82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101

  8 {
    CREATE TABLE x1(a, b COLLATE nocase, c COLLATE rtrim, PRIMARY KEY(c, b, a));
    CREATE TABLE y1(d, e, f, FOREIGN KEY(d, e, f) REFERENCES x1);
  } {
    CREATE INDEX 'y1_d_e_f' ON 'y1'('d' COLLATE rtrim, 'e' COLLATE nocase, 'f'); --> x1(c,b,a)
  }

  9 {
    CREATE TABLE p1(a, b UNIQUE);
    CREATE TABLE c1(x INTEGER PRIMARY KEY REFERENCES p1(b));
  } {
  }

} {
  forcedelete test.db
  sqlite3 db test.db
  execsql $schema

  set expected ""