Solved

Copy queries from views in upcycled databases

  • 28 August 2019
  • 1 reply
  • 63 views

Userlevel 1
Badge +1
During the upcycling of an application I got to the following problem: The views has been transferred from the upcycling database into the SF correctly but all the programmed logic behind it so the SQL queries hasn't. What I can do is copy the queries manually from the views in the upcycled database into the SF to make it run there. This is a proper solution if there are only some views. But in my case there are many of them, more than a hundred. Is there any possibility to do this automatically f. e. with a script? That would help a lot!
icon

Best answer by Kevin Horst 29 August 2019, 10:00

Hi Hauke,

In the current upcycle script there is no functionality which imports the definition of the views.
There are two reasons for that:

  1. We need to generate the definition of the model first
  2. Every database saves the 'Query' of the view a little bit differently, so most of the time you need to ever slightly change the import script.
Therefore we haven't incorporated it into the main upcycle script yet.

Luckily we do have the functionality available to import the view definitions into your model:


code:
--How to:
--Step 0 First do the main upcyle script and generate the definitions
--Step 1 Fill in the parameters below
--Step 2 Replace DEMO_SF -> SF database
--Step 3 Replace [demo] -> Upcycle databasedemo
Declare @project_id varchar(150) = 'DEMO'
,@project_vrs_id varchar(150) = '1.00'
--Step 4 Execute (F5)

----------First the control procedure for all the views
insert into DEMO_sf..[control_proc]
(project_id
,project_vrs_id
,control_proc_id
,control_proc_desc
,control_proc_type
,assign_type
,code_grp_id
,gen_order_no
,development_status
,priority
,reviewed
,tested
,generated)

select @project_id as project_id
,@project_vrs_id as project_vrs_id
,concat('vw_',v.name) as control_proc_id
,concat('Upcycled ', v.name, ' view') as control_proc_desc
,1 as control_proc_type
,0 as assign_type --Static
,'VIEWS' as code_grp_id
,100 as gen_order_no
,0 as development_status
,7 as priority
,0 as reviewed
,0 as tested
,0 as generated
FROM [demo].sys.views v

----the create the templates with the default definition
insert into DEMO_sf..[control_proc_template]
(project_id
,project_vrs_id
,control_proc_id
,template_id
,template_desc
,order_no
,no_of_object_item
,template_code
,code_generated)
select @project_id as project_id
,@project_vrs_id as project_vrs_id
,concat('vw_',v.name) as control_proc_id
,v.name as template_id
,concat(
'Upcycled definition of the '
, v.name
, ' view'
) as template_desc
,100 as order_no
,1 as no_of_object_item
,right(m.definition
,len(m.definition)
- CHARINDEX('SELECT',m.definition) + 1
) as template_code
,0 as code_generated
FROM [demo].sys.views v
join [demo].sys.schemas s
on s.schema_id = v.schema_id
INNER JOIN [demo].sys.sql_modules m ON m.object_id = v.object_id


--Now allocate the templates with the vies
----First generate the definition!
insert into DEMO_sf..prog_object_item
select @project_id as project_id
,@project_vrs_id as project_vrs_id
,concat('view_',v.name) as prog_object_id
,v.name as prog_object_item_id
,100 as order_no
,concat('vw_',v.name) as control_proc_id
,v.name as template_id
FROM [demo].sys.views v
View original

1 reply

Userlevel 4
Badge +1
Hi Hauke,

In the current upcycle script there is no functionality which imports the definition of the views.
There are two reasons for that:

  1. We need to generate the definition of the model first
  2. Every database saves the 'Query' of the view a little bit differently, so most of the time you need to ever slightly change the import script.
Therefore we haven't incorporated it into the main upcycle script yet.

Luckily we do have the functionality available to import the view definitions into your model:


code:
--How to:
--Step 0 First do the main upcyle script and generate the definitions
--Step 1 Fill in the parameters below
--Step 2 Replace DEMO_SF -> SF database
--Step 3 Replace [demo] -> Upcycle databasedemo
Declare @project_id varchar(150) = 'DEMO'
,@project_vrs_id varchar(150) = '1.00'
--Step 4 Execute (F5)

----------First the control procedure for all the views
insert into DEMO_sf..[control_proc]
(project_id
,project_vrs_id
,control_proc_id
,control_proc_desc
,control_proc_type
,assign_type
,code_grp_id
,gen_order_no
,development_status
,priority
,reviewed
,tested
,generated)

select @project_id as project_id
,@project_vrs_id as project_vrs_id
,concat('vw_',v.name) as control_proc_id
,concat('Upcycled ', v.name, ' view') as control_proc_desc
,1 as control_proc_type
,0 as assign_type --Static
,'VIEWS' as code_grp_id
,100 as gen_order_no
,0 as development_status
,7 as priority
,0 as reviewed
,0 as tested
,0 as generated
FROM [demo].sys.views v

----the create the templates with the default definition
insert into DEMO_sf..[control_proc_template]
(project_id
,project_vrs_id
,control_proc_id
,template_id
,template_desc
,order_no
,no_of_object_item
,template_code
,code_generated)
select @project_id as project_id
,@project_vrs_id as project_vrs_id
,concat('vw_',v.name) as control_proc_id
,v.name as template_id
,concat(
'Upcycled definition of the '
, v.name
, ' view'
) as template_desc
,100 as order_no
,1 as no_of_object_item
,right(m.definition
,len(m.definition)
- CHARINDEX('SELECT',m.definition) + 1
) as template_code
,0 as code_generated
FROM [demo].sys.views v
join [demo].sys.schemas s
on s.schema_id = v.schema_id
INNER JOIN [demo].sys.sql_modules m ON m.object_id = v.object_id


--Now allocate the templates with the vies
----First generate the definition!
insert into DEMO_sf..prog_object_item
select @project_id as project_id
,@project_vrs_id as project_vrs_id
,concat('view_',v.name) as prog_object_id
,v.name as prog_object_item_id
,100 as order_no
,concat('vw_',v.name) as control_proc_id
,v.name as template_id
FROM [demo].sys.views v

Reply