[DB-SIG] Prototype Zope mxODBC DA

Andrew M. Kuchling akuchlin@mems-exchange.org
Mon, 11 Oct 1999 16:42:23 -0400 (EDT)


Last week we switched our development server from Zope 1 to Zope 2,
and found that it broke the old Solid database adapter.  SELECTs
worked up, but operations that changed the database left an open
transaction dangling.  Today I set about trying to turn the Z Oracle
Database Adapter into a DA for the mxODBC package.

It proved unexpectedly simple, and the results seem to be working
fine.  (Is there a test suite or test procedure that I could use to
verify the level of compliance.  ZOracleDA is Level 3 compliant,
according to http://www.zope.org/Members/petrilli/DARoadmap; I don't
know if the mxODBC DA is too.)

The changes to the Z Oracle DA are:

     * Blow away the src/ and DCOracle subdirectories; I've assumed
that mxODBC is already installed.  

     * mxODBC wants mxDateTime, which uses the package name
'DateTime'.  This conflicts with Zope's DateTime package.
<sigh> The fix, luckily, is simple: edit mxDateTime.h in the Solid
subdirectory of mxODBC, to import mx.DateTime, and install mxDateTime
accordingly.

     * Apply the patches below.  The bulk of the patch is just simple
renaming; the only real changes are to import ODBC.Solid, to parse
apart the connection string (mxODBC wants three arguments, not just a
single string).

Outstanding bugs: the icon for the DA object is broken, and I can't
figure out why, and the database browser still doesn't work.

Anyway, the next step is to make the mxODBC DA not dependent on Solid,
but capable of using Adabas or any other database supported by mxODBC.

Question for Christopher Petrilli: because the patches from the Oracle
DA are so small, does merging the mxODBC and Oracle DAs into one code
base seem feasible?

Follow-ups set to zope-dev@zope.org.

-- 
A.M. Kuchling			http://starship.python.net/crew/amk/
Kids! Bringing about Armageddon can be dangerous. Do not attempt it in your
home.
    -- Terry Pratchett & Neil Gaiman, _Good Omens_


diff -r -C2 ZOracleDA/DA.py mxODBCda/DA.py
*** ZOracleDA/DA.py	Fri Jan 15 14:36:09 1999
--- mxODBCda/DA.py	Mon Oct 11 16:18:30 1999
***************
*** 95,99 ****
  # 
  ##############################################################################
! database_type='Oracle'
  __doc__='''%s Database Connection
  
--- 95,99 ----
  # 
  ##############################################################################
! database_type='mxODBC'
  __doc__='''%s Database Connection
  
***************
*** 107,113 ****
  from ExtensionClass import Base
  
! manage_addZOracleConnectionForm=HTMLFile('connectionAdd',globals())
  
! def manage_addZOracleConnection(self, id, title,
                                  connection_string,
                                  check=None, REQUEST=None):
--- 107,113 ----
  from ExtensionClass import Base
  
! manage_addZmxODBCConnectionForm=HTMLFile('connectionAdd',globals())
  
! def manage_addZmxODBCConnection(self, id, title,
                                  connection_string,
                                  check=None, REQUEST=None):
***************
*** 138,151 ****
  
  folder_methods={
!     'manage_addZOracleConnection':
!     manage_addZOracleConnection,
!     'manage_addZOracleConnectionForm':
!     manage_addZOracleConnectionForm,
      }
  
  __ac_permissions__=(
!     ('Add Z Oracle Database Connections',
!      ('manage_addZOracleConnectionForm',
!       'manage_addZOracleConnection')),
      )
  
--- 138,151 ----
  
  folder_methods={
!     'manage_addZmxODBCConnection':
!     manage_addZmxODBCConnection,
!     'manage_addZmxODBCConnectionForm':
!     manage_addZmxODBCConnectionForm,
      }
  
  __ac_permissions__=(
!     ('Add mxODBC Database Connections',
!      ('manage_addZmxODBCConnectionForm',
!       'manage_addZmxODBCConnection')),
      )
  
diff -r -C2 ZOracleDA/__init__.py mxODBCda/__init__.py
*** ZOracleDA/__init__.py	Fri Jan 15 13:09:48 1999
--- mxODBCda/__init__.py	Mon Oct 11 15:12:29 1999
***************
*** 95,99 ****
  # 
  ##############################################################################
! __doc__='''Generic Database Adapter Package Registration
  
  $Id: __init__.py,v 1.3 1999/01/15 18:09:48 jim Exp $'''
--- 95,99 ----
  # 
  ##############################################################################
! __doc__='''mxODBC Database Adapter Package Registration
  
  $Id: __init__.py,v 1.3 1999/01/15 18:09:48 jim Exp $'''
diff -r -C2 ZOracleDA/browse.dtml mxODBCda/browse.dtml
*** ZOracleDA/browse.dtml	Fri Jan 15 13:00:58 1999
--- mxODBCda/browse.dtml	Mon Oct 11 16:17:25 1999
***************
*** 4,8 ****
      <!--#var manage_tabs-->
      <!--#tree header=info-->
!       <IMG SRC="<!--#var SCRIPT_NAME-->/misc_/ZOracleDA/<!--#var icon-->"
         ALT="<!--#var Type-->" BORDER="0">
        <!--#var Name--><!--#var Description-->
--- 4,8 ----
      <!--#var manage_tabs-->
      <!--#tree header=info-->
