Make your logging data available in the GUI

This is an Archived topic. The solution is available in the Thinkstore inside the Software Factory.

Goal

When you have system versioned tables in your database, SQL Server logs all changes made to the data in those tables. This solution makes this logging data available for the user. This can look like this:

Solution

The solution in this topic creates views on all system-versioned tables. These views give the user access to the logging data. Also, all of these views get conditional layouts in the Software Factory. These conditional layouts give fields with changed values a color, which makes it easier to analyze the data.

Dynamic model meta_view_history

This code create a view definition for every system versioned table, along with conditional layouts to point out changed data.

When you add this code to the dynamic model, you have to change a few values first:

@screen_type_id
Your desired screen type: You can keep this null, or change the value.

@original_date_column
Name of an existing date column with settings that can be copied.

@valid_from
Name of columns that indicates start of the period.

@valid_to
Name of columns that indicates end of the period.

@conditional_layout_code
Column used for condional formating. E.g. (4) means value of column 4 has changed.

@date_dom_id
Domain of columns valid_from and valid_to. Use an already existing domain.

@conditional_layout_code_dom_id
Domain of column conditional_layout_code. Use an already existing domain.

Control procedure view_history

This control procedure adds the queries to all views on the system versioned tables. Also, the field conditional_layout_code is filled with the column numbers of the columns with altered values.

Template view_history

The template contains code for the view. COL_ID contains all columns of the table. COL_ID2 contains the columns to be compared for the conditional layout. PK contains the primary key columns in a row, separated by comma's.

In the attachment you will find the code of this solution.

Page 1 / 1

Great idea.

Unfortunately I get the following error while executing on SF2021.1

Followed by:

Any idea what I should do differently?

PS I tried it twice with @screen_type_id as NULL and as master_detail.

Hi Roy,

Looks to me like the dynamic model template contains a small error:

On line 437 you can see that it try to set the detail screen type to ‘logging’, which probably doesn't exist in your project.

Hi Kevin,

Good point … unfortunately after changing this screentype, I still get the MERGE error.

After adding five lines for project_id and version and running it on SSMS, I got the following messages:

Hi Roy,

Looks like there is another ‘hard’ detail screen type used, this time on line 562:

The last three error messages are probably because the table hasn't been added during the first failed merge statement.

We're getting closer. Currently we've come to the following messages:

@Roy van Uffelen Did you create the domains as stated on line 42 and 43 on the Dynamic_model procedure?

Hi Patrick,
We (Kevin and I) also discovered that in the past hour.

The following actions were needed:

Create the two domains: datetime_all_use (datetime2) and conditional_layout_code (varchar(200));
Create two screen types: logging and logging_detail. It appears that these are in fact the same. This could easily be a grid-only screen type. Just be aware that prefilters are copied from the parent-table
The reference between parent-table and history-view needs to be created manually. It may be possible to automatically generate this as well.
Strange thing is that after generating the definitions, the reference seems to have disappeared. At the same time, drawing it manually produces an error, saying that this reference already exists.
Conditional layout is based on grid_order_no. In my model two columns had the same value, so after changing any of these columns, this resulted in two columns that had its background color changed;
Sort-order of the history is currently ascending, meaning that the last change is at the bottom of the grid. Would be nice to also change that.

A workaround for the reference: modify the reference and disable ‘Show look-up’.
After this change the reference will not be deleted.

Or you can modify line 380 in the dynamic-model file but I haven't tested that :)

In the past we used log tables but recently we've changed it into temporal tables (system-versioned temporal tables). In addition to this, I would now like to create views on all system-versioned tables to give the users access to the logging data. Exactly as described in this topic.

You have to change a few values first but I'm probably not doing something correctly yet. It is still not completely clear to me how the blue marked parameters should be filled.

**Dynamic model** - meta_view_history - @parameters

It looks like something small, maybe with a little extra explanation we'll get there. I couldn't find any further documentation on it unfortunately.

You have to change a few values first but I'm probably not doing something correctly yet. It is still not completely clear to me how the blue marked parameters should be filled.

It looks like something small, maybe with a little extra explanation we'll get there. I couldn't find any further documentation on it unfortunately.

Hi Dennis,

This is due to a mandatory column in the table Col named function_input. At line 132, an insert into #desired_col is done, but function_input is not given in the insert statement, so SQL Server will raise an error. Adding the column function_input and giving it a value of 0 will suffice.

