Optional strings and variables

Chapter 3 DBMS Reference Guide

Optional strings and variables

You can use square brackets [ ] to:

Include optional strings and variables, or lists of strings and variables in the syntax of SQL statements [%--%]
Test the value of a variable and insert or reconsider a value depending of the result of the test. [%--%? is true : is false]
Test the content of a variable [%--%=--? if true : if false]

Variable	Generation	Reverse
[%--%]	Generated if variable is defined. If the variable is empty or assigned NO or FALSE it is not generated	Valuated if the parser detects a piece of SQL order corresponding to the variable. If the variable is empty or assigned NO or FALSE it is not valuated
[%--%? Is true : Is false] to test the value of the variable (conditional value)	If the variable is not empty, `Is true` is generated, if the variable is empty, `Is false` is generated	If the parser detects Is true, `Is true` is reversed, if the parser detects Is false, `Is false` is reversed and the % % variable is set to True or False respectively
[%--%=--? Is true : Is false] to test the content of the variable (conditional value)	If the variable equals the constant value, `Is true` is generated, if the variable is different, `Is false` is generated	If the parser detects Is true, `Is true` is reversed, if the parser detects Is false, `Is false` is reversed
[.Z: [s1][s2]...]	.Z is ignored	Specifies that the strings and variables between square brackets are not ordered
[.O: [s1][s2]...]	Only first item listed is generated	Specifies that the reverse parser must find one of the listed item to validate the full statement

Examples

[%--%]

[%OPTIONS%]

If %OPTIONS% is not FALSE, nor empty or assigned NO, the variable is generated, this text is replaced by the value of %OPTIONS% (physical options for the objects visible in the object property sheet).

[default %DEFAULT%]

In reverse engineering, if a text default 10 is found during reverse engineering, %DEFAULT% is filled with the value 10. However this specification is not mandatory and the SQL statement is reversed even if the specification is absent. In script generation, if default has a value (10 for example) during generation, the text is replaced by default 10 otherwise nothing is generated for the block.

[%--%? Is true : Is false]

You can use a conditional value for an optional string or variable. The two conditions are separated by a colon within the brackets used with the optional string or variable. For example, [%MAND%?Is true:Is false]. If %MAND% is evaluated as true or filled with a value (different from False or NO) during generation, this text is replaced by Is true . If not true, it is replaced by Is false .
[%--%=--? Is true : Is false]

You can also use keywords to test the content of a variable.

[%DELCONST%=RESTRICT?:[on delete %DELCONST%]]

Create table abc (a integer not null default 99)

Create table abc (a integer default 99 not null)

Both creation orders are identical but attributes are inverted.

Usually, the target XDB file supports only one notation with a specific order in the strings and variables. If you reverse engineer both orders, one of them will not go through because of the variable order. You can bypass this limitation using the .Z macro in the following way:

%COLUMN% %DATATYPE%[.Z: [%NOTNULL%][%DEFAULT%]]

With this macro, the reverse engineering parser no longer considers order within variables.

[.O:[procedure][proc]]

This statement will generate "procedure".

During reverse engineering, the parser will match either "procedure" and "proc" keywords, if none of them is present in the script, matching will fail.

Use of strings

A string between square brackets is always generated; however, whether this string is present or not in the SQL statement will not cancel the reverse engineering of the current statement since it is optional in the SQL syntax of the statement. For example, the syntax for creating a view includes a string:

create [or replace] view %VIEW% as %SQL%

When you reverse a script, if it contains only create or create or replace, in both situations the statement is reversed because the string is optional.