[rtg] Oracle driver

[Brian T. O'Neill]

Any idea on performance difference between Oracle and MySQL for this
type of data? We've been debating trying Oracle but haven't had time to
write the driver. Having a corporate license to run as many instances of
Oracle at no additional cost is a nice plus to giving it a shot for on
our side.

Brian

Quoting Leech, Jonathan (jleech at virtela.net) from  :
> Here is the working code for an Oracle database, using OCI.  It creates a connection pool with the same number of connections as RTG threads, but can be easily modified to use fewer connections.
> 
> librtgoci.c
> ----------------------
> /*
>  * RTG Oracle OCI database driver
>  */
> 
> #include "rtg.h"
> #include <oci.h>
> #include <sys/param.h>
> 
> // thread-specific global variable
> pthread_key_t key;
> 
> // only call the thread-specific variable setup once 
> pthread_once_t once = PTHREAD_ONCE_INIT;
> 
> // holds the config info for all the threads
> config_t *set;
> 
> // Oracle handles
> static OCIError   *errhp;
> static OCIEnv     *envhp;
> static OCICPool   *poolhp;
> 
> // stores the Oracle connection pool name and length
> static OraText *poolName;
> static sb4 poolNameLen;
> 
> /* Max,Min, and increment connections */
> static ub4 conMin = 1;
> static ub4 conIncr = 1;
> 
> // log any Oracle errors
> void checkerr(errhp, status)
> OCIError *errhp;
> sword status;
> {
>     text errbuf[512];
>     sb4 errcode = 0;
>  
>     switch (status)
>     {
>     case OCI_SUCCESS:
>       break;
>     case OCI_SUCCESS_WITH_INFO:
>       debug(LOW, "Error - OCI_SUCCESS_WITH_INFO\n");
>       break;
>     case OCI_NEED_DATA:
>       debug(LOW, "Error - OCI_NEED_DATA\n");
>       break;
>     case OCI_NO_DATA:
>       debug(LOW, "Error - OCI_NODATA\n");
>       break;
>     case OCI_ERROR:
>       (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
>                           errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
>       debug(LOW, "Error - %.*s\n", 512, errbuf);
>       break;
>     case OCI_INVALID_HANDLE:
>       debug(LOW, "Error - OCI_INVALID_HANDLE\n");
>       break;
>     case OCI_STILL_EXECUTING:
>       debug(LOW, "Error - OCI_STILL_EXECUTE\n");
>       break;
>     case OCI_CONTINUE:
>       debug(LOW, "Error - OCI_CONTINUE\n");
>       break;
>     default:
>       break;
>     }
> }
> 
> /* variable cleanup function */
> void killkey(void *target) {
>    free(target);
>    
>    // destroy the connection pool
>    checkerr(errhp, (sword)OCIConnectionPoolDestroy(poolhp, errhp, OCI_DEFAULT));
>    checkerr(errhp, OCIHandleFree((dvoid *)poolhp, OCI_HTYPE_CPOOL));
>    checkerr(errhp, OCIHandleFree((dvoid *)errhp, OCI_HTYPE_ERROR));
> }
> 
> /* called when library loads */
> void __attribute__ ((constructor)) dl_init(void) {
> }
> 
> /* this gets called once */
> void my_makekey() {
>    /* this shouldn't fail, and we're too early on to report errors */
>    pthread_key_create(&key, killkey);
> 
>    // create the connection pool
>    OCIEnvCreate (&envhp, OCI_THREADED, (dvoid *)0, NULL, NULL, NULL, 0, (dvoid *)0);
> 
>    (void)OCIHandleAlloc((dvoid *)envhp, (dvoid**)&errhp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **) 0);
> 
>    (void)OCIHandleAlloc((dvoid *) envhp, (dvoid **) &poolhp, OCI_HTYPE_CPOOL, (size_t) 0, (dvoid **) 0);
> 
>    checkerr(errhp, OCIConnectionPoolCreate(envhp,
>       errhp, poolhp, &poolName, &poolNameLen,
>       set->dbdb, (sb4)strlen((const signed char *)set->dbdb),
>       conMin, set->threads, conIncr,
>       set->dbuser, (sb4)strlen((const signed char *)set->dbuser),
>       set->dbpass, (sb4)strlen((const signed char *)set->dbpass),
>       OCI_DEFAULT)
>    );
>    OCIThreadProcessInit();
>    checkerr(errhp, OCIThreadInit(envhp, errhp));
> }
> 
> int __db_test() {
>    return TRUE;
> }
> 
> /*
>  * check the status of the connection
>  * we don't try and reconnect because this is sometimes used to confirm a disconnect
>  */
> int __db_status() {
>    sword lstat = OCI_SUCCESS;
>    char buf[100];
> 
>    OCISvcCtx *svchp = pthread_getspecific(key);
>    checkerr(errhp, lstat = OCIServerVersion(svchp, errhp, buf, 100, OCI_HTYPE_SVCCTX));
>    if (lstat == OCI_SUCCESS) {
>       return TRUE;
>    } else {
>       return FALSE;
>    }
> }
> 
> int __db_connect(config_t *config) {
>    // initialize the key and connection pool once
>    set = config;
>    pthread_once(&once, my_makekey);
> 
>    // connect to Oracle
>    OCISvcCtx *svchp = (OCISvcCtx *)0;
>    checkerr(errhp, OCILogon2(envhp, errhp, &svchp,
>       (CONST OraText *)config->dbuser, (ub4)strlen((const signed char *)config->dbuser),
>       (CONST OraText *)config->dbpass, (ub4)strlen((const signed char *)config->dbpass),
>       (CONST OraText *)poolName, (ub4)poolNameLen, OCI_CPOOL)
>    );
> 
>    // store the Oracle session in thread-specific storage
>    pthread_setspecific(key, svchp);
> 
>    return __db_status();
> }
> 
> int __db_disconnect() {
>    OCISvcCtx *svchp = pthread_getspecific(key);
>    checkerr(errhp, OCILogoff((dvoid *) svchp, errhp));
>    return TRUE;
> }
> 
> int __db_insert(char *table, int iid, unsigned long long insert_val, double insert_rate) {
>    char *query;
>    OCISvcCtx *svchp = pthread_getspecific(key);
>    OCIStmt *stmthp = (OCIStmt *)0;
>    sword lstat;
> 
>    asprintf(&query, "INSERT INTO %s (id,dtime,counter) VALUES (%i,sysdate,%llu)", table, iid, insert_val);
> 
>    debug(HIGH, "Query = \"%s\"\n", query);
> 
>    OCIHandleAlloc(envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);
> 
>    checkerr(errhp, lstat = OCIStmtPrepare (stmthp, errhp, (CONST OraText *)query, (ub4)strlen((const signed char *)query), OCI_NTV_SYNTAX, OCI_DEFAULT));
> 
>    checkerr(errhp, lstat = OCIStmtExecute (svchp, stmthp, errhp, (ub4)1, (ub4)0, (OCISnapshot *)0, (OCISnapshot *)0, OCI_DEFAULT));
> 
>    checkerr(errhp, lstat = OCITransCommit(svchp, errhp, (ub4)0));
> 
>    checkerr(errhp, lstat = OCIHandleFree((dvoid *)stmthp, OCI_HTYPE_STMT));
> 
>    free(query);
> 
>    if (lstat == OCI_SUCCESS) {
>       return TRUE;
>    } else {
>       return FALSE;
>    }
> }

> _______________________________________________
> RTG mailing list
> RTG at fireflynetworks.net
> http://fireflynetworks.net/mailman/listinfo/rtg


-- 
btoneill at misplaced.net

****************************************************************************
UNIX is simple and coherent, but it takes a genius (or at any rate a 
programmer) to understand and appreciate the simplicity."  - Dennis Ritchie
****************************************************************************