gnue/common/src/dbdrivers/db2 DBdriver.py

[Jason Cater]

CVSROOT:        /cvsroot/gnue
Module name:    gnue
Changes by:     Jason Cater <address@hidden>    02/01/10 16:09:51

Modified files:
        common/src/dbdrivers/db2: DBdriver.py 

Log message:
        added schema introspection support to DB2; THIS IS UNTESTED -- so it 
may need tweaking; Does not support user-defined datatypes (user-defined types 
will appear as 'text')

CVSWeb URLs:
http://savannah.gnu.org/cgi-bin/viewcvs/gnue/gnue/common/src/dbdrivers/db2/DBdriver.py.diff?tr1=1.9&tr2=1.10&r1=text&r2=text

Patches:
Index: gnue/common/src/dbdrivers/db2/DBdriver.py
diff -c gnue/common/src/dbdrivers/db2/DBdriver.py:1.9 
gnue/common/src/dbdrivers/db2/DBdriver.py:1.10
*** gnue/common/src/dbdrivers/db2/DBdriver.py:1.9       Sun Nov 11 01:11:02 2001
--- gnue/common/src/dbdrivers/db2/DBdriver.py   Thu Jan 10 16:09:51 2002
***************
*** 83,116 ****
      except self._DatabaseError, value:
        raise GDataObjects.LoginError, value
  
  
  class DB2_DataObject_Object(DB2_DataObject, \
!       DBSIG_DataObject_Object): 
  
!   def __init__(self): 
!     # Call DBSIG init first because DB2_DataObject needs to overwrite 
!     # some of its values
!     DBSIG_DataObject_Object.__init__(self) 
      DB2_DataObject.__init__(self)
  
!   def _buildQuery(self, conditions={}): 
      return DBSIG_DataObject_Object._buildQuery(self, conditions)
  
  
  class DB2_DataObject_SQL(DB2_DataObject, \
!       DBSIG_DataObject_SQL): 
    def __init__(self): 
      # Call DBSIG init first because DB2_DataObject needs to overwrite 
      # some of its values
      DBSIG_DataObject_SQL.__init__(self)
      DB2_DataObject.__init__(self)
  
!   def _buildQuery(self, conditions={}): 
      return DBSIG_DataObject_SQL._buildQuery(self, conditions)
  
  
  
! supportedDataObjects = { 
    'object': DB2_DataObject_Object,
    'sql':    DB2_DataObject_SQL
  }
--- 83,220 ----
      except self._DatabaseError, value:
        raise GDataObjects.LoginError, value
  
+   #
+   # Schema (metadata) functions
+   #
+ 
+   # Return a list of the types of Schema objects this driver provides
+   def getSchemaTypes(self):
+     return [ ('table',    'Table',1),
+              ('view',     'View', 1),
+              ('alias',    'Alias',1),
+              ('summary',  'Summary Table',1) ]
+ 
+ 
+   # Return a list of Schema objects
+   def getSchemaList(self, type=None):
+ 
+     where_user = ""
+     if type == None:
+       where_type = ['A','S','T','V']
+     else:
+       where_type = [string.upper(type[0])]
+ 
+ 
+     statement = \
+       "select tabschema||'.'||tabname||'.'||type full_name, " + \
+         "tabschema||'.'||tabname table_name, " + \
+         "type table_type " + \
+         "from syscat.tables where type in ('%s') and status = 'N' %s " \
+               % (string.join(where_type,"','"), where_user) + \
+           "order by tabname "
+ 
+     GDebug.printMesg(5,statement)
+ 
+     cursor = self._dataConnection.cursor()
+     cursor.execute(statement)
+ 
+     list = []
+     for rs in cursor.fetchall():
+       list.append(GDataObjects.Schema(attrs={'id':string.lower(rs[0]), 
'name':rs[1],
+                          'type':rs[2]},
+                          getChildSchema=self.__getFieldSchema))
+ 
+     cursor.close()
+     return list
+ 
+ 
+   # Find a schema object with specified name
+   def getSchemaByName(self, name, type=None):
+      return None
+ 
+ 
+   # Get fields for a table
+   def __getFieldSchema(self, parent):
+ 
+     # TODO: This does not support user-defined datatypes...
+     # TODO: it will always report such as TEXT-like fields.
+ 
+     schema, name, type = string.split(parent.id,'.')
+ 
+     cursor = self._dataConnection.cursor()
+ 
+     if type == 'a':
+       statement = "select base_tabschema, base_tabname " + \
+                   "from syscat.tables " + \
+                   "where tabschema = '%s' and tabname='%s'" % (schema, name)
+ 
+       GDebug.printMesg(5,statement)
+ 
+       cursor.execute(statement)
+       rs = cursor.fetchone()
+       schema, name = rs
+ 
+     statement = \
+        "select tabschema||'.'||tabname||'.'||colname, " + \
+        "colname, typename, nulls, length, scale, typeschema " + \
+        "from syscat.columns" + \
+        "where tabschema = '%s' and tabname = '%s' " % (schema, name) + \
+        "order by colno"
+ 
+     GDebug.printMesg(5,statement)
+ 
+     cursor.execute(statement)
+ 
+     list = []
+     for rs in cursor.fetchall():
+ 
+       attrs={'id': rs[0], 'name': rs[1],
+              'type':'field', 'nativetype': rs[2],
+              'required': rs[3] == 'Y'}
+ 
+       if rs[2] in ('SMALLINT','INTEGER','INT','FLOAT','DOUBLE',
+                    'DECIMAL','BIGINT','REAL','DEC','NUMERIC','NUMBER',
+                    'NUM'):
+         attrs['precision'] = rs[5]
+         attrs['datatype'] = 'number'
+       elif rs[2] in ('DATE','TIME','TIMESTAMP'):
+         attrs['datatype'] = 'date'
+       else:
+         attrs['datatype'] = 'text'
+ 
+       if rs[5] != 0:
+         attrs['length'] = rs[4]
+ 
+       list.append(GDataObjects.Schema(attrs=attrs))
+ 
+     cursor.close()
+     return tuple(list)
+ 
  
  class DB2_DataObject_Object(DB2_DataObject, \
!       DBSIG_DataObject_Object):
  
!   def __init__(self):
      DB2_DataObject.__init__(self)
  
!   def _buildQuery(self, conditions={}):
      return DBSIG_DataObject_Object._buildQuery(self, conditions)
  
  
  class DB2_DataObject_SQL(DB2_DataObject, \
!       DBSIG_DataObject_SQL):
    def __init__(self): 
      # Call DBSIG init first because DB2_DataObject needs to overwrite 
      # some of its values
      DBSIG_DataObject_SQL.__init__(self)
      DB2_DataObject.__init__(self)
  
!   def _buildQuery(self, conditions={}):
      return DBSIG_DataObject_SQL._buildQuery(self, conditions)
  
  
  
! supportedDataObjects = {
    'object': DB2_DataObject_Object,
    'sql':    DB2_DataObject_SQL
  }