Tuesday, 05 November 2019 20:20

Reverse Engineering a catalog table to a hdbdd file

Written by https://blogs.sap.com/2019/11/06/reverse-engineering-a-catalog-table-to-a-hdbdd-file/
Rate this item
(0 votes)

Recently I wanted to create a hdbdd file with multiple entities, based on a virtual table. This code works with any table though, as it is based on the table_columns dictionary

view.

This procedure creates the text for a single Entity, so use it in another procedure which calls it once per table and add the boiler plate text for a complete hdbdd file.

I took extra care on the data types but it seems some data types cannot be used in CDS, like the CHAR/NCHAR (fixed length strings) or BOOLEAN.

Should work with all Hana versions 1.0 included.

CREATE PROCEDURE "create_hdbdd" ( p_schema_name nvarchar(256), p_table_name nvarchar(256), out p_text nclob ) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER READS SQL DATA AS BEGIN declare v_line nvarchar(500) array; declare v_current_line nvarchar(500); declare v_data_type nvarchar(30); declare v_count integer; 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); select BINTOSTR( HEXTOBIN('0D0A') ) into v_crlf from dummy; for r_row as c1 do if r_row.is_primary_key = 'TRUE' then v_current_line := ' key '; else v_current_line := ' '; end if; v_current_line = rpad( v_current_line || r_row.column_name, 40) || ': '; if r_row.data_type_name = 'ALPHANUM' then v_data_type := 'hana.ALPHANUM(' || r_row.length || ')'; elseif r_row.data_type_name = 'BIGINT' then v_data_type := 'Integer64'; elseif r_row.data_type_name = 'BINARY' then v_data_type := 'hana.BINARY(' || r_row.length || ')'; elseif r_row.data_type_name = 'BINTEXT' then v_data_type := 'LargeBinary'; -- ????????????? elseif r_row.data_type_name = 'BLOB' then v_data_type := 'LargeBinary'; elseif r_row.data_type_name = 'BOOLEAN' then v_data_type := 'Integer'; -- ????????????? elseif r_row.data_type_name = 'CHAR' then v_data_type := 'hana.VARCHAR(' || r_row.length || ')'; elseif r_row.data_type_name = 'CLOB' then v_data_type := 'hana.CLOB'; elseif r_row.data_type_name = 'DATE' then v_data_type := 'LocalDate'; elseif r_row.data_type_name = 'DECIMAL' then if r_row.length = 0 then v_data_type := 'DecimalFloat'; else v_data_type := 'Decimal(' || r_row.length || ', ' || r_row.scale || ')'; end if; elseif r_row.data_type_name = 'DOUBLE' then v_data_type := 'BinaryFloat'; elseif r_row.data_type_name = 'INTEGER' then v_data_type := 'Integer'; elseif r_row.data_type_name = 'NCHAR' then v_data_type := 'String(' || r_row.length || ')'; -- ?? elseif r_row.data_type_name = 'NCLOB' then v_data_type := 'LargeString'; elseif r_row.data_type_name = 'NVARCHAR' then v_data_type := 'String(' || r_row.length || ')'; elseif r_row.data_type_name = 'REAL' then v_data_type := 'hana.REAL'; elseif r_row.data_type_name = 'SECONDDATE' then v_data_type := 'UTCDateTime'; elseif r_row.data_type_name = 'SHORTTEXT' then v_data_type := 'String(' || r_row.length || ')'; elseif r_row.data_type_name = 'SMALLDECIMAL' then v_data_type := 'hana.SMALLDECIMAL'; elseif r_row.data_type_name = 'SMALLINT' then v_data_type := 'hana.SMALLINT'; elseif r_row.data_type_name = 'ST_GEOMETRY' then v_data_type := 'hana.ST_GEOMETRY'; elseif r_row.data_type_name = 'ST_POINT' then v_data_type := 'hana.ST_POINT'; elseif r_row.data_type_name = 'TEXT' then v_data_type := 'LargeString'; elseif r_row.data_type_name = 'TIME' then v_data_type := 'LocalTime'; elseif r_row.data_type_name = 'TIMESTAMP' then v_data_type := 'UTCTimestamp'; elseif r_row.data_type_name = 'TINYINT' then v_data_type := 'hana.TINYINT'; elseif r_row.data_type_name = 'VARBINARY' then v_data_type := 'Binary(' || r_row.length || ')'; elseif r_row.data_type_name = 'VARCHAR' then v_data_type := 'hana.VARCHAR(' || r_row.length || ')'; end if; v_current_line := v_current_line || :v_data_type; if r_row.is_nullable = 'FALSE' then v_current_line := v_current_line || ' not null'; end if; v_current_line := v_current_line || ';'; v_line[CARDINALITY(:v_line)+1] = :v_current_line; end for; p_text = ' @Catalog.tableType: #COLUMN' || v_crlf || ' Entity ' || :p_table_name || ' {' || v_crlf; for v_count in 1..CARDINALITY(:v_line) do p_text = p_text || :v_line[:v_count] || v_crlf; end for; p_text = p_text || ' }' || v_crlf; END
Read 8 times

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.