using System;
using NUnit.Framework;

namespace Orciid.Core
{
	/// <summary>
	/// Test fixture
	/// </summary>
	[TestFixture]
	public class TestMySQLDBConnection
	{
		/// <summary>
		/// Test
		/// </summary>
		[Test]
		public void TestAdjustSQLSyntax()
		{
			MySQLDBConnection conn = new MySQLDBConnection(null, false);
			string result;

			string sql = "SELECT * FROM table";
			result = conn.AdjustSqlSyntax(sql);
			Assert.AreEqual("SELECT * FROM table", result);

			sql = "SELECT TOP 50 * FROM table";
			result = conn.AdjustSqlSyntax(sql);
			Assert.AreEqual("SELECT * FROM table LIMIT 50", result);

			sql = "UPDATE table SET field='foo TOP 50 bar'";
			result = conn.AdjustSqlSyntax(sql);
			Assert.AreEqual("UPDATE table SET field='foo TOP 50 bar'", result);

			sql = "SELECT 'foo TOP 50 bar' FROM table";
			result = conn.AdjustSqlSyntax(sql);
			Assert.AreEqual("SELECT 'foo TOP 50 bar' FROM table", result);

			sql = "UPDATE FieldData SET ControlledListValue=ControlledListValues.ID " +
				"FROM ControlledListValues WHERE FieldValue LIKE ItemValue " +
				"AND ControlledListID=1 AND FieldID=2";
			result = conn.AdjustSqlSyntax(sql);
			Assert.AreEqual("UPDATE FieldData,ControlledListValues " +
				"SET ControlledListValue=ControlledListValues.ID WHERE FieldValue LIKE ItemValue " +
				"AND ControlledListID=1 AND FieldID=2", result);

			sql = "UPDATE FieldData SET ControlledListValue='ControlledListValues.ID " +
				"FROM ControlledListValues' WHERE FieldValue LIKE ItemValue AND ControlledListID=1 " +
				"AND FieldID=2";
			result = conn.AdjustSqlSyntax(sql);
			Assert.AreEqual("UPDATE FieldData SET ControlledListValue='ControlledListValues.ID " +
				"FROM ControlledListValues' WHERE FieldValue LIKE ItemValue AND ControlledListID=1 " +
				"AND FieldID=2", result);
		}
		
		/// <summary>
		/// Test
		/// </summary>
		/// <remarks>
		/// Tests MySQL query rewriting
		/// </remarks>
		[Test]
		public void TestBug139()
		{
			MySQLDBConnection conn = new MySQLDBConnection(null, false);
			string result;

			string sql = "UPDATE x SET y='a FROM b WHERE c'";
			result = conn.AdjustSqlSyntax(sql);
			Assert.AreEqual("UPDATE x SET y='a FROM b WHERE c'", result);

			sql = "UPDATE x SET y='a FROM \\'b\\' WHERE c'";
			result = conn.AdjustSqlSyntax(sql);
			Assert.AreEqual("UPDATE x SET y='a FROM \\'b\\' WHERE c'", result);

			sql = "UPDATE\nx\nSET\ny\n=\n'\na\nFROM\nb\nWHERE\nc\n'\n";
			result = conn.AdjustSqlSyntax(sql);
			Assert.AreEqual("UPDATE\nx\nSET\ny\n=\n'\na\nFROM\nb\nWHERE\nc\n'\n", result);
		}

		/// <summary>
		/// Test
		/// </summary>
		/// <remarks>
		/// Tests MySQL query rewriting
		/// </remarks>
		[Test]
		public void TestBug138()
		{
			MySQLDBConnection conn = new MySQLDBConnection(null, false);
			string sql = "UPDATE table SET field={value} WHERE id={id}";
			Query query = new Query(conn, sql);
			query.AddParam("value", "taken from an email where the bug was first described");
			query.AddParam("id", 1);
			string result = query.SQL;
			int compchars = sql.IndexOf("{");
			Assert.AreEqual(sql.Substring(0, compchars), result.Substring(0, compchars));
		}

		/// <summary>
		/// Test
		/// </summary>
		/// <remarks>
		/// Tests MySQL query rewriting
		/// </remarks>
		[Test]
		public void TestVariableTop()
		{
			MySQLDBConnection conn = new MySQLDBConnection(null, false);
			string sql = "SELECT TOP {top} * FROM table";
			Query query = new Query(conn, sql);
			query.AddParam("top", 123);
			string result = query.SQL;
			Assert.AreEqual("SELECT * FROM table LIMIT 123", result);
		}

		/// <summary>
		/// Test
		/// </summary>
		/// <remarks>
		/// Tests MySQL query rewriting
		/// </remarks>
		[Test]
		public void TestBug1312()
		{
			MySQLDBConnection conn = new MySQLDBConnection(null, false);
			string sql = "SELECT DISTINCT TOP {top} {@FieldValue} AS V FROM FieldData";
			Query query = new Query(conn, sql);
			query.AddParam("top", 123);
			query.AddParam("@FieldValue", "FieldValue");
			string result = query.SQL;
			Assert.AreEqual("SELECT DISTINCT FieldValue AS V FROM FieldData LIMIT 123", result);
		}
	}
}
