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