/JMMServer/JMMServer/Databases/SQLServer.cs

Large files files are truncated, but you can click here to view the full file

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

using System.IO;

using Microsoft.Win32;

using JMMServer.Entities;

using JMMServer.Repositories;

using NLog;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

using System.Collections;



namespace JMMServer.Databases

{

	public class SQLServer

	{

		private static Logger logger = LogManager.GetCurrentClassLogger();



		public static string GetConnectionString()

		{

			return string.Format("Server={0};Database={1};UID={2};PWD={3};",

					ServerSettings.DatabaseServer, ServerSettings.DatabaseName, ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword);

		}



		public static bool DatabaseAlreadyExists()

		{

			int count = 0;

			string cmd = string.Format("Select count(*) from sysdatabases where name = '{0}'", ServerSettings.DatabaseName);

			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, "master")))

			{

				using (SqlCommand command = new SqlCommand(cmd, tmpConn))

				{

					tmpConn.Open();

					object result = command.ExecuteScalar();

					count = int.Parse(result.ToString());

				}

			}



			// if the Versions already exists, it means we have done this already

			if (count > 0) return true;



			return false;

		}



		public static ArrayList GetData(string sql)

		{

			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				ArrayList rowList = new ArrayList();

				using (SqlCommand command = new SqlCommand(sql, tmpConn))

				{

					tmpConn.Open();

					SqlDataReader reader = command.ExecuteReader();



					

					while (reader.Read())

					{

						object[] values = new object[reader.FieldCount];

						reader.GetValues(values);

						rowList.Add(values);

					}

				}

				return rowList;

			}

		}



		public static bool TestLogin()

		{

			return true;

		}



		public static void CreateDatabaseOld()

		{

			if (DatabaseAlreadyExists()) return;



			SQLServerDatabase db = new SQLServerDatabase();



			string dataPath = GetDatabasePath(ServerSettings.DatabaseServer);



			db.DatabaseName = ServerSettings.DatabaseName;

			db.MdfFileName = ServerSettings.DatabaseName;

			db.MdfFilePath = Path.Combine(dataPath, ServerSettings.DatabaseName + ".mdf");

			db.MdfFileSize = "3072KB";

			db.MdfMaxFileSize = "UNLIMITED";

			db.MdfFileGrowth = "1024KB";

			db.LdfFileName = ServerSettings.DatabaseName + "_log";

			db.LdfFilePath = Path.Combine(dataPath, ServerSettings.DatabaseName + ".ldf");

			db.LdfFileSize = "3072KB";

			db.LdfMaxFileSize = "2048GB";

			db.LdfFileGrowth = "1024KB";





			StringBuilder sb = new StringBuilder();

			sb.AppendFormat("CREATE DATABASE [{0}] ON PRIMARY ", db.DatabaseName);

			sb.AppendFormat("( NAME = N'{0}', FILENAME = N'{1}' , SIZE = ", db.MdfFileName, db.MdfFilePath);

			sb.AppendFormat("{0} , MAXSIZE = {1}, FILEGROWTH = {2}", db.MdfFileSize, db.MdfMaxFileSize, db.MdfFileGrowth);

			sb.Append(" )");

			sb.Append("    LOG ON ");

			sb.AppendFormat("( NAME = N'{0}', FILENAME = N'{1}' , SIZE = ", db.LdfFileName, db.LdfFilePath);

			sb.AppendFormat("{0} , MAXSIZE = {1}, FILEGROWTH = {2}", db.LdfFileSize, db.LdfMaxFileSize, db.LdfFileGrowth);

			sb.Append(" ) ");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database=master", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword)))

			{

				using (SqlCommand command = new SqlCommand(sb.ToString(), tmpConn))

				{

					tmpConn.Open();

					command.ExecuteNonQuery();



					Console.WriteLine("Database created successfully!");

				}



			}

		}



		public static void CreateDatabase()

		{

			if (DatabaseAlreadyExists()) return;



			ServerConnection conn = new ServerConnection(ServerSettings.DatabaseServer, ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword);

			Server srv = new Server(conn);

			

			Database db = new Database(srv, ServerSettings.DatabaseName);

			db.Create();

		}



		#region Schema Updates



		public static void UpdateSchema()

		{



			VersionsRepository repVersions = new VersionsRepository();

			Versions ver = repVersions.GetByVersionType(Constants.DatabaseTypeKey);

			if (ver == null) return;



			int versionNumber = 0;

			int.TryParse(ver.VersionValue, out versionNumber);



			try

			{

				UpdateSchema_002(versionNumber);

				UpdateSchema_003(versionNumber);

				UpdateSchema_004(versionNumber);

				UpdateSchema_005(versionNumber);

				UpdateSchema_006(versionNumber);

				UpdateSchema_007(versionNumber);

				UpdateSchema_008(versionNumber);

				UpdateSchema_009(versionNumber);

				UpdateSchema_010(versionNumber);

				UpdateSchema_011(versionNumber);

				UpdateSchema_012(versionNumber);

				UpdateSchema_013(versionNumber);

				UpdateSchema_014(versionNumber);

				UpdateSchema_015(versionNumber);

				UpdateSchema_016(versionNumber);

				UpdateSchema_017(versionNumber);

				UpdateSchema_018(versionNumber);

				UpdateSchema_019(versionNumber);

				UpdateSchema_020(versionNumber);

				UpdateSchema_021(versionNumber);

				UpdateSchema_022(versionNumber);

				UpdateSchema_023(versionNumber);

				UpdateSchema_024(versionNumber);

				UpdateSchema_025(versionNumber);

				UpdateSchema_026(versionNumber);

			}

			catch (Exception ex)

			{

				logger.ErrorException("Error updating schema: " + ex.ToString(), ex);

			}



		}



		private static void UpdateSchema_002(int currentVersionNumber)

		{

			int thisVersion = 2;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();



			cmds.Add("CREATE TABLE IgnoreAnime( " +

				" IgnoreAnimeID int IDENTITY(1,1) NOT NULL, " +

				" JMMUserID int NOT NULL, " +

				" AnimeID int NOT NULL, " +

				" IgnoreType int NOT NULL, " +

				" CONSTRAINT [PK_IgnoreAnime] PRIMARY KEY CLUSTERED  " +

				" ( " +

				" IgnoreAnimeID ASC " +

				" )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " +

				" ) ON [PRIMARY]");



			cmds.Add("CREATE UNIQUE INDEX UIX_IgnoreAnime_User_AnimeID ON IgnoreAnime(JMMUserID, AnimeID, IgnoreType)");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_003(int currentVersionNumber)

		{

			int thisVersion = 3;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();



			cmds.Add("CREATE TABLE Trakt_Friend( " +

				" Trakt_FriendID int IDENTITY(1,1) NOT NULL, " +

				" Username nvarchar(100) NOT NULL, " +

				" FullName nvarchar(100) NULL, " +

				" Gender nvarchar(100) NULL, " +

				" Age nvarchar(100) NULL, " +

				" Location nvarchar(100) NULL, " +

				" About nvarchar(MAX) NULL, " +

				" Joined int NOT NULL, " +

				" Avatar nvarchar(MAX) NULL, " +

				" Url nvarchar(MAX) NULL, " +

				" LastAvatarUpdate datetime NOT NULL, " +

				" CONSTRAINT [PK_Trakt_Friend] PRIMARY KEY CLUSTERED  " +

				" ( " +

				" Trakt_FriendID ASC " +

				" )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " +

				" ) ON [PRIMARY]");



			cmds.Add("CREATE UNIQUE INDEX UIX_Trakt_Friend_Username ON Trakt_Friend(Username)");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}





		private static void UpdateSchema_004(int currentVersionNumber)

		{

			int thisVersion = 4;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();



			cmds.Add("ALTER TABLE AnimeGroup ADD DefaultAnimeSeriesID int NULL");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_005(int currentVersionNumber)

		{

			int thisVersion = 5;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();



			cmds.Add("ALTER TABLE JMMUser ADD CanEditServerSettings int NULL");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_006(int currentVersionNumber)

		{

			int thisVersion = 6;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();



			cmds.Add("ALTER TABLE VideoInfo ADD VideoBitDepth varchar(max) NULL");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_007(int currentVersionNumber)

		{

			int thisVersion = 7;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);





			DatabaseHelper.FixDuplicateTvDBLinks();

			DatabaseHelper.FixDuplicateTraktLinks();

			



			List<string> cmds = new List<string>();



			cmds.Add("CREATE UNIQUE INDEX UIX_CrossRef_AniDB_TvDB_Season ON CrossRef_AniDB_TvDB(TvDBID, TvDBSeasonNumber)");



			cmds.Add("CREATE UNIQUE INDEX UIX_CrossRef_AniDB_Trakt_Season ON CrossRef_AniDB_Trakt(TraktID, TraktSeasonNumber)");

			cmds.Add("CREATE UNIQUE INDEX UIX_CrossRef_AniDB_Trakt_Anime ON CrossRef_AniDB_Trakt(AnimeID)");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_008(int currentVersionNumber)

		{

			int thisVersion = 8;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			DatabaseHelper.FixDuplicateTvDBLinks();

			DatabaseHelper.FixDuplicateTraktLinks();



			List<string> cmds = new List<string>();



			cmds.Add("ALTER TABLE jmmuser ALTER COLUMN Password NVARCHAR(150) NULL");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_009(int currentVersionNumber)

		{

			int thisVersion = 9;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();



			cmds.Add("ALTER TABLE ImportFolder ADD IsWatched int NULL");

			cmds.Add("UPDATE ImportFolder SET IsWatched = 1");

			cmds.Add("ALTER TABLE ImportFolder ALTER COLUMN IsWatched int NOT NULL");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_010(int currentVersionNumber)

		{

			int thisVersion = 10;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();



			cmds.Add("CREATE TABLE CrossRef_AniDB_MAL( " +

				" CrossRef_AniDB_MALID int IDENTITY(1,1) NOT NULL, " +

				" AnimeID int NOT NULL, " +

				" MALID int NOT NULL, " +

				" MALTitle nvarchar(500), " +

				" CrossRefSource int NOT NULL, " +

				" CONSTRAINT [PK_CrossRef_AniDB_MAL] PRIMARY KEY CLUSTERED " +

				" ( " +

				" CrossRef_AniDB_MALID ASC " +

				" )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " +

				" ) ON [PRIMARY] ");



			cmds.Add("CREATE UNIQUE INDEX UIX_CrossRef_AniDB_MAL_AnimeID ON CrossRef_AniDB_MAL(AnimeID)");

			cmds.Add("CREATE UNIQUE INDEX UIX_CrossRef_AniDB_MAL_MALID ON CrossRef_AniDB_MAL(MALID)");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_011(int currentVersionNumber)

		{

			int thisVersion = 11;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();



			cmds.Add("DROP INDEX [UIX_CrossRef_AniDB_MAL_AnimeID] ON [dbo].[CrossRef_AniDB_MAL] WITH ( ONLINE = OFF )");

			cmds.Add("DROP INDEX [UIX_CrossRef_AniDB_MAL_MALID] ON [dbo].[CrossRef_AniDB_MAL] WITH ( ONLINE = OFF )");

			cmds.Add("DROP TABLE [dbo].[CrossRef_AniDB_MAL]");



			cmds.Add("CREATE TABLE CrossRef_AniDB_MAL( " +

				" CrossRef_AniDB_MALID int IDENTITY(1,1) NOT NULL, " +

				" AnimeID int NOT NULL, " +

				" MALID int NOT NULL, " +

				" MALTitle nvarchar(500), " +

				" StartEpisodeType int NOT NULL, " +

				" StartEpisodeNumber int NOT NULL, " +

				" CrossRefSource int NOT NULL, " +

				" CONSTRAINT [PK_CrossRef_AniDB_MAL] PRIMARY KEY CLUSTERED " +

				" ( " +

				" CrossRef_AniDB_MALID ASC " +

				" )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " +

				" ) ON [PRIMARY] ");



			cmds.Add("CREATE UNIQUE INDEX UIX_CrossRef_AniDB_MAL_MALID ON CrossRef_AniDB_MAL(MALID)");

			cmds.Add("CREATE UNIQUE INDEX UIX_CrossRef_AniDB_MAL_Anime ON CrossRef_AniDB_MAL(AnimeID, StartEpisodeType, StartEpisodeNumber)");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_012(int currentVersionNumber)

		{

			int thisVersion = 12;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();





			cmds.Add("CREATE TABLE Playlist( " +

				" PlaylistID int IDENTITY(1,1) NOT NULL, " +

				" PlaylistName nvarchar(MAX) NULL, " +

				" PlaylistItems varchar(MAX) NULL, " +

				" DefaultPlayOrder int NOT NULL, " +

				" PlayWatched int NOT NULL, " +

				" PlayUnwatched int NOT NULL, " +

				" CONSTRAINT [PK_Playlist] PRIMARY KEY CLUSTERED " +

				" ( " +

				" PlaylistID ASC " +

				" )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " +

				" ) ON [PRIMARY] ");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_013(int currentVersionNumber)

		{

			int thisVersion = 13;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();



			cmds.Add("ALTER TABLE AnimeSeries ADD SeriesNameOverride nvarchar(500) NULL");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_014(int currentVersionNumber)

		{

			int thisVersion = 14;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();





			cmds.Add("CREATE TABLE BookmarkedAnime( " +

				" BookmarkedAnimeID int IDENTITY(1,1) NOT NULL, " +

				" AnimeID int NOT NULL, " +

				" Priority int NOT NULL, " +

				" Notes nvarchar(MAX) NULL, " +

				" Downloading int NOT NULL, " +

				" CONSTRAINT [PK_BookmarkedAnime] PRIMARY KEY CLUSTERED " +

				" ( " +

				" BookmarkedAnimeID ASC " +

				" )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " +

				" ) ON [PRIMARY] ");



			cmds.Add("CREATE UNIQUE INDEX UIX_BookmarkedAnime_AnimeID ON BookmarkedAnime(BookmarkedAnimeID)");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_015(int currentVersionNumber)

		{

			int thisVersion = 15;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();



			cmds.Add("ALTER TABLE VideoLocal ADD DateTimeCreated datetime NULL");

			cmds.Add("UPDATE VideoLocal SET DateTimeCreated = DateTimeUpdated");

			cmds.Add("ALTER TABLE VideoLocal ALTER COLUMN DateTimeCreated datetime NOT NULL");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_016(int currentVersionNumber)

		{

			int thisVersion = 16;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();



			cmds.Add("CREATE TABLE CrossRef_AniDB_TvDB_Episode( " +

				" CrossRef_AniDB_TvDB_EpisodeID int IDENTITY(1,1) NOT NULL, " +

				" AnimeID int NOT NULL, " +

				" AniDBEpisodeID int NOT NULL, " +

				" TvDBEpisodeID int NOT NULL, " +

				" CONSTRAINT [PK_CrossRef_AniDB_TvDB_Episode] PRIMARY KEY CLUSTERED " +

				" ( " +

				" CrossRef_AniDB_TvDB_EpisodeID ASC " +

				" )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " +

				" ) ON [PRIMARY] ");



			cmds.Add("CREATE UNIQUE INDEX UIX_CrossRef_AniDB_TvDB_Episode_AniDBEpisodeID ON CrossRef_AniDB_TvDB_Episode(AniDBEpisodeID)");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_017(int currentVersionNumber)

		{

			int thisVersion = 17;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();



			cmds.Add("CREATE TABLE AniDB_MylistStats( " +

				" AniDB_MylistStatsID int IDENTITY(1,1) NOT NULL, " +

				" Animes int NOT NULL, " +

				" Episodes int NOT NULL, " +

				" Files int NOT NULL, " +

				" SizeOfFiles bigint NOT NULL, " +

				" AddedAnimes int NOT NULL, " +

				" AddedEpisodes int NOT NULL, " +

				" AddedFiles int NOT NULL, " +

				" AddedGroups int NOT NULL, " +

				" LeechPct int NOT NULL, " +

				" GloryPct int NOT NULL, " +

				" ViewedPct int NOT NULL, " +

				" MylistPct int NOT NULL, " +

				" ViewedMylistPct int NOT NULL, " +

				" EpisodesViewed int NOT NULL, " +

				" Votes int NOT NULL, " +

				" Reviews int NOT NULL, " +

				" ViewiedLength int NOT NULL, " +

				" CONSTRAINT [PK_AniDB_MylistStats] PRIMARY KEY CLUSTERED " +

				" ( " +

				" AniDB_MylistStatsID ASC " +

				" )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " +

				" ) ON [PRIMARY] ");





			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_018(int currentVersionNumber)

		{

			int thisVersion = 18;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();



			cmds.Add("CREATE TABLE FileFfdshowPreset( " +

				" FileFfdshowPresetID int IDENTITY(1,1) NOT NULL, " +

				" Hash varchar(50) NOT NULL, " +

				" FileSize bigint NOT NULL, " +

				" Preset nvarchar(MAX) NULL, " +

				" CONSTRAINT [PK_FileFfdshowPreset] PRIMARY KEY CLUSTERED " +

				" ( " +

				" FileFfdshowPresetID ASC " +

				" )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " +

				" ) ON [PRIMARY] ");



			cmds.Add("CREATE UNIQUE INDEX UIX_FileFfdshowPreset_Hash ON FileFfdshowPreset(Hash, FileSize)");





			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_019(int currentVersionNumber)

		{

			int thisVersion = 19;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();



			cmds.Add("ALTER TABLE AniDB_Anime ADD DisableExternalLinksFlag int NULL");

			cmds.Add("UPDATE AniDB_Anime SET DisableExternalLinksFlag = 0");

			cmds.Add("ALTER TABLE AniDB_Anime ALTER COLUMN DisableExternalLinksFlag int NOT NULL");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_020(int currentVersionNumber)

		{

			int thisVersion = 20;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();



			cmds.Add("ALTER TABLE AniDB_File ADD FileVersion int NULL");

			cmds.Add("UPDATE AniDB_File SET FileVersion = 1");

			cmds.Add("ALTER TABLE AniDB_File ALTER COLUMN FileVersion int NOT NULL");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_021(int currentVersionNumber)

		{

			int thisVersion = 21;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();



			cmds.Add("CREATE TABLE RenameScript( " +

				" RenameScriptID int IDENTITY(1,1) NOT NULL, " +

				" ScriptName nvarchar(MAX) NULL, " +

				" Script nvarchar(MAX) NULL, " +

				" IsEnabledOnImport int NOT NULL, " +

				" CONSTRAINT [PK_RenameScript] PRIMARY KEY CLUSTERED " +

				" ( " +

				" RenameScriptID ASC " +

				" )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " +

				" ) ON [PRIMARY] ");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_022(int currentVersionNumber)

		{

			int thisVersion = 22;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();



			cmds.Add("ALTER TABLE AniDB_File ADD IsCensored int NULL");

			cmds.Add("ALTER TABLE AniDB_File ADD IsDeprecated int NULL");

			cmds.Add("ALTER TABLE AniDB_File ADD InternalVersion int NULL");



			cmds.Add("UPDATE AniDB_File SET IsCensored = 0");

			cmds.Add("UPDATE AniDB_File SET IsDeprecated = 0");

			cmds.Add("UPDATE AniDB_File SET InternalVersion = 1");



			cmds.Add("ALTER TABLE AniDB_File ALTER COLUMN IsCensored int NOT NULL");

			cmds.Add("ALTER TABLE AniDB_File ALTER COLUMN IsDeprecated int NOT NULL");

			cmds.Add("ALTER TABLE AniDB_File ALTER COLUMN InternalVersion int NOT NULL");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_023(int currentVersionNumber)

		{

			int thisVersion = 23;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();



			cmds.Add("ALTER TABLE VideoLocal ADD IsVariation int NULL");

			cmds.Add("UPDATE VideoLocal SET IsVariation = 0");

			cmds.Add("ALTER TABLE VideoLocal ALTER COLUMN IsVariation int NOT NULL");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_024(int currentVersionNumber)

		{

			int thisVersion = 24;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();



			cmds.Add("CREATE TABLE AniDB_Recommendation ( " +

				" AniDB_RecommendationID int IDENTITY(1,1) NOT NULL, " +

				" AnimeID int NOT NULL, " +

				" UserID int NOT NULL, " +

				" RecommendationType int NOT NULL, " +

				" RecommendationText nvarchar(MAX), " +

				" CONSTRAINT [PK_AniDB_Recommendation] PRIMARY KEY CLUSTERED " +

				" ( " +

				" AniDB_RecommendationID ASC " +

				" )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " +

				" ) ON [PRIMARY] ");



			cmds.Add("CREATE UNIQUE INDEX UIX_AniDB_Recommendation ON AniDB_Recommendation(AnimeID, UserID)");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_025(int currentVersionNumber)

		{

			int thisVersion = 25;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();



			cmds.Add("update CrossRef_File_Episode SET CrossRefSource=1 WHERE Hash IN (Select Hash from ANIDB_File) AND CrossRefSource=2");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateSchema_026(int currentVersionNumber)

		{

			int thisVersion = 26;

			if (currentVersionNumber >= thisVersion) return;



			logger.Info("Updating schema to VERSION: {0}", thisVersion);



			List<string> cmds = new List<string>();



			cmds.Add("CREATE TABLE LogMessage ( " +

				" LogMessageID int IDENTITY(1,1) NOT NULL, " +

				" LogType nvarchar(MAX) NOT NULL, " +

				" LogContent nvarchar(MAX) NOT NULL, " +

				" LogDate datetime NOT NULL, " +

				" CONSTRAINT [PK_LogMessage] PRIMARY KEY CLUSTERED " +

				" ( " +

				" LogMessageID ASC " +

				" )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " +

				" ) ON [PRIMARY] ");



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in cmds)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			UpdateDatabaseVersion(thisVersion);



		}



		private static void UpdateDatabaseVersion(int versionNumber)

		{

			VersionsRepository repVersions = new VersionsRepository();

			Versions ver = repVersions.GetByVersionType(Constants.DatabaseTypeKey);

			if (ver == null) return;



			ver.VersionValue = versionNumber.ToString();

			repVersions.Save(ver);

		}



		#endregion



		#region Create Initial Schema



		



		public static void CreateInitialSchema()

		{

			



			int count = 0;

			string cmd = string.Format("Select count(*) from sysobjects where name = 'Versions'");

			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				using (SqlCommand command = new SqlCommand(cmd, tmpConn))

				{

					tmpConn.Open();

					object result = command.ExecuteScalar();

					count = int.Parse(result.ToString());

				}

			}



			// if the Versions already exists, it means we have done this already

			if (count > 0) return;



			//Create all the commands to be executed

			List<string> commands = new List<string>();

			commands.AddRange(CreateTableString_Versions());

			commands.AddRange(CreateTableString_AniDB_Anime());

			commands.AddRange(CreateTableString_AniDB_Anime_Category());

			commands.AddRange(CreateTableString_AniDB_Anime_Character());

			commands.AddRange(CreateTableString_AniDB_Anime_Relation());

			commands.AddRange(CreateTableString_AniDB_Anime_Review());

			commands.AddRange(CreateTableString_AniDB_Anime_Similar());

			commands.AddRange(CreateTableString_AniDB_Anime_Tag());

			commands.AddRange(CreateTableString_AniDB_Anime_Title());

			commands.AddRange(CreateTableString_AniDB_Category());

			commands.AddRange(CreateTableString_AniDB_Character());

			commands.AddRange(CreateTableString_AniDB_Character_Seiyuu());

			commands.AddRange(CreateTableString_AniDB_Seiyuu());

			commands.AddRange(CreateTableString_AniDB_Episode());

			commands.AddRange(CreateTableString_AniDB_File());

			commands.AddRange(CreateTableString_AniDB_GroupStatus());

			commands.AddRange(CreateTableString_AniDB_ReleaseGroup());

			commands.AddRange(CreateTableString_AniDB_Review());

			commands.AddRange(CreateTableString_AniDB_Tag());

			commands.AddRange(CreateTableString_AnimeEpisode());

			commands.AddRange(CreateTableString_AnimeGroup());

			commands.AddRange(CreateTableString_AnimeSeries());

			commands.AddRange(CreateTableString_CommandRequest());

			commands.AddRange(CreateTableString_CrossRef_AniDB_Other());

			commands.AddRange(CreateTableString_CrossRef_AniDB_TvDB());

			commands.AddRange(CreateTableString_CrossRef_File_Episode());

			commands.AddRange(CreateTableString_CrossRef_Languages_AniDB_File());

			commands.AddRange(CreateTableString_CrossRef_Subtitles_AniDB_File());

			commands.AddRange(CreateTableString_FileNameHash());

			commands.AddRange(CreateTableString_Language());

			commands.AddRange(CreateTableString_ImportFolder());

			commands.AddRange(CreateTableString_ScheduledUpdate());

			commands.AddRange(CreateTableString_VideoInfo());

			commands.AddRange(CreateTableString_VideoLocal());

			commands.AddRange(CreateTableString_DuplicateFile());

			commands.AddRange(CreateTableString_GroupFilter());

			commands.AddRange(CreateTableString_GroupFilterCondition());

			commands.AddRange(CreateTableString_AniDB_Vote());

			commands.AddRange(CreateTableString_TvDB_ImageFanart());

			commands.AddRange(CreateTableString_TvDB_ImageWideBanner());

			commands.AddRange(CreateTableString_TvDB_ImagePoster());

			commands.AddRange(CreateTableString_TvDB_Episode());

			commands.AddRange(CreateTableString_TvDB_Series());

			commands.AddRange(CreateTableString_AniDB_Anime_DefaultImage());

			commands.AddRange(CreateTableString_MovieDB_Movie());

			commands.AddRange(CreateTableString_MovieDB_Poster());

			commands.AddRange(CreateTableString_MovieDB_Fanart());

			commands.AddRange(CreateTableString_JMMUser());

			commands.AddRange(CreateTableString_Trakt_Episode());

			commands.AddRange(CreateTableString_Trakt_ImagePoster());

			commands.AddRange(CreateTableString_Trakt_ImageFanart());

			commands.AddRange(CreateTableString_Trakt_Show());

			commands.AddRange(CreateTableString_Trakt_Season());

			commands.AddRange(CreateTableString_CrossRef_AniDB_Trakt());



			commands.AddRange(CreateTableString_AnimeEpisode_User());

			commands.AddRange(CreateTableString_AnimeSeries_User());

			commands.AddRange(CreateTableString_AnimeGroup_User());

			commands.AddRange(CreateTableString_VideoLocal_User());

			



			//commands.AddRange(CreateTableString_CrossRef_AnimeEpisode_Hash());



			using (SqlConnection tmpConn = new SqlConnection(string.Format("Server={0};User ID={1};Password={2};database={3}", ServerSettings.DatabaseServer,

				ServerSettings.DatabaseUsername, ServerSettings.DatabasePassword, ServerSettings.DatabaseName)))

			{

				tmpConn.Open();

				foreach (string cmdTable in commands)

				{

					using (SqlCommand command = new SqlCommand(cmdTable, tmpConn))

					{

						command.ExecuteNonQuery();

					}

				}

			}



			Console.WriteLine("Creating version...");

			Versions ver1 = new Versions();

			ver1.VersionType = Constants.DatabaseTypeKey;

			ver1.VersionValue = "1";



			VersionsRepository repVer = new VersionsRepository();

			repVer.Save(ver1);

		}





		public static List<string> CreateTableString_Versions()

		{

			List<string> cmds = new List<string>();

			cmds.Add("CREATE TABLE [Versions]( " +

				" [VersionsID] [int] IDENTITY(1,1) NOT NULL, " +

				" [VersionType] [varchar](100) NOT NULL, " +

				" [VersionValue] [varchar](100) NOT NULL,  " +

				" CONSTRAINT [PK_Versions] PRIMARY KEY CLUSTERED  " +

				" ( " +

				" [VersionsID] ASC " +

				" )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " +

				" ) ON [PRIMARY] ");



			cmds.Add("CREATE UNIQUE INDEX UIX_Versions_VersionType ON Versions(VersionType)");



			return cmds;

		}



		public static List<string> CreateTableString_AniDB_Anime()

		{

			List<string> cmds = new List<string>();

			cmds.Add("CREATE TABLE AniDB_Anime( " +

				" AniDB_AnimeID int IDENTITY(1,1) NOT NULL, " +

				" AnimeID int NOT NULL, " +

				" EpisodeCount int NOT NULL, " +

				" AirDate datetime NULL, " +

				" EndDate datetime NULL, " +

				" URL varchar(max) NULL, " +

				" Picname varchar(max) NULL, " +

				" BeginYear int NOT NULL, " +

				" EndYear int NOT NULL, " +

				" AnimeType int NOT NULL, " +

				" MainTitle nvarchar(500) NOT NULL, " +

				" AllTitles nvarchar(1500) NOT NULL, " +

				" AllCategories nvarchar(MAX) NOT NULL, " +

				" AllTags nvarchar(MAX) NOT NULL, " +

				" Description varchar(max) NOT NULL, " +

				" EpisodeCountNormal int NOT NULL, " +

				" EpisodeCountSpecial int NOT NULL, " +

				" Rating int NOT NULL, " +

				" VoteCount int NOT NULL, " +

				" TempRating int NOT NULL, " +

				" TempVoteCount int NOT NULL, " +

				" AvgReviewRating int NOT NULL, " +

				" ReviewCount int NOT NULL, " +

				" DateTimeUpdated datetime NOT NULL, " +

				" DateTimeDescUpdated datetime NOT NULL, " +

				" ImageEnabled int NOT NULL, " +

				" AwardList varchar(max) NOT NULL, " +

				" Restricted int NOT NULL, " +

				" AnimePlanetID int NULL, " +

				" ANNID int NULL, " +

				" AllCinemaID int NULL, " +

				" AnimeNfo int NULL, " +

				" [LatestEpisodeNumber] [int] NULL, " +

				" CONSTRAINT [PK_AniDB_Anime] PRIMARY KEY CLUSTERED  " +

				" ( " +

				" [AniDB_AnimeID] ASC " +

				" )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " +

				" ) ON [PRIMARY] ");



			cmds.Add("CREATE UNIQUE INDEX UIX_AniDB_Anime_AnimeID ON AniDB_Anime(AnimeID)");



			return cmds;

		}



		public static List<string> CreateTableString_AniDB_Anime_Category()

		{

			List<string> cmds = new List<string>();

			cmds.Add("CREATE TABLE AniDB_Anime_Category ( " +

				" AniDB_Anime_CategoryID int IDENTITY(1,1) NOT NULL, " +

				" AnimeID int NOT NULL, " +

				" CategoryID int NOT NULL, " +

				" Weighting int NOT NULL, " +

				" CONSTRAINT [PK_AniDB_Anime_Category] PRIMARY KEY CLUSTERED  " +

				" ( " +

				" AniDB_Anime_CategoryID ASC " +

				" )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " +

				" ) ON [PRIMARY] ");



			cmds.Add("CREATE INDEX IX_AniDB_Anime_Category_AnimeID on AniDB_Anime_Category(AnimeID)");

			cmds.Add("CREATE UNIQUE INDEX UIX_AniDB_Anime_Category_AnimeID_CategoryID ON AniDB_Anime_Category(AnimeID, CategoryID)");



			return cmds;

		}



		public static List<string> CreateTableString_AniDB_Anime_Character()

		{

			List<string> cmds = new List<string>();

			cmds.Add("CREATE TABLE AniDB_Anime_Character ( " +

				" AniDB_Anime_CharacterID int IDENTITY(1,1) NOT NULL, " +

				" AnimeID int NOT NULL, " +

				" CharID int NOT NULL, " +

				" CharType varchar(100) NOT NULL, " +

				" EpisodeListRaw varchar(max) NULL, " +

				" CONSTRAINT [PK_AniDB_Anime_Character] PRIMARY KEY CLUSTERED  " +

				" ( " +

				" AniDB_Anime_CharacterID ASC " +

				" )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " +

				" ) ON [PRIMARY] ");



			cmds.Add("CREATE INDEX IX_AniDB_Anime_Character_AnimeID on AniDB_Anime_Character(AnimeID)");

			cmds.Add("CREATE UNIQUE INDEX UIX_AniDB_Anime_Character_AnimeID_CharID ON AniDB_Anime_Character(AnimeID, CharID)");



			return cmds;

		}



		public static List<string> CreateTableString_AniDB_Anime_Relation()

		{

			List<string> cmds = new List<string>();

			cmds.Add("CREATE TABLE AniDB_Anime_Relation ( " +

				" AniDB_Anime_RelationID int IDENTITY(1,1) NOT NULL, " +

				" AnimeID int NOT NULL, " +

				" RelatedAnimeID int NOT NULL, " +

				" RelationType varchar(100) NOT NULL, " +

				" CONSTRAINT [PK_AniDB_Anime_Relation] PRIMARY KEY CLUSTERED  " +

				" ( " +

				" AniDB_Anime_RelationID ASC " +

				" )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " +

				" ) ON [PRIMARY] ");



			cmds.Add("CREATE INDEX IX_AniDB_Anime_Relation_AnimeID on AniDB_Anime_Relation(AnimeID)");

			cmds.Add("CREATE UNIQUE INDEX UIX_AniDB_Anime_Relation_AnimeID_RelatedAnimeID ON AniDB_Anime_Relation(AnimeID, RelatedAnimeID)");



			return cmds;

		}



		public static List<string> CreateTableString_AniDB_Anime_Review()

		{

			List<string> cmds = new List<string>();

			cmds.Add("CREATE TABLE AniDB_Anime_Review ( " +

				" AniDB_Anime_ReviewID int IDENTITY(1,1) NOT NULL, " +

				" AnimeID int NOT NULL, " +

				" ReviewID int NOT NULL, " +

				" CONSTRAINT [PK_AniDB_Anime_Review] PRIMARY KEY CLUSTERED  " +

				" ( " +

				" AniDB_Anime_ReviewID ASC " +

				" )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " +

				" ) ON [PRIMARY] ");



			cmds.Add("CREATE INDEX IX_AniDB_Anime_Review_AnimeID on AniDB_Anime_Review(AnimeID)");

			cmds.Add("CREATE UNIQUE INDEX UIX_AniDB_Anime_Review_AnimeID_ReviewID ON AniDB_Anime_Review(AnimeID, ReviewID)");



			return cmds;

		}



		public static List<string> CreateTableString_AniDB_Anime_Similar()

		{

			List<string> cmds = new List<string>();

			cmds.Add("CREATE TABLE AniDB_Anime_Similar ( " +

				" AniDB_Anime_SimilarID int IDENTITY(1,1) NOT NULL, " +

				" AnimeID int NOT NULL, " +

				" SimilarAnimeID int NOT NULL, " +

				" Approval int NOT NULL, " +

				" Total int NOT NULL, " +

				" CONSTRAINT [PK_AniDB_Anime_Similar] PRIMARY KEY CLUSTERED  " +

				" ( " +

				" AniDB_Anime_SimilarID ASC " +

				" )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " +

				" ) ON [PRIMARY] ");



			cmds.Add("CREATE INDEX IX_AniDB_Anime_Similar_AnimeID on AniDB_Anime_Similar(AnimeID)");

			cmds.Add("CREATE UNIQUE INDEX UIX_AniDB_Anime_Similar_AnimeID_SimilarAnimeID ON AniDB_Anime_Similar(AnimeID, SimilarAnimeID)");



			return cmds;

		}



		public static List<string> CreateTableString_AniDB_Anime_Tag()

		{

			List<string> cmds = new List<string>();

			cmds.Add("CREATE TABLE AniDB_Anime_Tag ( " +

				" AniDB_Anime_TagID int IDENTITY(1,1) NOT NULL, " +

				" AnimeID int NOT NULL, " +

				" TagID int NOT NULL, " +

				" Approval int NOT NULL, " +

				" CONSTRAINT [PK_AniDB_Anime_Tag] PRIMARY KEY CLUSTERED  " +

				" ( " +

				" AniDB_Anime_TagID ASC " +

				" )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " +

				" ) ON [PRIMARY] ");



			cmds.Add("CREATE INDEX IX_AniDB_Anime_Tag_AnimeID on AniDB_Anime_Tag(AnimeID)");

			cmds.Add("CREATE UNIQUE INDEX UIX_AniDB_Anime_Tag_AnimeID_TagID ON AniDB_Anime_Tag(AnimeID, TagID)");



			return cmds;

		}



		public static List<string> CreateTableString_AniDB_Anime_Title()

		{

			List<string> cmds = new List<string>();

			cmds.Add("CREATE TABLE [AniDB_Anime_Title]( " +

				" AniDB_Anime_Titl…

Large files files are truncated, but you can click here to view the full file