Source

htsql / doc / showcase.rst

Full commit

HTSQL Showcase

What is HTSQL?

HTSQL is a query language and web service for relational databases.

HTSQL is a Web Service

HTSQL is a query language for the web. Queries are URLs that can be directly typed into a browser.

HTSQL is a Relational Database Gateway

HTSQL wraps an existing relational database. Queries are translated into SQL.

HTSQL is an Advanced Query Language

HTSQL is a compact, high-level query language. Often times, short HTSQL queries are equivalent to much more complex SQL.

How do I use HTSQL?

HTSQL can be used with any number of higher-level tools.

HTSQL Makes Dashboarding Easy

The dashboard above (using the JQuery-based HTRAF toolkit) shows a 3-level drill down (school, department and course) for a university schema. The live demo for this dashboard is at http://htraf.htsql.org/.

HTSQL is a Communication Tool

HTSQL reduces the number of meetings in your organization:

From: "Anne (data analyst)" <ann@example.com>
To: "Dave (product manager)" <dave@example.com>
Cc: jess@example.com, tim@example.com, jose@example.com
Subject: do we need to meet?

Does the HTSQL below return, for each school, the average
number of courses offered in its departments? If so, then
Tim and I don't need to meet with you tomorrow.

http://demo.htsql.org/school{name,avg(department.count(course))}

- A

HTSQL is a common language usable by software developers, data analysts, database administrators, and even business users.

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, the complex stuff is easy too.

Database Introspection

On startup, HTSQL examines tables, primary keys, and foreign keys to construct a navigational graph of your database. For example:

     +------------+               +------------+
/---+| DEPARTMENT |>-------------o|   SCHOOL   |+---\
|.   +------------+        .      +------------+   .|
|  .                     .                       .  |
|   department       department        school may   |
|   offers           may be part       offer some   |
|   courses          of school         programs     |
|                                                   |
|    +------------+               +------------+    |
\---<|   COURSE   |               |  PROGRAM   |>---/
     +------------+               +------------+

This university schema is used in the examples below. The data model has two top-level tables, school and department, where department has an optional link to school. Subordinate tables, having mandatory foreign key references, are course and program.

Choosing a Table

HTSQL queries typically start with a table.

This query (Q1) returns all departments.

Selecting Columns

Output columns are selected with curly brackets {}; the :as decorator sets the title.

This query (Q2) returns, for each department, the name of the associated school and the name of the department.

Filtering Rows

HTSQL lets you filter results with arbitrary predicates.

This query (Q3) returns courses from the school of engineering having more than 3 credits.

Paging and Sorting

Table operations such as sorting and paging could be freely combined.

This query (Q4) returns courses 21 to 30 in the course catalog as sorted by number of credits.

Aggregating Data

In HTSQL, aggregates aren't a reason to run to the DBA.

This query (Q5) returns, for each school having a MS program, the average number of courses offered across its departments.

What's up Next?

Over the next few months we'll be adding more features (some of them are already implemented in our internal 1.X branch).

Projections

HTSQL supports complex grouping operations.

This query (N1) returns the number of programs per degree.

Hierarchical Output

HTSQL is not limited to tabular output.

This query (N2) returns programs and departments in each school.

More Backends

The current release of HTSQL supports PostgreSQL and SQLite. Subsequent releases will add support for MySQL, Oracle and Microsoft SQL Server.

The challenge here is providing consistent function definitions and semantics that work across various SQL database systems.