HTSQL Grammar¶

Table of Contents

Encoding
Lexical Structure
Syntax Structure

A valid input of an HTSQL processor is called an HTSQL query.

In a regular mode of operation, an HTSQL processor is started as a web service and accepts queries as HTTP GET requests. However, an HTSQL query can also be executed using a command-line utility htsql-ctl or via internal Python API.

Encoding ¶

An HTSQL query is a string of characters in UTF-8 encoding. Octets composing the string could be written literally or percent-encoded. A percent-encoded octet is serialized as three characters: % followed by two hexdecimal digits encoding the octet value.

◥/{'HTSQL', %27HTSQL%27, %27%48%54%53%51%4C%27}

‘HTSQL’	‘HTSQL’	‘HTSQL’
HTSQL	HTSQL	HTSQL

Percent-encoding is useful for transmitting an HTSQL query via channels that forbid certain characters in literal form. The list of characters that should be encoded depends on the channel type, but the percent (%) character itself must always be percent-encoded.

◥/{'%25'}

‘%25’
%

A NUL character cannot appear in an HTSQL query, neither in literal nor in percent-encoded form.

The HTSQL processor decodes percent-encoded octets before parsing the query. As a consequence, a percent-encoded punctuation or operator character still plays its syntax role.

Lexical Structure ¶

An HTSQL query is parsed into a sequence of tokens. The following tokens are recognized.

Name¶

A sequence of alphanumeric characters that does not start with a digit.

◥/school

code	name	campus
art	School of Art & Design	old
bus	School of Business	south
edu	College of Education	old

Number¶

A numeric literal: integer, decimal and exponential notations are recognized.

◥/{60, 2.125, 271828e-5}

60	2.125	271828e-5
60	2.125	2.71828

String¶

A string literal enclosed in single quotes; any single quote character should be doubled.

◥/{'HTSQL', 'O''Reilly'}

‘HTSQL’	‘O’‘Reilly’
HTSQL	O’Reilly

Symbol¶

A valid symbol in the HTSQL grammar; that includes operators and punctuation characters. Some symbols are represented by more than one character (e.g. <=, !~).

Individual tokens may be separated by whitespace characters.

See htsql.tr.scan.Scanner for detailed description of HTSQL tokens.

Syntax Structure ¶

A sequence of HTSQL tokens must obey the HTSQL grammar.

An HTSQL query starts is a segment expression optionally followed by a sequence of commands.

The following table lists HTSQL operations in the order of precedence, lowest to highest.

Operation	Description	Example Input	Output
`/ T`	segment	`/school`
`x :fn`	infix function call	`'HTSQL':length`	`5`
`x :fn y`		`1/3 :round 2`	`0.33`
`x :fn (y,z,...)`		`'HTSQL':slice(1,-1)`	`'TSQ'`
`x +`, `x -`	sorting direction	`program{degree+}`
`T ? p`	sieve	`program?degree='ms'`
`T ^ x`	projection	`program^degree`
`T {x,y,...}`	selection	`school{code,name}`
`p \| q`	logical OR	`true\|false`	`true`
`p & q`	logical AND	`true&false`	`false`
`! p`	logical NOT	`!true`	`false`
`x = y`, `x != y`,	comparison	`2+2=4`	`true`
`x == y`, `x !== y`		`'HTSQL'==null`	`false`
`x ~ y`, `x !~ y`		`'HTSQL'~'SQL'`	`true`
`x < y`, `x <= y`,		`12<7`	`false`
`x > y`, `x >= y`		`12>=7`	`true`
`x + y`, `x - y`	addition, subtraction	`'HT'+'SQL'`	`'HTSQL'`
`x * y`, `x / y`	multiplication, division	`12*7`	`84`
`- x`	negation	`-42`
`x -> T`	attachment	`'south' -> school{campus}`
`T := x`	assignment	`num_prog := count(program)`
`S . T`	composition	`school.program`
`T [id]`	locator	`course[comp.304]`
`@ T`	detachment	`@school`
`{x,y,...}`	record	`{'bs','ms'}`
`(...)`	grouping	`(7+4)*2`	`22`
`*`	wildcard selection	`school.*`
`* number`	wildcard selection	`school.*1`
`^`	projection complement	`count(^)`
`$ name`	reference	`$code`
`fn (...)`	function call	`round(1/3,2)`	`0.33`
`name`		`school`
`number`		`60`, `2.125`, `271828e-5`
`string`		`'HTSQL'`

