/ 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 Unified Diffs Ignore Whitespace Patch

Changes to ext/misc/spellfix.c.

2650
2651
2652
2653
2654
2655
2656

























2657
2658
2659
2660
2661
2662
2663
....
2682
2683
2684
2685
2686
2687
2688

2689
2690
2691
2692
2693
2694
2695
....
2742
2743
2744
2745
2746
2747
2748
2749
2750
2751
2752
2753
2754
2755
2756
2757
2758
2759
2760
2761
2762
2763
2764
2765
2766
2767
2768
2769
  if( pCur->pFullScan ){
    *pRowid = sqlite3_column_int64(pCur->pFullScan, 4);
  }else{
    *pRowid = pCur->a[pCur->iRow].iRowid;
  }
  return SQLITE_OK;
}


























/*
** The xUpdate() method.
*/
static int spellfix1Update(
  sqlite3_vtab *pVTab,
  int argc,
................................................................................
    int iRank = sqlite3_value_int(argv[SPELLFIX_COL_RANK+2]);
    const unsigned char *zSoundslike =
           sqlite3_value_text(argv[SPELLFIX_COL_SOUNDSLIKE+2]);
    int nSoundslike = sqlite3_value_bytes(argv[SPELLFIX_COL_SOUNDSLIKE+2]);
    char *zK1, *zK2;
    int i;
    char c;


    if( zWord==0 ){
      /* Inserts of the form:  INSERT INTO table(command) VALUES('xyzzy');
      ** cause zWord to be NULL, so we look at the "command" column to see
      ** what special actions to take */
      const char *zCmd = 
         (const char*)sqlite3_value_text(argv[SPELLFIX_COL_COMMAND+2]);
................................................................................
               "VALUES(%d,%d,%Q,%Q,%Q)",
               p->zDbName, p->zTableName,
               iRank, iLang, zWord, zK1, zK2
        );
      }else{
        newRowid = sqlite3_value_int64(argv[1]);
        spellfix1DbExec(&rc, db,
               "INSERT INTO \"%w\".\"%w_vocab\"(id,rank,langid,word,k1,k2) "
               "VALUES(%lld,%d,%d,%Q,%Q,%Q)",
               p->zDbName, p->zTableName,
               newRowid, iRank, iLang, zWord, zK1, zK2
        );
      }
      *pRowid = sqlite3_last_insert_rowid(db);
    }else{
      rowid = sqlite3_value_int64(argv[0]);
      newRowid = *pRowid = sqlite3_value_int64(argv[1]);
      spellfix1DbExec(&rc, db,
             "UPDATE \"%w\".\"%w_vocab\" SET id=%lld, rank=%d, langid=%d,"
             " word=%Q, k1=%Q, k2=%Q WHERE id=%lld",
             p->zDbName, p->zTableName, newRowid, iRank, iLang,
             zWord, zK1, zK2, rowid
      );
    }
    sqlite3_free(zK1);
    sqlite3_free(zK2);
  }
  return rc;







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>







 







|
|
|
|







|

|







2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
2661
2662
2663
2664
2665
2666
2667
2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
2680
2681
2682
2683
2684
2685
2686
2687
2688
....
2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
2721
....
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
  if( pCur->pFullScan ){
    *pRowid = sqlite3_column_int64(pCur->pFullScan, 4);
  }else{
    *pRowid = pCur->a[pCur->iRow].iRowid;
  }
  return SQLITE_OK;
}

/*
** This function is called by the xUpdate() method. It returns a string
** containing the conflict mode that xUpdate() should use for the current
** operation. One of: "ROLLBACK", "IGNORE", "ABORT" or "REPLACE".
*/
static const char *spellfix1GetConflict(sqlite3 *db){
  static const char *azConflict[] = {
    /* Note: Instead of "FAIL" - "ABORT". */
    "ROLLBACK", "IGNORE", "ABORT", "ABORT", "REPLACE"
  };
  int eConflict = sqlite3_vtab_on_conflict(db);

  assert( eConflict==SQLITE_ROLLBACK || eConflict==SQLITE_IGNORE
       || eConflict==SQLITE_FAIL || eConflict==SQLITE_ABORT
       || eConflict==SQLITE_REPLACE
  );
  assert( SQLITE_ROLLBACK==1 );
  assert( SQLITE_IGNORE==2 );
  assert( SQLITE_FAIL==3 );
  assert( SQLITE_ABORT==4 );
  assert( SQLITE_REPLACE==5 );

  return azConflict[eConflict-1];
}

