Re: Psycopg2 and LIXA - Mailing list psycopg

Daniele & Federico,
in the past few days we have discussed about a possible integration between LIXA and Psycopg2.

>>  I assume the function should be called with a Python interpreter well
>>  initialized, but if lixa is imported by a Python process and the above
>>  code is called by a function wrapped in a Python call I assume all the
>>  conditions required are met. Of course everything is pretty
>>  experimental: psycopg doesn't offer (yet) a C api, nor the import
>>  file are designed to be used outside the library: if the approach
>>  works we can provide a Python C API properly done.
>
> That's what I was thinking too but then psycopg users will lose custom
> connection (and cursor) objects. :/ IMHO the best approach would be for LIXA to
> extend its concept of "profile" to include connection factories and
> let Python code to register custom profiles and connection factories. I
> understand that is probably alien to what LIXA does now but would increase
> acceptance by Pythonistas.


After some replies, two possible solutions emerged:

1. the LIXA Transaction Manager open the connection(s) to the database(s) and pass it/them to Python libraries with a
dedicatedmethod. LIXA could open a DB API connection type (Python) and map it to C for its own usage; casting a native
Cconnection type (PGconn *) seems the worst case for Psycopg2 integration. 

PROS:
a. the role of the Transaction Manager is not subverted: the T.M. manages the connections to the Resource Managers

b. LIXA logic requires minor technical updates, necessary to deal with a cross-language environment (C/Python), but no
functionalchange is involved 
c. Python developers could use familiar connectionObject objects

CONS:
c. Python developers must change a little of their logic when using the LIXA Transaction Manager (it happens to C/C++
programmersfor Encina/TXSeries/Tuxedo and Java programmers for Jboss, WebSphere, WebLogic, any JEE application server) 

2. the Application Program opens the connection(s) to the database(s) and pass it/them to LIXA Transaction Manager.
LIXAshould register the connections instead of directly manage them; the connection would be "connectionObject". 
PROS:
a. highest acceptance level from Python community
CONS:
b. changing the role of the Transaction Manager and the Application Program with respect of well consolidated DTP
environmentslike C and Java could be incautious: some consistency and/or robustness issues might emerge in the future
becauseit's basically a not explored battlefield (the "happy path" seems to work, but guessing what happens when a
crashedsystem restarts is a completely different matter) 
c. LIXA logic requires a lot of rework: a concept of "dynamic profile" is probably necessary; the logic related to
consistencyand isolation must be re-designed to avoid undesired automatic recoveries 

d. some XA compliant Resource Managers could complain: when you use Oracle and DB2 for XA Distributed Transaction
Processing,you will not open a connection using the standard function, you will use a specific XA function (xa_open)
availableinside the "XA switch file" (a dynamically loaded module) supplied by the vendor. It may be the functions are
interchangeable,it may not: only experiments will give the right answer. Unfortunately, dealing with proprietary
softwareoutside documented use cases (xa_open usage) could be a nightmare. 

 I do think the discussion was really interesting and a lot of important information was retrieved.

LIXA can not live alone, nor it can live supporting only PostgreSQL: by design LIXA must integrate with as many
ResourceManagers as possible. LIXA must support as many programming languages as possible, too. 

I think it's time to suspend this discussion and transfer it to the teams are developing equivalent modules for other
ResourceManagers. 
In the C/C++ environment, LIXA works with PostgreSQL, MySQL, Oracle and DB2; I will poll MySQL, Oracle and DB2 Python
guysto pick-up their feedbacks. 

Stay tuned, I will come back in the next few days/weeks.

Ch.F.