r5943 - trunk/gnue-common/src/datasources/drivers/postgresql/Schema/Discovery

[johannes]

Author: johannes
Date: 2004-07-06 03:45:14 -0500 (Tue, 06 Jul 2004)
New Revision: 5943

Modified:
   
trunk/gnue-common/src/datasources/drivers/postgresql/Schema/Discovery/Introspection.py
Log:
Created a 'reference-implementation' for introspection classes; converted to 
the find-interface ()


Modified: 
trunk/gnue-common/src/datasources/drivers/postgresql/Schema/Discovery/Introspection.py
===================================================================
--- 
trunk/gnue-common/src/datasources/drivers/postgresql/Schema/Discovery/Introspection.py
      2004-07-05 15:06:39 UTC (rev 5942)
+++ 
trunk/gnue-common/src/datasources/drivers/postgresql/Schema/Discovery/Introspection.py
      2004-07-06 08:45:14 UTC (rev 5943)
@@ -18,172 +18,194 @@
 #
 # Copyright 2000-2004 Free Software Foundation
 #
-# FILE:
-# Introspection.py
-#
-# DESCRIPTION:
-#
-# NOTES:
-#
+# $Id: $
 
 __all__ = ['Introspection']
 
 import string
-from string import lower, join, split
-import sys
 
-
-from gnue.common.apps import GDebug, GConfig
 from gnue.common.datasources import GIntrospection
 
+# =============================================================================
+# This class implements schema introspection for PostgreSQL backends
+# =============================================================================
 
 class Introspection(GIntrospection.Introspection):
 
+
   # list of the types of Schema objects this driver provides
-  types =[ ('view',_('Views'),1),
-           ('table',_('Tables'),1) ]
+  types = [ ('view' , _('Views') , 1),
+            ('table', _('Tables'), 1) ]
 
-  #
-  # TODO: This is a quick hack to get this class
-  # TODO: into the new-style schema format.
-  # TODO: getSchema* should be merged into find()
-  #
-  def find(self, name=None, type=None):
-    if name is None:
-      return self.getSchemaList(type)
-    else:
-      rs = self.getSchemaByName(self._connection.identifier (name), type)
-      if rs:
-        return [rs]
-      else:
-        return None
 
+  # ---------------------------------------------------------------------------
+  # Find a schema element by name and/or type
+  # ---------------------------------------------------------------------------
 
-  # TODO: Merge into find()
-  # Return a list of Schema objects
-  def getSchemaList(self, type=None):
-    includeTables = (type in ('table','sources', None))
-    includeViews = (type in ('view','sources', None))
+  def find (self, name = None, type = None):
+    """
+    This function searches the schema for an element by name and/or type. If no
+    name and no type is given, all elements will be retrieved.
 
-    inClause = []
-    if includeTables:
-      inClause.append ("'r'")
-    if includeViews:
-      inClause.append ("'v'")
+    @param name: look for an element with this name
+    @param type: look for an element with this type
+    @return: A sequence of schema instances, one per element found, or None if
+        no element could be found.
+    """
 
-    # TODO: This excludes any system tables and views. Should it?
-    statement = "select relname, relkind, oid from pg_class " + \
-            "where relkind in (%s) " % (join(inClause,',')) + \
-            "and relname not like 'pg_%' " + \
-            "order by relname"
+    result = []
+    cond   = ["relname NOT LIKE 'pg_%'"]
 
-    cursor = self._connection.native.cursor()
-    cursor.execute(statement)
+    if name is not None:
+      cond = [u"relname = '%s'" % self._connection.identifier (name)]
 
-    list = []
-    for rs in cursor.fetchall():
-      list.append(GIntrospection.Schema(attrs={'id':rs[2], 'name':rs[0],
-                         'type':rs[1] == 'v' and 'view' or 'table',
-                         'primarykey': self.__getPrimaryKey(cursor, rs[2])},
-                         getChildSchema=self.__getFieldSchema))
+    reltypes = []
+    if type in ('table', 'sources', None):
+      reltypes.append ("'r'")
+    if type in ('view', 'sources', None):
+      reltypes.append ("'v'")
 
