/*============================================================================
  File:    StringFilters.cs

  Summary: Implements string filtering functions for use in MDX Queries.

  Date:    July 12, 2006

  ----------------------------------------------------------------------------
  This file is part of the Analysis Services Stored Procedure Project.
  http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
  KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
  IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/


using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.AnalysisServices.AdomdServer;
using Tuple = Microsoft.AnalysisServices.AdomdServer.Tuple; //resolves ambiguous reference in .NET 4 with System.Tuple


namespace ASStoredProcs
{
    /// <summary>
    /// 
    /// </summary>
    public class StringFilters
    {
        static Hashtable regExCache = new Hashtable( new RegExCacheIndexComparer());

		#region " Public 'Contains' functions"

		/// <summary>
		/// Contains are 3 times slower than using VB.InStr
		/// and should be used only when caseSensitive = true (InStr always case insensitive)
		/// </summary>
		/// <param name="valueToMatch"></param>
		/// <param name="substring"></param>
		/// <returns></returns>
		[SafeToPrepare(true)]
		public Boolean IsContain(String valueToMatch, String substring)
		{
			return IsContain(valueToMatch, substring, false);
		}

		/// <summary>
		/// Contains are 3 times slower than using VB.InStr
		/// and should be used only when caseSensitive = true (InStr always case insensitive)
		/// </summary>
		/// <param name="valueToMatch"></param>
		/// <param name="substring"></param>
		/// <param name="caseSensitive"></param>
		/// <returns></returns>
		[SafeToPrepare(true)]
		public Boolean IsContain(String valueToMatch, String substring, Boolean caseSensitive)
		{
			Context.TraceEvent(100, 0, "IsContain: Starting");

			// Contains(...) with .ToLower().ToUpper() ~100 times faster then IndexOf(..., StringComparison.CurrentCultureIgnoreCase), but IndexOf is enough fast in absolute ticks
			// valueToMatch.IndexOf(substring, StringComparison.CurrentCultureIgnoreCase) >= 0;
			// can be replaced with valueToMatch.ToLower().ToUpper().Contains(substring.ToLower().ToUpper())
			// as in some laguages (french, turkish) there is not 1-1 relation between upcase and lowcase chars
 			// but it will be durty hack :)
			bool result = caseSensitive
								? valueToMatch.Contains(substring)
								: valueToMatch.IndexOf(substring, StringComparison.CurrentCultureIgnoreCase) >= 0;

			Context.TraceEvent(100, 0, "IsContain: Finished");
			return result;
		}

		/// <summary>
		/// Contains are 3 times slower than using VB.InStr
		/// and should be used only when caseSensitive = true (InStr always case insensitive)
		/// </summary>
		/// <param name="setToFilter"></param>
		/// <param name="substring"></param>
		/// <param name="exp"></param>
		/// <returns></returns>
		[SafeToPrepare(true)]
		public static Set Contains(Set setToFilter, String substring, Expression exp)
		{
			return Contains(setToFilter, substring, exp, false);
		}

		/// <summary>
		/// Contains are 3 times slower than using VB.InStr
		/// and should be used only when caseSensitive = true (InStr always case insensitive)
		/// </summary>
		/// <param name="setToFilter"></param>
		/// <param name="substring"></param>
		/// <param name="exp"></param>
		/// <param name="caseSensitive"></param>
		/// <returns></returns>
		[SafeToPrepare(true)]
		public static Set Contains(Set setToFilter, String substring, Expression exp, Boolean caseSensitive)
		{
			Context.TraceEvent(100, 0, "Contains: Starting");

			if (substring.Length == 0)
			{
				Context.TraceEvent(100, 0, "Contains: Finished (No substring parameter)");
				return setToFilter;
			}

			if (setToFilter == null)
			{
				throw new ArgumentNullException("setToFilter");
			}
			else
			{
				using (SetBuilder sb = new SetBuilder())
				{
					foreach (Tuple t in setToFilter.Tuples)
					{
						string val = (string) exp.Calculate(t);
						bool match = caseSensitive
								            ? val.Contains(substring)
								            : val.IndexOf(substring, StringComparison.CurrentCultureIgnoreCase) >= 0;
						if (match)
						{
							sb.Add(t);
						}
					}
					Context.TraceEvent(100, sb.Count, "Contains: Finished (returning " + sb.Count.ToString() + " tuples");
					return sb.ToSet();
				}
			}
		}

		/// <summary>
		/// Contains are 3 times slower than using VB.InStr
		/// and should be used only when caseSensitive = true (InStr always case insensitive)
		/// </summary>
		/// <param name="setToFilter"></param>
		/// <param name="substring"></param>
		/// <param name="exp"></param>
		/// <param name="start"></param>
		/// <param name="count"></param>
		/// <returns></returns>
		[SafeToPrepare(true)]
		public static Set Contains(Set setToFilter, String substring, Expression exp, int start, int count)
		{
			return Contains(setToFilter, substring, exp, false, start, count);
		}

	    /// <summary>
		/// Contains are 3 times slower than using VB.InStr
		/// and should be used only when caseSensitive = true (InStr always case insensitive)
		/// </summary>
	    /// <param name="setToFilter"></param>
		/// <param name="substring"></param>
	    /// <param name="exp"></param>
	    /// <param name="start">The index of the first tuple in setToFilter to start pattern matching serch for</param>
	    /// <param name="count">Tuples count to check for from the start</param>
	    /// <param name="caseSensitive"></param>
	    /// <returns></returns>
	    [SafeToPrepare(true)]
		public static Set Contains(Set setToFilter, String substring, Expression exp, Boolean caseSensitive, int start, int count)
		{
			Context.TraceEvent(100, 0, "Contains: Starting");

			if (substring.Length == 0)
			{
				Context.TraceEvent(100, 0, "Contains: Finished (No substring parameter)");
				return setToFilter;
			}

			if (setToFilter == null)
			{
				throw new ArgumentNullException("setToFilter");
			}

			#region start-end SubSet adjustment / boundary checks

			if (start < 0)
			{
				start = 0;
			}

			if (count <= 0)
			{
				Context.TraceEvent(100, 0, "Contains: Finished (count parameter value is less or equal 0)");
				return new SetBuilder().ToSet();
			}

			#endregion

			using (SetBuilder sb = new SetBuilder())
			{
				int indexBefore = 0;
				int itemsCount = 0;
				bool takeIt = start == 0;

				foreach (Tuple t in setToFilter.Tuples)
				{
					string val = (string) exp.Calculate(t);
					bool match = caseSensitive
										? val.Contains(substring)
										: val.IndexOf(substring, StringComparison.CurrentCultureIgnoreCase) >= 0;
					if (match)
					{
						if (takeIt)
						{
							sb.Add(t);
							itemsCount++;
							if (itemsCount == count)
							{
								break;
							}
						}
						else
						{
							indexBefore++;
							if (indexBefore == start)
							{
								takeIt = true;
							}
						}
					}

				}

				Context.TraceEvent(100, sb.Count, "Contains: Finished (returning " + sb.Count.ToString() + " tuples");
				return sb.ToSet();
			}
		}
		#endregion

        #region " Public 'Like' functions"
        /// <summary>
        /// 
        /// </summary>
        /// <param name="valueToMatch">This is a string expression which </param>
        /// <param name="pattern">This paramter uses a pattern in the same form as the T-SQL LIKE operator</param>
        /// <returns>Boolean</returns>
        [SafeToPrepare(true)]
        public Boolean IsLike(String valueToMatch, String pattern)
        {
            return IsLike(valueToMatch, pattern, false);
		}

        [SafeToPrepare(true)]
        public Boolean IsLike(String valueToMatch, String pattern, Boolean caseSensitive)
        {
			bool result;
			Context.TraceEvent(100, 0, "IsLike: Starting");
			if (caseSensitive)
	        {
				// todo - write case sensitive SqlLikeStringUtilities.SqlLike

		        // todo - cache regex objects here
		        RegexOptions optRegex = RegexOptions.Compiled;
				// now it's always case sensitive - see "if"
		        //if (!caseSensitive)
		        //{
			    //    optRegex = optRegex | RegexOptions.IgnoreCase;
		        //}
		        Regex r = getCachedRegEx(LikeToRegEx(pattern), optRegex);
		        result = r.Match(valueToMatch).Success;
	        }
			else
			{
				result = SqlLikeStringUtilities.SqlLike(pattern, valueToMatch);
			}
			Context.TraceEvent(100, 0, "IsLike: Finished");
			return result;
        }

        [SafeToPrepare(true)]
        public static Set Like(Set setToFilter, String pattern, Expression exp)
        {
			Context.TraceEvent(100, 0, "Like: Starting");

			if (pattern.Length == 0)
			{
				Context.TraceEvent(100, 0, "Like: Finished (No pattern parameter)");
				return setToFilter;
			}

			if (setToFilter == null)
			{
				throw new ArgumentNullException("setToFilter");
			}
			else
			{
				using (SetBuilder sb = new SetBuilder())
				{
					foreach (Tuple t in setToFilter.Tuples)
					{
						string val = (string)exp.Calculate(t);
						if (SqlLikeStringUtilities.SqlLike(pattern, val))
						{
							sb.Add(t);
						}
					}
					Context.TraceEvent(100, sb.Count, "Like: Finished (returning " + sb.Count.ToString() + " tuples");
					return sb.ToSet();
				}
			}
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="setToFilter"></param>
		/// <param name="pattern"></param>
		/// <param name="exp"></param>
		/// <param name="start">The index of the first tuple in setToFilter to start pattern matching serch for</param>
		/// <param name="count">Tuples count to check for from the start</param>
		/// <returns></returns>
		[SafeToPrepare(true)]
		public static Set Like(Set setToFilter, String pattern, Expression exp, int start, int count)
		{
			Context.TraceEvent(100, 0, "Like: Starting");

			if (pattern.Length == 0)
			{
				Context.TraceEvent(100, 0, "Like: Finished (No pattern parameter)");
				return setToFilter;
			}

			if (setToFilter == null)
			{
				throw new ArgumentNullException("setToFilter");
			}

			#region start-end SubSet adjustment / boundary checks

			if (start < 0)
			{
				start = 0;
			}

			if (count <= 0)
			{
				Context.TraceEvent(100, 0, "LikeFilter: Finished (count parameter value is less or equal 0)");
				return new SetBuilder().ToSet();
			}

			#endregion
			
			using (SetBuilder sb = new SetBuilder())
			{
				int indexBefore = 0;
				int itemsCount = 0;
				bool takeIt = start == 0;
				foreach (Tuple t in setToFilter.Tuples)
				{
					string val = (string)exp.Calculate(t);
					if (SqlLikeStringUtilities.SqlLike(pattern, val))
					{
						if (takeIt)
						{
							sb.Add(t);
							itemsCount++;
							if (itemsCount == count)
							{
								break;
							}
						}
						else
						{
							indexBefore++;
							if (indexBefore == start)
							{
								takeIt = true;
							}
						}
					}

				}
				Context.TraceEvent(100, sb.Count, "Like: Finished (returning " + sb.Count.ToString() + " tuples");
				return sb.ToSet();
			}
		}

		[SafeToPrepare(true)]
		public static Set Like(Set setToFilter, String pattern, Expression exp, Boolean caseSensitive, int start, int count)
		{
			if (caseSensitive)
			{
				// todo made case sensitive variant of Like
				return RegExFilter(setToFilter, LikeToRegEx(pattern), exp, true, start, count);
			}
			return Like(setToFilter, pattern, exp, start, count);
		}

        [SafeToPrepare(true)]
        public static Set Like(Set setToFilter, String pattern, Expression exp, Boolean caseSensitive)
        {
	        if (caseSensitive)
	        {
				// todo made case sensitive variant of Like
		        return RegExFilter(setToFilter, LikeToRegEx(pattern), exp, true);
	        }
			return Like(setToFilter, pattern, exp);
        }
        #endregion

        #region " Public 'Regex' functions"
        [SafeToPrepare(true)]
        public static Set RegExFilter(Set setToFilter, String pattern, Expression exp)
        {
            return RegExFilter(setToFilter, pattern, exp, false);
        }

		[SafeToPrepare(true)]
		public static Set RegExFilter(Set setToFilter, String pattern, Expression exp, int start, int count)
		{
			return RegExFilter(setToFilter, pattern, exp, false, start, count);
		}

        [SafeToPrepare(true)]
        public static Set RegExFilter(Set setToFilter, String pattern, Expression exp, Boolean caseSensitive)
        {
            Context.TraceEvent(100, 0, "RegExFilter: Starting");

			// If pattern is empty, just return the whole set
            if (pattern.Length == 0)
            {
                Context.TraceEvent(100, 0, "RegExFilter: Finished (No pattern parameter)");
                return setToFilter;
            }

            if (setToFilter == null)
            {
                throw new ArgumentNullException("setToFilter");
            }

			using(SetBuilder sb = new SetBuilder())
            {
                RegexOptions optRegex = RegexOptions.Compiled;
                if (!caseSensitive)
                {
                    optRegex = optRegex | RegexOptions.IgnoreCase;
                }
                Regex r = getCachedRegEx(pattern, optRegex);                
                foreach (Tuple t in setToFilter.Tuples)
                {
                    string val = (string)exp.Calculate(t);
                    if (r.Match(val).Success)
                    {
                        sb.Add(t);
                    }
                        
                }
                Context.TraceEvent(100, sb.Count, "RegExFilter: Finished (returning " + sb.Count.ToString() + " tuples");
                return sb.ToSet();
            }
        }
		
		[SafeToPrepare(true)]
		public static Set RegExFilter(Set setToFilter, String pattern, Expression exp, Boolean caseSensitive, int start, int count)
		{
			Context.TraceEvent(100, 0, "RegExFilter: Starting");

			if (pattern.Length == 0)
			{
				Context.TraceEvent(100, 0, "RegExFilter: Finished (No pattern parameter)");
				return setToFilter;
			}

			if (setToFilter == null)
			{
				throw new ArgumentNullException("setToFilter");
			}

			#region start-end SubSet adjustment / boundary checks

			if (start < 0)
			{
				start = 0;
			}

			if (count <= 0)
			{
				Context.TraceEvent(100, 0, "RegExFilter: Finished (count parameter value is less or equal 0)");
				return new SetBuilder().ToSet();
			}

			#endregion

			using (SetBuilder sb = new SetBuilder())
			{
				RegexOptions optRegex = RegexOptions.Compiled;
				if (!caseSensitive)
				{
					optRegex = optRegex | RegexOptions.IgnoreCase;
				}
				Regex r = getCachedRegEx(pattern, optRegex);

				int indexBefore = 0;
				int itemsCount = 0;
				bool takeIt = start == 0;

				foreach (Tuple t in setToFilter.Tuples)
				{
					string val = (string)exp.Calculate(t);
					if (r.Match(val).Success)
					{
						if (takeIt)
						{
							sb.Add(t);
							itemsCount++;
							if (itemsCount == count)
							{
								break;
							}
						}
						else
						{
							indexBefore++;
							if (indexBefore == start)
							{
								takeIt = true;
							}
						}
					}

				}
				Context.TraceEvent(100, sb.Count, "RegExFilter: Finished (returning " + sb.Count.ToString() + " tuples");
				return sb.ToSet();
			}
		}
        #endregion

        #region "Private Helper Functions"
        /// <summary>
        /// This function converts a pattern from the T-SQL LIKE format
        /// into a RegEx pattern.
        /// 
        ///      % = .*
        ///      _ = .
        /// 
        /// In a regex Characters other than . $ ^ { [ ( | ) * + ? \ match themselves.
        /// There fore the above characters need to be escaped so that they are correctly
        /// matched if they are present in the "like" pattern.
        /// </summary>
        /// <param name="pattern">This is the pattern to match in T-SQL LIKE format</param>
        /// <returns>string</returns>
        public static string LikeToRegEx(string pattern)
        {
            Context.TraceEvent(100, 0, "Like: Converting Like to RegEx");
            Context.CheckCancelled(); // Check if the user has cancelled 

            StringBuilder sb = new StringBuilder(pattern);
            // the order of the following operations is important or one replacement
            // can end up corrupting a previous replacement.
            sb.Replace(@"\", @"\\"); // needs to be done first before any other backslashes are introduced
            sb.Replace("*", @"\*");  // needs to be done before the '.*' insertion
            sb.Replace(".", @"\.");  // needs to be done before the '.*' insertion
            sb.Replace("%", ".*");
            sb.Replace("$", @"\$");
            sb.Replace("(", @"\(");
            sb.Replace(")", @"\)");
            sb.Replace("|", @"\|");
            sb.Replace("+", @"\+");
            sb.Replace("?", @"\?");
            sb.Replace("^", @"\^");
            // fix up strings where the above replace was too agressive and the
            // ^ was being used as part of a 'not matching' expression.
            sb.Replace(@"[\^", @"[^");
            sb.Replace("[[]", @"\[");
            sb.Replace("_", ".");
            // the above replacement incorrectly converts both _ to . and [_] to [.]
            // the next line converts the incorrect [.] to _
            sb.Replace("[.]", @"_");

            if (!pattern.StartsWith("%")) { sb.Insert(0, @"\A"); }
            if (!pattern.EndsWith("%")) { sb.Append(@"\z"); }
            return sb.ToString();
        }

        private static Regex getCachedRegEx(string pattern, RegexOptions opt)
        {
            Context.CheckCancelled(); // Check if the user has cancelled 

            Boolean caseSensitive = (opt & RegexOptions.IgnoreCase) == RegexOptions.IgnoreCase;
            RegExCacheIndex ri = new RegExCacheIndex(pattern, caseSensitive);

            if (regExCache.ContainsKey(ri))
            {
                Context.TraceEvent(100, 0, "RegExFilter: Returning Cached RegEx");
                Regex cachedRegEx;
                lock (regExCache.SyncRoot)
                {
                    cachedRegEx = (Regex)regExCache[ri];
                }
                return cachedRegEx;
            }
	        Context.TraceEvent(100, 0, "RegExFilter: Adding RegEx to Cache");
	        Regex newRegEx = new Regex(pattern, opt);
	        lock(regExCache.SyncRoot)
	        {
		        regExCache.Add(ri, newRegEx);
	        }
	        return newRegEx;
        }
        #endregion "Helper Functions"

    } // End class

    #region "Helper Classes"

	#region SQL Like string utilities

	// author is http://stackoverflow.com/users/13355/komma8-komma1

	/*
	public class TestSqlLikeFunction
	{
		static void Main(string[] args)
		{
			TestSqlLikePattern(true, "%", "");
			TestSqlLikePattern(true, "%", " ");
			TestSqlLikePattern(true, "%", "asdfa asdf asdf");
			TestSqlLikePattern(true, "%", "%");
			TestSqlLikePattern(false, "_", "");
			TestSqlLikePattern(true, "_", " ");
			TestSqlLikePattern(true, "_", "4");
			TestSqlLikePattern(true, "_", "C");
			TestSqlLikePattern(false, "_", "CX");
			TestSqlLikePattern(false, "[ABCD]", "");
			TestSqlLikePattern(true, "[ABCD]", "A");
			TestSqlLikePattern(true, "[ABCD]", "b");
			TestSqlLikePattern(false, "[ABCD]", "X");
			TestSqlLikePattern(false, "[ABCD]", "AB");
			TestSqlLikePattern(true, "[B-D]", "C");
			TestSqlLikePattern(true, "[B-D]", "D");
			TestSqlLikePattern(false, "[B-D]", "A");
			TestSqlLikePattern(false, "[^B-D]", "C");
			TestSqlLikePattern(false, "[^B-D]", "D");
			TestSqlLikePattern(true, "[^B-D]", "A");
			TestSqlLikePattern(true, "%TEST[ABCD]XXX", "lolTESTBXXX");
			TestSqlLikePattern(false, "%TEST[ABCD]XXX", "lolTESTZXXX");
			TestSqlLikePattern(false, "%TEST[^ABCD]XXX", "lolTESTBXXX");
			TestSqlLikePattern(true, "%TEST[^ABCD]XXX", "lolTESTZXXX");
			TestSqlLikePattern(true, "%TEST[B-D]XXX", "lolTESTBXXX");
			TestSqlLikePattern(true, "%TEST[^B-D]XXX", "lolTESTZXXX");
			TestSqlLikePattern(true, "%Stuff.txt", "Stuff.txt");
			TestSqlLikePattern(true, "%Stuff.txt", "MagicStuff.txt");
			TestSqlLikePattern(false, "%Stuff.txt", "MagicStuff.txt.img");
			TestSqlLikePattern(false, "%Stuff.txt", "Stuff.txt.img");
			TestSqlLikePattern(false, "%Stuff.txt", "MagicStuff001.txt.img");
			TestSqlLikePattern(true, "Stuff.txt%", "Stuff.txt");
			TestSqlLikePattern(false, "Stuff.txt%", "MagicStuff.txt");
			TestSqlLikePattern(false, "Stuff.txt%", "MagicStuff.txt.img");
			TestSqlLikePattern(true, "Stuff.txt%", "Stuff.txt.img");
			TestSqlLikePattern(false, "Stuff.txt%", "MagicStuff001.txt.img");
			TestSqlLikePattern(true, "%Stuff.txt%", "Stuff.txt");
			TestSqlLikePattern(true, "%Stuff.txt%", "MagicStuff.txt");
			TestSqlLikePattern(true, "%Stuff.txt%", "MagicStuff.txt.img");
			TestSqlLikePattern(true, "%Stuff.txt%", "Stuff.txt.img");
			TestSqlLikePattern(false, "%Stuff.txt%", "MagicStuff001.txt.img");
			TestSqlLikePattern(true, "%Stuff%.txt", "Stuff.txt");
			TestSqlLikePattern(true, "%Stuff%.txt", "MagicStuff.txt");
			TestSqlLikePattern(false, "%Stuff%.txt", "MagicStuff.txt.img");
			TestSqlLikePattern(false, "%Stuff%.txt", "Stuff.txt.img");
			TestSqlLikePattern(false, "%Stuff%.txt", "MagicStuff001.txt.img");
			TestSqlLikePattern(true, "%Stuff%.txt", "MagicStuff001.txt");
			TestSqlLikePattern(true, "Stuff%.txt%", "Stuff.txt");
			TestSqlLikePattern(false, "Stuff%.txt%", "MagicStuff.txt");
			TestSqlLikePattern(false, "Stuff%.txt%", "MagicStuff.txt.img");
			TestSqlLikePattern(true, "Stuff%.txt%", "Stuff.txt.img");
			TestSqlLikePattern(false, "Stuff%.txt%", "MagicStuff001.txt.img");
			TestSqlLikePattern(false, "Stuff%.txt%", "MagicStuff001.txt");
			TestSqlLikePattern(true, "%Stuff%.txt%", "Stuff.txt");
			TestSqlLikePattern(true, "%Stuff%.txt%", "MagicStuff.txt");
			TestSqlLikePattern(true, "%Stuff%.txt%", "MagicStuff.txt.img");
			TestSqlLikePattern(true, "%Stuff%.txt%", "Stuff.txt.img");
			TestSqlLikePattern(true, "%Stuff%.txt%", "MagicStuff001.txt.img");
			TestSqlLikePattern(true, "%Stuff%.txt%", "MagicStuff001.txt");
			TestSqlLikePattern(true, "_Stuff_.txt_", "1Stuff3.txt4");
			TestSqlLikePattern(false, "_Stuff_.txt_", "1Stuff.txt4");
			TestSqlLikePattern(false, "_Stuff_.txt_", "1Stuff3.txt");
			TestSqlLikePattern(false, "_Stuff_.txt_", "Stuff3.txt4");

			Console.ReadKey();
		}

		public static void TestSqlLikePattern(bool expectedResult, string pattern, string testString)
		{
			bool result = testString.SqlLike(pattern);
			if (expectedResult != result)
			{
				Console.ForegroundColor = ConsoleColor.Red; System.Console.Out.Write("[SqlLike] FAIL");
			}
			else
			{
				Console.ForegroundColor = ConsoleColor.Green; Console.Write("[SqlLike] PASS");
			}
			Console.ForegroundColor = ConsoleColor.White; Console.WriteLine(": \"" + testString + "\" LIKE \"" + pattern + "\" == " + expectedResult);
		}
	}
	
	public static class SqlLikeStringExtensions
	{
		public static bool SqlLike(this string s, string pattern)
		{
			return SqlLikeStringUtilities.SqlLike(pattern, s);
		}
	}
	*/

	internal static class SqlLikeStringUtilities
	{
		public static bool SqlLike(string pattern, string str)
		{
			// todo bugfix extend with cases with mix of [A-H] and [XWZ] : TestSqlLikePattern(true, "[A-H1234-70]", "6") will fail
			// todo bugfix in some languages where national alphabet in codepage is not in alpabetical order the construction [A-Z] may parse to "List<char> set" incorrectly: for example in russian [À-ß] not include "¨"
			// todo made case sensitive variant
			// todo common usecases optimization
			// todo think: can it be rewritten to make pattren cacheable? Is it worh to cache it?

			bool isMatch = true,
				isWildCardOn = false,
				isCharWildCardOn = false,
				isCharSetOn = false,
				isNotCharSetOn = false,
				endOfPattern = false;
			int lastWildCard = -1;
			int patternIndex = 0;
			List<char> set = new List<char>();
			char p = '\0';

			for (int i = 0; i < str.Length; i++)
			{
				char c = str[i];
				endOfPattern = (patternIndex >= pattern.Length);
				if (!endOfPattern)
				{
					p = pattern[patternIndex];

					if (!isWildCardOn && p == '%')
					{
						lastWildCard = patternIndex;
						isWildCardOn = true;
						while (patternIndex < pattern.Length &&
							pattern[patternIndex] == '%')
						{
							patternIndex++;
						}
						p = patternIndex >= pattern.Length ? '\0' : pattern[patternIndex];
					}
					else if (p == '_')
					{
						isCharWildCardOn = true;
						patternIndex++;
					}
					else if (p == '[')
					{
						if (pattern[++patternIndex] == '^')
						{
							isNotCharSetOn = true;
							patternIndex++;
						}
						else isCharSetOn = true;

						set.Clear();
						if (pattern[patternIndex + 1] == '-' && pattern[patternIndex + 3] == ']')
						{
							char start = char.ToUpper(pattern[patternIndex]);
							patternIndex += 2;
							char end = char.ToUpper(pattern[patternIndex]);
							if (start <= end)
							{
								for (char ci = start; ci <= end; ci++)
								{
									set.Add(ci);
								}
							}
							patternIndex++;
						}

						while (patternIndex < pattern.Length &&
							pattern[patternIndex] != ']')
						{
							set.Add(pattern[patternIndex]);
							patternIndex++;
						}
						patternIndex++;
					}
				}

				if (isWildCardOn)
				{
					if (char.ToUpper(c) == char.ToUpper(p))
					{
						isWildCardOn = false;
						patternIndex++;
					}
				}
				else if (isCharWildCardOn)
				{
					isCharWildCardOn = false;
				}
				else if (isCharSetOn || isNotCharSetOn)
				{
					bool charMatch = (set.Contains(char.ToUpper(c)));
					if ((isNotCharSetOn && charMatch) || (isCharSetOn && !charMatch))
					{
						if (lastWildCard >= 0) patternIndex = lastWildCard;
						else
						{
							isMatch = false;
							break;
						}
					}
					isNotCharSetOn = isCharSetOn = false;
				}
				else
				{
					if (char.ToUpper(c) == char.ToUpper(p))
					{
						patternIndex++;
					}
					else
					{
						if (lastWildCard >= 0) patternIndex = lastWildCard;
						else
						{
							isMatch = false;
							break;
						}
					}
				}
			}
			endOfPattern = (patternIndex >= pattern.Length);

			if (isMatch && !endOfPattern)
			{
				bool isOnlyWildCards = true;
				for (int i = patternIndex; i < pattern.Length; i++)
				{
					if (pattern[i] != '%')
					{
						isOnlyWildCards = false;
						break;
					}
				}
				if (isOnlyWildCards) endOfPattern = true;
			}
			return isMatch && endOfPattern;
		}
	}
	#endregion

	#region RegEx helpers
	internal class RegExCacheIndex
    {
        public RegExCacheIndex(string pattern, Boolean caseSensitive)
        {
            m_caseSensitive = caseSensitive;
            m_pattern = pattern;
        }

        private Boolean m_caseSensitive = false;
        private String m_pattern = "";

        public Boolean CaseSensitive
        {
            get { return m_caseSensitive; }
            //set { m_caseSensitive = value; }
        }
        public String Pattern
        {
            get { return m_pattern; }
            //set { m_pattern = value; }
        }
    }

    internal class RegExCacheIndexComparer: IEqualityComparer
    {

        #region IEqualityComparer Members

        bool IEqualityComparer.Equals(object x, object y)
        {
            RegExCacheIndex left = (RegExCacheIndex)x;
            RegExCacheIndex right = (RegExCacheIndex)y;
            if (left.CaseSensitive == right.CaseSensitive)
            {
                if (left.CaseSensitive == true)
                { return (string.Compare(left.Pattern, right.Pattern, false)==0); }
                else
                { return (string.Compare(left.Pattern, right.Pattern, true)==0); }
            }
            else
            {
                return false;
            }
        }

        int IEqualityComparer.GetHashCode(object obj)
        {
            RegExCacheIndex ri = (RegExCacheIndex)obj;
            string pat = (ri.CaseSensitive ? "T:" : "F:") + ri.Pattern;
            return pat.GetHashCode();
        }

        #endregion
    }
	#endregion

	#endregion

} // End Namespace