gnue/gnue-common/src/dbdrivers cxoracle/DBdrive...

[Jason Cater]

CVSROOT:        /home/cvs
Module name:    gnue
Changes by:     Jason Cater <address@hidden>    01/11/06 16:57:41

Modified files:
        gnue-common/src/dbdrivers/cxoracle: DBdriver.py 
        gnue-common/src/dbdrivers/oracle: DBdriver.py 

Log message:
        Added basic introspection support to Oracle drivers

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

Patches:
Index: gnue/gnue-common/src/dbdrivers/cxoracle/DBdriver.py
diff -u gnue/gnue-common/src/dbdrivers/cxoracle/DBdriver.py:1.3 
gnue/gnue-common/src/dbdrivers/cxoracle/DBdriver.py:1.4
--- gnue/gnue-common/src/dbdrivers/cxoracle/DBdriver.py:1.3     Sun Nov  4 
17:22:34 2001
+++ gnue/gnue-common/src/dbdrivers/cxoracle/DBdriver.py Tue Nov  6 16:57:41 2001
@@ -81,27 +81,118 @@
     self._postConnect()
 
 
+  def _postConnect(self):
+    self.triggerExtensions = TriggerExtensions(self._dataConnection)
 
   #
   # Schema (metadata) functions
   #
 
-  # TODO: See postgresql for an example of what these functions do.
-
   # Return a list of the types of Schema objects this driver provides
   def getSchemaTypes(self):
-    return [('view','View',1), ('table','Table',1)]
+    return [ ('user_table',   'User Table',1),
+             ('user_view',    'User View',1), 
+             ('user_synonym', 'User Synonym',1),
+             ('all_table',    'System Table',1),
+             ('all_view',     'System View',1),
+             ('all_synonym',  'System Synonym',1) ]
 
   # Return a list of Schema objects
   def getSchemaList(self, type=None):
-    return []
+
+    where_user = ""
+    if type == None: 
+      where_type = ['TABLE', 'VIEW', 'SYNONYM']
+    else: 
+      scope, type = string.split(type,'_')
+      where_type = [string.upper(type)]
+      if scope == 'user': 
+        where_user = " AND OWNER = USER"
+    
+
+    statement = \
+      "select owner||'.'||table_name||'.'||table_type full_name, " + \
+        "decode(owner,user,null,owner||'.')||table_name table_name, " + \
+        "decode(owner,user,'user_','all_')||lower(table_type) table_type " + \
+        "from all_catalog where table_type in ('%s') %s " \
+              % (string.join(where_type,"','"), where_user) + \
+          "order by table_name "
+
+    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
+     return None
 
