Introduction
In this blog-post i want to show some optimizations for the OpenSQL replacement service.
OpenSQL replacement service
The OpenSQL replacement service replaces tables in OpenSQL-statements with fake-tables. The replacement is done at runtime. This service is quite simple to consume. In ABAP-Unit we could use one of the setup methods to start the replacement and one of the teardown methods to stop the replacement. Listing 1 shows a replacement of table
scarr with a fake-table named
zairline_fake.
CLASS replacement_service_demo DEFINITION FOR TESTING DURATION SHORT
RISK LEVEL HARMLESS.
PRIVATE SECTION.
CLASS-METHODS class_setup.
METHODS read_airlines_from_fake FOR TESTING.
CLASS-METHODS class_teardown.
ENDCLASS.
CLASS replacement_service_demo IMPLEMENTATION.
METHOD class_setup.
DATA: airline_stub TYPE zairline_fake.
cl_osql_replace=>activate_replacement( replacement_table = VALUE #( (
source = 'SCARR' target = 'ZAIRLINE_FAKE' ) ) ).
DELETE FROM zairline_fake.
airline_stub = VALUE #( carrid = 'TG' carrname = 'Thai Airways' currcode = 'THB' ).
INSERT zairline_fake FROM airline_stub.
COMMIT WORK AND WAIT.
ENDMETHOD.
METHOD read_airlines_from_fake.
DATA(expected_airlines) = VALUE scarr_tab( ( carrid = 'TG' carrname = 'Thai Airways' currcode = 'THB' ) ).
SELECT * FROM scarr INTO TABLE @DATA(found_airlines).
cl_abap_unit_assert=>assert_equals( exp = expected_airlines act = found_airlines ).
ENDMETHOD.
METHOD class_teardown.
" stop replacement with empty input parameters
cl_osql_replace=>activate_replacement( ).
ENDMETHOD.
ENDCLASS.
Listing 1: usage of OpenSQL replacement service
Now the OpenSQL replacement service ensures, that
SELECT * FROM scarr is replaced at runtime by
SELECT * FROM zairline_fake.
Why use it?
The OpenSQL replacement service solves a big problem. After copies from production to development systems, i saw many of my unit tests failing and i had to repair them. The reason was overriden test-data. With the replacement service, this problem is history.
But filling the fake-tables with test-data was very time consuming especially for huge tables like
mseg or
resb. The next problem was coming soon. It was very difficult to figure out the exact database records, when the OpenSQL replacement should be integraded in an existing test-class. So i started implementing some optimizations.
Optimization
First of all, i wanted to replace the manual process of copying single field values and paste them in the testclass with a automated algorithm. The automated algorithm should export a set of database records and use the exported data to fill the fake-tables. After some research, i found all necessary elements in the ABAP-language.
With dynamic OpenSQL-queries, the table name and the where-restriction can be set at runtime.
CLASS exporter DEFINITION ABSTRACT.
PUBLIC SECTION.
METHODS export_from_source
IMPORTING
source_table TYPE tabname
where_restriction TYPE string.
ENDCLASS.
CLASS exporter IMPLEMENTATION.
METHOD export_from_source.
DATA: content TYPE REF TO data.
FIELD-SYMBOLS: <content> TYPE STANDARD TABLE.
CREATE DATA content TYPE STANDARD TABLE OF (source_table).
ASSIGN content->* TO <content>.
SELECT * FROM (source_table) INTO TABLE <content> WHERE (where_restriction).
ENDMETHOD.
ENDCLASS.
Listing 2 export with dynamic OpenSQL-queries
With listing 2, it is possible to extract a part of any given dictionary table into an internal table. Now i just needed to save the internal table in some storage, which is connected to the transport system. I found two solution for this task:
- usage of ecatt-test-data containers
- usage of identity transformation with the command
CALL TRANSFORMATION id
As source of the identity transformation i used a internal table with the type
abap_trans_srcbind_tab. That makes the serialization of a set of internal tables possible. The result of the identity transformation was exported as binary object with the function module 'WWWDATA_EXPORT'. This binary object could be connected to the transport system.
With the api in class
cl_apl_ecatt_tdc_api the ecatt-test-data containers were more easier to fill.
In the last step, i created a utility function, which copies the exported content to the fake-table. Like in the export step, it was possible to use dynamic OpenSQL-Statements for this purpose.
CLASS import DEFINITION ABSTRACT.
PUBLIC SECTION.
METHODS import
IMPORTING
fake_table TYPE tabname
where_restriction TYPE string
content TYPE STANDARD TABLE.
ENDCLASS.
CLASS importer IMPLEMENTATION.
METHOD import.
DELETE (fake_table) FROM (where_restriction).
INSERT (fake_table) FROM TABLE content.
ENDMETHOD.
ENDCLASS.
Listing 3 import with dynamic OpenSQL-statements
The full solution is published at
https://github.com/bunysae/abap_db_preparator. I used it to integrate the OpenSQL replacement service in many of my unit-tests.
