/ Check-in [db829dc1]
Login

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

Overview
Comment:Have ALTER TABLE RENAME edit column references in CREATE VIEW statements.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | edit-trigger-wrapper
Files: files | file ages | folders
SHA3-256: db829dc1a2d7afa49798a2fd32d1f070185b23e513416e65d8144fda24f23b50
User & Date: dan 2018-08-14 20:18:50
Context
2018-08-14
20:40
Merge latest trunk and ALTER TABLE error message improvements into this branch. check-in: 7a45802d user: dan tags: edit-trigger-wrapper
20:18
Have ALTER TABLE RENAME edit column references in CREATE VIEW statements. check-in: db829dc1 user: dan tags: edit-trigger-wrapper
2018-08-13
17:14
Edit the WHEN and UPDATE OF clauses of trigger programs as part of ALTER TABLE RENAME COLUMN. check-in: 5fdb6b0a user: dan tags: edit-trigger-wrapper
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/alter.c.

   840    840     */
   841    841     zNew = sqlite3NameFromToken(db, pNew);
   842    842     if( !zNew ) goto exit_rename_column;
   843    843     assert( pNew->n>0 );
   844    844     bQuote = sqlite3Isquote(pNew->z[0]);
   845    845     sqlite3NestedParse(pParse, 
   846    846         "UPDATE \"%w\".%s SET "
   847         -      "sql = sqlite_rename_column(sql, %d, %d, %Q, %Q, %Q) "
          847  +      "sql = sqlite_rename_column(sql, %Q, %Q, %d, %Q, %d) "
   848    848         "WHERE name NOT LIKE 'sqlite_%%' AND ("
   849         -      "   type = 'table' OR (type IN ('index', 'trigger') AND tbl_name = %Q)"
          849  +      "       type IN ('table', 'view') "
          850  +      "   OR (type IN ('index', 'trigger') AND tbl_name = %Q)"
   850    851         ")",
   851         -      zDb, MASTER_NAME, iCol, bQuote, zNew, pTab->zName, zOld, pTab->zName
          852  +      zDb, MASTER_NAME, 
          853  +      zDb, pTab->zName, iCol, zNew, bQuote,
          854  +      pTab->zName
   852    855     );
   853    856   
   854    857     /* Drop and reload the database schema. */
   855    858     if( pParse->pVdbe ){
   856    859       sqlite3ChangeCookie(pParse, iSchema);
   857    860       sqlite3VdbeAddParseSchemaOp(pParse->pVdbe, iSchema, 0);
   858    861     }
................................................................................
   892    895   ** down into the Walker.
   893    896   */
   894    897   typedef struct RenameCtx RenameCtx;
   895    898   struct RenameCtx {
   896    899     RenameToken *pList;             /* List of tokens to overwrite */
   897    900     int nList;                      /* Number of tokens in pList */
   898    901     int iCol;                       /* Index of column being renamed */
          902  +  Table *pTab;                    /* Table being ALTERed */ 
   899    903     const char *zOld;               /* Old column name */
   900    904   };
   901    905   
   902    906   /*
   903    907   ** Add a new RenameToken object mapping parse tree element pPtr into
   904    908   ** token *pToken to the Parse object currently under construction.
   905    909   */
