SQLite

Check-in [327eff25]
Login

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

Overview
Comment: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.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 327eff25ba2420267cc8dc49dd3c3aab45f4bf9e060d1ad480e25d016d21f3ba
User & Date: dan 2017-04-06 12:06:56
Context
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)
2017-04-05
13:44
Remove a confusing and pointless sentence from the documentation for the sqlite3_interrupt() interface. (check-in: c5f1a2b6 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/shell.c.

︙︙
4327
4328
4329
4330
4331
4332
4333
4334

4335
4336
4337
4338
4339
4340
4341
4342
4343
4344
4345
4346
4347
4348

4349
4350
4351
4352
4353

4354
4355
4356
4357
4358
4359
4360

  **
  ** 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=2; i<nArg; i++){
    int n = (int)strlen(azArg[i]);
    if( n>1 && sqlite3_strnicmp("-verbose", azArg[i], n)==0 ){








|
>






|
<
<




|
>



<

>








4327
4328
4329
4330
4331
4332
4333
4334
4335
4336
4337
4338
4339
4340
4341
4342


4343
4344
4345
4346
4347
4348
4349
4350
4351

4352
4353
4354
4355
4356
4357
4358
4359
4360

  **
  ** 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], COALESCE(f.[to], p.[name]), 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], p.[name])) || ')'"


    ", "
    "     'CREATE INDEX ' || quote(s.name ||'_'|| group_concat(f.[from], '_'))"
    "  || ' ON ' || quote(s.name) || '('"
    "  || group_concat(quote(f.[from]) ||"
    "        fkey_collate_clause("
    "          f.[table], COALESCE(f.[to], p.[name]), s.name, f.[from]), ', ')"
    "  || ');'"
    ", "
    "     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 ){
︙︙

Changes to test/shell6.test.

︙︙
68
69
70
71
72
73
74














75
76
77
78
79
80
81


  6 {
    CREATE TABLE x1(a COLLATE nocase, b, UNIQUE(a));
    CREATE TABLE y1(a COLLATE rtrim REFERENCES x1(a));
  } {
    CREATE INDEX 'y1_a' ON 'y1'('a' COLLATE nocase); --> x1(a)
  }















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

  set expected ""








>
>
>
>
>
>
>
>
>
>
>
>
>
>








68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95


  6 {
    CREATE TABLE x1(a COLLATE nocase, b, UNIQUE(a));
    CREATE TABLE y1(a COLLATE rtrim REFERENCES x1(a));
  } {
    CREATE INDEX 'y1_a' ON 'y1'('a' COLLATE nocase); --> x1(a)
  }

  7 {
    CREATE TABLE x1(a PRIMARY KEY COLLATE nocase, b);
    CREATE TABLE y1(a REFERENCES x1);
  } {
    CREATE INDEX 'y1_a' ON 'y1'('a' COLLATE nocase); --> x1(a)
  }

  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 ""
︙︙