!       <IMG SRC="<!--#var SCRIPT_NAME-->/misc_/ZmxODBCDA/<!--#var icon-->"
         ALT="<!--#var Type-->" BORDER="0">
        <!--#var Name--><!--#var Description-->
diff -r -C2 ZOracleDA/connectionAdd.dtml mxODBCda/connectionAdd.dtml
*** ZOracleDA/connectionAdd.dtml	Fri Jan 15 13:00:58 1999
--- mxODBCda/connectionAdd.dtml	Mon Oct 11 16:17:54 1999
***************
*** 1,9 ****
  <html>
!   <head><title>Add Z Oracle Database Connection</title></head>
    <body bgcolor="#FFFFFF" link="#000099" vlink="#555555" alink="#77003B">
    
!     <h2>Add Z Oracle Database Connection</h2>
      
!     <form action="manage_addZOracleConnection" method="POST">
        <table cellspacing="2">
  	<tr>
--- 1,9 ----
  <html>
!   <head><title>Add Z mxODBC Database Connection</title></head>
    <body bgcolor="#FFFFFF" link="#000099" vlink="#555555" alink="#77003B">
    
!     <h2>Add Z mxODBC Database Connection</h2>
      
!     <form action="manage_addZmxODBCConnection" method="POST">
        <table cellspacing="2">
  	<tr>
***************
*** 11,15 ****
  	  <td align="LEFT" valign="TOP">
              <input type="TEXT" name="id" size="40"
!                 value="Oracle_database_connection">
            </td>
  	</tr>
--- 11,15 ----
  	  <td align="LEFT" valign="TOP">
              <input type="TEXT" name="id" size="40"
!                 value="mxODBC_database_connection">
            </td>
  	</tr>
***************
*** 18,22 ****
  	  <td align="LEFT" valign="TOP">
              <input type="TEXT" name="title" size="40"
!                 value="Z Oracle Database Connection">
            </td>
  	</tr>
--- 18,22 ----
  	  <td align="LEFT" valign="TOP">
              <input type="TEXT" name="title" size="40"
!                 value="mxODBC Database Connection">
            </td>
  	</tr>
***************
*** 47,52 ****
  <dd>
  <p>
!     The connection string used for Z Oracle Database Connection
!     are exactly the same connection strings required by Oracle tools. 
      The connection strings are typically of the form:
  <pre>
--- 47,52 ----
  <dd>
  <p>
!     The connection string used for Z mxODBC Database Connections
!     vary depending on the database being used.
      The connection strings are typically of the form:
  <pre>
diff -r -C2 ZOracleDA/db.py mxODBCda/db.py
*** ZOracleDA/db.py	Fri Aug 20 10:47:44 1999
--- mxODBCda/db.py	Mon Oct 11 16:27:41 1999
***************
*** 99,108 ****
  __version__='$Revision: 1.5 $'[11:-2]
  
! import DCOracle, DateTime
! DCOracle.dbi.dbiDate=DateTime.DateTime
  from Shared.DC.ZRDB.TM import TM
  
  import string, sys
! from string import strip, split, find
  from time import time
  
--- 99,109 ----
  __version__='$Revision: 1.5 $'[11:-2]
  
! import ODBC.Solid
! dbModule = ODBC.Solid
! 
  from Shared.DC.ZRDB.TM import TM
  
  import string, sys
! from string import strip, split, find, join
  from time import time
  
***************
*** 115,124 ****
      _p_oid=_p_changed=_registered=None
  
!     Database_Connection=DCOracle.Connect
!     Database_Error=DCOracle.error
  
      def __init__(self,connection):
          self.connection=connection
!         db=self.db=self.Database_Connection(connection)
          self.cursor=db.cursor()
  
--- 116,130 ----
      _p_oid=_p_changed=_registered=None
  
!     Database_Connection=dbModule.Connect
!     Database_Error=dbModule.error
  
      def __init__(self,connection):
          self.connection=connection
!         # XXX this is really only correct for Solid
!         L = split(connection)
!         connstr = join(L[0:3], ' ')
!         user = L[3] ; password=L[4]
!         db=self.db=self.Database_Connection(connstr, user, password)
! #        db=self.db=self.Database_Connection(connection)
          self.cursor=db.cursor()
  
***************
*** 127,134 ****
  
      def _finish(self):
!         self.db.execute('commit')
  
      def _abort(self):
!         self.db.execute('rollback')
          
      def str(self,v, StringType=type('')):
--- 133,140 ----
  
      def _finish(self):
!         self.db.commit()
  
      def _abort(self):
!         self.db.rollback()
          
      def str(self,v, StringType=type('')):
***************
*** 152,159 ****
  
      def tables(self, rdb=0,
!                _care=('TABLE', 'VIEW')):
          r=[]
          a=r.append
!         for name, typ in self.db.objects():
              if typ in _care:
                  a({'TABLE_NAME': name, 'TABLE_TYPE': typ})
--- 158,167 ----
  
      def tables(self, rdb=0,
!                _care=('BASE TABLE', 'VIEW')):
          r=[]
          a=r.append
!         try: r=c.execute('select table_name, table_type from sys_tables')
!         except: return ()
!         for name, typ in c.fetchall():
              if typ in _care:
                  a({'TABLE_NAME': name, 'TABLE_TYPE': typ})