back to the homepage
function array_create
p_array_name varchar(50),
p_array_size int
--
creates a new array,
with as many rows as stated by p_array_size.
--
returns the array_id
--
see also: array_size, array_exists
--
module : arrays
function array_copy_complete
p_array_name varchar(50),
p_new_array_name varchar(50),
on_key_conflict enum("use_first", "use_second"),
drop_new boolean,
key_regexp varchar(100),
value_regexp varchar(100)
--
creates a new array, copying the contents of an existing array.
if drop_new is false, then the first array is appended to the
second array.
If there is a conflict between keys, they are resolved according to the
value of on_key_conflict. If "use_first", then the value from the first
array is used, otherwise, the value from the second array is used.
If key_regexp or value_regexp are not null, they are used to filter
the original items, i.e. only items where the array key matches the
key_regexp expression and the array value matches the value_regexp
expression are copied.
if only one of them is not null, the other is ignored.
--
returns the array_name
--
see also: array_size, array_exists, array_create, array_grep, array_copy
--
module : arrays
function array_grep_complete
p_array_name varchar(50),
p_new_array_name varchar(50),
key_regexp varchar(100),
value_regexp varchar(100)
--
creates a new array, copying the contents of an existing array,
filtering its contents with key_regexp and value_regexp.
It is a shortcut for
array_copy_complete(p_array_name, p_new_array_name,
true, "use_second",key_regexp,value_regexp)
See array_copy_complete for more detail.
--
If key_regexp or value_regexp are not null, they are used to filter
the original items, i.e. only items where the array key matches the
key_regexp expression and the array value matches the value_regexp
expression are copied.
If only one of them is not null, the other is ignored.
If both are null, then it is like calling array_copy.
--
returns the array_name
--
see also: see also: array_size, array_exists, array_create, array_grep
--
module : arrays
function array_grep
p_array_name varchar(50),
p_new_array_name varchar(50),
value_regexp varchar(100)
--
creates a new array, copying the contents of an existing array
where the value matches value_regexp.
It is a shortcut for
array_grep_complete(p_array_name, p_new_array_name,
null,value_regexp)
See array_grep_complete for more detail.
--
If value_regexp is null, then it is like calling array_copy.
--
returns the array_name
--
see also: array_size, array_exists, array_create, array_grep_complete
--
module : arrays
function array_copy
p_array_name varchar(50),
p_new_array_name varchar(50)
--
creates a new array, copying the contents of an existing array.
It is a shortcut for
array_copy_complete(p_array_name, p_new_array_name,
true, "use_second",null,null)
See array_copy_complete for more detail;
--
returns the array_name
--
see also: array_size, array_exists, array_create
--
module : arrays
function array_append
p_base_array_name varchar(50),
p_second_array_name varchar(50)
--
appends the contents of p_second_array_name to the end of p_base_array_name.
--
returns the array_name
--
see also: array_size, array_exists, array_create
--
module : arrays
function array_sort_complete
p_array_name varchar(50),
p_new_array_name varchar(50),
order_by enum("K","V","VN","VK"),
order_direction enum("asc", "desc")
--
creates a new array, copying the contents of an existing array
in the given order, i.e. one of
- "K" by key
- "V" by value
- "VN" by value as numeric
- "VK" by value and key
The value of order_direction determines if the sorting is done
ascending ("asc") or descending ("desc").
--
returns the array_name
--
see also: array_size, array_exists, array_create, array_copy, array_sort
--
module : arrays
function array_sort
p_array_name varchar(50),
p_new_array_name varchar(50),
--
creates a new array, copying the contents of an existing array
order by value ascending.
For more options, use array_sort_complete
--
returns the array_name
--
see also: array_size, array_exists, array_create, array_copy, array_sort_complete
--
module : arrays
function array_merge_complete
p_first_array_name varchar(50),
p_second_array_name varchar(50),
p_new_array_name varchar(50),
on_key_conflict enum("use_first", "use_second")
)
--
creates a new array, copying the contents of two existing arrays.
If there is any key conflict, the key of the first or second array is
used according to the value of on_key_conflict;
--
returns the array_name
--
see also: array_size, array_exists, array_create, array_copy, array_sort,
array_sort_complete, array_merge
--
module : arrays
function array_merge
p_first_array_name varchar(50),
p_second_array_name varchar(50),
p_new_array_name varchar(50)
)
--
creates a new array, copying the contents of two existing arrays.
If there is any key conflict, the key of the first array is
used.
To have control on this behaviour, use array_merge_complete
--
returns the array_name
--
see also: array_size, array_exists, array_create, array_copy, array_sort,
array_sort_complete, array_merge_complete
--
module : arrays
function array_from_list_complete
p_list text,
p_array_name varchar(50),
p_separator varchar(10)
--
creates a new array, or replaces an existing one,
from the items in a p_separator separated list.
--
returns the array_name
--
see also: array_create, array_from_list,
array_to_list_complete, array_to_list
--
module : arrays
function array_from_list
p_list text,
p_array_name varchar(50)
--
creates a new array, or replaces an existing one,
from the items in a comma separated list.
--
It is a shortcut to array_from_list_complete(p_list,p_array_name,",");
--
returns the array_name
--
see also: array_create, array_from_list_complete,
array_to_list_complete, array_to_list
--
module : arrays
function array_from_pair_list_complete
p_list text,
p_array_name varchar(50),
p_list_separator varchar(10),
p_pair_separator varchar(10)
--
creates a new array, or replaces an existing one,
from the items in a p_list_separator separated list.
Each item is treated as a pair, with another separator
telling apart the key from the value. One example of
such a list is "one => 123, two=> 456, => three => 789"
--
returns the array_name
--
see also: array_create, array_from_list,
array_to_list_complete, array_to_list
array_to_pair_list_complete, array_to_pair_list
array_from_pair_list
--
module : arrays
function array_from_pair_list
p_list text,
p_array_name varchar(50)
--
creates a new array, or replaces an existing one,
from the items in a comma separated list.
Each item is treated as a pair, with the symbol "=>"
telling apart the key from the value. One example of
such a list is "one => 123, two=> 456, => three => 789"
--
This is just a shortcut for
array_from_pair_list_complete(p_list,p_array_name,",","=>");
--
returns the array_name
--
see also: array_create, array_from_list,
array_to_list_complete, array_to_list
array_from_pair_list_complete
--
module : arrays
function array_set_value_by_key
p_array_name varchar(50),
p_array_key varchar(50),
p_array_value text
--
inserts or modifies an array item by key.
if the array key exists, the original value is modified,
otherwise, it will be inserted.
--
returns the array size
--
see also: array_set_value_by_index, array_set_key_by_index
--
module : arrays
function array_unshift
p_array_name varchar(50),
p_array_value text
--
inserts a new item at the beginning of an array.
(used with array_shift implements a queue)
--
returns the array size
--
see also: array_shift, array_push, array_pop
--
module : arrays
function array_push
p_array_name varchar(50),
p_array_value text
--
inserts a new item at the end of an array.
(used with array_pop implements a stack)
--
returns the array size
--
see also: array_shift, array_unshift, array_pop
--
module : arrays
function array_set_key_by_index
p_array_name varchar(50),
p_array_index int,
p_array_key text
--
modifies the key of an existing item accessed by index
--
returns the 1 on success, 0 on failure
--
see also: array_set_value_by_key, array_set_value_by_index
--
module : arrays
function array_set_value_by_index
p_array_name varchar(50),
p_array_index int,
p_array_value text
--
inserts or modifies an array item at the given index
--
returns the array size
--
see also: array_set_value_by_key, array_set_key_by_index
--
module : arrays
function array_set
p_array_name varchar(50),
p_array_ndx_key varchar(50),
p_array_value text
--
inserts or modifies an array item.
if p_array_ndx_key is a number, it is treated as an index
and array_set_value_by_key is used.
If p_array_ndx_key is not a number, then it iss used as a key,
and array_set_value_by_key is used.
--
returns the array size
--
see also: array_set_value_by_key, array_set_key_by_index,
array_set_value_by_index array_setn
--
module : arrays
function array_setn
p_array_name varchar(50),
p_array_ndx_key varchar(50),
p_array_value text
--
works exactly like function array_set,
except that it returns the array_name instead of the
array size.
It is useful for concatenating several insertions into
a single SQL statement.
select array_setn( array_setn( array_setn(
"test1","keyA", "valueA"),
"keyB", "valueB"),
"keyC", "valueC");
select array_setn( array_setn( array_setn(
"test2",0, "valueA"),
1, "valueB"),
2, "valueC");
--
returns the array name
--
see also: array_set_value_by_key, array_set_key_by_index,
array_set_value_by_index, array_set
--
module : arrays
function array_get_value_by_index
p_array_name varchar(50),
p_array_index int
--
retrieves a value from array p_array_name at position p_array_index.
--
returns the array value
--
see also: array_get_value_by_key, array_get
--
module : arrays
function array_get_key_by_index
p_array_name varchar(50),
p_array_index int
--
retrieves a key from array p_array_name at position p_array_index.
--
returns the array key
--
see also: array_get_value_by_key, array_get, array_get_value_by_index
--
module : arrays
function array_to_list_complete
p_array_name varchar(50),
p_separator varchar(10)
--
converts the array values into a p_separator separated list
--
returns a new list as a string
--
see also: array_from_list_complete, array_from_list, array_to_list
--
module : arrays
function array_to_list
p_array_name varchar(50)
--
converts the array values into a comma separated list.
It is a shortcut to array_to_list_complete(p_array_name,",");
--
returns a new list as a string
--
see also: array_from_list_complete, array_from_list, array_to_list_complete
--
module : arrays
function array_to_list_complete
p_array_name varchar(50),
p_list_separator varchar(10),
p_pair_separator varchar(10)
--
converts the array values into a p_separator separated list
of pairs, each pair having a key and a value separated by
p_pair_separator
--
returns a new list as a string
--
see also: array_from_list_complete, array_from_list, array_to_list
array_to_pair_list, array_from_pair_list_complete, array_from_pair_list
--
module : arrays
function array_to_pair_list
p_array_name varchar(50)
--
converts the array values into a comma separated list of key/value pairs.
It is a shortcut to array_to_pair_list_complete(p_array_name,",","=>");
--
returns a new list as a string
--
see also: array_from_list_complete, array_from_list, array_to_list_complete
array_to_list, array_to_pair_list_complete
--
module : arrays
function array_get_value_by_key
p_array_name varchar(50),
p_array_key varchar(50)
--
retrieves a value from array p_array_name according to the value of p_array_key
(simulates Perl hashes or PHP arrays)
--
returns the array value
--
see also: array_get_value_by_index, array_get
--
module : arrays
function array_get
p_array_name varchar(50),
p_array_ndx_key varchar(50)
--
retrieves a value from array p_array_name.
If p_array_ndx_key is a number, then array_get_value_by_index is used,
otherwise it is treated as a key, and array_get_value_by_key is used.
(simulates Perl hashes or PHP arrays)
--
returns the array value
--
see also: array_get_value_by_index, array_get_value_by_key
--
module : arrays
function array_clear
p_array_name varchar(50),
--
removes all items from a given array
--
returns 1 on success, 0 on failure (if p_array_name does not exist)
--
see also: array_drop
--
module : arrays
function array_drop
p_array_name varchar(50),
--
removes all items from a given array and deletes it from the array index
--
returns 1 on success, 0 on failure (if p_array_name does not exist)
--
see also: array_clear
--
module : arrays
function array_size
p_array_name varchar(50),
--
returns the size of a given array (NULL if the array does not exist)
--
returns the array size
--
see also: array_create, array_exists
--
module : arrays
function array_max_index
p_array_name varchar(50),
--
returns the maximum index of a given array (NULL if the array does not exist)
--
returns the array maximum index
--
see also: array_create, array_exists, array_size
--
module : arrays
function array_exists
p_array_name varchar(50),
--
returns true if a given array exists, false otherwise.
--
see also: array_create, array_size
--
module : arrays
function array_pop
p_array_name varchar(50),
--
returns the last item in an array, and removes it from the array
(used with array_push, implements a stack)
--
see also: array_push, array_shift, array_unshift
--
module : arrays
function array_shift
p_array_name varchar(50),
--
returns the first item in an array, and removes it from the array
(used with array_unshift, implements a queue)
--
see also: array_push, array_pop, array_unshift
--
module : arrays
procedure array_create
p_array_name varchar(50),
p_array_size int
--
creates a new array,
with as many rows as stated by p_array_size.
--
returns the array_id
--
sets variable @array_create.
--
see also: array_size, array_exists
--
module : arrays
procedure array_copy_complete
p_array_name varchar(50),
p_new_array_name varchar(50),
on_key_conflict enum("use_first", "use_second"),
drop_new boolean,
key_regexp varchar(100),
value_regexp varchar(100)
--
creates a new array, copying the contents of an existing array.
if drop_new is false, then the first array is appended to the
second array.
If there is a conflict between keys, they are resolved according to the
value of on_key_conflict. If "use_first", then the value from the first
array is used, otherwise, the value from the second array is used.
If key_regexp or value_regexp are not null, they are used to filter
the original items, i.e. only items where the array key matches the
key_regexp expression and the array value matches the value_regexp
expression are copied.
if only one of them is not null, the other is ignored.
--
returns the array_name
--
sets variable @array_copy_complete.
--
see also: array_size, array_exists, array_create, array_grep, array_copy
--
module : arrays
procedure array_grep_complete
p_array_name varchar(50),
p_new_array_name varchar(50),
key_regexp varchar(100),
value_regexp varchar(100)
--
creates a new array, copying the contents of an existing array,
filtering its contents with key_regexp and value_regexp.
It is a shortcut for
array_copy_complete(p_array_name, p_new_array_name,
true, "use_second",key_regexp,value_regexp)
See array_copy_complete for more detail.
--
If key_regexp or value_regexp are not null, they are used to filter
the original items, i.e. only items where the array key matches the
key_regexp expression and the array value matches the value_regexp
expression are copied.
If only one of them is not null, the other is ignored.
If both are null, then it is like calling array_copy.
--
returns the array_name
--
sets variable @array_grep_complete.
--
see also: see also: array_size, array_exists, array_create, array_grep
--
module : arrays
procedure array_grep
p_array_name varchar(50),
p_new_array_name varchar(50),
value_regexp varchar(100)
--
creates a new array, copying the contents of an existing array
where the value matches value_regexp.
It is a shortcut for
array_grep_complete(p_array_name, p_new_array_name,
null,value_regexp)
See array_grep_complete for more detail.
--
If value_regexp is null, then it is like calling array_copy.
--
returns the array_name
--
sets variable @array_grep.
--
see also: array_size, array_exists, array_create, array_grep_complete
--
module : arrays
procedure array_copy
p_array_name varchar(50),
p_new_array_name varchar(50)
--
creates a new array, copying the contents of an existing array.
It is a shortcut for
array_copy_complete(p_array_name, p_new_array_name,
true, "use_second",null,null)
See array_copy_complete for more detail;
--
returns the array_name
--
sets variable @array_copy.
--
see also: array_size, array_exists, array_create
--
module : arrays
procedure array_append
p_base_array_name varchar(50),
p_second_array_name varchar(50)
--
appends the contents of p_second_array_name to the end of p_base_array_name.
--
returns the array_name
--
sets variable @array_append.
--
see also: array_size, array_exists, array_create
--
module : arrays
procedure array_sort_complete
p_array_name varchar(50),
p_new_array_name varchar(50),
order_by enum("K","V","VN","VK"),
order_direction enum("asc", "desc")
--
creates a new array, copying the contents of an existing array
in the given order, i.e. one of
- "K" by key
- "V" by value
- "VN" by value as numeric
- "VK" by value and key
The value of order_direction determines if the sorting is done
ascending ("asc") or descending ("desc").
--
returns the array_name
--
sets variable @array_sort_complete.
--
see also: array_size, array_exists, array_create, array_copy, array_sort
--
module : arrays
procedure array_sort
p_array_name varchar(50),
p_new_array_name varchar(50),
--
creates a new array, copying the contents of an existing array
order by value ascending.
For more options, use array_sort_complete
--
returns the array_name
--
sets variable @array_sort.
--
see also: array_size, array_exists, array_create, array_copy, array_sort_complete
--
module : arrays
procedure array_merge_complete
p_first_array_name varchar(50),
p_second_array_name varchar(50),
p_new_array_name varchar(50),
on_key_conflict enum("use_first", "use_second")
)
--
creates a new array, copying the contents of two existing arrays.
If there is any key conflict, the key of the first or second array is
used according to the value of on_key_conflict;
--
returns the array_name
--
sets variable @array_merge_complete.
--
see also: array_size, array_exists, array_create, array_copy, array_sort,
array_sort_complete, array_merge
--
module : arrays
procedure array_merge
p_first_array_name varchar(50),
p_second_array_name varchar(50),
p_new_array_name varchar(50)
)
--
creates a new array, copying the contents of two existing arrays.
If there is any key conflict, the key of the first array is
used.
To have control on this behaviour, use array_merge_complete
--
returns the array_name
--
sets variable @array_merge.
--
see also: array_size, array_exists, array_create, array_copy, array_sort,
array_sort_complete, array_merge_complete
--
module : arrays
procedure array_from_list_complete
p_list text,
p_array_name varchar(50),
p_separator varchar(10)
--
creates a new array, or replaces an existing one,
from the items in a p_separator separated list.
--
returns the array_name
--
sets variable @array_from_list_complete.
--
see also: array_create, array_from_list,
array_to_list_complete, array_to_list
--
module : arrays
procedure array_from_list
p_list text,
p_array_name varchar(50)
--
creates a new array, or replaces an existing one,
from the items in a comma separated list.
--
It is a shortcut to array_from_list_complete(p_list,p_array_name,",");
--
returns the array_name
--
sets variable @array_from_list.
--
see also: array_create, array_from_list_complete,
array_to_list_complete, array_to_list
--
module : arrays
procedure array_from_pair_list_complete
p_list text,
p_array_name varchar(50),
p_list_separator varchar(10),
p_pair_separator varchar(10)
--
creates a new array, or replaces an existing one,
from the items in a p_list_separator separated list.
Each item is treated as a pair, with another separator
telling apart the key from the value. One example of
such a list is "one => 123, two=> 456, => three => 789"
--
returns the array_name
--
sets variable @array_from_pair_list_complete.
--
see also: array_create, array_from_list,
array_to_list_complete, array_to_list
array_to_pair_list_complete, array_to_pair_list
array_from_pair_list
--
module : arrays
procedure array_from_pair_list
p_list text,
p_array_name varchar(50)
--
creates a new array, or replaces an existing one,
from the items in a comma separated list.
Each item is treated as a pair, with the symbol "=>"
telling apart the key from the value. One example of
such a list is "one => 123, two=> 456, => three => 789"
--
This is just a shortcut for
array_from_pair_list_complete(p_list,p_array_name,",","=>");
--
returns the array_name
--
sets variable @array_from_pair_list.
--
see also: array_create, array_from_list,
array_to_list_complete, array_to_list
array_from_pair_list_complete
--
module : arrays
procedure array_set_value_by_key
p_array_name varchar(50),
p_array_key varchar(50),
p_array_value text
--
inserts or modifies an array item by key.
if the array key exists, the original value is modified,
otherwise, it will be inserted.
--
returns the array size
--
sets variable @array_set_value_by_key.
--
see also: array_set_value_by_index, array_set_key_by_index
--
module : arrays
procedure array_unshift
p_array_name varchar(50),
p_array_value text
--
inserts a new item at the beginning of an array.
(used with array_shift implements a queue)
--
returns the array size
--
sets variable @array_unshift.
--
see also: array_shift, array_push, array_pop
--
module : arrays
procedure array_push
p_array_name varchar(50),
p_array_value text
--
inserts a new item at the end of an array.
(used with array_pop implements a stack)
--
returns the array size
--
sets variable @array_push.
--
see also: array_shift, array_unshift, array_pop
--
module : arrays
procedure array_set_key_by_index
p_array_name varchar(50),
p_array_index int,
p_array_key text
--
modifies the key of an existing item accessed by index
--
returns the 1 on success, 0 on failure
--
sets variable @array_set_key_by_index.
--
see also: array_set_value_by_key, array_set_value_by_index
--
module : arrays
procedure array_set_value_by_index
p_array_name varchar(50),
p_array_index int,
p_array_value text
--
inserts or modifies an array item at the given index
--
returns the array size
--
sets variable @array_set_value_by_index.
--
see also: array_set_value_by_key, array_set_key_by_index
--
module : arrays
procedure array_set
p_array_name varchar(50),
p_array_ndx_key varchar(50),
p_array_value text
--
inserts or modifies an array item.
if p_array_ndx_key is a number, it is treated as an index
and array_set_value_by_key is used.
If p_array_ndx_key is not a number, then it iss used as a key,
and array_set_value_by_key is used.
--
returns the array size
--
sets variable @array_set.
--
see also: array_set_value_by_key, array_set_key_by_index,
array_set_value_by_index array_setn
--
module : arrays
procedure array_list
--
prints a list of publicly viewable arrays.
Private arrays are the ones with a name starting with "_".
These are not listed. To see them, use array_full_list.
--
see also: array_full_list
--
module : arrays
procedure array_show
p_array_name varchar(50),
--
prints all elements of an array.
--
see also: array_list
--
module : arrays
procedure array_full_list
--
prints a list of all arrays, private first.
Private arrays are the ones with a name starting with "_"
--
see also: array_list
--
module : arrays
procedure array_clear
p_array_name varchar(50),
--
removes all items from a given array
--
returns 1 on success, 0 on failure (if p_array_name does not exist)
--
sets variable @array_clear.
--
see also: array_drop
--
module : arrays
procedure array_drop
p_array_name varchar(50),
--
removes all items from a given array and deletes it from the array index
--
returns 1 on success, 0 on failure (if p_array_name does not exist)
--
sets variable @array_drop.
--
see also: array_clear
--
module : arrays
procedure for_each_counter_complete
counter_start INT,
counter_end INT,
counter_delta INT,
sql_command text,
sql_before text,
sql_after text,
ba_mode enum("once","many")
--
executes a given sql_command using a counter,
starting at counter_start, incrementing it by
counter_delta units until it reaches counter_end.
sql_before and sql_after are commands to be executed
before and after the main command. If ba_mode is "once",
then sql_before is executed once, then all the sequence
of sql_command according to the counter, and finally
sql_after is executed. If ba_mode is "many", then sql_before
and sql_after are executed before and after each sql_command
within the loop.
--
User Variables:
the value of @FOR_COUNTER is used as the counter placeholder
instead of the default "$N"
--
Other variables:
Each sql command is preprocessed for placeholders, which
are replaced as follows:
$N takes the counter value within the loop
--
see also: for_each_counter, for_once, for_each_table_complete,
for_each_table_value_complete, for_each_array_item_complete
--
module : for each loops
procedure for_each_counter
counter_start INT,
counter_end INT,
counter_delta INT,
sql_command text
--
executes a given sql_command using a counter,
starting at counter_start, incrementing it by
counter_delta units until it reaches counter_end.
--
User Variables:
the value of @FOR_COUNTER is used as the counter placeholder
instead of the default "$N"
--
Other variables:
Each sql command is preprocessed for placeholders, which
are replaced as follows:
$N takes the counter value within the loop
--
see also: for_each_counter_complete, for_once, for_each_table_complete,
for_each_table_value_complete, for_each_array_item_complete
--
module : for each loops
procedure for_once
sql_command text
--
executes a given sql_command
It is a shortcut for
for_each_counter(1,1,1,sql_command);
It is useful when you need to execute a query from a
string and you do not want to use the longer procedure
of setting a variable, calling "prepare" and "execute".
--
see also: for_each_counter_complete, for_each_counter, for_each_table_complete,
for_each_table_value_complete, for_each_array_item_complete
--
module : for each loops
procedure for_each_table_complete
db_name varchar(50),
condition_text varchar(50),
sql_command text,
sql_before text,
sql_after text,
ba_mode enum("once","many")
--
executes a given sql_command for each table of a given
database.
--
"condition_text" is a filter to apply to the tables list.
The filter is against a query to information_schema.tables,
therefore any condition applicable to such table is acceptable.
--
sql_before and sql_after are commands to be executed
before and after the main command. If ba_mode is "once",
then sql_before is executed once, then all the sequence
of sql_command according to the counter, and finally
sql_after is executed. If ba_mode is "many", then sql_before
and sql_after are executed before and after each sql_command
within the loop.
--
User Variables:
- the value of @FOR_COUNTER is used as the counter placeholder
instead of the default "$N"
- @FOR_COUNTER_DELTA changes the counter increment (default: 1)
- @FOR_DB changes the database placeholder (default: "$D")
- @FOR_TABLE changes the table placeholder (default: "$T")
- @FOR_ENGINE changes the engine placeholder (default: "$E")
- @FOR_TYPE changes the type placeholder (default: "$Y")
- @FOR_ROWS changes the rows placeholder (default: "$R")
--
Other variables:
Each sql command is preprocessed for placeholders, which
are replaced as follows:
- $N takes the counter value within the loop
- $D takes the database name
- $T takes the table name
- $Y takes the table type
- $E takes the table engine
- $R takes the table rows
--
see also: for_each_counter, for_once, for_each_counter_complete,
for_each_table_value_complete, for_each_array_item_complete
for_each_table
--
module : for each loops
procedure for_each_table
db_name varchar(50),
condition_text varchar(50),
sql_command text
--
executes a given sql_command for each table of a given
database.
This is a shortcut for
for_each_table_complete(
db_name,
condition_text,
sql_command,
null,null,"once");
--
see syntax of for_each_table_complete, for a detailed
description of how the condition works and how to use
placeholders and user variables.
--
see also: for_each_counter, for_once, for_each_counter_complete,
for_each_table_complete, for_each_table_value_complete,
for_each_array_item_complete
--
module : for each loops
procedure for_each_array_item_complete
array_name varchar(50),
min_index int,
max_index int,
sql_command text,
sql_before text,
sql_after text,
ba_mode enum("once","many")
--
executes a given sql_command for each item of a given
array, starting at min_index and ending at max_index.
--
If an array item is null, execution is stopped, unless
the user variable @FOR_ARRAY_CONTINUE_ON_NULL is set.
--
sql_before and sql_after are commands to be executed
before and after the main command. If ba_mode is "once",
then sql_before is executed once, then all the sequence
of sql_command according to the counter, and finally
sql_after is executed. If ba_mode is "many", then sql_before
and sql_after are executed before and after each sql_command
within the loop.
--
User Variables:
- the value of @FOR_COUNTER is used as the counter placeholder
instead of the default "$N"
- @FOR_ITEM changes the item placeholder (default: "$I")
- @FOR_ARRAY_CONTINUE_ON_NULL determines whether the loop should
stop or go on when a NULL item is processed. The default
behavior is to exit the loop. If this variable is set, then
the item value is changed to an empty string, and the loop
continues.
--
Other variables:
Each sql command is preprocessed for placeholders, which
are replaced as follows:
- $N takes the counter value within the loop
- $I takes the item value
- $K takes the item key
--
see also: for_each_counter, for_once, for_each_counter_complete,
for_each_table_value_complete, for_each_table_complete
--
module : for each loops
procedure for_each_array_item
array_name varchar(50),
min_index int,
max_index int,
sql_command text
--
executes a given sql_command for each item of a given
array, starting at min_index and ending at max_index.
--
If an array item is null, execution is stopped, unless
the user variable @FOR_ARRAY_CONTINUE_ON_NULL is set.
--
This is a shortcut for
for_each_array_item_complete(
array_name,
min_index,
max_index,
sql_command,
null,
null,
"once");
See the syntax of for_each_array_item_complete, for a complete
explanation.
--
see also: for_each_counter, for_once, for_each_counter_complete,
for_each_table_value_complete, for_each_table_complete
for_each_array_item_complete for_each_array_item_simple
--
module : for each loops
procedure for_each_array_item
array_name varchar(50),
sql_command text
--
executes a given sql_command for each item of a given
array.
--
If an array item is null, execution is stopped, unless
the user variable @FOR_ARRAY_CONTINUE_ON_NULL is set.
--
This is a shortcut for
for_each_array_item_complete(
array_name,
0,
array_max_index(array_name),
sql_command,
null,
null,
"once");
See the syntax of for_each_array_item_complete, for a complete
explanation.
--
see also: for_each_counter, for_once, for_each_counter_complete,
for_each_table_value_complete, for_each_table_complete
for_each_array_item_complete for_each_array_item
--
module : for each loops
procedure for_each_list_item_complete
list_value text,
sql_command text,
sql_before text,
sql_after text,
ba_mode enum("once","many")
--
executes a given sql_command for each item of a given
comma-separated list
--
It is a shortcut for calling for_each_array_item_complete
with a temporary array created from the given list.
--
See for_each_array_item_complete for the explanation of
how placeholders and user variables are handled.
--
see also: for_each_counter, for_once, for_each_counter_complete,
for_each_table_value_complete, for_each_table_complete
for_each_array_item_complete, for_each_list_item
--
module : for each loops
procedure for_each_list_item
list_value text,
sql_command text
--
executes a given sql_command for each item of a given
comma-separated list
--
It is a shortcut for calling for_each_array_item_complete
with a temporary array created from the given list, and
without sql_before or sql_after
--
See for_each_array_item_complete for the explanation of
how placeholders and user variables are handled.
--
see also: for_each_counter, for_once, for_each_counter_complete,
for_each_table_value_complete, for_each_table_complete
for_each_array_item_complete, for_each_list_item_complete
--
module : for each loops
procedure for_each_table_value_complete
db_name varchar(50),
table_name varchar(50),
wanted_col1 varchar(50),
wanted_col2 varchar(50),
wanted_col3 varchar(50),
search_condition text,
sql_command text,
sql_before text,
sql_after text,
done_cond text,
ba_mode enum("once","many")
--
executes a given sql_command for each row of a given
table, according to a given search_condition.
--
Notice that, to overcome a present limitation in cursors syntax,
the table values are copied to a temporary table and then processed
from there. Thus, a loop from a large dataset can have a
significant overhead.
--
sql_before and sql_after are commands to be executed
before and after the main command. done_cond is a condition
that, if given, is evaluated for each loop, and if its result is true
it will terminate the loop immediately. It may be any SQL expression
that will evaluate to true or false. All placeholders are honoured.
If ba_mode is "once",
then sql_before is executed once, then all the sequence
of sql_command according to the counter, and finally
sql_after is executed. If ba_mode is "many", then sql_before
and sql_after are executed before and after each sql_command
within the loop.
--
User Variables:
- the value of @for_counter is used as the counter placeholder
instead of the default "$N"
- @for_item1 changes the item1 placeholder (default: "$I1")
- @for_item2 changes the item2 placeholder (default: "$I2")
- @for_item3 changes the item3 placeholder (default: "$I3")
--
Other variables:
Each sql command is preprocessed for placeholders, which
are replaced as follows:
- $N takes the counter value within the loop
- $I1 takes the value of wanted_col1
- $I2 takes the value of wanted_col2
- $I3 takes the value of wanted_col3
--
see also: for_each_counter, for_once, for_each_counter_complete,
for_each_array_item_complete, for_each_table_complete
for_each_table_value_simple
--
module : for each loops
procedure for_each_table_value
db_name varchar(50),
table_name varchar(50),
wanted_col1 varchar(50),
wanted_col2 varchar(50),
wanted_col3 varchar(50),
search_condition text,
sql_command text
--
executes a given sql_command for each row of a given
table, according to a given search_condition.
--
This is a shortcut for
for_each_table_value_complete (
db_name, table_name,
wanted_col1, wanted_col2, wanted_col3,
search_condition, sql_command,
null, null, null, "once");
See the documentation regarding for_each_table_value_complete,
for details on the implementation.
--
see also: for_each_counter, for_once, for_each_counter_complete,
for_each_array_item_complete, for_each_table_complete
for_each_table_value_complete, for_each_table_value_simple
--
module : for each loops
procedure for_each_table_value
db_name varchar(50),
table_name varchar(50),
wanted_col varchar(50),
search_condition text,
sql_command text
--
executes a given sql_command for each row of a given
table, according to a given search_condition.
--
This is a shortcut for
for_each_table_value_complete (
db_name, table_name,
wanted_col, null, null,
search_condition, sql_command,
null, null, "once");
See the documentation regarding for_each_table_value_complete,
for details on the implementation.
--
see also: for_each_counter, for_once, for_each_counter_complete,
for_each_array_item_complete, for_each_table_complete
for_each_table_value_complete, for_each_table_value
--
module : for each loops
function table_exists
p_db_name varchar(50),
p_table_name varchar(50)
--
Returns TRUE if a given table exists, FALSE otherwise.
Notice that it will return FALSE if given a name of a view
instead of a table. To get a view, use either view_exists
or table_or_view_exists
--
see also: view_exists, table_or_view_exists
--
module : globals
function table_or_view_exists
p_db_name varchar(50),
p_table_name varchar(50)
--
returns TRUE if a given table or view exists, FALSE otherwise
--
see also: table_exists, table_or_view_exists
--
module : globals
function view_exists
p_db_name varchar(50),
p_view_name varchar(50)
--
Returns TRUE if a given view exists, FALSE otherwise.
Notice that it will return FALSE if given a name of a table
instead of a view. To get a table, use either table_exists
or table_or_view_exists
--
see also: table_exists, table_or_view_exists
--
module : globals
function routine_exists
p_db_name varchar(50),
p_routine_name varchar(50),
p_routine_type enum("procedure", "function")
--
returns TRUE if a given routine exists, FALSE otherwise
--
see also: function_exists, procedure_exists
--
module : globals
function function_exists
p_db_name varchar(50),
p_function_name varchar(50)
--
Returns TRUE if a given function exists, FALSE otherwise.
Notice that it will return FALSE if given a name of an existing procedure
instead of a function.
--
It is a shortcut for calling
routine_exists(p_db_name, p_routine_name, "function");
--
see also: procedure_exists, routine_exists
--
module : globals
function procedure_exists
p_db_name varchar(50),
p_procedure_name varchar(50)
--
Returns TRUE if a given procedure exists, FALSE otherwise.
Notice that it will return FALSE if given a name of an existing function
instead of a procedure.
--
It is a shortcut for calling
routine_exists(p_db_name, p_routine_name, "procedure");
--
see also: function_exists, routine_exists
--
module : globals
function library_user
--
Returns the username of the current user.
Depending on its implementation, it allows for multi-user versions
of globals and arrays, or for shared version.
If it returns "all users", then it is the shared version.
--
Not to be called directly. It is used internally by routines in
globals and arrays modules.
--
Previously called "array_user", was moved to "globals" to be widely
available.
module : globals
function global_var_set
p_var_name varchar(50),
p_value text
--
creates or modifies a global variable p_var_name
with value p_value.
--
returns the variable value
--
see also: global_var_get, global_var_exists, global_var_drop
--
module : globals
function global_var_drop
p_var_name varchar(50)
--
Removes a global variable p_var_name
--
returns 1 if the variable existed, 0 otherwise
--
see also: global_var_set, global_var_exists
--
module : globals
function global_var_get
p_var_name varchar(50)
--
Returns the value of a global variable p_var_name
--
returns the variable value, null if variable does not exist
--
see also: global_var_set, global_var_exists
--
module : globals
function global_var_exists
p_var_name varchar(50)
--
Returns TRUE if a given global variable exists, FALSE otherwise
--
see also: global_var_set, global_var_get
--
module : globals
procedure global_var_set
p_var_name varchar(50),
p_value text
--
creates or modifies a global variable p_var_name
with value p_value.
--
returns the variable value
--
sets variable @global_var_set.
--
see also: global_var_get, global_var_exists, global_var_drop
--
module : globals
procedure global_var_drop
p_var_name varchar(50)
--
Removes a global variable p_var_name
--
returns 1 if the variable existed, 0 otherwise
--
sets variable @global_var_drop.
--
see also: global_var_set, global_var_exists
--
module : globals
function simple_sp
routine_name varchar(50),
parameters_array varchar(50)
--
returns a query to invoke a complex procedure with many parameters,
using an array as a list of named parameters.
Named parameters can be mentioned in any order.
--
returns the query with the parameters in the appropriate order.
--
see also: procedure simple_sp
--
module : named parameters
function simple_spl
routine_name varchar(50),
parameters_list text
--
returns a query to invoke a complex procedure with many parameters,
using a list of named parameters.
Named parameters can be mentioned in any order.
--
returns the query with the parameters in the appropriate order.
--
the default list and pair separators can be changes using
@list_separator and pair_separator user variables.
--
see also: procedure simple_spl
--
module : named parameters
procedure simple_sp
routine_name varchar(50),
parameters_array varchar(50)
--
calls a given procedure using an array as a list
of named parameters.
Named parameters can be mentioned in any order.
--
see also: function simple_sp
--
module : named parameters
procedure simple_sp
routine_name varchar(50),
parameters_list text
--
calls a given procedure using a list
of named parameters. List elements are separated by semicolons
(note that this is different from array_from_list defaults)
and pair elements are separated by "=>".
Named parameters can be mentioned in any order.
--
the default list and pair separators can be changes using
@list_separator and pair_separator user variables.
--
see also: function simple_sp, simple_spl
--
module : named parameters
function routine_syntax
p_db_name varchar(50),
p_routine_name varchar(50),
p_routine_type enum("function", "procedure")
--
returns the syntax of a given routine
(if it has been stored in the syntax table)
--
see also: fsyntax, psyntax
--
module : syntax helpers
function fsyntax
p_function_name varchar(50)
--
returns the syntax of a given function
(if it has been stored in the syntax table)
if the same function name is used in different databases,
then p_function_name could be espressed as "db_name.p_routine_name"
--
see also: routine_syntax, psyntax
--
module : syntax helpers
function psyntax
p_procedure_name varchar(50)
--
returns the syntax of a given procedure
(if it has been stored in the syntax table)
if the same procedure name is used in different databases,
then p_routine_name could be espressed as "db_name.p_routine_name"
--
see also: routine_syntax, fsyntax
--
module : syntax helpers
procedure my_routines
pattern varchar(50)
--
gives a list of routines in the syntax database,
according to the given pattern.
The pattern is treated with the REGEXP operator.
--
see also: my_procedures, my_functions
--
module : syntax helpers
procedure my_procedures
pattern varchar(50)
--
gives a list of procedures in the syntax database,
according to the given pattern.
The pattern is treated with the REGEXP operator.
--
see also: my_routines, my_functions
--
module : syntax helpers
procedure my_functions
pattern varchar(50)
--
gives a list of functions in the syntax database,
according to the given pattern.
The pattern is treated with the REGEXP operator.
--
see also: my_routines, my_procedures
--
module : syntax helpers
procedure check_table
p_db_name varchar(50),
p_table_name varchar(50)
--
Checks for a table existence and sets a test log for the record.
--
see also: check_routine, check_view
--
module : test utilities
procedure check_view
p_db_name varchar(50),
p_view_name varchar(50)
--
Checks for a view existence and sets a test log for the record.
--
see also: check_routine, check_table
--
module : test utilities
procedure check_routine
p_db_name varchar(50),
p_routine_name varchar(50),
p_routine_type enum("procedure", "function")
--
Checks for a routine existence and sets a test log for the record.
--
see also: check_routine_simple, check_for_routines_existence
--
module : test utilities
procedure check_routine_simple
p_routine_name varchar(50),
p_routine_type enum("procedure", "function")
--
Checks for a routine existence and sets a test log for the record.
Works like check_routine, without the "database" parameter. Instead
of that, a @database user variable is considered. It is useful
when you need to test several routines from the same database, and
you do not want to repeat the same parameter several times.
--
User Variables:
@database is used to determine the routine position
--
see also: check_routine, check_for_routines_existence
--
module : test utilities
procedure check_for_routines_existence
p_db_name varchar(50)
--
Checks for several routines existence and sets appropriate test logs for the record.
It should be used when checking many routines at once.
Their name and type should be entered into a _routine_list table,
and a call to check_for_routines_existence will invoke check_routine
for each record in such table.
--
Example:
insert into _routine_list (routine_name, routine_type)
values ("routine_a", "function"), ("routine_b", "procedure");
call check_for_routines_existence(database());
--
It should be used after a call to initialize_tests();
--
see also: check_routine, check_routine_simple, initialize_tests
--
module : test utilities
procedure initialize_tests
--
Creates a table for routine checking and prepares the log table for
testing.
--
It should be called before any other routine in a test unit.
--
see also: check_routine, check_routine_simple, initialize_tests
--
module : test utilities
procedure show_test_results
--
shows the details of all tests recorded in the testing unit,
including a summary of passed and failed tests.
If any test failed, their list is shown after the summary.
--
see also: log_test, show_test_results
--
module : test utilities
procedure log_test
p_description varchar(200),
p_result text,
p_expected text,
p_outcome boolean
--
Inserts a test result into table _test_results.
p_description is a description of what is being tested;
p_result is the value being tested;
p_expected is a textual description of what to expect;
p_outcome should be passed an expression, whose result will show if the
test passed or failed.
--
see also: initialize_tests, show_test_results
--
module : test utilities