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 returns`java.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.

Blog

Functions supported by the Aerospike SQL driver

Alexander Radzin