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