Automatically create a function for system versioned tables

  • 2 July 2021
  • 0 replies
  • 71 views

Userlevel 6
Badge +18

Goal

When working with the Software Factory, it is possible to make tables System versioned, or another name is temporal tables. The SF will create a history table when the the setting System versioning is set to true. However, to make use of this history data we recommend using a function to obtain records from the history table. 

 

Solution

Here is a Dynamic model control procedure that automatically creates a 'get_[table]_at_point_in_time’ function. And below this code, find more code - this is the Control procedure that is needed to create the function.

(This script works for the 2021.2 version of the Software Factory. Compatibility may change over time.)

Dynamic model (meta_function_point_in_time):

select 
@project_id as project_id,
@project_vrs_id as project_vrs_id,
'get_' + tab_id + '_at_point_in_time' as subroutine_id,
'Function' as subroutine_type_id,
2 as return_value,
null as subroutine_description,
0 as generated,
dbo.tsf_user() as insert_user,
sysdatetime() as insert_date_time,
dbo.tsf_user() as update_user,
sysdatetime() as update_date_time
into #desired_subroutine
from tab
where project_id = @project_id
and project_vrs_id = @project_vrs_id
and use_system_versioning = 1;

-- desired subroutine parameters
select
@project_id as project_id,
@project_vrs_id as project_vrs_id,
'get_' + tab_id + '_at_point_in_time' as subroutine_id,
'point_in_time' as subroutine_parmtr_id,
'point_in_time' as dom_id,
10 as order_no,
1 as abs_order_no,
1 as input_parmtr,
0 as output_parmtr,
dbo.tsf_user() as insert_user,
sysdatetime() as insert_date_time,
dbo.tsf_user() as update_user,
sysdatetime() as update_date_time
into #desired_subroutine_parmtr
from tab
where project_id = @project_id
and project_vrs_id = @project_vrs_id
and use_system_versioning = 1;

-- Delete undesired subroutine parameters
with current_subroutine_parmtr as
(
select *
from subroutine_parmtr
where project_id = @project_id
and project_vrs_id = @project_vrs_id
and generated_by_control_proc_id = @control_proc_id
)
merge current_subroutine_parmtr t
using #desired_subroutine_parmtr s
on s.project_id = t.project_id
and s.project_vrs_id = t.project_vrs_id
and s.subroutine_id = t.subroutine_id
and s.subroutine_parmtr_id = t.subroutine_parmtr_id
when not matched by source then
delete;

-- Delete, create or update subroutines
with current_subroutine as
(
select *
from subroutine
where project_id = @project_id
and project_vrs_id = @project_vrs_id
and generated_by_control_proc_id = @control_proc_id
)
merge current_subroutine t
using #desired_subroutine s
on s.project_id = t.project_id
and s.project_vrs_id = t.project_vrs_id
and s.subroutine_id = t.subroutine_id
when not matched by source then
delete
when matched and (t.subroutine_type_id <> s.subroutine_type_id
or t.return_value <> s.return_value
or t.generated <> s.generated) then
update
set subroutine_type_id = s.subroutine_type_id,
return_value = s.return_value,
subroutine_description = s.subroutine_description,
generated = s.generated,
update_user = s.update_user,
update_date_time = s.update_date_time
when not matched by target then
insert
(
project_id,
project_vrs_id,
subroutine_id,
subroutine_type_id,
return_value,
subroutine_description,
generated,
insert_user,
insert_date_time,
update_user,
update_date_time,
generated_by_control_proc_id
)
values
(
s.project_id,
s.project_vrs_id,
s.subroutine_id,
s.subroutine_type_id,
s.return_value,
s.subroutine_description,
s.generated,
s.insert_user,
s.insert_date_time,
s.update_user,
s.update_date_time,
@control_proc_id
);

