I add an extra element to a domain. Then I need to change some data in the table and set some records to this new element. So I write an upgrade script and assign it to after. However the upgrade fails since I am trying to add data that’s not conform the check constraints. This is caused by the fact that the check constraints get updated later then the upgrade script get executed.
I would like to be able to execute an upgrade script at the end of an upgrade, not at the end of the step upgrade. I find it confusing that an upgrade script that is assigned as after, gets exectuded during an upgrade.
The last code file that will be executed in a standard situation is the Manual code file. You are able to assign control procedure templates to this code file by creating control procedures for the MANUAL code group. This will execute the code at the end of all other code files, such as Upgrade, Triggers and Defaults.
By default, this code group is not visible due to an active prefilter. You can turn off the prefilter Facilitates application logic either by using the short key Alt+W or by going into the context menu and disabling it from there.
Would that suffice as solution?
This indeed is a technical solution to the problem, however I think it’s not ideal from a UX perspective. As a new user I’d expect that when I want to update data after an upgrade, I can use an upgrade script, however now in some cases you need to use a whole different type of script (that’s also hidden away and difficult to find). I think this can be confusing and hard to explain to people new to the SF.
I think a better solution should be a general procedure for all types of “upgrade scripts” that you want to run. Or make it possible to assign upgrade scripts to the same position as manual scripts.
The workaround would be to drop the check constraint inside the created upgrade control procedure. In addition, in case said table is set to be rebuild (in Data migration), then all check constraints of that particular table would be dropped during the Upgrade, and that would suffice in your case.
Discussed this with my colleagues but we are not looking to add a new code file just for this exotic situation.
I think dropping all constraints for an upgrade script is not a solid workaround, I want to have my “safe guards” in place for ensuring my data is protected when upgrading data. When the code is executed at the point where manual scripts are executed, my data is protected by the new check constraints.
I can follow the logic that you do not feel inclined to create a whole procedure for this problem. But what about creating an option to assign an upgrade script to be executed at the moment of a manual script. Or maybe execute scripts that are assigned as after the upgrade at this point, because I can’t think of a situation where you want to execute code before the rest of the steps of the creation process has been executed (but that can be just a lack of my imagination ).
No need to drop all constraints, simply dropping the one constraint preventing the action is doable in our opinion. The After upgrade section is normally for queries that mutate data.
Alternatively you can take matters into your own hands and creating a control procedure of type Program object, and also a new Code file that either is just before or after the Manual code file. The control procedure will insert a Program object with Items, which you then can base on the generated control procedure "upgrade_sql_custom_code”.
This way you could achieve assigning Upgrade scripts to the Code file you've just created
Challenging, but I'm not sure if it is worth the effort. I'll decline the idea as we are not going to implement this.
As you might have noticed, we aim for further improvements to our deployment automation and as it stands the Data Migration part of things is both buggy and lacking valuable features at this stage.
This is actually one of those potentially valuable features, which would have come in handy in two more actual Use cases during our PROD deployments this month:
Would you please be so kind to reopen this Idea?
We are not looking to expand the Software Factory to offer this functionality by default. The Upgrade code file is one whole that upgrades an existing database to the next version. Having code affecting this after other code files creates dependencies between the Upgrade code file and the additional code file this idea would require.
However, luckily this idea can be implemented by anyone by simply taking the control procedure upgrade_sql_custom_code as inspiration. This control procedure is one we provide which enables you to assign control procedure templates to its created program objects, that causes the code to be weaved into the Upgrade code file in the indicated place (before/during/after).
You are able to build upon that with a control procedure of type Program object. This new control procedure uses the following code - in version 2023.1:
The control procedure does not need any templates itself.
Note that this control procedure uses a code file named end_of_creation. This is just an example but you can name the code file anything you desire. Create a new code file with that name in the Code file screen, available in Model content > Code files.
After this is all done, you have this Code file:
Now you are able to assign created Upgrade control procedures to the newly created program objects:
Once assigned, only when upgrading from the named version, the assigned template code will be weaved. Any template code assigned to ug_end_of_creation_upgrade_always will always be weaved into the code file.