query_script_variables: common_schema documentation

query_script_variables

QueryScript Variables: creation, assignment, expansion & cleanup

SYNOPSIS

var $table_name;
set $table_name := 'rental';
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = $table_name;
ALTER TABLE sakila.:$table_name ENGINE=InnoDB;

DESCRIPTION

In addition to supporting MySQL's user defined variables, QueryScript introduces script local variables, with controlled creation and cleanup, and with supported in-place expansion.

Declaration, usage and cleanup

The following code declares, sets and reads local variables:

var $x;
set $x := 3; 
while ($x > 0)
{
  var $y;
  set $y := CONCAT('Value of $x is: ', $x);
  SELECT $y AS msg;
  set $x := $x - 1;
}

The $x and $y variables behave much like a user defined variable. They can be assigned to, read from, used within a query.

However, the following differentiates it from MySQL's user defined variables:

Variables must be declared by the var statement.
At the point of declaration, they are known to be NULL.
Local variables are only recognized within their scope (see following).
Once a variable's scope terminates, the variable is reset to NULL. In the above example, $y is being reset to null at the end of each loop iteration.

MySQL's user defined variables, in contrast, retain their value throughout the session, or until they are assigned a new one.

Variables can be declared at any point; they do not necessarily have to be declared at the beginning of a block or script.

A foreach loop also declares variables, where the var statement is not required.

Variable names are case-sensitive.

Note: current implementation uses MySQL's user defined variables, using variable names which are unique within the script and the session in which they are declared.

Visibility

A variable is only visible in the scope in which it is declared. In the above example, $x is recognized throughout the script, but $y may only be accessed from within the loop's block.

One may use the above facts to force both cleanup and hiding of variables, by creating sub-blocks of code:

{
  var $x;
  set $x := 3;
}
-- $x is known to be cleared at this point, and will
-- not be recognized from this point and on.
{
  var $y;
  set $y := 'abc';
} 
-- $y is known to be cleared at this point, and will
-- not be recognized from this point and on.

It is not allowed to declare two variables of the same name in the same script, even if they are not visible to each other. Thus, it would be an error to re-declare $x in the second block in the above example.

Expansion

A variable may be expanded in-place. Expansion means the variable is replaced with the constant value it holds. Expansion allows the programmer to use variables where variables are not allowed. To illustrate, we must first look at the basics.

Consider the following code:

var $x;
set $x := 3;
SELECT $x, :$x;

+--------------------+---+
| @__qs_local_var_16 | 3 |
+--------------------+---+
|                  3 | 3 |
+--------------------+---+

The above is somewhat delicate: the $x variable is in fact implemented as a MySQL user defined variable called @__qs_local_var_16. It has the value of 3. However, the :$x value is the expansion of $x, and is the constant 3 (as is evident from column's name).

Now consider cases where variables cannot be used, yet expansion allows for seamless script approach:

set @n := 2;
var $x;
set $x := @n + 1;

-- An error: -- SELECT Name FROM world.City ORDER BY Population DESC LIMIT @n;
-- An error: -- SELECT Name FROM world.City ORDER BY Population DESC LIMIT $x;
-- 
-- A valid statement:
SELECT Name FROM world.City ORDER BY Population DESC LIMIT :$x;

+-----------------+
| Name            |
+-----------------+
| Mumbai (Bombay) |
| Seoul           |
| São Paulo       |
+-----------------+

As another example, consider:

set @t := 'City';
var $tbl;
set $tbl := 'City';

-- An error: -- ALTER TABLE world.@t ENGINE=InnoDB;
-- An error: -- ALTER TABLE world.$tbl ENGINE=InnoDB;
-- 
-- A valid statement:
ALTER TABLE world.:$tbl ENGINE=InnoDB;

An ALTER TABLE does not accept variables for table names. However, when using expansion, the last statement translates to ALTER TABLE world.City ENGINE=InnoDB; before being sent to MySQL.

Expansion occurs just before query execution. It is therefore possible to expand changing values, as follows:

foreach($t: {City, Country, CountryLanguage})
{
  ALTER TABLE world.:$t ENGINE=InnoDB;
}

At this moment, expansion only applies to SQL statements. They do not work for QueryScript statements, nor for expressions.

AUTHOR

Shlomi Noach

common_schema documentation

common_schema