Retrieving Data

Zazler compiles an SQL statement according to the given variables (mostly from URL). Variables can include expressions that will be converted to SQL using the information about the structure of the database and additional variables defined by the user on the URL. The user is allowed to use most of SQL possibilities that are secure (i.e. those that don't access the operating system). Direct access to writing SQL is omitted, therefore preventing SQL injections.

One of the goals of Zazler is to present expressions as compact and readable as possible. Therefore whitespaces are not allowed in expressions and texts containing special characters (characters not allowed on URL) are provided as variables. Otherwise it would be even more confusing to read from a URL.

Syntax components

Zazler makes use of URI generic syntax:

foo://example.com/{database}{/tablename}{.template}[?variable[&...]]
Component Meaning
database The name of the database.
tablename The name of the table in the database or special table.
template The name of the template that is used to show query results.
variable Variables are the components of Zazler that can be used in compiling an SQL statement. Some variables are consumed exclusively by templates.

Variables

Variables are given from URL in a standard way. Some variables are consumed exclusively for making an SQL statement, other variables are consumed by templates and bundled formats.

Variable Syntax
select select=expression[@alias][,…]

Note: @ is interpreted as "AS" which retrieves data as "alias"
where where=expression
order order=expression[,…]

Note: ordering functions can also be used to order rows ascendingly or descendingly.
group group=expression[,…]
having having=expression
limit limit=rows[,offset]

Note: specifies the maximum number of rows to return, starting from specified offset.
join {join|ljoin|rjoin|cjoin|fjoin}.tablename[@alias]=expression

Note: @ is interpreted as "AS" which retrieves data as "alias"

Special variable "opts"

A special variable "opts" is used to pass keywords or boolean values, e.g. in JSON a query can be made as "opts=map" or "opts=-map". Some values in "opts" are also read by SQL generator, e.g. "SELECT DISTINCT" can be made if "opts=distinct" is added. For combining these values "opts=-map,distinct" should be added.

The following keywords can be used with "opts" variable:

Keyword Meaning
opts=[-]head Whether a header should be added to CSV, TSV or HTML.
opts=[-]map Whether the output format should be served as a key-value, i.e. map format or an array (JSON).
opts=[-]distinct Whether only distinct values should be returned.
opts=[-]askpw Whether a password should be asked. Note: may be useful when testing authentication with JavaScript silently, add "opts=-askpw".

Expression

Expression may include table field, arguments, operators and additional expressions that may in itself include variables defined by the user on the URL to compile an SQL statement. The syntax of an expression is following:

{table.field|function|integer|text|expression{operator}expression|(expression)}
Term Definition
table.field The name of the table field
function Functions as described under functions section
integer An integer that can also be negative.

Note: Numbers with decimal numerals should be defined as a separate variable (see term "text" below).
text Text is handled as follows:
1) first a user defined variable with that name is searched on the URL;
2) if no user defined variable with that name is found on the URL, then a table field with that name is searched;
3) if neither a user defined variable nor a table field with that name is found, then this text is handled as a constant.
operator Operators as described under general functions

Joins

Zazler supports following join types:

Join type Meaning
join Inner join
ljoin Left join
rjoin Right join
cjoin Cross join
fjoin Full join

Examples

Concatenate following: 1) a user defined variable "h", 2) a user defined variable "spc", 3) table field "firstname", 4) a user defined variable "spc", 5) table field "lastname" and 6) a user defined variable "ex". Define variable "spc" as whitespace, variable "h" as "Hi," and variable "ex" as exclamation mark.

?select=concat(h,spc,firstname,spc,lastname,ex)&spc=%20&h=Hi,&ex=!

Return all rows from table "presidents" with matching rows from tables "parties" (using alias "p") and "comments" (using alias "c").

?join.parties@p=p.IDParty=presidents.IDParty&ljoin.comments@c=c.IDpresident=presidents.IDpresident

Functions

General

Syntax Meaning Example
= Equal (as operator) ?where=IDparty=4
!= Not equal (as operator) ?where=IDparty!=4
> Greater than (as operator) ?where=IDparty>4
< Less than (as operator) ?where=IDparty<4
>= Greater than or equal (as operator) ?where=IDparty>=4
<= Less than or equal (as operator) ?where=IDparty<=4
like(string,pattern) Like ?where=like(lastname,X1)&X1=Har%25
null() Generates Null ?where=if(len(foo)=0,null(),foo)
isnull(value) Is null ?where=isnull(comment)
notnull(value) Not null ?where=notnull(comment)
coalesce(value[,…]) Coalesce ?select=coalesce(firstname,surname,IDpresident)
not(expression) Not ?where=not(IDparty=3)
in(expression,value[,…]) In ?where=in(IDparty,1,4)
| Or (as operator) ?where=partyname=democratic|partyname=whig
or(expr,expr) Or (as function) ?where=or(IDparty=1,IDparty=4)
: And (as operator) ?where=partyname=democratic:IDpresident<30
and(expr,expr) And (as function) ?where=and(active=true(),IDpresident<30)
true() True ?where=active=true()
false() False
str(value) Cast to string ?where=concat(str(age),O)&O=%20old
int(value) Cast to integer ?where=int(age)<=30
double(value) Cast to double ?where=double(sum)>S&S=12.1
num(value) Cast to numeric ?where=num(sum)>S&S=12.1
if(cond,value,value) If ?select=if(in(day,6,7),Free,Work)
nullif(value1,value2) Null if ?select=nullif(name,N)&N=_unnamed_
choose(value,expr,expr) Choose ?select=choose(day,Mon,Thu,Wed)

