Wednesday 12 November 2008

Delphi.Net and the Internet

A few days ago, I posted about Delphi 2009. I was pleased with the additions that Codegear had made to the suite and speculated that it could be a possible migration route from pure Delphi over to .Net. I have had a think about this now and have sort of changed my mind. Using Delphi 2009 it seems to me that mature code written in Delphi could be used in any other .Net application - this includes Net powered websites.

So, instead of being a migration to .Net from Delphi, it is more along the lines of making use of good, solid Delphi code that has been developed over a number of years in a .Net envronment. I really like this idea and am interested to see how many Delphi developers would look into this. It certainly opens up a whole new world of possibility.

Friday 7 November 2008

Delphi and .Net

I have been looking at Delphi 2009 lately. Pascal was the first programming language I ever learned, I would imagine that this is the same for many people my age, so to take a look at Delphi after so long is a bit nostalgic for me.

However, in saying that, I have always stayed away from Delphi as a development tool - a lot of the work I do needs to be cross platform and this is something that hasnt always been do-able with Delphi. However, version 2009 not only includes updates to Delphi.Net, but also includes Generics and anyonmous methods - it also adds user definable build configurations. This might seem a petty thing to be pleased about, but for anyone developing or working with cross platform soloutions, this is a godsend.

At first glance, the .Net abilities of Delphi 2009 seem to suggest that Codegear would like people to migrate from regular Delphi over to .Net - the IDE supports developing VB.Net and C# applications as well as Delphi and Delphi.Net apps. Personally, I am a little surprised that Microsoft hasn't tried its own version of Delphi.Net and called it D# or something - there are so many systems out in the wild using Delphi in a Windows environment.

As it stands right now, there is nothing stopping you from migrating core logic into C#, for instance, and using this in a Delphi.Net UI. I think this is a nice stepping stone for systems written in Delphi to make the hop from the desktop and onto the web, but we shall just have to wait and see. There is a wide dirth of new languages soon to be available under the .Net framework like Python, Ruby and F#, so it looks like the trend is moving towards dynamic languages for the time being.

Saturday 4 October 2008

Generating SQL Server Documentation with NAnt and RedGate

The other day I posted a method by which you can automatically sync schema from one MS SQL Server db to another. While I was looking at the RedGate tools, I found a handy utility called SQL Doc. This doesnt have an api (and doesnt really need one) it consits of a GUI and a commandline client, which is good.

Basically, this app allows you to create a set of documentation on any given db including the setup scripts needed to replicate that particular db. It can output this report as a set of web pages, a .chm or a .doc. I thought that this was pretty cool, it represents a really good way of documenting the db at certain points through out its life. One good use of this tool would be to generate documentation prior to a new release (or just after). That way, you can maintain a handy record of the changes going into your db that can be published on your intranet/wiki etc.

Of course, as a GUI tool, this isnt so great for CI, so whacking the commandline parameters into a NAnt script will make it slot into your automated process nicely. Plus, it's an easy win - the task itself is very easy to write:

using System;
using System.Text;
using NAnt.Core;
using NAnt.Core.Tasks;
using NAnt.Core.Attributes;

namespace Custom.NantTasks.RedGate
{
[TaskName("sqldoc")]
class SQLDoc : ExternalProgramBase
{
private string _projectPath;
[TaskAttribute("project", Required = true)]
public string ProjectPath
{
get { return _projectPath; }
set { _projectPath = value; }
}
private string _reportType;
[TaskAttribute("report-type", Required = true)]
public string ReportType
{
get { return _reportType; }
set { _reportType = value; }
}
private string _password;
[TaskAttribute("password", Required = true)]
public string Password
{
get { return _password; }
set { _password = value; }
}
private string _exePath;
[TaskAttribute("app-path", Required = true)]
public string ExePath
{
get { return _exePath; }
set { _exePath = value; }
}
public override string ExeName
{
get
{
return _exePath;
}
set
{
ExeName = value;
}
}

public override string ProgramArguments
{
get { return "/project:\"" + ProjectPath + "\"" + " /password:" + Password +" /filetype:" + ReportType; }
}
protected override void ExecuteTask()
{
Log(Level.Info, "Generating SQL documentation");
Log(Level.Info, "Running the following command: " + ProgramArguments.ToString());
base.ExecuteTask();
}
}
}