Hope this helps. If any other errors occur similar to this, it might be another column. I think this logging data Dynamic model script is created for a 2021.1 Software Factory although I'm not completely sure.

Now I get the following error:

Is it perhaps possible to renew the code for 2021.2? I certainly think it would be worthwhile as an item in the ThinkStore.

@Dennis van Leeuwen, @Frank Wijnhout I have updated this version to work with 2021.2 version of the SF. I have sent this to Frank separately so he can review/update the current version.

Furthermore I have added and changed some stuff as well:

Made lookups work (based on current data)
Show all columns in the grid (not based on tab-setting)
Optionally show the log as a detail tab
Removed hard-coded dependencies and added some checks to make life easier

Also added it as an attachment to this post.

Enjoy!

For the ones that are interested in this.
I have made some extra code which will set the roles the same as the base table. This way the user will never see more column in the logging that he should. Be aware, this will not enable the reference, but only the table with columns. So to be able to see the detail tab you should enable this manually.

declare @setup_roles bit
set @setup_roles = 1 --Defines if you want to setup roles based on the base tables.

--  19B. Assign roles to the history tables when setup.
if @setup_roles = 1
begin
    --Delete the rights to the history tables to be sure the right rights are set.
    delete rt
    from role_tab rt
    where rt.project_id = @project_id
    and rt.project_vrs_id = @project_vrs_id
    and rt.tab_id like 'view!_%!_history' escape '!'

    delete rc
    from role_col rc
    where rc.project_id = @project_id
    and rc.project_vrs_id = @project_vrs_id
    and rc.tab_id like 'view!_%!_history' escape '!'

    --Add role_tab select rights for the history table according to the existing rights on the base tables.
    insert into role_tab (project_id
        ,project_vrs_id
        ,role_id
        ,tab_id
        ,select_granted
        ,insert_granted
        ,copy_granted
        ,update_granted
        ,delete_granted
        ,add_grid_granted
        ,grid_edit_mode_granted
        ,search_granted
        ,filter_granted
        ,sort_granted
        ,grp_granted
        ,import_granted
        ,export_granted
        ,mass_update_granted
        ,copy_to_clipboard_granted
        ,print_preview_granted
        ,dragging_fields_granted
        ,insert_user
        ,insert_date_time
        ,update_user
        ,update_date_time
        ,generated_by_control_proc_id)
    select rt.project_id
        ,rt.project_vrs_id
        ,rt.role_id
        ,t.tab_id
        ,1 as select_granted
        ,0 as insert_granted
        ,0 as copy_granted
        ,0 as update_granted
        ,0 as delete_granted
        ,0 as add_grid_granted
        ,0 as grid_edit_mode_granted
        ,rt.search_granted
        ,rt.filter_granted
        ,rt.sort_granted
        ,rt.grp_granted
        ,0 as import_granted
        ,0 as export_granted
        ,0 as mass_update_granted
        ,rt.copy_to_clipboard_granted
        ,rt.print_preview_granted
        ,rt.dragging_fields_granted
        ,dbo.tsf_user()   as insert_user
        ,sysdatetime()    as insert_date_time
        ,dbo.tsf_user()   as update_user
        ,sysdatetime()    as update_date_time
        ,@control_proc_id as generated_by_control_proc_id
    from role_tab rt
    join tab t
    on t.project_id = rt.project_id
    and t.project_vrs_id = rt.project_vrs_id
    and t.tab_id = concat_ws('_','view',rt.tab_id,'history')
    where rt.project_id = @project_id
    and rt.project_vrs_id = @project_vrs_id
    and rt.select_granted = 1
    and not exists (select 1
                    from role_tab rt2
                    where rt2.project_id = rt.project_id
                    and rt2.project_vrs_id = rt.project_vrs_id
                    and rt2.role_id = rt.role_id
                    and rt2.tab_id = t.tab_id)

    --Add col rights according to the existing col rights on the base table.
    insert into role_col (project_id
        ,project_vrs_id
        ,role_id
        ,tab_id
        ,col_id
        ,col_access_type
        ,insert_user
        ,insert_date_time
        ,update_user
        ,update_date_time
        ,generated_by_control_proc_id)
    select c.project_id
        ,c.project_vrs_id
        ,rc.role_id
        ,c.tab_id
        ,c.col_id
        ,1 as col_access_type --read_only
        ,dbo.tsf_user()   as insert_user
        ,sysdatetime()    as insert_date_time
        ,dbo.tsf_user()   as update_user
        ,sysdatetime()    as update_date_time
        ,@control_proc_id as generated_by_control_proc_id
    from role_col rc
    join col c
    on c.project_id = rc.project_id
    and c.project_vrs_id = rc.project_vrs_id
    and c.tab_id = concat_ws('_','view',rc.tab_id,'history')
    and c.col_id = rc.col_id
    where rc.project_id = @project_id
    and rc.project_vrs_id = @project_vrs_id
    and not exists (select 1
                    from role_col r2
                    where r2.project_id = c.project_id
                    and r2.project_vrs_id = c.project_vrs_id
                    and r2.role_id = rc.role_id
                    and r2.tab_id = c.tab_id
                    and r2.col_id = c.col_id)

    union all

    --When having rights on the base table, automatically add rights to the valid cols.
    select rt.project_id
        ,rt.project_vrs_id
        ,rt.role_id
        ,c.tab_id
        ,c.col_id
        ,1 as col_access_type --read_only
        ,dbo.tsf_user()   as insert_user
        ,sysdatetime()    as insert_date_time
        ,dbo.tsf_user()   as update_user
        ,sysdatetime()    as update_date_time
        ,@control_proc_id as generated_by_control_proc_id
    from role_tab rt
    join tab t
    on t.project_id = rt.project_id
    and t.project_vrs_id = rt.project_vrs_id
    and t.tab_id = concat_ws('_','view',rt.tab_id,'history')
    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
    and c.col_id in (@valid_from,@valid_to)
    where rt.project_id = @project_id
    and rt.project_vrs_id = @project_vrs_id
    and rt.select_granted = 1
    and not exists (select 1
                    from role_col r2
                    where r2.project_id = rt.project_id
                    and r2.project_vrs_id = rt.project_vrs_id
                    and r2.role_id = rt.role_id
                    and r2.tab_id = c.tab_id
                    and r2.col_id = c.col_id)
