private bool DeleteGroupEntryFromXrefTable(int groupID, GroupDeleteTableType groupDeleteTableType)
{
bool isDeleted = false;
int count = -1;
int countDeleted = -1;
string tableName = GetTableNameForGroup(groupDeleteTableType);
string sql = "";
Logger.Log("Start deleting records from table '" + tableName + "' for group id " + groupID);
try {
DbInfo = new DatabaseWrapper(Lcf);
DbInfo.Connect();
Logger.Log("Getting records from table '" + tableName + "' for group id " + groupID);
count = GetRecordsForAGroupFromXref(groupID, tableName);
if (count > 0)
{
Logger.Log("Start deleting records from '" + tableName + "' for group id = " + groupID);
sql = GroupQB.GetDelteGroupSql(groupID, tableName, false);
bool success = false;
countDeleted = DbInfo.ExecuteSQL(sql, ref success);
if (count == countDeleted)
{
Logger.Log("Successfully deleted " + count + " records from " + tableName + " for group id = " + groupID);
isDeleted = true;
}
else
{
Logger.Log("Failed to delte " + count + " records from " + tableName + " for group id = " + groupID);
}
}
else if (count == 0)
{
Logger.Log("No records was found in table '" + tableName + "' for group id " + groupID);
return(true);
}
} catch (Exception ex) {
Logger.Log("Error deleting records from table '" + tableName + "' for group id " + groupID + " at: " + ex);
return(false);
} finally {
if (isDeleted)
{
SecureContentWrapper.SecurityHasBeenModifiedThisSession = true;
}
if (DbInfo != null)
{
DbInfo.Disconnect();
}
}
return(isDeleted);
}
//---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/// <summary>
/// Removes all xref records that link the specified user to any groups.
/// 30-Jan-2015 - added the bool toggle on whether or not to use the session wrapper. This causes an issue with threaded applications as in the
/// worker thread the session wrappers are not available.
/// </summary>
/// <param name="userID">The ID of the user to remove from all groups.</param>
/// <returns>True if successfull, false otherwise.</returns>
public bool UnassignAllGroupsFromUser(int userID, bool recordModificationInSessionWrapper)
{
if (userID < 1)
{
Logger.LogError(5, "Cannot UnassignAllGroupsFromUser where userID is not specified!");
return(false);
}
bool isSuccess = false;
DbInfo = new DatabaseWrapper(Lcf);
string sql = "";
try {
DbInfo.Connect();
sql = GroupQB.GetDeleteUserFromAllGroupsSql(userID);
if (sql == null)
{
Logger.LogError(5, "Failed to get SQL to delete user from all groups! Abandoning UnassignAllGroupsFromUser ...");
return(false);
}
bool success = false;
int numChanged = DbInfo.ExecuteSQL(sql, ref success);
if (numChanged == 0)
{
isSuccess = false;
}
else
{
isSuccess = true;
}
} catch (Exception ex) {
Logger.LogError(5, "Error in changing group association at: " + ex);
return(false);
} finally {
if (isSuccess && recordModificationInSessionWrapper)
{
SecureContentWrapper.SecurityHasBeenModifiedThisSession = true;
}
if (DbInfo != null)
{
DbInfo.Disconnect();
}
}
return(isSuccess);
}
public bool ChangeUserToGroupAssociation(MGGroup group, List <int> usersIDs, AssociationTypes associationType)
{
bool ISChanged = true;
DbInfo = new DatabaseWrapper(Lcf);
string sql = "";
string partMSG = "'" + associationType + "ing' (" + usersIDs.Count + ") users to Group '" + group.Name + "'";
try {
Logger.Log("Start " + partMSG);
DbInfo.Connect();
foreach (int userID in usersIDs)
{
if (associationType == AssociationTypes.Assign)
{
sql = GroupQB.GetAssignGroupForUserSql(userID, group.ID);
}
else
{
sql = GroupQB.GetUnAssignGroupForUserSql(userID, group.ID);
}
bool success = false;
int numChanged = DbInfo.ExecuteSQL(sql, ref success);
if (numChanged == 0)
{
ISChanged = false;
}
}
} catch (Exception ex) {
Logger.LogError(5, "Error " + partMSG + " at: " + ex);
return(false);
} finally {
if (ISChanged)
{
SecureContentWrapper.SecurityHasBeenModifiedThisSession = true;
}
if (DbInfo != null)
{
DbInfo.Disconnect();
}
}
return(ISChanged);
}
//---------------------------------------------------------------------------------------------------------------------------------------------------
/// <summary>
/// Change the group association for a given user.
/// It can assign the groups to a user and also can un assign groups linked to a user.
/// 30-Jan-2015 - added the bool toggle on whether or not to use the session wrapper. This causes an issue with threaded applications as in the
/// worker thread the session wrappers are not available.
/// </summary>
/// <param name="groupsIDs">Group Ids to Assign or UnAssign</param>
/// <param name="associationType">Assign or UnAssign</param>
/// <returns>True if successfull, false other wise</returns>
public bool ChangeGroupToUserAssociation(int userID, List <int> groupsIDs, AssociationTypes associationType, bool recordModificationInSessionWrapper)
{
bool isChangeSuccess = true;
DbInfo = new DatabaseWrapper(Lcf);
string sql = "";
try {
DbInfo.Connect();
foreach (int groupID in groupsIDs)
{
if (associationType == AssociationTypes.Assign)
{
sql = GroupQB.GetAssignGroupForUserSql(userID, groupID);
}
else
{
sql = GroupQB.GetUnAssignGroupForUserSql(userID, groupID);
}
bool success = false;
int numChanged = DbInfo.ExecuteSQL(sql, ref success);
if (numChanged == 0)
{
isChangeSuccess = false;
}
}
} catch (Exception ex) {
Logger.LogError(5, "Error in changing group association at: " + ex);
isChangeSuccess = false;
} finally {
if (isChangeSuccess && recordModificationInSessionWrapper)
{
SecureContentWrapper.SecurityHasBeenModifiedThisSession = true;
if (DbInfo != null)
{
DbInfo.Disconnect();
}
}
}
return(isChangeSuccess);
}
/// <summary>
/// Given a MG Group, Add to database
/// </summary>
/// <param name="group">Group to add</param>
/// <returns>Return true if success, false otherwidr</returns>
public bool AddGroup(MGGroup groupToAdd, out string message)
{
bool isAddSuccess = false;
message = string.Empty;
try {
DbInfo = new DatabaseWrapper(Lcf);
//Check if group can be added
if (CheckIfGroupCanBeAdded(groupToAdd, out message))
{
//Insert
string sql = GroupQB.GetInsertGroupSql(groupToAdd);
DbInfo.Connect();
bool success = false;
if (DbInfo.ExecuteSQL(sql, ref success) == 1)
{
isAddSuccess = true;
message = "Successfully added a group: '" + groupToAdd.Name + "'";
}
else
{
message = "Failed to add a group: '" + groupToAdd.Name + "'";
}
}
} catch (Exception ex) {
Logger.LogError(5, "Error adding a group at " + ex);
message = "Error adding a Group " + groupToAdd.Name + ". Contact MGL.";
isAddSuccess = false;
} finally {
if (isAddSuccess)
{
SecureContentWrapper.SecurityHasBeenModifiedThisSession = true;
}
if (DbInfo != null)
{
DbInfo.Disconnect();
}
}
return(isAddSuccess);
}
private bool DeleteGroupFromMain(int groupID)
{
bool isDeleted = false;
string sql = "";
string tableName = "";
try {
Logger.Log("Trying to delete the group entry from main table '" + GroupQB.GROUP_TBLE_NAME + "'");
tableName = GetTableNameForGroup(GroupDeleteTableType.Main);
sql = GroupQB.GetDelteGroupSql(groupID, tableName, true);
DbInfo = new DatabaseWrapper(Lcf);
DbInfo.Connect();
bool success = false;
int numChanged = DbInfo.ExecuteSQL(sql, ref success);
if (numChanged == 0)
{
isDeleted = false;
}
else
{
isDeleted = true;
}
} catch (Exception ex) {
Logger.LogError(5, "Error deleting the group entry from main table '" + GroupQB.GROUP_TBLE_NAME + "' at: " + ex);
return(false);
} finally {
if (isDeleted)
{
SecureContentWrapper.SecurityHasBeenModifiedThisSession = true;
}
if (DbInfo != null)
{
DbInfo.Disconnect();
}
}
return(isDeleted);
}
public bool EditGroup(MGGroup newGroup, out string message)
{
bool isAddSuccess = false;
message = string.Empty;
try {
DbInfo = new DatabaseWrapper(Lcf);
if (CheckIfGroupCanBeEdited(newGroup, out message))
{
//Edit
string sql = GroupQB.GetEditGroupSql(newGroup);
DbInfo.Connect();
bool success = false;
if (DbInfo.ExecuteSQL(sql, ref success) == 1)
{
isAddSuccess = true;
message = "Successfully edited group: '" + newGroup.Name + "'";
}
else
{
message = "Failed to edit group: '" + newGroup.Name + "'";
}
}
} catch (Exception ex) {
Logger.LogError(5, "Error editing a group at " + ex);
message = "Error editing a Group " + newGroup.Name + ". Contact MGL.";
isAddSuccess = false;
} finally {
if (isAddSuccess)
{
SecureContentWrapper.SecurityHasBeenModifiedThisSession = true;
}
if (DbInfo != null)
{
DbInfo.Disconnect();
}
}
return(isAddSuccess);
}
//-----------------------------------------------------------------------------------------------------------------------------------------------------------
/// <summary>
/// 14-Oct-13 - New method to log the page request information in the database ...
/// </summary>
public static void LogPageRequestInDatabase(ConfigurationInfo ci, string applicationName, string aspSessionID, string uniqueSessionID,
string pageURL, DateTime pageRequestTime, double serverProcessingMS, int currentUserID, string ipAddress)
{
bool success = false;
DatabaseWrapper dbInfo = null;
try {
//_____ Check for a null configurationInfo or dbConInfo object
if (ci == null || ci.DbConInfo.NAME == null)
{
Logger.LogError(200, "Error inserting the PageRequest log into the database - null configurationInfo found, probably because of an application reset or session timeout.");
}
else
{
// 3-Mar-2016 - ignore specific IP addresses (e.g. robots like the UptimeRobot), as these are artificially bloating the logs with limited utility!
if (ipAddress == null || AddressesToIgnore.Contains(ipAddress) == false)
{
//_____ Check for s***e data and make it blank if so, so it doesn't kill the database ...
applicationName = (applicationName == null) ? "" : applicationName;
aspSessionID = (aspSessionID == null) ? "" : aspSessionID;
uniqueSessionID = (uniqueSessionID == null) ? "" : uniqueSessionID;
pageURL = (pageURL == null) ? "" : pageURL;
//_____ Connect to the database ...
dbInfo = new DatabaseWrapper(ci);
dbInfo.Connect();
//_____ Create the table if it does not already exist
string createTableSQL = @"
CREATE TABLE " + logTN + @" (
ID int NOT NULL Auto_Increment, PRIMARY KEY(ID),
Application_Name VARCHAR(255), INDEX Application_Name(Application_Name),
Session_ID_ASP VARCHAR(50), INDEX Session_ID_ASP(Session_ID_ASP),
Session_ID_Unique VARCHAR(50), INDEX Session_ID_Unique(Session_ID_Unique),
Page_Name VARCHAR(255), INDEX Page_Name(Page_Name),
Page_URL LONGTEXT,
Page_Request_Date DATETIME, INDEX Page_Request_Date(Page_Request_Date),
Server_Render_Speed DOUBLE, INDEX Server_Render_Speed(Server_Render_Speed),
Current_User_ID INT, INDEX Current_User_ID(Current_User_ID),
IP_Address VARCHAR(20), INDEX IP_Address(IP_Address)
) ENGINE=MyISAM;
";
if (dbInfo.TableExists(logTN) == false)
{
dbInfo.ExecuteSQL(createTableSQL, ref success);
}
//_____ Parse the page name
string pageName = "";
{
// remove the ?...... page params
string[] bits1 = pageURL.Split(new string[] { "?", "&" }, StringSplitOptions.None);
string[] bits2 = bits1[0].Split(new string[] { "/", "\\" }, StringSplitOptions.None);
pageName = bits2[bits2.Length - 1];
}
//_____ Build the SQL and run the insert ...
StringBuilder sql = new StringBuilder();
sql.Append("INSERT INTO " + logTN
+ " (Application_Name, Session_ID_ASP, Session_ID_Unique, Page_Name, Page_URL, Page_Request_Date, Server_Render_Speed, Current_User_ID, IP_Address ) VALUES (");
sql.Append(DataUtilities.Quote(applicationName) + ", ");
sql.Append(DataUtilities.Quote(aspSessionID) + ", ");
sql.Append(DataUtilities.Quote(uniqueSessionID) + ", ");
sql.Append(DataUtilities.Quote(pageName) + ", ");
sql.Append(DataUtilities.Quote(pageURL) + ", ");
sql.Append(DataUtilities.Quote(DateTimeInformation.FormatDatabaseDate(pageRequestTime, true, true)) + ", ");
sql.Append(serverProcessingMS + ", ");
sql.Append(currentUserID + ", ");
sql.Append(DataUtilities.DatabaseifyString(ipAddress, false));
sql.Append(");");
int numInserts = dbInfo.ExecuteSQL(sql.ToString(), ref success);
if (success == false)
{
Logger.LogError(201, "Error inserting the PageRequest log into the database - check the detailed log for details.");
}
}
}
} catch (Exception ex) {
Logger.LogError(202, "Error inserting the PageRequest log into the database: " + ex.ToString());
} finally {
if (dbInfo != null)
{
dbInfo.Disconnect();
}
}
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------------
public static void PerformanceTestMySQLWrite()
{
Logger.LogSubHeading("Testing MySQL!");
bool success = false;
// Now lets connect to the MySQL db
Logger.Log("Connecting to the database...");
DatabaseWrapper dbInfo = new DatabaseWrapper(new DatabaseConnectionInfo("localhost", "mysql", "redis_comparison",
SecureStringWrapper.Encrypt("forum"), SecureStringWrapper.Encrypt(TestParameters.RedisAuth), 3306));
dbInfo.Connect();
if (TestParameters.DoWrite == true)
{
bool tnExists = dbInfo.TableExists(TestObj.ObjectName);
// Drop the table if it exists already ...
bool tnDeleted = dbInfo.DeleteTable(TestObj.ObjectName);
// And then recreate it ...
// 22-Aug-2016 - Remove the boolean index to mirror the Redis implementation - Index TestBool(TestBool),
bool tnCreated = dbInfo.CreateTable(TestObj.ObjectName, @"
ID bigint unsigned, Primary Key(ID),
TestInt int, Index TestInt(TestInt),
TestLong bigint, Index TestLong(TestLong),
TestDouble double, Index TestDouble(TestDouble),
TestBool bool,
TestStr Varchar(20), Index TestStr(TestStr),
TestDT DateTime, Index TestDT(TestDT)
", false);
}
Logger.Log("Writing data");
DateTime writeStart = DateTime.Now;
if (TestParameters.DoWrite == true)
{
int writeCount = 0;
foreach (object o in TestParameters.RandomObjects)
{
TestObj rto = (TestObj)o;
StringBuilder sql = new StringBuilder();
sql.Append("INSERT INTO " + TestObj.ObjectName + " (ID, TestInt, TestLong, TestDouble, TestBool, TestStr, TestDT) VALUES (");
sql.Append(rto.ID + ",");
sql.Append(rto.TestInt + ",");
sql.Append(rto.TestLong + ",");
sql.Append(rto.TestDouble + ",");
sql.Append(rto.TestBool + ",");
sql.Append(DataUtilities.Quote(rto.TestStr) + ",");
sql.Append(DataUtilities.Quote(DateTimeInformation.FormatDatabaseDate(rto.TestDT, true, true)));
sql.Append(");");
dbInfo.ExecuteSQL(sql.ToString(), ref success);
Logger.Log(++writeCount, 100, TestParameters.RandomObjects.Count);
}
}
Logger.Log("");
Logger.Log("Writing completed.");
TestParameters.WriteMySQL = DateTime.Now.Subtract(writeStart);
Logger.Log("Disconnect from the database.");
dbInfo.Disconnect();
}