boolean OCI_API OCI_DateSetDate(OCI_Library *pOCILib, OCI_Date *date, int year, int month, int day)
{
OCI_CHECK_PTR(pOCILib, OCI_IPC_DATE, date, FALSE);
OCIDateSetDate(date->handle, (sb2) year, (ub1) month, (ub1) day);
OCI_RESULT(pOCILib, TRUE);
return TRUE;
}
/* convert an epoch time to an Oracle date */
void epoch_to_ocidate(double e, OCIDate* ocidate) {
time_t t = (time_t)e;
struct tm* ut = localtime(&t); /* convert to a Unix time */
#ifdef DEBUG
char dbuf[256]; snprintf(dbuf, 255, "epoch_to_ocidate: epoch=%f year=%d month=%d day=%d", e, ut->tm_year + 1900, ut->tm_mon + 1, ut->tm_mday); debug(dbuf);
#endif
OCIDateSetDate(ocidate, ut->tm_year + 1900, ut->tm_mon + 1, ut->tm_mday);
OCIDateSetTime(ocidate, ut->tm_hour + 1, ut->tm_min, ut->tm_sec + 1);
}
boolean OCI_API OCI_DateSetDate
(
OCI_Date *date,
int year,
int month,
int day
)
{
OCI_LIB_CALL_ENTER(boolean, FALSE)
OCI_CHECK_PTR(OCI_IPC_DATE, date)
OCIDateSetDate(date->handle, (sb2) year, (ub1) month, (ub1) day);
call_retval = call_status = TRUE;
OCI_LIB_CALL_EXIT()
}
/*
* Called after val2str to realy binding
*/
int db_oracle_val2bind(bmap_t* _m, const db_val_t* _v, OCIDate* _o)
{
if (VAL_NULL(_v)) {
_m->addr = NULL;
_m->size = 0;
_m->type = SQLT_NON;
return 0;
}
switch (VAL_TYPE(_v)) {
case DB1_INT:
_m->addr = (int*)&VAL_INT(_v);
_m->size = sizeof(VAL_INT(_v));
_m->type = SQLT_INT;
break;
case DB1_BIGINT:
LM_ERR("BIGINT not supported");
return -1;
case DB1_BITMAP:
_m->addr = (unsigned*)&VAL_BITMAP(_v);
_m->size = sizeof(VAL_BITMAP(_v));
_m->type = SQLT_UIN;
break;
case DB1_DOUBLE:
_m->addr = (double*)&VAL_DOUBLE(_v);
_m->size = sizeof(VAL_DOUBLE(_v));
_m->type = SQLT_FLT;
break;
case DB1_STRING:
_m->addr = (char*)VAL_STRING(_v);
_m->size = strlen(VAL_STRING(_v))+1;
_m->type = SQLT_STR;
break;
case DB1_STR:
{
unsigned len = VAL_STR(_v).len;
char *estr, *pstr = VAL_STR(_v).s;
estr = (char*)memchr(pstr, 0, len);
if (estr) {
LM_WARN("truncate STR len from %u to: '%s'\n",
len, pstr);
len = (unsigned)(estr - pstr) + 1;
}
_m->size = len;
_m->addr = pstr;
_m->type = SQLT_CHR;
}
break;
case DB1_DATETIME:
{
struct tm* tm = localtime(&VAL_TIME(_v));
if (tm->tm_sec == 60)
--tm->tm_sec;
OCIDateSetDate(_o, (ub2)(tm->tm_year + 1900),
(ub1)(tm->tm_mon + 1), (ub1)tm->tm_mday);
OCIDateSetTime(_o, (ub1)tm->tm_hour, (ub1)tm->tm_min,
(ub1)tm->tm_sec);
_m->addr = _o;
_m->size = sizeof(*_o);
_m->type = SQLT_ODT;
}
break;
case DB1_BLOB:
_m->addr = VAL_BLOB(_v).s;
_m->size = VAL_BLOB(_v).len;
_m->type = SQLT_CLOB;
break;
default:
LM_ERR("unknown data type\n");
return -1;
}
return 0;
}
GdaOracleValue *
_gda_value_to_oracle_value (const GValue *value)
{
GdaOracleValue *ora_value;
OCIDate *oci_date;
GType type;
ora_value = g_new0 (GdaOracleValue, 1);
ora_value->g_type = G_VALUE_TYPE (value);
ora_value->indicator = 0;
ora_value->hdef = (OCIDefine *) 0;
ora_value->pard = (OCIParam *) 0;
type = ora_value->g_type;
if (type == GDA_TYPE_NULL)
ora_value->indicator = -1;
else if ((type == G_TYPE_INT64) ||
(type == G_TYPE_UINT64) ||
(type == G_TYPE_DOUBLE) ||
(type == G_TYPE_INT) ||
(type == G_TYPE_UINT) ||
(type == GDA_TYPE_NUMERIC) ||
(type == G_TYPE_FLOAT) ||
(type == GDA_TYPE_SHORT) ||
(type == GDA_TYPE_USHORT) ||
(type == G_TYPE_LONG) ||
(type == G_TYPE_ULONG) ||
(type == G_TYPE_CHAR) ||
(type == G_TYPE_UCHAR)) {
gchar *val_str;
val_str = gda_value_stringify ((GValue *) value);
if (!val_str)
return NULL;
ora_value->sql_type = SQLT_CHR;
ora_value->value = (void *) val_str;
ora_value->defined_size = strlen (val_str);
}
else if (type == G_TYPE_DATE) {
GDate *gda_date;
ora_value->sql_type = SQLT_ODT;
gda_date = (GDate*) g_value_get_boxed (value);
oci_date = g_new0(OCIDate, 1);
OCIDateSetDate(oci_date, gda_date->year, gda_date->month, gda_date->day);
ora_value->defined_size = sizeof (OCIDate);
ora_value->value = oci_date;
}
else if (type == GDA_TYPE_TIME) {
GdaTime *gda_time;
ora_value->sql_type = SQLT_ODT;
gda_time = (GdaTime *) gda_value_get_time ((GValue *) value);
oci_date = g_new0(OCIDate, 1);
OCIDateSetTime(oci_date, gda_time->hour, gda_time->minute, gda_time->second);
ora_value->defined_size = sizeof (OCIDate);
ora_value->value = oci_date;
}
else if (type == GDA_TYPE_TIMESTAMP) {
GdaTimestamp *gda_timestamp;
ora_value->sql_type = SQLT_ODT;
gda_timestamp = (GdaTimestamp *) gda_value_get_timestamp ((GValue *) value);
oci_date = g_new0(OCIDate, 1);
OCIDateSetDate(oci_date, gda_timestamp->year, gda_timestamp->month, gda_timestamp->day);
OCIDateSetTime(oci_date, gda_timestamp->hour, gda_timestamp->minute, gda_timestamp->second);
ora_value->defined_size = sizeof (OCIDate);
ora_value->value = oci_date;
}
else if (type == GDA_TYPE_BLOB) {
GdaBinary *bin;
bin = (GdaBinary *) gda_value_get_blob ((GValue *) value);
if (bin) {
ora_value->sql_type = SQLT_LNG;
ora_value->value = bin->data;
ora_value->defined_size = bin->binary_length;
}
else {
ora_value->sql_type = SQLT_CHR;
ora_value->value = g_strdup ("");
ora_value->defined_size = 0;
}
}
else if (type == GDA_TYPE_BINARY) {
GdaBinary *bin;
bin = (GdaBinary *) gda_value_get_binary ((GValue *) value);
if (bin) {
ora_value->sql_type = SQLT_LNG;
ora_value->value = bin->data;
ora_value->defined_size = bin->binary_length;
}
else {
ora_value->sql_type = SQLT_CHR;
ora_value->value = g_strdup ("");
ora_value->defined_size = 0;
}
}
else {
gchar *val_str;
val_str = gda_value_stringify ((GValue *) value);
if (!val_str)
return NULL;
ora_value->sql_type = SQLT_CHR;
ora_value->value = val_str;
ora_value->defined_size = strlen (val_str);
}
return ora_value;
}
/*
* Add a new message to the database.
* session - session name
* sender_uid -
* rcpts -
* content -
* recv_time - time of arrival
* quiet - print error messages?
* Returns 0 on success
*/
int oralog_db_new_msg(char *session, char *sender_uid, char **rcpts, char *content, time_t recv_time, int quiet)
{
int i;
/* There will be at least 2 INSERTs - 1 into 'messages' table and 1 (at least)
into the 'recipients' table. */
text *sqlstmt_msg = (text *)"INSERT INTO messages VALUES(messages_seq.nextval, :session_uid, :sender_uid, :content, :recv_time) RETURNING id INTO :msg_id";
text *sqlstmt_rcp = (text *)"INSERT INTO recipients VALUES(recipients_seq.nextval, :recipient_uid, :msg_id)";
/* Statement & bind handles */
OCIStmt *hp_stmt_msg = NULL;
OCIStmt *hp_stmt_rcp = NULL;
OCIBind *bind_msg[5] = { NULL };
OCIBind *bind_rcp[2] = { NULL };
/* Control vars */
sword retstat = 0;
int errors = 0;
int print_errors = (quiet) ? 0 : 1;
OCIError *hp_error = NULL;
/* Date handling */
uword invalid = 0;
OCIDate oci_recvtime;
struct tm *tm_recvtime = NULL;
/* ID of inserted message will be put here */
OCINumber msg_id;
int init_val = 0; /* this variable is needed for initialization only */
pthread_mutex_lock(&oralog_oper_lock);
if (!oralog_is_connected()) {
debug("[logsoracle] can't log msg - not connected\n");
pthread_mutex_unlock(&oralog_oper_lock);
return 1;
}
check_string_len(session);
check_string_len(sender_uid);
check_string_len(content);
/* Create local handles (OCIBind's are created automaticly) */
OCIHandleAlloc( (dvoid *)hp_env, (dvoid **)&hp_stmt_msg, OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);
OCIHandleAlloc( (dvoid *)hp_env, (dvoid **)&hp_stmt_rcp, OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);
OCIHandleAlloc( (dvoid *)hp_env, (dvoid **)&hp_error, OCI_HTYPE_ERROR, 0, (dvoid **) 0);
/* Create DATE in Oracle format */
tm_recvtime = localtime(&recv_time);
memset(&oci_recvtime, 0, sizeof(OCIDate));
OCIDateSetTime( &oci_recvtime, (ub1)tm_recvtime->tm_hour, (ub1)tm_recvtime->tm_min, (ub1)tm_recvtime->tm_sec );
OCIDateSetDate( &oci_recvtime, (sb2)tm_recvtime->tm_year+1900, (ub1)tm_recvtime->tm_mon+1, (ub1)tm_recvtime->tm_mday );
/* check if provided 'recv_time' was mapped correctly */
retstat = OCIDateCheck(hp_error, &oci_recvtime, &invalid);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* prepare SQL statements */
debug("[logsoracle] preparing new messages insert statement\n");
retstat = OCIStmtPrepare(hp_stmt_msg, hp_error, (text *)sqlstmt_msg, (ub4)ora_strlen((char *)sqlstmt_msg), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
debug("[logsoracle] preparing new recipients insert statement\n");
retstat = OCIStmtPrepare(hp_stmt_rcp, hp_error, (text *)sqlstmt_rcp, (ub4)ora_strlen((char *)sqlstmt_rcp), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* bind the placeholders */
debug("[logsoracle] binding messages insert..\n");
retstat = OCIBindByName(hp_stmt_msg, &bind_msg[0], hp_error, (text *) ":session_uid", -1, (dvoid *)session,
ora_strlen(session)+1, SQLT_STR, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
retstat = OCIBindByName(hp_stmt_msg, &bind_msg[1], hp_error, (text *) ":sender_uid", -1, (dvoid *)sender_uid,
ora_strlen(sender_uid)+1, SQLT_STR, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
retstat = OCIBindByName(hp_stmt_msg, &bind_msg[2], hp_error, (text *) ":content", -1, (dvoid *)content,
ora_strlen(content)+1, SQLT_STR, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
retstat = OCIBindByName(hp_stmt_msg, &bind_msg[3], hp_error, (text *) ":recv_time",
-1, (dvoid *) &oci_recvtime,
(sword) sizeof(OCIDate), SQLT_ODT, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* Create a new OCINumber - will be under msg_id */
retstat = OCINumberFromInt(hp_error, &init_val, sizeof(init_val), OCI_NUMBER_SIGNED, &msg_id);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* bind the OCINumber */
retstat = OCIBindByName(hp_stmt_msg, &bind_msg[4], hp_error, (text *) ":msg_id",
-1, (dvoid *) &msg_id,
(sword) sizeof(msg_id), SQLT_VNU, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* Statement ready - execute */
debug("[logsoracle] executing insert on messages\n");
retstat = OCIStmtExecute(hp_service, hp_stmt_msg, hp_error, (ub4) 1, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* You can check recieved message ID like this:
retstat = OCINumberToInt(hp_error, &msg_id, sizeof(int), OCI_NUMBER_SIGNED, &tmpval);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
debug("[logsoracle] recieved message id: %d\n", tmpval);
*/
/* Insert into recipients table */
if(rcpts) {
for(i=0; rcpts[i] != NULL; i++) {
check_string_len(rcpts[i]);
debug("[logsoracle] binding recipients\n");
retstat = OCIBindByName(hp_stmt_rcp, &bind_rcp[0], hp_error, (text *) ":recipient_uid", -1, (dvoid *)rcpts[i],
ora_strlen(rcpts[i])+1, SQLT_STR, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
retstat = OCIBindByName(hp_stmt_rcp, &bind_rcp[1], hp_error, (text *) ":msg_id",
-1, (dvoid *) &msg_id,
(sword) sizeof(msg_id), SQLT_VNU, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
debug("[logsoracle] executing insert on recipients\n");
retstat = OCIStmtExecute(hp_service, hp_stmt_rcp, hp_error, (ub4) 1, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
}
}
/* Commit transaction */
if(!errors) {
debug("[logsoracle] commit\n");
OCITransCommit(hp_service, hp_error, (ub4) 0);
}
else {
debug("[logsoracle] errors present - aborting transaction\n");
OCITransRollback(hp_service, hp_error, (ub4) OCI_DEFAULT);
}
/* Cleanup (bind handles should be removed as a part of statement) */
if(hp_stmt_msg)
OCIHandleFree(hp_stmt_msg, OCI_HTYPE_STMT);
if(hp_stmt_rcp)
OCIHandleFree(hp_stmt_rcp, OCI_HTYPE_STMT);
if(hp_error)
OCIHandleFree(hp_error, OCI_HTYPE_ERROR);
pthread_mutex_unlock(&oralog_oper_lock);
return 0;
}
/*
* Add a new status change to the database
* Params:
* session - user session name
* uid - uid of person changing status
* status - type of new status (away|back|...)
* descr - new description
* change_time - time of status change (no. of seconds since 1st Jan 1970)
* quiet - print error messages or not
* Returns 0 on success.
*/
int oralog_db_new_status(char *session, char *uid, char *status, char *descr, time_t change_time, int quiet)
{
OCIStmt *hp_stmt = NULL; /* SQL statement handle */
OCIBind *bind[5] = { NULL }; /* Needed for C-var to SQL-var binding */
sword retstat = 0; /* Return value of function */
int errors = 0; /* Error counter */
uword invalid = 0; /* for DateCheck() */
OCIDate oci_date; /* for 'change_time' - oracle internal representation */
struct tm *tm_chtime = NULL; /* for 'change_time' - standard C representation */
int print_errors = (quiet) ? 0 : 1; /* if nonzero then print errors into status window */
OCIError *hp_error = NULL; /* oci error handle */
/* SQL statement to execute */
text *sqlstmt = (text *) "INSERT INTO status_changes VALUES( status_changes_seq.nextval, :session_uid, :changes_uid, :status, :descr, :change_time)";
pthread_mutex_lock(&oralog_oper_lock);
if (!oralog_is_connected()) {
debug("[logsoracle] can't log status - not connected\n");
pthread_mutex_unlock(&oralog_oper_lock);
return 1;
}
check_string_len(session);
check_string_len(uid);
check_string_len(status);
check_string_len(descr);
/* Create local handles (OCIBind's are created automaticly) */
OCIHandleAlloc( (dvoid *)hp_env, (dvoid **)&hp_stmt, OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);
OCIHandleAlloc( (dvoid *)hp_env, (dvoid **)&hp_error, OCI_HTYPE_ERROR, 0, (dvoid **) 0);
/* Create DATE */
tm_chtime = localtime(&change_time);
memset(&oci_date, 0, sizeof(OCIDate));
OCIDateSetTime( &oci_date, (ub1)tm_chtime->tm_hour, (ub1)tm_chtime->tm_min, (ub1)tm_chtime->tm_sec );
OCIDateSetDate( &oci_date, (sb2)tm_chtime->tm_year+1900, (ub1)tm_chtime->tm_mon+1, (ub1)tm_chtime->tm_mday );
/* check if provided 'change_time' was mapped correctly */
retstat = OCIDateCheck(hp_error, &oci_date, &invalid);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* prepare SQL statement */
debug("[logsoracle] preparing new status statement\n");
retstat = OCIStmtPrepare(hp_stmt, hp_error, (text *)sqlstmt, (ub4)ora_strlen((char *)sqlstmt), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* bind variables */
debug("[logsoracle] binding..\n");
retstat = OCIBindByName(hp_stmt, &bind[0], hp_error, (text *) ":session_uid", -1, (dvoid *)session,
ora_strlen(session)+1, SQLT_STR, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
retstat = OCIBindByName(hp_stmt, &bind[1], hp_error, (text *) ":changes_uid",
-1, (dvoid *) uid,
ora_strlen(uid)+1, SQLT_STR, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
retstat = OCIBindByName(hp_stmt, &bind[2], hp_error, (text *) ":status",
-1, (dvoid *) status,
ora_strlen(status)+1, SQLT_STR, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
retstat = OCIBindByName(hp_stmt, &bind[3], hp_error, (text *) ":descr",
-1, (dvoid *) descr,
ora_strlen(descr)+1, SQLT_STR, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* Oracle type DATE can be easily bind by using OCIDate struct and SQLT_ODT datatype */
retstat = OCIBindByName(hp_stmt, &bind[4], hp_error, (text *) ":change_time",
-1, (dvoid *) &oci_date,
(sword) sizeof(OCIDate), SQLT_ODT, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* Statement ready - execute */
debug("[logsoracle] executing\n");
retstat = OCIStmtExecute(hp_service, hp_stmt, hp_error, (ub4) 1, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT);
if(oralog_is_error(hp_error, retstat, print_errors))
errors++;
/* Commit transaction */
if(!errors) {
debug("[logsoracle] commit\n");
OCITransCommit(hp_service, hp_error, (ub4) 0);
}
else {
debug("[logsoracle] errors present - aborting transaction\n");
OCITransRollback(hp_service, hp_error, (ub4) OCI_DEFAULT);
}
/* Cleanup (bind handles should be removed as a part of statement) */
if(hp_stmt)
OCIHandleFree(hp_stmt, OCI_HTYPE_STMT);
if(hp_error)
OCIHandleFree(hp_error, OCI_HTYPE_ERROR);
pthread_mutex_unlock(&oralog_oper_lock);
return 0;
}
