Feature request: Build package from snapshot & custom SQL

drhoten

I just started using SQL Packager a couple of hours ago, but unless I am missing something (which could be very likely) there are two features that I would like to see added.

Ideally I would like to avoid having to compare against "a pristine" version of the previous database or spending quite a bit of time tweaking and testing the VS project that is generated.

1. When upgrading an existing database, I would like the option of selecting a snapshot created from SQL Compare instead of having to select a previous version database.

Yes, I realize that this would only compare the schema - but in many cases this is all I need in order to deploy a database upgrade to our customers. We have customers that have database objects that are specific to their installation. So I will be creating snapshots of their current production database using the API we purchased, then using that snapshot to generate the change script for the schema.

Perhaps an additional option that allows me to compare the schema, data or both (the default) on the initial step of the wizard would be the way to approach this. Selecting the option for schema only, would then enable the option for selecting a snapshot instead of the previous database.

2. On the SQL Scripts step of the wizard.
a) Allow me to paste a SQL statement into a new fourth tab for "Custom SQL"
b) Override the SQL in either the SQL Schema or SQL Data tabs by letting me to manually select a script generated from either SQL Compare or SQL Data Compare.

Doug

Brian Donahue

Hello Doug,

You've given us quite a lot to think about here! The idea about importing a snapshot into Packager, though, that's a bit of a strange request. You have to compare a database to get a snapshot already so that just sounds like adding one extra step!

drhoten

Strange request - well, no one has ever accused me of being normal...

It would be an extra step if the previous version of the database was available on our network. Without going into too much detail, I want to

1. Generate a snapshot of a client's database (from their production server using the API) and then download it to our network.
2. Use the packager to compare the development version of the database to the snapshot in order to generate a package for just the schema changes.
3. Pass the packager off to a non-developer/non-dba person for performing the update.

Brian Donahue

Hi Doug,

You could use the Packager API to produce a package using a snapshot as the basis for the schema of the package. There is an example of how to use the API to make a package in the Toolkit help file. Rather than using Database.Register, you can use Database.LoadFromDisk() to put the database schema into memory.

drhoten

I haven't had a chance to look at the API yet, but now I am going to have to make the time.

Thanks.

Brian Donahue

Hi,

Here is the Packager example code, with the relevant bit replaced to base the database on a snapshot:

[c#]
using System;
using RedGate.SQL.Shared;
using RedGate.SQLPackager.Engine;
using System.IO;

namespace ConsoleApplication1
{
	class Class1
	{
		static void Main()
		{
			string serverName=".";
			string databaseName="WidgetDev";

			//get the execution block representing the schema
			RedGate.SQLCompare.Engine.Database database=new RedGate.SQLCompare.Engine.Database();
			database.LoadFromDisk(@"c:\temp\snappy.snp");
			
			//compare it with a null database to get a creation script
			RedGate.SQLCompare.Engine.Differences schema=database.CompareWith(null, RedGate.SQLCompare.Engine.Options.Default);

			//select all the differences

			foreach (RedGate.SQLCompare.Engine.Difference difference in schema)
			{
				difference.Selected=true;
			}

			RedGate.SQLCompare.Engine.Work work=new RedGate.SQLCompare.Engine.Work();
			
			//get the script
			work.BuildFromDifferences(schema, RedGate.SQLCompare.Engine.Options.Default, true);
			ExecutionBlock schemaBlock=work.ExecutionBlock;

			//now get the data script

			RedGate.SQLDataCompare.Engine.SqlProvider provider=new RedGate.SQLDataCompare.Engine.SqlProvider();
			RedGate.SQLDataCompare.Engine.Database database2=provider.GetDatabase(new RedGate.SQLDataCompare.Engine.SqlConnectionProperties(serverName, databaseName));
			RedGate.SQLDataCompare.Engine.ComparisonSession session=new RedGate.SQLDataCompare.Engine.ComparisonSession();

			RedGate.SQLDataCompare.Engine.TableComparisonSettings tablesToCompare=new RedGate.SQLDataCompare.Engine.TableComparisonSettings();

			foreach (RedGate.SQLDataCompare.Engine.Table table in database2.Tables)
			{
				//since we are always inserting data there is no need to define a primary key
				tablesToCompare.Add(new RedGate.SQLDataCompare.Engine.TableComparisonSetting(table.FullyQualifiedName, table.Fields, null));
			}

			session.CompareDatabases(database2, null, tablesToCompare);
			ExecutionBlock dataBlock=provider.GetMigrationSQL(session, true);
		}
	}
}