1.1. MetaSQL in Practice

1.1. MetaSQL in Practice
	1. MetaSQL

1.1. MetaSQL in Practice

To illustrate how dynamic, conditional queries are handled using MetaSQL, let's examine a sample report definition called "UsersMasterList". This is the same report definition we have worked with in previous chapters.

The UsersMasterList report definition is called whenever someone using the xTuple application wants to print a copy of the Users master list. That xTuple master list appears in the following screenshot:

Master list of xTuple Users

When someone selects the PRINT button, the information displayed on the screen is printed out using the UsersMasterList report definition.

If you look closely, you can see the option Show Inactive Users is selected. As a result, the Users master list is displaying both active and inactive users. The Show Inactive Users option is an example of a dynamic condition. If the option is selected, one list of users will be displayed. If it’s not selected, another list of users will be displayed. Logically, the report definition must accommodate either of these two conditions.

The report definition uses MetaSQL to handle these conditions. Let's look at the query source for the UsersMasterList report definition to understand how MetaSQL is embedded within a report definition:

Query source for UsersMasterList report definition

To locate embedded MetaSQL in a query source, simply look for the <? and ?> tags. These are the tags used to identify MetaSQL statements. As you can see, the WHERE clause contains several MetaSQL tags.

Let’s examine the WHERE clause to gain a better understanding for how MetaSQL works:

WHERE ((usr_locale_id=locale_id)
<? if not exists("showInactive") ?>
    AND (usr_active=true)
<? endif ?>
)

The MetaSQL is always contained inside <? and ?> tags. Our sample WHERE clause contains the following two lines of MetaSQL:

<? if not exists("showInactive") ?>
<? endif ?>

MetaSQL statements:

if not: This statement begins a MetaSQL condition.
endif: This statement ends the condition.

MetaSQL function:

exists: This MetaSQL function takes the name of the parameter provided. In this case, the provided parameter is named showInactive.

Parameters:

showInactive: The conditionality of the report centers on this parameter. It is this parameter which determines whether inactive users should be included in the report. Like all valid parameters referenced in a MetaSQL statement, the parameter showInactive. The showInactive parameter originates from within the source code of the application utilizing the report writer. In this case, that application is xTuple—and the parameter is included in the source code for the Users master list screen. When an xTuple user sends a print request from the Users master list screen, the MetaSQL parser interprets the existing conditions—namely, is the showInactive parameter being used, or not? The parser then uses this information to produce standard SQL meeting the specified conditions. This standard SQL, which has been stripped of its MetaSQL elements by the parser, is then delivered to the target database for processing.

Tip

The xTuple application automatically generates a parameter list whenever a user submits a print request. The parameter list will contain as many (or as few) parameters as pertain to the screen the print request was sent from. When a MetaSQL statement in an xTuple report definition refers to a parameter, that parameter should be one which would appear on the generated parameter list.

Resulting SQL:

If a user selects the Show Inactive Users option, the MetaSQL parser will send the following standard SQL to the target database:

SELECT usr_username,
       usr_propername,
       usr_initials,
       formatBoolYN(usr_active) AS
f_active,
       formatBoolYN(usr_agent) AS
f_pa,
       locale_code
FROM usr, locale
WHERE ((usr_locale_id=locale_id)
AND (usr_active=true))
ORDER BY usr_username;

If the user does not select the Show Inactive Users option, the MetaSQL parser will send the following standard SQL to the target database:

SELECT usr_username,
       usr_propername,
       usr_initials,
       formatBoolYN(usr_active) AS
f_active,
       formatBoolYN(usr_agent) AS
f_pa,
       locale_code
FROM usr, locale
WHERE ((usr_locale_id=locale_id)
ORDER BY usr_username;

Note

You may have noticed the AND is excluded in the second example. This occurs because in the second example the value of the parameter "showInactive" is false.

As the "UsersMasterList" example demonstrates, static report definitions can be made to handle dynamic conditions using MetaSQL. In the following sections, we will examine the range of MetaSQL building blocks.


1. MetaSQL		1.2. MetaSQL Syntax

OpenRPT Product Guide

1.1. MetaSQL in Practice

Tip

Note