The Preppis Preprocessor

Preppis is a preprocessor based on the same ideas as the well-known preprocessor in C/C++. The habitual C/C++ programmer should be aware of that some things work differently with Preppis than in C/C++.

Preppis is part of the AbaPerls file-loading process, and you execute Preppis indirectly through ABASQL, DBBUILD and the update scripts generated by DBUPDGEN. There is no command to execute Preppis independently.

Contents:
     Overview
     Including Files
     Conditional Compilation
     Macro Handling

Overview

When Preppis reads a file, it looks for two types of strings, directives and macros:

If Preppis finds a string which fits in with these descriptions, but there is no such directive or macro defined, this is an error. (With the exception of macro names in $IFDEF and $ELSEDEF directives, see below under Conditional Compilation.)

Macro expansion is currently not recursive. Thus if the macro &nisse has the value &kalle, Preppis will not try to expand &kalle, but AbaPerls will pass the string &kalle to SQL Server.

There are two sort of macros, short and long. Short macros are macros that are expanded within a line and can appear anywhere in the file outside comments. Long macros are multi-line macros, and they must appear alone on a line, see further below under $MACRO_LONG.

There are several ways to define a short macro:

• There are predefined macros.
• With the $MACRO directive.

• In config-files on global level or subsystem level with the -Macro option.

• On the command line to ABASQL, DBBUILD and the update scripts generated by DBUPDGEN with the -Macro switch.
• From the AbaPerls table abaconfigsettings, typically saved there by a previous execution of DBBUILD.

A long macro can only be defined with the $MACRO_LONG directive.

The AbaPerls file-loading process strips all comments from the input file before passing it to Preppis. Thus, Preppis never sees macros or directives that appears in comments. Macros can include comment delimiters, but it is up to you to keep track of what it leads to. Thus, such usage is discouraged.

If an macro appears in a string literals it is expanded as in this example:

   SELECT @x = 'Kalle Anka &co'

Preppis will attempt to translate the macro &co and emit an error message if &co is undefined. (On the other hand the string "Kalle Anka & co", will not cause an error, because a lone ampersand is never interpreted as a macro.)

This table lists the directives in Preppis, divided into three main categories.

When DBBUILD and an the update scripts generated by DBUPDGEN are running with the -noexec switch, Preppis does not expand macros, and processes only the directives $INCLUDE, $REQUIRE, $DLLINCLUDE, EXTERNAL NAME and $USEDBY.

Including Files

$INCLUDE

You use the macro $INCLUDE to include another file. The INCLUDE-file must have a $USEDBY directive for the including file (see below). The file you include must have the extension .SQLINC and reside in the INCLUDE directory.  $INCLUDE is only permitted in the following types of files: .SP, .SQLFUN, .TRI, .SQLINC, .VIEW and .VTRI. (Previously it was permitted to include any file from any file, but this has been removed, since the consequences for update scripts was unclear.)

Preppis looks up the include-file according the AbaPerls file-lookup order. Thus the same rules applies to include-files as to all other files you pass to AbaPerls.

An include-file can include other files. If you $INCLUDE the same file more than once in the same execution, the contents of the file is included each time.

The argument to $INCLUDE is macro expanded. (But since macros are not expanded when you run with -noexec, this may be of limited use.)

A typical use for $INCLUDE is to make it possible for a stored procedure to reference a temp table created by a calling stored procedure without the need to have the CREATE TABLE statement in two places. Here is an example. compute_sp performs some complex computations and places the result in the table #compute that calling stored procedures have to create. So in CALLER_SP.SP it would look this:

    CREATE PROCEDURE caller_sp @param int AS
    $INCLUDE compute.sqlinc
    -- Other declarations and initializations.
    EXEC @err = compute_sp @comp_param, @comp_param2
    SELECT @err = coalesce(nullif(@err, 0), @@error) IF @err <> 0 RETURN @err
    -- Further procssing.

In COMPUTE_SP.SP the temp table would be created before the stored procedure, like this:

    $INCLUDE compute.sqlinc
    go
    -- This go is required, as CREATE PROCEDURE must be the first in batch.
    CREATE PROCEDURE compute_sp @comp_param int,
                                @comp_param char(4) AS
    -- Various stuff
    INSERT #compute (...)
       SELECT ...
       FROM   ...

