User collation routines

Collation routines can be used to order results from a SELECT statement. You can define your own routine and tell QDB to use it by providing the COLLATE keyword in the ORDER BY clause.

These routines are specified in the database configuration object with the option, where tag is the name of the qdb_collation entry describing the collation, and is the name of a DLL object containing your code (this can be an absolute path or a relative path within the library search path).

The code is set up as follows:

static int mysort(void *arg,
                  int l1, const void *s1, int l2, const void *s2)

struct qdb_collation ctag = { 
    "nosort", SQLITE_UTF8, NULL, mysort, NULL

In this case, the tag value for the structure is ctag, the collation name as visible to SQL is nosort, and the C function that implements your collation routine is called mysort() and has a NULL value for its arg argument. For more information on defining SQLite collation sequences, refer to the SQLite docs on sqlite3_create_collation(). Full details on the meaning of each qdb_collation field are given in the subsection that follows.

Note: The ctag tag was used to clarify the example. You would probably use the name nosort here so the tag name matched the SQL name.

In this release, you can define only one collation per database. This collation must be loaded in the configuration object, by specifying a Collation:: entry that lists the name of the struct qdb_collation describing the collation, followed by the library filename.

The qdb_collation struct

The qdb_collation structure has these members:

struct qdb_collation {
  char  *name;
  int   encoding;
  void  *arg;
  int   (*compare)(void *, int, const void *, int, const void *);
  int   (*setup)(void *, const void *, int, char **);
The name used for this function in SQL statements. This is limited to 255 bytes, exclusive of the null-terminator, and it can't contain any special tokens or start with a digit. Any attempt to create a function with an invalid name will result in an SQLITE_ERROR error.
The character encoding of strings passed to your function. Can be one of:
An arbitrary pointer to user data that is passed as the first argument to either the comparison or setup function, each time it's invoked. The function can gain access to this pointer by using the sqlite_user_data() function.
A pointer to your comparison function.
A pointer to a setup function to allow dynamic configuration of sort order at runtime.

The setup function

The setup function takes this form:

int (*setup)(void *arg, const void *data, int nbytes, char **errmsg);

The function parameters are:

void *arg
The context pointer. This is the same as the arg to the compare function, and is copied from the arg field in the qdb_collation structure.
const void *data, int nbytes
The data used to configure the sort. When the routine is invoked at startup, these values are NULL and 0. At runtime, they refer to the data provided to the qdb_collation() function. QDB doesn't interpret the format in any way; the DLL must cooperate with the caller of qdb_collation() to exchange data of a known format.
char **errmsg
A pointer to an error message string that is available to qdb_geterrmsg() and is displayed on failure (actually, at startup, QDB will fail it; at runtime, qdb_collation() will fail and this string will be available through qdb_geterrmsg()).

The function returns either a POSIX errno value or EOK (if it succeeds).

If a collation structure has a non-NULL setup entry, then this function is invoked at startup and passed NULL for data and 0 for nbytes, which gives it a hint to use the default configuration. Then, whenever you call qdb_collation(), the setup function is invoked with new data.

If a collation has no dynamic configuration, it can specify NULL for the setup entry. Note that this entry can't be changed at runtime.