Abstract: CAML is a dialect of the ML programming language family like may descendants of ML. It has been applied in lots of popular platforms like SharePoint. It has been more and more popular. However, I cannot find the way to apply CAML to query database. In this article, I will introduce one way to solve this problem.
- Architecture
The root way to support the database fetching by CAML query is to convert the CAML query to the relative SQL query. So we need to parse the CAML query syntax and map to the relative SQL query syntax.
- CAML Query Compiler
The CAML query compiler is the most important thing in this solution. It is the key to implement the database fetching by CAML query. At first, we need to define the mapping relationship between CAML query syntax and SQL query syntax like the following table:
| CAML Query Syntax | Description | SQL Query Syntax |
| Eq | Equal to the value | = |
| Neq | Not equal to the value | <> |
| Gt | Great than the value | > |
| Lt | Less than the value | < |
| BeginsWith | One string begins with some words | Like %* |
| Contains | One string contains one sub string. | Like %*% |
| IsNotNull | One column is not null | Is not null |
| IsNull | One column is null | Is null |
| ASC | Order by asc | asc |
| Desc | Order by desc | desc |
| And | Join the multiple conditions | and |
| Or | Union the multiple conditions | or |
So we can start to build the compiler. We need first to break the CAML query string into small pieces of words. The words should be variable name, algorithm operator and so on. Then we need iterate all words and compose them to generate the syntax tokens which is stored in one syntax tree like the below code:
private void Tokenize(IList<string> syntaxes, Stack<CamlToken> stack, ref int index)
{
switch (syntaxes[index].ToLower())
{
case “(“:
{
CamlToken token = new CamlToken()
{
Name = “Parenthese“,
Operator = CamlOperator.LeftBracket
};
PushLeftBracket(stack, token);
break;
}
case “)”:
{
CamlToken token = new CamlToken()
{
Name = “Parenthese“,
Operator = CamlOperator.RightBraket
};
PushRightBracket(stack, token);
break;
}
case “or“:
{
CamlToken token = new CamlToken()
{
Name = “Or“,
Operator = CamlOperator.Or
};
PushOr(stack, token);
break;
}
case “and“:{
CamlToken token = new CamlToken()
{
Name = “And“,
Operator = CamlOperator.And
};
PushAnd(stack, token);
break;
}
case “,”:
{
CamlToken token = new CamlToken()
{
Name = “,”,
Operator = CamlOperator.Comma
};
PushComma(stack, token);
break;
}
default:
{
CamlOperator signal;
if(Enum.TryParse<CamlOperator>(syntaxes[index],true,out signal))
{
CamlToken token = new CamlToken();
switch (signal)
{
case CamlOperator.Eq:
case CamlOperator.Geq:
case CamlOperator.Gt:
case CamlOperator.Neq:
case CamlOperator.Leq:
case CamlOperator.Lt:
case CamlOperator.BeginsWith:
case CamlOperator.Contains:
{
token.Operator = signal;
token.Name = stack.Pop().Name;
index++;
token.Value = new List<object>()
{ syntaxes[index] };
PushOthers(stack, token);
break;
}
case CamlOperator.IsNotNull:
case CamlOperator.IsNull:
case CamlOperator.ASC:
case CamlOperator.DESC:
{
token.Operator = signal;
token.Name = stack.Pop().Name;
PushOthers(stack, token);
break;
}
default: break;
}
}
else
{
PushVariable(stack, new CamlToken() { Name = syntaxes[index], Operator = CamlOperator.Primitive });
}
break;
}
}
}
At final, we got the CAML query token tree. We only need to map the CAML query token to SQL query token. Then you can get the SQL query:
private void ConvertToSQLQuery(CamlToken token, StringBuilder buffer)
{
switch (token.Operator)
{
case CamlOperator.Primitive:
{
buffer.Append(token.Name);
break;
}
case CamlOperator.And:
{
IList<object> values = token.Value;
if (values.Count > 1)
{
var first = values.First() as CamlToken;
ConvertToSQLQuery(first, buffer);
buffer.AppendFormat(” {0} “, “and”);
var second = values.Last() as CamlToken;
ConvertToSQLQuery(second, buffer);
}
break;
}
case CamlOperator.Comma:
{
IList<object> values = token.Value;
if (values.Count > 1)
{
var first = values.First() ;
if (first is CamlToken)
{
ConvertToSQLQuery(first as CamlToken, buffer);
}
else
{
buffer.Append(first.ToString());
}
buffer.AppendFormat(“{0}”, “,”);
var second = values.Last();
if (second is CamlToken)
{
ConvertToSQLQuery(second as CamlToken, buffer);
}
else
{
buffer.Append(second.ToString());
}
}
break;
}
case CamlOperator.Or:
{
IList<object> values = token.Value;
if (values.Count > 1)
{
var first = values.First() as CamlToken;
ConvertToSQLQuery(first, buffer);
buffer.AppendFormat(” {0} “, “or”);
var second = values.Last() as CamlToken;
ConvertToSQLQuery(second, buffer);
}
break;
}
case CamlOperator.RightBraket:
case CamlOperator.LeftBracket:
{
IList<object> values = token.Value;
if (values!=null&&values.Count > 0)
{
buffer.AppendFormat(“(“);
foreach(var value in values)
{
if (value is CamlToken)
{
ConvertToSQLQuery(value as CamlToken, buffer);
}
else
{
buffer.Append(value.ToString());
}
}
buffer.AppendFormat(“)”);
}
break;
}
case CamlOperator.ASC:
{
buffer.AppendFormat(“{0} {1}”, token.Name, “asc”);
break;
}
case CamlOperator.DESC:
{
buffer.AppendFormat(“{0} {1}”, token.Name, “desc”);
break;
}
case CamlOperator.Eq:
{
buffer.AppendFormat(“{0} = {1}”, token.Name, token.Value.First());
break;
}
case CamlOperator.Geq:
{
buffer.AppendFormat(“{0} >= {1}”, token.Name, token.Value.First());
break;
}
case CamlOperator.Gt:
{
buffer.AppendFormat(“{0} > {1}”, token.Name, token.Value.First());
break;
}
case CamlOperator.Neq:
{
buffer.AppendFormat(“{0} <> {1}”, token.Name, token.Value.First());
break;
}
case CamlOperator.Leq:
{
buffer.AppendFormat(“{0} <= {1}”,token.Name, token.Value.First());
break;
}
case CamlOperator.Lt:
{
buffer.AppendFormat(“{0} < {1}”, token.Name, token.Value.First());
break;
}
case CamlOperator.BeginsWith:
case CamlOperator.Contains:
{
buffer.AppendFormat(“{0} like {1}”, token.Name, token.Value.First());
break;
}
case CamlOperator.IsNotNull:
{
buffer.AppendFormat(“{0} is not null”, token.Name);
break;
}
case CamlOperator.IsNull:
{
buffer.AppendFormat(“{0} is null”, token.Name);
break;
}
default: break;
}
}
