HTSQL Query API

Overview

The HTSQL server (htsql) is a WSGI application and was designed to use WSGI as its programming interface. However it was suggested to extend the functionality of the HTSQL server in order to give a programmer a direct way to perform HTSQL requests. In some cases, there is also a need to perform HTSQL sub-requests from inside the server.

There are several issues to consider:

• the context in which the request is initiated;
• the output which the request generates;
• how to perform several related requests in a single transaction.
• how to treat errors.

This topic is related to CommandFormatterUnification.

Context

There are several contexts in which a HTSQL request could be performed:

• a regular WSGI request;
• a specific HTSQL request out of a WSGI context;
• a specific HTSQL request within a WSGI context;
• a specific HTSQL request within an HTSQL command.

A regular WSGI request

The HTSQL server provides a regular WSGI-1.0 application interface: htsql(environ, start_response). Support for "WSGI-2.0" (htsql(environ) -> (status, headers, data)) may be added as well. A request creates and uses a dedicated database connection object.

This type of request is typically performed for regular HTTP queries when the HTSQL application is run within a WSGI server.

Errors are handled as follows: HTSQL syntax and semantics errors, as well as database execution errors, are transformed into 4xx and 5xx errors; unexpected exceptions are left unhandled.

A specific HTSQL request out of a WSGI context

Sometimes we need to perform an HTSQL request when no existing WSGI context is available. In some cases, we expect WSGI output, in other cases, we expect a generator producing result rows.

There are numerous examples of this sort of requests in HTSQL code:

• Shell engine, which, however, generates a complete request and expect a regular WSGI output, so, perhaps, it should be considered as a regular WSGI request. Note that the shell engine, as well as any post-processing middleware, will benefit from "WSGI-2.0" support.
• Test engine is very close to the shell engine.
• HTSQL runner: similar to the test engine except that it needs to perform all HTSQL queries within a single transaction. Any error is fatal.
• History dump and other user scripts: these cases are interesting because we need a result set generator.

A specific HTSQL request inside WSGI context

We may need to execute an HTSQL request when some WSGI context is available. In some sense, it's similar to the regular WSGI request. However it may be different from the regular WSGI request by the fact that we may need to execute several requests within a single transaction or may expect a generator rather than WSGI output.

It is difficult to come up with some examples. It looks like a login module in DBGUI could serve an example, but actually the module does not use the provided WSGI request information.

A specific HTSQL request within an HTSQL command

Some HTSQL commands could generate HTSQL queries and execute them.

Examples in htsql_contrib include import() and interact(). Other modules also heavily employ HTSQL sub-requests, see, for instance, pedigree:draw(). In most cases, a row set generator is expected.

Output

Typically, one of two kinds of output is expected: either a generator producing result rows or WSGI output data. In some case, we may not need any output at all.

A WSGI server engine, the shell engine, the test engine and the htsql runner expect WSGI output. "WSGI-2.0" output is preferable for the shell engine, the test engine and the htsql runner.

The history dumper, DBGUI login engine and commands performing subrequests expect a row generator.

Transactions

We may want to execute several HTSQL queries in a single transaction. In this case, we have to manage the connection object by ourselves. On the other hand, we may prefer that the transaction is handled within the query.

Transaction/connection is handled within the request: WSGI servers, the test/shell engines, the history dumper, pedigree:draw() (?).

Transaction/connection is handled out of the request: the htsql runner, the DBGUI login engine, htsql:import(), htsql:interact().

Errors

In some cases, we could prefer to receive exceptions unhandled so that we could catch them or pass them further. In other cases, we would like to receive an HTTP error code rather than an exception.

It is clear that an error condition could be handled within the request only if

• the transaction is handled within the query;
• the query uses WSGI interface.

WSGI servers and test/shell engines need errors to be handled within the request. In the other cases, errors are unhandled.

Summary

Client Code	Context	Output	Transactions	Errors
WSGI server	WSGI request is generated from an HTTP request	WSGI-1.0	by the server	HTTP error code
Test engine	WSGI request is generated from the test data	"WSGI-2.0"	by the server	HTTP error code
Shell engine	WSGI request is generated from the user input	"WSGI-2.0"	by the server	HTTP error code
HTSQL runner	WSGI request is generated from the user data	None ("WSGI-2.0"?)	by the client	HTSQL exception?
History dumper	None	row generator	by the client	HTSQL exception
DBGUI login	None (WSGI request is available, but not used)	row generator	by the client	HTSQL exception
htsql:import()	existing HTSQL request	None (row generator?)	by the client	HTSQL exception
pedigree.draw()	existing HTSQL request	row generator	by the client	HTSQL exception