Commits

Kirill Simonov committed aae7280

Tutorial: autogenerate output for HTSQL examples.

Comments (0)

Files changed (7)

 
 # Add any Sphinx extension module names here, as strings. They can be extensions
 # coming with Sphinx (named 'sphinx.ext.*') or your custom ones.
-extensions = ['sphinx.ext.autodoc', 'sphinx.ext.graphviz']
+sys.path.append(os.path.abspath('extensions'))
+extensions = ['sphinx.ext.autodoc', 'sphinx.ext.graphviz', 'htsqldoc']
 graphviz_output_format = 'svg'
+htsql_server = 'http://demo.htsql.org'
 
 # Sort automatically documented members by source order (requires Sphinx 1.0).
 autodoc_member_order = 'bysource'

doc/extensions/htsqldoc.py

+
+
+from docutils import nodes
+from docutils.parsers.rst import Directive, directives
+
+from urllib2 import quote, urlopen, Request, HTTPError
+from json import loads
+
+
+class HTSQLDirective(Directive):
+    required_arguments = 1
+    final_argument_whitespace = True
+    option_spec = {
+            'plain': directives.flag,
+            'error': directives.flag,
+    }
+
+    def run(self):
+        env = self.state.document.settings.env
+        query = self.arguments[0].replace("\n", " ")
+        query_node = nodes.literal_block(query, query)
+        query_node['language'] = 'htsql'
+        if not env.config.htsql_server:
+            raise self.error("htsql_server is not set")
+        uri = env.config.htsql_server+quote(query)
+        if not hasattr(env, 'htsql_uris'):
+            env.htsql_uris = {}
+        if uri not in env.htsql_uris:
+            result = load_uri(uri, 'error' in self.options)
+            if not result:
+                raise self.error("failed to load %s" % uri)
+            env.htsql_uris[uri] = result
+        content_type, content = env.htsql_uris[uri]
+        if 'plain' in self.options:
+            content_type = 'text/plain'
+        result_node = build_result(self.content_offset, content_type, content)
+        query_container = nodes.container('', query_node,
+                                          classes=['htsql-input'])
+        result_container = nodes.container('', result_node,
+                                           classes=['htsql-output'])
+        return [query_container, result_container]
+
+
+def load_uri(uri, error=False):
+    try:
+        headers = { 'Accept': 'application/json' }
+        request = Request(uri, headers=headers)
+        response = urlopen(request)
+        content_type = response.info().gettype()
+        content = response.read()
+    except HTTPError, response:
+        if not error:
+            return None
+        content_type = response.headers.gettype()
+        content = response.read()
+    return (content_type, content)
+
+
+def build_result(line, content_type, content):
+    if content_type == 'application/json':
+        data = loads(content)
+        size = len(data[0])
+        widths = [1]*size
+        for row in data:
+            for idx, value in enumerate(row):
+                widths[idx] = max(widths[idx], len(unicode(value)))
+        table_node = nodes.table()
+        group_node = nodes.tgroup(cols=size)
+        table_node += group_node
+        for width in widths:
+            colspec_node = nodes.colspec(colwidth=width)
+            group_node += colspec_node
+        head_node = nodes.thead()
+        group_node += head_node
+        row_node = nodes.row()
+        head_node += row_node
+        for title in data[0]:
+            entry_node = nodes.entry()
+            row_node += entry_node
+            para_node = nodes.paragraph()
+            entry_node += para_node
+            text_node = nodes.Text(title)
+            para_node += text_node
+        body_node = nodes.tbody()
+        group_node += body_node
+        for row in data[1:]:
+            row_node = nodes.row()
+            body_node += row_node
+            for value in row:
+                entry_node = nodes.entry()
+                row_node += entry_node
+                para_node = nodes.paragraph()
+                entry_node += para_node
+                if value is None:
+                    text_node = nodes.Text(u"\u2014")
+                elif value is True:
+                    text_node = nodes.emphasis()
+                    text_node += nodes.Text(u"true")
+                elif value is False:
+                    text_node = nodes.emphasis()
+                    text_node += nodes.Text(u"false")
+                else:
+                    text_node = nodes.Text(unicode(value))
+                para_node += text_node
+        result_node = table_node
+    else:
+        result_node = nodes.literal_block(content, content)
+        result_node['language'] = 'text'
+    return result_node
+
+
+def setup(app):
+    app.add_config_value('htsql_server', None, '')
+    app.add_directive('htsql', HTSQLDirective)
+    app.add_stylesheet('extra.css')
+    app.add_javascript('extra.js')
+
+
   HTSQL Showcase
 ******************
 
+
 What is HTSQL?
 ==============
 
 Show me this HTSQL!
 ===================
 
-HTSQL was designed from the ground up as a self-serve reporting tool 
-for data analysts.  With HTSQL, the easy stuff is truly easy; and, 
+HTSQL was designed from the ground up as a self-serve reporting tool
+for data analysts.  With HTSQL, the easy stuff is truly easy; and,
 well, complex stuff is easy too.
 
 Database Introspection
 HTSQL is a common language usable by software developers, data analysts,
 database administrators, and even business users.
 
+
 What's up Next?
 ===============
 
 
    .. sourcecode:: htsql
 
-      /school{name, 
-          /program{title}, 
-          /department{name}}
+      /school{name,
+              /program{title},
+              /department{name}}
 
    .. sourcecode:: sql
 
 Subsequent releases will add support for MySQL, Oracle and
 Microsoft SQL Server.
 
