/ Check-in [9f56a878]
Login

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

Overview
Comment:Add a new algorithm for handling INSERT which reduces fragmentation on a VACUUM. Ticket #2075. More testing needed. (CVS 3643)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9f56a878cbbc715262b3a48ee696148dbd7bf1d2
User & Date: drh 2007-02-13 15:01:11
References
2014-04-26
12:36 Ticket [f67b4138] "INSERT INTO tbl SELECT * FROM tbl2" statement may modify data if ALTER TABLE ADD COLUMN has been used on tbl2. status still Open with 6 other changes artifact: a5228e13 user: dan
Context
2007-02-14
09:19
Use OP_VColumn instead of OP_Column when querying virtual tables for values to save in aggregate context records. #2230. (CVS 3644) check-in: cb78f7cb user: danielk1977 tags: trunk
2007-02-13
15:01
Add a new algorithm for handling INSERT which reduces fragmentation on a VACUUM. Ticket #2075. More testing needed. (CVS 3643) check-in: 9f56a878 user: drh tags: trunk
14:11
Changes to the script that generates download.html so that it recognizes FTS2 modules. (CVS 3642) check-in: 06c22de2 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace 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.172 2006/08/29 18:46:14 drh Exp $
           15  +** $Id: insert.c,v 1.173 2007/02/13 15:01:11 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:
