[cx-oracle-users] Integrating LIXA with cx_Oracle

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Dear All,
I'm the founder of LIXA project ( http://sourceforge.net/projects/lixa/ http://lixa.sourceforge.net/manuals/ ) and I'm interested in integrating LIXA with cx_Oracle.
LIXA is LIbre XA, a free and open source XA compliant transaction manager: it implements XA interface to deal with the Resouce Managers and implements TX interface to deal with the Application Programs.
LIXA supports Oracle as a Resource Manager and allows to create distributed transaction s (2 phase commit) with PostgreSQL, MySQL, Oracle and DB2.
XA and TX are interfaces designed for C (and C++) programs.
I am now interested in expanding the LIXA project scope to Python language and Python programmers.
It seems to me cx_Oracle is a good start point because it wraps the Oracle C API; LIXA too uses OCI.

It's time to describe my issue to ask some help to you.
First of all, I'm not a Python expert.
I've generated a LIXA wrapper using SWIG and it works fine, the usage is something like this (my development environment is based on Ubuntu 10.04):

tiian@mojan:~/src/swig$ python
Python 2.6.5 (r265:79063, Apr 16 2010, 13:09:56)
[GCC 4.4.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import lixa
>>> lixa.tx_open()
0
>>> lixa.tx_begin()
0
>>> lixa.tx_commit()
0
>>> lixa.tx_close()
0
>>>

tx_open(), tx_begin(), tx_commit(), tx_close() are the standard functions described in "TX (Transaction Demarcation) Specification" (CAE X/Open standard).
The operations related to the Resource Managers must be put between "tx_begin()" and "tx_commit()".

TX standard specifies the Resource Managers must be opened by the Transaction Manager using "tx_open()" by the Application Program. This is the key point: cx_Oracle supplies its own method to get a valid Oracle session: "cx_Oracle.connect(...)", while LIXA wants to create it using "lixa.tx_open()": the Transaction Manager must open all the Resource Managers, not only Oracle.
Using C as the development language, Oracle supplies some convenience methods to retrieve the environment and the context for a connection opened by an XA Transaction Manager: xaoEnv, xaoSvcCtx.

The following one is an excerpt from a C sample (http://lixa.svn.sourceforge.net/viewvc/lixa/doc/examples/example2_ora.c?revision=703&view=markup): 

[...]

    /* open the resource manager(s) */
    if (TX_OK != (txrc = tx_open())) {
        fprintf(stderr, "tx_open error: %d\n", txrc);
        exit(txrc);
    }

    /* retrieve environment and context */
    if (NULL == (oci_env = xaoEnv(NULL))) {
        fprintf(stderr, "xaoEnv returned a NULL pointer\n");
        exit(1);
    }
    if (NULL == (oci_svc_ctx = xaoSvcCtx(NULL))) {
        fprintf(stderr, "xaoSvcCtx returned a NULL pointer\n");
        exit(1);
    }

    /* allocate statement and error handles */
    if (0 != OCIHandleAlloc( (dvoid *)oci_env, (dvoid **)&stmt_hndl,
                             OCI_HTYPE_STMT, (size_t)0, (dvoid **)0)) {
        fprintf(stderr, "Unable to allocate statement handle\n");
        exit(1);
    }
    if (0 != OCIHandleAlloc( (dvoid *)oci_env, (dvoid **)&err_hndl,
                             OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0)) {
        fprintf(stderr, "Unable to allocate error handle\n");
        exit(1);
    }

[...]

the Application Program does not use "OCIEnvNlsCreate(...)": the Transaction Manager calls "xa_open()" ("xaoopen") inside "tx_open()" and the Application Program retrieves the environment using a convenience method (xaoEnv).
The same happens when the connection must be closed using "tx_close()".

Thinking about integration between LIXA and cx_Oracle I'm proposing three different paths:
1. patching cx_Oracle substituting "OCIEnvNlsCreate" with "xaoEnv" and removing some functions using user/password, SID, etc... I don't like this solution because the patch must be managed and tested every time cx_Oracle releases something new; I don't think it would be well accepted by the users too

2. it could be easy to overload the "cx_Oracle.connect()" method: if it accepted something like a wrapped "OCIEnv *" too, the integration would be something like: 
cx_Oracle.connect(lixa.lixa_ora_get_ocienv() )
The proposed use case would be something like this:
import lixa
import cx_Oracle
import ...        (other Resource Manager modules)
lixa.tx_open()    ... create Transaction Manager context 
                  and open Resource Managers
liora = lixa.lixa_ora_get_ocienv()
conn = cx_Oracle.connect( some_function(liora) )
... some stuff ...
lixa.tx_begin()
... insert/update/delete using conn object ...
lixa.tx_commit()
conn.close()      ... it should destroy the Python object without calling the Oracle's close function
lixa.tx_close()   ... destroy Transaction Manager context 
                  and close Resource Manager connections 
                  using xa_close() (xaoclose)

3. it could be possible to call the logic of cx_Oracle.connect() and conn.close() inside lixa.tx_open() and lixa.tx_close()
The proposed use case would be something like this:
import lixa
import cx_Oracle
import ...        (other Resource Manager modules)
lixa.tx_open()    ... create Transaction Manager context, 
                  open Resource Managers, 
                  call cx_Oracle.connect logic (without OCIEnvNlsCreate)
conn = lixa.lixa_ora_get_conn()
... some stuff ...
lixa.tx_begin()
... insert/update/delete using conn object ...
lixa.tx_commit()
lixa.tx_close()   ... destroy Transaction Manager context, 
                  close Resource Manager connections, destroy 
                  conn connection object calling 
                  conn.close (cx_Oracle logic)

Option 3 requires more integration between LIXA and cx_Oracle object but could be more acceptable from the development community.

What's your opinions and suggestions?

If you were interested in, you could look at a previous discussion in Psycopg2 mailing list about the same topic (using PostgreSQL instead of Oracle):
http://archives.postgresql.org/psycopg/2012-02/msg00000.php
I'm trying to discuss the same topic in MySQLDB forum too:
https://sourceforge.net/projects/mysql-python/forums/forum/70461/topic/5035738

Thanks in advance for your help.
Regards,
Ch.F.

-------------------------------------------------------------------
Decent workarounds outperform poor solutions