/ Check-in [57862efe]
Login

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

Overview
Comment:Fix some issues with UPDATE changes in the session module.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sessions
Files: files | file ages | folders
SHA1: 57862efe718fdc93401998f9058511292a0e1a50
User & Date: dan 2011-03-12 17:22:46
Context
2011-03-14
19:49
Fix handling of return values from the conflict handler. Document the conflict handler arguments and return codes in sqlite3session.h. check-in: cbbb274e user: dan tags: sessions
2011-03-12
17:22
Fix some issues with UPDATE changes in the session module. check-in: 57862efe user: dan tags: sessions
2011-03-11
19:05
Add the sqlite3changeset_apply() function. Does not yet handle all cases. check-in: 2b19be7b user: dan tags: sessions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/session/sqlite3session.c.

   663    663     if( *pRc==SQLITE_OK ){
   664    664       SessionBuffer buf2 = {0, 0, 0};
   665    665       int bNoop = 1;
   666    666       int i;
   667    667       u8 *pCsr = p->aRecord;
   668    668       sessionAppendByte(pBuf, SQLITE_UPDATE, pRc);
   669    669       for(i=0; i<sqlite3_column_count(pStmt); i++){
   670         -      int nCopy = 0;
          670  +      int bChanged = 0;
   671    671         int nAdvance;
   672    672         int eType = *pCsr;
   673    673         switch( eType ){
   674    674           case SQLITE_NULL:
   675    675             nAdvance = 1;
   676    676             if( sqlite3_column_type(pStmt, i)!=SQLITE_NULL ){
   677         -            nCopy = 1;
          677  +            bChanged = 1;
   678    678             }
   679    679             break;
   680    680   
   681    681           case SQLITE_FLOAT:
   682    682           case SQLITE_INTEGER: {
   683    683             nAdvance = 9;
   684    684             if( eType==sqlite3_column_type(pStmt, i) ){
................................................................................
   687    687                 if( iVal==sqlite3_column_int64(pStmt, i) ) break;
   688    688               }else{
   689    689                 double dVal;
   690    690                 memcpy(&dVal, &iVal, 8);
   691    691                 if( dVal==sqlite3_column_double(pStmt, i) ) break;
   692    692               }
   693    693             }
   694         -          nCopy = 9;
          694  +          bChanged = 1;
   695    695             break;
   696    696           }
   697    697   
   698    698           case SQLITE_TEXT:
   699    699           case SQLITE_BLOB: {
   700    700             int nByte;
   701    701             int nHdr = 1 + sessionVarintGet(&pCsr[1], &nByte);
................................................................................
   702    702             nAdvance = nHdr + nByte;
   703    703             if( eType==sqlite3_column_type(pStmt, i) 
   704    704              && nByte==sqlite3_column_bytes(pStmt, i) 
   705    705              && 0==memcmp(&pCsr[nHdr], sqlite3_column_blob(pStmt, i), nByte)
   706    706             ){
   707    707               break;
   708    708             }
   709         -          nCopy = nAdvance;
          709  +          bChanged = 1;
   710    710           }
   711    711         }
   712         -      if( abPK[i] ){
   713         -        nCopy = nAdvance;
   714         -      }
   715    712   
   716         -      if( nCopy==0 ){
          713  +      if( bChanged || abPK[i] ){
          714  +        sessionAppendBlob(pBuf, pCsr, nAdvance, pRc);
          715  +      }else{
   717    716           sessionAppendByte(pBuf, 0, pRc);
   718         -        sessionAppendByte(&buf2, 0, pRc);
   719         -      }else{
   720         -        sessionAppendBlob(pBuf, pCsr, nCopy, pRc);
          717  +      }
          718  +
          719  +      if( bChanged ){
   721    720           sessionAppendCol(&buf2, pStmt, i, pRc);
   722    721           bNoop = 0;
          722  +      }else{
          723  +        sessionAppendByte(&buf2, 0, pRc);
   723    724         }
          725  +
   724    726         pCsr += nAdvance;
   725    727       }
   726    728   
   727    729       if( bNoop ){
   728    730         pBuf->nBuf -= (1 + sqlite3_column_count(pStmt));
   729    731       }else{
   730    732         sessionAppendBlob(pBuf, buf2.aBuf, buf2.nBuf, pRc);
................................................................................
  1447   1449       if( rc==SQLITE_OK ){
  1448   1450         rc = sqlite3_prepare_v2(db, (char *)buf.aBuf, buf.nBuf, ppStmt, 0);
  1449   1451       }
  1450   1452       sqlite3_free(buf.aBuf);
  1451   1453     }
  1452   1454     return rc;
  1453   1455   }
         1456  +
         1457  +static int sessionConstraintConflict(
         1458  +  sqlite3 *db,                    /* Database handle */
         1459  +  sqlite3_changeset_iter *pIter,  /* Changeset iterator */
         1460  +  u8 *abPK,                       /* Primary key flags array */
         1461  +  sqlite3_stmt *pSelect,          /* SELECT statement from sessionSelectRow() */
         1462  +  int(*xConflict)(void *, int, sqlite3_changeset_iter*),
         1463  +  void *pCtx
         1464  +){
         1465  +  int res;
         1466  +  int rc;
         1467  +  int i;
         1468  +  int nCol;
         1469  +  int op;
         1470  +  const char *zDummy;
         1471  +
         1472  +  sqlite3changeset_op(pIter, &zDummy, &nCol, &op);
         1473  +  assert( op==SQLITE_UPDATE || op==SQLITE_INSERT );
         1474  +
         1475  +  /* Bind the new.* PRIMARY KEY values to the SELECT statement. */
         1476  +  for(i=0; i<nCol; i++){
         1477  +    if( abPK[i] ){
         1478  +      sqlite3_value *pVal;
         1479  +      if( op==SQLITE_UPDATE ) rc = sqlite3changeset_old(pIter, i, &pVal);
         1480  +      else                    rc = sqlite3changeset_new(pIter, i, &pVal);
         1481  +      if( rc!=SQLITE_OK ) return rc;
         1482  +      sqlite3_bind_value(pSelect, i+1, pVal);
         1483  +    }
         1484  +  }
         1485  +
         1486  +  if( SQLITE_ROW==sqlite3_step(pSelect) ){
         1487  +    /* There exists another row with the new.* primary key. */
         1488  +    pIter->pConflict = pSelect;
         1489  +    res = xConflict(pCtx, SQLITE_CHANGESET_CONFLICT, pIter);
         1490  +    pIter->pConflict = 0;
         1491  +    sqlite3_reset(pSelect);
         1492  +  }else{
         1493  +    /* No other row with the new.* primary key. */
         1494  +    rc = sqlite3_reset(pSelect);
         1495  +    if( rc==SQLITE_OK ){
         1496  +      res = xConflict(pCtx, SQLITE_CHANGESET_CONSTRAINT, pIter);
         1497  +    }
         1498  +  }
         1499  +
         1500  +  return rc;
         1501  +}
  1454   1502   
  1455   1503   int sqlite3changeset_apply(
  1456   1504     sqlite3 *db,
  1457   1505     int nChangeset,
  1458   1506     void *pChangeset,
  1459   1507     int(*xConflict)(
  1460   1508       void *pCtx,                   /* Copy of fifth arg to _apply() */
................................................................................
  1475   1523   
  1476   1524     sqlite3_stmt *pDelete = 0;      /* DELETE statement */
  1477   1525     sqlite3_stmt *pUpdate = 0;      /* DELETE statement */
  1478   1526     sqlite3_stmt *pInsert = 0;      /* INSERT statement */
  1479   1527     sqlite3_stmt *pSelect = 0;      /* SELECT statement */
  1480   1528   
  1481   1529     rc = sqlite3_exec(db, "SAVEPOINT changeset_apply", 0, 0, 0);
         1530  +  if( rc!=SQLITE_OK ) return rc;
         1531  +
  1482   1532     sqlite3changeset_start(&pIter, nChangeset, pChangeset);
  1483   1533     while( SQLITE_ROW==sqlite3changeset_next(pIter) ){
  1484   1534       int op;
  1485   1535       const char *zThis;
  1486   1536       sqlite3changeset_op(pIter, &zThis, &nCol, &op);
  1487   1537       if( zTab==0 || sqlite3_strnicmp(zThis, zTab, nTab+1) ){
  1488   1538         sqlite3_free(azCol);
................................................................................
  1490   1540         nTab = strlen(zTab);
  1491   1541   
  1492   1542         sqlite3_finalize(pDelete);
  1493   1543         sqlite3_finalize(pUpdate);
  1494   1544         sqlite3_finalize(pInsert);
  1495   1545         sqlite3_finalize(pSelect);
  1496   1546         pSelect = pUpdate = pInsert = pDelete = 0;
         1547  +
         1548  +      if( (rc = sessionSelectRow(db, zTab, nCol, azCol, abPK, &pSelect))
         1549  +       || (rc = sessionUpdateRow(db, zTab, nCol, azCol, abPK, &pUpdate))
         1550  +       || (rc = sessionDeleteRow(db, zTab, nCol, azCol, abPK, &pDelete))
         1551  +      ){
         1552  +        break;
         1553  +      }
  1497   1554       }
  1498   1555   
  1499   1556       if( op==SQLITE_DELETE ){
  1500   1557         int res;
  1501   1558         int i;
  1502   1559         rc = sessionDeleteRow(db, zTab, nCol, azCol, abPK, &pDelete);
  1503   1560         for(i=0; rc==SQLITE_OK && i<nCol; i++){
................................................................................
  1555   1612           rc = sqlite3changeset_old(pIter, i, &pOld);
  1556   1613           if( rc==SQLITE_OK ){
  1557   1614             rc = sqlite3changeset_new(pIter, i, &pNew);
  1558   1615           }
  1559   1616           if( rc==SQLITE_OK ){
  1560   1617             if( pOld ) sqlite3_bind_value(pUpdate, i*3+1, pOld);
  1561   1618             sqlite3_bind_int(pUpdate, i*3+2, !!pNew);
  1562         -          if( pNew ) sqlite3_bind_value(pUpdate, i*3+3, pOld);
         1619  +          if( pNew ) sqlite3_bind_value(pUpdate, i*3+3, pNew);
  1563   1620           }
  1564   1621         }
  1565   1622         if( rc==SQLITE_OK ) rc = sqlite3_bind_int(pUpdate, nCol*3+1, 0);
  1566   1623         if( rc!=SQLITE_OK ) break;
  1567   1624   
  1568   1625         sqlite3_step(pUpdate);
  1569   1626         rc = sqlite3_reset(pUpdate);
................................................................................
  1588   1645           }else{
  1589   1646             rc = sqlite3_reset(pSelect);
  1590   1647             if( rc==SQLITE_OK ){
  1591   1648               res = xConflict(pCtx, SQLITE_CHANGESET_NOTFOUND, pIter);
  1592   1649             }
  1593   1650           }
  1594   1651         }else if( rc==SQLITE_CONSTRAINT ){
  1595         -        assert(0);
         1652  +        /* This may be a CONSTRAINT or CONFLICT error. It is a CONFLICT if
         1653  +        ** the only problem is a duplicate PRIMARY KEY, or a CONSTRAINT 
         1654  +        ** otherwise. */
         1655  +        int bPKChange = 0;
         1656  +
         1657  +        /* Check if the PK has been modified. */
         1658  +        rc = SQLITE_OK;
         1659  +        for(i=0; i<nCol && rc==SQLITE_OK; i++){
         1660  +          if( abPK[i] ){
         1661  +            sqlite3_value *pNew;
         1662  +            rc = sqlite3changeset_new(pIter, i, &pNew);
         1663  +            if( rc==SQLITE_OK && pNew ){
         1664  +              bPKChange = 1;
         1665  +              break;
         1666  +            }
         1667  +          }
         1668  +        }
         1669  +
         1670  +        if( bPKChange ){
         1671  +          /* See if there exists a row with a duplicate primary key. */
         1672  +          rc = sessionConstraintConflict(
         1673  +              db, pIter, abPK, pSelect, xConflict, pCtx
         1674  +          );
         1675  +        }else{
         1676  +          res = xConflict(pCtx, SQLITE_CHANGESET_CONSTRAINT, pIter);
         1677  +        }
  1596   1678         }
  1597   1679   
  1598   1680       }else{
  1599   1681         int i;
  1600   1682         assert( op==SQLITE_INSERT );
  1601   1683         if( pInsert==0 ){
  1602   1684           SessionBuffer buf = {0, 0, 0};
................................................................................
  1620   1702           }
  1621   1703         }
  1622   1704         if( rc!=SQLITE_OK ) break;
  1623   1705   
  1624   1706         sqlite3_step(pInsert);
  1625   1707         rc = sqlite3_reset(pInsert);
  1626   1708         if( rc==SQLITE_CONSTRAINT && xConflict ){
  1627         -        int res;
  1628         -
  1629         -        /* Figure out if this is a primary key or other constraint. */
  1630         -        rc = sessionSelectRow(db, zTab, nCol, azCol, abPK, &pSelect);
  1631         -        for(i=0; rc==SQLITE_OK && i<nCol; i++){
  1632         -          if( abPK[i] ){
  1633         -            sqlite3_value *pVal;
  1634         -            rc = sqlite3changeset_new(pIter, i, &pVal);
  1635         -            if( rc==SQLITE_OK ) sqlite3_bind_value(pSelect, i+1, pVal);
  1636         -          }
  1637         -        }
  1638         -        if( rc!=SQLITE_OK ) break;
  1639         -        if( SQLITE_ROW==sqlite3_step(pSelect) ){
  1640         -          pIter->pConflict = pSelect;
  1641         -          res = xConflict(pCtx, SQLITE_CHANGESET_CONFLICT, pIter);
  1642         -          pIter->pConflict = 0;
  1643         -          sqlite3_reset(pSelect);
  1644         -        }else{
  1645         -          rc = sqlite3_reset(pSelect);
  1646         -          if( rc==SQLITE_OK ){
  1647         -            res = xConflict(pCtx, SQLITE_CHANGESET_CONSTRAINT, pIter);
  1648         -          }
  1649         -        }
         1709  +        rc = sessionConstraintConflict(
         1710  +              db, pIter, abPK, pSelect, xConflict, pCtx
         1711  +        );
  1650   1712         }
  1651   1713       }
  1652   1714     }
  1653   1715     rc2 = sqlite3changeset_finalize(pIter);
  1654   1716     if( rc==SQLITE_DONE ) rc = rc2;
  1655   1717   
  1656   1718     if( rc==SQLITE_OK ){

Changes to test/session1.test.

   255    255   
   256    256   # Test UPDATE changesets.
   257    257   #
   258    258   do_execsql_test 3.3.1 {
   259    259     CREATE TABLE t4(a, b, c, PRIMARY KEY(b, c));
   260    260     INSERT INTO t4 VALUES(1, 2, 3);
   261    261     INSERT INTO t4 VALUES(4, 5, 6);
          262  +  INSERT INTO t4 VALUES(7, 8, 9);
          263  +  INSERT INTO t4 VALUES(10, 11, 12);
   262    264   }
   263    265   do_db2_test 3.3.2 {
   264         -  CREATE TABLE t4(a, b, c, PRIMARY KEY(b, c));
          266  +  CREATE TABLE t4(a NOT NULL, b, c, PRIMARY KEY(b, c));
   265    267     INSERT INTO t4 VALUES(0, 2, 3);
   266    268     INSERT INTO t4 VALUES(4, 5, 7);
          269  +  INSERT INTO t4 VALUES(7, 8, 9);
          270  +  INSERT INTO t4 VALUES(10, 11, 12);
   267    271   }
   268         -
   269         -do_conflict_test 3.2.3 -tables t4 -sql {
          272  +do_conflict_test 3.3.3 -tables t4 -sql {
   270    273     UPDATE t4 SET a = -1 WHERE b = 2;
          274  +  UPDATE t4 SET a = -1 WHERE b = 5;
          275  +  UPDATE t4 SET a = NULL WHERE c = 9;
          276  +  UPDATE t4 SET a = 'x' WHERE b = 11;
   271    277   } -conflicts {
   272         -  {UPDATE t4 DATA {i 1 i 2 i 3} {i -1 i 2 i 3} {i 0 i 2 i 3}}
          278  +  {UPDATE t4 DATA {i 1 i 2 i 3} {i -1 {} {} {} {}} {i 0 i 2 i 3}}
          279  +  {UPDATE t4 NOTFOUND {i 4 i 5 i 6} {i -1 {} {} {} {}}}
          280  +  {UPDATE t4 CONSTRAINT {i 7 i 8 i 9} {n {} {} {} {} {}}}
   273    281   }
   274    282   
   275         -do_db2_test 3.3.4 {
   276         -  SELECT * FROM t4
   277         -} {0 2 3 4 5 7}
          283  +do_db2_test     3.3.4 { SELECT * FROM t4 } {0 2 3 4 5 7 7 8 9 x 11 12}
          284  +do_execsql_test 3.3.5 { SELECT * FROM t4 } {-1 2 3 -1 5 6 {} 8 9 x 11 12}
   278    285   
   279    286   
   280    287   catch { db2 close }
   281    288   finish_test
   282    289