................................................................................
   114    114       }else{
   115    115         if( pItem->pTab->pSchema==pSchema && pItem->pTab->tnum==iTab ) return 1;
   116    116       }
   117    117     }
   118    118     return 0;
   119    119   }
   120    120   
          121  +#ifndef SQLITE_OMIT_AUTOINCREMENT
          122  +/*
          123  +** Write out code to initialize the autoincrement logic.  This code
          124  +** looks up the current autoincrement value in the sqlite_sequence
          125  +** table and stores that value in a memory cell.  Code generated by
          126  +** autoIncStep() will keep that memory cell holding the largest
          127  +** rowid value.  Code generated by autoIncEnd() will write the new
          128  +** largest value of the counter back into the sqlite_sequence table.
          129  +**
          130  +** This routine returns the index of the mem[] cell that contains
          131  +** the maximum rowid counter.
          132  +**
          133  +** Two memory cells are allocated.  The next memory cell after the
          134  +** one returned holds the rowid in sqlite_sequence where we will
          135  +** write back the revised maximum rowid.
          136  +*/
          137  +static int autoIncBegin(
          138  +  Parse *pParse,      /* Parsing context */
          139  +  int iDb,            /* Index of the database holding pTab */
          140  +  Table *pTab         /* The table we are writing to */
          141  +){
          142  +  int memId = 0;
          143  +  if( pTab->autoInc ){
          144  +    Vdbe *v = pParse->pVdbe;
          145  +    Db *pDb = &pParse->db->aDb[iDb];
          146  +    int iCur = pParse->nTab;
          147  +    int addr;
          148  +    assert( v );
          149  +    addr = sqlite3VdbeCurrentAddr(v);
          150  +    memId = pParse->nMem+1;
          151  +    pParse->nMem += 2;
          152  +    sqlite3OpenTable(pParse, iCur, iDb, pDb->pSchema->pSeqTab, OP_OpenRead);
          153  +    sqlite3VdbeAddOp(v, OP_Rewind, iCur, addr+13);
          154  +    sqlite3VdbeAddOp(v, OP_Column, iCur, 0);
          155  +    sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0);
          156  +    sqlite3VdbeAddOp(v, OP_Ne, 0x100, addr+12);
          157  +    sqlite3VdbeAddOp(v, OP_Rowid, iCur, 0);
          158  +    sqlite3VdbeAddOp(v, OP_MemStore, memId-1, 1);
          159  +    sqlite3VdbeAddOp(v, OP_Column, iCur, 1);
          160  +    sqlite3VdbeAddOp(v, OP_MemStore, memId, 1);
          161  +    sqlite3VdbeAddOp(v, OP_Goto, 0, addr+13);
          162  +    sqlite3VdbeAddOp(v, OP_Next, iCur, addr+4);
          163  +    sqlite3VdbeAddOp(v, OP_Close, iCur, 0);
          164  +  }
          165  +  return memId;
          166  +}
          167  +
          168  +/*
          169  +** Update the maximum rowid for an autoincrement calculation.
          170  +**
          171  +** This routine should be called when the top of the stack holds a
          172  +** new rowid that is about to be inserted.  If that new rowid is
          173  +** larger than the maximum rowid in the memId memory cell, then the
          174  +** memory cell is updated.  The stack is unchanged.
          175  +*/
          176  +static void autoIncStep(Parse *pParse, int memId){
          177  +  if( memId>0 ){
          178  +    sqlite3VdbeAddOp(pParse->pVdbe, OP_MemMax, memId, 0);
          179  +  }
          180  +}
          181  +
          182  +/*
          183  +** After doing one or more inserts, the maximum rowid is stored
          184  +** in mem[memId].  Generate code to write this value back into the
          185  +** the sqlite_sequence table.
          186  +*/
          187  +static void autoIncEnd(
          188  +  Parse *pParse,     /* The parsing context */
          189  +  int iDb,           /* Index of the database holding pTab */
          190  +  Table *pTab,       /* Table we are inserting into */
          191  +  int memId          /* Memory cell holding the maximum rowid */
          192  +){
          193  +  if( pTab->autoInc ){
          194  +    int iCur = pParse->nTab;
          195  +    Vdbe *v = pParse->pVdbe;
          196  +    Db *pDb = &pParse->db->aDb[iDb];
          197  +    int addr;
          198  +    assert( v );
          199  +    addr = sqlite3VdbeCurrentAddr(v);
          200  +    sqlite3OpenTable(pParse, iCur, iDb, pDb->pSchema->pSeqTab, OP_OpenWrite);
          201  +    sqlite3VdbeAddOp(v, OP_MemLoad, memId-1, 0);
          202  +    sqlite3VdbeAddOp(v, OP_NotNull, -1, addr+7);
          203  +    sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
          204  +    sqlite3VdbeAddOp(v, OP_NewRowid, iCur, 0);
          205  +    sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0);
          206  +    sqlite3VdbeAddOp(v, OP_MemLoad, memId, 0);
          207  +    sqlite3VdbeAddOp(v, OP_MakeRecord, 2, 0);
          208  +    sqlite3VdbeAddOp(v, OP_Insert, iCur, 0);
          209  +    sqlite3VdbeAddOp(v, OP_Close, iCur, 0);
          210  +  }
          211  +}
          212  +#else
          213  +/*
          214  +** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines
          215  +** above are all no-ops
          216  +*/
          217  +# define autoIncBegin(A,B,C) (0)
          218  +# define autoIncStep(A,B)
          219  +# define autoIncEnd(A,B,C,D)
          220  +#endif /* SQLITE_OMIT_AUTOINCREMENT */
          221  +
          222  +
          223  +/* Forward declaration */
          224  +static int xferOptimization(
          225  +  Parse *pParse,        /* Parser context */
          226  +  Table *pDest,         /* The table we are inserting into */
          227  +  Select *pSelect,      /* A SELECT statement to use as the data source */
          228  +  int onError,          /* How to handle constraint errors */
          229  +  int iDbDest           /* The database of pDest */
          230  +);
          231  +
   121    232   /*
   122    233   ** This routine is call to handle SQL of the following forms:
   123    234   **
   124    235   **    insert into TABLE (IDLIST) values(EXPRLIST)
   125    236   **    insert into TABLE (IDLIST) select
   126    237   **
   127    238   ** The IDLIST following the table name is always optional.  If omitted,
................................................................................
   129    240   ** appears in the pColumn parameter.  pColumn is NULL if IDLIST is omitted.
   130    241   **
   131    242   ** The pList parameter holds EXPRLIST in the first form of the INSERT
   132    243   ** statement above, and pSelect is NULL.  For the second form, pList is
   133    244   ** NULL and pSelect is a pointer to the select statement used to generate
   134    245   ** data for the insert.
   135    246   **
   136         -** The code generated follows one of three templates.  For a simple
          247  +** The code generated follows one of four templates.  For a simple
   137    248   ** select with data coming from a VALUES clause, the code executes
   138    249   ** once straight down through.  The template looks like this:
   139    250   **
   140    251   **         open write cursor to <table> and its indices
   141    252   **         puts VALUES clause expressions onto the stack
   142    253   **         write the resulting record into <table>
   143    254   **         cleanup
   144    255   **
   145         -** If the statement is of the form
          256  +** The three remaining templates assume the statement is of the form
   146    257   **
   147    258   **   INSERT INTO <table> SELECT ...
   148    259   **
   149         -** And the SELECT clause does not read from <table> at any time, then
   150         -** the generated code follows this template:
          260  +** If the SELECT clause is of the restricted form "SELECT * FROM <table2>" -
          261  +** in other words if the SELECT pulls all columns from a single table
          262  +** and there is no WHERE or LIMIT or GROUP BY or ORDER BY clauses, and
          263  +** if <table2> and <table1> are distinct tables but have identical
          264  +** schemas, including all the same indices, then a special optimization
          265  +** is invoked that copies raw records from <table2> over to <table1>.
          266  +** See the xferOptimization() function for the implementation of this
          267  +** template.  This is the second template.
          268  +**
          269  +**         open a write cursor to <table>
          270  +**         open read cursor on <table2>
          271  +**         transfer all records in <table2> over to <table>
          272  +**         close cursors
          273  +**         foreach index on <table>
          274  +**           open a write cursor on the <table> index
          275  +**           open a read cursor on the corresponding <table2> index
          276  +**           transfer all records from the read to the write cursors
          277  +**           close cursors
          278  +**         end foreach
          279  +**
          280  +** The third template is for when the second template does not apply
          281  +** and the SELECT clause does not read from <table> at any time.
          282  +** The generated code follows this template:
   151    283   **
   152    284   **         goto B
   153    285   **      A: setup for the SELECT
   154         -**         loop over the tables in the SELECT
          286  +**         loop over the rows in the SELECT
   155    287   **           gosub C
   156    288   **         end loop
   157    289   **         cleanup after the SELECT
   158    290   **         goto D
   159    291   **      B: open write cursor to <table> and its indices
   160    292   **         goto A
   161    293   **      C: insert the select result into <table>
   162    294   **         return
   163    295   **      D: cleanup
   164    296   **
   165         -** The third template is used if the insert statement takes its
          297  +** The fourth template is used if the insert statement takes its
   166    298   ** values from a SELECT but the data is being inserted into a table
   167    299   ** that is also read as part of the SELECT.  In the third form,
   168    300   ** we have to use a intermediate table to store the results of
   169    301   ** the select.  The template is like this:
   170    302   **
   171    303   **         goto B
   172    304   **      A: setup for the SELECT
................................................................................
   217    349     int iDb;
   218    350   
   219    351   #ifndef SQLITE_OMIT_TRIGGER
   220    352     int isView;                 /* True if attempting to insert into a view */
   221    353     int triggers_exist = 0;     /* True if there are FOR EACH ROW triggers */
   222    354   #endif
   223    355   
   224         -#ifndef SQLITE_OMIT_AUTOINCREMENT
   225         -  int counterRowid = 0;  /* Memory cell holding rowid of autoinc counter */
   226         -#endif
   227         -
   228    356     if( pParse->nErr || sqlite3MallocFailed() ){
   229    357       goto insert_cleanup;
   230    358     }
   231    359     db = pParse->db;
   232    360   
   233    361     /* Locate the table into which we will be inserting new information.
   234    362     */
