Data generators

Data generators allows users to generate random values. There
are various types of generators, each one producing different type
of data. A variable initialized with a data generator behaves like a
regular simple variable except that it has a different value each
time it is used.

Record affectation

To access a specific location in a record, one must use the line
number (starts at 0) and can use either the column name (between
quotes) or the column number (starts at 0). This specific location
behaves like a simple variable. Note that a record cannot contain a
record.

SET @R1[0]['@A'] = 1; -- First line & first column
SET @R1[0][0] = 1; -- Same location
SET @R1[4]['@B'] = 1; -- Fifth line & second column
SET @R1[0][1] = 1; -- Same location

In the above example, three empty lines are automatically
inserted between the first and the fifth. Using an invalid column
number or name results in an exception.

Specific location can be used as right values as well. A
specific line can also be used as right value.

SET @R1[0][0] = @R3[0][1], @A = @R2[0][0]; -- Behaves like simple variables
SET @A = @R1[1]; -- @A becomes a record which is the first line of @R1

Remember that SET @R1[0][0] = @R2 is impossible
because a record cannot contain a record.

It is possible to assign a record to a variable, in this case
the variable does not need to be declared:

SET @A = @R3; -- @A becomes a record because it is assigned a record

SQL queries

Any SQL query executed returns a record. If the query is a SELECT query then it returns the results of the query. If
it is something else then it returns a one-line record
(true) if this is a success otherwise a zero-line record
(false).

SET @A = SELECT * FROM table; -- @A is a record with the results of the query
SET @B = INSERT INTO table ...; -- @B is a one-line record if the query succeeds

When a record is converted to a string, it is converted to its flat
representation. When converted to a number, the record is first converted
to a string and then to a number (see string conversion for more
details).

When a number is converted to a string, it is converted to its
string representation. When converted to a record, it is converted to a
one-line-one-column record whose value is the number.

When a string is converted to a number, if the string represents a
number then this number is returned else an exception is thrown. When
converted to a record, either the program can find a record pattern in the string or it converts it to
a one-line-one-column record whose value is the string. A record pattern
is:

Remember a string is surrounded by simple quotes. Strings
composing a record must be surrounded by double quotes which are escaped
with \\ (we double the slash because it is already a
special character for the enclosing simple quotes).

Operations

Operations can only be performed between operands of the same
type. Cast values in order to conform to this criterion.

Comparisons result in a number which is 0 or 1.

Strings

Comparisons: = <> > < <= >= AND
OR

Concatenation: +

SET @B = @A + 'abcdef'; -- @A must be a string and @B will be a string

Boolean value: non-empty string is true, empty
string is false

Inverse boolean value: NOT

Case-insensitive comparison: ~=

Numbers

Comparisons: = <> > < <= >= AND
OR

Arithmetic: + - * / %

SET @A = CAST ('10' AS INTEGER) + 5; -- '10' string is converted to a number

Boolean value: 0 is false, anything else is true

Inverse boolean value: NOT (note that NOT NOT
10 = 1)

An arithmetic operation involving at least one real number gives
a real number as a result:

Records

Comparisons: = <> > < <= >= AND
OR

Boolean value: zero-line record is false, anything
else is true

Inverse boolean value: NOT

Comparisons for records are about inclusion and exclusion. Order
of lines does not matter. <= means that each row in
the left operand has a match in the right operand. >= means the opposite. = means that <= and >= are both true at the same time...

Comparisons are performed on strings: even if a record contains
numbers like 10 and 1e1 we will have '10' <> '1e1'.

Control-of-flow structures

Conditional structure

pgScript commands are optional. BEGIN and END keywords are optional if there is only one pgScript command.

Loop structure

WHILE condition
BEGIN
pgScript commands
END

pgScript commands are optional. BEGIN and END keywords are optional if there is only one pgScript command.

