/// <summary>
/// Gets a schema name that is unique among the schemas in the current connection.
/// </summary>
/// <param name="connection">MySQL Workbench connection to a MySQL server instance selected by users.</param>
/// <param name="proposedName">The proposed name for a new schema.</param>
/// <returns>A unique schema name.</returns>
public static string GetSchemaNameAvoidingDuplicates(this MySqlWorkbenchConnection connection, string proposedName)
{
if (connection == null)
{
return(null);
}
if (string.IsNullOrEmpty(proposedName))
{
proposedName = DEFAULT_NEW_SCHEMA_NAME;
}
var schemas = connection.GetSchemaInformation(SchemaInformationType.Databases, true, null);
if (schemas == null || schemas.Rows.Count == 0)
{
return(proposedName);
}
var suffix = 2;
var finalName = proposedName;
while (schemas.Rows.Cast <DataRow>().Any(schemaRow => string.Equals(schemaRow["DATABASE_NAME"].ToString(), finalName, StringComparison.InvariantCultureIgnoreCase)))
{
finalName = proposedName + suffix++;
}
return(finalName);
}
/// <summary>
/// Checks if an index with the given name exists in the given schema and table.
/// </summary>
/// <param name="connection">MySQL Workbench connection to a MySQL server instance selected by users.</param>
/// <param name="schemaName">Name of the database schema where the index resides.</param>
/// <param name="tableName">Name of the database table where the index resides.</param>
/// <param name="indexName">Name of the index to look for.</param>
/// <returns><c>true</c> if the index exists, <c>false</c> otherwise.</returns>
public static bool IndexExistsInSchema(this MySqlWorkbenchConnection connection, string schemaName, string tableName, string indexName)
{
if (string.IsNullOrEmpty(indexName))
{
return(false);
}
schemaName = string.IsNullOrEmpty(schemaName) ? connection.Schema : schemaName;
var dt = connection.GetSchemaInformation(SchemaInformationType.Indexes, true, null, schemaName, tableName, indexName);
return(dt != null && dt.Rows.Count > 0);
}
/// <summary>
/// Returns a table containing schema information for columns contained in a MySQL table with the given name.
/// </summary>
/// <param name="connection">MySQL Workbench connection to a MySQL server instance selected by users.</param>
/// <param name="schemaName">The schema the MySQL table belongs to.</param>
/// <param name="tableName">The name of a MySQL table.</param>
/// <param name="beautifyDataTypes">Flag indicating whether the data types are camel cased as shown in the Export Data data type combo box.</param>
/// <returns>A table containing schema information for columns contained in a MySQL table with the given name.</returns>
public static MySqlColumnsInformationTable GetColumnsInformationTable(this MySqlWorkbenchConnection connection, string schemaName, string tableName, bool beautifyDataTypes = false)
{
if (connection == null)
{
return(null);
}
schemaName = string.IsNullOrEmpty(schemaName) ? connection.Schema : schemaName;
var schemaTable = connection.GetSchemaInformation(SchemaInformationType.ColumnsFull, true, null, schemaName, tableName);
if (schemaTable == null)
{
return(null);
}
var columnsInfoTable = new MySqlColumnsInformationTable(schemaTable.TableName);
foreach (DataRow row in schemaTable.Rows)
{
var dataType = row["COLUMN_TYPE"].ToString();
if (beautifyDataTypes)
{
var mySqlDataType = new MySqlDataType(dataType, false);
dataType = mySqlDataType.FullType;
}
var infoRow = columnsInfoTable.NewRow();
infoRow["Name"] = row["COLUMN_NAME"];
infoRow["Type"] = dataType;
infoRow["Null"] = row["IS_NULLABLE"];
infoRow["Key"] = row["COLUMN_KEY"];
infoRow["Default"] = row["COLUMN_DEFAULT"];
infoRow["CharSet"] = row["CHARACTER_SET_NAME"];
infoRow["Collation"] = row["COLLATION_NAME"];
infoRow["Extra"] = row["EXTRA"];
columnsInfoTable.Rows.Add(infoRow);
}
return(columnsInfoTable);
}
/// <summary>
/// Fetches all schema names from the current connection and loads them in the <see cref="SchemasList"/> tree.
/// </summary>
/// <returns><c>true</c> if schemas were loaded successfully, <c>false</c> otherwise.</returns>
private bool LoadSchemas()
{
if (SchemasList.HeaderNodes.Count < 2)
{
return(false);
}
Cursor = Cursors.WaitCursor;
try
{
// Avoids flickering of schemas list while adding the items to it.
SchemasList.BeginUpdate();
LoadedSchemas.ForEach(schema => schema.Dispose());
LoadedSchemas.Clear();
foreach (TreeNode node in SchemasList.Nodes)
{
node.Nodes.Clear();
}
var databases = _wbConnection.GetSchemaInformation(SchemaInformationType.Databases, false, null);
foreach (DataRow row in databases.Rows)
{
var schemaName = row["DATABASE_NAME"].ToString();
// If the user has specified a filter then check it
if (!string.IsNullOrEmpty(_filter) && !schemaName.ToUpper().Contains(_filter))
{
continue;
}
// Create the DbSchema and MySqlListViewNode objects
var schemaObject = new DbSchema(_wbConnection, schemaName,
row["DEFAULT_CHARACTER_SET_NAME"].ToString(),
row["DEFAULT_COLLATION_NAME"].ToString(),
DisplaySchemaCollationsToolStripMenuItem.Checked);
var lcSchemaName = schemaName.ToLowerInvariant();
var headerNode = SchemasList.HeaderNodes[MySqlWorkbenchConnection.SystemSchemaNames.Contains(lcSchemaName) ? 1 : 0];
LoadedSchemas.Add(schemaObject);
var node = SchemasList.AddDbObjectNode(headerNode, schemaObject);
node.ImageIndex = DisplaySchemaCollationsToolStripMenuItem.Checked ? 1 : 0;
}
if (SchemasList.Nodes[0].GetNodeCount(true) > 0)
{
SchemasList.Nodes[0].Expand();
}
// Avoids flickering of schemas list while adding the items to it.
SchemasList.EndUpdate();
return(true);
}
catch (Exception ex)
{
Logger.LogException(ex, true, Resources.SchemasLoadingErrorTitle);
return(false);
}
finally
{
Cursor = Cursors.Default;
}
}