- <refentry id="loop-tuples">
- <refnamediv>
- <refname>LOOP-FOR-AS-TUPLES</refname>
- <refpurpose>Iterate over all the tuples of a
- query via a loop clause</refpurpose>
- <refclass>Loop Clause</refclass>
- </refnamediv>
- <refsect1>
- <title>Compatibility</title>
- <caution><para><function>loop-for-as-tuples</function> only works with &cmucl;.</para></caution>
- </refsect1>
- <refsect1>
- <title>Syntax</title>
- <synopsis><replaceable>var</replaceable> [<replaceable>type-spec</replaceable>] being {each | the} {record | records | tuple | tuples} {in | of} <replaceable>query</replaceable> [from <replaceable>database</replaceable>]</synopsis>
- </refsect1>
- <refsect1>
- <title>Arguments and Values</title>
- <variablelist>
- <varlistentry>
- <term><parameter>var</parameter></term>
- <listitem>
- <para>A <literal>d-var-spec</literal>, as defined in the
- grammar for <function>loop</function>-clauses in the
- ANSI Standard for Common Lisp. This allows for the
- usual loop-style destructuring.</para>
- </listitem>
- </varlistentry>
- <varlistentry>
- <term><parameter>type-spec</parameter></term>
- <listitem>
- <para>An optional <literal>type-spec</literal> either
- simple or destructured, as defined in the grammar for
- <function>loop</function>-clauses in the ANSI Standard
- for Common Lisp.</para>
- </listitem>
- </varlistentry>
- <varlistentry>
- <term><parameter>query</parameter></term>
- <listitem>
- <para>An <glossterm linkend="gloss-sql-expression">sql
- expression</glossterm> that represents an SQL
- query which is expected to return a (possibly empty)
- result set, where each tuple has as many attributes as
- <parameter>function</parameter> takes arguments.</para>
- </listitem>
- </varlistentry>
- <varlistentry>
- <term><parameter>database</parameter></term>
- <listitem>
- <para>An optional
- <glossterm linkend="gloss-database-object">database
- object</glossterm>. This will default to the value
- of <symbol>*default-database*</symbol>.</para>
- </listitem>
- </varlistentry>
- </variablelist>
- </refsect1>
- <refsect1>
- <title>Description</title>
- <para>This clause is an iteration driver for
- <function>loop</function>, that binds the given variable
- (possibly destructured) to the consecutive tuples (which are
- represented as lists of attribute values) in the result set
- returned by executing the SQL <parameter>query</parameter>
- expression on the <parameter>database</parameter>
- specified.</para>
- </refsect1>
- <refsect1>
- <title>Examples</title>
- <screen>
-(defvar *my-db* (connect '("dent" "newesim" "dent" "dent"))
- "My database"
-=> *MY-DB*
-(loop with time-graph = (make-hash-table :test #'equal)
- with event-graph = (make-hash-table :test #'equal)
- for (time event) being the tuples of "select time,event from log"
- from *my-db*
- do
- (incf (gethash time time-graph 0))
- (incf (gethash event event-graph 0))
- finally
- (flet ((show-graph (k v) (format t "~40A => ~5D~%" k v)))
- (format t "~&Time-Graph:~%===========~%")
- (maphash #'show-graph time-graph)
- (format t "~&~%Event-Graph:~%============~%")
- (maphash #'show-graph event-graph))
- (return (values time-graph event-graph)))
->> Time-Graph:
->> ===========
->> D => 53000
->> X => 3
->> test-me => 3000
->>
->> Event-Graph:
->> ============
->> CLOS Benchmark entry. => 9000
->> Demo Text... => 3
->> doit-text => 3000
->> C Benchmark entry. => 12000
->> CLOS Benchmark entry => 32000
-=> #<EQUAL hash table, 3 entries {48350A1D}>
-=> #<EQUAL hash table, 5 entries {48350FCD}>
- </screen>
- </refsect1>
- <refsect1>
- <title>Side Effects</title>
- <para>Whatever effects the execution of the SQL query has
- on the underlying database, if any.</para>
- </refsect1>
- <refsect1>
- <title>Affected by</title>
- <para>None.</para>
- </refsect1>
- <refsect1>
- <title>Exceptional Situations</title>
- <para>If the execution of the SQL query leads to any
- errors, an error of type
- <errortype>sql-database-error</errortype> is signalled.</para>
- <para>Otherwise, any of the exceptional situations of
- <function>loop</function> applies.</para>
- </refsect1>
- <refsect1>
- <title>See Also</title>
- <para>
- <simplelist>
- <member><link linkend="query"><function>query</function></link></member>
- <member><link linkend="map-query"><function>map-query</function></link></member>
- <member><link linkend="do-query"><function>do-query</function></link></member>
- </simplelist>
- </para>
- </refsect1>
- <refsect1>
- <title>Notes</title>
- <para>None.</para>
- </refsect1>
- </refentry>
-
- <refentry id="map-query">
- <refnamediv>
- <refname>MAP-QUERY</refname>
- <refpurpose>Map a function over all the tuples from a
- query</refpurpose>
- <refclass>Function</refclass>
- </refnamediv>
- <refsect1>
- <title>Syntax</title>
- <synopsis><function>map-query</function> <replaceable>output-type-spec</replaceable> <replaceable>function</replaceable> <replaceable>query-expression</replaceable> &key <replaceable>database</replaceable> <replaceable>result-types</replaceable> => <returnvalue>result</returnvalue></synopsis>
- </refsect1>
- <refsect1>
- <title>Arguments and Values</title>
- <variablelist>
- <varlistentry>
- <term><parameter>output-type-spec</parameter></term>
- <listitem>
- <para>A sequence type specifier or <symbol>nil</symbol>.</para>
- </listitem>
- </varlistentry>
- <varlistentry>
- <term><parameter>function</parameter></term>
- <listitem>
- <para>A function designator.
- <parameter>function</parameter> takes a single argument which
- is the atom value for a query single with a single column
- or is a list of values for a multi-column query.</para>
- </listitem>
- </varlistentry>
- <varlistentry>
- <term><parameter>query-expression</parameter></term>
- <listitem>
- <para>An <glossterm linkend="gloss-sql-expression">sql
- expression</glossterm> that represents an SQL
- query which is expected to return a (possibly empty)
- result set.</para>
- </listitem>
- </varlistentry>
- <varlistentry>
- <term><parameter>database</parameter></term>
- <listitem>
- <para>A
- <glossterm linkend="gloss-database-object">database
- object</glossterm>. This will default to the value
- of <symbol>*default-database*</symbol>.</para>
- </listitem>
- </varlistentry>
- <varlistentry>
- <term><parameter>result-types</parameter></term>
- <listitem>
- <para>
- A <glossterm linkend="gloss-field-types">field type specifier</glossterm>.
- The default is &nil;. See <link
- linkend="query"><function>query</function></link>
- for the semantics of this argument.
- </para>
- </listitem>
- </varlistentry>
- <varlistentry>
- <term><returnvalue>result</returnvalue></term>
- <listitem>
- <para>If <parameter>output-type-spec</parameter> is a
- type specifier other than <symbol>nil</symbol>, then a
- sequence of the type it denotes. Otherwise
- <symbol>nil</symbol> is returned.</para>
- </listitem>
- </varlistentry>
- </variablelist>
- </refsect1>
- <refsect1>
- <title>Description</title>
- <para>Applies <parameter>function</parameter> to the
- successive tuples in the result set returned
- by executing the SQL
- <parameter>query-expression</parameter>. If the
- <parameter>output-type-spec</parameter> is
- <symbol>nil</symbol>, then the result of each application
- of <parameter>function</parameter> is discarded, and
- <function>map-query</function> returns
- <symbol>nil</symbol>. Otherwise the result of each
- successive application of <parameter>function</parameter> is
- collected in a sequence of type
- <parameter>output-type-spec</parameter>, where the jths
- element is the result of applying
- <parameter>function</parameter> to the attributes of the
- jths tuple in the result set. The collected sequence is the
- result of the call to <function>map-query</function>.
- </para>
- <para>If the <parameter>output-type-spec</parameter> is a
- subtype of <type>list</type>, the result will be a
- <type>list</type>.</para>
- <para>If the <parameter>result-type</parameter> is a subtype
- of <type>vector</type>, then if the implementation can
- determine the element type specified for the
- <parameter>result-type</parameter>, the element type of the
- resulting array is the result of
- <emphasis>upgrading</emphasis> that element type; or, if the
- implementation can determine that the element type is
- unspecified (or <symbol>*</symbol>), the element type of the
- resulting array is <type>t</type>; otherwise, an error is
- signaled.</para>
- <para>If RESULT-TYPES is nil all results are returned as
- strings whereas the default value of :auto means that the lisp
- types are automatically computed for each field.</para>
- </refsect1>
- <refsect1>
- <title>Examples</title>
- <screen>
-(map-query 'list #'(lambda (tuple)
- (multiple-value-bind (salary name) tuple
- (declare (ignorable name))
- (read-from-string salary)))
- "select salary,name from simple where salary > 8000")
-=> (10000.0 8000.5)