File: Mnesia_chap6.html

package info (click to toggle)
erlang-doc-html 1%3A11.b.2-1
links: PTS
area: main
in suites: etch, etch-m68k
size: 23,284 kB
ctags: 10,724
sloc: erlang: 505; ansic: 323; makefile: 62; perl: 61; sh: 45
file content (1373 lines) | stat: -rw-r--r-- 33,034 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<!-- This document was generated using DocBuilder 3.3.3 -->
<HTML>
<HEAD>
  <TITLE>Database Queries
</TITLE>
  <SCRIPT type="text/javascript" src="../../../../doc/erlresolvelinks.js">
</SCRIPT>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#FF00FF"
      ALINK="#FF0000">
<CENTER>
<A HREF="http://www.erlang.se"><IMG BORDER=0 ALT="[Ericsson AB]" SRC="min_head.gif"></A>
</CENTER>
<A NAME="2"><!-- Empty --></A>
<H2>2 Database Queries
</H2>

<P>This chapter describes Mnemosyne, the Mnesia database query
language, and the syntax, semantics, and rules which apply to
Mnesia queries. The following sections are included: 


<P>
<UL>

<LI>
Mnemosyne - the Mnesia query language



</LI>


<LI>
Evaluating queries



</LI>


<LI>
Mnesia query examples



</LI>


<LI>
Matching



</LI>


<LI>
 Generated functions
</LI>


</UL>

<P>The following notational conventions are used in this chapter:




<P>
<UL>

<LI>
Reserved words and symbols are written like this: <CODE>table</CODE>.




</LI>


<LI>
Syntactic categories are written like this: <CODE>&#60;pattern&#62;</CODE>.
</LI>


</UL>
<A NAME="db_queries"><!-- Empty --></A><A NAME="2.1"><!-- Empty --></A>
<H3>2.1 Mnemosyne - the Mnesia Query Language</H3>

<P><STRONG>Mnemosyne</STRONG> is the query language and the optimizing query
compiler for the Mnesia Database Management System.
<A NAME="2.1.1"><!-- Empty --></A>
<H4>2.1.1 General Information about Queries</H4>

<P>Database <STRONG>queries</STRONG> are used when more complex
operations than just a simple key-value lookup are required on 
        a database. A query can find all records in a table that fulfills
a given property. For example, think of a table storing the
status of subscriber lines in a telephone exchange. A query in
such a table can take the format: &#34;Which subscriber
<STRONG>lines</STRONG> are 'blocked'?&#34;. 



<P>A query can also find records on the basis of their relationship to
other records in the same table, or in other tables. If the
table, which stores subscriber lines, is accompanied by a table,
which pairs subscriber numbers with a subscriber line
identification, we can modify our previous query and ask:
&#34;Which subscriber <STRONG>numbers</STRONG> are 'blocked' ?&#34;. This can
be answered by constructing a query, which finds the blocked
subscriber line identifications in the subscriber line table,
and then finds the associated subscriber number in the table,
which pairs subscriber number and subscriber line
identifications. 



<P>However, the proposed solution may not be the most efficient 
        solution, because it depends on what
the tables look like in runtime. In other words, how many records the
table contains, and the number of different values stored. 

        
<P>In a situation where there are only a couple of subscriber
numbers but a million blocked lines, it would be far more
efficient to first find the subscribers and then check if
their line is blocked. The query evaluation order depends on
how large the tables are compared to each other. The
evaluation order also depends on key and other value 
distribution, and if there are any indices defined (refer to
<STRONG>Mnesia Chapter 5: Indexing</STRONG> for more information). 



<P>The query compiler resolves the evaluation order. We need only
express <STRONG>what</STRONG> we want to do, and the query compiler
and query evaluator will determine the best evaluation order. 
        Therefore, we can express the query in the most readable
form. 
<A NAME="2.1.2"><!-- Empty --></A>
<H4>2.1.2 Queries in Mnesia</H4>

<P>Queries in Mnemosyne use first order predicate logic (similar
        to Prolog),
but in an syntax suitable for Erlang. The &#34;query list
comprehension&#34; used in Mnemosyne is taken from the functional
languages community. The advantage over embedded SQL, is that
        the constructs integrate smoothly with the Erlang language. 



        <A NAME="sub_query"><!-- Empty --></A>

<P>To illustrate the Mnemosyne query language, we will show the Erlang
code for the subscriber line and subscriber number tables
discussed above. We define two tables <CODE>subscriber</CODE> and
<CODE>line</CODE>. Their corresponding record declarations in the
file <CODE>subscriber.hrl</CODE> are: 