A command starts with / followed by a function application in infix notation.

◥/school/:csv

code,name,campus
art,School of Art & Design,old
bus,School of Business,south
…

Next we describe individual syntax elements.

Atomic Expressions¶

An atomic expression is a basic syntax unit. HTSQL recognizes the following atoms.

Identifier¶

An identifier is a sequence of characters which contains Latin letters, underscores (_), decimal digits and those Unicode characters that are classified as alphanumeric. An identifier must not start with a digit.

In HTSQL, identifiers are case-insensitive.

Identifiers are used to refer to database entities such as tables and attributes, to define calculated attributes, and to call functions.

◥/school{name, count(department)}/:csv

name,count(department)
School of Art & Design,1
School of Business,3
…

In this example, four identifiers school, name, count and department represent respectively a table, a table attribute, a built-in function and a table link.

Literal¶

HTSQL supports two types of literal values: quoted and unquoted.

An unquoted (or numeric) literal is a number written in integer, decimal or exponential notation.

◥/{60, 2.125, 271828e-5}

60	2.125	271828e-5
60	2.125	2.71828

The range of allowed numeric values depends on the database backend. The type of a numeric literal is determined from notation: literals written in integer, decimal and exponential notation are assigned to integer, decimal and float data type respectively.

A quoted literal is a (possibly empty) sequence of arbitrary characters enclosed in single quotes. Any single quote in the value must be doubled.

◥/{'HTSQL', 'O''Reilly'}

‘HTSQL’	‘O’‘Reilly’
HTSQL	O’Reilly

The data type of a quoted literal is inferred from the context in which the literal is used; the default data type is string.

Wildcard¶

A wildcard selection (*) selects all output columns of the table.

◥/department{school.*, *}

code	name	campus	code	name	school_code
bus	School of Business	south	acc	Accounting	bus
la	School of Arts and Humanities	old	arthis	Art History	la
ns	School of Natural Sciences	old	astro	Astronomy	ns

When followed by an integer literal N, a wildcard selects N-th output column of the table. N starts from 1 and should not exceed the number of output columns.

◥/school{name, count(department)}?*2>=4

name	count(department)
School of Engineering	4
School of Arts and Humanities	6
School of Music & Dance	4

Complement¶

A projection complement (^) represents a complement link from a projection to the projected flow.

Do not confuse a projection complement with a binary projection operator, which is also represented with the ^ character.

◥/program^degree{*, count(^)}

degree	count(^)
ba	10
bs	10
ct	4

In this example, the first and the second occurrences of ^ indicate a projection operator and a projection complement respectively.

Grouping¶

Any expression enclosed in parentheses ((...)) is treated syntactically as a single atom. Use grouping to override the default operator precedence.

◥/(7+4)*2

22

Do not confuse a grouping operation with a function call, which also uses parentheses.

Record¶

A comma-separated list of expressions enclosed in curly brackets ({...}) is called a record expression. Some functions and operators accept records as a way to specify multiple values.

◥/school?code={'eng','ns'}

code	name	campus
eng	School of Engineering	north
ns	School of Natural Sciences	old

Reference¶

A reference is an identifier preceded by a dollar sign ($). A reference is used to access a value defined in a different naming scope.

◥/course?credits>$avg_credits
 :where $avg_credits := avg(course.credits)

department_code	no	title	credits	description
acc	315	Financial Accounting	5	Integration of the conceptual and computational aspects of asset, liability and stockholders equity accounting.
acc	511	Audit	5	This course considers legal, procedural and ethical aspects of audit in accounting.
acc	620	Accounting Internship	6	Supervised internship in a business or nonprofit organization on an accounting position.

In this example, a reference $avg_credits is defined in the root scope, but accessed in the scope of course.