-The challenge here is providing consistent function definitions 
+The challenge here is providing consistent function definitions
 and semantics that work across various SQL database systems.
 

doc/static/extra.css

   font-size: 95%;
 }
 
+div.htsql-output {
+  max-height: 10em;
+  overflow: auto;
+  display: none;
+}
+
+div.htsql-toggle {
+  float: right;
+  cursor: pointer
+}
+

doc/static/extra.js

+
+
+function initToggles() {
+    $('.htsql-input pre').prepend("<div class=\"htsql-toggle\">[+]</div>");
+    $('.htsql-toggle').click(function () {
+        var output = this;
+        if ($(this).text() == '[+]') {
+            $(this).parents('.htsql-input').next('.htsql-output').css('display', 'block');
+            $(this).text('[-]');
+        }
+        else {
+            $(this).parents('.htsql-input').next('.htsql-output').css('display', 'none');
+            $(this).text('[+]');
+        }
+    });
+}
+
+
+$(initToggles);
+
+

doc/templates/layout.html

 {% extends "!layout.html" %}
-
-{% block extrahead %}
-    <link rel="stylesheet" href="{{ pathto('_static/extra.css', 1) }}" type="text/css" />
-{% endblock %}
-
 HTSQL is a schema-driven URI-to-SQL translator that takes a request over
 HTTP, converts it to a set of SQL queries, executes these queries in a
 single transaction, and returns the results in a format (CSV, HTML,
-JSON, etc.) requested by the user agent:: 
+JSON, etc.) requested by the user agent::
 
   /----------------\                   /------------------------\
   | USER AGENT     |                   |   HTSQL WEB SERVICE    |
-  *----------------*  HTTP Request     *------------------------* 
+  *----------------*  HTTP Request     *------------------------*
   |                | >---------------> -.                       |
   | * Web Browsers |  URI, headers,    | \      .---> Generated |
   |   HTML, TEXT   |  post/put body    |  v    /      SQL Query |
   | * Spreadsheets |  status, header,  |     Query       .      |
   |   CSV, XML     |  csv/html/json    |     Results <---/      |
   |                |  result body      |                        |
-  \----------------/                   \------------------------/  
+  \----------------/                   \------------------------/
 
 The HTSQL query processor does heavy lifting for you.  Using
 relationships between tables as permitted links, the HTSQL processor
 
 We'll use a fictional university that maintains a database for its
 student enrollment system.  There are four tables that describe the
-business units of the university and their relationship to the 
+business units of the university and their relationship to the
 courses offered::
 
-  +--------------------+              +---------------------+     
-  | DEPARTMENT         |              | SCHOOL              |     
-  +--------------------+              +---------------------+     
+  +--------------------+              +---------------------+
+  | DEPARTMENT         |              | SCHOOL              |
+  +--------------------+              +---------------------+
   | code            PK |--\       /--o| code             PK |----\
   | school          FK |>-|------/    | name          NN,UK |    |
   | name         NN,UK |  |    .      +---------------------+    |
   +--------------------+  |           | PROGRAM             |    |
   | department  FK,PK1 |>-/           +---------------------+    |
   | number         PK2 |              | school       PK1,FK |>---/
-  | title           NN |              | code            PK2 |        
-  | credits         NN |              | title            NN |        
-  | description        |              | degree           CK |        
-  +--------------------+              +---------------------+        
+  | title           NN |              | code            PK2 |
+  | credits         NN |              | title            NN |
+  | description        |              | degree           CK |
+  +--------------------+              +---------------------+
 
   PK - Primary Key   UK - Unique Key         FK - Foreign Key
-  NN - Not Null      CK - Check Constraint   
+  NN - Not Null      CK - Check Constraint
 
 The university consists of schools, which administer one or more
 degree-granting programs.  Departments are associated with a school
 
 HTSQL requests typically begin with a table name.  You can browse the
 contents of a table, search for specific data, and select the columns
-you want to see in the results.  
+you want to see in the results.
 
-The most basic HTSQL request (A1_) returns everything from a table::
+The most basic HTSQL request (A1_) returns everything from a table:
 
-   /school 
+.. sourcecode:: htsql
+
+   /school
 
 .. _A1:  http://demo.htsql.org/school
 
 The result set is a list of schools in the university, including all
-columns, sorted by the primary key for the table::
+columns, sorted by the primary key for the table:
 
-    school                                            
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    code | name                     
-    -----+--------------------------
-    art  | School of Art and Design                   
-    bus  | School of Business                
-    edu  | College of Education                       
-    egn  | School of Engineering
-    ...                      
+====  =============================
+code  name
+====  =============================
+art   School of Art and Design
+bus   School of Business
+edu   College of Education
+egn   School of Engineering
+la    School of Arts and Humanities
+mus   School of Music & Dance
+ns    School of Natural Sciences
+ph    Public Honorariums
+sc    School of Continuing Studies
+====  =============================
+
 
 Not all columns are useful for every context.  Use a *selector* to
-choose columns for display (A2_)::
+choose columns for display (A2_):
 
-    /program{school, code, title}
-
-    program
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
-    school | code     | title
-    -------+----------+-----------------------------------
-    art    | gart     | Post Baccalaureate in Art History
-    art    | uhist    | Bachelor of Arts in Art History  
-    art    | ustudio  | Bachelor of Arts in Studio Art   
-    bus    | pacc     | Graduate Certificate in Accounting
-    ...
+.. htsql:: /program{school, code, title}
 
 .. _A2: http://demo.htsql.org/program{school,code,title}
 
 Add a plus (``+``) sign to the column name to sort the column in
 ascending order.  Use a minus sign (``-``) for descending order.  For
