HTSQL -- A Query Language for Accidental Programmers

HTSQL is a high-level navigational query language for relational databases. It is written by Kirill Simonov and Clark Evans in the Python language. HTSQL works with SQLite, PostgreSQL, MySQL, Oracle, and Microsoft SQL Server.

Presenter:Clark C. Evans, Prometheus Research, LLC
Date:March 24th, 2011
Location:PgEast US 2011, New York, NY

HTSQL Query Preview

For a university database, the following HTSQL request returns returns schools, and for each school the number of corresponding programs and departments (link):

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

The un-optimized SQL equivalent is:

SELECT sch.name,
   (SELECT COUNT(1) FROM program
     WHERE school = sch.code),
   (SELECT COUNT(1) FROM department
     WHERE school = sch.code)
   FROM school AS sch
 ORDER BY sch.code ASC

HTSQL Query Preview (cont'd)

/school{name, count(program), count(department)} optimally translates into:

SELECT school.name,
       coalesce(correlated_department.count,0),
       coalesce(correlated_program.count,0),
FROM school as school
LEFT OUTER JOIN (
   SELECT school, COUNT(1) as count
   FROM department GROUP BY school
) AS correlated_department
ON (correlated_department.school = school.code)
LEFT OUTER JOIN (
   SELECT school, COUNT(1) as count
   FROM program GROUP BY school
) AS correlated_program
ON (correlated_program.school = school.code)
ORDER BY school.code ASC

Use as a Web Service

img/webservice.png

Use via .htsql Gateway

This permits standard HTML forms:

<form action="/canned.htsql">
  Enter an parameter value:
  <input type="text" name="arg" />
</form>

Direct use in Python

HTSQL can be used either as a WSGI component, or directly as a data query tool within Python.

::
>>> from htsql import HTSQL
>>> from htsql.request import produce
>>> htsql = HTSQL('pgsql:htsql_regress')
>>> with htsql:
...     for row in produce('/school'):
...         print row
...

HTRAF Dashboarding

img/htraf_screenshot.png

HTRAF Dashboard Source

<body>
<h3>Select a School</h3>
<select id="school"
  data-htsql="/school{code, name}?exists(department)">
</select>
<h3>Departments</h3>
<p>Filter by name: <input id="dept_name"/></p>
<table id="department"
  data-htsql="/department{code, name, school.name}
              ?school=$school&name~$dept_name"></table>
<p>The number of courses in this department:
  <strong data-htsql="/department{count(course)}
                      ?code=$department"></strong>
</p>
<h3>Courses</h3>
<table id="course"
  data-htsql="/course?department=$department"></table>
</body>

Evolution of HTSQL

What is HTSQL?

HTSQL is...

img/what_is_it.png

HTSQL processor takes a URL, translates it into SQL, executes the query against the relational backend, and returns the formatted result.

Relational Database Web Service

HTSQL wraps your database with a comprehensive, documented web service API.

Multi-Targeting Optimizing Compiler

HTSQL is a full-blown compiler targeting SQL variants from various DBMS implementations.

Social Data Set Sharing (1/2)

img/triangle.png

Social Data Set Sharing (2/2)

img/sharing.jpg

HTSQL in 10 Minutes

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 |>---/
     +-------------+       +---------+

#1 - Selecting Data

An example (E1):

/course{department+, no, credits-, title}

#2 - Linking Data

An example (E2):

/department{school.name, *}

#3 - Filtering Data

For example (E3):

/course?credits<3&department.school='ns'

#4 - Aggregating Data

An example (E4):

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

#5 - Projections

An example (E5):

/(program^degree){*,count(^)}

#6 - Pluggable Formatters

An example (E6):

/school/:txt

#7 - Table Expressions

An example (E7):

/department.sort(school).limit(10,5)

#8 - Multi-Segments

An example (E8):

/school{code, /department, /program}

Development Status

HTSQL is quite usable currently, but it may have gaps for a given application. Particular items we'll be addressing in coming months are:

How do I get it?

Open Community

Dual-License & Support

Q&A

Please visit our community site, http://htsql.org, our commercial site http://htsql.com, we are best found at #htsql on freenode. The source code is freely available at http://bitbucket.org/prometheus/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.