Nginx ngx_oracle or ngx_owa_module like Apache Mod_owa / Oracle Apache Mod_plsql

tjunussov · 2016-04-14T03:42:07+00:00

Hello! On Tue, Apr 12, 2016 at 2:46 PM, David Santhosh wrote: > I am using nginx geoip module with Maxmind and have a custom requirement > where I don...

Nginx ngx_oracle or ngx_owa_module like Apache Mod_owa / Oracle Apache Mod_plsql

tjunussov

Hi! Have you familiar with Oracle EPG ( embeded plsql gateway ) or Oracle Apache Mod_plsql ?

if no, short description, it is http wrapper to invoke plsql procedures/functions via web, ex: http://localhost/dadabase/schema.package.procedure?param1=value1

I assume it will be huge contribution in Nginx+Oracle world

Openresty great project, and i think with your background in writing Nginx modules, it would great it someone write analog of Apache Mod_Plsql in Nginx.

The task is :

handle configuration DAD (database access descriptor) to access database with user/pass/schema
handle connection pool
handle request and binding params (simple, key-value,arrays), call proc/function
handle optional before-request function call, after-request function call
handle error handling
handle url pattern match procedure call
handle file upload / download table/procedure
addition feature : cursor walk, cursor print to html/json
addition feature : SQL queries inside nginx configuration
addition feature : CRUD(DAV) support

Here is links :

Mod_Owa it is open sourced analog of Mod_Plsql - https://oss.oracle.com/projects/mod_owa/dist/documentation/modowa.htm

Discussion about nginx and oracle found here - https://forum.nginx.org/read.php?2,35329

Somone already written some code to connect to Oracle via Nginx, but it is not written as Gateway, - https://habrahabr.ru/post/69974/

Code is taken from link above :

