example with Open SQL Test Double Framework

[Michael_Keller]

Dear community, thanks to the blogs of bunysae and suketu.dave, I became interested in the test doubles topic again. Unfortunately, ABAP unit tests don't yet play the role in my day-to-day work as I would like - still, I'm sticking to the topic.

One important point in this context is the creation of test doubles (mocks) of database tables. So far I have had no idea how to do this. Because I had some time yesterday left, I worked with the Open SQL Test Double Framework. I've built a very simple example to see how it works in principle (no real world example, sorry). It's just an "one hour code hack", done on my SAP NetWeaver 7.52 Trial, that I want to share with you. Ok, let's get started 🙂

I wrote a small, global class to read client customizing from table T000.

CLASS zcl_system_clients DEFINITION

                         PUBLIC

                         FINAL

                         CREATE PUBLIC.



  PUBLIC SECTION.

    TYPES clients TYPE TABLE OF t000 WITH KEY mandt.



    METHODS get_client

      IMPORTING

        client        TYPE mandt

      RETURNING

        VALUE(result) TYPE clients.

ENDCLASS.



CLASS zcl_system_clients IMPLEMENTATION.

  METHOD get_client.

    SELECT * FROM t000 AS clients

             INTO TABLE result

             WHERE mandt = client.

  ENDMETHOD.

ENDCLASS.

That's the content of database table T000 in my SAP NetWeaver 7.52 Trial.

content of database table T000

Here is my local test class.

CLASS ltc_test DEFINITION FINAL

               FOR TESTING

               DURATION SHORT

               RISK LEVEL HARMLESS.



  PUBLIC SECTION.

    METHODS get_client_000 FOR TESTING.

    METHODS get_client_001 FOR TESTING.

    METHODS get_client_999 FOR TESTING.



  PRIVATE SECTION.

ENDCLASS.



CLASS ltc_test IMPLEMENTATION.

  METHOD get_client_000.

    DATA(cut) = NEW zcl_system_clients( ).

    DATA(result) = cut->get_client( '000' ).

    cl_aunit_assert=>assert_not_initial( result ).

  ENDMETHOD.



  METHOD get_client_001.

    DATA(cut) = NEW zcl_system_clients( ).

    DATA(result) = cut->get_client( '001' ).

    cl_aunit_assert=>assert_not_initial( result ).

  ENDMETHOD.



  METHOD get_client_999.

    DATA(cut) = NEW zcl_system_clients( ).

    DATA(result) = cut->get_client( '999' ).

    cl_aunit_assert=>assert_not_initial( result ).

  ENDMETHOD.

ENDCLASS.

This is the result when running the tests.

The tests for reading the customizing of clients 000 and 001 are ok. Client 999 doesn't exist, the test fails. That's fine - there is no client 999 in my system.

So either I have to create a client 999 in the database table T000 or I have to rethink my test. Another solution is to use a test double for the database table. Fortunately, this is possible with the class CL_OSQL_TEST_ENVIRONMENT ... and exactly what I want to try.

Here's the revision of my test class. Pay attention to the PRIVATE SECTION and the methods CLASS_SETUP and CLASS_TEARDOWN.

CLASS ltc_test DEFINITION FINAL

               FOR TESTING

               DURATION SHORT

               RISK LEVEL HARMLESS.



  PUBLIC SECTION.

    METHODS get_client_000 FOR TESTING.

    METHODS get_client_001 FOR TESTING.

    METHODS get_client_999 FOR TESTING.



  PRIVATE SECTION.

    CLASS-DATA osql_test_environment TYPE REF TO if_osql_test_environment.

    CLASS-METHODS class_setup.

    CLASS-METHODS class_teardown.

ENDCLASS.





