PDA

View Full Version : Ensuring SQL Scripts run conditionally



DMorisseau
11-18-2005, 04:35 PM
Here's an interesting challenge--I've thought about this for the past couple of hours and don't see a clear way to handle this, so I thought I'd ask the group for suggestions!

I am in the process of bringing our installations up to IS11. The latest project I am working on splits off a subset the old IS Pro 7 setup into its own installable product. The product has approximately 60+ SQL scripts.

To speed up the installation processes, the person who wrote the old projects created a database table to store the script name and the last run date. The installer would test each script included in the setup against the table and run only those which were new or modified since the last run date. The table would then be updated so that the next product update would skip those scripts if they did not change (All product updates were full ones to ensure that all db changes would be applied)

I have been directed that in splitting off this subset, I should keep this method in mind (one script in particular takes several minutes to run). Is there a way I can replicate this in IS11, or will I have to switch this project to an InstallScript project to do this properly.

MichaelU
11-18-2005, 06:13 PM
I think you'll find builtin functionality equivalent to what you're looking for in the "Schema Version" setting on each script. The KB article INFO: How Does SQL Script Schema Version Work? (http://support.installshield.com/kb/view.asp?articleid=Q111714) describes this in more detail than the help does.

DMorisseau
11-19-2005, 01:46 PM
I had looked into using it, but the hitch for me is that if I apply this to a database where these scripts had been run once already under the old regime, they will be run again when they don't need to be.

I like the idea of the schema versioning on scripts, but for my purposes the schema version it should be testing against is the version of the script, NOT the version of the database.

Do you have any other thoughts on how I might replicate the old functionality using Basic MSI techniques? I suppose if I had to I could bring the old InstallScript project up to IS11, but the Basic MSI project I've built is so much simpler (fewer scripts, no helper dlls)

MichaelU
11-21-2005, 11:31 AM
I see your point, that migrating to this sort of solution won't be trivial. However there really isn't builtin support for the method you desire. It should be possible to do it by associating each script with a separate component, and conditionalizing the component's install based on anything you want. You could easily do system searches for registry keys, or other custom actions for other methods, to set properties for those conditions.

Or rather each condition/search would be easy; if all 60 are really mutually independent, and you really would need 60 different conditions on 60 different variables, this may be more pain than it's worth as a system search; at least as a custom action you could set more than one property at a time.

DMorisseau
11-21-2005, 11:40 AM
How about this:

Right now each of the sql scripts are associated to their own components, but the files aren't actually "copied down" as themselves, but as a cryptic filename which is extracted during installation to the temp folder.

What if I attach the original script under the file view of each sql component, then create a custom action that cycles through each SQL file in the temp folder and use SQL-DMO to run the test I want to do (mod date of file > date last run stored in DB). The only thing I'm not sure of is what MSI function to invoke or property to set on the component to ensure it does not run, or when to best place in sequence.

The only hitch I see to this is that my setup permits multiple databases to be selected, I could only do this test once for the whole setup.

MichaelU
11-21-2005, 12:10 PM
The "canonical" MSI method for this may be to populate a set of properties like MYMOD_DATE_SQL01 and MYDB_DATE_SQL01, with a condition on the component like MYMOD_DATE_SQL01 > MYDB_DATE_SQL01, and a custom action that does the SQL and filesystem queries to populate those properties in comparable form. That puts all the information front and center, making it easy to understand the semantics.

More releastic may be to have a MYSKIP_SQL01 (defaulting to unset), a condition of NOT MYSKIP_SQL01, and a custom action which does the queries and sets the property if it should be skipped.

If you come up with the right naming scheme, it may even be possible to automate either of these with your temp folder filescan, and the only tedious manual step would be naming the component and putting on its condition. It's definitely an interesting problem, and I'd love to hear what solution you end up with.

DMorisseau
11-21-2005, 07:03 PM
Maybe I'm being a little dense, but I tried adding the corresponding SQL file under the Files branch of the Sqlcomponent and the file did not extract out. Should it, or would I need to create separate file components for these files (UGH!)

I wasn't being patient enough..the files did copy down, but it seems as if they copy down during the ISSQLServerInstall custom action (of course that's too late).

