Exemple #1
0
        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()));
        }
Exemple #2
0
        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);
        }
Exemple #4
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"));
        }
Exemple #5
0
        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}"));
        }
Exemple #6
0
        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}"));
        }
Exemple #7
0
        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}"));
        }
Exemple #8
0
        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)"));
        }
Exemple #9
0
        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}"));
        }
Exemple #10
0
            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);
            }