/Tests/Mite.Core.IntegrationTests/MsSql/MsSqlAdapterTestFixture.cs

/***************************************************COPYRIGHT***************************************

* Copyright (c) 2008, Whiteboard-IT

* All rights reserved.

*

* Redistribution and use in source and binary forms, with or without modification, are permitted provided that 

* the following conditions are met:

*

* Redistributions of source code must retain the above copyright notice, this list of conditions and the following 

 * disclaimer.

*

* Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following

 * disclaimer in the documentation and/or other materials provided with the distribution.

*

* Neither the name of Whiteboard-IT, LLC nor the names of its contributors may be used to endorse or promote 

 * products derived from this software without specific prior written permission.

*

* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, 

 * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE 

 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, 

 * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF 

 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON 

 * ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) 

 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 

*****************************************************************************************************************/

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using Boo.Lang;

using MbUnit.Framework;

using Mite.Configuration;

using Mite.MsSql;



namespace Mite.Core.Tests.Integration

{

	[Author("Nathan")]

	[TestCategory("MsSql Integration")]

	[TestFixture]

	public class MsSqlAdapterTestFixture : MsSqlIntegrationTestBase

	{

		[SetUp]

		public void SetUp()

		{

			Environment.CurrentDirectory = AppDomain.CurrentDomain.BaseDirectory;

			using (var conn = IoC.Resolve<IDbConnection>())

			{

				conn.Open();

				MsSqlUtility.CleanDatabase(conn, testDatabase.Name);

				conn.Close();

			}

		}



		[Test]

		public void CreateTable()

		{

			const string tableName = "some_table";

			using (IDbConnection conn = GetConnection())

			{

				conn.Open();



				MsSqlAdapter dao = GetMsSqlAdapter();



				IDbCommand cmd = dao.CreateTable(conn, new Table(tableName, GetIdColumn()));

				cmd.ExecuteNonQuery();

			}



			AssertTableExists(tableName);

		}



		[Test]

		public void DropTable()

		{

			const string tableName = "some_table";



			var table = new Table(tableName, GetIdColumn());



			MsSqlAdapter adapter = GetMsSqlAdapter();



			using (IDbConnection conn = GetConnection(adapter))

			{

				conn.Open();



				IDbCommand cmd = adapter.CreateTable(conn, table);

				cmd.ExecuteNonQuery();



				AssertTableExists(tableName);



				IDbCommand dropCommand = adapter.DropTable(conn, table);

				dropCommand.ExecuteNonQuery();



				AssertTableDoesNotExist(tableName);



				conn.Close();

			}

		}



		[Test]

		public void CreateSchemaTable()

		{

			using (IDbConnection conn = GetConnection())

			{

				conn.Open();

				MsSqlAdapter dao = GetMsSqlAdapter();



				IDbCommand[] cmds = dao.CreateSchemaTable(conn, testDatabase);

				cmds.ForEach(cmd => { cmd.ExecuteNonQuery(); });

			}



			AssertTableExists(Adapter.MIGRATION_SCHEMA_TABLE_NAME);

		}



		[Test]

		public void RenameTable()

		{

			const string newName = "new_name";

			Table table = GetTable();

			string originalName = table.Name;



			MsSqlAdapter adapter = GetMsSqlAdapter();



			using (IDbConnection conn = GetConnection(adapter))

			{

				conn.Open();



				IDbCommand createCommand = adapter.CreateTable(conn, table);

				createCommand.ExecuteNonQuery();



				AssertTableExists(originalName);



				IDbCommand renameCommand = adapter.RenameTable(conn, table, newName);

				renameCommand.ExecuteNonQuery();



				AssertTableDoesNotExist(originalName);

				AssertTableExists(newName);



				conn.Close();

			}

		}



		[Test]

		public void AlterColumn()

		{

			const string columnName = "some_column";



			Column originalColumn = new NVarCharColumn(columnName, 200);

			Column newColumn = new NVarCharColumn(columnName, 255)

							   {

								   IsNullable = true

							   };



			Table table = GetTable(originalColumn);

			newColumn.Table = table;



			MsSqlAdapter adapter = GetMsSqlAdapter();



			using (IDbConnection conn = GetConnection(adapter))

			{

				conn.Open();



				IDbCommand createCommand = adapter.CreateTable(conn, table);



				createCommand.ExecuteNonQuery();



				IDbCommand alterCommand = adapter.AlterColumn(conn, originalColumn, newColumn);

				alterCommand.ExecuteNonQuery();



				conn.Close();

			}

		}



		[Test]

		public void AlterTable_AddColumn()

		{

			CreateAlterMeTable();



			var addedColumn = new Column("added_column", DbType.Decimal);

			addedColumn.Table = new Table("alter_me", addedColumn);



			// TODO: This shouldn't be checked by the dao, but right now this is how it's done.

			MsSqlAdapter dao = GetMsSqlAdapter();

			using (IDbConnection conn = GetConnection())

			{

				conn.Open();



				IDbCommand cmd = dao.AddColumnToTable(conn, new Table("alter_me"), addedColumn);

				cmd.ExecuteNonQuery();



				Database db = dao.GetDatabase(conn, testDatabase.Name);

				Table tbl = db.Tables.Single(t => t.Name.Equals("alter_me"));



				Assert.IsTrue(tbl.Columns.Contains(addedColumn));

			}

		}



