static void Main(string[] args) { ConsoleInterop.DisableQuickEdit(); Console.CursorVisible = false; if (ConfigurationManager.AppSettings.AllKeys.Contains(SFTP_ServerKey)) { SFTP_Server = ConfigurationManager.AppSettings[SFTP_ServerKey]; } if (ConfigurationManager.AppSettings.AllKeys.Contains(SFTP_PortKey)) { SFTP_Port = int.Parse(ConfigurationManager.AppSettings[SFTP_PortKey]); } if (ConfigurationManager.AppSettings.AllKeys.Contains(SFTP_UserKey)) { SFTP_User = ConfigurationManager.AppSettings[SFTP_UserKey]; } if (ConfigurationManager.AppSettings.AllKeys.Contains(SFTP_PasswordKey)) { SFTP_Password = ConfigurationManager.AppSettings[SFTP_PasswordKey]; } if (ConfigurationManager.AppSettings.AllKeys.Contains(SFTP_PublishedFilesDirectoryKey)) { SFTP_PublishedFilesDirectory = ConfigurationManager.AppSettings[SFTP_PublishedFilesDirectoryKey]; } if (ConfigurationManager.AppSettings.AllKeys.Contains(SFTP_UploadedFilesDirectoryKey)) { SFTP_UploadedFilesDirectory = ConfigurationManager.AppSettings[SFTP_UploadedFilesDirectoryKey]; } SFTP_Credentials = new NetworkCredential(SFTP_User, SFTP_Password); _sftpClient = new SftpClient(SFTP_Server, SFTP_Port, SFTP_Credentials.UserName, SFTP_Credentials.Password); if (ConfigurationManager.AppSettings.AllKeys.Contains(FTP_ServerKey)) { FTP_Server = ConfigurationManager.AppSettings[FTP_ServerKey]; } if (ConfigurationManager.AppSettings.AllKeys.Contains(FTP_PortKey)) { FTP_Port = int.Parse(ConfigurationManager.AppSettings[FTP_PortKey]); } if (ConfigurationManager.AppSettings.AllKeys.Contains(FTP_UserKey)) { FTP_User = ConfigurationManager.AppSettings[FTP_UserKey]; } if (ConfigurationManager.AppSettings.AllKeys.Contains(FTP_PasswordKey)) { FTP_Password = ConfigurationManager.AppSettings[FTP_PasswordKey]; } if (ConfigurationManager.AppSettings.AllKeys.Contains(FTP_PublishedFilesDirectoryKey)) { FTP_PublishedFilesDirectory = ConfigurationManager.AppSettings[FTP_PublishedFilesDirectoryKey]; } if (ConfigurationManager.AppSettings.AllKeys.Contains(FTP_UploadedFilesDirectoryKey)) { FTP_UploadedFilesDirectory = ConfigurationManager.AppSettings[FTP_UploadedFilesDirectoryKey]; } FTP_Credentials = new NetworkCredential(FTP_User, FTP_Password); Console.Write("Add project dispositions if not exist : "); using (var sqlConn = new SqlConnection(GetConnectionString())) { ExecuteScript(sqlConn, "ProjectDispositionMemorized"); } Console.WriteLine("OK\n"); Console.WriteLine("Add IsDeleted to Audit if not exists"); using (var sqlConn = new SqlConnection(GetConnectionString())) { ExecuteCommandFormat <int>(sqlConn, $"EXEC AddColumnIfNotExists 'Audit', 'IsDeleted', '[BIT] NOT NULL DEFAULT((0))';"); sqlConn.Close(); } Console.WriteLine("OK\n"); Console.WriteLine("Add LinkedInspectionId to InspectionStep if not exists"); using (var sqlConn = new SqlConnection(GetConnectionString())) { bool exists = false; using (SqlDataReader reader = ExecuteCommandFormat <SqlDataReader>(sqlConn, $"SELECT column_id FROM sys.columns WHERE NAME = 'LinkedInspectionId' AND object_id = OBJECT_ID('InspectionStep');")) { exists = reader.HasRows; reader.Close(); sqlConn.Close(); } Console.WriteLine($"LinkedInspectionId exists : {(exists ? "Yes\n" : "No")}"); if (!exists) { Console.Write("Clear all inspections : "); ExecuteScript(sqlConn, "ClearAllInspections"); Console.WriteLine("OK"); Console.Write("Add LinkedInspectionId to InspectionStep : "); ExecuteScript(sqlConn, "AddInspectionStep_LinkedInspectionStep"); Console.WriteLine("OK\n"); } } Console.WriteLine("Add QualificationReason table if not exists"); using (var sqlConn = new SqlConnection(GetConnectionString())) { bool exists = false; using (SqlDataReader reader = ExecuteCommandFormat <SqlDataReader>(sqlConn, $"SELECT * FROM sys.tables WHERE NAME = 'QualificationReason';")) { exists = reader.HasRows; reader.Close(); sqlConn.Close(); } Console.WriteLine($"QualificationReason table exists : {(exists ? "Yes\n" : "No")}"); if (!exists) { Console.Write("Add QualificationReason table : "); ExecuteScript(sqlConn, "QualificationReason"); Console.WriteLine("OK\n"); } } Console.WriteLine("Add IsDeleted and AnomalyOrigin if not exist"); using (var sqlConn = new SqlConnection(GetConnectionString())) { ExecuteScript(sqlConn, "IsDeleted_AnomalyOrigin"); } Console.WriteLine("OK\n"); Console.WriteLine("Extract thumbnails"); string thumbnailsDir = @"C:\Utils\Thumbnails"; using (var sqlConn = new SqlConnection(GetConnectionString())) { Console.Write("Configure OLE : "); ExecuteScript(sqlConn, "ConfigureOle", true, "master"); Console.WriteLine("OK"); WindowsIdentity currentIdentity = WindowsIdentity.GetCurrent(); Console.Write($"Configure Role Server bulkadmin for {currentIdentity.Name} : "); ExecuteCommandFormat <int>(sqlConn, "ALTER SERVER ROLE [bulkadmin] ADD MEMBER [{0}];", currentIdentity.Name); Console.WriteLine("OK"); } using (var sqlConn = new SqlConnection(GetConnectionString())) { Console.Write("Add ThumbnailHash to Action : "); ExecuteScript(sqlConn, "ActionThumbnailHash"); Console.WriteLine("OK"); Console.Write("Add Action_ExportThumbnail procedure : "); ExecuteScript(sqlConn, "Action_ExportThumbnail"); Console.WriteLine("OK"); Console.Write("Add Action_ExportAllThumbnail procedure : "); ExecuteScript(sqlConn, "Action_ExportAllThumbnail"); Console.WriteLine("OK"); Console.Write($"Create thumbnails directory ({thumbnailsDir}) : "); Directory.CreateDirectory(thumbnailsDir); Console.WriteLine("OK"); Console.Write($"Extract thumbnails : "); ExecuteCommandFormat <int>(sqlConn, "EXECUTE dbo.[Action_ExportAllThumbnail] N'{0}', N'{1}';", thumbnailsDir, ".jpg"); Console.WriteLine("OK"); Console.Write($"List exported thumbnails : "); var thumbnails = Directory.EnumerateFiles(thumbnailsDir); Console.WriteLine("OK"); Console.Write($"Compute hashes and rename thumbnails : "); Dictionary <int, (string hash, string extension)> hashThumbnailsDict = new Dictionary <int, (string hash, string extension)>(); foreach (string thumbnail in thumbnails.Where(_ => int.TryParse(Path.GetFileNameWithoutExtension(_), out int tmpResult))) { HashAlgorithm murmur128 = MurmurHash.Create128(managed: false); string newName; using (var fileStream = File.OpenRead(thumbnail)) { newName = ToHashString(murmur128.ComputeHash(fileStream)); } hashThumbnailsDict.Add(int.Parse(Path.GetFileNameWithoutExtension(thumbnail)), (newName, Path.GetExtension(thumbnail))); newName = $"{newName}{Path.GetExtension(thumbnail)}"; if (File.Exists(Path.Combine(thumbnailsDir, newName))) { File.Delete(thumbnail); } else { File.Move(thumbnail, Path.Combine(thumbnailsDir, newName)); } } Console.WriteLine("OK"); Console.Write($"Insert thumbnails into CloudFile : "); var cloudFiles = new Dictionary <string, string>(); foreach (var kv in hashThumbnailsDict) { if (!cloudFiles.ContainsKey(kv.Value.hash)) { cloudFiles.Add(kv.Value.hash, kv.Value.extension); } } foreach (var kv in cloudFiles) { ExecuteCommandFormat <int>(sqlConn, "INSERT INTO [dbo].[CloudFile] ([Hash],[Extension]) VALUES ('{0}','{1}');", kv.Key, kv.Value); } Console.WriteLine("OK"); Console.Write($"Update CloudFile links in Action : "); foreach (var kv in hashThumbnailsDict) { ExecuteCommandFormat <int>(sqlConn, "UPDATE [dbo].[Action] SET [ThumbnailHash] = '{0}' WHERE [ActionId] = {1};", kv.Value.hash, kv.Key); } Console.WriteLine("OK"); Console.Write($"Delete Thumbnail from Action : "); ExecuteCommandFormat <int>(sqlConn, "ALTER TABLE [dbo].[Action] DROP COLUMN [Thumbnail];"); Console.WriteLine("OK"); Console.Write($"Delete Action_ExportAllThumbnail procedure : "); ExecuteCommandFormat <int>(sqlConn, "DROP PROCEDURE [dbo].Action_ExportAllThumbnail;"); Console.WriteLine("OK"); Console.Write($"Delete Action_ExportThumbnail procedure : "); ExecuteCommandFormat <int>(sqlConn, "DROP PROCEDURE [dbo].Action_ExportThumbnail;"); Console.WriteLine("OK\n"); } Console.Write("Copy thumbnails to SFTP : "); var thumbnailsToCopy = Directory.EnumerateFiles(thumbnailsDir); long totalSize = thumbnailsToCopy.Sum(_ => (new FileInfo(_)).Length); long alreadyCopied = 0; byte[] buffer = new byte[BufferSize]; try { _sftpClient.Connect(); foreach (var thumbnailToCopy in thumbnailsToCopy) { using (var localFileStream = File.OpenRead(thumbnailToCopy)) using (var remoteStream = _sftpClient.OpenWrite($"{SFTP_PublishedFilesDirectory}/{Path.GetFileName(thumbnailToCopy)}")) { int readBytes = localFileStream.Read(buffer, 0, buffer.Length); while (readBytes > 0) { remoteStream.Write(buffer, 0, readBytes); alreadyCopied += readBytes; ConsoleProgress.Write("{0}%", alreadyCopied * 100 / totalSize); readBytes = localFileStream.Read(buffer, 0, buffer.Length); } } } _sftpClient.Disconnect(); ConsoleProgress.Finish("OK\n"); } catch (Exception e) { Console.WriteLine("FAIL"); Console.WriteLine(e.Message); Console.WriteLine("\nFinish.\nPress a key to exit..."); Console.ReadKey(); return; } Console.WriteLine("Add CloudFile links if not exist"); using (var sqlConn = new SqlConnection(GetConnectionString())) { ExecuteScript(sqlConn, "CloudFile_Links"); } Console.WriteLine("OK\n"); Console.WriteLine("Migrate videos of projects to videos of processes"); using (var sqlConn = new SqlConnection(GetConnectionString())) { Console.Write("Insert video resources : "); ExecuteScript(sqlConn, "VideoResources"); Console.WriteLine("OK"); Console.Write("Add VideoSync table if not exists : "); ExecuteScript(sqlConn, "VideoSync"); Console.WriteLine("OK"); Console.Write("Update Video if necessary : "); ExecuteScript(sqlConn, "VideoProjectToProcess"); Console.WriteLine("OK\n"); } Console.WriteLine("Fix hashes"); using (var sqlConn = new SqlConnection(GetConnectionString())) { Console.Write("Part 1 : "); ExecuteScript(sqlConn, "FixHashes1"); Console.WriteLine("OK"); string constraintName = null; Console.Write("Update PublishedFile.Hash : "); using (SqlDataReader reader = ExecuteCommandFormat <SqlDataReader>(sqlConn, $"SELECT c.[name] FROM[sys].[key_constraints] c INNER JOIN[sys].[objects] o ON c.[parent_object_id] = o.[object_id] WHERE c.[type] = 'PK' AND o.[name] = 'PublishedFile';")) { if (reader.HasRows) { reader.Read(); constraintName = reader.GetString(0); } reader.Close(); sqlConn.Close(); } if (!string.IsNullOrEmpty(constraintName)) { ExecuteCommandFormat <int>(sqlConn, "ALTER TABLE [dbo].[PublishedFile] DROP CONSTRAINT {0};", constraintName); } ExecuteCommandFormat <int>(sqlConn, "ALTER TABLE [dbo].[PublishedFile] ALTER COLUMN [Hash] NCHAR(32) NOT NULL;"); ExecuteCommandFormat <int>(sqlConn, "ALTER TABLE [dbo].[PublishedFile] ADD CONSTRAINT PK_PublishedFile PRIMARY KEY (Hash);"); Console.WriteLine("OK"); constraintName = null; Console.Write("Update CutVideo.Hash : "); using (SqlDataReader reader = ExecuteCommandFormat <SqlDataReader>(sqlConn, $"SELECT c.[name] FROM[sys].[key_constraints] c INNER JOIN[sys].[objects] o ON c.[parent_object_id] = o.[object_id] WHERE c.[type] = 'PK' AND o.[name] = 'CutVideo';")) { if (reader.HasRows) { reader.Read(); constraintName = reader.GetString(0); } reader.Close(); sqlConn.Close(); } if (!string.IsNullOrEmpty(constraintName)) { ExecuteCommandFormat <int>(sqlConn, "ALTER TABLE [dbo].[CutVideo] DROP CONSTRAINT {0};", constraintName); } ExecuteCommandFormat <int>(sqlConn, "ALTER TABLE [dbo].[CutVideo] ALTER COLUMN [Hash] NCHAR(32) NOT NULL;"); ExecuteCommandFormat <int>(sqlConn, "ALTER TABLE [dbo].[CutVideo] ADD CONSTRAINT PK_CutVideo PRIMARY KEY (Hash);"); Console.WriteLine("OK"); Console.Write("Part 2 : "); ExecuteScript(sqlConn, "FixHashes2"); Console.WriteLine("OK\n"); } Console.WriteLine("Add Timeslot if not exist"); using (var sqlConn = new SqlConnection(GetConnectionString())) { ExecuteScript(sqlConn, "Timeslot"); } Console.WriteLine("OK\n"); Console.WriteLine("Add InspectionSchedule if not exist"); using (var sqlConn = new SqlConnection(GetConnectionString())) { ExecuteScript(sqlConn, "InspectionSchedule"); } Console.WriteLine("OK\n"); Console.Write("Copy FTP files to SFTP : "); if (FTP_to_SFTP()) { ConsoleProgress.Finish("OK\n"); } else { ConsoleProgress.Finish("FAIL\n"); } Console.WriteLine("\nFinish.\nPress a key to exit..."); Console.ReadKey(); }