Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Have r-tree virtual tables support on-conflict clauses. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | vtab-conflict |
Files: | files | file ages | folders |
SHA1: |
822ab52f1023b1c4973c806cc75454ac |
User & Date: | dan 2011-04-28 18:46:46.861 |
Context
2011-05-04
| ||
12:52 | Optimize "ORDER BY rowid/docid DESC/ASC" clauses on FTS tables. (check-in: 13395121e3 user: dan tags: vtab-conflict) | |
2011-04-28
| ||
18:46 | Have r-tree virtual tables support on-conflict clauses. (check-in: 822ab52f10 user: dan tags: vtab-conflict) | |
2011-04-27
| ||
16:02 | Add documentation for the newly introduced sqlite3_vtab_config() and on_conflict() API functions. Test that encountering an SQLITE_MISMATCH in fts3 does not corrupt the full text index. (check-in: abdd70ae04 user: dan tags: vtab-conflict) | |
Changes
Changes to ext/rtree/rtree.c.
︙ | ︙ | |||
2622 2623 2624 2625 2626 2627 2628 | sqlite3_step(pRtree->pWriteRowid); rc = sqlite3_reset(pRtree->pWriteRowid); *piRowid = sqlite3_last_insert_rowid(pRtree->db); return rc; } /* | | | < < < < < < < | < < < < < < < < < < | > < | 2622 2623 2624 2625 2626 2627 2628 2629 2630 2631 2632 2633 2634 2635 2636 2637 2638 2639 2640 2641 2642 2643 2644 2645 2646 2647 2648 2649 2650 2651 | sqlite3_step(pRtree->pWriteRowid); rc = sqlite3_reset(pRtree->pWriteRowid); *piRowid = sqlite3_last_insert_rowid(pRtree->db); return rc; } /* ** Remove the entry with rowid=iDelete from the r-tree structure. */ static int rtreeDeleteRowid(Rtree *pRtree, sqlite3_int64 iDelete){ int rc; /* Return code */ RtreeNode *pLeaf; /* Leaf node containing record iDelete */ int iCell; /* Index of iDelete cell in pLeaf */ RtreeNode *pRoot; /* Root node of rtree structure */ /* Obtain a reference to the root node to initialise Rtree.iDepth */ rc = nodeAcquire(pRtree, 1, 0, &pRoot); /* Obtain a reference to the leaf node that contains the entry ** about to be deleted. */ if( rc==SQLITE_OK ){ rc = findLeafNode(pRtree, iDelete, &pLeaf); } /* Delete the cell in question from the leaf node. */ if( rc==SQLITE_OK ){ int rc2; rc = nodeRowidIndex(pRtree, pLeaf, iDelete, &iCell); |
︙ | ︙ | |||
2718 2719 2720 2721 2722 2723 2724 | /* Release the reference to the root node. */ if( rc==SQLITE_OK ){ rc = nodeRelease(pRtree, pRoot); }else{ nodeRelease(pRtree, pRoot); } | | > | | > > > > > > > > > > > > > | > > | > > > > > > > > > > | < < < | 2701 2702 2703 2704 2705 2706 2707 2708 2709 2710 2711 2712 2713 2714 2715 2716 2717 2718 2719 2720 2721 2722 2723 2724 2725 2726 2727 2728 2729 2730 2731 2732 2733 2734 2735 2736 2737 2738 2739 2740 2741 2742 2743 2744 2745 2746 2747 2748 | /* Release the reference to the root node. */ if( rc==SQLITE_OK ){ rc = nodeRelease(pRtree, pRoot); }else{ nodeRelease(pRtree, pRoot); } return rc; } /* ** The xUpdate method for rtree module virtual tables. */ static int rtreeUpdate( sqlite3_vtab *pVtab, int nData, sqlite3_value **azData, sqlite_int64 *pRowid ){ Rtree *pRtree = (Rtree *)pVtab; int rc = SQLITE_OK; RtreeCell cell; /* New cell to insert if nData>1 */ int bHaveRowid = 0; /* Set to 1 after new rowid is determined */ rtreeReference(pRtree); assert(nData>=1); /* Constraint handling. A write operation on an r-tree table may return ** SQLITE_CONSTRAINT for two reasons: ** ** 1. A duplicate rowid value, or ** 2. The supplied data violates the "x2>=x1" constraint. ** ** In the first case, if the conflict-handling mode is REPLACE, then ** the conflicting row can be removed before proceeding. In the second ** case, SQLITE_CONSTRAINT must be returned regardless of the ** conflict-handling mode specified by the user. */ if( nData>1 ){ int ii; /* Populate the cell.aCoord[] array. The first coordinate is azData[3]. */ assert( nData==(pRtree->nDim*2 + 3) ); if( pRtree->eCoordType==RTREE_COORD_REAL32 ){ for(ii=0; ii<(pRtree->nDim*2); ii+=2){ cell.aCoord[ii].f = (float)sqlite3_value_double(azData[ii+3]); cell.aCoord[ii+1].f = (float)sqlite3_value_double(azData[ii+4]); |
︙ | ︙ | |||
2752 2753 2754 2755 2756 2757 2758 | if( cell.aCoord[ii].i>cell.aCoord[ii+1].i ){ rc = SQLITE_CONSTRAINT; goto constraint; } } } | | > | < < > > > > | | > > > > > > > > > | > > > > > > > > > > > > > > > > > > > | 2758 2759 2760 2761 2762 2763 2764 2765 2766 2767 2768 2769 2770 2771 2772 2773 2774 2775 2776 2777 2778 2779 2780 2781 2782 2783 2784 2785 2786 2787 2788 2789 2790 2791 2792 2793 2794 2795 2796 2797 2798 2799 2800 2801 2802 2803 2804 2805 2806 2807 2808 2809 2810 2811 2812 2813 2814 | if( cell.aCoord[ii].i>cell.aCoord[ii+1].i ){ rc = SQLITE_CONSTRAINT; goto constraint; } } } /* If a rowid value was supplied, check if it is already present in ** the table. If so, the constraint has failed. */ if( sqlite3_value_type(azData[2])!=SQLITE_NULL ){ cell.iRowid = sqlite3_value_int64(azData[2]); if( sqlite3_value_type(azData[0])==SQLITE_NULL || sqlite3_value_int64(azData[0])!=cell.iRowid ){ int steprc; sqlite3_bind_int64(pRtree->pReadRowid, 1, cell.iRowid); steprc = sqlite3_step(pRtree->pReadRowid); rc = sqlite3_reset(pRtree->pReadRowid); if( SQLITE_ROW==steprc ){ if( sqlite3_vtab_on_conflict(pRtree->db)==SQLITE_REPLACE ){ rc = rtreeDeleteRowid(pRtree, cell.iRowid); }else{ rc = SQLITE_CONSTRAINT; goto constraint; } } } bHaveRowid = 1; } } /* If azData[0] is not an SQL NULL value, it is the rowid of a ** record to delete from the r-tree table. The following block does ** just that. */ if( sqlite3_value_type(azData[0])!=SQLITE_NULL ){ rc = rtreeDeleteRowid(pRtree, sqlite3_value_int64(azData[0])); } /* If the azData[] array contains more than one element, elements ** (azData[2]..azData[argc-1]) contain a new record to insert into ** the r-tree structure. */ if( rc==SQLITE_OK && nData>1 ){ /* Insert the new record into the r-tree */ RtreeNode *pLeaf; /* Figure out the rowid of the new row. */ if( bHaveRowid==0 ){ rc = newRowid(pRtree, &cell.iRowid); } *pRowid = cell.iRowid; if( rc==SQLITE_OK ){ rc = ChooseLeaf(pRtree, &cell, 0, &pLeaf); } if( rc==SQLITE_OK ){ |
︙ | ︙ | |||
3003 3004 3005 3006 3007 3008 3009 3010 3011 3012 3013 3014 3015 3016 | }; int iErr = (argc<6) ? 2 : argc>(RTREE_MAX_DIMENSIONS*2+4) ? 3 : argc%2; if( aErrMsg[iErr] ){ *pzErr = sqlite3_mprintf("%s", aErrMsg[iErr]); return SQLITE_ERROR; } /* Allocate the sqlite3_vtab structure */ nDb = strlen(argv[1]); nName = strlen(argv[2]); pRtree = (Rtree *)sqlite3_malloc(sizeof(Rtree)+nDb+nName+2); if( !pRtree ){ return SQLITE_NOMEM; | > > | 3040 3041 3042 3043 3044 3045 3046 3047 3048 3049 3050 3051 3052 3053 3054 3055 | }; int iErr = (argc<6) ? 2 : argc>(RTREE_MAX_DIMENSIONS*2+4) ? 3 : argc%2; if( aErrMsg[iErr] ){ *pzErr = sqlite3_mprintf("%s", aErrMsg[iErr]); return SQLITE_ERROR; } sqlite3_vtab_config(db, SQLITE_VTAB_CONSTRAINT_SUPPORT, 1); /* Allocate the sqlite3_vtab structure */ nDb = strlen(argv[1]); nName = strlen(argv[2]); pRtree = (Rtree *)sqlite3_malloc(sizeof(Rtree)+nDb+nName+2); if( !pRtree ){ return SQLITE_NOMEM; |
︙ | ︙ |
Changes to ext/rtree/rtree1.test.
︙ | ︙ | |||
27 28 29 30 31 32 33 34 35 36 37 38 39 40 | # rtree-3.*: Linear scans of r-tree data. # rtree-4.*: Test INSERT # rtree-5.*: Test DELETE # rtree-6.*: Test UPDATE # rtree-7.*: Test renaming an r-tree table. # rtree-8.*: Test constrained scans of r-tree data. # ifcapable !rtree { finish_test return } #---------------------------------------------------------------------------- | > > | 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | # rtree-3.*: Linear scans of r-tree data. # rtree-4.*: Test INSERT # rtree-5.*: Test DELETE # rtree-6.*: Test UPDATE # rtree-7.*: Test renaming an r-tree table. # rtree-8.*: Test constrained scans of r-tree data. # # rtree-12.*: Test that on-conflict clauses are supported. # ifcapable !rtree { finish_test return } #---------------------------------------------------------------------------- |
︙ | ︙ | |||
412 413 414 415 416 417 418 419 | do_test rtree-11.2 { execsql { INSERT INTO t8 VALUES(NULL, 1.0, 1.0, 2.0, 2.0); SELECT last_insert_rowid(); } } {2} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 | do_test rtree-11.2 { execsql { INSERT INTO t8 VALUES(NULL, 1.0, 1.0, 2.0, 2.0); SELECT last_insert_rowid(); } } {2} #------------------------------------------------------------------------- # Test on-conflict clause handling. # db_delete_and_reopen do_execsql_test 12.0 { CREATE VIRTUAL TABLE t1 USING rtree_i32(idx, x1, x2, y1, y2); INSERT INTO t1 VALUES(1, 1, 2, 3, 4); INSERT INTO t1 VALUES(2, 2, 3, 4, 5); INSERT INTO t1 VALUES(3, 3, 4, 5, 6); CREATE TABLE source(idx, x1, x2, y1, y2); INSERT INTO source VALUES(5, 8, 8, 8, 8); INSERT INTO source VALUES(2, 7, 7, 7, 7); } db_save_and_close foreach {tn sql_template testdata} { 1 "INSERT %CONF% INTO t1 VALUES(2, 7, 7, 7, 7)" { ROLLBACK 0 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6} ABORT 0 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7} IGNORE 0 0 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7} FAIL 0 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7} REPLACE 0 0 {1 1 2 3 4 2 7 7 7 7 3 3 4 5 6 4 4 5 6 7} } 2 "INSERT %CONF% INTO t1 SELECT * FROM source" { ROLLBACK 1 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6} ABORT 1 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7} IGNORE 1 0 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7 5 8 8 8 8} FAIL 1 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7 5 8 8 8 8} REPLACE 1 0 {1 1 2 3 4 2 7 7 7 7 3 3 4 5 6 4 4 5 6 7 5 8 8 8 8} } 3 "UPDATE %CONF% t1 SET idx = 2 WHERE idx = 4" { ROLLBACK 1 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6} ABORT 1 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7} IGNORE 1 0 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7} FAIL 1 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7} REPLACE 1 0 {1 1 2 3 4 2 4 5 6 7 3 3 4 5 6} } 3 "UPDATE %CONF% t1 SET idx = ((idx+1)%5)+1 WHERE idx > 2" { ROLLBACK 1 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6} ABORT 1 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7} IGNORE 1 0 {1 1 2 3 4 2 2 3 4 5 4 4 5 6 7 5 3 4 5 6} FAIL 1 1 {1 1 2 3 4 2 2 3 4 5 4 4 5 6 7 5 3 4 5 6} REPLACE 1 0 {1 4 5 6 7 2 2 3 4 5 5 3 4 5 6} } 4 "INSERT %CONF% INTO t1 VALUES(2, 7, 6, 7, 7)" { ROLLBACK 0 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6} ABORT 0 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7} IGNORE 0 0 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7} FAIL 0 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7} REPLACE 0 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7} } } { foreach {mode uses error data} $testdata { db_restore_and_reopen set sql [string map [list %CONF% "OR $mode"] $sql_template] set testname "12.$tn.[string tolower $mode]" execsql { BEGIN; INSERT INTO t1 VALUES(4, 4, 5, 6, 7); } set res(0) {0 {}} set res(1) {1 {constraint failed}} do_catchsql_test $testname.1 $sql $res($error) do_test $testname.2 [list sql_uses_stmt db $sql] $uses do_execsql_test $testname.3 { SELECT * FROM t1 ORDER BY idx } $data do_test $testname.4 { rtree_check db t1 } 0 db close } } finish_test |
Changes to test/fts3conf.test.
︙ | ︙ | |||
52 53 54 55 56 57 58 | DROP TABLE temp.fts3check2; DROP TABLE temp.fts3check3; } uplevel [list do_test $tn [list set {} $m1] $m2] } | < < < < < < < < < < | 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | DROP TABLE temp.fts3check2; DROP TABLE temp.fts3check3; } uplevel [list do_test $tn [list set {} $m1] $m2] } do_execsql_test 1.0.1 { CREATE VIRTUAL TABLE t1 USING fts3(x); INSERT INTO t1(rowid, x) VALUES(1, 'a b c d'); INSERT INTO t1(rowid, x) VALUES(2, 'e f g h'); CREATE TABLE source(a, b); INSERT INTO source VALUES(4, 'z'); |
︙ | ︙ |
Changes to test/tester.tcl.
︙ | ︙ | |||
743 744 745 746 747 748 749 750 751 752 753 754 755 756 | # proc integrity_check {name {db db}} { ifcapable integrityck { do_test $name [list execsql {PRAGMA integrity_check} $db] {ok} } } proc fix_ifcapable_expr {expr} { set ret "" set state 0 for {set i 0} {$i < [string length $expr]} {incr i} { set char [string range $expr $i $i] set newstate [expr {[string is alnum $char] || $char eq "_"}] if {$newstate && !$state} { | > > > > > > > > > > > | 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 | # proc integrity_check {name {db db}} { ifcapable integrityck { do_test $name [list execsql {PRAGMA integrity_check} $db] {ok} } } # Return true if the SQL statement passed as the second argument uses a # statement transaction. # proc sql_uses_stmt {db sql} { set stmt [sqlite3_prepare $db $sql -1 dummy] set uses [uses_stmt_journal $stmt] sqlite3_finalize $stmt return $uses } proc fix_ifcapable_expr {expr} { set ret "" set state 0 for {set i 0} {$i < [string length $expr]} {incr i} { set char [string range $expr $i $i] set newstate [expr {[string is alnum $char] || $char eq "_"}] if {$newstate && !$state} { |
︙ | ︙ |