Alpaca - Unit tests over Hana

[former_member199541]

How Alpaca burn ?

Its all start when we was asked to move some code from java into db store procedure.

Usually when writing a code in java we use the TDD (Test Driven Development Methodology) and write unit test together with the development process .

Doing this in java its easy and we are using JUnit libraries for it.

However, There is no tool or framework that will allow me to write unit test on Hana db and there is no way to work TDD while developing store procedure and function in Hana .

Of course I can run Junit test that connect to Hana or write some XS code that check hana , but I wanted to test my code within the language itself without having other layers .

Inno Jam

We have in SAP Labs Israel contest that called "Inno Jam", each employee in the lab can work 3 days on an idea he or his friends thinnk of .
After the 3 days, every team present a demo (it must be demo) in only 6 minutes that show their idea working.

It was my first innoJam, the atmosphere was great , pizza and beers was served until night.

I choose the name ALPCA for my idea and 3 other employees from my team join too.

(from left to right: Sapir Golan, Ido Mosseri(me), Rony Lahav, Avi Klein)

Alpaca Target

We had 3 missions for Alpaca

1. Testing and working tdd within Hana
2. Insert the testing into quality process within Jenkins
3. Have a nice Alpaca report via mail

Alpaca Idea

The main idea was to create a framework that provide us a list of assert db functions that will help us testing our hana code.

By running the assert functions we will get a report record that contain the fields : tested object(name of the store procedure/ funtion), test name (for exmaple: "1+1=4"), test status (paas or fail) and messege .

So, user who want to work TDD , can start with creating the tests inside dedicated test store procedure, execute the test , fail , and fix the tested SP.

it will be more clear after looking at the  example.

Example

we want to add 'multi' functionalaty to the SP CALC (the sp calc get 2 items and action and return the result of the action on the items).

the currewnt situation is that we have the action plus and minus :

create  procedure "CALC" (  in item1 INTEGER, in item2 INTEGER ,

 in action varchar(256), out result INTEGER)

AS

    BEGIN

 if action = 'plus' then

 result := item1 + item2;

 end if;

 if action = 'minus' then

 result := item1 - item2;

    end if;

  

END;

now, lets have a look on the developer test SP. we have an ALPACA test that check the result of running the calc SP with 1, 3 and 'plus' .

create  procedure "TEST_CALC" ( out result TEST_RESULT)

AS

    sp_result INTEGER;

  

    BEGIN

 call "CALC"(1,3,'plus',sp_result)   ;

 call "ASSERT_TRUE" ('CALC','test 1+3= 4',sp_result,4,?);

 result = select * from "TEST_RESULTS" where tested_object = 'CALC' ;

END;

now, by calling :

CALL "TEST_CALC"(?);

we will get the output :

Now, lets start the TDD...

first we will write the test, so we add to TEST_CALC the lines :

call "CALC"(2,3,'multi',sp_result)   ;

call "ASSERT_TRUE" ('CALC','test 2*3=6',sp_result,6,?);

Lets run the test :

CALL "TEST_CALC"(?);

Ohhhh we get Exception , but Hey, this is what was expected since the multi action is not implemented yet ...

ok, lets develop the multi action, we will add to the calc sp the lines :

if action = 'multi' then

 result := item1 * item2;

end if;

now, lets run it again

CALL "TEST_CALC"(?);

Hurrey !!

lets add test that should fail and add those lines to TEST_CALC:

call "CALC"(2,5,'multi',sp_result)   ;

call "ASSERT_TRUE" ('CALC','test 2*5=9',sp_result,9,?);

In the same way we implement the ASSERT_TRUE function we also implement the ASSERT_EXIST that check if there is a record in a table.

Example:

CALL  ASSERT_EXIST ('ADD_PURCHASE'(sp name),

'Check new record in PURCHASES table '(test description),

'PURCHASES' (table name),

' customer_name=''IDO'' and product_name = ''BIKE'' ' (where clause),?);

Appendix

in 3 days we did all the development including adding suites(that contain several tests sp) , connecting the test suites to Jenkins and sending a report by email .

(example of email generated by Alpaca)

The Alpaca project is not ready for customers use since we need to implement more functionality over the  framework.

However,It can give huge benifit to the hana developers .

BTW

We didnt win the contest however we had a lot of fun implementing this idea  ..

[former_member185340]

Hi Ido,

thanks for the post.

I am interested in this topic. How did you integrate the test results with Jenkins? Also, on the HANA part, the post seems to be missing how you defined the stored proc ASSERT_TRUE.

Thanks,

Orel

[former_member199541]

Hi ,

we createde test suites store procedure that run the tests we would like to run from jenkins.

we created java code that run the suite, then parse the result (csv) into email.

the suite sp :

create  procedure "ALPACA"."TEST_PACKAGE" ( out result ALPACA.TEST_RESULT)

AS

BEGIN

       DELETE FROM "ALPACA"."TEST_RESULTS";

       CALL "ALPACA"."TEST_CALC"(?);

       CALL "ALPACA"."TEST_ADD_PURCHASE"(?);

       result = SELECT * from "ALPACA"."TEST_RESULTS";

END

the asssert function  :

CREATE PROCEDURE "ASSERT_TRUE"(in tested_object_in varchar(256), in test_name_in varchar(256) , in item1 INT, in item2 INT, out result TEST_RESULT)  AS

begin

delete from "TEST_RESULTS" where tested_object = tested_object_in and  test_name = test_name_in ;

IF item1 = item2 THEN

       insert into "TEST_RESULTS" values('1',tested_object_in,test_name_in,' SUCCESS');

ELSE

       insert into "TEST_RESULTS" values('0',tested_object_in,test_name_in,' FAIL');

END IF;

result = select * from "TEST_RESULTS" where tested_object = tested_object_in and  test_name = test_name_in ;

end;

Please let me know if you need more help.

Ido

[former_member199541]

and this is the assert_exist sp :

CREATE PROCEDURE "ALPACA"."ASSERT_EXIST"(in tested_object_in varchar(256), in test_name_in varchar(256) , table_name varchar(256), where_clause varchar(256), out result ALPACA.TEST_RESULT)

AS

       v_sql varchar(256);

       v_sql_tmp varchar(256);

       v_count INTEGER;

BEGIN

       delete from "ALPACA"."TEST_RESULTS" where tested_object = tested_object_in and  test_name = test_name_in ;

       delete from "ALPACA"."TMP";

       v_sql := 'insert into ALPACA.TMP select count (*) from ' || table_name || ' where ' || where_clause;

       exec v_sql;

       select tmp_int

       into v_count

       from "ALPACA"."TMP";

       IF v_count > 0 THEN

              insert into "ALPACA"."TEST_RESULTS" values('1',tested_object_in,test_name_in,' SUCCESS');

       ELSE

              insert into "ALPACA"."TEST_RESULTS" values('0',tested_object_in,test_name_in,' FAIL');

       END IF;

       result = select * from "ALPACA"."TEST_RESULTS" where tested_object = tested_object_in and  test_name = test_name_in ;

END;