Configuration Options

This page details all configuration options, as well as the precedence rules between the various places where you can specify configuration options.

Contents:
   Introduction
   Obfuscating SQL Objects (‑[no]crypt)
   Classifying the Purpose of a Database (‑environment DEV | TEST | PROD)
   Defining and Undefining Macros (‑Macro &macro=[value]‑undef &macro)
   Replacing Double Quotes (‑[no]quoterepl)
   Site-Specific Files (‑site site-id[:site-id...])
   Config-file options (‑onlylabel regexp ‑notlabel regexp)
   Precedence Rules

Introduction

Configuration options control how ABASQL, DBBUILD and the update scripts generated by DBUPDGEN load files into the database. Typically you specify the options with DBBUILD in a config-file or on the command line. When you run ABASQL and the update scripts generated by DBUPDGEN, they read the options from the database and apply them as if you had specified them on the command line to these tools. The condition is that you have installed the ABAPERLS subsystem so that the AbaPerls system tables are present in the database.

By saving the settings in the database you can specify once for all that, for instance, all stored procedures in a database are to be stored obfuscated, or that a certain macro should apply to the database (or just a single subsystem).

You should probably exercise some caution with specifying configuration options on subsystem level, because when developers manually loads files with ABASQL, they may not specify ‑subsystem, in which case the default settings and the settings on database level will apply.

You can override the settings saved in the database by specifying a different setting for a configuration option on the command line, see further the section Precedence rules.

To learn how to specify the configuration options in the config-file, see the config-file page.

Here is an summary of all configuration options. Each option is detailed below.

Option	Default	Purpose
`‑[no]crypt`	Off	Add WITH ENCRYPTION to stored procedures etc.
`‑environment`	DEV	What the database is used for.
`‑Macro &macro[=value]`		Define a Preppis macro.
`‑[no]quoterepl`	On	Replace `"` as string delimiter with `'`.
`‑site site-id[:site-id...]`		Define one or more site-id:s.
`‑undef &macro`		Undefine a Preppis macro.
`‑onlylabel`	See below	When searching for labels, consider only labels with this pattern.
`‑notlabel`		When searching for labels, do not consider labels with this pattern.

Obfuscating SQL Objects

‑[no]crypt

With this option in effect, ABASQL, DBBUILD and the update scripts generated by DBUPDGEN adds the WITH ENCRYPTION clause to the source code of stored procedures, scalar functions, triggers and views before sending the source to SQL Server. If you include WITH ENCRYPTION yourself in the source code, you must not use ‑crypt, because else AbaPerls will insert a second, giving you a syntax error.

Note that despite the name, WITH ENCRYPTION does not offer true encryption, but it is only a matter of obfuscating. Any person with db_owner access to the database will be able to retrieve the code in clear text; there are utilities for this purpose out there. To make it slightly more difficult to understand the code, AbaPerls strips the code entirely from comments (without removing any line breaks) when ‑crypt is in effect.

Beware that ‑crypt generally does not work with table-valued functions. (This is a bug.)

Note that Object Checking is not able to flag errors for missing mandatory parameters in obfscuated procedures. (Because Object Checking needs to parse the source code stored in the database to determine whether a parameter has a default value or not.)

Default is ‑nocrypt.

Classifying the Purpose of a Database

‑environment DEV | TEST | PROD

You use this option to state whether the database is for development, test or production. For test and production databases, the AbaPerls file-loading process performs a number of checks where it compares the data in the AbaPerls system tables with the data for the file being loaded. The purpose of these checks is to prevent various sorts of "accidents" from occurring. For instance, an update script should not overwrite a more recent version of a stored procedure; ABASQL should not load a file from the wrong version directory. In a production database, AbaPerls prevents these accidents. In a test database, AbaPerls only issue a warning to alert you. In a development database all checks are disabled. See further the section Version Checks for Production and Test Databases on the file-load page about these checks.

As with other configuration options, you can have a one global setting for the database, and a different setting for a certain subsystem. A reason to do this could be that you have a new subsystem which is still under development that you want to test out in a production or test database, but you don't want to be nagged by the checks.

Besides the checks when loading files, the ‑environment option also imposes a check on DBBUILD and update scripts built with DBUPDGEN. When the setting is TEST or PROD, all subsystems must have an explicit label on the standard AbaPerls format LetterMajor.Middle.Minor. That is you cannot use LATEST, a date or your own home-brewed labels. For this particular check, it is only the setting on database level that applies; you cannot have a deviating setting for a single subsystem.

