public static void AddIndexesToTables(IImportExportFile importExportFile, SqlDatabaseConnection conn)
{
// get all tables
var schemaTables = importExportFile.GetAllTableNames();
foreach (var schemaTable in schemaTables)
{
// find index
var tableIndexes = Indexes?.Where(i => i.TableName.ToLower() == schemaTable.TableName.ToLower()).ToList();
if (tableIndexes == null || tableIndexes?.Count == 0)
{
return;
}
// add all found indexes
foreach (var index in tableIndexes)
{
// create index command
var indexId = $"{index.TableName}_{Indexes.FindIndex(i => i == index)}";
var querySb = new StringBuilder($"CREATE INDEX IF NOT EXISTS {indexId} ON {schemaTable.TableName} (");
// add the columns
foreach (var column in index.IndexColumns)
{
querySb.Append(
$"[{column}],");
}
querySb.Length--;
querySb.Append(");");
var query = querySb.ToString();
Logger.Debug($"Create index query: {query}");
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
// execute create index command
cmd.ExecuteNonQuery();
// rebuild the index
query = $"ReIndex {indexId}";
cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
cmd.ExecuteNonQuery();
Logger.Info($"Added index {indexId} to {index.TableName} for columns {JsonConvert.SerializeObject(index.IndexColumns)}");
}
}
}
public static void WriteToDisk(IImportExportFile targetImportExport, ConfigureWriteFormData config, Settings settings, bool forceWrite = false)
{
// check if 5 seconds have passed since last write to disk
if (forceWrite || (DateTime.Now - LastWriteTime).TotalSeconds >= 5 && PendingWrites)
{
// write out to disk
targetImportExport.ExportTable(config.GetLocalTargetFilePath());
PendingWrites = false;
// write to Remote
if (config.FileWriteMode != Constants.FileModeLocal)
{
var localFileName = config.GetLocalTargetFilePath();
var remoteFileName = config.GetRemoteTargetFilePath();
switch (config.FileWriteMode)
{
case Constants.FileModeFtp:
using (var client = Utility.Utility.GetFtpClient())
{
try
{
var status = client.UploadFile(localFileName, remoteFileName);
if (status == FtpStatus.Failed)
{
throw new Exception($"Could not write file to remote {remoteFileName}");
}
}
finally
{
client.Disconnect();
}
}
break;
case Constants.FileModeSftp:
using (var client = Utility.Utility.GetSftpClient())
{
try
{
var fileStream = Utility.Utility.GetStream(localFileName, Constants.FileModeLocal, true);
client.UploadFile(fileStream.Stream, remoteFileName);
fileStream.Close();
}
catch
{
throw new Exception($"Could not write file to remote {remoteFileName}");
}
finally
{
client.Disconnect();
}
}
break;
}
}
}
}
