Proposal: ability to define sqlite3_create_function on Open

[le0pard]

Idea was taken from https://www.sqlite.org/src/file/ext/misc/memvfs.c. Sometimes will be cool not only define VFS in extension, but also provide additional functions from this extension. Examples for use:

• You can use some custom file storage file format, but provide functions like SELECT CUSTOM_VFS_BACKUP(filename)
• You can have methods to do optimizations or change settings for open database, like SELECT CUSTOM_VFS_BLOOM_FALSE_POSITIVE(0.02)

For such functions need access to sqlite3_vfs or sqlite3_file objects. As I can see, based on doc "The xFileControl() method is a generic interface that allows custom VFS implementations to directly control an open file using the sqlite3_file_control() interface". Here how it looks like in memfs:

static int memFileControl(sqlite3_file *pFile, int op, void *pArg){
  MemFile *p = (MemFile *)pFile;
  int rc = SQLITE_NOTFOUND;
  if( op==SQLITE_FCNTL_VFSNAME ){
    *(char**)pArg = sqlite3_mprintf("mem(%p,%lld)", p->aData, p->sz);
    rc = SQLITE_OK;
  }
  return rc;
}

After this defined custom functions can get access to SQLITE_FCNTL_VFSNAME by sqlite3_file_control method:

static void memvfsToFileFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  MemFile *p = 0;
  FILE *out;
  int rc;
  sqlite3 *db = sqlite3_context_db_handle(context);
  sqlite3_vfs *pVfs = 0;
  const char *zSchema = (const char*)sqlite3_value_text(argv[0]);
  const char *zFilename = (const char*)sqlite3_value_text(argv[1]);

  if( zFilename==0 ) return;
  out = fopen(zFilename, "wb");
  if( out==0 ) return;
  rc = sqlite3_file_control(db, zSchema, SQLITE_FCNTL_VFS_POINTER, &pVfs);
  if( rc || pVfs==0 ) return;
  if( strcmp(pVfs->zName,"memvfs")!=0 ) return;
  rc = sqlite3_file_control(db, zSchema, SQLITE_FCNTL_FILE_POINTER, &p);
  if( rc ) return;
  fwrite(p->aData, 1, (size_t)p->sz, out);
  fclose(out);
}

I know exists project https://github.com/riyaz-ali/sqlite, which allow to build custom functions, collations or virtual tables, but looks like it will not help here, because need deep integrations with internal pointers to objects in VFS.

My knowledge in C is limited, what is why I did not created PR and only make proposal for now. Maybe it will be useful for future of a project.