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.
Top comments (1)
Thank you very much for your comment. I have removed the duplicate line. I thought about
current_timestampand decided not to implement it because I provide functionnow(). However it is not a problem to providecurrent_timestampas well. Will be done soon.coalesce()is indeed very useful. I will implement it too.Thanks again.