HTSQL

Hyper Text Structured Query Language (HTSQL) is a schema-driven URI-to-SQL query language that takes a request over HTTP, converts it to a SQL query, executes the query against a database, and returns the results in a format best suited for the user agent (CSV, HTML, etc.)[1] The HTSQL language is implemented on "HTSQL servers," which use HTSQL to convert web requests into equivalent SQL, executes requests on a server-side database, and returns results in XML, HTML, CSV, JSON, or YAML formats. The current implementation as of April 2010 uses Python and works with PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server databases.

HTSQL was prototyped and developed by Clark Evans (who proposed YAML in 2001[2]) and implemented by Kirill Simonov,[3] both of Prometheus Research.

Scope

HTSQL's intended audience is business users who are not SQL programmers. It seeks to simplify non-intuitive relational queries, such as aggregates and projections. The goal is not completeness in replicating SQL, but simplicity and intuitiveness. HTSQL allows users who are familiar with the data, but not SQL syntax, to directly access the database without going through an application.[4]

Syntax

HTSQL uses a URI-based query syntax. Queries typically begin with a single table, optionally prefixed by a schema and followed by a filter expression to limit the number of rows that are returned.[5]

Examples

Joins can be made across tables by referencing the column name preceded by the table name. This example requests the title of a degree program from the program table and the school name from the school table:

/program{school.name, title}

The result of this query from the HTSQL command-line system is:

program
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
school.name               | title
--------------------------+-----------------------------------
School of Art and Design  | Post Baccalaureate in Art History
School of Art and Design  | Bachelor of Arts in Art History
School of Art and Design  | Bachelor of Arts in Studio Art
School of Business        | Graduate Certificate in Accounting

...

Aggregate expressions can be used when there are many-to-one relationships in the database.

/school{name, count(program), count(department)}

The result shows the count (number) of programs and departments in each school:

school
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
name                     | count(program) | count(department)
-------------------------+----------------+------------------
School of Art and Design | 3              | 2
School of Business       | 5              | 3
College of Education     | 7              | 2
School of Engineering    | 8              | 4

Applications

HTSQL can be used for ad hoc database queries. Its developers consider it to be more concise and intuitive and therefore less error-prone than SQL for non-technical users.[6]

HTSQL can also be used by "accidental programmers", such as data analysts and UI developers. Besides more readable, HTTP-based queries, HTSQL's developers claim it helps avoid common SQL mistakes like accidental joins.[7]

Versions and licensing

HTSQL follows a dual license model. The software may be used free of charge with open source relational databases, including MySQL and PostgreSQL.[8] Source code is available on the bitbucket site.[9]

A commercial version of HTSQL, compatible with commercial databases like Oracle and Microsoft SQL Server, is also available.[10]

References

External links


  This article uses material from the Wikipedia page available here. It is released under the Creative Commons Attribution-Share-Alike License 3.0.

HTSQL
 



 

Connect with defaultLogic
What We've Done
Led Digital Marketing Efforts of Top 500 e-Retailers.
Worked with Top Brands at Leading Agencies.
Successfully Managed Over $50 million in Digital Ad Spend.
Developed Strategies and Processes that Enabled Brands to Grow During an Economic Downturn.
Taught Advanced Internet Marketing Strategies at the graduate level.


Manage research, learning and skills at defaultLogic. Create an account using LinkedIn or facebook to manage and organize your Digital Marketing and Technology knowledge. defaultLogic works like a shopping cart for information -- helping you to save, discuss and share.


  Contact Us