/ Check-in [07937a81]
Login

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

Overview
Comment:Add "ON CONFLICT" handling to the spellfix module.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 07937a81c6c90eb7491a843eb65183805f5b675e
User & Date: drh 2015-06-24 18:22:51
Context
2015-06-24
23:17
Fix fuzzcheck so that it works with SQLITE_OMIT_PROGRESS_CALLBACK. check-in: 7a9c4eb3 user: drh tags: trunk
18:22
Add "ON CONFLICT" handling to the spellfix module. check-in: 07937a81 user: drh tags: trunk
17:21
Add "ON CONFLICT" handling to the spellfix module. Closed-Leaf check-in: 1d04def7 user: dan tags: spellfix-constraints
14:45
In the fuzzcheck test program, use the progress handler to limit the number of VDBE cycles to avoid getting stuck if the SQL under test contains an infinite CTE loop. Add the --limit-vdbe command-line option. check-in: fbf9c432 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/misc/spellfix.c.

  2650   2650     if( pCur->pFullScan ){
  2651   2651       *pRowid = sqlite3_column_int64(pCur->pFullScan, 4);
  2652   2652     }else{
  2653   2653       *pRowid = pCur->a[pCur->iRow].iRowid;
  2654   2654     }
  2655   2655     return SQLITE_OK;
  2656   2656   }
         2657  +
         2658  +/*
         2659  +** This function is called by the xUpdate() method. It returns a string
         2660  +** containing the conflict mode that xUpdate() should use for the current
         2661  +** operation. One of: "ROLLBACK", "IGNORE", "ABORT" or "REPLACE".
         2662  +*/
         2663  +static const char *spellfix1GetConflict(sqlite3 *db){
         2664  +  static const char *azConflict[] = {
         2665  +    /* Note: Instead of "FAIL" - "ABORT". */
         2666  +    "ROLLBACK", "IGNORE", "ABORT", "ABORT", "REPLACE"
         2667  +  };
         2668  +  int eConflict = sqlite3_vtab_on_conflict(db);
         2669  +
         2670  +  assert( eConflict==SQLITE_ROLLBACK || eConflict==SQLITE_IGNORE
         2671  +       || eConflict==SQLITE_FAIL || eConflict==SQLITE_ABORT
         2672  +       || eConflict==SQLITE_REPLACE
         2673  +  );
         2674  +  assert( SQLITE_ROLLBACK==1 );
         2675  +  assert( SQLITE_IGNORE==2 );
         2676  +  assert( SQLITE_FAIL==3 );
         2677  +  assert( SQLITE_ABORT==4 );
         2678  +  assert( SQLITE_REPLACE==5 );
         2679  +
         2680  +  return azConflict[eConflict-1];
         2681  +}
  2657   2682   
  2658   2683   /*
  2659   2684   ** The xUpdate() method.
  2660   2685   */
  2661   2686   static int spellfix1Update(
  2662   2687     sqlite3_vtab *pVTab,
  2663   2688     int argc,
................................................................................
  2682   2707       int iRank = sqlite3_value_int(argv[SPELLFIX_COL_RANK+2]);
  2683   2708       const unsigned char *zSoundslike =
  2684   2709              sqlite3_value_text(argv[SPELLFIX_COL_SOUNDSLIKE+2]);
  2685   2710       int nSoundslike = sqlite3_value_bytes(argv[SPELLFIX_COL_SOUNDSLIKE+2]);
  2686   2711       char *zK1, *zK2;
  2687   2712       int i;
  2688   2713       char c;
         2714  +    const char *zConflict = spellfix1GetConflict(db);
  2689   2715   
  2690   2716       if( zWord==0 ){
  2691   2717         /* Inserts of the form:  INSERT INTO table(command) VALUES('xyzzy');
  2692   2718         ** cause zWord to be NULL, so we look at the "command" column to see
  2693   2719         ** what special actions to take */
  2694   2720         const char *zCmd = 
  2695   2721            (const char*)sqlite3_value_text(argv[SPELLFIX_COL_COMMAND+2]);
................................................................................
  2742   2768                  "VALUES(%d,%d,%Q,%Q,%Q)",
  2743   2769                  p->zDbName, p->zTableName,
  2744   2770                  iRank, iLang, zWord, zK1, zK2
  2745   2771           );
  2746   2772         }else{
  2747   2773           newRowid = sqlite3_value_int64(argv[1]);
  2748   2774           spellfix1DbExec(&rc, db,
  2749         -               "INSERT INTO \"%w\".\"%w_vocab\"(id,rank,langid,word,k1,k2) "
  2750         -               "VALUES(%lld,%d,%d,%Q,%Q,%Q)",
  2751         -               p->zDbName, p->zTableName,
  2752         -               newRowid, iRank, iLang, zWord, zK1, zK2
         2775  +            "INSERT OR %s INTO \"%w\".\"%w_vocab\"(id,rank,langid,word,k1,k2) "
         2776  +            "VALUES(%lld,%d,%d,%Q,%Q,%Q)",
         2777  +            zConflict, p->zDbName, p->zTableName,
         2778  +            newRowid, iRank, iLang, zWord, zK1, zK2
  2753   2779           );
  2754   2780         }
  2755   2781         *pRowid = sqlite3_last_insert_rowid(db);
  2756   2782       }else{
  2757   2783         rowid = sqlite3_value_int64(argv[0]);
  2758   2784         newRowid = *pRowid = sqlite3_value_int64(argv[1]);
  2759   2785         spellfix1DbExec(&rc, db,
  2760         -             "UPDATE \"%w\".\"%w_vocab\" SET id=%lld, rank=%d, langid=%d,"
         2786  +             "UPDATE OR %s \"%w\".\"%w_vocab\" SET id=%lld, rank=%d, langid=%d,"
  2761   2787                " word=%Q, k1=%Q, k2=%Q WHERE id=%lld",
  2762         -             p->zDbName, p->zTableName, newRowid, iRank, iLang,
         2788  +             zConflict, p->zDbName, p->zTableName, newRowid, iRank, iLang,
  2763   2789                zWord, zK1, zK2, rowid
  2764   2790         );
  2765   2791       }
  2766   2792       sqlite3_free(zK1);
  2767   2793       sqlite3_free(zK2);
  2768   2794     }
  2769   2795     return rc;