-example, this request (A3_) returns departments in descending order::
+example, this request (A3_) returns departments in descending order:
 
-    /department{name-, school}
+.. htsql:: /department{name-, school}
 
-    department                                        
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    name                   | school
-    -----------------------+-------
-    Wind                   | mus   
-    Vocals                 | mus   
-    Teacher Education      | edu   
-    Studio Art             | art
-    ...   
-
-.. _A3: 
+.. _A3:
     http://demo.htsql.org/department{name-,school}
 
 Using two ordering indicators will sort on labeled columns as they
 appear in the selector.  In the example below, we sort in ascending
-order on ``department`` and then descending on ``credits`` (A4_)::
+order on ``department`` and then descending on ``credits`` (A4_):
 
+.. htsql:: /course{department+, number, credits-, title}
+
+.. _A4:
+    http://demo.htsql.org
     /course{department+, number, credits-, title}
 
-    course                                            
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    department | number | credits | title                     
-    -----------+--------+---------+---------------------------
-    acc        | 315    | 5       | Financial Accounting      
-    acc        | 200    | 3       | Principles of Accounting I
-    acc        | 426    | 3       | Corporate Taxation        
-    ...
+To display friendlier names for the columns, use ``:as`` to rename a
+column's title (A5_):
 
-.. _A4: 
-    http://demo.htsql.org
-    /course{department+, number, credits-, title}
- 
-To display friendlier names for the columns, use ``:as`` to rename a
-column's title (A5_)::
+.. htsql:: /course{department+ :as 'Dept Code', number :as 'No.',
+                   credits-, title}
 
-    /course{department+ :as 'Dept Code', number :as 'No.',
-            credits-, title}
-
-    course                                            
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    Dept Code | No. | credits | title                      
-    ----------+-----+---------+----------------------------
-    acc       | 315 | 5       | Financial Accounting       
-    acc       | 200 | 3       | Principles of Accounting I 
-    acc       | 426 | 3       | Corporate Taxation         
-    ...
-
-.. _A5: 
+.. _A5:
     http://demo.htsql.org
     /course{department+%20:as%20'Dept%20Code',number%20:as%20'No.',
             credits-, title}
 
 Selectors let you choose, rearrange, and sort columns of interest.  They
-are an easy way to exclude data that isn't meaningful to your report.   
+are an easy way to exclude data that isn't meaningful to your report.
 
 Linking Data
 ------------
 
 In our example schema, each ``program`` is administered by a ``school``.
 Since the HTSQL processor knows about this relationship, it is possible
-to link data accordingly (B1_)::
+to link data accordingly (B1_):
 
-    /program{school.name, title}
+.. htsql:: /program{school.name, title}
 
-    program                                           
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    school.name               | title                             
-    --------------------------+-----------------------------------
-    School of Art and Design  | Post Baccalaureate in Art History 
-    School of Art and Design  | Bachelor of Arts in Art History   
-    School of Art and Design  | Bachelor of Arts in Studio Art    
-    School of Business        | Graduate Certificate in Accounting
-    ...
-
-.. _B1: 
+.. _B1:
     http://demo.htsql.org
     /program{school.name, title}
 
 This request joins the ``program`` and ``school`` tables by the foreign
 key from ``program{school}`` to ``school{code}``.  This is called a
 *singular* relationship, since for every ``program``, there is exactly
-one ``school``.  
+one ``school``.
 
 It is possible to join through multiple foreign keys; since ``course``
 is offered by a ``department`` which belongs to a ``school``, we can
-list courses including school and department name (B2_)::
+list courses including school and department name (B2_):
 
-    /course{department.school.name, department.name, title}
+.. htsql:: /course{department.school.name, department.name, title}
 
-    course                                           
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    department.school.name | department.name | title                    
-    -----------------------+-----------------+---------------------------
-    School of Business     | Accounting      | Practical Bookkeeping      
-    School of Business     | Accounting      | Principles of Accounting I 
-    School of Business     | Accounting      | Financial Accounting       
-    School of Business     | Accounting      | Corporate Taxation         
-    ...
-
-.. _B2: 
+.. _B2:
     http://demo.htsql.org
     /course{department.school.name, department.name, title}
 
 This request can be shortened a bit by collapsing the duplicate mention
-of ``department``; the resulting request is equivalent (B3_)::
+of ``department``; the resulting request is equivalent (B3_):
 
-    /course{department{school.name, name}, title}
+.. htsql:: /course{department{school.name, name}, title}
 
-.. _B3: 
+.. _B3:
     http://demo.htsql.org
     /course{department{school.name, name}, title}
 
 For cases where you don't wish to specify each column explicitly, use
 the wildcard ``*`` selector.  The request below returns all columns from
-``department`` and all columns from its correlated ``school`` (B4_)::
+``department`` and all columns from its correlated ``school`` (B4_):
 
+.. htsql:: /department{*,school.*}
+
+.. _B4:
+    http://demo.htsql.org
     /department{*,school.*}
 
