Logical Databases

 
Logical databases are special ABAP programs that retrieve data and make it available to application programs. The most common use of logical databases is still to read data from database tables by linking them to executable ABAP programs.
However, from Release 4.5A, it has also been possible to call logical databases using the function module LDB_PROCESS. This allows you to call several logical databases from any ABAP program, nested in any way. It is also possible to call a logical database more than once in a program, if it has been programmed to allow this. This is particularly useful for programs with type 1.
Logical databases contain Open SQL statements that read data from the database. You do not therefore need to use SQL in your own programs. The logical database reads the program, stores them in the program if necessary, and then passes them line by line to the application program or the function module LDB_PROCESS using an interface work area.
Logical Databases - Views of Data A logical database provides a particular view of database tables in the R/3 System. It is always worth using logical databases if the structure of the data that you want to read corresponds to a view available through a logical database.
The data structure in a logical database is hierarchical. Many tables in the R/3 System are linked to each other using foreign key relationships. Some of these dependencies form tree-like hierarchical structures. Logical databases read data from database tables that are part of these structures.

The diagram illustrates how the R/3 System might represent the structure of a company. A logical database can read the lines of these tables one after the other into an executable program in a sequence which is normally defined by the hierarchical structure. The term logical database is sometimes used to mean not only the program itself, but also the data that it can procure. Tasks of Logical Databases As well as allowing you to read data from the database, logical databases also allow you to program other tasks centrally, making your application programs less complicated. They can be used for the following tasks:

• Reading the same data for several programs.

The individual programs do not then need to know the exact structure of the relevant database tables (and especially not their foreign key relationships). Instead, they can rely on the logical database to read the database entries in the right order during the GET event.

• Defining the same user interface for several programs.

Logical databases have a built-in selection screen. Therefore, all of the programs that use the logical database have the same user interface.

• Central authorization checks

Authorization checks for central and sensitive data can be programmed centrally in the database to prevent them from being bypassed by simple application programs.

• Improving performance

If you want to improve response times, logical databases permit you to take a number of measures to achieve this (for example, using joins instead of nested SELECT statements). These become immediately effective in all of the application programs concerned and save you from having to modify their source code.
 
Structure of Logical Databases 
A logical database is made up of three components (see illustration for further details). They are:

• Structure

The structure defines the data view of the logical database. It determines the structure of the other components and the behavior of the logical database at runtime. The order in which data is made available to the user depends on the hierarchical structure of the logical database concerned.

• Selections

The selections define a selection screen, which forms the user interface of the executable programs that use the logical database. Its layout is usually determined by the structure. You can adapt the selections to your own requirements and also add new ones. When you link a logical database to an executable program, the selections of the logical database become part of the standard selection screen of the program (screen number 1000). If you call a logical database using the function module LDB_PROCESS, the selections are filled using interface parameters.

• Database program

The database program contains the ABAP statements used to read the data and pass it to the user of the logical database. The structure of the database program is a collection of special subroutines. It is determined by the structure and the selections. You can adapt the database program to your own requirements and also extend it. Other components such as documentation, language-specific texts, and user-defined selection screens extend the functions further. Structure The structure of a logical database is usually based on the foreign key relationships between hierarchical tables in the R/3 System. Logical databases have a tree-like structure, which can be defined as follows: · There is a single node at the highest level. This is known as the root node.
· Each node can have one or several branches.
· Each node is derived from one other node.
The nodes must be structures defined in the ABAP Dictionary or data types from a type group. Normally, these are the structures of database tables which the logical database reads and passes to the user for further evaluation. However, it is also possible, and sometimes useful, to use ABAP Dictionary structures without an underlying database. For technical reasons, the maximum number of nodes allowed in the structure of a logical database is 300.
Any executable ABAP program that has a logical database linked to it can contain a GET statement for each node of the structure. When you run the program, the corresponding event blocks are processed in the sequence prescribed by the hierarchical structure of the logical database. If a program does not contain a GET statement for every node of a logical database, the processing passes through all the nodes that lie in the path from the root to the nodes specified by GET statements.
If you call a logical database using the function module LDB_PROCESS, the depth to which the system reads is controlled by an interface parameter.


Suppose LFA1 is the root node, LFBK and LFB1 are branches of LFA1, and LFC1 is a branch of LFB1.

