In my last two blog articles (ICE Validation: Part 1 and ICE Validation: Part 2) we discussed the need for validation of the MSI databases and a means by which you could include your own custom rules into the standard Internal Consistency Evaluator (ICE) validation checking mechanism. This time we will extend the thinking to provide a means of automatically resolving validation issues creating a Manipulating-ICE or MICE.
Aim
The obvious extension to the error flagging phase of the ICE validation is the error correction phase, which has, up until now, been a manual process. We need to remove the need for a technician performing validation to personally interpret the results. We seek to automate the resolution, fixing hundreds of instances in the same time it would take to resolve one issue manually.
To do this we will perform the following steps:
- Make a copy of the original MSI database.
- Inspect some element of the MSI database iterating through records as necessary.
- Perform resolution by updating the copy of the database.
- Commit all changes and compare to the original database to generate the transform.
This demonstration will be performed in Visual Basic Script for ease.
During this demonstration we will take a very simple example of a custom packaging Best Practice, that of ensuring that all packages are deployed per-machine by setting the value of the property ALLUSERS to 1.
Copy the Original Database
In the following code snippet we see (on line 18) the command line argument being processed to determine the working directory of the MSI which was dropped onto the VBScript. After that both the base MSI and the changed MSI are assigned names. The VBScript File System Object is used to duplicate the given MSI. Then, using the Windows Installer Object, both databases are opened (the base in read-only and the changed in read-write transact mode).
Inspect the Current Value of the ALLUSERS Property
In this snippet we see how to interact with the tables; it is quite similar to the way in which you talk to a SQL Database. First we create a view, then we execute that view and finally fetch records from the executed view.
The key to getting this right is the types of quotes used on line 42. The double quotes (" ") enclose the entire Select statement. We sometimes break out of that literal string to include VBScript variables calculated or parameterised earlier, and in those cases we concatenate them into the line using the ampersand (&). The single vertical quotes ('') are used to refer to strings within the Select statement. In our example we are looking for the string value of a property to be ALLUSERS. Any columns which are defined as numeric, such as the Root column in the Registry table, do not use the single vertical quotes. Finally the single angled quotes (` `) are used to escape column and table names; this is not necessary unless the column of table name clashes with an internal MSI-SQL keyword—for example, in the Registry table there is a column called Key, which is also a MSI-SQL keyword.
To highlight the problem of not escaping consider a somewhat contrived custom table called FROM containing a column called SELECT and wanting to read its value when a second column called WHERE has the value 1. The un-escaped Select statement would read:
“SELECT SELECT FROM FROM WHERE WHERE=2” and would fail miserably, whereas the escaped equivalent:
“SELECT `SELECT` FROM `FROM` WHERE `WHERE`=2” would work correctly.
Although this is a contrived example unless we learn all MSI-SQL keywords this could also happen in more realistic examples. So to be on the safe side we should escape all column and table names.
Perform the Resolution
In this snippet we perform the change necessary to fix our issue. In this case we have some alternate cases: the ALLUSERS value may not exist or it may exist with the non-prescribed value of 1.
The If statement on line 45 is true only if there is an ALLUSERS property in the MSI. We go on to extract its value using the “.StringData(1)” property. This extracts from the current record the value of the column in position 1 and returns it as a string representation. If the value does not already equal 1 we set it to 1 and then on line 56 we create a new view. The MSI-SQL statement here updates the property table and embeds the VBScript value of AllUsers. Note we must still execute that view but we do not proceed to fetch records from it.
The Else statement that begins on line 61 is true only if the earlier select statement failed to collect any records which means there is no ALLUSERS property (hence this is a non-standard per-user deployment). So in this case we should just insert the missing record into the property table.
Commit Changes and Make a Transform
Technically, at this point, we have completed our changes but since we opened the database in transact mode just closing the database will rollback any un-committed changes. So before we do that we must commit our changes.
In our last code snippet we see how to compare the changed database with the original read-only database to create a transform. This allows us to use this same script on a vendor MSI and express the same change without modifying the original vendor package. The line 149 shows how to make the transform more generic and not care too much when applying the transform.
Summary
In this article we have seen how to perform a simple check against the MSI database and fix it. Although fairly trivial, this example can easily be extended to perform more complex checks on different and multiple tables retrieving sets of records instead. However the resolution is just as simple and when applied to large packages generate massive time savings over the same manual operation.