-    department                                       
-    ~~~~~~~~~~~~~~~~~~~~ ... ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ...
-    code   | name        ... | school.name                ...  
-    -------+------------ ... +--------------------------- ...  
-    acc    | Accounting  ... | School of Business         ...  
-    arthis | Art History ... | School of Art and Design   ...  
-    artstd | Studio Art  ... | School of Art and Design   ...  
-    astro  | Astronomy   ... | School of Natural Sciences ...  
-    ...
-
-.. _B4: 
-    http://demo.htsql.org
-    /department{*,school.*}
-    
 Since the HTSQL processor knows about relationships between tables in
 your relational database, joining tables in your reports is trivial.
 
 Filtering Data
 --------------
 
-Predicate expressions in HTSQL follow the question mark ``?``.  
+Predicate expressions in HTSQL follow the question mark ``?``.
 For example, to return departments in the 'School of Engineering'
-we write (C1_)::
-  
-    /department?school='egn'
+we write (C1_):
 
-    department                            
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    code | name                   | school
-    -----+------------------------+-------
-    be   | Bioengineering         | egn   
-    comp | Computer Science       | egn   
-    ee   | Electrical Engineering | egn   
-    me   | Mechanical Engineering | egn
-    ...
+.. htsql:: /department?school='egn'
 
-.. _C1: 
+.. _C1:
     http://demo.htsql.org
     /department?school='egn'
 
 
 Often times we want to compare a column against values from a list.  The
 next example returns rows from the ``program`` table for the "Bachelors
-of Arts" (``'ba'``) or "Bachelors of Science" (``'bs'``) degrees (C2_)::
+of Arts" (``'ba'``) or "Bachelors of Science" (``'bs'``) degrees (C2_):
 
-    /program?degree={'ba','bs'}
+.. htsql:: /program?degree={'ba','bs'}
 
-    program                                                    
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    school | code     | title                             | degree
-    -------+----------+-----------------------------------+-------
-    art    | uhist    | Bachelor of Arts in Art History   | ba
-    art    | ustudio  | Bachelor of Arts in Studio Art    | ba
-    bus    | uacct    | Bachelor of Science in Accounting | bs
-    ...
-
-.. _C2: 
+.. _C2:
     http://demo.htsql.org
     /program?degree={'ba','bs'}
 
 Complex filters can be created using boolean connectors, such as the
 conjunction (``&``) and alternation (``|``) operators .  The following
 request returns programs in the "School of Business" that do not
-grant a "Bachelor of Science" degree (C3_)::
+grant a "Bachelor of Science" degree (C3_):
 
-    /program?school='bus'&degree!='bs'
+.. htsql:: /program?school='bus'&degree!='bs'
 
-    program                                                    
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    school | code | title                              | degree
-    -------+------+------------------------------------+-------
-    bus    | mba  | Masters of Business Administration | mb    
-    bus    | pacc | Graduate Certificate in Accounting | ct    
-    bus    | pcap | Certificate in Capital Markets     | ct
-    ...
-
-.. _C3: 
+.. _C3:
     http://demo.htsql.org
     /program?school='bus'&degree!='bs'
 
 Filters can be combined with selectors and links.  The following request
 returns courses, listing only department number and title, having less
-than 3 credits in the "School of Natural Science" (C4_)::
+than 3 credits in the "School of Natural Science" (C4_):
 
-    /course{department, number, title}
-      ?credits<3&department.school='ns'
+.. htsql:: /course{department, number, title}
+            ?credits<3&department.school='ns'
 
-    course                                              
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    department | number | title                         
-    -----------+--------+-------------------------------
-    phys       | 388    | Experimental Physics I        
-    chem       | 115    | Organic Chemistry Laboratory I
-    astro      | 142    | Solar System Lab              
-    astro      | 155    | Telescope Workshop            
-    ...
-
-.. _C4: 
+.. _C4:
     http://demo.htsql.org
     /course{department, number, title}
        ?credits<3&department.school='ns'
 
 It is sometimes desirable to specify the filter before the selector.
 Using a *table expression*, denoted by parenthesis, the previous request
-is equivalent to (C5_)::
+is equivalent to (C5_):
 
-    /(course?credits<3&department.school='ns')
-      {department, number, title}
+.. htsql:: /(course?credits<3&department.school='ns')
+            {department, number, title}
 
-.. _C5: 
+.. _C5:
     http://demo.htsql.org
     /(course?credits<3&department.school='ns')
       {department, number, title}
 response.  By default, HTSQL uses the ``Accept`` header to negotiate the
 output format with the user agent.  This can be overridden with a format
 command, such as ``/:json``.  For example, results in JSON format (RFC
-4627) can be requested as follows (D1_)::
+4627) can be requested as follows (D1_):
 
-    /school/:json
+.. htsql:: /school/:json
+   :plain:
 
-    [
-      ["code", "name"],
-      ["art", "School of Art and Design"],
-      ["bus", "School of Business"],
-      ["edu", "College of Education"],
-      ["egn", "School of Engineering"],
-      ["la", "School of Arts, Letters, and the Humanities"],
-      ["mart", "School of Modern Art"],
-      ["mus", "Musical School"],
-      ["ns", "School of Natural Sciences"],
-      ["sc", "School of Continuing Studies"]
-    ]
-
-.. _D1: 
+.. _D1:
     http://demo.htsql.org
     /school/:json
 
 Other formats include ``/:txt`` for plain-text formatting, ``/:html`` for
-display in web browsers, and ``/:csv`` for data exchange. 
+display in web browsers, and ``/:csv`` for data exchange.
 
 Putting it All Together
 -----------------------
 The following request selects records from the ``course`` table,
 filtered by all departments in the 'School of Business', sorted by
 ``course`` ``title``, including ``department``'s ``code`` and ``name``,
