public ResourceQuery OrderBy(ResourceOrderClause.SortTable table, string column, SortDirection direction) { State s = new State(_state); ResourceOrderClause roc = new ResourceOrderClause(); roc.table = table; roc.column = column; roc.dir = direction; if (s.orderClauses == null) { s.orderClauses = new ResourceOrderClause[] { roc }; } else { List <ResourceOrderClause> temp = new List <ResourceOrderClause>(s.orderClauses); temp.Add(roc); s.orderClauses = temp.ToArray(); } return(new ResourceQuery(s)); }
public ResourceQuery OrderBy(ResourceOrderClause.SortTable table, string column, SortDirection direction) { State s = new State(_state); ResourceOrderClause roc = new ResourceOrderClause(); roc.table = table; roc.column = column; roc.dir = direction; if(s.orderClauses == null) { s.orderClauses = new ResourceOrderClause[] { roc }; } else { List<ResourceOrderClause> temp = new List<ResourceOrderClause>(s.orderClauses); temp.Add(roc); s.orderClauses = temp.ToArray(); } return new ResourceQuery(s); }
public string ToString(string methodName) { StringBuilder querySb = new StringBuilder(); const string RESOURCE_ALIAS = "r"; const string RREVISION_ALIAS = "rr"; const string RESOURCECONTENTS_ALIAS = "rc"; string queryColumnAlias = _state.populateRevisions ? RREVISION_ALIAS : RESOURCE_ALIAS; //Query Header if (!string.IsNullOrEmpty(methodName)) { querySb.AppendFormat(" /* Resources::{0} */", methodName); } //Columns if (!_state.populateResourceCountOnly) { querySb.AppendFormat("\n SELECT {0}", GetColumns(queryColumnAlias)); if (_state.populateContent) { querySb.AppendFormat(", {0}.*", RESOURCECONTENTS_ALIAS); } } else { querySb.AppendFormat("\n SELECT count(*)"); } //Main table querySb.AppendFormat("\n FROM resources {0}", RESOURCE_ALIAS); //Joins if (_state.populateRevisions) { querySb.AppendFormat("\n JOIN resourcerevs {0}\n\t ON {1}.res_id = {0}.resrev_res_id", RREVISION_ALIAS, RESOURCE_ALIAS); } if (_state.populateContent) { querySb.AppendFormat("\n LEFT JOIN resourcecontents {0}\n\t ON {1}.resrev_content_id = {0}.rescontent_id", RESOURCECONTENTS_ALIAS, queryColumnAlias); } //Where clauses querySb.AppendFormat("\n WHERE 1=1"); if (!ArrayUtil.IsNullOrEmpty(_state.resourceIds)) { string resourceIdsStr = _state.resourceIds.ToCommaDelimitedString(); querySb.AppendFormat("\n AND {0}.res_id in ({1})", RESOURCE_ALIAS, resourceIdsStr); } if (!ArrayUtil.IsNullOrEmpty(_state.changeSetId)) { string transactionIdsStr = _state.changeSetId.ToCommaDelimitedString(); querySb.AppendFormat("\n AND {0}.resrev_changeset_id in ({1})", RESOURCE_ALIAS, transactionIdsStr); } if (_state.resourceDeletionFilter != DeletionFilter.ANY) { querySb.AppendFormat("\n AND {0}.res_deleted = {1}", RESOURCE_ALIAS, (int)_state.resourceDeletionFilter); } if (!ArrayUtil.IsNullOrEmpty(_state.resourceTypes)) { var resourceTypesStr = _state.resourceTypes.Select(x => (int)x).ToCommaDelimitedString(); querySb.AppendFormat("\n AND {0}.res_type in ({1})", RESOURCE_ALIAS, resourceTypesStr); } if (_state.changeType != ResourceBE.ChangeOperations.UNDEFINED) { querySb.Append("\n AND ( "); bool firstChange = true; foreach (ResourceBE.ChangeOperations c in Enum.GetValues(typeof(ResourceBE.ChangeOperations))) { if ((_state.changeType & c) == c && c != ResourceBE.ChangeOperations.UNDEFINED) { if (!firstChange) { querySb.Append(" OR "); } querySb.AppendFormat("{0}.resrev_change_mask & {1} = {1}", queryColumnAlias, (ushort)c); firstChange = false; } } querySb.Append(")"); } if (_state.resourceRevision != ResourceBE.HEADREVISION) { if (_state.resourceRevision > ResourceBE.HEADREVISION) { querySb.AppendFormat("\n AND {0}.resrev_rev = {1}", RREVISION_ALIAS, _state.resourceRevision); } else if (_state.resourceRevision < ResourceBE.HEADREVISION) { querySb.AppendFormat("\n AND {0}.resrev_rev = {1}.res_headrev - {2}", RREVISION_ALIAS, RESOURCE_ALIAS, Math.Abs(_state.resourceRevision)); } } if (_state.parentType.HasValue || !ArrayUtil.IsNullOrEmpty(_state.parentIds)) { string parentIdsStr = (ArrayUtil.IsNullOrEmpty(_state.parentIds) ? "is not null" : "in (" + _state.parentIds.ToCommaDelimitedString() + ")"); switch (_state.parentType) { case ResourceBE.ParentType.PAGE: querySb.AppendFormat("\n AND {0}.resrev_parent_page_id {1}", RESOURCE_ALIAS, parentIdsStr); break; case ResourceBE.ParentType.USER: querySb.AppendFormat("\n AND {0}.resrev_parent_user_id {1}", RESOURCE_ALIAS, parentIdsStr); break; case ResourceBE.ParentType.SITE: //SITE resources always have a parent_id=null, parent_user_id=null, parent_page_id=null querySb.AppendFormat("\n AND {0}.resrev_parent_id is null", RESOURCE_ALIAS); querySb.AppendFormat("\n AND {0}.resrev_parent_page_id is null", RESOURCE_ALIAS); querySb.AppendFormat("\n AND {0}.resrev_parent_user_id is null", RESOURCE_ALIAS); break; default: querySb.AppendFormat("\n AND {0}.resrev_parent_id {1}", RESOURCE_ALIAS, parentIdsStr); break; } } if (!ArrayUtil.IsNullOrEmpty(_state.nameFilter)) { querySb.Append("\n " + BuildNameFilterWhereClause(_state.nameFilter, RESOURCE_ALIAS)); } //Sort if (!ArrayUtil.IsNullOrEmpty(_state.orderClauses)) { StringBuilder orderList = new StringBuilder(); for (int i = 0; i < _state.orderClauses.Length; i++) { ResourceOrderClause roc = _state.orderClauses[i]; if (i > 0) { orderList.Append(","); } string tableAlias = string.Empty; switch (roc.table) { case ResourceOrderClause.SortTable.RESOURCES: tableAlias = RESOURCE_ALIAS + "."; break; case ResourceOrderClause.SortTable.RESOURCEREVISIONS: tableAlias = RREVISION_ALIAS + "."; break; } orderList.AppendFormat("{0}{1} {2}", tableAlias, roc.column, roc.dir.ToString()); } querySb.AppendFormat("\n ORDER BY {0}", orderList); } //Limit+offset uint?limit = _state.limit; if (limit == null && _state.offset != null) { limit = uint.MaxValue; } if (limit != null) { querySb.AppendFormat("\n LIMIT {0}", limit); } if (_state.offset != null) { querySb.AppendFormat("\n OFFSET {0}", _state.offset.Value); } return(querySb.ToString()); }