<PRE>
-record(subscriber, {snb, 
                     cost_limit,
                     li}).

</PRE>

<PRE>
-record(line, {li,
               state}).

</PRE>

<P>The query &#34;which subscriber numbers are blocked?&#34; can also be
        expressed as &#34;which subscribers have lines which are in state 'blocked'&#34;.
        This query can be coded as follows:

<PRE>
query
    [ S.snb ||                  % collect the subscriber number
        S &#60;- table(subscriber), % where S is taken from the subscriber table
        L &#60;- table(line),       % and L is taken from the line table
        L.state = blocked,      % and the state of that line is blocked
        L.li = S.li             % and L and S uses the same li
    ]
end

</PRE>

<P>In the example above, the aim is to get an answer from a logical
relation. Consider also the following example: 

<PRE>
query [E.name || E &#60;- table(employee),
                 E.sex = female]
end
      
</PRE>

<P>This means &#34;the Erlang list of all female employees&#34;. A formulation
closer to the list comprehension is: &#34;the Erlang list of all
names of E such that E is in the table 
<CODE>employee</CODE> and E's sex is female&#34;.
<P>Some words have a direct correspondence to the elements in the list
comprehension notation: 

<P>
<CENTER>
<TABLE CELLSPACING=0 CELLPADDING=2 BORDER=1>
  <CAPTION ALIGN=BOTTOM><EM>Natural Language Translation</EM></CAPTION>
  <TR>
    <TD ALIGN="LEFT" VALIGN="MIDDLE">
the Erlang list of all
    </TD>
    <TD ALIGN="LEFT" VALIGN="MIDDLE">
<CODE>[ ]</CODE>
    </TD>

  </TR>
  <TR>
    <TD ALIGN="LEFT" VALIGN="MIDDLE">
such that
    </TD>
    <TD ALIGN="LEFT" VALIGN="MIDDLE">
<CODE>||</CODE>
    </TD>

  </TR>
  <TR>
    <TD ALIGN="LEFT" VALIGN="MIDDLE">
is in
    </TD>
    <TD ALIGN="LEFT" VALIGN="MIDDLE">
<CODE>&#60;-</CODE>
    </TD>

  </TR>
  <TR>
    <TD ALIGN="LEFT" VALIGN="MIDDLE">
and
    </TD>
    <TD ALIGN="LEFT" VALIGN="MIDDLE">
<CODE>,</CODE>
    </TD>

  </TR>

</TABLE>
</CENTER>

<P>Another query component is <STRONG>rules</STRONG>, which can be compared to
<STRONG>views</STRONG> in Relational Databases, where the purpose is
to define a &#34;virtual table&#34;. This &#34;table&#34; looks like an
ordinary table to the user, which means that queries can be
formulated on stored data (as well as views). In the subscriber
line example, a rule can give the subscriber number and the
corresponding line status from both tables, and there is no
need to create a third table. 


<P>The rule is a definition of how
to calculate the records on demand from a query. In Erlang
modules, rules are written with the same syntax as the bodies
in the query list comprehensions. They are exported and can be
used by other modules. 

<P>Our <CODE>subscriber</CODE> example formulated as a rule would look as
follows: 

        <A NAME="mnemosyne_rule_example"><!-- Empty --></A>


<PRE>
blocked_subscribers(S, subscriber) :-
        S &#60;- table(subscriber),
        L &#60;- table(line),
        L.state = blocked,
        L.li = S.li.

</PRE>

<P>This rule can be used in a query in the same manner as a table
but with the keyword <CODE>rule</CODE> substituted for <CODE>table</CODE>.



