/ Check-in [5771b1d6]
Login

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

Overview
Comment:Fix the ".lint fkey-indexes" shell command so that it works with WITHOUT ROWID tables.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 5771b1d611b3562ea6c040f3f893073c4d0ee58c35b37ae211520d9aee8ed547
User & Date: dan 2017-12-01 18:40:18
Context
2017-12-05
14:58
Fix a compilation problem when both SQLITE_ENABLE_MULTITHREADED_CHECKS and SQLITE_ENABLE_API_ARMOUR are defined. check-in: 0342ce51 user: dan tags: trunk
2017-12-01
18:40
Fix the ".lint fkey-indexes" shell command so that it works with WITHOUT ROWID tables. check-in: 5771b1d6 user: dan tags: trunk
2017-11-30
11:21
Fix some minor problems in test scripts. check-in: 4c551fde user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to src/shell.c.in.

3891
3892
3893
3894
3895
3896
3897
3898
3899
3900
3901
3902
3903
3904
3905
3906
3907
3908
....
3922
3923
3924
3925
3926
3927
3928
3929
3930
3931
3932
3933
3934
3935
3936

  /*
  ** 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.
................................................................................
  **
  ** 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], COALESCE(f.[to], p.[name]), s.name, f.[from]),' AND ')"
    ", "
    "     'SEARCH TABLE ' || s.name || ' USING COVERING INDEX*('"
    "  || group_concat('*=?', ' AND ') || ')'"
    ", "







|

|
|







 







|







3891
3892
3893
3894
3895
3896
3897
3898
3899
3900
3901
3902
3903
3904
3905
3906
3907
3908
....
3922
3923
3924
3925
3926
3927
3928
3929
3930
3931
3932
3933
3934
3935
3936

  /*
  ** 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 1 FROM child_table WHERE child_key=?"
  **
  **    This SELECT is similar to the one 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.
................................................................................
  **
  ** 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 1 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 ') || ')'"
    ", "

Changes to test/shell6.test.

88
89
90
91
92
93
94








95
96
97
98
99
100
101
  }

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







>
>
>
>
>
>
>
>







88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
  }

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

  10 {
    CREATE TABLE parent (id INTEGER PRIMARY KEY); 
    CREATE TABLE child2 (id INT PRIMARY KEY, parentID INT REFERENCES parent) 
      WITHOUT ROWID;
  } {
    CREATE INDEX 'child2_parentID' ON 'child2'('parentID'); --> parent(id)
  }

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

  set expected ""