/*
** The xUpdate() method.
*/
static int spellfix1Update(
  sqlite3_vtab *pVTab,
  int argc,
................................................................................
    int iRank = sqlite3_value_int(argv[SPELLFIX_COL_RANK+2]);
    const unsigned char *zSoundslike =
           sqlite3_value_text(argv[SPELLFIX_COL_SOUNDSLIKE+2]);
    int nSoundslike = sqlite3_value_bytes(argv[SPELLFIX_COL_SOUNDSLIKE+2]);
    char *zK1, *zK2;
    int i;
    char c;
    const char *zConflict = spellfix1GetConflict(db);

    if( zWord==0 ){
      /* Inserts of the form:  INSERT INTO table(command) VALUES('xyzzy');
      ** cause zWord to be NULL, so we look at the "command" column to see
      ** what special actions to take */
      const char *zCmd = 
         (const char*)sqlite3_value_text(argv[SPELLFIX_COL_COMMAND+2]);
................................................................................
               "VALUES(%d,%d,%Q,%Q,%Q)",
               p->zDbName, p->zTableName,
               iRank, iLang, zWord, zK1, zK2
        );
      }else{
        newRowid = sqlite3_value_int64(argv[1]);
        spellfix1DbExec(&rc, db,
            "INSERT OR %s INTO \"%w\".\"%w_vocab\"(id,rank,langid,word,k1,k2) "
            "VALUES(%lld,%d,%d,%Q,%Q,%Q)",
            zConflict, p->zDbName, p->zTableName,
            newRowid, iRank, iLang, zWord, zK1, zK2
        );
      }
      *pRowid = sqlite3_last_insert_rowid(db);
    }else{
      rowid = sqlite3_value_int64(argv[0]);
      newRowid = *pRowid = sqlite3_value_int64(argv[1]);
      spellfix1DbExec(&rc, db,
             "UPDATE OR %s \"%w\".\"%w_vocab\" SET id=%lld, rank=%d, langid=%d,"
             " word=%Q, k1=%Q, k2=%Q WHERE id=%lld",
             zConflict, p->zDbName, p->zTableName, newRowid, iRank, iLang,
             zWord, zK1, zK2, rowid
      );
    }
    sqlite3_free(zK1);
    sqlite3_free(zK2);
  }
  return rc;

Changes to test/spellfix.test.

279
280
281
282
283
284
285



286










287
288







































































































289

  do_tracesql_test 6.2.3 {
    SELECT word, distance FROM t3 WHERE rowid = 10 AND word MATCH 'kiiner';
  } {keener 300
    {SELECT id, word, rank, k1  FROM "main"."t3_vocab" WHERE langid=0 AND k2>=?1 AND k2<?2}
  }
}
























































































































finish_test








>
>
>
|
>
>
>
>
>
>
>
>
>
>
|

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

>
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
  do_tracesql_test 6.2.3 {
    SELECT word, distance FROM t3 WHERE rowid = 10 AND word MATCH 'kiiner';
  } {keener 300
    {SELECT id, word, rank, k1  FROM "main"."t3_vocab" WHERE langid=0 AND k2>=?1 AND k2<?2}
  }
}

#------------------------------------------------------------------------- 
# Test that the spellfix1 table supports conflict handling (OR REPLACE 
# and so on).
#
do_execsql_test 7.1 {
  CREATE VIRTUAL TABLE t4 USING spellfix1;
  PRAGMA table_info = t4;
} {
  0 word {} 0 {} 0 
  1 rank {} 0 {} 0 
  2 distance {} 0 {} 0 
  3 langid {} 0 {} 0 
  4 score {} 0 {} 0 
  5 matchlen {} 0 {} 0
}

do_execsql_test 7.2.1 {
  INSERT INTO t4(rowid, word) VALUES(1, 'Archilles');
  INSERT INTO t4(rowid, word) VALUES(2, 'Pluto');
  INSERT INTO t4(rowid, word) VALUES(3, 'Atrides');
  INSERT OR REPLACE INTO t4(rowid, word) VALUES(2, 'Apollo');
  SELECT rowid, word FROM t4;
} {
  1 Archilles   2 Apollo   3 Atrides
}
do_catchsql_test 7.2.2 {
  INSERT OR ABORT INTO t4(rowid, word) VALUES(1, 'Leto');
} {1 {constraint failed}}
do_catchsql_test 7.2.3 {
  INSERT OR ROLLBACK INTO t4(rowid, word) VALUES(3, 'Zeus');
} {1 {constraint failed}}
do_catchsql_test 7.2.4 {
  INSERT OR FAIL INTO t4(rowid, word) VALUES(3, 'Zeus');
} {1 {constraint failed}}
do_execsql_test 7.2.5 {
  INSERT OR IGNORE INTO t4(rowid, word) VALUES(3, 'Zeus');
  SELECT rowid, word FROM t4;
} {
  1 Archilles   2 Apollo   3 Atrides
}

