Strategies for Control procedures

Hi everyone,

For the 2021.3 release, the complete Creation process has been overhauled to be fully run by Indicium. There was one other feature on the backlog that was very valuable to the Generate definition process, and now was the perfect time to implement it.  

When generating, all generated program objects would be dropped and re-created by the control procedures. This process costs performance and IO. It would be smarter if objects were not simply dropped and re-created, but instead created/changed/dropped only when that truly applies. 

Strategies

To improve the Generate definition process, several generation strategies have been implemented. A Strategy is a defined way of how the Software Factory should generate the control procedure. For every strategy there can be reasons to choose one over the other, but it is recommended to use the Staged strategy due to all its performance benefits. Read on to see how to implement these new strategies. 

The following strategies are now available for SQL-typed Control procedures:

• Delete: All objects generated by this control procedure will be dropped automatically during the Generate definition process and re-created by the control procedure. This is like how the Software Factory used to generate by default prior to version 2021.3.
• Fully managed: All objects generated by this control procedure will not be dropped automatically during the Generate definition process. This strategy means the developer has to manage all the objects generated by this control procedure. So, previously generated and outdated objects need to be dropped by the control procedure itself, if necessary.
• Staged: The control procedure will place the desired outcome in temporary staging tables. Subsequently, all objects generated by this control procedure will be either updated, inserted, or deleted based on the data present in the staging tables. Temporary staging tables are available in the control procedure code and can be filled to describe the desired result. More on this later in this blog. 

For new SQL-typed control procedures, Staged is the default strategy. Also, if the assignment of a control procedure is changed to SQL, the strategy will automatically be set to Staged. To convert a static-typed Control procedure, you can use task Switch assignment to SQL.

For existing SQL-typed Control procedures at the time of upgrading to 2021.3, the strategy is automatically set to Delete to make sure that after the upgrade everything runs smoothly.

For Static-typed Control procedures, no strategy can be applied manually. The Software Factory will automatically handle these control procedures like they use the Staged strategy. 

Implementation

Each strategy needs a different implementation so let's take a look at how:

Delete:

This strategy is as how it used to be so after the upgrade you won't have to change a thing. Control procedures using this strategy will mutate their objects directly in the prog_object_item and prog_object_item_parmtr tables. During the Generate definition process (and Generate code group), objects created by this control procedure will be dropped and thereafter re-created according to its code.

Fully managed:

Take matters into your own hands by using the strategy Fully managed. This strategy requires the code to mutate all data directly in the prog_object_item and prog_object_item_parmtr tables but including the deletion of data and dropping of objects. The Generate definition process will not automatically drop any object created by this control procedure. 

Staged:

This recommended strategy will ensure the most optimal performance and reduces IO; hence it's the default. This strategy makes use of temporary tables that are structurally similar to the prog_object_items tables, but have the added benefit that they are temporary and merge their data with the corresponding real table at the end of the control procedure code execution automatically. The table structure of these objects are described here: Functionality · Thinkwise Docs (thinkwisesoftware.com)

Rewriting a SQL-typed control procedure to use the Staged strategy

A few adjustments are needed to rewrite a previously made SQL-typed control procedure (with strategy Delete) to use the Staged strategy. Following the link above, rewrite the code by changing the following segments of the code:

• Replace any use of the prog_object tables to include a # in front. For example, #prog_object_item or #prog_object_item_parmtr. This will make the code use the created (temporary) staging tables. The Software Factory will create these tables by itself.
• Remove the following columns as these columns will be filled automatically with the correct value by the Software Factory when merging the staging tables:
  • project_id
  • project_vrs_id
  • control_procedure_id
• In very rare cases you could have a program object-typed control procedure. These two columns are not present in the temporary #prog_object table 
  • selected
  • prog_object_generated_code

Example SQL-typed control procedure with strategy set to Staged:

--Create Default for each table
insert into #prog_object_item
(
    prog_object_id,
    prog_object_item_id,
    order_no,
    template_id
)
select 
    'default_' + t.tab_id,
    @control_proc_id, --Control procedure name, e.g. update_edit_counter
    100,
    @control_proc_id  --update_edit_counter
from tab t
where t.project_id = @project_id
  and t.project_vrs_id = @project_vrs_id
  and t.allow_update = 1 --Edit has to be enabled
  and exists(select 1 
             from col c
             where c.project_id = t.project_id
               and c.project_vrs_id = t.project_vrs_id
               and c.tab_id = t.tab_id
               and c.col_id = 'edit_counter') --Column 'edit_counter' in Table 

--Replace [tab] in template to Table name
insert into #prog_object_item_parmtr
(
    prog_object_id,
    prog_object_item_id,
    parmtr_id,
    parmtr_value,
    order_no,
    no_line_when_empty
)
select
    'default_' + t.tab_id,
    @control_proc_id,
    'TAB',
    t.tab_id,
    0,
    0
from tab t
where t.project_id = @project_id
  and t.project_vrs_id = @project_vrs_id
  and t.allow_update = 1
  and exists(select 1 
             from col c
             where c.project_id = t.project_id
               and c.project_vrs_id = t.project_vrs_id
               and c.tab_id = t.tab_id
               and c.col_id = 'edit_counter')

There are numerous generated control procedures using this strategy present in the Software Factory for reference. Disable the Generated prefilter at Functionality > Control procedures and filter on type = SQL. 

That's all!, the Software Factory will subsequently use the data in these staging tables to take care of creating new program object items and -parameters and/or updating and removing them where needed.