And thats it!! There are, however, a few caveats. In order to automate this, you first need to set up a project file through the GUI. Unfortunately, there doesnt seem to be a commandline route to do this. Once you have configured the project and saved it, you basically get an XML file - which is very handy as you could use NAnt to alter the values held within it (I havent done this). The build file would look a little something like this:
<target name="doc">
<sqldoc project="C:\WidGetDev.sqldoc" password="password" report-type="html" app-path="C:\Program Files\Red Gate\SQL Doc 2\SQLDoc.exe" />
</target>

Unfortunately, there is no way to specify a username on the commandline, only a password, which I guess is good enough. Once done, you can run this as a NAnt task pretty much anywhere. I did this on Vista, so I got some interesting security notifications pop up. I also got a very annoying bug following a sucessful build. For some reason, SQL Doc would fail to exit correctly (exiting on code 77, which means that the user does not have permission), however this just went away for me in the end... which was odd. Regardless, once the task has ran and completed, you will be able to pick up your documentation, SQL Doc places it in a location specified during the project creation, but you can change this whenever you like. After the task is run, just do what ever you fancy with the results, my idea was to post them to a wiki, but you should be able to attach them as a build artefact in which ever CI server you are using (if any).

Friday 3 October 2008

Sync Schema with NAnt and RedGate SQL Compare

This was a problem that was bothering me a little throughout last year. On my previous blog I had a very basic NAnt task that made use of RedGate's SQL Compare API. RedGate makes some fantastic tools for use with MS SQL Server, if you have to spend time working on multiple databases, then I can't recommend it enough. For more infor mation, pop over to their website: http://www.redgate.com/.

First up, you need to have a little think about the problem at hand - you have a number of databases that you need to sync. SQL Compare gives you a nice GUI to do all of this, which is fine if you only have one db to worry about (or at a stretch, a couple.) but if you have a large amount of db's spread across multiple environments this will become a problem very quickly. To get around this problem, you can make use of the command line version of SQL Compare or you can do what I did and make use of the API to automate things just a little further. One clear benefit I have noticed so far is that using the API over the command line version results in a quicker operation.

Another thing to consider is this: If you have more than one db to manage, you are going to have to put the login credentials somewhere. This will potentially panic your IT manager or administrator - they dont really want login credentials floating around on machines, which is a very valid and fair point. Froms the example I have below, you will see that I am managing my login credentials by placing them in NAnt's app.config. This will send a shiver of fear down some peoples spines, as this means that they are there for all to see in plain text - but, dont panic. It is a very simple thing to encrypt them so that no one can see them. Just plonk all your db credentials in there, encrypt it and make sure that this .config is deployed correctly when ever the details change.

I am sure there will be other considerations to take into account depending on your own setup (there always is). But for me, this is all I needed. So, lets move on - what do I need to capture in my NAnt build script? Well, if all the db credentials are stored away in a .config file then all I need to do is retrieve them by name. This means that in my NAnt script, I just need to specify the source and destination db's based on the key value, e.g;

<add value="db-server-name, db-name, username, password" key="test">

It's up to you if you want this to be in plain text in NAnt's config file, personally I would encrypt it - but thats just me.

Now, onto the task. As I said above, I am only getting two attributes: the source and destination database names. This means the task would look a little like this:

