private Task <IReadOnlyList <IGlyphData> > InternalSearchAsync(string ftsTable, string table, string query, FontVariant variant) { return(Task.Run <IReadOnlyList <IGlyphData> >(() => { /* * Step 1: Perform single-result Hex Search if hex * Step 2: Perform FTS search if not hex or ambiguous * Step 3: Perform LIKE search if still space for results */ // 1. Decide if hex or FTS4 search // 1.1. If hex, search the main table (UnicodeIndex column is indexed) GlyphDescription hexResult = null; bool ambiguous = !variant.FontFace.IsSymbolFont && IsAmbiguousQuery(query); if (Utils.TryParseHexString(query, out int hex)) { // 1.2. To be more efficient, first check if the font actually contains the UnicodeIndex. // If it does then we ask the database, otherwise we can return without query. foreach (var range in variant.UnicodeRanges) { if (hex >= range.Item1 && hex <= range.Item2) { string hexsql = $"SELECT * FROM {table} WHERE UnicodeIndex == {hex} LIMIT 1"; var hexresults = _connection.Query <GlyphDescription>(hexsql, query)?.Cast <IGlyphData>()?.ToList(); if (hexresults == null || hexresults.Count == 0) { var label = hex.ToString("X"); hexresults = new List <IGlyphData>() { new GlyphDescription { UnicodeIndex = hex, UnicodeHex = label, Description = label } }; } // 1.3. If the search is ambiguous we should still search for description matches, // otherwise we can return right now if (!ambiguous) { return hexresults; } else { hexResult = hexresults.Cast <GlyphDescription>().FirstOrDefault(); break; } } } // 1.4. If the search is ambiguous we should still search for description matches, // otherwise we can return right now with no hex results // If we are a generic symbol font, that's all folks. Time to leave. if (!ambiguous) { return GlyphService.EMPTY_SEARCH; } } // 1.5. If we are a generic symbol font, we don't match by character name so time to go home. if (!FontFinder.IsMDL2(variant) && !IsFontAwesome(variant) && variant.FontFace.IsSymbolFont) { return GlyphService.EMPTY_SEARCH; } // 2. If we're performing SQL, create the base query filter StringBuilder sb = new StringBuilder(); bool next = false; foreach ((int, int)range in variant.UnicodeRanges) { if (next) { sb.AppendFormat(" OR UnicodeIndex BETWEEN {0} AND {1}", range.Item1, range.Item2); } else { next = true; sb.AppendFormat("WHERE (UnicodeIndex BETWEEN {0} AND {1}", range.Item1, range.Item2); } } sb.Append(")"); // 2.1. A helper method to inject the hex result for ambiguous searches List <IGlyphData> InsertHex(List <IGlyphData> list) { if (hexResult != null) { list.Insert(0, hexResult); } return list; } // 3. Otherwise, perform a multi-step text search. First perform an FTS4 search string sql = $"SELECT * FROM {ftsTable} {sb.ToString()} AND Description MATCH '{query}' LIMIT {SEARCH_LIMIT}"; var results = _connection.Query <GlyphDescription>(sql, query)?.Cast <IGlyphData>()?.ToList(); // 4. If we have SEARCH_LIMIT matches, we don't need to perform a partial search and can go home early if (results != null && results.Count == SEARCH_LIMIT) { return InsertHex(results); } // 5. Perform a partial search on non-FTS table. Only search for what we need. // This means limit the amount of results, and exclude anything we've already matched. int limit = results == null ? SEARCH_LIMIT : SEARCH_LIMIT - results.Count; if (limit != SEARCH_LIMIT) { // 5.1. We need to exclude anything already found above sb.AppendFormat("AND UnicodeIndex NOT IN ({0})", string.Join(", ", results.Select(r => r.UnicodeIndex))); } // 6. Execute on the non FTS tables string sql2 = $"SELECT * FROM {table} {sb.ToString()} AND Description LIKE '%{query}%' LIMIT {limit}"; var results2 = _connection.Query <GlyphDescription>(sql2, query)?.Cast <IGlyphData>()?.ToList(); if (results != null) { results.AddRange(results2); return InsertHex(results); } else { return InsertHex(results2); } })); }
private Task <IReadOnlyList <IGlyphData> > InternalSearchAsync(string ftsTable, string table, string query, FontVariant variant) { return(Task.Run <IReadOnlyList <IGlyphData> >(() => { /* * Step 1: Perform single-result Hex Search if hex * Step 2: Perform FTS search if not hex or ambiguous * Step 3: Perform LIKE search if still space for results */ // 1. Decide if hex or FTS4 search // 1.1. If hex, search the main table (UnicodeIndex column is indexed) GlyphDescription hexResult = null; bool ambiguous = !variant.FontFace.IsSymbolFont && IsAmbiguousQuery(query); if (Utils.TryParseHexString(query, out int hex)) { // 1.2. To be more efficient, first check if the font actually contains the UnicodeIndex. // If it does then we ask the database, otherwise we can return without query. foreach (var range in variant.UnicodeRanges) { if (hex >= range.First && hex <= range.Last) { string hexsql = $"SELECT * FROM {table} WHERE Ix == {hex} LIMIT 1"; var hexresults = _connection.Query <GlyphDescription>(hexsql, query)?.Cast <IGlyphData>()?.ToList(); if (hexresults == null || hexresults.Count == 0) { var label = hex.ToString("x4"); hexresults = new List <IGlyphData>() { new GlyphDescription { UnicodeIndex = hex, UnicodeHex = label, Description = label } }; } // 1.3. If the search is ambiguous we should still search for description matches, // otherwise we can return right now if (!ambiguous) { return hexresults; } else { hexResult = hexresults.Cast <GlyphDescription>().FirstOrDefault(); break; } } } // 1.4. If the search is ambiguous we should still search for description matches, // otherwise we can return right now with no hex results // If we are a generic symbol font, that's all folks. Time to leave. if (!ambiguous) { return GlyphService.EMPTY_SEARCH; } } // 2. If we're performing SQL, create the base query filter StringBuilder sb = new StringBuilder(); bool next = false; /// Note: SQLite only supports expression trees up to 1000 items, so we need to limit the range /// of Unicode ranges we search through. Very rare for any font to hit this - especially one with /// any useful search results. MS Office Symbol is an example of such a font (with no useful search /// results anyway). Certain complex Asian script fonts **may** theoretically hit this limit. /// We don't want to throw an exception if we ever hit this case, we'll just do our best. foreach (var range in variant.UnicodeRanges.Take(995)) { if (next) { sb.AppendFormat(" OR Ix BETWEEN {0} AND {1}", range.First, range.Last); } else { next = true; sb.AppendFormat("WHERE (Ix BETWEEN {0} AND {1}", range.First, range.Last); } } sb.Append(")"); // 2.1. A helper method to inject the hex result for ambiguous searches List <IGlyphData> InsertHex(List <IGlyphData> list) { if (hexResult != null) { list.Insert(0, hexResult); } return list; } List <IGlyphData> results = null; // 3. Otherwise, perform a multi-step text search. First perform an FTS4 search string sql = $"SELECT * FROM {ftsTable} {sb.ToString()} AND Description MATCH ? LIMIT {SEARCH_LIMIT}"; results = _connection.Query <GlyphDescription>(sql, $"{query}*")?.Cast <IGlyphData>()?.ToList(); // 4. If we have SEARCH_LIMIT matches, we don't need to perform a partial search and can go home early if (results != null && results.Count == SEARCH_LIMIT) { return InsertHex(results); } // 5. Perform a partial search on non-FTS table. Only search for what we need. // This means limit the amount of results, and exclude anything we've already matched. int limit = results == null ? SEARCH_LIMIT : SEARCH_LIMIT - results.Count; if (limit != SEARCH_LIMIT) { // 5.1. We need to exclude anything already found above sb.AppendFormat("AND Ix NOT IN ({0})", string.Join(", ", results.Select(r => r.UnicodeIndex))); } // 6. Execute on the non FTS tables string sql2 = $"SELECT * FROM {table} {sb.ToString()} AND Description LIKE ? LIMIT {limit}"; var results2 = _connection.Query <GlyphDescription>(sql2, $"%{query}%")?.Cast <IGlyphData>()?.ToList(); if (results != null) { results.AddRange(results2); return InsertHex(results); } else { return InsertHex(results2); } })); }