If the executable program contains GET statements for all nodes, the GET events are executed in the order LFA1, LFBK, LFB1, LFC1. If the program only contains a single GET statement for LFB1, the processing only passes through LFA1 and LFB1. Selections The selections in a logical database are defined using the normal statements for defining selection screens, that is, PARAMETERS, SELECT-OPTIONS; and SELECTION-SCREEN. In a logical database, you can also use the additions VALUE-REQUEST and HELP-REQUEST to define specific input and value help. You define the selection screen in a special include program known as the selection include.
When you write programs using a logical database, you can also add your own selections. The standard selection screen then contains the database-specific selections, followed by the program-specific selections that you have defined.
When the system generates the selection screen for an executable program, database-specific selection criteria and parameters are only displayed if you have declared an interface work area for them in your program using the NODES or TABLES statement.


Suppose you have a selection include containing the following lines:
SELECT-OPTIONS SLIFNR FOR LFA1-LIFNR.
PARAMETERS PBUKRS LIKE LFB1-BUKRS FOR TABLE LFB1. The selection criterion SLIFNR is linked to table LFA1, the parameter PBUKRS to table LFB1. If the TABLES statement in an executable program (report) declares LFA1 but not LFB1, SLIFNR is displayed on the selection screen, but PBUKRS does not appear. The selection screen of a logical database can contain dynamic selections as well as static ones. Dynamic selections are extra, user-defined selections that the user can make as well as using the static selections defined in the selection include. To improve performance, you should always use this option instead of reading more data than you need and then sorting it out in the application program.
To make dynamic selections available for the node <node> of a logical database, the selection include must contain the following statement: SELECTION-SCREEN DYNAMIC SELECTIONS FOR NODE|TABLE <node>. If the node <node> is requested by the user of the logical databases, the dynamic selections are included in the selection screen. A user can then choose Dynamic selections to enter extra selections for the corresponding fields. If you call the logical database using the function module LDB_PROCESS, you can pass a corresponding parameter. You can use these selections in dynamic statements in the logical database program to read data. The values of the program-specific selection criteria that you defined for a node for which dynamic selections were available are also passed to the logical database. The user can also define the fields for dynamic selections as a selection view for the logical database.
The selection screen of a logical database is part of the standard selection screen (number 1000) of the executable program to which the logical database is attached. It has a standardized layout - the selection criteria and parameters appear on separate lines in the order in which they were declared. You can change the layout using the SELECTION-SCREEN statement.
The runtime environment generates the selection screen with number 1000 for every program in which the attributes do not contain a different selection screen version. You can prevent certain input fields from the selection screen of a logical database from appearing on the selection screen by defining selection screen versions with a screen number lower than 1000 in the selection include, and entering this version number in the program attributes. By pressing F4 there, you can get an overview of the selection screen versions defined in the logical database concerned. To define a selection screen version, use the statements SELECTION-SCREEN BEGIN|END OF VERSION. Within these statements you can use SELECTION-SCREEN EXCLUDE to specify fields that you do not want to appear on the selection screen.
If the attributes of an executable program contain the number of a selection screen version, the version is used in the standard selection screen. Although the input fields that you excluded from the selection screen are not displayed, the corresponding selections still exist, and you can still edit them in the program or by calling the function module LDB_PROCESS.
Database Program The name of the database program of a logical database <ldb> conforms to the naming convention SAPDB<ldb>. It serves as a container for subroutines, which the ABAP runtime environment calls when a logical database is processed. The sequence of the calls and their interaction with the events in executable programs or the function module LDB_PROCESS depends on the structure of the logical database.
A logical database program usually contains the following subroutines:

• FORM LDB_PROCESS_INIT

Called once only before the logical database is processed. It prepares it to be called more than once by the function module LDB_PROCESS.

• FORM INIT

Called once only before the selection screen is processed.

• FORM PBO

Called before the selection screen is displayed, each time it is displayed. Consequently, it is only called when you use the logical database with an executable program, not with the function module LDB_PROCESS.

• FORM PAI

Called when the user interacts with the selection screen. Consequently, it is only called when you use the logical database with an executable program, not with the function module LDB_PROCESS. The interface parameters FNAME and MARK are passed to the subroutine.
FNAME contains the name of a selection criterion or parameter on the selection screen.
MARK describes the selection made by the user: MARK = SPACE means that the user has entered a simple single value or range selection. MARK = '*' means that the user has also made entries on the Multiple Selection screen.

• FORM LDB_PROCESS_CHECK_SELECTIONS

Called instead of the subroutine PAI if the logical database is called using the function module LDB_PROCESS without a selection screen. This subroutine can check the selections passed in the function module interface.

