/ Check-in [2a99c007]
Login

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

Overview
Comment:Merge the latest trunk changes into the apple-osx branch.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | apple-osx
Files: files | file ages | folders
SHA1: 2a99c0074aaabb4916d82464427be079d44d65e6
User & Date: drh 2012-05-05 01:03:31
Context
2012-05-09
22:36
Added support for SQLITE_ENABLE_PERSIST_WAL compile time macro, retrieving lastErrno from WAL file and setting last errno when writes fail due to space constraints check-in: 65479294 user: adam tags: apple-osx
2012-05-05
01:03
Merge the latest trunk changes into the apple-osx branch. check-in: 2a99c007 user: drh tags: apple-osx
2012-05-01
14:21
Update a test in io.test to account for [05f98d4eec]. check-in: bfa61e78 user: dan tags: trunk
2012-04-19
20:43
Update the apple-osx branch so that it includes all of the latest trunk changes. check-in: b72f07b4 user: drh tags: apple-osx
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Deleted art/2005osaward.gif.

cannot compute difference between binary files

Deleted art/SQLite.eps.

cannot compute difference between binary files

Deleted art/SQLite.gif.

cannot compute difference between binary files

Deleted art/SQLiteLogo3.tiff.

cannot compute difference between binary files

Deleted art/SQLite_big.gif.

cannot compute difference between binary files

Deleted art/nocopy.gif.

cannot compute difference between binary files

Deleted art/powered_by_sqlite.gif.

cannot compute difference between binary files

Deleted art/src_logo.gif.

cannot compute difference between binary files

