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: |
2a99c0074aaabb4916d82464427be079 |
| 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
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 {