public void Fill(Database database, string connectioString)
{
if (database.Options.Ignore.FilterSchema)
{
using (SqlConnection conn = new SqlConnection(connectioString))
{
using (SqlCommand command = new SqlCommand(GetSQL(), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Model.Schema item = new Model.Schema(database);
item.Id = (int)reader["schema_id"];
item.Name = reader["name"].ToString();
item.Owner = reader["owner"].ToString();
item.CreateDate = (DateTime) reader["create_date"];
item.ModifyDate = (DateTime) reader["modify_date"];
database.Schemas.Add(item);
}
}
}
}
}
}
public void Fill(Database database, string connectionString)
{
if (database.Options.Ignore.FilterFullText)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQL(), conn))
{
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
FullText item = new FullText(database);
item.Id = (int)reader["fulltext_catalog_id"];
item.Name = reader["Name"].ToString();
item.Owner = reader["Owner"].ToString();
item.IsAccentSensity = (bool)reader["is_accent_sensitivity_on"];
item.IsDefault = (bool)reader["is_default"];
if (!reader.IsDBNull(reader.GetOrdinal("path")))
item.Path = reader["path"].ToString().Substring(0, reader["path"].ToString().Length - item.Name.Length);
if (!reader.IsDBNull(reader.GetOrdinal("FileGroupName")))
item.FileGroupName = reader["FileGroupName"].ToString();
database.FullText.Add(item);
}
}
}
}
}
}
public void Fill(Database database, string connectionString)
{
if (database.Options.Ignore.FilterSynonyms)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQL(), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Synonym item = new Synonym(database) {
Id = (int) reader["object_id"],
Name = reader["Name"].ToString(),
Owner = reader["Owner"].ToString(),
Value = reader["base_object_name"].ToString(),
CreateDate = (DateTime) reader["create_date"],
ModifyDate = (DateTime) reader["modify_date"]
};
database.Synonyms.Add(item);
}
}
}
}
}
}
private static void FillParameters(Database database, string connectionString)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQLParameters(), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Parameter param = new Parameter();
param.Name = reader["Name"].ToString();
param.Type = reader["TypeName"].ToString();
param.Size = (short)reader["max_length"];
param.Scale = (byte)reader["scale"];
param.Precision = (byte)reader["precision"];
param.Output = (bool)reader["is_output"];
if (param.Type.Equals("nchar") || param.Type.Equals("nvarchar"))
{
if (param.Size != -1)
param.Size = param.Size / 2;
}
database.CLRFunctions[reader["ObjectName"].ToString()].Parameters.Add(param);
}
}
}
}
}
public void Fill(Database database, string connectionString)
{
if (database.Options.Ignore.FilterDDLTriggers)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand command = new SqlCommand(GetSQL(), conn))
{
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Trigger trigger = new Trigger(database);
trigger.Text = reader["Text"].ToString();
trigger.Name = reader["Name"].ToString();
trigger.InsteadOf = (bool)reader["is_instead_of_trigger"];
trigger.IsDisabled = (bool)reader["is_disabled"];
trigger.IsDDLTrigger = true;
trigger.NotForReplication = (bool)reader["is_not_for_replication"];
trigger.Owner = "";
database.DDLTriggers.Add(trigger);
}
}
}
}
}
}
public void Fill(Database database, string connectioString)
{
int lastObjectId = 0;
PartitionScheme item = null;
if (database.Options.Ignore.FilterPartitionScheme)
{
using (SqlConnection conn = new SqlConnection(connectioString))
{
using (SqlCommand command = new SqlCommand(GetSQL(), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (lastObjectId != (int)reader["ID"])
{
lastObjectId = (int)reader["ID"];
item = new PartitionScheme(database);
item.Id = (int)reader["ID"];
item.Name = reader["name"].ToString();
item.PartitionFunction = reader["FunctionName"].ToString();
database.PartitionSchemes.Add(item);
}
item.FileGroups.Add(reader["FileGroupName"].ToString());
}
}
}
}
}
}
private static void FillFiles(Database database, string connectionString)
{
if (database.Options.Ignore.FilterAssemblies)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQLFiles(), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (((int)reader["FileId"]) != 1)
{
Assembly assem = database.Assemblies[reader["Name"].ToString()];
AssemblyFile file = new AssemblyFile(assem,reader["FileName"].ToString(), ToHex((byte[])reader["FileContent"]));
assem.Files.Add(file);
}
}
}
}
}
}
}
public void Fill(Database database, string connectionString)
{
if (database.Options.Ignore.FilterRules)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQL(), conn))
{
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Rule item = new Rule(database);
item.Id = (int)reader["object_id"];
item.Name = reader["Name"].ToString();
item.Owner = reader["Owner"].ToString();
item.Text = reader["Definition"].ToString();
database.Rules.Add(item);
}
}
}
}
}
}
public void Fill(Database database, string connectionString)
{
try
{
if (database.Options.Ignore.FilterTableFileGroup)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQL(), conn))
{
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
FileGroup item = new FileGroup(database);
item.Id = (int)reader["ID"];
item.Name = reader["name"].ToString();
item.Owner = "";
item.IsDefaultFileGroup = (bool)reader["is_default"];
item.IsReadOnly = (bool)reader["is_read_only"];
item.IsFileStream = reader["type"].Equals("FD");
FillFiles(item,connectionString);
database.FileGroups.Add(item);
}
}
}
}
}
}
catch
{
throw;
}
}
public void Fill(Database database, string connectionString)
{
if (database.Options.Ignore.FilterRules)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQL(), conn))
{
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var name = reader["Name"].ToString();
var owner = reader["Owner"].ToString();
Default item = new Default(database);
item.Id = (int)reader["object_id"];
item.Name = Util.SqlStrings.HasHexEnding(name) ? AlternateName(owner, reader["TableName"].ToString(), reader["ColumnName"].ToString()) : name;
item.Owner = owner;
item.Value = reader["Definition"].ToString();
database.Defaults.Add(item);
}
}
}
}
}
}
public static string Get(DatabaseInfo.VersionTypeEnum version, Database databaseSchema)
{
if (version == DatabaseInfo.VersionTypeEnum.SQLServer2005) return Get2005(databaseSchema);
if (version == DatabaseInfo.VersionTypeEnum.SQLServer2008) return Get2008(databaseSchema);
if (version == DatabaseInfo.VersionTypeEnum.SQLServer2008R2) return Get2008R2(databaseSchema);
if (version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10) return GetAzure(databaseSchema);
return "";
}
public void Fill(Database database, string connectionString, List<MessageLog> messages)
{
//not supported in azure yet http://msdn.microsoft.com/en-us/library/ee336233.aspx
if (database.Info.Version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10) return;
try
{
if (database.Options.Ignore.FilterUserDataType)
{
root.RaiseOnReading(new ProgressEventArgs("Reading UDT...", Constants.READING_UDT));
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(UserDataTypeCommand.Get(database.Info.Version), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
root.RaiseOnReadingOne(reader["Name"]);
UserDataType item = new UserDataType(database);
item.Id = (int)reader["tid"];
item.AllowNull = (bool)reader["is_nullable"];
item.Size = (short)reader["max_length"];
item.Name = reader["Name"].ToString();
item.Owner = reader["owner"].ToString();
item.Precision = int.Parse(reader["precision"].ToString());
item.Scale = int.Parse(reader["scale"].ToString());
if (!String.IsNullOrEmpty(reader["defaultname"].ToString()))
{
item.Default.Name = reader["defaultname"].ToString();
item.Default.Owner = reader["defaultowner"].ToString();
}
if (!String.IsNullOrEmpty(reader["rulename"].ToString()))
{
item.Rule.Name = reader["rulename"].ToString();
item.Rule.Owner = reader["ruleowner"].ToString();
}
item.Type = reader["basetypename"].ToString();
item.IsAssembly = (bool)reader["is_assembly_type"];
item.AssemblyId = (int)reader["assembly_id"];
item.AssemblyName = reader["assembly_name"].ToString();
item.AssemblyClass = reader["assembly_class"].ToString();
database.UserTypes.Add(item);
}
}
}
}
if (database.Options.Ignore.FilterTable)
FillColumnsDependencies(database.UserTypes, connectionString);
}
}
catch (Exception ex)
{
messages.Add(new MessageLog(ex.Message, ex.StackTrace, MessageLog.LogType.Error));
}
}
public void Fill(Database database, string connectionString)
{
//not supported in azure yet
if (database.Info.Version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10) return;
int parentId = 0;
bool change = false;
Table parent = null;
root.RaiseOnReading(new ProgressEventArgs("Reading FullText Index...", Constants.READING_INDEXES));
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(FullTextIndexSQLCommand.Get(database.Info.Version), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
FullTextIndex item = null;
while (reader.Read())
{
root.RaiseOnReadingOne(reader["Name"]);
if (parentId != (int)reader["object_id"])
{
parentId = (int)reader["object_id"];
parent = database.Tables.Find(parentId);
change = true;
}
else
change = false;
if (change)
{
item = new FullTextIndex(parent);
item.Name = reader["Name"].ToString();
item.Owner = parent.Owner;
item.FullText = reader["FullTextCatalogName"].ToString();
item.Index = reader["IndexName"].ToString();
item.IsDisabled = !(bool)reader["is_enabled"];
item.ChangeTrackingState = reader["ChangeTracking"].ToString();
if (database.Info.Version == DatabaseInfo.VersionTypeEnum.SQLServer2008)
item.FileGroup = reader["FileGroupName"].ToString();
((Table)parent).FullTextIndex.Add(item);
}
FullTextIndexColumn ccon = new FullTextIndexColumn();
ccon.ColumnName = reader["ColumnName"].ToString();
ccon.Language = reader["LanguageName"].ToString();
item.Columns.Add(ccon);
}
}
}
}
}
public void Fill(Database database, string connectionString)
{
ICode code = null;
try
{
if ((database.Options.Ignore.FilterStoreProcedure) || (database.Options.Ignore.FilterView) || (database.Options.Ignore.FilterFunction) || (database.Options.Ignore.FilterTrigger))
{
root.RaiseOnReading(new ProgressEventArgs("Reading Text Objects...", Constants.READING_TEXTOBJECTS));
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQL(database.Options), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
code = null;
root.RaiseOnReadingOne(reader["name"]);
string type = reader["Type"].ToString().Trim();
string name = reader["name"].ToString();
string definition = reader["Text"].ToString();
int id = (int)reader["object_id"];
if (type.Equals("V"))
code = (ICode)database.Views.Find(id);
if (type.Equals("TR"))
code = (ICode)database.Find(id);
if (type.Equals("P"))
((ICode)database.Procedures.Find(id)).Text = GetObjectDefinition(type, name, definition);
if (type.Equals("IF") || type.Equals("FN") || type.Equals("TF"))
code = (ICode)database.Functions.Find(id);
if (code != null)
code.Text = reader["Text"].ToString();
}
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public void Fill(Database database, string connectionString, List<MessageLog> messages)
{
try
{
root.RaiseOnReading(new ProgressEventArgs("Reading views...", Constants.READING_VIEWS));
if (database.Options.Ignore.FilterView)
{
FillView(database,connectionString);
}
}
catch (Exception ex)
{
messages.Add(new MessageLog(ex.Message, ex.StackTrace, MessageLog.LogType.Error));
}
}
public void FillCheck(Database database, string connectionString)
{
int parentId = 0;
ISchemaBase table = null;
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(ConstraintSQLCommand.GetCheck(database.Info.Version), conn))
{
root.RaiseOnReading(new ProgressEventArgs("Reading constraint...", Constants.READING_CONSTRAINTS));
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
Constraint item = null;
while (reader.Read())
{
root.RaiseOnReadingOne(reader["Name"]);
if (parentId != (int)reader["parent_object_id"])
{
parentId = (int)reader["parent_object_id"];
if (reader["ObjectType"].ToString().Trim().Equals("U"))
table = database.Tables.Find(parentId);
else
table = database.TablesTypes.Find(parentId);
}
item = new Constraint(table);
item.Id = (int)reader["id"];
item.Name = reader["Name"].ToString();
item.Type = Constraint.ConstraintType.Check;
item.Definition = reader["Definition"].ToString();
item.WithNoCheck = (bool)reader["WithCheck"];
item.IsDisabled = (bool)reader["is_disabled"];
item.Owner = reader["Owner"].ToString();
if (database.Options.Ignore.FilterNotForReplication)
item.NotForReplication = (bool)reader["is_not_for_replication"];
if (reader["ObjectType"].ToString().Trim().Equals("U"))
((Table)table).Constraints.Add(item);
else
((TableType)table).Constraints.Add(item);
}
}
}
}
}
public void Fill(Database database, string connectioString)
{
string type;
if ((database.Options.Ignore.FilterUsers) || (database.Options.Ignore.FilterRoles))
{
using (SqlConnection conn = new SqlConnection(connectioString))
{
using (SqlCommand command = new SqlCommand(UserSQLCommand.Get(database.Info.Version), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
type = reader["type"].ToString();
if (database.Options.Ignore.FilterUsers && (type.Equals("S") || type.Equals("U")))
{
User item = new User(database);
item.Id = (int)reader["principal_id"];
item.Name = reader["name"].ToString();
item.Login = reader["Login"].ToString();
item.Owner = reader["default_schema_name"].ToString();
database.Users.Add(item);
}
if (database.Options.Ignore.FilterRoles && (type.Equals("A") || type.Equals("R")))
{
Role item = new Role(database);
item.Id = (int)reader["principal_id"];
item.Name = reader["name"].ToString();
item.Owner = reader["default_schema_name"].ToString();
item.Password = "";
item.IsSystem = (Boolean)reader["is_fixed_role"];
if (type.Equals("A"))
item.Type = Role.RoleTypeEnum.ApplicationRole;
else
item.Type = Role.RoleTypeEnum.DatabaseRole;
database.Roles.Add(item);
}
}
}
}
}
}
}
public void Fill(Database database, string connectionString)
{
int lastViewId = 0;
if (database.Options.Ignore.FilterAssemblies)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQL(), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
Assembly item = null;
while (reader.Read())
{
if (lastViewId != (int)reader["assembly_id"])
{
item = new Assembly(database)
{
Id = (int) reader["assembly_id"],
Name = reader["Name"].ToString(),
Owner = reader["Owner"].ToString(),
CLRName = reader["clr_name"].ToString(),
PermissionSet = reader["permission_set_desc"].ToString(),
Text = ToHex((byte[]) reader["content"]),
Visible = (bool) reader["is_visible"]
};
lastViewId = item.Id;
database.Assemblies.Add(item);
}
if (!String.IsNullOrEmpty(reader["Dependency"].ToString()))
item.DependenciesOut.Add(reader["Dependency"].ToString());
if (!String.IsNullOrEmpty(reader["ObjectDependency"].ToString()))
item.DependenciesOut.Add(reader["ObjectDependency"].ToString());
if (!String.IsNullOrEmpty(reader["UDTName"].ToString()))
item.DependenciesOut.Add(reader["UDTName"].ToString());
}
}
}
FillFiles(database, connectionString);
}
}
}
private void FillView(Database database, string connectionString)
{
int lastViewId = 0;
using (var conn = new SqlConnection(connectionString))
{
using (var command = new SqlCommand(ViewSQLCommand.GetView(database.Info.Version), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
View item = null;
while (reader.Read())
{
root.RaiseOnReadingOne(reader["name"]);
if (lastViewId != (int)reader["object_id"]) {
item = new View(database) {
Id = (int) reader["object_id"],
Name = reader["name"].ToString(),
Owner = reader["owner"].ToString(),
IsSchemaBinding = reader["IsSchemaBound"].ToString().Equals("1"),
CreateDate = (DateTime) reader["create_date"],
ModifyDate = (DateTime) reader["modify_date"]
};
database.Views.Add(item);
lastViewId = item.Id;
}
if (item.IsSchemaBinding)
{
if (!reader.IsDBNull(reader.GetOrdinal("referenced_major_id")))
database.Dependencies.Add(database,(int)reader["referenced_major_id"], item);
if (!String.IsNullOrEmpty(reader["TableName"].ToString()))
item.DependenciesIn.Add(reader["TableName"].ToString());
if (!String.IsNullOrEmpty(reader["DependOut"].ToString()))
item.DependenciesOut.Add(reader["DependOut"].ToString());
}
}
}
}
}
}
public void Fill(Database database, string connectioString)
{
int lastObjectId = 0;
PartitionFunction item = null;
if (database.Options.Ignore.FilterPartitionFunction)
{
using (SqlConnection conn = new SqlConnection(connectioString))
{
using (SqlCommand command = new SqlCommand(GetSQL(), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (lastObjectId != (int)reader["function_id"])
{
lastObjectId = (int)reader["function_id"];
item = new PartitionFunction(database);
item.Id = (int)reader["function_id"];
item.Name = reader["name"].ToString();
item.IsBoundaryRight = (bool)reader["IsRight"];
item.Precision = (byte)reader["precision"];
item.Scale = (byte)reader["scale"];
item.Size = (short)reader["max_length"];
item.Type = reader["TypeName"].ToString();
database.PartitionFunctions.Add(item);
}
if (item.Type.Equals("binary") || item.Type.Equals("varbinary"))
item.Values.Add(ToHex((byte[])reader["value"]));
else
item.Values.Add(reader["value"].ToString());
}
}
}
}
}
}
public static Database GenerateDiferences(Database Origen, Database Destino)
{
try
{
Database data = Origen;
(new CompareTables()).GenerateDiferences<Database>(Origen.Tables, Destino.Tables);
(new CompareAssemblies()).GenerateDiferences<Database>(Origen.Assemblies, Destino.Assemblies);
(new CompareUserDataTypes()).GenerateDiferences<Database>(Origen.UserTypes, Destino.UserTypes);
(new CompareXMLSchemas()).GenerateDiferences<Database>(Origen.XmlSchemas, Destino.XmlSchemas);
(new CompareSchemas()).GenerateDiferences<Database>(Origen.Schemas, Destino.Schemas);
(new CompareFileGroups()).GenerateDiferences<Database>(Origen.FileGroups, Destino.FileGroups);
(new CompareRules()).GenerateDiferences<Database>(Origen.Rules, Destino.Rules);
(new CompareDDLTriggers()).GenerateDiferences<Database>(Origen.DDLTriggers, Destino.DDLTriggers);
(new CompareSynonyms()).GenerateDiferences<Database>(Origen.Synonyms, Destino.Synonyms);
(new CompareUsers()).GenerateDiferences<Database>(Origen.Users, Destino.Users);
(new CompareStoreProcedures()).GenerateDiferences<Database>(Origen.Procedures, Destino.Procedures);
(new CompareCLRStoreProcedure()).GenerateDiferences<Database>(Origen.CLRProcedures, Destino.CLRProcedures);
(new CompareCLRFunction()).GenerateDiferences<Database>(Origen.CLRFunctions, Destino.CLRFunctions);
(new CompareViews()).GenerateDiferences<Database>(Origen.Views, Destino.Views);
(new CompareFunctions()).GenerateDiferences<Database>(Origen.Functions, Destino.Functions);
(new CompareRoles()).GenerateDiferences<Database>(Origen.Roles, Destino.Roles);
(new ComparePartitionFunction()).GenerateDiferences<Database>(Origen.PartitionFunctions, Destino.PartitionFunctions);
(new ComparePartitionSchemes()).GenerateDiferences<Database>(Origen.PartitionSchemes, Destino.PartitionSchemes);
(new CompareTableType()).GenerateDiferences<Database>(Origen.TablesTypes, Destino.TablesTypes);
(new CompareFullText()).GenerateDiferences<Database>(Origen.FullText, Destino.FullText);
data.SourceInfo = Destino.Info;
return data;
}
catch (SchemaException)
{
throw;
}
catch (Exception ex)
{
throw new SchemaException(ex.Message,ex);
}
}
public void Fill(Database database, string connectionString)
{
//TODO XML_SCHEMA_NAMESPACE function not supported in Azure, is there a workaround?
//not supported in azure yet
if (database.Info.Version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10) return;
if (database.Options.Ignore.FilterXMLSchema)
{
root.RaiseOnReading(new ProgressEventArgs("Reading XML Schema...", Constants.READING_XMLSCHEMAS));
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQLXMLSchema(), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
root.RaiseOnReadingOne(reader["name"]);
XMLSchema item = new XMLSchema(database);
item.Id = (int)reader["ID"];
item.Name = reader["name"].ToString();
item.Owner = reader["owner"].ToString();
item.Text = reader["Text"].ToString();
database.XmlSchemas.Add(item);
}
}
}
}
if (database.Options.Ignore.FilterTable)
FillColumnsDependencies(database.XmlSchemas, connectionString);
}
}
public override ISchemaBase Clone(ISchemaBase parent)
{
//Get a list of all of the objects that are SchemaLists, so that we can clone them all.
var item = new Database() { AllObjects = this.AllObjects };
var explicitProperties = (from properties in this.GetType().GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public)
where properties.PropertyType.GetInterface(typeof(DBDiff.Schema.Model.ISchemaList<Code, Database>).Name) != null
select properties).ToList();
foreach (var property in explicitProperties)
{
object value = property.GetValue(this, null);
//Clone the value
value = value.GetType().GetMethod("Clone").Invoke(value, new object[] { this });
//Set the value to the cloned object
property.SetValue(item, value, null);
}
return item;
}
public Schema(Database parent)
: base(parent, Enums.ObjectType.Schema)
{
}
public static Database Compare(Database databaseOriginalSchema, Database databaseCompareSchema)
{
Database merge = CompareDatabase.GenerateDiferences(databaseOriginalSchema, databaseCompareSchema);
return merge;
}
/// <summary>
/// Genera el schema de la base de datos seleccionada y devuelve un objeto Database.
/// </summary>
public Database Process()
{
var error = string.Empty;
var databaseSchema = new Database {Options = Options, Name = Name};
databaseSchema.Info = (new GenerateDatabase(connectionString, Options)).Get(databaseSchema);
(new GenerateRules(this)).Fill(databaseSchema, connectionString);
(new GenerateTables(this)).Fill(databaseSchema, connectionString, messages);
(new GenerateViews(this)).Fill(databaseSchema, connectionString, messages);
(new GenerateIndex(this)).Fill(databaseSchema, connectionString);
(new GenerateFullTextIndex(this)).Fill(databaseSchema, connectionString);
(new GenerateUserDataTypes(this)).Fill(databaseSchema, connectionString, messages);
(new GenerateXMLSchemas(this)).Fill(databaseSchema, connectionString);
(new GenerateSchemas(this)).Fill(databaseSchema, connectionString);
//not supported in azure yet
if (databaseSchema.Info.Version != DatabaseInfo.VersionTypeEnum.SQLServerAzure10) {
(new GeneratePartitionFunctions(this)).Fill(databaseSchema, connectionString);
(new GeneratePartitionScheme(this)).Fill(databaseSchema, connectionString);
(new GenerateFileGroups(this)).Fill(databaseSchema, connectionString);
}
(new GenerateDDLTriggers(this)).Fill(databaseSchema, connectionString);
(new GenerateSynonyms(this)).Fill(databaseSchema, connectionString);
//not supported in azure yet
if (databaseSchema.Info.Version != DatabaseInfo.VersionTypeEnum.SQLServerAzure10)
{
(new GenerateAssemblies(this)).Fill(databaseSchema, connectionString);
(new GenerateFullText(this)).Fill(databaseSchema, connectionString);
}
(new GenerateStoreProcedures(this)).Fill(databaseSchema, connectionString);
(new GenerateFunctions(this)).Fill(databaseSchema, connectionString);
(new GenerateTriggers(this)).Fill(databaseSchema, connectionString, messages);
(new GenerateTextObjects(this)).Fill(databaseSchema, connectionString);
(new GenerateUsers(this)).Fill(databaseSchema, connectionString);
if (String.IsNullOrEmpty(error))
{
/*Las propiedades extendidas deben ir despues de haber capturado el resto de los objetos de la base*/
(new GenerateExtendedProperties(this)).Fill(databaseSchema, connectionString, messages);
databaseSchema.BuildDependency();
return databaseSchema;
}
else
throw new SchemaException(error);
}
public void Fill(Database database, string connectionString, List<MessageLog> messages)
{
int parentId = 0;
ISchemaBase parent = null;
string type;
try
{
if (database.Options.Ignore.FilterTrigger)
{
root.RaiseOnReading(new ProgressEventArgs("Reading Triggers...", Constants.READING_TRIGGERS));
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQL(database.Info.Version, database.Options), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
root.RaiseOnReadingOne(reader["Name"]);
type = reader["ObjectType"].ToString().Trim();
if (parentId != (int)reader["parent_id"])
{
parentId = (int)reader["parent_id"];
if (type.Equals("V"))
parent = database.Views.Find(parentId);
else
parent = database.Tables.Find(parentId);
}
if (reader["type"].Equals("TR"))
{
Trigger item = new Trigger(parent);
item.Id = (int)reader["object_id"];
item.Name = reader["Name"].ToString();
item.InsteadOf = (bool)reader["is_instead_of_trigger"];
item.IsDisabled = (bool)reader["is_disabled"];
item.IsDDLTrigger = false;
item.Owner = reader["Owner"].ToString();
if (database.Options.Ignore.FilterNotForReplication)
item.NotForReplication = (bool)reader["is_not_for_replication"];
if (type.Equals("V"))
((View)parent).Triggers.Add(item);
else
((Table)parent).Triggers.Add(item);
}
else
{
CLRTrigger item = new CLRTrigger(parent);
item.Id = (int)reader["object_id"];
item.Name = reader["Name"].ToString();
item.IsDelete = (bool)reader["IsDelete"];
item.IsUpdate = (bool)reader["IsUpdate"];
item.IsInsert = (bool)reader["IsInsert"];
item.Owner = reader["Owner"].ToString();
item.IsAssembly = true;
item.AssemblyId = (int)reader["assembly_id"];
item.AssemblyName = reader["assembly_name"].ToString();
item.AssemblyClass = reader["assembly_class"].ToString();
item.AssemblyExecuteAs = reader["ExecuteAs"].ToString();
item.AssemblyMethod = reader["assembly_method"].ToString();
if (type.Equals("V"))
((View)parent).CLRTriggers.Add(item);
else
((Table)parent).CLRTriggers.Add(item);
/*if (!database.Options.Ignore.FilterIgnoreNotForReplication)
trigger.NotForReplication = (bool)reader["is_not_for_replication"];*/
}
}
}
}
}
}
}
catch (Exception ex)
{
messages.Add(new MessageLog(ex.Message, ex.StackTrace, MessageLog.LogType.Error));
}
}
public DatabaseInfo Get(Database database)
{
DatabaseInfo item = new DatabaseInfo();
using (SqlConnection conn = new SqlConnection(connectioString))
{
using (SqlCommand command = new SqlCommand(DatabaseSQLCommand.GetVersion(database), conn))
{
conn.Open();
item.Server = conn.DataSource;
item.Database = conn.Database;
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
string versionValue = reader["Version"] as string;
try
{
// used to use the decimal as well when Azure was 10.25
var version = new Version(versionValue);
item.VersionNumber = float.Parse(String.Format("{0}.{1}", version.Major, version.Minor), System.Globalization.CultureInfo.InvariantCulture);
int? edition = null;
if (reader.FieldCount > 1 && !reader.IsDBNull(1))
{
int validEdition;
string editionValue = reader[1].ToString();
if (!String.IsNullOrEmpty(editionValue) && int.TryParse(editionValue, out validEdition))
{
edition = validEdition;
}
}
item.SetEdition(edition);
}
catch (Exception notAGoodIdeaToCatchAllErrors)
{
bool useDefaultVersion = false;
//#if DEBUG
// useDefaultVersion = IsKeyPushedDown(System.Windows.Forms.Keys.LShiftKey)
// && IsKeyPushedDown(System.Windows.Forms.Keys.RShiftKey);
//#endif
var exception = new DBDiff.Schema.Misc.SchemaException(
String.Format("Error parsing ProductVersion. ({0})", versionValue ?? "[null]")
, notAGoodIdeaToCatchAllErrors);
if (!useDefaultVersion)
{
throw exception;
}
}
}
}
}
using (SqlCommand command = new SqlCommand(DatabaseSQLCommand.Get(item.Version, database), conn))
{
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
item.Collation = reader["Collation"].ToString();
item.HasFullTextEnabled = ((int)reader["IsFulltextEnabled"]) == 1;
}
}
}
}
return item;
}
public override IEnumerable<Row> Execute(IEnumerable<Row> rows)
{
var counter = 0;
foreach (var row in rows) {
counter++;
if (counter == 1) {
Guard.Against(!row.Contains(CONNECTION_STRING_KEY), "Row must contain connectionstring key.");
}
_connectionStringBuilder = new SqlConnectionStringBuilder(row[CONNECTION_STRING_KEY].ToString());
var sqlConnectionChecker = new SqlConnectionChecker(new[] { _connectionStringBuilder.ConnectionString });
if (!sqlConnectionChecker.AllGood())
continue;
var results = new Database();
var subRows = new List<Row>();
var generator = new Generate() { ConnectionString = _connectionStringBuilder.ConnectionString };
Debug("Started generating definitions on {0} for {1}", _connectionStringBuilder.DataSource, _connectionStringBuilder.InitialCatalog);
try {
results = generator.Process();
}
catch (Exception e) {
Warn("Trouble processing objects from {0}.{1}.\nError Message: {2}.", _connectionStringBuilder.DataSource, _connectionStringBuilder.InitialCatalog, e.Message);
}
Debug("Finished generating defs on {0} for {1}", _connectionStringBuilder.DataSource, _connectionStringBuilder.InitialCatalog);
subRows.AddRange(ToRows(results.Procedures, "StoredProcedures", "Stored Procedure"));
subRows.AddRange(ToRows(results.Functions, "Functions", "Function"));
subRows.AddRange(ToRows(results.Tables, "Tables", "Table"));
subRows.AddRange(ToRows(results.Views, "Views", "View"));
subRows.AddRange(ToRows(results.Schemas, "Schemas", "Schema"));
subRows.AddRange(ToRows(results.Synonyms, "Synonyms", "Synonym"));
subRows.AddRange(ToRows(results.FullText, "FullTextCatalogs", "Full Text Catalog"));
foreach (var table in results.Tables) {
subRows.AddRange(ToRows(table.Indexes, "Indexes", "Index", true));
subRows.AddRange(ToRows(table.Triggers, "Triggers", "Trigger", true));
foreach (var constraint in table.Constraints) {
switch (constraint.Type) {
case Constraint.ConstraintType.Check:
subRows.AddRange(ToRows(Enumerable.Repeat(constraint, 1), "CheckConstraints", "Check Constraint", true, true));
break;
case Constraint.ConstraintType.Default:
subRows.AddRange(ToRows(Enumerable.Repeat(constraint, 1), "DefaultConstraints", "Default Constraint", true, true));
break;
case Constraint.ConstraintType.ForeignKey:
subRows.AddRange(ToRows(Enumerable.Repeat(constraint, 1), "ForeignKeys", "Foreign Key", true, true));
break;
case Constraint.ConstraintType.PrimaryKey:
subRows.AddRange(ToRows(Enumerable.Repeat(constraint, 1), "PrimaryKeys", "Primary Key", true, true));
break;
case Constraint.ConstraintType.Unique:
subRows.AddRange(ToRows(Enumerable.Repeat(constraint, 1), "UniqueConstraints", "Unique Constraint", true, true));
break;
}
}
foreach (var index in table.FullTextIndex)
subRows.AddRange(ToRows(Enumerable.Repeat(index, 1), "FullTextIndexes", "Full Text Index", true));
}
Info("Found {0} in {1}.", subRows.Count, _connectionStringBuilder.InitialCatalog);
foreach (var subRow in subRows) {
yield return subRow;
}
}
}
public void Fill(Database database, string connectionString)
{
int lastViewId = 0;
if ((database.Options.Ignore.FilterFunction) || (database.Options.Ignore.FilterCLRFunction))
{
root.RaiseOnReading(new ProgressEventArgs("Reading functions...", Constants.READING_FUNCTIONS));
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(FunctionSQLCommand.Get(database.Info.Version), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
Function itemF = null;
CLRFunction itemC = null;
while (reader.Read())
{
root.RaiseOnReadingOne(reader["name"]);
if ((!reader["type"].ToString().Trim().Equals("FS")) && (database.Options.Ignore.FilterFunction))
{
if (lastViewId != (int)reader["object_id"])
{
itemF = new Function(database);
itemF.Id = (int)reader["object_id"];
itemF.Name = reader["name"].ToString();
itemF.Owner = reader["owner"].ToString();
itemF.IsSchemaBinding = reader["IsSchemaBound"].ToString().Equals("1");
itemF.CreateDate = (DateTime)reader["create_date"];
itemF.ModifyDate = (DateTime)reader["modify_date"];
database.Functions.Add(itemF);
lastViewId = itemF.Id;
}
if (itemF.IsSchemaBinding)
{
if (!reader.IsDBNull(reader.GetOrdinal("referenced_major_id")))
database.Dependencies.Add(database, (int)reader["referenced_major_id"], itemF);
if (!String.IsNullOrEmpty(reader["TableName"].ToString()))
itemF.DependenciesIn.Add(reader["TableName"].ToString());
if (!String.IsNullOrEmpty(reader["DependOut"].ToString()))
itemF.DependenciesOut.Add(reader["DependOut"].ToString());
}
}
if ((reader["type"].ToString().Trim().Equals("FS")) && (database.Options.Ignore.FilterCLRFunction))
{
itemC = new CLRFunction(database);
if (lastViewId != (int)reader["object_id"])
{
itemC.Id = (int)reader["object_id"];
itemC.Name = reader["name"].ToString();
itemC.Owner = reader["owner"].ToString();
itemC.IsAssembly = true;
itemC.AssemblyId = (int)reader["assembly_id"];
itemC.AssemblyName = reader["assembly_name"].ToString();
itemC.AssemblyClass = reader["assembly_class"].ToString();
itemC.AssemblyExecuteAs = reader["ExecuteAs"].ToString();
itemC.AssemblyMethod = reader["assembly_method"].ToString();
itemC.ReturnType.Type = reader["ReturnType"].ToString();
itemC.ReturnType.Size = (short)reader["max_length"];
itemC.ReturnType.Scale = (byte)reader["Scale"];
itemC.ReturnType.Precision = (byte)reader["precision"];
if (itemC.ReturnType.Type.Equals("nchar") || itemC.ReturnType.Type.Equals("nvarchar"))
{
if (itemC.ReturnType.Size != -1)
itemC.ReturnType.Size = itemC.ReturnType.Size / 2;
}
database.CLRFunctions.Add(itemC);
lastViewId = itemC.Id;
}
}
}
}
}
}
}
if (database.CLRFunctions.Count > 0)
FillParameters(database, connectionString);
}
