It's me again.
That one who is crazy for data structures and believes they are at all useful.
I know you've seen an article about arrays just a few days ago, and it was mine as well, but I have something new to share and here goes.
I told you that arrays were useful.
So useful, in fact, that improving the library itself became quite an easy task.
And so I made some more addition, such as operations on whole arrays, rather than on single items.
(Fifth part of the MySQL 5 general purpose routine library presentation.)
multiple users
It occurred to me that having arrays in a multi-user environment could be either a blessing or a disaster, depending on which side you are.If you are likely to see the benefits, having a way of sharing data among users could be a pro.
If you are more focused on the drawbacks, then you'll see that having a user modify an array while you are doing something highly sensitive on it would not be desirable.
So I said, let's have it both ways.
And I made a few adjustments to the library, so that arrays can be restricted or shareable depending on one single function that will influence all the routines.
create function array_user()This is the "multi-user" implementation. Installing the library this way, each user will see only the arrays (s)he has created. Using the library with this function, different users can use an array with the same name, and they will stay separated and independent. Changing the return statement so that it gives 'all users', all arrays are unique among multiple users, i.e. if user A creates an array 'X', that array can be accessed, modified and erased by user B as well.
returns varchar(50)
reads sql data
begin
-- To have separate arrays for each user
-- uncomment the following line.
return substring_index(user(),'@',1);
--
-- To have arrays shared among users
-- use the statement below.
-- return 'all users';
end//
copying
Before you had arrays, you didn't care much. But now that they exist and are usable, you start wondering if you can make a copy of an array without being forced to insert items one by one. Of course you could do that using a for_each like function, but why bother, since there is a low-level array_copy function that will do it for you?(Yes, I know that a few days ago it was not there. I just made it yesterday, it passed the tests today, and now it's ready for general usage. )
Back to the issue. To copy an array, just call array_copy, supplying the source and destination name.
select array_copy('A', 'B');
# now array 'B' contains the same items as array 'A'While copying, you can also do some additional actions, such as filtering, appending and more. As you suspect, there is a array_copy_complete that allows a wide degree of control on your copy.sorting
Sorting is a special case of copying, since it will create a new array, with the elements sorted. By default, array_sort will create a new array with the elements sorted by value. If this is not enough for you, there is array_sort_complete that lets you choose among different ways of sorting (by value, by key/value, by value as numeric) and the sorting direction (ascending, descending). call array_show('A');
+-------------+-----------+------------+
| array_index | array_key | "A" values |
+-------------+-----------+------------+
| 0 | g | sheep |
| 1 | b | cow |
| 2 | c | ox |
| 3 | d | donkey |
| 4 | a | dog |
+-------------+-----------+------------+
select array_sort('A','B');
call array_show('B');
+-------------+-----------+------------+
| array_index | array_key | "B" values |
+-------------+-----------+------------+
| 0 | b | cow |
| 1 | a | dog |
| 2 | d | donkey |
| 3 | c | ox |
| 4 | g | sheep |
+-------------+-----------+------------+
# sorted by value
select array_sort_complete('A','B','K','asc');
call array_show('B');
+-------------+-----------+------------+
| array_index | array_key | "B" values |
+-------------+-----------+------------+
| 0 | a | dog |
| 1 | b | cow |
| 2 | c | ox |
| 3 | d | donkey |
| 4 | g | sheep |
+-------------+-----------+------------+
# sorted by keyNotice that the original array is not changed.merging
If you want to create an array from the contents of two existing arrays, array_merge is your function. One complication that arises when merging arrays is the 'keys', which must be unique in each array. Thus, if both arrays have one or more key in common, only one value can be taken. By default, array_merge uses the key from the first array. As usual, if you want to control such detail, there is array_merge_complete. call array_show('A');
+-------------+-----------+------------+
| array_index | array_key | "A" values |
+-------------+-----------+------------+
| 0 | b | cow |
| 1 | c | ox |
| 2 | d | donkey |
| 3 | a | dog |
+-------------+-----------+------------+
call array_show('B');
+-------------+-----------+------------+
| array_index | array_key | "B" values |
+-------------+-----------+------------+
| 0 | z | hare |
| 1 | y | rabbit |
| 2 | w | mole |
| 3 | a | beaver |
+-------------+-----------+------------+
select array_merge('A','B','C');
+--------------------------+
| array_merge('A','B','C') |
+--------------------------+
| C |
+--------------------------+
call array_show('C');
+-------------+-----------+------------+
| array_index | array_key | "C" values |
+-------------+-----------+------------+
| 0 | b | cow |
| 1 | c | ox |
| 2 | d | donkey |
| 3 | a | dog |
| 4 | z | hare |
| 5 | y | rabbit |
| 6 | w | mole |
+-------------+-----------+------------+Notice that 'C' does not have 8 elements, but just 7. That's because key 'a' was present in both 'A' and 'B' source arrays. The conflict was resolved taking the value from the first array ('dog' from array 'A'). To change this behaviour, you could have done this: select array_merge_complete('A','B','C','use_second');
+------------------------------------------------+
| array_merge_complete('A','B','C','use_second') |
+------------------------------------------------+
| C |
+------------------------------------------------+
call array_show('C');
+-------------+-----------+------------+
| array_index | array_key | "C" values |
+-------------+-----------+------------+
| 0 | b | cow |
| 1 | c | ox |
| 2 | d | donkey |
| 3 | a | beaver |
| 4 | z | hare |
| 5 | y | rabbit |
| 6 | w | mole |
+-------------+-----------+------------+appending
Appending is a variation of merging. Instead of creating a new array, an existing one gets the contents of another. Conflicting keys are resolved in favour of the second array. select array_append('A','B');
+-----------------------+
| array_append('A','B') |
+-----------------------+
| A |
+-----------------------+
call array_show('A');
+-------------+-----------+------------+
| array_index | array_key | "A" values |
+-------------+-----------+------------+
| 0 | b | cow |
| 1 | c | ox |
| 2 | d | donkey |
| 3 | a | beaver |
| 4 | z | hare |
| 5 | y | rabbit |
| 6 | w | mole |
+-------------+-----------+------------+filtering
Perl programmers know what grep is. It's a filter applied to an array with a regular expression, so that the resulting array is made of all elements matching such expression. array_grep does the same thing. Given an array and a regular expression, it will create an array containing all the elements from the source array where the elements match the pattern. call array_show('A');
+-------------+-----------+------------+
| array_index | array_key | "A" values |
+-------------+-----------+------------+
| 0 | b | cow |
| 1 | c | ox |
| 2 | d | donkey |
| 3 | a | dog |
+-------------+-----------+------------+
select array_grep('A','B','^d');
+--------------------------+
| array_grep('A','B','^d') |
+--------------------------+
| B |
+--------------------------+
call array_show('B');
+-------------+-----------+------------+
| array_index | array_key | "B" values |
+-------------+-----------+------------+
| 0 | d | donkey |
| 1 | a | dog |
+-------------+-----------+------------+The regular expression is anything that MySQL built-in REGEXP operator recognizes as valid. In this example, only the elements starting by 'd' are selected.piping
The astute reader must have noticed that all the functions mentioned in this section return the array name, and that all array routine require an array name as their first argument. This fact leads to the pleasant result that array functions can be piped together. Wherever you need an array name, you can use an array function returning an array name instead. The above operation about grep could have been done with just one query: call array_show( array_grep('A','B','^d'));
+-------------+-----------+------------+
| array_index | array_key | "B" values |
+-------------+-----------+------------+
| 0 | d | donkey |
| 1 | a | dog |
+-------------+-----------+------------+Here the result of array_grep (which is 'B') is given as first argument to array_show. You can take one step further, and sort the elements in the same step: call array_show( array_sort(array_grep('A','B','^d'),'C'));
+-------------+-----------+------------+
| array_index | array_key | "C" values |
+-------------+-----------+------------+
| 0 | a | dog |
| 1 | d | donkey |
+-------------+-----------+------------+Array 'B' (as result of array_grep) is passed to array_sort, and its result, 'C', is used by array_show. If you think that this looks very much like Perl array syntax, where you can pipe grep, sort and so on, you are right. Since MYSQL SP syntax is so primitive, I wanted to add some perlish power to it. I hope I succeeded.
No comments:
Post a Comment