Re-inventing The Wheel

Running with the SQL

I wasn't totally sure where to head next with the blog entry. First, I was going to do for Insert what I did for Modify: get rid of the SQL statement. But I couldn't think of a way to make that much nicer: realistically, you have to know all of the columns in a table before you do an insert. If you know that much, its not too much to ask someone to format their SQL themselves.

Then, I was going to show how you can do my modify with a different kind of SQL statement: UPDATE. I even had a cool title for the post and everything (coming up with post titles is sometimes more difficult than coming up the posts themselves, which is why I ended up with this really lame Van Halen reference. Oh well, I'll find a way to work that title in somewhere). But it turns out that it isn't quite the same: UPDATE statements only work for columns which are not key columns in the table (it took me a while to find this information; one of my tests kept failing and I had no idea why. Turns out this is a Windows Installer rule.).

So I think I'll skip ahead a couple of posts and make the next leap I wanted to make: converting these helper functions into little build tasks instead.

Let's take a closer look at our function signatures:

public static void InsertIntoMsi(Database msi, string sql)
public static void ModifyTableData(Database msi, string tableName, string columnName, object value, string whereClause)

Both of these will be easy to convert into tasks; and the task parameters are laid out for us. Let's do InsertIntoMsi first, since that will be the easiest.

using System;
using Microsoft.Build.Framework;
using Microsoft.Build.Utilities;
using WindowsInstaller;

namespace SetupProjects.Tasks
{
    public class InsertIntoMsi : SetupProjectTask
    {
        protected override bool ExecuteTask()
        {
        }
    }
}

We already have the Database parameter taken care of, so we only need to worry about the SQL statement:

namespace SetupProjects.Tasks
{
    public class InsertIntoMsi : SetupProjectTask
    {

        protected string sql;

        [Required]
        public string Sql 
        {
            get { return sql; }
            set { sql = value; }
        }

        protected override bool ExecuteTask()
        {
        }
    }
}

From here, it's a short step to the get this task working:

protected override bool ExecuteTask()
{
    Utilities.InsertIntoMsi(Msi, Sql);
    Msi.Commit();
    return true;
}

Looks like I made another boo-boo: Wasn't I supposed to write the tests first? Well, look at this code: where should we write unit tests for this? The answer: anywhere something can break. I already have a test for Utilities.InsertIntoMsi, and testing the Sql property seems like overkill. So for now, I'm going to skip additional tests. Although I reserve the right to change my mind.

There is one thing that bothers me about this a little bit, though: what does this task really have to do with inserting? The answer: nothing. This is basically the msbuild-equivalent of WiRunSql.vbs (minus the console output for SELECT. Don't worry.). I think this task (and the helper function) need to be renamed:

namespace SetupProjects.Tasks
{
    public class ExecuteSql : SetupProjectTask
    {

        protected string sql;

        [Required]
        public string Sql 
        {
            get { return sql; }
            set { sql = value; }
        }

        protected override bool ExecuteTask()
        {
            Utilities.ExecuteSql(Msi, Sql);
            Msi.Commit();
            return true;
        }
    }
}

Fortunately, this is really easy to do with the Refactor tool within VS: it even updates our tests for us (which continue to pass, thank you very much).

Now to create a ModifyTableData task. We start with a basic template:

using System;
using Microsoft.Build.Framework;
using Microsoft.Build.Utilities;
using WindowsInstaller;

namespace SetupProjects.Tasks
{
    public class ModifyTableData : SetupProjectTask
    {
        protected override bool ExecuteTask()
        {
        }
    }
}

And add relevant parameters to it:

using System;
using Microsoft.Build.Framework;
using Microsoft.Build.Utilities;
using WindowsInstaller;

namespace SetupProjects.Tasks
{
    public class ModifyTableData : SetupProjectTask
    {
        protected string tableName;
        protected string columnName;
        protected object value;
        protected string whereClause;

        [Required]
        public string TableName
        {
            get { return tableName; }
            set { tableName = value; }
        }

        [Required]
        public string ColumnName
        {
            get { return columnName; }
            set { columnName = value; }
        }

        [Required]
        public object Value
        {
            get { return value; }
            set { this.value = value; }
        }

        [Required]
        public string WhereClause
        {
            get { return whereClause; }
            set { whereClause = value; }
        }

        protected override bool ExecuteTask()
        {
            Utilities.ModifyTableData(Msi, TableName, ColumnName, Value, WhereClause);
            Msi.Commit();
            return true;
        }
    }
}

I have one problem with this: I'm a little concerned about the type I used for the Value parameter. To this point we've dealt primarily with strings, booleans, and TaskItems. To give this a whirl, let's try creating a build task that performs some modifications. We won't use a unit test for this, but will instead model the test after our unit tests.

Let's go back to our standard formula of a test setup project (SetupModifyStringTableDataTest). Add an excluded file called "PostBuild.proj", set the PostBuildEvent to:

msbuild.exe /t:ModifyStringTableTableDataTest /p:Configuration="$(Configuration)" /p:BuiltOutputPath="$(BuiltOuputPath)" /p:ProjectDir="$(ProjectDir)."  p:BuiltOutputDir="$(ProjectDir)$(Configuration)" $(ProjectDir)\PostBuild.proj"

Let's write the project file to do an insertion and a modification, just like our unit test did. This is easy enough:

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
    <UsingTask TaskName="ExecuteSql" AssemblyFile="$(MSBuildExtensionsPath)\SetupProjects.Tasks.dll" />
    <UsingTask TaskName="ModifyTableData" AssemblyFile="$(MSBuildExtensionsPath)\SetupProjects.Tasks.dll" />

    <Target Name="ModifyStringTableTableDataTest">
        <ExecuteSql 
            MsiFileName="$(BuiltOutputPath)"
            Sql="INSERT INTO `Error` (`Error`, `Message`) VALUES ('1', 'Hello world')"
        />
          <ModifyTableData 
            MsiFileName="$(BuiltOutputPath)"
            TableName="Error" 
            ColumnName="Message" 
            Value="A new message" 
            Where="`Message`='Hello world'" 
          />
    </Target>
</Project>

Now, cross your fingers and build the setup project:

PostBuild.proj(15,13): error MSB4069: The "System.Object" type of the "Value" parameter of the "ModifyTableData" task is not supported by MSBuild.
PostBuild.proj(15,13): error MSB4026: The "Value=A new message" parameter for the "ModifyTableData" task is invalid.
PostBuild.proj(11,11): error MSB4063: The "ModifyTableData" task could not be initialized with its input parameters.

Drat. It looks like my fears were confirmed. Let's step back and gameplan a little.

We could work around this by creating 2 different tasks: one to modify integer data, and another to modify string data. Of course, we had that before, and chose to collapse it into one method.

Let's take a closer look: we use the type of the input parameter to decide whether to use set_IntegerData or set_StringData. Perhaps there is a different way to decide which function to use. Naturally, there is.

When we have a view, it is possible to query that view for either the types or column names of the fields in the view, the "ColumnInfo", if you will. Reading the information about View.get_ColumnInfo shows that if the information starts with an i or a j, the value is an integer. So, it should be pretty easy to use this in a helper function in our utilities:

private static bool IsIntegerData(View view, int column)
{
    Record columnInfo = view.get_ColumnInfo(MsiColumnInfo.msiColumnInfoTypes);
    return columnInfo.get_StringData(1).StartsWith("i", StringComparison.OrdinalIgnoreCase) ||
           columnInfo.get_StringData(1).StartsWith("j", StringComparison.OrdinalIgnoreCase);
}

So, now lets use something we are sure is more MSBuild friendly to get this running:

public static void ModifyTableData(Database msi, string tableName, string columnName, string value, string whereClause)
{
    string sql = string.Format("SELECT `{0}` FROM `{1}` WHERE {2}", columnName, tableName, whereClause);
    View view = msi.OpenView(sql);
    view.Execute(null);

    Record record = view.Fetch();
    if (IsIntegerData(view, 1))
    {
        record.set_IntegerData(1, int.Parse(value));
    }
    else
    {
        record.set_StringData(1, value);
    }

    view.Modify(MsiViewModify.msiViewModifyReplace, record);
    view.Close();

    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(record);
    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(view);
}

Building yields build failures because I am no longer able to pass an int into this method. So, I changed all the ints to strings to get rid of all the compiler warnings. Our unit tests still pass, so let's try our actual postbuild project.

The build succeeds, and viewing the MSI shows that the error message was updated.

Let's setup a similar project file for integers:

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
    <UsingTask TaskName="ExecuteSql" AssemblyFile="$(MSBuildExtensionsPath)\SetupProjects.Tasks.dll" />
    <UsingTask TaskName="ModifyTableData" AssemblyFile="$(MSBuildExtensionsPath)\SetupProjects.Tasks.dll" />

    <Target Name="ModifyIntTableTableDataTest">
        <ExecuteSql 
            MsiFileName="$(BuiltOutputPath)"
            Sql="INSERT INTO `Error` (`Error`, `Message`) VALUES ('1', 'Hello world')"
        />
        <ModifyTableData 
            MsiFileName="$(BuiltOutputPath)"
            TableName="Error" 
            ColumnName="Error" 
            Value="2" 
            Where="`Error`=1" 
        />
    </Target>
</Project>

Setting up our postbuild event, building, and checking via Orca shows the Error column has indeed been updated to 2. So, how could one use these new tasks in a practical way? I could update EnableLaunchApplication to do this, but that's big enough for another post (believe me, the way I'm going to write it, it will be). Instead, let's solve 2 problems that for which I've supplied script-based work-arounds in the past:

• Setting the ARPINSTALLLOCATION property of the installer
• Turning one of the fields in a text box dialog into a password field

Setting ARPINSTALLLOCATION

I first heard about this issue when a user raised it on the Microsoft Connect site. The upshot is that when using MsiGetProductInfo, the install location can not be retrieved. This is because the setup project is not setting the ARPINSTALLLOCATION property.

Reading the docs about ARPINSTALLLOCATION indicates the property should be set via a custom action. So, we'll create a Type 51 custom action to set the ARPINSTALLLOCATION to be [TARGETDIR]. This custom action will be run after the InstallValidate item (based on a recommendation I found on the web). Let's add our standard PostBuildEvent to this project:

msbuild.exe /t:PostBuild /p:Configuration="$(Configuration)" /p:BuiltOutputPath="$(BuiltOuputPath)" /p:ProjectDir="$(ProjectDir)." /p:BuiltOutputDir="$(ProjectDir)$(Configuration)" $(ProjectDir)\PostBuild.proj"

We also need to add our project file (based in the project directory) PostBuild.proj:

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
    <UsingTask TaskName="ExecuteSql" AssemblyFile="$(MSBuildExtensionsPath)\SetupProjects.Tasks.dll" />

    <Target Name="PostBuild">
        <ExecuteSql 
            MsiFileName="$(BuiltOutputPath)"
            Sql="INSERT INTO `CustomAction` (`Action`, `Type`, `Source`, `Target`) VALUES ('SetARPINSTALLLOCATION', '51', 'ARPINSTALLLOCATION', '[TARGETDIR]')"
        />
        <ExecuteSql 
            MsiFileName="$(BuiltOutputPath)"
            Sql="INSERT INTO `InstallExecuteSequence` (`Action`, `Condition`, `Sequence`) VALUES ('SetARPINSTALLLOCATION', 'NOT Installed', '1401')"
        />
    </Target>
</Project>

One could then write a simple app using MsiGetProductInfo to verify that this behaves correctly.

Using Password Input

There is an article on MSDN relating how to do turn an edit field on a text box to display password characters. However, setup projects do not expose this property in the Textboxes custom dialogs. Fortunately, this is easy enough to accomplish using a postbuild step.

Let's start by creating a new Setup project, SetupPasswordInput. Add the Textboxes (A) dialog in the User Interface Editor. Initialize the properties on this dialog to have some appropriate values, and then build and install msi. You will end up seeing something like this:

To enable password inputs on the second textbox, it should be as easy as modifying the Attributes of this edit control in the Control table. Looking in the msi with Orca, we see that the relevant properties for this control are:

Column	Value
Dialog_	CustomTextA
Control	Edit2
Attributes	0x07

We want to make the attributes 0x00200007 (visible, enabled, sunken, and password enabled). Let's add our standard PostBuildEvent to this project:

msbuild.exe /t:PostBuild /p:Configuration="$(Configuration)" /p:BuiltOutputPath="$(BuiltOuputPath)" /p:ProjectDir="$(ProjectDir)." /p:BuiltOutputDir="$(ProjectDir)$(Configuration)" $(ProjectDir)\PostBuild.proj"

We also need to add our project file (again, based in the project directory) PostBuild.proj:

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
    <UsingTask TaskName="ModifyTableData" AssemblyFile="$(MSBuildExtensionsPath)\SetupProjects.Tasks.dll" />

    <Target Name="PostBuild">
        <ModifyTableData 
            MsiFileName="$(BuiltOutputPath)"
            TableName="Control" 
            ColumnName="Attributes" 
            Value="0x00200007" 
            Where="`Dialog_`='CustomTextA' AND `Control`='Edit2'" 
        />
    </Target>
</Project>

Now build our project. What's this? an error?

E:\MWadeBlog\SetupPasswordInput\PostBuild.proj(5,9): error : Input string was not in a correct format.

Are you kidding me? int.Parse can't handle hex strings? So lammmmqe (the q is silent)!

Update the Value field to be a decimal value:

    <Target Name="PostBuild">
        <ModifyTableData 
            MsiFileName="$(BuiltOutputPath)"
            TableName="Control" 
            ColumnName="Attributes" 
            Value="2097159" 
            Where="`Dialog_`='CustomTextA' AND `Control`='Edit2'" 
        />
    </Target>

Build again and install: