/
foreach.html
443 lines (415 loc) · 20.2 KB
/
foreach.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>foreach: common_schema documentation</title>
<meta name="description" content="foreach: common_schema" />
<meta name="keywords" content="foreach: common_schema" />
<link rel="stylesheet" type="text/css" href="css/style.css" />
</head>
<body>
<div id="main">
<div id="header">
<h1>common_schema</h1> <strong>2.2</strong> documentation
<div class="subtitle">DBA's framework for MySQL</div>
</div>
<div id="contentwrapper">
<div id="content">
<h2><a href="foreach.html">foreach</a></h2>
<h3>NAME</h3>
foreach(): Invoke a script on each element of given collection. <strong>$()</strong> is a synonym of this routine.
<h3>TYPE</h3>
Procedure
<h3>DESCRIPTION</h3>
<p>
This procedure accepts collections of varying types, including result sets, and invokes a
<a href="query_script.html">QueryScript</a> code per element.
</p>
<p>
The script can be as simple as a query, a set of queries, or a complex code.
</p>
<p>The <i>foreach()</i> routine differs from the <a href="query_script_foreach.html">foreach</a> flow control structure in QueryScript, though
they both use similar syntax and share some use cases. Read <a href="query_script_foreach.html#notes">here</a> on the differences between the two.</p>
<p>
<i>foreach()</i> passes on information about iterated values onto the script in two ways:
<ul>
<li>Using <i>place holders</i> (e.g. <strong>${1}, ${2}</strong> etc.)
<br/>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.
<br/>See following tables and examples for more on placeholders.
</li>
<li>Using <a href="query_script_input.html">input</a> variables:
<br/>Variables are passed on to the script as input variables. These are dynamic variables, on which
the genral rules for user defined variables apply.
</li>
</ul>
</p>
<p>
<i>foreach()</i> acts on server side only, and does not require shell access nor the <i>mysql</i>
command line client, although it may be spawned from within the <i>mysql</i> client.
</p>
<p>
<i>foreach()</i> accepts several types of collections. They are automatically recognized by their
pattern. The following collections are recognized (also see EXAMPLES section below):
<ul>
<li><strong>A SELECT query</strong>: any <strong>SELECT</strong> statement makes for a collection,
which is the result set of the query.
<br/>The query must specify result columns. That is, a <strong>SELECT *</strong> query is not valid.
<br/>Otherwise any SELECT query is valid, with any result set. However, only first <strong>9</strong>
columns in the result set can be used as place holders for the callback queries.
<br/>Each row in the result set is an element.
<br/>The queries are allowed to act upon the table(s) being iterated, i.e. one can execute a
DELETE on rows being iterated.
<br/>The place holders <strong>${1}</strong> - <strong>${9}</strong> relate to columns <strong>#1</strong> - <strong>#9</strong>.
</li>
<li><strong>Numbers range</strong>: a range of integers, both inclusive, e.g. <strong>'1970:2038'</strong>.
<br/>Negative values are allowed. The first (left) value should be smaller or equal to the second (right) value,
or else no iteration is performed.
<br/>The place holder <strong>${1}</strong> indicates the iterated value.
</li>
<li><strong>Two dimensional numbers range</strong>: a double range of integers, e.g. <strong>'-10:10,1970:2038'</strong>.
<br/>Each one of the ranges answers to the same rules as for a single range.
<br/>There will be <strong>m * n</strong> iterations on ranges of size <strong>m</strong> and <strong>n</strong>. For example,
in the sample range above there will be <strong>11 * 69</strong> iterations (or elements).
<br/>The place holders <strong>${1}, ${2}</strong> indicate the iterated values.
</li>
<li><strong>A constants set</strong>: a predefined set of constant values, e.g. <strong>'{red, green, blue}'</strong>.
<br/>Constants are separated by either spaces or commas (or both).
<br/>Constants can be quoted so as to allow spaces or commas within constant value. Quotes themselves are discarded.
<br/>Empty constants are discarded.
<br/>The place holder <strong>${1}</strong> indicates the current constant value.
</li>
<li><strong>'schema'</strong>: this is the collection of available schemata (e.g. as with <strong>SHOW DATABASES</strong>).
<br/>The place holder <strong>${1}</strong> indicates the current schema. <strong>${schema}</strong> is a synonym for <strong>${1}</strong>.
</li>
<li><strong>'schema like <i>expr</i>'</strong>: databases whose names match the given <strong>LIKE</strong> expression.
<br/>The place holder <strong>${1}</strong> indicates the current schema. <strong>${schema}</strong> is a synonym for <strong>${1}</strong>.
</li>
<li><strong>'schema ~ <i>/regexp/</i>'</strong>: databases whose names match the given regular expression.
<br/>The place holder <strong>${1}</strong> indicates the current schema. <strong>${schema}</strong> is a synonym for <strong>${1}</strong>.
</li>
<li><strong>'table in <i>schema_names</i>'</strong>: collection of all tables in given schema. Only tables are included: views are not listed.
<br/>This syntax is <strong>INFORMATION_SCHEMA</strong> friendly, in that it only scans and opens <strong>.frm</strong> files for
given schema.
<br/>The place holder <strong>${1}</strong> indicates the current table. <strong>${table}</strong> is a synonym for <strong>${1}</strong>.
<br/>The place holder <strong>${2}</strong> indicates the schema. <strong>${schema}</strong> is a synonym for <strong>${2}</strong>.
<br/>The place holder <strong>${3}</strong> indicates the storage engine. <strong>${engine}</strong> is a synonym for <strong>${3}</strong>.
<br/>The place holder <strong>${4}</strong> indicates the CREATE_OPTIONS <strong>${create_options}</strong> is a synonym for <strong>${4}</strong>.
</li>
<li><strong>'table like <i>expr</i>'</strong>: all tables whose names match the given <strong>LIKE</strong> expression. These can be tables
from different databases/schemata.
<br/>This syntax is <strong>INFORMATION_SCHEMA</strong> friendly, in that it only scans and opens <strong>.frm</strong> files for
a single schema at a time. This reduces locks and table cache entries, while potentially taking longer to complete.
<br/>The place holder <strong>${1}</strong> indicates the current table. <strong>${table}</strong> is a synonym for <strong>${1}</strong>.
<br/>The place holder <strong>${2}</strong> indicates the schema for current table. <strong>${schema}</strong> is a synonym for <strong>${2}</strong>.
<br/>The place holder <strong>${3}</strong> indicates the storage engine. <strong>${engine}</strong> is a synonym for <strong>${3}</strong>.
<br/>The place holder <strong>${4}</strong> indicates the CREATE_OPTIONS <strong>${create_options}</strong> is a synonym for <strong>${4}</strong>.
</li>
<li><strong>'table ~ <i>/regexp/</i>'</strong>: all tables whose names match the given regular expression. These can be tables
from different databases/schemata.
<br/>This syntax is <strong>INFORMATION_SCHEMA</strong> friendly, in that it only scans and opens <strong>.frm</strong> files for
a single schema at a time. This reduces locks and table cache entries, while potentially taking longer to complete.
<br/>The place holder <strong>${1}</strong> indicates the current table. <strong>${table}</strong> is a synonym for <strong>${1}</strong>.
<br/>The place holder <strong>${2}</strong> indicates the schema for current table. <strong>${schema}</strong> is a synonym for <strong>${2}</strong>.
<br/>The place holder <strong>${3}</strong> indicates the storage engine. <strong>${engine}</strong> is a synonym for <strong>${3}</strong>.
<br/>The place holder <strong>${4}</strong> indicates the CREATE_OPTIONS <strong>${create_options}</strong> is a synonym for <strong>${4}</strong>.
</li>
</ul>
Any other type of input raises an error.
</p>
<p>
Following is a brief sample of valid collection input:
<table>
<tr><th>Collection type</th><th>Example of valid input</th></tr>
<tr><td>SELECT query</td><td>'SELECT id, name FROM INFORMATION_SCHEMA.PROCESSLIST WHERE time > 20'</td></tr>
<tr><td>Numbers range</td><td>'1970:2038'</td></tr>
<tr><td>Two dimensional numbers range</td><td>'0:23,0:59'</td></tr>
<tr><td>Constants set</td><td>'{USA, "GREAT BRITAIN", FRA, IT, JP}'</td></tr>
<tr><td>'schema'</td><td>'schema'</td></tr>
<tr><td>'schema like <i>expr</i>'</td><td>'schema like customer_%'</td></tr>
<tr><td>'schema ~ <i>/regexp/</i>'</td><td>'schema ~ /^customer_[0-9]+$/</td></tr>
<tr><td>'table in <i>schema_name</i>'</td><td>'table in sakila'</td></tr>
<tr><td>'table like <i>expr</i>'</td><td>'table like wp_%'</td></tr>
<tr><td>'table ~ <i>/regexp/</i>'</td><td>'table ~ /^state_[A-Z]{2}$/'</td></tr>
</table>
</p>
<p>
The following table summarizes the types of collections and the valid place holders:
<table>
<tr><th>Collection type</th><th>Valid place holders</th></tr>
<tr><td>SELECT query</td><td>${1}, ${2}, ..., ${9}, ${NR}</td></tr>
<tr><td>Numbers range</td><td>${1}, ${NR}</td></tr>
<tr><td>Two dimensional numbers range</td><td>${1}, ${2}, ${NR}</td></tr>
<tr><td>Constants set</td><td>${1}, ${NR}</td></tr>
<tr><td>'schema'</td><td>${1} or ${schema}, ${NR}</td></tr>
<tr><td>'schema like <i>expr</i>'</td><td>${1} or ${schema}, ${NR}</td></tr>
<tr><td>'schema ~ <i>/regexp/</i>'</td><td>${1} or ${schema}, ${NR}</td></tr>
<tr><td>'table in <i>schema_name</i>'</td><td>${1} or ${table), ${2} or ${schema}, ${3} or ${engine}, ${4} or ${create_options}, ${NR}</td></tr>
<tr><td>'table like <i>expr</i>'</td><td>${1} or ${table), ${2} or ${schema}, ${3} or ${engine}, ${4} or ${create_options}, ${NR}</td></tr>
<tr><td>'table ~ <i>/regexp/</i>'</td><td>${1} or ${table), ${2} or ${schema}, ${3} or ${engine}, ${4} or ${create_options}, ${NR}</td></tr>
</table>
<strong>${NR}</strong> is accepted in all collections, and returns the iteration index, <strong>1</strong> based. That is,
the first element in a collection has <strong>1</strong> for <strong>${NR}</strong>, the seconds has <strong>2</strong>, etc.
It is similar in concept to <strong>${NR}</strong> in <i>awk</i>.
</p>
<p>
Invoker of this procedure must have the privileges required for execution of given queries.
</p>
<h3>SYNOPSIS</h3>
<p>
<blockquote><pre>foreach(collection TEXT CHARSET utf8, execute_queries TEXT CHARSET utf8)</pre></blockquote>
</p>
<p>
Input:
<ul>
<li><strong>collection</strong>: the collection on which to iterate; must be in a recognized format as discussed above.
</li>
<li><strong>execute_queries</strong>: one or more queries to execute per loop iteration.
<br/>Queries are separated by semicolons (<strong>;</strong>). See <a href="exec.html">exec()</a> for
details.
</li>
</ul>
Since the routines relies on <a href="exec.html">exec()</a>, it accepts the following
input config:
<ul>
<li><strong>@common_schema_dryrun</strong>: when <strong>1</strong>, queries are not executed, but rather printed.</li>
<li><strong>@common_schema_verbose</strong>: when <strong>1</strong>, queries are verbosed.</li>
</ul>
Output:
<ul>
<li>Whatever output the queries may produce.</li>
</ul>
</p>
<h3>EXAMPLES</h3>
<ul>
<li>SELECT query
<p>
Kill queries for user <strong>'analytics'</strong>.
<br/>We take advantage of the fact we do not use <a href="http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_ansi_quotes">ANSI_QUOTES</a>,
and so we are able to use nicer quoting scheme, as with JavaScript or Python.
</p>
<blockquote><pre>mysql> call foreach(
"SELECT id FROM INFORMATION_SCHEMA.PROCESSLIST WHERE user = 'analytics'",
'KILL QUERY ${1}');
</pre></blockquote>
<p>
Select multiple columns; execute multiple queries based on those columns:
</p>
<blockquote><pre>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}');");
</pre></blockquote>
</li>
<li>Numbers range:
<p>
Delete records from July-August for years <strong>2001</strong> - <strong>2009</strong>:
</p>
<blockquote><pre>mysql> call foreach(
'2001:2009',
"DELETE FROM sakila.rental WHERE rental_date >= '${1}-07-01' AND rental_date < '${1}-09-01'");
</pre></blockquote>
<p>
Generate tables; use <i>$()</i> synonym of <i>foreach()</i>:
</p>
<blockquote><pre>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 |
...
+----------------+
</pre></blockquote>
</li>
<li>Two dimensional numbers range:
<p>
Fill in data for all tables generated on last step:
</p>
<blockquote><pre>mysql> call foreach('1:50,1970:2038', "INSERT INTO test.t_${1} VALUES (${2})");
</pre></blockquote>
</li>
<li>Constants set:
<p>
Generate databases:
</p>
<blockquote><pre>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 |
+-----------------+
</pre></blockquote>
</li>
<li>'schema':
<p>
List full tables on all schemata:
</p>
<blockquote><pre>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 |
+-----------------+------------+
</pre></blockquote>
</li>
<li>'schema like <i>expr</i>':
<p>
Create a new table in all hosted WordPress schemata:
</p>
<blockquote><pre>mysql> call foreach(
'schema like wp%',
'CREATE TABLE ${schema}.wp_likes(id int, data VARCHAR(128))');
</pre></blockquote>
</li>
<li>'schema ~ <i>/regexp/</i>':
<p>
Likewise, be more accurate on schema name:
</p>
<blockquote><pre>mysql> call foreach(
'schema ~ /^wp_[\d]+$/',
'CREATE TABLE ${schema}.wp_likes(id int, data VARCHAR(128))');
</pre></blockquote>
</li>
<li>'table in <i>schema_name</i>':
<p>
Convert all tables in <strong>world</strong> to InnoDB:
</p>
<blockquote><pre>mysql> call $('table in world', 'ALTER TABLE ${schema}.${table} ENGINE=InnoDB');
</pre></blockquote>
</li>
<li>'table like <i>expr</i>':
<p>
Add a column to all <strong>wp_posts</strong> tables in hosted WordPress databases:
</p>
<blockquote><pre>mysql> call foreach(
'table like wp_posts',
'ALTER TABLE ${schema}.${table} ADD COLUMN post_geo_location VARCHAR(128);');
</pre></blockquote>
</li>
<li>'table ~ <i>/regexp/</i>':
<p>
Add a column to tables whose name matches the given regular expression, in any database:
</p>
<blockquote><pre>mysql> call foreach(
'table ~ /^customer_data_[\d]+$/',
'ALTER TABLE ${schema}.${table} ADD COLUMN customer_geo_location VARCHAR(128);');
</pre></blockquote>
</li>
</ul>
<h3>ENVIRONMENT</h3>
MySQL 5.1 or newer
<h3>SEE ALSO</h3>
<a href="exec.html">exec()</a>,
<a href="exec_single.html">exec_single()</a>,
<a href="repeat_exec.html">repeat_exec()</a>
<h3>AUTHOR</h3>
Shlomi Noach, Roland Bouman
<br/>
</div>
<div id="sidebarwrapper">
<div id="search">
Search online documentation
<form id="search_form" name="search_form" method="GET"
action="http://www.google.com/search"
onsubmit="document.forms['search_form']['q'].value = 'site:http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/ '+document.forms['search_form']['search_term'].value;">
<input type="text" name="search_term" value=""/>
<input type="hidden" name="q" value=""/>
<input type="submit" value="go"/>
</form>
</div>
<div id="menu">
<ul>
<li><a title="Introduction" href="introduction.html">Introduction</a></li>
<li><a title="Documentation" href="documentation.html">Documentation</a></li>
<li><a title="Download" href="download.html">Download</a></li>
<li><a title="Install" href="install.html">Install</a></li>
<li><a title="Risks" href="risks.html">Risks</a></li>
</ul>
<h3>QUERY SCRIPT</h3>
<ul>
<li><a title="QueryScript" href="query_script.html">QueryScript</a></li>
<li><a title="Execution" href="query_script_execution.html">Execution</a></li>
<li><a title="Flow control" href="query_script_flow_control.html">Flow control</a></li>
<li><a title="Statements" href="query_script_statements.html">Statements</a></li>
<li><a title="Expressions" href="query_script_expressions.html">Expressions</a></li>
<li><a title="Variables" href="query_script_variables.html">Variables</a></li>
</ul>
<h3>DEBUG</h3>
<ul>
<li><a title="rdebug" href="rdebug.html">rdebug</a></li>
<li><a title="rdebug API" href="rdebug_api.html">rdebug API</a></li>
<li><a title="rdebug workflow" href="rdebug_workflow.html">Workflow</a></li>
</ul>
<h3>ROUTINES</h3>
<ul>
<li><a title="Execution & flow control" href="execution_routines.html">Execution & flow control</a></li>
<li><a title="General" href="general_routines.html">General</a></li>
<li><a title="Process" href="process_routines.html">Process</a></li>
<li><a title="Query analysis" href="query_analysis_routines.html">Query analysis</a></li>
<li><a title="Schema analysis" href="schema_analysis_routines.html">Schema analysis</a></li>
<li><a title="Security" href="security_routines.html">Security</a></li>
<li><a title="Text" href="text_routines.html">Text</a></li>
<li><a title="Time & date" href="temporal_routines.html">Time & date</a></li>
<li><a title="Charting" href="charting_routines.html">Charting</a></li>
</ul>
<h3>VIEWS</h3>
<ul>
<li><a title="Schema analysis" href="schema_analysis_views.html">Schema analysis</a></li>
<li><a title="Data dimension" href="data_dimension_views.html">Data dimension</a></li>
<li><a title="Process" href="process_views.html">Process</a></li>
<li><a title="Security" href="security_views.html">Security</a></li>
<li><a title="Monitoring" href="monitoring_views.html">Monitoring</a></li>
<li><a title="InnoDB Plugin" href="innodb_plugin_views.html">InnoDB Plugin</a></li>
<li><a title="Percona server" href="percona_server_views.html">Percona Server</a></li>
<li><a title="TokuDB" href="tokudb_views.html">TokuDB</a></li>
</ul>
<h3>DATA</h3>
<ul>
<li><a title="tables" href="tables.html">Tables</a></li>
<li><a title="variables" href="variables.html">Variables</a></li>
</ul>
<h3>META</h3>
<ul>
<li><a title="Help" href="help.html">help</a></li>
<li><a title="Metadata" href="metadata.html">metadata</a></li>
<li><a title="status" href="status.html">status</a></li>
</ul>
</div>
</div>
<div class="clear"> </div>
<div id="footnote" align="center">
<a href="">common_schema</a> documentation
</div>
</div>
</div>
</body>
</html>