All about SQL-typed Control procedures

(Personal story)

One aspect that I had difficulty with when creating control procedures, was making them work in a variety of scenarios. The way was to copy already created control procedures and templates and alter them slightly to match what needed to be done. Whilst there is nothing wrong with reusing great solutions, it becomes a problem when the model changes in such a way that each one of these control procedures had to be changed. Only at that point, it becomes obvious that this is not be the way to continue development. 

Understanding control procedures

As I became more and more familiar with the Software Factory, I began to understand that the Software Factory offers a solution I was desperately in need of. 

When switching the type of the Control procedure to SQL, it will allow you to take full control of template assignments and parameters. But to fully understand it, we need to break it down, let's dive into the definitions of Static control procedures:

Assigned templates: Control procedures can have multiple templates, and each of these templates can be assigned to Program objects - more on that later. Program objects can have multiple templates assigned to them of the same control procedure, but also of other control procedures.

Parameters: Inside control procedure templates, it is possible to use the convention [PARMTR]. What this allows you to do is replace it with any value. This can already be done using Static control procedures, but manually:

SQL-typed Control procedures

But there is a trick to it. With SQL-typed Control procedures, we can now replace one parameter with a set of values. For each time a parameter is replaced with a value, the row is duplicated in the generated code. This is perfect when listing numerous AND conditions or naming all options possible in a set. In the demonstration more on this!

Once that information has sunk in, let's get to the technical (and magical) stuff. First a couple of table definitions:

Program objects: Essentially, a Program object is a programmable object in which code can be weaved. Every table can have Default, Layout, or Context logic for example. The Software Factory offers this Logic for developers to utilize. So, for each table, a Default, Layout, and Context program object is generated. The GUI and Indicium will execute these logic procedures when activated for the table. 

Program object items: Once we have a program object, we can assign templates to it. These Program object items are items assigned to a specific program object. It is the link between templates and the program object. This allows developers to assign multiple templates to a table's Default logic for example.

Program object parameters: Earlier I explained parameters a bit. Program objects can also have parameters because the assigned templates have parameters. To replace each parameter with a particular name in any attached template, a record can be placed in this table.

Program object item parameters: Once a template is assigned to a Program object, parameters inside these templates (or program object item) can be replaced by adding records to this table. This will only replace parameters inside that specific item.

To make it all a bit clearer, this is the Data model:

Control procedures create program objects. Control procedure templates are assigned to program objects as program object items.

More info on Control procedures here: https://docs.thinkwisesoftware.com/docs/sf/functionality#definitions

Demonstration

Let's put theory into practice. To demonstrate the power of SQL-typed Control procedures, we need an understandable Use case.

Scenario

Let's say for each table you would like to add a simple Badge that shows the number of records in the table. The control procedure makes use of the Staged strategy.

Text-based solution

• Make sure every table uses and shows the badge (Dynamic model code)
• Assign the template to every table
• Replace parameter [TAB] with the table name
• Replace parameter [COL] with the columns used in Detail references in Absolute order ascending. This will create multiple lines of code - 1 line for each column (Usually PK columns)
• Replace parameter [AND] with the word AND for every line but the first. The first line should not have "AND”

In the Software Factory

Let's place it inside the Software Factory!

Dynamic model - Make sure every table uses and shows the badge (Strategy: Fully managed)

update t
set use_badges = 1,
    show_badge = 1
from tab t
where t.model_id = @model_id
  and t.branch_id = @branch_id
  and t.use_badges <> 1

Now onto the Control procedure:

Control procedure - Assign the template to every table (Strategy: Staged)

insert into #prog_object_item
(
    prog_object_id,
    prog_object_item_id,
    order_no,
    template_id
)
select
    'badge_' + t.tab_id,
    'content_count_badge',
    10,                                    
    @control_proc_id
from tab t

Control procedure - Replace parameter [TAB] with the 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
    'badge_' + t.tab_id,
    'content_count_badge',
    'TAB',                 -- Parameter [TAB]
    t.tab_id,              -- Replace with table name
    1,
    0
from tab t

Control procedure - Replace parameter [COL] with the columns used in Detail references in Absolute order ascending. This will create multiple lines of code - 1 line for each column (Usually PK columns)

insert into #prog_object_item_parmtr
(
    prog_object_id,
    prog_object_item_id,
    parmtr_id,
    parmtr_value,
    order_no,
    no_line_when_empty
)
select
    'badge_' + t.tab_id,
    'content_count_badge',
    'COL',
    c.col_id,
    c.abs_order_no,
    1
from tab t
join col c
  on c.model_id = @model_id
 and c.branch_id = @branch_id
 and c.tab_id = t.tab_id  

 -- Columns used inside the detail reference 
 and exists (select 1
             from ref_col rc
             join ref r
               on r.model_id = rc.model_id 
              and r.branch_id = rc.branch_id 
              and r.ref_id = rc.ref_id   
             where rc.model_id = c.model_id 
               and rc.branch_id = c.branch_id 
               and rc.target_tab_id = c.tab_id
               and rc.target_col_id = c.col_id
               and r.is_detail = 1)