• FORM PUT_<node>

Called in the sequence defined in the structure. Reads the data from the node <node> and uses the
PUT <node>.
statement to trigger a corresponding GET event in the ABAP runtime environment. The PUT statement is the central statement in this subroutine: It can only be used within a subroutine of a logical database. The logical database must contain the node <node>, and the subroutine name must begin with PUT_<node>. The PUT statement directs the program flow according to the structure of the logical database. The depth to which the logical database is read is determined by the GET statements in the application program or the interface parameter CALLBACK of the function module LDB_PROCESS.
First, the subroutine PUT_<root> is executed for the root node. The PUT statement then directs the program flow as follows:
i) If the database program contains the subroutine AUTHORITY_CHECK_<table>, the first thing the PUT_<node> statement does is to call it.
ii) Next, the PUT statement triggers a GET event in the runtime environment. If there is a corresponding GET <node> statement in the executable program to which the logical database is linked, the associated event block is processed. If the CALLBACK parameter of the function module LDB_PROCESS is filled accordingly, the corresponding callback routine is called.
iii) The PUT statement then directs the program flow as follows:
(a) To the next subroutine of a node that follows directly, if a lower-level node (not necessarily the very next) in the same subtree is requested by GET in the executable program or in the function module.
(b) To the subroutine of a node at the same level, if the preceding node branches to such a node and if a GET statement exists for such a node in the executable program or the function module.
The PUT statement in that subroutine starts again at step (i). In the subroutine of the lowest node in a subtree to be processed using GET, the program control does not branch further. Instead, the current subroutine is processed further. When a subroutine PUT_<node> has been executed in its entirety, the program flow returns to the PUT statement from which it branched to the subroutine PUT_<node>. iv) When control has returned from a lower-level subroutine PUT_<node>, the PUT statement triggers the event GET <node> LATE in the runtime environment.

• FORM AUTHORITY_CHECK_<node>

Called automatically by the PUT <table> statement. In this subroutine, you can specify authorization checks for the appropriate node <table> from the structure of the logical database.

• FORM PUT_<ldb>_SP

Called when the user makes a selection using a search help to process the key chosen in the search help. <ldba> is the name of the logical database. From this subroutine, you can use the entries in the search help tables to read the relevant entries from the root node <root>. The processing in the program can then be triggered using PUT <root>. The subroutine PUT_<root> is then not called automatically.

• FORM BEFORE_EVENT

Called before an event, the name of which is passed in the parameter EVENT. Currently, the EVENT field can only contain the value START-OF-SELECTION, to call a subroutine before this event.

• FORM AFTER_EVENT

Called after an event, the name of which is passed in the parameter EVENT. Currently, the EVENT field can only contain the value END-OF-SELECTION, to call a subroutine after this event.

• FORM <par>_VAL, <selop>_VAL, <selop>-LOW_VAL, <selop>-HIGH_VAL

Called when the user calls possible values help for the parameter <par> or the selection criterion <selop>. These must belong to the selections in the logical database.

• FORM <par>_HLP, <selop>_HLP, <selop>-LOW_HLP, <selop>-HIGH_HLP

Called when the user calls input help for the parameter <par> or the selection criterion <selop>. These must belong to the selections in the logical database. Example
Suppose that in the logical database structure, LFB1 is a branch of LFA1,
and that the following selection criteria are defined in the selection include:
SELECT-OPTIONS: SLIFNR FOR LFA1-LIFNR,
                SBUKRS FOR LFB1-BUKRS.
A section of the database program would then read:
FORM PUT_LFA1.
  SELECT * FROM LFA1
           WHERE LIFNR IN SLIFNR.
    PUT LFA1.
  ENDSELECT.
ENDFORM.

FORM PUT_LFB1.
  SELECT * FROM LFB1
           WHERE LIFNR =  LFA1-LIFNR.
           AND   BUKRS IN SBUKRS.
    PUT LFB1.
  ENDSELECT.
ENDFORM.
An executable program (report) linked to the logical database could contain the lines:
GET LFA1.
  WRITE LFA1-LIFNR.
