Add development chapters contributed by Dan Scott.
authorRobert Soulliere <rsoulliere@libdog.mohawkcollege.ca>
Thu, 12 Aug 2010 18:27:28 +0000 (14:27 -0400)
committerRobert Soulliere <rsoulliere@libdog.mohawkcollege.ca>
Thu, 12 Aug 2010 18:27:28 +0000 (14:27 -0400)
1.6/development/OpenSRF_intro.xml [new file with mode: 0644]
1.6/development/datamodelsandaccess.xml [new file with mode: 0644]
1.6/development/introduction_to_sql.xml [new file with mode: 0644]
1.6/development/workshop.xml [new file with mode: 0644]
1.6/media/10.png [new file with mode: 0644]
1.6/media/11.png [new file with mode: 0644]
1.6/media/12.png [new file with mode: 0644]
1.6/media/13.png [new file with mode: 0644]
1.6/media/14.png [new file with mode: 0644]
1.6/media/15.png [new file with mode: 0644]
1.6/root.xml

diff --git a/1.6/development/OpenSRF_intro.xml b/1.6/development/OpenSRF_intro.xml
new file mode 100644 (file)
index 0000000..7567d07
--- /dev/null
@@ -0,0 +1,1703 @@
+<?xml version="1.0" encoding="UTF-8"?>\r
+<chapter xml:id="opensrf" xmlns="http://docbook.org/ns/docbook" version="5.0" xml:lang="EN"\r
+    xmlns:xi="http://www.w3.org/2001/XInclude" xmlns:xlink="http://www.w3.org/1999/xlink">\r
+       <chapterinfo>\r
+               <title>OpenSRF</title>\r
+       </chapterinfo>\r
+       <abstract id="_abstract">\r
+               <simpara>One of the claimed advantages of\r
+               Evergreen over alternative integrated library systems is the underlying Open\r
+               Service Request Framework (OpenSRF, pronounced "open surf") architecture. This\r
+               article introduces OpenSRF, demonstrates how to build OpenSRF services through\r
+               simple code examples, and explains the technical foundations on which OpenSRF\r
+               is built.</simpara>\r
+       </abstract>\r
+       <section id="_introducing_opensrf">\r
+               <title>Introducing OpenSRF</title>\r
+               <simpara>OpenSRF is a message routing network that offers scalability and failover\r
+               support for individual services and entire servers with minimal development and\r
+               deployment overhead. You can use OpenSRF to build loosely-coupled applications\r
+               that can be deployed on a single server or on clusters of geographically\r
+               distributed servers using the same code and minimal configuration changes.\r
+               Although copyright statements on some of the OpenSRF code date back to Mike\r
+               Rylander&#8217;s original explorations in 2000, Evergreen was the first major\r
+               application to be developed with, and to take full advantage of, the OpenSRF\r
+               architecture starting in 2004. The first official release of OpenSRF was 0.1 in\r
+               February 2005 (<ulink url="http://evergreen-ils.org/blog/?p=21">http://evergreen-ils.org/blog/?p=21</ulink>), but OpenSRF&#8217;s development\r
+               continues a steady pace of enhancement and refinement, with the release of\r
+               1.0.0 in October 2008 and the most recent release of 1.2.2 in February 2010.</simpara>\r
+               <simpara>OpenSRF is a distinct break from the architectural approach used by previous\r
+               library systems and has more in common with modern Web applications. The\r
+               traditional "scale-up" approach to serve more transactions is to purchase a\r
+               server with more CPUs and more RAM, possibly splitting the load between a Web\r
+               server, a database server, and a business logic server. Evergreen, however, is\r
+               built on the Open Service Request Framework (OpenSRF) architecture, which\r
+               firmly embraces the "scale-out" approach of spreading transaction load over\r
+               cheap commodity servers. The <ulink url="http://evergreen-ils.org/blog/?p=56">initial GPLS\r
+               PINES hardware cluster</ulink>, while certainly impressive, may have offered the\r
+               misleading impression that Evergreen requires a lot of hardware to run.\r
+               However, Evergreen and OpenSRF easily scale down to a single server; many\r
+               Evergreen libraries run their entire library system on a single server, and\r
+               most OpenSRF and Evergreen development occurs on a virtual machine running on a\r
+               single laptop or desktop image.</simpara>\r
+               <simpara>Another common concern is that the flexibility of OpenSRF&#8217;s distributed\r
+               architecture makes it complex to configure and to write new applications. This\r
+               article demonstrates that OpenSRF itself is an extremely simple architecture on\r
+               which one can easily build applications of many kinds – not just library\r
+               applications – and that you can use a number of different languages to call and\r
+               implement OpenSRF methods with a minimal learning curve. With an application\r
+               built on OpenSRF, when you identify a bottleneck in your application&#8217;s business\r
+               logic layer, you can adjust the number of the processes serving that particular\r
+               bottleneck on each of your servers; or if the problem is that your service is\r
+               resource-hungry, you could add an inexpensive server to your cluster and\r
+               dedicate it to running that resource-hungry service.</simpara>\r
+               <simplesect id="_programming_language_support">\r
+                       <title>Programming language support</title>\r
+                       <simpara>If you need to develop an entirely new OpenSRF service, you can choose from a\r
+                       number of different languages in which to implement that service. OpenSRF\r
+                       client language bindings have been written for C, Java, JavaScript, Perl, and\r
+                       Python, and service language bindings have been written for C, Perl, and Python.\r
+                       This article uses Perl examples as a lowest common denominator programming\r
+                       language. Writing an OpenSRF binding for another language is a relatively small\r
+                       task if that language offers libraries that support the core technologies on\r
+                       which OpenSRF depends:</simpara>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       <ulink url="http://tools.ietf.org/html/rfc3920">Extensible Messaging and Presence\r
+                       Protocol</ulink> (XMPP, sometimes referred to as Jabber) - provides the base messaging\r
+                       infrastructure between OpenSRF clients and services\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       <ulink url="http://json.org">JavaScript Object Notation</ulink> (JSON) - serializes the content\r
+                       of each XMPP message in a standardized and concise format\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       <ulink url="http://memcached.org">memcached</ulink> - provides the caching service\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       <ulink url="http://tools.ietf.org/html/rfc5424">syslog</ulink> - the standard UNIX logging\r
+                       service\r
+                       </simpara>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+                       <simpara>Unfortunately, the\r
+                       <ulink url="http://evergreen-ils.org/dokuwiki/doku.php?id=osrf-devel:primer">OpenSRF\r
+                       reference documentation</ulink>, although augmented by the\r
+                       <ulink url="http://evergreen-ils.org/dokuwiki/doku.php?id=osrf-devel:terms">OpenSRF\r
+                       glossary</ulink>, blog posts like <ulink url="http://evergreen-ils.org/blog/?p=36">the description\r
+                       of OpenSRF and Jabber</ulink>, and even this article, is not a sufficient substitute\r
+                       for a complete specification on which one could implement a language binding.\r
+                       The recommended option for would-be developers of another language binding is\r
+                       to use the Python implementation as the cleanest basis for a port to another\r
+                       language.</simpara>\r
+               </simplesect>\r
+       </section>\r
+       <section id="writing_an_opensrf_service">\r
+               <title>Writing an OpenSRF Service</title>\r
+               <simpara>Imagine an application architecture in which 10 lines of Perl or Python, using\r
+               the data types native to each language, are enough to implement a method that\r
+               can then be deployed and invoked seamlessly across hundreds of servers.  You\r
+               have just imagined developing with OpenSRF – it is truly that simple. Under the\r
+               covers, of course, the OpenSRF language bindings do an incredible amount of\r
+               work on behalf of the developer. An OpenSRF application consists of one or more\r
+               OpenSRF services that expose methods: for example, the <literal>opensrf.simple-text</literal>\r
+               <ulink url="http://svn.open-ils.org/trac/OpenSRF/browser/trunk/src/perl/lib/OpenSRF/Application/Demo/SimpleText.pm">demonstration\r
+               service</ulink> exposes the <literal>opensrf.simple-text.split()</literal> and\r
+               <literal>opensrf.simple-text.reverse()</literal> methods. Each method accepts zero or more\r
+               arguments and returns zero or one results. The data types supported by OpenSRF\r
+               arguments and results are typical core language data types: strings, numbers,\r
+               booleans, arrays, and hashes.</simpara>\r
+               <simpara>To implement a new OpenSRF service, perform the following steps:</simpara>\r
+               <orderedlist numeration="arabic">\r
+               <listitem>\r
+               <simpara>\r
+               Include the base OpenSRF support libraries\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               Write the code for each of your OpenSRF methods as separate procedures\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               Register each method\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               Add the service definition to the OpenSRF configuration files\r
+               </simpara>\r
+               </listitem>\r
+               </orderedlist>\r
+               <simpara>For example, the following code implements an OpenSRF service. The service\r
+               includes one method named <literal>opensrf.simple-text.reverse()</literal> that accepts one\r
+               string as input and returns the reversed version of that string:</simpara>\r
+               <programlisting language="perl" linenumbering="unnumbered">#!/usr/bin/perl\r
+\r
+               package OpenSRF::Application::Demo::SimpleText;\r
+\r
+               use strict;\r
+\r
+               use OpenSRF::Application;\r
+               use parent qw/OpenSRF::Application/;\r
+\r
+               sub text_reverse {\r
+                   my ($self , $conn, $text) = @_;\r
+                   my $reversed_text = scalar reverse($text);\r
+                   return $reversed_text;\r
+               }\r
+\r
+               __PACKAGE__-&gt;register_method(\r
+                   method    =&gt; 'text_reverse',\r
+                   api_name  =&gt; 'opensrf.simple-text.reverse'\r
+               );</programlisting>\r
+               <simpara>Ten lines of code, and we have a complete OpenSRF service that exposes a single\r
+               method and could be deployed quickly on a cluster of servers to meet your\r
+               application&#8217;s ravenous demand for reversed strings! If you&#8217;re unfamiliar with\r
+               Perl, the <literal>use OpenSRF::Application; use parent qw/OpenSRF::Application/;</literal>\r
+               lines tell this package to inherit methods and properties from the\r
+               <literal>OpenSRF::Application</literal> module. For example, the call to\r
+               <literal>__PACKAGE__-&gt;register_method()</literal> is defined in <literal>OpenSRF::Application</literal> but due to\r
+               inheritance is available in this package (named by the special Perl symbol\r
+               <literal>__PACKAGE__</literal> that contains the current package name). The <literal>register_method()</literal>\r
+               procedure is how we introduce a method to the rest of the OpenSRF world.</simpara>\r
+               <simplesect id="serviceRegistration">\r
+                       <title>Registering a service with the OpenSRF configuration files</title>\r
+                       <simpara>Two files control most of the configuration for OpenSRF:</simpara>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       <literal>opensrf.xml</literal> contains the configuration for the service itself, as well as\r
+                       a list of which application servers in your OpenSRF cluster should start\r
+                       the service.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       <literal>opensrf_core.xml</literal> (often referred to as the "bootstrap configuration"\r
+                       file) contains the OpenSRF networking information, including the XMPP server\r
+                       connection credentials for the public and private routers. You only need to touch\r
+                       this for a new service if the new service needs to be accessible via the\r
+                       public router.\r
+                       </simpara>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+                       <simpara>Begin by defining the service itself in <literal>opensrf.xml</literal>. To register the\r
+                       <literal>opensrf.simple-text</literal> service, add the following section to the <literal>&lt;apps&gt;</literal>\r
+                       element (corresponding to the XPath <literal>/opensrf/default/apps/</literal>):</simpara>\r
+                       <programlisting language="xml" linenumbering="unnumbered">&lt;apps&gt;\r
+                         &lt;opensrf.simple-text&gt; <co id="CO1-1"/> \r
+                           &lt;keepalive&gt;3&lt;/keepalive&gt;<co id="CO1-2"/> \r
+                           &lt;stateless&gt;1&lt;/stateless&gt;<co id="CO1-3"/>\r
+                           &lt;language&gt;perl&lt;/language&gt;<co id="CO1-4"/> \r
+                           &lt;implementation&gt;OpenSRF::Application::Demo::SimpleText&lt;/implementation&gt;<co id="CO1-5"/> \r
+                           &lt;max_requests&gt;100&lt;/max_requests&gt;<co id="CO1-6"/> \r
+                           &lt;unix_config&gt;\r
+                             &lt;max_requests&gt;1000&lt;/max_requests&gt; <co id="CO1-7"/> \r
+                             &lt;unix_log&gt;opensrf.simple-text_unix.log&lt;/unix_log&gt; <co id="CO1-8"/> \r
+                             &lt;unix_sock&gt;opensrf.simple-text_unix.sock&lt;/unix_sock&gt;<co id="CO1-9"/> \r
+                             &lt;unix_pid&gt;opensrf.simple-text_unix.pid&lt;/unix_pid&gt; <co id="CO1-10"/> \r
+                             &lt;min_children&gt;5&lt;/min_children&gt;  <co id="CO1-11"/> \r
+                             &lt;max_children&gt;15&lt;/max_children&gt;<co id="CO1-12"/> \r
+                             &lt;min_spare_children&gt;2&lt;/min_spare_children&gt;<co id="CO1-13"/> \r
+                             &lt;max_spare_children&gt;5&lt;/max_spare_children&gt; <co id="CO1-14"/> \r
+                           &lt;/unix_config&gt;\r
+                         &lt;/opensrf.simple-text&gt;\r
+\r
+                         &lt;!-- other OpenSRF services registered here... --&gt;\r
+                       &lt;/apps&gt;</programlisting>\r
+                       <calloutlist>\r
+                       <callout arearefs="CO1-1">\r
+                       <simpara>\r
+                       The element name is the name that the OpenSRF control scripts use to refer\r
+                       to the service.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO1-2">\r
+                       <simpara>\r
+                       The <literal>&lt;keepalive&gt;</literal> element specifies the interval (in seconds) between\r
+                       checks to determine if the service is still running.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO1-3">\r
+                       <simpara>\r
+                       The <literal>&lt;stateless&gt;</literal> element specifies whether OpenSRF clients can call\r
+                       methods from this service without first having to create a connection to a\r
+                       specific service backend process for that service. If the value is <literal>1</literal>, then\r
+                       the client can simply issue a request and the router will forward the request\r
+                       to an available service and the result will be returned directly to the client.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO1-4">\r
+                       <simpara>\r
+                       The <literal>&lt;language&gt;</literal> element specifies the programming language in which the\r
+                       service is implemented.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO1-5">\r
+                       <simpara>\r
+                       The <literal>&lt;implementation&gt;</literal> element pecifies the name of the library or module\r
+                       in which the service is implemented.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO1-6">\r
+                       <simpara>\r
+                       (C implementations only): The <literal>&lt;max_requests&gt;</literal> element, as a direct child\r
+                       of the service element name, specifies the maximum number of requests a process\r
+                       serves before it is killed and replaced by a new process.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO1-7">\r
+                       <simpara>\r
+                       (Perl implementations only): The <literal>&lt;max_requests&gt;</literal> element, as a direct\r
+                       child of the <literal>&lt;unix_config&gt;</literal> element, specifies the maximum number of requests\r
+                       a process serves before it is killed and replaced by a new process.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO1-8">\r
+                       <simpara>\r
+                       The <literal>&lt;unix_log&gt;</literal> element specifies the name of the log file for\r
+                       language-specific log messages such as syntax warnings.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO1-9">\r
+                       <simpara>\r
+                       The <literal>&lt;unix_sock&gt;</literal> element specifies the name of the UNIX socket used for\r
+                       inter-process communications.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO1-10">\r
+                       <simpara>\r
+                       The <literal>&lt;unix_pid&gt;</literal> element specifies the name of the PID file for the\r
+                       master process for the service.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO1-11">\r
+                       <simpara>\r
+                       The <literal>&lt;min_children&gt;</literal> element specifies the minimum number of child\r
+                       processes that should be running at any given time.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO1-12">\r
+                       <simpara>\r
+                       The <literal>&lt;max_children&gt;</literal> element specifies the maximum number of child\r
+                       processes that should be running at any given time.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO1-13">\r
+                       <simpara>\r
+                       The <literal>&lt;min_spare_children&gt;</literal> element specifies the minimum number of idle\r
+                       child processes that should be available to handle incoming requests.  If there\r
+                       are fewer than this number of spare child processes, new processes will be\r
+                       spawned.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO1-14">\r
+                       <simpara>\r
+                       The`&lt;max_spare_children&gt;` element specifies the maximum number of idle\r
+                       child processes that should be available to handle incoming requests. If there\r
+                       are more than this number of spare child processes, the extra processes will be\r
+                       killed.\r
+                       </simpara>\r
+                       </callout>\r
+                       </calloutlist>\r
+                       <simpara>To make the service accessible via the public router, you must also\r
+                       edit the <literal>opensrf_core.xml</literal> configuration file to add the service to the list\r
+                       of publicly accessible services:</simpara>\r
+                       <formalpara><title>Making a service publicly accessible in <literal>opensrf_core.xml</literal></title><para>\r
+                       <programlisting language="xml" linenumbering="unnumbered">&lt;router&gt;<co id="CO2-1"/> \r
+                           &lt;!-- This is the public router. On this router, we only register applications\r
+                            which should be accessible to everyone on the opensrf network --&gt;\r
+                           &lt;name&gt;router&lt;/name&gt;\r
+                           &lt;domain&gt;public.localhost&lt;/domain&gt;<co id="CO2-2"/>\r
+                           &lt;services&gt;\r
+                               &lt;service&gt;opensrf.math&lt;/service&gt;\r
+                               &lt;service&gt;opensrf.simple-text&lt;/service&gt; <co id="CO2-3"/> \r
+                           &lt;/services&gt;\r
+                       &lt;/router&gt;</programlisting>\r
+                       </para></formalpara>\r
+                       <calloutlist>\r
+                       <callout arearefs="CO2-1">\r
+                       <simpara>\r
+                       This section of the <literal>opensrf_core.xml</literal> file is located at XPath\r
+                       <literal>/config/opensrf/routers/</literal>.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO2-2">\r
+                       <simpara>\r
+                       <literal>public.localhost</literal> is the canonical public router domain in the OpenSRF\r
+                       installation instructions.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO2-3">\r
+                       <simpara>\r
+                       Each <literal>&lt;service&gt;</literal> element contained in the <literal>&lt;services&gt;</literal> element\r
+                       offers their services via the public router as well as the private router.\r
+                       </simpara>\r
+                       </callout>\r
+                       </calloutlist>\r
+                       <simpara>Once you have defined the new service, you must restart the OpenSRF Router\r
+                       to retrieve the new configuration and start or restart the service itself.</simpara>\r
+                       <simpara>Complete working examples of the <link linkend="opensrf-core-xml">opensrf_core.xml</link> and\r
+                       <link linkend="opensrf-xml">opensrf.xml</link> configuration files are included with this article\r
+                       for your reference.</simpara>\r
+               </simplesect>\r
+               <simplesect id="_calling_an_opensrf_method">\r
+                       <title>Calling an OpenSRF method</title>\r
+                       <simpara>OpenSRF clients in any supported language can invoke OpenSRF services in any\r
+                       supported language. So let&#8217;s see a few examples of how we can call our fancy\r
+                       new <literal>opensrf.simple-text.reverse()</literal> method:</simpara>\r
+                       <simplesect id="_calling_opensrf_methods_from_the_srfsh_client">\r
+                               <title>Calling OpenSRF methods from the srfsh client</title>\r
+                               <simpara><literal>srfsh</literal> is a command-line tool installed with OpenSRF that you can use to call\r
+                               OpenSRF methods. To call an OpenSRF method, issue the <literal>request</literal> command and\r
+                               pass the OpenSRF service and method name as the first two arguments; then pass\r
+                               one or more JSON objects delimited by commas as the arguments to the method\r
+                               being invoked.</simpara>\r
+                               <simpara>The following example calls the <literal>opensrf.simple-text.reverse</literal> method of the\r
+                               <literal>opensrf.simple-text</literal> OpenSRF service, passing the string <literal>"foobar"</literal> as the\r
+                               only method argument:</simpara>\r
+                               <programlisting language="sh" linenumbering="unnumbered">$ srfsh\r
+                               srfsh # request opensrf.simple-text opensrf.simple-text.reverse "foobar"\r
+\r
+                               Received Data: "raboof"\r
+\r
+                               =------------------------------------\r
+                               Request Completed Successfully\r
+                               Request Time in seconds: 0.016718\r
+                               =------------------------------------</programlisting>\r
+                       </simplesect>\r
+                       <simplesect id="opensrfIntrospection">\r
+                               <title>Getting documentation for OpenSRF methods from the srfsh client</title>\r
+                               <simpara>The <literal>srfsh</literal> client also gives you command-line access to retrieving metadata\r
+                               about OpenSRF services and methods. For a given OpenSRF method, for example,\r
+                               you can retrieve information such as the minimum number of required arguments,\r
+                               the data type and a description of each argument, the package or library in\r
+                               which the method is implemented, and a description of the method. To retrieve\r
+                               the documentation for an opensrf method from <literal>srfsh</literal>, issue the <literal>introspect</literal>\r
+                               command, followed by the name of the OpenSRF service and (optionally) the\r
+                               name of the OpenSRF method. If you do not pass a method name to the <literal>introspect</literal>\r
+                               command, <literal>srfsh</literal> lists all of the methods offered by the service. If you pass\r
+                               a partial method name, <literal>srfsh</literal> lists all of the methods that match that portion\r
+                               of the method name.</simpara>\r
+                               <note><simpara>The quality and availability of the descriptive information for each\r
+                               method depends on the developer to register the method with complete and\r
+                               accurate information. The quality varies across the set of OpenSRF and\r
+                               Evergreen APIs, although some effort is being put towards improving the\r
+                               state of the internal documentation.</simpara></note>\r
+                               <programlisting language="sh" linenumbering="unnumbered">srfsh# introspect opensrf.simple-text "opensrf.simple-text.reverse"\r
+                               --&gt; opensrf.simple-text\r
+\r
+                               Received Data: {\r
+                                 "__c":"opensrf.simple-text",\r
+                                 "__p":{\r
+                                   "api_level":1,\r
+                                   "stream":0,      <co id="CO3-1"/>\r
+                                   "object_hint":"OpenSRF_Application_Demo_SimpleText",\r
+                                   "remote":0,\r
+                                   "package":"OpenSRF::Application::Demo::SimpleText", <co id="CO3-2"/>\r
+                                   "api_name":"opensrf.simple-text.reverse",<co id="CO3-3"/>\r
+                                   "server_class":"opensrf.simple-text",\r
+                                   "signature":{ <co id="CO3-4"/>\r
+                                     "params":[  <co id="CO3-5"/>\r
+                                       {\r
+                                         "desc":"The string to reverse",\r
+                                         "name":"text",\r
+                                         "type":"string"\r
+                                       }\r
+                                     ],\r
+                                     "desc":"Returns the input string in reverse order\n", <co id="CO3-6"/>\r
+                                     "return":{                                            <co id="CO3-7"/>\r
+                                       "desc":"Returns the input string in reverse order",\r
+                                       "type":"string"\r
+                                     }\r
+                                   },\r
+                                   "method":"text_reverse",  <co id="CO3-8"/>\r
+                                   "argc":1 <co id="CO3-9"/>\r
+                                 }\r
+                               }</programlisting>\r
+                               <calloutlist>\r
+                               <callout arearefs="CO3-1">\r
+                               <simpara>\r
+                               <literal>stream</literal> denotes whether the method supports streaming responses or not.\r
+                               </simpara>\r
+                               </callout>\r
+                               <callout arearefs="CO3-2">\r
+                               <simpara>\r
+                               <literal>package</literal> identifies which package or library implements the method.\r
+                               </simpara>\r
+                               </callout>\r
+                               <callout arearefs="CO3-3">\r
+                               <simpara>\r
+                               <literal>api_name</literal> identifies the name of the OpenSRF method.\r
+                               </simpara>\r
+                               </callout>\r
+                               <callout arearefs="CO3-4">\r
+                               <simpara>\r
+                               <literal>signature</literal> is a hash that describes the parameters for the method.\r
+                               </simpara>\r
+                               </callout>\r
+                               <callout arearefs="CO3-5">\r
+                               <simpara>\r
+                               <literal>params</literal> is an array of hashes describing each parameter in the method;\r
+                               each parameter has a description (<literal>desc</literal>), name (<literal>name</literal>), and type (<literal>type</literal>).\r
+                               </simpara>\r
+                               </callout>\r
+                               <callout arearefs="CO3-6">\r
+                               <simpara>\r
+                               <literal>desc</literal> is a string that describes the method itself.\r
+                               </simpara>\r
+                               </callout>\r
+                               <callout arearefs="CO3-7">\r
+                               <simpara>\r
+                               <literal>return</literal> is a hash that describes the return value for the method; it\r
+                               contains a description of the return value (<literal>desc</literal>) and the type of the\r
+                               returned value (<literal>type</literal>).\r
+                               </simpara>\r
+                               </callout>\r
+                               <callout arearefs="CO3-8">\r
+                               <simpara>\r
+                               <literal>method</literal> identifies the name of the function or method in the source\r
+                               implementation.\r
+                               </simpara>\r
+                               </callout>\r
+                               <callout arearefs="CO3-9">\r
+                               <simpara>\r
+                               <literal>argc</literal> is an integer describing the minimum number of arguments that\r
+                               must be passed to this method.\r
+                               </simpara>\r
+                               </callout>\r
+                               </calloutlist>\r
+                       </simplesect>\r
+                       <simplesect id="_calling_opensrf_methods_from_perl_applications">\r
+                               <title>Calling OpenSRF methods from Perl applications</title>\r
+                               <simpara>To call an OpenSRF method from Perl, you must connect to the OpenSRF service,\r
+                               issue the request to the method, and then retrieve the results.</simpara>\r
+                               <programlisting language="perl" linenumbering="unnumbered">#/usr/bin/perl\r
+                               use strict;\r
+                               use OpenSRF::AppSession;\r
+                               use OpenSRF::System;\r
+\r
+                               OpenSRF::System-&gt;bootstrap_client(config_file =&gt; '/openils/conf/opensrf_core.xml');<co id="CO4-1"/>\r
+\r
+                               my $session = OpenSRF::AppSession-&gt;create("opensrf.simple-text");<co id="CO4-2"/>\r
+\r
+                               print "substring: Accepts a string and a number as input, returns a string\n";\r
+                               my $result = $session-&gt;request("opensrf.simple-text.substring", "foobar", 3);<co id="CO4-3"/>\r
+                               my $request = $result-&gt;gather(); <co id="CO4-4"/>\r
+                               print "Substring: $request\n\n";\r
+\r
+                               print "split: Accepts two strings as input, returns an array of strings\n";\r
+                               $request = $session-&gt;request("opensrf.simple-text.split", "This is a test", " ");<co id="CO4-5"/>\r
+                               my $output = "Split: [";\r
+                               my $element;\r
+                               while ($element = $request-&gt;recv()) {   <co id="CO4-6"/>\r
+                                   $output .= $element-&gt;content . ", ";  <co id="CO4-7"/>\r
+                               }\r
+                               $output =~ s/, $/]/;\r
+                               print $output . "\n\n";\r
+\r
+                               print "statistics: Accepts an array of strings as input, returns a hash\n";\r
+                               my @many_strings = [\r
+                                   "First I think I'll have breakfast",\r
+                                   "Then I think that lunch would be nice",\r
+                                   "And then seventy desserts to finish off the day"\r
+                               ];\r
+\r
+                               $result = $session-&gt;request("opensrf.simple-text.statistics", @many_strings); <co id="CO4-8"/>\r
+                               $request = $result-&gt;gather();    <co id="CO4-9"/>\r
+                               print "Length: " . $result-&gt;{'length'} . "\n";\r
+                               print "Word count: " . $result-&gt;{'word_count'} . "\n";\r
+\r
+                               $session-&gt;disconnect();       <co id="CO4-10"/></programlisting>\r
+                               <calloutlist>\r
+                               <callout arearefs="CO4-1">\r
+                               <simpara>\r
+                               The <literal>OpenSRF::System-&gt;bootstrap_client()</literal> method reads the OpenSRF\r
+                               configuration information from the indicated file and creates an XMPP client\r
+                               connection based on that information.\r
+                               </simpara>\r
+                               </callout>\r
+                               <callout arearefs="CO4-2">\r
+                               <simpara>\r
+                               The <literal>OpenSRF::AppSession-&gt;create()</literal> method accepts one argument - the name\r
+                               of the OpenSRF service to which you want to want to make one or more requests -\r
+                               and returns an object prepared to use the client connection to make those\r
+                               requests.\r
+                               </simpara>\r
+                               </callout>\r
+                               <callout arearefs="CO4-3">\r
+                               <simpara>\r
+                               The <literal>OpenSRF::AppSession-&gt;request()</literal> method accepts a minimum of one\r
+                               argument - the name of the OpenSRF method to which you want to make a request -\r
+                               followed by zero or more arguments to pass to the OpenSRF method as input\r
+                               values. This example passes a string and an integer to the\r
+                               <literal>opensrf.simple-text.substring</literal> method defined by the <literal>opensrf.simple-text</literal>\r
+                               OpenSRF service.\r
+                               </simpara>\r
+                               </callout>\r
+                               <callout arearefs="CO4-4">\r
+                               <simpara>\r
+                               The <literal>gather()</literal> method, called on the result object returned by the\r
+                               <literal>request()</literal> method, iterates over all of the possible results from the result\r
+                               object and returns a single variable.\r
+                               </simpara>\r
+                               </callout>\r
+                               <callout arearefs="CO4-5">\r
+                               <simpara>\r
+                               This <literal>request()</literal> call passes two strings to the <literal>opensrf.simple-text.split</literal>\r
+                               method defined by the <literal>opensrf.simple-text</literal> OpenSRF service and returns (via\r
+                               <literal>gather()</literal>) a reference to an array of results.\r
+                               </simpara>\r
+                               </callout>\r
+                               <callout arearefs="CO4-6">\r
+                               <simpara>\r
+                               The <literal>opensrf.simple-text.split()</literal> method is a streaming method that\r
+                               returns an array of results with one element per <literal>recv()</literal> call on the\r
+                               result object. We could use the <literal>gather()</literal> method to retrieve all of the\r
+                               results in a single array reference, but instead we simply iterate over\r
+                               the result variable until there are no more results to retrieve.\r
+                               </simpara>\r
+                               </callout>\r
+                               <callout arearefs="CO4-7">\r
+                               <simpara>\r
+                               While the <literal>gather()</literal> convenience method returns only the content of the\r
+                               complete set of results for a given request, the <literal>recv()</literal> method returns an\r
+                               OpenSRF result object with <literal>status</literal>, <literal>statusCode</literal>, and <literal>content</literal> fields as\r
+                               we saw in <link linkend="OpenSRFOverHTTP">the HTTP results example</link>.\r
+                               </simpara>\r
+                               </callout>\r
+                               <callout arearefs="CO4-8">\r
+                               <simpara>\r
+                               This <literal>request()</literal> call passes an array to the\r
+                               <literal>opensrf.simple-text.statistics</literal> method defined by the <literal>opensrf.simple-text</literal>\r
+                               OpenSRF service.\r
+                               </simpara>\r
+                               </callout>\r
+                               <callout arearefs="CO4-9">\r
+                               <simpara>\r
+                               The result object returns a hash reference via <literal>gather()</literal>. The hash\r
+                               contains the <literal>length</literal> and <literal>word_count</literal> keys we defined in the method.\r
+                               </simpara>\r
+                               </callout>\r
+                               <callout arearefs="CO4-10">\r
+                               <simpara>\r
+                               The <literal>OpenSRF::AppSession-&gt;disconnect()</literal> method closes the XMPP client\r
+                               connection and cleans up resources associated with the session.\r
+                               </simpara>\r
+                               </callout>\r
+                               </calloutlist>\r
+                       </simplesect>\r
+               </simplesect>\r
+               <simplesect id="_accepting_and_returning_more_interesting_data_types">\r
+                       <title>Accepting and returning more interesting data types</title>\r
+                       <simpara>Of course, the example of accepting a single string and returning a single\r
+                       string is not very interesting. In real life, our applications tend to pass\r
+                       around multiple arguments, including arrays and hashes. Fortunately, OpenSRF\r
+                       makes that easy to deal with; in Perl, for example, returning a reference to\r
+                       the data type does the right thing. In the following example of a method that\r
+                       returns a list, we accept two arguments of type string: the string to be split,\r
+                       and the delimiter that should be used to split the string.</simpara>\r
+                       <formalpara><title>Basic text splitting method</title><para>\r
+                       <programlisting language="perl" linenumbering="unnumbered">sub text_split {\r
+                           my $self = shift;\r
+                           my $conn = shift;\r
+                           my $text = shift;\r
+                           my $delimiter = shift || ' ';\r
+\r
+                           my @split_text = split $delimiter, $text;\r
+                           return \@split_text;\r
+                       }\r
+\r
+                       __PACKAGE__-&gt;register_method(\r
+                           method    =&gt; 'text_split',\r
+                           api_name  =&gt; 'opensrf.simple-text.split'\r
+                       );</programlisting>\r
+                       </para></formalpara>\r
+                       <simpara>We simply return a reference to the list, and OpenSRF does the rest of the work\r
+                       for us to convert the data into the language-independent format that is then\r
+                       returned to the caller. As a caller of a given method, you must rely on the\r
+                       documentation used to register to determine the data structures - if the developer has\r
+                       added the appropriate documentation.</simpara>\r
+               </simplesect>\r
+               <simplesect id="_accepting_and_returning_evergreen_objects">\r
+                       <title>Accepting and returning Evergreen objects</title>\r
+                       <simpara>OpenSRF is agnostic about objects; its role is to pass JSON back and forth\r
+                       between OpenSRF clients and services, and it allows the specific clients and\r
+                       services to define their own semantics for the JSON structures. On top of that\r
+                       infrastructure, Evergreen offers the fieldmapper: an object-relational mapper\r
+                       that provides a complete definition of all objects, their properties, their\r
+                       relationships to other objects, the permissions required to create, read,\r
+                       update, or delete objects of that type, and the database table or view on which\r
+                       they are based.</simpara>\r
+                       <simpara>The Evergreen fieldmapper offers a great deal of convenience for working with\r
+                       complex system objects beyond the basic mapping of classes to database\r
+                       schemas. Although the result is passed over the wire as a JSON object\r
+                       containing the indicated fields, fieldmapper-aware clients then turn those\r
+                       JSON objects into native objects with setter / getter methods for each field.</simpara>\r
+                       <simpara>All of this metadata about Evergreen objects is defined in the\r
+                       fieldmapper configuration file (<literal>/openils/conf/fm_IDL.xml</literal>), and access to\r
+                       these classes is provided by the <literal>open-ils.cstore</literal>, <literal>open-ils.pcrud</literal>, and\r
+                       <literal>open-ils.reporter-store</literal> OpenSRF services which parse the fieldmapper\r
+                       configuration file and dynamically register OpenSRF methods for creating,\r
+                       reading, updating, and deleting all of the defined classes.</simpara>\r
+                       <formalpara><title>Example fieldmapper class definition for "Open User Summary"</title><para>\r
+                       <programlisting language="xml" linenumbering="unnumbered">&lt;class id="mous" controller="open-ils.cstore open-ils.pcrud"\r
+                        oils_obj:fieldmapper="money::open_user_summary"\r
+                        oils_persist:tablename="money.open_usr_summary"\r
+                        reporter:label="Open User Summary"&gt;                                <co id="CO5-1"/>\r
+                           &lt;fields oils_persist:primary="usr" oils_persist:sequence=""&gt; <co id="CO5-2"/> \r
+                               &lt;field name="balance_owed" reporter:datatype="money" /&gt;  <co id="CO5-3"/> \r
+                               &lt;field name="total_owed" reporter:datatype="money" /&gt;\r
+                               &lt;field name="total_paid" reporter:datatype="money" /&gt;\r
+                               &lt;field name="usr" reporter:datatype="link"/&gt;\r
+                           &lt;/fields&gt;\r
+                           &lt;links&gt;\r
+                               &lt;link field="usr" reltype="has_a" key="id" map="" class="au"/&gt;<co id="CO5-4"/> \r
+                           &lt;/links&gt;\r
+                           &lt;permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1"&gt;<co id="CO5-5"/> \r
+                               &lt;actions&gt;\r
+                                   &lt;retrieve permission="VIEW_USER"&gt;<co id="CO5-6"/> \r
+                                       &lt;context link="usr" field="home_ou"/&gt;<co id="CO5-7"/>\r
+                                   &lt;/retrieve&gt;\r
+                               &lt;/actions&gt;\r
+                           &lt;/permacrud&gt;\r
+                       &lt;/class&gt;</programlisting>\r
+                       </para></formalpara>\r
+                       <calloutlist>\r
+                       <callout arearefs="CO5-1">\r
+                       <simpara>\r
+                       The <literal>&lt;class&gt;</literal> element defines the class:\r
+                       </simpara>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>id</literal> attribute defines the <emphasis>class hint</emphasis> that identifies the class both\r
+                       elsewhere in the fieldmapper configuration file, such as in the value of the\r
+                       <literal>field</literal> attribute of the <literal>&lt;link&gt;</literal> element, and in the JSON object itself when\r
+                       it is instantiated. For example, an "Open User Summary" JSON object would have\r
+                       the top level property of <literal>"__c":"mous"</literal>.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>controller</literal> attribute identifies the services that have direct access\r
+                       to this class. If <literal>open-ils.pcrud</literal> is not listed, for example, then there is\r
+                       no means to directly access members of this class through a public service.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>oils_obj:fieldmapper</literal> attribute defines the name of the Perl\r
+                       fieldmapper class that will be dynamically generated to provide setter and\r
+                       getter methods for instances of the class.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>oils_persist:tablename</literal> attribute identifies the schema name and table\r
+                       name of the database table that stores the data that represents the instances\r
+                       of this class. In this case, the schema is <literal>money</literal> and the table is\r
+                       <literal>open_usr_summary</literal>.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>reporter:label</literal> attribute defines a human-readable name for the class\r
+                       used in the reporting interface to identify the class. These names are defined\r
+                       in English in the fieldmapper configuration file; however, they are extracted\r
+                       so that they can be translated and served in the user&#8217;s language of choice.\r
+                       </simpara>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+                       </callout>\r
+                       <callout arearefs="CO5-2">\r
+                       <simpara>\r
+                       The <literal>&lt;fields&gt;</literal> element lists all of the fields that belong to the object.\r
+                       </simpara>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>oils_persist:primary</literal> attribute identifies the field that acts as the\r
+                       primary key for the object; in this case, the field with the name <literal>usr</literal>.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>oils_persist:sequence</literal> attribute identifies the sequence object\r
+                       (if any) in this database provides values for new instances of this class. In\r
+                       this case, the primary key is defined by a field that is linked to a different\r
+                       table, so no sequence is used to populate these instances.\r
+                       </simpara>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+                       </callout>\r
+                       <callout arearefs="CO5-3">\r
+                       <simpara>\r
+                       Each <literal>&lt;field&gt;</literal> element defines a single field with the following attributes:\r
+                       </simpara>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>name</literal> attribute identifies the column name of the field in the\r
+                       underlying database table as well as providing a name for the setter / getter\r
+                       method that can be invoked in the JSON or native version of the object.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>reporter:datatype</literal> attribute defines how the reporter should treat\r
+                       the contents of the field for the purposes of querying and display.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>reporter:label</literal> attribute can be used to provide a human-readable name\r
+                       for each field; without it, the reporter falls back to the value of the <literal>name</literal>\r
+                       attribute.\r
+                       </simpara>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+                       </callout>\r
+                       <callout arearefs="CO5-4">\r
+                       <simpara>\r
+                       The <literal>&lt;links&gt;</literal> element contains a set of zero or more <literal>&lt;link&gt;</literal> elements,\r
+                       each of which defines a relationship between the class being described and\r
+                       another class.\r
+                       </simpara>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>field</literal> attribute identifies the field named in this class that links\r
+                       to the external class.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>reltype</literal> attribute identifies the kind of relationship between the\r
+                       classes; in the case of <literal>has_a</literal>, each value in the <literal>usr</literal> field is guaranteed\r
+                       to have a corresponding value in the external class.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>key</literal> attribute identifies the name of the field in the external\r
+                       class to which this field links.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The rarely-used <literal>map</literal> attribute identifies a second class to which\r
+                       the external class links; it enables this field to define a direct\r
+                       relationship to an external class with one degree of separation, to\r
+                       avoid having to retrieve all of the linked members of an intermediate\r
+                       class just to retrieve the instances from the actual desired target class.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>class</literal> attribute identifies the external class to which this field\r
+                       links.\r
+                       </simpara>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+                       </callout>\r
+                       <callout arearefs="CO5-5">\r
+                       <simpara>\r
+                       The <literal>&lt;permacrud&gt;</literal> element defines the permissions that must have been\r
+                       granted to a user to operate on instances of this class.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO5-6">\r
+                       <simpara>\r
+                       The <literal>&lt;retrieve&gt;</literal> element is one of four possible children of the\r
+                       <literal>&lt;actions&gt;</literal> element that define the permissions required for each action:\r
+                       create, retrieve, update, and delete.\r
+                       </simpara>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>permission</literal> attribute identifies the name of the permission that must\r
+                       have been granted to the user to perform the action.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>contextfield</literal> attribute, if it exists, defines the field in this class\r
+                       that identifies the library within the system for which the user must have\r
+                       prvileges to work. If a user has been granted a given permission, but has not been\r
+                       granted privileges to work at a given library, they can not perform the action\r
+                       at that library.\r
+                       </simpara>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+                       </callout>\r
+                       <callout arearefs="CO5-7">\r
+                       <simpara>\r
+                       The rarely-used <literal>&lt;context&gt;</literal> element identifies a linked field (<literal>link</literal>\r
+                       attribute) in this class which links to an external class that holds the field\r
+                       (<literal>field</literal> attribute) that identifies the library within the system for which the\r
+                       user must have privileges to work.\r
+                       </simpara>\r
+                       </callout>\r
+                       </calloutlist>\r
+                       <simpara>When you retrieve an instance of a class, you can ask for the result to\r
+                       <emphasis>flesh</emphasis> some or all of the linked fields of that class, so that the linked\r
+                       instances are returned embedded directly in your requested instance. In that\r
+                       same request you can ask for the fleshed instances to in turn have their linked\r
+                       fields fleshed. By bundling all of this into a single request and result\r
+                       sequence, you can avoid the network overhead of requiring the client to request\r
+                       the base object, then request each linked object in turn.</simpara>\r
+                       <simpara>You can also iterate over a collection of instances and set the automatically\r
+                       generated <literal>isdeleted</literal>, <literal>isupdated</literal>, or <literal>isnew</literal> properties to indicate that\r
+                       the given instance has been deleted, updated, or created respectively.\r
+                       Evergreen can then act in batch mode over the collection to perform the\r
+                       requested actions on any of the instances that have been flagged for action.</simpara>\r
+               </simplesect>\r
+               <simplesect id="_returning_streaming_results">\r
+                       <title>Returning streaming results</title>\r
+                       <simpara>In the previous implementation of the <literal>opensrf.simple-text.split</literal> method, we\r
+                       returned a reference to the complete array of results. For small values being\r
+                       delivered over the network, this is perfectly acceptable, but for large sets of\r
+                       values this can pose a number of problems for the requesting client. Consider a\r
+                       service that returns a set of bibliographic records in response to a query like\r
+                       "all records edited in the past month"; if the underlying database is\r
+                       relatively active, that could result in thousands of records being returned as\r
+                       a single network request. The client would be forced to block until all of the\r
+                       results are returned, likely resulting in a significant delay, and depending on\r
+                       the implementation, correspondingly large amounts of memory might be consumed\r
+                       as all of the results are read from the network in a single block.</simpara>\r
+                       <simpara>OpenSRF offers a solution to this problem. If the method returns results that\r
+                       can be divided into separate meaningful units, you can register the OpenSRF\r
+                       method as a streaming method and enable the client to loop over the results one\r
+                       unit at a time until the method returns no further results. In addition to\r
+                       registering the method with the provided name, OpenSRF also registers an additional\r
+                       method with <literal>.atomic</literal> appended to the method name. The <literal>.atomic</literal> variant gathers\r
+                       all of the results into a single block to return to the client, giving the caller\r
+                       the ability to choose either streaming or atomic results from a single method\r
+                       definition.</simpara>\r
+                       <simpara>In the following example, the text splitting method has been reimplemented to\r
+                       support streaming; very few changes are required:</simpara>\r
+                       <formalpara><title>Text splitting method - streaming mode</title><para>\r
+                       <programlisting language="perl" linenumbering="unnumbered">sub text_split {\r
+                           my $self = shift;\r
+                           my $conn = shift;\r
+                           my $text = shift;\r
+                           my $delimiter = shift || ' ';\r
+\r
+                           my @split_text = split $delimiter, $text;\r
+                           foreach my $string (@split_text) { <co id="CO6-1"/>\r
+                               $conn-&gt;respond($string);\r
+                           }\r
+                           return undef;\r
+                       }\r
+\r
+                       __PACKAGE__-&gt;register_method(\r
+                           method    =&gt; 'text_split',\r
+                           api_name  =&gt; 'opensrf.simple-text.split',\r
+                           stream    =&gt; 1<co id="CO6-2"/>\r
+                       );</programlisting>\r
+                       </para></formalpara>\r
+                       <calloutlist>\r
+                       <callout arearefs="CO6-1">\r
+                       <simpara>\r
+                       Rather than returning a reference to the array, a streaming method loops\r
+                       over the contents of the array and invokes the <literal>respond()</literal> method of the\r
+                       connection object on each element of the array.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO6-2">\r
+                       <simpara>\r
+                       Registering the method as a streaming method instructs OpenSRF to also\r
+                       register an atomic variant (<literal>opensrf.simple-text.split.atomic</literal>).\r
+                       </simpara>\r
+                       </callout>\r
+                       </calloutlist>\r
+               </simplesect>\r
+               <simplesect id="_error_warning_info_debug">\r
+                       <title>Error! Warning! Info! Debug!</title>\r
+                       <simpara>As hard as it may be to believe, it is true: applications sometimes do not\r
+                       behave in the expected manner, particularly when they are still under\r
+                       development. The service language bindings for OpenSRF include integrated\r
+                       support for logging messages at the levels of ERROR, WARNING, INFO, DEBUG, and\r
+                       the extremely verbose INTERNAL to either a local file or to a syslogger\r
+                       service. The destination of the log files, and the level of verbosity to be\r
+                       logged, is set in the <literal>opensrf_core.xml</literal> configuration file. To add logging to\r
+                       our Perl example, we just have to add the <literal>OpenSRF::Utils::Logger</literal> package to our\r
+                       list of used Perl modules, then invoke the logger at the desired logging level.</simpara>\r
+                       <simpara>You can include many calls to the OpenSRF logger; only those that are higher\r
+                       than your configured logging level will actually hit the log. The following\r
+                       example exercises all of the available logging levels in OpenSRF:</simpara>\r
+                       <programlisting language="perl" linenumbering="unnumbered">use OpenSRF::Utils::Logger;\r
+                       my $logger = OpenSRF::Utils::Logger;\r
+                       # some code in some function\r
+                       {\r
+                           $logger-&gt;error("Hmm, something bad DEFINITELY happened!");\r
+                           $logger-&gt;warn("Hmm, something bad might have happened.");\r
+                           $logger-&gt;info("Something happened.");\r
+                           $logger-&gt;debug("Something happened; here are some more details.");\r
+                           $logger-&gt;internal("Something happened; here are all the gory details.")\r
+                       }</programlisting>\r
+                       <simpara>If you call the mythical OpenSRF method containing the preceding OpenSRF logger\r
+                       statements on a system running at the default logging level of INFO, you will\r
+                       only see the INFO, WARN, and ERR messages, as follows:</simpara>\r
+                       <formalpara><title>Results of logging calls at the default level of INFO</title><para>\r
+                       <screen>[2010-03-17 22:27:30] opensrf.simple-text [ERR :5681:SimpleText.pm:277:] Hmm, something bad DEFINITELY happened!\r
+                       [2010-03-17 22:27:30] opensrf.simple-text [WARN:5681:SimpleText.pm:278:] Hmm, something bad might have happened.\r
+                       [2010-03-17 22:27:30] opensrf.simple-text [INFO:5681:SimpleText.pm:279:] Something happened.</screen>\r
+                       </para></formalpara>\r
+                       <simpara>If you then increase the the logging level to INTERNAL (5), the logs will\r
+                       contain much more information, as follows:</simpara>\r
+                       <formalpara><title>Results of logging calls at the default level of INTERNAL</title><para>\r
+                       <screen>[2010-03-17 22:48:11] opensrf.simple-text [ERR :5934:SimpleText.pm:277:] Hmm, something bad DEFINITELY happened!\r
+                       [2010-03-17 22:48:11] opensrf.simple-text [WARN:5934:SimpleText.pm:278:] Hmm, something bad might have happened.\r
+                       [2010-03-17 22:48:11] opensrf.simple-text [INFO:5934:SimpleText.pm:279:] Something happened.\r
+                       [2010-03-17 22:48:11] opensrf.simple-text [DEBG:5934:SimpleText.pm:280:] Something happened; here are some more details.\r
+                       [2010-03-17 22:48:11] opensrf.simple-text [INTL:5934:SimpleText.pm:281:] Something happened; here are all the gory details.\r
+                       [2010-03-17 22:48:11] opensrf.simple-text [ERR :5934:SimpleText.pm:283:] Resolver did not find a cache hit\r
+                       [2010-03-17 22:48:21] opensrf.simple-text [INTL:5934:Cache.pm:125:] Stored opensrf.simple-text.test_cache.masaa =&gt; "here" in memcached server\r
+                       [2010-03-17 22:48:21] opensrf.simple-text [DEBG:5934:Application.pm:579:] Coderef for [OpenSRF::Application::Demo::SimpleText::test_cache]...\r
+                       [2010-03-17 22:48:21] opensrf.simple-text [DEBG:5934:Application.pm:586:] A top level Request object is responding de nada\r
+                       [2010-03-17 22:48:21] opensrf.simple-text [DEBG:5934:Application.pm:190:] Method duration for [opensrf.simple-text.test_cache]:  10.005\r
+                       [2010-03-17 22:48:21] opensrf.simple-text [INTL:5934:AppSession.pm:780:] Calling queue_wait(0)\r
+                       [2010-03-17 22:48:21] opensrf.simple-text [INTL:5934:AppSession.pm:769:] Resending...0\r
+                       [2010-03-17 22:48:21] opensrf.simple-text [INTL:5934:AppSession.pm:450:] In send\r
+                       [2010-03-17 22:48:21] opensrf.simple-text [DEBG:5934:AppSession.pm:506:] AppSession sending RESULT to opensrf@private.localhost/... \r
+                       [2010-03-17 22:48:21] opensrf.simple-text [DEBG:5934:AppSession.pm:506:] AppSession sending STATUS to opensrf@private.localhost/... \r
+                       ...</screen>\r
+                       </para></formalpara>\r
+                       <simpara>To see everything that is happening in OpenSRF, try leaving your logging level\r
+                       set to INTERNAL for a few minutes - just ensure that you have a lot of free disk\r
+                       space available if you have a moderately busy system!</simpara>\r
+               </simplesect>\r
+               <simplesect id="_caching_results_one_secret_of_scalability">\r
+                       <title>Caching results: one secret of scalability</title>\r
+                       <simpara>If you have ever used an application that depends on a remote Web service\r
+                       outside of your control&#8201;&#8212;&#8201;say, if you need to retrieve results from a\r
+                       microblogging service&#8201;&#8212;&#8201;you know the pain of latency and dependability (or the\r
+                       lack thereof). To improve the response time for OpenSRF services, you can take\r
+                       advantage of the support offered by the <literal>OpenSRF::Utils::Cache</literal> module for\r
+                       communicating with a local instance or cluster of <literal>memcache</literal> daemons to store\r
+                       and retrieve persistent values. The following example demonstrates caching\r
+                       by sleeping for 10 seconds the first time it receives a given cache key and\r
+                       cannot retrieve a corresponding value from the cache:</simpara>\r
+                       <formalpara><title>Simple caching OpenSRF service</title><para>\r
+                       <programlisting language="perl" linenumbering="unnumbered">use OpenSRF::Utils::Cache;<co id="CO7-1"/>\r
+                       sub test_cache {\r
+                           my $self = shift;\r
+                           my $conn = shift;\r
+                           my $test_key = shift;\r
+                           my $cache = OpenSRF::Utils::Cache-&gt;new('global'); <co id="CO7-2"/>\r
+                           my $cache_key = "opensrf.simple-text.test_cache.$test_key"; <co id="CO7-3"/>\r
+                           my $result = $cache-&gt;get_cache($cache_key) || undef; <co id="CO7-4"/>\r
+                           if ($result) {\r
+                               $logger-&gt;info("Resolver found a cache hit");\r
+                               return $result;\r
+                           }\r
+                           sleep 10; <co id="CO7-5"/>\r
+                           my $cache_timeout = 300; <co id="CO7-6"/>\r
+                           $cache-&gt;put_cache($cache_key, "here", $cache_timeout); <co id="CO7-7"/>\r
+                           return "There was no cache hit.";\r
+                       }</programlisting>\r
+                       </para></formalpara>\r
+                       <calloutlist>\r
+                       <callout arearefs="CO7-1">\r
+                       <simpara>\r
+                       The OpenSRF::Utils::Cache module provides access to the built-in caching\r
+                       support in OpenSRF.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO7-2">\r
+                       <simpara>\r
+                       The constructor for the cache object accepts a single argument to define\r
+                       the cache type for the object. Each cache type can use a separate <literal>memcache</literal>\r
+                       server to keep the caches separated. Most Evergreen services use the <literal>global</literal>\r
+                       cache, while the <literal>anon</literal> cache is used for Web sessions.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO7-3">\r
+                       <simpara>\r
+                       The cache key is simply a string that uniquely identifies the value you\r
+                       want to store or retrieve. This line creates a cache key based on the OpenSRF\r
+                       method name and request input value.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO7-4">\r
+                       <simpara>\r
+                       The <literal>get_cache()</literal> method checks to see if the cache key already exists. If\r
+                       a matching key is found, the service immediately returns the stored value.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO7-5">\r
+                       <simpara>\r
+                       If the cache key does not exist, the code sleeps for 10 seconds to\r
+                       simulate a call to a slow remote Web service or an intensive process.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO7-6">\r
+                       <simpara>\r
+                       The <literal>$cache_timeout</literal> variable represents a value for the lifetime of the\r
+                       cache key in seconds.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO7-7">\r
+                       <simpara>\r
+                       After the code retrieves its value (or, in the case of this example,\r
+                       finishes sleeping), it creates the cache entry by calling the <literal>put_cache()</literal>\r
+                       method. The method accepts three arguments: the cache key, the value to be\r
+                       stored ("here"), and the timeout value in seconds to ensure that we do not\r
+                       return stale data on subsequent calls.\r
+                       </simpara>\r
+                       </callout>\r
+                       </calloutlist>\r
+               </simplesect>\r
+               <simplesect id="_initializing_the_service_and_its_children_child_labour">\r
+                       <title>Initializing the service and its children: child labour</title>\r
+                       <simpara>When an OpenSRF service is started, it looks for a procedure called\r
+                       <literal>initialize()</literal> to set up any global variables shared by all of the children of\r
+                       the service. The <literal>initialize()</literal> procedure is typically used to retrieve\r
+                       configuration settings from the <literal>opensrf.xml</literal> file.</simpara>\r
+                       <simpara>An OpenSRF service spawns one or more children to actually do the work\r
+                       requested by callers of the service. For every child process an OpenSRF service\r
+                       spawns, the child process clones the parent environment and then each child\r
+                       process runs the <literal>child_init()</literal> process (if any) defined in the OpenSRF service\r
+                       to initialize any child-specific settings.</simpara>\r
+                       <simpara>When the OpenSRF service kills a child process, it invokes the <literal>child_exit()</literal>\r
+                       procedure (if any) to clean up any resources associated with the child process.\r
+                       Similarly, when the OpenSRF service is stopped, it calls the <literal>DESTROY()</literal>\r
+                       procedure to clean up any remaining resources.</simpara>\r
+               </simplesect>\r
+               <simplesect id="_retrieving_configuration_settings">\r
+                       <title>Retrieving configuration settings</title>\r
+                       <simpara>The settings for OpenSRF services are maintained in the <literal>opensrf.xml</literal> XML\r
+                       configuration file. The structure of the XML document consists of a root\r
+                       element <literal>&lt;opensrf&gt;</literal> containing two child elements:</simpara>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>&lt;default&gt;</literal> element contains an <literal>&lt;apps&gt;</literal> element describing all\r
+                       OpenSRF services running on this system&#8201;&#8212;&#8201;see <xref linkend="serviceRegistration"/> --, as\r
+                       well as any other arbitrary XML descriptions required for global configuration\r
+                       purposes. For example, Evergreen uses this section for email notification and\r
+                       inter-library patron privacy settings.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>&lt;hosts&gt;</literal> element contains one element per host that participates in\r
+                       this OpenSRF system. Each host element must include an <literal>&lt;activeapps&gt;</literal> element\r
+                       that lists all of the services to start on this host when the system starts\r
+                       up. Each host element can optionally override any of the default settings.\r
+                       </simpara>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+                       <simpara>OpenSRF includes a service named <literal>opensrf.settings</literal> to provide distributed\r
+                       cached access to the configuration settings with a simple API:</simpara>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       <literal>opensrf.settings.default_config.get</literal> accepts zero arguments and returns\r
+                       the complete set of default settings as a JSON document.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       <literal>opensrf.settings.host_config.get</literal> accepts one argument (hostname) and\r
+                       returns the complete set of settings, as customized for that hostname, as a\r
+                       JSON document.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       <literal>opensrf.settings.xpath.get</literal> accepts one argument (an\r
+                       <ulink url="http://www.w3.org/TR/xpath/">XPath</ulink> expression) and returns the portion of\r
+                       the configuration file that matches the expression as a JSON document.\r
+                       </simpara>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+                       <simpara>For example, to determine whether an Evergreen system uses the opt-in\r
+                       support for sharing patron information between libraries, you could either\r
+                       invoke the <literal>opensrf.settings.default_config.get</literal> method and parse the\r
+                       JSON document to determine the value, or invoke the <literal>opensrf.settings.xpath.get</literal>\r
+                       method with the XPath <literal>/opensrf/default/share/user/opt_in</literal> argument to\r
+                       retrieve the value directly.</simpara>\r
+                       <simpara>In practice, OpenSRF includes convenience libraries in all of its client\r
+                       language bindings to simplify access to configuration values. C offers\r
+                       osrfConfig.c, Perl offers <literal>OpenSRF::Utils::SettingsClient</literal>, Java offers\r
+                       <literal>org.opensrf.util.SettingsClient</literal>, and Python offers <literal>osrf.set</literal>. These\r
+                       libraries locally cache the configuration file to avoid network roundtrips for\r
+                       every request and enable the developer to request specific values without\r
+                       having to manually construct XPath expressions.</simpara>\r
+               </simplesect>\r
+       </section>\r
+       <section id="_getting_under_the_covers_with_opensrf">\r
+               <title>OpenSRF Communication Flows</title>\r
+               <simpara>Now that you have seen that it truly is easy to create an OpenSRF service, we\r
+               can take a look at what is going on under the covers to make all of this work\r
+               for you.</simpara>\r
+               <simplesect id="_get_on_the_messaging_bus_safely">\r
+                       <title>Get on the messaging bus - safely</title>\r
+                       <simpara>One of the core innovations of OpenSRF was to use the Extensible Messaging and\r
+                       Presence Protocol (XMPP, more colloquially known as Jabber) as the messaging\r
+                       bus that ties OpenSRF services together across servers. XMPP is an "XML\r
+                       protocol for near-real-time messaging, presence, and request-response services"\r
+                       (<ulink url="http://www.ietf.org/rfc/rfc3920.txt">http://www.ietf.org/rfc/rfc3920.txt</ulink>) that OpenSRF relies on to handle most of\r
+                       the complexity of networked communications.  OpenSRF requres an XMPP server\r
+                       that supports multiple domains such as <ulink url="http://www.ejabberd.im/">ejabberd</ulink>.\r
+                       Multiple domain support means that a single server can support XMPP virtual\r
+                       hosts with separate sets of users and access privileges per domain. By\r
+                       routing communications through separate public and private XMPP domains,\r
+                       OpenSRF services gain an additional layer of security.</simpara>\r
+                       <simpara>The <ulink url="http://evergreen-ils.org/dokuwiki/doku.php?id=opensrf:1.2:install">OpenSRF\r
+                       installation documentation</ulink> instructs you to create two separate hostnames\r
+                       (<literal>private.localhost</literal> and <literal>public.localhost</literal>) to use as XMPP domains.  OpenSRF\r
+                       can control access to its services based on the domain of the client and\r
+                       whether a given service allows access from clients on the public domain.  When\r
+                       you start OpenSRF, the first XMPP clients that connect to the XMPP server are\r
+                       the OpenSRF public and private <emphasis>routers</emphasis>. OpenSRF routers maintain a list of\r
+                       available services and connect clients to available services. When an OpenSRF\r
+                       service starts, it establishes a connection to the XMPP server and registers\r
+                       itself with the private router. The OpenSRF configuration contains a list of\r
+                       public OpenSRF services, each of which must also register with the public\r
+                       router.</simpara>\r
+               </simplesect>\r
+               <simplesect id="_opensrf_communication_flows_over_xmpp">\r
+                       <title>OpenSRF communication flows over XMPP</title>\r
+                       <simpara>In a minimal OpenSRF deployment, two XMPP users named "router" connect to the\r
+                       XMPP server, with one connected to the private XMPP domain and one connected to\r
+                       the public XMPP domain. Similarly, two XMPP users named "opensrf" connect to\r
+                       the XMPP server via the private and public XMPP domains. When an OpenSRF\r
+                       service is started, it uses the "opensrf" XMPP user to advertise its\r
+                       availability with the corresponding router on that XMPP domain; the XMPP server\r
+                       automatically assigns a Jabber ID (<emphasis>JID</emphasis>) based on the client hostname to each\r
+                       service&#8217;s listener process and each connected drone process waiting to carry\r
+                       out requests. When an OpenSRF router receives a request to invoke a method on a\r
+                       given service, it connects the requester to the next available listener in the\r
+                       list of registered listeners for that service.</simpara>\r
+                       <simpara>Services and clients connect to the XMPP server using a single set of XMPP\r
+                       client credentials (for example, <literal>opensrf@private.localhost</literal>), but use XMPP\r
+                       resource identifiers to differentiate themselves in the JID for each\r
+                       connection. For example, the JID for a copy of the <literal>opensrf.simple-text</literal>\r
+                       service with process ID <literal>6285</literal> that has connected to the <literal>private.localhost</literal>\r
+                       domain using the <literal>opensrf</literal> XMPP client credentials could be\r
+                       <literal>opensrf@private.localhost/opensrf.simple-text_drone_at_localhost_6285</literal>.  By\r
+                       convention, the user name for OpenSRF clients is <literal>opensrf</literal>, and the user name\r
+                       for OpenSRF routers is <literal>router</literal>, so the XMPP server for OpenSRF will have four\r
+                       separate users registered:\r
+                         * <literal>opensrf@private.localhost</literal> is an OpenSRF client that connects with these\r
+                       credentials and which can access any OpenSRF service.\r
+                         * <literal>opensrf@public.localhost</literal> is an OpenSRF client that connects with these\r
+                       credentials and which can only access OpenSRF services that have registered\r
+                       with the public router.\r
+                         * <literal>router@private.localhost</literal> is the private OpenSRF router with which all\r
+                       services register.\r
+                         * <literal>router@public.localhost</literal> is the public OpenSRF router with which only\r
+                       services that must be publicly accessible register.</simpara>\r
+                       <simpara>All OpenSRF services automatically register themselves with the private XMPP\r
+                       domain, but only those services that register themselves with the public XMPP\r
+                       domain can be invoked from public OpenSRF clients.  The OpenSRF client and\r
+                       router user names, passwords, and domain names, along with the list of services\r
+                       that should be public, are contained in the <literal>opensrf_core.xml</literal> configuration\r
+                       file.</simpara>\r
+               </simplesect>\r
+               <simplesect id="OpenSRFOverHTTP">\r
+                       <title>OpenSRF communication flows over HTTP</title>\r
+                       <simpara>In some contexts, access to a full XMPP client is not a practical option. For\r
+                       example, while XMPP clients have been implemented in JavaScript, you might\r
+                       be concerned about browser compatibility and processing overhead - or you might\r
+                       want to issue OpenSRF requests from the command line with <literal>curl</literal>. Fortunately,\r
+                       any OpenSRF service registered with the public router is accessible via the\r
+                       OpenSRF HTTP Translator. The OpenSRF HTTP Translator implements the\r
+                       <ulink url="http://www.open-ils.org/dokuwiki/doku.php?id=opensrf_over_http">OpenSRF-over-HTTP\r
+                       proposed specification</ulink> as an Apache module that translates HTTP requests into\r
+                       OpenSRF requests and returns OpenSRF results as HTTP results to the initiating\r
+                       HTTP client.</simpara>\r
+                       <formalpara><title>Issuing an HTTP POST request to an OpenSRF method via the OpenSRF HTTP Translator</title><para>\r
+                       <programlisting language="bash" linenumbering="unnumbered"># curl request broken up over multiple lines for legibility\r
+                       curl -H "X-OpenSRF-service: opensrf.simple-text"<co id="CO8-1"/>\r
+                           --data 'osrf-msg=[  \<co id="CO8-2"/>\r
+                               {"__c":"osrfMessage","__p":{"threadTrace":0,"locale":"en-CA", <co id="CO8-3"/>\r
+                                   "type":"REQUEST","payload": {"__c":"osrfMethod","__p": \r
+                                       {"method":"opensrf.simple-text.reverse","params":["foobar"]}   \r
+                                   }}                                                                  \r
+                               }]'                                                                    \r
+                       http://localhost/osrf-http-translator <co id="CO8-4"/></programlisting>\r
+                       </para></formalpara>\r
+                       <calloutlist>\r
+                       <callout arearefs="CO8-1">\r
+                       <simpara>\r
+                       The <literal>X-OpenSRF-service</literal> header identifies the OpenSRF service of interest.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO8-2">\r
+                       <simpara>\r
+                       The POST request consists of a single parameter, the <literal>osrf-msg</literal> value,\r
+                       which contains a JSON array.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO8-3">\r
+                       <simpara>\r
+                       The first object is an OpenSRF message (<literal>"__c":"osrfMessage"</literal>) with a set of\r
+                       parameters (<literal>"__p":{}</literal>).\r
+                       </simpara>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The identifier for the request (<literal>"threadTrace":0</literal>); this value is echoed\r
+                       back in the result.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The message type (<literal>"type":"REQUEST"</literal>).\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The locale for the message; if the OpenSRF method is locale-sensitive, it\r
+                       can check the locale for each OpenSRF request and return different information\r
+                       depending on the locale.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The payload of the message (<literal>"payload":{}</literal>) containing the OpenSRF method\r
+                       request (<literal>"__c":"osrfMethod"</literal>) and its parameters (<literal>"__p:"{}</literal>).\r
+                       </simpara>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The method name for the request (<literal>"method":"opensrf.simple-text.reverse"</literal>).\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       A set of JSON parameters to pass to the method (<literal>"params":["foobar"]</literal>); in\r
+                       this case, a single string <literal>"foobar"</literal>.\r
+                       </simpara>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+                       </callout>\r
+                       <callout arearefs="CO8-4">\r
+                       <simpara>\r
+                       The URL on which the OpenSRF HTTP translator is listening,\r
+                       <literal>/osrf-http-translator</literal> is the default location in the Apache example\r
+                       configuration files shipped with the OpenSRF source, but this is configurable.\r
+                       </simpara>\r
+                       </callout>\r
+                       </calloutlist>\r
+                       <formalpara><title>Results from an HTTP POST request to an OpenSRF method via the OpenSRF HTTP Translator</title><para>\r
+                       <programlisting language="bash" linenumbering="unnumbered"># HTTP response broken up over multiple lines for legibility\r
+                       [{"__c":"osrfMessage","__p":   <co id="CO9-1"/>\r
+                           {"threadTrace":0, "payload": <co id="CO9-2"/>\r
+                               {"__c":"osrfResult","__p": <co id="CO9-3"/>\r
+                                   {"status":"OK","content":"raboof","statusCode":200} <co id="CO9-4"/>\r
+                               },"type":"RESULT","locale":"en-CA" <co id="CO9-5"/>\r
+                           }\r
+                       },\r
+                       {"__c":"osrfMessage","__p":   <co id="CO9-6"/>\r
+                           {"threadTrace":0,"payload":  <co id="CO9-7"/>\r
+                               {"__c":"osrfConnectStatus","__p": <co id="CO9-8"/>\r
+                                   {"status":"Request Complete","statusCode":205}<co id="CO9-9"/>\r
+                               },"type":"STATUS","locale":"en-CA"  <co id="CO9-10"/>\r
+                           }\r
+                       }]</programlisting>\r
+                       </para></formalpara>\r
+                       <calloutlist>\r
+                       <callout arearefs="CO9-1">\r
+                       <simpara>\r
+                       The OpenSRF HTTP Translator returns an array of JSON objects in its\r
+                       response. Each object in the response is an OpenSRF message\r
+                       (<literal>"__c":"osrfMessage"</literal>) with a collection of response parameters (<literal>"__p":</literal>).\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO9-2">\r
+                       <simpara>\r
+                       The OpenSRF message identifier (<literal>"threadTrace":0</literal>) confirms that this\r
+                       message is in response to the request matching the same identifier.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO9-3">\r
+                       <simpara>\r
+                       The message includes a payload JSON object (<literal>"payload":</literal>) with an OpenSRF\r
+                       result for the request (<literal>"__c":"osrfResult"</literal>).\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO9-4">\r
+                       <simpara>\r
+                       The result includes a status indicator string (<literal>"status":"OK"</literal>), the content\r
+                       of the result response - in this case, a single string "raboof"\r
+                       (<literal>"content":"raboof"</literal>) - and an integer status code for the request\r
+                       (<literal>"statusCode":200</literal>).\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO9-5">\r
+                       <simpara>\r
+                       The message also includes the message type (<literal>"type":"RESULT"</literal>) and the\r
+                       message locale (<literal>"locale":"en-CA"</literal>).\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO9-6">\r
+                       <simpara>\r
+                       The second message in the set of results from the response.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO9-7">\r
+                       <simpara>\r
+                       Again, the message identifier confirms that this message is in response to\r
+                       a particular request.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO9-8">\r
+                       <simpara>\r
+                       The payload of the message denotes that this message is an\r
+                       OpenSRF connection status message (<literal>"__c":"osrfConnectStatus"</literal>), with some\r
+                       information about the particular OpenSRF connection that was used for this\r
+                       request.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO9-9">\r
+                       <simpara>\r
+                       The response parameters for an OpenSRF connection status message include a\r
+                       verbose status (<literal>"status":"Request Complete"</literal>) and an integer status code for\r
+                       the connection status (`"statusCode":205).\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="CO9-10">\r
+                       <simpara>\r
+                       The message also includes the message type (<literal>"type":"RESULT"</literal>) and the\r
+                       message locale (<literal>"locale":"en-CA"</literal>).\r
+                       </simpara>\r
+                       </callout>\r
+                       </calloutlist>\r
+                       <tip><simpara>Before adding a new public OpenSRF service, ensure that it does\r
+                       not introduce privilege escalation or unchecked access to data. For example,\r
+                       the Evergreen <literal>open-ils.cstore</literal> private service is an object-relational mapper\r
+                       that provides read and write access to the entire Evergreen database, so it\r
+                       would be catastrophic to expose that service publicly. In comparison, the\r
+                       Evergreen <literal>open-ils.pcrud</literal> public service offers the same functionality as\r
+                       <literal>open-ils.cstore</literal> to any connected HTTP client or OpenSRF client, but the\r
+                       additional authentication and authorization layer in <literal>open-ils.pcrud</literal> prevents\r
+                       unchecked access to Evergreen&#8217;s data.</simpara></tip>\r
+               </simplesect>\r
+               <simplesect id="_stateless_and_stateful_connections">\r
+                       <title>Stateless and stateful connections</title>\r
+                       <simpara>OpenSRF supports both <emphasis>stateless</emphasis> and <emphasis>stateful</emphasis> connections.  When an OpenSRF\r
+                       client issues a <literal>REQUEST</literal> message in a <emphasis>stateless</emphasis> connection, the router\r
+                       forwards the request to the next available service and the service returns the\r
+                       result directly to the client.</simpara>\r
+                       <formalpara><title>REQUEST flow in a stateless connection</title><para><inlinemediaobject>\r
+                         <imageobject>\r
+                         <imagedata fileref="REQUEST.png"/>\r
+                         </imageobject>\r
+                         <textobject><phrase>REQUEST flow in a stateless connection</phrase></textobject>\r
+                       </inlinemediaobject></para></formalpara>\r
+                       <simpara>When an OpenSRF client issues a <literal>CONNECT</literal> message to create a <emphasis>stateful</emphasis> conection, the\r
+                       router returns the Jabber ID of the next available service to the client so\r
+                       that the client can issue one or more <literal>REQUEST</literal> message directly to that\r
+                       particular service and the service will return corresponding <literal>RESULT</literal> messages\r
+                       directly to the client. Until the client issues a <literal>DISCONNECT</literal> message, that\r
+                       particular service is only available to the requesting client. Stateful connections\r
+                       are useful for clients that need to make many requests from a particular service,\r
+                       as it avoids the intermediary step of contacting the router for each request, as\r
+                       well as for operations that require a controlled sequence of commands, such as a\r
+                       set of database INSERT, UPDATE, and DELETE statements within a transaction.</simpara>\r
+                       <formalpara><title>CONNECT, REQUEST, and DISCONNECT flow in a stateful connection</title><para><inlinemediaobject>\r
+                         <imageobject>\r
+                         <imagedata fileref="CONNECT.png"/>\r
+                         </imageobject>\r
+                         <textobject><phrase>CONNECT</phrase></textobject>\r
+                       </inlinemediaobject></para></formalpara>\r
+               </simplesect>\r
+               <simplesect id="_message_body_format">\r
+                       <title>Message body format</title>\r
+                       <simpara>OpenSRF was an early adopter of JavaScript Object Notation (JSON). While XMPP\r
+                       is an XML protocol, the Evergreen developers recognized that the compactness of\r
+                       the JSON format offered a significant reduction in bandwidth for the volume of\r
+                       messages that would be generated in an application of that size. In addition,\r
+                       the ability of languages such as JavaScript, Perl, and Python to generate\r
+                       native objects with minimal parsing offered an attractive advantage over\r
+                       invoking an XML parser for every message. Instead, the body of the XMPP message\r
+                       is a simple JSON structure. For a simple request, like the following example\r
+                       that simply reverses a string, it looks like a significant overhead: but we get\r
+                       the advantages of locale support and tracing the request from the requester\r
+                       through the listener and responder (drone).</simpara>\r
+                       <formalpara><title>A request for opensrf.simple-text.reverse("foobar"):</title><para>\r
+                       <programlisting language="xml" linenumbering="unnumbered">&lt;message from='router@private.localhost/opensrf.simple-text'\r
+                         to='opensrf@private.localhost/opensrf.simple-text_listener_at_localhost_6275'\r
+                         router_from='opensrf@private.localhost/_karmic_126678.3719_6288'\r
+                         router_to='' router_class='' router_command='' osrf_xid=''\r
+                       &gt;\r
+                         &lt;thread&gt;1266781414.366573.12667814146288&lt;/thread&gt;\r
+                         &lt;body&gt;\r
+                       [\r
+                         {"__c":"osrfMessage","__p":\r
+                           {"threadTrace":"1","locale":"en-US","type":"REQUEST","payload":\r
+                             {"__c":"osrfMethod","__p":\r
+                               {"method":"opensrf.simple-text.reverse","params":["foobar"]}\r
+                             }\r
+                           }\r
+                         }\r
+                       ]\r
+                         &lt;/body&gt;\r
+                       &lt;/message&gt;</programlisting>\r
+                       </para></formalpara>\r
+                       <formalpara><title>A response from opensrf.simple-text.reverse("foobar")</title><para>\r
+                       <programlisting language="xml" linenumbering="unnumbered">&lt;message from='opensrf@private.localhost/opensrf.simple-text_drone_at_localhost_6285'\r
+                         to='opensrf@private.localhost/_karmic_126678.3719_6288'\r
+                         router_command='' router_class='' osrf_xid=''\r
+                       &gt;\r
+                         &lt;thread&gt;1266781414.366573.12667814146288&lt;/thread&gt;\r
+                         &lt;body&gt;\r
+                       [\r
+                         {"__c":"osrfMessage","__p":\r
+                           {"threadTrace":"1","payload":\r
+                             {"__c":"osrfResult","__p":\r
+                               {"status":"OK","content":"raboof","statusCode":200}\r
+                             } ,"type":"RESULT","locale":"en-US"}\r
+                         },\r
+                         {"__c":"osrfMessage","__p":\r
+                           {"threadTrace":"1","payload":\r
+                             {"__c":"osrfConnectStatus","__p":\r
+                               {"status":"Request Complete","statusCode":205}\r
+                             },"type":"STATUS","locale":"en-US"}\r
+                         }\r
+                       ]\r
+                         &lt;/body&gt;\r
+                       &lt;/message&gt;</programlisting>\r
+                       </para></formalpara>\r
+                       <simpara>The content of the <literal>&lt;body&gt;</literal> element of the OpenSRF request and result should\r
+                       look familiar; they match the structure of the <link linkend="OpenSRFOverHTTP">OpenSRF over HTTP examples</link> that we previously dissected.</simpara>\r
+               </simplesect>\r
+               <simplesect id="_registering_opensrf_methods_in_depth">\r
+                       <title>Registering OpenSRF methods in depth</title>\r
+                       <simpara>Let&#8217;s explore the call to <literal>__PACKAGE__-&gt;register_method()</literal>; most of the members\r
+                       of the hash are optional, and for the sake of brevity we omitted them in the\r
+                       previous example. As we have seen in the results of the <link linkend="opensrfIntrospection">introspection call</link>, a\r
+                       verbose registration method call is recommended to better enable the internal\r
+                       documentation. Here is the complete set of members that you should pass to\r
+                       <literal>__PACKAGE__-&gt;register_method()</literal>:</simpara>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>method</literal> member specifies the name of the procedure in this module that is being registered as an OpenSRF method.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>api_name</literal> member specifies the invocable name of the OpenSRF method; by convention, the OpenSRF service name is used as the prefix.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The optional <literal>api_level</literal> member can be used for versioning the methods to allow the use of a deprecated API, but in practical use is always 1.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The optional <literal>argc</literal> member specifies the minimal number of arguments that the method expects.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The optional <literal>stream</literal> member, if set to any value, specifies that the method supports returning multiple values from a single call to subsequent requests. OpenSRF automatically creates a corresponding method with ".atomic" appended to its name that returns the complete set of results in a single request. Streaming methods are useful if you are returning hundreds of records and want to act on the results as they return.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The optional <literal>signature</literal> member is a hash that describes the method&#8217;s purpose, arguments, and return value.\r
+                       </simpara>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>desc</literal> member of the <literal>signature</literal> hash describes the method&#8217;s purpose.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>params</literal> member of the <literal>signature</literal> hash is an array of hashes in which each array element describes the corresponding method argument in order.\r
+                       </simpara>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>name</literal> member of the argument hash specifies the name of the argument.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>desc</literal> member of the argument hash describes the argument&#8217;s purpose.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>type</literal> member of the argument hash specifies the data type of the argument: for example, string, integer, boolean, number, array, or hash.\r
+                       </simpara>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>return</literal> member of the <literal>signature</literal> hash is a hash that describes the return value of the method.\r
+                       </simpara>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>desc</literal> member of the <literal>return</literal> hash describes the return value.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>type</literal> member of the <literal>return</literal> hash specifies the data type of the return value: for example, string, integer, boolean, number, array, or hash.\r
+                       </simpara>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+               </simplesect>\r
+       </section>\r
+       <section id="_evergreen_specific_opensrf_services">\r
+               <title>Evergreen-specific OpenSRF services</title>\r
+               <simpara>Evergreen is currently the primary showcase for the use of OpenSRF as an\r
+               application architecture. Evergreen 1.6.1 includes the following\r
+               set of OpenSRF services:</simpara>\r
+               <itemizedlist>\r
+               <listitem>\r
+               <simpara>\r
+               The <literal>open-ils.actor</literal> service supports common tasks for working with user\r
+                    accounts and libraries.\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               The <literal>open-ils.auth</literal> service supports authentication of Evergreen users.\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               The <literal>open-ils.booking</literal> service supports the management of reservations\r
+                   for bookable items.\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               The <literal>open-ils.cat</literal> service supports common cataloging tasks, such as\r
+                    creating, modifying, and merging bibliographic and authority records.\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               The <literal>open-ils.circ</literal> service supports circulation tasks such as checking\r
+                   out items and calculating due dates.\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               The <literal>open-ils.collections</literal> service supports tasks that assist collections\r
+                   agencies in contacting users with outstanding fines above a certain\r
+                   threshold.\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               The <literal>open-ils.cstore</literal> private service supports unrestricted access to\r
+                   Evergreen fieldmapper objects.\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               The <literal>open-ils.ingest</literal> private service supports tasks for importing\r
+                   data such as bibliographic and authority records.\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               The <literal>open-ils.pcrud</literal> service supports permission-based access to Evergreen\r
+                   fieldmapper objects.\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               The <literal>open-ils.penalty</literal> penalty service supports the calculation of\r
+                   penalties for users, such as being blocked from further borrowing, for\r
+                   conditions such as having too many items checked out or too many unpaid\r
+                   fines.\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               The <literal>open-ils.reporter</literal> service supports the creation and scheduling of\r
+                   reports.\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               The <literal>open-ils.reporter-store</literal> private service supports access to Evergreen\r
+                   fieldmapper objects for the reporting service.\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               The <literal>open-ils.search</literal> service supports searching across bibliographic\r
+                   records, authority records, serial records, Z39.50 sources, and ZIP codes.\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               The <literal>open-ils.storage</literal> private service supports a deprecated method of\r
+                   providing access to Evergreen fieldmapper objects. Implemented in Perl,\r
+                   this service has largely been replaced by the much faster C-based\r
+                   <literal>open-ils.cstore</literal> service.\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               The <literal>open-ils.supercat</literal> service supports transforms of MARC records into\r
+                   other formats, such as MODS, as well as providing Atom and RSS feeds and\r
+                   SRU access.\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               The <literal>open-ils.trigger</literal> private service supports event-based triggers for\r
+                   actions such as overdue and holds available notification emails.\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               The <literal>open-ils.vandelay</literal> service supports the import and export of batches of\r
+                   bibliographic and authority records.\r
+               </simpara>\r
+               </listitem>\r
+               </itemizedlist>\r
+               <simpara>Of some interest is that the <literal>open-ils.reporter-store</literal> and <literal>open-ils.cstore</literal>\r
+               services have identical implementations. Surfacing them as separate services\r
+               enables a deployer of Evergreen to ensure that the reporting service does not\r
+               interfere with the performance-critical <literal>open-ils.cstore</literal> service. One can also\r
+               direct the reporting service to a read-only database replica to, again, avoid\r
+               interference with <literal>open-ils.cstore</literal> which must write to the master database.</simpara>\r
+               <simpara>There are only a few significant services that are not built on OpenSRF in\r
+               Evergreen 1.6.0, such as the SIP and Z39.50 servers. These services implement\r
+               different protocols and build on existing daemon architectures (Simple2ZOOM\r
+               for Z39.50), but still rely on the other OpenSRF services to provide access\r
+               to the Evergreen data. The non-OpenSRF services are reasonably self-contained\r
+               and can be deployed on different servers to deliver the same sort of deployment\r
+               flexibility as OpenSRF services, but have the disadvantage of not being\r
+               integrated into the same configuration and control infrastructure as the\r
+               OpenSRF services.</simpara>\r
+       </section>\r
+       <section id="OpenSRF_attribution">\r
+               <simpara>This chapter was taken from Dan Scott's <emphasis>Easing gently into OpenSRF</emphasis> article, June, 2010.</simpara>\r
+       </section>\r
+</chapter>\r
diff --git a/1.6/development/datamodelsandaccess.xml b/1.6/development/datamodelsandaccess.xml
new file mode 100644 (file)
index 0000000..caadd3e
--- /dev/null
@@ -0,0 +1,760 @@
+<?xml version="1.0" encoding="UTF-8"?>\r
+<chapter xml:id="data_models_and_access" xmlns="http://docbook.org/ns/docbook" version="5.0" xml:lang="EN"\r
+    xmlns:xi="http://www.w3.org/2001/XInclude" xmlns:xlink="http://www.w3.org/1999/xlink">\r
+       <chapterinfo>\r
+       <title>Evergreen Data Models and Access</title>\r
+       </chapterinfo>\r
+       <section id="_database_schema">\r
+               <title>Database schema</title>\r
+               <simpara>The database schema is tied pretty tightly to PostgreSQL. Although PostgreSQL\r
+               adheres closely to ANSI SQL standards, the use of schemas, SQL functions\r
+               implemented in both plpgsql and plperl, and PostgreSQL&#8217;s native full-text\r
+               search would make it&#8230; challenging&#8230; to port to other database platforms.</simpara>\r
+               <simpara>A few common PostgreSQL interfaces for poking around the schema and\r
+               manipulating data are:</simpara>\r
+               <itemizedlist>\r
+               <listitem>\r
+               <simpara>\r
+               psql (the command line client)\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               pgadminIII (a GUI client).\r
+               </simpara>\r
+               </listitem>\r
+               </itemizedlist>\r
+               <simpara>Or you can read through the source files in Open-ILS/src/sql/Pg.</simpara>\r
+               <simpara>Let&#8217;s take a quick tour through the schemas, pointing out some highlights\r
+               and some key interdependencies:</simpara>\r
+               <itemizedlist>\r
+               <listitem>\r
+               <simpara>\r
+               actor.org_unit &#8594; asset.copy_location\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               actor.usr &#8594; actor.card\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               biblio.record_entry &#8594; asset.call_number &#8594; asset.copy\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               config.metabib_field &#8594; metabib.*_field_entry\r
+               </simpara>\r
+               </listitem>\r
+               </itemizedlist>\r
+       </section>\r
+       <section id="_database_access_methods">\r
+               <title>Database access methods</title>\r
+               <simpara>You could use direct access to the database via Perl DBI, JDBC, etc,\r
+               but Evergreen offers several database CRUD services for\r
+               creating / retrieving / updating / deleting data. These avoid tying\r
+               you too tightly to the current database schema and they funnel database\r
+               access through the same mechanism, rather than tying up connections\r
+               with other interfaces.</simpara>\r
+       </section>\r
+       <section id="_evergreen_interface_definition_language_idl">\r
+               <title>Evergreen Interface Definition Language (IDL)</title>\r
+               <simpara>Defines properties and required permissions for Evergreen classes.\r
+               To reduce network overhead, a given object is identified via a\r
+               class-hint and serialized as a JSON array of properties (no named properties).</simpara>\r
+               <simpara>As of 1.6, fields will be serialized in the order in which they appear\r
+               in the IDL definition file, and the is_new / is_changed / is_deleted\r
+               properties are automatically added. This has greatly reduced the size of\r
+               the <literal>fm_IDL.xml</literal> file and makes DRY people happier :)</simpara>\r
+               <itemizedlist>\r
+               <listitem>\r
+               <simpara>\r
+               &#8230; oils_persist:readonly tells us, if true, that the data lives in the database, but is pulled from the SELECT statement defined in the &lt;oils_persist:source_definition&gt; child element\r
+               </simpara>\r
+               </listitem>\r
+               </itemizedlist>\r
+               <simplesect id="_idl_basic_example_config_language_map">\r
+                       <title>IDL basic example (config.language_map)</title>\r
+                       <programlisting language="xml" linenumbering="unnumbered">&lt;class id="clm" controller="open-ils.cstore open-ils.pcrud"\r
+                               oils_obj:fieldmapper="config::language_map"\r
+                               oils_persist:tablename="config.language_map"\r
+                               reporter:label="Language Map" oils_persist:field_safe="true"&gt; <co id="dmCO5-1"/> <co id="dmCO5-2"/> <co id="dmCO5-3"/> <co id="dmCO5-4"/>\r
+                           &lt;fields oils_persist:primary="code" oils_persist:sequence=""&gt;  <co id="dmCO5-5"/>\r
+                               &lt;field reporter:label="Language Code" name="code"\r
+                                   reporter:selector="value" reporter:datatype="text"/&gt; <co id="dmCO5-6"/>\r
+                               &lt;field reporter:label="Language" name="value"\r
+                                   reporter:datatype="text" oils_persist:i18n="true"/&gt; <co id="dmCO5-7"/>\r
+                           &lt;/fields&gt;\r
+                           &lt;links/&gt;\r
+                           &lt;permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1"&gt; <co id="dmCO5-8"/>\r
+                               &lt;actions&gt;\r
+                                   &lt;create global_required="true" permission="CREATE_MARC_CODE"&gt; <co id="dmCO5-9"/>\r
+                                   &lt;retrieve global_required="true"\r
+                                       permission="CREATE_MARC_CODE UPDATE_MARC_CODE DELETE_MARC_CODE"&gt;\r
+                                   &lt;update global_required="true" permission="UPDATE_MARC_CODE"&gt;\r
+                                   &lt;delete global_required="true" permission="DELETE_MARC_CODE"&gt;\r
+                               &lt;/actions&gt;\r
+                           &lt;/permacrud&gt;\r
+                       &lt;/class&gt;</programlisting>\r
+                       <calloutlist>\r
+                       <callout arearefs="dmCO5-1">\r
+                       <simpara>\r
+                       The <literal>class</literal> element defines the attributes and permissions for classes,\r
+                       and relationships between classes.\r
+                       </simpara>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>id</literal> attribute on the <literal>class</literal> element defines the class hint that is\r
+                       used everywhere in Evergreen.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>controller</literal> attribute defines the OpenSRF\r
+                       services that provide access to the data for the class objects.\r
+                       </simpara>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+                       </callout>\r
+                       <callout arearefs="dmCO5-2">\r
+                       <simpara>\r
+                       The <literal>oils_obj::fieldmapper</literal> attribute defines the name of the class that\r
+                       is generated by <literal>OpenILS::Utils::Fieldmapper</literal>.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="dmCO5-3">\r
+                       <simpara>\r
+                       The <literal>oils_persist:tablename</literal> attribute defines the name of the table\r
+                       that contains the data for the class objects.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="dmCO5-4">\r
+                       <simpara>\r
+                       The reporter interface uses <literal>reporter:label</literal> attribute values in\r
+                       the source list to provide meaningful class and attribute names. The\r
+                       <literal>open-ils.fielder</literal> service generates a set of methods that provide direct\r
+                       access to the classes for which <literal>oils_persist:field_safe</literal> is <literal>true</literal>. For\r
+                       example,\r
+                       </simpara>\r
+                       <screen>srfsh# request open-ils.fielder open-ils.fielder.clm.atomic \\r
+                           {"query":{"code":{"=":"eng"}}}\r
+\r
+                       Received Data: [\r
+                         {\r
+                           "value":"English",\r
+                           "code":"eng"\r
+                         }\r
+                       ]</screen>\r
+                       </callout>\r
+                       <callout arearefs="dmCO5-5">\r
+                       <simpara>\r
+                       The <literal>fields</literal> element defines the list of fields for the class.\r
+                       </simpara>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>oils_persist:primary</literal> attribute defines the column that acts as\r
+                       the primary key for the table.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>oils_persist:sequence</literal> attribute holds the name of the database\r
+                       sequence.\r
+                       </simpara>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+                       </callout>\r
+                       <callout arearefs="dmCO5-6">\r
+                       <simpara>\r
+                       Each <literal>field</literal> element defines one property of the class.\r
+                       </simpara>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>name</literal> attribute defines the getter/setter method name for the field.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>reporter:label</literal> attribute defines the attribute name as used in\r
+                       the reporter interface.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>reporter:selector</literal> attribute defines the field used in the reporter\r
+                       filter interface to provide a selectable list. This gives the user a more\r
+                       meaningful access point than the raw numeric ID or abstract code.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>reporter:datatype</literal> attribute defines the type of data held by\r
+                       this property for the purposes of the reporter.\r
+                       </simpara>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+                       </callout>\r
+                       <callout arearefs="dmCO5-7">\r
+                       <simpara>\r
+                       The <literal>oils_persist:i18n</literal> attribute, when <literal>true</literal>, means that\r
+                       translated values for the field&#8217;s contents may be accessible in\r
+                       different locales.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="dmCO5-8">\r
+                       <simpara>\r
+                       The <literal>permacrud</literal> element defines the permissions (if any) required\r
+                       to <emphasis role="strong">c</emphasis>reate, <emphasis role="strong">r</emphasis>etrieve, <emphasis role="strong">u</emphasis>pdate, and <emphasis role="strong">d</emphasis>elete data for this\r
+                       class. <literal>open-ils.permacrud</literal> must be defined as a controller for the class\r
+                       for the permissions to be applied.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="dmCO5-9">\r
+                       <simpara>\r
+                       Each action requires one or more <literal>permission</literal> values that the\r
+                       user must possess to perform the action.\r
+                       </simpara>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       If the <literal>global_required</literal> attribute is <literal>true</literal>, then the user must\r
+                       have been granted that permission globally (depth = 0) to perform\r
+                       the action.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       The <literal>context_field</literal> attribute denotes the <literal>&lt;field&gt;</literal> that identifies\r
+                       the org_unit at which the user must have the pertinent permission.\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       An action element may contain a <literal>&lt;context_field&gt;</literal> element that\r
+                       defines the linked class (identified by the <literal>link</literal> attribute) and\r
+                       the field in the linked class that identifies the org_unit where\r
+                       the permission must be held.\r
+                       </simpara>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       If the <literal>&lt;context_field&gt;</literal> element contains a <literal>jump</literal> attribute,\r
+                       then it defines a link to a link to a class with a field identifying\r
+                       the org_unit where the permission must be held.\r
+                       </simpara>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+                       </callout>\r
+                       </calloutlist>\r
+               </simplesect>\r
+               <simplesect id="_reporter_data_types_and_their_possible_values">\r
+                       <title>Reporter data types and their possible values</title>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       <literal>bool</literal>: Boolean <literal>true</literal> or <literal>false</literal>\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       <literal>id</literal>: ID of the row in the database\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       <literal>int</literal>: integer value\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       <literal>interval</literal>: PostgreSQL time interval\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       <literal>link</literal>: link to another class, as defined in the <literal>&lt;links&gt;</literal>\r
+                       element of the class definition\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       <literal>money</literal>: currency amount\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       <literal>org_unit</literal>: list of org_units\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       <literal>text</literal>: text value\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       <literal>timestamp</literal>: PostgreSQL timestamp\r
+                       </simpara>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+               </simplesect>\r
+               <simplesect id="_idl_example_with_linked_fields_actor_workstation">\r
+                       <title>IDL example with linked fields (actor.workstation)</title>\r
+                       <simpara>Just as tables often include columns with foreign keys that point\r
+                       to values stored in the column of a different table, IDL classes\r
+                       can contain fields that link to fields in other classes. The <literal>&lt;links&gt;</literal>\r
+                       element defines which fields link to fields in other classes, and\r
+                       the nature of the relationship:</simpara>\r
+                       <programlisting language="xml" linenumbering="unnumbered">&lt;class id="aws" controller="open-ils.cstore"\r
+                               oils_obj:fieldmapper="actor::workstation"\r
+                               oils_persist:tablename="actor.workstation"\r
+                               reporter:label="Workstation"&gt;\r
+                           &lt;fields oils_persist:primary="id"\r
+                                   oils_persist:sequence="actor.workstation_id_seq"&gt;\r
+                               &lt;field reporter:label="Workstation ID" name="id"\r
+                                       reporter:datatype="id"/&gt;\r
+                               &lt;field reporter:label="Workstation Name" name="name"\r
+                                       reporter:datatype="text"/&gt;\r
+                               &lt;field reporter:label="Owning Library" name="owning_lib"\r
+                                       reporter:datatype="org_unit"/&gt;\r
+                               &lt;field reporter:label="Circulations" name="circulations"\r
+                                       oils_persist:virtual="true" reporter:datatype="link"/&gt; <co id="dmCO6-1"/>\r
+                           &lt;/fields&gt;\r
+                           &lt;links&gt;  <co id="dmCO6-2"/>\r
+                               &lt;link field="owning_lib" reltype="has_a" key="id"\r
+                                       map="" class="aou"/&gt;  <co id="dmCO6-3"/>\r
+                               &lt;link field="circulations" reltype="has_many" key="workstation"\r
+                                       map="" class="circ"/&gt;\r
+                               &lt;link field="circulation_checkins" reltype="has_many"\r
+                                       key="checkin_workstation" map="" class="circ"/&gt;\r
+                           &lt;/links&gt;\r
+                       &lt;/class&gt;</programlisting>\r
+                       <calloutlist>\r
+                       <callout arearefs="dmCO6-1">\r
+                       <simpara>\r
+                       This field includes an <literal>oils_persist:virtual</literal> attribute with the value of\r
+                       <literal>true</literal>, meaning that the linked class <literal>circ</literal> is a virtual class.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="dmCO6-2">\r
+                       <simpara>\r
+                       The <literal>&lt;links&gt;</literal> element contains 0 or more <literal>&lt;link&gt;</literal> elements.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="dmCO6-3">\r
+                       <simpara>\r
+                       Each <literal>&lt;link&gt;</literal> element defines the field (<literal>field</literal>) that links to a different\r
+                       class (<literal>class</literal>), the relationship (<literal>rel_type</literal>) between this field and the target\r
+                       field (<literal>key</literal>). If the field in this class links to a virtual class, the (<literal>map</literal>)\r
+                       attribute defines the field in the target class that returns a list of matching\r
+                       objects for each object in this class.\r
+                       </simpara>\r
+                       </callout>\r
+                       </calloutlist>\r
+               </simplesect>\r
+       </section>\r
+       <section id="open_ils_cstore_literal_data_access_interfaces">\r
+               <title><literal>open-ils.cstore</literal> data access interfaces</title>\r
+               <simpara>For each class documented in the IDL, the <literal>open-ils.cstore</literal> service\r
+               automatically generates a set of data access methods, based on the\r
+               <literal>oils_persist:tablename</literal> class attribute.</simpara>\r
+               <simpara>For example, for the class hint <literal>clm</literal>, cstore generates the following\r
+               methods with the <literal>config.language_map</literal> qualifer:</simpara>\r
+               <itemizedlist>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.cstore.direct.config.language_map.id_list {"code" { "like": "e%" } }</literal>\r
+               </simpara>\r
+               <simpara>Retrieves a list composed only of the IDs that match the query.</simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.cstore.direct.config.language_map.retrieve "eng"</literal>\r
+               </simpara>\r
+               <simpara>Retrieves the object that matches a specific ID.</simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.cstore.direct.config.language_map.search {"code" : "eng"}</literal>\r
+               </simpara>\r
+               <simpara>Retrieves a list of objects that match the query.</simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.cstore.direct.config.language_map.create &lt;_object_&gt;</literal>\r
+               </simpara>\r
+               <simpara>Creates a new object from the passed in object.</simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.cstore.direct.config.language_map.update &lt;_object_&gt;</literal>\r
+               </simpara>\r
+               <simpara>Updates the object that has been passed in.</simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.cstore.direct.config.language_map.delete "eng"</literal>\r
+               </simpara>\r
+               <simpara>Deletes the object that matches the query.</simpara>\r
+               </listitem>\r
+               </itemizedlist>\r
+       </section>\r
+       <section id="_open_ils_pcrud_data_access_interfaces">\r
+               <title>open-ils.pcrud data access interfaces</title>\r
+               <simpara>For each class documented in the IDL, the <literal>open-ils.pcrud</literal> service\r
+               automatically generates a set of data access methods, based on the\r
+               <literal>oils_persist:tablename</literal> class attribute.</simpara>\r
+               <simpara>For example, for the class hint <literal>clm</literal>, <literal>open-ils.pcrud</literal> generates the following\r
+               methods that parallel the <literal>open-ils.cstore</literal> interface:</simpara>\r
+               <itemizedlist>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.pcrud.id_list.clm &lt;_authtoken_&gt;, { "code": { "like": "e%" } }</literal>\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.pcrud.retrieve.clm &lt;_authtoken_&gt;, "eng"</literal>\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.pcrud.search.clm &lt;_authtoken_&gt;, { "code": "eng" }</literal>\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.pcrud.create.clm &lt;_authtoken_&gt;, &lt;_object_&gt;</literal>\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.pcrud.update.clm &lt;_authtoken_&gt;, &lt;_object_&gt;</literal>\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.pcrud.delete.clm &lt;_authtoken_&gt;, "eng"</literal>\r
+               </simpara>\r
+               </listitem>\r
+               </itemizedlist>\r
+       </section>\r
+       <section id="_transaction_and_savepoint_control">\r
+               <title>Transaction and savepoint control</title>\r
+               <simpara>Both <literal>open-ils.cstore</literal> and <literal>open-ils.pcrud</literal> enable you to control database transactions\r
+               to ensure that a set of operations either all succeed, or all fail,\r
+               atomically:</simpara>\r
+               <itemizedlist>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.cstore.transaction.begin</literal>\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.cstore.transaction.commit</literal>\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.cstore.transaction.rollback</literal>\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.pcrud.transaction.begin</literal>\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.pcrud.transaction.commit</literal>\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.pcrud.transaction.rollback</literal>\r
+               </simpara>\r
+               </listitem>\r
+               </itemizedlist>\r
+               <simpara>At a more granular level, <literal>open-ils.cstore</literal> and <literal>open-ils.pcrud</literal> enable you to set database\r
+               savepoints to ensure that a set of operations either all succeed, or all\r
+               fail, atomically, within a given transaction:</simpara>\r
+               <itemizedlist>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.cstore.savepoint.begin</literal>\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.cstore.savepoint.commit</literal>\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.cstore.savepoint.rollback</literal>\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.pcrud.savepoint.begin</literal>\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.pcrud.savepoint.commit</literal>\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               <literal>open-ils.pcrud.savepoint.rollback</literal>\r
+               </simpara>\r
+               </listitem>\r
+               </itemizedlist>\r
+               <simpara>Transactions and savepoints must be performed within a stateful\r
+               connection to the <literal>open-ils.cstore</literal> and <literal>open-ils.pcrud</literal> services.\r
+               In <literal>srfsh</literal>, you can open a stateful connection using the <literal>open</literal>\r
+               command, and then close the stateful connection using the <literal>close</literal>\r
+               command - for example:</simpara>\r
+               <screen>srfsh# open open-ils.cstore\r
+               ... perform various transaction-related work\r
+               srfsh# close open-ils.cstore</screen>\r
+               <simplesect id="_json_queries">\r
+                       <title>JSON Queries</title>\r
+                       <simpara>Beyond simply retrieving objects by their ID using the <literal>\*.retrieve</literal>\r
+                       methods, you can issue queries against the <literal>\*.delete</literal> and <literal>\*.search</literal>\r
+                       methods using JSON to filter results with simple or complex search\r
+                       conditions.</simpara>\r
+                       <simpara>For example, to generate a list of barcodes that are held in a\r
+                       copy location that allows holds and is visible in the OPAC:</simpara>\r
+                       <programlisting language="sh" linenumbering="unnumbered">srfsh# request open-ils.cstore open-ils.cstore.json_query  <co id="dmCO7-1"/>\r
+                           {"select": {"acp":["barcode"], "acpl":["name"]}, <co id="dmCO7-2"/>\r
+                            "from":   {"acp":"acpl"},   <co id="dmCO7-3"/>\r
+                            "where":  [     <co id="dmCO7-4"/>\r
+                                {"+acpl": "holdable"},   <co id="dmCO7-5"/>\r
+                                {"+acpl": "opac_visible"}     <co id="dmCO7-6"/>\r
+                            ]}\r
+\r
+                       Received Data: {\r
+                         "barcode":"BARCODE1",\r
+                         "name":"Stacks"\r
+                       }\r
+\r
+                       Received Data: {\r
+                         "barcode":"BARCODE2",\r
+                         "name":"Stacks"\r
+                       }</programlisting>\r
+                       <calloutlist>\r
+                       <callout arearefs="dmCO7-1">\r
+                       <simpara>\r
+                       Invoke the <literal>json_query</literal> service.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="dmCO7-2">\r
+                       <simpara>\r
+                       Select the <literal>barcode</literal> field from the <literal>acp</literal> class and the <literal>name</literal>\r
+                       field from the <literal>acpl</literal> class.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="dmCO7-3">\r
+                       <simpara>\r
+                       Join the <literal>acp</literal> class to the <literal>acpl</literal> class based on the linked field\r
+                       defined in the IDL.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="dmCO7-4">\r
+                       <simpara>\r
+                       Add a <literal>where</literal> clause to filter the results. We have more than one\r
+                       condition beginning with the same key, so we wrap the conditions inside\r
+                       an array.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="dmCO7-5">\r
+                       <simpara>\r
+                       The first condition tests whether the boolean value of the <literal>holdable</literal>\r
+                       field on the <literal>acpl</literal> class is true.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="dmCO7-6">\r
+                       <simpara>\r
+                       The second condition tests whether the boolean value of the\r
+                       <literal>opac_visible</literal> field on the <literal>acpl</literal> class is true.\r
+                       </simpara>\r
+                       </callout>\r
+                       </calloutlist>\r
+                       <simpara>For thorough coverage of the breadth of support offered by JSON\r
+                       query syntax, see <ulink url="http://open-ils.org/dokuwiki/doku.php?id=documentation:technical:jsontutorial">JSON Queries: A Tutorial</ulink>.</simpara>\r
+               </simplesect>\r
+               <simplesect id="_fleshing_linked_objects">\r
+                       <title>Fleshing linked objects</title>\r
+                       <simpara>A simplistic approach to retrieving a set of objects that are linked to\r
+                       an object that you are retrieving - for example, a set of call numbers\r
+                       linked to the barcodes that a given user has borrowed - would be to:\r
+                         1. Retrieve the list of circulation objects (<literal>circ</literal> class)\r
+                       for a given user (<literal>usr</literal> class).\r
+                         2. For each circulation object, look up the target copy (<literal>target_copy</literal>\r
+                       field, linked to the <literal>acp</literal> class).\r
+                         3. For each copy, look up the call number for that copy (<literal>call_number</literal>\r
+                       field, linked to the <literal>acn</literal> class).</simpara>\r
+                       <simpara>However, this would result in potentially hundreds of round-trip\r
+                       queries from the client to the server. Even with low-latency connections,\r
+                       the network overhead would be considerable. So, built into the <literal>open-ils.cstore</literal> and\r
+                       <literal>open-ils.pcrud</literal> access methods is the ability to <emphasis>flesh</emphasis> linked fields -\r
+                       that is, rather than return an identifier to a given linked field,\r
+                       the method can return the entire object as part of the initial response.</simpara>\r
+                       <simpara>Most of the interfaces that return class instances from the IDL offer the\r
+                       ability to flesh returned fields. For example, the\r
+                       <literal>open-ils.cstore.direct.\*.retrieve</literal> methods allow you to specify a\r
+                       JSON structure defining the fields you wish to flesh in the returned object.</simpara>\r
+                       <formalpara><title>Fleshing fields in objects returned by <literal>open-ils.cstore</literal></title><para>\r
+                       <programlisting language="sh" linenumbering="unnumbered">srfsh# request open-ils.cstore open-ils.cstore.direct.asset.copy.retrieve 1, \\r
+                           {\r
+                               "flesh": 1,    <co id="dmCO8-1"/>\r
+                               "flesh_fields": {   <co id="dmCO8-2"/>\r
+                                   "acp": ["location"]\r
+                               }\r
+                           }</programlisting>\r
+                       </para></formalpara>\r
+                       <calloutlist>\r
+                       <callout arearefs="dmCO8-1">\r
+                       <simpara>\r
+                       The <literal>flesh</literal> argument is the depth at which objects should be fleshed.\r
+                       For example, to flesh out a field that links to another object that includes\r
+                       a field that links to another object, you would specify a depth of 2.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="dmCO8-2">\r
+                       <simpara>\r
+                       The <literal>flesh_fields</literal> argument contains a list of objects with the fields\r
+                       to flesh for each object.\r
+                       </simpara>\r
+                       </callout>\r
+                       </calloutlist>\r
+                       <simpara>Let&#8217;s flesh things a little deeper. In addition to the copy location,\r
+                       let&#8217;s also flesh the call number attached to the copy, and then flesh\r
+                       the bibliographic record attached to the call number.</simpara>\r
+                       <formalpara><title>Fleshing fields in fields of objects returned by <literal>open-ils.cstore</literal></title><para>\r
+                       <programlisting language="java" linenumbering="unnumbered">request open-ils.cstore open-ils.cstore.direct.asset.copy.retrieve 1, \\r
+                           {\r
+                               "flesh": 2,\r
+                               "flesh_fields": {\r
+                                   "acp": ["location", "call_number"],\r
+                                   "acn": ["record"]\r
+                                }\r
+                           }</programlisting>\r
+                       </para></formalpara>\r
+               </simplesect>\r
+       </section>\r
+       <section id="_adding_an_idl_entry_for_resolverresolver">\r
+               <title>Adding an IDL entry for ResolverResolver</title>\r
+               <simpara>Most OpenSRF methods in Evergreen define their object interface in the\r
+               IDL. Without an entry in the IDL, the prospective caller of a given\r
+               method is forced to either call the method and inspect the returned\r
+               contents, or read the source to work out the structure of the JSON\r
+               payload. At this stage of the tutorial, we have not defined an entry\r
+               in the IDL to represent the object returned by the\r
+               <literal>open-ils.resolver.resolve_holdings</literal> method. It is time to complete\r
+               that task.</simpara>\r
+               <simpara>The <literal>open-ils.resolver</literal> service is unlike many of the other classes\r
+               defined in the IDL because its data is not stored in the Evergreen\r
+               database. Instead, the data is requested from an external Web service\r
+               and only temporarily cached in <literal>memcached</literal>. Fortunately, the IDL\r
+               enables us to represent this kind of class by setting the\r
+               <literal>oils_persist:virtual</literal> class attribute to <literal>true</literal>.</simpara>\r
+               <simpara>So, let&#8217;s add an entry to the IDL for the <literal>open-ils.resolver.resolve_holdings</literal>\r
+               service:</simpara>\r
+               <programlisting language="xml" linenumbering="unnumbered"></programlisting>\r
+               <simpara>And let&#8217;s make <literal>ResolverResolver.pm</literal> return an array composed of our new\r
+               <literal>rhr</literal> classes rather than raw JSON objects:</simpara>\r
+               <programlisting language="perl" linenumbering="unnumbered"></programlisting>\r
+               <simpara>Once we add the new entry to the IDL and copy the revised <literal>ResolverResolver.pm</literal>\r
+               Perl module to <literal>/openils/lib/perl5/OpenILS/Application/</literal>, we need to:</simpara>\r
+               <orderedlist numeration="arabic">\r
+               <listitem>\r
+               <simpara>\r
+               Copy the updated IDL to both the <literal>/openils/conf/</literal> and\r
+               <literal>/openils/var/web/reports/</literal> directories. The Dojo approach to\r
+               parsing the IDL uses the IDL stored in the reports directory.\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               Restart the Perl services to make the new IDL visible to the services\r
+               and refresh the <literal>open-ils.resolver</literal> implementation\r
+               </simpara>\r
+               </listitem>\r
+               <listitem>\r
+               <simpara>\r
+               Rerun <literal>/openils/bin/autogen.sh</literal> to regenerate the JavaScript versions\r
+               of the IDL required by the HTTP translator and gateway.\r
+               </simpara>\r
+               </listitem>\r
+               </orderedlist>\r
+               <simpara>We also need to adjust our JavaScript client to use the nifty new\r
+               objects that <literal>open-ils.resolver.resolve_holdings</literal> now returns.\r
+               The best approach is to use the support in Evergreen&#8217;s Dojo extensions\r
+               to generate the JavaScript classes directly from the IDL XML file.</simpara>\r
+               <formalpara><title>Accessing classes defined in the IDL via Fieldmapper</title><para>\r
+               <programlisting language="html" linenumbering="unnumbered"></programlisting>\r
+               </para></formalpara>\r
+               <calloutlist>\r
+               <callout arearefs="">\r
+               <simpara>\r
+               Load the Dojo core.\r
+               </simpara>\r
+               </callout>\r
+               <callout arearefs="">\r
+               <simpara>\r
+               <literal>fieldmapper.AutoIDL</literal> reads <literal>/openils/var/reports/fm_IDL.xml</literal> to\r
+               generate a list of class properties.\r
+               </simpara>\r
+               </callout>\r
+               <callout arearefs="">\r
+               <simpara>\r
+               <literal>fieldmapper.dojoData</literal> seems to provide a store for Evergreen data\r
+               accessed via Dojo.\r
+               </simpara>\r
+               </callout>\r
+               <callout arearefs="">\r
+               <simpara>\r
+               <literal>fieldmapper.Fieldmapper</literal> converts the list of class properties into\r
+               actual classes.\r
+               </simpara>\r
+               </callout>\r
+               <callout arearefs="">\r
+               <simpara>\r
+               <literal>fieldmapper.standardRequest</literal> invokes an OpenSRF method and returns\r
+               an array of objects.\r
+               </simpara>\r
+               </callout>\r
+               <callout arearefs="">\r
+               <simpara>\r
+               The first argument to <literal>fieldmapper.standardRequest</literal> is an array\r
+               containing the OpenSRF service name and method name.\r
+               </simpara>\r
+               </callout>\r
+               <callout arearefs="">\r
+               <simpara>\r
+               The second argument to <literal>fieldmapper.standardRequest</literal> is an array\r
+               containing the arguments to pass to the OpenSRF method.\r
+               </simpara>\r
+               </callout>\r
+               <callout arearefs="">\r
+               <simpara>\r
+               As Fieldmapper has instantiated the returned objects based on their\r
+               class hints, we can invoke getter/setter methods on the objects.\r
+               </simpara>\r
+               </callout>\r
+               </calloutlist>\r
+       </section>\r
+       <section id="dm_attribution">\r
+               <simpara>This chapter was taken from Dan Scott's <emphasis>Developer Workshop</emphasis>, February 2010.</simpara>\r
+       </section>\r
+</chapter>\r
diff --git a/1.6/development/introduction_to_sql.xml b/1.6/development/introduction_to_sql.xml
new file mode 100644 (file)
index 0000000..e6eceb3
--- /dev/null
@@ -0,0 +1,2021 @@
+<?xml version="1.0" encoding="UTF-8"?>\r
+<chapter xml:id="intro_to_sql" xmlns="http://docbook.org/ns/docbook" version="5.0" xml:lang="EN"\r
+    xmlns:xi="http://www.w3.org/2001/XInclude" xmlns:xlink="http://www.w3.org/1999/xlink">\r
+       <chapterinfo>\r
+               <title>Introduction to SQL for Evergreen Administrators</title>\r
+       </chapterinfo>\r
+       <section id="intro_to_databases">\r
+                       <title>Introduction to SQL Databases</title>\r
+               <simplesect>\r
+                       <title>Introduction</title>\r
+                       <simpara>Over time, the SQL database has become the standard method of storing,\r
+                       retrieving, and processing raw data for applications. Ranging from embedded\r
+                       databases such as SQLite and Apache Derby, to enterprise databases such as\r
+                       Oracle and IBM DB2, any SQL database offers basic advantages to application\r
+                       developers such as standard interfaces (Structured Query Language (SQL), Java\r
+                       Database Connectivity (JDBC), Open Database Connectivity (ODBC), Perl Database\r
+                       Independent Interface (DBI)), a standard conceptual model of data (tables,\r
+                       fields, relationships, constraints, etc), performance in storing and retrieving\r
+                       data, concurrent access, etc.</simpara>\r
+                       <simpara>Evergreen is built on PostgreSQL, an open source SQL database that began as\r
+                       <literal>POSTGRES</literal> at the University of California at Berkeley in 1986 as a research\r
+                       project led by Professor Michael Stonebraker. A SQL interface was added to a\r
+                       fork of the original POSTGRES Berkelely code in 1994, and in 1996 the project\r
+                       was renamed PostgreSQL.</simpara>\r
+               </simplesect>\r
+               <simplesect id="_tables">\r
+                       <title>Tables</title>\r
+                       <simpara>The table is the cornerstone of a SQL database. Conceptually, a database table\r
+                       is similar to a single sheet in a spreadsheet: every table has one or more\r
+                       columns, with each row in the table containing values for each column. Each\r
+                       column in a table defines an attribute corresponding to a particular data type.</simpara>\r
+                       <simpara>We&#8217;ll insert a row into a table, then display the resulting contents. Don&#8217;t\r
+                       worry if the INSERT statement is completely unfamiliar, we&#8217;ll talk more about\r
+                       the syntax of the insert statement later.</simpara>\r
+                       <formalpara><title><literal>actor.usr_note</literal> database table</title><para>\r
+                       <programlisting language="sql" linenumbering="unnumbered">evergreen=# INSERT INTO actor.usr_note (usr, creator, pub, title, value)\r
+                           VALUES (1, 1, TRUE, 'Who is this guy?', 'He''s the administrator!');\r
+\r
+                       evergreen=# select id, usr, creator, pub, title, value from actor.usr_note;\r
+                        id | usr | creator | pub |      title       |          value\r
+                       ----+-----+---------+-----+------------------+-------------------------\r
+                         1 |   1 |       1 | t   | Who is this guy? | He's the administrator!\r
+                       (1 rows)</programlisting>\r
+                       </para></formalpara>\r
+                       <simpara>PostgreSQL supports table inheritance, which lets you define tables that\r
+                       inherit the column definitions of a given parent table. A search of the data in\r
+                       the parent table includes the data in the child tables. Evergreen uses table\r
+                       inheritance: for example, the <literal>action.circulation</literal> table is a child of the\r
+                       <literal>money.billable_xact</literal> table, and the <literal>money.*_payment</literal> tables all inherit from\r
+                       the <literal>money.payment</literal> parent table.</simpara>\r
+               </simplesect>\r
+               <simplesect id="_schemas">\r
+                       <title>Schemas</title>\r
+                       <simpara>PostgreSQL, like most SQL databases, supports the use of schema names to group\r
+                       collections of tables and other database objects together. You might think of\r
+                       schemas as namespaces if you&#8217;re a programmer; or you might think of the schema\r
+                       / table / column relationship like the area code / exchange / local number\r
+                       structure of a telephone number.</simpara>\r
+                       <table\r
+                       frame="all"\r
+                       rowsep="1" colsep="1"\r
+                       >\r
+                       <title>Examples: database object names</title>\r
+                       <?dbhtml table-width="80%"?>\r
+                       <?dbfo table-width="80%"?>\r
+                       <tgroup cols="4">\r
+                       <colspec colname="col_1" colwidth="85*"/>\r
+                       <colspec colname="col_2" colwidth="85*"/>\r
+                       <colspec colname="col_3" colwidth="85*"/>\r
+                       <colspec colname="col_4" colwidth="85*"/>\r
+                       <thead>\r
+                       <row>\r
+                       <entry align="left" valign="top">Full name </entry>\r
+                       <entry align="left" valign="top">Schema name </entry>\r
+                       <entry align="left" valign="top">Table name </entry>\r
+                       <entry align="left" valign="top">Field name</entry>\r
+                       </row>\r
+                       </thead>\r
+                       <tbody>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara>actor.usr_note.title</simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>actor</simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>usr_note</simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>title</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara>biblio.record_entry.marc</simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>biblio</simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>record_entry</simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>marc</simpara></entry>\r
+                       </row>\r
+                       </tbody>\r
+                       </tgroup>\r
+                       </table>\r
+                       <simpara>The default schema name in PostgreSQL is <literal>public</literal>, so if you do not specify a\r
+                       schema name when creating or accessing a database object, PostgreSQL will use\r
+                       the <literal>public</literal> schema. As a result, you might not find the object that you&#8217;re\r
+                       looking for if you don&#8217;t use the appropriate schema.</simpara>\r
+                       <formalpara><title>Example: Creating a table without a specific schema</title><para>\r
+                       <programlisting language="sql" linenumbering="unnumbered">evergreen=# CREATE TABLE foobar (foo TEXT, bar TEXT);\r
+                       CREATE TABLE\r
+                       evergreen=# \d foobar\r
+                          Table "public.foobar"\r
+                        Column | Type | Modifiers\r
+                       --------+------+-----------\r
+                        foo    | text |\r
+                        bar    | text |</programlisting>\r
+                       </para></formalpara>\r
+                       <formalpara><title>Example: Trying to access a unqualified table outside of the public schema</title><para>\r
+                       <programlisting language="sql" linenumbering="unnumbered">evergreen=# SELECT * FROM usr_note;\r
+                       ERROR:  relation "usr_note" does not exist\r
+                       LINE 1: SELECT * FROM usr_note;\r
+                                             ^</programlisting>\r
+                       </para></formalpara>\r
+                       <simpara>Evergreen uses schemas to organize all of its tables with mostly intuitive,\r
+                       if short, schema names. Here&#8217;s the current (as of 2010-01-03) list of schemas\r
+                       used by Evergreen:</simpara>\r
+                       <table\r
+                       frame="all"\r
+                       rowsep="1" colsep="1"\r
+                       >\r
+                       <title>Evergreen schema names</title>\r
+                       <?dbhtml table-width="80%"?>\r
+                       <?dbfo table-width="80%"?>\r
+                       <tgroup cols="2">\r
+                       <colspec colname="col_1" colwidth="170*"/>\r
+                       <colspec colname="col_2" colwidth="170*"/>\r
+                       <thead>\r
+                       <row>\r
+                       <entry align="left" valign="top">Schema name </entry>\r
+                       <entry align="left" valign="top">Description</entry>\r
+                       </row>\r
+                       </thead>\r
+                       <tbody>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>acq</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Acquisitions</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>action</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Circulation actions</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>action_trigger</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Event mechanisms</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>actor</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Evergreen users and organization units</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>asset</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Call numbers and copies</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>auditor</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Track history of changes to selected tables</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>authority</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Authority records</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>biblio</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Bibliographic records</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>booking</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Resource bookings</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>config</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Evergreen configurable options</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>container</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Buckets for records, call numbers, copies, and users</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>extend_reporter</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Extra views for report definitions</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>metabib</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Metadata about bibliographic records</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>money</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Fines and bills</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>offline</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Offline transactions</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>permission</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>User permissions</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>query</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Stored SQL statements</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>reporter</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Report definitions</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>search</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Search functions</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>serial</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Serial MFHD records</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>stats</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Convenient views of circulation and asset statistics</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>vandelay</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>MARC batch importer and exporter</simpara></entry>\r
+                       </row>\r
+                       </tbody>\r
+                       </tgroup>\r
+                       </table>\r
+                       <note><simpara>The term <emphasis>schema</emphasis> has two meanings in the world of SQL databases. We have\r
+                       discussed the schema as a conceptual grouping of tables and other database\r
+                       objects within a given namespace; for example, "the <emphasis role="strong">actor</emphasis> schema contains the\r
+                       tables and functions related to users and organizational units". Another common\r
+                       usage of <emphasis>schema</emphasis> is to refer to the entire data model for a given database;\r
+                       for example, "the Evergreen database schema".</simpara></note>\r
+               </simplesect>\r
+               <simplesect id="_columns">\r
+                       <title>Columns</title>\r
+                       <simpara>Each column definition consists of:</simpara>\r
+                       <itemizedlist>\r
+                       <listitem>\r
+                       <simpara>\r
+                       a data type\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       (optionally) a default value to be used whenever a row is inserted that\r
+                            does not contain a specific value\r
+                       </simpara>\r
+                       </listitem>\r
+                       <listitem>\r
+                       <simpara>\r
+                       (optionally) one or more constraints on the values beyond data type\r
+                       </simpara>\r
+                       </listitem>\r
+                       </itemizedlist>\r
+                       <simpara>Although PostgreSQL supports dozens of data types, Evergreen makes our life\r
+                       easier by only using a handful.</simpara>\r
+                       <table\r
+                       frame="all"\r
+                       rowsep="1" colsep="1"\r
+                       >\r
+                       <title>PostgreSQL data types used by Evergreen</title>\r
+                       <?dbhtml table-width="90%"?>\r
+                       <?dbfo table-width="90%"?>\r
+                       <tgroup cols="3">\r
+                       <colspec colname="col_1" colwidth="77*"/>\r
+                       <colspec colname="col_2" colwidth="77*"/>\r
+                       <colspec colname="col_3" colwidth="230*"/>\r
+                       <thead>\r
+                       <row>\r
+                       <entry align="left" valign="top">Type name   </entry>\r
+                       <entry align="left" valign="top">Description    </entry>\r
+                       <entry align="left" valign="top">Limits</entry>\r
+                       </row>\r
+                       </thead>\r
+                       <tbody>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>INTEGER</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Medium integer</simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>-2147483648 to +2147483647</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>BIGINT</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Large integer</simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>-9223372036854775808 to 9223372036854775807</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>SERIAL</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Sequential integer</simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>1 to 2147483647</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>BIGSERIAL</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Large sequential integer</simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>1 to 9223372036854775807</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>TEXT</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Variable length character data</simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Unlimited length</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>BOOL</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Boolean</simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>TRUE or FALSE</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>TIMESTAMP WITH TIME ZONE</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Timestamp</simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>4713 BC to 294276 AD</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>TIME</literal></simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Time</simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Expressed in HH:MM:SS</simpara></entry>\r
+                       </row>\r
+                       <row>\r
+                       <entry align="left" valign="top"><simpara><literal>NUMERIC</literal>(precision, scale)</simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Decimal</simpara></entry>\r
+                       <entry align="left" valign="top"><simpara>Up to 1000 digits of precision. In Evergreen mostly used for money\r
+                       values, with a precision of 6 and a scale of 2 (<literal>####.##</literal>).</simpara></entry>\r
+                       </row>\r
+                       </tbody>\r
+                       </tgroup>\r
+                       </table>\r
+                       <simpara>Full details about these data types are available from the\r
+                       <ulink url="http://www.postgresql.org/docs/8.4/static/datatype.html">data types section of\r
+                       the PostgreSQL manual</ulink>.</simpara>\r
+               </simplesect>\r
+               <simplesect id="_constraints">\r
+                       <title>Constraints</title>\r
+                       <simplesect id="_prevent_null_values">\r
+                               <title>Prevent NULL values</title>\r
+                               <simpara>A column definition may include the constraint <literal>NOT NULL</literal> to prevent NULL\r
+                               values. In PostgreSQL, a NULL value is not the equivalent of zero or false or\r
+                               an empty string; it is an explicit non-value with special properties. We&#8217;ll\r
+                               talk more about how to work with NULL values when we get to queries.</simpara>\r
+                       </simplesect>\r
+                       <simplesect id="_primary_key">\r
+                               <title>Primary key</title>\r
+                               <simpara>Every table can have at most one primary key. A primary key consists of one or\r
+                               more columns which together uniquely identify each row in a table. If you\r
+                               attempt to insert a row into a table that would create a duplicate or NULL\r
+                               primary key entry, the database rejects the row and returns an error.</simpara>\r
+                               <simpara>Natural primary keys are drawn from the intrinsic properties of the data being\r
+                               modelled. For example, some potential natural primary keys for a table that\r
+                               contains people would be:</simpara>\r
+                               <table\r
+                               frame="all"\r
+                               rowsep="1" colsep="1"\r
+                               >\r
+                               <title>Example: Some potential natural primary keys for a table of people</title>\r
+                               <?dbhtml table-width="90%"?>\r
+                               <?dbfo table-width="90%"?>\r
+                               <tgroup cols="3">\r
+                               <colspec colname="col_1" colwidth="77*"/>\r
+                               <colspec colname="col_2" colwidth="153*"/>\r
+                               <colspec colname="col_3" colwidth="153*"/>\r
+                               <thead>\r
+                               <row>\r
+                               <entry align="left" valign="top">Natural key </entry>\r
+                               <entry align="left" valign="top">Pros </entry>\r
+                               <entry align="left" valign="top">Cons</entry>\r
+                               </row>\r
+                               </thead>\r
+                               <tbody>\r
+                               <row>\r
+                               <entry align="left" valign="top"><simpara>First name, last name, address</simpara></entry>\r
+                               <entry align="left" valign="top"><simpara>No two people with the same name would ever live at the same address, right?</simpara></entry>\r
+                               <entry align="left" valign="top"><simpara>Lots of columns force data duplication in referencing tables</simpara></entry>\r
+                               </row>\r
+                               <row>\r
+                               <entry align="left" valign="top"><simpara>SSN or driver&#8217;s license</simpara></entry>\r
+                               <entry align="left" valign="top"><simpara>These are guaranteed to be unique</simpara></entry>\r
+                               <entry align="left" valign="top"><simpara>Lots of people don&#8217;t have an SSN or a driver&#8217;s license</simpara></entry>\r
+                               </row>\r
+                               </tbody>\r
+                               </tgroup>\r
+                               </table>\r
+                               <simpara>To avoid problems with natural keys, many applications instead define surrogate\r
+                               primary keys. A surrogate primary keys is a column with an autoincrementing\r
+                               integer value added to a table definition that ensures uniqueness.</simpara>\r
+                               <simpara>Evergreen uses surrogate keys (a column named <literal>id</literal> with a <literal>SERIAL</literal> data type)\r
+                               for most of its tables.</simpara>\r
+                       </simplesect>\r
+                       <simplesect id="_foreign_keys">\r
+                               <title>Foreign keys</title>\r
+                               <simpara>Every table can contain zero or more foreign keys: one or more columns that\r
+                               refer to the primary key of another table.</simpara>\r
+                               <simpara>For example, let&#8217;s consider Evergreen&#8217;s modelling of the basic relationship\r
+                               between copies, call numbers, and bibliographic records. Bibliographic records\r
+                               contained in the <literal>biblio.record_entry</literal> table can have call numbers attached to\r
+                               them. Call numbers are contained in the <literal>asset.call_number</literal> table, and they can\r
+                               have copies attached to them. Copies are contained in the <literal>asset.copy</literal> table.</simpara>\r
+                               <table\r
+                               frame="all"\r
+                               rowsep="1" colsep="1"\r
+                               >\r
+                               <title>Example: Evergreen&#8217;s copy / call number / bibliographic record relationships</title>\r
+                               <?dbhtml table-width="100%"?>\r
+                               <?dbfo table-width="100%"?>\r
+                               <tgroup cols="4">\r
+                               <colspec colname="col_1" colwidth="106*"/>\r
+                               <colspec colname="col_2" colwidth="106*"/>\r
+                               <colspec colname="col_3" colwidth="106*"/>\r
+                               <colspec colname="col_4" colwidth="106*"/>\r
+                               <thead>\r
+                               <row>\r
+                               <entry align="left" valign="top">Table </entry>\r
+                               <entry align="left" valign="top">Primary key </entry>\r
+                               <entry align="left" valign="top">Column with a foreign key </entry>\r
+                               <entry align="left" valign="top">Points to</entry>\r
+                               </row>\r
+                               </thead>\r
+                               <tbody>\r
+                               <row>\r
+                               <entry align="left" valign="top"><simpara>asset.copy</simpara></entry>\r
+                               <entry align="left" valign="top"><simpara>asset.copy.id</simpara></entry>\r
+                               <entry align="left" valign="top"><simpara>asset.copy.call_number</simpara></entry>\r
+                               <entry align="left" valign="top"><simpara>asset.call_number.id</simpara></entry>\r
+                               </row>\r
+                               <row>\r
+                               <entry align="left" valign="top"><simpara>asset.call_number</simpara></entry>\r
+                               <entry align="left" valign="top"><simpara>asset.call_number.id</simpara></entry>\r
+                               <entry align="left" valign="top"><simpara>asset.call_number.record</simpara></entry>\r
+                               <entry align="left" valign="top"><simpara>biblio.record_entry.id</simpara></entry>\r
+                               </row>\r
+                               <row>\r
+                               <entry align="left" valign="top"><simpara>biblio.record_entry</simpara></entry>\r
+                               <entry align="left" valign="top"><simpara>biblio.record_entry.id</simpara></entry>\r
+                               <entry align="left" valign="top"><simpara></simpara></entry>\r
+                               <entry align="left" valign="top"><simpara></simpara></entry>\r
+                               </row>\r
+                               </tbody>\r
+                               </tgroup>\r
+                               </table>\r
+                       </simplesect>\r
+                       <simplesect id="_check_constraints">\r
+                               <title>Check constraints</title>\r
+                               <simpara>PostgreSQL enables you to define rules to ensure that the value to be inserted\r
+                               or updated meets certain conditions. For example, you can ensure that an\r
+                               incoming integer value is within a specific range, or that a ZIP code matches a\r
+                               particular pattern.</simpara>\r
+                       </simplesect>\r
+               </simplesect>\r
+               <simplesect id="_deconstructing_a_table_definition_statement">\r
+                       <title>Deconstructing a table definition statement</title>\r
+                       <simpara>The <literal>actor.org_address</literal> table is a simple table in the Evergreen schema that\r
+                       we can use as a concrete example of many of the properties of databases that\r
+                       we have discussed so far.</simpara>\r
+                       <programlisting language="sql" linenumbering="unnumbered">CREATE TABLE actor.org_address (\r
+                         id            SERIAL  PRIMARY KEY,      <co id="sqlCO1-1"/>\r
+                         valid         BOOL    NOT NULL DEFAULT TRUE, <co id="sqlCO1-2"/>\r
+                         address_type  TEXT    NOT NULL DEFAULT 'MAILING', <co id="sqlCO1-3"/>\r
+                         org_unit      INT     NOT NULL REFERENCES actor.org_unit (id)  <co id="sqlCO1-4"/>\r
+                                                 DEFERRABLE INITIALLY DEFERRED,\r
+                         street1       TEXT    NOT NULL,\r
+                         street2       TEXT, <co id="sqlCO1-5"/>\r
+                         city          TEXT    NOT NULL,\r
+                         county        TEXT,\r
+                         state         TEXT    NOT NULL,\r
+                         country       TEXT    NOT NULL,\r
+                         post_code     TEXT    NOT NULL\r
+                       );</programlisting>\r
+                       <calloutlist>\r
+                       <callout arearefs="sqlCO1-1">\r
+                       <simpara>\r
+                       The column named <literal>id</literal> is defined with a special data type of <literal>SERIAL</literal>; if\r
+                       given no value when a row is inserted into a table, the database automatically\r
+                       generates the next sequential integer value for the column. <literal>SERIAL</literal> is a\r
+                       popular data type for a primary key because it is guaranteed to be unique - and\r
+                       indeed, the constraint for this column identifies it as the <literal>PRIMARY KEY</literal>.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="sqlCO1-2">\r
+                       <simpara>\r
+                       The data type <literal>BOOL</literal> defines a boolean value: <literal>TRUE</literal> or <literal>FALSE</literal> are the only\r
+                       acceptable values for the column. The constraint <literal>NOT NULL</literal> instructs the\r
+                       database to prevent the column from ever containing a NULL value. The column\r
+                       property <literal>DEFAULT TRUE</literal> instructs the database to automatically set the value\r
+                       of the column to <literal>TRUE</literal> if no value is provided.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="sqlCO1-3">\r
+                       <simpara>\r
+                       The data type <literal>TEXT</literal> defines a text column of practically unlimited length.\r
+                       As with the previous column, there is a <literal>NOT NULL</literal> constraint, and a default\r
+                       value of <literal>'MAILING'</literal> will result if no other value is supplied.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="sqlCO1-4">\r
+                       <simpara>\r
+                       The <literal>REFERENCES actor.org_unit (id)</literal> clause indicates that this column has a\r
+                       foreign key relationship to the <literal>actor.org_unit</literal> table, and that the value of\r
+                       this column in every row in this table must have a corresponding value in the\r
+                       <literal>id</literal> column in the referenced table (<literal>actor.org_unit</literal>).\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="sqlCO1-5">\r
+                       <simpara>\r
+                       The column named <literal>street2</literal> demonstrates that not all columns have constraints\r
+                       beyond data type. In this case, the column is allowed to be NULL or to contain a\r
+                       <literal>TEXT</literal> value.\r
+                       </simpara>\r
+                       </callout>\r
+                       </calloutlist>\r
+               </simplesect>\r
+               <simplesect id="_displaying_a_table_definition_using_literal_psql_literal">\r
+                       <title>Displaying a table definition using <literal>psql</literal></title>\r
+                       <simpara>The <literal>psql</literal> command-line interface is the preferred method for accessing\r
+                       PostgreSQL databases. It offers features like tab-completion, readline support\r
+                       for recalling previous commands, flexible input and output formats, and\r
+                       is accessible via a standard SSH session.</simpara>\r
+                       <simpara>If you press the <literal>Tab</literal> key once after typing one or more characters of the\r
+                       database object name, <literal>psql</literal> automatically completes the name if there are no\r
+                       other matches. If there are other matches for your current input, nothing\r
+                       happens until you press the <literal>Tab</literal> key a second time, at which point <literal>psql</literal>\r
+                       displays all of the matches for your current input.</simpara>\r
+                       <simpara>To display the definition of a database object such as a table, issue the\r
+                       command <literal>\d _object-name_</literal>. For example, to display the definition of the\r
+                       actor.usr_note table:</simpara>\r
+                       <programlisting language="sh" linenumbering="unnumbered">$ psql evergreen <co id="sqlCO2-1"/>\r
+                       psql (8.4.1)\r
+                       Type "help" for help.\r
+\r
+                       evergreen=# \d actor.usr_note  <co id="sqlCO2-2"/>\r
+                                                               Table "actor.usr_note"\r
+                          Column    |           Type           |                          Modifiers\r
+                       -------------+--------------------------+-------------------------------------------------------------\r
+                        id          | bigint                   | not null default nextval('actor.usr_note_id_seq'::regclass)\r
+                        usr         | bigint                   | not null\r
+                        creator     | bigint                   | not null\r
+                        create_date | timestamp with time zone | default now()\r
+                        pub         | boolean                  | not null default false\r
+                        title       | text                     | not null\r
+                        value       | text                     | not null\r
+                       Indexes:\r
+                           "usr_note_pkey" PRIMARY KEY, btree (id)\r
+                           "actor_usr_note_creator_idx" btree (creator)\r
+                           "actor_usr_note_usr_idx" btree (usr)\r
+                       Foreign-key constraints:\r
+                           "usr_note_creator_fkey" FOREIGN KEY (creator) REFERENCES actor.usr(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED\r
+                           "usr_note_usr_fkey" FOREIGN KEY (usr) REFERENCES actor.usr(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED\r
+\r
+                       evergreen=# \q    <co id="sqlCO2-3"/>\r
+                       $</programlisting>\r
+                       <calloutlist>\r
+                       <callout arearefs="sqlCO2-1">\r
+                       <simpara>\r
+                       This is the most basic connection to a PostgreSQL database. You can use a\r
+                       number of other flags to specify user name, hostname, port, and other options.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="sqlCO2-2">\r
+                       <simpara>\r
+                       The <literal>\d</literal> command displays the definition of a database object.\r
+                       </simpara>\r
+                       </callout>\r
+                       <callout arearefs="sqlCO2-3">\r
+                       <simpara>\r
+                       The <literal>\q</literal> command quits the <literal>psql</literal> session and returns you to the shell prompt.\r
+                       </simpara>\r
+                       </callout>\r
+                       </calloutlist>\r
+               </simplesect>\r
+       </section>\r
+       <section id="basic_sql_queries">\r
+               <title>Basic SQL queries</title>\r
+               <simplesect id="_the_select_statement">\r
+                       <title>The SELECT statement</title>\r
+                       <simpara>The SELECT statement is the basic tool for retrieving information from a\r
+                       database. The syntax for most SELECT statements is:</simpara>\r
+                       <blockquote>\r
+                       <literallayout><literal>SELECT</literal> [<emphasis>columns(s)</emphasis>]\r
+                         <literal>FROM</literal> [<emphasis>table(s)</emphasis>]\r
+                         [<literal>WHERE</literal> <emphasis>condition(s)</emphasis>]\r
+                         [<literal>GROUP BY</literal> <emphasis>columns(s)</emphasis>]\r
+                         [<literal>HAVING</literal> <emphasis>grouping-condition(s)</emphasis>]\r
+                         [<literal>ORDER BY</literal> <emphasis>column(s)</emphasis>]\r
+                         [<literal>LIMIT</literal> <emphasis>maximum-results</emphasis>]\r
+                         [<literal>OFFSET</literal> <emphasis>start-at-result-#</emphasis>]\r
+                       ;</literallayout>\r
+                       </blockquote>\r
+                       <simpara>For example, to select all of the columns for each row in the\r
+                       <literal>actor.usr_address</literal> table, issue the following query:</simpara>\r
+                       <programlisting language="sql" linenumbering="unnumbered">SELECT *\r
+                         FROM actor.usr_address\r
+                       ;</programlisting>\r
+               </simplesect>\r
+               <simplesect id="_selecting_particular_columns_from_a_table">\r
+                       <title>Selecting particular columns from a table</title>\r
+                       <simpara><literal>SELECT *</literal> returns all columns from all of the tables included in your query.\r
+                       However, quite often you will want to return only a subset of the possible\r
+                       columns. You can retrieve specific columns by listing the names of the columns\r
+                       you want after the <literal>SELECT</literal> keyword. Separate each column name with a comma.</simpara>\r
+                       <simpara>For example, to select just the city, county, and state from the\r
+                       actor.usr_address table, issue the following query:</simpara>\r
+                       <programlisting language="sql" linenumbering="unnumbered">SELECT city, county, state\r
+                         FROM actor.usr_address\r
+                       ;</programlisting>\r
+               </simplesect>\r
+               <simplesect id="_sorting_results_with_the_order_by_clause">\r
+                       <title>Sorting results with the ORDER BY clause</title>\r
+                       <simpara>By default, a SELECT statement returns rows matching your query with no\r
+                       guarantee of any particular order in which they are returned. To force\r
+                       the rows to be returned in a particular order, use the ORDER BY clause\r
+                       to specify one or more columns to determine the sorting priority of the\r
+                       rows.</simpara>\r
+                       <simpara>For example, to sort the rows returned from your <literal>actor.usr_address</literal> query by\r
+                       city, with county and then zip code as the tie breakers, issue the\r
+                       following query:</simpara>\r
+                       <programlisting language="sql" linenumbering="unnumbered">SELECT city, county, state\r
+                         FROM actor.usr_address\r
+                         ORDER BY city, county, post_code\r
+                       ;</programlisting>\r
+               </simplesect>\r
+               <simplesect id="_filtering_results_with_the_where_clause">\r
+                       <title>Filtering results with the WHERE clause</title>\r
+                       <simpara>Thus far, your results have been returning all of the rows in the table.\r
+                       Normally, however, you would want to restrict the rows that are returned to the\r
+                       subset of rows that match one or more conditions of your search. The <literal>WHERE</literal>\r
+                       clause enables you to specify a set of conditions that filter your query\r
+                       results. Each condition in the <literal>WHERE</literal> clause is an SQL expression that returns\r
+                       a boolean (true or false) value.</simpara>\r
+                       <simpara>For example, to restrict the results returned from your <literal>actor.usr_address</literal>\r
+                       query to only those rows containing a state value of <emphasis>Connecticut</emphasis>, issue the\r
+                       following query:</simpara>\r
+                       <programlisting language="sql" linenumbering="unnumbered">SELECT city, county, state\r
+                         FROM actor.usr_address\r
+                         WHERE state = 'Connecticut'\r
+                         ORDER BY city, county, post_code\r
+                       ;</programlisting>\r
+                       <simpara>You can include more conditions in the <literal>WHERE</literal> clause with the <literal>OR</literal> and <literal>AND</literal>\r
+                       operators. For example, to further restrict the results returned from your\r
+                       <literal>actor.usr_address</literal> query to only those rows where the state column contains a\r
+                       value of <emphasis>Connecticut</emphasis> and the city column contains a value of <emphasis>Hartford</emphasis>,\r
+                       issue the following query:</simpara>\r
+                       <programlisting language="sql" linenumbering="unnumbered">SELECT city, county, state\r
+                         FROM actor.usr_address\r
+                         WHERE state = 'Connecticut'\r
+                           AND city = 'Hartford'\r
+                         ORDER BY city, county, post_code\r
+                       ;</programlisting>\r
+                       <note><simpara>To return rows where the state is <emphasis>Connecticut</emphasis> and the city is <emphasis>Hartford</emphasis> or\r
+                       <emphasis>New Haven</emphasis>, you must use parentheses to explicitly group the city value\r
+                       conditions together, or else the database will evaluate the <literal>OR city = 'New\r
+                       Haven'</literal> clause entirely on its own and match all rows where the city column is\r
+                       <emphasis>New Haven</emphasis>, even though the state might not be <emphasis>Connecticut</emphasis>.</simpara></note>\r
+                       <formalpara><title>Trouble with OR</title><para>\r
+                       <programlisting language="sql" linenumbering="unnumbered">SELECT city, county, state\r
+                         FROM actor.usr_address\r
+                         WHERE state = 'Connecticut'\r
+                           AND city = 'Hartford' OR city = 'New Haven'\r
+                         ORDER BY city, county, post_code\r
+                       ;\r
+\r
+                       -- Can return unwanted rows because the OR is not grouped!</programlisting>\r
+                       </para></formalpara>\r
+                       <formalpara><title>Grouped OR&#8217;ed conditions</title><para>\r
+                       <programlisting language="sql" linenumbering="unnumbered">SELECT city, county, state\r
+                         FROM actor.usr_address\r
+                         WHERE state = 'Connecticut'\r
+                           AND (city = 'Hartford' OR city = 'New Haven')\r
+                         ORDER BY city, county, post_code\r
+                       ;\r
+\r
+                       -- The parentheses ensure that the OR is applied to the cities, and the\r
+                       -- state in either case must be 'Connecticut'</programlisting>\r
+                       </para></formalpara>\r
+                       <simplesect id="_comparison_operators">\r
+                               <title>Comparison operators</title>\r
+                               <simpara>Here is a partial list of comparison operators that are commonly used in\r
+                               <literal>WHERE</literal> clauses:</simpara>\r
+                               <simplesect id="_comparing_two_scalar_values">\r
+                                       <title>Comparing two scalar values</title>\r
+                                       <itemizedlist>\r
+                                       <listitem>\r
+                                       <simpara>\r
+                                       <literal>x = y</literal> (equal to)\r
+                                       </simpara>\r
+                                       </listitem>\r
+                                       <listitem>\r
+                                       <simpara>\r
+                                       <literal>x != y</literal> (not equal to)\r
+                                       </simpara>\r
+                                       </listitem>\r
+                                       <listitem>\r
+                                       <simpara>\r
+                                       <literal>x &lt; y</literal> (less than)\r
+                                       </simpara>\r
+                                       </listitem>\r
+                                       <listitem>\r
+                                       <simpara>\r
+                                       <literal>x &gt; y</literal> (greater than)\r
+                                       </simpara>\r
+                                       </listitem>\r
+                                       <listitem>\r
+                                       <simpara>\r
+                                       <literal>x LIKE y</literal> (TEXT value x matches a subset of TEXT y, where y is a string that\r
+                                       can contain <emphasis>%</emphasis> as a wildcard for 0 or more characters, and <emphasis>_</emphasis> as a wildcard\r
+                                       for a single character. For example, <literal>WHERE 'all you can eat fish and chips\r
+                                       and a big stick' LIKE '%fish%stick'</literal> would return TRUE)\r
+                                       </simpara>\r
+                                       </listitem>\r
+                                       <listitem>\r
+                                       <simpara>\r
+                                       <literal>x ILIKE y</literal> (like LIKE, but the comparison ignores upper-case / lower-case)\r
+                                       </simpara>\r
+                                       </listitem>\r
+                                       <listitem>\r
+                                       <simpara>\r
+                                       <literal>x IN y</literal> (x is in the list of values y, where y can be a list or a SELECT\r
+                                       statement that returns a list)\r
+                                       </simpara>\r
+                                       </listitem>\r
+                                       </itemizedlist>\r
+                               </simplesect>\r
+                       </simplesect>\r
+               </simplesect>\r
+               <simplesect id="_null_values">\r
+                       <title>NULL values</title>\r
+                       <simpara>SQL databases have a special way of representing the value of a column that has\r
+                       no value: <literal>NULL</literal>. A <literal>NULL</literal> value is not equal to zero, and is not an empty\r
+                       string; it is equal to nothing, not even another <literal>NULL</literal>, because it has no value\r
+                       that can be compared.</simpara>\r
+                       <simpara>To return rows from a table where a given column is not <literal>NULL</literal>, use the\r
+                       <literal>IS NOT NULL</literal> comparison operator.</simpara>\r
+                       <formalpara><title>Retrieving rows where a column is not <literal>NULL</literal></title><para>\r
+                       <programlisting language="sql" linenumbering="unnumbered">SELECT id, first_given_name, family_name\r
+                         FROM actor.usr\r
+                         WHERE second_given_name IS NOT NULL\r
+                       ;</programlisting>\r
+                       </para></formalpara>\r
+                       <simpara>Similarly, to return rows from a table where a given column is <literal>NULL</literal>, use\r
+                       the <literal>IS NULL</literal> comparison operator.</simpara>\r
+                       <formalpara><title>Retrieving rows where a column is <literal>NULL</literal></title><para>\r
+                       <programlisting language="sql" linenumbering="unnumbered">SELECT id, first_given_name, second_given_name, family_name\r
+                         FROM actor.usr\r
+                         WHERE second_given_name IS NULL\r
+                       ;\r
+\r
+                        id | first_given_name | second_given_name |  family_name\r
+                       ----+------------------+-------------------+----------------\r
+                         1 | Administrator    |                   | System Account\r
+                       (1 row)</programlisting>\r
+                       </para></formalpara>\r
+                       <simpara>Notice that the <literal>NULL</literal> value in the output is displayed as empty space,\r
+                       indistinguishable from an empty string; this is the default display method in\r
+                       <literal>psql</literal>. You can change the behaviour of <literal>psql</literal> using the <literal>pset</literal> command:</simpara>\r
+                       <formalpara><title>Changing the way <literal>NULL</literal> values are displayed in <literal>psql</literal></title><para>\r
+                       <programlisting language="sql" linenumbering="unnumbered">evergreen=# \pset null '(null)'\r
+                       Null display is '(null)'.\r
+\r
+                       SELECT id, first_given_name, second_given_name, family_name\r
+                         FROM actor.usr\r
+                         WHERE second_given_name IS NULL\r
+                       ;\r
+\r
+                        id | first_given_name | second_given_name |  family_name\r
+                       ----+------------------+-------------------+----------------\r
+                         1 | Administrator    | (null)            | System Account\r
+                       (1 row)</programlisting>\r
+                       </para></formalpara>\r
+                       <simpara>Database queries within programming languages such as Perl and C have\r
+                       special methods of checking for <literal>NULL</literal> values in returned results.</simpara>\r
+               </simplesect>\r
+               <simplesect id="_text_delimiter">\r
+                       <title>Text delimiter: '</title>\r
+                       <simpara>You might have noticed that we have been using the <literal>'</literal> character to delimit\r
+                       TEXT values and values such as dates and times that are TEXT values. Sometimes,\r
+                       however, your TEXT value itself contains a <literal>'</literal> character, such as the word\r
+                       <literal>you&#8217;re</literal>. To prevent the database from prematurely ending the TEXT value at the\r
+                       first <literal>'</literal> character and returning a syntax error, use another <literal>'</literal> character to\r
+                       escape the following <literal>'</literal> character.</simpara>\r
+                       <simpara>For example, to change the last name of a user in the <literal>actor.usr</literal> table to\r
+                       <literal>L&#8217;estat</literal>, issue the following SQL:</simpara>\r
+                       <formalpara><title>Escaping <literal>'</literal> in TEXT values</title><para>\r
+                       <programlisting language="sql" linenumbering="unnumbered">UPDATE actor.usr\r
+                         SET family_name = 'L''estat'\r
+                         WHERE profile IN (\r
+                           SELECT id\r
+                             FROM permission.grp_tree\r
+                             WHERE name = 'Vampire'\r
+                         )\r
+                               ;</programlisting>\r
+                               </para></formalpara>\r
+                               <simpara>When you retrieve the row from the database, the value is displayed with just\r
+                               a single <literal>'</literal> character:</simpara>\r
+                               <programlisting language="sql" linenumbering="unnumbered">SELECT id, family_name\r
+                                 FROM actor.usr\r
+                                 WHERE family_name = 'L''estat'\r
+                               ;\r
+\r
+                                id | family_name\r
+                               ----+-------------\r
+                                 1 | L'estat\r
+                               (1 row)</programlisting>\r
+               </simplesect>\r
+               <simplesect id="_grouping_and_eliminating_results_with_the_group_by_and_having_clauses">\r
+                       <title>Grouping and eliminating results with the GROUP BY and HAVING clauses</title>\r
+                       <simpara>The GROUP BY clause returns a unique set of results for the desired columns.\r
+                       This is most often used in conjunction with an aggregate function to present\r
+                       results for a range of values in a single query, rather than requiring you to\r
+                       issue one query per target value.</simpara>\r
+                       <formalpara><title>Returning unique results of a single column with <literal>GROUP BY</literal></title><para>\r
+                       <programlisting language="sql" linenumbering="unnumbered">SELECT grp\r
+                         FROM permission.grp_perm_map\r
+                         GROUP BY grp\r
+                         ORDER BY grp;\r
+\r
+                        grp\r
+                       -----+\r
+                          1\r
+                          2\r
+                          3\r
+                          4\r
+                          5\r
+                          6\r
+                          7\r
+                         10\r
+                       (8 rows)</programlisting>\r
+                       </para></formalpara>\r
+                       <simpara>While <literal>GROUP BY</literal> can be useful for a single column, it is more often used\r
+                       to return the distinct results across multiple columns. For example, the\r
+                       following query shows us which groups have permissions at each depth in\r
+                       the library hierarchy:</simpara>\r
+                       <formalpara><title>Returning unique results of multiple columns with <literal>GROUP BY</literal></title><para>\r
+                       <programlisting language="sql" linenumbering="unnumbered">SELECT grp, depth\r
+                         FROM permission.grp_perm_map\r
+                         GROUP BY grp, depth\r
+                         ORDER BY depth, grp;\r
+\r
+                        grp | depth\r
+                       -----+-------\r
+                          1 |     0\r
+                          2 |     0\r
+                          3 |     0\r
+                          4 |     0\r
+                          5 |     0\r
+                         10 |     0\r
+                          3 |     1\r
+                          4 |     1\r
+                          5 |     1\r
+                          6 |     1\r
+                          7 |     1\r
+                         10 |     1\r
+                          3 |     2\r
+                          4 |     2\r
+                         10 |     2\r
+                       (15 rows)</programlisting>\r
+                       </para></formalpara>\r
+                       <simpara>Extending this further, you can use the <literal>COUNT()</literal> aggregate function to\r
+                       also return the number of times each unique combination of <literal>grp</literal> and <literal>depth</literal>\r
+                       appears in the table. <emphasis>Yes, this is a sneak peek at the use of aggregate\r
+                       functions! Keeners.</emphasis></simpara>\r
+                       <formalpara><title>Counting unique column combinations with <literal>GROUP BY</literal></title><para>\r
+                       <programlisting language="sql" linenumbering="unnumbered">SELECT grp, depth, COUNT(grp)\r
+                         FROM permission.grp_perm_map\r
+                         GROUP BY grp, depth\r
+                         ORDER BY depth, grp;\r
+\r
+                        grp | depth | count\r
+                       -----+-------+-------\r
+                          1 |     0 |     6\r
+                          2 |     0 |     2\r
+                          3 |     0 |    45\r
+                          4 |     0 |     3\r
+                          5 |     0 |     5\r
+                         10 |     0 |     1\r
+                          3 |     1 |     3\r
+                          4 |     1 |     4\r
+                          5 |     1 |     1\r
+                          6 |     1 |     9\r
+                          7 |     1 |     5\r
+                         10 |     1 |    10\r
+                          3 |     2 |    24\r
+                          4 |     2 |     8\r
+                         10 |     2 |     7\r
+                       (15 rows)</programlisting>\r
+                       </para></formalpara>\r
+                       <simpara>You can use the <literal>WHERE</literal> clause to restrict the returned results before grouping\r
+                       is applied to the results. The following query restricts the results to those\r
+                       rows that have a depth of 0.</simpara>\r
+                       <formalpara><title>Using the <literal>WHERE</literal> clause with <literal>GROUP BY</literal></title><para>\r
+                       <programlisting language="sql" linenumbering="unnumbered">SELECT grp, COUNT(grp)\r
+                         FROM permission.grp_perm_map\r
+                         WHERE depth = 0\r
+                         GROUP BY grp\r
+                         ORDER BY 2 DESC\r
+                       ;\r
+\r
+                        grp | count\r
+                       -----+-------\r
+                          3 |    45\r
+                          1 |     6\r
+                          5 |     5\r
+                          4 |     3\r
+                          2 |     2\r
+                         10 |     1\r
+                       (6 rows)</programlisting>\r
+                       </para></formalpara>\r
+                       <simpara>To restrict results after grouping has been applied to the rows, use the\r
+                       <literal>HAVING</literal> clause; this is typically used to restrict results based on\r
+                       a comparison to the value returned by an aggregate function. For example,\r
+                       the following query restricts the returned rows to those that have more than\r
+                       5 occurrences of the same value for <literal>grp</literal> in the table.</simpara>\r
+                       <formalpara><title><literal>GROUP BY</literal> restricted by a <literal>HAVING</literal> clause</title><para>\r
+                       <programlisting language="sql" linenumbering="unnumbered">SELECT grp, COUNT(grp)\r
+                         FROM permission.grp_perm_map\r
+                         GROUP BY grp\r
+                         HAVING COUNT(grp) &gt; 5\r
+                       ;\r
+\r
+                        grp | count\r
+                       -----+-------\r
+                          6 |     9\r
+                          4 |    15\r
+                          5 |     6\r
+                          1 |     6\r
+                          3 |    72\r
+                         10 |    18\r
+                       (6 rows)</programlisting>\r
+                       </para></formalpara>\r
+               </simplesect>\r
+               <simplesect id="_eliminating_duplicate_results_with_the_distinct_keyword">\r
+                       <title>Eliminating duplicate results with the DISTINCT keyword</title>\r
+                       <simpara><literal>GROUP BY</literal> is one way of eliminating duplicate results from the rows returned\r
+                       by your query. The purpose of the <literal>DISTINCT</literal> keyword is to remove duplicate\r
+                       rows from the results of your query. However, it works, and it is easy - so if\r
+                       you just want a quick list of the unique set of values for a column or set of\r
+                       columns, the <literal>DISTINCT</literal> keyword might be appropriate.</simpara>\r
+                       <simpara>On the other hand, if you are getting duplicate rows back when you don&#8217;t expect\r
+                       them, then applying the <literal>DISTINCT</literal> keyword might be a sign that you are\r
+                       papering over a real problem.</simpara>\r
+                       <formalpara><title>Returning unique results of multiple columns with <literal>DISTINCT</literal></title><para>\r
+                       <programlisting language="sql" linenumbering="unnumbered">SELECT DISTINCT grp, depth\r
+                         FROM permission.grp_perm_map\r
+                         ORDER BY depth, grp\r
+                       ;\r
+\r
+                        grp | depth\r
+                       -----+-------\r
+                          1 |     0\r
+                          2 |     0\r
+                          3 |     0\r
+                          4 |     0\r
+                          5 |     0\r
+                         10 |     0\r
+                          3 |     1\r
+                          4 |     1\r
+                          5 |     1\r
+                          6 |     1\r
+                          7 |     1\r
+                         10 |     1\r
+                          3 |     2\r
+                          4 |     2\r
+                         10 |     2\r
+                       (15 rows)</programlisting>\r
+                       </para></formalpara>\r
+               </simplesect>\r
+               <simplesect id="_paging_through_results_with_the_limit_and_offset_clauses">\r
+                       <title>Paging through results with the LIMIT and OFFSET clauses</title>\r
+                       <simpara>The <literal>LIMIT</literal> clause restricts the total number of rows returned from your query\r
+                       and is useful if you just want to list a subset of a large number of rows. For\r
+                       example, in the following query we list the five most frequently used\r
+                       circulation modifiers:</simpara>\r
+                       <formalpara><title>Using the <literal>LIMIT</literal> clause to restrict results</title><para>\r
+                       <programlisting language="sql" linenumbering="unnumbered">SELECT circ_modifier, COUNT(circ_modifier)\r
+                         FROM asset.copy\r
+                         GROUP BY circ_modifier\r
+                         ORDER BY 2 DESC\r
+                         LIMIT 5\r
+                       ;\r
+\r
+                        circ_modifier | count\r
+                       ---------------+--------\r
+                        CIRC          | 741995\r
+                        BOOK          | 636199\r
+                        SER           | 265906\r
+                        DOC           | 191598\r
+                        LAW MONO      | 126627\r
+                       (5 rows)</programlisting>\r
+                       </para></formalpara>\r
+                       <simpara>When you use the <literal>LIMIT</literal> clause to restrict the total number of rows returned\r
+                       by your query, you can also use the <literal>OFFSET</literal> clause to determine which subset\r
+                       of the rows will be returned. The use of the <literal>OFFSET</literal> clause assumes that\r
+                       you&#8217;ve used the <literal>ORDER BY</literal> clause to impose order on the results.</simpara>\r
+                       <simpara>In the following example, we use the <literal>OFFSET</literal> clause to get results 6 through\r
+                       10 from the same query that we prevously executed.</simpara>\r
+                       <formalpara><title>Using the <literal>OFFSET</literal> clause to return a specific subset of rows</title><para>\r
+                       <programlisting language="sql" linenumbering="unnumbered">SELECT circ_modifier, COUNT(circ_modifier)\r
+                         FROM asset.copy\r
+                         GROUP BY circ_modifier\r
+                         ORDER BY 2 DESC\r
+                         LIMIT 5\r
+                         OFFSET 5\r
+                       ;\r
+\r
+                        circ_modifier | count\r
+                       ---------------+--------\r
+                        LAW SERIAL    | 102758\r
+                        DOCUMENTS     |  86215\r
+                        BOOK_WEB      |  63786\r
+                        MFORM SER     |  39917\r
+                        REF           |  34380\r
+                       (5 rows)</programlisting>\r
+                       </para></formalpara>\r
+               </simplesect>\r
+       </section>\r
+       <section id="advanced_sql_queries">\r
+               <title>Advanced SQL queries</title>\r
+               <simplesect id="_transforming_column_values_with_functions">\r
+                       <title>Transforming column values with functions</title>\r
+                       <simpara>PostgreSQL includes many built-in functions for manipulating column data.\r
+                       You can also create your own functions (and Evergreen does make use of\r
+                       many custom functions). There are two types of functions used in\r
+                       databases: scalar functions and aggregate functions.</simpara>\r
+                       <simplesect id="_scalar_functions">\r
+                               <title>Scalar functions</title>\r
+                               <simpara>Scalar functions transform each value of the target column. If your query\r
+                               would return 50 values for a column in a given query, and you modify your\r
+                               query to apply a scalar function to the values returned for that column,\r
+                               it will still return 50 values. For example, the UPPER() function,\r
+                               used to convert text values to upper-case, modifies the results in the\r
+                               following set of queries:</simpara>\r
+                               <formalpara><title>Using the UPPER() scalar function to convert text values to upper-case</title><para>\r
+                               <programlisting language="sql" linenumbering="unnumbered">-- First, without the UPPER() function for comparison\r
+                               SELECT shortname, name\r
+                                 FROM actor.org_unit\r
+                                 WHERE id &lt; 4\r
+                               ;\r
+\r
+                                shortname |         name\r
+                               -----------+-----------------------\r
+                                CONS      | Example Consortium\r
+                                SYS1      | Example System 1\r
+                                SYS2      | Example System 2\r
+                               (3 rows)\r
+\r
+                               -- Now apply the UPPER() function to the name column\r
+                               SELECT shortname, UPPER(name)\r
+                                 FROM actor.org_unit\r
+                                 WHERE id &lt; 4\r
+                               ;\r
+\r
+                                shortname |       upper\r
+                               -----------+--------------------\r
+                                CONS      | EXAMPLE CONSORTIUM\r
+                                SYS1      | EXAMPLE SYSTEM 1\r
+                                SYS2      | EXAMPLE SYSTEM 2\r
+                               (3 rows)</programlisting>\r
+                               </para></formalpara>\r
+                               <simpara>There are so many scalar functions in PostgreSQL that we cannot cover them\r
+                               all here, but we can list some of the most commonly used functions:</simpara>\r
+                               <itemizedlist>\r
+                               <listitem>\r
+                               <simpara>\r
+                               || - concatenates two text values together\r
+                               </simpara>\r
+                               </listitem>\r
+                               <listitem>\r
+                               <simpara>\r
+                               COALESCE() - returns the first non-NULL value from the list of arguments\r
+                               </simpara>\r
+                               </listitem>\r
+                               <listitem>\r
+                               <simpara>\r
+                               LOWER() - returns a text value converted to lower-case\r
+                               </simpara>\r
+                               </listitem>\r
+                               <listitem>\r
+                               <simpara>\r
+                               REPLACE() - returns a text value after replacing all occurrences of a given text value with a different text value\r
+                               </simpara>\r
+                               </listitem>\r
+                               <listitem>\r
+                               <simpara>\r
+                               REGEXP_REPLACE() - returns a text value after being transformed by a regular expression\r
+                               </simpara>\r
+                               </listitem>\r
+                               <listitem>\r
+                               <simpara>\r
+                               UPPER() - returns a text value converted to upper-case\r
+                               </simpara>\r
+                               </listitem>\r
+                               </itemizedlist>\r
+                               <simpara>For a complete list of scalar functions, see\r
+                               <ulink url="http://www.postgresql.org/docs/8.3/interactive/functions.html">the PostgreSQL function documentation</ulink>.</simpara>\r
+                       </simplesect>\r
+                       <simplesect id="_aggregate_functions">\r
+                               <title>Aggregate functions</title>\r
+                               <simpara>Aggregate functions return a single value computed from the the complete set of\r
+                               values returned for the specified column.</simpara>\r
+                               <itemizedlist>\r
+                               <listitem>\r
+                               <simpara>\r
+                               AVG()\r
+                               </simpara>\r
+                               </listitem>\r
+                               <listitem>\r
+                               <simpara>\r
+                               COUNT()\r
+                               </simpara>\r
+                               </listitem>\r
+                               <listitem>\r
+                               <simpara>\r
+                               MAX()\r
+                               </simpara>\r
+                               </listitem>\r
+                               <listitem>\r
+                               <simpara>\r
+                               MIN()\r
+                               </simpara>\r
+                               </listitem>\r
+                               <listitem>\r
+                               <simpara>\r
+                               SUM()\r
+                               </simpara>\r
+                               </listitem>\r
+                               </itemizedlist>\r
+                       </simplesect>\r
+               </simplesect>\r
+               <simplesect id="_sub_selects">\r
+                       <title>Sub-selects</title>\r
+                       <simpara>A sub-select is the technique of using the results of one query to feed\r
+                       into another query. You can, for example, return a set of values from\r
+                       one column in a SELECT statement to be used to satisfy the IN() condition\r
+                       of another SELECT statement; or you could return the MAX() value of a\r
+                       column in a SELECT statement to match the = condition of another SELECT\r
+                       statement.</simpara>\r
+                       <simpara>For example, in the following query we use a sub-select to restrict the copies\r
+                       returned by the main SELECT statement to only those locations that have an\r
+                       <literal>opac_visible</literal> value of <literal>TRUE</literal>:</simpara>\r
+                       <formalpara><title>Sub-select example</title><para>\r
+                       <programlisting language="sql" linenumbering="unnumbered">SELECT call_number\r
+                         FROM asset.copy\r
+                         WHERE deleted IS FALSE\r
+                           AND location IN (\r
+                           SELECT id\r
+                             FROM asset.copy_location\r
+                             WHERE opac_visible IS TRUE\r
+                         )\r
+                       ;</programlisting>\r
+                       </para></formalpara>\r
+                       <simpara>Sub-selects can be an approachable way to breaking down a problem that\r
+                       requires matching values between different tables, and often result in\r
+                       a clearly expressed solution to a problem. However, if you start writing\r
+                       sub-selects within sub-selects, you should consider tackling the problem\r
+                       with joins instead.</simpara>\r
+               </simplesect>\r
+               <simplesect id="_joins">\r
+                       <title>Joins</title>\r
+                       <simpara>Joins enable you to access the values from multiple tables in your query\r
+                       results and comparison operators. For example, joins are what enable you to\r
+                       relate a bibliographic record to a barcoded copy via the <literal>biblio.record_entry</literal>,\r
+                       <literal>asset.call_number</literal>, and <literal>asset.copy</literal> tables. In this section, we discuss the\r
+                       most common kind of join&#8212;the inner join&#8212;as well as the less common outer join\r
+                       and some set operations which can compare and contrast the values returned by\r
+                       separate queries.</simpara>\r
+                       <simpara>When we talk about joins, we are going to talk about the left-hand table and\r
+                       the right-hand table that participate in the join. Every join brings together\r
+                       just two tables - but you can use an unlimited (for our purposes) number\r
+                       of joins in a single SQL statement. Each time you use a join, you effectively\r
+                       create a new table, so when you add a second join clause to a statement,\r
+                       table 1 and table 2 (which were the left-hand table and the right-hand table\r
+                       for the first join) now act as a merged left-hand table and the new table\r
+                       in the second join clause is the right-hand table.</simpara>\r
+                       <simpara>Clear as mud? Okay, let&#8217;s look at some examples.</simpara>\r
+                       <simplesect id="_inner_joins">\r
+                               <title>Inner joins</title>\r
+                               <simpara>An inner join returns all of the columns from the left-hand table in the join\r
+                               with all of the columns from the right-hand table in the joins that match a\r
+                               condition in the ON clause. Typically, you use the <literal>=</literal> operator to match the\r
+                               foreign key of the left-hand table with the primary key of the right-hand\r
+                               table to follow the natural relationship between the tables.</simpara>\r
+                               <simpara>In the following example, we return all of columns from the <literal>actor.usr</literal> and\r
+                               <literal>actor.org_unit</literal> tables, joined on the relationship between the user&#8217;s home\r
+                               library and the library&#8217;s ID. Notice in the results that some columns, like\r
+                               <literal>id</literal> and <literal>mailing_address</literal>, appear twice; this is because both the <literal>actor.usr</literal>\r
+                               and <literal>actor.org_unit</literal> tables include columns with these names. This is also why\r
+                               we have to fully qualify the column names in our queries with the schema and\r
+                               table names.</simpara>\r
+                               <formalpara><title>A simple inner join</title><para>\r
+                               <programlisting language="sql" linenumbering="unnumbered">SELECT *\r
+                                 FROM actor.usr\r
+                                   INNER JOIN actor.org_unit ON actor.usr.home_ou = actor.org_unit.id\r
+                                   WHERE actor.org_unit.shortname = 'CONS'\r
+                               ;\r
+\r
+                               -[ RECORD 1 ]------------------+---------------------------------\r
+                               id                             | 1\r
+                               card                           | 1\r
+                               profile                        | 1\r
+                               usrname                        | admin\r
+                               email                          |\r
+                               ...\r
+                               mailing_address                |\r
+                               billing_address                |\r
+                               home_ou                        | 1\r
+                               ...\r
+                               claims_never_checked_out_count | 0\r
+                               id                             | 1\r
+                               parent_ou                      |\r
+                               ou_type                        | 1\r
+                               ill_address                    | 1\r
+                               holds_address                  | 1\r
+                               mailing_address                | 1\r
+                               billing_address                | 1\r
+                               shortname                      | CONS\r
+                               name                           | Example Consortium\r
+                               email                          |\r
+                               phone                          |\r
+                               opac_visible                   | t\r
+                               fiscal_calendar                | 1</programlisting>\r
+                               </para></formalpara>\r
+                               <simpara>Of course, you do not have to return every column from the joined tables;\r
+                               you can (and should) continue to specify only the columns that you want to\r
+                               return. In the following example, we count the number of borrowers for\r
+                               every user profile in a given library by joining the <literal>permission.grp_tree</literal>\r
+                               table where profiles are defined against the <literal>actor.usr</literal> table, and then\r
+                               joining the <literal>actor.org_unit</literal> table to give us access to the user&#8217;s home\r
+                               library:</simpara>\r
+                               <formalpara><title>Borrower Count by Profile (Adult, Child, etc)/Library</title><para>\r
+                               <programlisting language="sql" linenumbering="unnumbered">SELECT permission.grp_tree.name, actor.org_unit.name, COUNT(permission.grp_tree.name)\r
+                                 FROM actor.usr\r
+                                   INNER JOIN permission.grp_tree\r
+                                     ON actor.usr.profile = permission.grp_tree.id\r
+                                   INNER JOIN actor.org_unit\r
+                                     ON actor.org_unit.id = actor.usr.home_ou\r
+                                 WHERE actor.usr.deleted IS FALSE\r
+                                 GROUP BY permission.grp_tree.name, actor.org_unit.name\r
+                                 ORDER BY actor.org_unit.name, permission.grp_tree.name\r
+                               ;\r
+\r
+                                name  |        name        | count\r
+                               -------+--------------------+-------\r
+                                Users | Example Consortium |     1\r
+                               (1 row)</programlisting>\r
+                               </para></formalpara>\r
+                       </simplesect>\r
+                       <simplesect id="_aliases">\r
+                               <title>Aliases</title>\r
+                               <simpara>So far we have been fully-qualifying all of our table names and column names to\r
+                               prevent any confusion. This quickly gets tiring with lengthy qualified\r
+                               table names like <literal>permission.grp_tree</literal>, so the SQL syntax enables us to assign\r
+                               aliases to table names and column names. When you define an alias for a table\r
+                               name, you can access its column throughout the rest of the statement by simply\r
+                               appending the column name to the alias with a period; for example, if you assign\r
+                               the alias <literal>au</literal> to the <literal>actor.usr</literal> table, you can access the <literal>actor.usr.id</literal>\r
+                               column through the alias as <literal>au.id</literal>.</simpara>\r
+                               <simpara>The formal syntax for declaring an alias for a column is to follow the column\r
+                               name in the result columns clause with <literal>AS</literal> <emphasis>alias</emphasis>. To declare an alias for a table name,\r
+                               follow the table name in the FROM clause (including any JOIN statements) with\r
+                               <literal>AS</literal> <emphasis>alias</emphasis>. However, the <literal>AS</literal> keyword is optional for tables (and columns as\r
+                               of PostgreSQL 8.4), and in practice most SQL statements leave it out.  For\r
+                               example, we can write the previous INNER JOIN statement example using aliases\r
+                               instead of fully-qualified identifiers:</simpara>\r
+                               <formalpara><title>Borrower Count by Profile (using aliases)</title><para>\r
+                               <programlisting language="sql" linenumbering="unnumbered">SELECT pgt.name AS "Profile", aou.name AS "Library", COUNT(pgt.name) AS "Count"\r
+                                 FROM actor.usr au\r
+                                   INNER JOIN permission.grp_tree pgt\r
+                                     ON au.profile = pgt.id\r
+                                   INNER JOIN actor.org_unit aou\r
+                                     ON aou.id = au.home_ou\r
+                                 WHERE au.deleted IS FALSE\r
+                                 GROUP BY pgt.name, aou.name\r
+                                 ORDER BY aou.name, pgt.name\r
+                               ;\r
+\r
+                                Profile |      Library       | Count\r
+                               ---------+--------------------+-------\r
+                                Users   | Example Consortium |     1\r
+                               (1 row)</programlisting>\r
+                               </para></formalpara>\r
+                               <simpara>A nice side effect of declaring an alias for your columns is that the alias\r
+                               is used as the column header in the results table. The previous version of\r
+                               the query, which didn&#8217;t use aliased column names, had two columns named\r
+                               <literal>name</literal>; this version of the query with aliases results in a clearer\r
+                               categorization.</simpara>\r
+                       </simplesect>\r
+                       <simplesect id="_outer_joins">\r
+                               <title>Outer joins</title>\r
+                               <simpara>An outer join returns all of the rows from one or both of the tables\r
+                               participating in the join.</simpara>\r
+                               <itemizedlist>\r
+                               <listitem>\r
+                               <simpara>\r
+                               For a LEFT OUTER JOIN, the join returns all of the rows from the left-hand\r
+                               table and the rows matching the join condition from the right-hand table, with\r
+                               NULL values for the rows with no match in the right-hand table.\r
+                               </simpara>\r
+                               </listitem>\r
+                               <listitem>\r
+                               <simpara>\r
+                               A RIGHT OUTER JOIN behaves in the same way as a LEFT OUTER JOIN, with the\r
+                               exception that all rows are returned from the right-hand table participating in\r
+                               the join.\r
+                               </simpara>\r
+                               </listitem>\r
+                               <listitem>\r
+                               <simpara>\r
+                               For a FULL OUTER JOIN, the join returns all the rows from both the left-hand\r
+                               and right-hand tables, with NULL values for the rows with no match in either\r
+                               the left-hand or right-hand table.\r
+                               </simpara>\r
+                               </listitem>\r
+                               </itemizedlist>\r
+                               <formalpara><title>Base tables for the OUTER JOIN examples</title><para>\r
+                               <programlisting language="sql" linenumbering="unnumbered">SELECT * FROM aaa;\r
+\r
+                                id | stuff\r
+                               ----+-------\r
+                                 1 | one\r
+                                 2 | two\r
+                                 3 | three\r
+                                 4 | four\r
+                                 5 | five\r
+                               (5 rows)\r
+\r
+                               SELECT * FROM bbb;\r
+\r
+                                id | stuff |   foo\r
+                               ----+-------+----------\r
+                                 1 | one   | oneone\r
+                                 2 | two   | twotwo\r
+                                 5 | five  | fivefive\r
+                                 6 | six   | sixsix\r
+                               (4 rows)</programlisting>\r
+                               </para></formalpara>\r
+                               <formalpara><title>Example of a LEFT OUTER JOIN</title><para>\r
+                               <programlisting language="sql" linenumbering="unnumbered">SELECT * FROM aaa\r
+                                 LEFT OUTER JOIN bbb ON aaa.id = bbb.id\r
+                               ;\r
+                                id | stuff | id | stuff |   foo\r
+                               ----+-------+----+-------+----------\r
+                                 1 | one   |  1 | one   | oneone\r
+                                 2 | two   |  2 | two   | twotwo\r
+                                 3 | three&