		[Test]

		public void RemoveColumn()

		{

			CreateAlterMeTable();



			MsSqlAdapter dao = GetMsSqlAdapter();

			using (IDbConnection conn = GetConnection())

			{

				conn.Open();



				IDbCommand cmd = dao.DropColumns(conn, "alter_me", "remove_me");

				cmd.ExecuteNonQuery();



				Database db = dao.GetDatabase(conn, testDatabase.Name);

				Table tbl = db.Tables.Single(t => t.Name.Equals("alter_me"));



				Assert.IsTrue(tbl.Columns.DoesNotContain(col => col.Name == "remove_me"));

				conn.Close();

			}

		}



		[Test]

		public void AlterColumn_ChangeName()

		{

			string newColumnName = "new_column_name";



			using (var conn = IoC.Resolve<IDbConnection>() as SqlConnection)

			{

				conn.Open();

				MsSqlAdapter adapter = GetMsSqlAdapter();



				Column idColumn = GetIdColumn();

				Column oldColumn = new Column("old_column", DbType.String);



				Table table = new Table("alter_test", idColumn, oldColumn);



				using (IDbCommand cmd = adapter.CreateTable(conn, table))

					cmd.ExecuteNonQuery();



				using (IDbCommand cmd = adapter.RenameColumn(conn, oldColumn, newColumnName))

					cmd.ExecuteNonQuery();



				Database db = adapter.GetDatabase(conn, testDatabase.Name);

				var updatedTable = db.Tables.Single(tbl => tbl.Equals(table));

				var updatedColumn = updatedTable.Columns.SingleOrDefault(col => col.Name.Equals(newColumnName));



				Assert.IsNotNull(updatedColumn, "Column rename failed.");



				conn.Close();

			}

		}



		[Test]

		public void AlterColumn_ChangeType()

		{

			const string decimalColumnName = "datatype_col_decimal";

			const string tableName = "alter_test";



			using (IDbConnection conn = GetConnection())

			{

				conn.Open();



				MsSqlAdapter dao = GetMsSqlAdapter();



				Column idColumn = GetIdColumn();

				Column decimalColumn = new Column(decimalColumnName, DbType.Decimal);



				Table table = new Table(tableName, idColumn, decimalColumn);



				using (IDbCommand cmd = dao.CreateTable(conn, table))

					cmd.ExecuteNonQuery();

				

				var modifiedColumn = decimalColumn.CloneColumn();

				modifiedColumn.DataType = DbType.StringFixedLength;

				modifiedColumn.Length = 255;



				using (IDbCommand cmd = dao.AlterColumn(conn, decimalColumn, modifiedColumn))

					cmd.ExecuteNonQuery();



				Assert.IsTrue(MsSqlUtility.ColumnIsOfDataType(conn, testDatabase.Name, tableName, decimalColumnName, "nchar"),

							  "Changing datatype of column failed.");



				conn.Close();

			}

		}



		[Test]

		public void AddForeignKey()

		{

			MsSqlAdapter adapter = GetAdapter(environment);



			using (var conn = environment.CreateConnection(adapter))

			{

				conn.Open();



				string pkTableName = "pk_table";

				string fkTableName = "fk_table";



				Table table = new Table(pkTableName,

				                        new Column("id", DbType.Int32, ColumnAttributes.PrimaryKey | ColumnAttributes.Identity),

										new Column("fk_tableId", DbType.Int32, ColumnAttributes.NotNull));



				Table referenceTable = new Table(fkTableName,

					new Column("Id", DbType.Int32, ColumnAttributes.PrimaryKey | ColumnAttributes.Identity));



				using (IDbTransaction trans = conn.BeginTransaction())

				{

					using (IDbCommand cmd = adapter.CreateTable(conn, table))

					{

						cmd.Transaction = trans;

						cmd.ExecuteNonQuery();

					}



					using (IDbCommand cmd = adapter.CreateTable(conn, referenceTable))

					{

						cmd.Transaction = trans;

						cmd.ExecuteNonQuery();

					}



					trans.Commit();

				}



				using (IDbCommand cmd = adapter.AddForeignKey(conn, pkTableName, fkTableName, "FK_BLEH_BLAH", new KeyValuePair<string, string>("fk_tableId", "Id")))

					cmd.ExecuteNonQuery();



				Assert.IsTrue(MsSqlUtility.ForeignKeyExists(conn, testDatabase.Name, "FK_BLEH_BLAH"), "Foreign Key not added.");



				conn.Close();

			}

		}



		[Test]

		public void AlterTable_RemoveForeignKey()