What can I do?

DMorisseau
11-22-2005, 12:36 PM
Ok, here's where I've gotten so far:

1. In the Files View of five (5) of my SQL Script components, I added the corresponding sql file.

2. I created a Deferred Custom Action and scheduled it after InstallFiles. This custom action creates the list of SQL files and loops through it. As the list is built, I create a MYSKIP_0NN property and leave it unset.

3. I ended up making use of the old project's helper dll to handle the date testing logic. It also has a function to update the table with scripts that need to be run.

4. If the SQL file needs to be run, I set the corresponding MYSKIP_0NN property to a "1".

Now here's where I might need a little help :rolleyes:

I understand that I will need to query the Files table to get the Component_ field, which then gets me the key to the Component table, which gets me to the Condition that I can set to "Not MYSKIP_0NN".

I understand how to open a view and read data back, but haven't had to write data back into the tables.

MichaelU
11-23-2005, 11:41 AM
Why do you need to write data back? One thing to be aware of is one cannot persist changes to the MSI database during install; all such modifications are temporary. If it's to set properties, so the conditions evaluate correctly, just use MsiSetProperty(hMsi, szName, szValue) from the custom action. Much easier (and probably more effective) than trying to write SQL updates to the Property table.

DMorisseau
11-23-2005, 11:47 AM
I was trying to be clever and automate both the creation of the SKIP property associated to a script and the assignment of the property to the script's corresponding component in order to avoid manually entering the 60+ conditions within the IDE.

I took a different tack this morning on a small scale. I set the conditions on 5 of my script components to Not SKIP_01..05. I then used my custom action to set those to non-blank values if the script did not need to run.

Since I haven't moved any other sequences, will ISSQLServerInstall recognize these conditions ?


http://community.installshield.com/showthread.php?t=154224

MichaelU
11-23-2005, 11:57 AM
It will respect the conditions, but if the sequence is out of order their value will be based on the preset value of the properties to which they refer instead of the value you want. When in doubt, write a simple test case. Set your properties like you do in your deferred CA, and add a VBS MsgBox action at the same sequence that you want to know if they're set by. Or if you're running on MSI 3, take a log file and check the timing of the property changes vs the launch of the ISSQLServerInstall action.