-and returned as a "Comma-Separated Values" (RFC 4180) (E1_)::
+and returned as a "Comma-Separated Values" (RFC 4180) (E1_):
 
-    /course{department{code,name},number,title+}?
-      department.school='bus'/:csv
+.. htsql:: /course{department{code,name},number,title+}?
+            department.school='bus'
+            /:csv
 
-    department.code,department.name,number,title
-    corpfi,Corporate Finance,234,Accounting Information Systems
-    acc,Accounting,527,Advanced Accounting
-    capmrk,Capital Markets,756,Capital Risk Management
-    corpfi,Corporate Finance,601,Case Studies in Corporate Finance
-    ... 
-
-.. _E1: 
+.. _E1:
     http://demo.htsql.org
     /course{department{code,name},number,title+}?
           department.school='bus'/:csv
-    
+
 HTSQL requests are powerful without being complex.  They are easy to
 read and modify.  They adapt to changes in the database.  These
 qualities increase the usability of databases by all types of users and
 record.  To select a *plural* expression in a result set, an *aggregate*
 function, such as ``sum``, ``count``, or ``exists`` must be used.  In
 this way, what would be many values is converted into a single data cell
-and integrated into a coherent result set. 
+and integrated into a coherent result set.
 
 By requiring aggregates for plural expressions, HTSQL reduces query
 construction time and reduces errors.  When a query starts with a table,
 rows returned are directly correlated to records in this table. Since
 cross products or projections cannot be created accidentally, the
-combined result set is always consistent and understandable. 
+combined result set is always consistent and understandable.
 
 Basic Linking
 -------------
 singular in one direction and plural in the other.
 
 If each row in your result set represents a ``course``, it is easy to
-get correlated information for each course's department (RA1_)::
+get correlated information for each course's department (RA1_):
 
-    /course{department.name, title}
-
-    course                                              
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    department.name        | title                      
-    -----------------------+----------------------------
-    Accounting             | Practical Bookkeeping      
-    Accounting             | Principles of Accounting I 
-    Accounting             | Financial Accounting       
-    ...
+.. htsql:: /course{department.name, title}
 
 .. _RA1:
     http://demo.htsql.org
 It's possible to join *up* a hierarchy in this way, but not down. If
 each row in your result set is a ``department``, then it is an error to
 request ``course``'s ``credits`` since there could be many courses in a
-given department (RA2_)::
+given department (RA2_):
 
-    /department{name, course.credits}
-    
-    400 Bad Request
-
-    a singular expression is required at position 26:
-    /department{name, course.credits}
-                             ^------
+.. htsql:: /department{name, course.credits}
+   :error:
 
 .. _RA2:
     http://demo.htsql.org
 
 In cases like this, an aggregate function, such as ``max`` is needed to
 convert a plural expression into a singular value.  The following
-example shows the maximum course credits by department (RA3_)::
+example shows the maximum course credits by department (RA3_):
 
-    /department{name, max(course.credits)}
-
-    department                          
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    name             | max(course.credits)
-    -----------------+--------------------
-    Accounting       |                   5
-    Alumni & Parents |                   4
-    Art History      |                   4
-    ...
+.. htsql:: /department{name, max(course.credits)}
 
 .. _RA3:
     http://demo.htsql.org
 
 Conversely, you cannot use aggregates with singular expressions.  For
 example, since ``school`` is singular relative to ``department``, it is
-an error to count them (RA4_)::
+an error to count them (RA4_):
 
-    /department{name, count(school)}
-    
-    400 Bad Request
-
-    a plural expression is required at position 25:
-    /department{name, count(school)}
-                            ^-----
+.. htsql:: /department{name, count(school)}
+   :error:
 
 .. _RA4:
     http://demo.htsql.org
 
 For single row or *scalar* expressions, an aggregate is always needed
 when referencing a table.  For example, the query below returns maximum
-number of course credits across all departments (RA5_)::
+number of course credits across all departments (RA5_):
 
-    /max(course.credits)
-
-    max(course.credits)                                                    
-    -------------------                                                    
-                      8                                                    
-                (1 row)    
+.. htsql:: /max(course.credits)
 
 .. _RA5:
     http://demo.htsql.org
 Aggregate Expressions
 ---------------------
 
-Since ``school`` table has a *plural* (one to many) relationship 
-with ``program`` and ``department``, we can count them (RB1_)::
+Since ``school`` table has a *plural* (one to many) relationship
+with ``program`` and ``department``, we can count them (RB1_):
 
-    /school{name, count(program), count(department)}
+.. htsql:: /school{name, count(program), count(department)}
 
-    school
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    name                     | count(program) | count(department)
-    -------------------------+----------------+------------------
-    School of Art and Design | 3              | 2                
-    School of Business       | 5              | 3                
-    College of Education     | 7              | 2                
-    School of Engineering    | 8              | 4                
-    ...
-
-.. _RB1: 
+.. _RB1:
     http://demo.htsql.org
     /school{name,count(program),count(department)}
 
-Filters may be used within an aggregate expression.  For example, the 
+Filters may be used within an aggregate expression.  For example, the
 following returns the number of courses, by department, that are at
-the 400 level or above (RB2_)::
+the 400 level or above (RB2_):
 