Control procedure - Replace parameter [AND] with the word AND for every line but the first. The first line should not have "AND”

insert into #prog_object_item_parmtr
(
    prog_object_id,
    prog_object_item_id,
    parmtr_id,
    parmtr_value,
    order_no,
    no_line_when_empty
)
select
    'badge_' + t.tab_id,
    'content_count_badge',
    'and',
    -- Row number per table ordered by Absolute order number
    case 
        when row_number() over (partition by t.tab_id order by c.abs_order_no) = 1 
        then '' else ' AND ' 
    end,
    c.abs_order_no,
    0
from tab t
join col c
  on c.model_id = @model_id
 and c.branch_id = @branch_id
 and c.tab_id = t.tab_id

 -- Columns used inside the detail reference 
 and exists (select 1
             from ref_col rc
             join ref r
               on r.model_id = rc.model_id 
              and r.branch_id = rc.branch_id 
              and r.ref_id = rc.ref_id   
             where rc.model_id = c.model_id 
               and rc.branch_id = c.branch_id 
               and rc.target_tab_id = c.tab_id
               and rc.target_col_id = c.col_id
               and r.is_detail = 1)

Full control procedure code:

-- Assign the template to every table
insert into #prog_object_item
(
    prog_object_id,
    prog_object_item_id,
    order_no,
    template_id
)
select
    'badge_' + t.tab_id,
    'content_count_badge',
    10,                                    
    @control_proc_id
from tab t


-- Replace parameter [TAB] with the 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
    'badge_' + t.tab_id,
    'content_count_badge',
    'TAB',                 -- Parameter [TAB]
    t.tab_id,              -- Replace with table name
    1,
    0
from tab t


-- Replace parameter [COL] with the columns used in Detail references in Absolute order ascending. 
-- This will create multiple lines of code - 1 line for each column (Usually PK columns)
insert into #prog_object_item_parmtr
(
    prog_object_id,
    prog_object_item_id,
    parmtr_id,
    parmtr_value,
    order_no,
    no_line_when_empty
)
select
    'badge_' + t.tab_id,
    'content_count_badge',
    'COL',
    c.col_id,
    c.abs_order_no,
    1
from tab t
join col c
  on c.model_id = @model_id
 and c.branch_id = @branch_id
 and c.tab_id = t.tab_id  

 -- Columns used inside the detail reference 
 and exists (select 1
             from ref_col rc
             join ref r
               on r.model_id = rc.model_id 
              and r.branch_id = rc.branch_id 
              and r.ref_id = rc.ref_id   
             where rc.model_id = c.model_id 
               and rc.branch_id = c.branch_id 
               and rc.target_tab_id = c.tab_id
               and rc.target_col_id = c.col_id
               and r.is_detail = 1)


-- Replace parameter [AND] with the word AND for every line but the first. 
-- The first line should not have "AND”
insert into #prog_object_item_parmtr
(
    prog_object_id,
    prog_object_item_id,
    parmtr_id,
    parmtr_value,
    order_no,
    no_line_when_empty
)
select
    'badge_' + t.tab_id,
    'content_count_badge',
    'and',
    -- Row number per table ordered by Absolute order number
    case 
        when row_number() over (partition by t.tab_id order by c.abs_order_no) = 1 
        then '' else ' AND ' 
    end,
    c.abs_order_no,
    0
from tab t
join col c
  on c.model_id = @model_id
 and c.branch_id = @branch_id
 and c.tab_id = t.tab_id

 -- Columns used inside the detail reference 
 and exists (select 1
             from ref_col rc
             join ref r
               on r.model_id = rc.model_id 
              and r.branch_id = rc.branch_id 
              and r.ref_id = rc.ref_id   
             where rc.model_id = c.model_id 
               and rc.branch_id = c.branch_id 
               and rc.target_tab_id = c.tab_id
               and rc.target_col_id = c.col_id
               and r.is_detail = 1)

Control procedure template

-- Count the number of records
-- Hide the badge when there are none
select @badge_value = nullif(count(*), 0)
from [TAB]
where 
   [AND]([COL] = @[COL] or @[COL] is null)

Result

Wrap-up

We have taken a look at one possible implementation of SQL-typed Control procedures, but there are many more scenarios where this option can benefit development. From Defaults to Layouts to Contexts to even Views, the possibilities are endless. The Software Factory uses this concept for the procedures that export and import of models, but also for the dynamic rename and delete tasks.

The main benefit is that because this is built using SQL, whenever a new table is added or a column has changed, control procedures automatically adapt to the change ensuring consistency throughout your model as well as save yourself quite some maintenance.

I hope this has helped you understand SQL-typed Control procedures and inspired you to take a look at the possibilities for your model. Together we will create and maintain a sustainable future.