public void MainInlineTest([DataSources] string context)
{
using var db = GetDataContext(context);
var q =
(
from c in
(
from c in db.Child
group c by c.ParentID into g
select new
{
ParentID = g.Key,
Count = g.Count()
}
)
.QueryName("Inline")
from p in db.Parent
where p.ParentID == c.ParentID
select new
{
p,
c.Count
}
)
.QueryName("Main")
;
_ = q.ToList();
Assert.That(LastQuery.Clean(), Contains.Substring("SELECT /* Main */".Clean()).Or.Contains("SELECT /*+ QB_NAME(Main) */".Clean()).Or.Contains("--Access"));
Assert.That(LastQuery.Clean(), Contains.Substring("SELECT /* Inline */".Clean()).Or.Contains("SELECT /*+ QB_NAME(Inline) */".Clean()).Or.Contains("--Access"));
Assert.That(LastQuery.Clean(), Is.Not.Contains("(SELECT /* Main */".Clean()).And.Not.Contains("(SELECT /*+ QB_NAME(Main) */".Clean()));
}
public void FromTest([DataSources] string context)
{
using var db = GetDataContext(context);
var q =
from p in db.Parent.QueryName("PARENT")
from c in db.Child.QueryName("CHILD")
where p.ParentID == c.ParentID
select p;
_ = q.ToList();
Assert.That(LastQuery.Clean(), Contains.Substring("FROM(SELECT /* PARENT */".Clean()).Or.Contains("FROM(SELECT /*+ QB_NAME(PARENT) */".Clean()).Or.Contains("--Access"));
Assert.That(LastQuery.Clean(), Contains.Substring(",(SELECT /* CHILD */".Clean()).Or.Contains(",(SELECT /*+ QB_NAME(CHILD) */".Clean()).Or.Contains("--Access"));
}
/// <summary>
/// This method represents the callback needed for sqlite3_exec.
/// Its c definition is: int (*callback)(void*,int,char**,char**)
/// This method is implictly called for every row which is part of the
/// result set.
/// </summary>
/// <param name="NotUsed">Not used</param>
/// <param name="argc">The number of columns.</param>
/// <param name="argv">The columns values (char**).</param>
/// <param name="azColName">The columns names (char**).</param>
/// <returns>Should always return 0.</returns>
protected int query(IntPtr NotUsed, int argc, IntPtr argv, IntPtr azColName)
{
var enc = Encoding.GetEncoding(SQLitePInvoke.SQLITE_ENCODING);
var row = new Dictionary <string, string>(argc);
for (int i = 0; i < argc; i++)
{
IntPtr pValue = Marshal.ReadIntPtr(argv, IntPtr.Size * i);
IntPtr pKey = Marshal.ReadIntPtr(azColName, IntPtr.Size * i);
string value = pValue.ToString(enc);
string key = pKey.ToString(enc);
row.Add(key, value);
}
LastQuery.Add(row);
return(0);
}
public void TablesInScopeHintTest(
[IncludeDataSources(true, TestProvName.AllSqlServer)] string context)
{
using var db = GetDataContext(context);
var q =
(
from p in db.Parent
from c in db.Child
from c1 in db.Child.AsSqlServer().WithIndex("IX_ChildIndex")
where c.ParentID == p.ParentID && c1.ParentID == p.ParentID
select p
)
.TablesInScopeHint(SqlServerHints.Table.NoLock);
q =
(
from p in q
from c in db.Child
from p1 in db.Parent.TablesInScopeHint(SqlServerHints.Table.HoldLock)
where c.ParentID == p.ParentID && c.Parent !.ParentID > 0 && p1.ParentID == p.ParentID
select p
)
.TablesInScopeHint(SqlServerHints.Table.NoWait);
q =
from p in q
from c in db.Child
where c.ParentID == p.ParentID
select p;
_ = q.ToList();
var test = LastQuery?.Replace("\r", "");
Assert.That(test, Contains.Substring("[Parent] [p] WITH (NoLock)"));
Assert.That(test, Contains.Substring("[Child] [c_1] WITH (NoLock)"));
Assert.That(test, Contains.Substring("[Child] [c_2] WITH (NoWait)"));
Assert.That(test, Contains.Substring("[Parent] [a_Parent] WITH (NoWait)"));
Assert.That(test, Contains.Substring("[Child] [c1] WITH (Index(IX_ChildIndex), NoLock)"));
Assert.That(test, Contains.Substring("[Parent] [p1] WITH (HoldLock)"));
Assert.That(test, Contains.Substring("[Child] [c_3]\n"));
}
public void QueryHintForUpdateSkipLockedTest([IncludeDataSources(true, TestProvName.AllPostgreSQL)] string context)
{
using var db = GetDataContext(context);
var q =
(
from p in db.Parent
join c in db.Child on p.ParentID equals c.ParentID
select p
)
.AsPostgreSQL()
.ForUpdateSkipLockedHint();
_ = q.ToList();
var skipLocked = LastQuery.Contains(ProviderName.PostgreSQL95) ? " SKIP LOCKED" : "";
Assert.That(LastQuery, Contains.Substring($"{PostgreSQLHints.ForUpdate}{skipLocked}"));
}
public void QueryHintForKeyShareNoWaitTest2([IncludeDataSources(true, TestProvName.AllPostgreSQL)] string context)
{
using var db = GetDataContext(context);
var q =
(
from p in db.Parent.TableID("Pr")
join c in db.Child.TableID("Ch") on p.ParentID equals c.ParentID
select p
)
.AsPostgreSQL()
.ForKeyShareNoWaitHint(Sql.TableAlias("Pr"), Sql.TableAlias("Ch"));
_ = q.ToList();
if (LastQuery.Contains(ProviderName.PostgreSQL92))
{
return;
}
Assert.That(LastQuery, Contains.Substring($"{PostgreSQLHints.ForKeyShare} OF p, c_1 {PostgreSQLHints.NoWait}"));
}
public void QueryHintForKeyShareTest([IncludeDataSources(true, TestProvName.AllPostgreSQL)] string context)
{
using var db = GetDataContext(context);
var q =
(
from p in db.Parent
join c in db.Child on p.ParentID equals c.ParentID
select p
)
.AsPostgreSQL()
.ForKeyShareHint();
_ = q.ToList();
if (LastQuery.Contains(ProviderName.PostgreSQL92))
{
return;
}
Assert.That(LastQuery, Contains.Substring($"{PostgreSQLHints.ForKeyShare}"));
}
public void TablesInScopeHintTest([IncludeDataSources(true, ProviderName.SqlCe)] string context)
{
using var db = GetDataContext(context);
var q =
(
from p in db.Person.TableHint(SqlCeHints.Table.Index, "PK_Person")
from c in db.Child
where c.ParentID == p.ID
select p
)
.TablesInScopeHint(SqlCeHints.Table.NoLock);
q =
(
from p in q
from c in db.Child
from p1 in db.Parent.TablesInScopeHint(SqlCeHints.Table.HoldLock)
where c.ParentID == p.ID && c.Parent !.ParentID > 0 && p1.ParentID == p.ID
select p
)
.TablesInScopeHint(SqlCeHints.Table.PagLock);
q =
from p in q
from c in db.Child
where c.ParentID == p.ID
select p;
_ = q.ToList();
var test = LastQuery?.Replace("\r", "");
Assert.That(test, Contains.Substring("[Person] [p] WITH (Index(PK_Person), NoLock)"));
Assert.That(test, Contains.Substring("[Child] [c_1] WITH (NoLock)"));
Assert.That(test, Contains.Substring("[Parent] [p1] WITH (HoldLock)"));
Assert.That(test, Contains.Substring("[Child] [c_2] WITH (PagLock)"));
Assert.That(test, Contains.Substring("[Parent] [a_Parent] WITH (PagLock)"));
}
public void QueryHintForNoKeyUpdateSkipLockedTest2([IncludeDataSources(true, TestProvName.AllPostgreSQL)] string context)
{
using var db = GetDataContext(context);
var q =
(
from p in db.Parent.TableID("Pr")
join c in db.Child.TableID("Ch") on p.ParentID equals c.ParentID
select p
)
.AsPostgreSQL()
.ForNoKeyUpdateSkipLockedHint(Sql.TableAlias("Pr"), Sql.TableAlias("Ch"));
_ = q.ToList();
if (LastQuery.Contains(ProviderName.PostgreSQL92))
{
return;
}
var skipLocked = LastQuery.Contains(ProviderName.PostgreSQL95) ? " SKIP LOCKED" : "";
Assert.That(LastQuery, Contains.Substring($"{PostgreSQLHints.ForNoKeyUpdate} OF p, c_1{skipLocked}"));
}
public async Task <LastQuery> Search()
{
// Get user input
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("Performing Search");
Console.ForegroundColor = ConsoleColor.White;
Console.WriteLine("Enter keywords: ");
var userQuery = Console.ReadLine();
// var userQuery = "laptop";
Console.WriteLine("Enter filters:");
var userAdvancedQuery = Console.ReadLine();
// var userAdvancedQuery = "";
// Build Facets
var facet = new GroupByParams {
field = FACET,
maximumNumberOfValues = 10,
sortCriteria = "occurrences",
injectionDepth = 1000,
completeFacetWithStandardValues = true,
};
GroupByParams[] facets = new GroupByParams[1];
facets.SetValue(facet, 0);
// Build Params
var queryParam = new QueryParams {
q = userQuery,
aq = userAdvancedQuery,
organizationId = this.orgId,
searchHub = SEARCH_HUB,
groupBy = facets
};
// Build Request
var url = SEARCH_ENDPOINT;
var body = new StringContent(JsonConvert.SerializeObject(queryParam), Encoding.UTF8, "application/json");
// Get Response
var response = await PostAsync(url, body);
response.EnsureSuccessStatusCode();
var responseString = response.Content.ReadAsStringAsync();
JObject responseJson = JObject.Parse(responseString.Result);
// Store Query Info
var lastQuery = new LastQuery
{
searchid = responseJson["searchUid"].ToString(),
keyword = queryParam.q,
aq = queryParam.aq,
responseTime = int.Parse(responseJson["duration"].ToString()),
totalCount = int.Parse(responseJson["totalCount"].ToString()),
results = responseJson
};
// Print Query Info
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("✅ Query Success");
Console.ForegroundColor = ConsoleColor.Gray;
Console.WriteLine($"Results: {responseJson["totalCount"]} Response Time: {responseJson["duration"]}ms");
Console.WriteLine($" ");
// Print Facet Info
foreach (var facetResult in responseJson["groupByResults"])
{
Console.WriteLine($"Filter {facetResult["field"]}");
foreach (var facetValue in facetResult["values"])
{
Console.WriteLine($"{facetValue["numberOfResults"]} - {facetValue["value"]} ");
}
}
Console.WriteLine($" ");
// Print Result List
Console.ForegroundColor = ConsoleColor.White;
var i = 1;
foreach (var result in responseJson["results"])
{
Console.WriteLine($"{i} | {result["raw"]["coveodpunifiedbrand"].ToString()} | {result["title"].ToString()} | ${result["raw"]["dpretailprice"]} ➜ ${result["raw"]["dpsaleprice"]}");
Console.WriteLine($" ");
i++;
}
// Return last query for UA
this.lastQuery = lastQuery;
return(lastQuery);
}
