context: using System;
  using System.Collections;
  using System.Collections.Generic;
  using log4net;
  using NHibernate.Dialect;
  using NHibernate.Dialect.Function;
  using NHibernate.DomainModel;
  using NUnit.Framework;
  
  namespace NHibernate.Test.Legacy
  {
  	/// <summary>
  	/// Summary description for SQLFunctionsTest.
  	/// </summary>
  	[TestFixture]
  	public class SQLFunctionsTest : TestCase
  	{
  		private static readonly ILog log =
  			LogManager.GetLogger(typeof(SQLFunctionsTest));
  
  		protected override IList Mappings
  		{
  			get { return new string[] { "Simple.hbm.xml", "Blobber.hbm.xml", "Broken.hbm.xml" }; }
  		}
  
  		[Test]
  		public void DialectSQLFunctions()
  		{
  			ISession s = OpenSession();
  			ITransaction t = s.BeginTransaction();
  
  			IEnumerator iter = s.CreateQuery("select max(s.Count) from s in class Simple").Enumerable()
  				.GetEnumerator();
  
  			if (Dialect is MySQLDialect
  				// Added two dialects below for NH
  				|| Dialect is MsSql2000Dialect
  				|| Dialect is PostgreSQLDialect)
  			{
  				Assert.IsTrue(iter.MoveNext());
  				Assert.IsNull(iter.Current);
  			}
  
  			Simple simple = new Simple();
  			simple.Name = "Simple Dialect Function Test";
  			simple.Address = "Simple Address";
  			simple.Pay = 45.8f;
  			simple.Count = 2;
  			s.Save(simple, 10L);
  
  			// Test to make sure allocating an specified object operates correctly.
  			Assert.AreEqual(1,
  							s.CreateQuery("select new S(s.Count, s.Address) from s in class Simple").List()
  								.Count);
  
  			// Quick check the base dialect functions operate correctly
  			Assert.AreEqual(1,
  							s.CreateQuery("select max(s.Count) from s in class Simple").List().Count);
  			Assert.AreEqual(1,
  							s.CreateQuery("select count(*) from s in class Simple").List().Count);
  
  			if (Dialect is Oracle8iDialect)
  			{
  				// Check Oracle Dialect mix of dialect functions - no args (no parenthesis and single arg functions
  				IList rset = s.CreateQuery("select s.Name, sysdate, trunc(s.Pay), round(s.Pay) from s in class Simple").List();
  				object[] row = (object[])rset[0];
  				Assert.IsNotNull(row[0], "Name string should have been returned");
  				Assert.IsNotNull(row[1], "Todays Date should have been returned");
  				Assert.AreEqual(45f, row[2], "trunc(45.8) result was incorrect");
  				Assert.AreEqual(46f, row[3], "round(45.8) result was incorrect");
  
  				simple.Pay = -45.8f;
  				s.Update(simple);
  
  				// Test type conversions while using nested functions (Float to Int).
  				rset = s.CreateQuery("select abs(round(s.Pay)) from s in class Simple").List();
  				Assert.AreEqual(46f, rset[0], "abs(round(-45.8)) result was incorrect");
  
  				// Test a larger depth 3 function example - Not a useful combo other than for testing
  				Assert.AreEqual(1,
  												s.CreateQuery("select trunc(round(length('A'))) from s in class Simple").List().Count);
  				// NOTE: In Oracle this will fail as the translator will expect two columns in return
  				//Assert.AreEqual(1,
  				//                s.CreateQuery("select trunc(round(sysdate)) from s in class Simple").List().Count);
  
  				// Test the oracle standard NVL funtion as a test of multi-param functions...
  				// NOTE: commented out for NH, since Pay is a value type and will never be null
  				//simple.Pay = null;
  				//s.Update( simple );
  				//Assert.AreEqual( 0,
  				//	s.CreateQuery("select MOD( NVL(s.Pay, 5000), 2 ) from Simple as s where s.id = 10").List()[0] );
  			}
  
  			// NOTE: Commented out for NHibernate, no HSQL dialect.
  			//if ( (getDialect() is HSQLDialect) ) 
  			//{
  			//	// Test the hsql standard MOD funtion as a test of multi-param functions...
  			//	Integer value = (Integer) s.find("select MOD(s.count, 2) from Simple as s where s.id = 10" ).get(0);
  			//	assertTrue( 0 == value.intValue() );
  			//}
  
  			s.Delete(simple);
  			t.Commit();
  			s.Close();
  		}
  
  		[Test]
  		[Ignore("NH-3893 is not fixed")]
  		public void LeftAndRight()
  		{
  			// As of NH-3893, left and right functions are broken. Seemed confused with join keyword, and not
  			// supported on Hibernate side.
  			using (var s = OpenSession())
  			using (var t = s.BeginTransaction())
  			{
  				var rset = s.CreateQuery("select left('abc', 2), right('abc', 2) from s in class Simple").List<object[]>();
  				var row = rset[0];
  				Assert.AreEqual("ab", row[0], "Left function is broken.");
  				Assert.AreEqual("bc", row[1], "Right function is broken.");
  				t.Commit();
  			}
  		}
  
  		[Test]
  		public void SetProperties()
  		{
  			ISession s = OpenSession();
  			ITransaction t = s.BeginTransaction();
  			Simple simple = new Simple();
  			simple.Name = "Simple 1";
  			s.Save(simple, (long)10);
  			IQuery q = s.CreateQuery("from s in class Simple where s.Name=:Name and s.Count=:Count");
  			q.SetProperties(simple);
  			Assert.AreEqual(simple, q.List()[0]);
  			s.Delete(simple);
  			t.Commit();
  			s.Close();
  		}
  
  
  		[Test]
  		public void Broken()
  		{
  			if (Dialect is Oracle8iDialect)
  				Assert.Ignore("Does not apply to " + typeof(Oracle8iDialect).FullName);
  
  			ISession s = OpenSession();
  			ITransaction t = s.BeginTransaction();
  
  			Broken b = new Fixed();
  			b.Id = 123;
  			b.OtherId = "foobar";
  			s.Save(b);
  			s.Flush();
  			b.Timestamp = DateTime.Now;
  			t.Commit();
  			s.Close();
  
  			s = OpenSession();
  			t = s.BeginTransaction();
  			s.Update(b);
  			t.Commit();
  			s.Close();
  
  			s = OpenSession();
  			t = s.BeginTransaction();
  			b = (Broken)s.Load(typeof(Broken), b);
  			t.Commit();
  			s.Close();
  
  			s = OpenSession();
  			t = s.BeginTransaction();
  			s.Delete(b);
  			t.Commit();
  			s.Close();
  		}
  
  		[Test]
  		public void NothingToUpdate()
  		{
  			ISession s = OpenSession();
  			ITransaction t = s.BeginTransaction();
  			Simple simple = new Simple();
  			simple.Name = "Simple 1";
  			s.Save(simple, (long)10);
  			t.Commit();
  			s.Close();
  
  			s = OpenSession();
  			t = s.BeginTransaction();
  			s.Update(simple, (long)10);
  			t.Commit();
  			s.Close();
  
  			s = OpenSession();
  			t = s.BeginTransaction();
  			s.Update(simple, (long)10);
  			s.Delete(simple);
  			t.Commit();
  			s.Close();
  		}
  
  
  		[Test]
  		public void CachedQuery()
  		{
  			ISession s = OpenSession();
  			ITransaction t = s.BeginTransaction();
  			Simple simple = new Simple();
  			simple.Name = "Simple 1";
  			s.Save(simple, 10L);
  			t.Commit();
  			s.Close();
  
  			s = OpenSession();
  			t = s.BeginTransaction();
  			IQuery q = s.CreateQuery("from Simple s where s.Name=?");
  			q.SetCacheable(true);
  			q.SetString(0, "Simple 1");
  
  			Assert.AreEqual(1, q.List().Count);
  			Assert.AreEqual(1, q.List().Count);
  			Assert.AreEqual(1, q.List().Count);
  
  			q = s.CreateQuery("from Simple s where s.Name=:name");
  			q.SetCacheable(true);
  			q.SetString("name", "Simple 1");
  			Assert.AreEqual(1, q.List().Count);
  
  			simple = (Simple)q.List()[0];
  
  			q.SetString("name", "Simple 2");
  			Assert.AreEqual(0, q.List().Count);
  			Assert.AreEqual(0, q.List().Count);
  			simple.Name = "Simple 2";
  			Assert.AreEqual(1, q.List().Count);
  			Assert.AreEqual(1, q.List().Count);
  			t.Commit();
  			s.Close();
  
  			s = OpenSession();
  			t = s.BeginTransaction();
  			q = s.CreateQuery("from Simple s where s.Name=:name");
  			q.SetString("name", "Simple 2");
  			q.SetCacheable(true);
  			Assert.AreEqual(1, q.List().Count);
  			Assert.AreEqual(1, q.List().Count);
  			t.Commit();
  			s.Close();
  
  			s = OpenSession();
  			t = s.BeginTransaction();
  			s.Update(simple, 10L);
  			s.Delete(simple);
  			t.Commit();
  			s.Close();
  
  			s = OpenSession();
  			t = s.BeginTransaction();
  			q = s.CreateQuery("from Simple s where s.Name=?");
  			q.SetCacheable(true);
  			q.SetString(0, "Simple 1");
  			Assert.AreEqual(0, q.List().Count);
  			Assert.AreEqual(0, q.List().Count);
  			t.Commit();
  			s.Close();
  		}
  
  		private string LocateAppropriateDialectFunctionNameForAliasTest()
  		{
  			foreach (KeyValuePair<string, ISQLFunction> de in Dialect.Functions)
  			{
  				ISQLFunction function = de.Value;
  				if (!function.HasArguments && !function.HasParenthesesIfNoArguments)
  				{
  					return de.Key;
  				}
  			}
  
  			return null;
  		}
  
  		[Test]
  		public void SQLFunctionAsAlias()
  		{
  			string functionName = LocateAppropriateDialectFunctionNameForAliasTest();
  			if (functionName == null)
  			{
  
gt: 				log.Info("Dialect does not list any no-arg functions");