GET LFB1.
  WRITE LFB1-BUKRS.In this example, the runtime environment calls the routine PUT_LFA1 after the event START-OF-SELECTION. The event GET LFA1 is triggered by the statement PUT LFA1. Once the corresponding event block in the program is complete, PUT LFA1 branches to the subroutine PUT_LFB1. From this subroutine, the event GET LFB1 is triggered in the application program. If LFB1 is the last node to be read, processing resumes with the SELECT loop in PUT_LFB1. Otherwise, the program flow moves to the subroutine PUT_<node> of the next node. At the end of the SELECT loop of the last node, processing resumes in the SELECT loop of the node at the next level up. The example of programming using nested SELECT loops is only used to make the program flow clearer. In a real logical database, you would avoid doing this in order to minimize the number of database accesses.
The following diagram shows the program flow:

In this example, the PUT statements do not branch to the authorization check subroutines.



 
Example of a Logical Database 
Let us consider the logical database TEST_LDB. Structure Selections in the Selection Include    SELECT-OPTIONS: SLIFNR   FOR LFA1-LIFNR,
                   SBUKRS   FOR LFB1-BUKRS,
                   SGJAHR   FOR LFC1-GJAHR,
                   SBELNR   FOR BKPF-BELNR. Database Program *-------------------------------------------------------*
* DATABASE PROGRAM OF THE LOGICAL DATABASE TEST_LDB
*-------------------------------------------------------*
PROGRAM SAPDBTEST_LDB DEFINING DATABASE TEST_LDB.
TABLES: LFA1,
        LFB1,
        LFC1,
        BKPF.
*-------------------------------------------------------*
* Initialize selection screen (process before PBO)
*-------------------------------------------------------*
FORM INIT.
 ....
ENDFORM.                                "INIT
*-------------------------------------------------------*
* PBO of selection screen (always before selection
* screen
*-------------------------------------------------------*
FORM PBO.
 ....
ENDFORM.                                "PBO
*-------------------------------------------------------*
* PAI of selection screen (process always after ENTER)
*-------------------------------------------------------*
FORM PAI USING FNAME MARK.
  CASE FNAME.
    WHEN 'SLIFNR'.
     ....
    WHEN 'SBUKRS'.
     ....
    WHEN 'SGJAHR'.
     ....
    WHEN 'SBELNR'.
     ....
  ENDCASE.
ENDFORM.                                "PAI
*-------------------------------------------------------*
* Call event GET LFA1
*-------------------------------------------------------*
FORM PUT_LFA1.
  SELECT * FROM LFA1
WHERE LIFNR IN SLIFNR.
    PUT LFA1.
  ENDSELECT.
ENDFORM.                                "PUT_LFA1
*-------------------------------------------------------*
* Call event GET LFB1
*-------------------------------------------------------*
FORM PUT_LFB1.
  SELECT * FROM LFB1
WHERE LIFNR = LFA1-LIFNR
AND BUKRS IN SBULRS.
    PUT LFB1.
  ENDSELECT.
ENDFORM.                                "PUT_LFB1
*-------------------------------------------------------*
* Call event GET LFC1
*-------------------------------------------------------*
FORM PUT_LFC1.
  SELECT * FROM LFC1
WHERE LIFNR = LFA1-LIFNR
AND BUKRS = LFB1-BUKRS
AND GJAHR IN SGJAHR.
    PUT LFC1.
  ENDSELECT.
ENDFORM.                                "PUT_LFC1
*-------------------------------------------------------*
* Call event GET BKPF
*-------------------------------------------------------*
FORM PUT_BKPF.
  SELECT * FROM BKPF
WHERE BUKRS = LFB1-BUKRS
AND BELNR IN SBELNR
AND GJAHR IN SGJAHR.
    PUT BKPF.
ENDSELECT.
ENDFORM.                                "PUT_BKPF
The PROGRAM statement has the addition DEFINING DATABASE TEST_LDB. This defines the database program as belonging to the logical database TEST_LDB.
The nodes of the structure are declared with the TABLES statement which generates the appropriate table work areas. You can also use the NODES statement to define database tables as nodes. If a node of a logical database is not a database table, you must use the NODES statement. The interface work areas are shared by the database program and the user, and so act as an interface for passing data. The term "user" here can mean either an executable program to which the logical database is linked, or the function module LDB_PROCESS.
The subroutines INIT and PBO initialize the selection screen.
In the PAI subroutine, you can include an authorization check for the user input on the selection screen. Plausibility or value range checks are also possible. If a check fails, you can write an error dialog. The corresponding field on the selection screen is then made ready for input again.
The PUT_<node> subroutines read the database tables according to the selection criteria entered by the user and trigger the relevant events in the executable program. This program is intended only to show the essential structure of a logical database. It does not contain any refinements to improve response times. The order in which the subroutines are called is determined by the structure of the logical database.