reference/sqlite3/sqlite3/createaggregate.xml
855bfee2f3db70d7dbb4c60c7c4a4efa567f1c60
855bfee2f3db70d7dbb4c60c7c4a4efa567f1c60
...
...
@@ -1,6 +1,5 @@
1
1
<?xml version="1.0" encoding="utf-8"?>
2
2
<!-- $Revision$ -->
3
-
4
3
<refentry xml:id="sqlite3.createaggregate" xmlns="http://docbook.org/ns/docbook" xmlns:xlink="http://www.w3.org/1999/xlink">
5
4
<refnamediv>
6
5
<refname>SQLite3::createAggregate</refname>
...
...
@@ -9,12 +8,12 @@
9
8
10
9
<refsect1 role="description">
11
10
&reftitle.description;
12
-
<methodsynopsis>
11
+
<methodsynopsis role="SQLite3">
13
12
<modifier>public</modifier> <type>bool</type><methodname>SQLite3::createAggregate</methodname>
14
13
<methodparam><type>string</type><parameter>name</parameter></methodparam>
15
-
<methodparam><type>mixed</type><parameter>step_callback</parameter></methodparam>
16
-
<methodparam><type>mixed</type><parameter>final_callback</parameter></methodparam>
17
-
<methodparam choice="opt"><type>int</type><parameter>argument_count</parameter><initializer>-1</initializer></methodparam>
14
+
<methodparam><type>callable</type><parameter>stepCallback</parameter></methodparam>
15
+
<methodparam><type>callable</type><parameter>finalCallback</parameter></methodparam>
16
+
<methodparam choice="opt"><type>int</type><parameter>argCount</parameter><initializer>-1</initializer></methodparam>
18
17
</methodsynopsis>
19
18
<para>
20
19
Registers a PHP function or user-defined function for use as an SQL
...
...
@@ -35,25 +34,106 @@
35
34
</listitem>
36
35
</varlistentry>
37
36
<varlistentry>
38
-
<term><parameter>step_callback</parameter></term>
37
+
<term><parameter>stepCallback</parameter></term>
39
38
<listitem>
40
39
<para>
41
-
The name of a PHP function or user-defined function to apply as a
42
-
callback for every item in the aggregate.
40
+
Callback function called for each row of the result set. Your PHP
41
+
function should accumulate the result and store it in the aggregation
42
+
context.
43
+
</para>
44
+
<para>
45
+
This function need to be defined as:
46
+
<methodsynopsis>
47
+
<type>mixed</type><methodname><replaceable>step</replaceable></methodname>
48
+
<methodparam><type>mixed</type><parameter>context</parameter></methodparam>
49
+
<methodparam><type>int</type><parameter>rownumber</parameter></methodparam>
50
+
<methodparam><type>mixed</type><parameter>value</parameter></methodparam>
51
+
<methodparam rep="repeat"><type>mixed</type><parameter>values</parameter></methodparam>
52
+
</methodsynopsis>
53
+
<variablelist>
54
+
<varlistentry>
55
+
<term><parameter>context</parameter></term>
56
+
<listitem>
57
+
<para>
58
+
&null; for the first row; on subsequent rows it will have the value
59
+
that was previously returned from the step function; you should use
60
+
this to maintain the aggregate state.
61
+
</para>
62
+
</listitem>
63
+
</varlistentry>
64
+
<varlistentry>
65
+
<term><parameter>rownumber</parameter></term>
66
+
<listitem>
67
+
<para>
68
+
The current row number.
69
+
</para>
70
+
</listitem>
71
+
</varlistentry>
72
+
<varlistentry>
73
+
<term><parameter>value</parameter></term>
74
+
<listitem>
75
+
<para>
76
+
The first argument passed to the aggregate.
77
+
</para>
78
+
</listitem>
79
+
</varlistentry>
80
+
<varlistentry>
81
+
<term><parameter>values</parameter></term>
82
+
<listitem>
83
+
<para>
84
+
Further arguments passed to the aggregate.
85
+
</para>
86
+
</listitem>
87
+
</varlistentry>
88
+
</variablelist>
89
+
The return value of this function will be used as the
90
+
<parameter>context</parameter> argument in the next call of the step or
91
+
finalize functions.
43
92
</para>
44
93
</listitem>
45
94
</varlistentry>
46
95
<varlistentry>
47
-
<term><parameter>final_callback</parameter></term>
96
+
<term><parameter>finalCallback</parameter></term>
48
97
<listitem>
49
98
<para>
50
-
The name of a PHP function or user-defined function to apply as a
51
-
callback at the end of the aggregate data.
99
+
Callback function to aggregate the "stepped" data from each row.
100
+
Once all the rows have been processed, this function will be called
101
+
and it should then take the data from the aggregation context and
102
+
return the result. This callback function should return a type understood
103
+
by SQLite (i.e. <link linkend="language.types.intro">scalar type</link>).
104
+
</para>
105
+
<para>
106
+
This function need to be defined as:
107
+
<methodsynopsis>
108
+
<type>mixed</type><methodname><replaceable>fini</replaceable></methodname>
109
+
<methodparam><type>mixed</type><parameter>context</parameter></methodparam>
110
+
<methodparam><type>int</type><parameter>rownumber</parameter></methodparam>
111
+
</methodsynopsis>
112
+
<variablelist>
113
+
<varlistentry>
114
+
<term><parameter>context</parameter></term>
115
+
<listitem>
116
+
<para>
117
+
Holds the return value from the very last call to the step function.
118
+
</para>
119
+
</listitem>
120
+
</varlistentry>
121
+
<varlistentry>
122
+
<term><parameter>rownumber</parameter></term>
123
+
<listitem>
124
+
<para>
125
+
Always <literal>0</literal>.
126
+
</para>
127
+
</listitem>
128
+
</varlistentry>
129
+
</variablelist>
130
+
The return value of this function will be used as the return value for
131
+
the aggregate.
52
132
</para>
53
133
</listitem>
54
134
</varlistentry>
55
135
<varlistentry>
56
-
<term><parameter>argument_count</parameter></term>
136
+
<term><parameter>argCount</parameter></term>
57
137
<listitem>
58
138
<para>
59
139
The number of arguments that the SQL aggregate takes. If
...
...
@@ -64,19 +144,108 @@
64
144
</varlistentry>
65
145
</variablelist>
66
146
</para>
67
-
68
147
</refsect1>
69
148
70
149
<refsect1 role="returnvalues">
71
150
&reftitle.returnvalues;
72
151
<para>
73
-
Returns &true; upon successful creation of the aggregate, &false; on
74
-
failure.
152
+
Returns &true; upon successful creation of the aggregate, &return.falseforfailure;.
75
153
</para>
76
154
</refsect1>
77
155
78
-
</refentry>
156
+
<refsect1 role="examples">
157
+
&reftitle.examples;
158
+
<para>
159
+
<example>
160
+
<title>max_length aggregation function example</title>
161
+
<programlisting role="php">
162
+
<![CDATA[
163
+
<?php
164
+
$data = array(
165
+
'one',
166
+
'two',
167
+
'three',
168
+
'four',
169
+
'five',
170
+
'six',
171
+
'seven',
172
+
'eight',
173
+
'nine',
174
+
'ten',
175
+
);
176
+
$db = new SQLite3(':memory:');
177
+
$db->exec("CREATE TABLE strings(a)");
178
+
$insert = $db->prepare('INSERT INTO strings VALUES (?)');
179
+
foreach ($data as $str) {
180
+
$insert->bindValue(1, $str);
181
+
$insert->execute();
182
+
}
183
+
$insert = null;
184
+
185
+
function max_len_step($context, $rownumber, $string)
186
+
{
187
+
if (strlen($string) > $context) {
188
+
$context = strlen($string);
189
+
}
190
+
return $context;
191
+
}
192
+
193
+
function max_len_finalize($context, $rownumber)
194
+
{
195
+
return $context === null ? 0 : $context;
196
+
}
79
197
198
+
$db->createAggregate('max_len', 'max_len_step', 'max_len_finalize');
199
+
200
+
var_dump($db->querySingle('SELECT max_len(a) from strings'));
201
+
?>
202
+
]]>
203
+
</programlisting>
204
+
&example.outputs;
205
+
<screen role="php">
206
+
<![CDATA[
207
+
int(5)
208
+
]]>
209
+
</screen>
210
+
</example>
211
+
</para>
212
+
<para>
213
+
In this example, we are creating an aggregating function that will
214
+
calculate the length of the longest string in one of the columns of the
215
+
table. For each row, the <literal>max_len_step</literal> function is
216
+
called and passed a <literal>$context</literal> parameter. The context
217
+
parameter is just like any other PHP variable and be set to hold an array
218
+
or even an object value. In this example, we are simply using it to hold
219
+
the maximum length we have seen so far; if the
220
+
<literal>$string</literal> has a length longer than the current
221
+
maximum, we update the context to hold this new maximum length.
222
+
</para>
223
+
<para>
224
+
After all of the rows have been processed, SQLite calls the
225
+
<literal>max_len_finalize</literal> function to determine the aggregate
226
+
result. Here, we could perform some kind of calculation based on the
227
+
data found in the <literal>$context</literal>. In our simple example
228
+
though, we have been calculating the result as the query progressed, so we
229
+
simply need to return the context value.
230
+
</para>
231
+
<tip>
232
+
<para>
233
+
It is NOT recommended for you to store a copy of the values in the context
234
+
and then process them at the end, as you would cause SQLite to use a lot of
235
+
memory to process the query - just think of how much memory you would need
236
+
if a million rows were stored in memory, each containing a string 32 bytes
237
+
in length.
238
+
</para>
239
+
</tip>
240
+
<tip>
241
+
<para>
242
+
You can use <methodname>SQLite3::createAggregate</methodname> to override SQLite
243
+
native SQL functions.
244
+
</para>
245
+
</tip>
246
+
</refsect1>
247
+
248
+
</refentry>
80
249
<!-- Keep this comment at the end of the file
81
250
Local variables:
82
251
mode: sgml
83
252