Re: [sqlite] Implementing a CREATE_FUNCTION function

[Igor Tandetnik]

Dan Bishop wrote:
 It's convenient to be able to define new functions in C.  But sometimes,
 it would be *more* convenient to be able to define new functions in
 SQL.  This could be done by registering a CREATE_FUNCTION() function;
 then you could write something like:
 
 SELECT CREATE_FUNCTION('LEFT', 2, 'SUBSTR(?1, 1, ?2)');
 
 My first idea for implementing CREATE_FUNCTION is:
 
 1. Create (if not exists) a table with columns for the function name,
 number of arguments, and SQL expression.
 2. Add the new function to the table.
 3. Call sqlite3_create_function to register the new function.
 
 C doesn't have the ability to create functions at runtime, so the xFunc
 parameter would refer to a common global function, which would:
 
 1. Look up the SQL expression corresponding to the SQL function name.
 2. Evaluate the expression.
 
 But how do I get the SQL function name from within the xFunc function?
 Can I get it from the sqlite3_context object, or do I have to use
 sqlite3_user_data()?

You have to use sqlite3_user_data. In fact, it's not clear why you would want 
to store anything in the table. Just allocate some structure describing your 
new function (containing the same information that you planned to store in 
that table), and pass its address to sqlite3_create_function, to be picked up 
with sqlite3_user_data.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Implementing a CREATE_FUNCTION function

[Dan Bishop]

It's convenient to be able to define new functions in C.  But sometimes, 
it would be *more* convenient to be able to define new functions in 
SQL.  This could be done by registering a CREATE_FUNCTION() function; 
then you could write something like:

SELECT CREATE_FUNCTION('LEFT', 2, 'SUBSTR(?1, 1, ?2)');

My first idea for implementing CREATE_FUNCTION is:

1. Create (if not exists) a table with columns for the function name, 
number of arguments, and SQL expression.
2. Add the new function to the table.
3. Call sqlite3_create_function to register the new function.

C doesn't have the ability to create functions at runtime, so the xFunc 
parameter would refer to a common global function, which would:

1. Look up the SQL expression corresponding to the SQL function name.
2. Evaluate the expression.

But how do I get the SQL function name from within the xFunc function?  
Can I get it from the sqlite3_context object, or do I have to use 
sqlite3_user_data()?

Or is there a better approach?

As a first step, I've written an EVAL() function, which supports 
parameter binding.

// EVAL(expr [, param]*)
void x_sqlite_eval(sqlite3_context* pContext,
   int argc,
   sqlite3_value* argv[])
{
   sqlite3* pDB = sqlite3_context_db_handle(pContext);
   sqlite3_stmt*pStmt   = NULL;
   const unsigned char* expr= NULL;
   char*sql = NULL;
   size_t   len;
   int  err;
   int  index;

   if (argc == 0)
   {
  sqlite3_result_null(pContext);
  return;
   }

   expr = sqlite3_value_text(argv[0]);
   len = sqlite3_value_bytes(argv[0]);

   // Build the SQL statement SELECT (expr)

   sql = malloc(len + 10); 
 
   if (sql == NULL)
   {
  sqlite3_result_error_nomem(pContext);
  return;
   }

   memcpy(sql, SELECT (, 8);
   memcpy(sql + 8, expr, len);
   sql[8 + len] = ')';

   err = sqlite3_prepare_v2(pDB, sql, len + 9, pStmt, NULL);

   if (err != SQLITE_OK)
   {
  sqlite3_result_error_code(pContext, err);
  goto EXIT;
   }

   // bind parameters
   for (index = 1; index  argc; index++)
   {
  err = sqlite3_bind_value(pStmt, index, argv[index]);

  if (err != SQLITE_OK)
  {
 sqlite3_result_error_code(pContext, err);
 goto EXIT;
  }
   }

   // execute the statement
   err = sqlite3_step(pStmt);

   if (err != SQLITE_ROW)
   {
  sqlite3_result_error_code(pContext, err);
  goto EXIT;
   }

   sqlite3_result_value(pContext, sqlite3_column_value(pStmt, 0));

EXIT:
   sqlite3_finalize(pStmt);
}

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users