Skip to main content
Solved

Copy queries from views in upcycled databases

  • August 28, 2019
  • 1 reply
  • 144 views

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!

Best answer by Kevin Horst

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:


 

--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 views
declare cursor_voorbeeld cursor local static read_only forward_only
 for
 select dbo.replace_illegal_characters(concat('view_',v.name)) as prog_object_id 
 ,concat('vw_',dbo.replace_illegal_characters(v.name)) as control_proc_id 
 ,dbo.replace_illegal_characters(v.name) as template_id 
 FROM [demo].sys.views v
 order by prog_object_id
-- variables for the cursor
declare @prog_object_id prog_object_id
 ,@control_proc_id control_proc_id
 ,@template_id template_id
-- open the cursor
open cursor_voorbeeld

-- Get the next one
fetch next from cursor_voorbeeld into @prog_object_id, @control_proc_id, @template_id

-- @@FETCH_STATUS = 0 means as long as you can get a new record out of the cursor
while @@FETCH_STATUS = 0
begin

 EXEC task_template_assignment_add
 @project_id = @project_id,
 @project_vrs_id = @project_vrs_id,
 @prog_object_id = @prog_object_id,
 @control_proc_id = @control_proc_id,
 @template_id = @template_id


 --Get the nex view
 fetch next from cursor_voorbeeld into @prog_object_id, @control_proc_id, @template_id

end


-- Close and forget the cursor
close cursor_voorbeeld

deallocate cursor_voorbeeld

 

View original
Did this topic help you find an answer to your question?
This topic has been closed for comments

1 reply

Kevin Horst
Thinkwise blogger
Forum|alt.badge.img+5
  • Thinkwise blogger
  • 33 replies
  • Answer
  • August 29, 2019

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:


 

--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 views
declare cursor_voorbeeld cursor local static read_only forward_only
 for
 select dbo.replace_illegal_characters(concat('view_',v.name)) as prog_object_id 
 ,concat('vw_',dbo.replace_illegal_characters(v.name)) as control_proc_id 
 ,dbo.replace_illegal_characters(v.name) as template_id 
 FROM [demo].sys.views v
 order by prog_object_id
-- variables for the cursor
declare @prog_object_id prog_object_id
 ,@control_proc_id control_proc_id
 ,@template_id template_id
-- open the cursor
open cursor_voorbeeld

-- Get the next one
fetch next from cursor_voorbeeld into @prog_object_id, @control_proc_id, @template_id

-- @@FETCH_STATUS = 0 means as long as you can get a new record out of the cursor
while @@FETCH_STATUS = 0
begin

 EXEC task_template_assignment_add
 @project_id = @project_id,
 @project_vrs_id = @project_vrs_id,
 @prog_object_id = @prog_object_id,
 @control_proc_id = @control_proc_id,
 @template_id = @template_id


 --Get the nex view
 fetch next from cursor_voorbeeld into @prog_object_id, @control_proc_id, @template_id

end


-- Close and forget the cursor
close cursor_voorbeeld

deallocate cursor_voorbeeld

 


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings