Prevent dropping of objects

Justin Spindler

I am using the SQL Toolkit to create a database update wizard that my company will use to propogate database schema changes to our clients that accompany new versions of our software. Our solution is designed in such a manner that we permit our clients to make their own schema changes, such as adding new tables or adding columns onto existing tables. I need this update system to respect their changes while merging in our changes.

From what I've seen I do believe that this can be accomplished fairly easily with top level objects, such as tables and stored procedures. However, I cannot determine how I could prevent the comparison from attempting to drop new columns that it finds in the target database that are not found in the source database. I do want it to make any other necessary change to that particular table.

Is there anything I can do to select differences at a field-by-field basis?

Thanks

Brian Donahue

Hi Justin,

SQLCompare.Engine doesn't give you the granularity to omit column changes from a database script. The closest I can seem to get is to omit the object entirely from the script if there are only column differences, otherwise it looks like you'll have to go with the solution that you had mentioned earlier.

You can omit the object from synchronization by checking only for column diffs and then setting the difference's selected property to false. In the sample code provided with SQL Toolkit, you'll see a loop which sets the difference's Selected property to true immediately after calling the CompareWith method. Change it to look something like this:

   foreach (Difference d in obDatabaseDifferences) 
         { 
            d.Selected = true; 
            if (d.Type==DifferenceType.Different && d.DatabaseObjectType==ObjectType.Table) 
            { 
               RedGate.SQLCompare.Engine.Table t1= (RedGate.SQLCompare.Engine.Table) d.ObjectIn1; 
               RedGate.SQLCompare.Engine.Table t2= (RedGate.SQLCompare.Engine.Table) d.ObjectIn2; 
               if (!t1.Fields.Equals(t2.Fields)) 
                  d.Selected=false; 
               } 
             
         }

Hopefully you will find this useful!

I'll double-check with the programmers on Monday to see if there may be a better way and also see if they are implementing anything new in v4 of the engine.

Justin Spindler

Brian,

That would work well in situations where the only difference is that the target database has a field or index that the source database does not, and I will implement those changes. However, if the source database also has a field difference that should be propogated then the table change must still occur.

I am currently looking into scanning the scripts themselves to attempt to remove any field or index dropping statements. I noticed that the full script will occasionally drop objects, such as constraints, in order to facilitate the migration. Do you know if columns or indexes are dropped for this purpose?

Also, what triggers a table rebuild? I will also have to attempt to modify those scenarios in order to prevent the new table from losing the fields.

Please put me in for a feature request to permit selecting and unselecting of the individual changes that comprise a table update.

Brian Donahue

Hello Justin,

If you want to push new columns 'one way', maybe comparing the number of Fields would work. For instance, if the Fields.Count in ObjectIn1 > Fields.Count in ObjectIn2 then Selected=true. Something like that.

SQL Compare drops whatever needs to be dropped. If a primary key depends on a column that you're dropping, we need to drop the PK first to prevent an error. If a foreign key and primary key depend on a column, we have to drop the FK, then the PK, then the column.

If you want to get to the information about what SQL Compare is about to do in the migration script, you can access the warnings and summary from RedGate.SQLCompare.Engine.Work.Warnings and Messages properties. Table rebuilds will be reported as a warning.

Some things that can force a table rebuild are inserting columns when the force column order to be identical option is set, or when you change an identity column to not being an identity column and vice-versa. There may be more that I'm not aware of.

mikkelbm

Will it in future releases be possible to be more specific in what you want dropped?

We have the same problem/situation as the subject of this thread.

For us it's not enough to check the length of the fields in each table.

Imagine we have a customer-specific column in table2 (created specific to this customer) we want to keep, but in the same, we have deleted a column and afterwords added a new column to table1 (which is our snapshot). In this situation, the length of the two tables will be the same, but there are new columns in table1 which is necessary for our application to run.

It could be a very nice feature, if it's in future releases would be possible to tell the compare-mechanism that is should jump over specific columns - instead of, as it is now, where the only two possibilities are: Difference.Selected = true or Difference.Selected = false;


Hope you understand?

Thanks

Mikkel Bo Mikkelsen
Software Developer
TallyFlex Software ApS
Denmark

Brian Donahue

Loud and clear. I will make this suggestion.

Justin Spindler

Hey Brian,

This is probably outside of the scope of support, but I'm trying to develop a workaround to this issue and I'm running into a handful of issues.