In COMPUTE.SQLINC we find this:

    $USEDBY compute_sp.sp
    $USEDBY caller_sp.sp
    -- See below about $USEDBY, which is mandatory here.
    CREATE TABLE #compute (first_column int NOT NULL,
                           second_column ...
    )

Notice that there must not be a go in COMPUTE.SQLINC, as this go would be inserted in CALLER_SP.SP and sever the second half of the code in the file from the stored procedure.

Another typical example where $INCLUDE comes in handy is dynamic search conditions. Say that you have a complex SELECT statement that could return data for all customers, or only one. It could be about impossible to have one SELECT statement to cover both cases and get good performance for requests on a single customer. You could put the SELECT for all customers in an INCLUDE-file and then add this line at the end:

   &more_conditions

Then you would have two stored procedures. The one to get data for a single customer would like:

   CREATE PROCEDURE get_data_for_customer @customerid int AS
   $MACRO &more_conditions tbl.customerid = @customerid
   $INCLUDE get_data.sqlinc

The procedure to get all customers would take no parameters and just define &more_conditions to be blank.

(The knowledgeable reader may have some questions on this use, so let me briefly answer them. Q1) this is not necessary with deferred name resolution, so why? A1) AbaPerls thinks deferred name resolution is bad, and checks for references to non-existing objects. Read further about Object checking. Q2) Wouldn't it be better to implement compute_sp as a table-valued function. A2) Yes, sometimes. But compute_sp may have to call other stored procedures. And even with an UDF, the callers may need the table declaration, because they will do further processing on the table.)

$REQUIRE

On the surface, $REQUIRE may seem similar to $INCLUDE, but the two directives serve very different purposes. The purpose of $INCLUDE is to incorporate a piece of code here and now. The purpose of $REQUIRE is make sure that a file has been loaded, so that the current file will load properly. This is to ensure that DBBUILD and update scripts generated by DBUPDGEN loads files in the correct order. To take an example, say for instance you have two views outer and inner and the definition of outer refers to inner. In this case OUTER.VIEW should include this line:

   $REQUIRE inner.view
   go

to guarantee that INNER.VIEW is executed before outer is (re)created.

A $REQUIRE can result in file being inserted into the calling file, just like with $INCLUDE, but it does not happen in these cases:

• The tool is ABASQL. In this case, the user is supposed to be able to load the files in the correct order himself.
• The file has already been loaded in the same run of DBBUILD or the update script.
• For a production database, AbaPerls will not load the file, if the version in the database (according to abasysobjects) is newer than the version loaded through $REQUIRE.

As a mirror of the second point: If DBBUILD or an update script first loads a file through $REQUIRE, and then encounters the file itself in the build, they will not load the file a second time.

As for $INCLUDE, the file loaded by $REQUIRE must include a $USEDBY directive for the referring file and AbaPerls checks that $USEDBY is present, even if the file is not loaded.

$REQUIRE is only permitted in the following combinations:

• .SQL and .POSTSQL in the MESSAGE directory may $REQUIRE another files of the same type.
• An INSERT-file may $REQUIRE another INSERT-file. This is the most common scenario: Say, that you have two tables main and sub which both have INSERT-files with predefined data. The INSERT-file for sub contains references to values in main loaded by the INSERT-file for main, and loading SUB.INS before MAIN.INS could result in foreign-key violations. Use $REQUIRE to prevent this.
• An .ASSEM file in the ASSEMBLIES directory may $REQUIRE another assembly.
• .SQLFUN files in the FUNCTIONS directory may $REQUIRE other .SQLFUN files.
• .VIEW files in the VIEW directory may $REQUIRE other .VIEW files. .VIEW files are also permitted to $REQUIRE .SQLFUN files, except that it doesn't work due to a bug in the AbaPerls file-loading process.

Why these combinations and no others? Since DBBUILD and update scripts generated by DBUPDGEN load one type of files at a time, you mainly need $REQUIRE when a file depends on a file of the same type, unless the dependency is opposite to the load order.

You may still miss some combinations, like stored procedures calling other stored procedures. $REQUIRE appears a bit unpractical here, due to the sheer volume. While you get a warning if you load procedures in the wrong order, the tool LISTERRS takes care of this situation.

