Development

How to fetch database by CAML query

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.

  1. 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.

1

  1. 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;

}

         caseor“:

{

CamlToken token = new CamlToken()

{

Name = “Or“,

Operator = CamlOperator.Or

};

PushOr(stack, token);

  break;

}

      caseand“:{

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;

}

}

 

 

About the Author

More from this Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to the Weekly Blog Digest:

Sign Up
Categories