Changes to test/spellfix.test.

   279    279     do_tracesql_test 6.2.3 {
   280    280       SELECT word, distance FROM t3 WHERE rowid = 10 AND word MATCH 'kiiner';
   281    281     } {keener 300
   282    282       {SELECT id, word, rank, k1  FROM "main"."t3_vocab" WHERE langid=0 AND k2>=?1 AND k2<?2}
   283    283     }
   284    284   }
   285    285   
          286  +#------------------------------------------------------------------------- 
          287  +# Test that the spellfix1 table supports conflict handling (OR REPLACE 
          288  +# and so on).
          289  +#
          290  +do_execsql_test 7.1 {
          291  +  CREATE VIRTUAL TABLE t4 USING spellfix1;
          292  +  PRAGMA table_info = t4;
          293  +} {
          294  +  0 word {} 0 {} 0 
          295  +  1 rank {} 0 {} 0 
          296  +  2 distance {} 0 {} 0 
          297  +  3 langid {} 0 {} 0 
          298  +  4 score {} 0 {} 0 
          299  +  5 matchlen {} 0 {} 0
          300  +}
          301  +
          302  +do_execsql_test 7.2.1 {
          303  +  INSERT INTO t4(rowid, word) VALUES(1, 'Archilles');
          304  +  INSERT INTO t4(rowid, word) VALUES(2, 'Pluto');
          305  +  INSERT INTO t4(rowid, word) VALUES(3, 'Atrides');
          306  +  INSERT OR REPLACE INTO t4(rowid, word) VALUES(2, 'Apollo');
          307  +  SELECT rowid, word FROM t4;
          308  +} {
          309  +  1 Archilles   2 Apollo   3 Atrides
          310  +}
          311  +do_catchsql_test 7.2.2 {
          312  +  INSERT OR ABORT INTO t4(rowid, word) VALUES(1, 'Leto');
          313  +} {1 {constraint failed}}
          314  +do_catchsql_test 7.2.3 {
          315  +  INSERT OR ROLLBACK INTO t4(rowid, word) VALUES(3, 'Zeus');
          316  +} {1 {constraint failed}}
          317  +do_catchsql_test 7.2.4 {
          318  +  INSERT OR FAIL INTO t4(rowid, word) VALUES(3, 'Zeus');
          319  +} {1 {constraint failed}}
          320  +do_execsql_test 7.2.5 {
          321  +  INSERT OR IGNORE INTO t4(rowid, word) VALUES(3, 'Zeus');
          322  +  SELECT rowid, word FROM t4;
          323  +} {
          324  +  1 Archilles   2 Apollo   3 Atrides
          325  +}
          326  +
          327  +do_execsql_test 7.3.1 {
          328  +  UPDATE OR REPLACE t4 SET rowid=3 WHERE rowid=1;
          329  +  SELECT rowid, word FROM t4;
          330  +} {2 Apollo 3 Archilles}
          331  +do_catchsql_test 7.3.2 {
          332  +  UPDATE OR ABORT t4 SET rowid=3 WHERE rowid=2;
          333  +} {1 {constraint failed}}
          334  +do_catchsql_test 7.3.3 {
          335  +  UPDATE OR ROLLBACK t4 SET rowid=3 WHERE rowid=2;
          336  +} {1 {constraint failed}}
          337  +do_catchsql_test 7.3.4 {
          338  +  UPDATE OR FAIL t4 SET rowid=3 WHERE rowid=2;
          339  +} {1 {constraint failed}}
          340  +do_execsql_test 7.3.5 {
          341  +  UPDATE OR IGNORE t4 SET rowid=3 WHERE rowid=2;
          342  +  SELECT rowid, word FROM t4;
          343  +} {2 Apollo  3 Archilles}
          344  +
          345  +do_execsql_test 7.4.1 {
          346  +  DELETE FROM t4;
          347  +  INSERT INTO t4(rowid, word) VALUES(10, 'Agamemnon');
          348  +  INSERT INTO t4(rowid, word) VALUES(20, 'Patroclus');
          349  +  INSERT INTO t4(rowid, word) VALUES(30, 'Chryses');
          350  +
          351  +  CREATE TABLE t5(i, w);
          352  +  INSERT INTO t5 VALUES(5,  'Poseidon');
          353  +  INSERT INTO t5 VALUES(20, 'Chronos');
          354  +  INSERT INTO t5 VALUES(30, 'Hera');
          355  +}
          356  +
          357  +db_save_and_close
          358  +foreach {tn conflict err bRollback res} {
          359  +  0 ""            {1 {constraint failed}} 0
          360  +                  {10 Agamemnon 20 Patroclus 30 Chryses}
          361  +  1 "OR REPLACE"  {0 {}} 0
          362  +                  {5 Poseidon 10 Agamemnon 20 Chronos 30 Hera}
          363  +  2 "OR ABORT"    {1 {constraint failed}} 0
          364  +                  {10 Agamemnon 20 Patroclus 30 Chryses}
          365  +  3 "OR ROLLBACK" {1 {constraint failed}} 1
          366  +                  {10 Agamemnon 20 Patroclus 30 Chryses}
          367  +  5 "OR IGNORE"   {0 {}} 0
          368  +                  {5 Poseidon 10 Agamemnon 20 Patroclus 30 Chryses}
          369  +} {
          370  +  db_restore_and_reopen
          371  +  load_static_extension db spellfix nextchar
          372  +
          373  +  execsql BEGIN
          374  +  set sql "INSERT $conflict INTO t4(rowid, word) SELECT i, w FROM t5"
          375  +  do_catchsql_test 7.4.2.$tn.1 $sql $err
          376  +  do_execsql_test 7.4.2.$tn.2 { SELECT rowid, word FROM t4 } $res
          377  +
          378  +  do_test 7.4.2.$tn.3 { sqlite3_get_autocommit db } $bRollback
          379  +  catchsql ROLLBACK
          380  +}
   286    381   
          382  +foreach {tn conflict err bRollback res} {
          383  +  0 ""            {1 {constraint failed}} 0
          384  +                  {10 Agamemnon 20 Patroclus 30 Chryses}
          385  +  1 "OR REPLACE"  {0 {}} 0
          386  +                  {15 Agamemnon 45 Chryses}
          387  +  2 "OR ABORT"    {1 {constraint failed}} 0
          388  +                  {10 Agamemnon 20 Patroclus 30 Chryses}
          389  +  3 "OR ROLLBACK" {1 {constraint failed}} 1
          390  +                  {10 Agamemnon 20 Patroclus 30 Chryses}
          391  +  5 "OR IGNORE"   {0 {}} 0
          392  +                  {15 Agamemnon 20 Patroclus 45 Chryses}
          393  +} {
          394  +  db_restore_and_reopen
          395  +  load_static_extension db spellfix nextchar
   287    396   
          397  +  execsql BEGIN
          398  +  set sql "UPDATE $conflict t4 SET rowid=rowid + (rowid/2)"
          399  +  do_catchsql_test 7.5.2.$tn.1 $sql $err
          400  +  do_execsql_test 7.5.2.$tn.2 { SELECT rowid, word FROM t4 } $res
          401  +  do_test 7.5.2.$tn.3 { sqlite3_get_autocommit db } $bRollback
          402  +  catchsql ROLLBACK
          403  +}
   288    404   
   289    405   finish_test
          406  +