................................................................................
   287    415     sqlite3BeginWriteOperation(pParse, pSelect || triggers_exist, iDb);
   288    416   
   289    417     /* if there are row triggers, allocate a temp table for new.* references. */
   290    418     if( triggers_exist ){
   291    419       newIdx = pParse->nTab++;
   292    420     }
   293    421   
   294         -#ifndef SQLITE_OMIT_AUTOINCREMENT
          422  +#ifndef SQLITE_OMIT_XFER_OPT
          423  +  /* If the statement is of the form
          424  +  **
          425  +  **       INSERT INTO <table1> SELECT * FROM <table2>;
          426  +  **
          427  +  ** Then special optimizations can be applied that make the transfer
          428  +  ** very fast and which reduce fragmentation of indices.
          429  +  */
          430  +  if( pColumn==0 && xferOptimization(pParse, pTab, pSelect, onError, iDb) ){
          431  +    assert( !triggers_exist );
          432  +    assert( pList==0 );
          433  +    goto insert_cleanup;
          434  +  }
          435  +#endif /* SQLITE_OMIT_XFER_OPT */
          436  +
   295    437     /* If this is an AUTOINCREMENT table, look up the sequence number in the
   296    438     ** sqlite_sequence table and store it in memory cell counterMem.  Also
   297    439     ** remember the rowid of the sqlite_sequence table entry in memory cell
   298    440     ** counterRowid.
   299    441     */
   300         -  if( pTab->autoInc ){
   301         -    int iCur = pParse->nTab;
   302         -    int addr = sqlite3VdbeCurrentAddr(v);
   303         -    counterRowid = pParse->nMem++;
   304         -    counterMem = pParse->nMem++;
   305         -    sqlite3OpenTable(pParse, iCur, iDb, pDb->pSchema->pSeqTab, OP_OpenRead);
   306         -    sqlite3VdbeAddOp(v, OP_Rewind, iCur, addr+13);
   307         -    sqlite3VdbeAddOp(v, OP_Column, iCur, 0);
   308         -    sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0);
   309         -    sqlite3VdbeAddOp(v, OP_Ne, 0x100, addr+12);
   310         -    sqlite3VdbeAddOp(v, OP_Rowid, iCur, 0);
   311         -    sqlite3VdbeAddOp(v, OP_MemStore, counterRowid, 1);
   312         -    sqlite3VdbeAddOp(v, OP_Column, iCur, 1);
   313         -    sqlite3VdbeAddOp(v, OP_MemStore, counterMem, 1);
   314         -    sqlite3VdbeAddOp(v, OP_Goto, 0, addr+13);
   315         -    sqlite3VdbeAddOp(v, OP_Next, iCur, addr+4);
   316         -    sqlite3VdbeAddOp(v, OP_Close, iCur, 0);
   317         -  }
   318         -#endif /* SQLITE_OMIT_AUTOINCREMENT */
          442  +  counterMem = autoIncBegin(pParse, iDb, pTab);
   319    443   
   320    444     /* Figure out how many columns of data are supplied.  If the data
   321    445     ** is coming from a SELECT statement, then this step also generates
   322    446     ** all the code to implement the SELECT statement and invoke a subroutine
   323    447     ** to process each row of the result. (Template 2.) If the SELECT
   324    448     ** statement uses the the table that is being inserted into, then the
   325    449     ** subroutine is also coded here.  That subroutine stores the SELECT
................................................................................
   587    711         sqlite3VdbeAddOp(v, OP_NewRowid, base, counterMem);
   588    712         sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0);
   589    713       }else if( IsVirtual(pTab) ){
   590    714         sqlite3VdbeAddOp(v, OP_Null, 0, 0);
   591    715       }else{
   592    716         sqlite3VdbeAddOp(v, OP_NewRowid, base, counterMem);
   593    717       }
   594         -#ifndef SQLITE_OMIT_AUTOINCREMENT
   595         -    if( pTab->autoInc ){
   596         -      sqlite3VdbeAddOp(v, OP_MemMax, counterMem, 0);
   597         -    }
   598         -#endif /* SQLITE_OMIT_AUTOINCREMENT */
          718  +    autoIncStep(pParse, counterMem);
   599    719   
   600    720       /* Push onto the stack, data for all columns of the new entry, beginning
   601    721       ** with the first column.
   602    722       */
   603    723       for(i=0; i<pTab->nCol; i++){
   604    724         if( i==pTab->iPKey ){
   605    725           /* The value of the INTEGER PRIMARY KEY column is always a NULL.
................................................................................
   684    804       /* Close all tables opened */
   685    805       sqlite3VdbeAddOp(v, OP_Close, base, 0);
   686    806       for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
   687    807         sqlite3VdbeAddOp(v, OP_Close, idx+base, 0);
   688    808       }
   689    809     }
   690    810   
   691         -#ifndef SQLITE_OMIT_AUTOINCREMENT
   692    811     /* Update the sqlite_sequence table by storing the content of the
   693    812     ** counter value in memory counterMem back into the sqlite_sequence
   694    813     ** table.
   695    814     */
   696         -  if( pTab->autoInc ){
   697         -    int iCur = pParse->nTab;
   698         -    int addr = sqlite3VdbeCurrentAddr(v);
   699         -    sqlite3OpenTable(pParse, iCur, iDb, pDb->pSchema->pSeqTab, OP_OpenWrite);
   700         -    sqlite3VdbeAddOp(v, OP_MemLoad, counterRowid, 0);
   701         -    sqlite3VdbeAddOp(v, OP_NotNull, -1, addr+7);
   702         -    sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
   703         -    sqlite3VdbeAddOp(v, OP_NewRowid, iCur, 0);
   704         -    sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0);
   705         -    sqlite3VdbeAddOp(v, OP_MemLoad, counterMem, 0);
   706         -    sqlite3VdbeAddOp(v, OP_MakeRecord, 2, 0);
   707         -    sqlite3VdbeAddOp(v, OP_Insert, iCur, 0);
   708         -    sqlite3VdbeAddOp(v, OP_Close, iCur, 0);
   709         -  }
   710         -#endif
          815  +  autoIncEnd(pParse, iDb, pTab, counterMem);
   711    816   
   712    817     /*
   713    818     ** Return the number of rows inserted. If this routine is 
   714    819     ** generating code because of a call to sqlite3NestedParse(), do not
   715    820     ** invoke the callback function.
   716    821     */
   717    822     if( db->flags & SQLITE_CountRows && pParse->nested==0 && !pParse->trigStack ){
................................................................................
  1136   1241       VdbeComment((v, "# %s", pIdx->zName));
  1137   1242       sqlite3VdbeOp3(v, op, i+base, pIdx->tnum, (char*)pKey, P3_KEYINFO_HANDOFF);
  1138   1243     }
  1139   1244     if( pParse->nTab<=base+i ){
  1140   1245       pParse->nTab = base+i;
  1141   1246     }
  1142   1247   }
         1248  +
         1249  +#ifndef SQLITE_OMIT_XFER_OPT
         1250  +/*
         1251  +** Check to collation names to see if they are compatible.
         1252  +*/
         1253  +static int xferCompatibleCollation(const char *z1, const char *z2){
         1254  +  if( z1==0 ){
         1255  +    return z2==0;
         1256  +  }
         1257  +  if( z2==0 ){
         1258  +    return 0;
         1259  +  }
         1260  +  return sqlite3StrICmp(z1, z2)==0;
         1261  +}
         1262  +
         1263  +
         1264  +/*
         1265  +** Check to see if index pSrc is compatible as a source of data
         1266  +** for index pDest in an insert transfer optimization.  The rules
         1267  +** for a compatible index:
         1268  +**
         1269  +**    *   The index is over the same set of columns
         1270  +**    *   The same DESC and ASC markings occurs on all columns
         1271  +**    *   The same onError processing (OE_Abort, OE_Ignore, etc)
         1272  +**    *   The same collating sequence on each column
         1273  +*/
         1274  +static int xferCompatibleIndex(Index *pDest, Index *pSrc){
         1275  +  int i;
         1276  +  assert( pDest && pSrc );
         1277  +  assert( pDest->pTable!=pSrc->pTable );
         1278  +  if( pDest->nColumn!=pSrc->nColumn ){
         1279  +    return 0;   /* Different number of columns */
         1280  +  }
         1281  +  if( pDest->onError!=pSrc->onError ){
         1282  +    return 0;   /* Different conflict resolution strategies */
         1283  +  }
         1284  +  for(i=0; i<pSrc->nColumn; i++){
         1285  +    if( pSrc->aiColumn[i]!=pDest->aiColumn[i] ){
         1286  +      return 0;   /* Different columns indexed */
         1287  +    }
         1288  +    if( pSrc->aSortOrder[i]!=pDest->aSortOrder[i] ){
         1289  +      return 0;   /* Different sort orders */
         1290  +    }
         1291  +    if( pSrc->azColl[i]!=pDest->azColl[i] ){
         1292  +      return 0;   /* Different sort orders */
         1293  +    }
         1294  +  }
         1295  +
         1296  +  /* If no test above fails then the indices must be compatible */
         1297  +  return 1;
         1298  +}
         1299  +
         1300  +/*
         1301  +** Attempt the transfer optimization on INSERTs of the form
         1302  +**
         1303  +**     INSERT INTO tab1 SELECT * FROM tab2;
         1304  +**
         1305  +** This optimization is only attempted if
         1306  +**
         1307  +**    (1)  tab1 and tab2 have identical schemas including all the
         1308  +**         same indices
         1309  +**
         1310  +**    (2)  tab1 and tab2 are different tables
         1311  +**
         1312  +**    (3)  There must be no triggers on tab1
         1313  +**
         1314  +**    (4)  The result set of the SELECT statement is "*"
         1315  +**
         1316  +**    (5)  The SELECT statement has no WHERE, HAVING, ORDER BY, GROUP BY,
         1317  +**         or LIMIT clause.
         1318  +**
         1319  +**    (6)  The SELECT statement is a simple (not a compound) select that
         1320  +**         contains only tab2 in its FROM clause
         1321  +**
         1322  +** This method for implementing the INSERT transfers raw records from
         1323  +** tab2 over to tab1.  The columns are not decoded.  Raw records from
         1324  +** the indices of tab2 are transfered to tab1 as well.  In so doing,
         1325  +** the resulting tab1 has much less fragmentation.
         1326  +**
         1327  +** This routine returns TRUE if the optimization is attempted.  If any
         1328  +** of the conditions above fail so that the optimization should not
         1329  +** be attempted, then this routine returns FALSE.
         1330  +*/
         1331  +static int xferOptimization(
         1332  +  Parse *pParse,        /* Parser context */
         1333  +  Table *pDest,         /* The table we are inserting into */
         1334  +  Select *pSelect,      /* A SELECT statement to use as the data source */
         1335  +  int onError,          /* How to handle constraint errors */
         1336  +  int iDbDest           /* The database of pDest */
         1337  +){
         1338  +  ExprList *pEList;                /* The result set of the SELECT */
         1339  +  Table *pSrc;                     /* The table in the FROM clause of SELECT */
         1340  +  Index *pSrcIdx, *pDestIdx;       /* Source and destination indices */
         1341  +  struct SrcList_item *pItem;      /* An element of pSelect->pSrc */
         1342  +  int i;                           /* Loop counter */
         1343  +  int iDbSrc;                      /* The database of pSrc */
         1344  +  int iSrc, iDest;                 /* Cursors from source and destination */
         1345  +  int addr1, addr2;                /* Loop addresses */
         1346  +  int emptyDestTest;               /* Address of test for empty pDest */
         1347  +  int emptySrcTest;                /* Address of test for empty pSrc */
         1348  +  int memRowid;                    /* A memcell containing a rowid from pSrc */
         1349  +  Vdbe *v;                         /* The VDBE we are building */
         1350  +  KeyInfo *pKey;                   /* Key information for an index */
         1351  +  int counterMem;                  /* Memory register used by AUTOINC */
         1352  +
         1353  +  if( pSelect==0 ){
         1354  +    return 0;   /* Must be of the form  INSERT INTO ... SELECT ... */
         1355  +  }
         1356  +  if( pDest->pTrigger ){
         1357  +    return 0;   /* tab1 must not have triggers */
         1358  +  }
         1359  +#ifndef SQLITE_OMIT_VIRTUALTABLE
         1360  +  if( pDest->isVirtual ){
         1361  +    return 0;   /* tab1 must not be a virtual table */
         1362  +  }
         1363  +#endif
         1364  +  if( onError==OE_Default ){
         1365  +    onError = OE_Abort;
         1366  +  }
         1367  +  if( onError!=OE_Abort && onError!=OE_Rollback ){
         1368  +    return 0;   /* Cannot do OR REPLACE or OR IGNORE or OR FAIL */
         1369  +  }
         1370  +  if( pSelect->pSrc==0 ){
         1371  +    return 0;   /* SELECT must have a FROM clause */
         1372  +  }
         1373  +  if( pSelect->pSrc->nSrc!=1 ){
         1374  +    return 0;   /* FROM clause must have exactly one term */
         1375  +  }
         1376  +  if( pSelect->pSrc->a[0].pSelect ){
         1377  +    return 0;   /* FROM clause cannot contain a subquery */
         1378  +  }
         1379  +  if( pSelect->pWhere ){
         1380  +    return 0;   /* SELECT may not have a WHERE clause */
         1381  +  }
         1382  +  if( pSelect->pOrderBy ){
         1383  +    return 0;   /* SELECT may not have an ORDER BY clause */
         1384  +  }
         1385  +  if( pSelect->pHaving ){
         1386  +    return 0;   /* SELECT may not have a HAVING clause */
         1387  +  }
         1388  +  if( pSelect->pGroupBy ){
         1389  +    return 0;   /* SELECT may not have a GROUP BY clause */
         1390  +  }
         1391  +  if( pSelect->pLimit ){
         1392  +    return 0;   /* SELECT may not have a LIMIT clause */
         1393  +  }
         1394  +  if( pSelect->pOffset ){
         1395  +    return 0;   /* SELECT may not have an OFFSET clause */
         1396  +  }
         1397  +  if( pSelect->pPrior ){
         1398  +    return 0;   /* SELECT may not be a compound query */
         1399  +  }
         1400  +  if( pSelect->isDistinct ){
         1401  +    return 0;   /* SELECT may not be DISTINCT */
         1402  +  }
         1403  +  pEList = pSelect->pEList;
         1404  +  assert( pEList!=0 );
         1405  +  if( pEList->nExpr!=1 ){
         1406  +    return 0;   /* The result set must have exactly one column */
         1407  +  }
         1408  +  assert( pEList->a[0].pExpr );
         1409  +  if( pEList->a[0].pExpr->op!=TK_ALL ){
         1410  +    return 0;   /* The result set must be the special operator "*" */
         1411  +  }
         1412  +
         1413  +  /* At this point we have established that the statement is of the
         1414  +  ** correct syntactic form to participate in this optimization.  Now
         1415  +  ** we have to check the semantics.
         1416  +  */
         1417  +  pItem = pSelect->pSrc->a;
         1418  +  pSrc = sqlite3LocateTable(pParse, pItem->zName, pItem->zDatabase);
         1419  +  if( pSrc==0 ){
         1420  +    return 0;   /* FROM clause does not contain a real table */
         1421  +  }
         1422  +  if( pSrc==pDest ){
         1423  +    return 0;   /* tab1 and tab2 may not be the same table */
         1424  +  }
         1425  +#ifndef SQLITE_OMIT_VIRTUALTABLE
         1426  +  if( pSrc->isVirtual ){
         1427  +    return 0;   /* tab2 must not be a virtual table */
         1428  +  }
         1429  +#endif
         1430  +  if( pSrc->pSelect ){
         1431  +    return 0;   /* tab2 may not be a view */
         1432  +  }
         1433  +  if( pDest->nCol!=pSrc->nCol ){
         1434  +    return 0;   /* Number of columns must be the same in tab1 and tab2 */
         1435  +  }
         1436  +  if( pDest->iPKey!=pSrc->iPKey ){
         1437  +    return 0;   /* Both tables must have the same INTEGER PRIMARY KEY */
         1438  +  }
         1439  +  for(i=0; i<pDest->nCol; i++){
         1440  +    if( pDest->aCol[i].affinity!=pSrc->aCol[i].affinity ){
         1441  +      return 0;    /* Affinity must be the same on all columns */
         1442  +    }
         1443  +    if( !xferCompatibleCollation(pDest->aCol[i].zColl, pSrc->aCol[i].zColl) ){
         1444  +      return 0;    /* Collating sequence must be the same on all columns */
         1445  +    }
         1446  +    if( pDest->aCol[i].notNull && !pSrc->aCol[i].notNull ){
         1447  +      return 0;    /* tab2 must be NOT NULL if tab1 is */
         1448  +    }
         1449  +  }
         1450  +  for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
         1451  +    for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){
         1452  +      if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
         1453  +    }
         1454  +    if( pSrcIdx==0 ){
         1455  +      return 0;    /* pDestIdx has no corresponding index in pSrc */
         1456  +    }
         1457  +  }
         1458  +
         1459  +  /* If we get this far, it means either:
         1460  +  **
         1461  +  **    *   We can always do the transfer if the table contains an
         1462  +  **        an integer primary key
         1463  +  **
         1464  +  **    *   We can conditionally do the transfer if the destination
         1465  +  **        table is empty.
         1466  +  */
         1467  +  iDbSrc = sqlite3SchemaToIndex(pParse->db, pSrc->pSchema);
         1468  +  v = sqlite3GetVdbe(pParse);
         1469  +  iSrc = pParse->nTab++;
         1470  +  iDest = pParse->nTab++;
         1471  +  counterMem = autoIncBegin(pParse, iDbDest, pDest);
         1472  +  sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite);
         1473  +  if( pDest->iPKey<0 ){
         1474  +    /* The tables do not have an INTEGER PRIMARY KEY so that
         1475  +    ** transfer optimization is only allowed if the destination
         1476  +    ** table is initially empty
         1477  +    */
         1478  +    addr1 = sqlite3VdbeAddOp(v, OP_Rewind, iDest, 0);
         1479  +    emptyDestTest = sqlite3VdbeAddOp(v, OP_Goto, 0, 0);
         1480  +    sqlite3VdbeJumpHere(v, addr1);
         1481  +  }else{
         1482  +    emptyDestTest = 0;
         1483  +  }
         1484  +  sqlite3OpenTable(pParse, iSrc, iDbSrc, pSrc, OP_OpenRead);
         1485  +  emptySrcTest = sqlite3VdbeAddOp(v, OP_Rewind, iSrc, 0);
         1486  +  memRowid = pParse->nMem++;
         1487  +  sqlite3VdbeAddOp(v, OP_Rowid, iSrc, 0);
         1488  +  sqlite3VdbeAddOp(v, OP_MemStore, memRowid, 1);
         1489  +  addr1 = sqlite3VdbeAddOp(v, OP_Rowid, iSrc, 0);
         1490  +  sqlite3VdbeAddOp(v, OP_Dup, 0, 0);
         1491  +  addr2 = sqlite3VdbeAddOp(v, OP_NotExists, iDest, 0);
         1492  +  sqlite3VdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError, 
         1493  +                    "PRIMARY KEY must be unique", P3_STATIC);
         1494  +  sqlite3VdbeJumpHere(v, addr2);
         1495  +  autoIncStep(pParse, counterMem);
         1496  +  sqlite3VdbeAddOp(v, OP_RowData, iSrc, 0);
         1497  +  sqlite3VdbeOp3(v, OP_Insert, iDest, OPFLAG_NCHANGE|OPFLAG_LASTROWID,
         1498  +                    pDest->zName, 0);
         1499  +  sqlite3VdbeAddOp(v, OP_Next, iSrc, addr1);
         1500  +  autoIncEnd(pParse, iDbDest, pDest, counterMem);
         1501  +  for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
         1502  +    for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){
         1503  +      if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
         1504  +    }
         1505  +    assert( pSrcIdx );
         1506  +    sqlite3VdbeAddOp(v, OP_Close, iSrc, 0);
         1507  +    sqlite3VdbeAddOp(v, OP_Close, iDest, 0);
         1508  +    sqlite3VdbeAddOp(v, OP_Integer, iDbSrc, 0);
         1509  +    pKey = sqlite3IndexKeyinfo(pParse, pSrcIdx);
         1510  +    VdbeComment((v, "# %s", pSrcIdx->zName));
         1511  +    sqlite3VdbeOp3(v, OP_OpenRead, iSrc, pSrcIdx->tnum, 
         1512  +                   (char*)pKey, P3_KEYINFO_HANDOFF);
         1513  +    sqlite3VdbeAddOp(v, OP_Integer, iDbDest, 0);
         1514  +    pKey = sqlite3IndexKeyinfo(pParse, pDestIdx);
         1515  +    VdbeComment((v, "# %s", pDestIdx->zName));
         1516  +    sqlite3VdbeOp3(v, OP_OpenWrite, iDest, pDestIdx->tnum, 
         1517  +                   (char*)pKey, P3_KEYINFO_HANDOFF);
         1518  +    addr1 = sqlite3VdbeAddOp(v, OP_Rewind, iSrc, 0);
         1519  +    sqlite3VdbeAddOp(v, OP_RowKey, iSrc, 0);
         1520  +    if( pDestIdx->onError!=OE_None ){
         1521  +      sqlite3VdbeAddOp(v, OP_MemLoad, memRowid, 0);
         1522  +      addr2 = sqlite3VdbeAddOp(v, OP_IsUnique, iDest, 0);
         1523  +      sqlite3VdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError, 
         1524  +                    "UNIQUE constraint failed", P3_STATIC);
         1525  +      sqlite3VdbeJumpHere(v, addr2);
         1526  +    }
         1527  +    sqlite3VdbeAddOp(v, OP_IdxInsert, iDest, 0);
         1528  +    sqlite3VdbeAddOp(v, OP_Next, iSrc, addr1+1);
         1529  +    sqlite3VdbeJumpHere(v, addr1);
         1530  +  }
         1531  +  sqlite3VdbeJumpHere(v, emptySrcTest);
         1532  +  sqlite3VdbeAddOp(v, OP_Close, iSrc, 0);
         1533  +  sqlite3VdbeAddOp(v, OP_Close, iDest, 0);
         1534  +  if( emptyDestTest ){
         1535  +    sqlite3VdbeAddOp(v, OP_Halt, SQLITE_OK, 0);
         1536  +    sqlite3VdbeJumpHere(v, emptyDestTest);
         1537  +    sqlite3VdbeAddOp(v, OP_Close, iDest, 0);
         1538  +    return 0;
         1539  +  }else{
         1540  +    return 1;
         1541  +  }
         1542  +}
         1543  +#endif /* SQLITE_OMIT_XFER_OPT */