(I'm being so vague and indirect because I don't actually know the right answer here, and it would be better for you to check the exact way you're doing, than for me to mock up a test of how I think you're doing it and get it wrong. That and since it's time critical, this is a good strategy for getting your own answers.)

DMorisseau
11-23-2005, 02:22 PM
As near as I can tell, the SKIP_nn properties are set without error (I have MessageBoxes being raised if they don't), and they are set before ISSQLServerInstall fires, but the scripts run just the same.

Do I need to move (or clone) ISSqlServerCosting so that it happens for ISSqlServerInstall to force recosting?

DMorisseau
11-29-2005, 09:43 AM
I ran into a further complication that doesn't make much sense, maybe someone can enlighten me?

As part of my custom action to get the component that corresponds to the SQL file, I query the File table. Since these scripts have long file names, the File table represents the FileName column as shortname|longname. The problem is that since each file is in its own component, I have multiple entries in the file table with FileName = FileNa~1|longfilename. As a result, my SQL query is failing on some of my SQL files. Oddly enough, it is succeeding in some cases where it shouldn't. Also, the File column of the File table contains the long file name, but I can't seem to include it in a WHERE clause without having an error raised ( `File`.`File` doesn't seem to work)

As I understand, the SQL syntax is somewhat sparse for Windows Installer (no use of string functions to check parts of string).

I'm almost at the point where I feel like renaming all the scripts to use short names so that the query works, but then I'm violating the existing process of testing whether or not the query may have been run as part of a previous installation.

Any guidance would be appreciated.

DMorisseau
12-01-2005, 11:08 AM
Ok, getting past what I said in my last post about the file search, I went back and pared down my project to a small set of scripts and did the following:

1. Property Manager: Initialized SKIP_001 to SKIP_005 to 1.
2. SQL Scripts View: Set condition SKIP_00n=1 on each of the 5 scripts.
3. Deferred Custom Action: went thru debugger to verify that file was located in file table, correct component name returned, SKIP_0NN being set to 0 on scripts that met the date stamp test.
4. Added another Deferred CA prior to ISSQLServerInitialize to verify the values of the five SKIP_00n properties.

The scripts still execute!!!

Am I dealing with a Chicken and Egg issue here (i.e. I have to "install" the component file to INSTALLDIR do the date test, but the ISSQLServerInstall runs the internal copies of the scripts from TempDir that it extracted before I was able to do the test)

What can I do??? I really really don't want to have to manually dictate the execution order of 60+ scripts.

burked
12-02-2005, 08:53 AM
through discussions with InstallShield developers, I have found there are issues here with the conditions.

The only workaround I found was to imbed conditional code within the scripts themselves. So the scripts would run everytime, but the comparison is done inside the sql scripts, which always get populated correctly, when using the text replacement.

daren

DMorisseau
12-02-2005, 09:00 AM
Unfortunately, I do not have rights to change the scripts provided by development.

What issues/limits exist with SQL conditions? At what point during the sequences are Script conditions evaulated?

Please IS, this stuff NEEDS to be FULLY documented.

burked
12-02-2005, 09:03 AM
I found in my case the conditions on SQL Scripts that were used in the execution part of the install were set to whatever they were during the initial pre-UI sequence.

For example, if I had a condition that was originally =1 before install started, even though I changed the condition during the UI sequence, the condition resolved to =1 during execution.

Sounds crazy, but that's my experience with this.

Daren

DMorisseau
12-02-2005, 09:15 AM
And they didn't see fit to fix this?? What's the point of even having the SQL Script Condition functionality if it is only effective on properties set during the UI sequence?

Since the help implies that there's some sort of tie-in with component state, I even made sure that when I did not want the script to run, I used MsiSetComponentState to set the installation state of the script's corresponding component to INSTALLSTATE_ABSENT.

If there's a bug in this functionality, when will it be fixed in an 11.0 HotFix or SP?

As much as I'd like to call for support, I don't have access to the purse strings for support contracts or per-incident calls.

hidenori
12-02-2005, 07:25 PM
The ISSQLServerCosting action saves all the SQL related information including the state of components assoicated with SQL scripts to a temporary text file. Since the ISSQLServerInstall action is a deferred DLL CA, the DLL cannot access to the MSI database and its properties. It reads SQL configurations from the text file.

So your CA setting the conditions has to be an immediate action and be sequenced before the ISSQLServerCosting action. Also, you may want to put your files in the Support Files view instead of the Files view so that the files get copied to the target machine at the eariler stage, as setup.exe copies them before lanuching the MSI package. You can use the SUPPORTDIR predefined folder to access the files from your InstallScript.

Hope I understood your issues correctly and answered your questions.

DMorisseau
12-03-2005, 09:37 AM
I understand, but if I take them out of the Files View, I lose the association between the sql script and the component name. I had to make the CA deferred because it was dependent on having the files physically installed so I could examine them. I could put them in the Support Files view as well, but then I would have 3 copies of the same file in my installer, which seems wasteful.

If a deferred action CA is unable to access the MSI and its properties, how is it that my deferred InstallScript action is able to without raising errors??
(I use MsiGetTargetDir, MsiSetProperty, MsiSetComponentState in my CA).

Where is this temp file you refer to stored?? What is its name? If I can access it and understand its structure correctly, I could just alter the file during my CA.

Hidenori, I really appreciate your assistance, please just be aware that it is frustrating to try to use SQL Script Conditions to regulate whether or not a SQL Script is to run (as is shown in the Help Documentation) only to discover that they only work if the conditions aren't going to change once the UI kicks off.

Again, I pose the question -- When will this be fixed in an IS11 SP or Hotfix?

MichaelU
12-05-2005, 11:53 AM
For each of your scripts you have one SQL-script, and one .sql file both associated with a single component. You then want to use the .sql file to perform your custom action which conditionalizes the install of the component and thus the execution SQL-script. Do I have this right? If so, this is in fact the chicken and egg scenario, and isn't really a "bug" to fix.

I don't know the SQL half of the timing issues here (i.e. is it too late to change conditions once you have the IS-controlled database connection?), but I do know it's too late to not install a component once you've already installed it. I expect even Windows Installer won't reverse the installation of the components; if you take a verbose log, what are the corresponding install states for the components you're trying to conditionalize off?

If you take Hidenori's suggestion of using the support files view, could you just associate .sql with component by name of the script / name of the property? That should get around the need to install the component to figure out whether to install it.

DMorisseau
12-05-2005, 12:08 PM
The thought had crossed my mind, save for the fact that the naming convention of the script files does not exactly conform to Windows Installer component naming rules.

With the timing of this project being close, I've had to backtrack and emulate how the old project handled this issue and not use the SQL Scripts view/condtions to manage my scripts.

I have to disagree with the idea that this isn't really a bug. The IS Help says as part of the Run Script During Install the following : "This option is also associated with component state because each script is tied in with a feature by design. Therefore, you can specify conditional statements in conjunction with this setting in the Script Condition section of this tab".

But in reality the script condition CONTROLS the component state. It may be possible not just for me, but for others, that a script condition may not be fully evaulatable until files are installed to the target PC.

Given Hidenori's statement that the ISSQLServerCosting action saves data to a temporary file used by ISSQLServerInstall, I would think there would be a documentable way to access and affect this file so that developers can work around the limits of ISSQLServerInstall being a deferred CA.

hidenori
12-05-2005, 11:45 PM
The ISSQLServerCosting action works as designed. Microsoft resitricts custom actions that change the installation state of features or components must be sequenced before the InstallValidate action. Since the ISSQLServerCosting action is sequenced after the InstallValidate action, the state of components should never be changed after the SQL related information is saved. See the Sequencing Custom Actions (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/msi/setup/sequencing_custom_actions.asp) help topic for more information.

You probably need to deal with support files to achieve your requirments. The support files will be extracted to the SUPPORTDIR InstallScript predefined folder. Also I filed the work order #IOC-000044023 to consider how InstallShield can support your requirement in the built-in SQL feature for a future release.

Thank you for your patience and cooperation.

DMorisseau
12-06-2005, 08:50 AM
Thank you for looking at this, I appreciate your feedback.

For the benefit of the user base, would your group please fully document the behavior of the ISSQLServer related custom actions and Script Condition logic in a KB article? If I had been able to know how the SQL Condition logic really worked, I might have saved myself a lot of time on this issue.

DMorisseau
12-20-2005, 10:48 AM
I got my project out the door my not using the SQL scripts view and evaulating each of the individual scripts that were installed. Once I had some spare time, I created a separate test project and tried to use the Support Files view as suggested to evaluate conditions for installation.

Unfortunately, I found that this approach won't work either, since the Date last modified attached to the sql scripts ends up being the date/time it was created on the PC, NOT the date last modified that comes up when the file is installed as part of a component! As a result, all of the scripts in my test project ran.

So now my only issue with my working project is that I have to leave the SQL scripts out on the target pc and not clean them off at the end of the installation (in order to avoid MSI Self-Repair when the app launches).

Anybody know when KB articles on the ISSQLServer custom actions will be available? Schema Version # is simply not always the answer for determining when to run a script. We need workarounds (really, we need this behavior changed :o

I've finished ranting now, time to get back to work!