HTSQL Handbook

This handbook presumes you have installed HTSQL. It also assumes that you have a GNU/Linux based system, although other systems might also work. If you need help, please see our HTSQL Community page for assistance.

Up & Running

To verify your installation, try htsql-ctl‘s version routine:

$ htsql-ctl version
...

If htsql-ctl isn’t found or it doesn’t work, you have an installation issue.

Getting Familiar

To get started, you could test queries with our htsql_demo demo SQLite database. To get a copy of this, download it using wget or some other tool:

$ wget -q http://dist.htsql.org/misc/htsql_demo.sqlite

Then use htsql-ctl shell to walk through our overview and/or tutorial:

$ htsql-ctl shell sqlite:htsql_demo.sqlite
Type 'help' for more information, 'exit' to quit the shell
htsql_demo$ /school
 | school                                        |
 +------+-------------------------------+--------+
 | code | name                          | campus |
-+------+-------------------------------+--------+-
 | art  | School of Art & Design        | old    |
 | bus  | School of Business            | south  |
 | edu  | College of Education          | old    |
...

Shell comes with a describe command, which lists tables, or, if you provide a table, its columns and links:

htsql_demo$ describe school
...

Labels:
  code                     : text column
  name                     : text column
  campus                   : text column
  department               : plural link to department
  program                  : plural link to program

Shell has schema-based completion. For example, if you type /s and then press TAB, it will list all of of the possibe completions: school, semester, and student. For more information, please see the htsql-ctl reference.

Test Drive

To attach HTSQL to your database, you’ll need a Database URI which take the following form:

<engine>://<user>:<pass>@<host>:<port>/<database>

For this example, we’ll use the pgsql engine on a local demo database using the -p option to prompt for a password. The exact connection details will depend upon your local configuration:

$ htsql-ctl shell -p pgsql://demo@localhost:5432/htsql_demo
Password: ******
Type 'help' for more information, 'exit' to quit the shell.
htsql_demo$ describe
PGSQL://demo@localhost:5432/htsql_demo - HTSQL database

Labels:
  course                   : table
  department               : table
  program                  : table
...

If it seems links aren’t working properly, you could verify links for a specific table using describe:

htsql_demo$ describe department
DEPARTMENT - table
...

Foreign keys:
  ad.department(school_code) -> ad.school(code) {nullable}
  ad.course(department_code) -> ad.department(code)
  id.appointment(department_code) -> ad.department(code)
...

Labels:
  code                     : text column
  name                     : text column
  school_code              : text column
  school                   : link to school
  course                   : plural link to course
  appointment              : plural link to appointment

You should see a signular link for every foreign key referencing other table and a (generally, plural) link for every foreign key referencing this table from another table. In this example, we see that department has a singular link to school and plural links to course and appointment.

If links are not introspected, you’ve got a few options. The best option is to create them in your database if they don’t exist (this isn’t an option for MyISAM). Otherwise, you have a few configuration options, including manually specifying links or bridging relationship detail from a SQLAlchemy or Django model.

Web Service

Besides shell, the htsql-ctl program provides a built-in demonstration webserver. You could start it as follows:

$ htsql-ctl serve sqlite:htsql_demo.sqlite
Starting an HTSQL server on localhost:8080 over htsql_demo.sqlite

Then, it might be accessed using any user agent, such as wget:

$ wget -q -O - --header='Accept: text/csv' http://localhost:8080/school
code,name,campus
art,School of Art & Design,old
bus,School of Business,south
edu,College of Education,old
...

On http://demo.htsql.org, we enable a tweak.shell extension:

$ htsql-ctl serve -E tweak.shell.default sqlite:htsql_demo.sqlite
Starting an HTSQL server on localhost:8080 over htsql_demo.sqlite

You could then navigate to http://localhost:8080 with your web browser and type in queries there. This plugin replaces the default HTML formatter with our visual shell. If you press CTRL+SPACE it should bring up a context sensitive menu item.

HTSQL Extensions

Everything is an Extension

For HTSQL, everything (even database adapters) are plugins that are independently installed, loaded and configured. Extensions can be loaded on the command line using -E or in a configuration file format. You could list installed extensions at the command line:

$ htsql-ctl extension
Available extensions:
  engine                   : provides implementations of HTSQL for specific servers
  engine.mysql             : implements HTSQL for MySQL
  engine.pgsql             : implements HTSQL for PostgreSQL
  engine.sqlite            : implements HTSQL for SQLite
  htsql                    : HTSQL translator and HTTP service
  tweak                    : contain various tweaks for HTSQL
  tweak.autolimit          : limit number of rows returned by queries

One handy extension is tweak.autolimit which limits the number of rows returned by default. Using this plugin lets you explore tables with lots of rows without having to constantly add .limit(n) to each of your queries. In this example, we set the limit to 5 rows:

$ htsql-ctl shell -E tweak.autolimit:limit=5 sqlite:htsql_demo.sqlite
Type 'help' for more information, 'exit' to quit the shell.
htsql_demo$ /count(department)
 | count(department) |
-+-------------------+-
 |                27 |

htsql_demo$ /department
 | department                                    |
 +--------+------------------------+-------------+
 | code   | name                   | school_code |
-+--------+------------------------+-------------+-
 | acc    | Accounting             | bus         |
 | arthis | Art History            | la          |
 | astro  | Astronomy              | ns          |
 | be     | Bioengineering         | eng         |
 | bursar | Bursar's Office        |             |

One of the more interesting plugins is tweak.meta. This adds a in-memory SQLite database with table and link detail based upon the current configuration, and a function meta() to let you query it:

$ htsql-ctl shell -E tweak.meta sqlite:htsql_demo.sqlite
Type 'help' for more information, 'exit' to quit the shell.
htsql_demo$ /meta(/link{name, is_singular}?table_name='school')
 | link                     |
 +------------+-------------+
 | name       | is_singular |
-+------------+-------------+-
 | department | false       |
 | program    | false       |

The PostgreSQL specific tweak.timeout plugin provides a way to automatically kill expensive queries after a specified number of seconds have elapsed:

$ htsql-ctl shell -E tweak.timeout:timeout=3 pgsql:htsql_demo
Type 'help' for more information, 'exit' to quit the shell.
htsql_demo$ /count(enrollment.fork().fork())
Got an error from the database driver:
    canceling statement due to statement timeout
While processing:
    /count(enrollment.fork().fork())
    ^

The enrollment table has 15k rows, and fork() associates each row with every row of the same table (a CROSS JOIN). Hence, this query would count 15K^3 rows. Having a query like this auto killed after 3s is a great way to keep everyone happy.

Extension Configuration

Addons and configuration parameters can also be provided by a configuration file in YAML (or JSON) format and then included using -C on the command line. Here is an example configuration file for a PostgreSQL database with some addons enabled.

# demo-config.yaml
htsql:
  db:
    engine: pgsql
    database: htsql_demo
    username: htsql_demo
    password: secret
    host: localhost
    port: 5432
tweak.autolimit:
  limit: 1000
tweak.cors:
tweak.meta:
tweak.shell:
  server-root: http://demo.htsql.org
tweak.shell.default:
tweak.timeout:
  timeout: 600

You can then start the shell using these parameters:

$ htsql-ctl serve -C demo-config.yaml

If both -E and -C are used, explicit command line options override values provided in the configuration file. This permits a configuration file to be used as a default perhaps using a different database URI.

MetaData Configuration

The tweak.override plugin provides comprehensive control over the HTSQL system catalog.

Note

For more information about configuring and using HTSQL, please see our Using and Configuring HTSQL guide.