[wix-users] ExecuteOnInstall and RollbackOnUninstall attributes on SqlScript

[Ven H]

Thanks a lot for the detailed explanation Edwin. Currently, I am just
trying out a very simple scenario wherein I have an SQL script which just
creates a sample table called Employees with ID and Name columns, which I
am adding into an existing DB during installation. During uninstall, I
would like to rollback this action (remove / drop the table). I tried with
Nir's code, but it doesn't seem to be removing the table during uninstall.
The following is the code fragment for this (not the entire mark up). I
even tried setting the Sequence attribute, but no luck.

<Binary Id="binCreateTbl" SourceFile="CREATE_TABLE.sql" />

 <sql:SqlScript BinaryKey="binCreateTbl" Id="script_CreateTbl"
ExecuteOnInstall="yes" Sequence="2"/>
 <sql:SqlScript BinaryKey="binCreateTbl" Id="script_CreateTbl1"
RollbackOnUninstall="yes" Sequence="1" />



Regards,
Venkatesh

On Wed, Feb 28, 2018 at 10:31 PM, Edwin Castro <egcastr at gmail.com> wrote:

> Standard actions implemented by the Windows Installer already take
> rollback into consideration. Any custom actions we implement must
> explicitly consider rollback since we're providing that functionality.
> SqlScript allows you to execute a SQL script but because SqlScript doesn't
> know what is being done by the script, it can only provide a mechanism to
> schedule scripts to be executed at different times. We're still responsible
> for providing rollback scripts when appropriate.
>
> Here's a list of execution contexts you must consider and test when
> implementing any custom action (anything not implemented directly by the
> Windows Installer, this includes actions provided by WiX):
> * Install - typically install the resource
> * Install Rollback - typically uninstall the resource
> * Reinstall - typically install the resource when it may already exist or
> partially exist
> * Reinstall Rollback - I never know what to do here, I always imagine this
> effectively should be the same as Reinstall but perhaps it makes sense to
> revert back to the potentially broken state before Reinstall?
> * Uninstall - typically uninstall the resource
> * Uninstall Rollback - typically install the resource
>
> Upgrades in general also come into play here. In my experience, it is
> generally easier to handle rollback when you use major upgrades and can
> allow everything to be uninstalled and installed again during that major
> upgrade. The difficulty comes with data (like configuration files and
> databases) where uninstalling the current data might be irreparably
> destructive! You must be very careful to merge data carefully in those
> cases. Often, we setup developers punt that responsibility to be performed
> by the application at run-time. We simply install/deliver the resources
> necessary for the application to merge the data. For example, we might
> install a new default configuration file and require the application
> actually merge any changes into the current configuration file so that
> existing custom configuration can override any default configuration.
> Similar care needs to be taken for databases.
>
> Note when you are explicitly scheduling rollback actions for your custom
> actions, then you *must* schedule the rollback action before the custom
> action in question. This is a requirement based on how the Windows
> Installer works. For example, suppose I had a custom actions to call an API
> that registers and unregisters a plugin. I'll call those custom action
> functions RegisterPlugin and UnregisterPlugin. At a minimum, I probably
> need the following:
>
> <CustomAction Id="RegisterPlugin" DllEntry="RegisterPlugin"
> Execute="deferred" ... />
> <CustomAction Id="RegisterPluginRollback" DllEntry="UnregisterPlugin"
> Execute="rollback" ... />
>
> <CustomAction Id="UnregisterPlugin" DllEntry="UnregisterPlugin"
> Execute="deferred" ... />
> <CustomAction Id="UnregisterPluginRollback" DllEntry="RegisterPlugin"
> Execute="rollback" ... />
>
> And I'd need to schedule them something like this (assume I only need to
> install the files associated with the plugin):
>
> <InstallExecuteSequence>
>     <Custom Action="UnregisterPluginRollback"
> Before="UnregisterPlugin"><![CDATA[ Condition ]]></Custom>
>     <Custom Action="UnregisterPlugin" Before="RemoveFiles"><![CDATA[
> Condition ]]></Custom>
>
>     <Custom Action="RegisterPluginRollback" After="InstallFiles"><![CDATA[
> Condition ]]></Custom>
>     <Custom Action="RegisterPlugin" After="RegisterPlugin"><![CDATA[
> Condition ]]></Custom>
> </InstallExecuteSequence>
>
> This minimal set of actions require that the conditions are enough to
> determine whether the custom actions should be executed. They do not really
> take reinstall into consideration yet. If you need to run custom code to
> determine whether the custom actions should be executed, then the best
> approach is to have a "schedule" immediate custom action that makes
> decisions and schedules the rollback and deferred custom action if
> necessary. That looks more like the following:
>
> <CustomAction Id="RegisterPluginSchedule" DllEntry="RegisterPluginSchedule"
> Execute="immediate" ... />
> <CustomAction Id="RegisterPluginExecute" DllEntry="RegisterPlugin"
> Execute="deferred" ... />
> <CustomAction Id="RegisterPluginRollback" DllEntry="UnregisterPlugin"
> Execute="rollback" ... />
>
> <CustomAction Id="UnregisterPluginSchedule" DllEntry="UnregisterPluginSchedule"
> Execute="immediate" ... />
> <CustomAction Id="UnregisterPluginExecute" DllEntry="UnregisterPlugin"
> Execute="deferred" ... />
> <CustomAction Id="UnregisterPluginRollback" DllEntry="RegisterPlugin"
> Execute="rollback" ... />
>
> <InstallExecuteSequence>
>     <Custom Action="UnregisterPluginSchedule" Before="RemoveFiles"/>
>
>     <Custom Action="RegisterPluginSchedule" After="InstallFiles"/>
> </InstallExecuteSequence>
>
> This pattern allows you to make decisions including ones about reinstall
> in the schedule custom action and then the deferred and rollback actions
> are implemented in a generic fashion where they act on the resources
> specified by the schedule action. This approach typically requires that the
> schedule action create "custom action data" that is passed to the rollback
> and deferred actions. It is through this "custom action data" that the
> actions communicate with each other.
>
> I understand this is a very high-level overview without many specifics
> about your SQL scripts but that is because I don't know what your SQL
> scripts do nor how the general concept of rollback affects them. I'm
> hopeful that this general, high-level introduction will be helpful at least
> in generating interesting follow-up questions. :-)
>
> --
> Edwin G. Castro
>
>
> On Wed, Feb 28, 2018 at 5:16 AM, Ven H via wix-users <
> wix-users at lists.wixtoolset.org> wrote:
>
>> Thanks a lot for your response, Nir. So, do you mean something like below?
>> The below works for me.
>>
>> <Binary Id="binCreateTbl" SourceFile="CREATE_TABLE.sql" />
>>  <Binary Id="binDropTbl" SourceFile="DROP_TABLE.sql" />
>>
>> <sql:SqlScript BinaryKey="binCreateTbl" Id="script_CreateTbl"
>> ExecuteOnInstall="yes" />
>>  <sql:SqlScript BinaryKey="binDropTbl" Id="script_DropTbl"
>> ExecuteOnUninstall="yes" />
>>
>> But I am not able to understand the purpose of the Rollback attributes
>> like
>> RollbackOnUninstall,  RollbackOnInstall,  RollbackOnReinstall. Where and
>> when to use these? Can you please explain?
>>
>>
>> Regards,
>> Venkatesh
>>
>> On Wed, Feb 28, 2018 at 5:38 PM, Nir Bar via wix-users <
>> wix-users at lists.wixtoolset.org> wrote:
>>
>> > Duplicate the SqlScript element- one for install and the other for
>> > uninstall rollback.
>> >
>> >
>> >
>> >
>> > --
>> > Nir Bar, Independent WiX Expert. Creator of-
>> > JetBA: WiX WPF Bootstrapper User Interface Framework
>> >
>> > JetBA++: WiX Native Bootstrapper User Interface Framework
>> >
>> >
>> >
>> >
>> > ____________________________________________________________________
>> > WiX Toolset Users Mailing List provided by FireGiant
>> > http://www.firegiant.com/
>> >
>>
>> ____________________________________________________________________
>> WiX Toolset Users Mailing List provided by FireGiant
>> http://www.firegiant.com/
>>
>
>