// вариант БЕЗ поддержки настроек через nginx.conf и ОБРАБОТКИ ошибок
#include <ngx_config.h>
#include <ngx_core.h>
#include <ngx_http.h>
#include <string.h>
#include <oci.h>
const text *db_user_name = (const text*)"orauser";
const text *db_password = (const text*)"pass";
const text *db_conn_str = (const text*)"comp:1521/xe";
const text *command = (const text*)
"BEGIN PAY_SYS.PROCESS_TRANS(:v1,:v2,:v3,:v4); END;";
OCIEnv *env;
OCISvcCtx *context;
OCISession *session;
OCIServer *server;
OCIError *error;
OCIStmt *statement;
OCIBind *bnd1, *bnd2, *bnd3, *bnd4;
int operatorID, accountID, sum, result;
static char* ngx_http_payment_init (ngx_conf_t *cf,
ngx_command_t *cmd, void *conf);
// массив возможных опций модуля в nginx.conf
static ngx_command_t ngx_http_payment_commands[] =
{ // задаем только одну основную опцию, включающую модуль в указанном Location
{ ngx_string("payment_enabled"),
NGX_HTTP_LOC_CONF|NGX_CONF_NOARGS,
ngx_http_payment_init,
NGX_HTTP_LOC_CONF_OFFSET,
0,
NULL },
ngx_null_command // терминирует массив
};
// callback-и, не используем
static ngx_http_module_t ngx_http_payment_module_ctx =
{
NULL, /* preconfiguration */
NULL, /* postconfiguration */
NULL, /* create main configuration */
NULL, /* init main configuration */
NULL, /* create server configuration */
NULL, /* merge server configuration */
NULL, /* create location configuration */
NULL /* merge location configuration */
};
// дескриптор модуля, включает в себя все параметры
ngx_module_t ngx_http_payment_module =
{
NGX_MODULE_V1,
&ngx_http_payment_module_ctx, /* module context */
ngx_http_payment_commands, /* module directives */
NGX_HTTP_MODULE, /* module type */
NULL, /* init master */
NULL, /* init module */
NULL, /* init process */
NULL, /* init thread */
NULL, /* exit thread */
NULL, /* exit process */
NULL, /* exit master */
NGX_MODULE_V1_PADDING
};
void SetCallParams(int oper, int account, int money)
{
if (bnd1 != 0) OCIHandleFree((dvoid*)bnd1, OCI_HTYPE_BIND);
if (bnd2 != 0) OCIHandleFree((dvoid*)bnd2, OCI_HTYPE_BIND);
if (bnd3 != 0) OCIHandleFree((dvoid*)bnd3, OCI_HTYPE_BIND);
if (bnd4 != 0) OCIHandleFree((dvoid*)bnd4, OCI_HTYPE_BIND);
// копируем из стека, чтобы иметь возможность передать валидный указатель
result = 0;
operatorID = oper;
accountID = account;
sum = money;
// привязываем параметры к вызову SQL
OCIBindByPos(statement, &bnd1, error, 1, &accountID,
sizeof(accountID), SQLT_INT,(dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
OCIBindByPos(statement, &bnd2, error, 2, &operatorID,
sizeof(operatorID), SQLT_INT,(dvoid *) 0,
(ub2 *) 0, (ub2 *) 0,(ub4) 0, (ub4 *) 0, OCI_DEFAULT);
OCIBindByPos(statement, &bnd3, error, 3, &sum, sizeof(sum), SQLT_INT,
(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0,
(ub4 *) 0, OCI_DEFAULT);
OCIBindByPos(statement, &bnd4, error, 4, &result, sizeof(result),SQLT_INT,
(dvoid *) 0,(ub2 *) 0,(ub2 *) 0,(ub4) 0,(ub4 *)0, OCI_DEFAULT);
}
// основной обработчик, вся работа выполняется здесь
static ngx_int_t ngx_http_payment_handler(ngx_http_request_t *r)
{
// очищаем body-раздел
ngx_int_t rc = ngx_http_discard_request_body(r);
if (rc != NGX_OK && rc != NGX_AGAIN)
{
ngx_log_error(NGX_LOG_ERR, r->connection->log, 0,
"Failed ngx_http_discard_request_body()");
return rc;
}
// строки в nginx хранятся как пара length-data,
// а не как традиционный C-style zero-end
// копируем для корректной работы парсера
const int buflen = 100;
char buf[buflen+1];
int len = (r->args.len < buflen)? r->args.len : buflen;
buf[len] = '\0';
ngx_memcpy(buf, r->args.data, r->args.len);
int successful = 0;
int operator,abonent,money, result=-1;
// вызов простейшего парсера
operator = GetIntParam(buf, "operator=");
abonent = GetIntParam(buf, "abonent=");
money = GetIntParam(buf, "money=");
if (operator > 0 && abonent > 0 && money > 0)
{
SetCallParams(operator, abonent, money);
int retCode = OCIStmtExecute(
context, statement, error, (ub4) 1, (ub4) 0,
(OCISnapshot *) NULL, (OCISnapshot *) NULL,
(ub4) OCI_COMMIT_ON_SUCCESS);
if (retCode == OCI_SUCCESS)
successful = 1;
}
r->headers_out.content_type.len = sizeof("text/html") - 1;
r->headers_out.content_type.data = (u_char *) "text/html";
r->headers_out.content_length_n = 0;
if (successful == 1)
r->headers_out.status = NGX_HTTP_OK;
else
r->headers_out.status = NGX_HTTP_INTERNAL_SERVER_ERROR;
// ... формирование body ответа и передача его nginx-у.. см примеры emiller
}
static char* ngx_http_payment_init(ngx_conf_t *cf,
ngx_command_t *cmd,
void *our_conf)
{
// set nginx handler
ngx_http_core_loc_conf_t *core_conf =
ngx_http_conf_get_module_loc_conf(cf, ngx_http_core_module);
core_conf->handler = ngx_http_payment_handler;
// oracle
bnd1 = (OCIBind *) 0;
bnd2 = (OCIBind *) 0;
bnd3 = (OCIBind *) 0;
bnd4 = (OCIBind *) 0;
OCIEnvCreate((OCIEnv **)&env,(ub4)OCI_DEFAULT,
(dvoid *)0,(dvoid * (*)(dvoid *, size_t))0,
(dvoid * (*)(dvoid *, dvoid *, size_t))0,
(void (*)(dvoid *, dvoid *))0,(size_t)0,(dvoid **)0);
/* allocate a server handle */
OCIHandleAlloc ((dvoid *)env, (dvoid **)&server,
OCI_HTYPE_SERVER, 0, (dvoid **) 0);
/* allocate an error handle */
OCIHandleAlloc ((dvoid *)env, (dvoid **)&error,
OCI_HTYPE_ERROR, 0, (dvoid **) 0);
/* create a server context */
int retCode = OCIServerAttach (server, error, db_conn_str,
strlen((const char*)db_conn_str), OCI_DEFAULT);
/* allocate a service handle */
retCode = OCIHandleAlloc ((dvoid *)env, (dvoid **)&context,
OCI_HTYPE_SVCCTX, 0, (dvoid **) 0);
/* set the server attribute in the service context handle*/
retCode = OCIAttrSet ((dvoid *)context, OCI_HTYPE_SVCCTX,
(dvoid *)server, (ub4) 0, OCI_ATTR_SERVER, error);
/* allocate a user session handle */
retCode = OCIHandleAlloc ((dvoid *)env, (dvoid **)&session,
OCI_HTYPE_SESSION, 0, (dvoid **) 0);
// set up user & password for our session
retCode = OCIAttrSet ((dvoid *)session, OCI_HTYPE_SESSION,
(void*)db_user_name,(ub4)strlen((const char*)db_user_name),
OCI_ATTR_USERNAME, error);
retCode = OCIAttrSet ((dvoid *)session, OCI_HTYPE_SESSION,
(void*)db_password,(ub4)strlen((const char*)db_password),
OCI_ATTR_PASSWORD, error);
// start session
retCode = OCISessionBegin (context, error, session,
OCI_CRED_RDBMS, OCI_DEFAULT);
/* set the user session attribute in the service context handle*/
retCode = OCIAttrSet ((dvoid *)context, OCI_HTYPE_SVCCTX,
(dvoid *)session, (ub4) 0,
OCI_ATTR_SESSION, error);
OCIHandleAlloc((dvoid *) env, (dvoid **) &statement,
OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);
OCIStmtPrepare(statement, error, command, (ub4)strlen((char*)command),
(ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
return NGX_CONF_OK;
}

* This source code was highlighted with Source Code Highlighter.

p.s I could assist/help you if needed.

agentzh

Hello!

On Wed, Apr 13, 2016 at 12:42 PM, Timur Junussov wrote:
> Hi! Have you familiar with Oracle EPG ( embeded plsql gateway ) or Oracle
> Apache Mod_plsql ?
> if no, short description, it is http wrapper to invoke plsql
> procedures/functions via web, ex:
> http://localhost/dadabase/schema.package.procedure?param1=value1
>
> I assume it will be huge contribution in Nginx+Oracle world
> Openresty great project, and i think with your background in writing Nginx
> modules, it would great it someone write analog of  Apache Mod_Plsql in
> Nginx.
> The task is :
>

As long as you speak to Oracle in a nonblocking fashion and you use
the nginx event loop to dispatch events on the file descriptors of the
Oracle database connections, then you are good to go.

You can check out the existing ngx_drizzle module (for MySQL and
Drizzle) and ngx_postgres module (for PostgreSQL) for such examples.

If the protocol is open, then you can consider building a Lua client
atop OpenResty's nonblocking cosocket API. You can check out the
lua-resty-mysql for such an example.

Regards,
-agentzh