MySQLdb and LIXA integration

Help
2012-02-15
2012-09-19
  • 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 MySQLdb.

    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 MySQL as a Resource Manager and allows to create distributed
    transaction (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 MySQLdb is a good start point because it wraps the MySQL C API;
    LIXA too wraps mysql.h

    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)

    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 standards specifies the Resource Managers must be opened by the Transaction
    Manager using "tx_open()" by the Application Program. This is the key point:
    MySQLdb supplies its own method to get a valid MySQL session:
    "_mysql.connect(...)", while LIXA wants to create it using "lixa.tx_open()":
    the Transaction Manager must open all the Resource Managers, not only MySQL.

    Using C as the development language, LIXA supplies some convenience methods:

    MYSQL *lixa_my_get_conn_by_rmid(int rmid);

    MYSQL *lixa_my_get_conn_by_pos(int pos);

    MYSQL *lixa_my_get_conn(void);

    if there is only one MySQL database, the third one is good enought. Using the
    convenience method, the C program becomes:

    / open the resource manager(s) /

    if (TX_OK != (txrc = tx_open())) {

    fprintf(stderr, "tx_open error: %d\n", txrc);

    exit(txrc);

    }

    / retrieve MySQL connection /

    conn = lixa_my_get_conn();

    / start a new transaction /

    if (TX_OK != (txrc = tx_begin())) {

    fprintf(stderr, "tx_begin error: %d\n", txrc);

    exit(txrc);

    }

    the Application Program does not use "mysql_connect(...)": the Transaction
    Manager calls it inside "tx_open()" and the Application Program retrieves the
    connection using a convenience method (lixa_my_get_conn).

    The same when the connection must be closed using "tx_close()".

    Translated to Python, it becomes:

    tiian@mojan:~/src/swig$ python

    Python 2.6.5 (r265:79063, Apr 16 2010, 13:09:56)

    on linux2

    Type "help", "copyright", "credits" or "license" for more information.

    import lixa

    lixa.tx_open()

    0

    conn = lixa.lixa_my_get_conn()

    print conn

    Coming back to MySQLdb,

    _mysql.connect(...)

    does not return a simple handler to a MySQL connection, but a complex object
    with additional methods and properties as specified by DB API.

    Thinking about integration between LIXA and MySQLdb I'm proposing three
    different paths:

    1. patching MySQLdb substituting "mysql_connect(...)" with "lixa_my_get_conn": I don't like this solution because the patch must be managed and tested every time MySQLdb releases something new; I don't think it would be well accepted by the users too

    2. it could be easy to overload the "_mysql.connect()" method: if it accepted something like a wrapped "MYSQL *" too, the integration would be something like:
      _mysql.connect( lixa.lixa_pq_get_conn() )

    The proposed use case would be something like this:

    import lixa

    import _mysql

    import ... (other Resource Manager modules)

    lixa.tx_open() ... create Transaction Manager context and open Resource
    Managers

    lico = lixa.lixa_my_get_conn()

    conn = _mysql.connect( some_function(lico) )

    ... some stuff ...

    lixa.tx_begin()

    ... insert/update/delete using conn object ...

    lixa.tx_commit()

    db.close() ... it should destroy the Python object without calling
    mysql_close()

    lixa.tx_close() ... destroy Transaction Manager context and close Resource
    Manager connections

    1. it could be possible to call the logic of _mysql.connect() and conn.close() inside lixa.tx_open() and lixa.tx_close()
      The proposed use case would be something like this:

    import lixa

    import _mysql

    import ... (other Resource Manager modules)

    lixa.tx_open() ... create Transaction Manager context, open Resource Managers,
    call _mysql.connect logic (without mysql_connect)

    conn = lixa.lixa_my_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
    (MySQLdb logic)

    Option 3 requires more integration between LIXA and MySQLdb 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 issue (using PostgreSQL instead of MySQL):

    http://archives.postgresql.org/psycopg/2012-02/msg00000.php

    Thanks in advance for your help.

    Regards,

    Ch.F.