sqlite3 で自作関数

/*
  // to compile with vc
  CL /EHsc /GR /MD /c sqli.cpp
  link /DLL sqli.obj
  mt -manifest sqli.dll.manifest -outputresource:sqli.dll;2
*/

/*
  sqlite3 test.db
  > create table test ( id integer primary key, name text );
  > select load_extension('sqli.dll');
  > select test_insert( 'hoge' );
  > select * from test;
*/

#include <sqlite3ext.h>

extern "C" {

  SQLITE_EXTENSION_INIT1

  static void test_insert_func( sqlite3_context* context, int argc, sqlite3_value** argv ) {
    const char sql[] = "insert or replace into test (name) values (?)";
    sqlite3* db = (sqlite3*)sqlite3_user_data( context );
    sqlite3_stmt* statement;
    int err = sqlite3_prepare_v2( db, sql, -1, &statement, 0 );
    const char* p = (const char*)sqlite3_value_text( argv[0] );
    size_t len    = sqlite3_value_bytes( argv[0] );
    sqlite3_bind_text( statement, 1, p, len, SQLITE_STATIC );
    while( err = sqlite3_step( statement ), err == SQLITE_BUSY ) {}
    sqlite3_finalize( statement );
  }

  __declspec(dllexport) int sqlite3_extension_init(sqlite3 *db, char **errmsg, const sqlite3_api_routines *api) {
    SQLITE_EXTENSION_INIT2(api);
    sqlite3_create_function(db, "test_insert", 1, SQLITE_UTF8, (void*)db, test_insert_func, 0, 0);
    return 0;
  }


}

という訳で、sqlite3 でも、ストアド・プロシジャからトリガまで自由自在やね。やったね！