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();
}
static bool FTP_to_SFTP()
{
long alreadyCopied = 0;
byte[] buffer = new byte[BufferSize];
try
{
using (var _ftpClient = new FtpClient(FtpProtocol.Ftp, FTP_Server, FTP_Port, FTP_Credentials))
{
_sftpClient.Connect();
var filesToCopy = _ftpClient.ListEntries(FTP_PublishedFilesDirectory);
long totalSize = filesToCopy.Sum(_ => _.Size ?? 0);
int totalFiles = filesToCopy.Count();
int copiedFiles = 0;
foreach (var fileToCopy in filesToCopy)
{
var sftpFileName = $"{SFTP_PublishedFilesDirectory}/{Path.GetFileName(fileToCopy.Name.Replace("-", ""))}";
HashAlgorithm murmur128 = MurmurHash.Create128(managed: false);
string ftpHash = null;
string sftpHash = null;
long ftpSize = fileToCopy.Size.Value;
long sftpSize = 0;
string tempFile = Path.GetTempFileName();
using (var ftpFileStream = _ftpClient.Retr(fileToCopy.Path))
using (var tempFileStream = File.Open(tempFile, FileMode.Create, FileAccess.Write, FileShare.None))
{
int readBytes = ftpFileStream.Read(buffer, 0, buffer.Length);
while (readBytes > 0)
{
tempFileStream.Write(buffer, 0, readBytes);
readBytes = ftpFileStream.Read(buffer, 0, buffer.Length);
}
}
Log.WriteLine($"Copy {fileToCopy.Name} from FTP to {tempFile}");
using (var tempFileStream = File.Open(tempFile, FileMode.Open, FileAccess.Read, FileShare.Read))
{
ftpHash = ToHashString(murmur128.ComputeHash(tempFileStream));
}
if (_sftpClient.Exists(sftpFileName))
{
sftpSize = _sftpClient.Get(sftpFileName).Length;
using (var sftpFileStream = _sftpClient.OpenRead(sftpFileName))
{
sftpHash = ToHashString(murmur128.ComputeHash(sftpFileStream));
}
}
if (ftpHash == sftpHash)
{
alreadyCopied += fileToCopy.Size.Value;
copiedFiles++;
ConsoleProgress.Write("({1}/{2}) {0}%", alreadyCopied * 100 / totalSize, copiedFiles, totalFiles);
File.Delete(tempFile);
continue;
}
Log.WriteLine($"{fileToCopy.Name} - {ftpHash} ({ftpSize}) - {sftpHash} ({(string.IsNullOrEmpty(sftpHash) ? 0 : sftpSize)})");
using (var tempFileStream = File.Open(tempFile, FileMode.Open, FileAccess.Read, FileShare.Read))
using (var sftpFileStream = _sftpClient.Open(sftpFileName, FileMode.Create, FileAccess.Write))
{
int readBytes = tempFileStream.Read(buffer, 0, buffer.Length);
while (readBytes > 0)
{
sftpFileStream.Write(buffer, 0, readBytes);
alreadyCopied += readBytes;
ConsoleProgress.Write("({1}/{2}) {0}%", alreadyCopied * 100 / totalSize, copiedFiles, totalFiles);
readBytes = tempFileStream.Read(buffer, 0, buffer.Length);
}
}
File.Delete(tempFile);
copiedFiles++;
sftpSize = _sftpClient.Get(sftpFileName).Length;
using (var sftpFileStream = _sftpClient.OpenRead(sftpFileName))
{
sftpHash = ToHashString(murmur128.ComputeHash(sftpFileStream));
}
Log.WriteLine($"Verify {fileToCopy.Name} - {ftpHash} ({ftpSize}) - {sftpHash} ({sftpSize})");
}
_sftpClient.Disconnect();
return(true);
}
}
catch (Exception e)
{
Log.WriteLine(e.Message);
return(false);
}
}