................................................................................
   938    942     RenameToken *p;
   939    943     for(p=pToken; p; p=pNext){
   940    944       pNext = p->pNext;
   941    945       sqlite3DbFree(db, p);
   942    946     }
   943    947   }
   944    948   
   945         -static void renameTokenFind(Parse *pParse, RenameCtx *pCtx, void *pPtr){
          949  +/*
          950  +** Search the Parse object passed as the first argument for a RenameToken
          951  +** object associated with parse tree element pPtr. If found, remove it
          952  +** from the Parse object and add it to the list maintained by the
          953  +** RenameCtx object passed as the second argument.
          954  +*/
          955  +static void renameTokenFind(Parse *pParse, struct RenameCtx *pCtx, void *pPtr){
   946    956     RenameToken **pp;
   947    957     for(pp=&pParse->pRename; (*pp); pp=&(*pp)->pNext){
   948    958       if( (*pp)->p==pPtr ){
   949    959         RenameToken *pToken = *pp;
   950    960         *pp = pToken->pNext;
   951    961         pToken->pNext = pCtx->pList;
   952    962         pCtx->pList = pToken;
   953    963         pCtx->nList++;
   954    964         break;
   955    965       }
   956    966     }
   957    967   }
   958    968   
          969  +static int renameColumnSelectCb(Walker *pWalker, Select *p){
          970  +  return WRC_Continue;
          971  +}
          972  +
          973  +
          974  +/*
          975  +** This is a Walker expression callback.
          976  +**
          977  +** For every TK_COLUMN node in the expression tree, search to see
          978  +** if the column being references is the column being renamed by an
          979  +** ALTER TABLE statement.  If it is, then attach its associated
          980  +** RenameToken object to the list of RenameToken objects being
          981  +** constructed in RenameCtx object at pWalker->u.pRename.
          982  +*/
   959    983   static int renameColumnExprCb(Walker *pWalker, Expr *pExpr){
   960    984     RenameCtx *p = pWalker->u.pRename;
   961    985     if( p->zOld && pExpr->op==TK_DOT ){
   962    986       Expr *pLeft = pExpr->pLeft;
   963    987       Expr *pRight = pExpr->pRight;
   964    988       assert( pLeft->op==TK_ID && pRight->op==TK_ID );
   965    989       if( 0==sqlite3_stricmp(pLeft->u.zToken, "old")
   966    990        || 0==sqlite3_stricmp(pLeft->u.zToken, "new")
   967    991       ){
   968    992         if( 0==sqlite3_stricmp(pRight->u.zToken, p->zOld) ){
   969    993           renameTokenFind(pWalker->pParse, p, (void*)pRight);
   970    994         }
   971    995       }
   972         -  }else if( pExpr->op==TK_COLUMN && pExpr->iColumn==p->iCol ){
          996  +  }else if( pExpr->op==TK_COLUMN && pExpr->iColumn==p->iCol 
          997  +         && (p->pTab==0 || p->pTab==pExpr->pTab)
          998  +  ){
   973    999       renameTokenFind(pWalker->pParse, p, (void*)pExpr);
   974   1000     }
   975   1001     return WRC_Continue;
   976   1002   }
   977   1003   
         1004  +/*
         1005  +** The RenameCtx contains a list of tokens that reference a column that
         1006  +** is being renamed by an ALTER TABLE statement.  Return the "first"
         1007  +** RenameToken in the RenameCtx and remove that RenameToken from the
         1008  +** RenameContext.  "First" means the first RenameToken encountered when
         1009  +** the input SQL from left to right.  Repeated calls to this routine
         1010  +** return all column name tokens in the order that they are encountered
         1011  +** in the SQL statement.
         1012  +*/
   978   1013   static RenameToken *renameColumnTokenNext(RenameCtx *pCtx){
   979   1014     RenameToken *pBest = pCtx->pList;
   980   1015     RenameToken *pToken;
   981   1016     RenameToken **pp;
   982   1017   
   983   1018     for(pToken=pBest->pNext; pToken; pToken=pToken->pNext){
   984   1019       if( pToken->t.z>pBest->t.z ) pBest = pToken;
................................................................................
   986   1021     for(pp=&pCtx->pList; *pp!=pBest; pp=&(*pp)->pNext);
   987   1022     *pp = pBest->pNext;
   988   1023   
   989   1024     return pBest;
   990   1025   }
   991   1026   
   992   1027   /*
   993         -** sqlite_rename_column(SQL, iCol, bQuote, zNew, zTable, zOld)
         1028  +** SQL function:
         1029  +**
         1030  +**     sqlite_rename_column(zSql, iCol, bQuote, zNew, zTable, zOld)
         1031  +**
         1032  +**   0. zSql:     SQL statement to rewrite
         1033  +**   1. Database: Database name (e.g. "main")
         1034  +**   2. Table:    Table name
         1035  +**   3. iCol:     Index of column to rename
         1036  +**   4. zNew:     New column name
         1037  +**   5. bQuote: True if the new column name should be quoted
         1038  +**
         1039  +** Do a column rename operation on the CREATE statement given in zSql.
         1040  +** The iCol-th column (left-most is 0) of table zTable is renamed from zCol
         1041  +** into zNew.  The name should be quoted if bQuote is true.
         1042  +**
         1043  +** This function is used internally by the ALTER TABLE RENAME COLUMN command.
         1044  +** Though accessible to application code, it is not intended for use by
         1045  +** applications.  The existance of this function, and the way it works,
         1046  +** is subject to change without notice.
         1047  +**
         1048  +** If any of the parameters are out-of-bounds, then simply return NULL.
         1049  +** An out-of-bounds parameter can only occur when the application calls
         1050  +** this function directly.  The parameters will always be well-formed when
         1051  +** this routine is invoked by the bytecode for a legitimate ALTER TABLE
         1052  +** statement.
   994   1053   */
   995   1054   static void renameColumnFunc(
   996   1055     sqlite3_context *context,
   997   1056     int NotUsed,
   998   1057     sqlite3_value **argv
   999   1058   ){
  1000   1059     sqlite3 *db = sqlite3_context_db_handle(context);
  1001   1060     RenameCtx sCtx;
  1002   1061     const char *zSql = (const char*)sqlite3_value_text(argv[0]);
  1003   1062     int nSql = sqlite3_value_bytes(argv[0]);
  1004         -  int bQuote = sqlite3_value_int(argv[2]);
  1005         -  const char *zNew = (const char*)sqlite3_value_text(argv[3]);
  1006         -  int nNew = sqlite3_value_bytes(argv[3]);
  1007         -  const char *zTable = (const char*)sqlite3_value_text(argv[4]);
  1008         -  const char *zOld = (const char*)sqlite3_value_text(argv[5]);
         1063  +  const char *zDb = (const char*)sqlite3_value_text(argv[1]);
         1064  +  const char *zTable = (const char*)sqlite3_value_text(argv[2]);
         1065  +  int iCol = sqlite3_value_int(argv[3]);
         1066  +  const char *zNew = (const char*)sqlite3_value_text(argv[4]);
         1067  +  int nNew = sqlite3_value_bytes(argv[4]);
         1068  +  int bQuote = sqlite3_value_int(argv[5]);
         1069  +  const char *zOld;
  1009   1070   
  1010   1071     int rc;
  1011   1072     char *zErr = 0;
  1012   1073     Parse sParse;
  1013   1074     Walker sWalker;
  1014   1075     Index *pIdx;
  1015   1076     char *zOut = 0;
  1016   1077   
  1017   1078     char *zQuot = 0;                /* Quoted version of zNew */
  1018   1079     int nQuot = 0;                  /* Length of zQuot in bytes */
  1019   1080     int i;
         1081  +  Table *pTab;
  1020   1082   
         1083  +  if( zSql==0 ) return;
         1084  +  if( zNew==0 ) return;
         1085  +  if( zTable==0 ) return;
         1086  +  if( iCol<0 ) return;
         1087  +  pTab = sqlite3FindTable(db, zTable, zDb);
         1088  +  if( pTab==0 || iCol>=pTab->nCol ) return;
         1089  +  zOld = pTab->aCol[iCol].zName;
  1021   1090     memset(&sCtx, 0, sizeof(sCtx));
  1022         -  sCtx.iCol = sqlite3_value_int(argv[1]);
         1091  +  sCtx.iCol = ((iCol==pTab->iPKey) ? -1 : iCol);
  1023   1092   
  1024   1093     memset(&sParse, 0, sizeof(sParse));
  1025   1094     sParse.eParseMode = PARSE_MODE_RENAME_COLUMN;
  1026   1095     sParse.db = db;
  1027   1096     sParse.nQueryLoop = 1;
  1028   1097     rc = sqlite3RunParser(&sParse, zSql, &zErr);
  1029   1098     assert( sParse.pNewTable==0 || sParse.pNewIndex==0 );
................................................................................
  1039   1108       if( zQuot==0 ){
  1040   1109         rc = SQLITE_NOMEM;
  1041   1110       }else{
  1042   1111         nQuot = sqlite3Strlen30(zQuot);
  1043   1112       }
  1044   1113     }
  1045   1114   
  1046         -  if( rc!=SQLITE_OK ){
  1047         -    if( zErr ){
  1048         -      sqlite3_result_error(context, zErr, -1);
  1049         -    }else{
  1050         -      sqlite3_result_error_code(context, rc);
  1051         -    }
  1052         -    sqlite3DbFree(db, zErr);
  1053         -    goto renameColumnFunc_done;
  1054         -  }
  1055         -
  1056   1115     if( bQuote ){
  1057   1116       zNew = zQuot;
  1058   1117       nNew = nQuot;
  1059   1118     }
  1060   1119   
  1061   1120   #ifdef SQLITE_DEBUG
  1062   1121     assert( sqlite3Strlen30(zSql)==nSql );
  1063         -  {
         1122  +  if( rc==SQLITE_OK ){
  1064   1123       RenameToken *pToken;
  1065   1124       for(pToken=sParse.pRename; pToken; pToken=pToken->pNext){
  1066   1125         assert( pToken->t.z>=zSql && &pToken->t.z[pToken->t.n]<=&zSql[nSql] );
  1067   1126       }
  1068   1127     }
  1069   1128   #endif
  1070   1129   
  1071   1130     /* Find tokens that need to be replaced. */
  1072   1131     memset(&sWalker, 0, sizeof(Walker));
  1073   1132     sWalker.pParse = &sParse;
  1074   1133     sWalker.xExprCallback = renameColumnExprCb;
         1134  +  sWalker.xSelectCallback = renameColumnSelectCb;
  1075   1135     sWalker.u.pRename = &sCtx;
  1076   1136   
         1137  +  if( rc!=SQLITE_OK ) goto renameColumnFunc_done;
  1077   1138     if( sParse.pNewTable ){
  1078         -    int bFKOnly = sqlite3_stricmp(zTable, sParse.pNewTable->zName);
  1079         -    FKey *pFKey;
  1080         -    if( bFKOnly==0 ){
  1081         -      renameTokenFind(
  1082         -          &sParse, &sCtx, (void*)sParse.pNewTable->aCol[sCtx.iCol].zName
  1083         -      );
  1084         -      assert( sCtx.iCol>=0 );
  1085         -      if( sParse.pNewTable->iPKey==sCtx.iCol ){
  1086         -        sCtx.iCol = -1;
  1087         -      }
  1088         -      sqlite3WalkExprList(&sWalker, sParse.pNewTable->pCheck);
  1089         -      for(pIdx=sParse.pNewTable->pIndex; pIdx; pIdx=pIdx->pNext){
  1090         -        sqlite3WalkExprList(&sWalker, pIdx->aColExpr);
         1139  +    Select *pSelect = sParse.pNewTable->pSelect;
         1140  +    if( pSelect ){
         1141  +      sCtx.pTab = pTab;
         1142  +      sParse.rc = SQLITE_OK;
         1143  +      sqlite3SelectPrep(&sParse, sParse.pNewTable->pSelect, 0);
         1144  +      rc = (db->mallocFailed ? SQLITE_NOMEM : sParse.rc);
         1145  +      if( rc==SQLITE_OK ){
         1146  +        sqlite3WalkSelect(&sWalker, pSelect);
         1147  +      }else if( rc==SQLITE_ERROR ){
         1148  +        /* Failed to resolve all symboles in the view. This is not an 
         1149  +        ** error, but it will not be edited. */
         1150  +        sqlite3DbFree(db, sParse.zErrMsg);
         1151  +        sParse.zErrMsg = 0;
         1152  +        rc = SQLITE_OK;
  1091   1153         }
  1092         -    }
  1093         -
  1094         -    for(pFKey=sParse.pNewTable->pFKey; pFKey; pFKey=pFKey->pNextFrom){
  1095         -      for(i=0; i<pFKey->nCol; i++){
  1096         -        if( bFKOnly==0 && pFKey->aCol[i].iFrom==sCtx.iCol ){
  1097         -          renameTokenFind(&sParse, &sCtx, (void*)&pFKey->aCol[i]);
         1154  +      if( rc!=SQLITE_OK ) goto renameColumnFunc_done;
         1155  +    }else{
         1156  +      /* A regular table */
         1157  +      int bFKOnly = sqlite3_stricmp(zTable, sParse.pNewTable->zName);
         1158  +      FKey *pFKey;
         1159  +      assert( sParse.pNewTable->pSelect==0 );
         1160  +      if( bFKOnly==0 ){
         1161  +        renameTokenFind(
         1162  +            &sParse, &sCtx, (void*)sParse.pNewTable->aCol[iCol].zName
         1163  +        );
         1164  +        if( sCtx.iCol<0 ){
         1165  +          renameTokenFind(&sParse, &sCtx, (void*)&sParse.pNewTable->iPKey);
         1166  +        }
         1167  +        sqlite3WalkExprList(&sWalker, sParse.pNewTable->pCheck);
         1168  +        for(pIdx=sParse.pNewTable->pIndex; pIdx; pIdx=pIdx->pNext){
         1169  +          sqlite3WalkExprList(&sWalker, pIdx->aColExpr);
  1098   1170           }
  1099         -        if( 0==sqlite3_stricmp(pFKey->zTo, zTable)
  1100         -         && 0==sqlite3_stricmp(pFKey->aCol[i].zCol, zOld)
  1101         -        ){
  1102         -          renameTokenFind(&sParse, &sCtx, (void*)pFKey->aCol[i].zCol);
         1171  +      }
         1172  +
         1173  +      for(pFKey=sParse.pNewTable->pFKey; pFKey; pFKey=pFKey->pNextFrom){
         1174  +        for(i=0; i<pFKey->nCol; i++){
         1175  +          if( bFKOnly==0 && pFKey->aCol[i].iFrom==sCtx.iCol ){
         1176  +            renameTokenFind(&sParse, &sCtx, (void*)&pFKey->aCol[i]);
         1177  +          }
         1178  +          if( 0==sqlite3_stricmp(pFKey->zTo, zTable)
         1179  +           && 0==sqlite3_stricmp(pFKey->aCol[i].zCol, zOld)
         1180  +          ){
         1181  +            renameTokenFind(&sParse, &sCtx, (void*)pFKey->aCol[i].zCol);
         1182  +          }
  1103   1183           }
  1104   1184         }
  1105   1185       }
  1106   1186     }else if( sParse.pNewIndex ){
  1107   1187       sqlite3WalkExprList(&sWalker, sParse.pNewIndex->aColExpr);
  1108   1188       sqlite3WalkExpr(&sWalker, sParse.pNewIndex->pPartIdxWhere);
  1109   1189     }else{
................................................................................
  1115   1195           if( 0==sqlite3_stricmp(zName, zOld) ){
  1116   1196             renameTokenFind(&sParse, &sCtx, (void*)zName);
  1117   1197           }
  1118   1198         }
  1119   1199       }
  1120   1200     }
  1121   1201   
         1202  +  assert( rc==SQLITE_OK );
  1122   1203     assert( nQuot>=nNew );
  1123   1204     zOut = sqlite3DbMallocZero(db, nSql + sCtx.nList*nQuot + 1);
  1124   1205     if( zOut ){
  1125   1206       int nOut = nSql;
  1126   1207       memcpy(zOut, zSql, nSql);
  1127   1208       while( sCtx.pList ){
  1128   1209         int iOff;                   /* Offset of token to replace in zOut */
................................................................................
  1148   1229         }
  1149   1230         memcpy(&zOut[iOff], zReplace, nReplace);
  1150   1231         sqlite3DbFree(db, pBest);
  1151   1232       }
  1152   1233   
  1153   1234       sqlite3_result_text(context, zOut, -1, SQLITE_TRANSIENT);
  1154   1235       sqlite3DbFree(db, zOut);
         1236  +  }else{
         1237  +    rc = SQLITE_NOMEM;
  1155   1238     }
  1156   1239   
  1157   1240   renameColumnFunc_done:
         1241  +  if( rc!=SQLITE_OK ){
         1242  +    if( zErr ){
         1243  +      sqlite3_result_error(context, zErr, -1);
         1244  +    }else{
         1245  +      sqlite3_result_error_code(context, rc);
         1246  +    }
         1247  +  }
         1248  +
  1158   1249     if( sParse.pVdbe ){
  1159   1250       sqlite3VdbeFinalize(sParse.pVdbe);
  1160   1251     }
  1161   1252     sqlite3DeleteTable(db, sParse.pNewTable);
  1162   1253     if( sParse.pNewIndex ) sqlite3FreeIndex(db, sParse.pNewIndex);
  1163   1254     sqlite3DeleteTrigger(db, sParse.pNewTrigger);
  1164   1255     renameTokenFree(db, sParse.pRename);

Changes to src/build.c.

  1366   1366       }
  1367   1367     }
  1368   1368     if( nTerm==1
  1369   1369      && pCol
  1370   1370      && sqlite3StrICmp(sqlite3ColumnType(pCol,""), "INTEGER")==0
  1371   1371      && sortOrder!=SQLITE_SO_DESC
  1372   1372     ){
         1373  +    if( IN_RENAME_COLUMN && pList ){
         1374  +      sqlite3MoveRenameToken(pParse, &pTab->iPKey, pList->a[0].pExpr);
         1375  +    }
  1373   1376       pTab->iPKey = iCol;
  1374   1377       pTab->keyConf = (u8)onError;
  1375   1378       assert( autoInc==0 || autoInc==1 );
  1376   1379       pTab->tabFlags |= autoInc*TF_Autoincrement;
  1377   1380       if( pList ) pParse->iPkSortOrder = pList->a[0].sortOrder;
  1378   1381     }else if( autoInc ){
  1379   1382   #ifndef SQLITE_OMIT_AUTOINCREMENT
................................................................................
  2168   2171     if( sqlite3FixSelect(&sFix, pSelect) ) goto create_view_fail;
  2169   2172   
  2170   2173     /* Make a copy of the entire SELECT statement that defines the view.
  2171   2174     ** This will force all the Expr.token.z values to be dynamically
  2172   2175     ** allocated rather than point to the input string - which means that
  2173   2176     ** they will persist after the current sqlite3_exec() call returns.
  2174   2177     */
  2175         -  p->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE);
         2178  +  if( IN_RENAME_COLUMN ){
         2179  +    p->pSelect = pSelect;
         2180  +    pSelect = 0;
         2181  +  }else{
         2182  +    p->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE);
         2183  +  }
  2176   2184     p->pCheck = sqlite3ExprListDup(db, pCNames, EXPRDUP_REDUCE);
  2177   2185     if( db->mallocFailed ) goto create_view_fail;
  2178   2186   
  2179   2187     /* Locate the end of the CREATE VIEW statement.  Make sEnd point to
  2180   2188     ** the end.
  2181   2189     */
  2182   2190     sEnd = pParse->sLastToken;

Changes to src/insert.c.

  1174   1174     testcase( w.eCode==0 );
  1175   1175     testcase( w.eCode==CKCNSTRNT_COLUMN );
  1176   1176     testcase( w.eCode==CKCNSTRNT_ROWID );
  1177   1177     testcase( w.eCode==(CKCNSTRNT_ROWID|CKCNSTRNT_COLUMN) );
  1178   1178     return !w.eCode;
  1179   1179   }
  1180   1180   
  1181         -/*
  1182         -** An instance of the ConstraintAddr object remembers the byte-code addresses
  1183         -** for sections of the constraint checks that deal with uniqueness constraints
  1184         -** on the rowid and on the upsert constraint.
  1185         -**
  1186         -** This information is passed into checkReorderConstraintChecks() to insert
  1187         -** some OP_Goto operations so that the rowid and upsert constraints occur
  1188         -** in the correct order relative to other constraints.
  1189         -*/
  1190         -typedef struct ConstraintAddr ConstraintAddr;
  1191         -struct ConstraintAddr {
  1192         -  int ipkTop;          /* Subroutine for rowid constraint check */
  1193         -  int upsertTop;       /* Label for upsert constraint check subroutine */
  1194         -  int upsertTop2;      /* Copy of upsertTop not cleared by the call */
  1195         -  int upsertBtm;       /* upsert constraint returns to this label */
  1196         -  int ipkBtm;          /* Return opcode rowid constraint check */
  1197         -};
  1198         -
  1199         -/*
  1200         -** Generate any OP_Goto operations needed to cause constraints to be
  1201         -** run that haven't already been run.
  1202         -*/
  1203         -static void reorderConstraintChecks(Vdbe *v, ConstraintAddr *p){
  1204         -  if( p->upsertTop ){
  1205         -    testcase( sqlite3VdbeLabelHasBeenResolved(v, p->upsertTop) );
  1206         -    sqlite3VdbeGoto(v, p->upsertTop);
  1207         -    VdbeComment((v, "call upsert subroutine"));
  1208         -    sqlite3VdbeResolveLabel(v, p->upsertBtm);
  1209         -    p->upsertTop = 0;
  1210         -  }
  1211         -  if( p->ipkTop ){
  1212         -    sqlite3VdbeGoto(v, p->ipkTop);
  1213         -    VdbeComment((v, "call rowid unique-check subroutine"));
  1214         -    sqlite3VdbeJumpHere(v, p->ipkBtm);
  1215         -    p->ipkTop = 0;
  1216         -  }
  1217         -}
  1218         -
  1219   1181   /*
  1220   1182   ** Generate code to do constraint checks prior to an INSERT or an UPDATE
  1221   1183   ** on table pTab.
  1222   1184   **
  1223   1185   ** The regNewData parameter is the first register in a range that contains
  1224   1186   ** the data to be inserted or the data after the update.  There will be
  1225   1187   ** pTab->nCol+1 registers in this range.  The first register (the one
................................................................................
  1321   1283     int i;               /* loop counter */
  1322   1284     int ix;              /* Index loop counter */
  1323   1285     int nCol;            /* Number of columns */
  1324   1286     int onError;         /* Conflict resolution strategy */
  1325   1287     int addr1;           /* Address of jump instruction */
  1326   1288     int seenReplace = 0; /* True if REPLACE is used to resolve INT PK conflict */
  1327   1289     int nPkField;        /* Number of fields in PRIMARY KEY. 1 for ROWID tables */
  1328         -  ConstraintAddr sAddr;/* Address information for constraint reordering */
  1329   1290     Index *pUpIdx = 0;   /* Index to which to apply the upsert */
  1330   1291     u8 isUpdate;         /* True if this is an UPDATE operation */
  1331   1292     u8 bAffinityDone = 0;  /* True if the OP_Affinity operation has been run */
  1332   1293     int upsertBypass = 0;  /* Address of Goto to bypass upsert subroutine */
         1294  +  int upsertJump = 0;    /* Address of Goto that jumps into upsert subroutine */
         1295  +  int ipkTop = 0;        /* Top of the IPK uniqueness check */
         1296  +  int ipkBottom = 0;     /* OP_Goto at the end of the IPK uniqueness check */
  1333   1297   
  1334   1298     isUpdate = regOldData!=0;
  1335   1299     db = pParse->db;
  1336   1300     v = sqlite3GetVdbe(pParse);
  1337   1301     assert( v!=0 );
  1338   1302     assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  1339   1303     nCol = pTab->nCol;
  1340         -  memset(&sAddr, 0, sizeof(sAddr));
  1341   1304     
  1342   1305     /* pPk is the PRIMARY KEY index for WITHOUT ROWID tables and NULL for
  1343   1306     ** normal rowid tables.  nPkField is the number of key fields in the 
  1344   1307     ** pPk index or 1 for a rowid table.  In other words, nPkField is the
  1345   1308     ** number of fields in the true primary key of the table. */
  1346   1309     if( HasRowid(pTab) ){
  1347   1310       pPk = 0;
................................................................................
  1437   1400       pParse->iSelfTab = 0;
  1438   1401     }
  1439   1402   #endif /* !defined(SQLITE_OMIT_CHECK) */
  1440   1403   
  1441   1404     /* UNIQUE and PRIMARY KEY constraints should be handled in the following
  1442   1405     ** order:
  1443   1406     **
  1444         -  **   (1)  OE_Abort, OE_Fail, OE_Rollback, OE_Ignore
  1445         -  **   (2)  OE_Update
         1407  +  **   (1)  OE_Update
         1408  +  **   (2)  OE_Abort, OE_Fail, OE_Rollback, OE_Ignore
  1446   1409     **   (3)  OE_Replace
  1447   1410     **
  1448   1411     ** OE_Fail and OE_Ignore must happen before any changes are made.
  1449   1412     ** OE_Update guarantees that only a single row will change, so it
  1450   1413     ** must happen before OE_Replace.  Technically, OE_Abort and OE_Rollback
  1451   1414     ** could happen in any order, but they are grouped up front for
  1452   1415     ** convenience.
         1416  +  **
         1417  +  ** 2018-08-14: Ticket https://www.sqlite.org/src/info/908f001483982c43
         1418  +  ** The order of constraints used to have OE_Update as (2) and OE_Abort
         1419  +  ** and so forth as (1). But apparently PostgreSQL checks the OE_Update
         1420  +  ** constraint before any others, so it had to be moved.
  1453   1421     **
  1454   1422     ** Constraint checking code is generated in this order:
  1455   1423     **   (A)  The rowid constraint
  1456   1424     **   (B)  Unique index constraints that do not have OE_Replace as their
  1457   1425     **        default conflict resolution strategy
  1458   1426     **   (C)  Unique index that do use OE_Replace by default.
  1459   1427     **
................................................................................
  1466   1434       if( pUpsert->pUpsertTarget==0 ){
  1467   1435         /* An ON CONFLICT DO NOTHING clause, without a constraint-target.
  1468   1436         ** Make all unique constraint resolution be OE_Ignore */
  1469   1437         assert( pUpsert->pUpsertSet==0 );
  1470   1438         overrideError = OE_Ignore;
  1471   1439         pUpsert = 0;
  1472   1440       }else if( (pUpIdx = pUpsert->pUpsertIdx)!=0 ){
  1473         -      /* If the constraint-target is on some column other than
  1474         -      ** then ROWID, then we might need to move the UPSERT around
  1475         -      ** so that it occurs in the correct order. */
  1476         -      sAddr.upsertTop = sAddr.upsertTop2 = sqlite3VdbeMakeLabel(v);
  1477         -      sAddr.upsertBtm = sqlite3VdbeMakeLabel(v);
         1441  +      /* If the constraint-target uniqueness check must be run first.
         1442  +      ** Jump to that uniqueness check now */
         1443  +      upsertJump = sqlite3VdbeAddOp0(v, OP_Goto);
         1444  +      VdbeComment((v, "UPSERT constraint goes first"));
  1478   1445       }
  1479   1446     }
  1480   1447   
  1481   1448     /* If rowid is changing, make sure the new rowid does not previously
  1482   1449     ** exist in the table.
  1483   1450     */
  1484   1451     if( pkChng && pPk==0 ){
................................................................................
  1502   1469       }
  1503   1470   
  1504   1471       /* If the response to a rowid conflict is REPLACE but the response
  1505   1472       ** to some other UNIQUE constraint is FAIL or IGNORE, then we need
  1506   1473       ** to defer the running of the rowid conflict checking until after
  1507   1474       ** the UNIQUE constraints have run.
  1508   1475       */
  1509         -    assert( OE_Update>OE_Replace );
  1510         -    assert( OE_Ignore<OE_Replace );
  1511         -    assert( OE_Fail<OE_Replace );
  1512         -    assert( OE_Abort<OE_Replace );
  1513         -    assert( OE_Rollback<OE_Replace );
  1514         -    if( onError>=OE_Replace
  1515         -     && (pUpsert || onError!=overrideError)
  1516         -     && pTab->pIndex
         1476  +    if( onError==OE_Replace      /* IPK rule is REPLACE */
         1477  +     && onError!=overrideError   /* Rules for other contraints are different */
         1478  +     && pTab->pIndex             /* There exist other constraints */
  1517   1479       ){
  1518         -      sAddr.ipkTop = sqlite3VdbeAddOp0(v, OP_Goto)+1;
         1480  +      ipkTop = sqlite3VdbeAddOp0(v, OP_Goto)+1;
         1481  +      VdbeComment((v, "defer IPK REPLACE until last"));
  1519   1482       }
  1520   1483   
  1521   1484       if( isUpdate ){
  1522   1485         /* pkChng!=0 does not mean that the rowid has changed, only that
  1523   1486         ** it might have changed.  Skip the conflict logic below if the rowid
  1524   1487         ** is unchanged. */
  1525   1488         sqlite3VdbeAddOp3(v, OP_Eq, regNewData, addrRowidOk, regOldData);
................................................................................
  1606   1569         case OE_Ignore: {
  1607   1570           testcase( onError==OE_Ignore );
  1608   1571           sqlite3VdbeGoto(v, ignoreDest);
  1609   1572           break;
  1610   1573         }
  1611   1574       }
  1612   1575       sqlite3VdbeResolveLabel(v, addrRowidOk);
  1613         -    if( sAddr.ipkTop ){
  1614         -      sAddr.ipkBtm = sqlite3VdbeAddOp0(v, OP_Goto);
  1615         -      sqlite3VdbeJumpHere(v, sAddr.ipkTop-1);
         1576  +    if( ipkTop ){
         1577  +      ipkBottom = sqlite3VdbeAddOp0(v, OP_Goto);
         1578  +      sqlite3VdbeJumpHere(v, ipkTop-1);
  1616   1579       }
  1617   1580     }
  1618   1581   
  1619   1582     /* Test all UNIQUE constraints by creating entries for each UNIQUE
  1620   1583     ** index and making sure that duplicate entries do not already exist.
  1621   1584     ** Compute the revised record entries for indices as we go.
  1622   1585     **
................................................................................
  1626   1589     for(ix=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, ix++){
  1627   1590       int regIdx;          /* Range of registers hold conent for pIdx */
  1628   1591       int regR;            /* Range of registers holding conflicting PK */
  1629   1592       int iThisCur;        /* Cursor for this UNIQUE index */
  1630   1593       int addrUniqueOk;    /* Jump here if the UNIQUE constraint is satisfied */
  1631   1594   
  1632   1595       if( aRegIdx[ix]==0 ) continue;  /* Skip indices that do not change */
  1633         -    if( bAffinityDone==0 ){
  1634         -      sqlite3TableAffinity(v, pTab, regNewData+1);
  1635         -      bAffinityDone = 1;
  1636         -    }
  1637   1596       if( pUpIdx==pIdx ){
  1638         -      addrUniqueOk = sAddr.upsertBtm;
         1597  +      addrUniqueOk = upsertJump+1;
  1639   1598         upsertBypass = sqlite3VdbeGoto(v, 0);
  1640   1599         VdbeComment((v, "Skip upsert subroutine"));
  1641         -      sqlite3VdbeResolveLabel(v, sAddr.upsertTop2);
         1600  +      sqlite3VdbeJumpHere(v, upsertJump);
  1642   1601       }else{
  1643   1602         addrUniqueOk = sqlite3VdbeMakeLabel(v);
         1603  +    }
         1604  +    if( bAffinityDone==0 && (pUpIdx==0 || pUpIdx==pIdx) ){
         1605  +      sqlite3TableAffinity(v, pTab, regNewData+1);
         1606  +      bAffinityDone = 1;
  1644   1607       }
  1645   1608       VdbeNoopComment((v, "uniqueness check for %s", pIdx->zName));
  1646   1609       iThisCur = iIdxCur+ix;
  1647   1610   
  1648   1611   
  1649   1612       /* Skip partial indices for which the WHERE clause is not true */
  1650   1613       if( pIdx->pPartIdxWhere ){
................................................................................
  1709   1672         if( pUpsert->pUpsertSet==0 ){
  1710   1673           onError = OE_Ignore;  /* DO NOTHING is the same as INSERT OR IGNORE */
  1711   1674         }else{
  1712   1675           onError = OE_Update;  /* DO UPDATE */
  1713   1676         }
  1714   1677       }
  1715   1678   
  1716         -    /* Invoke subroutines to handle IPK replace and upsert prior to running
  1717         -    ** the first REPLACE constraint check. */
  1718         -    if( onError==OE_Replace ){
  1719         -      testcase( sAddr.ipkTop );
  1720         -      testcase( sAddr.upsertTop
  1721         -             && sqlite3VdbeLabelHasBeenResolved(v,sAddr.upsertTop) );
  1722         -      reorderConstraintChecks(v, &sAddr);
  1723         -    }
  1724         -
  1725   1679       /* Collision detection may be omitted if all of the following are true:
  1726   1680       **   (1) The conflict resolution algorithm is REPLACE
  1727   1681       **   (2) The table is a WITHOUT ROWID table
  1728   1682       **   (3) There are no secondary indexes on the table
  1729   1683       **   (4) No delete triggers need to be fired if there is a conflict
  1730   1684       **   (5) No FK constraint counters need to be updated if a conflict occurs.
  1731   1685       */ 
................................................................................
  1839   1793               regR, nPkField, 0, OE_Replace,
  1840   1794               (pIdx==pPk ? ONEPASS_SINGLE : ONEPASS_OFF), iThisCur);
  1841   1795           seenReplace = 1;
  1842   1796           break;
  1843   1797         }
  1844   1798       }
  1845   1799       if( pUpIdx==pIdx ){
         1800  +      sqlite3VdbeGoto(v, upsertJump+1);
  1846   1801         sqlite3VdbeJumpHere(v, upsertBypass);
  1847   1802       }else{
  1848   1803         sqlite3VdbeResolveLabel(v, addrUniqueOk);
  1849   1804       }
  1850   1805       if( regR!=regIdx ) sqlite3ReleaseTempRange(pParse, regR, nPkField);
         1806  +  }
  1851   1807   
         1808  +  /* If the IPK constraint is a REPLACE, run it last */
         1809  +  if( ipkTop ){
         1810  +    sqlite3VdbeGoto(v, ipkTop+1);
         1811  +    VdbeComment((v, "Do IPK REPLACE"));
         1812  +    sqlite3VdbeJumpHere(v, ipkBottom);
  1852   1813     }
  1853         -  testcase( sAddr.ipkTop!=0 );
  1854         -  testcase( sAddr.upsertTop
  1855         -         && sqlite3VdbeLabelHasBeenResolved(v,sAddr.upsertTop) );
  1856         -  reorderConstraintChecks(v, &sAddr);
  1857         -  
         1814  +
  1858   1815     *pbMayReplace = seenReplace;
  1859   1816     VdbeModuleComment((v, "END: GenCnstCks(%d)", seenReplace));
  1860   1817   }
  1861   1818   
  1862   1819   #ifdef SQLITE_ENABLE_NULL_TRIM
  1863   1820   /*
  1864   1821   ** Change the P5 operand on the last opcode (which should be an OP_MakeRecord)

Changes to src/os_unix.c.

   698    698   ** Function unixMutexHeld() is used to assert() that the global mutex 
   699    699   ** is held when required. This function is only used as part of assert() 
   700    700   ** statements. e.g.
   701    701   **
   702    702   **   unixEnterMutex()
   703    703   **     assert( unixMutexHeld() );
   704    704   **   unixEnterLeave()
          705  +**
          706  +** To prevent deadlock, the global unixBigLock must must be acquired
          707  +** before the unixInodeInfo.pLockMutex mutex, if both are held.  It is
          708  +** OK to get the pLockMutex without holding unixBigLock first, but if
          709  +** that happens, the unixBigLock mutex must not be acquired until after
          710  +** pLockMutex is released.
          711  +**
          712  +**      OK:     enter(unixBigLock),  enter(pLockInfo)
          713  +**      OK:     enter(unixBigLock)
          714  +**      OK:     enter(pLockInfo)
          715  +**   ERROR:     enter(pLockInfo), enter(unixBigLock)
   705    716   */
   706    717   static sqlite3_mutex *unixBigLock = 0;
   707    718   static void unixEnterMutex(void){
          719  +  assert( sqlite3_mutex_notheld(unixBigLock) );  /* Not a recursive mutex */
   708    720     sqlite3_mutex_enter(unixBigLock);
   709    721   }
   710    722   static void unixLeaveMutex(void){
          723  +  assert( sqlite3_mutex_held(unixBigLock) );
   711    724     sqlite3_mutex_leave(unixBigLock);
   712    725   }
   713    726   #ifdef SQLITE_DEBUG
   714    727   static int unixMutexHeld(void) {
   715    728     return sqlite3_mutex_held(unixBigLock);
   716    729   }
   717    730   #endif
