/ Check-in [2c62ffcb]
Login

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

Overview
Comment:The XFER optimization works if the target table lacks an integer primary key and is not empty as long as it has no indices. It always has and continues to work if the target table was empty. (CVS 3779)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 2c62ffcb865655e8b91aaf81601548ea540c6088
User & Date: drh 2007-03-31 13:00:26
Context
2007-03-31
15:02
Improved test coverage for printf.c. (CVS 3780) check-in: c2badb20 user: drh tags: trunk
13:00
The XFER optimization works if the target table lacks an integer primary key and is not empty as long as it has no indices. It always has and continues to work if the target table was empty. (CVS 3779) check-in: 2c62ffcb user: drh tags: trunk
10:00
More coverage for pager.c. (CVS 3778) check-in: 665b119a user: danielk1977 tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/insert.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle INSERT statements in SQLite.
    14     14   **
    15         -** $Id: insert.c,v 1.181 2007/03/29 13:35:36 drh Exp $
           15  +** $Id: insert.c,v 1.182 2007/03/31 13:00:26 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** Set P3 of the most recently inserted opcode to a column affinity
    21     21   ** string for index pIdx. A column affinity string has one character
    22     22   ** for each column in the table, according to the affinity of the column:
................................................................................
  1500   1500   #endif
  1501   1501     iDbSrc = sqlite3SchemaToIndex(pParse->db, pSrc->pSchema);
  1502   1502     v = sqlite3GetVdbe(pParse);
  1503   1503     iSrc = pParse->nTab++;
  1504   1504     iDest = pParse->nTab++;
  1505   1505     counterMem = autoIncBegin(pParse, iDbDest, pDest);
  1506   1506     sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite);
  1507         -  if( pDest->iPKey<0 ){
  1508         -    /* The tables do not have an INTEGER PRIMARY KEY so that
  1509         -    ** transfer optimization is only allowed if the destination
  1510         -    ** table is initially empty
         1507  +  if( pDest->iPKey<0 && pDest->pIndex!=0 ){
         1508  +    /* If tables do not have an INTEGER PRIMARY KEY and there
         1509  +    ** are indices to be copied and the destination is not empty,
         1510  +    ** we have to disallow the transfer optimization because the
         1511  +    ** the rowids might change which will mess up indexing.
  1511   1512       */
  1512   1513       addr1 = sqlite3VdbeAddOp(v, OP_Rewind, iDest, 0);
  1513   1514       emptyDestTest = sqlite3VdbeAddOp(v, OP_Goto, 0, 0);
  1514   1515       sqlite3VdbeJumpHere(v, addr1);
  1515   1516     }else{
  1516   1517       emptyDestTest = 0;
  1517   1518     }
................................................................................
  1518   1519     sqlite3OpenTable(pParse, iSrc, iDbSrc, pSrc, OP_OpenRead);
  1519   1520     emptySrcTest = sqlite3VdbeAddOp(v, OP_Rewind, iSrc, 0);
  1520   1521     if( pDest->pIndex!=0 ){
  1521   1522       sqlite3VdbeAddOp(v, OP_Rowid, iSrc, 0);
  1522   1523       memRowid = pParse->nMem++;
  1523   1524       sqlite3VdbeAddOp(v, OP_MemStore, memRowid, pDest->iPKey>=0);
  1524   1525     }
  1525         -  addr1 = sqlite3VdbeAddOp(v, OP_Rowid, iSrc, 0);
  1526   1526     if( pDest->iPKey>=0 ){
         1527  +    addr1 = sqlite3VdbeAddOp(v, OP_Rowid, iSrc, 0);
  1527   1528       sqlite3VdbeAddOp(v, OP_Dup, 0, 0);
  1528   1529       addr2 = sqlite3VdbeAddOp(v, OP_NotExists, iDest, 0);
  1529   1530       sqlite3VdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError, 
  1530   1531                         "PRIMARY KEY must be unique", P3_STATIC);
  1531   1532       sqlite3VdbeJumpHere(v, addr2);
  1532   1533       autoIncStep(pParse, counterMem);
         1534  +  }else if( pDest->pIndex==0 ){
         1535  +    addr1 = sqlite3VdbeAddOp(v, OP_NewRowid, iDest, 0);
  1533   1536     }else{
         1537  +    addr1 = sqlite3VdbeAddOp(v, OP_Rowid, iSrc, 0);
  1534   1538       assert( pDest->autoInc==0 );
  1535   1539     }
  1536   1540     sqlite3VdbeAddOp(v, OP_RowData, iSrc, 0);
  1537   1541     sqlite3VdbeOp3(v, OP_Insert, iDest,
  1538   1542                       OPFLAG_NCHANGE|OPFLAG_LASTROWID|OPFLAG_APPEND,
  1539   1543                       pDest->zName, 0);
  1540   1544     sqlite3VdbeAddOp(v, OP_Next, iSrc, addr1);