I have created a procedure creating a 1:1 view for a source object. This is quite typical for the first abstraction layer l1, the ingestion layer, which are essentially views on top of the tables.
Creating these views is cumbersome as every single table, every column needs to be specified. Or this function is called for each object. It returns the various versions on how you might create a view. To be precise, it returns
- The create-view SQL command as nclob.
- The content for a XS hdbview file as nclob.
- The content for a XSA hdbview file as nclob.
- The content for a hdbdd file as nclob.
The code is also prepared to manage cases where the view is slightly different from the source, e.g. adding or removing a column.
PROCEDURE "USER1"."create_hdbview" (
p_schema_name nvarchar(256),
p_table_name nvarchar(256),
p_prefix nvarchar(256),
p_target_schema_name nvarchar(256),
p_target_prefix nvarchar(256),
out p_text_create_view nclob,
out p_text_hdbview_xs nclob,
out p_text_hdbview_xsa nclob,
out p_text_hdbdd nclob
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA AS
BEGIN
declare v_line nvarchar(500) array;
declare v_data_type nvarchar(30);
declare v_count integer;
declare v_table_name nvarchar(256);
declare v_view_select nclob;
declare cursor c1 for
select
c.column_name,
c.data_type_name,
c.length,
c.scale,
c.is_nullable,
c.comments,
p.is_primary_key
from table_columns c
left outer join constraints p
on (p.is_primary_key = 'TRUE' and
p.schema_name = c.schema_name and
p.table_name = c.table_name and
p.column_name = c.column_name)
where c.schema_name = :p_schema_name and
c.table_name = :p_table_name
order by p.position nulls last, c.position;
declare v_crlf NVARCHAR(2);
v_crlf = BINTOSTR( HEXTOBIN('0D0A') );
v_table_name = substring(p_table_name, length(p_prefix)+1);
for r_row as c1 do
v_line[CARDINALITY(:v_line)+1] = ' "' || r_row.column_name || '"';
end for;
v_view_select = 'select ' || v_crlf;
for v_count in 1..CARDINALITY(:v_line) do
if v_count > 1 then
v_view_select = v_view_select || ',' || v_crlf;
end if;
v_view_select = v_view_select || :v_line[:v_count];
end for;
v_view_select = v_view_select || v_crlf;
v_view_select = v_view_select || 'from "' ||
p_schema_name || '"."' || p_table_name || '"';
p_text_create_view = 'create view "' ||
p_target_schema_name || '"."' || p_target_prefix ||
v_table_name || '" as ' || v_crlf ||
v_view_select || ';' || v_crlf || v_crlf;
p_text_hdbview_xsa = 'view "' || p_target_prefix || v_table_name ||
'" as ' || v_crlf || v_view_select || v_crlf;
p_text_hdbview_xs = 'schema="' || p_target_schema_name ||
'";' || v_crlf;
p_text_hdbview_xs = p_text_hdbview_xs || 'public=false;' || v_crlf;
p_text_hdbview_xs = p_text_hdbview_xs || 'query="' ||
replace(v_view_select, '"', '\"') ||
'";' || v_crlf;
p_text_hdbdd = ' View ' || p_target_prefix ||
v_table_name || ' as ' || v_crlf;
p_text_hdbdd = p_text_hdbdd || ' select from "' ||
p_table_name || '" {' || v_crlf;
for v_count in 1..CARDINALITY(:v_line) do
if v_count > 1 then
p_text_hdbdd = p_text_hdbdd || ',' || v_crlf;
end if;
p_text_hdbdd = p_text_hdbdd || ' ' || :v_line[:v_count];
end for;
p_text_hdbdd = p_text_hdbdd || v_crlf;
p_text_hdbdd = p_text_hdbdd || ' };' || v_crlf;
END
Bluesky
