/ Check-in [ddeea5ab]
Login

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

Overview
Comment:Disable the transfer optimization if the destination table contains any foreign key constraint and foreign key constraints are enabled. Ticket [6284df89debdf].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:ddeea5ab5f6c0c4a86cdfbbb9f24d9d54bf8d301
User & Date: drh 2011-04-24 22:56:07
Context
2011-04-25
18:49
Add support for on conflict clauses to fts3/fts4. check-in: 6d2633a6 user: dan tags: vtab-conflict
18:01
Invoke the unix open() system call through a wrapper to avoid problems resulting from differing declarations to that function in various systems. check-in: 4c7ff4dd user: drh tags: trunk
2011-04-24
22:56
Disable the transfer optimization if the destination table contains any foreign key constraint and foreign key constraints are enabled. Ticket [6284df89debdf]. check-in: ddeea5ab user: drh tags: trunk
2011-04-22
22:55
Add the "getlock" utility for determining if a database file (on unix) is currently locked. check-in: 0ab24b13 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

  1729   1729         return 0;    /* pDestIdx has no corresponding index in pSrc */
  1730   1730       }
  1731   1731     }
  1732   1732   #ifndef SQLITE_OMIT_CHECK
  1733   1733     if( pDest->pCheck && sqlite3ExprCompare(pSrc->pCheck, pDest->pCheck) ){
  1734   1734       return 0;   /* Tables have different CHECK constraints.  Ticket #2252 */
  1735   1735     }
         1736  +#endif
         1737  +#ifndef SQLITE_OMIT_FOREIGN_KEY
         1738  +  /* Disallow the transfer optimization if the destination table constains
         1739  +  ** any foreign key constraints.  This is more restrictive than necessary.
         1740  +  ** But the main beneficiary of the transfer optimization is the VACUUM 
         1741  +  ** command, and the VACUUM command disables foreign key constraints.  So
         1742  +  ** the extra complication to make this rule less restrictive is probably
         1743  +  ** not worth the effort.  Ticket [6284df89debdfa61db8073e062908af0c9b6118e]
         1744  +  */
         1745  +  if( (pParse->db->flags & SQLITE_ForeignKeys)!=0 && pDest->pFKey!=0 ){
         1746  +    return 0;
         1747  +  }
  1736   1748   #endif
  1737   1749   
  1738   1750     /* If we get this far, it means either:
  1739   1751     **
  1740   1752     **    *   We can always do the transfer if the table contains an
  1741   1753     **        an integer primary key
  1742   1754     **

Changes to test/insert4.test.

   321    321       DROP TABLE t6b;
   322    322       CREATE TABLE t6b(x CHECK( x COLLATE nocase <>'abc' ));
   323    323     }
   324    324     catchsql {
   325    325       INSERT INTO t6b SELECT * FROM t6a;
   326    326     }
   327    327   } {1 {constraint failed}}
          328  +
          329  +# Ticket [6284df89debdfa61db8073e062908af0c9b6118e]
          330  +# Disable the xfer optimization if the destination table contains
          331  +# a foreign key constraint
          332  +#
          333  +ifcapable foreignkey {
          334  +  do_test insert4-7.1 {
          335  +    set ::sqlite3_xferopt_count 0
          336  +    execsql {
          337  +      CREATE TABLE t7a(x INTEGER PRIMARY KEY); INSERT INTO t7a VALUES(123);
          338  +      CREATE TABLE t7b(y INTEGER REFERENCES t7a);
          339  +      CREATE TABLE t7c(z INT);  INSERT INTO t7c VALUES(234);
          340  +      INSERT INTO t7b SELECT * FROM t7c;
          341  +      SELECT * FROM t7b;
          342  +    }
          343  +  } {234}
          344  +  do_test insert4-7.2 {
          345  +    set ::sqlite3_xferopt_count
          346  +  } {1}
          347  +  do_test insert4-7.3 {
          348  +    set ::sqlite3_xferopt_count 0
          349  +    execsql {
          350  +      DELETE FROM t7b;
          351  +      PRAGMA foreign_keys=ON;
          352  +    }
          353  +    catchsql {
          354  +      INSERT INTO t7b SELECT * FROM t7c;
          355  +    }
          356  +  } {1 {foreign key constraint failed}}
          357  +  do_test insert4-7.4 {
          358  +    execsql {SELECT * FROM t7b}
          359  +  } {}
          360  +  do_test insert4-7.5 {
          361  +    set ::sqlite3_xferopt_count
          362  +  } {0}
          363  +  do_test insert4-7.6 {
          364  +    set ::sqlite3_xferopt_count 0
          365  +    execsql {
          366  +      DELETE FROM t7b; DELETE FROM t7c;
          367  +      INSERT INTO t7c VALUES(123);
          368  +      INSERT INTO t7b SELECT * FROM t7c;
          369  +      SELECT * FROM t7b;
          370  +    }
          371  +  } {123}
          372  +  do_test insert4-7.7 {
          373  +    set ::sqlite3_xferopt_count
          374  +  } {0}
          375  +  do_test insert4-7.7 {
          376  +    set ::sqlite3_xferopt_count 0
          377  +    execsql {
          378  +      PRAGMA foreign_keys=OFF;
          379  +      DELETE FROM t7b;
          380  +      INSERT INTO t7b SELECT * FROM t7c;
          381  +      SELECT * FROM t7b;
          382  +    }
          383  +  } {123}
          384  +  do_test insert4-7.8 {
          385  +    set ::sqlite3_xferopt_count
          386  +  } {1}
          387  +}
   328    388   
   329    389   finish_test