private async void loginButton_Click(object sender, EventArgs e)
{
var agent = this.sqlAgentsComboBox.SelectedValue as SqlAgentBase;
if (agent == null)
{
MessageBox.Show("Agent type not specified.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (agent.IsFileBased && databaseTextBox.Text.IsNullOrWhiteSpace())
{
MessageBox.Show("Database file not specified.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
try
{
ISqlDictionary dictionary = null;
if (this.useSqlRepositoryCheckBox.Checked)
{
dictionary = new SqlDictionary(this.sqlRepositoryFolderTextBox.Text, true);
}
_agent = (SqlAgentBase)Activator.CreateInstance(agent.GetType(),
this.connectionStringTextBox.Text, this.databaseTextBox.Text, dictionary, null);
await _agent.TestConnectionAsync();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
this.DialogResult = DialogResult.OK;
this.Close();
}
/// <summary>
/// Gets the mysql native index schema definition.
/// </summary>
/// <param name="schema">the canonical field schema to translate</param>
/// <remarks>MySql creates an index for each foreign key.</remarks>
internal static string GetIndexDefinition(this DbFieldSchema schema, SqlAgentBase agent)
{
if (schema.IsNull())
{
throw new ArgumentNullException(nameof(schema));
}
if (agent.IsNull())
{
throw new ArgumentNullException(nameof(agent));
}
// no backing index for primary key
if (schema.IndexType == DbIndexType.None || schema.IndexType == DbIndexType.Primary ||
schema.IndexType == DbIndexType.ForeignPrimary)
{
return(string.Empty);
}
if (schema.IndexType == DbIndexType.Unique)
{
return(string.Format("UNIQUE KEY `{0}` (`{1}`)", schema.IndexName.ToConventional(agent),
schema.Name.ToConventional(agent)));
}
return(string.Format("KEY `{0}` (`{1}`)", schema.IndexName.ToConventional(agent),
schema.Name.ToConventional(agent)));
}
public CloneDatabaseProgressForm(SqlAgentBase sourceAgent, SqlAgentBase targetAgent, DbSchema schema)
{
InitializeComponent();
_sourceAgent = sourceAgent;
_targetAgent = targetAgent;
_schema = schema;
}
private void loginSQLServerToolStripMenuItem_Click(object sender, EventArgs e)
{
using (var childForm = new InitSqlAgentForm())
{
if (childForm.ShowDialog(this) == DialogResult.OK)
{
_agent = childForm.Agent;
}
}
}
/// <summary>
/// Gets the mysql native foreign key schema definition.
/// </summary>
/// <param name="schema">the canonical field schema to translate</param>
internal static string GetForeignKeyDefinition(this DbFieldSchema schema, SqlAgentBase agent)
{
if (schema.IsNull())
{
throw new ArgumentNullException(nameof(schema));
}
if (agent.IsNull())
{
throw new ArgumentNullException(nameof(agent));
}
if (!schema.IndexType.IsForeignKey())
{
return(string.Empty);
}
return(string.Format("CONSTRAINT `{0}` FOREIGN KEY `{0}`(`{1}`) REFERENCES `{2}`(`{3}`) ON DELETE {4} ON UPDATE {5}",
schema.IndexName.ToConventional(agent), schema.Name.ToConventional(agent),
schema.RefTable.ToConventional(agent), schema.RefField.ToConventional(agent),
schema.OnDeleteForeignKey.GetNativeActionType(), schema.OnUpdateForeignKey.GetNativeActionType()));
}
/// <summary>
/// Gets a list of statements required to drop the database table.
/// </summary>
/// <param name="schema">the table schema to drop</param>
/// <param name="dbName">the database to drop the table for</param>
internal static List <string> GetDropTableStatements(this DbTableSchema schema,
string dbName, SqlAgentBase agent)
{
if (schema.IsNull())
{
throw new ArgumentNullException(nameof(schema));
}
if (dbName.IsNullOrWhitespace())
{
throw new ArgumentNullException(nameof(dbName));
}
if (agent.IsNull())
{
throw new ArgumentNullException(nameof(agent));
}
return(new List <string>()
{
string.Format("DROP TABLE {0}.{1};",
dbName.Trim(), schema.Name.ToConventional(agent))
});
}
/// <summary>
/// Gets a list of statements required to drop the index.(also drops a foreign key if required)
/// </summary>
/// <param name="schema">the field schema containing the index to drop</param>
internal static List <string> GetDropIndexStatements(this DbFieldSchema schema, SqlAgentBase agent)
{
if (schema.IsNull())
{
throw new ArgumentNullException(nameof(schema));
}
if (agent.IsNull())
{
throw new ArgumentNullException(nameof(agent));
}
if (schema.IndexType != DbIndexType.Simple && schema.IndexType != DbIndexType.Unique)
{
return(new List <string>());
}
return(new List <string>()
{
string.Format("DROP INDEX {0};", schema.IndexName.ToConventional(agent))
});
}
/// <summary>
/// Gets a list of statements required to drop the database table field.
/// </summary>
/// <param name="schema">the field schema to drop</param>
/// <param name="dbName">the database to drop the field for</param>
/// <param name="tblName">the database table to drop the field for</param>
internal static List <string> GetDropFieldStatements(this DbFieldSchema schema,
string dbName, string tblName, SqlAgentBase agent)
{
if (schema.IsNull())
{
throw new ArgumentNullException(nameof(schema));
}
if (dbName.IsNullOrWhitespace())
{
throw new ArgumentNullException(nameof(dbName));
}
if (tblName.IsNullOrWhitespace())
{
throw new ArgumentNullException(nameof(tblName));
}
if (agent.IsNull())
{
throw new ArgumentNullException(nameof(agent));
}
return(new List <string>()
{
string.Format("ALTER TABLE `{0}`.`{1}` DROP COLUMN {2};",
dbName.Trim(), tblName.ToConventional(agent), schema.Name.ToConventional(agent))
});
}
private void cancelButton_Click(object sender, EventArgs e)
{
_agent = null;
this.DialogResult = DialogResult.Cancel;
this.Close();
}
/// <summary>
/// Gets a list of statements required to add a new database table field using the field schema specified.
/// (also fixes datetime defaults and adds index if required)
/// </summary>
/// <param name="schema">a canonical schema of the new database table field</param>
/// <param name="dbName">the database to add the field for</param>
/// <param name="tblName">the database table to add the field for</param>
internal static List <string> GetAddFieldStatements(this DbFieldSchema schema,
string dbName, string tblName, SqlAgentBase agent)
{
if (schema.IsNull())
{
throw new ArgumentNullException(nameof(schema));
}
if (dbName.IsNullOrWhitespace())
{
throw new ArgumentNullException(nameof(dbName));
}
if (tblName.IsNullOrWhitespace())
{
throw new ArgumentNullException(nameof(tblName));
}
if (agent.IsNull())
{
throw new ArgumentNullException(nameof(agent));
}
var result = new List <string>()
{
string.Format("ALTER TABLE `{0}`.`{1}` ADD COLUMN {2};",
dbName.Trim(), tblName.ToConventional(agent), schema.GetFieldDefinition(agent))
};
if (schema.NotNull && (schema.DataType == DbDataType.Date))
{
result.Add(string.Format("UPDATE `{0}`.`{1}` SET {2}='{3}';",
dbName.Trim(), tblName.ToConventional(agent), schema.Name.ToConventional(agent),
DateTime.UtcNow.ToString("yyyy'-'MM'-'dd")));
}
if (schema.NotNull && (schema.DataType == DbDataType.Time ||
schema.DataType == DbDataType.DateTime || schema.DataType == DbDataType.TimeStamp))
{
result.Add(string.Format("UPDATE `{0}`.`{1}` SET {2}='{3}';",
dbName.Trim(), tblName.ToConventional(agent), schema.Name.ToConventional(agent),
DateTime.UtcNow.ToString("yyyy'-'MM'-'dd HH':'mm':'ss")));
}
result.AddRange(schema.GetAddIndexStatements(dbName, tblName, agent));
return(result);
}
/// <summary>
/// Gets a list of statements required to alter the database table field schema
/// to match the specified gauge schema.(does not fixes indexes)
/// </summary>
/// <param name="schema">the gauge field schema to apply</param>
/// <param name="dbName">the database to alter the field for</param>
/// <param name="tblName">the database table to alter the field for</param>
internal static List <string> GetAlterFieldStatements(this DbFieldSchema schema,
string dbName, string tblName, SqlAgentBase agent)
{
if (schema.IsNull())
{
throw new ArgumentNullException(nameof(schema));
}
if (dbName.IsNullOrWhitespace())
{
throw new ArgumentNullException(nameof(dbName));
}
if (tblName.IsNullOrWhitespace())
{
throw new ArgumentNullException(nameof(tblName));
}
if (agent.IsNull())
{
throw new ArgumentNullException(nameof(agent));
}
return(new List <string>()
{
string.Format("ALTER TABLE `{0}`.`{1}` MODIFY COLUMN {2};",
dbName.Trim(), tblName.ToConventional(agent),
schema.GetFieldDefinition(agent).Replace("PRIMARY KEY", ""))
});
}
private async void CreateButton_Click(object sender, EventArgs e)
{
var agentPrototype = this.sqlAgentsComboBox.SelectedValue as SqlAgentBase;
if (agentPrototype == null)
{
MessageBox.Show("Agent type not specified.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (databaseTextBox.Text.IsNullOrWhiteSpace())
{
if (agentPrototype.IsFileBased)
{
MessageBox.Show("Database file not specified.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
MessageBox.Show("Database name not specified.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return;
}
if (this.schemaPathTextBox.Text.IsNullOrWhiteSpace())
{
MessageBox.Show("Database schema path not specified.", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
SqlAgentBase agent = null;
var currentCursor = this.Cursor;
this.Cursor = Cursors.WaitCursor;
this.CreateButton.Enabled = false;
try
{
agent = (SqlAgentBase)Activator.CreateInstance(agentPrototype.GetType(),
this.connectionStringTextBox.Text, this.databaseTextBox.Text, null, null);
var schema = new DbSchema();
if (this.schemaInFileButton.Checked)
{
schema.LoadXmlFile(this.schemaPathTextBox.Text);
}
else
{
schema.LoadXmlFolder(this.schemaPathTextBox.Text);
}
await Task.Run(async() => await agent.GetDefaultSchemaManager().CreateDatabaseAsync(schema));
}
catch (Exception ex)
{
this.Cursor = currentCursor;
this.CreateButton.Enabled = true;
MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
this.Cursor = currentCursor;
this.CreateButton.Enabled = true;
}
/// <summary>
/// Gets the mysql native field schema definition.
/// </summary>
/// <param name="schema">the canonical field schema to translate</param>
internal static string GetFieldDefinition(this DbFieldSchema schema, SqlAgentBase agent)
{
if (schema.IsNull())
{
throw new ArgumentNullException(nameof(schema));
}
if (agent.IsNull())
{
throw new ArgumentNullException(nameof(agent));
}
var result = string.Format("{0} {1}", schema.Name.ToConventional(agent), schema.GetNativeDataType());
if (schema.DataType == DbDataType.Char || schema.DataType == DbDataType.VarChar)
{
result = string.Format("{0}({1})", result, schema.Length.ToString());
}
if (schema.DataType == DbDataType.Decimal)
{
result = string.Format("{0}({1}, {2})", result, (schema.Length + 15).ToString(), schema.Length.ToString());
}
if (schema.DataType == DbDataType.Enum)
{
var enumValues = schema.EnumValues.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries)
.Select(v => string.Format("'{0}'", v.ToConventional(agent)));
result = string.Format("{0}({1})", result, string.Join(", ", enumValues));
}
if (schema.DataType == DbDataType.Char || schema.DataType == DbDataType.Enum ||
schema.DataType == DbDataType.Text || schema.DataType == DbDataType.TextLong ||
schema.DataType == DbDataType.TextMedium || schema.DataType == DbDataType.VarChar)
{
if (schema.CollationType == DbFieldCollationType.ASCII_Binary)
{
result = result + " CHARACTER SET 'ascii' COLLATE 'ascii_bin'";
}
else if (schema.CollationType == DbFieldCollationType.ASCII_CaseInsensitive)
{
result = result + " CHARACTER SET 'ascii' COLLATE 'ascii_general_ci'";
}
}
if (schema.Unsigned && (schema.DataType.IsDbDataTypeInteger() ||
schema.DataType.IsDbDataTypeFloat() || schema.DataType == DbDataType.Decimal))
{
result = result + " UNSIGNED";
}
if (schema.NotNull)
{
result = result + " NOT NULL";
}
if (schema.Autoincrement && schema.DataType.IsDbDataTypeInteger())
{
result = result + " AUTO_INCREMENT";
}
if (schema.IndexType.IsPrimaryKey())
{
result = result + " PRIMARY KEY";
}
return(result);
}
/// <summary>
/// Gets a list of statements required to add a new database table using the schema specified.
/// </summary>
/// <param name="schema">a canonical schema of the new database table</param>
/// <param name="dbName">the database to add the table for</param>
/// <param name="engine">the SQL engine to use for the table</param>
/// <param name="charset">the default charset for the table</param>
internal static List <string> GetCreateTableStatements(this DbTableSchema schema, string dbName,
string engine, string charset, SqlAgentBase agent)
{
if (schema.IsNull())
{
throw new ArgumentNullException(nameof(schema));
}
if (dbName.IsNullOrWhitespace())
{
throw new ArgumentNullException(nameof(dbName));
}
if (agent.IsNull())
{
throw new ArgumentNullException(nameof(agent));
}
var lines = schema.Fields.Select(field => field.GetFieldDefinition(agent)).ToList();
lines.AddRange(from field in schema.Fields
where field.IndexType == DbIndexType.Simple ||
field.IndexType == DbIndexType.Unique ||
field.IndexType == DbIndexType.ForeignKey
select field.GetIndexDefinition(agent));
lines.AddRange(from field in schema.Fields
where field.IndexType.IsForeignKey()
select field.GetForeignKeyDefinition(agent));
return(new List <string>()
{
string.Format("CREATE TABLE {0}.{1}({2}) ENGINE={3} DEFAULT CHARSET={4};",
dbName.Trim(), schema.Name.ToConventional(agent), string.Join(", ", lines.ToArray()),
engine.Trim(), charset.Trim())
});
}
/// <summary>
/// Gets a list of statements required to add a new index using the field schema specified.
/// (also adds a foreign key if required)
/// </summary>
/// <param name="schema">a canonical database table field schema to apply</param>
/// <param name="dbName">the database to add the index for</param>
/// <param name="tblName">the database table to add the index for</param>
internal static List <string> GetAddIndexStatements(this DbFieldSchema schema,
string dbName, string tblName, SqlAgentBase agent)
{
if (schema.IsNull())
{
throw new ArgumentNullException(nameof(schema));
}
if (dbName.IsNullOrWhitespace())
{
throw new ArgumentNullException(nameof(dbName));
}
if (tblName.IsNullOrWhitespace())
{
throw new ArgumentNullException(nameof(tblName));
}
if (agent.IsNull())
{
throw new ArgumentNullException(nameof(agent));
}
if (schema.IndexType == DbIndexType.None || schema.IndexType == DbIndexType.Primary)
{
return(new List <string>());
}
string result;
if (schema.IndexType.IsForeignKey())
{
result = string.Format("ALTER TABLE `{0}`.`{1}` ADD CONSTRAINT `{2}` FOREIGN KEY `{2}`(`{3}`) REFERENCES `{4}`(`{5}`) ON DELETE {6} ON UPDATE {7};",
dbName.Trim(), tblName.ToConventional(agent), schema.IndexName.ToConventional(agent),
schema.Name.ToConventional(agent), schema.RefTable.ToConventional(agent),
schema.RefField.ToConventional(agent), schema.OnDeleteForeignKey.GetNativeActionType(),
schema.OnUpdateForeignKey.GetNativeActionType());
}
else
{
var uniqueStr = "";
if (schema.IndexType == DbIndexType.Unique)
{
uniqueStr = "UNIQUE ";
}
result = string.Format("CREATE {0}INDEX `{1}` ON `{2}`.`{3}` (`{4}`);",
uniqueStr, schema.IndexName.ToConventional(agent), dbName.Trim(), tblName.ToConventional(agent),
schema.Name.ToConventional(agent));
}
return(new List <string>()
{
result
});
}
/// <summary>
/// Gets a list of statements required to add a new index using the field schema specified.
/// (also adds a foreign key if required)
/// </summary>
/// <param name="schema">a canonical database table field schema to apply</param>
/// <param name="tblName">the database table to add the index for</param>
internal static List <string> GetAddIndexStatements(this DbFieldSchema schema, string tblName, SqlAgentBase agent)
{
if (schema.IsNull())
{
throw new ArgumentNullException(nameof(schema));
}
if (tblName.IsNullOrWhiteSpace())
{
throw new ArgumentNullException(nameof(tblName));
}
if (agent.IsNull())
{
throw new ArgumentNullException(nameof(agent));
}
// No backing index for foreign key in sqlite
if (schema.IndexType == DbIndexType.None || schema.IndexType == DbIndexType.Primary ||
schema.IndexType == DbIndexType.ForeignKey || schema.IndexType == DbIndexType.ForeignPrimary)
{
return(new List <string>());
}
string result;
if (schema.IndexType == DbIndexType.Simple)
{
result = string.Format("CREATE INDEX {0} ON {1}({2});",
schema.IndexName.ToConventional(agent), tblName.ToConventional(agent),
schema.Name.ToConventional(agent));
}
else
{
result = string.Format("CREATE UNIQUE INDEX {0} ON {1}({2});",
schema.IndexName.ToConventional(agent), tblName.ToConventional(agent), schema.Name.ToConventional(agent));
}
return(new List <string>()
{
result
});
}
/// <summary>
/// Gets a list of statements required to add a new database table field using the field schema specified.
/// (also fixes datetime defaults and adds index if required)
/// </summary>
/// <param name="schema">a canonical schema of the new database table field</param>
/// <param name="tblName">the database table to add the field for</param>
internal static List <string> GetAddFieldStatements(this DbFieldSchema schema, string tblName, SqlAgentBase agent)
{
if (schema.IsNull())
{
throw new ArgumentNullException(nameof(schema));
}
if (agent.IsNull())
{
throw new ArgumentNullException(nameof(agent));
}
if (tblName.IsNullOrWhiteSpace())
{
throw new ArgumentNullException(nameof(tblName));
}
var alterStatement = string.Format("ALTER TABLE {0} ADD COLUMN {1};",
tblName.ToConventional(agent), schema.GetFieldDefinition(true, agent));
var result = new List <string>()
{
alterStatement
};
result.AddRange(schema.GetAddIndexStatements(tblName, agent));
return(result);
}
/// <summary>
/// Gets the SQLite native field schema definition.
/// </summary>
/// <param name="schema">the canonical field schema to translate</param>
/// <param name="addSafe">whether the definition should be safe for add field,
/// i.e. dto add default value for not null fields</param>
internal static string GetFieldDefinition(this DbFieldSchema schema, bool addSafe, SqlAgentBase agent)
{
if (schema.IsNull())
{
throw new ArgumentNullException(nameof(schema));
}
if (agent.IsNull())
{
throw new ArgumentNullException(nameof(agent));
}
if (schema.Autoincrement)
{
schema.DataType = DbDataType.Integer;
}
var result = string.Format("{0} {1}", schema.Name.ToConventional(agent), schema.GetNativeDataType());
if ((schema.DataType == DbDataType.Char || schema.DataType == DbDataType.VarChar) &&
schema.Length > 0)
{
result = string.Format("{0}({1})", result, schema.Length.ToString(CultureInfo.InvariantCulture));
}
if (schema.NotNull)
{
if (addSafe)
{
result = string.Format("{0} DEFAULT {1} NOT NULL", result,
GetDefaultValueForFieldType(schema.DataType, schema.EnumValues));
}
else
{
result = string.Format("{0} NOT NULL", result);
}
}
if (schema.IndexType == DbIndexType.Primary)
{
if (schema.Autoincrement)
{
result = result + " PRIMARY KEY AUTOINCREMENT";
}
else
{
result = result + " PRIMARY KEY";
}
}
if (schema.IndexType == DbIndexType.ForeignKey || schema.IndexType == DbIndexType.ForeignPrimary)
{
result = result + string.Format(" REFERENCES {0}({1}) ON UPDATE {2} ON DELETE {3}",
schema.RefTable.ToConventional(agent), schema.RefField.ToConventional(agent),
schema.OnUpdateForeignKey.GetNativeActionType(),
schema.OnDeleteForeignKey.GetNativeActionType());
}
return(result);
}
/// <summary>
/// Gets a list of statements required to drop the index.(also drops a foreign key if required)
/// </summary>
/// <param name="schema">the field schema containing the index to drop</param>
/// <param name="dbName">the database to drop the index for</param>
/// <param name="tblName">the database table to drop the index for</param>
internal static List <string> GetDropIndexStatements(this DbFieldSchema schema,
string dbName, string tblName, SqlAgentBase agent)
{
if (schema.IsNull())
{
throw new ArgumentNullException(nameof(schema));
}
if (dbName.IsNullOrWhitespace())
{
throw new ArgumentNullException(nameof(dbName));
}
if (tblName.IsNullOrWhitespace())
{
throw new ArgumentNullException(nameof(tblName));
}
if (agent.IsNull())
{
throw new ArgumentNullException(nameof(agent));
}
if (schema.IndexType == DbIndexType.None || schema.IndexType == DbIndexType.Primary)
{
return(new List <string>());
}
string result;
if (schema.IndexType == DbIndexType.ForeignKey || schema.IndexType == DbIndexType.ForeignPrimary)
{
result = string.Format("ALTER TABLE `{0}`.`{1}` DROP FOREIGN KEY `{2}`;",
dbName.Trim(), tblName.ToConventional(agent), schema.IndexName.ToConventional(agent));
}
else
{
result = string.Format("DROP INDEX `{0}` ON `{1}`.`{2}`;",
schema.IndexName.ToConventional(agent), dbName.Trim(), tblName.ToConventional(agent));
}
return(new List <string>()
{
result
});
}
/// <summary>
/// Gets a list of statements required to add a new database table using the schema specified.
/// </summary>
/// <param name="schema">a canonical schema of the new database table</param>
internal static List <string> GetCreateTableStatements(this DbTableSchema schema, SqlAgentBase agent)
{
if (schema.IsNull())
{
throw new ArgumentNullException(nameof(schema));
}
if (agent.IsNull())
{
throw new ArgumentNullException(nameof(agent));
}
var lines = schema.Fields.Select(field => field.GetFieldDefinition(false, agent)).ToArray();
var result = new List <string>()
{
string.Format("CREATE TABLE {0}({1});",
schema.Name.ToConventional(agent), string.Join(", ", lines))
};
foreach (var field in schema.Fields)
{
if (field.IndexType == DbIndexType.Simple || field.IndexType == DbIndexType.Unique)
{
result.AddRange(field.GetAddIndexStatements(schema.Name, agent));
}
}
return(result);
}
public SqlBrowserForm(SqlAgentBase agent)
{
InitializeComponent();
_agent = agent;
}
public DbSchemaErrorsForm(SqlAgentBase agent)
{
InitializeComponent();
_agent = agent;
}