[TaskName("schema-sync")]
public class SchemaSync : Task
{
public Sync sync = new Sync();
private string _sourceDBName;
[TaskAttribute("source", Required = true)]
public string SourceDBName
{
get { return _sourceDBName; }
set { _sourceDBName = value; }
}
private string _destinationDBName;
[TaskAttribute("destination", Required = true)]
public string DestinationDBName
{
get { return _destinationDBName; }
set { _destinationDBName = value; }
}

Now, we get onto the good stuff - getting the sync underway!

I am going to present this in the way I figured it out - I am fairly sure that a lot of people reading this will want to rejig it to fit there own needs. Lets start off getting a collection of differences between two databases:



private Differences GetDifferences(string dbServerNameSource,
string dbUserNameSource,
string dbPasswordSource,
string dbNameSource,
string dbServerNameDest,
string dbUsernameDest,
string dbPasswordDest,
string dbNameDest)
{
Differences differences = null;
Database db1 = new Database();
Database db2 = new Database();
db1.Register(new ConnectionProperties(dbServerNameSource,
dbNameSource,
dbUserNameSource,
dbPasswordSource),
Options.Default);
db2.Register(new ConnectionProperties(dbServerNameDest,
dbNameDest,
dbUsernameDest,
dbPasswordDest),
Options.Default);
differences = db1.CompareWith(db2, Options.Default);
foreach (Difference difference in differences)
{
difference.Selected = ExcludeObject(difference);
}
return differences;
}

This method returns a collection of differences between two database's as supplied through the NAnt script. Note that this doesnt actually affect any changes at all on your db's, all it does it find out just how many differences there are. Next, we want to build the SQL needed to sync the schema from DB1 to DB2, this is fairly simple:


private ExecutionBlock ScriptDifferences(Differences differences)
{
Work work = new Work();
work.BuildFromDifferences(differences, Options.Default, true);
ExecutionBlock block = work.ExecutionBlock;
return block;
}

The returned block now contains the SQL script that will alter the destination db. To execute this SQL, we need the following:


private BlockExecutor SyncDifferences(string dbServerNameDest,
string dbNameDest,
string dbUserNameDest,
string dbPasswordDest,
ExecutionBlock block)
{
BlockExecutor executor = new BlockExecutor();
executor.ExecuteBlock(block,
dbServerNameDest,
dbNameDest,
true,
dbUserNameDest,
dbPasswordDest);
return executor;
}

And thats it, this will effectively sync db1 to db2 for you. Incidentally, this seemed to me to be a lot simpler that fudging a script together using the commandline Compare app... You will notice that I am also filtering out some database's from the compare it self, the following will do this for you:



private static bool ExcludeObject(Difference difference)
{
if (difference.DatabaseObjectType != ObjectType.User &&
difference.DatabaseObjectType != ObjectType.Role &&
difference.DatabaseObjectType != ObjectType.Queue &&
difference.DatabaseObjectType != ObjectType.Service &&
SpecialObjects(difference) == false)
{
return true;
}
return false;
}
private static bool SpecialObjects(Difference difference)
{
if ((difference.DatabaseObjectType == ObjectType.Table &&
difference.Name.ToLower().StartsWith("[dbo].[aspnet_sql"))
(difference.DatabaseObjectType == ObjectType.StoredProcedure &&
difference.Name.ToLower().StartsWith("[dbo].[aspnet_sql"))
(difference.DatabaseObjectType == ObjectType.StoredProcedure &&
difference.Name.ToLower().StartsWith("[dbo].[sqlquery")))
{
return true;
}
return false;
}

To tie it all up, I have one public method, which is called from my task class:


public object SyncDB(string dbServerNameSource,
string dbUserNameSource,
string dbPasswordSource,
string dbNameSource,
string dbServerNameDest,
string dbUserNameDest,
string dbPasswordDest,
string dbNameDest)
{
object obj = null;
Differences diff = GetDifferences(dbServerNameSource,
dbUserNameSource,
dbPasswordSource,
dbNameSource,
dbServerNameDest,
dbUserNameDest,
dbPasswordDest,
dbNameDest);
ExecutionBlock script = ScriptDifferences(diff);

obj = SyncDifferences(dbServerNameDest,
dbNameDest,
dbUserNameDest,
dbPasswordDest,
script);
return obj;
}

Et voila, we now have enough code to automatically sync schema from one db to another. All that is left to do is to fit this all into my NAnt task. Below is the complete code I used:


using NAnt.Core;
using NAnt.Core.Attributes;
using NAnt.Core.Tasks;
using NAnt.Core.Util;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Text;
using RedGate.Shared.SQL.ExecutionBlock;
using RedGate.SQLCompare.Engine;
using Custom.NantTasks.RedGate.Compare;

namespace Custom.NantTasks
{
[TaskName("schema-sync")]
public class SchemaSync : Task
{
public Sync sync = new Sync();
private string _sourceDBName;
[TaskAttribute("source", Required = true)]
public string SourceDBName
{
get { return _sourceDBName; }
set { _sourceDBName = value; }
}
private string _destinationDBName;
[TaskAttribute("destination", Required = true)]
public string DestinationDBName
{
get { return _destinationDBName; }
set { _destinationDBName = value; }
}
protected override void ExecuteTask()
{
string dbServerNameSource;
string dbNameSource;
string dbUserNameSource;
string dbPasswordSource;
GetSourceDetails(out dbServerNameSource,
out dbNameSource,
out dbUserNameSource,
out dbPasswordSource);
string dbServerNameDest;
string dbNameDest;
string dbUserNameDest;
string dbPasswordDest;
GetDestDetails(out dbServerNameDest,
out dbNameDest,
out dbUserNameDest,
out dbPasswordDest);
try
{
SyncDB(dbServerNameSource,
dbNameSource,
dbUserNameSource,
dbPasswordSource,
dbServerNameDest,
dbNameDest,
dbUserNameDest,
dbPasswordDest);
}
catch (Exception e)
{
Log(Level.Error, "Sync failed");
throw new BuildException(e.Message);
}
}

private void SyncDB(string dbServerNameSource,
string dbNameSource,
string dbUserNameSource,
string dbPasswordSource,
string dbServerNameDest,
string dbNameDest,
string dbUserNameDest,
string dbPasswordDest)
{

Log(Level.Info, "Syncing {0} to {1}", SourceDBName, DestinationDBName);
try
{
sync.SyncDB(dbServerNameSource,
dbUserNameSource,
dbPasswordSource,
dbNameSource,
dbServerNameDest,
dbUserNameDest,
dbPasswordDest,
dbNameDest);
Log(Level.Info, "Sync complete");
}
catch (Exception e)
{
throw new BuildException(e.Message);
}
}

private void GetDestDetails(out string dbServerNameDest,
out string dbNameDest,
out string dbUserNameDest,
out string dbPasswordDest)
{
Log(Level.Info, "Getting credentials for: " + DestinationDBName);
string[] destDetails =
ConfigurationManager.AppSettings.GetValues(DestinationDBName)[0].ToString().Split(',');
dbServerNameDest = destDetails[0].Trim();
dbNameDest = destDetails[1].Trim();
dbUserNameDest = destDetails[2].Trim();
dbPasswordDest = destDetails[3].Trim();
}

private void GetSourceDetails(out string dbServerNameSource,
out string dbNameSource,
out string dbUserNameSource,
out string dbPasswordSource)
{
string[] sourceDetails =
ConfigurationManager.AppSettings.GetValues(SourceDBName)[0].ToString().Split(',');
Log(Level.Info, "Getting credentials for: " + SourceDBName);
dbServerNameSource = sourceDetails[0].Trim();
dbNameSource = sourceDetails[1].Trim();
dbUserNameSource = sourceDetails[2].Trim();
dbPasswordSource = sourceDetails[3].Trim();
}
}
}



GetDestinationDetails() and GetSourceDetails() handle the retrieval of db credentials from NAnt's .config file. To put this new task into use, the build script would look a little like this:

<?xml version="1.0" encoding="utf-8" ?>
<project name="Test" default="test">
<target name="Test">
<schema-sync source="WidGetDev" destination="WidGetLive" />
</target>
</project>

If an error is encountered, then this will be thrown as a build exception and fail the build (which is nice). If you are syncing a number of db's at one time it means the operation will stop at the last successful sync. If you have a number of db's to sync, then it would be a good idea to us NAnt's foreach over a list of db's that you want to sync. This lets you skip over anything you dont want to go from the source to the destination (like a db full of environment variables for instance).

And thats pretty much it, one simple and fairly quick way to use the power of RedGate's SQL Compare engine with NAnt. Thus giving you a nice opportunity to use CI servers like CruiseControl.Net or TeamCity etc.

Below is the complete code for the schema compare:


using NAnt.Core;
using NAnt.Core.Attributes;
using NAnt.Core.Tasks;
using NAnt.Core.Util;
using RedGate.Shared.SQL.ExecutionBlock;
using RedGate.SQLCompare.Engine;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Custom.NantTasks.RedGate.Compare
{
public class Sync
{
private Differences GetDifferences(string dbServerNameSource,
string dbUserNameSource,
string dbPasswordSource,
string dbNameSource,
string dbServerNameDest,
string dbUsernameDest,
string dbPasswordDest,
string dbNameDest)
{
Differences differences = null;
Database db1 = new Database();
Database db2 = new Database();
db1.Register(new ConnectionProperties(dbServerNameSource,
dbNameSource,
dbUserNameSource,
dbPasswordSource),
Options.Default);
db2.Register(new ConnectionProperties(dbServerNameDest,
dbNameDest,
dbUsernameDest,
dbPasswordDest),
Options.Default);
differences = db1.CompareWith(db2, Options.Default);
foreach (Difference difference in differences)
{
difference.Selected = ExcludeObject(difference);
}
return differences;
}
private ExecutionBlock ScriptDifferences(Differences differences)
{
Work work = new Work();
work.BuildFromDifferences(differences, Options.Default, true);
ExecutionBlock block = work.ExecutionBlock;
return block;
}
private BlockExecutor SyncDifferences(string dbServerNameDest,
string dbNameDest,
string dbUserNameDest,
string dbPasswordDest,
ExecutionBlock block)
{
BlockExecutor executor = new BlockExecutor();
executor.ExecuteBlock(block,
dbServerNameDest,
dbNameDest,
true,
dbUserNameDest,
dbPasswordDest);
return executor;
}
private static bool ExcludeObject(Difference difference)
{
if (difference.DatabaseObjectType != ObjectType.User &&
difference.DatabaseObjectType != ObjectType.Role &&
difference.DatabaseObjectType != ObjectType.Queue &&
difference.DatabaseObjectType != ObjectType.Service &&
SpecialObjects(difference) == false)
{
return true;
}
return false;
}
private static bool SpecialObjects(Difference difference)
{
if ((difference.DatabaseObjectType == ObjectType.Table &&
difference.Name.ToLower().StartsWith("[dbo].[aspnet_sql"))
(difference.DatabaseObjectType == ObjectType.StoredProcedure &&
difference.Name.ToLower().StartsWith("[dbo].[aspnet_sql"))
(difference.DatabaseObjectType == ObjectType.StoredProcedure &&
difference.Name.ToLower().StartsWith("[dbo].[sqlquery")))
{
return true;
}
return false;
}


public object SyncDB(string dbServerNameSource,
string dbUserNameSource,
string dbPasswordSource,
string dbNameSource,
string dbServerNameDest,
string dbUserNameDest,
string dbPasswordDest,
string dbNameDest)
{
object obj = null;
Differences diff = GetDifferences(dbServerNameSource,
dbUserNameSource,
dbPasswordSource,
dbNameSource,
dbServerNameDest,
dbUserNameDest,
dbPasswordDest,
dbNameDest);
ExecutionBlock script = ScriptDifferences(diff);

obj = SyncDifferences(dbServerNameDest,
dbNameDest,
dbUserNameDest,
dbPasswordDest,
script);
return obj;
}
}
}