Solved

Copy queries from views in upcycled databases

  • 28 August 2019
  • 1 reply
  • 84 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!
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:


 

--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

1 reply

Userlevel 5
Badge +2

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

 

Reply