private void CheckTable(string tableName, string[] columnNames, TextBoxInfoDialog infoDlg, MySqlConnection conn)
{
// Check for existence of the table
bool tableExists = this.TableExists(tableName, conn);
if (tableExists)
{
m_logger.DebugFormat("Table \"{0}\" found.", tableName);
infoDlg.AppendLine(string.Format("Table \"{0}\" found.", tableName));
}
else
{
m_logger.DebugFormat("Table {0} not found.", tableName);
infoDlg.AppendLine(string.Format("ERROR: Table \"{0}\" not found.", tableName));
return;
}
if (tableExists)
{
// Get column names from DB
List <string> columnNamesFromDB = new List <string>();
string query = string.Format("DESCRIBE {0}", tableName);
MySqlCommand cmd = new MySqlCommand(query, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
string colName = rdr[0].ToString();
columnNamesFromDB.Add(colName);
}
rdr.Close();
// Check for appropriate columns.
bool ok = true;
foreach (string c in columnNames)
{
if (columnNamesFromDB.Contains(c, StringComparer.CurrentCultureIgnoreCase))
{
infoDlg.AppendLine(string.Format("Found column \"{0}\"", c));
}
else
{
ok = false;
infoDlg.AppendLine(string.Format("ERROR: Column \"{0}\" not found!", c));
}
}
if (!ok)
{
infoDlg.AppendLine(string.Format("ERROR: Table \"{0}\" schema looks incorrect.", tableName));
}
}
}
private void createTableBtn_Click(object sender, EventArgs e)
{
string connStr = this.BuildConnectionString();
if (connStr == null) return;
TextBoxInfoDialog infoDlg = new TextBoxInfoDialog();
infoDlg.ClearText();
infoDlg.Show();
try
{
using (MySqlConnection conn = new MySqlConnection(connStr))
{
infoDlg.AppendLine("Connecting...");
conn.Open();
// User table
string tableName = this.userTableTB.Text.Trim();
infoDlg.AppendLine(Environment.NewLine +
string.Format("Creating table \"{0}\"", tableName));
if (!this.TableExists(tableName, conn))
{
// Column names
string pk = this.userPrimaryKeyColTB.Text.Trim();
string unameCol = this.unameColTB.Text.Trim();
string hashMethodCol = this.hashMethodColTB.Text.Trim();
string passwdCol = this.passwdColTB.Text.Trim();
// Is the primary key the same as the username?
bool pkIsUserName =
unameCol.Equals(pk, StringComparison.CurrentCultureIgnoreCase);
StringBuilder sql = new StringBuilder();
sql.AppendFormat("CREATE TABLE {0} ( \r\n", tableName);
if (!pkIsUserName)
sql.AppendFormat(" {0} BIGINT auto_increment PRIMARY KEY, \r\n", pk);
sql.AppendFormat(" {0} VARCHAR(128) {1}, \r\n", unameCol, pkIsUserName ? "PRIMARY KEY" : "NOT NULL UNIQUE");
sql.AppendFormat(" {0} TEXT NOT NULL, \r\n", hashMethodCol);
sql.AppendFormat(" {0} TEXT \r\n", passwdCol);
sql.Append(")"); // End create table.
infoDlg.AppendLine("Executing SQL:");
infoDlg.AppendLine(sql.ToString());
using (MySqlCommand cmd = new MySqlCommand(sql.ToString(), conn))
{
cmd.ExecuteNonQuery();
infoDlg.AppendLine(string.Format("Table \"{0}\" created.", tableName));
}
}
else
{
infoDlg.AppendLine(
string.Format("WARNING: Table \"{0}\"already exists, skipping.", tableName));
}
// Group table
tableName = this.groupTableNameTB.Text.Trim();
infoDlg.AppendLine(Environment.NewLine +
string.Format("Creating table \"{0}\"", tableName));
if (!this.TableExists(tableName, conn))
{
// Column names
string pk = this.groupTablePrimaryKeyColTB.Text.Trim();
string groupNameCol = this.groupNameColTB.Text.Trim();
// Is the primary key the same as the group name?
bool pkIsGroupName =
groupNameCol.Equals(pk, StringComparison.CurrentCultureIgnoreCase);
StringBuilder sql = new StringBuilder();
sql.AppendFormat("CREATE TABLE {0} ( \r\n", tableName);
if (!pkIsGroupName)
sql.AppendFormat(" {0} BIGINT AUTO_INCREMENT PRIMARY KEY, \r\n", pk);
sql.AppendFormat(" {0} VARCHAR(128) {1} \r\n", groupNameCol, pkIsGroupName ? "PRIMARY KEY" : "NOT NULL UNIQUE");
sql.Append(")"); // End create table.
infoDlg.AppendLine("Executing SQL:");
infoDlg.AppendLine(sql.ToString());
using (MySqlCommand cmd = new MySqlCommand(sql.ToString(), conn))
{
cmd.ExecuteNonQuery();
infoDlg.AppendLine(string.Format("Table \"{0}\" created.", tableName));
}
}
else
{
infoDlg.AppendLine(
string.Format("WARNING: Table \"{0}\"already exists, skipping.", tableName));
}
// user-Group table
tableName = this.userGroupTableNameTB.Text.Trim();
infoDlg.AppendLine(Environment.NewLine +
string.Format("Creating table \"{0}\"", tableName));
if (!this.TableExists(tableName, conn))
{
// Column names
string userFK = this.userGroupUserFKColTB.Text.Trim();
string userPK = this.userPrimaryKeyColTB.Text.Trim();
string groupFK = this.userGroupGroupFKColTB.Text.Trim();
string groupPK = this.groupTablePrimaryKeyColTB.Text.Trim();
string groupNameCol = this.groupNameColTB.Text.Trim();
string unameCol = this.unameColTB.Text.Trim();
// Is the primary key the same as the group name?
bool pkIsGroupName =
groupNameCol.Equals(groupPK, StringComparison.CurrentCultureIgnoreCase);
bool pkIsUserName =
unameCol.Equals(userPK, StringComparison.CurrentCultureIgnoreCase);
StringBuilder sql = new StringBuilder();
sql.AppendFormat("CREATE TABLE {0} ( \r\n", tableName);
sql.AppendFormat(" {0} {1}, \r\n", groupFK, pkIsGroupName ? "VARCHAR(128)" : "BIGINT");
sql.AppendFormat(" {0} {1}, \r\n", userFK, pkIsUserName ? "VARCHAR(128)" : "BIGINT");
sql.AppendFormat(" PRIMARY KEY ({0}, {1}) \r\n", userFK, groupFK);
sql.Append(")"); // End create table.
infoDlg.AppendLine("Executing SQL:");
infoDlg.AppendLine(sql.ToString());
MySqlCommand cmd = new MySqlCommand(sql.ToString(), conn);
cmd.ExecuteNonQuery();
infoDlg.AppendLine(string.Format("Table \"{0}\" created.", tableName));
}
else
{
infoDlg.AppendLine(
string.Format("WARNING: Table \"{0}\"already exists, skipping.", tableName));
}
}
}
catch (MySqlException ex)
{
infoDlg.AppendLine(String.Format("ERROR: {0}", ex.Message));
}
finally
{
infoDlg.AppendLine(Environment.NewLine + "Finished.");
}
}
private void testBtn_Click(object sender, EventArgs e)
{
TextBoxInfoDialog infoDlg = new TextBoxInfoDialog();
infoDlg.Show();
infoDlg.AppendLine("Beginning test of MySQL database..." + Environment.NewLine);
MySqlConnection conn = null;
string tableName = this.userTableTB.Text.Trim();
try
{
string connStr = this.BuildConnectionString();
if (connStr == null) return;
infoDlg.AppendLine("Connection Status");
infoDlg.AppendLine("-------------------------------------");
conn = new MySqlConnection(connStr);
conn.Open();
infoDlg.AppendLine(string.Format("Connection to {0} successful.", this.hostTB.Text.Trim()));
// Variables to be used repeatedly below
MySqlCommand cmd = null;
MySqlDataReader rdr = null;
string query = "";
// Check SSL status
if (useSslCB.Checked)
{
string cipher = "";
query = "SHOW STATUS LIKE 'Ssl_cipher'";
cmd = new MySqlCommand(query, conn);
rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
rdr.Read();
cipher = rdr[1].ToString();
}
rdr.Close();
if (string.IsNullOrEmpty(cipher))
{
infoDlg.AppendLine( "Not using SSL." );
}
else
{
infoDlg.AppendLine("SSL enabled, using cipher: " + cipher);
}
}
else
{
infoDlg.AppendLine( "Not using SSL." );
}
infoDlg.AppendLine( Environment.NewLine + "User Table" );
infoDlg.AppendLine( "-------------------------------");
CheckTable(tableName,
new string[] { this.unameColTB.Text.Trim(), this.passwdColTB.Text.Trim(), this.hashMethodColTB.Text.Trim(), this.userPrimaryKeyColTB.Text.Trim() },
infoDlg, conn);
infoDlg.AppendLine(Environment.NewLine + "Group Table");
infoDlg.AppendLine("-------------------------------");
CheckTable(this.groupTableNameTB.Text.Trim(),
new string[] { this.groupNameColTB.Text.Trim(), this.groupTablePrimaryKeyColTB.Text.Trim() },
infoDlg, conn);
infoDlg.AppendLine(Environment.NewLine + "User-Group Table");
infoDlg.AppendLine("-------------------------------");
CheckTable(this.userGroupTableNameTB.Text.Trim(),
new string[] { this.userGroupUserFKColTB.Text.Trim(), this.userGroupGroupFKColTB.Text.Trim() },
infoDlg, conn);
}
catch (Exception ex)
{
if (ex is MySqlException)
{
MySqlException mysqlEx = ex as MySqlException;
infoDlg.AppendLine("MySQL ERROR: " + mysqlEx.Message);
}
else
{
infoDlg.AppendLine(string.Format("ERROR: A fatal error occured: {0}", ex));
}
}
finally
{
infoDlg.AppendLine(Environment.NewLine + "Closing connection.");
if (conn != null)
conn.Close();
infoDlg.AppendLine("Test complete.");
}
}
private void createTableBtn_Click(object sender, EventArgs e)
{
string connStr = this.BuildConnectionString();
if (connStr == null) return;
TextBoxInfoDialog infoDlg = new TextBoxInfoDialog();
infoDlg.ClearText();
infoDlg.Show();
try
{
using (MySqlConnection conn = new MySqlConnection(connStr))
{
infoDlg.AppendLine("Connecting...");
conn.Open();
// User table
string tableName = this.userTableTB.Text.Trim();
infoDlg.AppendLine(Environment.NewLine +
string.Format("Creating table \"{0}\"", tableName));
if (!this.TableExists(tableName, conn))
{
// Column names
string pk = this.userPrimaryKeyColTB.Text.Trim();
string unameCol = this.unameColTB.Text.Trim();
string hashMethodCol = this.hashMethodColTB.Text.Trim();
string passwdCol = this.passwdColTB.Text.Trim();
// Is the primary key the same as the username?
bool pkIsUserName =
unameCol.Equals(pk, StringComparison.CurrentCultureIgnoreCase);
StringBuilder sql = new StringBuilder();
sql.AppendFormat("CREATE TABLE {0} ( \r\n", tableName);
if (!pkIsUserName)
sql.AppendFormat(" {0} BIGINT auto_increment PRIMARY KEY, \r\n", pk);
sql.AppendFormat(" {0} VARCHAR(128) {1}, \r\n", unameCol, pkIsUserName ? "PRIMARY KEY" : "NOT NULL UNIQUE");
sql.AppendFormat(" {0} TEXT NOT NULL, \r\n", hashMethodCol);
sql.AppendFormat(" {0} TEXT \r\n", passwdCol);
sql.Append(")"); // End create table.
infoDlg.AppendLine("Executing SQL:");
infoDlg.AppendLine(sql.ToString());
using (MySqlCommand cmd = new MySqlCommand(sql.ToString(), conn))
{
cmd.ExecuteNonQuery();
infoDlg.AppendLine(string.Format("Table \"{0}\" created.", tableName));
}
}
else
{
infoDlg.AppendLine(
string.Format("WARNING: Table \"{0}\"already exists, skipping.", tableName));
}
// Group table
tableName = this.groupTableNameTB.Text.Trim();
infoDlg.AppendLine(Environment.NewLine +
string.Format("Creating table \"{0}\"", tableName));
if (!this.TableExists(tableName, conn))
{
// Column names
string pk = this.groupTablePrimaryKeyColTB.Text.Trim();
string groupNameCol = this.groupNameColTB.Text.Trim();
// Is the primary key the same as the group name?
bool pkIsGroupName =
groupNameCol.Equals(pk, StringComparison.CurrentCultureIgnoreCase);
StringBuilder sql = new StringBuilder();
sql.AppendFormat("CREATE TABLE {0} ( \r\n", tableName);
if (!pkIsGroupName)
sql.AppendFormat(" {0} BIGINT AUTO_INCREMENT PRIMARY KEY, \r\n", pk);
sql.AppendFormat(" {0} VARCHAR(128) {1} \r\n", groupNameCol, pkIsGroupName ? "PRIMARY KEY" : "NOT NULL UNIQUE");
sql.Append(")"); // End create table.
infoDlg.AppendLine("Executing SQL:");
infoDlg.AppendLine(sql.ToString());
using (MySqlCommand cmd = new MySqlCommand(sql.ToString(), conn))
{
cmd.ExecuteNonQuery();
infoDlg.AppendLine(string.Format("Table \"{0}\" created.", tableName));
}
}
else
{
infoDlg.AppendLine(
string.Format("WARNING: Table \"{0}\"already exists, skipping.", tableName));
}
// user-Group table
tableName = this.userGroupTableNameTB.Text.Trim();
infoDlg.AppendLine(Environment.NewLine +
string.Format("Creating table \"{0}\"", tableName));
if (!this.TableExists(tableName, conn))
{
// Column names
string userFK = this.userGroupUserFKColTB.Text.Trim();
string userPK = this.userPrimaryKeyColTB.Text.Trim();
string groupFK = this.userGroupGroupFKColTB.Text.Trim();
string groupPK = this.groupTablePrimaryKeyColTB.Text.Trim();
string groupNameCol = this.groupNameColTB.Text.Trim();
string unameCol = this.unameColTB.Text.Trim();
// Is the primary key the same as the group name?
bool pkIsGroupName =
groupNameCol.Equals(groupPK, StringComparison.CurrentCultureIgnoreCase);
bool pkIsUserName =
unameCol.Equals(userPK, StringComparison.CurrentCultureIgnoreCase);
StringBuilder sql = new StringBuilder();
sql.AppendFormat("CREATE TABLE {0} ( \r\n", tableName);
sql.AppendFormat(" {0} {1}, \r\n", groupFK, pkIsGroupName ? "VARCHAR(128)" : "BIGINT");
sql.AppendFormat(" {0} {1}, \r\n", userFK, pkIsUserName ? "VARCHAR(128)" : "BIGINT");
sql.AppendFormat(" PRIMARY KEY ({0}, {1}) \r\n", userFK, groupFK);
sql.Append(")"); // End create table.
infoDlg.AppendLine("Executing SQL:");
infoDlg.AppendLine(sql.ToString());
MySqlCommand cmd = new MySqlCommand(sql.ToString(), conn);
cmd.ExecuteNonQuery();
infoDlg.AppendLine(string.Format("Table \"{0}\" created.", tableName));
}
else
{
infoDlg.AppendLine(
string.Format("WARNING: Table \"{0}\"already exists, skipping.", tableName));
}
}
}
catch (MySqlException ex)
{
infoDlg.AppendLine(String.Format("ERROR: {0}", ex.Message));
}
finally
{
infoDlg.AppendLine(Environment.NewLine + "Finished.");
}
}
private void CheckTable(string tableName, string[] columnNames, TextBoxInfoDialog infoDlg, MySqlConnection conn)
{
// Check for existence of the table
bool tableExists = this.TableExists(tableName, conn);
if (tableExists)
{
m_logger.DebugFormat("Table \"{0}\" found.", tableName);
infoDlg.AppendLine(string.Format("Table \"{0}\" found.", tableName));
}
else
{
m_logger.DebugFormat("Table {0} not found.", tableName);
infoDlg.AppendLine(string.Format("ERROR: Table \"{0}\" not found.", tableName));
return;
}
if (tableExists)
{
// Get column names from DB
List<string> columnNamesFromDB = new List<string>();
string query = string.Format("DESCRIBE {0}", tableName);
MySqlCommand cmd = new MySqlCommand(query, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
string colName = rdr[0].ToString();
columnNamesFromDB.Add(colName);
}
rdr.Close();
// Check for appropriate columns.
bool ok = true;
foreach (string c in columnNames)
{
if (columnNamesFromDB.Contains(c, StringComparer.CurrentCultureIgnoreCase))
infoDlg.AppendLine(string.Format("Found column \"{0}\"", c));
else
{
ok = false;
infoDlg.AppendLine(string.Format("ERROR: Column \"{0}\" not found!", c));
}
}
if (!ok)
infoDlg.AppendLine(string.Format("ERROR: Table \"{0}\" schema looks incorrect.", tableName));
}
}
private void testBtn_Click(object sender, EventArgs e)
{
TextBoxInfoDialog infoDlg = new TextBoxInfoDialog();
infoDlg.Show();
infoDlg.AppendLine("Beginning test of MySQL database..." + Environment.NewLine);
MySqlConnection conn = null;
string tableName = this.userTableTB.Text.Trim();
try
{
string connStr = this.BuildConnectionString();
if (connStr == null) return;
infoDlg.AppendLine("Connection Status");
infoDlg.AppendLine("-------------------------------------");
conn = new MySqlConnection(connStr);
conn.Open();
infoDlg.AppendLine(string.Format("Connection to {0} successful.", this.hostTB.Text.Trim()));
// Variables to be used repeatedly below
MySqlCommand cmd = null;
MySqlDataReader rdr = null;
string query = "";
// Check SSL status
if (useSslCB.Checked)
{
string cipher = "";
query = "SHOW STATUS LIKE 'Ssl_cipher'";
cmd = new MySqlCommand(query, conn);
rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
rdr.Read();
cipher = rdr[1].ToString();
}
rdr.Close();
if (string.IsNullOrEmpty(cipher))
{
infoDlg.AppendLine( "Not using SSL." );
}
else
{
infoDlg.AppendLine("SSL enabled, using cipher: " + cipher);
}
}
else
{
infoDlg.AppendLine( "Not using SSL." );
}
infoDlg.AppendLine( Environment.NewLine + "User Table" );
infoDlg.AppendLine( "-------------------------------");
CheckTable(tableName,
new string[] { this.unameColTB.Text.Trim(), this.passwdColTB.Text.Trim(), this.hashMethodColTB.Text.Trim(), this.userPrimaryKeyColTB.Text.Trim() },
infoDlg, conn);
infoDlg.AppendLine(Environment.NewLine + "Group Table");
infoDlg.AppendLine("-------------------------------");
CheckTable(this.groupTableNameTB.Text.Trim(),
new string[] { this.groupNameColTB.Text.Trim(), this.groupTablePrimaryKeyColTB.Text.Trim() },
infoDlg, conn);
infoDlg.AppendLine(Environment.NewLine + "User-Group Table");
infoDlg.AppendLine("-------------------------------");
CheckTable(this.userGroupTableNameTB.Text.Trim(),
new string[] { this.userGroupUserFKColTB.Text.Trim(), this.userGroupGroupFKColTB.Text.Trim() },
infoDlg, conn);
}
catch (Exception ex)
{
if (ex is MySqlException)
{
MySqlException mysqlEx = ex as MySqlException;
infoDlg.AppendLine("MySQL ERROR: " + mysqlEx.Message);
}
else
{
infoDlg.AppendLine(string.Format("ERROR: A fatal error occured: {0}", ex));
}
}
finally
{
infoDlg.AppendLine(Environment.NewLine + "Closing connection.");
if (conn != null)
conn.Close();
infoDlg.AppendLine("Test complete.");
}
}
