Execute Upgrade Script really at the end of an upgrade

Hi Harry,

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?

Hi Mark, 

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.

@Mark Jongeling Wow, this Idea lived a short life… I would appreciate a longer dialogue with your customer(s) on this one very much.

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:

• We use a Trigger to set the Insert user and Insert DateTime. Currently, when we add a new Table and include an Upgrade (after) script to populate this new Table with data, the Insert user and Insert DateTime are not set, because the Trigger does not yet exist and is only created later on in the Upgrade process. Adding the Columns in the Upgrade script would have worked in this particular instance, but would break logic in instances where the Table already exists and new data is added, since it would trigger setting the Update user and Update DateTime (this is unwanted for an insert).
• We updated our Data Model by adding 2 Columns to a Table. For a specific set of records the Data had to be moved from existing Columns to the new Columns. On Insert/Update/Delete of the existing Columns a Trigger was assigned to take care of data in another Table. Similar logic had been added to the same Trigger for the new Columns. However, the Upgrade (after) script only worked partly: the Trigger on Delete worked for the existing Columns, but the Trigger on Insert did not work for the new Columns since it was not yet updated with the new logic. 
   

Would you please be so kind to reopen this Idea?

Hi Arie,

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:

insert into #prog_object
(
    prog_object_id,
    prog_object_type_id,
    code_file_id,
    order_no,
    prog_object_file_spec,
    prog_object_status
)
select 'ug_end_of_creation_upgrade_always',
       'MANUAL',
       'end_of_creation',
       998,
       @prog_object_folder_spec + 'ug_end_of_creation_upgrade_always.sql',
       0 -- Not flagged

insert into #prog_object
(
    prog_object_id,
    prog_object_type_id,
    code_file_id,
    order_no,
    prog_object_file_spec,
    prog_object_status
)
select dbo.replace_illegal_characters('ug_end_of_creation_upgrade_from_' + m.model_vrs_description),
       'MANUAL',
       'end_of_creation',
       999,
       @prog_object_folder_spec + dbo.replace_illegal_characters('ug_end_of_creation_upgrade_from_' + m.model_vrs_description) + '.sql',
       0 -- Not flagged
from model_vrs m
where m.model_id = @model_id
  and m.branch_id = @branch_id
  and m.use_upgrade_logic = 1 -- Only then prog objects should be created
  and m.model_vrs_description is not null -- Extra check, should always be present

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.