static void Work(Argument arguments)
{
try
{
DBDiff.Schema.SQLServer.Generates.Model.Database origen;
DBDiff.Schema.SQLServer.Generates.Model.Database destino;
if (TestConnection(arguments.ConnectionString1, arguments.ConnectionString2))
{
Generate sql = new Generate();
SqlOption opt = new SqlOption(true);
opt.Ignore.FilterSchema = false;
opt.Ignore.FilterUsers = false;
opt.Ignore.FilterTableFileGroup = false;
sql.Options = opt;
sql.ConnectionString = arguments.ConnectionString1;
System.Console.WriteLine("Reading first database...");
//sql.Options = SqlFilter;
origen = sql.Process();
sql.ConnectionString = arguments.ConnectionString2;
System.Console.WriteLine("Reading second database...");
destino = sql.Process();
System.Console.WriteLine("Comparing databases schemas...");
origen = Generate.Compare(origen, destino);
System.Console.WriteLine("Generating SQL file...");
SaveFile(arguments.OutputFile, origen.ToSql());
}
}
catch (Exception ex)
{
System.Console.WriteLine(ex.Message);
}
}
private void Form1_Load(object sender, EventArgs e)
{
ShowSQL2005();
txtNewObject.ConfigurationManager.Language = "mssql";
txtNewObject.IsReadOnly = false;
txtNewObject.Styles.LineNumber.BackColor = Color.White;
txtNewObject.Styles.LineNumber.IsVisible = false;
txtOldObject.ConfigurationManager.Language = "mssql";
txtOldObject.IsReadOnly = false;
txtOldObject.Styles.LineNumber.BackColor = Color.White;
txtOldObject.Styles.LineNumber.IsVisible = false;
txtDiff.ConfigurationManager.Language = "mssql";
txtDiff.IsReadOnly = false;
txtDiff.Styles.LineNumber.IsVisible = false;
txtDiff.Margins[0].Width = 20;
Project LastConfiguration = Project.GetLastConfiguration();
if (LastConfiguration != null)
{
ActiveProject = LastConfiguration;
mySqlConnectFront1.ConnectionString = LastConfiguration.ConnectionStringSource;
mySqlConnectFront2.ConnectionString = LastConfiguration.ConnectionStringDestination;
SqlFilter = LastConfiguration.Options;
}
txtSyncScript.Text = "";
}
private static string GetSQL(SqlOption options)
{
string filter = "";
string sql = "";
sql += "SELECT O.name, O.type, M.object_id, OBJECT_DEFINITION(M.object_id) AS Text FROM sys.sql_modules M ";
sql += "INNER JOIN sys.objects O ON O.object_id = M.object_id ";
sql += "WHERE ";
if (options.Ignore.FilterStoreProcedure)
{
filter += "O.type = 'P' OR ";
}
if (options.Ignore.FilterView)
{
filter += "O.type = 'V' OR ";
}
if (options.Ignore.FilterTrigger)
{
filter += "O.type = 'TR' OR ";
}
if (options.Ignore.FilterFunction)
{
filter += "O.type IN ('IF','FN','TF') OR ";
}
filter = filter.Substring(0, filter.Length - 4);
return(sql + filter);
}
private static string GetSQL(DatabaseInfo.VersionTypeEnum version, SqlOption options)
{
string sql = "";
sql += "SELECT T.object_id, O.type AS ObjectType, ISNULL(CONVERT(varchar,AM.execute_as_principal_id),'CALLER') as ExecuteAs, AF.name AS assembly_name, AM.assembly_class, AM.assembly_id, AM.assembly_method, T.type, CAST(ISNULL(tei.object_id,0) AS bit) AS IsInsert, CAST(ISNULL(teu.object_id,0) AS bit) AS IsUpdate, CAST(ISNULL(ted.object_id,0) AS bit) AS IsDelete, T.parent_id, S.name AS Owner,T.name,is_disabled,is_not_for_replication,is_instead_of_trigger ";
sql += "FROM sys.triggers T ";
sql += "INNER JOIN sys.objects O ON O.object_id = T.parent_id ";
sql += "INNER JOIN sys.schemas S ON S.schema_id = O.schema_id ";
sql += "LEFT JOIN sys.trigger_events AS tei ON tei.object_id = T.object_id and tei.type=1 ";
sql += "LEFT JOIN sys.trigger_events AS teu ON teu.object_id = T.object_id and teu.type=2 ";
sql += "LEFT JOIN sys.trigger_events AS ted ON ted.object_id = T.object_id and ted.type=3 ";
if (version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10)
{
sql += ",(SELECT null as execute_as_principal_id, null as assembly_class, null as assembly_id, null as assembly_method) AS AM,";
sql += "(SELECT null AS name) AS AF";
}
else
{
sql += "LEFT JOIN sys.assembly_modules AM ON AM.object_id = T.object_id ";
sql += "LEFT JOIN sys.assemblies AF ON AF.assembly_id = AM.assembly_id";
}
sql += " ORDER BY T.parent_id";
return(sql);
}
public IOption GetDefaultProjectOptions()
{
if (Option == null)
{
Option = new Options.SqlOption();
}
return(Option);
}
private static string SerializeOptions(SqlOption options)
{
if (options == null)
{
return(string.Empty);
}
//Escape single quote in JSON due to SQLite standard
return(JsonConvert.SerializeObject(options).Replace("'", "''"));
}
private static string GetSQL(DatabaseInfo.VersionTypeEnum version, SqlOption options)
{
if (version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10)
{
return(SQLQueries.SQLQueryFactory.Get("DBDiff.Schema.SQLServer.Generates.SQLQueries.GetTriggers", version));
}
else
{
return(SQLQueries.SQLQueryFactory.Get("DBDiff.Schema.SQLServer.Generates.SQLQueries.GetTriggers"));
}
}
private static string GetSQL(DatabaseInfo.SQLServerVersion version, SqlOption options)
{
if (version == DatabaseInfo.SQLServerVersion.SQLServerAzure10)
{
return(SQLQueries.SQLQueryFactory.Get("GetTriggers", version));
}
else
{
return(SQLQueries.SQLQueryFactory.Get("GetTriggers"));
}
}
public AddItem(SqlOption sqlOption, int Index)
{
InitializeComponent();
FillCombo();
this.sqlOption = sqlOption;
indexFilter = Index;
if (indexFilter != -1)
{
txtFilter.Text = sqlOption.Filters.Items[indexFilter].Filter;
cboObjects.SelectedValue = sqlOption.Filters.Items[indexFilter].Type.ToString();
}
}
public void SetProjectOptions(IOption option)
{
if (option == null)
{
throw new ArgumentNullException(nameof(option));
}
else if (!(option is SqlOption))
{
throw new NotSupportedException($"This project handler only supports {nameof(SqlOption)} options. {option.GetType().Name} not supported");
}
Option = option as SqlOption;
}
public AddExclusionPatternForm(SqlOption sqlOption, int Index)
{
InitializeComponent();
PopulateObjectTypeDropDownList();
this.sqlOption = sqlOption;
indexFilter = Index;
if (indexFilter != -1)
{
txtFilter.Text = sqlOption.Filters.Items[indexFilter].FilterPattern;
cboObjects.SelectedValue = sqlOption.Filters.Items[indexFilter].ObjectType;
}
}
private static string GetSQL(SqlOption options)
{
var filterQuery = SQLQueries.SQLQueryFactory.Get("GetTextObjectsQuery");
string filter = "";
if (options.Ignore.FilterStoredProcedure)
{
filter += "O.type = 'P' OR ";
}
if (options.Ignore.FilterView)
{
filter += "O.type = 'V' OR ";
}
if (options.Ignore.FilterTrigger)
{
filter += "O.type = 'TR' OR ";
}
if (options.Ignore.FilterFunction)
{
filter += "O.type IN ('IF','FN','TF') OR ";
}
filter = filter.Substring(0, filter.Length - 4);
return(filterQuery.Replace("{FILTER}", filter));
}
/// <summary>
/// Constructor de la clase.
/// </summary>
/// <param name="connectioString">Connection string de la base</param>
public GenerateDatabase(string connectioString, SqlOption filter)
{
this.connectioString = connectioString;
this.objectFilter = filter;
}
public new void Load(SqlOption valueOption)
{
this.option = valueOption;
txtBlob.Text = option.Defaults.DefaultBlobValue;
txtDate.Text = option.Defaults.DefaultDateValue;
txtDefaultInteger.Text = option.Defaults.DefaultIntegerValue;
txtDefaultReal.Text = option.Defaults.DefaultRealValue;
txtNText.Text = option.Defaults.DefaultNTextValue;
txtText.Text = option.Defaults.DefaultTextValue;
txtVariant.Text = option.Defaults.DefaultVariantValue;
txtTime.Text = option.Defaults.DefaultTime;
txtXML.Text = option.Defaults.DefaultXml;
chkCompAssemblys.Checked = option.Ignore.FilterAssemblies;
chkCompCLRFunctions.Checked = option.Ignore.FilterCLRFunction;
chkCompCLRStore.Checked = option.Ignore.FilterCLRStoreProcedure;
chkCompCLRTrigger.Checked = option.Ignore.FilterCLRTrigger;
chkCompCLRUDT.Checked = option.Ignore.FilterCLRUDT;
chkConstraints.Checked = option.Ignore.FilterConstraint;
chkConstraintsPK.Checked = option.Ignore.FilterConstraintPK;
chkConstraintsFK.Checked = option.Ignore.FilterConstraintFK;
chkConstraintsUK.Checked = option.Ignore.FilterConstraintUK;
chkConstraintsCheck.Checked = option.Ignore.FilterConstraintCheck;
chkCompExtendedProperties.Checked = option.Ignore.FilterExtendedPropertys;
chkCompFunciones.Checked = option.Ignore.FilterFunction;
chkIndex.Checked = option.Ignore.FilterIndex;
chkIndexFillFactor.Checked = option.Ignore.FilterIndexFillFactor;
chkIndexIncludeColumns.Checked = option.Ignore.FilterIndexIncludeColumns;
chkIndexFilter.Checked = option.Ignore.FilterIndexFilter;
chkFullText.Checked = option.Ignore.FilterFullText;
chkFullTextPath.Checked = option.Ignore.FilterFullTextPath;
chkCompSchemas.Checked = option.Ignore.FilterSchema;
chkCompStoreProcedure.Checked = option.Ignore.FilterStoreProcedure;
chkTableOption.Checked = option.Ignore.FilterTableOption;
chkTables.Checked = option.Ignore.FilterTable;
chkTablesColumnIdentity.Checked = option.Ignore.FilterColumnIdentity;
chkTablesColumnCollation.Checked = option.Ignore.FilterColumnCollation;
chkTableLockEscalation.Checked = option.Ignore.FilterTableLockEscalation;
chkTableChangeTracking.Checked = option.Ignore.FilterTableChangeTracking;
chkTablesColumnOrder.Checked = option.Ignore.FilterColumnOrder;
chkIgnoreNotForReplication.Checked = option.Ignore.FilterNotForReplication;
chkCompTriggersDDL.Checked = option.Ignore.FilterDDLTriggers;
chkCompTriggers.Checked = option.Ignore.FilterTrigger;
chkCompUDT.Checked = option.Ignore.FilterUserDataType;
chkCompVistas.Checked = option.Ignore.FilterView;
chkCompXMLSchemas.Checked = option.Ignore.FilterXMLSchema;
chkFileGroups.Checked = option.Ignore.FilterTableFileGroup;
chkCompUsers.Checked = option.Ignore.FilterUsers;
chkCompRoles.Checked = option.Ignore.FilterRoles;
chkCompRules.Checked = option.Ignore.FilterRules;
if (option.Script.AlterObjectOnSchemaBinding)
{
optScriptSchemaBindingAlter.Checked = true;
}
else
{
optScriptSchemaDrop.Checked = true;
}
if (option.Comparison.CaseSensityType == SqlOptionComparison.CaseSensityOptions.Automatic)
{
rdoCaseAutomatic.Checked = true;
}
if (option.Comparison.CaseSensityType == SqlOptionComparison.CaseSensityOptions.CaseInsensity)
{
rdoCaseInsensitive.Checked = true;
}
if (option.Comparison.CaseSensityType == SqlOptionComparison.CaseSensityOptions.CaseSensity)
{
rdoCaseSensitive.Checked = true;
}
if (option.Comparison.CaseSensityInCode == SqlOptionComparison.CaseSensityOptions.CaseInsensity)
{
rdoCaseInsensityInCode.Checked = true;
}
if (option.Comparison.CaseSensityInCode == SqlOptionComparison.CaseSensityOptions.CaseSensity)
{
rdoCaseSensityInCode.Checked = true;
}
chkIgnoreWhiteSpaceInCode.Checked = option.Comparison.IgnoreWhiteSpacesInCode;
LoadFilters();
}
/// <summary>
/// Genera el schema de la base de datos seleccionada y devuelve un objeto Database.
/// </summary>
public Database Process(SqlOption filters)
{
string error = "";
try
{
Database databaseSchema = new Database();
//tables.OnTableProgress += new Progress.ProgressHandler(tables_OnTableProgress);
databaseSchema.Options = filters;
databaseSchema.Name = this.Name;
databaseSchema.Info = (new GenerateDatabase(connectionString, filters)).Get(databaseSchema);
Thread t1 = new Thread(delegate()
{
try
{
GenerateRules.Fill(databaseSchema, connectionString);
GenerateTables.Fill(databaseSchema, connectionString, messages);
GenerateUserDataTypes.Fill(databaseSchema, connectionString, messages);
GenerateXMLSchemas.Fill(databaseSchema, connectionString);
GenerateSchemas.Fill(databaseSchema, connectionString);
GenerateUsers.Fill(databaseSchema, connectionString);
}
catch (Exception ex)
{
error = ex.StackTrace;
}
});
Thread t2 = new Thread(delegate()
{
try
{
GeneratePartionFunctions.Fill(databaseSchema, connectionString);
GenerateFileGroups.Fill(databaseSchema, connectionString);
GenerateDDLTriggers.Fill(databaseSchema, connectionString);
GenerateSynonyms.Fill(databaseSchema, connectionString);
GenerateAssemblies.Fill(databaseSchema, connectionString);
}
catch (Exception ex)
{
error = ex.StackTrace;
}
});
Thread t3 = new Thread(delegate()
{
try
{
GenerateStoreProcedures.Fill(databaseSchema, connectionString);
GenerateViews.Fill(databaseSchema, connectionString);
GenerateFunctions.Fill(databaseSchema, connectionString);
}
catch (Exception ex)
{
error = ex.StackTrace;
}
});
t1.Start();
t2.Start();
t3.Start();
t1.Join();
t2.Join();
t3.Join();
if (String.IsNullOrEmpty(error))
{
/*Las propiedades extendidas deben ir despues de haber capturado el resto de los objetos de la base*/
GenerateExtendedProperties.Fill(databaseSchema, connectionString, messages);
databaseSchema.BuildDependency();
return(databaseSchema);
}
else
{
throw new SchemaException(error);
}
}
catch
{
throw;
}
}
public void Show(IWin32Window owner, SqlOption filter)
{
SqlFilter = filter;
sqlOptionsFront1.Load(filter);
this.Show(owner);
}
public SqlAttribute(SqlOption selectedOption = SqlOption.Index)
{
this.Option = selectedOption;
}
/// <summary>
/// 参数
/// </summary>
/// <param name="field">字段</param>
/// <param name="option">查询类型</param>
/// <param name="value">值</param>
public Arg(String field, SqlOption option, Object value)
{
this.Option = option;
this.Stantard = value;
this.Field = field;
}
public AddExclusionPatternForm(SqlOption sqlOption)
: this(sqlOption, -1)
{
}
public PrincipalForm()
{
InitializeComponent();
SqlFilter = new SqlOption();
}
public TESTContext(DbContextOptions <TESTContext> options, IOptions <SqlOption> connectionString)
: base(options)
{
this._connectionString = connectionString.Value;
}
//public TESTContext()
//{
//}
public TESTContext(IOptions <SqlOption> connectionString)
{
this._connectionString = connectionString.Value;
}