Mathematical/Operators

Syntax Meaning Example
add(variable[,…]) Add ?select=add(income,income)
sub(variable[,…]) Substract ?select=sub(leftoffice,tookoffice)
mul(variable[,…]) Multiply ?select=mul(income,2)
div(variable[,…]) Divide ?select=div(income,2)
abs(value) Absolute value ?select=abs(number)
exp(value) Exponential value ?select=exp(10)

String

Syntax Meaning Example
lower(string) To lowercase ?select=lower(firstname)
upper(string) To uppercase ?select=upper(firstname)
substr(string,from,count) Substring ?select=substr(firstname,1,1)
md5(variable) MD5 hash value md5(lastname)
concat(field[,…]) Concatenation ?select=concat(firstname,spc,lastname)&spc=%20
trim Remove leading and trailing whitespaces ?select=trim(comment)
ltrim Remove leading whitespaces ?select=ltrim(comment)
rtrim Remove trailing whitespaces ?select=rtrim(comment)
replace(string,search,repl) Replace all occurrences in string of substring search with substring repl ?select=replace(comment,comma,dot)&comma=,&dot=.
lpad Pad the character value left justified to a total width of n character position ?select=lpad(str(id),5,str(0))
rpad Pad the character value right justified to a total width of n character position ?select=rpad(name,15,spc)&spc=%20

Date/Time

Syntax Meaning Example
date(value) Convert argument to date ?select=date(now())
now() Now ?select=now()@current_datetime

Aggregates

Syntax Meaning Example
avg(expression) Average ?select=avg(income)
count(expression) Count ?select=count(lastname)
countif(expression) Count if ?select=countif(IDparty=3)@party3Count
max(expression) Maximum ?select=max(income)
min(expression) Minimum ?select=min(income)
sum(expression) Sum ?select=sum(income)
every(expression) Every ?select=every(income>1000)

Ordering

Syntax Meaning Example
desc(expression) Descending ?order=desc(income)
asc(expression) Descending ?order=asc(lastname)

Special Tables

Zazler uses special tables called _schema and _empty. They may be used for queries and used with different formats but not in joins with database tables.

''_schema table''

This table shows the database structure (tables and fields) in one table and also shows read, write and protect permissions. The data from this table comes either from the database (see "DB" field in the table below) or from Zazler's database configuration file (see "Zazler" field in the table below).

A format called "dashboard" comes bundled with Zazler and is meant to work with _schema table. This combination is the default index page of every database.

The _schema table contains following fields:

Field Meaning DB Zazler
name The name of a table or field. X
tablename The name of the table where a field belongs to. X
rawtype Type as described in database ("table" for tables). X
gentype General type ("str", "int", "float", "bool", "date", "datetime" or "time") that can be used in templates for building content depending on type. X
comment Field or table comment. X
read Boolean value, whether the field is readable or not. Note: if one field is readable, then the whole table is readable. If you want to hide something from a table, refer to "hide" parameter under section "Permissions". X
write Boolean value, whether the field is readable or not. Note: if one field is writable, then the whole table is writable. If you want to hide something from a table, refer to "hide" parameter under section "Permissions". X
protect Boolean value, whether the password is required or not. Note: if a query contains protected field then password is required. X

''_empty table''

This table is an empty rowset. It can be used for templates that only do subqueries.

Templates and bundled formats

Data can be retrieved using created templates or templates that come bundled with Zazler (that can also be called formats).

Bundled formats can be used as models for creating new templates. They can also be called out from other formats (see "format" function under template functions).

Zazler includes the following bundled formats:

Format Description
html Most simple HTML output.
Note: The header can be turned out using opts=-head.
pivot.html Pivoted html table.
min.datatable Table based on datatable js library
json JSON output as an array.
Note: Adding opts=-map will get data as list.
xml XML output that adds XSD.
Note: XSD is a separate format.
tsv Tab separated values.
Notes: Be careful not to forget that TSV doesn't escape content!
The header can be turned out using opts=-head.
csv Comma separated values.
Notes: Be careful not to forget that CSV doesn't escape content!
The header can be turned out using opts=-head.
dashboard Provides an overview of the database that comes from _schema table.
Note: This is the only bundled format that depends on the structure of the table.