[Zope-DB] Zope database connectivity

[Maciej Wisniowski]

 > You could recode DA's to close the connection at the end of every
 > transaction and reopen them on demand,
Seems for me that difference betwen "connect on load"
and "connect on demand" is in _begin, _finish and
__init__ methods of TransactionManager (usually defined in db.py
in specific database adapter).

 > but to the best of my knowledge
 > none of the DAs work that way.
ZSQLRelayDA opens connections in _begin method and
closes them in _finish method of it's TransactionManager.
It seems to be "on demand" scenario for me.

 > The "connect on load" which most other DAs implement can
 > cause problems in some situations e.g. if the
 > connection to the database is down or not available
 > for some reason, but you still want to edit the connection
 > object via the ZMI.
It may cause problems too when you kill (on the server side)
your opened connection. This connection will stay broken on the
client (Zope), because some adapters (at last DCOracle2)
doesn't have ability to reconnect in this situation.

 > Most of the open buttons on database connections are pretty useless --
 > they just make the connection open early.  Most DA's open a connection
 > on demand.  For "Most" read "all" because I don't know of one that
 > DOESNT work that way.
The thing I am not sure are volatile attributes
_v_database_connection. AFAIR volatile atributes are
defined for connections to ZODB, so they're independent
between ZODB connestions. So... in "connect on load"
scenario you may have several connections to database
opened in the same time - the number of connections
to relational database may be even same as the number of
connections to ZODB. Am I right???.
If yes, then default 'Close connection' button
implementation is useless too because it closes only one
self._v_database_connection that was used for Close operation.


 > "Connect on demand" also tends to save resources and plays
 > well with connections that time out after a while.
Isn't it slower to open and close connection all the time
instead of using already opened connection?


As far as I understand whole process it is something like
(if you want, then please, correct me):

----------------------------------------------------------------
1. Demand appears (somebody request a query execution).
For example ZSQLMethod is run. ZSQLMethod inherits from
Shared.DC.ZRDB.DA.DA so Shared.DC.ZRDB.DA.DA.__call__
is executed. It may also be a ZPublisher which executes
Shared.DC.ZRDB.DA.DA.__call__ (???)

2. Shared.DC.ZRDB.DA.DA.__call__ finds
Shared.DC.ZRDB.Connection.Connection object which is
simply a DatabaseConnection visible in ZMI.

3. When Shared.DC.ZRDB.Connection.Connection is found
it is executed (__call__) and supposed to return
instance of Shared.DC.ZRDB.TM.TM
(or rather it's descendant defined in specific database
adapter like SQLRelayDA or DCoracle2)

4. Shared.DC.ZRDB.Connection.Connection.__call__
checks whether it has instance of Shared.DC.ZRDB.TM.TM and
if so it is returned. Otherwise instance of
Shared.DC.ZRDB.TM.TM is created, stored in
volatile attribute this way:

self._v_database_connection=DB(s)

and returned.

According to M.-A. Lemburg we have two scenarios here:

a) "connect on load" scenario
constructor of class DA(Shared.DC.ZRDB.TM.TM) creates
a connection to database like DCOracle2:

def __init__(self,connection_string):
    self.connection_string=connection_string
    db=self.db=DCOracle2.connect(connection_string)
    self.cursor=db.cursor()

which means we have opened connection to database which
is held in volatile _v_database_connection.

b) "connect on demand" scenario
Constructor of class DA(Shared.DC.ZRDB.TM.TM) doesn't
create a connection to database. Like SQLRelayDA which
only stores necessary parameters in __init__

5. Shared.DC.ZRDB.DA.DA.__call__
executes 'query' function on connection object retured
by instance of Shared.DC.ZRDB.Connection.Connection.

6. Transaction manager is being registered - function _begin
of transaction manager is called. It may be defined like:
def _begin(self):
    self.con = PySQLRDB.connect(self.host, self.port,
                                self.socket, self.user,
                                self.password,0,1)
    self.cur = self.con.cursor()

in SQLRelayDA (connection is created during query execution).
Or function _begin may do nothing because connection is
already stored in TransactionManager (it was created in
constructor) (DCOracle2).

7. Query is executed with database connection

8. ZPublisher commits TransactionManagers

9. Function _finish of TransactionManager is called - it may close
the database connection like SQLRelayDA:
def _finish(self, *ignored):
    self.con.commit()
    self.con.close()

or just commit a connection (DCOracle2).
----------------------------------------------------------------


-- 
Maciej Wisniowski