do_execsql_test 7.3.1 {
  UPDATE OR REPLACE t4 SET rowid=3 WHERE rowid=1;
  SELECT rowid, word FROM t4;
} {2 Apollo 3 Archilles}
do_catchsql_test 7.3.2 {
  UPDATE OR ABORT t4 SET rowid=3 WHERE rowid=2;
} {1 {constraint failed}}
do_catchsql_test 7.3.3 {
  UPDATE OR ROLLBACK t4 SET rowid=3 WHERE rowid=2;
} {1 {constraint failed}}
do_catchsql_test 7.3.4 {
  UPDATE OR FAIL t4 SET rowid=3 WHERE rowid=2;
} {1 {constraint failed}}
do_execsql_test 7.3.5 {
  UPDATE OR IGNORE t4 SET rowid=3 WHERE rowid=2;
  SELECT rowid, word FROM t4;
} {2 Apollo  3 Archilles}

do_execsql_test 7.4.1 {
  DELETE FROM t4;
  INSERT INTO t4(rowid, word) VALUES(10, 'Agamemnon');
  INSERT INTO t4(rowid, word) VALUES(20, 'Patroclus');
  INSERT INTO t4(rowid, word) VALUES(30, 'Chryses');

  CREATE TABLE t5(i, w);
  INSERT INTO t5 VALUES(5,  'Poseidon');
  INSERT INTO t5 VALUES(20, 'Chronos');
  INSERT INTO t5 VALUES(30, 'Hera');
}

db_save_and_close
foreach {tn conflict err bRollback res} {
  0 ""            {1 {constraint failed}} 0
                  {10 Agamemnon 20 Patroclus 30 Chryses}
  1 "OR REPLACE"  {0 {}} 0
                  {5 Poseidon 10 Agamemnon 20 Chronos 30 Hera}
  2 "OR ABORT"    {1 {constraint failed}} 0
                  {10 Agamemnon 20 Patroclus 30 Chryses}
  3 "OR ROLLBACK" {1 {constraint failed}} 1
                  {10 Agamemnon 20 Patroclus 30 Chryses}
  5 "OR IGNORE"   {0 {}} 0
                  {5 Poseidon 10 Agamemnon 20 Patroclus 30 Chryses}
} {
  db_restore_and_reopen
  load_static_extension db spellfix nextchar

  execsql BEGIN
  set sql "INSERT $conflict INTO t4(rowid, word) SELECT i, w FROM t5"
  do_catchsql_test 7.4.2.$tn.1 $sql $err
  do_execsql_test 7.4.2.$tn.2 { SELECT rowid, word FROM t4 } $res

  do_test 7.4.2.$tn.3 { sqlite3_get_autocommit db } $bRollback
  catchsql ROLLBACK
}

foreach {tn conflict err bRollback res} {
  0 ""            {1 {constraint failed}} 0
                  {10 Agamemnon 20 Patroclus 30 Chryses}
  1 "OR REPLACE"  {0 {}} 0
                  {15 Agamemnon 45 Chryses}
  2 "OR ABORT"    {1 {constraint failed}} 0
                  {10 Agamemnon 20 Patroclus 30 Chryses}
  3 "OR ROLLBACK" {1 {constraint failed}} 1
                  {10 Agamemnon 20 Patroclus 30 Chryses}
  5 "OR IGNORE"   {0 {}} 0
                  {15 Agamemnon 20 Patroclus 45 Chryses}
} {
  db_restore_and_reopen
  load_static_extension db spellfix nextchar

  execsql BEGIN
  set sql "UPDATE $conflict t4 SET rowid=rowid + (rowid/2)"
  do_catchsql_test 7.5.2.$tn.1 $sql $err
  do_execsql_test 7.5.2.$tn.2 { SELECT rowid, word FROM t4 } $res
  do_test 7.5.2.$tn.3 { sqlite3_get_autocommit db } $bRollback
  catchsql ROLLBACK
}

finish_test