-    cursor.close()
-    return list
+    cond.append (u"relkind in (%s)" % string.join (reltypes, ","))
 
+    cmd = u"SELECT oid, relname, relkind FROM pg_class WHERE %s " \
+           "ORDER BY relname" % string.join (cond, " AND ")
 
-  # TODO: Merge into find()
-  # Find a schema object with specified name
-  def getSchemaByName(self, name, type=None):
-    statement = "select relname, relkind, oid from pg_class " + \
-            "where relname = '%s'" % (name)
+    cursor = self._connection.makecursor (cmd)
 
-    cursor = self._connection.makecursor (statement)
-    # cursor = self._connection.native.cursor()
-    # cursor.execute(statement)
+    try:
+      for rs in cursor.fetchall ():
+        attrs = {'id'  : rs [0],
+                 'name': self._connection.identifier (rs [1]),
+                 'type': rs [2] == 'v' and 'view' or 'table',
+                 'primarykey': self.__getPrimaryKey (rs [0])}
 
-    rs = cursor.fetchone()
-    if rs:
-      schema = GIntrospection.Schema(attrs={'id':rs[2], 'name':rs[0],
-                           'type':rs[1] == 'v' and 'view' or 'table',
-                           'primarykey': self.__getPrimaryKey(cursor, rs[2]) },
-                           getChildSchema=self.__getFieldSchema)
-    else:
-      schema = None
+        result.append ( \
+          GIntrospection.Schema (attrs, getChildSchema = self._getChildSchema))
 
-    cursor.close()
-    return schema
+    finally:
+      cursor.close ()
 
-  def __getPrimaryKey(self, cursor, oid):
-    cursor = self._connection.native.cursor()
-    cursor.execute("select indkey from pg_index where indrelid=%s" % oid)
-    rs = cursor.fetchone()
-    statement = "select attname from pg_attribute " \
-                "where attrelid = %s and attnum = %%s" % oid
-    if rs:
-      pks = []
-      for indpos in string.split(rs[0]):
-        cursor.execute(statement % int(indpos))
-        pks.append(cursor.fetchone()[0])
-      cursor.close()
-      return tuple(pks)
-    else:
-      cursor.close()
-      return None
+    return len (result) and result or None
 
-  # Get fields for a table
-  def __getFieldSchema(self, parent):
 
-    statement = "select attname, pg_type.oid, typname, " + \
-            " attnotnull, atthasdef, atttypmod, attnum, attlen " + \
-            "from pg_attribute, pg_type " + \
-            "where attrelid = %s and " % (parent.id) + \
-            "pg_type.oid = atttypid and attnum >= 0" + \
-            "order by attnum"
+  # ---------------------------------------------------------------------------
+  # Get all fields of a relation/view
+  # ---------------------------------------------------------------------------
 
-    cursor = self._connection.native.cursor()
-    cursor.execute(statement)
+  def _getChildSchema (self, parent):
+    """
+    This function returns a list of all child elements of a given parent
+    relation.
 
-    list = []
-    for rs in cursor.fetchall():
+    @param parent: schema object instance whose child elements should be
+        fetched.
+    @return: sequence of schema instances, one per element found
+    """
 
-      attrs={'id': rs[1], 'name': rs[0],
-             'type':'field', 'nativetype': rs[2],
-             'required': rs[3] and not rs[4]}
+    result = []
 
-      if rs[2] in 
('numeric','float4','float8','money','bool','int8','int2','int4'):
-        attrs['datatype']='number'
-      elif rs[2] in ('date','time','timestamp','abstime','reltime'):
-        attrs['datatype']='date'
-      else:
-        attrs['datatype']='text'
+    cmd = u"SELECT attrelid, attname, t.oid, t.typname, attnotnull, " \
+           "atthasdef, atttypmod, attnum, attlen " \
+           "FROM pg_attribute, pg_type t " \
+           "WHERE attrelid = %s AND attnum >= 0 AND t.oid = atttypid " \
+           "ORDER BY attnum" % parent.id
 
-      if rs[7] > 0:
-        attrs['length'] = rs[7]
-      elif rs[5] != -1: #text field
-        attrs['length'] = rs[5] - 4
+    cursor = self._connection.makecursor (cmd)
 
