Using eXtremeSQL User Defined Functions in C++

eXtremeSQL supports User Defined Functions (UDF) that can optimize the performance of database operations. UDFs are written in C++ and compiled into dynamic load libraries which can then be loaded at runtime by C++ applications.

For example, the following code snippet defines then registers a UDF mod():

     
    static Value* mod(Value* a, Value* b) 
    {
        if (a->isNull() || b->isNull()) 
        {
            return NULL;
        }
        return new IntValue(a->intValue() % b->intValue());
    }
     
    // Below, f1 is an instance of the SqlFunctionDeclaration class.  The
    // constructor links this declaration of a UDF named “mod” to the list
    // of all UDFs maintained internally by eXtremeSQL:
     
    static SqlFunctionDeclaration f1(
        tpInt,      // tpInt is the return value of the UDF
        "mod",      // the name of the function as we’ll use it in a query
        (void*)mod, // the function pointer
        2          // the number of arguments to the UDF
    );
     

Once registered, the UDF can then be called from within this application in a normal SQL select statement. For example, the following statement selects records where the value of the column named code is divisible by 3:

 
    QueryResult result(engine.executeQuery("select * from T where mod(code,3) = 0;"));
     

Loadable UDFs

UDFs can also be compiled into a dynamic load library. Then these UDFs can be loaded by the SQL engine from any embedded eXtremeSQL application. To illustrate, consider the following code snippet extracted from SDK sample source file samples/xsql/data_loaders/CME/modcme/modcme.cpp, which creates UDF load_cme_trades:

 
    extern "C" {
        CME_EXPORTS McoSql::Value *load_cme_trades(McoSql::Runtime *runtime, McoSql::Vector<McoSql::Value> *args)
        {
            return load_security_rec<securityTrades, int, Trade> (runtime->engine, runtime->trans, args, 11);
        }
    }
     

This UDF calls function load_security_rec() that is performs a relatively complex set of database operations:

 
    template <class secAcc, class sKey, class secRec>
    McoSql::Value *load_security_rec(McoSql::SqlEngine *engine, 
        McoSql::Transaction *currTrans,  McoSql::Vector<McoSql::Value> *args, int ncolumns)
    {
        McoSql::Value *input = args->at(0);
        McoSql::Value *book = args->at(1);
        std::map<sKey, secAcc *> securities;
 
        if (input->type() != McoSql::tpString) 
        {
            throw McoSql::InvalidArgument("input - string sxpected");
        }
 
        char *fname = input->stringValue(currTrans->allocator)->cstr();
         
        if (book->type() != McoSql::tpString) 
        {
            throw McoSql::InvalidArgument("book - string sxpected");
        }
        char *Book = book->stringValue(currTrans->allocator)->cstr();
 
        CsvReader r(currTrans->allocator, fname, ',', 2, ncolumns);
        secRec s;
 
        while (r.next(&s)) 
        {
            secAcc *st = NULL;
            sKey key = s.key();
            typename std::map<sKey, secAcc*>::iterator f = securities.find(key);
            if (f == securities.end()) 
            {
                st = new secAcc(engine, currTrans, Book, key, BATCH_SIZE);
                st->findRecord();
                securities[key] = st;
            } 
            else 
            {
                st = f->second;
            }
             
            st->apply(&s);
        }
         
        typename std::map<sKey, secAcc *>::iterator it;
        for (it = securities.begin(); it != securities.end(); it++) 
        {
            it->second->store();
            delete it->second;
        }
 
        return McoSql::IntValue::create(currTrans->allocator, securities.size());
    }
     

In order to build this file into a dynamic load library, a number of linker parameters must be specified like the following:

     
    $(QUIET_PREFIX)$(LDCXX) $(SOLDFLAGS) $(LDOUTPUTF) $(MCO_BIN_SO)/libmcocme$(MCO_BUILDRT_SFX)$(MCO_CFG_SUFFIX_SO) $(OBJ_L) $(QUIET_SUFFIX)
     

Note that this makefile snippet uses several macros from file include/header.mak in the eXtremeDB SDK build system. (For further details please see the full makefile samples/xsql/data_loaders/CME/modcme/makefile.)

Loading and Executing UDFs

To load UDFs from external dynamic load libraries, an embedded eXtremeSQL application calls the SQL DDL command create function specifying the file and UDF name. For example:

 
    create function load_trades(fname string, book string) returns integer as 'mcocme', 'load_cme_trades';
     

The create function command can be included in the sql_statements section of the xSQL configuration file to cause xSQL to create the UDF and load its implementation from the dynamic load library. Or UDFs can be loaded when the SQL engine is opened in a C++ application by setting the SqlOptimizerParamter class parameter preloadUDF to true to enable the preload functions feature. Then the optimizer parameters are added to the McoSqlOpenParameters passed the McoSqlEngine method open(). For example:

 
    McoSqlEngine engine;
    McoSqlOpenParameters params;
    SqlOptimizerParameters oParams;
    oParams.preloadUDF = true;
    ...
    params.optimizerParams = oParams;
    engine.open(params);
    ...
     

Once loaded by the SQL engine, any eXtremeSQL application can invoke the UDF through an SQL select statement. For example, the following Python snippet executes the load_trades UDF defined above:

 
    is_shm = False
    is_debug = True
    is_disk = True
    tmgr='mursiw'
     
    exdb.init_runtime(is_disk, tmgr, is_shm, is_debug)
    conn = exdb.connect("localhost:5001")
     
    cursor = conn.cursor()
     
    sql =  '''SELECT load_trades('%s', '%s')''' % (fname, book)
    cursor.execute(sql)
    cursor.fetchall()
    ...