query_script_foreach: common_schema documentation

query_script_foreach

QueryScript Flow Control: foreach statement

SYNOPSIS

foreach ($var1 [, $var2...] : collection)
{
  statement;
  statement, typically using $var1 [, $var2...];
}
[otherwise
  statement;]

DESCRIPTION

foreach is a flow control looping structure. It iterates collections of various types, assigns iterated values onto variables, and executes statements per iteration.

foreach iterates all elements in given collection, and executes a statement (or block of statements) per element. foreach terminates when all elements have been processed. The break and return statements also break iteration execution.

As opposed to the foreach() routine, foreach loops can be nested within other flow control structures, such as if-else, while, loop-while and foreach itself (see examples below). read more about the difference between the foreach control flow and the foreach() routine following.

The otherwise clause is optional. It will execute once should no iteration take place. That is, if at least one collection element exists, otherwise is skipped.

Variables

The foreach construct is also a variable declaration point. Variables are created and are assigned to, per loop iteration. As with the var statement, at least one variable is expected on foreach clause.

Any variables declared in the foreach clause are assigned with values as per iteration element. There are various types of collections, as described following. These can vary from SELECT statements to number ranges. Elements within collections can be single-valued or multi-valued. To illustrate, consider these examples:

foreach($counter: 1:1024)
{
  -- Collection is of numbers-range type. It is single valued.
  -- Do something with $counter
}

In the above example, a single variable, named $counter is declared. It is assigned with the integer values 1, 2, 3, ..., 1024, one value per iteration.

foreach($name, $pop: SELECT Name, Population FROM world.Country)
{
  -- Collection is of query type. It may hold up to 9 values (generated by 9 first columns).
  -- Do something with $name and $pop.
}

In this example, the SELECT query returns 2 columns: Name and Population, which we assign onto the variables $name and $pop, respectively. This happens per row in query's result set.

At least one variable must be declared. However, it is OK to list less than the amount of variables available from the collection. Hence, the following is valid:

foreach($name: SELECT Name, Population FROM world.Country)
{
  -- Do something with $name. We never bother to actually read the Population value.
}

Variable expansion is of importance, as it allows using variable names in place of constants which are otherwise non-dynamic, such as schema or table names. Read more on Variables before checking up on the examples.

Collection variables

Collections (described following) may themselves use local variables, as well as expanded variables.

Collections

foreach accepts several types of collections. They are automatically recognized by their pattern. The following collections are recognized (also see EXAMPLES section below):

A SELECT query: any SELECT statement makes for a collection, which is the result set of the query.
Each row in the result set is an element.
The query must specify result columns. That is, a SELECT * query is not valid.
Otherwise any SELECT query is valid, with any result set. However, only first 9 columns in the result set can be assigned to variables. Variables are matched to columns by order of definition.
Column values are treated as text, even though they may originally be of other types.
Numbers range: a range of integers, both inclusive, e.g. '1970:2038'.
Negative values are allowed. The first (left) value should be smaller or equal to the second (right) value, or else no iteration is performed.
One variable is assigned with value on this collection.
Two dimensional numbers range: a double range of integers, e.g. '-10:10,1970:2038'.
Each one of the ranges answers to the same rules as for a single range.
There will be m * n iterations on ranges of size m and n. For example, in the sample range above there will be 11 * 69 iterations (or elements).
Two variables are assigned with values on this collection.
This type of collection is maintained in compatibility with the foreach() routine. Script-wise, though, it is perfectly possible to nest two foreach number-ranges loops.
A constants set: a predefined set of constant values, e.g. '{red, green, blue}'.
Constants are separated by either spaces or commas (or both).
Constants can be quoted so as to allow spaces or commas within constant value. Quotes themselves are discarded.
Empty constants are discarded.
One variable is assigned with value on this collection.
'schema': this is the collection of available schemata (e.g. as with SHOW DATABASES).
One variable is assigned with value on this collection. This value is the name of the schema.
'schema like expr': databases whose names match the given LIKE expression.
One variable is assigned with value on this collection. This value is the name of the schema.
'schema ~ 'regexp'': databases whose names match the given regular expression.
One variable is assigned with value on this collection. This value is the name of the schema.
'table in schema_names': collection of all tables in given schema. Only tables are included: views are not listed.
This syntax is INFORMATION_SCHEMA friendly, in that it only scans and opens .frm files for given schema.
An element of this collection has 4 values, which can be mapped up to 4 variables:
1. Table name
2. Schema name
3. Storage engine name
4. Table's create options
'table like expr': all tables whose names match the given LIKE expression. These can be tables from different databases/schemata.
This syntax is INFORMATION_SCHEMA friendly, in that it only scans and opens .frm files for a single schema at a time. This reduces locks and table cache entries, while potentially taking longer to complete.
An element of this collection has 4 values, which can be mapped up to 4 variables:
1. Table name
2. Schema name
3. Storage engine name
4. Table's create options
'table ~ 'regexp'': all tables whose names match the given regular expression. These can be tables from different databases/schemata.
This syntax is INFORMATION_SCHEMA friendly, in that it only scans and opens .frm files for a single schema at a time. This reduces locks and table cache entries, while potentially taking longer to complete.
An element of this collection has 4 values, which can be mapped up to 4 variables:
1. Table name
2. Schema name
3. Storage engine name
4. Table's create options

