HTSQL 2.2.0b2

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/introduction.html

Get taste of HTSQL

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

HTSQL is copyright by Prometheus Research, LLC. HTSQL is written by Clark C. Evans <[e-mail]> and Kirill Simonov <[e-mail]>.

Generous support for HTSQL was provided by the Simons Foundation. This material is also based upon work supported by the National Science Foundation under Grant #0944460.

Installation Instructions

Download HTSQL source code:

$ hg clone http://bitbucket.org/prometheus/htsql

Build and install HTSQL:

$ cd htsql
$ make build
# make install

Installing HTSQL creates a htsql-ctl script. For general help and a list of commands, run:

$ htsql-ctl help

To start a command-line HTSQL shell, run:

$ htsql-ctl shell DBURI

To start an HTTP server running HTSQL, run:

$ htsql-ctl server DBURI [HOST [PORT]]

Here, DBURI specifies how to connect to the database and must have the form:

ENGINE://USER:PASS@HOST:PORT/DATABASE

See also:

http://htsql.org/doc/install.html

Installation and Administration Guide

List of Changes

2.2.0 beta 2 (2011-09-22)

• Rebuilt source packages to include missing resource files.

2.2.0 beta 1 (2011-09-20)

• Fixed silent data truncation bug on invalid values; users of SQLite are adviced to upgrade.

• Added support for loading application configuration from a file; option -C FILE.

• Refactored plugin architecture; added support for plugin parameters.

• Added SQLAlchemy adapter.

• Added support for statement-like functions; converted formatters to statements.

• Added plugin tweak.meta: database metadata exposed as a database.

• Added plugin tweak.shell: a simple web-based HTSQL editor.

• Removed plugin tweak.schema: functionality is implemented in the core.

2.1.1 final (2011-08-30)

• General code cleanup and refactoring.

• Improved emitted SQL for projections and aggregates.

• Changed operator precedence to avoid parentheses around filtering and projection operators.

• Added a test data generator for HTSQL regression tests (thank to Yuri Yerokhin).

• Added support for hierarchical headers for /:txt and /:html formatters.

• Updated documentation.

2.1.0 rc 1 (2011-06-21)

• Added /:tsv formatter that generates output in tab-separated format (thank to Andrey Popp).

• A lot of updates to the tutorial, the reference and other documentation.

• Minor code updates and bug fixes.

2.1.0 beta 3 (2011-05-13)

• Added a fiber linking operator: {x,y} -> T{x,y}.

• Provide named attributes for kernel expressions and the complement link in a quotient scope: (program^degree) generates attribute degree as an alias for *1 and program as an alias for ^.

• Added in-selector definitions: {attr := expr} is de-sugared to define(attr := expr){attr}.

• Added $reference syntax for inter-scope references.

• Added flow method fork(): clone the last node in the flow, e.g. /course?credits=max(fork().credits).

• Added flow methods .filter() and .select() as aliases to ? and {} operators.

• Added method distinct(): distinct(table{kernel}) produces a flow of unique values of kernel as it runs over table; it is equivalent to table^kernel. distinct() and ^ operators now filter out NULL values in the kernel.

• Changed semantics of . and {} expressions: specifier (.) now allows arbitrary expressions on both sides, selector ({}) now sets the output columns in the current scope.

• Added syntax *N, where N is an integer literal, to get the value of the N-th output column.

• Added addon tweak.cors: append Access-Control-Allow-Origin: * to every HTTP response.

• Added addon tweak.pgsql.inet: basic manipulations on values of INET data type.

• Added addon tweak.pgsql.view: determine foreign key relationships for views in Postgresql.

• Use search_path or its analogues to choose a table when the table name is ambiguous.

• Optimized introspection for Oracle.

• Added option -p to ask for a password on startup.

• Added shell command run filename.htsql to load and execute an HTSQL query from a file.

• Many other fixes and documentation updates.

2.1.0 beta 2 (2011-03-29)

• Added support for datetime and time date types.

• Added support for calculated attributes, define() and where() macros.

• Added addon tweak.pgsql.catalog: configure primary and foreign keys for pg_catalog.* tables.

• Added addon tweak.schema: allow addressing a table in a specified schema as <schema>_<table> identifier.

• Added addon tweak.autolimit: add LIMIT clause to each query.

• Added addon tweak.pgsql.timeout: set statement timeout for each query.

• Added Jinja integration demo.

• Many minor fixes and updates.

2.1.0 beta 1 (2011-03-08)

• Ported to MySQL, Oracle and MS SQL Server.

• Added support for projections (base^kernel).

2.0.1 final (2011-02-01)

• Merged /:json and /:jsonex formatters; use /:jsonex layout to avoid CSRF issues.

• Documentation updates.

2.0.0 rc 1 (2011-01-21)

• HTRAF demo is promoted to a standalone project; see http://bitbucket.org/prometheus/htraf.

• Added /:jsonex format: JSON with extra metadata.

• Documentation updates.

2.0.0 beta 5 (2010-12-14)

• Completed the SQLite backend.

• Completed the standard library.

2.0.0 beta 4 (2010-12-05)

• Refactored peep-hole optimization of the frame tree and SQL serialization; refined the generated SQL.

• Refactored the internal API for adding functions.

• Added new (experimental) syntax for infix function call.

• Added an “Examples” section to the documentation.

• Fixed compatibility with Python 2.5.

• Many minor bugfixes, documentation updates, etc.

2.0.0 beta 3 (2010-11-09)

• Refactored the translator from the term tree to the frame tree; added a new translation step to collapse nested frames and reduce SQL expressions.

• Added the HTRAF demo: use HTSQL with client-side javascript to define complex dashboards.

• Added the SSI demo: using HTSQL for web without giving direct access to the database.

• Updated the tutorial, minor bugfixes, regression test updates, etc.

2.0.0 beta 2 (2010-10-01)

• Refactored the translator from the expression graph to the term tree.

• Added Content-Disposition header to :json and :csv renderers.

• Merged the tutorial to the repository.

2.0.0 beta 1 (2010-09-21)

• Initial beta release.

• PostgreSQL and SQLite backends are (mostly) supported, the former more than the latter.

• The SQL translator needs more work, in particular, the term tree, the frame structure and the serializer.

• Lots of functions are either not implemented or missing a proper serializer, especially in the SQLite backend.

• Other areas which need more work before the final release: rendering, introspection, regression tests and documentation.