private DatabaseWriterEventArgs GetEventArg(DatabaseWriterRequests request)
{
DatabaseWriterEventArgs dbwEvent = m_Factory.Get();
dbwEvent.Clean();
dbwEvent.Request = request;
return(dbwEvent);
}// GetEventArg()
}// SendEmailMessage().
//
//
//
// **** InsertIntoTable() ****
//
/// <summary>
///
/// </summary>
/// <param name="tableName"></param>
/// <param name="data">Dictionary object loaded directly into event. Not affected by this routine.</param>
/// <param name="logMessage"></param>
/// <returns>String of outgoing query, suitable to add to your log.</returns>
public bool InsertIntoTable(string tableName, ref Dictionary <string, string> data, out string logMessage)
{
bool isSuccessful = true;
StringBuilder msg = new StringBuilder();
msg.AppendFormat("DatabaseWriterHub.InsertIntoTable {0} ", tableName);
//
// Create query
//
DatabaseWriterEventArgs dbEntry = GetEventArg();
// base query
dbEntry.QueryBase.AppendFormat("INSERT INTO {0} (", tableName);
string[] keys = new string[data.Keys.Count];
data.Keys.CopyTo(keys, 0);
for (int i = 0; i < keys.Length; ++i)
{
dbEntry.QueryBase.AppendFormat(" {0}", keys[i]);
if (i != keys.Length - 1)
{
dbEntry.QueryBase.Append(",");
}
}
dbEntry.QueryBase.Append(") VALUES ");
// add the values for query
dbEntry.QueryValues.Append("(");
for (int i = 0; i < keys.Length; ++i)
{
dbEntry.QueryValues.AppendFormat(" \'{0}\'", data[keys[i]]);
if (i != keys.Length - 1)
{
dbEntry.QueryValues.Append(",");
}
}
dbEntry.QueryValues.Append(");");
isSuccessful = this.HubEventEnqueue(dbEntry);
// Write Log.
if (isSuccessful)
{
msg.Append("Success writing: ");
}
else
{
msg.Append("Failed writing: ");
}
foreach (string s in data.Keys)
{
msg.AppendFormat("[{0}={1}]", s, data[s]);
}
this.Log.NewEntry(LogLevel.Major, msg.ToString());
// Exit;
logMessage = msg.ToString();
return(isSuccessful);
}//InsertIntoTable().
}// ProcessFailedQueries().
//
//
//
// *****************************************************
// **** ProcessChronicQueries() ****
// *****************************************************
/// <summary>
/// These queries have failed many times already. There are many possible reasons.
/// This method will make some attempt to correct these reasons, otherwise write these
/// chronic queries to a file for the user to figure out later.
/// </summary>
private void ProcessChronicQueries()
{
Log.NewEntry(LogLevel.Warning, "ProcessChronicQueries: attempting to fix chronically failed queries.");
Queue <DatabaseWriterEventArgs> queriesToDump = new Queue <DatabaseWriterEventArgs>();
Queue <string> valuesList = new Queue <string>();
while (m_ChronicQueries.Count > 0)
{
DatabaseWriterEventArgs chronicQuery = m_ChronicQueries.Dequeue();
const string valueKey = "values";
int valueStart = chronicQuery.Query.IndexOf(valueKey, StringComparison.CurrentCultureIgnoreCase);
string headerString = chronicQuery.Query.Substring(0, valueStart);
string valueString = chronicQuery.Query.Substring(valueStart + valueKey.Length);
if (valueString.Contains("),("))
{ // This query has compound rows, try to split them out.
string s3 = valueString.Trim(';', ' '); // remove trailing ';'
string[] s2 = s3.Split(new string[] { "),(" }, StringSplitOptions.RemoveEmptyEntries);
foreach (string s in s2) // strip out silly entries.
{
string element = s.Trim(')', '(', ' ');
if (!String.IsNullOrEmpty(element))
{
valuesList.Enqueue(element);
}
}
// Process elements
if (valuesList.Count > 1)
{
Log.NewEntry(LogLevel.Warning, "ProcessChronicQueries: Breaking up chronic query into {0} pieces.", valuesList.Count.ToString());
while (valuesList.Count > 0)
{
string element = valuesList.Dequeue();
if (!String.IsNullOrEmpty(element) && element.Length > 1)
{
DatabaseWriterEventArgs f = this.GetEventArg(DatabaseWriterRequests.Write);
f.QueryBase.Append(headerString);
f.QueryValues.AppendFormat("VALUES ({0});", element); // reconstituting trailing semicolon!
m_FailedQueries.Enqueue(f); // try this again.
}
}
}
else
{
queriesToDump.Enqueue(chronicQuery);
}
}
else
{
queriesToDump.Enqueue(chronicQuery);
}
}
if (queriesToDump.Count > 0)
{
DumpChronicQuery(queriesToDump);
}
}// ProcessChronicQueries()
} //WriteVerbose
//
//
#endregion//Private Methods
#region HubEvent Handlers
// *****************************************************************
// **** HubEvent Handlers ****
// *****************************************************************
//
protected override void HubEventHandler(EventArgs[] eArgList)
{
//
// Extract and separate eventArgs we process.
//
foreach (EventArgs eArg in eArgList)
{
// DBW Requests
if (eArg is DatabaseWriterEventArgs)
{
DatabaseWriterEventArgs dbwEvent = (DatabaseWriterEventArgs)eArg;
if (dbwEvent.Request == DatabaseWriterRequests.Stop)
{
m_IsBeginningExitSequence = true; // signal our desired to shutdown.
}
else if (dbwEvent.Request == DatabaseWriterRequests.Write)
{
m_NewQueries.Enqueue(dbwEvent); // add to outgoing write queue.
}
else if (dbwEvent.Request == DatabaseWriterRequests.SendEmail)
{
m_NewEmails.Enqueue(dbwEvent); // add to outgoing emails.
}
else
{
Log.NewEntry(LogLevel.Warning, "Unknown DBWEventArg request received. EventArg = {0}.", dbwEvent.ToString());
}
}
else
{
Log.NewEntry(LogLevel.Warning, "Unknown EventArg received. EventArg = {0}.", eArg.ToString());
}
}//next event
//
// Test for exit condition.
//
if (m_IsBeginningExitSequence)
{
base.m_WaitListenUpdatePeriod = 1500;
if (m_NewEmails.Count > 0)
{
SendEmailNow();
}
if (m_NewQueries.Count > 0)
{
WriteNow();
}
base.Stop();
}
}//HubEventHandler
}// Initialize().
//
//
#endregion//Constructors
#region Public Methods
// *****************************************************************
// **** Public Methods ****
// *****************************************************************
//
//
// **** Send Email Message ****
//
/// <summary>
/// This method provides a simple way to send emails, constructing the appropriate
/// DBWRequest object for a send email request.
/// </summary>
/// <param name="subject"></param>
/// <param name="aMsg"></param>
/// <returns></returns>
public bool SendEmailMessage(string subject, string aMsg)
{
if (IsEmailSendOn)
{
DatabaseWriterEventArgs eventArg = this.GetEventArg(DatabaseWriterRequests.SendEmail);
eventArg.QueryBase.Append(subject);
eventArg.QueryValues.Append(aMsg);
return(this.HubEventEnqueue(eventArg));
}
else
{
return(false);
}
}// SendEmailMessage().
//
// *****************************************************
// **** ProcessFailQueries() ****
// *****************************************************
/// <summary>
/// Queries in the FailQueriesQueue have failed to be written in the past.
/// It might have been they that threw the write exception, or another query.
/// Here, we try to write them again. If they fail several more times, they are pushed
/// onto the Chronic queue for later study.
/// </summary>
private void ProcessFailedQueries()
{
MySqlCommand cmd = new MySqlCommand();
int nFailedRowsWritten = 0;
Log.NewEntry(LogLevel.Warning, "WriteNow: Retry failed {0} queries.", m_FailedQueries.Count.ToString());
Queue <DatabaseWriterEventArgs> failedAgain = new Queue <DatabaseWriterEventArgs>();
while (m_FailedQueries.Count > 0)
{
DatabaseWriterEventArgs f = m_FailedQueries.Dequeue();
if (f.NFails < 3)
{
cmd.CommandText = f.Query;
cmd.Connection = m_MySqlBarTableConnection;
try
{
nFailedRowsWritten += cmd.ExecuteNonQuery();
m_Factory.Recycle(f); //this.RecycleEventArg(f);
}
catch (Exception ex)
{
f.NFails++; // increment failure counter.
if (failedAgain.Count < 2)
{
Log.NewEntry(LogLevel.Warning, "WriteNow: Query failed {1} times. Exception = {0} Query = {1}.", ex.Message, f.NFails.ToString(), f.Query);
}
failedAgain.Enqueue(f); // store failed query.
}
}
else
{ // Mark this query as chronic.
if (m_ChronicQueries.Count < 2)
{
Log.NewEntry(LogLevel.Warning, "WriteNow: Chronic queries have been found.");
}
m_ChronicQueries.Enqueue(f);
}
}
// Push those that failed again onto the queue.
if (failedAgain.Count > 0)
{
Log.NewEntry(LogLevel.Warning, "WriteNow: A total of {0} queries failed again.", failedAgain.Count.ToString());
while (failedAgain.Count > 0)
{
m_FailedQueries.Enqueue(failedAgain.Dequeue()); // push back onto failed queue.
}
}
}// ProcessFailedQueries().
}//InsertIntoTable().
//
//
// **** Execute NonQuery() ****
/// <summary>
/// User has provided a non-query command for us to process. We do nothing but
/// send off his request.
/// </summary>
/// <param name="msg"></param>
/// <returns></returns>
public bool ExecuteNonQuery(string msg)
{
bool isSuccessful = true;
// Create query
DatabaseWriterEventArgs dbEntry = GetEventArg();
dbEntry.QueryBase.Append(msg);
dbEntry.QueryValues.Remove(0, dbEntry.QueryValues.Length); // double check this is clear.
isSuccessful = this.HubEventEnqueue(dbEntry);
// Write Log.
if (!isSuccessful)
{
Log.NewEntry(LogLevel.Warning, "NonQueryExecution: Failed to enqueue {0}.", msg);
}
return(isSuccessful);
}//Execute
}// ProcessChronicQueries()
//
//
//
// *****************************************************
// **** DumpChronicQuery( ) ****
// *****************************************************
private void DumpChronicQuery(Queue <DatabaseWriterEventArgs> queriesToDump)
{
System.IO.StreamWriter streamWriter = null;
int nWritten = 0;
try
{
streamWriter = new System.IO.StreamWriter(m_ErrorFileName, true);
while (queriesToDump.Count > 0)
{
DatabaseWriterEventArgs query = queriesToDump.Dequeue();
streamWriter.WriteLine(query.Query);
nWritten++;
m_Factory.Recycle(query); // this.RecycleEventArg(query);
}
streamWriter.Flush();
}
catch (Exception e)
{
Log.NewEntry(LogLevel.Error, "DumpChronicQuery: Failed to write. Exception = {0}", e.Message);
while (queriesToDump.Count > 0)
{
DatabaseWriterEventArgs query = queriesToDump.Dequeue();
Log.NewEntry(LogLevel.Error, "DumpChronicQuery: FailedQuery = {0}", query.Query);
}
}
finally
{
if (streamWriter != null)
{
streamWriter.Close();
}
}
Log.NewEntry(LogLevel.Warning, "DumpChronicQuery: Dumped {0} chronic queries to error file = {1}.", nWritten.ToString(), m_ErrorFileName);
}//DumpChronicQuery()
}// GetEventArg()
//
//
//
// **** Write Now() ****
//
/// <summary>
/// Here we do actual writing the data to the database.
/// </summary>
private void WriteNow()
{
if (!IsTryToConnect())
{
Log.NewEntry(LogLevel.Warning, "WriteNow: {0} queries waiting. Connection failed. Skipping WriteNow.", m_NewQueries.Count.ToString());
return;
}
DateTime time1 = Log.GetTime();
System.IO.StreamWriter dropStreamWriter = null;
try
{
if (!string.IsNullOrEmpty(m_DropCopyFileName))
{
dropStreamWriter = new System.IO.StreamWriter(m_DropCopyFileName, true);
}
}
catch (Exception e)
{
Log.NewEntry(LogLevel.Warning, "WriteNow: Failed to open drop copy writer. Exception {0}", e.Message);
if (dropStreamWriter != null)
{
dropStreamWriter.Dispose();
dropStreamWriter = null; // signals that we won't make drop copy.
}
}
//
// Execute queries
//
int nRowsWritten = 0;
bool areExecutionsGood = true; // flag for errors.
using (MySqlCommand cmd = new MySqlCommand())
{
while (m_NewQueries.Count > 0)
{
DatabaseWriterEventArgs queryItem = m_NewQueries.Dequeue();
if (dropStreamWriter != null)
{
dropStreamWriter.WriteLine(queryItem.Query);
}
// Try to execute the non-query
if (areExecutionsGood)
{
cmd.CommandText = queryItem.Query;
cmd.Connection = m_MySqlBarTableConnection;
try
{
nRowsWritten += cmd.ExecuteNonQuery();
m_Factory.Recycle(queryItem); //this.RecycleEventArg(queryItem); // return this to heap.
}
catch (Exception ex)
{
Log.NewEntry(LogLevel.Error, "WriteNow: Exception = {0}. Query = {1}", ex.Message, queryItem.Query);
areExecutionsGood = false; // on first failure, we will cease writing.
m_FailedQueries.Enqueue(queryItem); // store failed query.
if (m_MySqlBarTableConnection != null) // for moment, I have to assume the connection is bad.
{
m_MySqlBarTableConnection.Close();
m_MySqlBarTableConnection = null;
}
if (IsEmailSendOnException) // Send email warnings on failure!
{
string msg = string.Format("WriteNow: Exception = {0}. Query = {1}", ex.Message, queryItem.Query);
SendEmailMessage(string.Format("{0} {1}", m_HubName, m_Database.Location.ToString()), msg);
}
}
}
else
{
m_FailedQueries.Enqueue(queryItem); // move to the list of failed queries.
}
}//while new qeuries remain.
}// using MySqlCommand
if (dropStreamWriter != null)
{
dropStreamWriter.Flush();
dropStreamWriter.Close();
dropStreamWriter.Dispose();
}
TimeSpan timespan = Log.GetTime().Subtract(time1);
Log.NewEntry(LogLevel.Minor, "WriteNow: {0} rows written to {1}. Completed={2}. FailedQueryQueue={3}. Elapsed time = {4}s.", nRowsWritten.ToString(), m_Database.Location.ToString(), areExecutionsGood.ToString(), m_FailedQueries.Count.ToString(), timespan.TotalSeconds.ToString("0.000"));
//
// Inform subscribers of write
//
if (this.WriteCompleted != null)
{
WriteStatusEventArgs e = new WriteStatusEventArgs();
e.Message = string.Format("{0} rows written to {1}. Completed={2}. FailedQueryQueue={3}. Elapsed time = {4}s.", nRowsWritten.ToString(), m_Database.Location.ToString(), areExecutionsGood.ToString(), m_FailedQueries.Count.ToString(), timespan.TotalSeconds.ToString("0.000"));;
WriteCompleted(this, e);
}
//
// Clean up.
//
if ((areExecutionsGood && m_FailedQueries.Count > 0) || m_FailedQueries.Count > 1000)
{
Log.NewEntry(LogLevel.Warning, "Attempting to Process {0} failed queries", m_FailedQueries.Count);
ProcessFailedQueries();
}
if (m_ChronicQueries.Count > 0)
{
Log.NewEntry(LogLevel.Warning, "Attempting to Process {0} chronic queries", m_ChronicQueries.Count);
ProcessChronicQueries();
}
}//WriteNow().
//
//
//
#endregion//public methods
#region Private Email Methods
// *****************************************************************
// **** Private Email Methods ****
// *****************************************************************
//
//
/// <summary>
/// This method compiles all the messages intended
/// </summary>
private void SendEmailNow()
{
string subject = String.Empty;
StringBuilder msg = new StringBuilder();
DateTime thisTime = Log.GetTime();
msg.AppendFormat("TimeStamp: {0} {1} \n", thisTime.ToShortDateString(), thisTime.ToString("HH:mm:ss.fff"));
//
// Compile multiple email messages into one.
//
int nEmails = 1;
while (m_NewEmails.Count > 0)
{
DatabaseWriterEventArgs eventArg = m_NewEmails.Dequeue();
// TODO: Confirm these mesages for same receivers, and same subject,
// if so, then append their bodies. Now I assume everyone gets every email message.
if (String.IsNullOrEmpty(subject))
{
subject = eventArg.QueryBase.ToString();
}
else if (!subject.Contains(eventArg.QueryBase.ToString()))
{
subject = String.Format("{0},{1}", subject, eventArg.QueryBase.ToString());
}
// Append messages.
msg.AppendFormat("\n -------------------------- Message #{0:#0}\n", nEmails.ToString());
msg.AppendFormat("{0}\n", eventArg.QueryValues.ToString());
nEmails++;
}//wend
//
// Divide recipents into groups of same domains. (Multiple domains yields exception on send.)
//
Dictionary <string, List <string> > emailRecp = new Dictionary <string, List <string> >();
foreach (string recp in m_Services.EmailRecipients)
{
string[] s = recp.Split(new char[] { '@' }, StringSplitOptions.RemoveEmptyEntries);
if (s.Length == 2)
{ // This seems like a good address.
if (!emailRecp.ContainsKey(s[1]))
{
emailRecp.Add(s[1], new List <string>()); // make an entry for this domain name.
}
emailRecp[s[1]].Add(recp); // add email address to list for this domain name.
}
else
{
Log.NewEntry(LogLevel.Error, "DatabaseWriterHub.SendEmailNow() failed to split off domain name from email recipient {0}.", recp);
}
}//next email recpient
foreach (string domainName in emailRecp.Keys)
{
//
// Create email
//
System.Net.Mail.MailMessage email = new System.Net.Mail.MailMessage();
email.From = new System.Net.Mail.MailAddress(m_Services.AppEmailAddress, m_Services.AppName);//"*****@*****.**", "Tramp");
//foreach (string recp in m_Services.EmailRecipients)
foreach (string recp in emailRecp[domainName])
{
email.To.Add(recp);
}
if (String.IsNullOrEmpty(m_EmailSubjectPrefix))
{
email.Subject = string.Format("{0}", subject);
}
else
{
email.Subject = string.Format("{0}-{1}", m_EmailSubjectPrefix, subject);
}
email.Body = msg.ToString();
//
// Send message.
//
DateTime dt = Log.GetTime();
bool isSuccessful = true;
System.Net.Mail.SmtpClient client = new System.Net.Mail.SmtpClient("smtp.gmail.com", 587);
client.EnableSsl = true;
client.UseDefaultCredentials = false;
client.Credentials = new System.Net.NetworkCredential("*****@*****.**", "DVTr@ding1");
client.DeliveryMethod = System.Net.Mail.SmtpDeliveryMethod.Network;
try
{
client.Send(email);
}
catch (Exception ex)
{
isSuccessful = false;
Log.NewEntry(LogLevel.Error, "SendEmailNow: Exception={0}", ex.Message);
}
TimeSpan ts = Log.GetTime().Subtract(dt);
Log.NewEntry(LogLevel.Minor, "SendEmailNow: Success = {0}. Appended {1} messages. ElapsedTime = {2:0.000} ", isSuccessful.ToString(), nEmails.ToString(), ts.TotalSeconds);
} // next domain name to send email.
} //SendEmailNow().
