Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| 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: |
57862efe718fdc93401998f905851129 |
| User & Date: | dan 2011-03-12 17:22:46 |
|
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 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