Any other type of input raises an error.

Following is a brief sample of valid foreach expressions:

Collection type	Example of valid input
SELECT query	$id, $n: SELECT id, name FROM INFORMATION_SCHEMA.PROCESSLIST WHERE time > 20
Numbers range	$year: 1970:2038
Two dimensional numbers range	$hr, $minute: 0:23,0:59
Constants set	$country: {USA, "GREAT BRITAIN", FRA, IT, JP}
'schema'	$schema_name: schema
'schema like expr'	$customer_schema_name: schema like customer_%
'schema ~ 'regexp''	$customer_schema_name: schema ~ '^customer_[0-9]+$'
'table in schema_name'	$table_name, $schema_name, $engine, $create_options: table in sakila
'table like expr'	$table_name, $schema_name, $engine: table like wp_%
'table ~ 'regexp''	$table_name, $schema_name: table ~ '^state_[A-Z]{2}$'

EXAMPLES

SELECT query

Kill queries for user 'analytics'.

foreach($id: SELECT id FROM INFORMATION_SCHEMA.PROCESSLIST WHERE user = 'analytics') 
  KILL QUERY :$id;

(But see also killall())

Select multiple columns; execute multiple queries based on those columns:

foreach($code, $name: SELECT Code, Name FROM world.Country WHERE Continent='Europe')
{ 
  DELETE FROM world.CountryLanguage WHERE CountryCode = $code; 
  DELETE FROM world.City WHERE CountryCode = $code; 
  DELETE FROM world.Country WHERE Code = $code; 
  INSERT INTO test.logs (msg) VALUES (CONCAT('deleted country: name=', $name));
}

Numbers range:

Delete records from July-August for years 2001 - 2009:

foreach($year: 2001:2009) 
  DELETE FROM sakila.rental WHERE rental_date >= CONCAT($year, '-07-01') AND rental_date < CONCAT($year, '-09-01');

Generate tables:

foreach($i: 1:8)
  CREATE TABLE test.t_:${i} (id INT);
		
SHOW TABLES FROM test;
+----------------+ 
| Tables_in_test | 
+----------------+ 
| t_1            |
| t_2            |
| t_3            |
| t_4            |
| t_5            |
| t_6            |
| t_7            |
| t_8            |
+----------------+

Constants set:

Generate databases:

foreach($shard: {US, GB, Japan, FRA})
  CREATE DATABASE dbshard_:${shard};

show databases LIKE 'dbshard_%';
+----------------------+
| Database (dbshard_%) |
+----------------------+
| dbshard_FRA          |
| dbshard_GB           |
| dbshard_Japan        |
| dbshard_US           |
+----------------------+

'schema':

List full tables on all schemata:

foreach($scm: schema)
  SHOW FULL TABLES FROM :$scm;
