As I’ve discussed the Tribal SITS Data Model does not have the nicest table and fields names and has a huge number of lookup tables. In this situation I like to build a layer of views over the base tables to provide a friendlier interface to build queries on. Still this is a bunch of work – could I auto create these views from the SITS data dictionary? Short answer: Yes. But they’ll probably need a bit of manual tidying up. That said I can produce working views that will still save a lot of effort.
The SITS data dictionary tables provide everything I’ll need to build a basic view on any table and associated lookup tables. My goal will be to produce a view of all fields in a table using the more verbose column description as the view column names. And that joins to all lookup tables. And includes the lookup table description column for all lookup values. All the dictionary data required will come from this view which has already been discussed:
CREATE view vw_desc_sits_entity as select d.dct_code dictionary , e.ent_prfx + e.ent_code table_name , e.ent_code entity_code , e.ent_name , f.fld_seqn , f.fld_code column_name , f.fld_name description , f.fld_indx indexed , f.fld_mand mandatory , f.fld_type field_type , u.udf_name , u.udf_type , f.fld_indb in_database , le.ent_prfx + le.ent_code lookup_table , lf.fld_code lookup_PK , 'left join ' + le.ent_prfx + le.ent_code + ' ' + le.ent_code + ' on ' + e.ent_code + '.' + f.fld_code + ' = ' + le.ent_code + '.' + lf.fld_code lookup_join , u.udf_prof , f.fld_help help_text from men_dct d join men_ent e on d.dct_code = e.ent_dctc join dbo.men_fld f on e.ent_dctc = f.fld_dctc and e.ent_code = f.fld_entc left join men_fld lf on lf.fld_dctc = f.fld_dct1 and lf.fld_entc = f.fld_ent1 and lf.fld_code = f.fld_fld1 left join men_ent le on lf.fld_dctc = le.ent_dctc and lf.fld_entc = le.ent_code left join men_udf u on f.fld_dctc = u.udf_dctc and f.fld_entc = u.udf_entc and f.fld_code = u.udf_fldc
So I’ll create a stored procedure that will take a SITS table name and the dictionary the entity is in. It will then return a create view script for that table.
create procedure create_sits_view @sits_table varchar(255) , @sits_dictionary varchar(255) as
First I’ll get the entity code of the table and the entity name. The entity name will be cleaned up a bit and used as the view name:
declare @entity_code varchar(255) declare @view_name varchar(255) select @view_name = 'vw_' + lower(replace(max(ent_name), ' ', '_')) , @entity_code = max(entity_code) from vw_desc_sits_entity where table_name = @sits_table
Then I’ll initialise a select clause and a from clause variable. In the from clause the entity code will be used as the table alias:
declare @select_clause varchar(max) = 'select ' declare @from_clause varchar(max) = 'from ' + @sits_table + ' ' + @entity_code + ' '
Next the rest of the variables I’ll need:
| declare @column_name as varchar(255) | a base table column name |
|---|---|
| declare @column_name_desc as varchar(255) | cleaned up column name to be used as view column name |
| declare @view_column_name as varchar(255) | view column name in [] |
| declare @udf_column_name as varchar(255) | cleaned up UDF name to be used as view column name |
| declare @lookup_table as varchar(255) | a lookup table name |
| declare @lookup_join as varchar(255) | a join from the base table to a lookup table |
| declare @lookup_entity_code as varchar(255) | the entity code of a lookup table |
| declare @lookup_column_name as varchar(255) | a column from the lookup table |
| declare @lookup_view_column_name as varchar(255) | cleaned up lookup column name to be used as view column name |
Then I’ll setup a cursor to loop through all columns in the table. Any columns in the dictionary that are not in the database are excluded by in_database = ‘Y’:
declare view_columns cursor for select column_name , lower(replace(description, ' ', '_')) column_name_desc , '[' + lower(replace(description, ' ', '_')) + '_' + column_name + ']' view_column_name , '[' + lower(replace(udf_name, ' ', '_')) + '_' + column_name + ']' udf_column_name , lookup_table , lookup_join from vw_desc_sits_entity where table_name = @sits_table and dictionary = @sits_dictionary and in_database = 'Y' order by fld_seqn OPEN view_columns FETCH NEXT FROM view_columns INTO @column_name, @column_name_desc, @view_column_name, @udf_column_name, @lookup_table, @lookup_join WHILE @@FETCH_STATUS = 0 BEGIN
For each column I’ll add the column to the select clause. If the field is a UDF I’ll use the configured UDF field name as the view column name:
set @view_column_name = isnull(@udf_column_name, @view_column_name) set @select_clause = @select_clause + @entity_code + '.' + @column_name + ' ' + @view_column_name + ', '
Next if the column has a lookup table I’ll retrieve the lookup table entity code and the name column from the lookup table. Most lookup tables have a name column. If there is not one I’ll create the join to the table anyway but won’t include any columns from the lookup table in the select clause:
select @lookup_entity_code = max(entity_code) , @lookup_column_name = max(column_name) , @lookup_view_column_name = '[' + @column_name_desc + '_' + lower(replace(max(description), ' ', '_')) + ']' from vw_desc_sits_entity where table_name = @lookup_table and column_name like '%name'
Now I’ll add in the join to the lookup table to the from clause and the name column, if there is one, to the select clause. In the data dictionary the primary key on a table lookups up to itself so I need to exclude this case:
if @lookup_entity_code != @entity_code begin set @from_clause = @from_clause + @lookup_join + ' ' select @select_clause = @select_clause + @lookup_entity_code + '.' + @lookup_column_name + ' ' + @lookup_view_column_name + ', ' end
Finally I close the cursor loop and return the complete create view query:
FETCH NEXT FROM view_columns INTO @column_name, @column_name_desc, @view_column_name, @udf_column_name, @lookup_table, @lookup_join end close view_columns deallocate view_columns select 'create view ' + @view_name + ' as ' + left(@select_clause, len(@select_clause) - 1) + ' ' + @from_clause
Note that I’ve used select to output rather than print as print is limited to 8000 characters and SITS table definitions are BIG! Also not that some field names in the SITS data dictionary are not unique so I’ve had to suffix all the view column names with the field code. This is a shame but still produces a much better column name. Still it would be good to only do this on the non unique field names rather than them all. I’ve tested with a few of the big SITS tables and it produces working create view scripts. For example:
create_sits_view 'ins_stu', 'srs'
Finally here’s the complete stored procedure code:
create procedure create_sits_view @sits_table varchar(255) , @sits_dictionary varchar(255) as declare @entity_code varchar(255) declare @view_name varchar(255) select @view_name = 'vw_' + lower(replace(max(ent_name), ' ', '_')) , @entity_code = max(entity_code) from vw_desc_sits_entity where table_name = @sits_table declare @select_clause varchar(max) = 'select ' declare @from_clause varchar(max) = 'from ' + @sits_table + ' ' + @entity_code + ' ' declare @column_name as varchar(255) declare @column_name_desc as varchar(255) declare @view_column_name as varchar(255) declare @udf_column_name as varchar(255) declare @lookup_table as varchar(255) declare @lookup_join as varchar(255) declare @lookup_entity_code as varchar(255) declare @lookup_column_name as varchar(255) declare @lookup_view_column_name as varchar(255) declare view_columns cursor for select column_name , lower(replace(description, ' ', '_')) column_name_desc , '[' + lower(replace(description, ' ', '_')) + '_' + column_name + ']' view_column_name , '[' + lower(replace(udf_name, ' ', '_')) + '_' + column_name + ']' udf_column_name , lookup_table , lookup_join from vw_desc_sits_entity where table_name = @sits_table and dictionary = @sits_dictionary and in_database = 'Y' order by fld_seqn OPEN view_columns FETCH NEXT FROM view_columns INTO @column_name, @column_name_desc, @view_column_name, @udf_column_name, @lookup_table, @lookup_join WHILE @@FETCH_STATUS = 0 BEGIN set @view_column_name = isnull(@udf_column_name, @view_column_name) set @select_clause = @select_clause + @entity_code + '.' + @column_name + ' ' + @view_column_name + ', ' if @lookup_table is not null begin select @lookup_entity_code = max(entity_code) , @lookup_column_name = max(column_name) , @lookup_view_column_name = '[' + @column_name_desc + '_' + lower(replace(max(description), ' ', '_')) + ']' from vw_desc_sits_entity where table_name = @lookup_table and column_name like '%name' if @lookup_entity_code != @entity_code begin set @from_clause = @from_clause + @lookup_join + ' ' select @select_clause = @select_clause + @lookup_entity_code + '.' + @lookup_column_name + ' ' + @lookup_view_column_name + ', ' end end FETCH NEXT FROM view_columns INTO @column_name, @column_name_desc, @view_column_name, @udf_column_name, @lookup_table, @lookup_join end close view_columns deallocate view_columns select 'create view ' + @view_name + ' as ' + left(@select_clause, len(@select_clause) - 1) + ' ' + @from_clause