1. MetaSQL

1. MetaSQL
Prev	Chapter 3. Advanced Topics	Next

MetaSQL is a scripting language developed by xTuple for use by the report writer. The language is designed to handle dynamic database queries. MetaSQL statements are embedded within standard SQL—for example, within the query source of a report definition. When a report is run, a parsing engine interprets the MetaSQL using a list of named parameters. The parsed result is standard SQL, which in turn is sent to the target database. The following diagram illustrates this process:

MetaSQL Parser Handles Embedded MetaSQL

Dynamic queries are queries which contain conditional statements. Query results will vary depending on how the conditions are satisfied. For example, a report may return one result if a user makes one selection—or a different result if the user makes a different selection. The report results are conditional upon choices made by the user. The MetaSQL scripting language enables you to allow for and incorporate this conditionality into your report definitions.

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.2. MetaSQL Syntax

MetaSQL syntax is comprised of control statements and functions. Control statements contain standard SQL which will be used if certain conditions are met. Functions are generally used to evaluate parameters. Collectively, control statements and functions are referred to as “statements.” When these statements are embedded within standard SQL, they create MetaSQL.

As we have mentioned previously, MetaSQL statements are bracketed between an opening angle bracket and question mark pair (“<?”) and a closing question mark and angle bracket pair (“?>”). All content within (and including) the opening and closing character pairs is referred to as a “tag.” Each tag is comprised of a single statement and also any additional arguments, parameters, or modifiers which apply to the statement. The first word following the tag opening (i.e., “<?”) is the statement. Any additional text after the statement up to the closing angle bracket (“?>”) is broken into tokens and processed accordingly.

Tip

You don’t need to worry about inserting excess blank spaces when writing MetaSQL statements. The MetaSQL parser ignores excess blank spaces.

Next we will look at the range of available control statements and functions, offering descriptions for how each may be used.

1.2.1. Control Statements

Control statements form blocks which contain standard SQL and MetaSQL tags. While control statements are not directly responsible for producing output, they do determine whether their contents should be outputted or not. The complete list of available MetaSQL control statements is described below:

if: Use the if statement to begin a conditional control block. One or more tokens (i.e., single elements) should follow this opening statement. Individual tokens may be separated from each other using the and and or tokens. Explicit token groups should be enclosed within parentheses ( ). You can reverse the boolean result of a token group by placing a not token directly proceeding the token group. If the aggregate sum of all the token groups in a control block is a true value, then the contents of the if block will be executed. If the condition is false, then the next following elseif condition will be evaluated, assuming one exists.
elseif: Place an elseif statement within an if block to divide the if block into multiple blocks. An elseif statement behaves in the same manner as an if statement. Any number of elseif conditions may be included within an if block.
else: An else condition, if present, is executed when both the if condition and any elseif conditions are false. At most, there may be one else condition included within an if block.
endif: Use this statement to end an if block.
foreach: This statement operates on the parameter which immediately follows it. If the parameter represents a list of values, the foreach block will be executed once for each value in the list. If the parameter represents a single value, then the block will be executed once. If no values exists, the block will not be executed.
endforeach: Use this statement to end a foreach block.

1.2.2. Functions

Functions are independent statements which perform specific operations. If a function returns a value, the value will be outputted. The complete list of available MetaSQL functions is described below:

value: This function operates on the parameter which immediately follows it and returns the value of that parameter. If the parameter is called within a foreach block and the parameter represents a list, the function will return the current item of that list. If the parameter is called outside of a foreach block and the parameter represents a list, the value will default to the first entry. If the parameter named does not exist, then a blank or null value will be returned.
exists: This function operates on the parameter which immediately follows it and returns true if that parameter exists. If the parameter does not exist, then the function returns false. If the parameter is null or blank, the function will still return true because the parameter exists.
reExists: This function takes a regular expression and returns true if one or more parameter exists which match the regular expression. The regular expression is case-sensitive.
isFirst: his function operates on the parameter which immediately follows it. If the parameter is called inside a foreach block and the parameter represents a list, the function will return true if the item returned by the call would be the first item in the list. If the parameter is called outside a foreach block—or if the parameter specified does not represent a list but does exist—then the function will return true. In all other situations, the function will return false.
isLast: This function operates on the parameter which immediately follows it. If the parameter is called inside a foreach block and the parameter represents a list, the function will return true if the item returned by the call would be the last item in the list. If the parameter is called outside a foreach block and the parameter represents a list, the function will return true only if the list contains one item. In all cases, the function will return true if the parameter does not represent a list, but instead represents a single parameter. If the parameter does not exist—or in any other case—this function will return false.
continue: This function will cause the innermost loop to execute to the end and continue as normal. If this statement is used outside of a loop, the function has no effect. If a number is specified, the specified number determines how many loops will be continued. If the number of specified loops is greater than the number of nested loops, then the function will continue the outermost loop.
break: This function will cause the current loop to execute to the end and stop. If the function is used outside of a loop, the function will have no effect. If the number of loops is specified, that determines how many loops will be terminated. If the number of loops to break is greater than the number of nested loops, then all loops will broken.