-  def _postConnect(self):
-    self.triggerExtensions = TriggerExtensions(self._dataConnection)
+
+  # Get fields for a table
+  def __getFieldSchema(self, parent):
+
+    owner, name, type = string.split(parent.id,'.')
+
+    cursor = self._dataConnection.cursor()
+
+    if type == 'SYNONYM': 
+      statement = "select table_owner, table_name, " + \
+                  "decode(db_link,null,null,'@'||db_link) name " + \
+                  "from all_synonyms " + \
+                  "where owner = '%s' and synonym_name='%s'" % (owner, name)
+
+      GDebug.printMesg(5,statement)
+
+      cursor.execute(statement)
+      rs = cursor.fetchone()
+      owner, name, link = rs
+    else: 
+      link = ""
+
+    statement = \
+       "select owner||'.'||table_name||'.'||column_name||'.%s', " % (link) + \
+       "column_name, data_type, nullable, data_length, data_scale " + \
+       "from all_tab_columns%s " % (link) + \
+       "where owner = '%s' and table_name = '%s' " % (owner, name) + \
+       "order by column_id"
+
+    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 ('NUMBER','LONG','LONG RAW'):
+        attrs['precision'] = rs[5]
+        attrs['datatype'] = 'number'
+      elif rs[2] in ('DATE'):
+        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 Oracle_DataObject_Object(Oracle_DataObject, \
Index: gnue/gnue-common/src/dbdrivers/oracle/DBdriver.py
diff -u gnue/gnue-common/src/dbdrivers/oracle/DBdriver.py:1.9 
gnue/gnue-common/src/dbdrivers/oracle/DBdriver.py:1.10
--- gnue/gnue-common/src/dbdrivers/oracle/DBdriver.py:1.9       Sun Nov  4 
17:22:35 2001
+++ gnue/gnue-common/src/dbdrivers/oracle/DBdriver.py   Tue Nov  6 16:57:41 2001
@@ -32,7 +32,7 @@
 #     service=    This is the Oracle TNS name for your connection  (required)
 #
 
-from string import lower
+import string
 import sys
 from gnue.common import GDebug, GDataObjects, GConnections
 from gnue.common.dbdrivers._dbsig.DBdriver \
@@ -43,7 +43,7 @@
   import DCOracle2 as SIG2api
 except ImportError, message:
   raise GConnections.AdapterNotInstalled, \
-         "Driver not installed: cx_Oracle [%s]" % message
+         "Driver not installed: DCOracle2 [%s]" % message
 
 
 
@@ -81,31 +81,118 @@
     self._postConnect()
 
 
+  def _postConnect(self):
+    self.triggerExtensions = TriggerExtensions(self._dataConnection)
+
 
   #
   # Schema (metadata) functions
   #
 
-  # TODO: See postgresql for an example of what these functions do.
-
   # Return a list of the types of Schema objects this driver provides
   def getSchemaTypes(self):
-    return [('view','View',1), ('table','Table',1)]
+    return [ ('user_table',   'User Table',1),
+             ('user_view',    'User View',1), 
+             ('user_synonym', 'User Synonym',1),
+             ('all_table',    'System Table',1),
+             ('all_view',     'System View',1),
+             ('all_synonym',  'System Synonym',1) ]
 
   # Return a list of Schema objects
   def getSchemaList(self, type=None):
-    return []
 
+    where_user = ""
+    if type == None: 
+      where_type = ['TABLE', 'VIEW', 'SYNONYM']
+    else: 
+      scope, type = string.split(type,'_')
+      where_type = [string.upper(type)]
+      if scope == 'user': 
+        where_user = " AND OWNER = USER"
+    
+
+    statement = \
+      "select owner||'.'||table_name||'.'||table_type full_name, " + \
+        "decode(owner,user,null,owner||'.')||table_name table_name, " + \
+        "decode(owner,user,'user_','all_')||lower(table_type) table_type " + \
+        "from all_catalog where table_type in ('%s') %s " \
+              % (string.join(where_type,"','"), where_user) + \
+          "order by table_name "
+
+    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
+     return None
 
-  def _postConnect(self):
-    self.triggerExtensions = TriggerExtensions(self._dataConnection)
 
+  # Get fields for a table
+  def __getFieldSchema(self, parent):
 
-  def _postConnect(self):
-    self.triggerExtensions = TriggerExtensions(self._dataConnection)
+    owner, name, type = string.split(parent.id,'.')
+
+    cursor = self._dataConnection.cursor()
+
+    if type == 'SYNONYM': 
+      statement = "select table_owner, table_name, " + \
+                  "decode(db_link,null,null,'@'||db_link) name " + \
+                  "from all_synonyms " + \
+                  "where owner = '%s' and synonym_name='%s'" % (owner, name)
+
+      GDebug.printMesg(5,statement)
+
+      cursor.execute(statement)
+      rs = cursor.fetchone()
+      owner, name, link = rs
+    else: 
+      link = ""
+
+    statement = \
+       "select owner||'.'||table_name||'.'||column_name||'.%s', " % (link) + \
+       "column_name, data_type, nullable, data_length, data_scale " + \
+       "from all_tab_columns%s " % (link) + \
+       "where owner = '%s' and table_name = '%s' " % (owner, name) + \
+       "order by column_id"
+
+    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 ('NUMBER','LONG','LONG RAW'):
+        attrs['precision'] = rs[5]
+        attrs['datatype'] = 'number'
+      elif rs[2] in ('DATE'):
+        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 Oracle_DataObject_Object(Oracle_DataObject, \