-    /department{name, count(course?number>=400)}
-
-    department
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    name           | count(course?number>=400)
-    ---------------+--------------------------
-    Accounting     |                         3
-    Art History    |                         4
-    Astronomy      |                         0
-    Bioengineering |                         2
-    ...
+.. htsql:: /department{name, count(course?number>=400)}
 
 .. _RB2:
     http://demo.htsql.org
     /department{name, count(course?number>=400)}
 
 It's possible to nest aggregate expressions.  This request returns the
-average number of courses each department offers (RB3_)::
+average number of courses each department offers (RB3_):
 
-    /school{name, avg(department.count(course))}
-
-    school                                                  
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    name                     | avg(department.count(course))
-    -------------------------+------------------------------
-    School of Art and Design |            6.5000000000000000
-    School of Business       |            4.3333333333333333
-    College of Education     |            5.0000000000000000
-    School of Engineering    |            5.2500000000000000
-    ...
+.. htsql:: /school{name, avg(department.count(course))}
 
 .. _RB3:
     http://demo.htsql.org
     /school{name, avg(department.count(course))}
 
 Filters and nested aggregates can be combined.  Here we count, for each
-school, departments offering 4 or more credits (RB4_)::
+school, departments offering 4 or more credits (RB4_):
 
-    /school{name, count(department?exists(course?credits>3))}
-
-    school                                                               
-    ---------------------------------------------------------------------
-    name                     | count(department?exists(course?credits>3))
-    -------------------------+-------------------------------------------
-    School of Art and Design |                                          2
-    School of Business       |                                          1
-    College of Education     |                                          1
-    School of Engineering    |                                          4
-    ...
+.. htsql:: /school{name, count(department?exists(course?credits>3))}
 
 .. _RB4:
     http://demo.htsql.org
     /school{name, count(department?exists(course?credits>3))}
 
 Filtering can be done on one column, with aggregation on another.  This
-example shows average credits from only high-level courses (RB5_)::
+example shows average credits from only high-level courses (RB5_):
 
-    /department{name, avg((course?number>400).credits)}
-
-    department                                       
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    name           | avg((course?number>400).credits)
-    ---------------+---------------------------------
-    Accounting     |               3.0000000000000000
-    Art History    |               3.2500000000000000
-    Astronomy      |                                 
-    Bioengineering |               5.5000000000000000
+.. htsql:: /department{name, avg((course?number>400).credits)}
 
 .. _RB5:
     http://demo.htsql.org
     /department{name, avg((course?number>400).credits)}
 
 Numerical aggregates are supported.  This request computes some useful
-``course.credit`` statistics (RB6_)::
+``course.credit`` statistics (RB6_):
 
-    /department{code, min(course.credits), max(course.credits), 
-                      avg(course.credits)}
-
-    department                                                              
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    code   | min(course.credits) | max(course.credits) | avg(course.credits)
-    -------+---------------------+---------------------+--------------------
-    acc    |                   2 |                   5 |  3.1666666666666667
-    arthis |                   3 |                   4 |  3.1666666666666667
-    astro  |                   1 |                   3 |  2.2500000000000000
-    be     |                   3 |                   8 |  4.2500000000000000
-    ...
+.. htsql:: /department{code, min(course.credits), max(course.credits),
+                       avg(course.credits)}
 
 .. _RB6:
     http://demo.htsql.org
