The Problem

When writing extensions (especially virtual tables) to sqlite3, it is sometimes desirable to call a custom function to pass values or configure settings in your custom code. the first idea was some kind of custom pragma

, like this:

pragma myCustomSetting = true

Unfortunately, custom pragmas aren't available for virtual table modules. They are vailable for virtual files system (VFS) modules, but that is a bigger step ahead (explaining this would be a separate post).

But there is a workaround:

Right now (SQLite version 3.8.x) the workaround is to define a custom function and use a SELECT statement to perform it. A good return value could be a bool value with 1 for TRUE and 0 for FALSE if the function succeeds. The syntax would be:

SELECT setcustomsetting("mycustomsetting", 1);

Of course this seems to contradict the meaning of a function a bit which actually should just calculate and return a field value, but with a proper name given and arguments it might be okay (in terms of readability). Still, it bothers me a bit that it is not obvious that this function does something outside the database. A SELECT statement shouldn't actually change something.

One could consider the result as a return value of the function.

In the example code the function returns 0 or 1, but you could also return a string with a message or push an error message via the C API, where the query execution will not return SQLITE_OK.

Such a function could also write or read a real file or connect to a completely different entity for storing/retrieving settings.

Example code

/*
  example code to register custom function
*/

  static bool myCustomSetting = false;

  // setCustomSettings is being registered in the sqlite3 database Connection
  // and expects 2 arguments, a string and an integer value
  void setCustomSettings(sqlite3_context* context, int i, sqlite3_value ** value)
  {
    // expect a string and a int value    
    if ((sqlite3_value_type(value[0]) == SQLITE_TEXT)
      && (sqlite3_value_type(value[1]) == SQLITE_INTEGER))
    {
      // retrieve the settings name and value
      const char* settingsname = 
          (const char*)
          sqlite3_value_text(value[0]);
      int newvalue = 
          sqlite3_value_int(value[1]);

      // do we know the Setting

      if ( !strcmp( settingsname, \"mycustomsetting\") )
      {
        // set the actual value:
        myCustomSetting = (newvalue != 0) ? true : false;

        // return 1 to indicate it worked
        sqlite3_result_int(context, 1);
        return;
      }
    }
    // return false to indicate it didn\'t work out
    sqlite3_result_int(context, 0); // return false
  }

  void registerfunction(sqlite3* db)
  {
    // see https://www.sqlite.org/c3ref/create_function.html

    sqlite3_create_function_v2(
       db,
       \"setcustomsetting\",
       2, 
       SQLITE_UTF8 | SQLITE_DETERMINISTIC,
        nullptr, 
       setCustomSettings,
       nullptr,
       nullptr,
       nullptr);
  }