CLASS ltc_test IMPLEMENTATION.

  METHOD class_setup.

    osql_test_environment = cl_osql_test_environment=>create( VALUE #( ( 'T000' ) ) ).

    DATA(clients_test_data) = VALUE zcl_system_clients=>clients( ( mandt = '000' mtext = 'Test double' )

                                                                 ( mandt = '001' mtext = 'Test double' )

                                                                 ( mandt = '999' mtext = 'Test double' ) ).

    osql_test_environment->insert_test_data( clients_test_data ).

  ENDMETHOD.



  METHOD class_teardown.

    osql_test_environment->destroy( ).

  ENDMETHOD.



  METHOD get_client_000.

    DATA(cut) = NEW zcl_system_clients( ).

    DATA(result) = cut->get_client( '000' ).

    cl_aunit_assert=>assert_not_initial( result ).

  ENDMETHOD.



  METHOD get_client_001.

    DATA(cut) = NEW zcl_system_clients( ).

    DATA(result) = cut->get_client( '001' ).

    cl_aunit_assert=>assert_not_initial( result ).

  ENDMETHOD.



  METHOD get_client_999.

    DATA(cut) = NEW zcl_system_clients( ).

    DATA(result) = cut->get_client( '999' ).

    cl_aunit_assert=>assert_not_initial( result ).

  ENDMETHOD.

ENDCLASS.

This is the result when rerunning the tests.

The test double seems to have worked. Unfortunately I couldn't find a way to easily and quickly trace whether the test double or the database table were accessed. Some hints from the experts?

Ok, that's all I have to say about my little research trip 🙂 Who is also interested in the topic: This blog and this page helped me.

 

Best regards, thanks for reading and please stay healthy

Michael

 

P.S.: Please support the virtual wishing well.

P.S.S.: Not tired of reading blogs? Check this blog by bfeeb8ed7fa64a7d95efc21f74a8c135.

[MBartsch71]

Hi Michael,

 

thanks for this post, I will try this also. For now I just worked with ECATT Containers and mock data.

Your example is perfect for testing the SQL statements itself.

 

Kind regards

Matthias

[suketu_dave]

Hi Michael,

Thanks for the post and I'm glad to know that you've developed an interest in this skill area.

Regarding the question you posed at the end of your article - How to easily and quickly trace whether the test double or the database table were accessed, here is one quick suggestion from my side:

Just immediately either before or after the assert statements, implement a SELECT * on the DB Table you would like to check the entries for - in this case its t000, and fetch it into an internal table (say lt_t000) like SELECT * FROM t000 INTO TABLE DATA(lt_t000)

Then place a debugger point on this SELECT query and check for the values of lt_t000.

Happy Learning!

Best Regards,
Suketu Dave.

[Michael_Keller]

Hi Matthias, so far, I had no idea how to test methods with access to database tables. The appropriate data would have to be available in the database tables for this. It's difficult to ensure. You would have separated test data and test cases. Not good. With the Open SQL Test Double Framework you can keep both together. I hope to do more with it. It's a big step for me to change work behavior to base on unit tests 😉

[Michael_Keller]

Hi Suketu, thanks for the hint. I'll try that out.

[Nadia_S]

Hi Michael,

you can put some dummy values in your osql test double, that do not exist in your original db table, like you did with 999 and check if your result line is = ( mandt = 999 mtext = ‘Text double’ ) with the method assert_equals.

[Michael_Keller]

Hi Nadejda, that's an approach 🙂 However, if you transfer your application to another SAP ERP system of another customer, there could actually be a client 999 there. Admittedly, very unlikely 😉

[jdloranger]

I have begun to use this same test double methodology in my own testing.  It is very handy once you get the hang of it.

i would second nadejdasacara.  Put in some test values you know to be bogus in the real table.  Debugging can also be done, as was already suggested.

regards,

justin

[sergey_muratov]

I think that Open SQL Test Double Framework is a cool feature. But it's available since 7.5*. I found helpful techniques showed in book "Test-Driven Development in Microsoft .NET". In chapters 5 and 10 authors disscussed about utilizing unit testing framework for testing DAL layer. In examples they demonstrate Gateway pattern for organizing DAL and Transaction Services for control real DB changes in fixture methods. Adaptation of these techniques works from ABAP 7.02 and above.

[Michael_Keller]

Very good hint, Sergey! Some years ago, I started to provide my customizing for an application via class (object). That had a lot of impacts. The whole representation switched to class.

Today, even simple ALVs I've written have a class to provide the data of the list. The list is itself an object! It's build by different other classes with a clear focus of every involved class (composition).

Life is a lot easier when you think of a lot of things as a class or interface 🙂

[Nadia_S]

Comparing the result line, you check also the text and I guess no real client is called Text double.

[Sandra_Rossi]

To be exact, Open SQL Test Double Framework is available since 7.52.

[abo]

On a system too old to have the OSQL TDF, what would be the best approach to try and introduce some unit testing? I've got a smallish program to rewrite and I'm planning to perhaps add tests for the calculation logic and forget about the extraction part.

[Sandra_Rossi]

I don't know. Like any other dependency, by defining an interface? (productive class: implements OSQL, test class: direct data injection)

[alexandr_razinkin]

Please check out my new github repository https://github.com/raaleksandr/zsql_test_double_framework

I have tried to create someting like virtual database on ABAP, it can work with Open SQL selects and updates. It works starting from 7.02.

My goal was to simulate Open SQL Test Double Framework on older systems

[joachimrees1]

Very nice and short example on how to use CL_OSQL_TEST_ENVIRONMENT. -> that's what I searched for, after German Wikipedia told me

"Ab ABAP 7.52 können mittels der Klasse CL_OSQL_TEST_ENVIRONMENT Datenbank-Tabellen gemockt werden.".

I have a use case of "I want to UnitTest something, but the code under test relies on a DB select", so I think your example is just what I needed!

Thanks a lot!
Joachim

[Michael_Keller]

Dear joachim.rees3, you are always welcome and I thank you for your words of encouragement!

For me, I try to start with a very basic and simple example before I use a technology in important developments. So why not share it with the rest of the SAP Community? 🙂