HTSQL -- A Query Language for the Accidental Programmer - Mailing list pgsql-announce

I'm thrilled to announce HTSQL -- a new high-level database
query language for relational databases.

We expect that we'll wrap up our beta and have a 2.0 version
tagged by year's end.  Initially we only support PostgreSQL,
but we'll soon add support for MySQL and SQLite in 2.1 release.

Good luck and have fun!

Clark C. Evans & Kirill Simonov


=======================================================
HTSQL -- A Query Language for the Accidental Programmer
=======================================================

HTSQL ("Hyper Text Structured Query Language") is a high-level
query language for relational databases.   The target audience
for HTSQL is the accidental programmer -- one who is not a SQL
expert, yet needs a usable, comprehensive query tool for data
access and reporting.

HTSQL is also a web service which takes a request via HTTP,
translates it into a SQL query, executes the query against a
relational database, and returns the results in a format
requested by the user agent (JSON, CSV, HTML, etc.).

Use of HTSQL with open source databases (PostgreSQL, MySQL,
SQLite) is royalty free under BSD-style conditions.  Use of HTSQL
with proprietary database systems (Oracle, Microsoft SQL)
requires a commercial license. See LICENSE for details.

For installation instructions, see INSTALL.  For list of new
features in this release, see NEWS.  HTSQL documentation is in
the doc directory.

    http://htsql.org/
        The HTSQL homepage

    http://htsql.org/doc/tutorial.html
        The HTSQL tutorial

    http://bitbucket.org/prometheus/htsql
        HTSQL source code

    irc://irc.freenode.net#htsql
        IRC chat in #htsql on freenode

    http://lists.htsql.org/mailman/listinfo/htsql-users
        The mailing list for users of HTSQL

Generous support for HTSQL was provided by Prometheus Research,
LLC and The Simons Foundation. This material is also based upon
work supported by the National Science Foundation under Grant
#0944460. Any opinions, findings, and conclusions or recommendations
expressed in this material are those of the author(s) and do not
necessarily reflect the views of the National Science Foundation.

HTSQL is copyright by Prometheus Research, LLC.  HTSQL is written
by Clark C. Evans <cce@clarkevans.com> and Kirill Simonov
<xi@resolvent.net>.


Examples
========

HTSQL provides outstanding clarity without sacrificing rigor.

Let's assume we have a data model, with schools, departments,
programs and courses.  Here it is:

         +-------------+       +--------+
    /---m| DEPARTMENT  |>-----o| SCHOOL |m----\
    |.   +-------------+  .    +--------+    .|
    | .                  .                  . |
    |   department   department    a school   |
    |   offers       may be part   has one or |
    |   courses      of school     programs   |
    |                                         |
    |    +-------------+       +---------+    |
    \---<| COURSE      |       | PROGRAM |>---/
         +-------------+       +---------+


List all schools
----------------

An HTSQL query:

    /school

An equivalent SQL query:

    SELECT code, name
    FROM ad.school
    ORDER BY code;


Programs ordered by the title
-----------------------------

HTSQL:

    /program{title+}

SQL:

    SELECT title
    FROM ad.program
    ORDER BY title, school, code;


All courses missing a description
---------------------------------

HTSQL:

    /course?!description

SQL:

    SELECT department, number, title, credits, description
    FROM ad.course
    WHERE NULLIF(description, '') IS NULL
    ORDER BY 1, 2;


Departments in schools having "art" in its name
-----------------------------------------------

HTSQL:

    /department?school.name~'art'

SQL:

    SELECT d.code, d.name, d.school
    FROM ad.department AS d
    LEFT OUTER JOIN
         ad.school AS s ON (d.school = s.code)
    WHERE s.name ILIKE '%art%'
    ORDER BY 1;


The number of schools
---------------------

HTSQL:

    /count(school)

SQL:

    SELECT COUNT(TRUE)
    FROM ad.school;


Schools with programs
---------------------

HTSQL:

    /school?exists(program)

SQL:

    SELECT s.code, s.name
    FROM ad.school AS s
    WHERE EXISTS(SELECT TRUE
                 FROM ad.program AS p
                 WHERE s.code = p.school)
    ORDER BY 1;


The number of schools with programs
-----------------------------------

HTSQL:

    /count(school?exists(program))

SQL:

    SELECT COUNT(TRUE)
    FROM ad.school AS s
    WHERE EXISTS(SELECT TRUE
                 FROM ad.program AS p
                 WHERE (s.code = p.school));


Number of programs and departments per school
---------------------------------------------

HTSQL:

    /school{name, count(program), count(department)}

SQL:

    SELECT s.name, COALESCE(p.cnt, 0), COALESCE(d.cnt, 0)
    FROM ad.school AS s
    LEFT OUTER JOIN
         (SELECT COUNT(TRUE) AS cnt, p.school
          FROM ad.program AS p
          GROUP BY 2) AS p ON (s.code = p.school)
    LEFT OUTER JOIN
         (SELECT COUNT(TRUE) AS cnt, d.school
          FROM ad.department AS d
          GROUP BY 2) AS d ON (s.code = d.school)
    ORDER BY s.code;


Average number of courses offered by departments in each school
---------------------------------------------------------------

HTSQL:

    /school{name, avg(department.count(course))}

SQL:

    SELECT s.name, d.av
    FROM ad.school AS s
    LEFT OUTER JOIN
         (SELECT AVG(CAST(COALESCE(c.cnt, 0) AS NUMERIC)) AS av,
                 d.school
          FROM ad.department AS d
          LEFT OUTER JOIN
               (SELECT COUNT(TRUE) AS cnt, c.department
               FROM ad.course AS c
               GROUP BY 2) AS c ON (d.code = c.department)
          GROUP BY 2) AS d ON (s.code = d.school)
    ORDER BY s.code;