+    try:
+      for rs in cursor.fetchall ():
+        attrs = {'id'        : "%s.%s" % (rs [0], rs [7]),
+                 'name'      : self._connection.identifier (rs [1]),
+                 'type'      : 'field',
+                 'nativetype': rs [3],
+                 'required'  : rs [4] and not rs [5]}
 
-      # Find any default values
-      if rs[4]:
-        cursor.execute("select adsrc " + \
-                       "from pg_attrdef " + \
-                       "where adrelid = %s and adnum = %s" % (parent.id, 
rs[6]))
-        defrs = cursor.fetchone()
-        if defrs:
-          dflt = defrs[0]
-          if dflt[:8] == 'nextval(':
-            attrs['defaulttype'] = 'sequence'
-            attrs['defaultval'] = split(dflt,"'")[1]
-          elif dflt == 'now()':
-            attrs['defaulttype'] = 'system'
-            attrs['defaultval'] = 'timestamp'
-          else:
-            attrs['defaulttype'] = 'constant'
-            attrs['defaultval'] = dflt
+        if rs [3] in ('numeric', 'float4', 'float8', 'money', 'bool', 'int8',
+                      'int2', 'int4'):
+          attrs ['datatype'] = 'number'
 
-      list.append(GIntrospection.Schema(attrs=attrs))
+        elif rs [3] in ('date', 'time', 'timestamp', 'abstime', 'reltime'):
+          attrs ['datatype'] = 'date'
+        else:
+          attrs ['datatype'] = 'text'
 
-    cursor.close()
-    return list
+        if rs [8] > 0:
+          attrs ['length'] = rs [8]
 
+        elif rs [6] != -1: #text field
+          attrs ['length'] = rs [6] - 4
 
+        # If attribute has default values, we fetch them too
+        if rs [5]:
+          cmd = u"SELECT adsrc FROM pg_attrdef " \
+                 "WHERE adrelid = %s AND adnum = %s" % (parent.id, rs [7])
+          defcursor = self._connection.makecursor (cmd)
+
+          try:
+            defrs = defcursor.fetchone ()
+            if defrs:
+              default = defrs [0]
+
+              if default [:8] == 'nextval(':
+                attrs ['defaulttype'] = 'sequence'
+                attrs ['defaultval']  = default.split ("'") [1]
+
+              elif default == 'now()':
+                attrs ['defaulttype'] = 'system'
+                attrs ['defaultval']  = 'timestamp'
+              else:
+                attrs ['defaulttype'] = 'constant'
+                attrs ['defaultval']  = default.split ("::") [0]
+
+          finally:
+            defcursor.close ()
+
+        result.append (GIntrospection.Schema (attrs))
+
+    finally:
+      cursor.close ()
+
+    return result
+
+
+  # ---------------------------------------------------------------------------
+  # Get all parts of a primary key
+  # ---------------------------------------------------------------------------
+
+  def __getPrimaryKey(self, relid):
+    """
+    This function fetches all fields of a primary key for the given relation.
+
+    @param relid: object id of the relation in question
+    @return: sequence with the fieldnames of the primary key or None, if the
+        relation has no primary key.
+    """
+
+    result = []
+    cmd    = u"SELECT indkey FROM pg_index " \
+              "WHERE indrelid = %s AND indisprimary = TRUE" % relid
+
+    cursor = self._connection.makecursor (cmd)
+
+    try:
+      pkrs = cursor.fetchone ()
+
+      if pkrs:
+        cmd = u"SELECT attname FROM pg_attribute " \
+               "WHERE attrelid = %s AND attnum in (%s)" \
+              % (relid, string.join (pkrs [0].split (), ","))
+
+        fcursor = self._connection.makecursor (cmd)
+
+        try:
+          result = [rs [0] for rs in fcursor.fetchall ()]
+
+        finally:
+          fcursor.close ()
+
+
+    finally:
+      cursor.close ()
+
+    return len (result) and result or None
+


Property changes on: 
trunk/gnue-common/src/datasources/drivers/postgresql/Schema/Discovery/Introspection.py
___________________________________________________________________
Name: svn:keywords
   + +Id