For an example of $REQUIRE in use with INSERT-files, see the example that comes with INSFILGEN.

$DLLINCLUDE

This directive is only permitted in .ASSEM files and will read a file with the same name of the .ASSEM file with the extension .DLL and include the file as one long hex-string.

Say that you have a file ABACALC.ASSEM, that looks like this:

   CREATE ASSEMBLY abacalc AS
   $DLLINCLUDE

Preppis will read ABACALC.DLL and, the resulting SQL will look something like this:

   CREATE ASSEMBLY AS abacalc AS 0x4D5A9000030000...

For more information about using the CLR in AbaPerls, see the CLR page.

$USEDBY

The $USEDBY directive is used to state that a file references the current file. You need to use $USEDBY when you use any of the directives $INCLUDE, $REQUIRE and EXTERNAL NAME.

For instance, if file MY_SP.SP says $INCLUDE TEMPTBL.SQLINC, and TEMP.SQLINC does not say $USEDBY MY_SP.SP, this is an error. If TEMPTBL.SQLINC in its turn includes OTHER.SQLINC, OTHER.SQLINC needs to have a $USEDBY TEMPTBL.SQLINC. OTHER.SQLINC does not need to have a $USEDBY for MY_SP.SP.

The main consumer of the $USEDBY directives is DBUPDGEN. When DBUPDGEN finds that a file that could include a $USEDBY directive, DBUPDGEN reads the file, and adds the files listed in $USEDBY directives to the update script, even if these files themselves are unchanged. The same applies if a .DLL file has changed; in this case DBUPDGEN will search the corresponding .ASSEM file for references.

If the file mentioned in the $USEDBY directive does not exist in the current subsystem, DBUPDGEN does not include the file in the update script. This is a good thing when an include-file is shared in SourceSafe between multiple subsystems. (Unfortunately, there is no way to refer to an include-file in a different subsystem directly.)

When you specify the name of the includer for $USEDBY, the name should be in the AbaPerls SQL directory structure. That is, if you include a file from SOME_SP.SP that is in SP, the main directory for stored procedures, you should say:

   $USEDBY some_sp.sp

On the other hand, if the file is in the sub-directory SP/SUB, you should say:

   $USEDBY sub/some_sp.sp

The argument to $USEDBY is not macro-expanded.

EXTERNAL NAME

EXTERNAL NAME is a T-SQL clause that appears in the definition of a CLR object. For instance:

   CREATE PROCEDURE clrsp @z int
   AS EXTERNAL NAME clrsp_assem.StoredProcedures.testclr

Preppis reacts on EXTERNAL NAME and reads CLRSP_ASSEM.ASSEM to verify that this file includes this line:

   $USEDBY clrsp.sp

When loading an assembly, AbaPerls faces the problem that ALTER ASSEMBLY is not always possible. For instance, say that you have an assembly that implements a stored procedure, and you add a parameter to the CLR routine. ALTER ASSEMBLY fails in this case, since the definition of the stored procedure no longer agrees with the assembly. In this case AbaPerls will drop all objects that depend on the assembly. By including all files that depend on the assembly in the update script, DBUPDGEN can ensure that no objects will be lost from the database.

Conditional Compilation

With conditional compilation you can specify that some parts of a file is to be included or excluded according to some condition. In this way you can have different code for different versions of SQL Server, or different code at different customer sites.

A block of conditional compilation starts with one of the directives $IF or $IFDEF, followed by zero or more $ELSEIF or $ELSEDEF, then zero or one $ELSE and closes with a mandatory $ENDIF. After each $IF, $IFDEF, $ELSEIF, $ELSEDEF and $ELSE comes a sub-block of zero or more lines of other code – SQL code or other Preppis directives, including a new $IF or $IFDEF starting a nested block of conditional compilation. The first sub-block that follows an $IF, $IFDEF, $ELSEIF or $ELSEDEF that evaluates to true is included in the code that is passed to SQL Server, and the rest of the blocks up to the closing $ENDIF is discarded. If no $IF, $IFDEF, $ELSEIF or $ELSEDEF has a true expression, the sub-block following any $ELSE will be passed to SQL Server.

