Tutorial

UQI - Queries and table scans

Starting with version 2.2.0, upscaledb offers a built-in query interface with a query language that looks a bit like SQL SELECT statements. These queries can be used to perform full-table scans over a single database and apply aggregation functions and filter predicates. Functions and predicates can be extended through user-supplied plugins. Joins are not supported.

In order to use this interface, C/C++ developers have to include the header file ups/upscaledb_uqi.h in their sources.

To run a query, call either uqi_select or uqi_select_range.

UPS_EXPORT ups_status_t UPS_CALLCONV
uqi_select(ups_env_t *env, const char *query, uqi_result_t **result);

uqi_select_range can be used to specify a begin cursor, and optionally also an end cursor (if the end cursor is null then the query will run till the end of the database). The begin cursor is advanced to the first position after the end cursor; you can use this to scan the database "piece by piece".

UPS_EXPORT ups_status_t UPS_CALLCONV
uqi_select_range(ups_env_t *env, const char *query, ups_cursor_t *begin,
                            const ups_cursor_t *end, uqi_result_t **result);

Here are a few examples for UQI queries.

Calculates the sum of all keys from database 1: SUM($key) from database 1.

Calculates the sum of all records from database 1: SUM($record) from database 1. Calculates the maximum of all records: MAX($record) from database 1.

Calculates the top 20 of all records: TOP($record) from database 1 LIMIT 20. (Note that the limit clause is currently only allowed with TOP/BOTTOM queries! )

This blog post has more information about the queries, and the sample "uqi1.c" shows how to use the UQI API. Visit the samples page to download the file.

User-supplied plugins

The query interface can be extended through plugins. These plugins can also be loaded from an external library (such as a .so file on unix or a .DLL on Windows). A plugin is either for aggregating or for filtering the data. Aggregating plugins are specified in the beginning of the query (i.e. MAX, SUM), filters are specified at the WHERE clause:

MAX($record) FROM DATABASE 2 WHERE is_even($key)

The sample "uqi2.c" creates an aggregation plugin and a filter plugin, and shows how to use them in a query. Visit the samples page to download the file.