/ 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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/shell.c.in.

  3891   3891   
  3892   3892     /*
  3893   3893     ** This SELECT statement returns one row for each foreign key constraint
  3894   3894     ** in the schema of the main database. The column values are:
  3895   3895     **
  3896   3896     ** 0. The text of an SQL statement similar to:
  3897   3897     **
  3898         -  **      "EXPLAIN QUERY PLAN SELECT rowid FROM child_table WHERE child_key=?"
         3898  +  **      "EXPLAIN QUERY PLAN SELECT 1 FROM child_table WHERE child_key=?"
  3899   3899     **
  3900         -  **    This is the same SELECT that the foreign keys implementation needs
  3901         -  **    to run internally on child tables. If there is an index that can
         3900  +  **    This SELECT is similar to the one that the foreign keys implementation
         3901  +  **    needs to run internally on child tables. If there is an index that can
  3902   3902     **    be used to optimize this query, then it can also be used by the FK
  3903   3903     **    implementation to optimize DELETE or UPDATE statements on the parent
  3904   3904     **    table.
  3905   3905     **
  3906   3906     ** 1. A GLOB pattern suitable for sqlite3_strglob(). If the plan output by
  3907   3907     **    the EXPLAIN QUERY PLAN command matches this pattern, then the schema
  3908   3908     **    contains an index that can be used to optimize the query.
................................................................................
  3922   3922     **
  3923   3923     ** 5. The name of the parent table.
  3924   3924     **
  3925   3925     ** These six values are used by the C logic below to generate the report.
  3926   3926     */
  3927   3927     const char *zSql =
  3928   3928     "SELECT "
  3929         -    "     'EXPLAIN QUERY PLAN SELECT rowid FROM ' || quote(s.name) || ' WHERE '"
         3929  +    "     'EXPLAIN QUERY PLAN SELECT 1 FROM ' || quote(s.name) || ' WHERE '"
  3930   3930       "  || group_concat(quote(s.name) || '.' || quote(f.[from]) || '=?' "
  3931   3931       "  || fkey_collate_clause("
  3932   3932       "       f.[table], COALESCE(f.[to], p.[name]), s.name, f.[from]),' AND ')"
  3933   3933       ", "
  3934   3934       "     'SEARCH TABLE ' || s.name || ' USING COVERING INDEX*('"
  3935   3935       "  || group_concat('*=?', ' AND ') || ')'"
  3936   3936       ", "

Changes to test/shell6.test.

    88     88     }
    89     89   
    90     90     9 {
    91     91       CREATE TABLE p1(a, b UNIQUE);
    92     92       CREATE TABLE c1(x INTEGER PRIMARY KEY REFERENCES p1(b));
    93     93     } {
    94     94     }
           95  +
           96  +  10 {
           97  +    CREATE TABLE parent (id INTEGER PRIMARY KEY); 
           98  +    CREATE TABLE child2 (id INT PRIMARY KEY, parentID INT REFERENCES parent) 
           99  +      WITHOUT ROWID;
          100  +  } {
          101  +    CREATE INDEX 'child2_parentID' ON 'child2'('parentID'); --> parent(id)
          102  +  }
    95    103   
    96    104   } {
    97    105     forcedelete test.db
    98    106     sqlite3 db test.db
    99    107     execsql $schema
   100    108   
   101    109     set expected ""