Table of Contents
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.
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.
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.
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.
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 https://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.
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.
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: https://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.
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.