HTSQL 2.0 RC1 -- a Query Language for the Accidental Programmer

[Clark C. Evans]

Kirill Simonov and myself would like to introduce HTSQL, a novel
approach
to relational database access which is neither an ORM nor raw SQL.

HTSQL is a URI-based high-level query language for relational databases.
It's implemented as a Python WSGI application. Currently it supports
PostgreSQL and SQLite (more databases & juicy features forthcoming).

Homepage: http://htsql.org
Download: http://pypi.python.org/pypi/HTSQL/
Source: http://bitbucket.org/prometheus/htsql

At this point, HTSQL 2.0 may not be mature enough for production use; we
expect to fill in any remaining gaps in the coming months.

We're curious what you think. Join us in #htsql on freenode [1],
subscribe
to the mailing list [2] and please come to our PyCon 2011 talk [3].

Clark & Kirill

[1] irc://irc.freenode.net/#htsql
[2] http://lists.htsql.org/mailman/listinfo/htsql-users
[3] http://us.pycon.org/2011/schedule/sessions/264/

 

[rusi]

Kirill Simonov and myself would like to introduce HTSQL, a novel
approach to relational database access which is neither an ORM nor raw SQL. :
We're curious what you think.

Thanks -- looks interesting.

Given the claim htsql is higher level than sql I am interested in
bill-of-materials type (recursive) queries.

 

[Clark C. Evans]

Given the claim htsql is higher level than sql I am interested in
bill-of-materials type (recursive) queries.

Rusi,

HTSQL 2.0 does not yet support SQL's common table expressions.
However, this particular use case, along with CUBE, server-side
stored procedures, and related needs is what made us branch
from our 1.X production release. Our immediate focus is SQL-92.
Once we cover most SELECT patterns and SQL back-ends, we'll be
looking at SQL:1999, SQL:2003, and SQL:2008 (as well as
proprietary equivalents such as Oracle's CONNECT BY).

Best,

Clark

 

[Kirill Simonov]

Thanks -- looks interesting.

Given the claim htsql is higher level than sql I am interested in
bill-of-materials type (recursive) queries.

Currently HTSQL does not support recursive queries. That said, it's
certainly within the reach of HTSQL and I could sketch here how the
support may look like:

We add an operator `closure()` that, given a self-referential link
`link`, produces a transitive closure `closure(link)` of the link.

For example, take a table `program` with a link `program.part_of`. Then
`program.closure(part_of)` is a plural link mapping a program to its
super-programs, which you can use just like a regular plural link, for
instance, in aggregate expressions.

To return, for each program, a list of its super-programs:

/program{code, /closure(part_of){code}}

To return all sub-programs of a specific program 'xxx':

/program?exists(closure(part_of).code='xxx')

Compare that with

/program{code, part_of.code}
/program?part_of.code='xxx'

I think it would be a modest improvement over a SQL alternative.

I'm adding it to the roadmap right now, but don't hold your breath -- Q4
this year or early next year is a realistic ETA. I expect the
implementation to be at least moderately painful and, obviously, it
could only work with those backends that support WITH RECURSIVE.


Thanks,
Kirill

 

[rusi]

Currently HTSQL does not support recursive queries.  That said, it's
certainly within the reach of HTSQL and I could sketch here how the
support may look like:

We add an operator `closure()` that, given a self-referential link
`link`, produces a transitive closure `closure(link)` of the link.

For example, take a table `program` with a link `program.part_of`.  Then
`program.closure(part_of)` is a plural link mapping a program to its
super-programs, which you can use just like a regular plural link, for
instance, in aggregate expressions.

To return, for each program, a list of its super-programs:

     /program{code, /closure(part_of){code}}

To return all sub-programs of a specific program 'xxx':

     /program?exists(closure(part_of).code='xxx')

Compare that with

     /program{code, part_of.code}
     /program?part_of.code='xxx'

I think it would be a modest improvement over a SQL alternative.

I'm adding it to the roadmap right now, but don't hold your breath -- Q4
this year or early next year is a realistic ETA.  I expect the
implementation to be at least moderately painful and, obviously, it
could only work with those backends that support WITH RECURSIVE.

O well...
I was hoping for some some quick-queries (one-liners?) to probe
firefox's bookmarks (which are in sqlite)