All problems are solved. Oracle backend functions properly.
Patch attached.
Items count function implemented.
To fix NLS-related issues:
1) your Oracle DB must be created with UTF8 as default charsets, both
for NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET;
2) must be set environment variable NLS_LANG, this way:
$ export NLS_LANG=.UTF8
(in jabbers's account shell)
It works for me but I couldn't say that the code is perfect :) It
needs polishing.
Please, test.
--
Viva Open Source!
Submitted By: Oleksiy Kramarenko <Lindroid at gmail dot com>
Date: 2008-10-17
Description: Fix Oracle backend.
diff -Naur jabberd-2.2.4-orig/storage/authreg_oracle.c jabberd-2.2.4/storage/authreg_oracle.c
--- jabberd-2.2.4-orig/storage/authreg_oracle.c 2008-08-28 15:02:25.000000000 +0300
+++ jabberd-2.2.4/storage/authreg_oracle.c 2008-10-17 14:52:27.000000000 +0300
@@ -29,6 +29,7 @@
//#define MYSQL_LP 256 /* maximum length of password - should correspond to field length */
#define BLOCKSIZE (1024)
+#define PWSIZE (257)
typedef struct Oracle_context_st
{
@@ -100,48 +101,6 @@
return nStatus;
}
-static int oracle_ping( authreg_t ar )
-{
- Oracle_context_t odpOracleDriver = (Oracle_context_t)ar->private;
-
- // Prepare the check statement
- int nResultCode = OCIStmtPrepare(odpOracleDriver->ociStatement, odpOracleDriver->ociError,
- "select sysdate from dual", (ub4) 24, OCI_NTV_SYNTAX,
- OCI_DEFAULT);
-
- // This is the real check
- nResultCode = OCIStmtExecute(odpOracleDriver->ociService, odpOracleDriver->ociStatement, odpOracleDriver->ociError,
- (ub4) 0, (ub4) 0,
- (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL,
- OCI_DESCRIBE_ONLY );
-
-
- // If there was an error...
- if (nResultCode != 0)
- {
- char szErrorBuffer[250];
- char *svHost, *svUser, *svPass;
-
- OCIErrorGet((dvoid *)odpOracleDriver->ociError, (ub4) 1, (text *) NULL, &nResultCode, szErrorBuffer, (ub4) sizeof(szErrorBuffer), OCI_HTYPE_ERROR);
- log_write(ar->c2s->log, LOG_ERR, "storage_oracle.c (oracle_ping): %s", szErrorBuffer);
-
- // Obtain user configuration
- svHost = config_get_one(ar->c2s->config, "storage.oracle.host", 0);
- svUser = config_get_one(ar->c2s->config, "storage.oracle.user", 0);
- svPass = config_get_one(ar->c2s->config, "storage.oracle.pass", 0);
-
- // Logon to the database
- nResultCode = OCILogon((dvoid *)odpOracleDriver->ociEnvironment, (dvoid *)odpOracleDriver->ociError, &(odpOracleDriver->ociService), svUser, strlen(svUser), svPass, strlen(svPass), svHost, strlen(svHost));
-
- if (nResultCode != 0)
- {
- OCIErrorGet((dvoid *)odpOracleDriver->ociError, (ub4) 1, (text *) NULL, &nResultCode, szErrorBuffer, (ub4) sizeof(szErrorBuffer), OCI_HTYPE_ERROR);
- log_write(ar->c2s->log, LOG_ERR, "storage_oracle.c (oracle_ping): %s", szErrorBuffer);
- }
- }
-
- return nResultCode;
-}
static int _sql_length( char* sql )
{
@@ -285,14 +244,14 @@
}
-static int _ar_oracle_get_password( authreg_t ar, char *username, char *realm, char password[257] )
+static int _ar_oracle_get_password( authreg_t ar, char *username, char *realm, char password[PWSIZE] )
{
Oracle_context_t _ctx = (Oracle_context_t)ar->private;
char* _sqlbuf = NULL;
int _nNumberOfFields = 0;
int _nResultCode = 0;
int _len = 0;
- char _password[257];
+ char _password[PWSIZE];
memset( _password, '\0', sizeof(_password) );
ORACLE_SAFE( _sqlbuf, strlen(username) + strlen(realm) + _sql_length(_ctx->sql_select), _len );
sprintf( _sqlbuf, _ctx->sql_select, username, realm );
@@ -306,7 +265,7 @@
}
_nResultCode = checkOCIError(ar, "_ar_oracle_get_user_tuple:define pos", _ctx->ociError, OCIDefineByPos( _ctx->ociStatement, \
- &_ctx->ociDefine, _ctx->ociError, 1, &_password, 257, SQLT_STR, 0, 0, 0, OCI_DEFAULT ) );
+ &_ctx->ociDefine, _ctx->ociError, 1, &_password, PWSIZE, SQLT_STR, 0, 0, 0, OCI_DEFAULT ) );
if( _nResultCode != 0 )
{
free( _sqlbuf );
@@ -324,12 +283,13 @@
OCIStmtFetch2( _ctx->ociStatement, _ctx->ociError, 1, OCI_FETCH_FIRST, 0, OCI_DEFAULT );
free( _sqlbuf );
- if( strlen(_password) != 0 )strncpy( password, _password, strlen(_password) );
+ strncpy( password, _password, PWSIZE - 1 );
+ password[PWSIZE - 1] = '\0';
return 0;
}
-static int _ar_oracle_set_password(authreg_t ar, char *username, char *realm, char password[257])
+static int _ar_oracle_set_password(authreg_t ar, char *username, char *realm, char password[PWSIZE])
{
Oracle_context_t _ctx = (Oracle_context_t)ar->private;
char* _sqlbuf = NULL;
@@ -485,25 +445,10 @@
ORACLE_SAFE( oracle_server_host, strlen(host) + strlen(port) + strlen(dbname) + _sql_length(oracle_server_parameters), _len );
sprintf( oracle_server_host, oracle_server_parameters, host, port, dbname );
- log_debug( ZONE, "oracle9i connecting as '%s' to database '%s' on %s:%s", user, dbname, host, port );
-
-#ifdef __COMPATIBLE
- /* Initialize OCI */
- nResultCode = OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0, (dvoid * (*)(dvoid *, size_t))0, (dvoid * (*)(dvoid *, dvoid *, size_t))0, (void (*)(dvoid *, dvoid *)) 0);
+ log_debug( ZONE, "OCI connecting as '%s' to database '%s' on %s:%s", user, dbname, host, port );
-// fprintf( stdout, "LINE = %d: nResultCode = %d\n", __LINE__, nResultCode );
- if (nResultCode != 0)
- {
- log_write(ar->c2s->log, LOG_ERR, "(st_oracle_init: ) Could not Initialize OCI (%d)", nResultCode);
- return 1;
- }
+ nResultCode = OCIEnvCreate( (OCIEnv**)&ociEnvironment, OCI_DEFAULT, (dvoid*)0, 0, 0, 0, (size_t)0, (dvoid **)0 );
- /* Initialize evironment */
- nResultCode = OCIEnvInit( (OCIEnv **)&ociEnvironment, OCI_DEFAULT, (size_t)0, (dvoid **)0 );
-#else
- nResultCode = OCIEnvCreate( (OCIEnv**)&ociEnvironment, OCI_OBJECT, (dvoid*)0, 0, 0, 0, (size_t)0, (dvoid **)0 );
-#endif
-// fprintf( stdout, "LINE = %d: nResultCode = %d\n", __LINE__, nResultCode );
if (nResultCode != 0)
{
log_write(ar->c2s->log, LOG_ERR, "(st_oracle_init: ) Could not Initialize OCI Environment (%d)", nResultCode);
@@ -513,7 +458,6 @@
/* Initialize handles */
nResultCode = OCIHandleAlloc( (dvoid *)ociEnvironment, (dvoid **) &ociError, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0 );
-// fprintf( stdout, "LINE = %d: nResultCode = %d\n", __LINE__, nResultCode );
if (nResultCode != 0)
{
log_write(ar->c2s->log, LOG_ERR, "(st_oracle_init: ) Could not create OCI Error object (%d)" , nResultCode);
@@ -524,7 +468,6 @@
nResultCode = checkOCIError(ar, "st_oracle_init: Allocate Service", ociError, OCIHandleAlloc((dvoid *)ociEnvironment,
(dvoid **)&ociService, OCI_HTYPE_SVCCTX,
(size_t)NULL, (dvoid **)NULL) );
-// fprintf( stdout, "LINE = %d: nResultCode = %d\n", __LINE__, nResultCode );
if (nResultCode != 0)
{
nResultCode = OCIHandleFree((dvoid *) ociError, OCI_HTYPE_ERROR);
@@ -536,7 +479,6 @@
nResultCode = checkOCIError(ar, "st_oracle_init: Connect to Server", ociError, OCILogon(ociEnvironment, ociError, &ociService,
user, strlen(user), pass, strlen(pass),
oracle_server_host, strlen(oracle_server_host)));
-// fprintf( stdout, "LINE = %d: nResultCode = %d, ociError=%s\n", __LINE__, nResultCode, ociError );
if (nResultCode != 0)
{
@@ -551,7 +493,6 @@
(dvoid **)&ociStatement, OCI_HTYPE_STMT,
(size_t)NULL, (dvoid **)NULL));
-// fprintf( stdout, "LINE = %d: nResultCode = %d\n", __LINE__, nResultCode );
if (nResultCode != 0)
{
nResultCode = OCILogoff(ociService, ociError);
@@ -573,7 +514,6 @@
ar->set_password = _ar_oracle_set_password;
ar->create_user = _ar_oracle_create_user;
ar->delete_user = _ar_oracle_delete_user;
- ar->get_authreg_user = _ar_oracle_get_authreg_user;
return 0;
}
diff -Naur jabberd-2.2.4-orig/storage/storage_oracle.c jabberd-2.2.4/storage/storage_oracle.c
--- jabberd-2.2.4-orig/storage/storage_oracle.c 2008-08-28 15:02:25.000000000 +0300
+++ jabberd-2.2.4/storage/storage_oracle.c 2008-10-17 14:52:27.000000000 +0300
@@ -33,8 +33,6 @@
OCIBind *ociBind;
xht filters;
char *prefix;
- char *svUser;
- char *svPass;
} *OracleDriverPointer;
#define BLOCKSIZE (1024)
@@ -199,6 +197,9 @@
{
char szErrorBuffer[250];
char *svHost, *svUser, *svPass;
+ char *svPort, *svSid, *oracle_server_host = NULL;
+ static char* oracle_server_parameters = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=\"%s\")(PORT=\"%s\"))(CONNECT_DATA=(SID=\"%s\")))";
+ int _len = 0;
OCIErrorGet((dvoid *)odpOracleDriver->ociError, (ub4) 1, (text *) NULL, &nResultCode, szErrorBuffer, (ub4) sizeof(szErrorBuffer), OCI_HTYPE_ERROR);
log_write(drv->st->sm->log, LOG_ERR, "storage_oracle.c (oracle_ping): %s", szErrorBuffer);
@@ -207,9 +208,14 @@
svHost = config_get_one(drv->st->sm->config, "storage.oracle.host", 0);
svUser = config_get_one(drv->st->sm->config, "storage.oracle.user", 0);
svPass = config_get_one(drv->st->sm->config, "storage.oracle.pass", 0);
+ svPort = config_get_one(drv->st->sm->config, "storage.oracle.port", 0);
+ svSid = config_get_one(drv->st->sm->config, "storage.oracle.dbname", 0);
+
+ ORACLE_SAFE( oracle_server_host, strlen(svHost) + strlen(svPort) + strlen(svSid) + strlen(oracle_server_parameters), _len );
+ sprintf( oracle_server_host, oracle_server_parameters, svHost, svPort, svSid );
// Logon to the database
- nResultCode = OCILogon((dvoid *)odpOracleDriver->ociEnvironment, (dvoid *)odpOracleDriver->ociError, &(odpOracleDriver->ociService), svUser, strlen(svUser), svPass, strlen(svPass), svHost, strlen(svHost));
+ nResultCode = OCILogon((dvoid *)odpOracleDriver->ociEnvironment, (dvoid *)odpOracleDriver->ociError, &(odpOracleDriver->ociService), svUser, strlen(svUser), svPass, strlen(svPass), oracle_server_host, strlen(oracle_server_host));
if (nResultCode != 0)
@@ -217,6 +223,8 @@
OCIErrorGet((dvoid *)odpOracleDriver->ociError, (ub4) 1, (text *) NULL, &nResultCode, szErrorBuffer, (ub4) sizeof(szErrorBuffer), OCI_HTYPE_ERROR);
log_write(drv->st->sm->log, LOG_ERR, "storage_oracle.c (oracle_ping): %s", szErrorBuffer);
}
+
+ free(oracle_server_host);
}
return nResultCode;
@@ -556,8 +564,8 @@
{
return st_NOTFOUND;
}
- else
- {
+
+
/*
* TODO: Handle memory better.
* The DDL for the "vcard" table has 21 fields. The following implementation allocates 82K for 21 fields.
@@ -740,11 +748,82 @@
break;
}
}
- }
+
return st_SUCCESS;
}
+static int _st_oracle_count( st_driver_t drv, char *a_szType, char *owner, char *filter, int *count )
+{
+ OracleDriverPointer data = (OracleDriverPointer) drv->private;
+ const char *szStmtTemplate = "SELECT COUNT(*) FROM \"%s\" WHERE %s";
+ char *szQuery = NULL;
+ char szBuffer[128];
+ char *szWhereClause = NULL;
+ int nResultCode = 0;
+ int nQueryLength = 0;
+
+ if( !owner ) {
+ log_debug(ZONE,"_st_oracle_count: owner is null");
+ return st_FAILED;
+ }
+
+ if(oracle_ping(drv) != 0)
+ {
+ log_write(drv->st->sm->log, LOG_ERR, "_st_oracle_count: Connection to database lost!");
+ return st_FAILED;
+ }
+
+ if(data->prefix != NULL)
+ {
+ snprintf(szBuffer, sizeof(szBuffer), "%s%s", data->prefix, a_szType);
+ a_szType = szBuffer;
+ }
+
+
+ szWhereClause = _st_oracle_convert_filter(drv, owner, filter);
+ log_debug(ZONE, "_st_oracle_count: Generated Filter: %s", szWhereClause);
+
+ ORACLE_SAFE(szQuery, strlen(a_szType) + strlen(szWhereClause) + strlen(szStmtTemplate), nQueryLength);
+ sprintf(szQuery, szStmtTemplate, a_szType, szWhereClause);
+ free(szWhereClause);
+
+ nResultCode = checkOCIError(drv, "_st_oracle_count: Prepare Statement", data->ociError, OCIStmtPrepare(data->ociStatement, data->ociError,
+ szQuery, (ub4)strlen(szQuery), OCI_NTV_SYNTAX,
+ OCI_DEFAULT));
+
+ if (nResultCode != 0)
+ {
+ free(szQuery);
+ return st_FAILED;
+ }
+
+ nResultCode = checkOCIError(drv, "_st_oracle_count: Define Pos", data->ociError, OCIDefineByPos( data->ociStatement, &data->ociDefine, data->ociError,
+ 1, count, sizeof(int), SQLT_INT, 0, 0, 0, OCI_DEFAULT ) );
+
+ if (nResultCode != 0)
+ {
+ free(szQuery);
+ return st_FAILED;
+ }
+
+ nResultCode = checkOCIError(drv, "_st_oracle_count: Statement Execute", data->ociError, OCIStmtExecute(data->ociService,
+ data->ociStatement, data->ociError, (ub4)0,
+ (ub4)0, (CONST OCISnapshot *)NULL,
+ (OCISnapshot *)NULL, OCI_STMT_SCROLLABLE_READONLY));
+
+ if (nResultCode != 0)
+ {
+ free(szQuery);
+ return st_FAILED;
+ }
+
+ OCIStmtFetch2( data->ociStatement, data->ociError, 1, OCI_FETCH_FIRST, 0, OCI_DEFAULT);
+ free(szQuery);
+
+ return st_SUCCESS;
+}
+
static st_ret_t _st_oracle_delete(st_driver_t drv, char *type, char *owner, char *filter)
{
OracleDriverPointer data = (OracleDriverPointer) drv->private;
@@ -840,34 +919,32 @@
st_ret_t st_init(st_driver_t drv) {
int nResultCode;
char *svHost, *svUser, *svPass;
+ char *svPort, *svSid, *oracle_server_host = NULL;
OCIEnv *ociEnvironment;
OCIError *ociError;
OCISvcCtx *ociService;
OCIStmt *ociStatement;
+ static char* oracle_server_parameters = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=\"%s\")(PORT=\"%s\"))(CONNECT_DATA=(SID=\"%s\")))";
+ int _len = 0;
OracleDriverPointer data;
svHost = config_get_one(drv->st->sm->config, "storage.oracle.host", 0);
svUser = config_get_one(drv->st->sm->config, "storage.oracle.user", 0);
svPass = config_get_one(drv->st->sm->config, "storage.oracle.pass", 0);
+ svPort = config_get_one(drv->st->sm->config, "storage.oracle.port", 0);
+ svSid = config_get_one(drv->st->sm->config, "storage.oracle.dbname", 0);
- if(svHost == NULL || svUser == NULL || svPass == NULL)
+ if(svHost == NULL || svUser == NULL || svPass == NULL || svPort == NULL || svSid == NULL)
{
log_write(drv->st->sm->log, LOG_ERR, "(st_oracle_init: ) Invalid driver config from XML file.");
return st_FAILED;
}
- /* Initialize OCI */
- nResultCode = OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0, (dvoid * (*)(dvoid *, size_t))0, (dvoid * (*)(dvoid *, dvoid *, size_t))0, (void (*)(dvoid *, dvoid *)) 0);
+ ORACLE_SAFE( oracle_server_host, strlen(svHost) + strlen(svPort) + strlen(svSid) + strlen(oracle_server_parameters), _len );
+ sprintf( oracle_server_host, oracle_server_parameters, svHost, svPort, svSid );
- if (nResultCode != 0)
- {
- log_write(drv->st->sm->log, LOG_ERR, "(st_oracle_init: ) Could not Initialize OCI (%d)", nResultCode);
- return st_FAILED;
- }
-
- /* Initialize evironment */
- nResultCode = OCIEnvInit((OCIEnv **) &ociEnvironment, OCI_DEFAULT, (size_t) 0, (dvoid **) 0);
+ nResultCode = OCIEnvCreate( (OCIEnv**)&ociEnvironment, OCI_DEFAULT, (dvoid*)0, 0, 0, 0, (size_t)0, (dvoid **)0 );
if (nResultCode != 0)
{
@@ -898,7 +975,7 @@
/* Connect to database server */
nResultCode = checkOCIError(drv, "st_oracle_init: Connect to Server", ociError, OCILogon(ociEnvironment, ociError, &ociService,
svUser, strlen(svUser), svPass, strlen(svPass),
- svHost, strlen(svHost)));
+ oracle_server_host, strlen(oracle_server_host)));
if (nResultCode != 0)
{
@@ -922,6 +999,8 @@
return st_FAILED;
}
+ free(oracle_server_host);
+
data = (OracleDriverPointer) calloc(1, sizeof(struct OracleDriver));
data->ociEnvironment = ociEnvironment;
@@ -930,8 +1009,6 @@
data->ociStatement = ociStatement;
data->ociDefine = NULL;
data->ociBind = NULL;
- data->svUser = svUser;
- data->svPass = svPass;
data->filters = xhash_new(17);
@@ -941,6 +1018,7 @@
drv->add_type = _st_oracle_add_type;
drv->put = _st_oracle_put;
+ drv->count = _st_oracle_count;
drv->get = _st_oracle_get;
drv->delete = _st_oracle_delete;
drv->replace = _st_oracle_replace;