For a more detailed description of references, see the section on naming scopes.

Function Calls¶

HTSQL has a large library of built-in functions and can be extended with user-defined functions.

A function call is represented as a function name followed by (, a comma-separated list of arguments, and ).

◥/round(1/3, 2)

0.33

A function may accept no arguments, but the parentheses are still required.

◥/today()

2013-01-31

For functions with at least one argument, HTSQL supports an alternative infix call notation. In this notation, the expression starts with the first argument followed by : and a function name, and then the rest of the arguments. The trailing arguments must be enclosed in parentheses if their number is greater than one.

◥/{today() :year, 1/3 :round 2, 'HTSQL' :slice(1, -1)}

today() :year	1/3 :round 2	‘HTSQL’ :slice(1,-1)
2013	0.33	TSQ

This example could be equivalently expressed as

◥/{year(today()), round(1/3, 2), slice('HTSQL', 1, -1)}

year(today())	round(1/3,2)	slice(‘HTSQL’,1,-1)
2013	0.33	TSQ

Infix function calls are composable and have the lowest precedence among the operators.

◥/{'h'+'t'+'t'+'p' :replace('tp', 'sql') :upper}

‘h’+’t’+’t’+’p’ :replace(‘tp’,’sql’) :upper
HTSQL

A function which argument is a segment expression is called a command.

◥/csv(/school)

code,name,campus
art,School of Art & Design,old
bus,School of Business,south
…

To use infix call notation with a command, prepend the : indicator with /.

◥/school/:csv

code,name,campus
art,School of Art & Design,old
bus,School of Business,south
…

Consider the difference between regular infix function call and a command application.

◥/school:top/:csv

code,name,campus
art,School of Art & Design,old

This example could be equivalently expressed as

◥/csv(/top(school))

code,name,campus
art,School of Art & Design,old

For a list and description of built-in functions, see Functions and Operators.

Operators¶

An HTSQL operator is denoted by a special character or a sequence of characters (e.g. +, <=). HTSQL has infix, prefix and postfix operators, and some operators admit all three forms.

The current version of HTSQL does not support user-defined operators; future versions may add this ability.

In HTSQL, the order in which operators are applied is determined by operator precedence. For example, multiplication and division operators have a higher precedence than addition and subtraction.

Some HTSQL operators are composable (e.g. arithmetic operators) and some are not (e.g. equality operators). We call the former associative and the latter non-associative.

Below we describe the syntax of HTSQL operators. For a more comprehensive description, see Functions and Operators.

Logical Operators¶

HTSQL supports the following logical operators:

logical OR: p | q
logical AND: p & q
logical NOT: ! p

In this list, the operators are sorted by the order of precedence, from lowest to highest. All logical operators are left-associative.

◥/{true|false, true&false, !false}

true\|false	true&false	!false
true	false	true

Comparison Operators¶

HTSQL supports the following comparison operators:

equality operators: x = y, x != y, x == y, x !== y
containing operators: x ~ y, x !~ y
ordering operators: x < y, x <= y, x > y, x >= y

◥/{2+2=4, 'HTSQL'~'SQL', 12>7&7>=2}

2+2=4	‘HTSQL’~’SQL’	12>7&7>=2
true	true	true

All comparison operators have the same precedence and are not associative.

Future versions of HTSQL may make ordering operators left-associative to express between operation (e.g. a <= x <= b).

Arithmetic Operators¶

HTSQL supports the usual set of arithmetic operators:

addition: x + y
subtraction: x - y
multiplication: x * y
division: x / y
negation: - x

◥/{'HT'+'SQL', today()-1, -6*4/5}

Arithmetic operators have standard precedence and associativity.

Flow Operators¶

HTSQL supports specialized operators to work with flow expressions:

sieve: T ? p
projection: T ^ x
selection: T {x,y,...}

The sieve operator (T ? p) produces rows of T satisfying condition p.

◥/school?campus='south'

code	name	campus
bus	School of Business	south
mus	School of Music & Dance	south

The projection operator (T ^ x) produces a flow of unique values of x as it ranges over T. Do not confuse the projection operator with a projection complement.

◥/program^degree