+---------------------------------------+-------------+
| Tables_in_information_schema          | Table_type  |
+---------------------------------------+-------------+
| CHARACTER_SETS                        | SYSTEM VIEW |
| COLLATIONS                            | SYSTEM VIEW |
| COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW |
| COLUMNS                               | SYSTEM VIEW |
| COLUMN_PRIVILEGES                     | SYSTEM VIEW |
...
+---------------------------------------+-------------+

...
		
+-----------------+------------+
| Tables_in_world | Table_type |
+-----------------+------------+
| City            | BASE TABLE |
| Country         | BASE TABLE |
| CountryLanguage | BASE TABLE |
| Region          | BASE TABLE |
+-----------------+------------+

'schema like expr':

Create a new table in all hosted WordPress schemata:

foreach($scm: schema like wp%)
{
  CREATE TABLE :$scm.wp_likes(id int, data VARCHAR(128));
}

'schema ~ 'regexp'':

Likewise, be more accurate on schema name:

foreach ($scm: schema ~ '^wp_[\d]+$') 
{
  CREATE TABLE :$scm.wp_likes(id int, data VARCHAR(128));
}

'table in schema_name':

Compress InnoDB tables in sakila. Leave other engines untouched.

foreach($table, $schema, $engine: table in sakila)
  if ($engine = 'InnoDB')
    ALTER TABLE :$schema.:$table ENGINE=InnoDB ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8;

'table like expr':

Add a column to all wp_posts tables in hosted WordPress databases:

foreach($tbl, $scm: table like wp_posts)
  ALTER TABLE :$scm.:$tbl ADD COLUMN post_geo_location VARCHAR(128);

'table ~ 'regexp'':

Add a column to tables whose name matches the given regular expression, in any database:

foreach ($tbl, $scm: table ~ '^customer_data_[\d]+$')
  ALTER TABLE :$scm.:$tbl ADD COLUMN customer_geo_location VARCHAR(128);

Use an otherwise clause:

The following collection will not match any table:

foreach ($table, $schema: table like non_existing_table_name_%)
  select $table, $schema;
otherwise
  echo 'No tables found';

+-------------------+
| echo              |
+-------------------+
| 'No tables found' |
+-------------------+

NOTES

foreach vs. foreach()

The foreach flow control structure and the foreach() routine both iterate collections, accept similar (but not identical) collection syntax, and invoke scripts per loop iteration. They share some similar use cases, but are nevertheless different.

Very briefly, in Geekish, the differences are:

foreach() is more META.
foreach() cannot be nested, nor called from within a script.
foreach is more limited in terms of what it can do with iterated values.

To fully understand the differences, one must first be acquainted with variables expansion . Consider the following example:

foreach() routine
```
mysql> call foreach(
	'2001:2009', 
	"DELETE FROM sakila.rental WHERE rental_date >= '${1}-07-01' AND rental_date < '${1}-09-01'");
```
Iterated values are integers in the range 2001 - 2009. The placeholder ${1} is assigned with iterated value. The script (a single query in this case) never sees ${1} because the text of the script gets manipulated before being invoked. Thus, there are 9 different looking scripts invoked. For example, the second iteration would execute the following script:
```
DELETE FROM sakila.rental WHERE rental_date >= '2002-07-01' AND rental_date < '2002-09-01'
		
```
Since placeholders make for text manipulation of the script even before it is invoked, the options are limit-less. There is no constraint on what can or cannot be used as placeholder, as long as the resulting manipulated text makes for a valid script.

foreach flow control structure:
```
foreach($year: 2001:2009) 
  DELETE FROM sakila.rental WHERE rental_date >= CONCAT($year, '-07-01') AND rental_date < CONCAT($year, '-09-01');
```
The $year variable has the same limitations as any user-defined variable. It cannot be used from within a quoted text: '$year-07-01' is just a string, and the fact the term $year appears inside this quoted text means nothing. Hence, we must use CONCAT in order to build the date.

AUTHOR

Shlomi Noach

Search online documentation

common_schema documentation

common_schema