The expression that follows $IF and $ELSEIF is macro-expanded like regular code, and undefined macros will yield an error message. For $IFDEF and $ELSEDEF the macro-expansion is done in an unique way: undefined macros are expanded to 0 and defined macros to 1 regardless of their actual value.

After macro-expansion, Preppis passes the expression to Perl for evaluation. This has the side effect that if the syntax is not correct, the error message is likely to be more or less cryptic, not the least for a user who is not very well versed in Perl. On the other hand, I was saved the task of writing a parser.

Note that normally Preppis does not evaluate directives in a sub-block excluded by conditional compilation. Thus, Preppis may fail to alert to of a missing include file will not be flagged. However, when -noexec is in force, Preppis does not process the conditional-compilation directives, and thus in this case Preppis will process all $INCLUDE and $REQUIRE directives in the file.

Since Perl is used to evaluate the expressions, there is a vast offering of available operators that you can use. I would encourage you to stick to the simple stuff, though. Here is a short list of the operators you are most likely to use.

Example on use (For the predefined macros &SQL_version and &SQL7 see below.)

   -- For best performance, clustered indexes in SQL7 should be short,
   -- but in SQL6.5 a high number of duplicates should be avoided.
   $IF &SQL_version >= &SQL7
      CREATE CLUSTERED INDEX c_zipcode_ix ON addresses (zipcode)
   $ELSE
      CREATE CLUSTERED INDEX c_zipcode_ix zipcode (zipcode, lastname, SSN)
   $ENDIF

   -- Different way of doing things at different customer sites.
   $IFDEF &Compaq or &Dell
   -- They want it this way.
   $ELSEDEF &HP
   -- But HP want it that way.
   $ELSE
   -- Everyone else go for the standard way.
   $ENDIF

Macro Handling

$MACRO

With $MACRO you define a new macro, or change the value of an existing. The value of the macro is the text that follows the macro name. If there is no text, the value will be an empty string, but the macro will still be defined.

If the value deifnition includes a macro, Preppis expands this macro before defining the new macro. From this follows that the PRINT statement below will print 'Ada'.

    $MACRO &kalle 'Ada'
    $MACRO &nisse &kalle
    $MACRO &kalle 'Lovelace'
    PRINT &nisse

In difference to the C preprocessor, Preppis does not offer any possibility to use parameters in macros.

$MACRO_LONG

definies a multi-line macro. All lines that follows up to the directive $ENDMACRO are part of the macro definition. One case where long macros are useful is when you have a complex SELECT statement with optional search condition and you want to make use indexes as much as possible. Then you can do something like:

   $MACRO_LONG &BASE_SELECT
      SELECT ....
      FROM   ...
      WHERE  ...
   $ENDMACRO

   IF @thatpar IS NOT NULL
   BEGIN
      &BASE_SELECT
      AND thatcol = @thatpar
   END
   ELSE IF @thispar IS NOT NULL
   BEGIN
      &BASE_SELECT
      AND thiscol = @thispar
   END
   ...

When you use a long macro it must be alone on a line (save leading and trailing spaces). If there is a syntax error within the long macro, the AbaPerls File-loading Process will refer you to the line in the macro definition.

The definition of a long macro cannot include any other Preppis directive.

$UNDEF

Removes a previously defined macro, be that a short or long macro.

Pre-defined Macros

There are a number of predefined macros for SQL versions.

You cannot redefine or delete these macros.

When you compare &SQL_version in an $IF directive to one of the predefined macros for different versions of SQL Server, or some version string that you supply, Preppis only compares the part of the version string that is supplied on both sides. Say that &SQL_version has the value 8.00.194. Then the following expressions are true:

    &SQL_version == 8
    &SQL_version == 8.00
    &SQL_version > 8.00.193
    &SQL_version lt 8.50

The permits you to make the comparison with SQL version as fine-grained as you need. In most cases, you only care whether you are on SQL6.5, SQL2000 etc. But occasionally you may need different code depending on service pack. E.g, a bug in the RTM version may call for a workaround that is slow, so you don't want to use it on a server where the service pack has been applied.

(Those who know Perl should know that these comparisons are unrelated to the rules that Perl has for comparing its version. If I tell you that the actual value of a macro like &SQL6 is new VersionCmp('6.00'), then you might be able to guess the rest.)