		{

			const string pkTableName = "pk_table";

			const string fkTableName = "fk_table";

			const string fkColumnName = "foreign_key_test";

			const string fkName = "FK_BLEH_BLUR";



			using (var conn = IoC.Resolve<IDbConnection>())

			{

				conn.Open();



				using (IDbTransaction trans = conn.BeginTransaction())

				{

					using (IDbCommand cmd = conn.CreateCommand())

					{

						cmd.CommandText = String.Format("CREATE TABLE {0} ([id] int primary key identity not null);", pkTableName);

						cmd.Transaction = trans;

						cmd.ExecuteNonQuery();

					}



					using (IDbCommand cmd = conn.CreateCommand())

					{

						cmd.CommandText = String.Format("CREATE TABLE {0} ([id] int primary key identity not null, [{1}] int not null);",

														fkTableName, fkColumnName);

						cmd.Transaction = trans;

						cmd.ExecuteNonQuery();

					}



					using (IDbCommand cmd = conn.CreateCommand())

					{

						cmd.CommandText = String.Format("ALTER TABLE {0} ADD CONSTRAINT {1} FOREIGN KEY ({2}) REFERENCES {3} ({4});",

														fkTableName, fkName, fkColumnName, pkTableName, "id");

						cmd.Transaction = trans;

						cmd.ExecuteNonQuery();

					}



					trans.Commit();

				}



				MsSqlAdapter dao = GetMsSqlAdapter();

				using (IDbCommand cmd = dao.DropForeignKey(conn, fkTableName, fkName))

					cmd.ExecuteNonQuery();





				Assert.IsFalse(MsSqlUtility.ForeignKeyExists(conn, testDatabase.Name, fkName));

				conn.Close();

			}

		}



		[Test]

		public void Create_unique_index_on_two_columns()

		{

			string colToIndexName = "col_to_index";

			string tableName = "table_name";

			using (var conn = IoC.Resolve<IDbConnection>())

			{

				conn.Open();



				using (IDbCommand cmd = conn.CreateCommand())

				{

					cmd.CommandText = string.Format("CREATE TABLE {0} ([id] int primary key not null, [{1}] nvarchar(200))", tableName,

													colToIndexName);

					cmd.ExecuteNonQuery();

				}





				MsSqlAdapter adapter = GetMsSqlAdapter();

				var index = new Index("IX_Test", new Table(tableName), true, new Hash(),

									  GetIdColumn(),

									  new MigrationBase.String(colToIndexName) { Length = 200});



				IDbCommand createIndexCommand = adapter.CreateIndex(index, conn);



				Console.WriteLine(createIndexCommand.CommandText);



				createIndexCommand.ExecuteNonQuery();



				conn.Close();

			}

		}



		private MsSqlAdapter GetAdapter(MiteEnvironment environment)

		{

			return GetMsSqlAdapter();

		}



		/// <summary>

		/// Create a table with name "test" and an id int32 identity column.

		/// </summary>

		private static Table GetTable(params Column[] columns)

		{

			var columnList = new List<Column>();

			columnList.Add(GetIdColumn());

			columnList.AddRange(columns);

			return new Table("test", columnList.ToArray());

		}



		private void CreateAlterMeTable()

		{

			using (var conn = IoC.Resolve<IDbConnection>())

			{

				conn.Open();



				conn.ChangeDatabase(testDatabase.Name);



				using (IDbCommand cmd = conn.CreateCommand())

				{

					cmd.CommandText =

						String.Format("CREATE TABLE [alter_me] ([id] int not null identity primary key, [remove_me] nchar(2) null)");

					cmd.ExecuteNonQuery();

				}



				conn.Close();

			}

		}



		protected MsSqlAdapter GetMsSqlAdapter()

		{

			var adapter = new MsSqlAdapter();

			return adapter;

		}



		protected IDbConnection GetConnection()

		{

			var adapter = new MsSqlAdapter();

			IDbConnection conn = adapter.CreateConnection(

				environment.Host,

				environment.Database,

				environment.Username,

				environment.Password,

				environment.Options);

			return conn;

		}



		protected IDbConnection GetConnection(Adapter adapter)

		{

			return adapter.CreateConnection(environment.Host, environment.Database, environment.Username, environment.Password,

											environment.Options);

		}



		private static Column GetIdColumn()

		{

			return new Column("Id", DbType.Int32, ColumnAttributes.Identity | ColumnAttributes.PrimaryKey);

		}



		private static void AssertTableExists(string tableName)

		{

			using (var conn = IoC.Resolve<IDbConnection>() as SqlConnection)

			{

				conn.Open();





				IEnumerable<string> tableNames = MsSqlUtility.GetTableNamesInDatabase(DATABASE_NAME, conn);

				Assert.IsTrue(tableNames.Contains(tableName));





				conn.Close();

			}

		}



		private static void AssertTableDoesNotExist(string tableName)

		{

			using (var conn = IoC.Resolve<IDbConnection>())

			{

				conn.Open();





				IEnumerable<string> tableNames = MsSqlUtility.GetTableNamesInDatabase(DATABASE_NAME, conn);

				Assert.IsFalse(tableNames.Contains(tableName));

			}

		}



		private readonly Database testDatabase = new Database(DATABASE_NAME);

	}

}