end

I have made 2 small adjustments, in the current code the conditional layout is based on the grid_order_no, since there is no guarantee that these are unique you can get in a weird situation with the conditional layout in the view.

In the dynamic model I have changed the value from c.grid_order_no to c.abs_order_no * 10

insert into conditional_layout_condition (project_id
                                         ,project_vrs_id
                                         ,tab_id
                                         ,conditional_layout_id
                                         ,col_id
                                         ,condition
                                         ,type_of_value
                                         ,value
                                         ,generated_by_control_proc_id
                                         ) 
select c.project_id
      ,c.project_vrs_id
      ,c.tab_id
      ,'flag_' + c.col_id
      ,@conditional_layout_code
      ,8
      ,0
      ,'(' + case
                 when c.col_id = @valid_to
                   then 'x'
                 else cast(c.abs_order_no * 10 as varchar(10))
             end + ')'
      ,@control_proc_id
  from #desired_col as c
   where c.col_id not in (@ongeldig_gemaakt_door)

In the control procedure SQL code I have changed how the parameter is filled, it was c.grid_order_no and now it is c.abs_order_no * 10

select @project_id as                          project_id
      ,@project_vrs_id as                      project_vrs_id
      ,'view_view_' + c.tab_id + '_history' as prog_object_id
      ,@control_proc_id as                     prog_object_item_id
      ,'COL_ORDER_NO' as                       parmtr_id
      ,c.abs_order_no * 10 as                  parmtr_value
      ,c.abs_order_no as                       order_no
      ,0 as                                    no_line_when_empty

@Robert Jan de Nie @Robbert van Tongeren

After a few hours of debugging yesterday I found that it’s not possible to have an expression field as your display column. That was giving me an error when inserting in the ref table.

This makes sense, since the expression columns aren’t in the DB. But I think it should take the PK as a fallback then. That would be a solution for this problem.

If you could make those adjustments, that would be great.

Regards,

Peter

@Robbert van Tongeren @Robert Jan de Nie (or someone else from Thinkwise)
Can you make your latest version available in the Thinkstore? The version there is still the one with the 'function_input’-error that already has been fixed in this topic, and also the ‘Step 19b’ is missing.

Goal

Solution

Sign up

Login to the Thinkwise Software Community

Scanning file for viruses.

This file cannot be downloaded