<PRE>
query [ S.snb || S &#60;- rule(blocked_subscribers) ] end

</PRE>
<A NAME="2.1.3"><!-- Empty --></A>
<H4>2.1.3 Query Syntax</H4>

<P> Database queries can be included in an Erlang program, but there
must be a directive in the Erlang file which informs the compiler
about its behavior towards queries. This directive is: 


<PRE>
        -include_lib(&#34;mnemosyne/include/mnemosyne.hrl&#34;).
      
</PRE>

<P>The high-level syntax of the query list comprehension is:



<PRE>
query [ &#60;pattern&#62; || &#60;body&#62; ] end
</PRE>
<A NAME="lc_body_syntax"><!-- Empty --></A>
<P>The <CODE>&#60;body&#62;</CODE> is a comma-separated sequence of:

<P>
<OL>

<LI>
<CODE>&#60;logical-variable&#62; &#60;- table( &#60;table-name&#62; [ , &#60;table-type&#62; ] )</CODE>



</LI>


<LI>
<CODE>&#60;logical-variable&#62; &#60;- rule( &#60;rule-name&#62; )</CODE>

</LI>


<LI>
<CODE>&#60;logical-variable&#62; &#60;- rule( &#60;rule-name&#62; )</CODE>




</LI>


<LI>
<CODE>&#60;logical-variable&#62; &#60;- rule( &#60;module&#62; : &#60;rule-name&#62; ])</CODE>




</LI>


<LI>
<CODE>&#60;logical-variable&#62; &#60;- &#60;erlang-list-expression&#62;</CODE>



         
        
</LI>


<LI>
<CODE>&#60;expression&#62; &#60;relop&#62; &#60;expression&#62;</CODE>
        
</LI>


<LI>
<CODE>&#60;erlang-test-expression&#62;</CODE>

</LI>


</OL>

<P>The <CODE>&#60;relop&#62;</CODE> operators are:



<P>
<UL>

<LI>
<CODE>=</CODE> for unification



</LI>


<LI>
<CODE>/=</CODE> for not unification



</LI>


<LI>
<CODE>&#60;</CODE> for less than



        
</LI>


<LI>
<CODE>&#62;</CODE> for greater than



        
</LI>


<LI>
<CODE>=&#60;</CODE> for equal to or less than
         


        
</LI>


<LI>
<CODE>&#62;=</CODE> for equal to or greater than.
</LI>


</UL>

<P>A <CODE>&#60;logical-variable&#62;</CODE> is written exactly as an Erlang
variable. The <CODE>&#60;table-name&#62;</CODE>, <CODE>&#60;table-type&#62;</CODE>, 
        <CODE>&#60;rule-name&#62;</CODE> and
<CODE>&#60;module&#62;</CODE> are atoms. The <CODE>&#60;table-name&#62;</CODE> and 
        <CODE>&#60;table-type&#62;</CODE> can also be
an Erlang variable. The logical variables are local to a list
comprehension and shadows any Erlang variable with the same
name. 




<P>The <CODE>&#60;pattern&#62;</CODE> is an Erlang term without function calls. It
may contain (bound) Erlang variables and it usually has one or
more <CODE>&#60;logical-variable&#62;</CODE>, since these are used to get
data out from the query body and into the produced list. 

<P>An <CODE>&#60;expression&#62;</CODE> is any Erlang expression which may include
function calls and <CODE>&#60;logical-variable&#62;</CODE>. The variant
<CODE>&#60;erlang-list-expression&#62;</CODE> is an 
        <CODE>&#60;expression&#62;</CODE> which must produce a list where all elements are
records of the same type. 

<P> The <CODE>&#60;erlang-test-expression&#62;</CODE> is an <CODE>&#60;expression&#62;</CODE>
which has the values <CODE>true</CODE> or <CODE>false</CODE>. 

<P>Erlang variables are allowed in all variations of <CODE>&#60;expression&#62;</CODE>
and in <CODE>&#60;pattern&#62;</CODE>. They must be bound in the query list
comprehension. 
<A NAME="2.1.4"><!-- Empty --></A>
<H4>2.1.4 Query Semantics</H4>

<P>The constructs used in the Mnemosyne query language have the following meanings:


<P>
<UL>

<LI>
<STRONG>Comma</STRONG>. The comma, used to separate different body
elements, is equivalent to &#34;and&#34;. Thus, the body
can be viewed as a collection of tests and statements which
should be true for each solution which is produced when
evaluating the query list comprehension. Refer to <A HREF="#sub_query"> subscriber query</A> as an example of
this. 


        
</LI>


<LI>
<STRONG><CODE>&#60;logical-variable&#62; &#60;- ...</CODE></STRONG>. This expression means
that the variable is taken from the values in the expression
to the right of the arrow. For example, <CODE>E &#60;- [#e{a=1},
#e{a=2}]</CODE> says that <CODE>E</CODE> takes the values
<CODE>#e{a=1}</CODE>, or <CODE>#e{a=2}</CODE> 


</LI>


<LI>
<STRONG><CODE>&#60;-</CODE></STRONG>. These constructs usually generate
values. However, if the logical variable is bound it tests
that value. If a test fails it means that the query tries
another alternative. For example: 




<PRE>
query [ X.a || X &#60;- [#e{a=1}, #e{a=2}],
               X &#60;- [#e{a=3}],
               .... ]
end
</PRE>


The body means that the field 'a' of <CODE>X</CODE> should be 3 and at
the same time either 1 or 2. So the list of solutions will
always be empty. <BR>

</LI>


</UL>

<P>The test <CODE>&#60;expression&#62; &#60;relop&#62; &#60;expression&#62;</CODE> and the
<CODE>true</CODE>-or-<CODE>false</CODE> returning test 
<CODE>&#60;erlang-test-expression&#62;</CODE> simply filters out the solutions. The
purpose of the latter test is to provide user defined data tests. 
<P>We will next consider the logical variables <STRONG>associated
records</STRONG> in an expression like <CODE>x &#60;- table(a)</CODE>. We
have already established the following rules and assumptions: 
        
        

<P>
<OL>

<LI>
the values stored in tables are records


</LI>


<LI>
all records in a table must be of the same type


</LI>


<LI>
by default, the record definition has the same name as the table itself


</LI>


<LI>
The <CODE>&#60;logical-variable&#62;</CODE> must have the same record association as the records produced by the right side of the <CODE>&#60;-</CODE> constructs.
</LI>


</OL>

<P>In the example <CODE>X &#60;- table(a)</CODE>, the associated record of
<STRONG>x</STRONG> is <STRONG>a</STRONG> because table <STRONG>a</STRONG> stores
records with the name <STRONG>a</STRONG>. Since release 3.4 of Mnesia it has
        been possible to separate record name and its table type. 
        If the type of the table is different from its name, this can
        be specified in Mnemosyne using <CODE>X &#60;- table(Name, Type)</CODE>
        where Name is the Name of the table and Type is the record name.
        



<P>Similar to tables, rules produce or test records. The return type
        for a rule is by default the name of the rule. Rules can be declared
        to return other types.
        This makes it possible to construct a rule for some
        special cases with a name like <CODE>blocked_subscriber</CODE> which
        still produces or tests subscriber records. 



<P>In Erlang we must always tell the compiler which record definition
it should use by putting the record name after a hash mark. In
general, this is not needed in Mnemosyne since, in most cases, 
        the query
compiler can deduce the associated record. That
is the reason <CODE>S.li</CODE> is acceptable instead of the
full <CODE>S#subscriber.li</CODE>. It will not cause an error 
        if the longer version
was written, but if we do write the record name it must be the
same record name as the one the query compiler deduces. Sometimes
        the compiler is unable to find the
associated record. When this happens, an error message is
issued. 
        It is also preferred to write out the type of the associated record
        for performance reasons. If the associated record is part of a complex
        constraint, the constraint may be compiled to a function if the
        type of the associated record is known (explicitly or
        deducable) at Erlang compile time.


<P>
<TABLE CELLPADDING=4>
  <TR>
    <TD VALIGN=TOP><IMG ALT="Note!" SRC="note.gif"></TD>
    <TD>

<P> A function used in a query list comprehension must <STRONG>never</STRONG>
directly or indirectly: 
<P>
<OL>

<LI>
have side effects;





</LI>


<LI>
access the database neither by a query nor by Mnesia functions;





</LI>


<LI>
spawn processes, or;





</LI>


<LI>
send or receive messages.
</LI>


</OL>
    </TD>
  </TR>
</TABLE>
<A NAME="2.1.5"><!-- Empty --></A>
<H4>2.1.5 Rules</H4>

<P>A rule is composed of <STRONG>clauses</STRONG> and each clause has the structure:



<PRE>
&#60;head&#62; :- &#60;body&#62;
</PRE>

<P>
<UL>

<LI>
The clauses are separated by semicolon, and the rule is terminated by a dot.






</LI>


<LI>
The <CODE>&#60;head&#62;</CODE> looks like an Erlang function with one or two arguments,
where the first argument is a variable and the second, optional, argument
an atom. If there is a second argument, it must be present in all clauses and
have the same value.






</LI>


<LI>
 The <CODE>&#60;body&#62;</CODE> has the same syntax as the <CODE>&#60;body&#62;</CODE>. <A HREF="#lc_body_syntax">in query list comprehensions</A>






</LI>


<LI>
The argument variable of a rule clause has an associated record.






</LI>


<LI>
The default associated record is the name of the rule. This can be
changed by declaring the associated record type in the head of the clause:


<PRE>
&#60;rule-name&#62; (&#60;return-var&#62;, &#60;record-name&#62;)
</PRE>


The syntax used in previous mnemosyne versions, by declaring the the 
associated recordtype with an <CODE>argtype</CODE> declaration still works but 
is depreciated.


</LI>


</UL>

<P>
<TABLE CELLPADDING=4>
  <TR>
    <TD VALIGN=TOP><IMG ALT="Note!" SRC="note.gif"></TD>
    <TD>

<P> The <CODE>&#60;logical-variable&#62;</CODE> mentioned in the <CODE>&#60;head&#62;</CODE> must also occur in the <CODE>&#60;body&#62;</CODE>.    </TD>
  </TR>
</TABLE>

<P>Review the <A HREF="#mnemosyne_rule_example"> rule example</A>. 

<PRE>
blocked_subscribers(S, subscriber) :-
        S &#60;- table(subscriber),
        L &#60;- table(line),
        L.state = blocked,
        L.li = S.li.

</PRE>

<P>It produces a list of <CODE>subscriber</CODE> records.
        Rules with a single argument return records of the same
        type as the name of the rule. For example, the following
        rule produces records of type <CODE>blocked</CODE>
        

<PRE>
-record (blocked, {snb, li}).
blocked (X) :-
    S &#60;- table (subscriber),
    L &#60;- table(line),
    L.state = blocked,
    L.li = S.li,
    X = #blocked{snb=S#subscriber.snb, li=S#subscriber.li}.



</PRE>
<A NAME="2.2"><!-- Empty --></A>
<H3>2.2 Evaluating Queries</H3>

<P>The previous sections described how to define queries. This
section describes how to evaluate queries.


<P>The principle is simple: query list comprehensions, compile and
optimize the query and return a <STRONG>handle</STRONG>. This handle is 
then passed on for execution:


<PRE>
    Handle = 
        query 
           [ S.snb || S &#60;- table(subscriber),
                      S.li = none]
        end,

    AllAnswers = 
        mnesia:transaction(
             fun() -&#62; 
                      mnemosyne:eval(Handle)
             end)

</PRE>

<P>There are three ways of evaluating queries. The <CODE>mnemosyne:eval/1</CODE>
is the simplest of the three. It takes a handle and returns all
solutions. Sometimes we only need to view a few solutions,
examine them and possibly get more. Think of an airline routing
database: you do not want to know all possible connections
between two cities, but usually enough information is given 
after observing one or two. 


<P>Use the <STRONG>cursor</STRONG> with a query evaluation to produce a few
solutions only. With a handle we create a cursor by calling
<CODE>mnemosyne:cursor/1</CODE>. With the cursor we can repeatedly
call <CODE>mnemosyne:next_answers</CODE> to get more solutions. When
an empty list is returned there are no more possible solutions. 
Delete the cursor with <CODE>mnemosyne:delete_cursor/1</CODE>. 


<PRE>
    Handle = 
        query 
           [ S.snb || S &#60;- table(subscriber),
                      S.li = none]
        end,

    AFewAnswers = 
        mnesia:transaction(
             fun() -&#62; 
                     Cursor = mnemosyne:cursor(Handle),
                     % now get at least two but not 
                     % more than five solutions:
                     L = mnemosyne:next_answers(Cursor,2,5),
                     mnemosyne:delete_cursor(Cursor),
                     L
             end)

</PRE>

<P>A query evaluation can be time consuming, but can be broken up by using the cursor with <CODE>setup_query/1</CODE> and <CODE>init_query/1</CODE>:


<PRE>
    Handle = 
        query 
           [ S.snb || S &#60;- table(subscriber),
                      S.li = none]
        end,

    QuerySetup = mnemosyne:setup_query(Handle),

    AFewAnswers = 
        mnesia:transaction(
             fun() -&#62;
                    Cursor = mnemosyne:init_query(QuerySetup),
                    mnemosyne:next_answers(Cursor, 5, 5)
             end),
      
      % Here we may call more init_query-next_answers constructions
      % with the same Handle. Note that the query is evaluated from
      % &#34;scratch&#34; because of the call to mnemosyne:init_query/1.

      mnemosyne:delete_query(QuerySetup)

</PRE>

<P>Because of table updates, a query which is compiled and optimized
may be incorrect when the handle returns. This can be rectified with
the function <CODE>mnemosyne:reoptimize/1</CODE> which takes a handle,
re-optimizes the query and returns a new handle.

<A NAME="2.3"><!-- Empty --></A>
<H3>2.3 Query Examples</H3>

<P>This section describes an example which illustrates the use of Mnemosyne. 
The example given is of a simplified local
exchange, with AXE-10 exchange as a model. The purpose of this
section is to show different constructs in a telecom
environment. It should not be taken as a proposed data model for
a modern telecom system. 


<P>Our telephone example includes the following components,
relationships, and events: 

<P>
<UL>

<LI>
The exchange has a number of <STRONG>subscribers</STRONG>.


</LI>


<LI>
Each subscriber has a <STRONG>subscriber number</STRONG>, which is abbreviated 
<STRONG>snb</STRONG>.


</LI>


<LI>
Each physical line enters the exchange through a <STRONG>line interface
card</STRONG>. Lines are abbreviated <STRONG>li</STRONG>.


</LI>


<LI>
The <STRONG>li</STRONG> has an associated <STRONG>status</STRONG> which indicates if the line is blocked, or available.


</LI>


<LI>
One single table stores the accumulated cost for each subscriber.
</LI>


</UL>
<A NAME="2.3.1"><!-- Empty --></A>
<H4>2.3.1 Program Definitions</H4>

<P>We identify three tables:





<P>
<UL>

<LI>
<CODE>subscriber</CODE> with subscriber numbers <CODE>snb</CODE>, line interface number <CODE>li</CODE>, and a maximum cost <CODE>cost_limit</CODE> which must not be exceeded.





</LI>


<LI>
<CODE>line</CODE> with line interface number <CODE>li</CODE>, and its <CODE>state</CODE>.
</LI>


<LI>
<CODE>account</CODE>, a table which stores the cost of calls. It has an <CODE>snb</CODE> field, and the accumulated cost in <CODE>cost</CODE>.
</LI>


</UL>

<P>The corresponding record definitions are stored in a file named <CODE>subscriber.hrl</CODE>, which has the following record definitions:

<PRE>
-record(subscriber, {snb, 
                     cost_limit,
                     li}).

</PRE>

<PRE>
-record(line, {li,
               state}).

</PRE>

<PRE>
-record(account, {snb,
                  cost}).

</PRE>

<P>The program file is titled <CODE>subscriber.erl</CODE>. It declares the module name <CODE>subscriber</CODE>, calls the record definition in <CODE>subscriber.hrl</CODE>, and Mnesia query support <CODE>mnemosyne.hrl</CODE>. 

<PRE>
-module(subscriber).
-compile(export_all).

-include(&#34;subscriber.hrl&#34;).
-include_lib(&#34;mnemosyne/include/mnemosyne.hrl&#34;).

</PRE>

<P>We then create the required tables and load data by entering table definitions into a file named <CODE>subscriber.tables</CODE>, which has the following content:
<PRE>
{tables,
 [{subscriber, [{attributes, [snb,cost_limit,li]}]},
  {line, [{attributes, [li, state]}]},
  {account, [{attributes, [snb, cost]}]}
  ]
}.

%% Subscribers
{subscriber, 1230,   0,   none}.
{subscriber, 1231,   0,   none}.
{subscriber, 1232,   0,   none}.
{subscriber, 1233,   0,   none}.
{subscriber, 1234, 100, {li,1}}.
{subscriber, 1235, 200, {li,3}}.
{subscriber, 1236, 150, {li,2}}.
{subscriber, 1237,   0,   none}.
{subscriber, 1238,   0,   none}.
{subscriber, 1239,   0,   none}.

%% Lines
{line, {li,0}, blocked}.
{line, {li,1},  normal}.
{line, {li,2},  normal}.
{line, {li,3}, blocked}.
{line, {li,4}, blocked}.
{line, {li,5}, blocked}.
{line, {li,6}, blocked}.
{line, {li,7}, blocked}.



%% Accounts
{account, 1234, 0}.
{account, 1235, 0}.
{account, 1236, 0}.
{account, 1237, 0}.


</PRE>
<A NAME="2.3.2"><!-- Empty --></A>
<H4>2.3.2 Program Output</H4>

<P>In our program, this file is called with the statement:





<PRE>
mnesia:load_textfile(&#34;subscriber.tables&#34;)
</PRE>

<P>To retrieve a list of all free subscriber numbers we call the following 
        function in a transaction:
<PRE>
free_subscriber_numbers() -&#62;
    mnemosyne:eval(
      query [ S.snb || S &#60;- table(subscriber),
                       S.li = none]
      end
     ).

</PRE>

<P>The rule <CODE>too_high_cost/0</CODE> locates and returns all subscribers with an 
        accumulated cost that exceeds their limit:


<PRE>
limit_exceeded(S, subscriber) :-
    S &#60;- table(subscriber),
    A &#60;- table(account),
    A.snb = S.snb,
    A.cost &#62; S.cost_limit.

</PRE>

<P>We could find all subscriber numbers of subscribers who have exceeded their cost limit as follows:



<PRE>
        Q = query
       [ S.snb || S &#60;- rule(limit_exceeded) ]
        end
      
</PRE>
<A NAME="2.4"><!-- Empty --></A>
<H3>2.4  Matching </H3>

<P>Mnesia provides the programmer with a method of matching objects 
against a pattern. This is the Mnesia matching function:



<P><CODE>mnesia:match_object(Pattern) -&#62;transaction abort | ObjList</CODE>. 



<P>This function matches <CODE>Pattern</CODE> for objects. A <CODE>Pattern</CODE> 
is a tuple with the name (identity) of the table as the first element. 
The table collates all data retrieved. 



<P>In comparison to a list comprehension query, <CODE>mnesia:match_object</CODE> is a low level
function. The following two functions both return the same
objects; however, the second example uses matching.


<PRE>
f1() -&#62;
    Q = query 
         [E || E &#60;- table(employee), 
          E.sex = female]
    end, 
    F = fun() -&#62; mnemosyne:eval(Q) end,
    mnesia:transaction(F).

</PRE>

<P>and


<PRE>
f2() -&#62;
    WildPat = mnesia:table_info(employee, wild_pattern),
    Pat = WildPat#employee{sex = female},
    F = fun() -&#62; mnesia:match_object(Pat) end,
    mnesia:transaction(F).

</PRE>

<P>The pattern supplied to the <CODE>mnesia:match_object/1</CODE>
function must be a valid record, and the first element
of the provided tuple must be a valid table name. The
special element <CODE>'_'</CODE> matches all the records.


<P>There are advantages in using the Mnemosyne query syntax
instead of the <CODE>mnesia:match_object/1</CODE> function:


<P>
<UL>

<LI>
The pattern is computed in compile time by the
         Mnemosyne compiler instead of doing it in run time in the
         <CODE>f2/0</CODE> function.

<BR>

</LI>


<LI>
Mnemosyne provides more sophisticated evaluation
         optimizations based on indices and on statistics from and about the
         table. 
         <BR>
Whereas, the optimizations that <CODE>mnesia:match_object/1</CODE>
         function provides are limited in both scope and number. 
         The <CODE>mnesia:match_object</CODE> function is also performed
         during run time, which in turn reduces performance.

         <BR>

</LI>


<LI>
The Mnemosyne query syntax is quite compact and makes
         it easier to express complex queries.
<BR>

</LI>


</UL>

<P>It is also possible to use the match function if we want to
         check the equality of different attributes. Assume we have
         the following record definition:

        
<PRE>
          -record(foo, {a, b, c}).
        
</PRE>

<P>The pattern <CODE>{foo, '$1', '$1', '_'}</CODE> then extracts all objects of type 
         <CODE>foo</CODE> where the first two attributes have the same value.


        
<P>If the key attribute is bound in a pattern, the match operation is very efficient.
         The pattern <CODE>{foo, 123, '_', elvis}</CODE> can be used to extract all
         objects with key <CODE>123</CODE>, and the last attribute set to the atom
         <CODE>elvis</CODE>. This is the same as extracting all the <CODE>elvis</CODE> 
         objects from the result of <CODE>mnesia:read({foo, 123})</CODE>, but 
         more efficient.


        
<P>If the key attribute in a pattern is given as <CODE>'_'</CODE>, or <CODE>'$1'</CODE>, the
         whole <CODE>foo</CODE> table must be searched for objects that match. If the table
         is large, this may be a time consuming operation. This can be remedied with
         indices (refer to <STRONG>Mnesia Chapter 5: Indexing</STRONG> for more information).


        
<P>This chapter closes with an example of information extraction from a Company database:


        
<P>
<UL>

<LI>
all employees who have a salary higher than <CODE>X</CODE>.




         
</LI>


<LI>
all employees who work in the <CODE>Dep</CODE> department.
        
</LI>


</UL>

<P>The first example demonstrates the query execution with
         list comprehension notation. The second example illustrates a
         query coded with a matching function.





<P>The list comprehension based implementation looks as follows:

<PRE>
get_emps(Salary, Dep) -&#62;
    Q = query 
          [E || E &#60;- table(employee),
                At &#60;- table(at_dep),
                E.salary &#62; Salary,
                E.emp_no = At.emp,
                At.dept_id = Dep]
        end,
    F = fun() -&#62; mnemosyne:eval(Q) end,
    mnesia:transaction(F).

</PRE>

<P>The data model for the Company database introduced in
the Mnesia documentation was designed to facilitate the posting of
queries like the one shown above.


<P>To implement the same query by directly searching the database is 
more complex. The following function does precisely this:

        
<PRE>
get_emps2(Salary, Dep) -&#62;
    Epat = mnesia:table_info(employee, wild_pattern),
    Apat = mnesia:table_info(at_dep, wild_pattern),
    F = fun() -&#62;
                All = mnesia:match_object(Epat),
                High = filter(All, Salary),
                Alldeps = mnesia:match_object(Apat),
                filter_deps(High, Alldeps, Dep)
        end,
    mnesia:transaction(F).
                

filter([E|Tail], Salary) -&#62;
    if 
        E#employee.salary &#62; Salary -&#62;
            [E | filter(Tail, Salary)];
        true -&#62;
            filter(Tail, Salary)
    end;
filter([], _) -&#62;
    [].

filter_deps([E|Tail], Deps, Dep) -&#62;
    case search_deps(E#employee.name, Deps, Dep) of
        true -&#62;
            [E | filter_deps(Tail, Deps, Dep)];
        false -&#62;
            filter_deps(Tail, Deps, Dep)
    end;
filter_deps([], _,_) -&#62; 
    [].


search_deps(Name, [D|Tail], Dep) -&#62;
    if
        D#at_dep.emp == Name,
        D#at_dep.dept_id == Dep -&#62; true;
        true -&#62; search_deps(Name, Tail, Dep)
    end;
search_deps(Name, Tail, Dep) -&#62;
    false.


</PRE>

<P>The function <CODE>mnesia:match_object/1</CODE> will automatically
make use of indices if any exist. However, no heuristics are
performed in order to select the best index, if 
more than one exists.

<P>As can be seen, the list comprehension provides a more elegant solution.

<A NAME="2.5"><!-- Empty --></A>
<H3>2.5  Matching in Record Fields</H3>

<P>There is a difference when matching record fields in a 
mnemosyne list comprehension and in Erlang in general 
(for example, a function clause header). The following code
returns true for all <CODE>employee</CODE> where <CODE>emp_id</CODE> is <CODE>312</CODE>
or <CODE>400</CODE>:

<PRE>
      test_employee(#employee{emp_id = 312}) -&#62; true;
      test_employee(#employee{emp_id = 400}) -&#62; true;
      test_employee(_) -&#62; false.
    
</PRE>

<P> That is, it does not check other fields of the employee record.
Compare that with the following mnemosyne query:

<PRE>
      query [E || E &#60;- table(employee),
                  E &#60;- [#employee{emp_id=312},
                        #employee{emp_id=400}]]
    
</PRE>

<P> The query will return all employees from the employee table
whos <CODE>emp_id</CODE> is either <CODE>312</CODE> or <CODE>400</CODE> 
<STRONG> and have the other fields set to the default values 
        for an employee</STRONG>.
To select all items that have a field set to some values
(disregarding the other fields) the constraint can be put in 
separate function. For example, select all employees whos <CODE>emp_id</CODE>
is either <CODE>312</CODE> or <CODE>400</CODE> independently of other fields:

<PRE>
      query [E || E &#60;- table(employee),
                  test_employee(E)]

      test_employee(#employee{emp_id = 312}) -&#62; true;
      test_employee(#employee{emp_id = 400}) -&#62; true;
      test_employee(_) -&#62; false.
    
</PRE>

<P> If there is only one acceptable value for a record field
it is more efficient to write it
directly in the query. Select employees whos <CODE>emp_id</CODE> is 312:

<PRE>
      query [E || E &#60;- table(employee),
                  E#employee.emp_id = 312]
    
</PRE>
<CENTER>
<HR>
<SMALL>
Copyright &copy; 1991-2006
<A HREF="http://www.erlang.se">Ericsson AB</A><BR>
</SMALL>
</CENTER>
</BODY>
</HTML>