-    /department{code, min(course.credits), max(course.credits), 
+    /department{code, min(course.credits), max(course.credits),
                       avg(course.credits)}
 
 The ``every`` aggregate tests that a predicate is true for every row in
 the correlated set.  This example returns ``department`` records that
 either lack correlated ``course`` records or where every one of those
-``course`` records have exactly ``3`` credits (RB7_)::
+``course`` records have exactly ``3`` credits (RB7_):
 
-    /department{name, avg(course.credits), count(course)} 
-      ?every(course.credits=3)
-
-    department
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    name               | avg(course.credits) | count(course)
-    -------------------+---------------------+--------------
-    Bursar's Office    |                     |             0
-    Capital Markets    |  3.0000000000000000 |             4
-    Career Development |                     |             0
-    Corporate Finance  |  3.0000000000000000 |             3
-    ...
+.. htsql:: /department{name, avg(course.credits), count(course)}
+            ?every(course.credits=3)
 
 .. _RB7:
     http://demo.htsql.org
-    /department{name, avg(course.credits), count(course)} 
+    /department{name, avg(course.credits), count(course)}
       ?every(course.credits=3)
 
 
 Comparison Operators
 --------------------
 
-The quality operator (``=``) is overloaded to support various types. 
+The quality operator (``=``) is overloaded to support various types.
 For character strings, this depends upon the underlying database's
 collation rules but typically is case-sensitive.  For example, to return
-a ``course`` by ``title`` (PC1_)::
+a ``course`` by ``title`` (PC1_):
 
-    /course?title='Drawing'
-
-    course
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    department | number    | title   | credits 
-    -----------+-----------+---------+---------
-    stdart     | 333       | Drawing | 3         
+.. htsql:: /course?title='Drawing'
 
 .. _PC1:
     http://demo.htsql.org
 
 If you're not sure of the exact course title, use the case-insensitive
 *contains* operator (``~``).  The example below returns all ``course``
-records that contain the substring ``'lab'`` (PC2_)::
+records that contain the substring ``'lab'`` (PC2_):
 
-    /course?title~'lab'
-
-    course
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    department | number | title                          | credits
-    -----------+--------+--------------------------------+--------
-    astro      |    142 | Solar System Lab               |       2
-    chem       |    115 | Organic Chemistry Laboratory I |       2
-    chem       |    314 | Laboratory Chemistry           |       3
-    comp       |    710 | Laboratory in Computer Science |       4
-    ...
+.. htsql:: /course?title~'lab'
 
 .. _PC2:
     http://demo.htsql.org
     /course?title~'lab'
 
 Use the *not-contains* operator (``!~``) to exclude all courses with
-physics in the title (PC3_)::
+physics in the title (PC3_):
 
-    /course?title!~'lab'
+.. htsql:: /course?title!~'lab'
 
 .. _PC3:
     http://demo.htsql.org
     /course?title!~'lab'
 
-To exclude a specific class, use the *not-equals* operator (PC4_)::
+To exclude a specific class, use the *not-equals* operator (PC4_):
 
-    /course?title!='Organic Chemistry Laboratory I'
+
+.. htsql:: /course?title!='Organic Chemistry Laboratory I'
 
 .. _PC4:
     http://demo.htsql.org
 
 
 The *equality* (``=``) and *inequality* (``!=``) operators are
-straightforward when used with numbers (PC5_)::
+straightforward when used with numbers (PC5_):
 
-    /course{department,number,title}?number=101 
- 
-    course                                                 
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    department | number | title                      
-    -----------+--------+----------------------------
-    eng        |    101 | Introduction to Composition
-    mth        |    101 | College Algebra            
-    ...
+.. htsql:: /course{department,number,title}?number=101
 
 .. _PC5:
     http://demo.htsql.org
 
 The *in* operator (``={}``) can be thought of as equality over a set.
 This example, we return courses that are in neither the "Art History"
-nor the "Studio Art" department (PC6_)::
+nor the "Studio Art" department (PC6_):
 
-    /course?department!={'arthis','stdart'}
+.. htsql:: /course?department!={'arthis','stdart'}
 
 .. _PC6:
     http://demo.htsql.org
     /course?department!={'arthis','stdart'}
 
 Use the *greater-than* (``>``) operator to request courses with more
-than 3 credits (PC7_):: 
+than 3 credits (PC7_):
 
-     /course?credits>3
-
-     course
-     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-     department | number    | title                     | credits
-     -----------+-----------+---------------------------+--------
-     arthis     | 712       | Museum and Gallery Mgmt   | 4        
-     stdart     | 411       | Underwater Basket Weaving | 4         
-     .
+.. htsql:: /course?credits>3
 
 .. _PC7:
     http://demo.htsql.org
     /course?credits>3
 
 Use the *greater-than-or-equal-to* (``>=``) operator request courses
-that have three credits or more (PC8_)::
+that have three credits or more (PC8_):
 
-    /course?credits>=3
+.. htsql:: /course?credits>=3
 
 .. _PC8:
     http://demo.htsql.org
 Using comparison operators with strings tells HTSQL to compare them
 alphabetically (once again, dependent upon database's collation).  For
 example, the *greater-than* (``>``) operator can be used to request
-departments whose ``code`` follows ``'me'`` in the alphabet (PC9_)::
+departments whose ``code`` follows ``'me'`` in the alphabet (PC9_):
 
-    /department?code>'me'
-
-    department                         
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    code  | name              | school
-    ------+-------------------+-------
-    mth   | Mathematics       | ns    
-    phys  | Physics           | ns    
-    pia   | Piano             | mus   
-    poli  | Political Science | la    
-    ...
+.. htsql:: /department?code>'me'
 
 .. _PC9:
     http://demo.htsql.org
 
 HTSQL uses function notation for constants such as ``true()``, ``false()``
 and ``null()``.  For the text formatter, a ``NULL`` is shown as a blank,
-while the empty string is presented as a double-quoted pair (PA1_)::
+while the empty string is presented as a double-quoted pair (PA1_):
 
-    /{true(), false(), null(), ''}
-
-                            
-    true() | false() | null() | '' 
-    -------+---------+--------+---
-    true   | false   |        | ""
+.. htsql:: /{true(), false(), null(), ''}
 
 .. _PA1:
     http://demo.htsql.org
 
 The ``is_null()`` function returns ``true()`` if it's operand is
 ``null()``.  In our schema, non-academic ``department`` records with
-a ``NULL`` ``school`` can be listed (PA2_)::
+a ``NULL`` ``school`` can be listed (PA2_):
 
-    /department{code, name}?is_null(school)
-
-    department
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    code    | name              
-    --------+-------------------
-    bursar  | Bursar's Office   
-    career  | Career Development
-    parent  | Parents & Alumni  
-                        (3 rows)
+.. htsql:: /department{code, name}?is_null(school)
 
 .. _PA2:
     http://demo.htsql.org
     /department{code, name}?is_null(school)
 
 The *negation* operator (``!``) is ``true()`` when it's operand is
-``false()``.   To skip non-academic ``department`` records (PA3_)::
+``false()``.   To skip non-academic ``department`` records (PA3_):
 
-    /department{code, name}?!is_null(school)
-
-    department
-    ~~~~~~~~~~~~~~~~~~~~~~~
-    code   | name          
-    -------+---------------
-    acc    | Accounting          
-    arthis | Art History         
-    astro  | Astronomy           
-    be     | Bioengineering      
-    ...
+.. htsql:: /department{code, name}?!is_null(school)
 
 .. _PA3:
     http://demo.htsql.org
 
 The *conjunction* (``&``) operator is ``true()`` only if both of its
 operands are ``true()``.   This example asks for courses in the
-``'Accounting'`` department having less than 3 credits (PA4_)::
+``'Accounting'`` department having less than 3 credits (PA4_):
 
-    /course?department='acc'&credits<3
-
-    course
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    department | number    | title                | credits
-    -----------+-----------+----------------------+--------
-    acc        | 100       | Practical Bookkeeping | 2
+.. htsql:: /course?department='acc'&credits<3
 
 .. _PA4:
     http://demo.htsql.org
 
 The *alternation* (``|``) operator is ``true()`` if either of its
 operands is ``true()``.  For example, we could list courses having
-anomalous number of credits (PA5_)::
+anomalous number of credits (PA5_):
 
-    /course?credits>4|credits<3
-  
-   course
-   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-   department | number | title                          | credits
-   -----------+--------+--------------------------------+--------
-   acc        |    100 | Practical Bookkeeping          |       2
-   acc        |    315 | Financial Accounting           |       5
-   astro      |    142 | Solar System Lab               |       2
-   astro      |    155 | Telescope Workshop             |       1
-   ...
+.. htsql:: /course?credits>4|credits<3
 
 .. _PA5:
     http://demo.htsql.org
 more tightly than alternation.  Parenthesis can be used to override this
 default grouping rule or to better clarify intent.  The next example
 returns courses that are in "Art History" or "Studio Art" departments
-that have more than three credits (PA6_)::
+that have more than three credits (PA6_):
 
-    /course?(department='arthis'|department='stdart')&credits>3
-
-    course                                                       
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
-    department | number | title                         | credits
-    -----------+--------+-------------------------------+-------- 
-    arthis     | 712    | Museum and Gallery Management | 4       
-    stdart     | 411    | Underwater Basket Weaving     | 4       
-    stdart     | 509    | Twentieth Century Printmaking | 4       
-    stdart     | 614    | Drawing Master Class          | 5       
-    ...
+.. htsql:: /course?(department='arthis'|department='stdart')&credits>3
 
 .. _PA6:
     http://demo.htsql.org
     /course?(department='arthis'|department='stdart')&credits>3
 
 Without the parenthesis, the expression above would show all courses
-from ``'arthis'`` regardless of credits (PA7_)::
+from ``'arthis'`` regardless of credits (PA7_):
 
-    /course?department='arthis'|department='stdart'&credits>3
-
-    course
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    department | number | title                           | credits
-    -----------+--------+---------------------------------+--------
-    arthis     | 202    | History of Art Criticism        | 3        
-    arthis     | 340    | Arts of Asia                    | 3        
-    arthis     | 623    | Contemporary Latin American Art | 3        
-    ...       
+.. htsql:: /course?department='arthis'|department='stdart'&credits>3
 
 .. _PA7:
     http://demo.htsql.org
 rule shortens URLs and makes them more readable.
 
 For example, this query returns only ``course`` records having a
-``description`` (PA8_)::
-    
-    /course?description
-    
+``description`` (PA8_):
+
+.. htsql:: /course?description
+
 .. _PA8:
     http://demo.htsql.org
     /course?description
 
 The predicate ``?description`` is treated as a short-hand for
 ``?(!is_null(description)&description!='')``.  The negated variant of
-this shortcut is more illustrative (PA9_)::
+this shortcut is more illustrative (PA9_):
 
-    /course{department,number,description}? !description
-
-    course
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    department | number | description
-    -----------+--------+------------
-    acc        |    100 |            
-    me         |    627 | ""         
-                             (2 rows)
+.. htsql:: /course{department,number,description}? !description
 
 .. _PA9:
     http://demo.htsql.org
 
 HTSQL supports *boolean*, *date*, *numeric*, and *string* data types, as
 well as variants.  The pluggable type system can be used to augment the
-core types provided. 
+core types provided.
 
 Working with NULLs
 ------------------
 however, careful attention must be paid.  For starters, the standard
 equality operator (``=``) is null-regular, that is, if either operand is
 ``null()`` the result is ``null()``.  The following request always
-returns 0 rows (WN1_)::
-  
-   /department?school=null()
+returns 0 rows (WN1_):
 
-    department
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    code    | name              
-    --------+-------------------
-                          0 rows
+.. htsql:: /department?school=null()
 
 .. _WN1:
     http://demo.htsql.org
 While you wouldn't directly write that query, it could be the final
 result after parameter substitution for a templatized query such as
 ``/department?school=$var``.  For cases like this, use *total equality*
-operator (``==``) which treats ``NULL`` values as equivalent (WN2_)::
+operator (``==``) which treats ``NULL`` values as equivalent (WN2_):
 
-    /department?school==null()
-
-    department
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    code    | name              
-    --------+-------------------
-    bursar  | Bursar's Office   
-    career  | Career Development
-    parent  | Parents & Alumni  
-                        (3 rows)
+.. htsql:: /department?school==null()
 
 .. _WN2:
     http://demo.htsql.org
     /department?school==null()
 
-The ``!==`` operator lists distinct values, including records with 
-a ``NULL`` for the field tested (WN3_)::
+The ``!==`` operator lists distinct values, including records with
+a ``NULL`` for the field tested (WN3_):
 
-    /department?school!=='art'
-
-    department
-    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    code   | name                   | school
-    -------+------------------------+-------
-    acc    | Accounting             | bus   
-    astro  | Astronomy              | ns    
-    be     | Bioengineering         | egn   
-    bursar | Bursar's Office        |       
-    capmrk | Capital Markets        | bus   
-    ...
+.. htsql:: /department?school!=='art'
 
 .. _WN3:
     http://demo.htsql.org
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.