private static void AggregateTest(Table table)
{
int iterations = 1000;
AggregationQuery query = new AggregationQuery();
query.Aggregator = new Model.Aggregations.SumAggregator();
query.AggregationColumns = new string[] { "ID" };
query.TableName = "Bugs";
query.Where = SelectQuery.ParseWhere("\"Created Date\" < \"2013-01-01\"");
query.Dimensions.Add(new AggregationDimension("Priority", "Priority < 0", "Priority = 0", "Priority = 1", "Priority > 1"));
query.Dimensions.Add(new AggregationDimension("Framework", "Maui", "Apex", "Tao"));
//query.Dimensions.Add(new AggregationDimension("Created Month",
// "\"Created Date\" >= \"2011-01-01\" AND \"Created Date\" < \"2011-02-01\"",
// "\"Created Date\" >= \"2011-02-01\" AND \"Created Date\" < \"2011-03-01\"",
// "\"Created Date\" >= \"2011-03-01\" AND \"Created Date\" < \"2011-04-01\""
//));
AggregationResult result = null;
Console.WriteLine("\r\n{0}\r\n", query);
Trace.Write("Aggregating Bugs...");
for (int i = 0; i < iterations; ++i)
{
result = table.Query(query);
}
Console.WriteLine();
ShowResult(result.Values);
}
private IResponse AllCount(IRequestContext ctx, Route route)
{
string queryString = ctx.Request.ResourceParameters["q"] ?? "";
AllCountResult result = new AllCountResult(queryString);
// Build a Count query
IQuery<AggregationResult> query = new AggregationQuery("count", null, queryString);
// Wrap in Joins, if found
query = WrapInJoinQueryIfFound(query, this.Database, ctx);
// Run server correctors
using (ctx.Monitor(MonitorEventLevel.Verbose, "Correct", type: "AllCount", detail: query.Where.ToString()))
{
query.Correct(this.CurrentCorrectors(ctx));
}
// Accumulate Results for each table
IPrincipal user = ctx.Request.User;
using (ctx.Monitor(MonitorEventLevel.Information, "AllCount", type: "AllCount", detail: query.Where.ToString()))
{
IExpression defaultWhere = query.Where;
foreach (string tableName in this.Database.TableNames)
{
if (this.HasTableAccess(tableName, user, PermissionScope.Reader))
{
query.TableName = tableName;
query.Where = defaultWhere;
AggregationResult tableCount = this.Database.Query(query, (si) => this.IsInIdentity(ctx.Request.User, si));
if (!tableCount.Details.Succeeded || tableCount.Values == null)
{
result.ResultsPerTable.Add(new CountResult(tableName, 0, true, false));
}
else
{
result.ResultsPerTable.Add(new CountResult(tableName, (ulong)tableCount.Values[0, 0], true, tableCount.Details.Succeeded));
}
}
else
{
result.ResultsPerTable.Add(new CountResult(tableName, 0, false, false));
}
}
}
// Sort results so that succeeding tables are first and are subsorted by count [descending]
result.ResultsPerTable.Sort((left, right) =>
{
int order = right.Succeeded.CompareTo(left.Succeeded);
if (order != 0) return order;
return right.Count.CompareTo(left.Count);
});
return ArribaResponse.Ok(result);
}
public void PostUsersAggregatesQueryTest()
{
// TODO: add unit test for the method 'PostUsersAggregatesQuery'
AggregationQuery body = null; // TODO: replace null with proper value
var response = instance.PostUsersAggregatesQuery(body);
Assert.IsInstanceOf <PresenceQueryResponse> (response, "response is PresenceQueryResponse");
}
public void ITable_Aggregate_Sum(Func <ITable> factoryMethod)
{
// Define columns and add sample data
ITable table = factoryMethod();
AddSampleData(table);
AggregationQuery query = new AggregationQuery();
AggregationResult result;
query.Aggregator = new SumAggregator();
query.AggregationColumns = new string[] { "ID" };
query.Where = SelectQuery.ParseWhere("ID < 12000");
// Dimensionless Aggregation - verify four bugs aggregated, one cell returned with sum of IDs
result = table.Query(query);
Assert.AreEqual(4, (int)result.Total);
Assert.AreEqual(1, result.Values.ColumnCount);
Assert.AreEqual(1, result.Values.RowCount);
Assert.AreEqual((long)47097, result.Values[0, 0]);
// One dimension - verify values split out and are correct; dimension queries echoed
query.Dimensions.Add(new AggregationDimension("[Priority]", "[Priority] < 2", "[Priority] >= 2"));
result = table.Query(query);
AssertBlockEquals(result.Values, @"
[Priority] < 2, 23455
[Priority] >= 2, 23642
, 47097
");
// Two dimensions - verify values split on both; values are correct
query.Dimensions.Add(new AggregationDimension("[Title]", "[Title]:One | [Title]:Two", "-([Title]:One | [Title]:Two)"));
result = table.Query(query);
AssertBlockEquals(result.Values, @"
[Priority] < 2, [Title]:One OR [Title]:Two, 11512
[Priority] < 2, NOT([Title]:One OR [Title]:Two), 11943
[Priority] < 2, , 23455
[Priority] >= 2, [Title]:One OR [Title]:Two, 11643
[Priority] >= 2, NOT([Title]:One OR [Title]:Two), 11999
[Priority] >= 2, , 23642
, [Title]:One OR [Title]:Two, 23155
, NOT([Title]:One OR [Title]:Two), 23942
, , 47097
");
// Add an empty cell and re-check
query.Dimensions.Clear();
query.Dimensions.Add(new AggregationDimension("Title", "Title:unused"));
result = table.Query(query);
AssertBlockEquals(result.Values, @"
[Title]:unused, null
, 47097
");
}
private void TestAggregations <T>(string columnType, T[] ascending, string sum)
{
DataBlock block = new DataBlock(new string[] { "ID" }, ascending.Length);
block.SetColumn(0, ascending);
// Add to a table with the desired column type, big enough to have multiple partitions
Table table = new Table("Sample", 100000);
table.AddColumn(new ColumnDetails("ID", columnType, null, "", true));
table.AddOrUpdate(block);
// Build an AggregationQuery
AggregationQuery q = new AggregationQuery();
q.AggregationColumns = new string[] { "ID" };
AggregationResult result;
// Verify the Max equals the last value from the array
q.Aggregator = new MaxAggregator();
result = table.Query(q);
Assert.AreEqual(ascending[ascending.Length - 1].ToString(), result.Values[0, 0].ToString());
// Verify the Min equals the first value from the array
q.Aggregator = new MinAggregator();
result = table.Query(q);
Assert.AreEqual(ascending[0].ToString(), result.Values[0, 0].ToString());
// Verify the Count equals the rows added
q.Aggregator = new CountAggregator();
result = table.Query(q);
Assert.AreEqual(ascending.Length.ToString(), result.Values[0, 0].ToString());
// Verify the Sum equals the sum from the array [if computable]
q.Aggregator = new SumAggregator();
if (sum == null)
{
Verify.Exception <NotImplementedException>(() => table.Query(q));
}
else
{
result = table.Query(q);
Assert.AreEqual(sum, result.Values[0, 0].ToString());
}
// Verify BaseAggregator throws NotImplemented [the default]
q.Aggregator = new BaseAggregator();
Verify.Exception <NotImplementedException>(() => table.Query(q));
}
public void ITable_Empty(Func <ITable> factoryMethod)
{
// Define columns and but DO NOT add sample data
ITable table = factoryMethod();
AddColumns(table);
// Verify select sorted column search returns nothing
SelectQuery selectQuery = new SelectQuery();
selectQuery.Columns = new string[] { "ID", "Priority" };
selectQuery.Count = 3;
selectQuery.Where = SelectQuery.ParseWhere("[ID] > -1");
SelectResult selectResult = table.Query(selectQuery);
Assert.AreEqual(0, (int)selectResult.Total);
// Verify select word match returns nothing
selectQuery.Where = SelectQuery.ParseWhere("Title:One");
selectResult = table.Query(selectQuery);
Assert.AreEqual(0, (int)selectResult.Total);
// Verify count returns 0
AggregationQuery aggregateQuery = new AggregationQuery();
aggregateQuery.Aggregator = new CountAggregator();
aggregateQuery.AggregationColumns = new string[] { "ID" };
AggregationResult aggregateResult = table.Query(aggregateQuery);
Assert.AreEqual((ulong)0, aggregateResult.Values[0, 0]);
// Verify sum returns null [can't merge correctly if there are no values]
aggregateQuery.Aggregator = new SumAggregator();
aggregateResult = table.Query(aggregateQuery);
Assert.AreEqual(null, aggregateResult.Values[0, 0]);
// Verify Min/Max return null
aggregateQuery.Aggregator = new MinAggregator();
aggregateResult = table.Query(aggregateQuery);
Assert.IsNull(aggregateResult.Values[0, 0]);
aggregateQuery.Aggregator = new MaxAggregator();
aggregateResult = table.Query(aggregateQuery);
Assert.IsNull(aggregateResult.Values[0, 0]);
}
public void Aggregator_RequireMerge()
{
Table t = new Table("Sample", 100);
TableTests.AddSampleData(t);
var aggregator = new AggregatorNeedingMerge();
var aq = new AggregationQuery();
aq.Aggregator = aggregator;
aggregator.RequireMerge = false;
AggregationResult result = t.Query(aq);
Assert.AreEqual((int)result.Values[0, 0], 2);
aggregator.RequireMerge = true;
result = t.Query(aq);
Assert.AreEqual((int)result.Values[0, 0], 1);
}
private static bool CompareAggregate(Table expected, Table actual, AggregationQuery query)
{
StringBuilder differences = new StringBuilder();
AggregationResult resultExpected = expected.Query(query);
AggregationResult resultActual = actual.Query(query);
if (resultExpected.Total != resultActual.Total)
{
differences.AppendLine(String.Format("Total; Expect: {0:n0}, Actual {1:n0}.", resultExpected.Total, resultActual.Total));
}
CompareValues(resultExpected.Values, resultActual.Values, differences);
if (differences.Length > 0)
{
Trace.WriteLine(String.Format("{0}\r\n========================================\r\n{1}\r\n========================================\r\n\r\n", query, differences));
}
return(differences.Length == 0);
}
private static async Task ExampleShort()
{
// Connect to local Arriba server
using (var client = new ArribaClient(ArribaServerUrl))
{
// Create Table [2 Partition Bits = 4 Partitions = 256k items]
CreateTableRequest tableRequest = new CreateTableRequest("Test_" + DateTime.Now.Ticks.ToString(), 2);
tableRequest.Columns.Add(new ColumnDetails("ID", "int", -1, String.Empty, true));
tableRequest.Columns.Add(new ColumnDetails("Name", "string", null));
tableRequest.Columns.Add(new ColumnDetails("Age", "int", -1));
var table = await client.CreateTableAsync(tableRequest);
// Add or Update items (CSV) [ID,Name,Age]
using (MemoryStream ms = BuildSampleUserCsv())
{
await table.ImportFileAsync(ms, "csv");
}
// Select ID, Name WHERE Age = 32
SelectResult selectResult = await table.Select(new SelectQuery(new string[] { "ID" }, "Age = 32"));
Console.WriteLine("Found {0:n0} 32 year olds (expected 2)", selectResult.Total);
// Aggregate COUNT(*) WHERE {ALL} BY Age < 30, Age >= 30
AggregationQuery aggregateQuery = new AggregationQuery("Count", new string[] { "ID" }, "");
aggregateQuery.Dimensions.Add(new AggregationDimension("Age", "Age < 30", "Age >= 30"));
AggregationResult aggregateResult = await table.Aggregate(aggregateQuery);
Console.WriteLine("Found {0:n0} under 30 year olds (expected 1)", aggregateResult.Values[0, 1]);
Console.WriteLine("Found {0:n0} over 30 year olds (expected 3)", aggregateResult.Values[1, 1]);
Console.WriteLine("Found {0:n0} altogether (expected 4)", aggregateResult.Values[2, 1]);
// Delete WHERE Age < 30
int countDeleted = await table.Delete(SelectQuery.ParseWhere("Age < 30"));
Console.WriteLine("Deleted {0:n0} users (expected 1)", countDeleted);
}
}
private static async Task <AggregationQuery> BuildAggregateFromContext(IRequestContext ctx)
{
if (ctx.Request.Method == RequestVerb.Post && ctx.Request.HasBody)
{
return(await ctx.Request.ReadBodyAsync <AggregationQuery>());
}
string aggregationFunction = ctx.Request.ResourceParameters["a"] ?? "count";
string columnName = ctx.Request.ResourceParameters["col"];
string queryString = ctx.Request.ResourceParameters["q"];
AggregationQuery query = new AggregationQuery();
query.Aggregator = AggregationQuery.BuildAggregator(aggregationFunction);
query.AggregationColumns = String.IsNullOrEmpty(columnName) ? null : new string[] { columnName };
using (ctx.Monitor(MonitorEventLevel.Verbose, "Arriba.ParseQuery", String.IsNullOrEmpty(queryString) ? "<none>" : queryString))
{
query.Where = String.IsNullOrEmpty(queryString) ? new AllExpression() : SelectQuery.ParseWhere(queryString);
}
for (char dimensionPrefix = 'd'; ctx.Request.ResourceParameters.Contains(dimensionPrefix.ToString() + "1"); ++dimensionPrefix)
{
List <string> dimensionParts = ReadParameterSet(ctx.Request, dimensionPrefix.ToString());
if (dimensionParts.Count == 1 && dimensionParts[0].EndsWith(">"))
{
query.Dimensions.Add(new DistinctValueDimension(QueryParser.UnwrapColumnName(dimensionParts[0].TrimEnd('>'))));
}
else
{
query.Dimensions.Add(new AggregationDimension("", dimensionParts));
}
}
return(query);
}
private AggregationQuery BuildAggregateFromContext(IRequestContext ctx, NameValueCollection p)
{
string aggregationFunction = p["a"] ?? "count";
string columnName = p["col"];
string queryString = p["q"];
AggregationQuery query = new AggregationQuery();
query.Aggregator = AggregationQuery.BuildAggregator(aggregationFunction);
query.AggregationColumns = String.IsNullOrEmpty(columnName) ? null : new string[] { columnName };
using (ctx.Monitor(MonitorEventLevel.Verbose, "Arriba.ParseQuery", String.IsNullOrEmpty(queryString) ? "<none>" : queryString))
{
query.Where = String.IsNullOrEmpty(queryString) ? new AllExpression() : SelectQuery.ParseWhere(queryString);
}
for (char dimensionPrefix = 'd'; true; ++dimensionPrefix)
{
List <string> dimensionParts = ReadParameterSet(p, dimensionPrefix.ToString());
if (dimensionParts.Count == 0)
{
break;
}
if (dimensionParts.Count == 1 && dimensionParts[0].EndsWith(">"))
{
query.Dimensions.Add(new DistinctValueDimension(QueryParser.UnwrapColumnName(dimensionParts[0].TrimEnd('>'))));
}
else
{
query.Dimensions.Add(new AggregationDimension("", dimensionParts));
}
}
return(query);
}
public AggregationResult(AggregationQuery query) : base(query)
{
}
public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
{
return(AggregationQuery.BuildAggregator((string)reader.Value));
}
private async Task ExecuteReport()
{
QueryErrorMessage.Value = "";
IsErrorVisible.Value = false;
if (string.IsNullOrEmpty(GroupByField.Value))
{
ApplicationModel.Current.Notifications.Add(new Notification("You must select a field to group by"));
return;
}
if (ValueCalculations.Count == 0)
{
ApplicationModel.Current.Notifications.Add(new Notification("You must add at least one Value"));
return;
}
var facets = new List <AggregationQuery>();
foreach (var value in ValueCalculations)
{
var facetForField = facets.FirstOrDefault(f => f.AggregationField == value.Field);
if (facetForField != null)
{
facetForField.Aggregation |= value.SummaryMode;
}
else
{
facets.Add(new AggregationQuery
{
Name = GroupByField.Value,
DisplayName = GroupByField.Value + "-" + value.Field,
AggregationField = value.Field,
Aggregation = value.SummaryMode
});
}
}
ResultColumns = null;
Results.Clear();
var cancelationTokenSource = new CancellationTokenSource();
var progressWindow = new ProgressWindow()
{
Title = "Preparing Report",
IsIndeterminate = false,
CanCancel = true
};
progressWindow.Closed += delegate { cancelationTokenSource.Cancel(); };
progressWindow.Show();
var queryStartTime = DateTime.UtcNow.Ticks;
try
{
var results = new List <KeyValuePair <string, FacetResult> >();
var hasMoreResults = true;
var fetchedResults = 0;
while (hasMoreResults)
{
var queryFacetsTask = DatabaseCommands.GetFacetsAsync(IndexName,
new IndexQuery()
{
Query = FilterDoc.Text
},
AggregationQuery.GetFacets(facets),
fetchedResults, 256);
await TaskEx.WhenAny(
queryFacetsTask,
TaskEx.Delay(int.MaxValue, cancelationTokenSource.Token));
if (cancelationTokenSource.IsCancellationRequested)
{
return;
}
var facetResults = await queryFacetsTask;
results.AddRange(facetResults.Results);
fetchedResults += facetResults.Results.Select(r => r.Value.Values.Count).Max();
var remainingResults = facetResults.Results.Select(r => r.Value.RemainingTermsCount).Max();
var totalResults = fetchedResults + remainingResults;
progressWindow.Progress = (int)((fetchedResults / (double)totalResults) * 100);
hasMoreResults = remainingResults > 0;
}
var rowsByKey = new Dictionary <string, ReportRow>();
var rows = new List <ReportRow>();
foreach (var facetResult in results)
{
var calculatedField = facetResult.Key.Split('-')[1];
foreach (var facetValue in facetResult.Value.Values)
{
ReportRow result;
if (!rowsByKey.TryGetValue(facetValue.Range, out result))
{
result = new ReportRow {
Key = facetValue.Range
};
rowsByKey.Add(result.Key, result);
rows.Add(result);
}
foreach (
var valueCalculation in ValueCalculations.Where(v => v.Field == calculatedField))
{
var value = facetValue.GetAggregation(valueCalculation.SummaryMode);
if (value.HasValue)
{
result.Values.Add(valueCalculation.Header,
facetValue.GetAggregation(valueCalculation.SummaryMode) ?? 0);
}
}
}
}
var columns = new ColumnsModel();
columns.Columns.Add(new ColumnDefinition()
{
Header = "Key",
Binding = "Key"
});
columns.Columns.AddRange(
ValueCalculations.Select(
k => new ColumnDefinition()
{
Header = k.Header, Binding = "Values[" + k.Header + "]"
}));
Results.AddRange(rows);
ResultColumns = columns;
var queryEndTime = DateTime.UtcNow.Ticks;
ExecutionElapsedTime.Value = new TimeSpan(queryEndTime - queryStartTime);
}
catch (AggregateException ex)
{
var badRequest = ex.ExtractSingleInnerException() as BadRequestException;
if (badRequest != null)
{
QueryErrorMessage.Value = badRequest.Message;
IsErrorVisible.Value = true;
}
else
{
throw;
}
}
catch (TaskCanceledException)
{
}
finally
{
// there's a bug in silverlight where if a ChildWindow gets closed too soon after it's opened, it leaves the UI
// disabled; so delay closing the window by a few milliseconds
TaskEx.Delay(TimeSpan.FromMilliseconds(350))
.ContinueOnSuccessInTheUIThread(progressWindow.Close);
}
}
public void Init()
{
instance = new AggregationQuery();
}
public void SecureDatabase_AggregationQuerySecurity()
{
SecureDatabase db = BuildSampleDB();
AggregationQuery q = new AggregationQuery("sum", new string[] { "ID" }, "One");
q.TableName = sampleTableName;
AggregationResult result;
// Run the Query without security. Expect no restrictions and no security checks.
result = db.Query(new AggregationQuery(q), (si) => { Assert.Fail("No security checks for unsecured table"); return(true); });
Assert.AreEqual("[*]:One", result.Query.Where.ToString());
// Restrict the secret columns to people in "G1", or users in "G2" can see everything but only for SecretPriority < 1 items.
SecureSampleDB(db);
// Run the Query as a user in all column restriction groups
// Verify the query is unrestricted - no WHERE clause and no filtered columns
result = db.Query(new AggregationQuery(q), (si) => si.Name == "g1" || si.Name == "g2");
Assert.AreEqual("[*]:One", result.Query.Where.ToString());
Assert.IsFalse(HasRestrictedClauses(result.Query.Where));
// Run the Query as a user in G3.
// Verify WHERE clause restriction.
result = db.Query(new AggregationQuery(q), (si) => si.Name == "g3");
Assert.AreEqual("[SecretPriority] > 1 AND [*]:One", result.Query.Where.ToString());
Assert.IsFalse(HasRestrictedClauses(result.Query.Where));
// Run the Query as a user in all groups
// Verify WHERE clause restriction for *first* matching group
result = db.Query(new AggregationQuery(q), (si) => true);
Assert.AreEqual("[SecretPriority] > 1 AND [*]:One", result.Query.Where.ToString());
Assert.IsFalse(HasRestrictedClauses(result.Query.Where));
// Run the Query as a user in no groups.
// Verify all column restrictions, no where clause filter
result = db.Query(new AggregationQuery(q), (si) => false);
Assert.AreEqual("[*]:One", result.Query.Where.ToString());
Assert.IsTrue(HasRestrictedClauses(result.Query.Where));
// Add a query clause for a disallowed column when not in group. Verify error.
result = db.Query(new AggregationQuery(q)
{
Where = QueryParser.Parse("One AND SecretOwner=Bob")
}, (si) => false);
Assert.AreEqual("", result.Query.Where.ToString());
Assert.IsFalse(result.Details.Succeeded);
Assert.AreEqual(String.Format(ExecutionDetails.DisallowedColumnQuery, "SecretOwner"), result.Details.Errors);
// Add a query clause for a disallowed column when in group. Verify success.
result = db.Query(new AggregationQuery(q)
{
Where = QueryParser.Parse("One AND SecretOwner=Bob")
}, (si) => si.Name == "g1");
Assert.AreEqual("[*]:One AND [SecretOwner] = Bob", result.Query.Where.ToString());
Assert.IsTrue(HasRestrictedClauses(result.Query.Where));
// Ask to aggregate on a restricted column. Verify error.
result = db.Query(new AggregationQuery(q)
{
AggregationColumns = new string[] { "SecretPriority" }
}, (si) => si.Name == "g1");
Assert.AreEqual("", result.Query.Where.ToString());
Assert.IsFalse(result.Details.Succeeded);
Assert.AreEqual(String.Format(ExecutionDetails.DisallowedColumnQuery, "SecretPriority"), result.Details.Errors);
// Ask to aggregate over a restricted column dimension. Verify error.
AggregationQuery a = new AggregationQuery(q);
a.Dimensions.Add(new AggregationDimension("Columns", new string[] { "ID > 5", "ID > 10", "SecretPriority = 1" }));
result = db.Query(a, (si) => si.Name == "g1");
Assert.AreEqual("", result.Query.Where.ToString());
Assert.IsFalse(result.Details.Succeeded);
Assert.AreEqual(String.Format(ExecutionDetails.DisallowedColumnQuery, "SecretPriority"), result.Details.Errors);
}
/// <summary>
/// Query for flow aggregates
/// </summary>
/// <exception cref="PureCloudPlatform.Client.V2.Client.ApiException">Thrown when fails to make API call</exception>
/// <param name="body">query</param>
/// <returns>AggregateQueryResponse</returns>
public AggregateQueryResponse PostAnalyticsFlowsAggregatesQuery(AggregationQuery body)
{
ApiResponse <AggregateQueryResponse> localVarResponse = PostAnalyticsFlowsAggregatesQueryWithHttpInfo(body);
return(localVarResponse.Data);
}
public void ITable_Aggregate_Count(Func <ITable> factoryMethod)
{
// Define columns and add sample data
ITable table = factoryMethod();
AddSampleData(table);
AggregationQuery query = new AggregationQuery();
AggregationResult result;
query.Aggregator = new CountAggregator();
query.AggregationColumns = new string[] { "ID" };
query.Where = SelectQuery.ParseWhere("ID < 12000");
// Dimensionless Aggregation - verify four bugs aggregated, one cell returned with sum of IDs
result = table.Query(query);
Assert.AreEqual((ulong)4, result.Values[0, 0]);
// One dimension - verify values split out and are correct; dimension queries echoed
query.Dimensions.Add(new AggregationDimension("Priority", "Priority < 2", "Priority >= 2"));
result = table.Query(query);
AssertBlockEquals(result.Values, @"
[Priority] < 2, 2
[Priority] >= 2, 2
, 4");
Assert.AreEqual((ulong)2, result.Values[0, 1]);
Assert.AreEqual((ulong)2, result.Values[1, 1]);
Assert.AreEqual((ulong)4, result.Values[2, 1]);
// Two dimensions - verify values split on both; values are correct
query.Dimensions.Add(new AggregationDimension("Title", "Title:One | Title:Two", "-(Title:One | Title:Two)"));
result = table.Query(query);
AssertBlockEquals(result.Values, @"
[Priority] < 2, [Title]:One OR [Title]:Two, 1
[Priority] < 2, NOT([Title]:One OR [Title]:Two), 1
[Priority] < 2, , 2
[Priority] >= 2, [Title]:One OR [Title]:Two, 1
[Priority] >= 2, NOT([Title]:One OR [Title]:Two), 1
[Priority] >= 2, , 2
, [Title]:One OR [Title]:Two, 2
, NOT([Title]:One OR [Title]:Two), 2
, , 4
");
// Sparse dimensions - verify skipping is correct
query.Dimensions.Clear();
query.Dimensions.Add(new AggregationDimension("[Priority]", "[Priority] = 0", "[Priority] = 1", "[Priority] = 2", "[Priority] = 3"));
query.Dimensions.Add(new AggregationDimension("ID", "[ID] < 11900", "[ID] >= 11900"));
result = table.Query(query);
AssertBlockEquals(result.Values, @"
[Priority] = 0, [ID] < 11900, 1
[Priority] = 0, [ID] >= 11900, null
[Priority] = 0, , 1
[Priority] = 1, [ID] < 11900, null
[Priority] = 1, [ID] >= 11900, 1
[Priority] = 1, , 1
[Priority] = 2, [ID] < 11900, null
[Priority] = 2, [ID] >= 11900, null
[Priority] = 2, , null
[Priority] = 3, [ID] < 11900, 1
[Priority] = 3, [ID] >= 11900, 1
[Priority] = 3, , 2
, [ID] < 11900, 2
, [ID] >= 11900, 2
, , 4
");
// Add an empty cell and re-check
query.Dimensions.Clear();
query.Dimensions.Add(new AggregationDimension("[Title]", "[Title]:unused"));
result = table.Query(query);
AssertBlockEquals(result.Values, @"
[Title]:unused, null
, 4
");
}
/// <summary>
/// Query for flow aggregates
/// </summary>
/// <exception cref="PureCloudPlatform.Client.V2.Client.ApiException">Thrown when fails to make API call</exception>
/// <param name="body">query</param>
/// <returns>Task of AggregateQueryResponse</returns>
public async System.Threading.Tasks.Task <AggregateQueryResponse> PostAnalyticsFlowsAggregatesQueryAsync(AggregationQuery body)
{
ApiResponse <AggregateQueryResponse> localVarResponse = await PostAnalyticsFlowsAggregatesQueryAsyncWithHttpInfo(body);
return(localVarResponse.Data);
}
protected void ApplyTableSecurity <T>(IQuery <T> query, Func <SecurityIdentity, bool> isCurrentUserIn, ExecutionDetails details)
{
SecurityPermissions security = this.Security(query.TableName);
// If table has row restrictions and one matches, restrict rows and allow
// NOTE: If restricted rows are returned, columns are NOT restricted.
foreach (var rowRestriction in security.RowRestrictedUsers)
{
if (isCurrentUserIn(rowRestriction.Key))
{
query.Where = new AndExpression(QueryParser.Parse(rowRestriction.Value), query.Where);
return;
}
}
// If table has column restrictions, build a list of excluded columns
IList <string> restrictedColumns = GetRestrictedColumns(query.TableName, isCurrentUserIn);
// If no columns were restricted, return query as-is
if (restrictedColumns == null)
{
return;
}
// Exclude disallowed columns from where clauses
// If a disallowed column is requested specifically, block the query and return an error
ColumnSecurityCorrector c = new ColumnSecurityCorrector(restrictedColumns);
try
{
query.Correct(c);
}
catch (ArribaColumnAccessDeniedException e)
{
query.Where = new EmptyExpression();
details.AddDeniedColumn(e.Message);
details.AddError(ExecutionDetails.DisallowedColumnQuery, e.Message);
}
// If columns are excluded, remove those from the select list
IQuery <T> primaryQuery = query;
if (query is JoinQuery <T> )
{
primaryQuery = ((JoinQuery <T>)query).PrimaryQuery;
}
if (primaryQuery.GetType().Equals(typeof(SelectQuery)))
{
SelectQuery sq = (SelectQuery)primaryQuery;
List <string> filteredColumns = null;
if (sq.Columns.Count == 1 && sq.Columns[0] == "*")
{
filteredColumns = new List <string>();
foreach (ColumnDetails column in this[sq.TableName].ColumnDetails)
{
if (restrictedColumns.Contains(column.Name))
{
details.AddDeniedColumn(column.Name);
}
else
{
filteredColumns.Add(column.Name);
}
}
}
else
{
foreach (string columnName in sq.Columns)
{
if (restrictedColumns.Contains(columnName))
{
if (filteredColumns == null)
{
filteredColumns = new List <string>(sq.Columns);
}
filteredColumns.Remove(columnName);
details.AddDeniedColumn(columnName);
}
}
}
if (filteredColumns != null)
{
sq.Columns = filteredColumns;
}
}
else if (primaryQuery.GetType().Equals(typeof(AggregationQuery)))
{
AggregationQuery aq = (AggregationQuery)primaryQuery;
if (aq.AggregationColumns != null)
{
foreach (string columnName in aq.AggregationColumns)
{
if (restrictedColumns.Contains(columnName))
{
details.AddDeniedColumn(columnName);
details.AddError(ExecutionDetails.DisallowedColumnQuery, columnName);
aq.Where = new EmptyExpression();
}
}
}
}
else if (primaryQuery.GetType().Equals(typeof(DistinctQuery)))
{
DistinctQuery dq = (DistinctQuery)primaryQuery;
if (restrictedColumns.Contains(dq.Column))
{
details.AddDeniedColumn(dq.Column);
details.AddError(ExecutionDetails.DisallowedColumnQuery, dq.Column);
dq.Where = new EmptyExpression();
}
}
else
{
// IQuery is extensible; there's no way to ensure that user-implemented
// queries respect security rules.
details.AddError(ExecutionDetails.DisallowedQuery, primaryQuery.GetType().Name);
primaryQuery.Where = new EmptyExpression();
}
}
private static async Task Example()
{
using (var client = new ArribaClient(ArribaServerUrl))
{
// Create Table [2 Partition Bits = 4 Partitions = 256k items]
CreateTableRequest tableRequest = new CreateTableRequest("Test_" + DateTime.Now.Ticks.ToString(), 2);
tableRequest.Columns.Add(new ColumnDetails("ID", "int", -1, String.Empty, true));
tableRequest.Columns.Add(new ColumnDetails("Name", "string", null));
tableRequest.Columns.Add(new ColumnDetails("Age", "int", -1));
var table = await client.CreateTableAsync(tableRequest);
// Add columns (after creation)
await table.AddColumnsAsync(new ColumnDetails[] { new ColumnDetails("Team", "string", null) });
// Add items (CSV) [ID,Name,Age]
using (MemoryStream ms = BuildSampleUserCsv())
{
await table.ImportFileAsync(ms, "csv");
}
// Add items (DataBlock)
DataBlock block = new DataBlock(
new string[] { "ID", "Name", "Age", "Team" },
1,
new Array[] {
new int[] { 4 },
new string[] { "Karl" },
new int[] { 30 },
new string[] { "VSPlat" }
});
await table.ImportDataBlock(block);
// Get Table Information
var d = await table.GetTableInformationAsync();
// Select ID, Name WHERE Age = 32
SelectResult selectResult = await table.Select(new SelectQuery(new string[] { "ID" }, "Age = 32"));
Trace.WriteLine(String.Format("Found {0:n0} 32 year olds (expected 2)", selectResult.Total));
// Aggregate COUNT(*) WHERE {ALL} BY Age < 30, Age >= 30
AggregationQuery aggregateQuery = new AggregationQuery("Count", new string[] { "ID" }, "");
aggregateQuery.Dimensions.Add(new AggregationDimension("Age", "Age < 30", "Age >= 30"));
AggregationResult aggregateResult = await table.Aggregate(aggregateQuery);
Trace.WriteLine(String.Format("Found {0:n0} under 30 year olds (expected 1)", aggregateResult.Values[0, 1]));
Trace.WriteLine(String.Format("Found {0:n0} over 30 year olds (expected 3)", aggregateResult.Values[1, 1]));
Trace.WriteLine(String.Format("Found {0:n0} altogether (expected 4)", aggregateResult.Values[2, 1]));
// Delete WHERE ID = 2
int countDeleted = await table.Delete(SelectQuery.ParseWhere("ID = 2"));
Trace.WriteLine(String.Format("Deleted {0:n0} users (expected 1)", countDeleted));
// Select ID, Name WHERE Age = 32 (again)
selectResult = await table.Select(new SelectQuery(new string[] { "ID" }, "Age = 32"));
Trace.WriteLine(String.Format("Found {0:n0} 32 year olds (expected 1)", selectResult.Total));
// Add write permission to another user
await table.GrantPermissionsAsync(Model.Security.PermissionScope.Reader, Model.Security.IdentityScope.User, "phil");
SecurityPermissions permissions = await table.GetPermissionsAsync();
Trace.WriteLine(String.Format("Expecting 'Phil' as reader. Readers: {0}", permissions.Readers.First().Name));
// Deny myself write permission
await table.RevokePermissionsAsync(Model.Security.PermissionScope.Reader, Model.Security.IdentityScope.User, "phil");
permissions = await table.GetPermissionsAsync();
Trace.WriteLine(String.Format("Found {0:n0} readers (expected 0)", permissions.Readers.Count()));
// Delete table
await table.DeleteAsync();
}
}
/// <summary>
/// Query for flow aggregates
/// </summary>
/// <exception cref="PureCloudPlatform.Client.V2.Client.ApiException">Thrown when fails to make API call</exception>
/// <param name="body">query</param>
/// <returns>Task of ApiResponse (AggregateQueryResponse)</returns>
public async System.Threading.Tasks.Task <ApiResponse <AggregateQueryResponse> > PostAnalyticsFlowsAggregatesQueryAsyncWithHttpInfo(AggregationQuery body)
{
// verify the required parameter 'body' is set
if (body == null)
{
throw new ApiException(400, "Missing required parameter 'body' when calling FlowsApi->PostAnalyticsFlowsAggregatesQuery");
}
var localVarPath = "/api/v2/analytics/flows/aggregates/query";
var localVarPathParams = new Dictionary <String, String>();
var localVarQueryParams = new List <Tuple <String, String> >();
var localVarHeaderParams = new Dictionary <String, String>(Configuration.DefaultHeader);
var localVarFormParams = new Dictionary <String, String>();
var localVarFileParams = new Dictionary <String, FileParameter>();
Object localVarPostBody = null;
// to determine the Content-Type header
String[] localVarHttpContentTypes = new String[] {
"application/json"
};
String localVarHttpContentType = this.Configuration.ApiClient.SelectHeaderContentType(localVarHttpContentTypes);
// to determine the Accept header
String[] localVarHttpHeaderAccepts = new String[] {
"application/json"
};
String localVarHttpHeaderAccept = this.Configuration.ApiClient.SelectHeaderAccept(localVarHttpHeaderAccepts);
if (localVarHttpHeaderAccept != null)
{
localVarHeaderParams.Add("Accept", localVarHttpHeaderAccept);
}
// set "format" to json by default
// e.g. /pet/{petId}.{format} becomes /pet/{petId}.json
localVarPathParams.Add("format", "json");
// Path params
// Query params
// Header params
// Form params
// Body param
if (body != null && body.GetType() != typeof(byte[]))
{
localVarPostBody = this.Configuration.ApiClient.Serialize(body); // http body (model) parameter
}
else
{
localVarPostBody = body; // byte array
}
// authentication (PureCloud OAuth) required
// oauth required
if (!String.IsNullOrEmpty(Configuration.AccessToken))
{
localVarHeaderParams["Authorization"] = "Bearer " + this.Configuration.AccessToken;
}
// make the HTTP request
IRestResponse localVarResponse = (IRestResponse)await this.Configuration.ApiClient.CallApiAsync(localVarPath,
Method.POST, localVarQueryParams, localVarPostBody, localVarHeaderParams, localVarFormParams, localVarFileParams,
localVarPathParams, localVarHttpContentType);
int localVarStatusCode = (int)localVarResponse.StatusCode;
Dictionary <string, string> localVarHeaders = localVarResponse.Headers.ToDictionary(x => x.Name, x => x.Value.ToString());
if (localVarStatusCode >= 400)
{
throw new ApiException(localVarStatusCode, "Error calling PostAnalyticsFlowsAggregatesQuery: " + localVarResponse.Content, localVarResponse.Content, localVarHeaders);
}
else if (localVarStatusCode == 0)
{
throw new ApiException(localVarStatusCode, "Error calling PostAnalyticsFlowsAggregatesQuery: " + localVarResponse.ErrorMessage, localVarResponse.ErrorMessage);
}
return(new ApiResponse <AggregateQueryResponse>(localVarStatusCode,
localVarHeaders,
(AggregateQueryResponse)this.Configuration.ApiClient.Deserialize(localVarResponse, typeof(AggregateQueryResponse)),
localVarResponse.Content,
localVarResponse.StatusDescription));
}
public async Task <AggregationResult> Aggregate(AggregationQuery query)
{
return(await _client.SendObjectAsync <AggregationResult>(HttpMethod.Post, String.Format("table/{0}?action=aggregate", _tableName), value : query));
}
public void CanCalculateMultipleFieldsForOneAggregate()
{
using (var store = NewDocumentStore())
{
new Orders_All().Execute(store);
using (var session = store.OpenSession())
{
session.Store(new Order {
Currency = Currency.EUR, Product = "Milk", Total = 1000, Quantity = 1
});
session.Store(new Order {
Currency = Currency.NIS, Product = "Milk", Total = 2000, Quantity = 2
});
session.Store(new Order
{
Currency = Currency.EUR,
Product = "iPhone",
Total = 3000,
Quantity = 3
});
session.SaveChanges();
}
WaitForIndexing(store);
var queries = new List <AggregationQuery>
{
new AggregationQuery
{
Aggregation = FacetAggregation.Sum,
AggregationField = "Total_Range",
DisplayName = "Product-Total",
Name = "Product",
},
new AggregationQuery
{
Aggregation = FacetAggregation.Sum,
AggregationField = "Quantity_Range",
DisplayName = "Product-Quantity",
Name = "Product",
},
};
var result =
store.AsyncDatabaseCommands.GetFacetsAsync("Orders/All", new IndexQuery(), AggregationQuery.GetFacets(queries),
start: 0, pageSize: 512).Result;
Assert.Equal(6000, result.Results["Product-Total"].Values.Sum(s => s.Sum));
Assert.Equal(6, result.Results["Product-Quantity"].Values.Sum(s => s.Sum));
}
}