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 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;
}
public PrincipalForm()
{
InitializeComponent();
SqlFilter = new SqlOption();
}
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;
}
/// <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();
}
private SqlOption BuildOptions()
{
var config = (Configuration) Configuration;
var options = new SqlOption(false)
{
Ignore =
{
FilterConstraint = config[OptionType.FilterConstraint].ConfiguredValue,
FilterFunction = config[OptionType.FilterFunction].ConfiguredValue,
FilterStoreProcedure = config[OptionType.FilterStoreProcedure].ConfiguredValue,
FilterView = config[OptionType.FilterView].ConfiguredValue,
FilterTable = config[OptionType.FilterTable].ConfiguredValue,
FilterTableOption = config[OptionType.FilterTableOption].ConfiguredValue,
FilterUserDataType = config[OptionType.FilterUserDataType].ConfiguredValue,
FilterTrigger = config[OptionType.FilterTrigger].ConfiguredValue,
FilterSchema = config[OptionType.FilterSchema].ConfiguredValue,
FilterXMLSchema = config[OptionType.FilterXMLSchema].ConfiguredValue,
FilterTableFileGroup = config[OptionType.FilterTableFileGroup].ConfiguredValue,
FilterExtendedPropertys =
config[OptionType.FilterExtendedProperties].ConfiguredValue,
FilterDDLTriggers = config[OptionType.FilterDDLTriggers].ConfiguredValue,
FilterSynonyms = config[OptionType.FilterSynonyms].ConfiguredValue,
FilterRules = config[OptionType.FilterRules].ConfiguredValue,
FilterAssemblies = config[OptionType.FilterAssemblies].ConfiguredValue,
FilterTableChangeTracking =
config[OptionType.FilterTableChangeTracking].ConfiguredValue,
FilterTableLockEscalation =
config[OptionType.FilterTableLockEscalation].ConfiguredValue,
FilterFullTextPath = config[OptionType.FilterFullTextPath].ConfiguredValue,
FilterFullText = config[OptionType.FilterFullText].ConfiguredValue,
FilterCLRStoreProcedure =
config[OptionType.FilterCLRStoredProcedure].ConfiguredValue,
FilterCLRUDT = config[OptionType.FilterCLRUserDefinedType].ConfiguredValue,
FilterCLRTrigger = config[OptionType.FilterCLRTrigger].ConfiguredValue,
FilterCLRFunction = config[OptionType.FilterCLRFunction].ConfiguredValue,
FilterRoles = config[OptionType.FilterRoles].ConfiguredValue,
FilterUsers = config[OptionType.FilterUsers].ConfiguredValue,
FilterNotForReplication =
config[OptionType.FilterNotForReplication].ConfiguredValue,
FilterColumnCollation =
config[OptionType.FilterColumnCollation].ConfiguredValue,
FilterColumnIdentity = config[OptionType.FilterColumnIdentity].ConfiguredValue,
FilterColumnOrder = config[OptionType.FilterColumnOrder].ConfiguredValue,
FilterIndexRowLock = config[OptionType.FilterIndexRowLock].ConfiguredValue,
FilterIndexIncludeColumns =
config[OptionType.FilterIndexIncludeColumns].ConfiguredValue,
FilterIndexFillFactor =
config[OptionType.FilterIndexFillFactor].ConfiguredValue,
FilterConstraintCheck =
config[OptionType.FilterConstraintCheck].ConfiguredValue,
FilterConstraintUK =
config[OptionType.FilterConstraintUniqueKey].ConfiguredValue,
FilterConstraintFK =
config[OptionType.FilterConstraintForeignKey].ConfiguredValue,
FilterConstraintPK =
config[OptionType.FilterConstraintPrimaryKey].ConfiguredValue,
FilterIndex = config[OptionType.FilterIndex].ConfiguredValue,
FilterIndexFilter = config[OptionType.FilterIndexFilter].ConfiguredValue,
FilterPartitionScheme =
config[OptionType.FilterPartitionScheme].ConfiguredValue,
FilterPartitionFunction =
config[OptionType.FilterPartitionFunction].ConfiguredValue,
},
Comparison =
{
IgnoreWhiteSpacesInCode =
config[OptionType.ComparisonIgnoreWhiteSpacesInCode].ConfiguredValue,
CaseSensityType =
config[OptionType.FilterPartitionFunction].ConfiguredValue
? SqlOptionComparison.CaseSensityOptions.CaseSensity
: SqlOptionComparison.CaseSensityOptions.Automatic,
CaseSensityInCode =
config[OptionType.FilterPartitionFunction].ConfiguredValue
? SqlOptionComparison.CaseSensityOptions.CaseSensity
: SqlOptionComparison.CaseSensityOptions.Automatic,
},
Script =
{
AlterObjectOnSchemaBinding =
config[OptionType.ScriptAlterObjectOnSchemaBinding].ConfiguredValue
}
};
return options;
}
public void Show(IWin32Window owner, SqlOption filter)
{
SqlFilter = filter;
sqlOptionsFront1.Load(filter);
this.Show(owner);
}