Using SQLite to eval expressions

_References

SQL Fiddle as example: http://sqlfiddle.com/#!7/fe32d/12
  
SQLite syntax specifications: http://www.sqlite.org/lang_expr.html

ExpressionEvaluator.as

package
{
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	import flash.filesystem.File;

	public class ExpressionEvaluator
	{
		
		private var _dbFile:File;
		
		public function ExpressionEvaluator()
		{
		}
		
		public function dispose():void
		{
			if (_dbFile) _dbFile = null;
		}
			
		
		public function evaluateExpression(sql:String, values:Object=null):String
		{
			// Inject values in the sql request
			var sqlText:String = replaceTokens(sql, values || {});
			
			var dbConn:SQLConnection = new SQLConnection();
			dbConn.open(dbFile);
			
			var sqlStatement:SQLStatement = new SQLStatement();
			sqlStatement.sqlConnection  = dbConn;
			sqlStatement.text = sqlText;
			
			sqlStatement.addEventListener(SQLErrorEvent.ERROR, sqlErrorHandler); 
			
			sqlStatement.execute();
			var result:SQLResult = sqlStatement.getResult(); 
			var compData:Object = result.data[0];
			var out:String = compData.out;
			dbConn.close();
			return out;
		}
		
		protected function get dbFile():File
		{
			if (_dbFile == null) 
			{
				_dbFile = File.applicationStorageDirectory.resolvePath("SqlEvalSwap.db");
			}
			return _dbFile;
		}
		
		protected function sqlErrorHandler(event:SQLErrorEvent):void
		{
			trace("sqlErrorHandler(event) " + event);
			
		}
		
		
		
		/**
		 * Take a string, search for ${tokens} or %tokens% and replace them 
		 * with the values in the supplied object.
		 * @param text - text you would like to search for tokens
		 * @param paramObj - an object to use to find and replace tokens with
		 * @param replaceUndefineds - string to use when undefined are found
		 * @param overrideRegEx - override the default regex pattern
		 * @return 
		 * 
		 */		
		private function replaceTokens(text:String, paramObj:Object=null, replaceUndefineds:String="", overrideRegEx:RegExp=null):String
		{
			if (!paramObj) return text;
			
			var pattern:RegExp = overrideRegEx ? overrideRegEx : DOUBLE_AT_PATTERN;
			var cleanText:String = text.replace(pattern, function():* { return paramObj[arguments[1]] } );
			if (replaceUndefineds != null)
				cleanText = cleanText.replace(/undefined/gi, replaceUndefineds);
			
			return cleanText;
		}
		
		private static const CURLY_BRACES_PATTERN:RegExp 	= /\$\{([\w'-]+)\}/g;
		private static const BRACKET_PATTERN:RegExp 		= /\$\[([\w'-]+)\]/g;
		private static const DOUBLE_AT_PATTERN:RegExp 		= /%([\w'-]+)%/g;
	
	}
}

rules-formatting-sample.json

{
  "defaultOutput":"default",
  "rules":[
    {
      "rule":"NOT(%hasBeenSubscribed%) AND %isGuest% AND (%nbVisits% % 3 =0)",
      "output":"promoGuest"
    }, {
      "rule":"%hasBeenSubscribed%  AND %isGuest% AND (%nbVisits% % 3 =0)",
      "output":"promoPastSubscriber"
    }
  ]
}

sample-sql-structure.sql

SELECT
CASE
  WHEN (NOT(1) AND 1 AND (3 %3=0))
    THEN 'promoGuest'
  WHEN (    1  AND 1 AND (3 %3=0))
    THEN 'promoPastSubscriber'
  ELSE 'default'
END
AS out

SampleSqlCall.as

// Sample use
public function testSQLEval():void
{
	var values:Object = {isGuest:1, nbVisits:3, hasBeenSubscribed:1};
	
	var sqlText:String = "SELECT " +
		"CASE " +
		"  WHEN (NOT(%hasBeenSubscribed%) AND %isGuest% AND (%nbVisits% % 3 =0)) " +
		"    THEN 'promoGuest' " +
		"  WHEN (    %hasBeenSubscribed%  AND %isGuest% AND (%nbVisits% % 3 =0)) " +
		"    THEN 'promoPastSubscriber' " +
		"  ELSE 'default' " +
		"  END " +
		"AS out";
	
	var evaluator:ExpressionEvaluator = new ExpressionEvaluator();
	var result:String = evaluator.evaluateExpression(sqlText, values);
	trace("result :"+result);
}

sql-template.sql

SELECT
  CASE
    WHEN (NOT(%hasBeenSubscribed%) AND %isGuest% AND (%nbVisits% % 3 =0))
      THEN 'promoGuest'
    WHEN (    %hasBeenSubscribed%  AND %isGuest% AND (%nbVisits% % 3 =0))
      THEN 'promoPastSubscriber'
    ELSE 'default'
  END
AS out