................................................................................
  1107   1120   **
  1108   1121   ** A single inode can have multiple file descriptors, so each unixFile
  1109   1122   ** structure contains a pointer to an instance of this object and this
  1110   1123   ** object keeps a count of the number of unixFile pointing to it.
  1111   1124   **
  1112   1125   ** Mutex rules:
  1113   1126   **
  1114         -**  (1) The pLockMutex mutex must be held in order to read or write
         1127  +**  (1) Only the pLockMutex mutex must be held in order to read or write
  1115   1128   **      any of the locking fields:
  1116         -**          nShared, nLock, eFileLock, or bProcessLock
         1129  +**          nShared, nLock, eFileLock, bProcessLock, pUnused
  1117   1130   **
  1118   1131   **  (2) When nRef>0, then the following fields are unchanging and can
  1119   1132   **      be read (but not written) without holding any mutex:
  1120   1133   **          fileId, pLockMutex
  1121   1134   **
  1122   1135   **  (3) With the exceptions above, all the fields may only be read
  1123   1136   **      or written while holding the global unixBigLock mutex.
         1137  +**
         1138  +** Deadlock prevention:  The global unixBigLock mutex may not
         1139  +** be acquired while holding the pLockMutex mutex.  If both unixBigLock
         1140  +** and pLockMutex are needed, then unixBigLock must be acquired first.
  1124   1141   */
  1125   1142   struct unixInodeInfo {
  1126   1143     struct unixFileId fileId;       /* The lookup key */
  1127   1144     sqlite3_mutex *pLockMutex;      /* Hold this mutex for... */
  1128   1145     int nShared;                      /* Number of SHARED locks held */
  1129   1146     int nLock;                        /* Number of outstanding file locks */
  1130   1147     unsigned char eFileLock;          /* One of SHARED_LOCK, RESERVED_LOCK etc. */
  1131   1148     unsigned char bProcessLock;       /* An exclusive process lock is held */
         1149  +  UnixUnusedFd *pUnused;            /* Unused file descriptors to close */
  1132   1150     int nRef;                       /* Number of pointers to this structure */
  1133   1151     unixShmNode *pShmNode;          /* Shared memory associated with this inode */
  1134         -  UnixUnusedFd *pUnused;          /* Unused file descriptors to close */
  1135   1152     unixInodeInfo *pNext;           /* List of all unixInodeInfo objects */
  1136   1153     unixInodeInfo *pPrev;           /*    .... doubly linked */
  1137   1154   #if SQLITE_ENABLE_LOCKING_STYLE
  1138   1155     unsigned long long sharedByte;  /* for AFP simulated shared lock */
  1139   1156   #endif
  1140   1157   #if OS_VXWORKS
  1141   1158     sem_t *pSem;                    /* Named POSIX semaphore */
................................................................................
  1143   1160   #endif
  1144   1161   };
  1145   1162   
  1146   1163   /*
  1147   1164   ** A lists of all unixInodeInfo objects.
  1148   1165   */
  1149   1166   static unixInodeInfo *inodeList = 0;  /* All unixInodeInfo objects */
  1150         -static unsigned int nUnusedFd = 0;    /* Total unused file descriptors */
         1167  +
         1168  +#ifdef SQLITE_DEBUG
         1169  +/*
         1170  +** True if the inode mutex is held, or not.  Used only within assert()
         1171  +** to help verify correct mutex usage.
         1172  +*/
         1173  +int unixFileMutexHeld(unixFile *pFile){
         1174  +  assert( pFile->pInode );
         1175  +  return sqlite3_mutex_held(pFile->pInode->pLockMutex);
         1176  +}
         1177  +int unixFileMutexNotheld(unixFile *pFile){
         1178  +  assert( pFile->pInode );
         1179  +  return sqlite3_mutex_notheld(pFile->pInode->pLockMutex);
         1180  +}
         1181  +#endif
  1151   1182   
  1152   1183   /*
  1153   1184   **
  1154   1185   ** This function - unixLogErrorAtLine(), is only ever called via the macro
  1155   1186   ** unixLogError().
  1156   1187   **
  1157   1188   ** It is invoked after an error occurs in an OS function and errno has been
................................................................................
  1249   1280   /*
  1250   1281   ** Close all file descriptors accumuated in the unixInodeInfo->pUnused list.
  1251   1282   */ 
  1252   1283   static void closePendingFds(unixFile *pFile){
  1253   1284     unixInodeInfo *pInode = pFile->pInode;
  1254   1285     UnixUnusedFd *p;
  1255   1286     UnixUnusedFd *pNext;
         1287  +  assert( unixFileMutexHeld(pFile) );
  1256   1288     for(p=pInode->pUnused; p; p=pNext){
  1257   1289       pNext = p->pNext;
  1258   1290       robust_close(pFile, p->fd, __LINE__);
  1259   1291       sqlite3_free(p);
  1260         -    nUnusedFd--;
  1261   1292     }
  1262   1293     pInode->pUnused = 0;
  1263   1294   }
  1264   1295   
  1265   1296   /*
  1266   1297   ** Release a unixInodeInfo structure previously allocated by findInodeInfo().
  1267   1298   **
  1268   1299   ** The mutex entered using the unixEnterMutex() function must be held
  1269   1300   ** when this function is called.
  1270   1301   */
  1271   1302   static void releaseInodeInfo(unixFile *pFile){
  1272   1303     unixInodeInfo *pInode = pFile->pInode;
  1273   1304     assert( unixMutexHeld() );
         1305  +  assert( unixFileMutexNotheld(pFile) );
  1274   1306     if( ALWAYS(pInode) ){
  1275   1307       pInode->nRef--;
  1276   1308       if( pInode->nRef==0 ){
  1277   1309         assert( pInode->pShmNode==0 );
         1310  +      sqlite3_mutex_enter(pInode->pLockMutex);
  1278   1311         closePendingFds(pFile);
         1312  +      sqlite3_mutex_leave(pInode->pLockMutex);
  1279   1313         if( pInode->pPrev ){
  1280   1314           assert( pInode->pPrev->pNext==pInode );
  1281   1315           pInode->pPrev->pNext = pInode->pNext;
  1282   1316         }else{
  1283   1317           assert( inodeList==pInode );
  1284   1318           inodeList = pInode->pNext;
  1285   1319         }
................................................................................
  1287   1321           assert( pInode->pNext->pPrev==pInode );
  1288   1322           pInode->pNext->pPrev = pInode->pPrev;
  1289   1323         }
  1290   1324         sqlite3_mutex_free(pInode->pLockMutex);
  1291   1325         sqlite3_free(pInode);
  1292   1326       }
  1293   1327     }
  1294         -  assert( inodeList!=0 || nUnusedFd==0 );
  1295   1328   }
  1296   1329   
  1297   1330   /*
  1298   1331   ** Given a file descriptor, locate the unixInodeInfo object that
  1299   1332   ** describes that file descriptor.  Create a new one if necessary.  The
  1300   1333   ** return value might be uninitialized if an error occurs.
  1301   1334   **
................................................................................
  1357   1390     memset(&fileId, 0, sizeof(fileId));
  1358   1391     fileId.dev = statbuf.st_dev;
  1359   1392   #if OS_VXWORKS
  1360   1393     fileId.pId = pFile->pId;
  1361   1394   #else
  1362   1395     fileId.ino = (u64)statbuf.st_ino;
  1363   1396   #endif
  1364         -  assert( inodeList!=0 || nUnusedFd==0 );
  1365   1397     pInode = inodeList;
  1366   1398     while( pInode && memcmp(&fileId, &pInode->fileId, sizeof(fileId)) ){
  1367   1399       pInode = pInode->pNext;
  1368   1400     }
  1369   1401     if( pInode==0 ){
  1370   1402       pInode = sqlite3_malloc64( sizeof(*pInode) );
  1371   1403       if( pInode==0 ){
................................................................................
  1822   1854   /*
  1823   1855   ** Add the file descriptor used by file handle pFile to the corresponding
  1824   1856   ** pUnused list.
  1825   1857   */
  1826   1858   static void setPendingFd(unixFile *pFile){
  1827   1859     unixInodeInfo *pInode = pFile->pInode;
  1828   1860     UnixUnusedFd *p = pFile->pPreallocatedUnused;
         1861  +  assert( unixFileMutexHeld(pFile) );
  1829   1862     p->pNext = pInode->pUnused;
  1830   1863     pInode->pUnused = p;
  1831   1864     pFile->h = -1;
  1832   1865     pFile->pPreallocatedUnused = 0;
  1833         -  nUnusedFd++;
  1834   1866   }
  1835   1867   
  1836   1868   /*
  1837   1869   ** Lower the locking level on file descriptor pFile to eFileLock.  eFileLock
  1838   1870   ** must be either NO_LOCK or SHARED_LOCK.
  1839   1871   **
  1840   1872   ** If the locking level of the file descriptor is already at or below
................................................................................
  1984   2016   
  1985   2017       /* Decrement the count of locks against this same file.  When the
  1986   2018       ** count reaches zero, close any other file descriptors whose close
  1987   2019       ** was deferred because of outstanding locks.
  1988   2020       */
  1989   2021       pInode->nLock--;
  1990   2022       assert( pInode->nLock>=0 );
  1991         -    if( pInode->nLock==0 ){
  1992         -      closePendingFds(pFile);
  1993         -    }
         2023  +    if( pInode->nLock==0 ) closePendingFds(pFile);
  1994   2024     }
  1995   2025   
  1996   2026   end_unlock:
  1997   2027     sqlite3_mutex_leave(pInode->pLockMutex);
  1998         -  if( rc==SQLITE_OK ) pFile->eFileLock = eFileLock;
         2028  +  if( rc==SQLITE_OK ){
         2029  +    pFile->eFileLock = eFileLock;
         2030  +  }
  1999   2031     return rc;
  2000   2032   }
  2001   2033   
  2002   2034   /*
  2003   2035   ** Lower the locking level on file descriptor pFile to eFileLock.  eFileLock
  2004   2036   ** must be either NO_LOCK or SHARED_LOCK.
  2005   2037   **
................................................................................
  2062   2094   
  2063   2095   /*
  2064   2096   ** Close a file.
  2065   2097   */
  2066   2098   static int unixClose(sqlite3_file *id){
  2067   2099     int rc = SQLITE_OK;
  2068   2100     unixFile *pFile = (unixFile *)id;
         2101  +  unixInodeInfo *pInode = pFile->pInode;
         2102  +
         2103  +  assert( pInode!=0 );
  2069   2104     verifyDbFile(pFile);
  2070   2105     unixUnlock(id, NO_LOCK);
         2106  +  assert( unixFileMutexNotheld(pFile) );
  2071   2107     unixEnterMutex();
  2072   2108   
  2073   2109     /* unixFile.pInode is always valid here. Otherwise, a different close
  2074   2110     ** routine (e.g. nolockClose()) would be called instead.
  2075   2111     */
  2076   2112     assert( pFile->pInode->nLock>0 || pFile->pInode->bProcessLock==0 );
  2077         -  if( ALWAYS(pFile->pInode) && pFile->pInode->nLock ){
         2113  +  sqlite3_mutex_enter(pInode->pLockMutex);
         2114  +  if( pFile->pInode->nLock ){
  2078   2115       /* If there are outstanding locks, do not actually close the file just
  2079   2116       ** yet because that would clear those locks.  Instead, add the file
  2080   2117       ** descriptor to pInode->pUnused list.  It will be automatically closed 
  2081   2118       ** when the last lock is cleared.
  2082   2119       */
  2083   2120       setPendingFd(pFile);
  2084   2121     }
         2122  +  sqlite3_mutex_leave(pInode->pLockMutex);
  2085   2123     releaseInodeInfo(pFile);
  2086   2124     rc = closeUnixFile(id);
  2087   2125     unixLeaveMutex();
  2088   2126     return rc;
  2089   2127   }
  2090   2128   
  2091   2129   /************** End of the posix advisory lock implementation *****************
................................................................................
  2675   2713    ** Close a file.
  2676   2714    */
  2677   2715   static int semXClose(sqlite3_file *id) {
  2678   2716     if( id ){
  2679   2717       unixFile *pFile = (unixFile*)id;
  2680   2718       semXUnlock(id, NO_LOCK);
  2681   2719       assert( pFile );
         2720  +    assert( unixFileMutexNotheld(pFile) );
  2682   2721       unixEnterMutex();
  2683   2722       releaseInodeInfo(pFile);
  2684   2723       unixLeaveMutex();
  2685   2724       closeUnixFile(id);
  2686   2725     }
  2687   2726     return SQLITE_OK;
  2688   2727   }
................................................................................
  3115   3154           pInode->eFileLock = NO_LOCK;
  3116   3155           pFile->eFileLock = NO_LOCK;
  3117   3156         }
  3118   3157       }
  3119   3158       if( rc==SQLITE_OK ){
  3120   3159         pInode->nLock--;
  3121   3160         assert( pInode->nLock>=0 );
  3122         -      if( pInode->nLock==0 ){
  3123         -        closePendingFds(pFile);
  3124         -      }
         3161  +      if( pInode->nLock==0 ) closePendingFds(pFile);
  3125   3162       }
  3126   3163     }
  3127   3164     
  3128   3165     sqlite3_mutex_leave(pInode->pLockMutex);
  3129         -  if( rc==SQLITE_OK ) pFile->eFileLock = eFileLock;
         3166  +  if( rc==SQLITE_OK ){
         3167  +    pFile->eFileLock = eFileLock;
         3168  +  }
  3130   3169     return rc;
  3131   3170   }
  3132   3171   
  3133   3172   /*
  3134   3173   ** Close a file & cleanup AFP specific locking context 
  3135   3174   */
  3136   3175   static int afpClose(sqlite3_file *id) {
  3137   3176     int rc = SQLITE_OK;
  3138   3177     unixFile *pFile = (unixFile*)id;
  3139   3178     assert( id!=0 );
  3140   3179     afpUnlock(id, NO_LOCK);
         3180  +  assert( unixFileMutexNotheld(pFile) );
  3141   3181     unixEnterMutex();
  3142         -  if( pFile->pInode && pFile->pInode->nLock ){
  3143         -    /* If there are outstanding locks, do not actually close the file just
  3144         -    ** yet because that would clear those locks.  Instead, add the file
  3145         -    ** descriptor to pInode->aPending.  It will be automatically closed when
  3146         -    ** the last lock is cleared.
  3147         -    */
  3148         -    setPendingFd(pFile);
         3182  +  if( pFile->pInode ){
         3183  +    unixInodeInfo *pInode = pFile->pInode;
         3184  +    sqlite3_mutex_enter(pInode->pLockMutex);
         3185  +    if( pFile->pInode->nLock ){
         3186  +      /* If there are outstanding locks, do not actually close the file just
         3187  +      ** yet because that would clear those locks.  Instead, add the file
         3188  +      ** descriptor to pInode->aPending.  It will be automatically closed when
         3189  +      ** the last lock is cleared.
         3190  +      */
         3191  +      setPendingFd(pFile);
         3192  +    }
         3193  +    sqlite3_mutex_leave(pInode->pLockMutex);
  3149   3194     }
  3150   3195     releaseInodeInfo(pFile);
  3151   3196     sqlite3_free(pFile->lockingContext);
  3152   3197     rc = closeUnixFile(id);
  3153   3198     unixLeaveMutex();
  3154   3199     return rc;
  3155   3200   }
................................................................................
  4447   4492     if( p==0 ) return SQLITE_NOMEM_BKPT;
  4448   4493     memset(p, 0, sizeof(*p));
  4449   4494     assert( pDbFd->pShm==0 );
  4450   4495   
  4451   4496     /* Check to see if a unixShmNode object already exists. Reuse an existing
  4452   4497     ** one if present. Create a new one if necessary.
  4453   4498     */
         4499  +  assert( unixFileMutexNotheld(pDbFd) );
  4454   4500     unixEnterMutex();
  4455   4501     pInode = pDbFd->pInode;
  4456   4502     pShmNode = pInode->pShmNode;
  4457   4503     if( pShmNode==0 ){
  4458   4504       struct stat sStat;                 /* fstat() info for database file */
  4459   4505   #ifndef SQLITE_SHM_DIRECTORY
  4460   4506       const char *zBasePath = pDbFd->zPath;
................................................................................
  4829   4875   ** any load or store begun after the barrier.
  4830   4876   */
  4831   4877   static void unixShmBarrier(
  4832   4878     sqlite3_file *fd                /* Database file holding the shared memory */
  4833   4879   ){
  4834   4880     UNUSED_PARAMETER(fd);
  4835   4881     sqlite3MemoryBarrier();         /* compiler-defined memory barrier */
         4882  +  assert( unixFileMutexNotheld((unixFile*)fd) );
  4836   4883     unixEnterMutex();               /* Also mutex, for redundancy */
  4837   4884     unixLeaveMutex();
  4838   4885   }
  4839   4886   
  4840   4887   /*
  4841   4888   ** Close a connection to shared-memory.  Delete the underlying 
  4842   4889   ** storage if deleteFlag is true.
................................................................................
  4870   4917     /* Free the connection p */
  4871   4918     sqlite3_free(p);
  4872   4919     pDbFd->pShm = 0;
  4873   4920     sqlite3_mutex_leave(pShmNode->mutex);
  4874   4921   
  4875   4922     /* If pShmNode->nRef has reached 0, then close the underlying
  4876   4923     ** shared-memory file, too */
         4924  +  assert( unixFileMutexNotheld(pDbFd) );
  4877   4925     unixEnterMutex();
  4878   4926     assert( pShmNode->nRef>0 );
  4879   4927     pShmNode->nRef--;
  4880   4928     if( pShmNode->nRef==0 ){
  4881   4929       if( deleteFlag && pShmNode->h>=0 ){
  4882   4930         osUnlink(pShmNode->zFilename);
  4883   4931       }
................................................................................
  5196   5244     unixUnlock,               /* xUnlock method */
  5197   5245     unixCheckReservedLock,    /* xCheckReservedLock method */
  5198   5246     unixShmMap                /* xShmMap method */
  5199   5247   )
  5200   5248   IOMETHODS(
  5201   5249     nolockIoFinder,           /* Finder function name */
  5202   5250     nolockIoMethods,          /* sqlite3_io_methods object name */
  5203         -  3,                        /* shared memory is disabled */
         5251  +  3,                        /* shared memory and mmap are enabled */
  5204   5252     nolockClose,              /* xClose method */
  5205   5253     nolockLock,               /* xLock method */
  5206   5254     nolockUnlock,             /* xUnlock method */
  5207   5255     nolockCheckReservedLock,  /* xCheckReservedLock method */
  5208   5256     0                         /* xShmMap method */
  5209   5257   )
  5210   5258   IOMETHODS(
................................................................................
  5692   5740     ** almost certain that an open() call on the same path will also fail.
  5693   5741     ** For this reason, if an error occurs in the stat() call here, it is
  5694   5742     ** ignored and -1 is returned. The caller will try to open a new file
  5695   5743     ** descriptor on the same path, fail, and return an error to SQLite.
  5696   5744     **
  5697   5745     ** Even if a subsequent open() call does succeed, the consequences of
  5698   5746     ** not searching for a reusable file descriptor are not dire.  */
  5699         -  if( nUnusedFd>0 && 0==osStat(zPath, &sStat) ){
         5747  +  if( inodeList!=0 && 0==osStat(zPath, &sStat) ){
  5700   5748       unixInodeInfo *pInode;
  5701   5749   
  5702   5750       pInode = inodeList;
  5703   5751       while( pInode && (pInode->fileId.dev!=sStat.st_dev
  5704   5752                        || pInode->fileId.ino!=(u64)sStat.st_ino) ){
  5705   5753          pInode = pInode->pNext;
  5706   5754       }
  5707   5755       if( pInode ){
  5708   5756         UnixUnusedFd **pp;
         5757  +      assert( sqlite3_mutex_notheld(pInode->pLockMutex) );
         5758  +      sqlite3_mutex_enter(pInode->pLockMutex);
  5709   5759         for(pp=&pInode->pUnused; *pp && (*pp)->flags!=flags; pp=&((*pp)->pNext));
  5710   5760         pUnused = *pp;
  5711   5761         if( pUnused ){
  5712         -        nUnusedFd--;
  5713   5762           *pp = pUnused->pNext;
  5714   5763         }
         5764  +      sqlite3_mutex_leave(pInode->pLockMutex);
  5715   5765       }
  5716   5766     }
  5717   5767     unixLeaveMutex();
  5718   5768   #endif    /* if !OS_VXWORKS */
  5719   5769     return pUnused;
  5720   5770   }
  5721   5771   

Changes to src/prepare.c.

    21     21   */
    22     22   static void corruptSchema(
    23     23     InitData *pData,     /* Initialization context */
    24     24     const char *zObj,    /* Object being parsed at the point of error */
    25     25     const char *zExtra   /* Error information */
    26     26   ){
    27     27     sqlite3 *db = pData->db;
    28         -  if( !db->mallocFailed && (db->flags & SQLITE_WriteSchema)==0 ){
           28  +  if( db->mallocFailed ){
           29  +    pData->rc = SQLITE_NOMEM_BKPT;
           30  +  }else if( pData->pzErrMsg[0]!=0 ){
           31  +    /* A error message has already been generated.  Do not overwrite it */
           32  +  }else if( pData->mInitFlags & INITFLAG_AlterTable ){
           33  +    *pData->pzErrMsg = sqlite3DbStrDup(db, zExtra);
           34  +    pData->rc = SQLITE_ERROR;
           35  +  }else if( db->flags & SQLITE_WriteSchema ){
           36  +    pData->rc = SQLITE_CORRUPT_BKPT;
           37  +  }else{
    29     38       char *z;
    30     39       if( zObj==0 ) zObj = "?";
    31     40       z = sqlite3MPrintf(db, "malformed database schema (%s)", zObj);
    32     41       if( zExtra && zExtra[0] ) z = sqlite3MPrintf(db, "%z - %s", z, zExtra);
    33         -    sqlite3DbFree(db, *pData->pzErrMsg);
    34     42       *pData->pzErrMsg = z;
           43  +    pData->rc = SQLITE_CORRUPT_BKPT;
    35     44     }
    36         -  pData->rc = db->mallocFailed ? SQLITE_NOMEM_BKPT : SQLITE_CORRUPT_BKPT;
    37     45   }
    38     46   
    39     47   /*
    40     48   ** This is the callback routine for the code that initializes the
    41     49   ** database.  See sqlite3Init() below for additional information.
    42     50   ** This routine is also called from the OP_ParseSchema opcode of the VDBE.
    43     51   **
................................................................................
   128    136   ** Attempt to read the database schema and initialize internal
   129    137   ** data structures for a single database file.  The index of the
   130    138   ** database file is given by iDb.  iDb==0 is used for the main
   131    139   ** database.  iDb==1 should never be used.  iDb>=2 is used for
   132    140   ** auxiliary databases.  Return one of the SQLITE_ error codes to
   133    141   ** indicate success or failure.
   134    142   */
   135         -int sqlite3InitOne(sqlite3 *db, int iDb, char **pzErrMsg){
          143  +int sqlite3InitOne(sqlite3 *db, int iDb, char **pzErrMsg, u32 mFlags){
   136    144     int rc;
   137    145     int i;
   138    146   #ifndef SQLITE_OMIT_DEPRECATED
   139    147     int size;
   140    148   #endif
   141    149     Db *pDb;
   142    150     char const *azArg[4];
................................................................................
   163    171     azArg[2] = "CREATE TABLE x(type text,name text,tbl_name text,"
   164    172                               "rootpage int,sql text)";
   165    173     azArg[3] = 0;
   166    174     initData.db = db;
   167    175     initData.iDb = iDb;
   168    176     initData.rc = SQLITE_OK;
   169    177     initData.pzErrMsg = pzErrMsg;
          178  +  initData.mInitFlags = mFlags;
   170    179     sqlite3InitCallback(&initData, 3, (char **)azArg, 0);
   171    180     if( initData.rc ){
   172    181       rc = initData.rc;
   173    182       goto error_out;
   174    183     }
   175    184   
   176    185     /* Create a cursor to hold the database open
................................................................................
   369    378     assert( sqlite3_mutex_held(db->mutex) );
   370    379     assert( sqlite3BtreeHoldsMutex(db->aDb[0].pBt) );
   371    380     assert( db->init.busy==0 );
   372    381     ENC(db) = SCHEMA_ENC(db);
   373    382     assert( db->nDb>0 );
   374    383     /* Do the main schema first */
   375    384     if( !DbHasProperty(db, 0, DB_SchemaLoaded) ){
   376         -    rc = sqlite3InitOne(db, 0, pzErrMsg);
          385  +    rc = sqlite3InitOne(db, 0, pzErrMsg, 0);
   377    386       if( rc ) return rc;
   378    387     }
   379    388     /* All other schemas after the main schema. The "temp" schema must be last */
   380    389     for(i=db->nDb-1; i>0; i--){
   381    390       assert( i==1 || sqlite3BtreeHoldsMutex(db->aDb[i].pBt) );
   382    391       if( !DbHasProperty(db, i, DB_SchemaLoaded) ){
   383         -      rc = sqlite3InitOne(db, i, pzErrMsg);
          392  +      rc = sqlite3InitOne(db, i, pzErrMsg, 0);
   384    393         if( rc ) return rc;
   385    394       }
   386    395     }
   387    396     if( commit_internal ){
   388    397       sqlite3CommitInternalChanges(db);
   389    398     }
   390    399     return SQLITE_OK;

Changes to src/sqliteInt.h.

  3325   3325   ** from sqlite3Init and OP_ParseSchema into the sqlite3InitCallback.
  3326   3326   */
  3327   3327   typedef struct {
  3328   3328     sqlite3 *db;        /* The database being initialized */
  3329   3329     char **pzErrMsg;    /* Error message stored here */
  3330   3330     int iDb;            /* 0 for main database.  1 for TEMP, 2.. for ATTACHed */
  3331   3331     int rc;             /* Result code stored here */
         3332  +  u32 mInitFlags;     /* Flags controlling error messages */
  3332   3333   } InitData;
  3333   3334   
         3335  +/*
         3336  +** Allowed values for mInitFlags
         3337  +*/
         3338  +#define INITFLAG_AlterTable   0x0001  /* This is a reparse after ALTER TABLE */
         3339  +
  3334   3340   /*
  3335   3341   ** Structure containing global configuration data for the SQLite library.
  3336   3342   **
  3337   3343   ** This structure also contains some state information.
  3338   3344   */
  3339   3345   struct Sqlite3Config {
  3340   3346     int bMemstat;                     /* True to enable memory status */
................................................................................
  3797   3803   void sqlite3ExprListSetSortOrder(ExprList*,int);
  3798   3804   void sqlite3ExprListSetName(Parse*,ExprList*,Token*,int);
  3799   3805   void sqlite3ExprListSetSpan(Parse*,ExprList*,const char*,const char*);
  3800   3806   void sqlite3ExprListDelete(sqlite3*, ExprList*);
  3801   3807   u32 sqlite3ExprListFlags(const ExprList*);
  3802   3808   int sqlite3Init(sqlite3*, char**);
  3803   3809   int sqlite3InitCallback(void*, int, char**, char**);
  3804         -int sqlite3InitOne(sqlite3*, int, char**);
         3810  +int sqlite3InitOne(sqlite3*, int, char**, u32);
  3805   3811   void sqlite3Pragma(Parse*,Token*,Token*,Token*,int);
  3806   3812   #ifndef SQLITE_OMIT_VIRTUALTABLE
  3807   3813   Module *sqlite3PragmaVtabRegister(sqlite3*,const char *zName);
  3808   3814   #endif
  3809   3815   void sqlite3ResetAllSchemasOfConnection(sqlite3*);
  3810   3816   void sqlite3ResetOneSchema(sqlite3*,int);
  3811   3817   void sqlite3CollapseDatabaseArray(sqlite3*);

Changes to src/vdbe.c.

  5718   5718     if( rc ) goto abort_due_to_error;
  5719   5719     break;
  5720   5720   }
  5721   5721   
  5722   5722   /* Opcode: ParseSchema P1 * * P4 *
  5723   5723   **
  5724   5724   ** Read and parse all entries from the SQLITE_MASTER table of database P1
  5725         -** that match the WHERE clause P4. 
         5725  +** that match the WHERE clause P4.  If P4 is a NULL pointer, then the
         5726  +** entire schema for P1 is reparsed.
  5726   5727   **
  5727   5728   ** This opcode invokes the parser to create a new virtual machine,
  5728   5729   ** then runs the new virtual machine.  It is thus a re-entrant opcode.
  5729   5730   */
  5730   5731   case OP_ParseSchema: {
  5731   5732     int iDb;
  5732   5733     const char *zMaster;
................................................................................
  5747   5748     assert( iDb>=0 && iDb<db->nDb );
  5748   5749     assert( DbHasProperty(db, iDb, DB_SchemaLoaded) );
  5749   5750   
  5750   5751   #ifndef SQLITE_OMIT_ALTERTABLE
  5751   5752     if( pOp->p4.z==0 ){
  5752   5753       sqlite3SchemaClear(db->aDb[iDb].pSchema);
  5753   5754       db->mDbFlags &= ~DBFLAG_SchemaKnownOk;
  5754         -    rc = sqlite3InitOne(db, iDb, &p->zErrMsg);
         5755  +    rc = sqlite3InitOne(db, iDb, &p->zErrMsg, INITFLAG_AlterTable);
  5755   5756       db->mDbFlags |= DBFLAG_SchemaChange;
  5756   5757     }else
  5757   5758   #endif
  5758         -  /* Used to be a conditional */ {
         5759  +  {
  5759   5760       zMaster = MASTER_NAME;
  5760   5761       initData.db = db;
  5761   5762       initData.iDb = pOp->p1;
  5762   5763       initData.pzErrMsg = &p->zErrMsg;
  5763   5764       zSql = sqlite3MPrintf(db,
  5764   5765          "SELECT name, rootpage, sql FROM '%q'.%s WHERE %s ORDER BY rowid",
  5765   5766          db->aDb[iDb].zDbSName, zMaster, pOp->p4.z);

Changes to src/vdbe.h.

   234    234   void sqlite3VdbeRunOnlyOnce(Vdbe*);
   235    235   void sqlite3VdbeReusable(Vdbe*);
   236    236   void sqlite3VdbeDelete(Vdbe*);
   237    237   void sqlite3VdbeClearObject(sqlite3*,Vdbe*);
   238    238   void sqlite3VdbeMakeReady(Vdbe*,Parse*);
   239    239   int sqlite3VdbeFinalize(Vdbe*);
   240    240   void sqlite3VdbeResolveLabel(Vdbe*, int);
   241         -#ifdef SQLITE_COVERAGE_TEST
   242         -  int sqlite3VdbeLabelHasBeenResolved(Vdbe*,int);
   243         -#endif
   244    241   int sqlite3VdbeCurrentAddr(Vdbe*);
   245    242   #ifdef SQLITE_DEBUG
   246    243     int sqlite3VdbeAssertMayAbort(Vdbe *, int);
   247    244   #endif
   248    245   void sqlite3VdbeResetStepResult(Vdbe*);
   249    246   void sqlite3VdbeRewind(Vdbe*);
   250    247   int sqlite3VdbeReset(Vdbe*);

Changes to src/vdbeaux.c.

   433    433       }
   434    434   #endif
   435    435       assert( p->aLabel[j]==(-1) ); /* Labels may only be resolved once */
   436    436       p->aLabel[j] = v->nOp;
   437    437     }
   438    438   }
   439    439   
   440         -#ifdef SQLITE_COVERAGE_TEST
   441         -/*
   442         -** Return TRUE if and only if the label x has already been resolved.
   443         -** Return FALSE (zero) if label x is still unresolved.
   444         -**
   445         -** This routine is only used inside of testcase() macros, and so it
   446         -** only exists when measuring test coverage.
   447         -*/
   448         -int sqlite3VdbeLabelHasBeenResolved(Vdbe *v, int x){
   449         -  return v->pParse->aLabel && v->pParse->aLabel[ADDR(x)]>=0;
   450         -}
   451         -#endif /* SQLITE_COVERAGE_TEST */
   452         -
   453    440   /*
   454    441   ** Mark the VDBE as one that can only be run one time.
   455    442   */
   456    443   void sqlite3VdbeRunOnlyOnce(Vdbe *p){
   457    444     p->runOnlyOnce = 1;
   458    445   }
   459    446   

Changes to test/altercol.test.

    62     62   
    63     63    12 {CREATE TABLE t1(a, b, c);   CREATE INDEX t1i ON t1(b+b+b+b, c) WHERE b>0}
    64     64       {{CREATE TABLE t1(a, d, c)} {CREATE INDEX t1i ON t1(d+d+d+d, c) WHERE d>0}}
    65     65   
    66     66    13 {CREATE TABLE t1(a, b, c, FOREIGN KEY (b) REFERENCES t2)}
    67     67       {CREATE TABLE t1(a, d, c, FOREIGN KEY (d) REFERENCES t2)}
    68     68   
           69  + 15 {CREATE TABLE t1(a INTEGER, b INTEGER, c BLOB, PRIMARY KEY(b))}
           70  +    {CREATE TABLE t1(a INTEGER, d INTEGER, c BLOB, PRIMARY KEY(d))}
           71  +
           72  + 16 {CREATE TABLE t1(a INTEGER, b INTEGER PRIMARY KEY, c BLOB)}
           73  +    {CREATE TABLE t1(a INTEGER, d INTEGER PRIMARY KEY, c BLOB)}
           74  +
           75  + 14 {CREATE TABLE t1(a INTEGER, b TEXT, c BLOB, PRIMARY KEY(b))}
           76  +    {CREATE TABLE t1(a INTEGER, d TEXT, c BLOB, PRIMARY KEY(d))}
           77  +
           78  + 15 {CREATE TABLE t1(a INTEGER, b INTEGER, c BLOB, PRIMARY KEY(b))}
           79  +    {CREATE TABLE t1(a INTEGER, d INTEGER, c BLOB, PRIMARY KEY(d))}
           80  +
           81  + 16 {CREATE TABLE t1(a INTEGER, b INTEGER PRIMARY KEY, c BLOB)}
           82  +    {CREATE TABLE t1(a INTEGER, d INTEGER PRIMARY KEY, c BLOB)}
           83  +
    69     84   } {
    70     85     reset_db
    71     86     do_execsql_test 1.$tn.0 $before
    72     87   
    73     88     do_execsql_test 1.$tn.1 {
    74     89       INSERT INTO t1 VALUES(1, 2, 3);
    75     90     }
................................................................................
   205    220   } {0 {}}
   206    221   
   207    222   do_execsql_test 6.3 {
   208    223     SELECT "where" FROM blob;
   209    224   } {}
   210    225   
   211    226   #-------------------------------------------------------------------------
          227  +# Triggers.
   212    228   #
   213    229   reset_db
   214    230   do_execsql_test 7.0 {
   215    231     CREATE TABLE c(x);
   216    232     INSERT INTO c VALUES(0);
   217    233     CREATE TABLE t6("col a", "col b", "col c");
   218    234     CREATE TRIGGER zzz AFTER UPDATE OF "col a", "col c" ON t6 BEGIN
................................................................................
   231    247   }
   232    248   
   233    249   do_execsql_test 7.3 {
   234    250     UPDATE t6 SET "col 3" = 0;
   235    251     SELECT * FROM c;
   236    252   } {2}
   237    253   
   238         -finish_test
          254  +#-------------------------------------------------------------------------
          255  +# Views.
          256  +#
          257  +reset_db
          258  +do_execsql_test 8.0 {
          259  +  CREATE TABLE a1(x INTEGER, y TEXT, z BLOB, PRIMARY KEY(x));
          260  +  CREATE TABLE a2(a, b, c);
          261  +  CREATE VIEW v1 AS SELECT x, y, z FROM a1;
          262  +}
          263  +
          264  +do_execsql_test 8.1 {
          265  +  ALTER TABLE a1 RENAME y TO yyy;
          266  +  SELECT sql FROM sqlite_master WHERE type='view';
          267  +} {{CREATE VIEW v1 AS SELECT x, yyy, z FROM a1}}
          268  +
          269  +do_execsql_test 8.2.1 {
          270  +  DROP VIEW v1;
          271  +  CREATE VIEW v2 AS SELECT x, x+x, a, a+a FROM a1, a2;
          272  +} {}
          273  +do_execsql_test 8.2.2 {
          274  +  ALTER TABLE a1 RENAME x TO xxx;
          275  +}
          276  +do_execsql_test 8.2.3 {
          277  +  SELECT sql FROM sqlite_master WHERE type='view';
          278  +} {{CREATE VIEW v2 AS SELECT xxx, xxx+xxx, a, a+a FROM a1, a2}}
          279  +
          280  +do_execsql_test 8.3.1 {
          281  +  DROP TABLE a2;
          282  +  DROP VIEW v2;
          283  +  CREATE TABLE a2(a INTEGER PRIMARY KEY, b, c);
          284  +  CREATE VIEW v2 AS SELECT xxx, xxx+xxx, a, a+a FROM a1, a2;
          285  +} {}
          286  +do_execsql_test 8.3.2 {
          287  +  ALTER TABLE a1 RENAME xxx TO x;
          288  +}
          289  +do_execsql_test 8.3.3 {
          290  +  SELECT sql FROM sqlite_master WHERE type='view';
          291  +} {{CREATE VIEW v2 AS SELECT x, x+x, a, a+a FROM a1, a2}}
          292  +
          293  +do_execsql_test 8.4.0 {
          294  +  CREATE TABLE b1(a, b, c);
          295  +  CREATE TABLE b2(x, y, z);
          296  +}
          297  +
          298  +do_execsql_test 8.4.1 {
          299  +  CREATE VIEW vvv AS SELECT c+c || coalesce(c, c) FROM b1, b2 WHERE x=c GROUP BY c HAVING c>0;
          300  +  ALTER TABLE b1 RENAME c TO "a;b";
          301  +  SELECT sql FROM sqlite_master WHERE name='vvv';
          302  +} {{CREATE VIEW vvv AS SELECT "a;b"+"a;b" || coalesce("a;b", "a;b") FROM b1, b2 WHERE x="a;b" GROUP BY "a;b" HAVING "a;b">0}}
          303  +
          304  +do_execsql_test 8.4.2 {
          305  +  CREATE VIEW www AS SELECT b FROM b1 UNION ALL SELECT y FROM b2;
          306  +  ALTER TABLE b1 RENAME b TO bbb;
          307  +  SELECT sql FROM sqlite_master WHERE name='www';
          308  +} {{CREATE VIEW www AS SELECT bbb FROM b1 UNION ALL SELECT y FROM b2}}
          309  +
          310  +db collate nocase {string compare}
          311  +
          312  +do_execsql_test 8.4.3 {
          313  +  CREATE VIEW xxx AS SELECT a FROM b1 UNION SELECT x FROM b2 ORDER BY 1 COLLATE nocase;
          314  +}
          315  +
          316  +do_execsql_test 8.4.4 {
          317  +  ALTER TABLE b2 RENAME x TO hello;
          318  +  SELECT sql FROM sqlite_master WHERE name='xxx';
          319  +} {{CREATE VIEW xxx AS SELECT a FROM b1 UNION SELECT hello FROM b2 ORDER BY 1 COLLATE nocase}}
          320  +
          321  +do_execsql_test 8.4.5 {
          322  +  CREATE VIEW zzz AS SELECT george, ringo FROM b1;
          323  +  ALTER TABLE b1 RENAME a TO aaa;
          324  +  SELECT sql FROM sqlite_master WHERE name = 'zzz'
          325  +} {{CREATE VIEW zzz AS SELECT george, ringo FROM b1}}
          326  +
   239    327   
          328  +finish_test

Changes to test/trigger7.test.

   109    109     execsql {
   110    110       PRAGMA writable_schema=on;
   111    111       UPDATE sqlite_master SET sql='nonsense';
   112    112     }
   113    113     db close
   114    114     catch { sqlite3 db test.db }
   115    115     catchsql { DROP TRIGGER t2r5 }
   116         -} {1 {malformed database schema (t2r12)}}
          116  +} {/1 {malformed database schema .*}/}
   117    117   
   118    118   finish_test

Changes to test/upsert1.test.

   123    123     PRAGMA integrity_check;
   124    124   } {ok}
   125    125   do_execsql_test upsert1-610 {
   126    126     DELETE FROM t1;
   127    127     INSERT OR IGNORE INTO t1(a) VALUES('1'),(1) ON CONFLICT(a) DO NOTHING;
   128    128     PRAGMA integrity_check;
   129    129   } {ok}
          130  +
          131  +# 2018-08-14
          132  +# Ticket https://www.sqlite.org/src/info/908f001483982c43
          133  +# If there are multiple uniqueness contraints, the UPSERT should fire
          134  +# if the one constraint it targets fails, regardless of whether or not
          135  +# the other constraints pass or fail.  In other words, the UPSERT constraint
          136  +# should be tested first.
          137  +#
          138  +do_execsql_test upsert1-700 {
          139  +  DROP TABLE t1;
          140  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT, d INT, e INT);
          141  +  CREATE UNIQUE INDEX t1b ON t1(b);
          142  +  CREATE UNIQUE INDEX t1e ON t1(e);
          143  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          144  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          145  +    ON CONFLICT(e) DO UPDATE SET c=excluded.c;
          146  +  SELECT * FROM t1;
          147  +} {1 2 33 4 5}
          148  +do_execsql_test upsert1-710 {
          149  +  DELETE FROM t1;
          150  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          151  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          152  +    ON CONFLICT(a) DO UPDATE SET c=excluded.c;
          153  +  SELECT * FROM t1;
          154  +} {1 2 33 4 5}
          155  +do_execsql_test upsert1-720 {
          156  +  DELETE FROM t1;
          157  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          158  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          159  +    ON CONFLICT(b) DO UPDATE SET c=excluded.c;
          160  +  SELECT * FROM t1;
          161  +} {1 2 33 4 5}
          162  +do_execsql_test upsert1-730 {
          163  +  DROP TABLE t1;
          164  +  CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT);
          165  +  CREATE UNIQUE INDEX t1a ON t1(a);
          166  +  CREATE UNIQUE INDEX t1b ON t1(b);
          167  +  CREATE UNIQUE INDEX t1e ON t1(e);
          168  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          169  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          170  +    ON CONFLICT(e) DO UPDATE SET c=excluded.c;
          171  +  SELECT * FROM t1;
          172  +} {1 2 33 4 5}
          173  +do_execsql_test upsert1-740 {
          174  +  DELETE FROM t1;
          175  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          176  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          177  +    ON CONFLICT(a) DO UPDATE SET c=excluded.c;
          178  +  SELECT * FROM t1;
          179  +} {1 2 33 4 5}
          180  +do_execsql_test upsert1-750 {
          181  +  DELETE FROM t1;
          182  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          183  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          184  +    ON CONFLICT(b) DO UPDATE SET c=excluded.c;
          185  +  SELECT * FROM t1;
          186  +} {1 2 33 4 5}
          187  +do_execsql_test upsert1-760 {
          188  +  DROP TABLE t1;
          189  +  CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT, d INT, e INT) WITHOUT ROWID;
          190  +  CREATE UNIQUE INDEX t1a ON t1(a);
          191  +  CREATE UNIQUE INDEX t1b ON t1(b);
          192  +  CREATE UNIQUE INDEX t1e ON t1(e);
          193  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          194  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          195  +    ON CONFLICT(e) DO UPDATE SET c=excluded.c;
          196  +  SELECT * FROM t1;
          197  +} {1 2 33 4 5}
          198  +do_execsql_test upsert1-770 {
          199  +  DELETE FROM t1;
          200  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          201  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          202  +    ON CONFLICT(a) DO UPDATE SET c=excluded.c;
          203  +  SELECT * FROM t1;
          204  +} {1 2 33 4 5}
          205  +do_execsql_test upsert1-780 {
          206  +  DELETE FROM t1;
          207  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          208  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          209  +    ON CONFLICT(b) DO UPDATE SET c=excluded.c;
          210  +  SELECT * FROM t1;
          211  +} {1 2 33 4 5}
          212  +
   130    213   
   131    214   finish_test