Changes to src/pager.c.

  3007   3007       /* If a WAL transaction is being committed, there is no point in writing
  3008   3008       ** any pages with page numbers greater than nTruncate into the WAL file.
  3009   3009       ** They will never be read by any client. So remove them from the pDirty
  3010   3010       ** list here. */
  3011   3011       PgHdr *p;
  3012   3012       PgHdr **ppNext = &pList;
  3013   3013       nList = 0;
  3014         -    for(p=pList; (*ppNext = p); p=p->pDirty){
         3014  +    for(p=pList; (*ppNext = p)!=0; p=p->pDirty){
  3015   3015         if( p->pgno<=nTruncate ){
  3016   3016           ppNext = &p->pDirty;
  3017   3017           nList++;
  3018   3018         }
  3019   3019       }
  3020   3020       assert( pList );
  3021   3021     }else{

Changes to src/resolve.c.

   879    879   */
   880    880   static int resolveOrderGroupBy(
   881    881     NameContext *pNC,     /* The name context of the SELECT statement */
   882    882     Select *pSelect,      /* The SELECT statement holding pOrderBy */
   883    883     ExprList *pOrderBy,   /* An ORDER BY or GROUP BY clause to resolve */
   884    884     const char *zType     /* Either "ORDER" or "GROUP", as appropriate */
   885    885   ){
   886         -  int i;                         /* Loop counter */
          886  +  int i, j;                      /* Loop counters */
   887    887     int iCol;                      /* Column number */
   888    888     struct ExprList_item *pItem;   /* A term of the ORDER BY clause */
   889    889     Parse *pParse;                 /* Parsing context */
   890    890     int nResult;                   /* Number of terms in the result set */
   891    891   
   892    892     if( pOrderBy==0 ) return 0;
   893    893     nResult = pSelect->pEList->nExpr;
................................................................................
   915    915         continue;
   916    916       }
   917    917   
   918    918       /* Otherwise, treat the ORDER BY term as an ordinary expression */
   919    919       pItem->iOrderByCol = 0;
   920    920       if( sqlite3ResolveExprNames(pNC, pE) ){
   921    921         return 1;
          922  +    }
          923  +    for(j=0; j<pSelect->pEList->nExpr; j++){
          924  +      if( sqlite3ExprCompare(pE, pSelect->pEList->a[j].pExpr)==0 ){
          925  +        pItem->iOrderByCol = j+1;
          926  +      }
   922    927       }
   923    928     }
   924    929     return sqlite3ResolveOrderGroupBy(pParse, pSelect, pOrderBy, zType);
   925    930   }
   926    931   
   927    932   /*
   928    933   ** Resolve names in the SELECT statement p and all of its descendents.

Changes to src/shell.c.

   495    495   /*
   496    496   ** Output the given string as a hex-encoded blob (eg. X'1234' )
   497    497   */
   498    498   static void output_hex_blob(FILE *out, const void *pBlob, int nBlob){
   499    499     int i;
   500    500     char *zBlob = (char *)pBlob;
   501    501     fprintf(out,"X'");
   502         -  for(i=0; i<nBlob; i++){ fprintf(out,"%02x",zBlob[i]); }
          502  +  for(i=0; i<nBlob; i++){ fprintf(out,"%02x",zBlob[i]&0xff); }
   503    503     fprintf(out,"'");
   504    504   }
   505    505   
   506    506   /*
   507    507   ** Output the given string as a quoted string using SQL quoting conventions.
   508    508   */
   509    509   static void output_quoted_string(FILE *out, const char *z){
................................................................................
  2244   2244     }else
  2245   2245   
  2246   2246     if( c=='s' && strncmp(azArg[0], "stats", n)==0 && nArg>1 && nArg<3 ){
  2247   2247       p->statsOn = booleanValue(azArg[1]);
  2248   2248     }else
  2249   2249   
  2250   2250     if( c=='t' && n>1 && strncmp(azArg[0], "tables", n)==0 && nArg<3 ){
         2251  +    sqlite3_stmt *pStmt;
  2251   2252       char **azResult;
  2252         -    int nRow;
  2253         -    char *zErrMsg;
         2253  +    int nRow, nAlloc;
         2254  +    char *zSql = 0;
         2255  +    int ii;
  2254   2256       open_db(p);
  2255         -    if( nArg==1 ){
  2256         -      rc = sqlite3_get_table(p->db,
  2257         -        "SELECT name FROM sqlite_master "
  2258         -        "WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' "
  2259         -        "UNION ALL "
  2260         -        "SELECT name FROM sqlite_temp_master "
  2261         -        "WHERE type IN ('table','view') "
  2262         -        "ORDER BY 1",
  2263         -        &azResult, &nRow, 0, &zErrMsg
  2264         -      );
  2265         -    }else{
  2266         -      zShellStatic = azArg[1];
  2267         -      rc = sqlite3_get_table(p->db,
  2268         -        "SELECT name FROM sqlite_master "
  2269         -        "WHERE type IN ('table','view') AND name LIKE shellstatic() "
  2270         -        "UNION ALL "
  2271         -        "SELECT name FROM sqlite_temp_master "
  2272         -        "WHERE type IN ('table','view') AND name LIKE shellstatic() "
  2273         -        "ORDER BY 1",
  2274         -        &azResult, &nRow, 0, &zErrMsg
  2275         -      );
  2276         -      zShellStatic = 0;
  2277         -    }
  2278         -    if( zErrMsg ){
  2279         -      fprintf(stderr,"Error: %s\n", zErrMsg);
  2280         -      sqlite3_free(zErrMsg);
  2281         -      rc = 1;
  2282         -    }else if( rc != SQLITE_OK ){
  2283         -      fprintf(stderr,"Error: querying sqlite_master and sqlite_temp_master\n");
  2284         -      rc = 1;
         2257  +    rc = sqlite3_prepare_v2(p->db, "PRAGMA database_list", -1, &pStmt, 0);
         2258  +    if( rc ) return rc;
         2259  +    zSql = sqlite3_mprintf(
         2260  +        "SELECT name FROM sqlite_master"
         2261  +        " WHERE type IN ('table','view')"
         2262  +        "   AND name NOT LIKE 'sqlite_%%'"
         2263  +        "   AND name LIKE ?1");
         2264  +    while( sqlite3_step(pStmt)==SQLITE_ROW ){
         2265  +      const char *zDbName = (const char*)sqlite3_column_text(pStmt, 1);
         2266  +      if( zDbName==0 || strcmp(zDbName,"main")==0 ) continue;
         2267  +      if( strcmp(zDbName,"temp")==0 ){
         2268  +        zSql = sqlite3_mprintf(
         2269  +                 "%z UNION ALL "
         2270  +                 "SELECT 'temp.' || name FROM sqlite_temp_master"
         2271  +                 " WHERE type IN ('table','view')"
         2272  +                 "   AND name NOT LIKE 'sqlite_%%'"
         2273  +                 "   AND name LIKE ?1", zSql);
         2274  +      }else{
         2275  +        zSql = sqlite3_mprintf(
         2276  +                 "%z UNION ALL "
         2277  +                 "SELECT '%q.' || name FROM \"%w\".sqlite_master"
         2278  +                 " WHERE type IN ('table','view')"
         2279  +                 "   AND name NOT LIKE 'sqlite_%%'"
         2280  +                 "   AND name LIKE ?1", zSql, zDbName, zDbName);
         2281  +      }
         2282  +    }
         2283  +    sqlite3_finalize(pStmt);
         2284  +    zSql = sqlite3_mprintf("%z ORDER BY 1", zSql);
         2285  +    rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
         2286  +    sqlite3_free(zSql);
         2287  +    if( rc ) return rc;
         2288  +    nRow = nAlloc = 0;
         2289  +    azResult = 0;
         2290  +    if( nArg>1 ){
         2291  +      sqlite3_bind_text(pStmt, 1, azArg[1], -1, SQLITE_TRANSIENT);
  2285   2292       }else{
         2293  +      sqlite3_bind_text(pStmt, 1, "%", -1, SQLITE_STATIC);
         2294  +    }
         2295  +    while( sqlite3_step(pStmt)==SQLITE_ROW ){
         2296  +      if( nRow>=nAlloc ){
         2297  +        char **azNew;
         2298  +        int n = nAlloc*2 + 10;
         2299  +        azNew = sqlite3_realloc(azResult, sizeof(azResult[0])*n);
         2300  +        if( azNew==0 ){
         2301  +          fprintf(stderr, "Error: out of memory\n");
         2302  +          break;
         2303  +        }
         2304  +        nAlloc = n;
         2305  +        azResult = azNew;
         2306  +      }
         2307  +      azResult[nRow] = sqlite3_mprintf("%s", sqlite3_column_text(pStmt, 0));
         2308  +      if( azResult[nRow] ) nRow++;
         2309  +    }
         2310  +    sqlite3_finalize(pStmt);        
         2311  +    if( nRow>0 ){
  2286   2312         int len, maxlen = 0;
  2287   2313         int i, j;
  2288   2314         int nPrintCol, nPrintRow;
  2289         -      for(i=1; i<=nRow; i++){
  2290         -        if( azResult[i]==0 ) continue;
         2315  +      for(i=0; i<nRow; i++){
  2291   2316           len = strlen30(azResult[i]);
  2292   2317           if( len>maxlen ) maxlen = len;
  2293   2318         }
  2294   2319         nPrintCol = 80/(maxlen+2);
  2295   2320         if( nPrintCol<1 ) nPrintCol = 1;
  2296   2321         nPrintRow = (nRow + nPrintCol - 1)/nPrintCol;
  2297   2322         for(i=0; i<nPrintRow; i++){
  2298         -        for(j=i+1; j<=nRow; j+=nPrintRow){
  2299         -          char *zSp = j<=nPrintRow ? "" : "  ";
         2323  +        for(j=i; j<nRow; j+=nPrintRow){
         2324  +          char *zSp = j<nPrintRow ? "" : "  ";
  2300   2325             printf("%s%-*s", zSp, maxlen, azResult[j] ? azResult[j] : "");
  2301   2326           }
  2302   2327           printf("\n");
  2303   2328         }
  2304   2329       }
  2305         -    sqlite3_free_table(azResult);
         2330  +    for(ii=0; ii<nRow; ii++) sqlite3_free(azResult[ii]);
         2331  +    sqlite3_free(azResult);
  2306   2332     }else
  2307   2333   
  2308   2334     if( c=='t' && n>=8 && strncmp(azArg[0], "testctrl", n)==0 && nArg>=2 ){
  2309   2335       static const struct {
  2310   2336          const char *zCtrlName;   /* Name of a test-control option */
  2311   2337          int ctrlCode;            /* Integer code for that option */
  2312   2338       } aCtrl[] = {
................................................................................
  2433   2459     if( HAS_TIMER && c=='t' && n>=5 && strncmp(azArg[0], "timer", n)==0
  2434   2460      && nArg==2
  2435   2461     ){
  2436   2462       enableTimer = booleanValue(azArg[1]);
  2437   2463     }else
  2438   2464     
  2439   2465     if( c=='t' && strncmp(azArg[0], "trace", n)==0 && nArg>1 ){
         2466  +    open_db(p);
  2440   2467       output_file_close(p->traceOut);
  2441   2468       p->traceOut = output_file_open(azArg[1]);
  2442   2469   #ifndef SQLITE_OMIT_TRACE
  2443   2470       if( p->traceOut==0 ){
  2444   2471         sqlite3_trace(p->db, 0, 0);
  2445   2472       }else{
  2446   2473         sqlite3_trace(p->db, sql_trace_callback, p->traceOut);
................................................................................
  2568   2595     int startline = 0;
  2569   2596   
  2570   2597     while( errCnt==0 || !bail_on_error || (in==0 && stdin_is_interactive) ){
  2571   2598       fflush(p->out);
  2572   2599       free(zLine);
  2573   2600       zLine = one_input_line(zSql, in);
  2574   2601       if( zLine==0 ){
  2575         -      break;  /* We have reached EOF */
         2602  +      /* End of input */
         2603  +      if( stdin_is_interactive ) printf("\n");
         2604  +      break;
  2576   2605       }
  2577   2606       if( seenInterrupt ){
  2578   2607         if( in!=0 ) break;
  2579   2608         seenInterrupt = 0;
  2580   2609       }
  2581   2610       lineno++;
  2582   2611       if( (zSql==0 || zSql[0]==0) && _all_whitespace(zLine) ) continue;

Added src/test_spellfix.c.

            1  +/*
            2  +** 2012 April 10
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +**
           13  +** This module implements a VIRTUAL TABLE that can be used to search
           14  +** a large vocabulary for close matches.  For example, this virtual
           15  +** table can be used to suggest corrections to misspelled words.  Or,
           16  +** it could be used with FTS4 to do full-text search using potentially
           17  +** misspelled words.
           18  +**
           19  +** Create an instance of the virtual table this way:
           20  +**
           21  +**    CREATE VIRTUAL TABLE demo USING spellfix1;
           22  +**
           23  +** The "spellfix1" term is the name of this module.  The "demo" is the
           24  +** name of the virtual table you will be creating.  The table is initially
           25  +** empty.  You have to populate it with your vocabulary.  Suppose you
           26  +** have a list of words in a table named "big_vocabulary".  Then do this:
           27  +**
           28  +**    INSERT INTO demo(word) SELECT word FROM big_vocabulary;
           29  +**
           30  +** If you intend to use this virtual table in cooperation with an FTS4
           31  +** table (for spelling correctly of search terms) then you can extract
           32  +** the vocabulary using an fts3aux table:
           33  +**
           34  +**    INSERT INTO demo(word) SELECT term FROM search_aux WHERE col='*';
           35  +**
           36  +** You can also provide the virtual table with a "rank" for each word.
           37  +** The "rank" is an estimate of how common the word is.  Larger numbers
           38  +** mean the word is more common.  If you omit the rank when populating
           39  +** the table, then a rank of 1 is assumed.  But if you have rank 
           40  +** information, you can supply it and the virtual table will show a
           41  +** slight preference for selecting more commonly used terms.  To
           42  +** populate the rank from an fts4aux table "search_aux" do something
           43  +** like this:
           44  +**
           45  +**    INSERT INTO demo(word,rank)
           46  +**        SELECT term, documents FROM search_aux WHERE col='*';
           47  +**
           48  +** To query the virtual table, include a MATCH operator in the WHERE
           49  +** clause.  For example:
           50  +**
           51  +**    SELECT word FROM demo WHERE word MATCH 'kennasaw';
           52  +**
           53  +** Using a dataset of American place names (derived from
           54  +** http://geonames.usgs.gov/domestic/download_data.htm) the query above
           55  +** returns 20 results beginning with:
           56  +**
           57  +**    kennesaw
           58  +**    kenosha
           59  +**    kenesaw
           60  +**    kenaga
           61  +**    keanak
           62  +**
           63  +** If you append the character '*' to the end of the pattern, then
           64  +** a prefix search is performed.  For example:
           65  +**
           66  +**    SELECT word FROM demo WHERE word MATCH 'kennes*';
           67  +**
           68  +** Yields 20 results beginning with:
           69  +**
           70  +**    kennesaw
           71  +**    kennestone
           72  +**    kenneson
           73  +**    kenneys
           74  +**    keanes
           75  +**    keenes
           76  +**
           77  +** The virtual table actually has a unique rowid with five columns plus three
           78  +** extra hidden columns.  The columns are as follows:
           79  +**
           80  +**    rowid         A unique integer number associated with each
           81  +**                  vocabulary item in the table.  This can be used
           82  +**                  as a foreign key on other tables in the database.
           83  +**
           84  +**    word          The text of the word that matches the pattern.
           85  +**                  Both word and pattern can contains unicode characters
           86  +**                  and can be mixed case.
           87  +**
           88  +**    rank          This is the rank of the word, as specified in the
           89  +**                  original INSERT statement.
           90  +**
           91  +**    distance      This is an edit distance or Levensthein distance going
           92  +**                  from the pattern to the word.
           93  +**
           94  +**    langid        This is the language-id of the word.  All queries are
           95  +**                  against a single language-id, which defaults to 0.
           96  +**                  For any given query this value is the same on all rows.
           97  +**
           98  +**    score         The score is a combination of rank and distance.  The
           99  +**                  idea is that a lower score is better.  The virtual table
          100  +**                  attempts to find words with the lowest score and 
          101  +**                  by default (unless overridden by ORDER BY) returns
          102  +**                  results in order of increasing score.
          103  +**
          104  +**    top           (HIDDEN)  For any query, this value is the same on all
          105  +**                  rows.  It is an integer which is the maximum number of
          106  +**                  rows that will be output.  The actually number of rows
          107  +**                  output might be less than this number, but it will never
          108  +**                  be greater.  The default value for top is 20, but that
          109  +**                  can be changed for each query by including a term of
          110  +**                  the form "top=N" in the WHERE clause of the query.
          111  +**
          112  +**    scope         (HIDDEN)  For any query, this value is the same on all
          113  +**                  rows.  The scope is a measure of how widely the virtual
          114  +**                  table looks for matching words.  Smaller values of
          115  +**                  scope cause a broader search.  The scope is normally
          116  +**                  choosen automatically and is capped at 4.  Applications
          117  +**                  can change the scope by including a term of the form
          118  +**                  "scope=N" in the WHERE clause of the query.  Increasing
          119  +**                  the scope will make the query run faster, but will reduce
          120  +**                  the possible corrections.
          121  +**
          122  +**    srchcnt       (HIDDEN)  For any query, this value is the same on all
          123  +**                  rows.  This value is an integer which is the number of
          124  +**                  of words examined using the edit-distance algorithm to
          125  +**                  find the top matches that are ultimately displayed.  This
          126  +**                  value is for diagnostic use only.
          127  +**
          128  +**    soundslike    (HIDDEN)  When inserting vocabulary entries, this field
          129  +**                  can be set to an spelling that matches what the word
          130  +**                  sounds like.  See the DEALING WITH UNUSUAL AND DIFFICULT
          131  +**                  SPELLINGS section below for details.
          132  +**
          133  +** When inserting into or updating the virtual table, only the rowid, word,
          134  +** rank, and langid may be changes.  Any attempt to set or modify the values
          135  +** of distance, score, top, scope, or srchcnt is silently ignored.
          136  +**
          137  +** ALGORITHM
          138  +**
          139  +** A shadow table named "%_vocab" (where the % is replaced by the name of
          140  +** the virtual table; Ex: "demo_vocab" for the "demo" virtual table) is
          141  +** constructed with these columns:
          142  +**
          143  +**    id            The unique id (INTEGER PRIMARY KEY)
          144  +**
          145  +**    rank          The rank of word.
          146  +**
          147  +**    langid        The language id for this entry.
          148  +**
          149  +**    word          The original UTF8 text of the vocabulary word
          150  +**
          151  +**    k1            The word transliterated into lower-case ASCII.  
          152  +**                  There is a standard table of mappings from non-ASCII
          153  +**                  characters into ASCII.  Examples: "æ" -> "ae",
          154  +**                  "þ" -> "th", "ß" -> "ss", "á" -> "a", ...  The
          155  +**                  accessory function spellfix1_translit(X) will do
          156  +**                  the non-ASCII to ASCII mapping.  The built-in lower(X)
          157  +**                  function will convert to lower-case.  Thus:
          158  +**                  k1 = lower(spellfix1_translit(word)).
          159  +**
          160  +**    k2            This field holds a phonetic code derived from k1.  Letters
          161  +**                  that have similar sounds are mapped into the same symbol.
          162  +**                  For example, all vowels and vowel clusters become the
          163  +**                  single symbol "A".  And the letters "p", "b", "f", and
          164  +**                  "v" all become "B".  All nasal sounds are represented
          165  +**                  as "N".  And so forth.  The mapping is base on
          166  +**                  ideas found in Soundex, Metaphone, and other
          167  +**                  long-standing phonetic matching systems.  This key can
          168  +**                  be generated by the function spellfix1_charclass(X).  
          169  +**                  Hence: k2 = spellfix1_charclass(k1)
          170  +**
          171  +** There is also a function for computing the Wagner edit distance or the
          172  +** Levenshtein distance between a pattern and a word.  This function
          173  +** is exposed as spellfix1_editdist(X,Y).  The edit distance function
          174  +** returns the "cost" of converting X into Y.  Some transformations
          175  +** cost more than others.  Changing one vowel into a different vowel,
          176  +** for example is relatively cheap, as is doubling a constant, or
          177  +** omitting the second character of a double-constant.  Other transformations
          178  +** or more expensive.  The idea is that the edit distance function returns
          179  +** a low cost of words that are similar and a higher cost for words
          180  +** that are futher apart.  In this implementation, the maximum cost
          181  +** of any single-character edit (delete, insert, or substitute) is 100,
          182  +** with lower costs for some edits (such as transforming vowels).
          183  +**
          184  +** The "score" for a comparison is the edit distance between the pattern
          185  +** and the word, adjusted down by the base-2 logorithm of the word rank.
          186  +** For example, a match with distance 100 but rank 1000 would have a
          187  +** score of 122 (= 100 - log2(1000) + 32) where as a match with distance
          188  +** 100 with a rank of 1 would have a score of 131 (100 - log2(1) + 32).
          189  +** (NB:  The constant 32 is added to each score to keep it from going
          190  +** negative in case the edit distance is zero.)  In this way, frequently
          191  +** used words get a slightly lower cost which tends to move them toward
          192  +** the top of the list of alternative spellings.
          193  +**
          194  +** A straightforward implementation of a spelling corrector would be
          195  +** to compare the search term against every word in the vocabulary
          196  +** and select the 20 with the lowest scores.  However, there will 
          197  +** typically be hundreds of thousands or millions of words in the
          198  +** vocabulary, and so this approach is not fast enough.
          199  +**
          200  +** Suppose the term that is being spell-corrected is X.  To limit
          201  +** the search space, X is converted to a k2-like key using the
          202  +** equivalent of:
          203  +**
          204  +**    key = spellfix1_charclass(lower(spellfix1_translit(X)))
          205  +**
          206  +** This key is then limited to "scope" characters.  The default scope
          207  +** value is 4, but an alternative scope can be specified using the
          208  +** "scope=N" term in the WHERE clause.  After the key has been truncated,
          209  +** the edit distance is run against every term in the vocabulary that
          210  +** has a k2 value that begins with the abbreviated key.
          211  +**
          212  +** For example, suppose the input word is "Paskagula".  The phonetic 
          213  +** key is "BACACALA" which is then truncated to 4 characters "BACA".
          214  +** The edit distance is then run on the 4980 entries (out of
          215  +** 272,597 entries total) of the vocabulary whose k2 values begin with
          216  +** BACA, yielding "Pascagoula" as the best match.
          217  +** 
          218  +** Only terms of the vocabulary with a matching langid are searched.
          219  +** Hence, the same table can contain entries from multiple languages
          220  +** and only the requested language will be used.  The default langid
          221  +** is 0.
          222  +**
          223  +** DEALING WITH UNUSUAL AND DIFFICULT SPELLINGS
          224  +**
          225  +** The algorithm above works quite well for most cases, but there are
          226  +** exceptions.  These exceptions can be dealt with by making additional
          227  +** entries in the virtual table using the "soundslike" column.
          228  +**
          229  +** For example, many words of Greek origin begin with letters "ps" where
          230  +** the "p" is silent.  Ex:  psalm, pseudonym, psoriasis, psyche.  In
          231  +** another example, many Scottish surnames can be spelled with an
          232  +** initial "Mac" or "Mc".  Thus, "MacKay" and "McKay" are both pronounced
          233  +** the same.
          234  +**
          235  +** Accommodation can be made for words that are not spelled as they
          236  +** sound by making additional entries into the virtual table for the
          237  +** same word, but adding an alternative spelling in the "soundslike"
          238  +** column.  For example, the canonical entry for "psalm" would be this:
          239  +**
          240  +**   INSERT INTO demo(word) VALUES('psalm');
          241  +**
          242  +** To enhance the ability to correct the spelling of "salm" into
          243  +** "psalm", make an addition entry like this:
          244  +**
          245  +**   INSERT INTO demo(word,soundslike) VALUES('psalm','salm');
          246  +**
          247  +** It is ok to make multiple entries for the same word as long as
          248  +** each entry has a different soundslike value.  Note that if no
          249  +** soundslike value is specified, the soundslike defaults to the word
          250  +** itself.
          251  +**
          252  +** Listed below are some cases where it might make sense to add additional
          253  +** soundslike entries.  The specific entries will depend on the application
          254  +** and the target language.
          255  +**
          256  +**   *   Silent "p" in words beginning with "ps":  psalm, psyche
          257  +**
          258  +**   *   Silent "p" in words beginning with "pn":  pneumonia, pneumatic
          259  +**
          260  +**   *   Silent "p" in words beginning with "pt":  pterodactyl, ptolemaic
          261  +**
          262  +**   *   Silent "d" in words beginning with "dj":  djinn, Djikarta
          263  +**
          264  +**   *   Silent "k" in words beginning with "kn":  knight, Knuthson
          265  +**
          266  +**   *   Silent "g" in words beginning with "gn":  gnarly, gnome, gnat
          267  +**
          268  +**   *   "Mac" versus "Mc" beginning Scottish surnames
          269  +**
          270  +**   *   "Tch" sounds in Slavic words:  Tchaikovsky vs. Chaykovsky
          271  +**
          272  +**   *   The letter "j" pronounced like "h" in Spanish:  LaJolla
          273  +**
          274  +**   *   Words beginning with "wr" versus "r":  write vs. rite
          275  +**
          276  +**   *   Miscellanous problem words such as "debt", "tsetse",
          277  +**       "Nguyen", "Van Nuyes".
          278  +*/
          279  +#if SQLITE_CORE
          280  +# include "sqliteInt.h"
          281  +#else
          282  +# include <string.h>
          283  +# include <stdio.h>
          284  +# include <stdlib.h>
          285  +# include "sqlite3ext.h"
          286  +  SQLITE_EXTENSION_INIT1
          287  +#endif /* !SQLITE_CORE */
          288  +
          289  +/*
          290  +** Character classes for ASCII characters:
          291  +**
          292  +**   0   ''        Silent letters:   H W
          293  +**   1   'A'       Any vowel:   A E I O U (Y)
          294  +**   2   'B'       A bilabeal stop or fricative:  B F P V
          295  +**   3   'C'       Other fricatives or back stops:  C G J K Q S X Z
          296  +**   4   'D'       Alveolar stops:  D T
          297  +**   5   'H'       Letter H at the beginning of a word
          298  +**   6   'L'       Glides:  L R
          299  +**   7   'M'       Nasals:  M N
          300  +**   8   'W'       Letter W at the beginning of a word
          301  +**   9   'Y'       Letter Y at the beginning of a word.
          302  +**   10  '9'       A digit: 0 1 2 3 4 5 6 7 8 9
          303  +**   11  ' '       White space
          304  +**   12  '?'       Other.
          305  +*/
          306  +#define CCLASS_SILENT         0
          307  +#define CCLASS_VOWEL          1
          308  +#define CCLASS_B              2
          309  +#define CCLASS_C              3
          310  +#define CCLASS_D              4
          311  +#define CCLASS_H              5
          312  +#define CCLASS_L              6
          313  +#define CCLASS_M              7
          314  +#define CCLASS_W              8
          315  +#define CCLASS_Y              9
          316  +#define CCLASS_DIGIT         10
          317  +#define CCLASS_SPACE         11
          318  +#define CCLASS_OTHER         12
          319  +
          320  +/*
          321  +** The following table gives the character class for non-initial ASCII
          322  +** characters.
          323  +*/
          324  +static const unsigned char midClass[] = {
          325  +          /* x0  x1  x2  x3  x4  x5  x6  x7    x8  x9  xa  xb  xc  xd  xe  xf */
          326  +  /* 0x */   12, 12, 12, 12, 12, 12, 12, 12,   12, 11, 11, 12, 11, 12, 12, 12,
          327  +  /* 1x */   12, 12, 12, 12, 12, 12, 12, 12,   12, 12, 12, 12, 12, 12, 12, 12,
          328  +  /* 2x */   11, 12, 12, 12, 12, 12, 12, 12,   12, 12, 12, 12, 12, 12, 12, 12,
          329  +  /* 3x */   10, 10, 10, 10, 10, 10, 10, 10,   10, 10, 12, 12, 12, 12, 12, 12,
          330  +  /* 4x */   12,  1,  2,  3,  4,  1,  2,  3,    0,  1,  3,  3,  6,  7,  7,  1,
          331  +  /* 5x */    2,  3,  6,  3,  4,  1,  2,  0,    3,  1,  3, 12, 12, 12, 12, 12,
          332  +  /* 6x */   12,  1,  2,  3,  4,  1,  2,  3,    0,  1,  3,  3,  6,  7,  7,  1,
          333  +  /* 7x */    2,  3,  6,  3,  4,  1,  2,  0,    3,  1,  3, 12, 12, 12, 12, 12,
          334  +};
          335  +
          336  +/* 
          337  +** This tables gives the character class for ASCII characters that form the
          338  +** initial character of a word.  The only difference from midClass is with
          339  +** the letters H, W, and Y.
          340  +*/
          341  +static const unsigned char initClass[] = {
          342  +          /* x0  x1  x2  x3  x4  x5  x6  x7    x8  x9  xa  xb  xc  xd  xe  xf */
          343  +  /* 0x */   12, 12, 12, 12, 12, 12, 12, 12,   12, 11, 11, 12, 11, 12, 12, 12,
          344  +  /* 1x */   12, 12, 12, 12, 12, 12, 12, 12,   12, 12, 12, 12, 12, 12, 12, 12,
          345  +  /* 2x */   11, 12, 12, 12, 12, 12, 12, 12,   12, 12, 12, 12, 12, 12, 12, 12,
          346  +  /* 3x */   10, 10, 10, 10, 10, 10, 10, 10,   10, 10, 12, 12, 12, 12, 12, 12,
          347  +  /* 4x */   12,  1,  2,  3,  4,  1,  2,  3,    5,  1,  3,  3,  6,  7,  7,  1,
          348  +  /* 5x */    2,  3,  6,  3,  4,  1,  2,  8,    3,  9,  3, 12, 12, 12, 12, 12,
          349  +  /* 6x */   12,  1,  2,  3,  4,  1,  2,  3,    5,  1,  3,  3,  6,  7,  7,  1,
          350  +  /* 7x */    2,  3,  6,  3,  4,  1,  2,  8,    3,  9,  3, 12, 12, 12, 12, 12,
          351  +};
          352  +
          353  +/*
          354  +** Mapping from the character class number (0-12) to a symbol for each
          355  +** character class.  Note that initClass[] can be used to map the class
          356  +** symbol back into the class number.
          357  +*/
          358  +static const unsigned char className[] = ".ABCDHLMWY9 ?";
          359  +
          360  +/*
          361  +** Generate a string of character classes corresponding to the
          362  +** ASCII characters in the input string zIn.  If the input is not
          363  +** ASCII then the behavior is undefined.
          364  +**
          365  +** Space to hold the result is obtained from sqlite3_malloc()
          366  +**
          367  +** Return NULL if memory allocation fails.  
          368  +*/
          369  +static unsigned char *characterClassString(const unsigned char *zIn, int nIn){
          370  +  unsigned char *zOut = sqlite3_malloc( nIn + 1 );
          371  +  int i;
          372  +  int nOut = 0;
          373  +  char cPrev = 0x77;
          374  +  const unsigned char *aClass = initClass;
          375  +
          376  +  if( zOut==0 ) return 0;
          377  +  for(i=0; i<nIn; i++){
          378  +    unsigned char c = zIn[i];
          379  +    c = aClass[c&0x7f];
          380  +    if( c==CCLASS_OTHER && cPrev!=CCLASS_DIGIT ) continue;
          381  +    cPrev = c;
          382  +    if( c==CCLASS_SILENT ) continue;
          383  +    if( c==CCLASS_SPACE ) continue;
          384  +    aClass = midClass;
          385  +    c = className[c];
          386  +    if( c!=zOut[nOut-1] ) zOut[nOut++] = c;
          387  +  }
          388  +  zOut[nOut] = 0;
          389  +  return zOut;
          390  +}
          391  +
          392  +/*
          393  +** This is an SQL function wrapper around characterClassString().  See
          394  +** the description of characterClassString() for additional information.
          395  +*/
          396  +static void characterClassSqlFunc(
          397  +  sqlite3_context *context,
          398  +  int argc,
          399  +  sqlite3_value **argv
          400  +){
          401  +  const unsigned char *zIn;
          402  +  unsigned char *zOut;
          403  +
          404  +  zIn = sqlite3_value_text(argv[0]);
          405  +  if( zIn==0 ) return;
          406  +  zOut = characterClassString(zIn, sqlite3_value_bytes(argv[0]));
          407  +  if( zOut==0 ){
          408  +    sqlite3_result_error_nomem(context);
          409  +  }else{
          410  +    sqlite3_result_text(context, (char*)zOut, -1, sqlite3_free);
          411  +  }
          412  +}
          413  +
          414  +/*
          415  +** Return the character class number for a character given its
          416  +** context.
          417  +*/
          418  +static char characterClass(char cPrev, char c){
          419  +  return cPrev==0 ? initClass[c&0x7f] : midClass[c&0x7f];
          420  +}
          421  +
          422  +/*
          423  +** Return the cost of inserting or deleting character c immediately
          424  +** following character cPrev.  If cPrev==0, that means c is the first
          425  +** character of the word.
          426  +*/
          427  +static int insertOrDeleteCost(char cPrev, char c){
          428  +  char classC = characterClass(cPrev, c);
          429  +  char classCprev;
          430  +
          431  +  if( classC==CCLASS_SILENT ){
          432  +    /* Insert or delete "silent" characters such as H or W */
          433  +    return 1;
          434  +  }
          435  +  if( cPrev==c ){
          436  +    /* Repeated characters, or miss a repeat */
          437  +    return 10;
          438  +  }
          439  +  classCprev = characterClass(cPrev, cPrev);
          440  +  if( classC==classCprev ){
          441  +    if( classC==CCLASS_VOWEL ){
          442  +      /* Remove or add a new vowel to a vowel cluster */
          443  +      return 15;
          444  +    }else{
          445  +      /* Remove or add a consonant not in the same class */
          446  +      return 50;
          447  +    }
          448  +  }
          449  +
          450  +  /* any other character insertion or deletion */
          451  +  return 100;
          452  +}
          453  +
          454  +/*
          455  +** Divide the insertion cost by this factor when appending to the
          456  +** end of the word.
          457  +*/
          458  +#define FINAL_INS_COST_DIV  4
          459  +
          460  +/*
          461  +** Return the cost of substituting cTo in place of cFrom assuming
          462  +** the previous character is cPrev.  If cPrev==0 then cTo is the first
          463  +** character of the word.
          464  +*/
          465  +static int substituteCost(char cPrev, char cFrom, char cTo){
          466  +  char classFrom, classTo;
          467  +  if( cFrom==cTo ){
          468  +    /* Exact match */
          469  +    return 0;
          470  +  }
          471  +  if( cFrom==(cTo^0x20) && ((cTo>='A' && cTo<='Z') || (cTo>='a' && cTo<='z')) ){
          472  +    /* differ only in case */
          473  +    return 0;
          474  +  }
          475  +  classFrom = characterClass(cPrev, cFrom);
          476  +  classTo = characterClass(cPrev, cTo);
          477  +  if( classFrom==classTo ){
          478  +    /* Same character class */
          479  +    return classFrom=='A' ? 25 : 40;
          480  +  }
          481  +  if( classFrom>=CCLASS_B && classFrom<=CCLASS_Y
          482  +      && classTo>=CCLASS_B && classTo<=CCLASS_Y ){
          483  +    /* Convert from one consonant to another, but in a different class */
          484  +    return 75;
          485  +  }
          486  +  /* Any other subsitution */
          487  +  return 100;
          488  +}
          489  +
          490  +/*
          491  +** Given two strings zA and zB which are pure ASCII, return the cost
          492  +** of transforming zA into zB.  If zA ends with '*' assume that it is
          493  +** a prefix of zB and give only minimal penalty for extra characters
          494  +** on the end of zB.
          495  +**
          496  +** Smaller numbers mean a closer match.
          497  +**
          498  +** Negative values indicate an error:
          499  +**    -1  One of the inputs is NULL
          500  +**    -2  Non-ASCII characters on input
          501  +**    -3  Unable to allocate memory 
          502  +*/
          503  +static int editdist(const char *zA, const char *zB){
          504  +  int nA, nB;            /* Number of characters in zA[] and zB[] */
          505  +  int xA, xB;            /* Loop counters for zA[] and zB[] */
          506  +  char cA, cB;           /* Current character of zA and zB */
          507  +  char cAprev, cBprev;   /* Previous character of zA and zB */
          508  +  int d;                 /* North-west cost value */
          509  +  int dc = 0;            /* North-west character value */
          510  +  int res;               /* Final result */
          511  +  int *m;                /* The cost matrix */
          512  +  char *cx;              /* Corresponding character values */
          513  +  int *toFree = 0;       /* Malloced space */
          514  +  int mStack[60+15];     /* Stack space to use if not too much is needed */
          515  +
          516  +  /* Early out if either input is NULL */
          517  +  if( zA==0 || zB==0 ) return -1;
          518  +
          519  +  /* Skip any common prefix */
          520  +  while( zA[0] && zA[0]==zB[0] ){ dc = zA[0]; zA++; zB++; }
          521  +  if( zA[0]==0 && zB[0]==0 ) return 0;
          522  +
          523  +#if 0
          524  +  printf("A=\"%s\" B=\"%s\" dc=%c\n", zA, zB, dc?dc:' ');
          525  +#endif
          526  +
          527  +  /* Verify input strings and measure their lengths */
          528  +  for(nA=0; zA[nA]; nA++){
          529  +    if( zA[nA]>127 ) return -2;
          530  +  }
          531  +  for(nB=0; zB[nB]; nB++){
          532  +    if( zB[nB]>127 ) return -2;
          533  +  }
          534  +
          535  +  /* Special processing if either string is empty */
          536  +  if( nA==0 ){
          537  +    cBprev = dc;
          538  +    for(xB=res=0; (cB = zB[xB])!=0; xB++){
          539  +      res += insertOrDeleteCost(cBprev, cB)/FINAL_INS_COST_DIV;
          540  +      cBprev = cB;
          541  +    }
          542  +    return res;
          543  +  }
          544  +  if( nB==0 ){
          545  +    cAprev = dc;
          546  +    for(xA=res=0; (cA = zA[xA])!=0; xA++){
          547  +      res += insertOrDeleteCost(cAprev, cA);
          548  +      cAprev = cA;
          549  +    }
          550  +    return res;
          551  +  }
          552  +
          553  +  /* A is a prefix of B */
          554  +  if( zA[0]=='*' && zA[1]==0 ) return 0;
          555  +
          556  +  /* Allocate and initialize the Wagner matrix */
          557  +  if( nB<(sizeof(mStack)*4)/(sizeof(mStack[0])*5) ){
          558  +    m = mStack;
          559  +  }else{
          560  +    m = toFree = sqlite3_malloc( (nB+1)*5*sizeof(m[0])/4 );
          561  +    if( m==0 ) return -3;
          562  +  }
          563  +  cx = (char*)&m[nB+1];
          564  +
          565  +  /* Compute the Wagner edit distance */
          566  +  m[0] = 0;
          567  +  cx[0] = dc;
          568  +  cBprev = dc;
          569  +  for(xB=1; xB<=nB; xB++){
          570  +    cB = zB[xB-1];
          571  +    cx[xB] = cB;
          572  +    m[xB] = m[xB-1] + insertOrDeleteCost(cBprev, cB);
          573  +    cBprev = cB;
          574  +  }
          575  +  cAprev = dc;
          576  +  for(xA=1; xA<=nA; xA++){
          577  +    int lastA = (xA==nA);
          578  +    cA = zA[xA-1];
          579  +    if( cA=='*' && lastA ) break;
          580  +    d = m[0];
          581  +    dc = cx[0];
          582  +    m[0] = d + insertOrDeleteCost(cAprev, cA);
          583  +    cBprev = 0;
          584  +    for(xB=1; xB<=nB; xB++){
          585  +      int totalCost, insCost, delCost, subCost, ncx;
          586  +      cB = zB[xB-1];
          587  +
          588  +      /* Cost to insert cB */
          589  +      insCost = insertOrDeleteCost(cx[xB-1], cB);
          590  +      if( lastA ) insCost /= FINAL_INS_COST_DIV;
          591  +
          592  +      /* Cost to delete cA */
          593  +      delCost = insertOrDeleteCost(cx[xB], cA);
          594  +
          595  +      /* Cost to substitute cA->cB */
          596  +      subCost = substituteCost(cx[xB-1], cA, cB);
          597  +
          598  +      /* Best cost */
          599  +      totalCost = insCost + m[xB-1];
          600  +      ncx = cB;
          601  +      if( (delCost + m[xB])<totalCost ){
          602  +        totalCost = delCost + m[xB];
          603  +        ncx = cA;
          604  +      }
          605  +      if( (subCost + d)<totalCost ){
          606  +        totalCost = subCost + d;
          607  +      }
          608  +
          609  +#if 0
          610  +      printf("%d,%d d=%4d u=%4d r=%4d dc=%c cA=%c cB=%c"
          611  +             " ins=%4d del=%4d sub=%4d t=%4d ncx=%c\n",
          612  +             xA, xB, d, m[xB], m[xB-1], dc?dc:' ', cA, cB,
          613  +             insCost, delCost, subCost, totalCost, ncx?ncx:' ');
          614  +#endif
          615  +
          616  +      /* Update the matrix */
          617  +      d = m[xB];
          618  +      dc = cx[xB];
          619  +      m[xB] = totalCost;
          620  +      cx[xB] = ncx;
          621  +      cBprev = cB;
          622  +    }
          623  +    cAprev = cA;
          624  +  }
          625  +
          626  +  /* Free the wagner matrix and return the result */
          627  +  if( cA=='*' && nB>nA ){
          628  +    res = m[nA];
          629  +    for(xB=nA+1; xB<=nB; xB++){
          630  +      if( m[xB]<res ) res = m[xB];
          631  +    }
          632  +  }else{
          633  +    res = m[nB];
          634  +  }
          635  +  sqlite3_free(toFree);
          636  +  return res;
          637  +}
          638  +
          639  +/*
          640  +** Function:    editdist(A,B)
          641  +**
          642  +** Return the cost of transforming string A into string B.  Both strings
          643  +** must be pure ASCII text.  If A ends with '*' then it is assumed to be
          644  +** a prefix of B and extra characters on the end of B have minimal additional
          645  +** cost.
          646  +*/
          647  +static void editdistSqlFunc(
          648  +  sqlite3_context *context,
          649  +  int argc,
          650  +  sqlite3_value **argv
          651  +){
          652  +  int res = editdist((const char*)sqlite3_value_text(argv[0]),
          653  +                    (const char*)sqlite3_value_text(argv[1]));
          654  +  if( res<0 ){
          655  +    if( res==(-3) ){
          656  +      sqlite3_result_error_nomem(context);
          657  +    }else if( res==(-2) ){
          658  +      sqlite3_result_error(context, "non-ASCII input to editdist()", -1);
          659  +    }else{
          660  +      sqlite3_result_error(context, "NULL input to editdist()", -1);
          661  +    }
          662  +  }else{ 
          663  +    sqlite3_result_int(context, res);
          664  +  }
          665  +}
          666  +
          667  +#if !SQLITE_CORE
          668  +/*
          669  +** This lookup table is used to help decode the first byte of
          670  +** a multi-byte UTF8 character.
          671  +*/
          672  +static const unsigned char sqlite3Utf8Trans1[] = {
          673  +  0x00, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07,
          674  +  0x08, 0x09, 0x0a, 0x0b, 0x0c, 0x0d, 0x0e, 0x0f,
          675  +  0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17,
          676  +  0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
          677  +  0x00, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07,
          678  +  0x08, 0x09, 0x0a, 0x0b, 0x0c, 0x0d, 0x0e, 0x0f,
          679  +  0x00, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07,
          680  +  0x00, 0x01, 0x02, 0x03, 0x00, 0x01, 0x00, 0x00,
          681  +};
          682  +#endif
          683  +
          684  +/*
          685  +** Return the value of the first UTF-8 character in the string.
          686  +*/
          687  +static int utf8Read(const unsigned char *z, int n, int *pSize){
          688  +  int c, i;
          689  +
          690  +  if( n==0 ){
          691  +    c = i = 0;
          692  +  }else{
          693  +    c = z[0];
          694  +    i = 1;
          695  +    if( c>=0xc0 ){
          696  +      c = sqlite3Utf8Trans1[c-0xc0];
          697  +      while( i<n && (z[i] & 0xc0)==0x80 ){
          698  +        c = (c<<6) + (0x3f & z[i++]);
          699  +      }
          700  +    }
          701  +  }
          702  +  *pSize = i;
          703  +  return c;
          704  +}
          705  +
          706  +/*
          707  +** Table of translations from unicode characters into ASCII.
          708  +*/
          709  +static const struct {
          710  + unsigned short int cFrom;
          711  + unsigned char cTo0, cTo1;
          712  +} translit[] = {
          713  +  { 0x00A0,  0x20, 0x00 },  /*   to   */
          714  +  { 0x00B5,  0x75, 0x00 },  /* µ to u */
          715  +  { 0x00C0,  0x41, 0x00 },  /* À to A */
          716  +  { 0x00C1,  0x41, 0x00 },  /* Á to A */
          717  +  { 0x00C2,  0x41, 0x00 },  /* Â to A */
          718  +  { 0x00C3,  0x41, 0x00 },  /* Ã to A */
          719  +  { 0x00C4,  0x41, 0x65 },  /* Ä to Ae */
          720  +  { 0x00C5,  0x41, 0x61 },  /* Å to Aa */
          721  +  { 0x00C6,  0x41, 0x45 },  /* Æ to AE */
          722  +  { 0x00C7,  0x43, 0x00 },  /* Ç to C */
          723  +  { 0x00C8,  0x45, 0x00 },  /* È to E */
          724  +  { 0x00C9,  0x45, 0x00 },  /* É to E */
          725  +  { 0x00CA,  0x45, 0x00 },  /* Ê to E */
          726  +  { 0x00CB,  0x45, 0x00 },  /* Ë to E */
          727  +  { 0x00CC,  0x49, 0x00 },  /* Ì to I */
          728  +  { 0x00CD,  0x49, 0x00 },  /* Í to I */
          729  +  { 0x00CE,  0x49, 0x00 },  /* Î to I */
          730  +  { 0x00CF,  0x49, 0x00 },  /* Ï to I */
          731  +  { 0x00D0,  0x44, 0x00 },  /* Ð to D */
          732  +  { 0x00D1,  0x4E, 0x00 },  /* Ñ to N */
          733  +  { 0x00D2,  0x4F, 0x00 },  /* Ò to O */
          734  +  { 0x00D3,  0x4F, 0x00 },  /* Ó to O */
          735  +  { 0x00D4,  0x4F, 0x00 },  /* Ô to O */
          736  +  { 0x00D5,  0x4F, 0x00 },  /* Õ to O */
          737  +  { 0x00D6,  0x4F, 0x65 },  /* Ö to Oe */
          738  +  { 0x00D7,  0x78, 0x00 },  /* × to x */
          739  +  { 0x00D8,  0x4F, 0x00 },  /* Ø to O */
          740  +  { 0x00D9,  0x55, 0x00 },  /* Ù to U */
          741  +  { 0x00DA,  0x55, 0x00 },  /* Ú to U */
          742  +  { 0x00DB,  0x55, 0x00 },  /* Û to U */
          743  +  { 0x00DC,  0x55, 0x65 },  /* Ü to Ue */
          744  +  { 0x00DD,  0x59, 0x00 },  /* Ý to Y */
          745  +  { 0x00DE,  0x54, 0x68 },  /* Þ to Th */
          746  +  { 0x00DF,  0x73, 0x73 },  /* ß to ss */
          747  +  { 0x00E0,  0x61, 0x00 },  /* à to a */
          748  +  { 0x00E1,  0x61, 0x00 },  /* á to a */
          749  +  { 0x00E2,  0x61, 0x00 },  /* â to a */
          750  +  { 0x00E3,  0x61, 0x00 },  /* ã to a */
          751  +  { 0x00E4,  0x61, 0x65 },  /* ä to ae */
          752  +  { 0x00E5,  0x61, 0x61 },  /* å to aa */
          753  +  { 0x00E6,  0x61, 0x65 },  /* æ to ae */
          754  +  { 0x00E7,  0x63, 0x00 },  /* ç to c */
          755  +  { 0x00E8,  0x65, 0x00 },  /* è to e */
          756  +  { 0x00E9,  0x65, 0x00 },  /* é to e */
          757  +  { 0x00EA,  0x65, 0x00 },  /* ê to e */
          758  +  { 0x00EB,  0x65, 0x00 },  /* ë to e */
          759  +  { 0x00EC,  0x69, 0x00 },  /* ì to i */
          760  +  { 0x00ED,  0x69, 0x00 },  /* í to i */
          761  +  { 0x00EE,  0x69, 0x00 },  /* î to i */
          762  +  { 0x00EF,  0x69, 0x00 },  /* ï to i */
          763  +  { 0x00F0,  0x64, 0x00 },  /* ð to d */
          764  +  { 0x00F1,  0x6E, 0x00 },  /* ñ to n */
          765  +  { 0x00F2,  0x6F, 0x00 },  /* ò to o */
          766  +  { 0x00F3,  0x6F, 0x00 },  /* ó to o */
          767  +  { 0x00F4,  0x6F, 0x00 },  /* ô to o */
          768  +  { 0x00F5,  0x6F, 0x00 },  /* õ to o */
          769  +  { 0x00F6,  0x6F, 0x65 },  /* ö to oe */
          770  +  { 0x00F7,  0x3A, 0x00 },  /* ÷ to : */
          771  +  { 0x00F8,  0x6F, 0x00 },  /* ø to o */
          772  +  { 0x00F9,  0x75, 0x00 },  /* ù to u */
          773  +  { 0x00FA,  0x75, 0x00 },  /* ú to u */
          774  +  { 0x00FB,  0x75, 0x00 },  /* û to u */
          775  +  { 0x00FC,  0x75, 0x65 },  /* ü to ue */
          776  +  { 0x00FD,  0x79, 0x00 },  /* ý to y */
          777  +  { 0x00FE,  0x74, 0x68 },  /* þ to th */
          778  +  { 0x00FF,  0x79, 0x00 },  /* ÿ to y */
          779  +  { 0x0100,  0x41, 0x00 },  /* Ā to A */
          780  +  { 0x0101,  0x61, 0x00 },  /* ā to a */
          781  +  { 0x0102,  0x41, 0x00 },  /* Ă to A */
          782  +  { 0x0103,  0x61, 0x00 },  /* ă to a */
          783  +  { 0x0104,  0x41, 0x00 },  /* Ą to A */
          784  +  { 0x0105,  0x61, 0x00 },  /* ą to a */
          785  +  { 0x0106,  0x43, 0x00 },  /* Ć to C */
          786  +  { 0x0107,  0x63, 0x00 },  /* ć to c */
          787  +  { 0x0108,  0x43, 0x68 },  /* Ĉ to Ch */
          788  +  { 0x0109,  0x63, 0x68 },  /* ĉ to ch */
          789  +  { 0x010A,  0x43, 0x00 },  /* Ċ to C */
          790  +  { 0x010B,  0x63, 0x00 },  /* ċ to c */
          791  +  { 0x010C,  0x43, 0x00 },  /* Č to C */
          792  +  { 0x010D,  0x63, 0x00 },  /* č to c */
          793  +  { 0x010E,  0x44, 0x00 },  /* Ď to D */
          794  +  { 0x010F,  0x64, 0x00 },  /* ď to d */
          795  +  { 0x0110,  0x44, 0x00 },  /* Đ to D */
          796  +  { 0x0111,  0x64, 0x00 },  /* đ to d */
          797  +  { 0x0112,  0x45, 0x00 },  /* Ē to E */
          798  +  { 0x0113,  0x65, 0x00 },  /* ē to e */
          799  +  { 0x0114,  0x45, 0x00 },  /* Ĕ to E */
          800  +  { 0x0115,  0x65, 0x00 },  /* ĕ to e */
          801  +  { 0x0116,  0x45, 0x00 },  /* Ė to E */
          802  +  { 0x0117,  0x65, 0x00 },  /* ė to e */
          803  +  { 0x0118,  0x45, 0x00 },  /* Ę to E */
          804  +  { 0x0119,  0x65, 0x00 },  /* ę to e */
          805  +  { 0x011A,  0x45, 0x00 },  /* Ě to E */
          806  +  { 0x011B,  0x65, 0x00 },  /* ě to e */
          807  +  { 0x011C,  0x47, 0x68 },  /* Ĝ to Gh */
          808  +  { 0x011D,  0x67, 0x68 },  /* ĝ to gh */
          809  +  { 0x011E,  0x47, 0x00 },  /* Ğ to G */
          810  +  { 0x011F,  0x67, 0x00 },  /* ğ to g */
          811  +  { 0x0120,  0x47, 0x00 },  /* Ġ to G */
          812  +  { 0x0121,  0x67, 0x00 },  /* ġ to g */
          813  +  { 0x0122,  0x47, 0x00 },  /* Ģ to G */
          814  +  { 0x0123,  0x67, 0x00 },  /* ģ to g */
          815  +  { 0x0124,  0x48, 0x68 },  /* Ĥ to Hh */
          816  +  { 0x0125,  0x68, 0x68 },  /* ĥ to hh */
          817  +  { 0x0126,  0x48, 0x00 },  /* Ħ to H */
          818  +  { 0x0127,  0x68, 0x00 },  /* ħ to h */
          819  +  { 0x0128,  0x49, 0x00 },  /* Ĩ to I */
          820  +  { 0x0129,  0x69, 0x00 },  /* ĩ to i */
          821  +  { 0x012A,  0x49, 0x00 },  /* Ī to I */
          822  +  { 0x012B,  0x69, 0x00 },  /* ī to i */
          823  +  { 0x012C,  0x49, 0x00 },  /* Ĭ to I */
          824  +  { 0x012D,  0x69, 0x00 },  /* ĭ to i */
          825  +  { 0x012E,  0x49, 0x00 },  /* Į to I */
          826  +  { 0x012F,  0x69, 0x00 },  /* į to i */
          827  +  { 0x0130,  0x49, 0x00 },  /* İ to I */
          828  +  { 0x0131,  0x69, 0x00 },  /* ı to i */
          829  +  { 0x0132,  0x49, 0x4A },  /* IJ to IJ */
          830  +  { 0x0133,  0x69, 0x6A },  /* ij to ij */
          831  +  { 0x0134,  0x4A, 0x68 },  /* Ĵ to Jh */
          832  +  { 0x0135,  0x6A, 0x68 },  /* ĵ to jh */
          833  +  { 0x0136,  0x4B, 0x00 },  /* Ķ to K */
          834  +  { 0x0137,  0x6B, 0x00 },  /* ķ to k */
          835  +  { 0x0138,  0x6B, 0x00 },  /* ĸ to k */
          836  +  { 0x0139,  0x4C, 0x00 },  /* Ĺ to L */
          837  +  { 0x013A,  0x6C, 0x00 },  /* ĺ to l */
          838  +  { 0x013B,  0x4C, 0x00 },  /* Ļ to L */
          839  +  { 0x013C,  0x6C, 0x00 },  /* ļ to l */
          840  +  { 0x013D,  0x4C, 0x00 },  /* Ľ to L */
          841  +  { 0x013E,  0x6C, 0x00 },  /* ľ to l */
          842  +  { 0x013F,  0x4C, 0x2E },  /* Ŀ to L. */
          843  +  { 0x0140,  0x6C, 0x2E },  /* ŀ to l. */
          844  +  { 0x0141,  0x4C, 0x00 },  /* Ł to L */
          845  +  { 0x0142,  0x6C, 0x00 },  /* ł to l */
          846  +  { 0x0143,  0x4E, 0x00 },  /* Ń to N */
          847  +  { 0x0144,  0x6E, 0x00 },  /* ń to n */
          848  +  { 0x0145,  0x4E, 0x00 },  /* Ņ to N */
          849  +  { 0x0146,  0x6E, 0x00 },  /* ņ to n */
          850  +  { 0x0147,  0x4E, 0x00 },  /* Ň to N */
          851  +  { 0x0148,  0x6E, 0x00 },  /* ň to n */
          852  +  { 0x0149,  0x27, 0x6E },  /* ʼn to 'n */
          853  +  { 0x014A,  0x4E, 0x47 },  /* Ŋ to NG */
          854  +  { 0x014B,  0x6E, 0x67 },  /* ŋ to ng */
          855  +  { 0x014C,  0x4F, 0x00 },  /* Ō to O */
          856  +  { 0x014D,  0x6F, 0x00 },  /* ō to o */
          857  +  { 0x014E,  0x4F, 0x00 },  /* Ŏ to O */
          858  +  { 0x014F,  0x6F, 0x00 },  /* ŏ to o */
          859  +  { 0x0150,  0x4F, 0x00 },  /* Ő to O */
          860  +  { 0x0151,  0x6F, 0x00 },  /* ő to o */
          861  +  { 0x0152,  0x4F, 0x45 },  /* Πto OE */
          862  +  { 0x0153,  0x6F, 0x65 },  /* œ to oe */
          863  +  { 0x0154,  0x52, 0x00 },  /* Ŕ to R */
          864  +  { 0x0155,  0x72, 0x00 },  /* ŕ to r */
          865  +  { 0x0156,  0x52, 0x00 },  /* Ŗ to R */
          866  +  { 0x0157,  0x72, 0x00 },  /* ŗ to r */
          867  +  { 0x0158,  0x52, 0x00 },  /* Ř to R */
          868  +  { 0x0159,  0x72, 0x00 },  /* ř to r */
          869  +  { 0x015A,  0x53, 0x00 },  /* Ś to S */
          870  +  { 0x015B,  0x73, 0x00 },  /* ś to s */
          871  +  { 0x015C,  0x53, 0x68 },  /* Ŝ to Sh */
          872  +  { 0x015D,  0x73, 0x68 },  /* ŝ to sh */
          873  +  { 0x015E,  0x53, 0x00 },  /* Ş to S */
          874  +  { 0x015F,  0x73, 0x00 },  /* ş to s */
          875  +  { 0x0160,  0x53, 0x00 },  /* Š to S */
          876  +  { 0x0161,  0x73, 0x00 },  /* š to s */
          877  +  { 0x0162,  0x54, 0x00 },  /* Ţ to T */
          878  +  { 0x0163,  0x74, 0x00 },  /* ţ to t */
          879  +  { 0x0164,  0x54, 0x00 },  /* Ť to T */
          880  +  { 0x0165,  0x74, 0x00 },  /* ť to t */
          881  +  { 0x0166,  0x54, 0x00 },  /* Ŧ to T */
          882  +  { 0x0167,  0x74, 0x00 },  /* ŧ to t */
          883  +  { 0x0168,  0x55, 0x00 },  /* Ũ to U */
          884  +  { 0x0169,  0x75, 0x00 },  /* ũ to u */
          885  +  { 0x016A,  0x55, 0x00 },  /* Ū to U */
          886  +  { 0x016B,  0x75, 0x00 },  /* ū to u */
          887  +  { 0x016C,  0x55, 0x00 },  /* Ŭ to U */
          888  +  { 0x016D,  0x75, 0x00 },  /* ŭ to u */
          889  +  { 0x016E,  0x55, 0x00 },  /* Ů to U */
          890  +  { 0x016F,  0x75, 0x00 },  /* ů to u */
          891  +  { 0x0170,  0x55, 0x00 },  /* Ű to U */
          892  +  { 0x0171,  0x75, 0x00 },  /* ű to u */
          893  +  { 0x0172,  0x55, 0x00 },  /* Ų to U */
          894  +  { 0x0173,  0x75, 0x00 },  /* ų to u */
          895  +  { 0x0174,  0x57, 0x00 },  /* Ŵ to W */
          896  +  { 0x0175,  0x77, 0x00 },  /* ŵ to w */
          897  +  { 0x0176,  0x59, 0x00 },  /* Ŷ to Y */
          898  +  { 0x0177,  0x79, 0x00 },  /* ŷ to y */
          899  +  { 0x0178,  0x59, 0x00 },  /* Ÿ to Y */
          900  +  { 0x0179,  0x5A, 0x00 },  /* Ź to Z */
          901  +  { 0x017A,  0x7A, 0x00 },  /* ź to z */
          902  +  { 0x017B,  0x5A, 0x00 },  /* Ż to Z */
          903  +  { 0x017C,  0x7A, 0x00 },  /* ż to z */
          904  +  { 0x017D,  0x5A, 0x00 },  /* Ž to Z */
          905  +  { 0x017E,  0x7A, 0x00 },  /* ž to z */
          906  +  { 0x017F,  0x73, 0x00 },  /* ſ to s */
          907  +  { 0x0192,  0x66, 0x00 },  /* ƒ to f */
          908  +  { 0x0218,  0x53, 0x00 },  /* Ș to S */
          909  +  { 0x0219,  0x73, 0x00 },  /* ș to s */
          910  +  { 0x021A,  0x54, 0x00 },  /* Ț to T */
          911  +  { 0x021B,  0x74, 0x00 },  /* ț to t */
          912  +  { 0x0386,  0x41, 0x00 },  /* Ά to A */
          913  +  { 0x0388,  0x45, 0x00 },  /* Έ to E */
          914  +  { 0x0389,  0x49, 0x00 },  /* Ή to I */
          915  +  { 0x038A,  0x49, 0x00 },  /* Ί to I */
          916  +  { 0x038C,  0x4f, 0x00 },  /* Ό to O */
          917  +  { 0x038E,  0x59, 0x00 },  /* Ύ to Y */
          918  +  { 0x038F,  0x4f, 0x00 },  /* Ώ to O */
          919  +  { 0x0390,  0x69, 0x00 },  /* ΐ to i */
          920  +  { 0x0391,  0x41, 0x00 },  /* Α to A */
          921  +  { 0x0392,  0x42, 0x00 },  /* Β to B */
          922  +  { 0x0393,  0x47, 0x00 },  /* Γ to G */
          923  +  { 0x0394,  0x44, 0x00 },  /* Δ to D */
          924  +  { 0x0395,  0x45, 0x00 },  /* Ε to E */
          925  +  { 0x0396,  0x5a, 0x00 },  /* Ζ to Z */
          926  +  { 0x0397,  0x49, 0x00 },  /* Η to I */
          927  +  { 0x0398,  0x54, 0x68 },  /* Θ to Th */
          928  +  { 0x0399,  0x49, 0x00 },  /* Ι to I */
          929  +  { 0x039A,  0x4b, 0x00 },  /* Κ to K */
          930  +  { 0x039B,  0x4c, 0x00 },  /* Λ to L */
          931  +  { 0x039C,  0x4d, 0x00 },  /* Μ to M */
          932  +  { 0x039D,  0x4e, 0x00 },  /* Ν to N */
          933  +  { 0x039E,  0x58, 0x00 },  /* Ξ to X */
          934  +  { 0x039F,  0x4f, 0x00 },  /* Ο to O */
          935  +  { 0x03A0,  0x50, 0x00 },  /* Π to P */
          936  +  { 0x03A1,  0x52, 0x00 },  /* Ρ to R */
          937  +  { 0x03A3,  0x53, 0x00 },  /* Σ to S */
          938  +  { 0x03A4,  0x54, 0x00 },  /* Τ to T */
          939  +  { 0x03A5,  0x59, 0x00 },  /* Υ to Y */
          940  +  { 0x03A6,  0x46, 0x00 },  /* Φ to F */
          941  +  { 0x03A7,  0x43, 0x68 },  /* Χ to Ch */
          942  +  { 0x03A8,  0x50, 0x73 },  /* Ψ to Ps */
          943  +  { 0x03A9,  0x4f, 0x00 },  /* Ω to O */
          944  +  { 0x03AA,  0x49, 0x00 },  /* Ϊ to I */
          945  +  { 0x03AB,  0x59, 0x00 },  /* Ϋ to Y */
          946  +  { 0x03AC,  0x61, 0x00 },  /* ά to a */
          947  +  { 0x03AD,  0x65, 0x00 },  /* έ to e */
          948  +  { 0x03AE,  0x69, 0x00 },  /* ή to i */
          949  +  { 0x03AF,  0x69, 0x00 },  /* ί to i */
          950  +  { 0x03B1,  0x61, 0x00 },  /* α to a */
          951  +  { 0x03B2,  0x62, 0x00 },  /* β to b */
          952  +  { 0x03B3,  0x67, 0x00 },  /* γ to g */
          953  +  { 0x03B4,  0x64, 0x00 },  /* δ to d */
          954  +  { 0x03B5,  0x65, 0x00 },  /* ε to e */
          955  +  { 0x03B6,  0x7a, 0x00 },  /* ζ to z */
          956  +  { 0x03B7,  0x69, 0x00 },  /* η to i */
          957  +  { 0x03B8,  0x74, 0x68 },  /* θ to th */
          958  +  { 0x03B9,  0x69, 0x00 },  /* ι to i */
          959  +  { 0x03BA,  0x6b, 0x00 },  /* κ to k */
          960  +  { 0x03BB,  0x6c, 0x00 },  /* λ to l */
          961  +  { 0x03BC,  0x6d, 0x00 },  /* μ to m */
          962  +  { 0x03BD,  0x6e, 0x00 },  /* ν to n */
          963  +  { 0x03BE,  0x78, 0x00 },  /* ξ to x */
          964  +  { 0x03BF,  0x6f, 0x00 },  /* ο to o */
          965  +  { 0x03C0,  0x70, 0x00 },  /* π to p */
          966  +  { 0x03C1,  0x72, 0x00 },  /* ρ to r */
          967  +  { 0x03C3,  0x73, 0x00 },  /* σ to s */
          968  +  { 0x03C4,  0x74, 0x00 },  /* τ to t */
          969  +  { 0x03C5,  0x79, 0x00 },  /* υ to y */
          970  +  { 0x03C6,  0x66, 0x00 },  /* φ to f */
          971  +  { 0x03C7,  0x63, 0x68 },  /* χ to ch */
          972  +  { 0x03C8,  0x70, 0x73 },  /* ψ to ps */
          973  +  { 0x03C9,  0x6f, 0x00 },  /* ω to o */
          974  +  { 0x03CA,  0x69, 0x00 },  /* ϊ to i */
          975  +  { 0x03CB,  0x79, 0x00 },  /* ϋ to y */
          976  +  { 0x03CC,  0x6f, 0x00 },  /* ό to o */
          977  +  { 0x03CD,  0x79, 0x00 },  /* ύ to y */
          978  +  { 0x03CE,  0x69, 0x00 },  /* ώ to i */
          979  +  { 0x0400,  0x45, 0x00 },  /* Ѐ to E */
          980  +  { 0x0401,  0x45, 0x00 },  /* Ё to E */
          981  +  { 0x0402,  0x44, 0x00 },  /* Ђ to D */
          982  +  { 0x0403,  0x47, 0x00 },  /* Ѓ to G */
          983  +  { 0x0404,  0x45, 0x00 },  /* Є to E */
          984  +  { 0x0405,  0x5a, 0x00 },  /* Ѕ to Z */
          985  +  { 0x0406,  0x49, 0x00 },  /* І to I */
          986  +  { 0x0407,  0x49, 0x00 },  /* Ї to I */
          987  +  { 0x0408,  0x4a, 0x00 },  /* Ј to J */
          988  +  { 0x0409,  0x49, 0x00 },  /* Љ to I */
          989  +  { 0x040A,  0x4e, 0x00 },  /* Њ to N */
          990  +  { 0x040B,  0x44, 0x00 },  /* Ћ to D */
          991  +  { 0x040C,  0x4b, 0x00 },  /* Ќ to K */
          992  +  { 0x040D,  0x49, 0x00 },  /* Ѝ to I */
          993  +  { 0x040E,  0x55, 0x00 },  /* Ў to U */
          994  +  { 0x040F,  0x44, 0x00 },  /* Џ to D */
          995  +  { 0x0410,  0x41, 0x00 },  /* А to A */
          996  +  { 0x0411,  0x42, 0x00 },  /* Б to B */
          997  +  { 0x0412,  0x56, 0x00 },  /* В to V */
          998  +  { 0x0413,  0x47, 0x00 },  /* Г to G */
          999  +  { 0x0414,  0x44, 0x00 },  /* Д to D */
         1000  +  { 0x0415,  0x45, 0x00 },  /* Е to E */
         1001  +  { 0x0416,  0x5a, 0x68 },  /* Ж to Zh */
         1002  +  { 0x0417,  0x5a, 0x00 },  /* З to Z */
         1003  +  { 0x0418,  0x49, 0x00 },  /* И to I */
         1004  +  { 0x0419,  0x49, 0x00 },  /* Й to I */
         1005  +  { 0x041A,  0x4b, 0x00 },  /* К to K */
         1006  +  { 0x041B,  0x4c, 0x00 },  /* Л to L */
         1007  +  { 0x041C,  0x4d, 0x00 },  /* М to M */
         1008  +  { 0x041D,  0x4e, 0x00 },  /* Н to N */
         1009  +  { 0x041E,  0x4f, 0x00 },  /* О to O */
         1010  +  { 0x041F,  0x50, 0x00 },  /* П to P */
         1011  +  { 0x0420,  0x52, 0x00 },  /* Р to R */
         1012  +  { 0x0421,  0x53, 0x00 },  /* С to S */
         1013  +  { 0x0422,  0x54, 0x00 },  /* Т to T */
         1014  +  { 0x0423,  0x55, 0x00 },  /* У to U */
         1015  +  { 0x0424,  0x46, 0x00 },  /* Ф to F */
         1016  +  { 0x0425,  0x4b, 0x68 },  /* Х to Kh */
         1017  +  { 0x0426,  0x54, 0x63 },  /* Ц to Tc */
         1018  +  { 0x0427,  0x43, 0x68 },  /* Ч to Ch */
         1019  +  { 0x0428,  0x53, 0x68 },  /* Ш to Sh */
         1020  +  { 0x0429,  0x53, 0x68 },  /* Щ to Shch */
         1021  +  { 0x042B,  0x59, 0x00 },  /* Ы to Y */
         1022  +  { 0x042D,  0x45, 0x00 },  /* Э to E */
         1023  +  { 0x042E,  0x49, 0x75 },  /* Ю to Iu */
         1024  +  { 0x042F,  0x49, 0x61 },  /* Я to Ia */
         1025  +  { 0x0430,  0x61, 0x00 },  /* а to a */
         1026  +  { 0x0431,  0x62, 0x00 },  /* б to b */
         1027  +  { 0x0432,  0x76, 0x00 },  /* в to v */
         1028  +  { 0x0433,  0x67, 0x00 },  /* г to g */
         1029  +  { 0x0434,  0x64, 0x00 },  /* д to d */
         1030  +  { 0x0435,  0x65, 0x00 },  /* е to e */
         1031  +  { 0x0436,  0x7a, 0x68 },  /* ж to zh */
         1032  +  { 0x0437,  0x7a, 0x00 },  /* з to z */
         1033  +  { 0x0438,  0x69, 0x00 },  /* и to i */
         1034  +  { 0x0439,  0x69, 0x00 },  /* й to i */
         1035  +  { 0x043A,  0x6b, 0x00 },  /* к to k */
         1036  +  { 0x043B,  0x6c, 0x00 },  /* л to l */
         1037  +  { 0x043C,  0x6d, 0x00 },  /* м to m */
         1038  +  { 0x043D,  0x6e, 0x00 },  /* н to n */
         1039  +  { 0x043E,  0x6f, 0x00 },  /* о to o */
         1040  +  { 0x043F,  0x70, 0x00 },  /* п to p */
         1041  +  { 0x0440,  0x72, 0x00 },  /* р to r */
         1042  +  { 0x0441,  0x73, 0x00 },  /* с to s */
         1043  +  { 0x0442,  0x74, 0x00 },  /* т to t */
         1044  +  { 0x0443,  0x75, 0x00 },  /* у to u */
         1045  +  { 0x0444,  0x66, 0x00 },  /* ф to f */
         1046  +  { 0x0445,  0x6b, 0x68 },  /* х to kh */
         1047  +  { 0x0446,  0x74, 0x63 },  /* ц to tc */
         1048  +  { 0x0447,  0x63, 0x68 },  /* ч to ch */
         1049  +  { 0x0448,  0x73, 0x68 },  /* ш to sh */
         1050  +  { 0x0449,  0x73, 0x68 },  /* щ to shch */
         1051  +  { 0x044B,  0x79, 0x00 },  /* ы to y */
         1052  +  { 0x044D,  0x65, 0x00 },  /* э to e */
         1053  +  { 0x044E,  0x69, 0x75 },  /* ю to iu */
         1054  +  { 0x044F,  0x69, 0x61 },  /* я to ia */
         1055  +  { 0x0450,  0x65, 0x00 },  /* ѐ to e */
         1056  +  { 0x0451,  0x65, 0x00 },  /* ё to e */
         1057  +  { 0x0452,  0x64, 0x00 },  /* ђ to d */
         1058  +  { 0x0453,  0x67, 0x00 },  /* ѓ to g */
         1059  +  { 0x0454,  0x65, 0x00 },  /* є to e */
         1060  +  { 0x0455,  0x7a, 0x00 },  /* ѕ to z */
         1061  +  { 0x0456,  0x69, 0x00 },  /* і to i */
         1062  +  { 0x0457,  0x69, 0x00 },  /* ї to i */
         1063  +  { 0x0458,  0x6a, 0x00 },  /* ј to j */
         1064  +  { 0x0459,  0x69, 0x00 },  /* љ to i */
         1065  +  { 0x045A,  0x6e, 0x00 },  /* њ to n */
         1066  +  { 0x045B,  0x64, 0x00 },  /* ћ to d */
         1067  +  { 0x045C,  0x6b, 0x00 },  /* ќ to k */
         1068  +  { 0x045D,  0x69, 0x00 },  /* ѝ to i */
         1069  +  { 0x045E,  0x75, 0x00 },  /* ў to u */
         1070  +  { 0x045F,  0x64, 0x00 },  /* џ to d */
         1071  +  { 0x1E02,  0x42, 0x00 },  /* Ḃ to B */
         1072  +  { 0x1E03,  0x62, 0x00 },  /* ḃ to b */
         1073  +  { 0x1E0A,  0x44, 0x00 },  /* Ḋ to D */
         1074  +  { 0x1E0B,  0x64, 0x00 },  /* ḋ to d */
         1075  +  { 0x1E1E,  0x46, 0x00 },  /* Ḟ to F */
         1076  +  { 0x1E1F,  0x66, 0x00 },  /* ḟ to f */
         1077  +  { 0x1E40,  0x4D, 0x00 },  /* Ṁ to M */
         1078  +  { 0x1E41,  0x6D, 0x00 },  /* ṁ to m */
         1079  +  { 0x1E56,  0x50, 0x00 },  /* Ṗ to P */
         1080  +  { 0x1E57,  0x70, 0x00 },  /* ṗ to p */
         1081  +  { 0x1E60,  0x53, 0x00 },  /* Ṡ to S */
         1082  +  { 0x1E61,  0x73, 0x00 },  /* ṡ to s */
         1083  +  { 0x1E6A,  0x54, 0x00 },  /* Ṫ to T */
         1084  +  { 0x1E6B,  0x74, 0x00 },  /* ṫ to t */
         1085  +  { 0x1E80,  0x57, 0x00 },  /* Ẁ to W */
         1086  +  { 0x1E81,  0x77, 0x00 },  /* ẁ to w */
         1087  +  { 0x1E82,  0x57, 0x00 },  /* Ẃ to W */
         1088  +  { 0x1E83,  0x77, 0x00 },  /* ẃ to w */
         1089  +  { 0x1E84,  0x57, 0x00 },  /* Ẅ to W */
         1090  +  { 0x1E85,  0x77, 0x00 },  /* ẅ to w */
         1091  +  { 0x1EF2,  0x59, 0x00 },  /* Ỳ to Y */
         1092  +  { 0x1EF3,  0x79, 0x00 },  /* ỳ to y */
         1093  +  { 0xFB00,  0x66, 0x66 },  /* ff to ff */
         1094  +  { 0xFB01,  0x66, 0x69 },  /* fi to fi */
         1095  +  { 0xFB02,  0x66, 0x6C },  /* fl to fl */
         1096  +  { 0xFB05,  0x73, 0x74 },  /* ſt to st */
         1097  +  { 0xFB06,  0x73, 0x74 },  /* st to st */
         1098  +};
         1099  +
         1100  +/*
         1101  +** Convert the input string from UTF-8 into pure ASCII by converting
         1102  +** all non-ASCII characters to some combination of characters in the
         1103  +** ASCII subset.
         1104  +**
         1105  +** The returned string might contain more characters than the input.
         1106  +**
         1107  +** Space to hold the returned string comes from sqlite3_malloc() and
         1108  +** should be freed by the caller.
         1109  +*/
         1110  +static unsigned char *transliterate(const unsigned char *zIn, int nIn){
         1111  +  unsigned char *zOut = sqlite3_malloc( nIn*4 + 1 );
         1112  +  int i, c, sz, nOut;
         1113  +  if( zOut==0 ) return 0;
         1114  +  i = nOut = 0;
         1115  +  while( i<nIn ){
         1116  +    c = utf8Read(zIn, nIn, &sz);
         1117  +    zIn += sz;
         1118  +    nIn -= sz;
         1119  +    if( c<=127 ){
         1120  +      zOut[nOut++] = c;
         1121  +    }else{
         1122  +      int xTop, xBtm, x;
         1123  +      xTop = sizeof(translit)/sizeof(translit[0]) - 1;
         1124  +      xBtm = 0;
         1125  +      while( xTop>=xBtm ){
         1126  +        x = (xTop + xBtm)/2;
         1127  +        if( translit[x].cFrom==c ){
         1128  +          zOut[nOut++] = translit[x].cTo0;
         1129  +          if( translit[x].cTo1 ){
         1130  +            zOut[nOut++] = translit[x].cTo1;
         1131  +            /* Add an extra "ch" after the "sh" for Щ and щ */
         1132  +            if( c==0x0429 || c== 0x0449 ){
         1133  +              zOut[nOut++] = 'c';
         1134  +              zOut[nOut++] = 'h';
         1135  +            }
         1136  +          }
         1137  +          c = 0;
         1138  +          break;
         1139  +        }else if( translit[x].cFrom>c ){
         1140  +          xTop = x-1;
         1141  +        }else{
         1142  +          xBtm = x+1;
         1143  +        }
         1144  +      }
         1145  +      if( c ) zOut[nOut++] = '?';
         1146  +    }
         1147  +  }
         1148  +  zOut[nOut] = 0;
         1149  +  return zOut;
         1150  +}
         1151  +
         1152  +/*
         1153  +**    spellfix1_translit(X)
         1154  +**
         1155  +** Convert a string that contains non-ASCII Roman characters into 
         1156  +** pure ASCII.
         1157  +*/
         1158  +static void transliterateSqlFunc(
         1159  +  sqlite3_context *context,
         1160  +  int argc,
         1161  +  sqlite3_value **argv
         1162  +){
         1163  +  const unsigned char *zIn = sqlite3_value_text(argv[0]);
         1164  +  int nIn = sqlite3_value_bytes(argv[0]);
         1165  +  unsigned char *zOut = transliterate(zIn, nIn);
         1166  +  if( zOut==0 ){
         1167  +    sqlite3_result_error_nomem(context);
         1168  +  }else{
         1169  +    sqlite3_result_text(context, (char*)zOut, -1, sqlite3_free);
         1170  +  }
         1171  +}
         1172  +
         1173  +/*
         1174  +**    spellfix1_scriptcode(X)
         1175  +**
         1176  +** Try to determine the dominant script used by the word X and return
         1177  +** its ISO 15924 numeric code.
         1178  +**
         1179  +** The current implementation only understands the following scripts:
         1180  +**
         1181  +**    215  (Latin)
         1182  +**    220  (Cyrillic)
         1183  +**    200  (Greek)
         1184  +**
         1185  +** This routine will return 998 if the input X contains characters from
         1186  +** two or more of the above scripts or 999 if X contains no characters
         1187  +** from any of the above scripts.
         1188  +*/
         1189  +static void scriptCodeSqlFunc(
         1190  +  sqlite3_context *context,
         1191  +  int argc,
         1192  +  sqlite3_value **argv
         1193  +){
         1194  +  const unsigned char *zIn = sqlite3_value_text(argv[0]);
         1195  +  int nIn = sqlite3_value_bytes(argv[0]);
         1196  +  int c, sz;
         1197  +  int scriptMask = 0;
         1198  +  int res;
         1199  +# define SCRIPT_LATIN       0x0001
         1200  +# define SCRIPT_CYRILLIC    0x0002
         1201  +# define SCRIPT_GREEK       0x0004
         1202  +
         1203  +  while( nIn>0 ){
         1204  +    c = utf8Read(zIn, nIn, &sz);
         1205  +    zIn += sz;
         1206  +    nIn -= sz;
         1207  +    if( c<0x02af ){
         1208  +      scriptMask |= SCRIPT_LATIN;
         1209  +    }else if( c>=0x0400 && c<=0x04ff ){
         1210  +      scriptMask |= SCRIPT_CYRILLIC;
         1211  +    }else if( c>=0x0386 && c<=0x03ce ){
         1212  +      scriptMask |= SCRIPT_GREEK;
         1213  +    }
         1214  +  }
         1215  +  switch( scriptMask ){
         1216  +    case 0:                res = 999; break;
         1217  +    case SCRIPT_LATIN:     res = 215; break;
         1218  +    case SCRIPT_CYRILLIC:  res = 220; break;
         1219  +    case SCRIPT_GREEK:     res = 200; break;
         1220  +    default:               res = 998; break;
         1221  +  }
         1222  +  sqlite3_result_int(context, res);
         1223  +}
         1224  +
         1225  +/*****************************************************************************
         1226  +** Fuzzy-search virtual table
         1227  +*****************************************************************************/
         1228  +
         1229  +typedef struct spellfix1_vtab spellfix1_vtab;
         1230  +typedef struct spellfix1_cursor spellfix1_cursor;
         1231  +
         1232  +/* Fuzzy-search virtual table object */
         1233  +struct spellfix1_vtab {
         1234  +  sqlite3_vtab base;      /* Base class - must be first */
         1235  +  sqlite3 *db;            /* Database connection */
         1236  +  char *zDbName;          /* Name of database holding this table */
         1237  +  char *zTableName;       /* Name of the virtual table */
         1238  +};
         1239  +
         1240  +/* Fuzzy-search cursor object */
         1241  +struct spellfix1_cursor {
         1242  +  sqlite3_vtab_cursor base;    /* Base class - must be first */
         1243  +  spellfix1_vtab *pVTab;         /* The table to which this cursor belongs */
         1244  +  int nRow;                    /* Number of rows of content */
         1245  +  int nAlloc;                  /* Number of allocated rows */
         1246  +  int iRow;                    /* Current row of content */
         1247  +  int iLang;                   /* Value of the lang= constraint */
         1248  +  int iTop;                    /* Value of the top= constraint */
         1249  +  int iScope;                  /* Value of the scope= constraint */
         1250  +  int nSearch;                 /* Number of vocabulary items checked */
         1251  +  struct spellfix1_row {         /* For each row of content */
         1252  +    sqlite3_int64 iRowid;         /* Rowid for this row */
         1253  +    char *zWord;                  /* Text for this row */
         1254  +    int iRank;                    /* Rank for this row */
         1255  +    int iDistance;                /* Distance from pattern for this row */
         1256  +    int iScore;                   /* Score for sorting */
         1257  +  } *a; 
         1258  +};
         1259  +
         1260  +/*
         1261  +** Construct one or more SQL statements from the format string given
         1262  +** and then evaluate those statements. The success code is written
         1263  +** into *pRc.
         1264  +**
         1265  +** If *pRc is initially non-zero then this routine is a no-op.
         1266  +*/
         1267  +static void spellfix1DbExec(
         1268  +  int *pRc,              /* Success code */
         1269  +  sqlite3 *db,           /* Database in which to run SQL */
         1270  +  const char *zFormat,   /* Format string for SQL */
         1271  +  ...                    /* Arguments to the format string */
         1272  +){
         1273  +  va_list ap;
         1274  +  char *zSql;
         1275  +  if( *pRc ) return;
         1276  +  va_start(ap, zFormat);
         1277  +  zSql = sqlite3_vmprintf(zFormat, ap);
         1278  +  va_end(ap);
         1279  +  if( zSql==0 ){
         1280  +    *pRc = SQLITE_NOMEM;
         1281  +  }else{
         1282  +    *pRc = sqlite3_exec(db, zSql, 0, 0, 0);
         1283  +    sqlite3_free(zSql);
         1284  +  }
         1285  +}
         1286  +
         1287  +/*
         1288  +** xDisconnect/xDestroy method for the fuzzy-search module.
         1289  +*/
         1290  +static int spellfix1Uninit(int isDestroy, sqlite3_vtab *pVTab){
         1291  +  spellfix1_vtab *p = (spellfix1_vtab*)pVTab;
         1292  +  int rc = SQLITE_OK;
         1293  +  if( isDestroy ){
         1294  +    sqlite3 *db = p->db;
         1295  +    spellfix1DbExec(&rc, db, "DROP TABLE IF EXISTS \"%w\".\"%w_vocab\"",
         1296  +                  p->zDbName, p->zTableName);
         1297  +  }
         1298  +  if( rc==SQLITE_OK ){
         1299  +    sqlite3_free(p->zTableName);
         1300  +    sqlite3_free(p);
         1301  +  }
         1302  +  return rc;
         1303  +}
         1304  +static int spellfix1Disconnect(sqlite3_vtab *pVTab){
         1305  +  return spellfix1Uninit(0, pVTab);
         1306  +}
         1307  +static int spellfix1Destroy(sqlite3_vtab *pVTab){
         1308  +  return spellfix1Uninit(1, pVTab);
         1309  +}
         1310  +
         1311  +/*
         1312  +** xConnect/xCreate method for the spellfix1 module. Arguments are:
         1313  +**
         1314  +**   argv[0]   -> module name  ("spellfix1")
         1315  +**   argv[1]   -> database name
         1316  +**   argv[2]   -> table name
         1317  +**   argv[3].. -> optional arguments (currently ignored)
         1318  +*/
         1319  +static int spellfix1Init(
         1320  +  int isCreate,
         1321  +  sqlite3 *db,
         1322  +  void *pAux,
         1323  +  int argc, const char *const*argv,
         1324  +  sqlite3_vtab **ppVTab,
         1325  +  char **pzErr
         1326  +){
         1327  +  spellfix1_vtab *pNew = 0;
         1328  +  const char *zModule = argv[0];
         1329  +  const char *zDbName = argv[1];
         1330  +  const char *zTableName = argv[2];
         1331  +  int nDbName;
         1332  +  int rc = SQLITE_OK;
         1333  +
         1334  +  if( argc<3 ){
         1335  +    *pzErr = sqlite3_mprintf(
         1336  +        "%s: wrong number of CREATE VIRTUAL TABLE arguments", argv[0]
         1337  +    );
         1338  +    rc = SQLITE_ERROR;
         1339  +  }else{
         1340  +    nDbName = strlen(zDbName);
         1341  +    pNew = sqlite3_malloc( sizeof(*pNew) + nDbName + 1);
         1342  +    if( pNew==0 ){
         1343  +      rc = SQLITE_NOMEM;
         1344  +    }else{
         1345  +      memset(pNew, 0, sizeof(*pNew));
         1346  +      pNew->zDbName = (char*)&pNew[1];
         1347  +      memcpy(pNew->zDbName, zDbName, nDbName+1);
         1348  +      pNew->zTableName = sqlite3_mprintf("%s", zTableName);
         1349  +      pNew->db = db;
         1350  +      if( pNew->zTableName==0 ){
         1351  +        rc = SQLITE_NOMEM;
         1352  +      }else{
         1353  +        rc = sqlite3_declare_vtab(db, 
         1354  +             "CREATE TABLE x(word,rank,distance,langid,"
         1355  +             "score,top HIDDEN,scope HIDDEN,srchcnt HIDDEN,"
         1356  +             "soundslike HIDDEN)"
         1357  +        );
         1358  +      }
         1359  +      if( rc==SQLITE_OK && isCreate ){
         1360  +        sqlite3_uint64 r;
         1361  +        spellfix1DbExec(&rc, db,
         1362  +           "CREATE TABLE IF NOT EXISTS \"%w\".\"%w_vocab\"(\n"
         1363  +           "  id INTEGER PRIMARY KEY,\n"
         1364  +           "  rank INT,\n"
         1365  +           "  langid INT,\n"
         1366  +           "  word TEXT,\n"
         1367  +           "  k1 TEXT,\n"
         1368  +           "  k2 TEXT\n"
         1369  +           ");\n",
         1370  +           zDbName, zTableName
         1371  +        );
         1372  +        sqlite3_randomness(sizeof(r), &r);
         1373  +        spellfix1DbExec(&rc, db,
         1374  +           "CREATE INDEX IF NOT EXISTS \"%w\".\"%w_index_%llx\" "
         1375  +              "ON \"%w_vocab\"(langid,k2);",
         1376  +           zDbName, zModule, r, zTableName
         1377  +        );
         1378  +      }
         1379  +    }
         1380  +  }
         1381  +
         1382  +  *ppVTab = (sqlite3_vtab *)pNew;
         1383  +  return rc;
         1384  +}
         1385  +
         1386  +/*
         1387  +** The xConnect and xCreate methods
         1388  +*/
         1389  +static int spellfix1Connect(
         1390  +  sqlite3 *db,
         1391  +  void *pAux,
         1392  +  int argc, const char *const*argv,
         1393  +  sqlite3_vtab **ppVTab,
         1394  +  char **pzErr
         1395  +){
         1396  +  return spellfix1Init(0, db, pAux, argc, argv, ppVTab, pzErr);
         1397  +}
         1398  +static int spellfix1Create(
         1399  +  sqlite3 *db,
         1400  +  void *pAux,
         1401  +  int argc, const char *const*argv,
         1402  +  sqlite3_vtab **ppVTab,
         1403  +  char **pzErr
         1404  +){
         1405  +  return spellfix1Init(1, db, pAux, argc, argv, ppVTab, pzErr);
         1406  +}
         1407  +
         1408  +/*
         1409  +** Reset a cursor so that it contains zero rows of content but holds
         1410  +** space for N rows.
         1411  +*/
         1412  +static void spellfix1ResetCursor(spellfix1_cursor *pCur, int N){
         1413  +  int i;
         1414  +  for(i=0; i<pCur->nRow; i++){
         1415  +    sqlite3_free(pCur->a[i].zWord);
         1416  +  }
         1417  +  pCur->a = sqlite3_realloc(pCur->a, sizeof(pCur->a[0])*N);
         1418  +  pCur->nAlloc = N;
         1419  +  pCur->nRow = 0;
         1420  +  pCur->iRow = 0;
         1421  +  pCur->nSearch = 0;
         1422  +}
         1423  +
         1424  +/*
         1425  +** Close a fuzzy-search cursor.
         1426  +*/
         1427  +static int spellfix1Close(sqlite3_vtab_cursor *cur){
         1428  +  spellfix1_cursor *pCur = (spellfix1_cursor *)cur;
         1429  +  spellfix1ResetCursor(pCur, 0);
         1430  +  sqlite3_free(pCur);
         1431  +  return SQLITE_OK;
         1432  +}
         1433  +
         1434  +/*
         1435  +** Search for terms of these forms:
         1436  +**
         1437  +**   (A)    word MATCH $str
         1438  +**   (B)    langid == $langid
         1439  +**   (C)    top = $top
         1440  +**   (D)    scope = $scope
         1441  +**
         1442  +** The plan number is a bit mask formed with these bits:
         1443  +**
         1444  +**   0x01   (A) is found
         1445  +**   0x02   (B) is found
         1446  +**   0x04   (C) is found
         1447  +**   0x08   (D) is found
         1448  +**
         1449  +** filter.argv[*] values contains $str, $langid, $top, and $scope,
         1450  +** if specified and in that order.
         1451  +*/
         1452  +static int spellfix1BestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
         1453  +  int iPlan = 0;
         1454  +  int iLangTerm = -1;
         1455  +  int iTopTerm = -1;
         1456  +  int iScopeTerm = -1;
         1457  +  int i;
         1458  +  const struct sqlite3_index_constraint *pConstraint;
         1459  +  pConstraint = pIdxInfo->aConstraint;
         1460  +  for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){
         1461  +    if( pConstraint->usable==0 ) continue;
         1462  +
         1463  +    /* Terms of the form:  word MATCH $str */
         1464  +    if( (iPlan & 1)==0 
         1465  +     && pConstraint->iColumn==0
         1466  +     && pConstraint->op==SQLITE_INDEX_CONSTRAINT_MATCH
         1467  +    ){
         1468  +      iPlan |= 1;
         1469  +      pIdxInfo->aConstraintUsage[i].argvIndex = 1;
         1470  +      pIdxInfo->aConstraintUsage[i].omit = 1;
         1471  +    }
         1472  +
         1473  +    /* Terms of the form:  langid = $langid  */
         1474  +    if( (iPlan & 2)==0
         1475  +     && pConstraint->iColumn==3
         1476  +     && pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ
         1477  +    ){
         1478  +      iPlan |= 2;
         1479  +      iLangTerm = i;
         1480  +    }
         1481  +
         1482  +    /* Terms of the form:  top = $top */
         1483  +    if( (iPlan & 4)==0
         1484  +     && pConstraint->iColumn==5
         1485  +     && pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ
         1486  +    ){
         1487  +      iPlan |= 4;
         1488  +      iTopTerm = i;
         1489  +    }
         1490  +
         1491  +    /* Terms of the form:  scope = $scope */
         1492  +    if( (iPlan & 8)==0
         1493  +     && pConstraint->iColumn==6
         1494  +     && pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ
         1495  +    ){
         1496  +      iPlan |= 8;
         1497  +      iScopeTerm = i;
         1498  +    }
         1499  +  }
         1500  +  if( iPlan&1 ){
         1501  +    int idx = 2;
         1502  +    pIdxInfo->idxNum = iPlan;
         1503  +    if( pIdxInfo->nOrderBy==1
         1504  +     && pIdxInfo->aOrderBy[0].iColumn==4
         1505  +     && pIdxInfo->aOrderBy[0].desc==0
         1506  +    ){
         1507  +      pIdxInfo->orderByConsumed = 1;  /* Default order by iScore */
         1508  +    }
         1509  +    if( iPlan&2 ){
         1510  +      pIdxInfo->aConstraintUsage[iLangTerm].argvIndex = idx++;
         1511  +      pIdxInfo->aConstraintUsage[iLangTerm].omit = 1;
         1512  +    }
         1513  +    if( iPlan&4 ){
         1514  +      pIdxInfo->aConstraintUsage[iTopTerm].argvIndex = idx++;
         1515  +      pIdxInfo->aConstraintUsage[iTopTerm].omit = 1;
         1516  +    }
         1517  +    if( iPlan&8 ){
         1518  +      pIdxInfo->aConstraintUsage[iScopeTerm].argvIndex = idx++;
         1519  +      pIdxInfo->aConstraintUsage[iScopeTerm].omit = 1;
         1520  +    }
         1521  +    pIdxInfo->estimatedCost = (double)10000;
         1522  +  }else{
         1523  +    pIdxInfo->idxNum = 0;
         1524  +    pIdxInfo->estimatedCost = (double)10000000;
         1525  +  }
         1526  +  return SQLITE_OK;
         1527  +}
         1528  +
         1529  +/*
         1530  +** Open a new fuzzy-search cursor.
         1531  +*/
         1532  +static int spellfix1Open(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){
         1533  +  spellfix1_vtab *p = (spellfix1_vtab*)pVTab;
         1534  +  spellfix1_cursor *pCur;
         1535  +  pCur = sqlite3_malloc( sizeof(*pCur) );
         1536  +  if( pCur==0 ) return SQLITE_NOMEM;
         1537  +  memset(pCur, 0, sizeof(*pCur));
         1538  +  pCur->pVTab = p;
         1539  +  *ppCursor = &pCur->base;
         1540  +  return SQLITE_OK;
         1541  +}
         1542  +
         1543  +/*
         1544  +** Adjust a distance measurement by the words rank in order to show
         1545  +** preference to common words.
         1546  +*/
         1547  +static int spellfix1Score(int iDistance, int iRank){
         1548  +  int iLog2;
         1549  +  for(iLog2=0; iRank>0; iLog2++, iRank>>=1){}
         1550  +  return iDistance + 32 - iLog2;
         1551  +}
         1552  +
         1553  +/*
         1554  +** Compare two spellfix1_row objects for sorting purposes in qsort() such
         1555  +** that they sort in order of increasing distance.
         1556  +*/
         1557  +static int spellfix1RowCompare(const void *A, const void *B){
         1558  +  const struct spellfix1_row *a = (const struct spellfix1_row*)A;
         1559  +  const struct spellfix1_row *b = (const struct spellfix1_row*)B;
         1560  +  return a->iScore - b->iScore;
         1561  +}
         1562  +
         1563  +/*
         1564  +** This version of the xFilter method work if the MATCH term is present
         1565  +** and we are doing a scan.
         1566  +*/
         1567  +static int spellfix1FilterForMatch(
         1568  +  spellfix1_cursor *pCur,
         1569  +  int idxNum,
         1570  +  int argc,
         1571  +  sqlite3_value **argv
         1572  +){
         1573  +  const unsigned char *zPatternIn;
         1574  +  char *zPattern;
         1575  +  int nPattern;
         1576  +  char *zClass;
         1577  +  int nClass;
         1578  +  int iLimit = 20;
         1579  +  int iScope = 4;
         1580  +  int iLang = 0;
         1581  +  char *zSql;
         1582  +  int rc;
         1583  +  sqlite3_stmt *pStmt;
         1584  +  int idx = 1;
         1585  +  spellfix1_vtab *p = pCur->pVTab;
         1586  +
         1587  +  if( idxNum&2 ){
         1588  +    iLang = sqlite3_value_int(argv[idx++]);
         1589  +  }
         1590  +  if( idxNum&4 ){
         1591  +    iLimit = sqlite3_value_int(argv[idx++]);
         1592  +    if( iLimit<1 ) iLimit = 1;
         1593  +  }
         1594  +  if( idxNum&8 ){
         1595  +    iScope = sqlite3_value_int(argv[idx++]);
         1596  +    if( iScope<1 ) iScope = 1;
         1597  +  }
         1598  +  spellfix1ResetCursor(pCur, iLimit);
         1599  +  zPatternIn = sqlite3_value_text(argv[0]);
         1600  +  if( zPatternIn==0 ) return SQLITE_OK;
         1601  +  zPattern = (char*)transliterate(zPatternIn, sqlite3_value_bytes(argv[0]));
         1602  +  if( zPattern==0 ) return SQLITE_NOMEM;
         1603  +  nPattern = strlen(zPattern);
         1604  +  if( zPattern[nPattern-1]=='*' ) nPattern--;
         1605  +  if( nPattern<iScope ) iScope = nPattern;
         1606  +  zClass = (char*)characterClassString((unsigned char*)zPattern,
         1607  +                                       strlen(zPattern));
         1608  +  nClass = strlen(zClass);
         1609  +  if( nClass>iScope ){
         1610  +    zClass[iScope] = 0;
         1611  +    nClass = iScope;
         1612  +  }
         1613  +  zSql = sqlite3_mprintf(
         1614  +     "SELECT id, word, rank, k1"
         1615  +     "  FROM \"%w\".\"%w_vocab\""
         1616  +     " WHERE langid=%d AND k2 GLOB '%q*'",
         1617  +     p->zDbName, p->zTableName, iLang, zClass
         1618  +  );
         1619  +  rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
         1620  +  sqlite3_free(zSql);
         1621  +  if( rc==SQLITE_OK ){
         1622  +    const char *zK1;
         1623  +    int iDist;
         1624  +    int iRank;
         1625  +    int iScore;
         1626  +    int iWorst = 999999999;
         1627  +    int idx;
         1628  +    int idxWorst;
         1629  +    int i;
         1630  +
         1631  +    while( sqlite3_step(pStmt)==SQLITE_ROW ){
         1632  +      zK1 = (const char*)sqlite3_column_text(pStmt, 3);
         1633  +      if( zK1==0 ) continue;
         1634  +      pCur->nSearch++;
         1635  +      iRank = sqlite3_column_int(pStmt, 2);
         1636  +      iDist = editdist(zPattern, zK1);
         1637  +      iScore = spellfix1Score(iDist,iRank);
         1638  +      if( pCur->nRow<pCur->nAlloc ){
         1639  +        idx = pCur->nRow;
         1640  +      }else if( iScore<iWorst ){
         1641  +        idx = idxWorst;
         1642  +        sqlite3_free(pCur->a[idx].zWord);
         1643  +      }else{
         1644  +        continue;
         1645  +      }
         1646  +      pCur->a[idx].zWord = sqlite3_mprintf("%s", sqlite3_column_text(pStmt, 1));
         1647  +      pCur->a[idx].iRowid = sqlite3_column_int64(pStmt, 0);
         1648  +      pCur->a[idx].iRank = iRank;
         1649  +      pCur->a[idx].iDistance = iDist;
         1650  +      pCur->a[idx].iScore = iScore;
         1651  +      if( pCur->nRow<pCur->nAlloc ) pCur->nRow++;
         1652  +      if( pCur->nRow==pCur->nAlloc ){
         1653  +        iWorst = pCur->a[0].iScore;
         1654  +        idxWorst = 0;
         1655  +        for(i=1; i<pCur->nRow; i++){
         1656  +          iScore = pCur->a[i].iScore;
         1657  +          if( iWorst<iScore ){
         1658  +            iWorst = iScore;
         1659  +            idxWorst = i;
         1660  +          }
         1661  +        }
         1662  +      }
         1663  +    }
         1664  +  }
         1665  +  qsort(pCur->a, pCur->nRow, sizeof(pCur->a[0]), spellfix1RowCompare);
         1666  +  pCur->iTop = iLimit;
         1667  +  pCur->iScope = iScope;
         1668  +  sqlite3_finalize(pStmt);
         1669  +  sqlite3_free(zPattern);
         1670  +  sqlite3_free(zClass);
         1671  +  return SQLITE_OK;
         1672  +}
         1673  +
         1674  +/*
         1675  +** This version of xFilter handles a full-table scan case
         1676  +*/
         1677  +static int spellfix1FilterForFullScan(
         1678  +  spellfix1_cursor *pCur,
         1679  +  int idxNum,
         1680  +  int argc,
         1681  +  sqlite3_value **argv
         1682  +){
         1683  +  spellfix1ResetCursor(pCur, 0);
         1684  +  return SQLITE_OK;
         1685  +}
         1686  +
         1687  +
         1688  +/*
         1689  +** Called to "rewind" a cursor back to the beginning so that
         1690  +** it starts its output over again.  Always called at least once
         1691  +** prior to any spellfix1Column, spellfix1Rowid, or spellfix1Eof call.
         1692  +*/
         1693  +static int spellfix1Filter(
         1694  +  sqlite3_vtab_cursor *cur, 
         1695  +  int idxNum, const char *idxStr,
         1696  +  int argc, sqlite3_value **argv
         1697  +){
         1698  +  spellfix1_cursor *pCur = (spellfix1_cursor *)cur;
         1699  +  int rc;
         1700  +  if( idxNum & 1 ){
         1701  +    rc = spellfix1FilterForMatch(pCur, idxNum, argc, argv);
         1702  +  }else{
         1703  +    rc = spellfix1FilterForFullScan(pCur, idxNum, argc, argv);
         1704  +  }
         1705  +  return rc;
         1706  +}
         1707  +
         1708  +
         1709  +/*
         1710  +** Advance a cursor to its next row of output
         1711  +*/
         1712  +static int spellfix1Next(sqlite3_vtab_cursor *cur){
         1713  +  spellfix1_cursor *pCur = (spellfix1_cursor *)cur;
         1714  +  if( pCur->iRow < pCur->nRow ) pCur->iRow++;
         1715  +  return SQLITE_OK;
         1716  +}
         1717  +
         1718  +/*
         1719  +** Return TRUE if we are at the end-of-file
         1720  +*/
         1721  +static int spellfix1Eof(sqlite3_vtab_cursor *cur){
         1722  +  spellfix1_cursor *pCur = (spellfix1_cursor *)cur;
         1723  +  return pCur->iRow>=pCur->nRow;
         1724  +}
         1725  +
         1726  +/*
         1727  +** Return columns from the current row.
         1728  +*/
         1729  +static int spellfix1Column(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){
         1730  +  spellfix1_cursor *pCur = (spellfix1_cursor*)cur;
         1731  +  switch( i ){
         1732  +    case 0: {
         1733  +      sqlite3_result_text(ctx, pCur->a[pCur->iRow].zWord, -1, SQLITE_STATIC);
         1734  +      break;
         1735  +    }
         1736  +    case 1: {
         1737  +      sqlite3_result_int(ctx, pCur->a[pCur->iRow].iRank);
         1738  +      break;
         1739  +    }
         1740  +    case 2: {
         1741  +      sqlite3_result_int(ctx, pCur->a[pCur->iRow].iDistance);
         1742  +      break;
         1743  +    }
         1744  +    case 3: {
         1745  +      sqlite3_result_int(ctx, pCur->iLang);
         1746  +      break;
         1747  +    }
         1748  +    case 4: {
         1749  +      sqlite3_result_int(ctx, pCur->a[pCur->iRow].iScore);
         1750  +      break;
         1751  +    }
         1752  +    case 5: {
         1753  +      sqlite3_result_int(ctx, pCur->iTop);
         1754  +      break;
         1755  +    }
         1756  +    case 6: {
         1757  +      sqlite3_result_int(ctx, pCur->iScope);
         1758  +      break;
         1759  +    }
         1760  +    case 7: {
         1761  +      sqlite3_result_int(ctx, pCur->nSearch);
         1762  +      break;
         1763  +    }
         1764  +    default: {
         1765  +      sqlite3_result_null(ctx);
         1766  +      break;
         1767  +    }
         1768  +  }
         1769  +  return SQLITE_OK;
         1770  +}
         1771  +
         1772  +/*
         1773  +** The rowid.
         1774  +*/
         1775  +static int spellfix1Rowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
         1776  +  spellfix1_cursor *pCur = (spellfix1_cursor*)cur;
         1777  +  *pRowid = pCur->a[pCur->iRow].iRowid;
         1778  +  return SQLITE_OK;
         1779  +}
         1780  +
         1781  +/*
         1782  +** The xUpdate() method.
         1783  +*/
         1784  +static int spellfix1Update(
         1785  +  sqlite3_vtab *pVTab,
         1786  +  int argc,
         1787  +  sqlite3_value **argv,
         1788  +  sqlite_int64 *pRowid
         1789  +){
         1790  +  int rc = SQLITE_OK;
         1791  +  sqlite3_int64 rowid, newRowid;
         1792  +  spellfix1_vtab *p = (spellfix1_vtab*)pVTab;
         1793  +  sqlite3 *db = p->db;
         1794  +
         1795  +  if( argc==1 ){
         1796  +    /* A delete operation on the rowid given by argv[0] */
         1797  +    rowid = *pRowid = sqlite3_value_int64(argv[0]);
         1798  +    spellfix1DbExec(&rc, db, "DELETE FROM \"%w\".\"%w_vocab\" "
         1799  +                           " WHERE id=%lld",
         1800  +                  p->zDbName, p->zTableName, rowid);
         1801  +  }else{
         1802  +    const unsigned char *zWord = sqlite3_value_text(argv[2]);
         1803  +    int nWord = sqlite3_value_bytes(argv[2]);
         1804  +    int iLang = sqlite3_value_int(argv[5]);
         1805  +    int iRank = sqlite3_value_int(argv[3]);
         1806  +    const unsigned char *zSoundslike = sqlite3_value_text(argv[10]);
         1807  +    int nSoundslike = sqlite3_value_bytes(argv[10]);
         1808  +    char *zK1, *zK2;
         1809  +    int i;
         1810  +    char c;
         1811  +
         1812  +    if( zWord==0 ){
         1813  +      pVTab->zErrMsg = sqlite3_mprintf("%w.word may not be NULL",
         1814  +                            p->zTableName);
         1815  +      return SQLITE_CONSTRAINT;
         1816  +    }
         1817  +    if( iRank<1 ) iRank = 1;
         1818  +    if( zSoundslike ){
         1819  +      zK1 = (char*)transliterate(zSoundslike, nSoundslike);
         1820  +    }else{
         1821  +      zK1 = (char*)transliterate(zWord, nWord);
         1822  +    }
         1823  +    if( zK1==0 ) return SQLITE_NOMEM;
         1824  +    for(i=0; (c = zK1[i])!=0; i++){
         1825  +       if( c>='A' && c<='Z' ) zK1[i] += 'a' - 'A';
         1826  +    }
         1827  +    zK2 = (char*)characterClassString((const unsigned char*)zK1, i);
         1828  +    if( zK2==0 ){
         1829  +      sqlite3_free(zK1);
         1830  +      return SQLITE_NOMEM;
         1831  +    }
         1832  +    if( sqlite3_value_type(argv[0])==SQLITE_NULL ){
         1833  +      spellfix1DbExec(&rc, db,
         1834  +             "INSERT INTO \"%w\".\"%w_vocab\"(rank,langid,word,k1,k2) "
         1835  +             "VALUES(%d,%d,%Q,%Q,%Q)",
         1836  +             p->zDbName, p->zTableName,
         1837  +             iRank, iLang, zWord, zK1, zK2
         1838  +      );
         1839  +      *pRowid = sqlite3_last_insert_rowid(db);
         1840  +    }else{
         1841  +      rowid = sqlite3_value_int64(argv[0]);
         1842  +      newRowid = *pRowid = sqlite3_value_int64(argv[1]);
         1843  +      spellfix1DbExec(&rc, db,
         1844  +             "UPDATE \"%w\".\"%w_vocab\" SET id=%lld, rank=%d, lang=%d,"
         1845  +             " word=%Q, rank=%d, k1=%Q, k2=%Q WHERE id=%lld",
         1846  +             p->zDbName, p->zTableName, newRowid, iRank, iLang,
         1847  +             zWord, zK1, zK2, rowid
         1848  +      );
         1849  +    }
         1850  +    sqlite3_free(zK1);
         1851  +    sqlite3_free(zK2);
         1852  +  }
         1853  +  return rc;
         1854  +}
         1855  +
         1856  +/*
         1857  +** Rename the spellfix1 table.
         1858  +*/
         1859  +static int spellfix1Rename(sqlite3_vtab *pVTab, const char *zNew){
         1860  +  spellfix1_vtab *p = (spellfix1_vtab*)pVTab;
         1861  +  sqlite3 *db = p->db;
         1862  +  int rc = SQLITE_OK;
         1863  +  char *zNewName = sqlite3_mprintf("%s", zNew);
         1864  +  if( zNewName==0 ){
         1865  +    return SQLITE_NOMEM;
         1866  +  }
         1867  +  spellfix1DbExec(&rc, db, 
         1868  +     "ALTER TABLE \"%w\".\"%w_vocab\" RENAME TO \"%w_vocab\"",
         1869  +     p->zDbName, p->zTableName, zNewName
         1870  +  );
         1871  +  if( rc==SQLITE_OK ){
         1872  +    sqlite3_free(p->zTableName);
         1873  +    p->zTableName = zNewName;
         1874  +  }
         1875  +  return rc;
         1876  +}
         1877  +
         1878  +
         1879  +/*
         1880  +** A virtual table module that provides fuzzy search.
         1881  +*/
         1882  +static sqlite3_module spellfix1Module = {
         1883  +  0,                       /* iVersion */
         1884  +  spellfix1Create,         /* xCreate - handle CREATE VIRTUAL TABLE */
         1885  +  spellfix1Connect,        /* xConnect - reconnected to an existing table */
         1886  +  spellfix1BestIndex,      /* xBestIndex - figure out how to do a query */
         1887  +  spellfix1Disconnect,     /* xDisconnect - close a connection */
         1888  +  spellfix1Destroy,        /* xDestroy - handle DROP TABLE */
         1889  +  spellfix1Open,           /* xOpen - open a cursor */
         1890  +  spellfix1Close,          /* xClose - close a cursor */
         1891  +  spellfix1Filter,         /* xFilter - configure scan constraints */
         1892  +  spellfix1Next,           /* xNext - advance a cursor */
         1893  +  spellfix1Eof,            /* xEof - check for end of scan */
         1894  +  spellfix1Column,         /* xColumn - read data */
         1895  +  spellfix1Rowid,          /* xRowid - read data */
         1896  +  spellfix1Update,         /* xUpdate */
         1897  +  0,                       /* xBegin */
         1898  +  0,                       /* xSync */
         1899  +  0,                       /* xCommit */
         1900  +  0,                       /* xRollback */
         1901  +  0,                       /* xFindMethod */
         1902  +  spellfix1Rename,         /* xRename */
         1903  +};
         1904  +
         1905  +/*
         1906  +** Register the various functions and the virtual table.
         1907  +*/
         1908  +static int spellfix1Register(sqlite3 *db){
         1909  +  int nErr = 0;
         1910  +  int i;
         1911  +  nErr += sqlite3_create_function(db, "spellfix1_translit", 1, SQLITE_UTF8, 0,
         1912  +                                  transliterateSqlFunc, 0, 0);
         1913  +  nErr += sqlite3_create_function(db, "spellfix1_editdist", 2, SQLITE_UTF8, 0,
         1914  +                                  editdistSqlFunc, 0, 0);
         1915  +  nErr += sqlite3_create_function(db, "spellfix1_charclass", 1, SQLITE_UTF8, 0,
         1916  +                                  characterClassSqlFunc, 0, 0);
         1917  +  nErr += sqlite3_create_function(db, "spellfix1_scriptcode", 1, SQLITE_UTF8, 0,
         1918  +                                  scriptCodeSqlFunc, 0, 0);
         1919  +  nErr += sqlite3_create_module(db, "spellfix1", &spellfix1Module, 0);
         1920  +
         1921  +  /* Verify sanity of the translit[] table */
         1922  +  for(i=0; i<sizeof(translit)/sizeof(translit[0])-1; i++){
         1923  +    assert( translit[i].cFrom<translit[i+1].cFrom );
         1924  +  }  
         1925  +
         1926  +  return nErr ? SQLITE_ERROR : SQLITE_OK;
         1927  +}
         1928  +
         1929  +#if SQLITE_CORE || defined(SQLITE_TEST)
         1930  +/*
         1931  +** Register the spellfix1 virtual table and its associated functions.
         1932  +*/
         1933  +int sqlite3Spellfix1Register(sqlite3 *db){
         1934  +  return spellfix1Register(db);
         1935  +}
         1936  +#endif
         1937  +
         1938  +
         1939  +#if !SQLITE_CORE
         1940  +/*
         1941  +** Extension load function.
         1942  +*/
         1943  +int sqlite3_extension_init(
         1944  +  sqlite3 *db, 
         1945  +  char **pzErrMsg, 
         1946  +  const sqlite3_api_routines *pApi
         1947  +){
         1948  +  SQLITE_EXTENSION_INIT2(pApi);
         1949  +  return spellfix1Register(db);
         1950  +}
         1951  +#endif /* !SQLITE_CORE */

Changes to src/vtab.c.

   443    443   static int vtabCallConstructor(
   444    444     sqlite3 *db, 
   445    445     Table *pTab,
   446    446     Module *pMod,
   447    447     int (*xConstruct)(sqlite3*,void*,int,const char*const*,sqlite3_vtab**,char**),
   448    448     char **pzErr
   449    449   ){
   450         -  VtabCtx sCtx;
          450  +  VtabCtx sCtx, *pPriorCtx;
   451    451     VTable *pVTable;
   452    452     int rc;
   453    453     const char *const*azArg = (const char *const*)pTab->azModuleArg;
   454    454     int nArg = pTab->nModuleArg;
   455    455     char *zErr = 0;
   456    456     char *zModuleName = sqlite3MPrintf(db, "%s", pTab->zName);
   457    457   
................................................................................
   468    468     pVTable->pMod = pMod;
   469    469   
   470    470     /* Invoke the virtual table constructor */
   471    471     assert( &db->pVtabCtx );
   472    472     assert( xConstruct );
   473    473     sCtx.pTab = pTab;
   474    474     sCtx.pVTable = pVTable;
          475  +  pPriorCtx = db->pVtabCtx;
   475    476     db->pVtabCtx = &sCtx;
   476    477     rc = xConstruct(db, pMod->pAux, nArg, azArg, &pVTable->pVtab, &zErr);
   477         -  db->pVtabCtx = 0;
          478  +  db->pVtabCtx = pPriorCtx;
   478    479     if( rc==SQLITE_NOMEM ) db->mallocFailed = 1;
   479    480   
   480    481     if( SQLITE_OK!=rc ){
   481    482       if( zErr==0 ){
   482    483         *pzErr = sqlite3MPrintf(db, "vtable constructor failed: %s", zModuleName);
   483    484       }else {
   484    485         *pzErr = sqlite3MPrintf(db, "%s", zErr);

Changes to src/where.c.

  1558   1558     **
  1559   1559     **   1. The index is itself UNIQUE, and
  1560   1560     **
  1561   1561     **   2. All of the columns in the index are either part of the pDistinct
  1562   1562     **      list, or else the WHERE clause contains a term of the form "col=X",
  1563   1563     **      where X is a constant value. The collation sequences of the
  1564   1564     **      comparison and select-list expressions must match those of the index.
         1565  +  **
         1566  +  **   3. All of those index columns for which the WHERE clause does not
         1567  +  **      contain a "col=X" term are subject to a NOT NULL constraint.
  1565   1568     */
  1566   1569     for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
  1567   1570       if( pIdx->onError==OE_None ) continue;
  1568   1571       for(i=0; i<pIdx->nColumn; i++){
  1569   1572         int iCol = pIdx->aiColumn[i];
  1570         -      if( 0==findTerm(pWC, iBase, iCol, ~(Bitmask)0, WO_EQ, pIdx) 
  1571         -       && 0>findIndexCol(pParse, pDistinct, iBase, pIdx, i)
  1572         -      ){
  1573         -        break;
         1573  +      if( 0==findTerm(pWC, iBase, iCol, ~(Bitmask)0, WO_EQ, pIdx) ){
         1574  +        int iIdxCol = findIndexCol(pParse, pDistinct, iBase, pIdx, i);
         1575  +        if( iIdxCol<0 || pTab->aCol[pIdx->aiColumn[i]].notNull==0 ){
         1576  +          break;
         1577  +        }
  1574   1578         }
  1575   1579       }
  1576   1580       if( i==pIdx->nColumn ){
  1577   1581         /* This index implies that the DISTINCT qualifier is redundant. */
  1578   1582         return 1;
  1579   1583       }
  1580   1584     }
................................................................................
  1714   1718     if( j>=nTerm ){
  1715   1719       /* All terms of the ORDER BY clause are covered by this index so
  1716   1720       ** this index can be used for sorting. */
  1717   1721       return 1;
  1718   1722     }
  1719   1723     if( pIdx->onError!=OE_None && i==pIdx->nColumn
  1720   1724         && (wsFlags & WHERE_COLUMN_NULL)==0
  1721         -      && !referencesOtherTables(pOrderBy, pMaskSet, j, base) ){
  1722         -    /* All terms of this index match some prefix of the ORDER BY clause
  1723         -    ** and the index is UNIQUE and no terms on the tail of the ORDER BY
  1724         -    ** clause reference other tables in a join.  If this is all true then
  1725         -    ** the order by clause is superfluous.  Not that if the matching
  1726         -    ** condition is IS NULL then the result is not necessarily unique
  1727         -    ** even on a UNIQUE index, so disallow those cases. */
  1728         -    return 1;
         1725  +      && !referencesOtherTables(pOrderBy, pMaskSet, j, base) 
         1726  +  ){
         1727  +    Column *aCol = pIdx->pTable->aCol;
         1728  +    int i;
         1729  +
         1730  +    /* All terms of this index match some prefix of the ORDER BY clause,
         1731  +    ** the index is UNIQUE, and no terms on the tail of the ORDER BY
         1732  +    ** refer to other tables in a join. So, assuming that the index entries
         1733  +    ** visited contain no NULL values, then this index delivers rows in
         1734  +    ** the required order.
         1735  +    **
         1736  +    ** It is not possible for any of the first nEqCol index fields to be
         1737  +    ** NULL (since the corresponding "=" operator in the WHERE clause would 
         1738  +    ** not be true). So if all remaining index columns have NOT NULL 
         1739  +    ** constaints attached to them, we can be confident that the visited
         1740  +    ** index entries are free of NULLs.  */
         1741  +    for(i=nEqCol; i<pIdx->nColumn; i++){
         1742  +      if( aCol[pIdx->aiColumn[i]].notNull==0 ) break;
         1743  +    }
         1744  +    return (i==pIdx->nColumn);
  1729   1745     }
  1730   1746     return 0;
  1731   1747   }
  1732   1748   
  1733   1749   /*
  1734   1750   ** Prepare a crude estimate of the logarithm of the input value.
  1735   1751   ** The results need not be exact.  This is only used for estimating

Changes to test/distinct.test.

    73     73   do_execsql_test 1.0 {
    74     74     CREATE TABLE t1(a, b, c, d);
    75     75     CREATE UNIQUE INDEX i1 ON t1(b, c);
    76     76     CREATE UNIQUE INDEX i2 ON t1(d COLLATE nocase);
    77     77   
    78     78     CREATE TABLE t2(x INTEGER PRIMARY KEY, y);
    79     79   
    80         -  CREATE TABLE t3(c1 PRIMARY KEY, c2);
           80  +  CREATE TABLE t3(c1 PRIMARY KEY NOT NULL, c2 NOT NULL);
    81     81     CREATE INDEX i3 ON t3(c2);
           82  +
           83  +  CREATE TABLE t4(a, b NOT NULL, c NOT NULL, d NOT NULL);
           84  +  CREATE UNIQUE INDEX t4i1 ON t4(b, c);
           85  +  CREATE UNIQUE INDEX t4i2 ON t4(d COLLATE nocase);
    82     86   }
    83     87   foreach {tn noop sql} {
    84     88   
    85         -  1   1   "SELECT DISTINCT b, c FROM t1"
    86         -  2   1   "SELECT DISTINCT c FROM t1 WHERE b = ?"
           89  +  1.1 0   "SELECT DISTINCT b, c FROM t1"
           90  +  1.2 1   "SELECT DISTINCT b, c FROM t4"
           91  +  2.1 0   "SELECT DISTINCT c FROM t1 WHERE b = ?"
           92  +  2.2 1   "SELECT DISTINCT c FROM t4 WHERE b = ?"
    87     93     3   1   "SELECT DISTINCT rowid FROM t1"
    88     94     4   1   "SELECT DISTINCT rowid, a FROM t1"
    89     95     5   1   "SELECT DISTINCT x FROM t2"
    90     96     6   1   "SELECT DISTINCT * FROM t2"
    91     97     7   1   "SELECT DISTINCT * FROM (SELECT * FROM t2)"
    92     98   
    93         -  8   1   "SELECT DISTINCT * FROM t1"
           99  +  8.1 0   "SELECT DISTINCT * FROM t1"
          100  +  8.2 1   "SELECT DISTINCT * FROM t4"
    94    101   
    95    102     8   0   "SELECT DISTINCT a, b FROM t1"
    96    103   
    97    104     9   0   "SELECT DISTINCT c FROM t1 WHERE b IN (1,2)"
    98    105     10  0   "SELECT DISTINCT c FROM t1"
    99    106     11  0   "SELECT DISTINCT b FROM t1"
   100    107   
   101         -  12  0   "SELECT DISTINCT a, d FROM t1"
   102         -  13  0   "SELECT DISTINCT a, b, c COLLATE nocase FROM t1"
   103         -  14  1   "SELECT DISTINCT a, d COLLATE nocase FROM t1"
   104         -  15  0   "SELECT DISTINCT a, d COLLATE binary FROM t1"
   105         -  16  1   "SELECT DISTINCT a, b, c COLLATE binary FROM t1"
          108  +  12.1 0   "SELECT DISTINCT a, d FROM t1"
          109  +  12.2 0   "SELECT DISTINCT a, d FROM t4"
          110  +  13.1 0   "SELECT DISTINCT a, b, c COLLATE nocase FROM t1"
          111  +  13.2 0   "SELECT DISTINCT a, b, c COLLATE nocase FROM t4"
          112  +  14.1 0   "SELECT DISTINCT a, d COLLATE nocase FROM t1"
          113  +  14.2 1   "SELECT DISTINCT a, d COLLATE nocase FROM t4"
          114  +
          115  +  15   0   "SELECT DISTINCT a, d COLLATE binary FROM t1"
          116  +  16.1 0   "SELECT DISTINCT a, b, c COLLATE binary FROM t1"
          117  +  16.2 1   "SELECT DISTINCT a, b, c COLLATE binary FROM t4"
   106    118   
   107    119     16  0   "SELECT DISTINCT t1.rowid FROM t1, t2"
   108    120     17  0   { /* Technically, it would be possible to detect that DISTINCT
   109    121               ** is a no-op in cases like the following. But SQLite does not
   110    122               ** do so. */
   111    123               SELECT DISTINCT t1.rowid FROM t1, t2 WHERE t1.rowid=t2.rowid }
   112    124   
................................................................................
   116    128     21  0   "SELECT DISTINCT c2 FROM t3"
   117    129   
   118    130     22  0   "SELECT DISTINCT * FROM (SELECT 1, 2, 3 UNION SELECT 4, 5, 6)"
   119    131     23  1   "SELECT DISTINCT rowid FROM (SELECT 1, 2, 3 UNION SELECT 4, 5, 6)"
   120    132   
   121    133     24  0   "SELECT DISTINCT rowid/2 FROM t1"
   122    134     25  1   "SELECT DISTINCT rowid/2, rowid FROM t1"
   123         -  26  1   "SELECT DISTINCT rowid/2, b FROM t1 WHERE c = ?"
          135  +  26.1  0   "SELECT DISTINCT rowid/2, b FROM t1 WHERE c = ?"
          136  +  26.2  1   "SELECT DISTINCT rowid/2, b FROM t4 WHERE c = ?"
   124    137   } {
   125    138     if {$noop} {
   126    139       do_distinct_noop_test 1.$tn $sql
   127    140     } else {
   128    141       do_distinct_not_noop_test 1.$tn $sql
   129    142     }
   130    143   }

Changes to test/io.test.

   141    141   # Set the device-characteristic mask to include the SQLITE_IOCAP_ATOMIC,
   142    142   # then do another INSERT similar to the one in io-2.2. This should
   143    143   # only write 1 page and require a single fsync().
   144    144   # 
   145    145   # The single fsync() is the database file. Only one page is reported as
   146    146   # written because page 1 - the change-counter page - is written using
   147    147   # an out-of-band method that bypasses the write counter.
          148  +#
          149  +# UPDATE: As of [05f98d4eec] (adding SQLITE_DBSTATUS_CACHE_WRITE), the
          150  +# second write is also counted. So this now reports two writes and a
          151  +# single fsync.
   148    152   #
   149    153   sqlite3_simulate_device -char atomic
   150    154   do_test io-2.3 {
   151    155     execsql { INSERT INTO abc VALUES(3, 4) }
   152    156     list [nWrite db] [nSync]
   153         -} {1 1}
          157  +} {2 1}
   154    158   
   155    159   # Test that the journal file is not created and the change-counter is
   156    160   # updated when the atomic-write optimization is used.
   157    161   #
   158    162   do_test io-2.4.1 {
   159    163     execsql {
   160    164       BEGIN;

Changes to test/select9.test.

   410    410   do_test select9-4.X {
   411    411     execsql {
   412    412       DROP INDEX i1;
   413    413       DROP INDEX i2;
   414    414       DROP VIEW v1;
   415    415     }
   416    416   } {}
          417  +
          418  +# Testing to make sure that queries involving a view of a compound select
          419  +# are planned efficiently.  This detects a problem reported on the mailing
          420  +# list on 2012-04-26.  See
          421  +#
          422  +#  http://www.mail-archive.com/sqlite-users%40sqlite.org/msg69746.html
          423  +#
          424  +# For additional information.
          425  +#
          426  +do_test select9-5.1 {
          427  +  db eval {
          428  +    CREATE TABLE t51(x, y);
          429  +    CREATE TABLE t52(x, y);
          430  +    CREATE VIEW v5 as
          431  +       SELECT x, y FROM t51
          432  +       UNION ALL
          433  +       SELECT x, y FROM t52;
          434  +    CREATE INDEX t51x ON t51(x);
          435  +    CREATE INDEX t52x ON t52(x);
          436  +    EXPLAIN QUERY PLAN
          437  +       SELECT * FROM v5 WHERE x='12345' ORDER BY y;
          438  +  }
          439  +} {~/SCAN TABLE/}  ;# Uses indices with "*"
          440  +do_test select9-5.2 {
          441  +  db eval {
          442  +    EXPLAIN QUERY PLAN
          443  +       SELECT x, y FROM v5 WHERE x='12345' ORDER BY y;
          444  +  }
          445  +} {~/SCAN TABLE/}  ;# Uses indices with "x, y"
          446  +do_test select9-5.3 {
          447  +  db eval {
          448  +    EXPLAIN QUERY PLAN
          449  +       SELECT x, y FROM v5 WHERE +x='12345' ORDER BY y;
          450  +  }
          451  +} {/SCAN TABLE/}   ;# Full table scan if the "+x" prevents index usage.
   417    452   
   418    453   
   419    454   finish_test

Name change from tool/shell1.test to test/shell1.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   #
    12     12   # The focus of this file is testing the CLI shell tool.
    13     13   #
    14         -# $Id: shell1.test,v 1.7 2009/07/17 16:54:48 shaneh Exp $
    15     14   #
    16     15   
    17     16   # Test plan:
    18     17   #
    19     18   #   shell1-1.*: Basic command line option handling.
    20     19   #   shell1-2.*: Basic "dot" command token parsing.
    21     20   #   shell1-3.*: Basic test that "dot" command can be called.
    22     21   #
    23         -
    24         -package require sqlite3
    25         -
    26         -set CLI "./sqlite3"
    27         -
    28         -proc do_test {name cmd expected} {
    29         -  puts -nonewline "$name ..."
    30         -  set res [uplevel $cmd]
    31         -  if {$res eq $expected} {
    32         -    puts Ok
    33         -  } else {
    34         -    puts Error
    35         -    puts "  Got: $res"
    36         -    puts "  Expected: $expected"
    37         -    exit
    38         -  }
           22  +set testdir [file dirname $argv0]
           23  +source $testdir/tester.tcl
           24  +if {$tcl_platform(platform)=="windows"} {
           25  +  set CLI "sqlite3.exe"
           26  +} else {
           27  +  set CLI "./sqlite3"
           28  +}
           29  +if {![file executable $CLI]} {
           30  +  finish_test
           31  +  return
    39     32   }
    40         -
    41         -proc execsql {sql} {
    42         -  uplevel [list db eval $sql]
    43         -}
    44         -
    45         -proc catchsql {sql} {
    46         -  set rc [catch {uplevel [list db eval $sql]} msg]
    47         -  list $rc $msg
    48         -}
    49         -
    50         -proc catchcmd {db {cmd ""}} {
    51         -  global CLI
    52         -  set out [open cmds.txt w]
    53         -  puts $out $cmd
    54         -  close $out
    55         -  set line "exec $CLI $db < cmds.txt"
    56         -  set rc [catch { eval $line } msg]
    57         -  list $rc $msg
    58         -}
    59         -
    60         -file delete -force test.db test.db.journal
           33  +db close
           34  +forcedelete test.db test.db-journal test.db-wal
    61     35   sqlite3 db test.db
    62     36   
    63     37   #----------------------------------------------------------------------------
    64     38   # Test cases shell1-1.*: Basic command line option handling.
    65     39   #
    66     40   
    67     41   # invalid option
................................................................................
   713    687   } {1 {Error: unknown command or invalid arguments:  "timer". Enter ".help" for help}}
   714    688   
   715    689   do_test shell1-3-28.1 {
   716    690     catchcmd test.db \
   717    691        ".log stdout\nSELECT coalesce(sqlite_log(123,'hello'),'456');"
   718    692   } "0 {(123) hello\n456}"
   719    693   
   720         -puts "CLI tests completed successfully"
          694  +# Test the output of the ".dump" command
          695  +#
          696  +do_test shell1-4.1 {
          697  +  db eval {
          698  +    CREATE TABLE t1(x);
          699  +    INSERT INTO t1 VALUES(null), (1), (2.25), ('hello'), (x'807f');
          700  +  }
          701  +  catchcmd test.db {.dump}
          702  +} {0 {PRAGMA foreign_keys=OFF;
          703  +BEGIN TRANSACTION;
          704  +CREATE TABLE t1(x);
          705  +INSERT INTO "t1" VALUES(NULL);
          706  +INSERT INTO "t1" VALUES(1);
          707  +INSERT INTO "t1" VALUES(2.25);
          708  +INSERT INTO "t1" VALUES('hello');
          709  +INSERT INTO "t1" VALUES(X'807F');
          710  +COMMIT;}}
          711  +
          712  +# Test the output of ".mode insert"
          713  +#
          714  +do_test shell1-4.2 {
          715  +  catchcmd test.db ".mode insert t1\nselect * from t1;"
          716  +} {0 {INSERT INTO t1 VALUES(NULL);
          717  +INSERT INTO t1 VALUES(1);
          718  +INSERT INTO t1 VALUES(2.25);
          719  +INSERT INTO t1 VALUES('hello');
          720  +INSERT INTO t1 VALUES(X'807f');}}
          721  +
          722  +
          723  +finish_test

Name change from tool/shell2.test to test/shell2.test.

    14     14   # $Id: shell2.test,v 1.7 2009/07/17 16:54:48 shaneh Exp $
    15     15   #
    16     16   
    17     17   # Test plan:
    18     18   #
    19     19   #   shell2-1.*: Misc. test of various tickets and reported errors.
    20     20   #
    21         -
    22         -package require sqlite3
    23         -
    24         -set CLI "./sqlite3"
    25         -
    26         -proc do_test {name cmd expected} {
    27         -  puts -nonewline "$name ..."
    28         -  set res [uplevel $cmd]
    29         -  if {$res eq $expected} {
    30         -    puts Ok
    31         -  } else {
    32         -    puts Error
    33         -    puts "  Got: $res"
    34         -    puts "  Expected: $expected"
    35         -    exit
    36         -  }
           21  +set testdir [file dirname $argv0]
           22  +source $testdir/tester.tcl
           23  +if {$tcl_platform(platform)=="windows"} {
           24  +  set CLI "sqlite3.exe"
           25  +} else {
           26  +  set CLI "./sqlite3"
           27  +}
           28  +if {![file executable $CLI]} {
           29  +  finish_test
           30  +  return
    37     31   }
    38         -
    39         -proc execsql {sql} {
    40         -  uplevel [list db eval $sql]
    41         -}
    42         -
    43         -proc catchsql {sql} {
    44         -  set rc [catch {uplevel [list db eval $sql]} msg]
    45         -  list $rc $msg
    46         -}
    47         -
    48         -proc catchcmd {db {cmd ""}} {
    49         -  global CLI
    50         -  set out [open cmds.txt w]
    51         -  puts $out $cmd
    52         -  close $out
    53         -  set line "exec $CLI $db < cmds.txt"
    54         -  set rc [catch { eval $line } msg]
    55         -  list $rc $msg
    56         -}
    57         -
    58         -file delete -force test.db test.db.journal
           32  +db close
           33  +forcedelete test.db test.db-journal test.db-wal
    59     34   sqlite3 db test.db
    60     35   
    61     36   
    62     37   #----------------------------------------------------------------------------
    63     38   #   shell2-1.*: Misc. test of various tickets and reported errors.
    64     39   #
    65     40   
................................................................................
   215    190   1
   216    191   2
   217    192   SELECT * FROM foo2;
   218    193   b
   219    194   1
   220    195   2}}
   221    196   
   222         -puts "CLI tests completed successfully"
          197  +finish_test

Name change from tool/shell3.test to test/shell3.test.

    15     15   #
    16     16   
    17     17   # Test plan:
    18     18   #
    19     19   #   shell3-1.*: Basic tests for running SQL statments from command line.
    20     20   #   shell3-2.*: Basic tests for running SQL file from command line.
    21     21   #
    22         -
    23         -package require sqlite3
    24         -
    25         -set CLI "./sqlite3"
    26         -
    27         -proc do_test {name cmd expected} {
    28         -  puts -nonewline "$name ..."
    29         -  set res [uplevel $cmd]
    30         -  if {$res eq $expected} {
    31         -    puts Ok
    32         -  } else {
    33         -    puts Error
    34         -    puts "  Got: $res"
    35         -    puts "  Expected: $expected"
    36         -    exit
    37         -  }
           22  +set testdir [file dirname $argv0]
           23  +source $testdir/tester.tcl
           24  +if {$tcl_platform(platform)=="windows"} {
           25  +  set CLI "sqlite3.exe"
           26  +} else {
           27  +  set CLI "./sqlite3"
           28  +}
           29  +if {![file executable $CLI]} {
           30  +  finish_test
           31  +  return
    38     32   }
    39         -
    40         -proc execsql {sql} {
    41         -  uplevel [list db eval $sql]
    42         -}
    43         -
    44         -proc catchsql {sql} {
    45         -  set rc [catch {uplevel [list db eval $sql]} msg]
    46         -  list $rc $msg
    47         -}
    48         -
    49         -proc catchcmd {db {cmd ""}} {
    50         -  global CLI
    51         -  set out [open cmds.txt w]
    52         -  puts $out $cmd
    53         -  close $out
    54         -  set line "exec $CLI $db < cmds.txt"
    55         -  set rc [catch { eval $line } msg]
    56         -  list $rc $msg
    57         -}
    58         -
    59         -file delete -force test.db test.db.journal
           33  +db close
           34  +forcedelete test.db test.db-journal test.db-wal
    60     35   sqlite3 db test.db
    61         -
    62     36   
    63     37   #----------------------------------------------------------------------------
    64     38   #   shell3-1.*: Basic tests for running SQL statments from command line.
    65     39   #
    66     40   
    67     41   # Run SQL statement from command line
    68     42   do_test shell3-1.1 {
................................................................................
   116     90   do_test shell3-2.6 {
   117     91     catchcmd "foo.db" ".tables"
   118     92   } {0 {}}
   119     93   do_test shell3-2.7 {
   120     94     catchcmd "foo.db" "CREATE TABLE"
   121     95   } {1 {Error: incomplete SQL: CREATE TABLE}}
   122     96   
   123         -
   124         -puts "CLI tests completed successfully"
           97  +finish_test

Name change from tool/shell4.test to test/shell4.test.

    15     15   # $Id: shell4.test,v 1.7 2009/07/17 16:54:48 shaneh Exp $
    16     16   #
    17     17   
    18     18   # Test plan:
    19     19   #
    20     20   #   shell4-1.*: Basic tests specific to the "stats" command.
    21     21   #
    22         -
    23         -set CLI "./sqlite3"
    24         -
    25         -proc do_test {name cmd expected} {
    26         -  puts -nonewline "$name ..."
    27         -  set res [uplevel $cmd]
    28         -  if {$res eq $expected} {
    29         -    puts Ok
    30         -  } else {
    31         -    puts Error
    32         -    puts "  Got: $res"
    33         -    puts "  Expected: $expected"
    34         -    exit
    35         -  }
           22  +set testdir [file dirname $argv0]
           23  +source $testdir/tester.tcl
           24  +if {$tcl_platform(platform)=="windows"} {
           25  +  set CLI "sqlite3.exe"
           26  +} else {
           27  +  set CLI "./sqlite3"
           28  +}
           29  +if {![file executable $CLI]} {
           30  +  finish_test
           31  +  return
    36     32   }
    37         -
    38         -proc catchcmd {db {cmd ""}} {
    39         -  global CLI
    40         -  set out [open cmds.txt w]
    41         -  puts $out $cmd
    42         -  close $out
    43         -  set line "exec $CLI $db < cmds.txt"
    44         -  set rc [catch { eval $line } msg]
    45         -  list $rc $msg
    46         -}
    47         -
    48         -file delete -force test.db test.db.journal
           33  +db close
           34  +forcedelete test.db test.db-journal test.db-wal
           35  +sqlite3 db test.db
    49     36   
    50     37   #----------------------------------------------------------------------------
    51     38   # Test cases shell4-1.*: Tests specific to the "stats" command.
    52     39   #
    53     40   
    54     41   # should default to off
    55     42   do_test shell4-1.1.1 {
................................................................................
   122    109   SELECT 1;
   123    110   }]
   124    111     list [regexp {Memory Used} $res] \
   125    112          [regexp {Heap Usage} $res] \
   126    113          [regexp {Autoindex Inserts} $res]
   127    114   } {1 1 1}
   128    115   
   129         -puts "CLI tests completed successfully"
          116  +finish_test

Name change from tool/shell5.test to test/shell5.test.

    15     15   # $Id: shell5.test,v 1.7 2009/07/17 16:54:48 shaneh Exp $
    16     16   #
    17     17   
    18     18   # Test plan:
    19     19   #
    20     20   #   shell5-1.*: Basic tests specific to the ".import" command.
    21     21   #
    22         -
    23         -set CLI "./sqlite3"
    24         -
    25         -proc do_test {name cmd expected} {
    26         -  puts -nonewline "$name ..."
    27         -  set res [uplevel $cmd]
    28         -  if {$res eq $expected} {
    29         -    puts Ok
    30         -  } else {
    31         -    puts Error
    32         -    puts "  Got: $res"
    33         -    puts "  Expected: $expected"
    34         -    exit
    35         -  }
           22  +set testdir [file dirname $argv0]
           23  +source $testdir/tester.tcl
           24  +if {$tcl_platform(platform)=="windows"} {
           25  +  set CLI "sqlite3.exe"
           26  +} else {
           27  +  set CLI "./sqlite3"
           28  +}
           29  +if {![file executable $CLI]} {
           30  +  finish_test
           31  +  return
    36     32   }
    37         -
    38         -proc catchcmd {db {cmd ""}} {
    39         -  global CLI
    40         -  set out [open cmds.txt w]
    41         -  puts $out $cmd
    42         -  close $out
    43         -  set line "exec $CLI $db < cmds.txt"
    44         -  set rc [catch { eval $line } msg]
    45         -  list $rc $msg
    46         -}
    47         -
    48         -file delete -force test.db test.db.journal
           33  +db close
           34  +forcedelete test.db test.db-journal test.db-wal
           35  +sqlite3 db test.db
    49     36   
    50     37   #----------------------------------------------------------------------------
    51     38   # Test cases shell5-1.*: Basic handling of the .import and .separator commands.
    52     39   #
    53     40   
    54     41   # .import FILE TABLE     Import data from FILE into TABLE
    55     42   do_test shell5-1.1.1 {
................................................................................
   235    222     }
   236    223     close $in
   237    224     set res [catchcmd "test.db" {CREATE TABLE t3(a);
   238    225   .import shell5.csv t3
   239    226   SELECT COUNT(*) FROM t3;}]
   240    227   } [list 0 $rows]
   241    228   
   242         -
   243         -puts "CLI tests completed successfully"
          229  +finish_test

Changes to test/tester.tcl.

   495    495     set_test_counter count [expr [set_test_counter count] + 1]
   496    496   }
   497    497   
   498    498   
   499    499   # Invoke the do_test procedure to run a single test 
   500    500   #
   501    501   proc do_test {name cmd expected} {
   502         -
   503    502     global argv cmdlinearg
   504    503   
   505    504     fix_testname name
   506    505   
   507    506     sqlite3_memdebug_settitle $name
   508    507   
   509    508   #  if {[llength $argv]==0} { 
................................................................................
   526    525     puts -nonewline $name...
   527    526     flush stdout
   528    527   
   529    528     if {![info exists ::G(match)] || [string match $::G(match) $name]} {
   530    529       if {[catch {uplevel #0 "$cmd;\n"} result]} {
   531    530         puts "\nError: $result"
   532    531         fail_test $name
   533         -    } elseif {[string compare $result $expected]} {
   534         -      puts "\nExpected: \[$expected\]\n     Got: \[$result\]"
   535         -      fail_test $name
   536    532       } else {
   537         -      puts " Ok"
          533  +      if {[regexp {^~?/.*/$} $expected]} {
          534  +        if {[string index $expected 0]=="~"} {
          535  +          set re [string range $expected 2 end-1]
          536  +          set ok [expr {![regexp $re $result]}]
          537  +        } else {
          538  +          set re [string range $expected 1 end-1]
          539  +          set ok [regexp $re $result]
          540  +        }
          541  +      } else {
          542  +        set ok [expr {[string compare $result $expected]==0}]
          543  +      }
          544  +      if {!$ok} {
          545  +        puts "\nExpected: \[$expected\]\n     Got: \[$result\]"
          546  +        fail_test $name
          547  +      } else {
          548  +        puts " Ok"
          549  +      }
   538    550       }
   539    551     } else {
   540    552       puts " Omitted"
   541    553       omit_test $name "pattern mismatch" 0
   542    554     }
   543    555     flush stdout
   544    556   }
          557  +
          558  +proc catchcmd {db {cmd ""}} {
          559  +  global CLI
          560  +  set out [open cmds.txt w]
          561  +  puts $out $cmd
          562  +  close $out
          563  +  set line "exec $CLI $db < cmds.txt"
          564  +  set rc [catch { eval $line } msg]
          565  +  list $rc $msg
          566  +}
   545    567   
   546    568   proc filepath_normalize {p} {
   547    569     # test cases should be written to assume "unix"-like file paths
   548    570     if {$::tcl_platform(platform)!="unix"} {
   549    571       # lreverse*2 as a hack to remove any unneeded {} after the string map
   550    572       lreverse [lreverse [string map {\\ /} [regsub -nocase -all {[a-z]:[/\\]+} $p {/}]]]
   551    573     } {

Added test/tkt-2a5629202f.test.

            1  +# 2012 April 19
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# The tests in this file were used while developing the SQLite 4 code. 
           12  +#
           13  +
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +set testprefix tkt-2a5629202f
           17  +
           18  +# This procedure executes the SQL.  Then it checks to see if the OP_Sort
           19  +# opcode was executed.  If an OP_Sort did occur, then "sort" is appended
           20  +# to the result.  If no OP_Sort happened, then "nosort" is appended.
           21  +#
           22  +# This procedure is used to check to make sure sorting is or is not
           23  +# occurring as expected.
           24  +#
           25  +proc cksort {sql} {
           26  +  set data [execsql $sql]
           27  +  if {[db status sort]} {set x sort} {set x nosort}
           28  +  lappend data $x
           29  +  return $data
           30  +}
           31  +
           32  +do_execsql_test 1.1 {
           33  +  CREATE TABLE t8(b TEXT, c TEXT);
           34  +  INSERT INTO t8 VALUES('a',  'one');
           35  +  INSERT INTO t8 VALUES('b',  'two');
           36  +  INSERT INTO t8 VALUES(NULL, 'three');
           37  +  INSERT INTO t8 VALUES(NULL, 'four');
           38  +}
           39  +
           40  +do_execsql_test 1.2 {
           41  +  SELECT coalesce(b, 'null') || '/' || c FROM t8 x ORDER BY x.b, x.c
           42  +} {null/four null/three a/one b/two}
           43  +
           44  +do_execsql_test 1.3 {
           45  +  CREATE UNIQUE INDEX i1 ON t8(b);
           46  +  SELECT coalesce(b, 'null') || '/' || c FROM t8 x ORDER BY x.b, x.c
           47  +} {null/four null/three a/one b/two}
           48  +
           49  +#-------------------------------------------------------------------------
           50  +#
           51  +
           52  +do_execsql_test 2.1 {
           53  +  CREATE TABLE t2(a, b NOT NULL, c);
           54  +  CREATE UNIQUE INDEX t2ab ON t2(a, b);
           55  +  CREATE UNIQUE INDEX t2ba ON t2(b, a);
           56  +}
           57  +
           58  +do_test 2.2 {
           59  +  cksort { SELECT * FROM t2 WHERE a = 10 ORDER BY a, b, c }
           60  +} {nosort}
           61  +
           62  +do_test 2.3 {
           63  +  cksort { SELECT * FROM t2 WHERE b = 10 ORDER BY a, b, c }
           64  +} {sort}
           65  +
           66  +do_test 2.4 {
           67  +  cksort { SELECT * FROM t2 WHERE a IS NULL ORDER BY a, b, c }
           68  +} {sort}
           69  +
           70  +finish_test
           71  +

Added test/tkt-385a5b56b9.test.

            1  +# 2012 April 02
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# The tests in this file were used while developing the SQLite 4 code. 
           12  +#
           13  +set testdir [file dirname $argv0]
           14  +source $testdir/tester.tcl
           15  +set testprefix tkt-385a5b56b9
           16  +
           17  +do_execsql_test 1.0 { 
           18  +  CREATE TABLE t1(x, y);
           19  +  INSERT INTO t1 VALUES(1, NULL);
           20  +  INSERT INTO t1 VALUES(2, NULL);
           21  +  INSERT INTO t1 VALUES(1, NULL);
           22  +}
           23  +
           24  +do_execsql_test 1.1 { SELECT DISTINCT x, y FROM t1 } {1 {} 2 {}}
           25  +do_execsql_test 1.2 { CREATE UNIQUE INDEX i1 ON t1(x, y) }
           26  +do_execsql_test 1.3 { SELECT DISTINCT x, y FROM t1 } {1 {} 2 {}}
           27  +
           28  +
           29  +#-------------------------------------------------------------------------
           30  +
           31  +do_execsql_test 2.0 {
           32  +  CREATE TABLE t2(x, y NOT NULL);
           33  +  CREATE UNIQUE INDEX t2x ON t2(x);
           34  +  CREATE UNIQUE INDEX t2y ON t2(y);
           35  +}
           36  +
           37  +do_eqp_test 2.1 { SELECT DISTINCT x FROM t2 } {
           38  +  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2x (~1000000 rows)}
           39  +}
           40  +
           41  +do_eqp_test 2.2 { SELECT DISTINCT y FROM t2 } {
           42  +  0 0 0 {SCAN TABLE t2 (~1000000 rows)}
           43  +}
           44  +
           45  +do_eqp_test 2.3 { SELECT DISTINCT x, y FROM t2 WHERE y=10 } {
           46  +  0 0 0 {SEARCH TABLE t2 USING INDEX t2y (y=?) (~1 rows)}
           47  +}
           48  +
           49  +do_eqp_test 2.4 { SELECT DISTINCT x, y FROM t2 WHERE x=10 } {
           50  +  0 0 0 {SEARCH TABLE t2 USING INDEX t2x (x=?) (~1 rows)}
           51  +}
           52  +
           53  +finish_test
           54  +

Changes to test/where.test.

  1101   1101   } {1/1 1/4 4/1 4/4 nosort}
  1102   1102   do_test where-14.4 {
  1103   1103     cksort {
  1104   1104       SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b DESC
  1105   1105     } 
  1106   1106   } {1/1 1/4 4/1 4/4 nosort}
  1107   1107   do_test where-14.5 {
         1108  +  # This test case changed from "nosort" to "sort". See ticket 2a5629202f.
  1108   1109     cksort {
  1109   1110       SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b
  1110   1111     } 
  1111         -} {4/1 4/4 1/1 1/4 nosort}
         1112  +} {4/1 4/4 1/1 1/4 sort}
  1112   1113   do_test where-14.6 {
         1114  +  # This test case changed from "nosort" to "sort". See ticket 2a5629202f.
  1113   1115     cksort {
  1114   1116       SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC
  1115   1117     } 
  1116         -} {4/1 4/4 1/1 1/4 nosort}
         1118  +} {4/1 4/4 1/1 1/4 sort}
  1117   1119   do_test where-14.7 {
  1118   1120     cksort {
  1119   1121       SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b
  1120   1122     } 
  1121   1123   } {4/1 4/4 1/1 1/4 sort}
  1122   1124   do_test where-14.7.1 {
  1123   1125     cksort {