//Email, Local Folder, AWS S3
private void LoadOtherSettings()
{
try
{
AWSS3Helper awsHelper = new AWSS3Helper();
AWSS3 = awsHelper.GetConfig();
if (AWSS3 == null)
{
AWSS3 = new AWSS3Setting();
}
EmailHelper emailHelper = new EmailHelper();
Email = emailHelper.GetConfig();
if (Email == null)
{
Email = new EmailSetting();
}
GeneralSettingHelper generalHelper = new GeneralSettingHelper();
General = generalHelper.GetConfig();
if (General == null)
{
General = new GeneralSetting();
}
}
catch (Exception ex)
{
string message = Functions.GetErrorFromException(ex);
MessageBox.Show("A problem occurred while loading settings. \n" + message, "Warning", MessageBoxButton.OK, MessageBoxImage.Warning);
}
}
protected void btnUploadFile_Click(object sender, EventArgs e)
{
AWSS3Helper awsHelper = new AWSS3Helper();
Boolean fileOK = true;
if (FileUpload1.HasFile)
{
String fileExtension =
System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
}
if (fileOK)
{
try
{
awsHelper.SaveFile("MoreFiles", Guid.NewGuid() + FileUpload1.FileName, FileUpload1.FileBytes, true);
Label1.Text = "File uploaded!";
}
catch (Exception ex)
{
Label1.Text = "File could not be uploaded.";
}
}
else
{
Label1.Text = "Cannot accept files of this type.";
}
}
public void DeleteDataTest()
{
AWSS3Helper awss3Helper = new AWSS3Helper(regionEndPoint, myAccessKey, mySecretKey);
string dataname = "Excel Test File";;
Assert.True(awss3Helper.DeleteDataItem(bucketname, dataname));
}
public void LoadStringTest()
{
AWSS3Helper awss3Helper = new AWSS3Helper(regionEndPoint, myAccessKey, mySecretKey);
string dataname = "First Try";
string datavalue = "";
Assert.True(awss3Helper.LoadString(bucketname, dataname, out datavalue));
}
public void DataItemExistTest()
{
AWSS3Helper awss3Helper = new AWSS3Helper(regionEndPoint, myAccessKey, mySecretKey);
string dataname = "Excel Test File";
Assert.False(awss3Helper.DataItemExists(bucketname, dataname));
dataname = "First Try";
Assert.True(awss3Helper.DataItemExists(bucketname, dataname));
}
public void DownloadFileTest()
{
AWSS3Helper awss3Helper = new AWSS3Helper(regionEndPoint, myAccessKey, mySecretKey);
// TODO: Put the Path of the file
string dataname = "Excel Test File";
string filepath = @"";
Assert.True(awss3Helper.FileDownload(bucketname, dataname, filepath));
}
public static AmazonS3GetAssetResult GetAssetFromS3(string bucketName, string fileNameAndPath)
{
try
{
if (String.IsNullOrEmpty(fileNameAndPath))
{
throw new ArgumentNullException(nameof(fileNameAndPath));
}
if (String.IsNullOrEmpty(bucketName))
{
throw new ArgumentNullException(nameof(bucketName));
}
var client = new AmazonS3Client(new AmazonS3Config()
{
RegionEndpoint = RegionEndpoint.APSouth1,
UseAccelerateEndpoint = true
});
GetObjectRequest request = new GetObjectRequest
{
BucketName = bucketName,
Key = fileNameAndPath
};
GetObjectResponse response = client.GetObjectAsync(request).Result;
if (response.HttpStatusCode.Equals(HttpStatusCode.OK))
{
Stream responseStream = response.ResponseStream;
var byteData = StreamHelper.ReadFully(responseStream);
AmazonS3File file = new AmazonS3File
{
Headers = AWSS3Helper.ToNameValueCollection(response.Headers),
Content = byteData,
ContentType = response.Headers.ContentType
};
return(new AmazonS3GetAssetResult {
IsSuccess = true, File = file
});
}
else
{
return(new AmazonS3GetAssetResult {
IsSuccess = false, Message = $"Error getting the file, statuscode : {response.HttpStatusCode} and AWSRequestId : {response.ResponseMetadata?.RequestId}"
});
}
}
catch (Exception ex)
{
return(new AmazonS3GetAssetResult {
IsSuccess = false, Message = $"Error getting file from s3"
});
}
}
private bool Open()
{
if (IsOpened())
{
return(true);
}
var bucketName = Details.Get("bucketName", "");
// sanity checks
if (String.IsNullOrEmpty(bucketName))
{
_setStatus(false, "Empty S3 Bucket Name");
return(false);
}
// check for empty login/password
if (String.IsNullOrEmpty(Details.Login) && String.IsNullOrEmpty(Details.Password))
{
Details.Login = Details.Get("awsAccessKey", Details.Login);
Details.Password = Details.Get("awsSecretAccessKey", Details.Password);
}
var s3Region = Amazon.RegionEndpoint.GetBySystemName(Details.Get("awsRegion", "us-east-1"));
if (s3Region.DisplayName == "Unknown")
{
_setStatus(false, "Invalid S3 Region Endpoint");
return(false);
}
for (var i = 0; i < Details.RetryCount; i++)
{
try
{
client = new AWSS3Helper(Details.Login, Details.Password, bucketName, s3Region, true);
_setStatus(true);
}
catch (Exception ex)
{
_setStatus(ex);
Close();
}
if (client != null)
{
break;
}
System.Threading.Thread.Sleep(Details.RetryWaitMs);
}
return(IsOpened());
}
public void DataListItemTest()
{
AWSS3Helper awss3Helper = new AWSS3Helper(regionEndPoint, myAccessKey, mySecretKey);
string datanameExcel = "Excel Test File";
string datanameString = "First Try";
List <string> dataitems = new List <string>();
//awss3Helper.ListDataItems (bucketname, dataitems, NotificationCallback);
awss3Helper.ListDataItems(bucketname, dataitems, delegate(int count, out bool shouldAbort) { shouldAbort = false; });
Assert.True(dataitems.ElementAt(0).Equals(datanameExcel));
Assert.True(dataitems.ElementAt(1).Equals(datanameString));
}
private void Close()
{
if (client != null)
{
try
{
using (var ptr = client)
{
client = null;
}
}
catch (Exception ex)
{
_setStatus(ex);
}
}
client = null;
}
private IEnumerable <string> GetFilesFromS3(AWSS3Helper s3, FileTransferDetails parsed)
{
// get file from s3
if (parsed.UseWildCardSearch)
{
var rgx = new System.Text.RegularExpressions.Regex(parsed.SearchPattern, System.Text.RegularExpressions.RegexOptions.IgnoreCase | System.Text.RegularExpressions.RegexOptions.Singleline);
foreach (var f in s3.GetFileList(parsed.FilePath, true, true).Where(f => !f.EndsWith("/")))
{
if (rgx.IsMatch(f))
{
yield return(f);
}
}
}
else
{
foreach (var f in s3.GetFileList(parsed.FilePath, true, true).Where(f => !f.EndsWith("/")))
{
yield return(f);
}
}
}
public bool Execute(ISessionContext context)
{
var logger = context.GetLogger();
var options = context.Options;
AWSS3Helper s3 = null;
List <string> files = null;
FileTransferDetails parsedErrorLocation = null;
try
{
var inputSearchPath = options.Get("inputSearchPath", "");
if (String.IsNullOrEmpty(inputSearchPath))
{
throw new ArgumentNullException("inputSearchPath");
}
var backupLocation = options.Get("backupLocation", "");
if (String.IsNullOrEmpty(backupLocation))
{
throw new ArgumentNullException("backupLocation");
}
var loadScript = options.Get("sqlScriptPath", "");
if (String.IsNullOrEmpty(loadScript))
{
throw new ArgumentNullException("sqlScriptPath");
}
var errorLocation = options.Get("errorLocation", "");
if (String.IsNullOrEmpty(errorLocation))
{
throw new ArgumentNullException("errorLocation");
}
var customCSharpScriptPath = options.Get("customCSharpScriptPath", "");
if (String.IsNullOrEmpty(customCSharpScriptPath))
{
throw new ArgumentNullException("customCSharpScriptPath");
}
// prepare paths
var parsedInput = FileTransferDetails.ParseSearchPath(inputSearchPath);
var parsedLoadScript = FileTransferDetails.ParseSearchPath(loadScript);
var parsedBackupLocation = FileTransferDetails.ParseSearchPath(backupLocation);
parsedErrorLocation = FileTransferDetails.ParseSearchPath(errorLocation);
var parsedCustomCSharpScriptPath = FileTransferDetails.ParseSearchPath(customCSharpScriptPath);
// open s3 connection
s3 = new AWSS3Helper(options.Get("awsAccessKey", ""), options.Get("awsSecretAccessKey", ""), parsedInput.BucketName, Amazon.RegionEndpoint.USEast1, true);
var csharpScript = s3.ReadFileAsText(parsedCustomCSharpScriptPath.FilePath, true);
// generate code
var evaluator = ScriptEvaluator.CompileAndCreateModel(csharpScript);
if (evaluator.HasError || evaluator.Model == null)
{
throw new Exception("Script compilation error. " + (evaluator.Message ?? "<empty>"));
}
// 1. check if there is any new file
files = GetFilesFromS3(s3, parsedInput).ToList();
if (files.Any())
{
logger.Info("Files found: " + files.Count);
}
else
{
logger.Debug("No file found");
return(false);
}
var connectionString = RedshiftHelper.GetConnectionString(context);
foreach (var f in files)
{
var sqlScript = s3.ReadFileAsText(parsedLoadScript.FilePath, true);
if (String.IsNullOrEmpty(sqlScript))
{
throw new Exception("invalid sql script");
}
using (var conn = new Npgsql.NpgsqlConnection(connectionString))
{
conn.Open();
var fullFilename = System.IO.Path.Combine("s3://", parsedInput.BucketName, f.Trim()).Replace('\\', '/');
options.Set("InputFilename", fullFilename);
evaluator.Model.Initialize(conn, s3, context);
evaluator.Model.BeforeExecution();
sqlScript = evaluator.Model.PrepareSqlCOPYCommand(sqlScript);
// Create a PostgeSQL connection string.
ExecuteRedshiftLoad(connectionString, logger, sqlScript, new List <string> ()
{
f
}, parsedInput);
logger.Debug("Moving files to backup folder");
evaluator.Model.AfterExecution();
// move files
var destName = System.IO.Path.Combine(parsedBackupLocation.FilePath, System.IO.Path.GetFileName(f));
s3.MoveFile(f, destName, false);
}
logger.Success("Done");
}
}
catch (Exception ex)
{
context.Error = ex.Message;
logger.Error(ex);
try
{
if (files != null && s3 != null)
{
// move files
foreach (var f in files)
{
var destName = System.IO.Path.Combine(parsedErrorLocation.FilePath, System.IO.Path.GetFileName(f));
s3.MoveFile(f, destName, false);
}
}
}
catch { }
return(false);
}
return(true);
}
public bool Execute(ISessionContext context)
{
var logger = context.GetLogger();
var options = context.Options;
AWSS3Helper s3 = null;
try
{
var loadScript = options.Get("sqlScriptPath", "");
var customCSharpScriptPath = options.Get("customCSharpScriptPath", "");
if ((!String.IsNullOrWhiteSpace(loadScript)) && (!String.IsNullOrWhiteSpace(customCSharpScriptPath)))
{
throw new Exception("No action configured");
}
// prepare paths
var parsedLoadScript = FileTransferDetails.ParseSearchPath(loadScript);
var parsedCustomCSharpScriptPath = FileTransferDetails.ParseSearchPath(customCSharpScriptPath);
// open s3 connection
s3 = new AWSS3Helper(options.Get("awsAccessKey", ""), options.Get("awsSecretAccessKey", ""), parsedLoadScript.BucketName, Amazon.RegionEndpoint.USEast1, true);
// load sql script
string sqlScript = null;
if (!String.IsNullOrWhiteSpace(loadScript))
{
sqlScript = s3.ReadFileAsText(parsedLoadScript.FilePath, true);
}
// generate code
IAWSRedshiftPluginDynamicScript customCode = null;
if (!String.IsNullOrWhiteSpace(customCSharpScriptPath))
{
// load custom code
var csharpScript = s3.ReadFileAsText(parsedCustomCSharpScriptPath.FilePath, true);
var evaluator = ScriptEvaluator.CompileAndCreateModel(csharpScript);
if (evaluator.HasError || evaluator.Model == null)
{
throw new Exception("Script compilation error. " + (evaluator.Message ?? "<empty>"));
}
customCode = evaluator.Model;
}
// execute commands
using (var conn = new Npgsql.NpgsqlConnection(RedshiftHelper.GetConnectionString(context)))
{
conn.Open();
if (customCode != null)
{
logger.Debug("Custom csharp code Initialize");
customCode.Initialize(conn, s3, context);
logger.Debug("Custom csharp code BeforeExecution");
customCode.BeforeExecution();
logger.Debug("Custom csharp code PrepareSqlCOPYCommand");
if (!String.IsNullOrEmpty(sqlScript))
{
sqlScript = customCode.PrepareSqlCOPYCommand(sqlScript);
}
}
if (!String.IsNullOrEmpty(sqlScript))
{
logger.Debug("SQL command start");
try
{
conn.Execute(sqlScript);
}
catch (Exception ex)
{
// do nothing in case of timeout... some operations may take a while to complete...
if (ex.Message.IndexOf("timeout", StringComparison.OrdinalIgnoreCase) < 0)
{
throw ex;
}
logger.Info("SQL command executed, but is still running (connection timeout)...");
}
logger.Debug("SQL command end");
}
if (customCode != null)
{
logger.Debug("Custom csharp code AfterExecution");
customCode.AfterExecution();
}
}
logger.Success("Done");
}
catch (Exception ex)
{
context.Error = ex.Message;
logger.Error(ex);
return(false);
}
return(true);
}
private async void btnValidateAws_Click(object sender, RoutedEventArgs e)
{
//validations
if (string.IsNullOrWhiteSpace(AWSS3.BucketName))
{
MessageAWS = "Bucket name cannot be empty";
return;
}
if (!string.IsNullOrWhiteSpace(AWSS3.FolderName))
{
if (AWSS3.FolderName.Contains("\\"))
{
MessageAWS = "Folder name should not contain back slashes (\\). Use forward slash (/) to specify sub-folders";
return;
}
}
if (string.IsNullOrWhiteSpace(AWSS3.AccessKeyId))
{
MessageAWS = "Access Key Id cannot be empty";
return;
}
if (string.IsNullOrWhiteSpace(AWSS3.AccessSecretKey))
{
MessageAWS = "Access Secret Key cannot be empty";
return;
}
if (string.IsNullOrWhiteSpace(AWSS3.AWSRegion))
{
MessageAWS = "AWS Region cannot be empty";
return;
}
if (AWSS3.BackupTime == null)
{
MessageAWS = "Backup time cannot be empty";
return;
}
try
{
//Create a temp file
string tmpFile = Path.Combine(EnVar.AppTempPath, "test_upload_s3_" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".txt");
File.WriteAllText(tmpFile, "File created to check write access to this bucket.");
//Try uploading a file to S3
AWSS3Helper helper = new AWSS3Helper(AWSS3.AccessKeyId, AWSS3.AccessSecretKey, AWSS3.AWSRegion, AWSS3.BucketName, AWSS3.FolderName);
MessageAWS = "Please wait while testing access to '" + AWSS3.BucketName + "' ...";
btnValidateAWS.IsEnabled = false;
AWSS3.IsValid = await helper.UploadToS3Async(tmpFile);
btnValidateAWS.IsEnabled = true;
if (AWSS3.IsValid == false)
{
MessageAWS = "AWS S3 access check failed. Settings were still saved.";
MessageBox.Show("Could not upload to specified AWS bucket.\n" + helper.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
AWSS3Helper awsHelper = new AWSS3Helper();
awsHelper.SaveConfig(AWSS3);
RaisePropertyChanged("AWSS3");
MessageAWS = "Settings successfully saved";
}
catch (Exception ex)
{
string message = Functions.GetErrorFromException(ex);
MessageBox.Show("A problem occurred while validating details.\n" + message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
public bool Execute(ISessionContext context)
{
var logger = context.GetLogger();
var options = context.Options;
AWSS3Helper s3 = null;
List <string> files = null;
FileTransferDetails parsedErrorLocation = null;
try
{
var inputSearchPath = options.Get("inputSearchPath", "");
if (String.IsNullOrEmpty(inputSearchPath))
{
throw new ArgumentNullException("inputSearchPath");
}
var backupLocation = options.Get("backupLocation", "");
if (String.IsNullOrEmpty(backupLocation))
{
throw new ArgumentNullException("backupLocation");
}
var loadScript = options.Get("sqlScriptPath", "");
if (String.IsNullOrEmpty(loadScript))
{
throw new ArgumentNullException("sqlScriptPath");
}
var errorLocation = options.Get("errorLocation", "");
if (String.IsNullOrEmpty(errorLocation))
{
throw new ArgumentNullException("errorLocation");
}
// prepare paths
var parsedInput = FileTransferDetails.ParseSearchPath(inputSearchPath);
var parsedLoadScript = FileTransferDetails.ParseSearchPath(loadScript);
var parsedBackupLocation = FileTransferDetails.ParseSearchPath(backupLocation);
parsedErrorLocation = FileTransferDetails.ParseSearchPath(errorLocation);
// open s3 connection
s3 = new AWSS3Helper(options.Get("awsAccessKey", ""), options.Get("awsSecretAccessKey", ""), parsedInput.BucketName, Amazon.RegionEndpoint.USEast1, true);
// 1. check if there is any new file
files = GetFilesFromS3(s3, parsedInput).Where(f => !f.EndsWith("/")).ToList();
if (files.Any())
{
logger.Info("Files found: " + files.Count);
var sqlScript = s3.ReadFileAsText(parsedLoadScript.FilePath, false);
if (String.IsNullOrEmpty(sqlScript))
{
throw new Exception("Invalid sql script: " + parsedLoadScript.FilePath);
}
// Create a PostgeSQL connection string.
var connectionString = RedshiftHelper.GetConnectionString(context);
ExecuteRedshiftLoad(connectionString, logger, sqlScript, files, parsedInput);
logger.Debug("Moving files to backup folder");
// move files
// TODO: what happens if move fails?
foreach (var f in files)
{
var destName = System.IO.Path.Combine(parsedBackupLocation.FilePath, System.IO.Path.GetFileName(f));
if (s3.MoveFile(f, destName, false))
{
System.Threading.Thread.Sleep(250);
if (s3.MoveFile(f, destName, false))
{
logger.Error(String.Format("Error moving file {0}, {1}", f, s3.LastError));
}
}
}
logger.Success("Done");
return(true);
}
else
{
logger.Debug("No file found");
return(false);
}
}
catch (Exception ex)
{
context.Error = ex.Message;
logger.Error(ex);
try
{
if (files != null && s3 != null)
{
// move files
foreach (var f in files)
{
var destName = System.IO.Path.Combine(parsedErrorLocation.FilePath, System.IO.Path.GetFileName(f));
s3.MoveFile(f, destName, false);
}
}
} catch {}
return(false);
}
return(true);
}
private void GenerateSummary()
{
AwsS3SettingColor = "LightGray";
EmailSettingColor = "LightGray";
ServiceColor = "LightGray";
try
{
MSSQLHelper mssqlHelper = new MSSQLHelper();
List<MSSQLBackup> sqlitems = mssqlHelper.GetConfigList();
MSSQLCount = sqlitems != null ? sqlitems.Count() : 0;
MYSQLHelper mysqlHelper = new MYSQLHelper();
List<MYSQLBackup> mysqlitems = mysqlHelper.GetConfigList();
MySQLCount = mysqlitems != null ? mysqlitems.Count() : 0;
FolderHelper folderHelper = new FolderHelper();
List<FolderBackup> folderitems = folderHelper.GetConfigList();
FolderCount = folderitems != null ? folderitems.Count() : 0;
AWSS3Helper awsHelper = new AWSS3Helper();
AWSS3Setting awsSetting = awsHelper.GetConfig();
if (awsSetting == null)
{
AwsS3SettingStatus = "AWS S3 settings not defined";
}
else if (awsSetting.IsValid == false)
{
AwsS3SettingStatus = "AWS S3 settings are not valid";
}
else if (awsSetting.IsActive == false)
{
AwsS3SettingStatus = "AWS S3 settings are not active";
}
else
{
AwsS3SettingStatus = "AWS S3 settings are valid & active";
AwsS3SettingColor = "Green";
}
EmailHelper emailHelper = new EmailHelper();
EmailSetting emailSetting = emailHelper.GetConfig();
if (emailSetting == null)
{
EmailSettingStatus = "E-Mail settings are not defined";
}
else if (emailSetting.IsValid == false)
{
EmailSettingStatus = "E-Mail settings are not valid";
}
else
{
EmailSettingStatus = "Email settings are valid & active";
EmailSettingColor = "Green";
}
ServiceStatus = "Background service is not installed";
GeneralSettingHelper generalHelper = new GeneralSettingHelper();
GeneralSetting generalSetting = generalHelper.GetConfig();
if (generalSetting != null && generalSetting.ServiceInstalled == true)
{
ServiceStatus = "Background service is installed";
ServiceColor = "Green";
btnInstallService.Visibility = Visibility.Collapsed;
btnUnInstallService.Visibility = Visibility.Visible;
}
}
catch (Exception ex)
{
string message = Functions.GetErrorFromException(ex);
MessageBox.Show("An error occurred. \n" + message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
public bool Execute(params IEnumerable <Record>[] dataStreams)
{
_lastError = null;
List <string> files = null;
FileSearchDetails parsedErrorLocation = null;
try
{
var inputSearchPath = _options.Get("inputSearchPath", "");
if (String.IsNullOrEmpty(inputSearchPath))
{
throw new ArgumentNullException("inputSearchPath");
}
var backupLocation = _options.Get("backupLocation", "");
if (String.IsNullOrEmpty(backupLocation))
{
throw new ArgumentNullException("backupLocation");
}
var loadScript = _options.Get("sqlScriptPath", "");
if (String.IsNullOrEmpty(loadScript))
{
throw new ArgumentNullException("sqlScriptPath");
}
var errorLocation = _options.Get("errorLocation", "");
if (String.IsNullOrEmpty(errorLocation))
{
throw new ArgumentNullException("errorLocation");
}
// prepare paths
var parsedInput = FileSearchDetails.ParseSearchPath(inputSearchPath);
var parsedLoadScript = FileSearchDetails.ParseSearchPath(loadScript);
var parsedBackupLocation = FileSearchDetails.ParseSearchPath(backupLocation);
parsedErrorLocation = FileSearchDetails.ParseSearchPath(errorLocation);
// open s3 connection
_s3 = new AWSS3Helper(_options.Get("awsAccessKey", ""), _options.Get("awsSecretAccessKey", ""), parsedInput.BucketName, Amazon.RegionEndpoint.USEast1, true);
// 1. check if there is any new file
files = GetFilesFromS3(_s3, parsedInput).Where(f => !f.EndsWith("/")).ToList();
if (files.Any())
{
_logger.Log("Files found: " + files.Count);
var sqlScript = _s3.ReadFileAsText(parsedLoadScript.FilePath, true);
if (String.IsNullOrEmpty(sqlScript))
{
throw new Exception("invalid sql script");
}
// Create a PostgeSQL connection string.
ExecuteRedshiftLoad(sqlScript, files, parsedInput);
_logger.Log("Moving files to backup folder");
// move files
// TODO: what happens if move fails?
foreach (var f in files)
{
var destName = System.IO.Path.Combine(parsedBackupLocation.FilePath, System.IO.Path.GetFileName(f));
_s3.MoveFile(f, destName, false);
}
_logger.Success("Done");
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
_lastError = ex.Message;
_logger.Error("[Error] " + _lastError);
try
{
if (files != null && _s3 != null)
{
// move files
foreach (var f in files)
{
var destName = System.IO.Path.Combine(parsedErrorLocation.FilePath, System.IO.Path.GetFileName(f));
_s3.MoveFile(f, destName, false);
}
}
}
catch { }
return(false);
}
return(true);
}