BREAK ends the enclosing WHILE loop, while CONTINUE causes
the next iteration of the loop to execute. RETURN behaves like BREAK.

WHILE condition1
BEGIN
IF condition2
BEGIN
BREAK;
END
END

Conditions

Conditions are in fact results of operations. For example the
string comparison 'ab' = 'ac' will result in a number which
is false (the equality is not true).

IF 'ab' ~= 'AB' -- Case-insensitive comparison which result in 1 (true) which is true
BEGIN
-- This happens
END
IF 0 -- false
BEGIN
-- This does not happen
END
ELSE
BEGIN
-- This happens
END
WHILE 1
BEGIN
-- Infinite loop: use BREAK for exiting
END

It is possible to the result of a SQL SELECT query directly as a
condition. The query needs to be surrounded by parenthesis:

IF (SELECT 1 FROM table)
BEGIN
-- This means that table exists otherwise the condition would be false
END

Additional functions and procedures

Procedures

Procedures do not return a result. They must be used alone on a
line and cannot be assigned to a variable.

Print

Prints an expression on the screen:

PRINT 'The value of @A is' + CAST (@A AS STRING);

Assert

Throws an exception if the expression evaluated is false:

ASSERT 5 > 3 AND 'a' = 'a';

Remove line

Removes the specified line of a record:

RMLINE(@R[1]); -- Removes @R second line

Functions

Functions do return a result. Their return value can be assigned
to a variable, like the CAST operation.

Trim

Removes extra spaces surrounding a string:

SET @A = TRIM(' a '); -- @A = 'a'

Lines

Gives the number of lines in a record:

IF LINES(@R) > 0
BEGIN
-- Process
END

Columns

Gives the number of columns in a record:

IF COLUMNS(@R) > 0
BEGIN
-- Process
END

Random data generators

Overview of the generators

One can assign a variable (SET) with a random
data generators. This means each time the variable will be used it will
have a different value.

Sequence and seeding

Common parameters for data generators are sequence and seed.

sequence means that a sequence of values is
generated in a random order, in other words each value appears only once
before the sequence starts again: this is useful for columns with a UNIQUE constraint. For example, this generator:

SET @G = INTEGER(10, 15, 1); -- 1 means generate a sequence

It can generate such values: 14 12 10 13 11 15 14
12 10 13 11... Where each number appears once before
the sequence starts repeating.

sequence parameter must be an integer: if it
is 0 then no sequence is generated (default) and if something other than
0 then generate a sequence.

seed is an integer value for initializing a
generator: two generators with the same parameters and the same seed
will generate exactly the same
values.

seed must be an integer: it is used directly
to initialize the random data generator.

Timestamps (date/times)

min is a string representing a timestamp, max is a string representing a timestamp, sequence is an integer and seed is an
integer.

Strings

STRING ( min, max, [nb], [seed] );
STRING ( 10, 20, 5 );

min is an integer representing the minimum length
of a word, max is an integer representing the maximum
length of a word, nb is an integer representing the
number of words (default: 1) and seed is an
integer.

In the above example we generate 5 words (separated with a
space) whose size is between 10 and 20 characters.

Strings from regular expressions

REGEX ( regex, [seed] );
REGEX ( '[a-z]{1,3}@[0-9]{3}' );

regex is a string representing a simplified regular
expressions and seed is an integer.

Simplified regular expressions are composed of:

Sets of possible characters like [a-z_.] for
characters between a and z + _ and .

Single characters

It is possible to specify the minimum and maximum
length of the preceding set or single character:

{min, max} like {1,3} which stands
for length between 1 and 3

{min} like {3} which stands for
length of 3

Default (when nothing is specified) is length of 1

Note: be careful with spaces because 'a {3}' means
one a followed by three spaces because the 3 is about the last character or set of characters which is a space in this example.

If you need to use []\{ or }, they must be escaped because they are
special characters. Remember to use double
backslash: '\\[{3}' for three [.