public static IEnumerable<ItemLocation> PerformSearch(string query, SearchResultsType types, int maxResultsPerType)
{
List<ItemLocation> results = new List<ItemLocation>();
List<string> clauses;
List<string> terms;
types = ParseQuery(query, types, out clauses, out terms);
if (clauses.Count() == 0)
return results;
string where = string.Join(" AND ", clauses.ToArray());
// NOTE: DISTINCT is to filter out "Ley" and "Ley Sector" (different names, same result).
// TODO: Include the searched-for name in the results, and show alternate names in the result set.
// {0} is the list of distinct fields (i.e. coordinates), {1} is the list of fields in the subquery (same as {0} but with "name" added, {2} is the table, {3} is the filter
// Since we need the distinct values from the term {0} but don't use the name for the results construction, we can ignore name in the resultset.
// This allows us to get the top N results from the database, sort by name, and then toss out duplicates not based on name but on the other, used, columns
// Here's a sample subquery that works for the sector table.
// SELECT DISTINCT TOP 160 tt.x, tt.y FROM (SELECT TOP 160 x, y,name FROM sectors WHERE (name LIKE 'LEY%' OR name LIKE '%LEY%') ORDER BY name ASC) AS tt;
string query_format = "SELECT DISTINCT TOP " + maxResultsPerType + " {0} FROM (SELECT TOP " + maxResultsPerType + " {1} FROM {2} WHERE {3}) AS TT";
using (var connection = DBUtil.MakeConnection())
{
// Sectors
if (types.HasFlag(SearchResultsType.Sectors))
{
// Note duplicated field names so the results of both queries can come out right.
string sql = string.Format(query_format, "TT.x, TT.y", "x, y", "sectors", where);
using (var sqlCommand = new SqlCommand(sql, connection))
{
for (int i = 0; i < terms.Count; ++i)
sqlCommand.Parameters.AddWithValue(string.Format("@term{0}", i), terms[i]);
using (var row = sqlCommand.ExecuteReader())
{
while (row.Read())
{
results.Add(new SectorLocation(row.GetInt32(0), row.GetInt32(1)));
}
}
}
}
// Subsectors
if (types.HasFlag(SearchResultsType.Subsectors))
{
// Note duplicated field names so the results of both queries can come out right.
string sql = string.Format(query_format, "TT.sector_x, TT.sector_y, TT.subsector_index", "sector_x, sector_y, subsector_index", "subsectors", where);
using (var sqlCommand = new SqlCommand(sql, connection))
{
for (int i = 0; i < terms.Count; ++i)
sqlCommand.Parameters.AddWithValue(string.Format("@term{0}", i), terms[i]);
using (var row = sqlCommand.ExecuteReader())
{
while (row.Read())
{
char[] chars = new char[1];
row.GetChars(2, 0, chars, 0, chars.Length);
results.Add(new SubsectorLocation(row.GetInt32(0), row.GetInt32(1), chars[0]));
}
}
}
}
// Worlds & UWPs, etc
if (types.HasFlag(SearchResultsType.Worlds))
{
// Note duplicated field names so the results of both queries can come out right.
string sql = string.Format(query_format, "TT.sector_x, TT.sector_y, TT.hex_x, TT.hex_y", "sector_x, sector_y, hex_x, hex_y", "worlds", where);
using (var sqlCommand = new SqlCommand(sql, connection))
{
for (int i = 0; i < terms.Count; ++i)
sqlCommand.Parameters.AddWithValue(string.Format("@term{0}", i), terms[i]);
using (var row = sqlCommand.ExecuteReader())
{
while (row.Read())
results.Add(new WorldLocation(row.GetInt32(0), row.GetInt32(1), (byte)row.GetInt32(2), (byte)row.GetInt32(3)));
}
}
}
// Labels
if (types.HasFlag(SearchResultsType.Labels))
{
// Note duplicated field names so the results of both queries can come out right.
string sql = string.Format(query_format, "TT.x, TT.y, TT.radius, TT.name", "x, y, radius, name", "labels", where);
using (var sqlCommand = new SqlCommand(sql, connection))
{
for (int i = 0; i < terms.Count; ++i)
sqlCommand.Parameters.AddWithValue(string.Format("@term{0}", i), terms[i]);
using (var row = sqlCommand.ExecuteReader())
{
while (row.Read())
{
results.Add(new LabelLocation(row.GetString(3), new Point(row.GetInt32(0), row.GetInt32(1)), row.GetInt32(2)));
}
}
}
}
}
return results;
}
public static IEnumerable <SearchResult> PerformSearch(string milieu, string query, SearchResultsType types, int maxResultsPerType, bool random = false)
{
List <SearchResult> results = new List <SearchResult>();
types = ParseQuery(query, types, out var clauses, out var terms);
if (clauses.Count() == 0 && !random)
{
return(results);
}
clauses.Insert(0, "milieu = @term");
terms.Insert(0, milieu ?? SectorMap.DEFAULT_MILIEU);
string where = string.Join(" AND ",
clauses.Select((clause, index) => "(" + clause.Replace("@term", $"@term{index}") + ")"));
string orderBy = random ? "ORDER BY NEWID()" : "";
// NOTE: DISTINCT is to filter out "Ley" and "Ley Sector" (different names, same result).
// * {0} is the list of distinct fields (i.e. coordinates),
// * {1} is the list of fields in the subquery (same as {0} but with "name" added,
// * {2} is the table
// * {3} is the filter (WHERE clause, not including "WHERE")
// * {4} is the order (ORDERBY clause)
// * {5} is the limit (TOP clause, not including "TOP")
// Since we need the distinct values from the term {0} but don't use the name for the results construction,
// we can ignore name in the resultset. This allows us to get the top N results from the database, sort by
// name, and then toss out duplicates not based on name but on the other, used, columns. Here's a sample
// subquery that works for the sector table:
// SELECT DISTINCT TOP 160 tt.x, tt.y
// FROM (SELECT TOP 160 x, y,name
// FROM sectors
// WHERE (name LIKE 'LEY%' OR name LIKE '%LEY%')
// ORDER BY name ASC) AS tt;
// TODO: Include the searched-for name in the results, and show alternate names in the result set.
string query_format = "SELECT DISTINCT TOP {5} {0} FROM (SELECT TOP {5} {1} FROM {2} WHERE {3} {4}) AS TT";
using (var connection = DBUtil.MakeConnection())
{
// Sectors
if (types.HasFlag(SearchResultsType.Sectors))
{
// Note duplicated field names so the results of both queries can come out right.
string sql = string.Format(query_format, "TT.x, TT.y", "x, y", "sectors", where, orderBy, maxResultsPerType);
using (var sqlCommand = new SqlCommand(sql, connection))
{
for (int i = 0; i < terms.Count; ++i)
{
sqlCommand.Parameters.AddWithValue($"@term{i}", terms[i]);
}
using (var row = sqlCommand.ExecuteReader())
{
while (row.Read())
{
results.Add(new SectorResult(row.GetInt32(0), row.GetInt32(1)));
}
}
}
}
// Subsectors
if (types.HasFlag(SearchResultsType.Subsectors))
{
// Note duplicated field names so the results of both queries can come out right.
string sql = string.Format(query_format, "TT.sector_x, TT.sector_y, TT.subsector_index", "sector_x, sector_y, subsector_index", "subsectors", where, orderBy, maxResultsPerType);
using (var sqlCommand = new SqlCommand(sql, connection))
{
for (int i = 0; i < terms.Count; ++i)
{
sqlCommand.Parameters.AddWithValue($"@term{i}", terms[i]);
}
using (var row = sqlCommand.ExecuteReader())
{
while (row.Read())
{
char[] chars = new char[1];
row.GetChars(2, 0, chars, 0, chars.Length);
results.Add(new SubsectorResult(row.GetInt32(0), row.GetInt32(1), chars[0]));
}
}
}
}
// Worlds & UWPs, etc
if (types.HasFlag(SearchResultsType.Worlds))
{
// Note duplicated field names so the results of both queries can come out right.
string sql = string.Format(query_format, "TT.sector_x, TT.sector_y, TT.hex_x, TT.hex_y", "sector_x, sector_y, hex_x, hex_y", "worlds", where, orderBy, maxResultsPerType);
using (var sqlCommand = new SqlCommand(sql, connection))
{
for (int i = 0; i < terms.Count; ++i)
{
sqlCommand.Parameters.AddWithValue($"@term{i}", terms[i]);
}
using (var row = sqlCommand.ExecuteReader())
{
while (row.Read())
{
results.Add(new WorldResult(row.GetInt32(0), row.GetInt32(1), (byte)row.GetInt32(2), (byte)row.GetInt32(3)));
}
}
}
}
// Labels
if (types.HasFlag(SearchResultsType.Labels))
{
// Note duplicated field names so the results of both queries can come out right.
string sql = string.Format(query_format, "TT.x, TT.y, TT.radius, TT.name", "x, y, radius, name", "labels", where, orderBy, maxResultsPerType);
using (var sqlCommand = new SqlCommand(sql, connection))
{
for (int i = 0; i < terms.Count; ++i)
{
sqlCommand.Parameters.AddWithValue($"@term{i}", terms[i]);
}
using (var row = sqlCommand.ExecuteReader())
{
while (row.Read())
{
results.Add(new LabelResult(row.GetString(3), new Point(row.GetInt32(0), row.GetInt32(1)), row.GetInt32(2)));
}
}
}
}
}
return(results);
}
public static IEnumerable <ItemLocation> PerformSearch(string query, ResourceManager resourceManager, SearchResultsType types, int numResults)
{
List <ItemLocation> results = new List <ItemLocation>();
using (var connection = DBUtil.MakeConnection())
{
List <string> clauses = new List <string>();
List <string> terms = new List <string>();
foreach (string t in query.Split((char[])null, StringSplitOptions.RemoveEmptyEntries))
{
string term = t;
string clause;
if (term.StartsWith("uwp:"))
{
term = term.Substring(term.IndexOf(':') + 1);
clause = "uwp LIKE @term";
types = SearchResultsType.UWP;
}
else if (term.StartsWith("exact:"))
{
term = term.Substring(term.IndexOf(':') + 1);
clause = "name LIKE @term";
}
else if (term.StartsWith("like:"))
{
term = term.Substring(term.IndexOf(':') + 1);
clause = "SOUNDEX(name) = SOUNDEX(@term)";
}
else if (term.Contains("%") || term.Contains("_"))
{
clause = "name LIKE @term";
}
else
{
clause = "name LIKE @term + '%' OR name LIKE '% ' + @term + '%'";
}
clause = clause.Replace("@term", String.Format("@term{0}", terms.Count));
clauses.Add("(" + clause + ")");
terms.Add(term);
}
string where = String.Join(" AND ", clauses.ToArray());
// NOTE: DISTINCT is to filter out "Ley" and "Ley Sector" (different names, same result).
// TODO: Include the searched-for name in the results, and show alternate names in the result set.
// {0} is the list of distinct fields (i.e. coordinates), {1} is the table, {2} is the filter
string query_format = "SELECT DISTINCT TOP " + numResults + " {0},name FROM {1} WHERE {2} ORDER BY name ASC";
// Sectors
if (types.HasFlag(SearchResultsType.Sectors) && numResults > 0)
{
string sql = String.Format(query_format, "x, y", "sectors", where);
using (var sqlCommand = new SqlCommand(sql, connection))
{
for (int i = 0; i < terms.Count; ++i)
{
sqlCommand.Parameters.AddWithValue(String.Format("@term{0}", i), terms[i]);
}
using (var row = sqlCommand.ExecuteReader())
{
while (row.Read())
{
results.Add(new SectorLocation(row.GetInt32(0), row.GetInt32(1)));
numResults -= 1;
}
}
}
}
// Subsectors
if (types.HasFlag(SearchResultsType.Subsectors) && numResults > 0)
{
string sql = String.Format(query_format, "sector_x, sector_y, subsector_index", "subsectors", where);
using (var sqlCommand = new SqlCommand(sql, connection))
{
for (int i = 0; i < terms.Count; ++i)
{
sqlCommand.Parameters.AddWithValue(String.Format("@term{0}", i), terms[i]);
}
using (var row = sqlCommand.ExecuteReader())
{
while (row.Read())
{
char[] chars = new char[1];
row.GetChars(2, 0, chars, 0, chars.Length);
results.Add(new SubsectorLocation(row.GetInt32(0), row.GetInt32(1), chars[0]));
}
}
}
}
// Worlds & UWPs
if ((types.HasFlag(SearchResultsType.Worlds) || types.HasFlag(SearchResultsType.UWP)) && numResults > 0)
{
string sql = String.Format(query_format, "sector_x, sector_y, hex_x, hex_y", "worlds", where);
using (var sqlCommand = new SqlCommand(sql, connection))
{
for (int i = 0; i < terms.Count; ++i)
{
sqlCommand.Parameters.AddWithValue(String.Format("@term{0}", i), terms[i]);
}
using (var row = sqlCommand.ExecuteReader())
{
while (row.Read())
{
results.Add(new WorldLocation(row.GetInt32(0), row.GetInt32(1), row.GetInt32(2), row.GetInt32(3)));
}
}
}
}
}
return(results);
}
public static IEnumerable<ItemLocation> PerformSearch(string query, ResourceManager resourceManager, SearchResultsType types, int numResults)
{
List<ItemLocation> results = new List<ItemLocation>();
using (var connection = DBUtil.MakeConnection())
{
List<string> clauses = new List<string>();
List<string> terms = new List<string>();
foreach (string t in query.Split((char[])null, StringSplitOptions.RemoveEmptyEntries))
{
string term = t;
string clause;
if (term.StartsWith("uwp:"))
{
term = term.Substring(term.IndexOf(':') + 1);
clause = "uwp LIKE @term";
types = SearchResultsType.UWP;
}
else if (term.StartsWith("exact:"))
{
term = term.Substring(term.IndexOf(':') + 1);
clause = "name LIKE @term";
}
else if (term.StartsWith("like:"))
{
term = term.Substring(term.IndexOf(':') + 1);
clause = "SOUNDEX(name) = SOUNDEX(@term)";
}
else if (term.Contains("%") || term.Contains("_"))
{
clause = "name LIKE @term";
}
else
{
clause = "name LIKE @term + '%' OR name LIKE '% ' + @term + '%'";
}
clause = clause.Replace("@term", String.Format("@term{0}", terms.Count));
clauses.Add("(" + clause + ")");
terms.Add(term);
}
string where = String.Join(" AND ", clauses.ToArray());
// NOTE: DISTINCT is to filter out "Ley" and "Ley Sector" (different names, same result).
// TODO: Include the searched-for name in the results, and show alternate names in the result set.
// {0} is the list of distinct fields (i.e. coordinates), {1} is the table, {2} is the filter
string query_format = "SELECT DISTINCT TOP " + numResults + " {0},name FROM {1} WHERE {2} ORDER BY name ASC";
// Sectors
if (types.HasFlag(SearchResultsType.Sectors) && numResults > 0)
{
string sql = String.Format(query_format, "x, y", "sectors", where);
using (var sqlCommand = new SqlCommand(sql, connection))
{
for (int i = 0; i < terms.Count; ++i)
{
sqlCommand.Parameters.AddWithValue(String.Format("@term{0}", i), terms[i]);
}
using (var row = sqlCommand.ExecuteReader())
{
while (row.Read())
{
results.Add(new SectorLocation(row.GetInt32(0), row.GetInt32(1)));
numResults -= 1;
}
}
}
}
// Subsectors
if (types.HasFlag(SearchResultsType.Subsectors) && numResults > 0)
{
string sql = String.Format(query_format, "sector_x, sector_y, subsector_index", "subsectors", where);
using (var sqlCommand = new SqlCommand(sql, connection))
{
for (int i = 0; i < terms.Count; ++i)
{
sqlCommand.Parameters.AddWithValue(String.Format("@term{0}", i), terms[i]);
}
using (var row = sqlCommand.ExecuteReader())
{
while (row.Read())
{
char[] chars = new char[1];
row.GetChars(2, 0, chars, 0, chars.Length);
results.Add(new SubsectorLocation(row.GetInt32(0), row.GetInt32(1), chars[0]));
}
}
}
}
// Worlds & UWPs
if ((types.HasFlag(SearchResultsType.Worlds) || types.HasFlag(SearchResultsType.UWP)) && numResults > 0)
{
string sql = String.Format(query_format, "sector_x, sector_y, hex_x, hex_y", "worlds", where);
using (var sqlCommand = new SqlCommand(sql, connection))
{
for (int i = 0; i < terms.Count; ++i)
{
sqlCommand.Parameters.AddWithValue(String.Format("@term{0}", i), terms[i]);
}
using (var row = sqlCommand.ExecuteReader())
{
while (row.Read())
{
results.Add(new WorldLocation(row.GetInt32(0), row.GetInt32(1), row.GetInt32(2), row.GetInt32(3)));
}
}
}
}
}
return results;
}
public static IEnumerable <ItemLocation> PerformSearch(string query, SearchResultsType types, int maxResultsPerType)
{
List <ItemLocation> results = new List <ItemLocation>();
List <string> clauses;
List <string> terms;
types = ParseQuery(query, types, out clauses, out terms);
if (clauses.Count() == 0)
{
return(results);
}
string where = string.Join(" AND ", clauses.ToArray());
// NOTE: DISTINCT is to filter out "Ley" and "Ley Sector" (different names, same result).
// TODO: Include the searched-for name in the results, and show alternate names in the result set.
// {0} is the list of distinct fields (i.e. coordinates), {1} is the list of fields in the subquery (same as {0} but with "name" added, {2} is the table, {3} is the filter
// Since we need the distinct values from the term {0} but don't use the name for the results construction, we can ignore name in the resultset.
// This allows us to get the top N results from the database, sort by name, and then toss out duplicates not based on name but on the other, used, columns
// Here's a sample subquery that works for the sector table.
// SELECT DISTINCT TOP 160 tt.x, tt.y FROM (SELECT TOP 160 x, y,name FROM sectors WHERE (name LIKE 'LEY%' OR name LIKE '%LEY%') ORDER BY name ASC) AS tt;
string query_format = "SELECT DISTINCT TOP " + maxResultsPerType + " {0} FROM (SELECT TOP " + maxResultsPerType + " {1} FROM {2} WHERE {3}) AS TT";
using (var connection = DBUtil.MakeConnection())
{
// Sectors
if (types.HasFlag(SearchResultsType.Sectors))
{
// Note duplicated field names so the results of both queries can come out right.
string sql = string.Format(query_format, "TT.x, TT.y", "x, y", "sectors", where);
using (var sqlCommand = new SqlCommand(sql, connection))
{
for (int i = 0; i < terms.Count; ++i)
{
sqlCommand.Parameters.AddWithValue(string.Format("@term{0}", i), terms[i]);
}
using (var row = sqlCommand.ExecuteReader())
{
while (row.Read())
{
results.Add(new SectorLocation(row.GetInt32(0), row.GetInt32(1)));
}
}
}
}
// Subsectors
if (types.HasFlag(SearchResultsType.Subsectors))
{
// Note duplicated field names so the results of both queries can come out right.
string sql = string.Format(query_format, "TT.sector_x, TT.sector_y, TT.subsector_index", "sector_x, sector_y, subsector_index", "subsectors", where);
using (var sqlCommand = new SqlCommand(sql, connection))
{
for (int i = 0; i < terms.Count; ++i)
{
sqlCommand.Parameters.AddWithValue(string.Format("@term{0}", i), terms[i]);
}
using (var row = sqlCommand.ExecuteReader())
{
while (row.Read())
{
char[] chars = new char[1];
row.GetChars(2, 0, chars, 0, chars.Length);
results.Add(new SubsectorLocation(row.GetInt32(0), row.GetInt32(1), chars[0]));
}
}
}
}
// Worlds & UWPs, etc
if (types.HasFlag(SearchResultsType.Worlds))
{
// Note duplicated field names so the results of both queries can come out right.
string sql = string.Format(query_format, "TT.sector_x, TT.sector_y, TT.hex_x, TT.hex_y", "sector_x, sector_y, hex_x, hex_y", "worlds", where);
using (var sqlCommand = new SqlCommand(sql, connection))
{
for (int i = 0; i < terms.Count; ++i)
{
sqlCommand.Parameters.AddWithValue(string.Format("@term{0}", i), terms[i]);
}
using (var row = sqlCommand.ExecuteReader())
{
while (row.Read())
{
results.Add(new WorldLocation(row.GetInt32(0), row.GetInt32(1), (byte)row.GetInt32(2), (byte)row.GetInt32(3)));
}
}
}
}
// Labels
if (types.HasFlag(SearchResultsType.Labels))
{
// Note duplicated field names so the results of both queries can come out right.
string sql = string.Format(query_format, "TT.x, TT.y, TT.radius, TT.name", "x, y, radius, name", "labels", where);
using (var sqlCommand = new SqlCommand(sql, connection))
{
for (int i = 0; i < terms.Count; ++i)
{
sqlCommand.Parameters.AddWithValue(string.Format("@term{0}", i), terms[i]);
}
using (var row = sqlCommand.ExecuteReader())
{
while (row.Read())
{
results.Add(new LabelLocation(row.GetString(3), new Point(row.GetInt32(0), row.GetInt32(1)), row.GetInt32(2)));
}
}
}
}
}
return(results);
}
