foreach: common_schema documentation

foreach

NAME

foreach(): Invoke a script on each element of given collection. $() is a synonym of this routine.

TYPE

Procedure

DESCRIPTION

This procedure accepts collections of varying types, including result sets, and invokes a QueryScript code per element.

The script can be as simple as a query, a set of queries, or a complex code.

The foreach() routine differs from the foreach flow control structure in QueryScript, though they both use similar syntax and share some use cases. Read here on the differences between the two.

foreach() passes on information about iterated values onto the script in two ways:

Using place holders (e.g. ${1}, ${2} etc.)
In this approach the script's text is manipulated such that placeholder occurrences are replaced with iterated values. This is a simple text search & replace approach, is very flexible, and allows for a lot of META tweaks.
See following tables and examples for more on placeholders.
Using input variables:
Variables are passed on to the script as input variables. These are dynamic variables, on which the genral rules for user defined variables apply.

foreach() acts on server side only, and does not require shell access nor the mysql command line client, although it may be spawned from within the mysql client.

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.
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 used as place holders for the callback queries.
Each row in the result set is an element.
The queries are allowed to act upon the table(s) being iterated, i.e. one can execute a DELETE on rows being iterated.
The place holders ${1} - ${9} relate to columns #1 - #9.
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.
The place holder ${1} indicates the iterated value.
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).
The place holders ${1}, ${2} indicate the iterated values.
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.
The place holder ${1} indicates the current constant value.
'schema': this is the collection of available schemata (e.g. as with SHOW DATABASES).
The place holder ${1} indicates the current schema. ${schema} is a synonym for ${1}.
'schema like expr': databases whose names match the given LIKE expression.
The place holder ${1} indicates the current schema. ${schema} is a synonym for ${1}.
'schema ~ /regexp/': databases whose names match the given regular expression.
The place holder ${1} indicates the current schema. ${schema} is a synonym for ${1}.
'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.
The place holder ${1} indicates the current table. ${table} is a synonym for ${1}.
The place holder ${2} indicates the schema. ${schema} is a synonym for ${2}.
The place holder ${3} indicates the storage engine. ${engine} is a synonym for ${3}.
The place holder ${4} indicates the CREATE_OPTIONS ${create_options} is a synonym for ${4}.
'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.
The place holder ${1} indicates the current table. ${table} is a synonym for ${1}.
The place holder ${2} indicates the schema for current table. ${schema} is a synonym for ${2}.
The place holder ${3} indicates the storage engine. ${engine} is a synonym for ${3}.
The place holder ${4} indicates the CREATE_OPTIONS ${create_options} is a synonym for ${4}.
'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.
The place holder ${1} indicates the current table. ${table} is a synonym for ${1}.
The place holder ${2} indicates the schema for current table. ${schema} is a synonym for ${2}.
The place holder ${3} indicates the storage engine. ${engine} is a synonym for ${3}.
The place holder ${4} indicates the CREATE_OPTIONS ${create_options} is a synonym for ${4}.

Any other type of input raises an error.

Following is a brief sample of valid collection input:

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

The following table summarizes the types of collections and the valid place holders:

Collection type	Valid place holders
SELECT query	${1}, ${2}, ..., ${9}, ${NR}
Numbers range	${1}, ${NR}
Two dimensional numbers range	${1}, ${2}, ${NR}
Constants set	${1}, ${NR}
'schema'	${1} or ${schema}, ${NR}
'schema like expr'	${1} or ${schema}, ${NR}
'schema ~ /regexp/'	${1} or ${schema}, ${NR}
'table in schema_name'	${1} or ${table), ${2} or ${schema}, ${3} or ${engine}, ${4} or ${create_options}, ${NR}
'table like expr'	${1} or ${table), ${2} or ${schema}, ${3} or ${engine}, ${4} or ${create_options}, ${NR}
'table ~ /regexp/'	${1} or ${table), ${2} or ${schema}, ${3} or ${engine}, ${4} or ${create_options}, ${NR}

