/ Check-in [b76ec7cb]
Login

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

Overview
Comment:Try to optimize spellfix1 by storing a NULL in the k1 column of %_vocab if it would otherwise have the same value as the word column.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:b76ec7cb5841f9ad02b342271e91c5f5a598006f69df7953a6213c706e01f7a2
User & Date: drh 2018-02-14 14:13:09
Context
2018-02-14
15:30
New test cases for the editdist3() function in the spellfix extension. check-in: 2d3f1f22 user: drh tags: trunk
14:13
Try to optimize spellfix1 by storing a NULL in the k1 column of %_vocab if it would otherwise have the same value as the word column. check-in: b76ec7cb user: drh tags: trunk
2018-02-13
19:13
Improved test cases for the [f484b65f3d6230593c34f] bug. check-in: 1f300514 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/misc/spellfix.c.

  2488   2488     if( zPattern==0 ){
  2489   2489       x.rc = SQLITE_NOMEM;
  2490   2490       goto filter_exit;
  2491   2491     }
  2492   2492     nPattern = (int)strlen(zPattern);
  2493   2493     if( zPattern[nPattern-1]=='*' ) nPattern--;
  2494   2494     zSql = sqlite3_mprintf(
  2495         -     "SELECT id, word, rank, k1"
         2495  +     "SELECT id, word, rank, coalesce(k1,word)"
  2496   2496        "  FROM \"%w\".\"%w_vocab\""
  2497   2497        " WHERE langid=%d AND k2>=?1 AND k2<?2",
  2498   2498        p->zDbName, p->zTableName, iLang
  2499   2499     );
  2500   2500     if( zSql==0 ){
  2501   2501       x.rc = SQLITE_NOMEM;
  2502   2502       pStmt = 0;
................................................................................
  2822   2822         sqlite3_free(zK1);
  2823   2823         return SQLITE_NOMEM;
  2824   2824       }
  2825   2825       if( sqlite3_value_type(argv[0])==SQLITE_NULL ){
  2826   2826         if( sqlite3_value_type(argv[1])==SQLITE_NULL ){
  2827   2827           spellfix1DbExec(&rc, db,
  2828   2828                  "INSERT INTO \"%w\".\"%w_vocab\"(rank,langid,word,k1,k2) "
  2829         -               "VALUES(%d,%d,%Q,%Q,%Q)",
         2829  +               "VALUES(%d,%d,%Q,nullif(%Q,%Q),%Q)",
  2830   2830                  p->zDbName, p->zTableName,
  2831         -               iRank, iLang, zWord, zK1, zK2
         2831  +               iRank, iLang, zWord, zK1, zWord, zK2
  2832   2832           );
  2833   2833         }else{
  2834   2834           newRowid = sqlite3_value_int64(argv[1]);
  2835   2835           spellfix1DbExec(&rc, db,
  2836   2836               "INSERT OR %s INTO \"%w\".\"%w_vocab\"(id,rank,langid,word,k1,k2) "
  2837         -            "VALUES(%lld,%d,%d,%Q,%Q,%Q)",
         2837  +            "VALUES(%lld,%d,%d,%Q,nullif(%Q,%Q),%Q)",
  2838   2838               zConflict, p->zDbName, p->zTableName,
  2839         -            newRowid, iRank, iLang, zWord, zK1, zK2
         2839  +            newRowid, iRank, iLang, zWord, zK1, zWord, zK2
  2840   2840           );
  2841   2841         }
  2842   2842         *pRowid = sqlite3_last_insert_rowid(db);
  2843   2843       }else{
  2844   2844         rowid = sqlite3_value_int64(argv[0]);
  2845   2845         newRowid = *pRowid = sqlite3_value_int64(argv[1]);
  2846   2846         spellfix1DbExec(&rc, db,
  2847   2847                "UPDATE OR %s \"%w\".\"%w_vocab\" SET id=%lld, rank=%d, langid=%d,"
  2848         -             " word=%Q, k1=%Q, k2=%Q WHERE id=%lld",
         2848  +             " word=%Q, k1=nullif(%Q,%Q), k2=%Q WHERE id=%lld",
  2849   2849                zConflict, p->zDbName, p->zTableName, newRowid, iRank, iLang,
  2850         -             zWord, zK1, zK2, rowid
         2850  +             zWord, zK1, zWord, zK2, rowid
  2851   2851         );
  2852   2852       }
  2853   2853       sqlite3_free(zK1);
  2854   2854       sqlite3_free(zK2);
  2855   2855     }
  2856   2856     return rc;
  2857   2857   }

Changes to test/spellfix.test.

   275    275       SELECT word, distance FROM t3 WHERE rowid = 10;
   276    276     } {keener {}
   277    277       {SELECT word, rank, NULL, langid, id FROM "main"."t3_vocab" WHERE rowid=?}
   278    278     }
   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         -    {SELECT id, word, rank, k1  FROM "main"."t3_vocab" WHERE langid=0 AND k2>=?1 AND k2<?2}
          282  +    {SELECT id, word, rank, coalesce(k1,word)  FROM "main"."t3_vocab" WHERE langid=0 AND k2>=?1 AND k2<?2}
   283    283     }
   284    284   }
   285    285   
   286    286   #------------------------------------------------------------------------- 
   287    287   # Test that the spellfix1 table supports conflict handling (OR REPLACE 
   288    288   # and so on).
   289    289   #