-- Create or update subroutine parameters
with current_subroutine_parmtr as
(
select *
from subroutine_parmtr
where project_id = @project_id
and project_vrs_id = @project_vrs_id
and generated_by_control_proc_id = @control_proc_id
)
merge current_subroutine_parmtr t
using #desired_subroutine_parmtr s
on s.project_id = t.project_id
and s.project_vrs_id = t.project_vrs_id
and s.subroutine_id = t.subroutine_id
and s.subroutine_parmtr_id = t.subroutine_parmtr_id
when matched and (t.dom_id <> s.dom_id
or t.order_no <> s.order_no
or t.abs_order_no <> s.abs_order_no
or t.input_parmtr <> s.input_parmtr
or t.output_parmtr <> s.output_parmtr) then
update
set dom_id = s.dom_id,
order_no = s.order_no,
abs_order_no = s.abs_order_no,
input_parmtr = s.input_parmtr,
output_parmtr = s.output_parmtr,
update_user = s.update_user,
update_date_time = s.update_date_time
when not matched by target then
insert
(
project_id,
project_vrs_id,
subroutine_id,
subroutine_parmtr_id,
dom_id,
order_no,
abs_order_no,
input_parmtr,
output_parmtr,
insert_user,
insert_date_time,
update_user,
update_date_time,
generated_by_control_proc_id
)
values
(
s.project_id,
s.project_vrs_id,
s.subroutine_id,
s.subroutine_parmtr_id,
s.dom_id,
s.order_no,
s.abs_order_no,
s.input_parmtr,
s.output_parmtr,
s.insert_user,
s.insert_date_time,
s.update_user,
s.update_date_time,
@control_proc_id
);

insert dom
(
project_id,
project_vrs_id,
dom_id,
prog_lang_id,
dttp_id,
prec,
dttp,
user_defined_dttp,
control_id,
show_action_button
)
select
@project_id,
@project_vrs_id,
'point_in_time',
'SQL',
'DATETIME2',
2,
'datetime2(2)',
'point_in_time',
'DATETIME',
2
where not exists (select 1
from dom
where project_id = @project_id
and project_vrs_id = @project_vrs_id
and dom_id = 'point_in_time')

drop table #desired_subroutine;
drop table #desired_subroutine_parmtr;

 

 

Solution part 2

Here is the Control procedure 'function_point_in_time’ needed. Create a new SQL-typed Control procedure for code group FUNCTIONS with the following as Control procedure code:

Control procedure (function_point_in_time):

insert into prog_object_item
(
project_id,
project_vrs_id,
prog_object_id,
prog_object_item_id,
order_no,
control_proc_id,
template_id
)
select
t.project_id,
t.project_vrs_id,
'func_get_' + t.tab_id + '_at_point_in_time',
@control_proc_id,
10,
@control_proc_id,
@control_proc_id
from tab t
where t.project_id = @project_id
and t.project_vrs_id = @project_vrs_id
and t.use_system_versioning = 1

insert into prog_object_item_parmtr
(
project_id,
project_vrs_id,
prog_object_id,
prog_object_item_id,
parmtr_id,
parmtr_value,
order_no,
no_line_when_empty
)
select
t.project_id,
t.project_vrs_id,
'func_get_' + t.tab_id + '_at_point_in_time',
@control_proc_id,
'TAB_ID',
t.tab_id,
0,
0
from tab t
where t.project_id = @project_id
and t.project_vrs_id = @project_vrs_id
and t.use_system_versioning = 1

insert into prog_object_item_parmtr
(
project_id,
project_vrs_id,
prog_object_id,
prog_object_item_id,
parmtr_id,
parmtr_value,
order_no,
no_line_when_empty
)
select
t.project_id,
t.project_vrs_id,
'func_get_' + t.tab_id + '_at_point_in_time',
@control_proc_id,
'COL_ID',
c.col_id,
c.abs_order_no,
0
from tab t
join col c
on c.project_id = t.project_id
and c.project_vrs_id = t.project_vrs_id
and c.tab_id = t.tab_id
where t.project_id = @project_id
and t.project_vrs_id = @project_vrs_id
and t.use_system_versioning = 1
and c.calculated_field_type <> 1 -- Exclude expressions

insert into prog_object_item_parmtr
(
project_id,
project_vrs_id,
prog_object_id,
prog_object_item_id,
parmtr_id,
parmtr_value,
order_no,
no_line_when_empty
)
select
t.project_id,
t.project_vrs_id,
'func_get_' + t.tab_id + '_at_point_in_time',
@control_proc_id,
'COMMA',
iif(max(c.abs_order_no) over (partition by c.project_id, c.project_vrs_id, c.tab_id) = c.abs_order_no, '', ','),
c.abs_order_no,
0
from tab t
join col c
on c.project_id = t.project_id
and c.project_vrs_id = t.project_vrs_id
and c.tab_id = t.tab_id
where t.project_id = @project_id
and t.project_vrs_id = @project_vrs_id
and t.use_system_versioning = 1
and c.calculated_field_type <> 1 -- Exclude expressions

 

 

Solution part 3 (last)

Create a template named 'function_point_in_time’ with the following code:

Template (function_point_in_time):

return
select
[COL_ID][comma]
from [TAB_ID]
for system_time as of @point_in_time

 

 

That's it, generate and upgrade your project to have history tables and functions

 

 


0 replies

Be the first to reply!

Reply