${NR} is accepted in all collections, and returns the iteration index, 1 based. That is, the first element in a collection has 1 for ${NR}, the seconds has 2, etc. It is similar in concept to ${NR} in awk.

Invoker of this procedure must have the privileges required for execution of given queries.

SYNOPSIS

foreach(collection TEXT CHARSET utf8, execute_queries TEXT CHARSET utf8)

Input:

collection: the collection on which to iterate; must be in a recognized format as discussed above.
execute_queries: one or more queries to execute per loop iteration.
Queries are separated by semicolons (;). See exec() for details.

Since the routines relies on exec(), it accepts the following input config:

@common_schema_dryrun: when 1, queries are not executed, but rather printed.
@common_schema_verbose: when 1, queries are verbosed.

Output:

Whatever output the queries may produce.

EXAMPLES

SELECT query

Kill queries for user 'analytics'.
We take advantage of the fact we do not use ANSI_QUOTES, and so we are able to use nicer quoting scheme, as with JavaScript or Python.

mysql> call foreach(
	"SELECT id FROM INFORMATION_SCHEMA.PROCESSLIST WHERE user = 'analytics'", 
	'KILL QUERY ${1}');

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

mysql> call foreach(
	"SELECT Code, Name FROM world.Country WHERE Continent='Europe'", 
	"DELETE FROM world.CountryLanguage WHERE CountryCode = '${1}'; 
	DELETE FROM world.City WHERE CountryCode = '${1}'; 
	DELETE FROM Country WHERE Code = '${1}'; 
	INSERT INTO logs (msg) VALUES ('deleted country: name=${2}');");

Numbers range:

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

mysql> call foreach(
	'2001:2009', 
	"DELETE FROM sakila.rental WHERE rental_date >= '${1}-07-01' AND rental_date < '${1}-09-01'");

Generate tables; use $() synonym of foreach():

mysql> call $('1:50', "CREATE TABLE test.t_${1} (id INT)");
		
mysql> SHOW TABLES FROM test;
+----------------+ 
| Tables_in_test | 
+----------------+ 
| from_file      | 
| t              | 
| t_1            | 
| t_10           | 
| t_11           | 
| t_12           |
| t_13           |
| t_14           |
...
+----------------+

Two dimensional numbers range:

Fill in data for all tables generated on last step:

mysql> call foreach('1:50,1970:2038', "INSERT INTO test.t_${1} VALUES (${2})");

Constants set:

Generate databases:

mysql> call foreach('{US, GB, Japan, FRA}', 'CREATE DATABASE db_${1}');

mysql> show databases LIKE 'db_%';
+-----------------+
| Database (db_%) |
+-----------------+
| db_FRA          |
| db_GB           |
| db_Japan        |
| db_US           |
+-----------------+

'schema':

List full tables on all schemata:

mysql> call foreach('schema', "SHOW FULL TABLES FROM ${schema}");
+---------------------------------------+-------------+
| 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:

mysql> call foreach(
	'schema like wp%', 
	'CREATE TABLE ${schema}.wp_likes(id int, data VARCHAR(128))');

'schema ~ /regexp/':

Likewise, be more accurate on schema name:

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

'table in schema_name':

Convert all tables in world to InnoDB:

mysql> call $('table in world',	'ALTER TABLE ${schema}.${table} ENGINE=InnoDB');

'table like expr':

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

mysql> call foreach(
	'table like wp_posts', 
	'ALTER TABLE ${schema}.${table} ADD COLUMN post_geo_location VARCHAR(128);');

'table ~ /regexp/':

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

mysql> call foreach(
	'table ~ /^customer_data_[\d]+$/', 
	'ALTER TABLE ${schema}.${table} ADD COLUMN customer_geo_location VARCHAR(128);');

ENVIRONMENT

MySQL 5.1 or newer

AUTHOR

Shlomi Noach, Roland Bouman

Search online documentation

common_schema documentation

common_schema

foreach

NAME

TYPE

DESCRIPTION

SYNOPSIS

EXAMPLES

ENVIRONMENT

SEE ALSO

AUTHOR

QUERY SCRIPT

DEBUG

ROUTINES

VIEWS

DATA

META