In difference to other config-options, ‑environment can be only used when you build a database or a subsystem with DBBUILD; you cannot override it with ABASQL or an update script generated by DBUPDGEN.

The default is DEV, that is, a development database

Defining and Undefining Macros

‑Macro &macro=[value]
‑undef &macro

Defines (‑Macro) or undefines (‑undef) a Preppis macro.

If you omit value, the macro will be defined to have the value of the empty string. Note that you still have to provide an equal sign. (AbaPerls currently permits you to omit the equal sign, but in this case the value of the macro is set to 1, due to the current behaviour of Getopt::Long, but a future version of Getopt::Long – which comes with Perl, not AbaPerls.- could change this).

When you use ‑Macro and ‑undef on the command line, you need to enclose the macro name (and the value for ‑Macro) in double quotes, as & is a meta-character in the command-line shell. This does not apply to config-files.

In a config-file if you want to define a macro with a space in the value, you should put the ‑Macro option on a line on its own, preceded by a period, see the config-file page for further details.

You can include several ‑Macro or ‑undef to define or undefine any number of macros, as well as on the command line as in the config-file, both on database level

Replacing Double Quotes

‑[no]quoterepl

With this option in effect (and it is in effect by default), ABASQL, DBBUILD and the update scripts generated by DBUPDGEN replaces all occurrences of double quotes (") as string delimiters with single quotes ('). Any nested single quotes in the string are doubled, so that the string syntax remains correct and the resulting string at execution is unchanged. Here are some examples:

SELECT "This is a string"       => SELECT 'This is a string'
SELECT "It's five o'clock tea"  => SELECT 'It''s five o''clock tea'
SELECT """Heroes"""             => SELECT '"Heroes"'
RAISERROR('Error "%s"', 16, -1) => RAISERROR('Error "%s"!', 16, -1)
EXEC ("SELECT '" + @val + "'")  => EXEC (''SELECT ''' + @val + '''')

When would you use ‑quoterepl?

You have legacy code which freely uses double quote to delimit strings, and you don't want to change all that code.
You program a lot in Visual Basic where double quote is the only string delimiter, and you have a problem to relearn for T‑SQL.
You want to be able to use two types of quotes when you nest strings, for instance in dynamic SQL. See further the note below.

When would you specify ‑noquoterepl?

You want to delimit identifiers that include special characters with double quotes to be ANSI compliant. For instance:
```
SELECT * FROM "Order details"  
```
Note: SQL Server also use permit you to square brackets [] to delimit identifiers, and this more commonly used in my experience.

Note: As seen from the next-to-last example above, AbaPerls does not change double quotes that are part of strings, even if the delimit a string nested in that string. From this follows that if you use EXEC() or sp_executesql to delimit dynamic SQL, you should use single quotes as delimiters in strings that are nested within the dynamic SQL code. If you use double quotes in the nested strings, you are likely to get errors when the dynamic SQL code is executed.

The default is ‑quoterepl. (This may appal some readers, but the system AbaPerls is mainly used with origins from the nineties, when double quote was a legal string delimiter in SQL Server and there was no support for quoted identifiers.)

Site-Specific Files

‑site site-id[:site-id...]

Defines site-id:s that apply to this database or subsystem. If a filename includes an at sign(@), the part that comes after the @ is the site-id. Such a file will only be loaded by ABASQL, DBBUILD and the update scripts generated by DBUPDGEN if and only if the site-id has been specified with ‑site.

As an example, assume the following situation: There is a table parameters that defines various parameters in the system. There is a set of default values, but you want to override the defaults depending on a customer wants to use the system. You may also override some parameters – for instance the default language of the application – on country level. Say that you have customers in the US, South Korea and Japan, and among you customers you count LG, IBM and Sony to name a few. A solution to handle this situation would be to have a parameters.ins which sets up the default values. Then you would have parameters@us.ins, parameters@kr.ins and parameters@jp.ins with the country-specific settings and parameters@lg.ins, parameters@ibm.ins and parameters@sony.ins with the customer-specific settings.

In the config-file for Sony you would put:

-site=jp:sony

AbaPerls would load parameters.ins, parameters@jp.ins and parameters@sony.ins, but not the other files with @ in the name.

Files with site-id:s may appear anywhere in the AbaPerls SQL directory structure, with one exception: you can not have site-specific GRANT.TEMPLATE.

You can only have one ‑site on database level, and only one per subsystem. A ‑site on subsystem level completely overrides any ‑site on database level.

When you generate an update script with DBUPDGEN, and the main file has changed, all site-specific files are also included in the update script, even if they are unchanged.

Site-id:s is an older way to single out site-specific information with AbaPerls. An alternate method is to use the Preppis macro $IFDEF which permits you to have the default settings as well as the country- and customer-specific settings defined in a single file. You can use the tool INSFILGEN to create such files from an Excel file.

Note: AbaPerls also permits using % as the separator, and this was in fact the original design. However, there were bugs with in SourceSafe with % in file names, and Microsoft resolved this by outlawing the use

Config-file options

‑onlylabel regexp ‑notlabel regexp

These two options are different from the other configuration options, since they affect how the config-file itself is interpreted, and they are not saved in the database.

These two options control which labels AbaPerls considers when it searches for the labels of a project, when there is no version specification in the config-file. The argument is a regular expression according to Perl rules. By default AbaPerls considers only labels that match the pattern ^[A-Z]\d+\.\d+\.\d+$, that is, the standard AbaPerls label format of LetterMajor.Middle.Minor. You can use these options to change this. To include all labels in the search, specify ‑onlylabel with the argument of a single dot.

The use case for ‑onlylabel is somewhat apparent: you want to build a database from non-standard labels. And the use case for ‑notlabel is? You'll figure it out. Hey, it was dirt simple to implement!

These options are accepted on the command line to ABASQL, DBBUILD and the update scripts generated by DBUPDGEN. However, they only have effect if you specify them in the config-file.

Precedence Rules

The settings for a configuration option can have several possible sources:

In the config-file on database level.
In the config-file on subsystem level.
In the database on database level.
In the database on subsystem level.
On the command line.

If a configuration option is specified in more than one place, there may be a conflict. More precisely there is a conflict in these cases:

`‑crypt ‑quoterepl`	E.g. `‑crypt` in one place, and `‑nocrypt` in another.
`‑site`	Different values, e.g. `‑site mnbpb:fi` in one place and `‑site fi` in another.
`‑Macro ‑undef`	Different values or different states for the same macro. E.g. `‑Macro &nisse=1` in one place and `‑Macro &nisse=2` or `‑undef &nisse` in another. Thus, the definitions of two different macros are never in conflict with each other.
`‑environment ‑onlylabel ‑notlabel`	Since these options only have an effect in a config-file (and in case of `‑envrionment` with DBBUILD), there can not be any conflicts with these options.

These are the rules for which source that applies. First without taking the command-line in regard:

If there is a conflict between subsystem level (2 or 4) and database level (1 or 3), the subsystem level takes precedence.
1 and 2 can apply only to DBBUILD, never to ABASQL or an update script created by DBUPDGEN.
When running DBBUILD, 1 and 2 apply when:
- there is no subsystem present in the database, i.e. you are building a new database.
- you specify ‑rebuild, which will erase all saved options and replace them with the contents of the config-file.
For DBBUILD, 3 and 2 apply when you add a new subsystem to a database in which one or more subsystems are already present. In this case database-level options in the config-file are discarded.
For ABASQL and the update scripts generated by DBUPDGEN, 3 and 4 always apply.
4 never applies to DBBUILD. (Because either DBBUILD creates the subsystem, in which case there are is nothing to read from, or one of ‑rebuild and ‑restruct was specified, in which case the database never applies.)

And now we add the rules for the command line.

If you do not specify the command-line switch ‑force, there must be no conflicts between the command-line options and the other sources, neither on database level nor on subsystem level.
If you specify ‑force, the command-line switches will be in force, and override the options on subsystem level and database level in case of a conflict. Options from 1-4 that are not in conflict with the command-line still apply.
In the case you add a new subsystem with DBBUILD, the command-line options you specify will be saved on database level, even if you only add one subsystem using the ‑subsystem option. Any conflicting options specified in the config-file for the subsystem will not be saved. (Thus, the only way to save settings on subsystem level is to use a config-file.)
No options saved in the database can ever be changed with ABASQL or an update script generated by DBUPDGEN; you can only override the saved configuration with ‑force.