Reverse Engineering a catalog table to a hdbdd file

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);
    
    v_crlf = BINTOSTR(HEXTOBIN('0D0A'));
    
    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