Source

htsql / doc / reference.rst

Full commit

HTSQL Reference

This page describes the use of the HTSQL language. We start with describing the syntax of HTSQL, then we list available data types and functions, and conclude with a discussion on the HTSQL data model and the query semantics.

Syntax

A valid input of the HTSQL translator is called an HTSQL query.

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 written as a series of three characters: % followed by two hexdecimal digits encoding the octet value.

The percent (%) character itself must always be percent-encoded.

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

Percent-encoding is useful when transmitting an HTSQL query via channels that forbid certain characters in literal form.

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.
NUMBER
A numeric literal: integer, float and scientific notations are recognized.
STRING
A string literal enclosed in single quotes; any single quote character should be doubled.
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 :class:`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 with a symbol / followed by a valid HTSQL expression and concluded with an optional query decorator.

The following table lists HTSQL operations in the order of precedence.

Operation Description Example Input Output
x :name postfix function call 'HTSQL':length 5
x :name y 1/3 :round 2 0.33
x :name (y,z,...) 'HTSQL':slice(1,-1) 'TSQ'
x +, x - sorting decorator program{degree+}  
p | q logical OR operator true()|false() true()
p & q logical AND operator true()&false() false()
! p logical NOT operator !true() false()
x = y, x != y, x == y, x !== y comparison operators 2+2=4 true()
x ~ y, x !~ y 'HTSQL'~'SQL' true()
x < y, x <= y, 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  
chain ? p sieve operator program?degree='ms'  
chain {x,y,...} selector operator school{code,name}  
chain . name (...) chained function call school.count(program)  
chain . * wildcard attribute school.*  
chain . name traversal operator school.program  
attribute access school.name  
{...} selection 9!={2,3,5,7,11} true()
name (...) function call round(1/3,2) 0.33
(...) grouping (7+4)*2 22
* wildcard name school{*}  
name   school  
number   60, 2.125, 271828e-5  
string   'HTSQL'  

An optional query decorator starts with a symbol / followed by a postfix function call.

See :class:`htsql.tr.parse.QueryParser` for detailed description of the HTSQL grammar.

Data Types

Type Description Example Input Output
boolean logical data type, with two values: TRUE and FALSE true()  
false()  
integer binary integer type 4096  
decimal arbitrary-precision exact numeric type 124.49  
float IEEE 754 floating-point inexact numeric type 271828e-5  
string text data type string('HTSQL')  
enum enumeration data type, with predefined set of valid string values    
date date data type date('2010-04-15')  
opaque unrecognized data type    

Special Data Types

Type Description Example Input Output
untyped initially assigned type of quoted literals 'HTSQL'  
tuple type of chain expressions    
void type without any valid values    

Function Syntax

A few observations about HTSQL's function and operator usage:

  • For any function, "f(x,y)" can be written "x :f(y)" and depending upon grammatical context, abbreviated to "x :f y".
  • Unless annotated, functions are null-regular, that is, if any of their arguments is null() then the result is null().
  • HTSQL uses zero-based indexes, e.g. the 1st item in a collection is indexed by 0, the 2nd character indexed by 1, and so on.
  • A single quoted string in an HTSQL request is an untyped literal, and is automatically cast depending upon the context -- it is not necessarily a string value.

Logical Operators

Function Description Example Input Output
boolean(x) cast x to Boolean boolean(true()) true()
boolean(false()) false()
boolean(1) true()
boolean(string('HTSQL')) true()
boolean(string('')) false()
boolean(date('2010-04-15')) true()
boolean(null()) null()
boolean(string(null())) false()
true() logical TRUE value true()  
false() logical FALSE value false()  
p & q logical AND operator; treats nulls as UNKNOWN true()&true() true()
true()&false() false()
false()&false() false()
true()&null() null()
false()&null() false()
p | q logical OR operator; treats nulls as UNKNOWN true()|true() true()
true()|false() true()
false()|false() false()
true()|null() true()
false()|null() null()
!p logical NOT operator; treats nulls as UNKNOWN !true() false()
!false() true()
!null() null()
is_null(x) x is null is_null(null()) true()
x = y x is equal to y 'HTSQL'='QUEL' false()
2=null() null()
x != y x is not equal to y 'HTSQL'!='QUEL' true()
2!=null() null()
x == y x is equal to y; treats nulls as regular values 'HTSQL'=='QUEL' false()
2==null() false()
x !== y x is not equal to y; treats nulls as regular values 'HTSQL'!=='QUEL' true()
2!==null() true()
x = {a,b,c,...} x is among a, b, c, ... 5={2,3,5,7}' true()
x != {a,b,c,...} x is not among a, b, c, ... 5!={2,3,5,7}' false()
x < y x is less than y 1<10 true()
'omega'<'alpha' false()
x <= y x is less than or equal to y 1<=10 true()
'omega'<='alpha' false()
x > y x is greater than y 1>10 false()
'omega'>'alpha' true()
x >= y x is greater than or equal to y 1>=10 false()
'omega'>='alpha' true()
if_null(x,y) x if x is not null; y otherwise if_null(1,0) 1
if_null(null(),0) 0
null_if(x,y) x if x is not equal to y; null otherwise null_if(1,0) 1
null_if(0,0) null()
if(p1,c1,...,pn,cn) first ck such that pk is TRUE; o or null otherwise if(true(),'up','down') 'up'
if(p1,c1,...,pn,cn,o) if(false(),'up','down') 'down'
switch(x,y1,c1,...,yn,cn) first ck such that x is equal to yk; o or null otherwise switch(1,1,'up',0,'down') 'up'
switch(x,y1,c1,...,yn,cn,o) switch(0,1,'up',0,'down') 'down'

Numeric Functions

Function Description Example Input Output
integer(x) cast x to integer integer(60) 60
integer(17.25) 17
integer(223607e-5) 2
integer(string('60')) 60
decimal(x) cast x to decimal decimal(60) 60.0
decimal(17.25) 17.25
decimal(223607e-5) 2.23607
decimal(string('17.25')) 17.25
float(x) cast x to float float(60) 6e1
float(17.25) 1725e-2
float(223607e-5) 223607e-5
float(string('223607e-5')) 223607e-5
-x negate x -7  
x + y add x to y 13+7 20
x - y subtract y from x 13-7 6
x * y multiply x by y 13*7 91
x / y divide x by y 13/7 1.85714285714286
round(x) round x to the nearest integer round(17.25) 17
round(x,n) round x to n decimal places round(17.25,1) 17.3

String Functions

By convention, string functions take a string as its first parameter. When an untyped literal, such as 'value' is used and a string is expected, it is automatically cast. Hence, for convenience, we write string typed values using single quotes in the output column.

Function Description Example Input Output
string(x) cast x to string string('Hello') 'Hello'
string(1.0) '1.0'
string(null()) null()
string(true()) 'true'
string(false()) 'false'
string(date('2010-04-15')) '2010-04-15'
length(s) number of characters in s length('HTSQL') 5
x + y concatenate x and y; treats nulls as empty strings 'Hello' + ' World' 'Hello World'
'Hello' + null() 'Hello'
x ~ y x contains y; case-insensitive 'HTSQL' ~ 'sql' true()
x !~ y x does not contain y; case-insensitive 'HTSQL' !~ 'sql' false()
head(s) first character of s head('HTSQL') 'H'
head(s,n) first n characters of s head('HTSQL',2) 'HT'
head('HTSQL',-3) 'HT'
tail(s) last character of s tail('HTSQL') 'L'
tail(s,n) last n characters of s tail('HTSQL',3) 'SQL'
tail('HTSQL',-2) 'SQL'
slice(s,i,j) i-th to j-th characters of s; null or missing index means the beginning or the end of the string slice('HTSQL',1,4) 'TSQ'
slice('HTSQL',-4,-1) 'TSQ'
slice('HTSQL',null(),2) 'HT'
slice('HTSQL',2,null()) 'SQL'
at(s,k) k-th character of s at('HTSQL',2) 'S'
at(s,k,n) n characters of s starting with k-th character at('HTSQL',1,3) 'TSQ'
at('HTSQL,-4,3) 'TSQ'
at('HTSQL,4,-3) 'TSQ'
upper(s) upper case of s upper('htsql') 'HTSQL'
lower(s) lower case of s lower('HTSQL') 'htsql'
trim(s) strip leading and trailing spaces from s trim('  HTSQL  ') 'HTSQL'
ltrim(s) strip leading spaces from s ltrim('  HTSQL  ') 'HTSQL  '
rtrim(s) strips trailing spaces from s rtrim('  HTSQL  ') '  HTSQL'
replace(s,x,y) replace all occurences of x in s with y; in s with y; null x is treated as an empty string replace('HTSQL','SQL','RAF') 'HTRAF'
replace('HTSQL',null(),'RAF') 'HTSQL'

Date Functions

Function Description Example Input Output
date(x) cast x to date date('2010-04-15')  
date(yyyy,mm,dd) date yyyy-mm-dd date(2010,4,15) date('2010-04-15')
date(2010,3,46) date('2010-04-15')
date(2011,-8,15) date('2010-04-15')
today() current date today()  
year(d) year of d year(date('2010-04-15')) 2010
month(d) month of d month(date('2010-04-15')) 4
day(d) day of d day(date('2010-04-15')) 15
d + n increment d by n days date('1991-08-20')+6813 date('2010-04-15')
d - n decrement d by n days date('2028-12-09')-6813 date('2010-04-15')
d1 - d2 number of days between d1 and d2 date('2028-12-09')-date('1991-08-20') 13626

Aggregate Functions

Function Description Example Input Output
exists(ps) TRUE if ps contains at least one TRUE value; FALSE otherwise exists(course.credits>5)  
every(ps) TRUE if ps contains only TRUE values; FALSE otherwise every(course.credits>5)  
count(ps) number of TRUE values in ps count(course.credits>5)  
min(xs) smallest x in sx min(course.credits)  
max(xs) largest x in sx max(course.credits)  
sum(xs) sum of x in xs sum(course.credits)  
avg(xs) average value of x in xs avg(course.credits)  

Navigation Operations

Function Description Example Input Output
chain . link traverse a link school.program  
chain . attr extract attribute value school.name  
chain . * extract all attributes school.*  
chain ? p records from chain satisfying condition p school?code='edu'  
chain.sort(x,...) records from chain sorted by x, ... course.sort(credits-)  
chain.limit(n) first n records from chain course.limit(10)  
chain.limit(n,k) n records from chain starting from k-th course.limit(10,20)  
chain {x,...} select x, ... from chain school{code,name}  
root() scalar class    
this() current chain    

Decorators

Function Description Example Input Output
as(x,title) set the column title number :as 'No.'  
x+ sort by x in ascending order credits+  
x- sort by x in descending order credits-  

Formatters

Function Description Example Input Output
/:html HTML tabular output    
/:txt plain text tabular output    
/:csv CSV (comma-separated values) output    
/:json JSON-serialized output