private static void Main() { SelectQuery.Execute(); JoinQuery.Execute(); GroupByQuery.Execute(); OrderBy.Execute(); SubQuery.Execute(); }
public void SecureDatabase_JoinQuerySecurity() { SecureDatabase db = BuildSampleDB(); SecureSampleDB(db); JoinQuery <SelectResult> q = new JoinQuery <SelectResult>(db, new SelectQuery() { TableName = sampleTableName, Columns = new string[] { "*" } }, new SelectQuery[] { new SelectQuery() { TableName = sampleTableName, Where = QueryParser.Parse("One") }, new SelectQuery() { TableName = sampleTableName, Where = QueryParser.Parse("SecretOwner=Bob") } }); SelectResult result; // Run a JOIN with full access - verify success (inner query expanded). q.Where = QueryParser.Parse("SecretPriority = 1 AND SecretOwner = #Q2[SecretOwner]"); result = db.Query(q, (si) => si.Name == "g1" || si.Name == "g2"); Assert.IsTrue(result.Details.Succeeded); Assert.AreEqual("[SecretPriority] = 1 AND [SecretOwner] = IN(Bob, Bob, Bob)", result.Query.Where.ToString()); // Run a JOIN with a disallowed clause on the top query - verify error q.Where = QueryParser.Parse("SecretPriority = 1 AND SecretOwner = #Q1[SecretOwner]"); result = db.Query(q, (si) => si.Name == "g2"); Assert.IsFalse(result.Details.Succeeded); Assert.AreEqual("", result.Query.Where.ToString()); Assert.AreEqual(String.Format(ExecutionDetails.DisallowedColumnQuery, "SecretOwner"), result.Details.Errors); // Run a JOIN where the join column in the outer query is disallowed - verify error q.Where = QueryParser.Parse("ID > 1 AND SecretOwner = #Q1[ID]"); result = db.Query(q, (si) => false); Assert.IsFalse(result.Details.Succeeded); Assert.AreEqual("", result.Query.Where.ToString()); Assert.AreEqual(String.Format(ExecutionDetails.DisallowedColumnQuery, "SecretOwner"), result.Details.Errors); // Run a JOIN where an inner query uses a disallowed clause - verify error q.Where = QueryParser.Parse("ID > 1 AND ID = #Q2[ID]"); result = db.Query(q, (si) => false); Assert.IsFalse(result.Details.Succeeded); Assert.AreEqual("", result.Query.Where.ToString()); Assert.AreEqual(String.Format(ExecutionDetails.DisallowedColumnQuery, "SecretOwner"), result.Details.Errors); // Run a JOIN where no secured columns are accessed - verify success (inner query expanded) q.Where = QueryParser.Parse("ID > 1 AND ID = #Q1[ID]"); result = db.Query(q, (si) => false); Assert.IsTrue(result.Details.Succeeded); Assert.AreEqual("[ID] > 1 AND [ID] = IN(1)", result.Query.Where.ToString()); }
/// <summary> /// Clone a new join item /// </summary> /// <returns></returns> public JoinItem Clone() { return(new JoinItem() { Sort = Sort, JoinType = JoinType, JoinFields = JoinFields?.ToDictionary(c => c.Key, c => c.Value), JoinQuery = JoinQuery?.Clone(), ExtraQuery = ExtraQuery?.Clone(), Operator = Operator }); }
/// <summary> /// Gets the joined data. /// </summary> /// <param name="context"> /// The execution context. /// </param> /// <param name="query"> /// The query. /// </param> /// <returns> /// A data set containing the rows retrieved from the source. /// </returns> protected override IAsyncEnumerable <Row> GetRows(IInternalExecutionContext context, JoinQuery query) { throw new NotImplementedException(); }
/// <summary> /// Retrieves the data from the source as an <see cref="IAsyncEnumerable{T}"/>. /// </summary> /// <param name="context"> /// The context. /// </param> /// <param name="query"> /// The query expression. /// </param> /// <returns> /// A task returning the rows. /// </returns> protected abstract IAsyncEnumerable <Row> GetRows(IInternalExecutionContext context, JoinQuery query);
public void Database_Join() { Database db = new Database(); Table people = db.AddTable("People", 1000); people.AddOrUpdate(new DataBlock(new string[] { "Alias", "Name", "Team", "Groups" }, 3, new Array[] { new string[] { "mikefan", "rtaket", "v-scolo" }, new string[] { "Michael Fanning", "Ryley Taketa", "Scott Louvau" }, new string[] { "T1", "T1", "T2" }, new string[] { "G1; G2", "G1; G3", "G4" } }), new AddOrUpdateOptions() { AddMissingColumns = true }); Table orders = db.AddTable("Orders", 1000); orders.AddOrUpdate(new DataBlock(new string[] { "OrderNumber", "OrderedByAlias" }, 6, new Array[] { new string[] { "O1", "O2", "O3", "O4", "O5", "O6" }, new string[] { "mikefan", "mikefan", "rtaket", "v-scolo", "rtaket", "mikefan; rtaket" } }), new AddOrUpdateOptions() { AddMissingColumns = true }); SelectResult result; // Get Orders where OrderedByAlias is any Alias matching "T1" in People (equals JOIN) JoinQuery <SelectResult> q = new JoinQuery <SelectResult>( db, new SelectQuery() { Where = SelectQuery.ParseWhere("OrderedByAlias=#Q1[Alias]"), TableName = "Orders", Columns = new string[] { "OrderNumber" } }, new SelectQuery() { Where = SelectQuery.ParseWhere("T1"), TableName = "People" } ); q.Correct(null); result = db.Query(q); Assert.AreEqual("O5, O3, O2, O1", JoinResultColumn(result)); // Get People where Groups contains a Group Mike is in (contains self JOIN) q = new JoinQuery <SelectResult>( db, new SelectQuery() { Where = SelectQuery.ParseWhere("Groups:#Q1[Groups]"), TableName = "People", Columns = new string[] { "Alias" } }, new SelectQuery() { Where = SelectQuery.ParseWhere("mikefan"), TableName = "People" } ); q.Correct(null); result = db.Query(q); Assert.AreEqual("rtaket, mikefan", JoinResultColumn(result)); // TODO: // Unknown column in Join query // Reference Join query out of range // Join returns nothing // Join returns too many // Select column from Join is unknown // Nested Join }
/// <summary> /// Retrieves records for the join. /// </summary> /// <param name="context"> /// The execution context. /// </param> /// <param name="query"> /// The query. /// </param> /// <returns> /// A data set containing the rows retrieved from the source. /// </returns> protected override IAsyncEnumerable <Row> GetRows(IInternalExecutionContext context, [NotNull] JoinQuery query) { var rowBuilder = new RowBuilder(); return(context.CreateAsyncEnumerable( async() => { var leftData = await this.Left.GetRows(context, query.LeftQuery).MaterializeAsync().ConfigureAwait(false); var extraFilter = (await new[] { query.JoinExpression, }.ToRangedExpressionAsync(leftData, this.Right.Aliases).ConfigureAwait(false)).Select(JoinSource.RangesToJoinFilter).First(); var rightData = await this.Right.GetRows(context, query.RightQuery.ReplaceFilter(extraFilter)).MaterializeAsync().ConfigureAwait(false); return this.IsInnerJoin ? leftData.PreSortedJoin(rightData, query.LeftKey, query.JoinType, query.RightKey, query.JoinFilter, rowBuilder.CombineRows) : leftData.PreSortedLeftJoin(rightData, query.LeftKey, query.JoinType, query.RightKey, query.JoinFilter, rowBuilder.CombineRows); }) .Where(query.ResultFilter) .OrderBy(query.OrderBy)); }
/// <summary> /// Retrieves the data from the source as an <see cref="IAsyncEnumerable{T}"/>. /// </summary> /// <param name="context"> /// The context. /// </param> /// <param name="query"> /// The query expression. /// </param> /// <returns> /// A task returning the rows. /// </returns> protected override IAsyncEnumerable <Row> GetRows(IInternalExecutionContext context, [NotNull] JoinQuery query) { var rowBuilder = new RowBuilder(); return(this.Left.GetRows(context, query.LeftQuery) .CrossJoin(this.Right.GetRows(context, query.RightQuery), rowBuilder.CombineRows) .Where(query.ResultFilter?.GetRowFilter()) .OrderBy(query.OrderBy)); }
/// <summary> /// Retrieves the data from the source as an <see cref="IAsyncEnumerable{T}"/>. /// </summary> /// <param name="context"> /// The context. /// </param> /// <param name="query"> /// The query expression. /// </param> /// <returns> /// A task returning the rows. /// </returns> protected override IAsyncEnumerable <Row> GetRows(IInternalExecutionContext context, [NotNull] JoinQuery query) { var rowBuilder = new RowBuilder(); var leftRows = this.Left.GetRows(context, query.LeftQuery); var rightRows = this.Right.GetRows(context, query.RightQuery); return (this.isInnerJoin ? leftRows.Zip(rightRows, rowBuilder.CombineRows) : leftRows.ZipAll(rightRows, rowBuilder.CombineRows) .Where(query.ResultFilter?.GetRowFilter()) .OrderBy(query.OrderBy)); }