Instead of attempting to modify the generated scripts themselves, which will be a cumbersome task, I decided to see if I could fake the engine by using .NET reflection to crack into the underlying ArrayLists and copying the new fields from the target database into the source database snapshot.

I am successful in cloning the Field class as well as adding it to the target table. Using the Work.ScriptObjects() method I am able to see that SQL Compare does think that the field belongs to the target table. However, the BuildFromDifferences() method still generates a script to drop that field.

Any assistance you could provide me with this project would be greatly appreciated. If you want, I can send you the code I am using with reflection to clone the Field class.

Brian Donahue

Hi Justin,

Sure--you can send the code. You may be asking for trouble with the code reflection, though, because most of the stuff in the Engine is obfuscated. I think that will make things considerably more difficult.

The key to getting a different synchronization script is to alter the collection of database differences generated by CompareWith(). Try as I might I cannot change or add anything because it all seems to be read-only.

Maybe it would work better to construct your own string of SQL, intercept the 'naughty bits' of SQL and replace them with your own strings? For instance, in this function, I am going to return a string called sMigrationSQL:

obWork.BuildFromDifferences(obDatabaseDifferences, enOptions, false);
for(int i=0; i<obWork.ExecutionBlock.BatchCount; i++) 
			{
				Batch b = obWork.ExecutionBlock.GetBatch(i);
				if (b.Contents.StartsWith("ALTER TABLE") && b.Contents.IndexOf("DROP")>-1) 
				{
					sMigrationSQL+="ALTER TABLE [xyz] ADD COLUMN [something]";
				}
				else sMigrationSQL+=b.Contents;
			}

Justin Spindler

Brian,

Actually, I think I worked it out. My mistake was that I was adding the field to the private ArrayList in the SerializableCollection, but not the HybridDictionary. As a result, the field would appear when scripting the object, but not when comparing the object.

Obfuscation does make this task more difficult, but because the underlying types are standard .NET types only their names are obfuscated. So if I enumerate all of the nonpublic instance fields of the SerializableCollection I will pass over both the private ArrayList and HybridDictionary and can extract them. I then use reflection to create a new Field class instance and enumerate the private instance fields to copy them into the new instance, with the exception of the Table field which I populate with the table from the target database.

Here is the code that I am using. I haven't yet really "prettied it up," but it's functional. This needs to be called prior to performing Work.BuildDifferences():

private void ScanTables(RedGate.SQLCompare.Engine.Table schemaTable, RedGate.SQLCompare.Engine.Table targetTable) {
	foreach(Field field in targetTable.Fields) {
		Field schemaField = schemaTable.Fields[field.Name];
		if(schemaField == null) {
			AddField(schemaTable, field);
		}
	}
}

private void AddField(RedGate.SQLCompare.Engine.Table targetTable, Field field) {
	ArrayList list = null;
	HybridDictionary dict = null;
	foreach(FieldInfo fieldInfo in typeof(SerializableCollection).GetFields(BindingFlags.Instance | BindingFlags.NonPublic)) {
		if(fieldInfo.FieldType == typeof(ArrayList)) {
			list = (ArrayList)fieldInfo.GetValue(targetTable.Fields);
		}
		if(fieldInfo.FieldType == typeof(HybridDictionary)) {
			dict = (HybridDictionary)fieldInfo.GetValue(targetTable.Fields);
		}
	}
	if(list == null || dict == null) {
		return;
	}
	Field copy = CopyField(field, targetTable);
	list.Add(copy);
	dict.Add(copy.Name, copy);
}

private static Field CopyField(Field source, RedGate.SQLCompare.Engine.Table targetTable) {
	ConstructorInfo ctor = typeof(Field).GetConstructor(BindingFlags.Instance | BindingFlags.NonPublic, null, new Type[] { typeof(string) }, null);
	if(ctor == null) {
		return null;
	}
	Field target = (Field)ctor.Invoke(new object[] { source.Name });
	foreach(FieldInfo field in typeof(Field).GetFields(BindingFlags.Instance | BindingFlags.NonPublic)) {
		if(field.FieldType == typeof(RedGate.SQLCompare.Engine.Table)) {
			field.SetValue(target, targetTable);
		}
		else {
			object value = field.GetValue(source);
			field.SetValue(target, value);
		}
	}
	return target;
}

This might be useful for others who are in the same predicament that I am in.