query_script_variables: common_schema documentation

query_script_variables

QueryScript Variables: creation, assignment, expansion & cleanup

SYNOPSIS

var $v1, $v2, $v3;
set $v1 := 1;
var $v_pi := 3.14;
var $table_name := 'rental';
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = $table_name;
ALTER TABLE sakila.:$table_name ENGINE=InnoDB; 
CREATE TABLE test.tmp_:${table_name}_tbl (n INT) ENGINE=InnoDB;
INSERT INTO test.tmp_:${table_name}_tbl SELECT n FROM numbers;
SELECT $rowcount, $found_rows;

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

Multiple variables can be declared with a single var statement:

var $x, $y, $z;

It is possible to declare and assign a variable within the var statement as follows:

var $x := 'declared!';

However this is limited to a single variable. It is not possible to declare and assign multiple variables from within the same var statement. Multiple var statements would be required for that - one per variable.

QueryScript variables behave much like a user defined variable. They can be assigned to, read from, used within a query:

var $x := 3;
SELECT $x, $x + 1 AS next_value;
SET $x := POW($x, 2);
SET @msg := CONCAT('value is ', $x);

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 (unless assigned with a value at point of declaration)
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 while loop 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 & scope

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 := 'abc';
} 
-- $y is known to be cleared at this point, and will
-- not be recognized from this point and on.

One may declare two variables of the same name, as long as they are invisible to each other. In other words, they must be in non-overlapping scopes. For example, the following is valid:

{
  var $a := 3;
  var $b := 4;
}
var $a := 5;
{
  var $b := '6';
}

The second declaration point of $a appears after the first one went out of scope, which makes this a valid declaration. The same goes for $b.

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.

The value held by the local variable is interpreted as text, and is seamlessly integrated with the surrounding statement or expression.

Expansion syntax:

var $foo := 3;
SELECT $foo, :$foo, :${foo};

var $bar := 'Population > 1000000';
SELECT * FROM world.Country WHERE :$bar;

Consider the following code:

var $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).

Both :$x and :${x} result with the expanded value of $x. The latter is a more expressive form, and is useful in resolving ambiguities as in the following:

var $table_name := 'links';
CREATE TABLE test.:$table_name;                 -- fine
CREATE TABLE test.personal_:$table_name;        -- fine
CREATE TABLE test.:$table_name_to_categories;   -- impossible to resolve variable name
CREATE TABLE test.:${table_name}_to_categories; -- fine

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

set @n := 2;
var $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;
}

Expansion limitations:

Expansion applies for SQL statements and expressions.
Expansion does not apply to variables of QueryScript statements.
Expansion in expressions only applies once. Thus, in a while(:${condition}) {...} loop, the expansion of :${condition} occurs at one time only, before the first loop iteration. Future changes to the $condition local variable itself do not affect the expression.
Expanded variables must not specify local variables. Expanding the variable var $some_var := '$another_var' will result in a runtime error. You may relate to MySQL's user defined variables.

Built-in variables

The following variables are built into the system:

$found_rows: number of rows returned by previous SELECT statement, if any.
This value reflects MySQL's FOUND_ROWS(). Due to the interpreted nature of QueryScript, the transient FOUND_ROWS() value is lost by the time next statement executes. Hence the use of this variable.
This variable is transient, in that it only relates to the previously executed statement.
$rowcount: number of rows changed, deleted, or inserted by the last statement, if applicable.
This value reflects MySQL's ROW_COUNT(). Due to the interpreted nature of QueryScript, the transient ROW_COUNT() value is lost by the time next statement executes. Hence the use of this variable.
This variable is transient, in that it only relates to the previously executed statement.

AUTHOR

Shlomi Noach

Search online documentation

common_schema documentation

common_schema