HTSQL makes accessing data as easy as browsing the web. An HTSQL processor translates web requests into relational database queries and returns the results in a form ready for display or processing. Information in a database can then be directly accessed from a browser, a mobile application, statistics tool, or a rich Internet application. Like any web resource, an HTSQL service can be secured via encryption and standard authentication mechanisms -- either on your own private intranet or publicly on the Internet.
HTSQL users are data experts. They may be business users, but they can also be technical users who value data transparency and direct access. Business users can use HTSQL to quickly search the database and create reports without the help of IT. Programmers can use it as data access layer for web applications. HTSQL can be installed by DBAs to provide easy, safe database access for power users.
At its core, HTSQL is a schema-driven URI-to-SQL translator that takes a request over HTTP, converts it to a set of SQL queries, executes these queries in a single transaction, and returns the results in a format (CSV, HTML, JSON, etc.) requested by the user agent:
/----------------\ /------------------------\ | USER AGENT | | HTSQL WEB SERVICE | *----------------* HTTP Request *------------------------* | | >---------------> -. | | * Web Browsers | URI, headers, | \ .---> Generated | | HTML, TEXT | post/put body | v / SQL Query | | | | HTSQL | | | * Applications | | PROCESSOR v | | JSON, XML | HTTP Response | / ^. SECURED | | | <---------------< -. \ DATABASE | | * Spreadsheets | status, header, | Query . | | CSV, XML | csv/html/json | Results <---/ | | | result body | | \----------------/ \------------------------/
The HTSQL query processor does heavy lifting for you. Using relationships between tables as permitted links, the HTSQL processor translates graph-oriented web requests into corresponding relational queries. This translation can be especially involved for sophisticated requests having projections and aggregates. For complex cases, an equivalent hand-written SQL query is tedious to write and non-obvious without extensive training. By doing graph to relational mapping on your behalf, HTSQL permits your time to be spent exploring information instead of debugging.
The HTSQL language is easy to use. We've designed HTSQL to be broadly usable by semi-technical domain experts, or what we call accidental programmers. We've field tested the toolset with business analysts, medical researchers, statisticians, and web application developers. By using a formalized directed graph as the underpinning of the query algebra and by using a URI-inspired syntax over HTTP, we've obtained a careful balance between clarity and functionality.
We hope you like it.
The following examples show output from the HTSQL command-line system, which is plain text. HTSQL can output HTML, CSV, XML and many other formats. This makes it suitable not only for direct queries, but as a data access layer for application development.
We'll use a fictional university that maintains a database for its student enrollment system. There are four tables that describe the business units of the university and their relationship to the courses offered:
+--------------------+ +---------------------+
| DEPARTMENT | | SCHOOL |
+--------------------+ +---------------------+
| code PK |--\ /---| code PK |----\
| school NN,FK |>-|------/ | name NN,UK | |
| name NN,UK | | . +---------------------+ |
+--------------------+ | . . |
. | departments . |
a department . | belong to . |
offers one . | exactly one a school |
or more course | school administers one |
| or more programs |
+--------------------+ | |
| COURSE | | +---------------------+ |
+--------------------+ | | PROGRAM | |
| department FK,PK1 |>-/ +---------------------+ |
| number PK2 | | school PK1,FK |>---/
| title NN | | code PK2 |
| credits NN | | title NN |
| description | | degree CK |
+--------------------+ +---------------------+
PK - Primary Key UK - Unique Key FK - Foreign Key
NN - Not Null CK - Check Constraint
The university consists of schools, which administer one or more degree-granting programs. Departments are associated with a school and offer courses. Further on in the tutorial we will introduce other tables such as student, instructor and enrollment.
HTSQL requests typically begin with a table name. You can browse the contents of a table, search for specific data, and select the columns you want to see in the results.
The most basic HTSQL request (A1) returns everything from a table:
/school
The result set is a list of schools in the university, including all columns, sorted by the primary key for the table:
school ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ code | name -----+-------------------------- art | School of Art and Design bus | School of Business edu | College of Education egn | School of Engineering ...
Not all columns are useful for every context. Use a selector to choose columns for display (A2):
/program{school, code, title}
program
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
school | code | title
-------+----------+-----------------------------------
art | gart | Post Baccalaureate in Art History
art | uhist | Bachelor of Arts in Art History
art | ustudio | Bachelor of Arts in Studio Art
bus | pacc | Graduate Certificate in Accounting
...
Add a plus (+) sign to the column name to sort the column in ascending order. Use a minus sign (-) for descending order. For example, this request (A3) returns departments in descending order:
/department{name-, school}
department
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
name | school
-----------------------+-------
Wind | mus
Vocals | mus
Teacher Education | edu
Studio Art | art
...
Using two ordering indicators will sort on labeled columns as they appear in the selector. In the example below, we sort in ascending order on department and then descending on credits (A4):
/course{department+, number, credits-, title}
course
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
department | number | credits | title
-----------+--------+---------+---------------------------
acc | 315 | 5 | Financial Accounting
acc | 200 | 3 | Principles of Accounting I
acc | 426 | 3 | Corporate Taxation
...
To display friendlier names for the columns, use as to rename a column's title (A5):
/course{department as 'Dept Code'+, number as 'No.',
credits-, title}
course
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dept Code | No. | credits | title
----------+-----+---------+----------------------------
acc | 315 | 5 | Financial Accounting
acc | 200 | 3 | Principles of Accounting I
acc | 426 | 3 | Corporate Taxation
...
Selectors let you choose, rearrange, and sort columns of interest. They are an easy way to exclude data that isn't meaningful to your report.
In our example schema, each program is administered by a school. Since the HTSQL processor knows about this relationship, it is possible to link data accordingly (B1):
/program{school.name, title}
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
...
This request joins the program and school tables by the foreign key from program{school} to school{code}. This is called a singular relationship, since for every program, there is exactly one school.
It is possible to join through multiple foreign keys; since course is offered by a department which belongs to a school, we write:
/course{department.school.name, department.name, title}
course
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
department.school.name | department.name | title
-----------------------+-----------------+---------------------------
School of Business | Accounting | Practical Bookkeeping
School of Business | Accounting | Principles of Accounting I
School of Business | Accounting | Financial Accounting
School of Business | Accounting | Corporate Taxation
...
This query can be shortened a bit by collapsing the duplicate mention of department; the resulting request is identical (B2):
/course{department{school.name, name}, title}
For cases where you don't wish to specify each column explicitly, one can use the wildcard * selector. The request below returns all columns from program, and all columns from school (B3):
/department{*,school.*}
department
~~~~~~~~~~~~~~~~~~~~ ... ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ...
code | name ... | school.name ...
-------+------------ ... +--------------------------- ...
acc | Accounting ... | School of Business ...
arthis | Art History ... | School of Art and Design ...
artstd | Studio Art ... | School of Art and Design ...
astro | Astronomy ... | School of Natural Sciences ...
...
Since the HTSQL processor knows about relationships between tables in your relational database, joining tables in your reports is trivial.
Predicate expressions in HTSQL follow the question mark ?. For example, to return departments in the "School of Engineering" write (C1):
/department?school='egn' department ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ code | name | school -----+------------------------+------- be | Bioengineering | egn comp | Computer Science | egn ee | Electrical Engineering | egn me | Mechanical Engineering | egn ...
The request above returns all rows in the department table where the column school is equal to 'eng'. In HTSQL, literal values are single quoted, in this way we know eng isn't the name of a column.
Complex filters can be created using boolean connectors, such as the conjunction operator (&). The following request returns programs in the "School of Business" that do not grant a "Bachelor of Science" degree (C2):
/program?school='bus'°ree!='bs' program ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ school | code | title | degree -------+------+------------------------------------+------- bus | mba | Masters of Business Administration | mb bus | pacc | Graduate Certificate in Accounting | ct bus | pcap | Certificate in Capital Markets | ct ...
Filters can be combined with selectors and links. The following request returns courses, listing only department number and title, having less than 3 credits in the school of natural science (C3):
/course{department, number, title}?
credits<3&department.school='ns'
course
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
department | number | title
-----------+--------+-------------------------------
phys | 388 | Experimental Physics I
chem | 115 | Organic Chemistry Laboratory I
astro | 142 | Solar System Lab
astro | 155 | Telescope Workshop
...
It is sometimes desirable to specify the filter before the selector. Using a table expression, denoted by parenthesis, the previous request is equivalent to:
/(course?credits<3&department.school='ns')
{department, number, title}
HTSQL supports far more than the simple predicate expressions shown above, a whole suite of functions and operators is supported. HTSQL also has a plug-in mechanism where custom data types, operations and functions may be integrated to support domain specific needs.
One data is selected, linked and filtered, it is formatted for the response. HTSQL uses the Accept header to negotiate the output format with the user agent. This can be overridden by using a HTSQL command (a forthcoming feature in community release).
For example "JavaScript Object Notation" (JSON) format can be used by software applications (D1):
GET /school
Accept: application/json
[{"code": "art",
"name": "School of Art and Design"},
{"code": "bus",
"name": "School of Business"},
{"code": "edu",
"name": "College of Education"},
...
]
Built-in formats include plain text, HTML, JSON, XML, YAML and CSV. The plug-in mechanism permits other formats, such as ones to generate charts and graphs, to be integrated easily.
The following request selects records from the course table, filtered by all departments in the 'School of Business', sorted by course title, including department's code and name, and returned as a CSV file (E1):
GET /course{department{code,name},number,title+}?
department.school='bus'
Accept: application/json
department.code,department.name,number,title
corpfi,Corporate Finance,234,Accounting Information Systems
acc,Accounting,527,Advanced Accounting
capmrk,Capital Markets,756,Capital Risk Management
corpfi,Corporate Finance,601,Case Studies in Corporate Finance
...
HTSQL requests are powerful without being complex. They are easy to read and modify. They adapt to changes in the database. These qualities increase the usability of databases by all types of users and reduce the likelihood of costly errors.
HTSQL is designed for complex reporting. For the typical report, a given request pulls data from several database tables, often having multiple (and even nested) aggregate expressions such as sum, count, or exists. The architecture of HTSQL also supports projection structures, which are used to discover or report on emergent relations in the underlying data. To facilitate nested report structures, HTSQL supports forest products -- structurally correlated database queries stitched together for a hierarchical output format. The combination of these features, together with extensive expression mechanism provide a rich grammatical structure to express data requests. It's a rare reporting request that escapes a succinct representation.
HTSQL requests are easy to grok. Our query model was refined by user feedback from both accidental programmers and hard core SQL junkies. The main difficulty in standard relational database technology is creating a join between tables that is not just clearly correct but also scalable. We've accomplished this by modeling links in a graph model, where HTSQL translates intuitive link requests into the corresponding relational algebra. We've worked out the edge cases, and by separating singular and plural joins, made this our foundation. Since it is trivial to create meaningful relations in data, and impossible to create bad joins, we've answered the core difficulty our users have. HTSQL's consistent query model makes casual inspection of database requests possible.
Simplicity of expression makes all the difference.
Since school table has a plural (one to many) relationship with program and department, we can count them (F1):
/school{name, count(program), count(department)}
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
...
The above request is simple, but non-trivial in a relational database sense since it requires a correlated sub-query. Of course, a qualified count is possible. The request below counts only departments that have a course with less than 3 credits (F2):
/school{name, count(department?exists(course.credits<3))}
school
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
name | count(department?exists(course.credits<3))
-------------------------+-------------------------------------------
School of Art and Design | 1
School of Business | 1
College of Education | 0
School of Engineering | 0
...
BELOW THIS POINT HAS NOT HAD RECENT REVIEW
A filter further refines results by including or excluding data by specific criteria. The examples below use operators to compare results to letters, numbers, and conditions.
The course table contains the university's academic offerings. Course titles are strings, or sequences of letters. To search for a particular string, first decide what type of matching you need.
To exactly match a course title, use the equals sign (=):
/course?title='Drawing' course +-------------------------------------------- ... | department | number | title | credits +------------+-----------+---------+--------- ... | artstd | 333 | Drawing | 3
Be sure to put the string you're looking for in single quotes, so HTSQL will interpret it literally. Exact matches are just that--they're case sensitive and have to match perfectly, or nothing will be returned.
If you're not sure of the course title, use the contains operator (~) to match any course that contains a word like 'physics':
/course?title~'physics' course +----------------------------------------------------------- ... | department | number | title | credits +------------+-----------+------------------------+--------- ... | phys | 211 | General Physics I | 3 | phys | 388 | Experimental Physics I | 2 ...
This request returns all courses that have physics anywhere in the title, regardless of case.
What if you hate physics? Use the not-contains operator (!~) to exclude all courses with physics in the title:
/course?title!~'physics'
To exclude a specific class, use the not-equals operator:
/course?title!='General Physics I'
To find entries that match a string at the beginning, use the starts-with operator (^~):
/school?name^~'school' school +-------------------------------------------+ | code | name | +------+------------------------------------+ | art | School of Art and Design | | eng | School of Engineering | ...
The ends-with operator ($~) does the same thing, but from the end of the entry:
/department?name$~'engineering' department +----------------------------------------+ | code | name | school | +------+------------------------+--------+ | be | Bioengineering | eng | | ee | Electrical Engineering | eng | ...
The comparison operators >, <, >=, <=, = and != are used to compare numbers or strings.
The equality (=) and inequality (!=) operators are straightforward when used with numbers:
/course{department,number,title}?number=367
course
+--------------------------------------------------------+
| department | number | title |
+------------+--------+----------------------------------+
| tched | 367 | Problems in Education Management |
Use the greater-than (>) operator to request courses with more than 3 credits:
/course?credits>3 course +------------------------------------------------------------- ... | department | number | title | credits +------------+-----------+---------------------------+-------- ... | arthis | 712 | Museum and Gallery Mgmt | 4 | artstd | 411 | Underwater Basket Weaving | 4 ...
Use the greater-than-or-equal-to operator request courses that have three credits or more:
/course?credits>=3
Using comparison operators with strings tells HTSQL to compare them alphabetically. The greater-than operator is used to request departments that follow me in the alphabet:
/department?code>'me' department +------------------------------------+ | code | name | school | +-------+-------------------+--------+ | phys | Physics | sm | | poli | Political Science | la | ...
Boolean operators are used with true/false statements. Filters can be combined using a conjunction (&):
/course?department='arthis'&credits<=3 course +------------------------------------------------------------- ... | department | number | title | credits +------------+-----------+---------------------------+-------- ... | arthis | 202 | History of Art Criticism | 3 | arthis | 340 | Arts of Asia | 3 ...
This request asks for courses in the art history department with three or fewer credits.
Alternation (|) lets you find courses that are in 'Art History' or 'Studio Art' department:
/course?department='arthis'|department='artstd'
Negation (!) lets you find all courses that are NOT in either 'Art History' or 'Studio Art':
/course?!(department='arthis'|department='artstd')
The following request shows courses that are in 'Art History' or 'Studio Art' having more than three credits:
/course?(department='arthis'|department='artstd')&credits>3 course +-------------------------------------------------------------- ... | department | number | title | credits +------------+--------+-------------------------------+-------- ... | arthis | 712 | Museum and Gallery Management | 4 | artstd | 411 | Underwater Basket Weaving | 4 | artstd | 509 | Twentieth Century Printmaking | 4 | artstd | 614 | Drawing Master Class | 5 ...
Without parenthesis, since conjunction (&) binds tighter than alternation (|), this request would return all courses for 'Art History' and those in 'Studio Art' having more than three credits (including 'Art History' with three credits or less):
/course?department='arthis'|department='artstd'&credits>3 course +---------------------------------------------------------------- ... | department | number | title | credits +------------+--------+---------------------------------+-------- ... | arthis | 202 | History of Art Criticism | 3 | arthis | 340 | Arts of Asia | 3 | arthis | 623 | Contemporary Latin American Art | 3 ...
It's common to need to combine data from two or more tables. When two tables are linked by a column, you can select data from both tables with a join. In database terms, this means one table must have a foreign key to the other. HTSQL distinguishes between two kinds of joins, one-to-one and one-to-many.
A singular join returns at most one value from table B for every value in table A. Since the department and school tables share a column (the school code), you can request the school name (from the school table) and the department name (from the department table):
/department{school.name, name}
department
+-------------------------------------------------------------+
| school.name | name |
+------------------------------------+------------------------+
| Graduate School of Business | Accounting |
| School of Art and Design | Art History |
| School of Art and Design | Studio Art |
...
This returns one for every department with the corresponding school name.
You can use the same technique for three or more tables, as long as every pair of tables shares a key. They don't have to share same key. For instance, program and school share the school code, and program and department share the department code:
/course{school.code, department.code, number}
course
+-------------------------------------------------------+
| school.code | department.code | number |
+--------------------+----------------------+-----------+
| bus | acc | 200 |
| bus | acc | 315 |
| bus | acc | 426 |
...
To show all columns in two tables, use an asterisk (*) instead of the column names:
/department{*, school.*}
department
+-----------------------------------------------------------------...
| code | name | school | school.code | school
+---------+------------------------+--------+-------------+-------...
| acc | Accounting | bus | bus | Gradua
| arthis | Art History | art | art | School
| artstd | Studio Art | art | art | School
...
If a join doesn't work, the most likely reason is that there is no relationship between the tables you are trying to join. For instance, the following request asks for students and their schools:
/student{name,school.name}
Students are enrolled in programs, not schools, so there's no relationship between school and student. This results in an error:
invalid reference: not an aspect at the position 15:
/student{name,school.name}
^-----
Looking at the database schema is usually the best way to understand the relationships between tables, including foreign keys. If that's not an option, you can perform a test query.
A plural join involves a one-to-many relationship, such as that between one school and many departments. Since a flat result can't show a one-to-many relationship, an aggregate function is used to summarize the result into a single cell.
For example, the count function shows the number of departments in each school:
/school{name, count(department)}
school
+--------------------------------------------------+
| name | count(department) |
+------------------------------+-------------------+
| School of Continuing Studies | 0 |
| School of Art and Design | 2 |
| College of Education | 2 |
| Graduate School of Business | 3 |
...
This is a singular join because there is one school for every department. A request that uses school as the context will not work because there is more than one department for every school:
/school{name, department.name}
Instead, you will receive an error saying that a singular expression is required:
invalid argument: a singular expression is required at the position 25:
/school{name,department.name}
^---
A simple test for a plural join is to see whether any items exist. The exists function returns true if there is at least one, false if there are none. This request asks whether there is at least one department for every school:
/school{name,exists(department)}
school
+---------------------------------------------------------+
| name | exists(department) |
+------------------------------------+--------------------+
| School of Art and Design | true |
| School of Continuing Studies | false |
| School of Mathematics and Sciences | true |
...
HTSQL enforces restrictions on the types of functions that can be used with singular and plural joins. This helps protect users from costly mistakes that can be hard to spot.
Using HTSQL to crunch numbers for you can be more convenient than doing it in a client application like a web app or spreadsheet. It can also reduce errors by ensuring the person who understand the data is the one who writes the formula.
HTSQL can perform simple arithmetic like addition, subtraction, and division, and powers.
At the fictitious university, students receive an extra credit for serving as teaching assistants, 50% credit for dropping the class mid-term, and one credit less for dropping without taking the final exam:
/course{number, credits, credits+1, credits*.5, credits-1}
course
+----------------------------------------------------------------+
| number | credits | (credits+1) | (credits*.5) | (credits-1) |
+-----------+---------+-------------+--------------+-------------+
| 200 | 3 | 4 | 1.5 | 2 |
| 315 | 3 | 4 | 1.5 | 2 |
| 712 | 4 | 5 | 2.0 | 3 |
| 527 | 3 | 4 | 1.5 | 2 |
...
Numerical operators can be grouped (using parentheses) to create more complex mathematical expressions:
/course{number, credits+(2.5*credits) div (10.5+credits)}
course
+-------------------------------------------------------+
| number | (credits+((2.5*credits) div (10.5+credits))) |
+--------+----------------------------------------------+
| 200 | 3.55555555555555555556 |
| 712 | 4.68965517241379310345 |
...
This request asks HTSQL to divide the result from column 2 above by the result from column 3.
The result is rounded to 16 decimals places--a few more than you probably need. The trunc function truncates a number to the decimal places you specify. You can then add a title to the entire expression so that it looks nice in the output:
/course{number, trunc(credits+(2.5*credits) div (10.5+credits),2)}
course
+-----------------------------------------------------------------+
| number | trunc((credits+((2.5*credits) div (10.5+credits))), 2) |
+--------+--------------------------------------------------------+
| 200 | 3.55 |
| 712 | 4.68 |
...
This request tells HTSQL to truncate the number to 2 decimal places. You might prefer to use round to round up the result rather than simply cut it off.
HTSQL lets you do a wide range of things with numbers, including arithmetic, mathematical, and trigonometric functions. A full list can be found in the HTSQL Reference. Using HTSQL to perform the "heavy lifting" of calculations can cut down on work--and errors--downstream. It harnesses the power of the server and lightens the burden on the client application.
Just as with other types of data, aggregate functions can be used to turn many numbers into one.
The sum function adds up a list of numbers, such as the credits for a courses by department:
/department{name, sum(course.credits)}
department
+----------------------------------------------+
| name | sum(course.credits) |
+------------------------+---------------------+
| Accounting | 15 |
| Art History | 19 |
| Studio Art | 19 |
The avg function averages (shows the arithmetic mean) for credits in a department:
/department{name, avg(course.credits)}
department
+----------------------------------------------+
| name | avg(course.credits) |
------------------------+---------------------+
| Accounting | 3.0000000000000000 |
| Art History | 3.1666666666666667 |
| Studio Art | 3.8000000000000000 |
...
Strings are sequences of characters. In the university database, most data is strings--IDs, course titles, names, etc. HTSQL can modify strings to give you the results you want for reports or programs.
Concatenation merges two strings, such as gender and dob, and/or a literal of your choice:
/student{'Student: ' + name + ' Gender :' + gender}
student
+-------------------------------------------+
| ((('Student: '+name)+' Gender: ')+gender) |
+-------------------------------------------+
| Student: Karen Yuen Gender: f |
| Student: Helmut Dietmark Gender: m |
...
To display only the first 14 characters of the title, use the head function. To display the last 10, use tail:
/course{title.head(14), title.head(10)}
course
+--------------------------------------------+
| number | title.head(14) | title.tail(10) |
+--------+------------------+----------------+
| 200 | "Principles of " | counting I |
| 315 | Financial Acco | Accounting |
| 426 | Corporate Taxa | e Taxation |
| 527 | Advanced Accou | Accounting |
...
To get a specific number of characters from the middle of the string, use the substr function. The first number tells HTSQL where, from left to start counting. The second tells it the number of characters to return:
/course{number, title.substr(2,10)
course
+---------------------------------+
| number | title.substr(2, 10) |
+-----------+---------------------+
| 200 | inciples o |
| 315 | nancial Ac |
| 426 | rporate Ta |
| 527 | vanced Acc |
...
Use slice to trim the the first four characters and leave the next five:
/instructor{name, phone.slice(4,9)}
instructor
+------------------------------------------+
| name | phone.slice(4, 9) |
+----------------------+-------------------+
| Andre Frenski | 1723 |
| Adrian Laang | 0973 |
| Antoinette Miller | 7728 |
...
The course table contains titles and descriptions of courses, some of which are quite long. To see exactly how long, use the length function:
/course{number, title.length()}
course
+----------------------------+
| number | title.length() |
+-----------+----------------+
| 200 | 26 |
| 315 | 20 |
| 426 | 18 |
| 527 | 19 |
...
The class table contains a quarter with the year and season for each class:
/class{course, quarter}
class
+--------------------------+
| course | quarter_code |
+-----------+--------------+
| 200 | 2010-SUMMER |
| 315 | 2011-FALL |
| 426 | 2011-FALL |
| 527 | 2011-SPRING |
...
To convert the quarter to lowercase, use the lower function:
/class{course, quarter.lower()}
class
+----------------------------------+
| course | quarter.lower() |
+-----------+----------------------+
| 200 | 2010-summer |
| 315 | 2011-fall |
| 426 | 2011-fall |
| 527 | 2011-spring |
...
To rename the Fall semester the Autumn semester, use the replace function. Put single quotes around the strings so HTSQL will treat them literally, and remember that matching is case sensitive:
/class{course, quarter.replace('FALL','AUTUMN')}
class
+----------------------------------------------------+
| course | quarter.replace('FALL', 'AUTUMN') |
+-----------+----------------------------------------+
| 200 | 2010-SUMMER |
| 315 | 2011-AUTUMN |
| 426 | 2011-AUTUMN |
| 527 | 2011-SPRING |
...
As with numerical functions, string functions use HTSQL to change the data returned from the database into what you want to see in your report. If you're writing requests to be used by a client application, string functions save programming time and processing power.
Many of the same operations that are performed with numbers (addition, subtraction, multiplication) can be performed with dates. There are also special functions to get the current date and time.
The student table contains personal information about students, including their names and birth dates. To find the current age of students, subtract their their birth dates from today's date:
/student{name, today()-dob}
student
+----------------------------------------+
| name | (today()-dob) |
+------------------------+---------------+-
| Karen Yuen | 6810 |
| Helmut Dietmark | 7452 |
| Christine Leung | 6906 |
| Narissa Maya | 6577 |
...
The result is the student's age--in days. If you're not good at doing math in your head, you can divide the result by 365 to see years:
/student{name, div(today()-dob,365)}
student
+--------------------------------------------------+
| name | div((today()-dob), 365) |
+------------------------+-------------------------+
| Karen Yuen | 18 |
| Helmut Dietmark | 20 |
| Christine Leung | 18 |
| Narissa Maya | 18 |
...
Missing or omitted data in a database is represented by a NULL. A NULL isn't an empty string or zero, but instead, a sign that data is missing. NULLs are often unavoidable, but can leave holes in reports and cause unexpected results when used in functions.
HTSQL provides special functions for dealing with NULLs. The instructor table contains contact information for faculty. Before generating the faculty directory, find out if any data is missing by using the is_null function:
/instructor{name, is_null(phone)}
The result shows in which cases it's true that the instructor has no phone number listed:
instructor +---------------------------------------+ | name | is_null(phone) | +----------------------+----------------+ | Alesia Caspar | true | | Andre Frensky | false | | Adrian Laang | false | ...
Some instructors don't want their phone numbers listed -- but showing a blank on a report can look bad. You can use coalesce function to replace NULL with a message:
/instructor{name, coalesce(phone, 'Unlisted')}
instructor
+----------------------------------------------------+
| name | coalesce(phone, 'Unlisted') |
+----------------------+-----------------------------+
| Alesia Caspar | Unlisted |
| Benjamin Burling | 555-1823 |
| Benjamin Sacks | 555-2212 |
| Adam Ferguson | Unlisted |
...
For scalar operations, NULLs can be considered "contagious"--they "infect" any operation where they occur. In the appointment table, the percentage column can be either 1.00 (full-time), 0.50 (part-time), or NULL (for instance, for a professor emeritus). For appointments in the astronomy department, you can write:
/appointment{instructor, 100*percent}?department='astro'
appointment
+------------------------------+
| instructor | (100*percent) |
+--------------+---------------+
| asacco | |
| cfergus12 | 50.00 |
| kmaas11 | |
(3 rows)
You might expect NULL * 100 to equal 0.00, but this is not the case, since a NULL isn't a zero.
Aggregate functions ignore NULLs. The example below compares the count of instructors and appointment percentages. Some instructors have a NULL for their percentage, so it's simply omitted from the count:
/department{code, count(appointment.percent), count(appointment)}
department
+-----------------------------------------------------------+
| code | count(appointment.percent) | count(appointment) |
+---------+----------------------------+--------------------+
| acc | 1 | 1 |
| arthis | 2 | 2 |
| artstd | 2 | 2 |
| astro | 1 | 3 |
...
The second column counts the number of instructors with an appointment percentage (i.e., who have teaching responsibilities). The third column counts the number of instructors. Astronomy apparently has three instructors, but only one who teaches.
Since the avg function ignores rows having a NULL, the astronomy department has an average of 0.50 (one active teacher with a .5 work load), not 0.167 (three teachers with a .50 work load among them):
/department{name+, avg(appointment.percent)}
department
+---------------------------------------------------+
| name | avg(appointment.percent) |
+------------------------+--------------------------+
| Accounting | 1.00000000000000000000 |
| Art History | 0.75000000000000000000 |
| Astronomy | 0.50000000000000000000 |
...
Testing for NULLs--and having a way to handle them--is a good practice to avoid ugly reports and costly errors.
Conditional expressions let you tell HTSQL what do do if something is true.
The conditional if tests for the condition, listed first, and then takes an action if it's true--in this case, adds a note to a new degree column if the instructor's title is dr:
/instructor{name, if(title='dr','doctorate') as degree}
instructor
+----------------------------------+
| name | degree |
+----------------------+-----------+
| Alesia Caspar | doctorate |
| Andre Frensky | |
| Adrian Laang | |
...
The conditional is_null tests for a NULL, and does something if is_null is true--in the case below, prints a message:
/instructor{name, if(is_null(phone),'No phone')}
instructor
+-------------------------------------------------------+
| name | if(is_null(phone), 'No phone') |
+----------------------+--------------------------------+
| Alesia Caspar | No phone |
| Andre Frensky | |
...
The conditional switch tests any number of conditions, and does something for the first it encounters that's true. The example below prints the cost of courses for different credit levels:
/course{department, number, credits,
switch(credits, 3,'$500',4,'$750',5,'Ask Bursar') as Cost}
course
+--------------------------------------------+
| department | number | credits | Cost |
+------------+--------+---------+------------+
| acc | 200 | 3 | $500 |
| arthis | 712 | 4 | $750 |
| artstd | 614 | 5 | Ask Bursar |
...
The conditional in tests a value against a list. Use an equals sign (=) followed by the list of values to see the rows that match:
/school?code={'edu','la','sm'}
school
+-------------------------------------------+-
| code | name |
+------+------------------------------------+-
| edu | College of Education |
| la | School of Liberal Arts |
| sm | School of Mathematics and Sciences |
(3 rows)
This is more succinct than the Boolean version:
/school?code='edu'|code='la'|code='sm'
You can also use the negative of in:
/school?code!={'edu','la','sm'}
All the examples above use a context--that is, a table to which an expression applies. HTSQL doesn't require a context. A scalar request is a single request that returns a value without referring to a table.
A scalar request can perform a calculation or simply print a string:
/{2*3*7 as "Life, the Universe, and Everything"}
| Life, the Universe, and Everything |
+------------------------------------+
| 42 |
Since the whole point of HTSQL is to connect to a database, you may not find a lot of uses for scalar requests. However, they provide important functions for developers, who may need to access scalar values:
/{today(), cos(0), pi(), true(), 'literal' }
| today() | cos(0) | pi() | true() | 'literal' |
+------------+--------+---------------+--------+-----------+
| 2012-12-21 | 1.0 | 3.14159265359 | true | literal |
HTSQL provides many more functions that what's covered here, all of which can be found in the HTSQL Reference. Using functions in scalar requests is a good way to familiarize yourself with them before you try them with live data.
We've tried to design HTSQL to make sense--to be intuitive and easy to use. We're not perfect, and neither are the underlying technologies--including SQL and URIs--that HTSQL relies on.
Below are some points to keep in mind, especially when working with unusual or complex queries.
Escaping is using special characters to prevent HTSQL (or any program) from interpreting something as a command.
For literal values that contain quotes, the single-quote (') is escaped by doubling up the quote:
/instructor?name~'O''Brien'
instructor
+---------------------------------------------------------------------+
| code | title | name | phone | email |
+-----------+-------+-----------------+-------+-----------------------+
| tobrien21 | prof | Timothy O'Brien | | tobrien21@example.edu |
(1 row)
Without the extra single quote, HTSQL will think there are three single quotes setting off the search string and respond with an error message:
invalid syntax: unexpected character "'" at the position 26:
/instructor?name~'O'Brien'
^
Since HTSQL requests are uniform resource locators (URLs), the percent character is used to encode special characters (as UTF-8 sequences). This is consistent with international standards for URLs.
It's a bit ugly, but for compatibility with URLs, HTSQL queries must always percent-encode the percent sign (%) as %25 and the hash (#) as %23. For example, the title #5 is the 50% solution must be encoded as:
/course?title='%235 is the 50%25 solution'
Many desktop applications escape additional characters in your HTSQL request. It's not unusual to copy and paste an HTSQL URL between applications and see something like this:
/course?title=%27Relativity%20%26%20Cosmology%27
In this case the single quote is encoded as %27 and the space is encoded as %20. This practice is also called "percent encoding" or "URL encoding".
HTSQL's use of characters that require percent-encoding in URLs is an necessary compromise to make requests readable. The important thing to remember is that if you copy and paste a non-escaped HTSQL request into your browser, it will work. Whether it will work anywhere else depends on the application. In many cases, percent-encoding the request will fix the problem, even if it isn't pretty.
When combining operators, be aware of the order in which HTSQL handles them:
``|, &, ->, !, =/!=/~, +/-, */div/mod, ^``
You can use as many operators as you wish in a single expression:
/course{number, title, credits}?credits=4|credits=2&number>400
course
+------------------------------------------------------------------------+
| number | title | credits |
+-----------+--------------------------------------------------+---------+
| 712 | Museum and Gallery Management | 4 |
| 411 | Underwater Basket Weaving | 4 |
| 412 | Ecology Writing Workshop | 2 |
| 105 | Introduction to Spanish | 4 |
...
This request returns all courses with four credits, OR courses with two credits whose course number is greater than 400.
To change the order of evaluation, use parentheses. In this request, the conjunction (&) is evaluated before the alternation (|):
/course?{number, title, credits}?(credits=4|credits=2)&number>400
course
+------------------------------------------------------------------------+
| number | title | credits |
+-----------+--------------------------------------------------+---------+
| 712 | Museum and Gallery Management | 4 |
| 411 | Underwater Basket Weaving | 4 |
| 509 | Twentieth Century Printmaking | 4 |
| 710 | Laboratory in Computer Science | 4 |
...
This request returns courses with EITHER two or four credits whose course number is greater than 400.
The as (or alias) macro binds tighter than addition, which can lead to unexpected results. In some cases, parenthesis around the aliased expression is needed:
/{1 + 2 as bad, (1+2) as good}
| (1+bad) | good |
+---------+------+
| 3 | 3 |
Beyond operator precedence, HTSQL has flexible syntax to help you write more readable requests. Most functions can be written as either function (a,b) or a function b:
/{100 div 2, div(100, 2)}
| (100 div 2) | div(100, 2) |
+-------------+-------------+
| 50 | 50 |
HTSQL always performs an outer join, meaning all data in both tables is returned, even if rows in one table don't have corresponding rows in the second table. When a row in the joined table is absent, NULL is returned for corresponding columns.
This makes it difficult to determine if a cell is NULL because the row is missing, or if the row exists but the cell is NULL. For example, thee instructor table has a related table, confidential, which contains Social Security numbers and other private data. Both tables have missing data, represented as NULL. The university doesn't bother to collect private data for guest instructors, so some instructors who are listed in the instructor table are not in the confidential table.
The request below asks for the instructor's name from the instructor table and their SSNs and home phones from the confidential table:
/instructor{name, confidential.ssn, confidential.home_phone}
instructor
+-------------------------------------------------------------------+
| name | confidential.ssn | confidential.home_phone |
+----------------------+------------------+-------------------------+
| Alesia Caspar | | |
| Andre Frenski | 987-65-4321 | |
| Adrian Laang | 788-33-0000 | 702-555-1721 |
...
It's obvious that Prof. Frenski has a record in the confidential table, and that it's missing his home phone. But what about Prof. Caspar? Because of the outer join always returns all records from all tables, it's impossible to tell whether she has a record in the confidential table and the columns we picked just happen to be NULL, or whether her record is missing altogether.
HTSQL perform Boolean casting to let you carry out true/false tests for strings and numbers:
/instructor{name, is_null(phone)}
instructor
+---------------------------------------+
| name | is_null(phone) |
+----------------------+----------------+
| Alesia Caspar | true |
| Andre Frensky | false |
...
A Boolean test for a string is false if the string has a length of zero. A test for a course number is false if the number equals zero. Any other data type returns false if it's NULL:
/course?!credits
course
+----------------------------------------------------------------------- ...
| department | number | title | credits | descr
+------------+--------+--------------------------------+---------+------ ...
| artstd | 119 | Spring Basket Weaving Workshop | | A jus
| artstd | 714 | Peer Portfolio Review | 0 | An op
| edpol | 551 | Classroom Visit | | Elect
(3 rows)
This request returns courses where credits equals zero OR credits is NULL.
What if you only want to see courses where the credits is NULL? You must test specifically for NULL, since the Boolean ! will otherwise return zero-credit courses:
/course?{is_null(credits)}
course
+----------------------------------------------------------------------- ...
| department | number | title | credits | descr
+------------+--------+--------------------------------+---------+------ ...
| artstd | 119 | Spring Basket Weaving Workshop | | A jus
| edpol | 551 | Classroom Visit | | Elect
(2 rows)
User interfaces often blur the distinction between 0 and NULL, but you should keep it in mind when making Boolean requests.
Another use of casting is to change the data type when you want to use a function like concatenation. The course ID is the department (a string) plus the course number:
/course{department+number}
Because of the mismatch between department and number, this will result in an error:
invalid data: invalid input syntax for type numeric: "astro"
at the position 2:
/course{department+number}
^------------------------
Recasting number as a string allows you to concatenate both:
/course{department+string(number)}
course
+-----------------------------+
| (department+string(number)) |
+-----------------------------+
| acc200 |
| acc315 |
...
You can recast columns as other data types, including integer, float, decimal, and, of course, boolean.
TODO: write paragraph here describing how the translation is done, or link to an essay that accomplishes this.
HTSQL is a native web query language for your SQL database. Prometheus Research offers HTSQL under a dual licensing model.
- HTSQL Community Edition is free of charge (even for proprietary applications) when used in conjunction with open source database system software, such as SQLite, PostgreSQL or MySQL.
- HTSQL Subscription is our commercial offering for use with proprietary licensed database software or for those wishing a support contract.
The community release is available at http://htsql.org -- you can find us in #htsql on irc.freenode.net
Support contracts and commercial licensing are available at http://htsql.com