degree
ba
bs
ct

The selection operator (T {x,y,...}) specifies output columns.

◥/school{code, name}

code	name
art	School of Art & Design
bus	School of Business
edu	College of Education

Sieve, projection and selection operators have the same precedence and are left-associative.

◥/school?count(department)>2
       ^campus
       {campus, avg(school.count(department))}

campus	avg(school.count(department))
north	4.0
old	5.0
south	3.5

Composition¶

HTSQL supports a flow composition operator:

T . x

The composition operator evaluates x for each row of the flow T. The values of x form the resulting flow.

◥/school.filter(campus='south').department

code	name	school_code
acc	Accounting	bus
econ	Economics	bus
mm	Management & Marketing	bus

The composition operator is left-associative.

Locator¶

The locator operation selects an individual entity by its identity:

T [id]

The identity is a sequence of attributes and links which uniquely identify an entity. Values of separate attributes are separated by a period (.). An individual value could be left unquoted when it consists of digits, alphabetical characters or dash (-). Values could be grouped using pairs of brackets ([]) or parentheses (()).

◥/department[comp]

code	name	school_code
comp	Computer Science	eng

◥/course[comp.515]

department_code	no	title	credits	description
comp	515	Software Design	3	Study of good software development techniques: UML, object-oriented design, design patterns, GUI design principles, testing, debugging and profiling.

◥/class[(comp.515).(2010.spring).001]

department_code	course_no	year	season	section	instructor_code	class_seq
comp	515	2010	spring	001	rmejia	1405

Attachment and Detachment¶

HTSQL has two operators for generating ad-hoc links:

detachment: @ T
attachment: x -> T

The detachment operator (@ T) generates an ad-hoc link to T by associating each row from the input flow with all rows from T.

◥/course?credits>avg(@course.credits)

department_code	no	title	credits	description
acc	315	Financial Accounting	5	Integration of the conceptual and computational aspects of asset, liability and stockholders equity accounting.
acc	511	Audit	5	This course considers legal, procedural and ethical aspects of audit in accounting.
acc	620	Accounting Internship	6	Supervised internship in a business or nonprofit organization on an accounting position.

The precedence of the detachment operator is higher than of any other operator. Therefore, to apply the operator to any expression other than an identifier or a function call, use parentheses.

The attachment operator (x -> T) generates ad-hoc link to T by associating each row from the input flow with all rows from T such that values of expression x evaluated against respective rows coincide.

◥/student{name, dob+}?count(dob -> student)>2

name	dob
Virginia Hester	1986-03-20
Marvin Hall	1986-03-20
Douglas Robles	1986-03-20

Segment Operator¶

A segment expression is a prefix operator:

/ T

A segment expression converts a flow to a list value.

◥/school{name, /department{name}}

name	department
name	name
School of Art & Design	Studio Art
School of Business	Accounting
	Economics
	Management & Marketing

Sorting Decorators¶

The following postfix decorators indicate ascending and descending sorting order respectively:

x +, x -

◥/course.sort(department_code+,credits-)

department_code	no	title	credits	description
acc	620	Accounting Internship	6	Supervised internship in a business or nonprofit organization on an accounting position.
acc	315	Financial Accounting	5	Integration of the conceptual and computational aspects of asset, liability and stockholders equity accounting.
acc	511	Audit	5	This course considers legal, procedural and ethical aspects of audit in accounting.

Sorting decorators have the same precedence as infix function call.

Sorting decorators are only meaningful when used as arguments of the sort() function and in a selector expression.

Assignment¶

An assignment expression has the form:

T := x

The left side of an assignment expression indicates the name and formal parameters (if any) of a calculated attribute. It must be an identifier, a reference or a function call and can be preceded by an optional dot-separated sequence of identifiers.

The right side of an assignment is an arbitrary expression indicating the value of a calculated attribute.

◥/school{name, num_dept}?num_dept>=4
 :where school.num_dept := count(department)

name	num_dept
School of Engineering	4
School of Arts and Humanities	6
School of Music & Dance	4

An assignment expression could be used only as an argument of functions define() and where(), or in a selector expression.