Python API

Serverboards SQL dialect

Serverboards uses ExoSQL as the Database Management System. This allows us to securely access any remote service and manipulate it as SQL data.

There are good tutorials around on the internet. Check them for learning SQL.

The SQL dialect is closer to Postgres. Here is a function reference. For up to date information on ExoSQL check the projects page.

Supported SQL functions

(main article)

Builtins

String operations

format(format_str, args...)

Formats a String using C sprintf-like parameters. Known placeholders are:

  • %s – String
  • %10s – String. String at the right, add spaces until 10 chars. (padleft)
  • %-10s – String. String at the left, add spaces until 10 chars. (padright)
  • %d – Integer number
  • +%d – Integer number, always add sign
  • %02d – Number padded with 0 to fill 2 chars
  • %f – Float
  • %.2f – Float with precision
  • +%f – Float, always add sign. 2 chars of precision.
  • %k – Metric System suffix: k, M, G, T. Try to show most relevant information.
  • %.2k – Metric System suffix with precision
  • %,2k – Metric System, using . to separate thousands and , for decimals. Follow Spanish numbering system.

lower(str)

Lower case a full string.

join(str, sep=",")

Joins all elements from a list into a string, using the given separator.

join([1,2,3,4], "/")
"1/2/3/4"

split(str, sep=[", ", ",", " "])

Splits a string into a list using the given separator.

split("1, 2,3 4")
["1", "2", "3", "4"]

substr(str, start, end=10000) / substr(str, end)

Extracts a substring from the first argument.

Can use negative indexes to start to count from the end.

substr('#test#', 1, -1)
"test"

to_string(arg)

Converts the given argument into a string.

to_string(1)
"1"

upper(str)

Upper cases a full string

Date time functions

datediff(start, end, unit \\ "days") / datediff(range, unit \\ "days")

Returns how many unit has passed since start to end.

now()

Returns current datetime.

strftime(datetime, format_str)

Convert a datetime to a string. Can be used also to extract some parts of a date, as the day, year and so on.

Normally strftime can be used directly with a string or an integer as it does the conversion to datetime implicitly.

It is based on Timex formatting.

Most common markers:

  • %Y – Year four digits
  • %y – Year two digits
  • %m – Month number
  • %d – Day of month
  • %H – Hour
  • %M – Minute
  • %S – Second
  • %V – ISO Week (01-53)
  • %s – Unix time
  • %F – ISO year: yyyy-mm-dd
  • %H – Time: HH:MM:SS

to_datetime(str | int, mod \\ nil) / to_datetime(str | int, timezone)

Converts the given string or integer to a date.

The string must be in ISO8859 sub string format:

  • YYYY-mm-dd
  • YYYY-mm-ddTHH:MM
  • YYYY-mm-dd HH:MM
  • YYYY-mm-ddTHH:MM:SS
  • YYYY-mm-dd HH:MM:SS
  • or an Unix epoch integer.

This is called implicitly on strftime calls, and normally is not needed.

Last argument can be a modifier to add or subtract time, or a timezone shifter.

Datetime modifier

mod MUST start with + or -

If mod is given it is a duration modifier as defined by ISO8601, with the following changes:

  • Must start with + or -
  • A subsequent P is optional

For example:

  • Subtract one month to_datetime(NOW(), "-1M")
  • Add 30 minutes: to_datetime(NOW(), "+T30M")
  • One year and a half and 6 minutes ago: to_datetime(NOW(), "-1Y1MT6M")
Datetime timezone change

If a timezone is provided the datetime will be changed from the current timezone to the provided one, making the appropiate changes to the datetime.

For example, to_datetime(0, 'Europe/Madrid') gives the Madrid time for unix epoch 0: 1970-01-01 01:00:00+01:00 CET Europe/Madrid.

Check https://en.wikipedia.org/wiki/List_of_tz_database_time_zones for the timezone names.

Boolean functions

bool(arg)

Converts to boolean. Equivalent to NOT NOT arg

Aggregation functions

avg(expr)

Calculates the average of the calculated expression on the group rows. Equivalent to sum(expr) / count(expr).

If no rows, returns NULL.

count(*)

Counts the number of rows of the aggregates expression.

max(expr)

Returns the maximum value of the given expression for the group.

min

Returns the minimum value of the given expression for the group.

sum(expr)

For each of the grouped rows, calculates the expression and returns the sum. If there are no rows, returns 0.

Miscellaneous functions

coalesce(a, b, ...)

Returns the first not NULL value.

generate_series(end) / generate_series(start, end, step=0)

This function generates a virtual table with one column and on each row a value of the series.

Can be reverse with a larger start than end and negative step.

It can be used to for example fill all holes in a temporal serie:

SELECT month, SUM(value)
  FROM generate_series(12) AS month
LEFT JOIN purchases
  ON strftime(purchases.datetime, "%m") == month
GROUP BY month

This will return 0 for empty months on the purchases table.

greatest(a, b[, c, d, e])

Get the greatest value of all the given ones.

It’s similar to max in other languages, but in SQL can not use max as it is an aggregation function with different semantics.

jp(json, selector)

Does JSON Pointer selection:

  • Use / to navigate through the object keys or array indexes.
  • If no data found, return NULL

least(a, b[, c, d, e])

Get the lowest value of all the given ones.

It’s similar to min in other languages, but in SQL can not use min as it is an aggregation function with different semantics.

lower(range)

Get the lower bound of a range.

nullif(a,b)

Returns NULL if A and B are equal. Else returns A.

This is used for example to set a default value:

SELECT coalesce(nullif(name, ''), 'John Doe') FROM users

random()

Return a random float between 0 and 1.

randint(max) / RANDINT(min, max)

Returns a random integer between min and max.

range(start, end)

Returns a halt open interval [start, end) that later can be used to get intersection * or membership.

The range includes the start but not the end (all X | start >= X < end). This is important for later datediff and similar.

For example, the following query will check if NOW() is in the intersection of some range given by parameters $start and $end and the range set by the columns start and end.

SELECT NOW() IN (range(start, end) * range($start, $end))
FROM dates

It works for both dates, texts and numbers.

Ranges can be decomposed with lower(range) and upper(range).

regex(str, regex, query \\ nil)

Performs a regex search on the string.

It uses elixir regex, so use it as reference.

Can use groups and named groups for matching and it will return a list of a map with the result. It can optionally use directly JSON pointer queries. See jp function.

If matches the result will be “trueish” (or “falsy” if doesn’t) so can be used as a boolean.

round(number, precision=0)

Returns the number rounded to the given precission. May be convert to integer if precission is 0.

upper(range)

Get the upper bound of a range.

urlparse(string, sel="")

Parses an URL and returns a JSON.

If selector is given it does the equivalent of callong jp with that selector.

width_bucket(n, start, end, buckets)

Given a n value it is assigned a bucket between 0 and buckets, that correspond to the full width between start and end.

If a value is out of bounds it is set either to 0 or to buckets - 1.

This helper eases the generation of histograms.

For example an histogram of prices:

SELECT n, SUM(price)
  FROM (SELECT width_bucket(price, 0, 200, 10) AS n, price
          FROM products)
  GROUP BY n

or more complete, with filling zeroes:

SELECT m, SUM(price)
  FROM generate_series(10) AS m
  LEFT JOIN (
        SELECT width_bucket(price, 0, 200, 10) AS n, price
          FROM products
    )
    ON n = m
 GROUP BY m