protected static string GetSetOperationStatement(StandardProviderParameters std, esDynamicQuery query) { string sql = String.Empty; IDynamicQueryInternal iQuery = query as IDynamicQueryInternal; if (iQuery.InternalSetOperations != null) { foreach (esSetOperation setOperation in iQuery.InternalSetOperations) { switch (setOperation.SetOperationType) { case esSetOperationType.Union: sql += " UNION "; break; case esSetOperationType.UnionAll: sql += " UNION ALL "; break; case esSetOperationType.Intersect: sql += " INTERSECT "; break; case esSetOperationType.Except: sql += " EXCEPT "; break; } sql += BuildQuery(std, setOperation.Query); } } return(sql); }
protected static string GetFromStatement(StandardProviderParameters std, esDynamicQuery query) { IDynamicQueryInternal iQuery = query as IDynamicQueryInternal; string sql = String.Empty; if (iQuery.InternalFromQuery == null) { sql = Shared.CreateFullName(std.request); if (iQuery.JoinAlias != " ") { sql += " " + iQuery.JoinAlias; } } else { IDynamicQueryInternal iSubQuery = iQuery.InternalFromQuery as IDynamicQueryInternal; iSubQuery.IsInSubQuery = true; sql += "("; sql += BuildQuery(std, iQuery.InternalFromQuery); sql += ")"; if (iSubQuery.SubQueryAlias != " ") { sql += " AS " + iSubQuery.SubQueryAlias; } iSubQuery.IsInSubQuery = false; } return(sql); }
//static public string CreateFullName(esDataRequest request, esDynamicQuerySerializable query) static public string CreateFullName(esDataRequest request, esDynamicQuery query) { //IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; IDynamicQueryInternal iQuery = query as IDynamicQueryInternal; esProviderSpecificMetadata providerMetadata = iQuery.ProviderMetadata as esProviderSpecificMetadata; string name = String.Empty; string schema = iQuery.Schema ?? request.Schema ?? providerMetadata.Schema; if (schema != null) { name += Delimiters.TableOpen + schema + Delimiters.TableClose + "."; } name += Delimiters.TableOpen; if (query.querySource != null) { name += query.querySource; } else { name += providerMetadata.Destination; } name += Delimiters.TableClose; return(name); }
static public string CreateFullName(esDynamicQuerySerializable query) { IDynamicQueryInternal iQuery = query as IDynamicQueryInternal; string name = String.Empty; if ((iQuery.Catalog != null || iQuery.ProviderMetadata.Catalog != null) && (iQuery.Schema != null || iQuery.ProviderMetadata.Schema != null)) { name += Delimiters.TableOpen; name += iQuery.Catalog != null ? iQuery.Catalog : iQuery.ProviderMetadata.Catalog; name += Delimiters.TableClose + "."; } if (iQuery.Schema != null || iQuery.ProviderMetadata.Schema != null) { name += Delimiters.TableOpen; name += iQuery.Schema != null ? iQuery.Schema : iQuery.ProviderMetadata.Schema; name += Delimiters.TableClose + "."; } name += Delimiters.TableOpen; if (query.es.QuerySource != null) { name += query.es.QuerySource; } else { name += iQuery.ProviderMetadata.Destination; } name += Delimiters.TableClose; return(name); }
protected static string GetOrderByStatement(StandardProviderParameters std, esDynamicQuery query) { string sql = String.Empty; string comma = String.Empty; IDynamicQueryInternal iQuery = query as IDynamicQueryInternal; if (iQuery.InternalOrderByItems != null) { sql += " ORDER BY "; foreach (esOrderByItem orderByItem in iQuery.InternalOrderByItems) { bool literal = false; sql += comma; string columnName = orderByItem.Expression.Column.Name; if (columnName != null && columnName[0] == '<') { sql += columnName.Substring(1, columnName.Length - 2); if (orderByItem.Direction == esOrderByDirection.Unassigned) { literal = true; // They must provide the DESC/ASC in the literal string } } else { sql += GetExpressionColumn(std, query, orderByItem.Expression, false, false); } if (!literal) { if (orderByItem.Direction == esOrderByDirection.Ascending) { sql += " ASC"; } else { sql += " DESC"; } } comma = ","; } } return(sql); }
protected static string BuildQuery(StandardProviderParameters std, esDynamicQuery query) { bool paging = false; if (query.pageNumber.HasValue && query.pageSize.HasValue) { paging = true; } IDynamicQueryInternal iQuery = query as IDynamicQueryInternal; string select = GetSelectStatement(std, query); string from = GetFromStatement(std, query); string join = GetJoinStatement(std, query); string where = GetComparisonStatement(std, query, iQuery.InternalWhereItems, " WHERE "); string groupBy = GetGroupByStatement(std, query); string having = GetComparisonStatement(std, query, iQuery.InternalHavingItems, " HAVING "); string orderBy = GetOrderByStatement(std, query); string setOperation = GetSetOperationStatement(std, query); string sql = String.Empty; sql += "SELECT " + select + " FROM " + from + join + where + setOperation + groupBy + having + orderBy; if (paging) { int begRow = ((query.pageNumber.Value - 1) * query.pageSize.Value); sql += " LIMIT " + query.pageSize.ToString(); sql += " OFFSET " + begRow.ToString() + " "; } else if (query.top >= 0) { sql += " LIMIT " + query.top.ToString() + " "; } else if (iQuery.Skip.HasValue || iQuery.Take.HasValue) { if (iQuery.Take.HasValue) { sql += " LIMIT " + iQuery.Take.ToString() + " "; } if (iQuery.Skip.HasValue) { sql += " OFFSET " + iQuery.Skip.ToString() + " "; } } return(sql); }
/// <summary> /// Used to describe the "where" conditions of the join itself /// </summary> /// <param name="items"></param> /// <returns></returns> public esDynamicQuery On(params object[] items) { if (this.data.WhereItems == null) { this.data.WhereItems = new List <esComparison>(); } foreach (object item in items) { esComparison wi = item as esComparison; if (wi != null) { if (wi.data.WhereExpression != null) { foreach (esComparison exp in wi.data.WhereExpression) { esDynamicQuery q = exp.Value as esDynamicQuery; if (q != null) { IDynamicQueryInternal iQ = q as IDynamicQueryInternal; iQ.HookupProviderMetadata(q); } } this.data.WhereItems.AddRange(wi.data.WhereExpression); } else { this.data.WhereItems.Add(wi); } esDynamicQuery query = wi.Value as esDynamicQuery; if (query != null) { IDynamicQueryInternal iQ = query as IDynamicQueryInternal; iQ.HookupProviderMetadata(query); } } else { throw new Exception("Unsupported Join Syntax"); } } return(this.parentQuery); }
private static string GetSubquerySearchCondition(esDynamicQuery query) { string searchCondition = String.Empty; IDynamicQueryInternal iQuery = query as IDynamicQueryInternal; switch (iQuery.SubquerySearchCondition) { case esSubquerySearchCondition.All: searchCondition = "ALL"; break; case esSubquerySearchCondition.Any: searchCondition = "ANY"; break; case esSubquerySearchCondition.Some: searchCondition = "SOME"; break; } return(searchCondition); }
/// <summary> /// This initializes the esDataRequest for the query. /// </summary> /// <param name="request">The request to populate.</param> protected void PopulateRequest(esDataRequest request) { IMetadata meta = this.Meta; esConnection conn = this.es2.Connection; esProviderSpecificMetadata providerMetadata = meta.GetProviderMetadata(conn.ProviderMetadataKey); IDynamicQueryInternal iQuery = this as IDynamicQueryInternal; if ((this.queries != null && this.queries.Count > 0) || iQuery.InternalSetOperations != null) { AssignProviderMetadata(this, new List <esDynamicQuery>()); } string catalog = conn.Catalog; string schema = conn.Schema; iData.Catalog = catalog; iData.Schema = schema; iData.DataID = meta.DataID; iData.ProviderMetadata = providerMetadata; iData.Columns = meta.Columns; request.ConnectionString = conn.ConnectionString; request.CommandTimeout = conn.CommandTimeout; request.QueryType = esQueryType.DynamicQuery; request.DynamicQuery = this; request.DataID = meta.DataID; request.ProviderMetadata = providerMetadata; request.Catalog = catalog; request.Schema = schema; request.Columns = meta.Columns; if (this.m_selectAll) { _selectAll(); } if (this.querySource == null || this.querySource.Length == 0) { this.es.QuerySource(providerMetadata.Source); } }
protected static string GetJoinStatement(StandardProviderParameters std, esDynamicQuery query) { string sql = String.Empty; IDynamicQueryInternal iQuery = query as IDynamicQueryInternal; if (iQuery.InternalJoinItems != null) { foreach (esJoinItem joinItem in iQuery.InternalJoinItems) { esJoinItem.esJoinItemData joinData = (esJoinItem.esJoinItemData)joinItem; switch (joinData.JoinType) { case esJoinType.InnerJoin: sql += " INNER JOIN "; break; case esJoinType.LeftJoin: sql += " LEFT JOIN "; break; case esJoinType.RightJoin: sql += " RIGHT JOIN "; break; case esJoinType.FullJoin: sql += " FULL JOIN "; break; } IDynamicQueryInternal iSubQuery = joinData.Query as IDynamicQueryInternal; sql += Shared.CreateFullName(std.request, joinData.Query); sql += " " + iSubQuery.JoinAlias + " ON "; sql += GetComparisonStatement(std, query, joinData.WhereItems, String.Empty); } } return(sql); }
static public string CreateFullName(esDynamicQuery query) { IDynamicQueryInternal iQuery = query as IDynamicQueryInternal; esProviderSpecificMetadata providerMetadata = iQuery.ProviderMetadata as esProviderSpecificMetadata; string name = String.Empty; name += Delimiters.TableOpen; if (query.querySource != null) { name += query.querySource; } else { name += providerMetadata.Destination; } name += Delimiters.TableClose; return(name); }
protected static string GetColumnName(esColumnItem column) { if (column.Query == null || column.Query.es.JoinAlias == " ") { return(Delimiters.ColumnOpen + column.Name + Delimiters.ColumnClose); } else { IDynamicQueryInternal iQuery = column.Query as IDynamicQueryInternal; if (iQuery.IsInSubQuery) { return(column.Query.es.JoinAlias + "." + Delimiters.ColumnOpen + column.Name + Delimiters.ColumnClose); } else { string alias = iQuery.SubQueryAlias == string.Empty ? iQuery.JoinAlias : iQuery.SubQueryAlias; return(alias + "." + Delimiters.ColumnOpen + column.Name + Delimiters.ColumnClose); } } }
public esCase When(esComparison comparison) { this.WhenItem = new esExpressionOrComparison(); this.WhenItem.Comparisons = new List <esComparison>(); if (comparison != null) { if (comparison.data.WhereExpression != null) { foreach (esComparison exp in comparison.data.WhereExpression) { esDynamicQuery q = exp.Value as esDynamicQuery; if (q != null) { IDynamicQueryInternal iQ = q as IDynamicQueryInternal; iQ.HookupProviderMetadata(q); } } this.WhenItem.Comparisons.AddRange(comparison.data.WhereExpression); } else { this.WhenItem.Comparisons.Add(comparison); } esDynamicQuery query = comparison.Value as esDynamicQuery; if (query != null) { IDynamicQueryInternal iQ = query as IDynamicQueryInternal; iQ.HookupProviderMetadata(query); } } return(this); }
protected static string GetGroupByStatement(StandardProviderParameters std, esDynamicQuery query) { string sql = String.Empty; string comma = String.Empty; IDynamicQueryInternal iQuery = query as IDynamicQueryInternal; if (iQuery.InternalGroupByItems != null) { sql += " GROUP BY "; foreach (esGroupByItem groupBy in iQuery.InternalGroupByItems) { sql += comma; string columnName = groupBy.Expression.Column.Name; if (columnName != null && columnName[0] == '<') { sql += columnName.Substring(1, columnName.Length - 2); } else { sql += GetExpressionColumn(std, query, groupBy.Expression, false, false); } comma = ","; } if (query.withRollup) { sql += " WITH ROLLUP"; } } return(sql); }
protected static string GetComparisonStatement(StandardProviderParameters std, esDynamicQuery query, List <esComparison> items, string prefix) { string sql = String.Empty; string comma = String.Empty; IDynamicQueryInternal iQuery = query as IDynamicQueryInternal; //======================================= // WHERE //======================================= if (items != null) { sql += prefix; string compareTo = String.Empty; foreach (esComparison comparisonItem in items) { esComparison.esComparisonData comparisonData = (esComparison.esComparisonData)comparisonItem; esDynamicQuery subQuery = null; bool requiresParam = true; std.needsStringParameter = false; std.needsIntegerParameter = false; std.needsDoubleParameter = false; if (comparisonData.IsParenthesis) { if (comparisonData.Parenthesis == esParenthesis.Open) { sql += "("; } else { sql += ")"; } continue; } if (comparisonData.IsConjunction) { switch (comparisonData.Conjunction) { case esConjunction.And: sql += " AND "; break; case esConjunction.Or: sql += " OR "; break; case esConjunction.AndNot: sql += " AND NOT "; break; case esConjunction.OrNot: sql += " OR NOT "; break; } continue; } Dictionary <string, SQLiteParameter> types = null; if (comparisonData.Column.Query != null) { IDynamicQueryInternal iLocalQuery = comparisonData.Column.Query as IDynamicQueryInternal; types = Cache.GetParameters(iLocalQuery.DataID, (esProviderSpecificMetadata)iLocalQuery.ProviderMetadata, (esColumnMetadataCollection)iLocalQuery.Columns); } if (comparisonData.IsLiteral) { if (comparisonData.Column.Name[0] == '<') { sql += comparisonData.Column.Name.Substring(1, comparisonData.Column.Name.Length - 2); } else { sql += comparisonData.Column.Name; } continue; } if (comparisonData.ComparisonColumn.Name == null) { subQuery = comparisonData.Value as esDynamicQuery; if (subQuery == null) { if (comparisonData.Column.Name != null) { IDynamicQueryInternal iColQuery = comparisonData.Column.Query as IDynamicQueryInternal; esColumnMetadataCollection columns = (esColumnMetadataCollection)iColQuery.Columns; compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString(); } else { compareTo = Delimiters.Param + "Expr" + (++std.pindex).ToString(); } } else { // It's a sub query compareTo = GetSubquerySearchCondition(subQuery) + " (" + BuildQuery(std, subQuery) + ") "; requiresParam = false; } } else { compareTo = GetColumnName(comparisonData.ComparisonColumn); requiresParam = false; } switch (comparisonData.Operand) { case esComparisonOperand.Exists: sql += " EXISTS" + compareTo; break; case esComparisonOperand.NotExists: sql += " NOT EXISTS" + compareTo; break; //----------------------------------------------------------- // Comparison operators, left side vs right side //----------------------------------------------------------- case esComparisonOperand.Equal: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " = " + compareTo; } else { sql += compareTo + " = " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.NotEqual: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " <> " + compareTo; } else { sql += compareTo + " <> " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.GreaterThan: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " > " + compareTo; } else { sql += compareTo + " > " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.LessThan: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " < " + compareTo; } else { sql += compareTo + " < " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.LessThanOrEqual: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " <= " + compareTo; } else { sql += compareTo + " <= " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.GreaterThanOrEqual: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " >= " + compareTo; } else { sql += compareTo + " >= " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.Like: string esc = comparisonData.LikeEscape.ToString(); if (String.IsNullOrEmpty(esc) || esc == "\0") { sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo; std.needsStringParameter = true; } else { sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo; sql += " ESCAPE '" + esc + "'"; std.needsStringParameter = true; } break; case esComparisonOperand.NotLike: esc = comparisonData.LikeEscape.ToString(); if (String.IsNullOrEmpty(esc) || esc == "\0") { sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo; std.needsStringParameter = true; } else { sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo; sql += " ESCAPE '" + esc + "'"; std.needsStringParameter = true; } break; case esComparisonOperand.Contains: sql += " CONTAINS(" + GetColumnName(comparisonData.Column) + ", " + compareTo + ")"; std.needsStringParameter = true; break; case esComparisonOperand.IsNull: sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NULL"; requiresParam = false; break; case esComparisonOperand.IsNotNull: sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NOT NULL"; requiresParam = false; break; case esComparisonOperand.In: case esComparisonOperand.NotIn: { if (subQuery != null) { // They used a subquery for In or Not sql += ApplyWhereSubOperations(std, query, comparisonData); sql += (comparisonData.Operand == esComparisonOperand.In) ? " IN" : " NOT IN"; sql += compareTo; } else { comma = String.Empty; if (comparisonData.Operand == esComparisonOperand.In) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " IN ("; } else { sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT IN ("; } foreach (object oin in comparisonData.Values) { string str = oin as string; if (str != null) { // STRING sql += comma + Delimiters.StringOpen + str + Delimiters.StringClose; comma = ","; } else if (null != oin as System.Collections.IEnumerable) { // LIST OR COLLECTION OF SOME SORT System.Collections.IEnumerable enumer = oin as System.Collections.IEnumerable; if (enumer != null) { System.Collections.IEnumerator iter = enumer.GetEnumerator(); while (iter.MoveNext()) { object o = iter.Current; string soin = o as string; if (soin != null) { sql += comma + Delimiters.StringOpen + soin + Delimiters.StringClose; } else { sql += comma + Convert.ToString(o); } comma = ","; } } } else { // NON STRING OR LIST sql += comma + Convert.ToString(oin); comma = ","; } } sql += ")"; requiresParam = false; } } break; case esComparisonOperand.Between: SQLiteCommand sqlCommand = std.cmd as SQLiteCommand; sql += ApplyWhereSubOperations(std, query, comparisonData) + " BETWEEN "; sql += compareTo; if (comparisonData.ComparisonColumn.Name == null) { sqlCommand.Parameters.AddWithValue(compareTo, comparisonData.BetweenBegin); } if (comparisonData.ComparisonColumn2.Name == null) { IDynamicQueryInternal iColQuery = comparisonData.Column.Query as IDynamicQueryInternal; esColumnMetadataCollection columns = (esColumnMetadataCollection)iColQuery.Columns; compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString(); sql += " AND " + compareTo; sqlCommand.Parameters.AddWithValue(compareTo, comparisonData.BetweenEnd); } else { sql += " AND " + Delimiters.ColumnOpen + comparisonData.ComparisonColumn2 + Delimiters.ColumnClose; } requiresParam = false; break; } if (requiresParam) { SQLiteParameter p; if (comparisonData.Column.Name != null) { p = types[comparisonData.Column.Name]; p = Cache.CloneParameter(p); p.ParameterName = compareTo; p.Value = comparisonData.Value; if (std.needsStringParameter) { p.DbType = DbType.String; } else if (std.needsIntegerParameter) { p.DbType = DbType.Int32; } else if (std.needsDoubleParameter) { p.DbType = DbType.Double; } } else { p = new SQLiteParameter(compareTo, comparisonData.Value); } std.cmd.Parameters.Add(p); } } } return(sql); }
protected static string GetSelectStatement(StandardProviderParameters std, esDynamicQuery query) { string sql = String.Empty; string comma = String.Empty; bool selectAll = true; IDynamicQueryInternal iQuery = query as IDynamicQueryInternal; if (query.distinct) { sql += " DISTINCT "; } if (iQuery.InternalSelectColumns != null) { selectAll = false; foreach (esExpression expressionItem in iQuery.InternalSelectColumns) { if (expressionItem.Query != null) { IDynamicQueryInternal iSubQuery = expressionItem.Query as IDynamicQueryInternal; sql += comma; if (iSubQuery.SubQueryAlias == string.Empty) { sql += iSubQuery.JoinAlias + ".*"; } else { iSubQuery.IsInSubQuery = true; sql += " (" + BuildQuery(std, expressionItem.Query as esDynamicQuery) + ") AS " + iSubQuery.SubQueryAlias; iSubQuery.IsInSubQuery = false; } comma = ","; } else { sql += comma; string columnName = expressionItem.Column.Name; if (columnName != null && columnName[0] == '<') { sql += columnName.Substring(1, columnName.Length - 2); } else { sql += GetExpressionColumn(std, query, expressionItem, false, true); } comma = ","; } } sql += " "; } if (query.countAll) { selectAll = false; sql += comma; sql += "COUNT(*)"; if (query.countAll != null) { // Need DBMS string delimiter here sql += " AS " + Delimiters.StringOpen + query.countAll + Delimiters.StringClose; } } if (selectAll) { sql += "*"; } return(sql); }
/// <summary> /// /// </summary> private void AssignProviderMetadata(esDynamicQuery query, List <esDynamicQuery> beenThere) { if (beenThere.Contains(query)) { return; } beenThere.Add(query); esDynamicQuery theQuery = query as esDynamicQuery; IDynamicQueryInternal iQuery = query as IDynamicQueryInternal; if (theQuery != null) { esConnection conn = theQuery.es2.Connection; if (iQuery.ProviderMetadata == null) { esProviderSpecificMetadata providerMetadata = theQuery.Meta.GetProviderMetadata(conn.ProviderMetadataKey); iQuery.DataID = theQuery.Meta.DataID; iQuery.Columns = theQuery.Meta.Columns; iQuery.ProviderMetadata = providerMetadata; } iQuery.Catalog = conn.Catalog; iQuery.Schema = conn.Schema; } // This code is for proxies as they are unable to work with column and provider metadata // until serialized back to the server if (iQuery.SelectAll) { foreach (esColumnMetadata col in (esColumnMetadataCollection)iQuery.Columns) { esQueryItem item = new esQueryItem(this, col.Name, col.esType); query.Select(item); } } else { List <esQueryItem> columns = iQuery.SelectAllExcept; if (columns != null) { foreach (esColumnMetadata col in (esColumnMetadataCollection)iQuery.Columns) { bool found = false; for (int i = 0; i < columns.Count; i++) { if (col.Name == (string)columns[i]) { found = true; break; } } if (found) { continue; } esExpression item = new esQueryItem(this, col.Name, col.esType); query.Select(item); } } } foreach (esDynamicQuery subQuery in iQuery.queries.Values) { AssignProviderMetadata(subQuery, beenThere); } if (iQuery.InternalSetOperations != null) { foreach (esSetOperation setOperation in iQuery.InternalSetOperations) { AssignProviderMetadata(setOperation.Query, beenThere); } } }