Functions supported by the Aerospike SQL driver

Preface

This post continues series of articles that present the Aerosplike SQL driver

Introduction

There are two types of SQL functions, aggregate functions, and scalar(non-aggregate) functions. Aggregate functions operate on many records and produce a summary, works with GROUP BY whereas non-aggregate functions operate on each record independently.[1]

This statement is correct for the most of relational databases that can store only simple type in one cell. Aerospike supports built-in lists and maps. Aerospike JDBC driver provides several functions that operate these types. Since lists and maps are not scalars we can define 3 types of functions provided by the Aerospike JDBC driver:

• scalar functions (that operate scalar types into one record)
• collection functions (that operate lists and maps into one record)
• aggregate functions that operate many records

Built-in scalar and collection functions

Name	Description
len(x), length(x)	returns length of given string, list, map
ascii(c)	returns ASCII code of given character
char(c)	returns char corresponding to the given ASCII code
locate(subStr, str, [offset=1])	returns position of subStr into str starting from offset (that is 1 if omitted)
instr(subStr, str)	returns position of subStr into str
trim(s)	trims string (removes spaces from both sides)
ltrim(s)	removes leading spaces from the string
rtrim(s)	removes trailing spaces from the string
strcmp(s1, s2)	compares given strings
left(s, n)	returns substring of length n starting from the beginning of the given string
lower(s), lcase(s)	converts a string to lower-case
upper(s), ucase(s)	converts a string to upper-case
str(x)	returns string representation of given value (like toString() in java)
space(n)	returns string that contains n spcaces
reverse(s)	reverses given string
to_base64(bytes)	generates Base64 representation of given byte array
from_base64(str)	returns byte array from given Base64
substr(str, from, to)	returns substring of given string
concat(str1, str2, ...)	concatenates given strings
concat_ws(separator, str1, str2, ...)	concatenates given strings using separator
date([x])	creates java.util.Date from either string or epoch. If argument is not provided this function returns date that represents current time.
calendar([x])	similar to date([x]) but returnsjava.util.Calendar`
now()	returns epoch (ms)
year([x]), month([x]), dayofmonth([x]), hour([x]), minute([x]) ,second([x]), millisecond([x])	return year, month, etc respectively. Optional argument may be either formatted date or epoch in milliseconds. If it is not provided current time is used.
map(s), list(s), array(s)	create map, list and array respectively from their string representation

Built-in aggregate functions

The aggregate functions allow you to perform a calculation on a set of values to return a single scalar value. The following functions are supported:

• count()
• sum()
• max()
• min()
• avg()
• sumsqs() - sum of squares of values

Distinct is considered a modifier but syntactically looks like are function and is supported as well.

For performance and better memory consumption reasons aggregate functions are implemented in Lua and run on server side (into Aerospike DB).

Project home

The project is available in GitHub.

What's next

Next article of this series will explain how to add custom function to the driver.

Comments

[Alexander Radzin]

Thank you very much for your comment. I have removed the duplicate line. I thought about current_timestamp and decided not to implement it because I provide function now(). However it is not a problem to provide current_timestamp as well. Will be done soon.

coalesce() is indeed very useful. I will implement it too.

Thanks again.