There's some misunderstanding on how DotNetNuke Modules are installed and which steps are executed during a module installation or upgrade.
I'll try to cover both scenarios on this post and I hope this helps to get a clearer idea of how this works.
Let's first take a look at what happens when a module is installed:
As you may know all the internal versioning of modules (and even the core itself) is built around the <version> node on the .dnn installer manifest and the sql scripts that are included in the installation zip.
When the module is installed DNN will run all sql scripts with a number lower or equal than the one specified in the <version> node. Finally the version in that node will be saved on the DesktopModules table. This will help the core on future upgrades so it can know what version is currently installed.
First thing to note here: A given xx.xx.xx.SqlDataProvider script will only be run once. So no matter how many times you reinstall the same module, each sql script will only run once. This is great because otherwise you will see lots of errors when (for example) trying to create a table that already exists.
This gives to another point: if you had some trouble with a module (for example you removed a stored procedure accidentally during development), repeating the same process again (without first deinstalling the module) will not produce the same result.
So don't expect that reinstalling the same version that's currently installed will solve that problem. Can solve others, but not problems with the database.
Another thing that happens when you install a module is that it fires the "UpgradeModule" method if the module implements the IUpgradeable interface.
Public Interface IUpgradeable
Function UpgradeModule(ByVal Version As String) As String
End Interface
In fact this method will be called once after any sql script has been executed and it will be passed the version number of the current script. So you have to code your upgrade logic knowing that this method can be called multiple times. This is usually done testing for the Version parameter on a "select case" statement.
The method call will be routed through the EventQueue system after the web application is restarted. A minor difference with regards to how the sql scripts are executed exists though: even if there's no sql script on the module, this method will be called at least once. This is needed to be sure that at least the upgrade logic is executed, even if no sql script exists. So if you are installing a module whose final version will be 03.03.03, be sure that UpgradeModule will be called at least once with that version number.
Also notice that all UpgradeModule(..) calls will be made after all sql scripts have already run. This is true because they will be fired after the application is restarted and the end of the module installation process.
So again, be sure not to do something "strange" on your upgrade logic, assuming that the database has a special structure. For example query a given stored proc, you know in version 01.01.01 existed, because the code will be executed when all scripts for version 03.03.03 are already installed and maybe that stored proc. doesn't exist anymore.
On first time installations this is more or less all that's involved. Upgrading a module has a couple of differences worth noting.
What happens when a module is upgraded:
Execution of sql scripts is more or less the same: each file with a version number greater than the current version number stored on the DesktopModules table, and less or equal than the number included in the <version> node of the .dnn installer manifest will be run in order.
After each xx.xx.xx.SqlDataProvider script is run, the core will search for a corresponding "xx.xx.xx.txt" file on the upgrade package. This file can contain a list of files you want to remove from older versions of your module. You have to specify a file on any line using the relative path from the root of the DNN installation. For example:
bin\DotNetNuke.Modules.Survey.dll
bin\DotNetNuke.Modules.Survey.SqlDataProvider.dll
DesktopModules\Survey\c.gif
So if this file is 03.03.03.txt, and there's a 03.03.03.SqlDataProvider file, it will be "executed" just after the sqlscript and before the UpgradeModule for this version.
This is also another point where you have to make attention: the delete file will only be processed if there's a related sql script.
Afterwards the application is restarted all the UpgradeModule(...) will be called for each sql script that has been run before. Again, UpgradeModule will be called at least once for the final version (the one in the <version> node) , even if no sql script exists.
I hope this brief explanation helps to understand how DNN processes modules during installation and successive upgrades.