public WhereForm(SelectStatement selectStatement, WhereColumn whereColumn)
{
_selectStatement = selectStatement;
_whereColumn = whereColumn;
InitializeComponent();
columns.ValueMember = "Alias";
columns.DisplayMember = "Alias";
columns.DataSource = _selectStatement.SelectColumns.Where(item => (item.ColumnType & ColumnType.Where) == ColumnType.Where).ToList();
if (whereColumn != null)
{
columns.Enabled = false;
columns.SelectedValue = _selectStatement.SelectColumns.Single(item => item.Alias == whereColumn.ColumnDef.Name);
operators.SelectedItem = whereColumn.Operator;
SelectColumn selectColumn = (SelectColumn)columns.SelectedItem;
if (selectColumn != null)
{
if (selectColumn.OptionsSelectStatement != null)
{
options.SelectedValue = whereColumn.Value1;
}
else
{
value1.Text = whereColumn.Value1;
value2.Text = whereColumn.Value2;
}
}
}
}
private void columns_SelectedIndexChanged(object sender, EventArgs e)
{
SelectColumn selectColumn = (SelectColumn)columns.SelectedItem;
if (selectColumn != null)
{
if (selectColumn.OptionsSelectStatement != null)
{
value1.Visible = false;
options.Visible = true;
value2.Enabled = false;
operators.DataSource = new[] { Operator.IsEqualTo };
operators.SelectedItem = Operator.IsEqualTo;
options.ValueMember = "Id";
options.DisplayMember = "Text";
DataTable dataTable = selectColumn.OptionsSelectStatement.Execute().Tables[0];
options.DataSource = dataTable.Rows.Cast <DataRow>().Select(item => new ComboBoxItem
{
Id = item[0].ToString(),
Text = item.ItemArray.Skip(1).Select(item2 => item2.ToString()).Aggregate((a, b) => a + " - " + b)
}).ToList();
}
else
{
operators.DataSource = new List <object>(Enum.GetValues(typeof(Operator)).Cast <object>());
value1.Visible = true;
options.Visible = false;
value2.Enabled = false;
}
}
}
protected override SqlStatement PrepareStatement(IRequest context)
{
var tableName = context.Access().ResolveTableName(TableName);
if (!context.Access().TableExists(tableName))
{
throw new ObjectNotFoundException(tableName);
}
var queryExp = new SqlQueryExpression(new SelectColumn[] { SelectColumn.Glob("*") });
queryExp.FromClause.AddTable(tableName.FullName);
queryExp.WhereExpression = WhereExpression;
var queryInfo = new QueryInfo(context, queryExp);
if (Limit > 0)
{
queryInfo.Limit = new QueryLimit(Limit);
}
var queryPlan = context.Query.Context.QueryPlanner().PlanQuery(queryInfo);
return(new Prepared(tableName, queryPlan));
}
IStatement IPreparableStatement.Prepare(IRequest request)
{
var tableName = request.Query.ResolveTableName(TableName);
if (!request.Query.TableExists(tableName))
{
throw new ObjectNotFoundException(tableName);
}
var queryExp = new SqlQueryExpression(new SelectColumn[] { SelectColumn.Glob("*") });
queryExp.FromClause.AddTable(tableName.FullName);
queryExp.WhereExpression = WhereExpression;
var queryInfo = new QueryInfo(request, queryExp);
if (Limit > 0)
{
queryInfo.Limit = new QueryLimit(Limit);
}
var queryPlan = request.Query.Context.QueryPlanner().PlanQuery(queryInfo);
return(new Prepared(tableName, queryPlan));
}
private bool IsIdentitySelect(TableSelectExpression expression)
{
if (expression.Columns.Count != 1)
{
return(false);
}
if (expression.From == null)
{
return(false);
}
if (expression.From.AllTables.Count != 1)
{
return(false);
}
SelectColumn column = expression.Columns[0];
if (column.Alias == null)
{
return(false);
}
if (column.Alias.Name != "IDENTITY")
{
return(false);
}
return(true);
}
public string BuildSelect(SelectColumn columns, Filter filter, Sorting sorting, Pagination pagination, out object parameters)
{
Contract.RequiresNotNull(columns, "columns != null");
Contract.RequiresNotNull(filter, "filter != null");
Contract.RequiresNotNull(sorting, "sorting != null");
Contract.RequiresNotNull(pagination, "paggination != null");
var where = BuildWhere(filter, out parameters);
var selectColumns = BuildColumns(columns);
var sort = BuildSorting(sorting);
var page = BuildPagging(pagination);
var tableAlias = Dialect.TableAlias(TableMap.Table);
var builder = new StringBuilder($"select {selectColumns} from {tableAlias}");
if (!string.IsNullOrWhiteSpace(@where))
{
builder.Append($" where {@where}");
}
if (!string.IsNullOrWhiteSpace(sort))
{
builder.Append($" order by {sort}");
}
if (!string.IsNullOrWhiteSpace(page))
{
builder.Append(page);
}
return(builder.ToString());
}
public string BuildSelect(SelectColumn columns, Filter filter, out object parameters)
{
Contract.RequiresNotNull(columns, "columns != null");
Contract.RequiresNotNull(filter, "filter != null");
return(BuildSelect(columns, filter, new Sorting(), out parameters));
}
public void RemoveUnusedColumns_SupportQuickSearch_WithCount_Ensure_FieldExpression_NotReplaced()
{
SelectColumn col1 = new SelectColumn {
Expression = new ResourceDataColumn()
};
SelectColumn col2 = new SelectColumn {
Expression = new ResourceDataColumn()
};
StructuredQuery query = new StructuredQuery();
query.SelectColumns.Add(col1);
query.SelectColumns.Add(col2);
ClientAggregate agg = new ClientAggregate();
agg.AggregatedColumns.Add(new ReportAggregateField {
AggregateMethod = AggregateMethod.Count
});
StructuredQuery result = ReportRollupHelper.RemoveUnusedColumns(query, agg, true);
Assert.That(result.SelectColumns.Count, Is.EqualTo(2));
Assert.That(result.SelectColumns[0].Expression, Is.TypeOf <ResourceDataColumn>());
Assert.That(result.SelectColumns[1].Expression, Is.TypeOf <ResourceDataColumn>());
Assert.IsTrue(result.SelectColumns[0].IsHidden);
Assert.IsTrue(result.SelectColumns[1].IsHidden);
}
private static int MakeupFunctions(QuerySelectColumnSet columnSet, IList <Expression> extraAggregateFunctions, out Expression[] defFunList, out string[] defFunNames)
{
// Make up the functions list,
List <SelectColumn> functionsList = columnSet.FunctionColumns;
int fsz = functionsList.Count;
ArrayList completeFunList = new ArrayList();
for (int i = 0; i < fsz; ++i)
{
SelectColumn scol = functionsList[i];
completeFunList.Add(scol.Expression);
completeFunList.Add(scol.InternalName.Name);
}
for (int i = 0; i < extraAggregateFunctions.Count; ++i)
{
completeFunList.Add(extraAggregateFunctions[i]);
completeFunList.Add("HAVINGAG_" + (i + 1));
}
int fsz2 = completeFunList.Count / 2;
defFunList = new Expression[fsz2];
defFunNames = new string[fsz2];
for (int i = 0; i < fsz2; ++i)
{
defFunList[i] = (Expression)completeFunList[i * 2];
defFunNames[i] = (string)completeFunList[(i * 2) + 1];
}
return(fsz);
}
private static IQueryPlanNode EvaluateSingle(QuerySelectColumnSet columnSet)
{
if (columnSet.AggregateCount > 0)
{
throw new ApplicationException("Invalid use of aggregate function in select with no FROM clause");
}
// Make up the lists
List <SelectColumn> selectedColumns = columnSet.SelectedColumns;
int colCount = selectedColumns.Count;
var colNames = new string[colCount];
var expList = new Expression[colCount];
var subsetVars = new ObjectName[colCount];
var aliases1 = new ObjectName[colCount];
for (int i = 0; i < colCount; ++i)
{
SelectColumn scol = selectedColumns[i];
expList[i] = scol.Expression;
colNames[i] = scol.InternalName.Name;
subsetVars[i] = scol.InternalName.Clone();
aliases1[i] = scol.Alias.Clone();
}
return(new SubsetNode(new CreateFunctionsNode(new SingleRowTableNode(), expList, colNames), subsetVars, aliases1));
}
private void selects_SelectedIndexChanged(object sender, EventArgs e)
{
if (selects.SelectedItems.Count == 1)
{
_selectColumn = ((SelectColumnListViewItem)selects.SelectedItems[0]).SelectColumn;
}
}
public static int DeleteFrom(this IQuery context, ObjectName tableName, SqlExpression expression, int limit)
{
if (expression is SqlQueryExpression)
{
return(context.DeleteFrom(tableName, (SqlQueryExpression)expression, limit));
}
var table = context.GetMutableTable(tableName);
if (table == null)
{
throw new ObjectNotFoundException(tableName);
}
var queryExpression = new SqlQueryExpression(new List <SelectColumn> {
SelectColumn.Glob("*")
});
queryExpression.FromClause.AddTable(tableName.Name);
queryExpression.WhereExpression = expression;
var planExpression = queryExpression.Evaluate(context, null);
var plan = (SqlQueryObject)((SqlConstantExpression)planExpression).Value.Value;
var deleteSet = plan.QueryPlan.Evaluate(context);
return(context.DeleteFrom(tableName, deleteSet, limit));
}
/// <summary>
/// Patch up the client aggregate with counts _if_ rollup grand totals or sub totals are requested and there is no count included in the client aggregate.
/// </summary>
/// <param name="clientAggregate"></param>
/// <param name="query"></param>
public static void EnsureShowTotalsHasCount(StructuredQuery query, ClientAggregate clientAggregate)
{
// if ((rollupGrandTotals || rollupSubTotals) && clientAggregate.GroupedColumns.Any()) //<< remove commented code and delete the follow_if_ when the builder sets these flags!
if (clientAggregate.GroupedColumns.Count > 0)
{
ReportGroupField reportGroupField = clientAggregate.GroupedColumns.First( );
// Check to see if the first column group contains a count in the aggregate
if (clientAggregate.AggregatedColumns.FirstOrDefault(ac => ac.AggregateMethod == AggregateMethod.Count) == null)
{
// Inject a count aggregate for a column that is _not_ a group column
SelectColumn column = query.SelectColumns.FirstOrDefault(sc => clientAggregate.GroupedColumns.All(gc => gc.ReportColumnId != sc.ColumnId));
if (column != null)
{
clientAggregate.AggregatedColumns.Add(new ReportAggregateField
{
AggregateMethod = AggregateMethod.Count,
IncludedCount = true,
ReportColumnId = column.ColumnId,
ReportColumnEntityId = column.EntityId,
ShowGrandTotals = reportGroupField.ShowGrandTotals,
ShowRowCounts = reportGroupField.ShowRowCounts,
ShowOptionLabel = reportGroupField.ShowOptionLabel,
ShowRowLabels = reportGroupField.ShowRowLabels,
ShowSubTotals = reportGroupField.ShowSubTotals
});
}
}
}
}
private IQueryPlanNode EvaluateToSingle(PreparedQuerySelectColumns columns)
{
if (columns.AggregateCount > 0)
{
throw new InvalidOperationException("Invalid use of aggregate function in select with no FROM clause");
}
// Make up the lists
var selectedColumns = columns.SelectedColumns.ToList();
int colCount = selectedColumns.Count;
var colNames = new string[colCount];
var expList = new SqlExpression[colCount];
var subsetVars = new ObjectName[colCount];
var aliases1 = new ObjectName[colCount];
for (int i = 0; i < colCount; ++i)
{
SelectColumn scol = selectedColumns[i];
expList[i] = scol.Expression;
colNames[i] = scol.InternalName.Name;
subsetVars[i] = scol.InternalName;
aliases1[i] = scol.ResolvedName;
}
return(new SubsetNode(new CreateFunctionsNode(new SingleRowTableNode(), expList, colNames), subsetVars, aliases1));
}
private static IEnumerable <ByColumn> ResolveOrderByRefs(QuerySelectColumnSet columnSet, IEnumerable <ByColumn> orderBy)
{
// Resolve any numerical references in the ORDER BY list (eg.
// '1' will be a reference to column 1.
var result = new List <ByColumn>();
if (orderBy != null)
{
List <SelectColumn> preparedColSet = columnSet.SelectedColumns;
foreach (ByColumn col in orderBy)
{
var byColumn = col;
Expression exp = col.Expression;
if (exp is ConstantExpression)
{
Number bnum = ((ConstantExpression)exp).Value.ToNumber();
if (bnum.Scale == 0)
{
int colRef = bnum.ToInt32() - 1;
if (colRef >= 0 && colRef < preparedColSet.Count)
{
SelectColumn scol = preparedColSet[colRef];
byColumn = new ByColumn(scol.Expression, byColumn.Ascending);
}
}
}
result.Add(byColumn);
}
}
return(result.AsReadOnly());
}
private void ParseColumns(IEnumerable <string[]> selectionWithColumnPath)
{
foreach (var selectionColumnPath in selectionWithColumnPath)
{
SelectColumn parentColumn = new RootColumn(_baseType);
for (var depth = 0; depth < selectionColumnPath.Length; depth++)
{
if (IsGreaterThenMaxDepth(depth))
{
break;
}
var columnName = selectionColumnPath[depth];
var property = parentColumn.Type.GetTypeOrGenericType().GetProperties().FirstOrDefault(x => x.Name.ToLowerInvariant() == columnName.ToLowerInvariant());
if (property == null || IsGreaterThanMaxDepth(property, depth))
{
break;
}
var key = string.Join(".", selectionColumnPath.Take(depth + 1)).ToLowerInvariant();
var currentColumn = _allSelectColumns.FirstOrDefault(all => all.Key == key);
if (currentColumn != null)
{
parentColumn = currentColumn;
}
else
{
// pass non selectable & unselectable properties
if (IsNotSelectableProperty(key) || IsUnselectableProperty(key) || IsAnIndexerProperty(property))
{
continue;
}
var column = new SelectColumn(columnName, key, property.PropertyType);
if (_rawSelection.Contains(column.Key + ".*", StringComparer.OrdinalIgnoreCase))
{
column.InclusionType = SelectInclusingType.IncludeAllProperties;
ProcessInclusingType(column);
}
else if (property.PropertyType.IsCustomObjectType() && _rawSelection.Contains(column.Key, StringComparer.OrdinalIgnoreCase))
{
column.InclusionType = SelectInclusingType.IncludeBaseProperties;
ProcessInclusingType(column);
}
_allSelectColumns.Add(column);
if (depth == 0)
{
_selectColumns.Add(column);
}
column.ParentColumn = parentColumn;
parentColumn.SubColumns.Add(column);
parentColumn = column;
}
}
}
}
public static SelectColumn BuildColumn(PlSqlParser.SelectedElementContext context)
{
string alias = null;
if (context.column_alias() != null &&
!context.column_alias().IsEmpty)
{
alias = Name.Simple(context.column_alias());
}
SelectColumn column;
if (context.expression() != null &&
!context.expression().IsEmpty)
{
column = new SelectColumn(Expression.Build(context.expression()), alias);
}
else if (context.selectedColumn() != null &&
!context.selectedColumn().IsEmpty)
{
bool glob = context.selectedColumn().glob != null;
ObjectName name = Name.Select(context.selectedColumn().objectName(), glob);
var exp = SqlExpression.Reference(name);
column = new SelectColumn(exp, alias);
}
else
{
throw new ParseCanceledException();
}
return(column);
}
/// <summary>
/// Applies the sort order.
/// </summary>
/// <param name="structuredQuery">The structured query.</param>
/// <param name="reportSortOrders">The report sort orders.</param>
private static void ApplySortOrder(StructuredQuery structuredQuery, IEnumerable <ReportSortOrder> reportSortOrders)
{
// Check that all sort orders are valid, that is the column ID GUIDs are in the report _and_ the Order is an
// orderBy direction _or_ empty.
OrderByDirection orderByDirection = OrderByDirection.Ascending;
IEnumerable <ReportSortOrder> sortOrders = reportSortOrders as IList <ReportSortOrder> ?? reportSortOrders.ToList();
if (!sortOrders.All(col => structuredQuery.SelectColumns.Any(sc => sc.EntityId.ToString(CultureInfo.InvariantCulture) == col.ColumnId)) ||
!sortOrders.All(col => Enum.TryParse(col.Order, out orderByDirection) || col.Order == string.Empty))
{
throw new ArgumentOutOfRangeException("reportSortOrders");
}
// Clear any existing sort order clauses that may be saved with the report
structuredQuery.OrderBy.Clear();
foreach (ReportSortOrder sortOrder in sortOrders.Where(sortOrder => Enum.TryParse(sortOrder.Order, out orderByDirection)))
{
long sortEntityColumnId;
if (!long.TryParse(sortOrder.ColumnId, out sortEntityColumnId))
{
continue;
}
SelectColumn sortColumn = structuredQuery.SelectColumns.FirstOrDefault(sc => sc.EntityId == sortEntityColumnId);
if (sortColumn != null && sortColumn.Expression != null && sortColumn.Expression.ExpressionId != Guid.Empty && sortColumn.ColumnId != Guid.Empty)
{
structuredQuery.OrderBy.Add(new OrderByItem
{
Direction = orderByDirection,
Expression = new ColumnReference {
EntityId = sortEntityColumnId, ExpressionId = sortColumn.Expression.ExpressionId, ColumnId = sortColumn.ColumnId
}
});
}
}
}
protected override SqlStatement PrepareStatement(IRequest context)
{
var tableName = context.Access().ResolveTableName(TableName);
if (!context.Access().TableExists(tableName))
{
throw new ObjectNotFoundException(tableName);
}
var queryExpression = new SqlQueryExpression(new[] { SelectColumn.Glob("*") });
queryExpression.FromClause.AddTable(tableName.FullName);
queryExpression.WhereExpression = WherExpression;
var queryFrom = QueryExpressionFrom.Create(context, queryExpression);
var queryPlan = context.Query.Context.QueryPlanner().PlanQuery(new QueryInfo(context, queryExpression));
var columns = new List <SqlAssignExpression>();
foreach (var assignment in Assignments)
{
var columnName = ObjectName.Parse(assignment.ColumnName);
var refName = queryFrom.ResolveReference(columnName);
var expression = assignment.Expression.Prepare(queryFrom.ExpressionPreparer);
var assign = SqlExpression.Assign(SqlExpression.Reference(refName), expression);
columns.Add(assign);
}
return(new Prepared(tableName, queryPlan, columns.ToArray(), Limit));
}
public void RemoveUnusedColumns_WithCount_Ensure_AggregateExpressionWithGrouping_Removed( )
{
ResourceEntity resNode = new ResourceEntity( );
AggregateEntity aggNode = new AggregateEntity {
GroupedEntity = resNode
};
aggNode.GroupBy.Add(new ResourceDataColumn( ));
SelectColumn col1 = new SelectColumn {
Expression = new ResourceDataColumn( )
};
SelectColumn col2 = new SelectColumn {
Expression = new AggregateExpression {
NodeId = aggNode.NodeId
}
};
StructuredQuery query = new StructuredQuery( );
query.RootEntity = aggNode;
query.SelectColumns.Add(col1);
query.SelectColumns.Add(col2);
ClientAggregate agg = new ClientAggregate( );
agg.AggregatedColumns.Add(new ReportAggregateField {
AggregateMethod = AggregateMethod.Count
});
StructuredQuery result = ReportRollupHelper.RemoveUnusedColumns(query, agg);
Assert.That(result.SelectColumns.Count, Is.EqualTo(2));
Assert.That(result.SelectColumns [0].Expression, Is.TypeOf <IdExpression>( ));
Assert.That(result.SelectColumns [1].Expression, Is.TypeOf <AggregateExpression>( ));
}
public ColumnCollection GetColumns()
{
ColumnCollection columns = new ColumnCollection();
ISqlScript sqlScript = SqlParserService.Parse(_select);
if (sqlScript.Statements != null && sqlScript.Statements.Count > 0)
{
ISqlStatement statement = sqlScript.Statements[0];
if (statement is ISelectStatement)
{
ISelectStatement selectStatement = statement as ISelectStatement;
if (selectStatement.QueryExpression != null && selectStatement.QueryExpression is ISelectExpression)
{
ISelectExpression selectExpression = selectStatement.QueryExpression as ISelectExpression;
for (int i = 0; i < selectExpression.SelectList.Count; i++)
{
SelectColumn selectColumn = selectExpression.SelectList[i];
Column column = _table[selectColumn.Name];
if (column != null)
{
columns.Add(column);
}
}
}
}
}
return(columns);
}
public SelectColumnListViewItem(string text, SelectColumn selectColumn)
{
SelectColumn = selectColumn;
Text = text;
SubItems.Add("");
SubItems.Add("");
RefreshText();
}
public string BuildSelect(SelectColumn columns, Filter filter, Sorting sorting, out object parameters)
{
Contract.RequiresNotNull(columns, "columns != null");
Contract.RequiresNotNull(filter, "filter != null");
Contract.RequiresNotNull(sorting, "sorting != null");
return(BuildSelect(columns, filter, sorting, new Pagination(), out parameters));
}
/// <summary>
/// Renders a sinle select column
/// </summary>
protected virtual void SelectColumn(StringBuilder builder, SelectColumn col)
{
Expression(builder, col.Expression);
if (col.ColumnAlias != null)
{
builder.Append(" ");
Identifier(builder, col.ColumnAlias);
}
}
/// <summary>
/// Builds the addition order column dictionary.
/// </summary>
/// <remarks>
/// Lifted from the report info service
/// </remarks>
/// <param name="query">The query.</param>
/// <param name="clientAggregate">The client aggregate.</param>
/// <returns>System.Collections.Generic.Dictionary{System.Guid,EDC.ReadiNow.Metadata.Query.Structured.SelectColumn}.</returns>
private Dictionary <Guid, SelectColumn> BuildAdditionOrderColumnDictionary(StructuredQuery query, ClientAggregate clientAggregate)
{
Dictionary <Guid, SelectColumn> additionalOrderColumns = new Dictionary <Guid, SelectColumn>();
foreach (ReportAggregateField reportAggregateField in clientAggregate.AggregatedColumns)
{
if (reportAggregateField.AggregateMethod == AggregateMethod.Max || reportAggregateField.AggregateMethod == AggregateMethod.Min)
{
SelectColumn currentSelectColumn = query.SelectColumns.FirstOrDefault(sc => sc.ColumnId == reportAggregateField.ReportColumnId);
if (currentSelectColumn != null && currentSelectColumn.Expression is ResourceExpression && ((ResourceExpression)currentSelectColumn.Expression).CastType is ChoiceRelationshipType)
{
//add choice field column's order column in additional order column dictionary.
if (!additionalOrderColumns.ContainsKey(currentSelectColumn.ColumnId))
{
SelectColumn orderColumn = new SelectColumn
{
ColumnId = Guid.NewGuid(),
ColumnName = "EnumOrder",
DisplayName = "EnumOrder",
Expression =
new ResourceDataColumn
{
NodeId = ((EntityExpression)currentSelectColumn.Expression).NodeId,
FieldId = new Model.EntityRef("core:enumOrder")
}
};
query.SelectColumns.Add(orderColumn);
additionalOrderColumns.Add(currentSelectColumn.ColumnId, orderColumn);
}
}
else if (currentSelectColumn != null && currentSelectColumn.Expression is AggregateExpression &&
((AggregateExpression)currentSelectColumn.Expression).Expression is ResourceExpression &&
((ResourceExpression)(((AggregateExpression)currentSelectColumn.Expression).Expression)).CastType is ChoiceRelationshipType)
{
//add aggregated choice field column's order column in additional order column dictionary.
if (!additionalOrderColumns.ContainsKey(currentSelectColumn.ColumnId))
{
SelectColumn orderColumn = new SelectColumn
{
ColumnId = Guid.NewGuid(),
ColumnName = "EnumOrder",
DisplayName = "EnumOrder",
Expression =
new AggregateExpression
{
NodeId = ((AggregateExpression)currentSelectColumn.Expression).NodeId,
Expression = currentSelectColumn.Expression
}
};
additionalOrderColumns.Add(currentSelectColumn.ColumnId, orderColumn);
}
}
}
}
return(additionalOrderColumns);
}
/// <summary>
/// Adds a field to be included in the select statement of the query.
/// </summary>
public void SelectField(string fieldId, SelectColumnAggregate?aggregate = null, SelectColumnFormat?format = null)
{
var column = new SelectColumn
{
FieldId = fieldId,
Aggregate = aggregate,
Format = format
};
Raw.Select.Add(column);
}
/// <summary>
/// Renders a row count SELECT statement.
/// </summary>
/// <param name="query">Query definition to count rows for</param>
/// <returns>Generated SQL statement</returns>
/// <remarks>
/// Renders a SQL statement which returns a result set with one row and one cell which contains the number of rows <paramref name="query"/> can generate.
/// The generated statement will work nicely with <see cref="System.Data.IDbCommand.ExecuteScalar"/> method.
/// </remarks>
public override string RenderRowCount(SelectQuery query)
{
string baseSql = RenderSelect(query, false);
SelectQuery countQuery = new SelectQuery();
SelectColumn col = new SelectColumn("*", null, "cnt", SqlAggregationFunction.Count);
countQuery.Columns.Add(col);
countQuery.FromClause.BaseTable = FromTerm.SubQuery(baseSql, "t");
return(RenderSelect(countQuery));
}
private void ckbSelected_Loaded(object sender, RoutedEventArgs e)
{
var element = sender as CheckBox;
if (!(element.DataContext is MarkObject))
{
var data = ((System.Windows.Controls.DataGridCell)(((System.Windows.Controls.Primitives.ToggleButton)(sender)).Parent)).DataContext;
element.DataContext = SelectColumn.GetMarkObject(data);
element.IsChecked = SelectColumn.GetMarkObject(data).Selected;
}
}
public async Task <int> SaveAsync(TableMap tableMap, SelectColumn columns, object value)
{
var builder = SqlBuilder.Save(tableMap);
var sql = builder.Build(columns, value, out object parameters);
using (var connection = _connectionFactory.CreateConnection())
{
var result = await connection.ExecuteScalarAsync(sql, parameters);
return(Convert.ToInt32(result));
}
}
public async Task <IEnumerable <TEntity> > SelectAllAsync(TableMap tableMap, SelectColumn columns, Filter filter)
{
var builder = SqlBuilder.Select(tableMap);
var sql = builder.BuildSelect(columns, filter, out object parameters);
using (var connection = _connectionFactory.CreateConnection())
{
var result = await connection.QueryAsync <dynamic>(sql, parameters);
return(_entityFactory.BuildMultiple(result));
}
}
/// <summary>
/// Renders a SELECT statement
/// </summary>
/// <param name="query">Query definition</param>
/// <returns>Generated SQL statement</returns>
public override string RenderSelect(SelectQuery query)
{
if (query.Top > -1 && query.OrderByTerms.Count > 0)
{
string baseSql = RenderSelect(query, -1);
SelectQuery countQuery = new SelectQuery();
SelectColumn col = new SelectColumn("*");
countQuery.Columns.Add(col);
countQuery.FromClause.BaseTable = FromTerm.SubQuery(baseSql, "t");
return RenderSelect(countQuery, query.Top);
}
else
{
return RenderSelect(query, query.Top);
}
}
/*
void RenderFromPhrase(StringBuilder builder, FromClause fromClause)
{
this.From(builder);
this.FromTerm(builder, fromClause.BaseTable);
foreach(Join join in fromClause.Joins)
{
builder.AppendFormat(" {0} join ", join.Type.ToString().ToLower());
this.FromTerm(builder, join.RightTable);
if (join.Type != JoinType.Cross)
{
builder.AppendFormat(" on ");
this.QualifiedIdentifier(builder, join.LeftTable.RefName, join.LeftField);
builder.AppendFormat(" = ");
this.QualifiedIdentifier(builder, join.RightTable.RefName, join.RightField);
}
}
}
*/
/// <summary>
/// Renders a row count SELECT statement.
/// </summary>
/// <param name="query">Query definition to count rows for</param>
/// <returns>Generated SQL statement</returns>
/// <remarks>
/// Renders a SQL statement which returns a result set with one row and one cell which contains the number of rows <paramref name="query"/> can generate.
/// The generated statement will work nicely with <see cref="System.Data.IDbCommand.ExecuteScalar"/> method.
/// </remarks>
public override string RenderRowCount(SelectQuery query)
{
string baseSql = RenderSelect(query);
SelectQuery countQuery = new SelectQuery();
SelectColumn col = new SelectColumn("*", null, "cnt", SqlAggregationFunction.Count);
countQuery.Columns.Add(col);
countQuery.FromClause.BaseTable = FromTerm.SubQuery(baseSql, "t");
return RenderSelect(countQuery);
}
/// <summary>
/// Prepares the given SelectColumn by fully qualifying the expression and
/// allocating it correctly within this context.
/// </summary>
/// <param name="col"></param>
/// <param name="context"></param>
private SelectColumn PrepareSelectColumn(SelectColumn col, IQueryContext context)
{
// Check to see if we have any Select statements in the
// Expression. This is necessary, because we can't have a
// sub-select evaluated during list table downloading.
if (col.Expression.HasSubQuery())
throw new ApplicationException("Sub-command not allowed in column list.");
// First fully qualify the select expression
var exp = col.Expression.Prepare(fromSet.ExpressionQualifier);
col = new SelectColumn(exp, col.Alias);
// If the expression isn't a simple variable, then add to
// function list.
ObjectName v = exp.AsVariable();
if (v == null) {
// This means we have a complex expression.
++runningFunNumber;
string aggStr = runningFunNumber.ToString();
// If this is an aggregate column then add to aggregate count.
if (col.Expression.HasAggregateFunction(context)) {
++AggregateCount;
// Add '_A' code to end of internal name of column to signify this is
// an aggregate column
aggStr += "_A";
}
// If this is a constant column then add to constant cound.
else if (exp.IsConstant()) {
ConstantCount = ConstantCount + 1;
} else {
// Must be an expression with variable's embedded ( eg.
// (part_id + 3) * 4, (id * value_of_part), etc )
}
if (col.Alias == null) {
col = new SelectColumn(col.Expression, new ObjectName(col.Expression.ToString()));
}
col.InternalName = new ObjectName(FunctionTableName, aggStr);
FunctionColumns.Add(col);
} else {
// Not a complex expression
if (col.Alias == null) {
col = new SelectColumn(col.Expression, v);
} else {
col = new SelectColumn(col.Expression, col.Alias);
}
col.InternalName = v;
}
return col;
}
private Field SelectAggregate(string functionName, params SqlExpression[] args)
{
var column = new SelectColumn(SqlExpression.FunctionCall(functionName, args));
var query = new SqlQueryExpression(new[] { column });
query.FromClause.AddTable("APP.test_table");
var result = AdminQuery.Select(query);
var row = result.FirstOrDefault();
if (row == null)
throw new InvalidOperationException();
return row.GetValue(0);
}
public static SelectColumn Deserialize(BinaryReader reader)
{
var exp = SqlExpression.Deserialize(reader);
string alias = null;
var hasAlias = reader.ReadByte() == 1;
if (hasAlias)
alias = reader.ReadString();
var column = new SelectColumn(exp, alias);
var hasInternalName = reader.ReadByte() == 1;
if (hasInternalName)
column.InternalName = ObjectName.Deserialize(reader);
var hasResolvedName = reader.ReadByte() == 1;
if (hasResolvedName)
column.ResolvedName = ObjectName.Deserialize(reader);
return column;
}
public static void Serialize(SelectColumn column, BinaryWriter writer)
{
SqlExpression.Serialize(column.Expression, writer);
var hasAlias = !String.IsNullOrEmpty(column.Alias);
if (!hasAlias) {
writer.Write((byte)0);
} else {
writer.Write((byte)1);
writer.Write(column.Alias);
}
bool hasInternalName = column.InternalName != null;
if (hasInternalName) {
writer.Write((byte)1);
ObjectName.Serialize(column.InternalName, writer);
} else {
writer.Write((byte)0);
}
bool hasResolvedName = column.ResolvedName != null;
if (hasResolvedName) {
writer.Write((byte) 1);
ObjectName.Serialize(column.ResolvedName, writer);
} else {
writer.Write((byte)0);
}
}
/// <summary>
/// Renders a sinle select column
/// </summary>
protected virtual void SelectColumn(StringBuilder builder, SelectColumn col)
{
Expression(builder, col.Expression);
if (col.ColumnAlias != null)
{
builder.Append(" ");
Identifier(builder, col.ColumnAlias);
}
}
/// <summary>
/// Adds all the columns from the given IFromTableSource object.
/// </summary>
/// <param name="table"></param>
private void AddAllFromTable(IFromTableSource table)
{
// Select all the tables
ObjectName[] vars = table.AllColumns;
foreach (ObjectName v in vars) {
// Make up the SelectColumn
Expression e = Expression.Variable(v);
SelectColumn ncol = new SelectColumn(e, v);
ncol.InternalName = v;
// Add to the list of columns selected
SelectSingleColumn(ncol);
}
}
/// <summary>
/// Renders a row count SELECT statement.
/// </summary>
/// <param name="query">Query definition to count rows for</param>
/// <returns>Generated SQL statement</returns>
/// <remarks>
/// Renders a SQL statement which returns a result set with one row and one cell which contains the number of rows <paramref name="query"/> can generate.
/// The generated statement will work nicely with <see cref="System.Data.IDbCommand.ExecuteScalar"/> method.
/// </remarks>
public override string RenderRowCount(SelectQuery query)
{
SelectQuery queryAddition = query.Clone();
queryAddition.Columns.Clear();
SelectColumn col = new SelectColumn("*", null, "", SqlAggregationFunction.Count);
queryAddition.Columns.Add(col);
queryAddition.OrderByTerms.Clear();
return RenderSelect(queryAddition);
}
/// <summary>
/// Adds a single SelectColumn to the list of output columns
/// from the command.
/// </summary>
/// <param name="col"></param>
/// <remarks>
/// Note that at this point the the information in the given
/// SelectColumn may not be completely qualified.
/// </remarks>
public void SelectSingleColumn(SelectColumn col)
{
SelectedColumns.Add(col);
}
