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);
}
}));
}
