r6520 - in trunk/gnue-common/src/datasources/drivers: Base Base/Schema/Creation DBSIG2/Schema/Creation interbase/Schema/Creation interbase/Schema/Discovery mysql/Schema/Creation mysql/Schema/Discovery postgresql/Schema/Discovery

[johannes]

Author: johannes
Date: 2004-10-20 08:35:13 -0500 (Wed, 20 Oct 2004)
New Revision: 6520

Modified:
   trunk/gnue-common/src/datasources/drivers/Base/Connection.py
   trunk/gnue-common/src/datasources/drivers/Base/Schema/Creation/Creation.py
   trunk/gnue-common/src/datasources/drivers/DBSIG2/Schema/Creation/Creation.py
   
trunk/gnue-common/src/datasources/drivers/interbase/Schema/Creation/Creation.py
   
trunk/gnue-common/src/datasources/drivers/interbase/Schema/Discovery/Introspection.py
   trunk/gnue-common/src/datasources/drivers/mysql/Schema/Creation/Creation.py
   
trunk/gnue-common/src/datasources/drivers/mysql/Schema/Discovery/Introspection.py
   
trunk/gnue-common/src/datasources/drivers/postgresql/Schema/Discovery/Introspection.py
Log:
Added support for index-introspection and -creation for PostgreSQL, 
Interbase/Firebird and MySQL. Added createDatabase () to MySQL


Modified: trunk/gnue-common/src/datasources/drivers/Base/Connection.py
===================================================================
--- trunk/gnue-common/src/datasources/drivers/Base/Connection.py        
2004-10-20 10:50:23 UTC (rev 6519)
+++ trunk/gnue-common/src/datasources/drivers/Base/Connection.py        
2004-10-20 13:35:13 UTC (rev 6520)
@@ -1,6 +1,9 @@
+# GNU Enterprise Common - Base DB Driver - Connection
 #
-# This file is part of GNU Enterprise.
+# Copyright 2001-2004 Free Software Foundation
 #
+# This file is part of GNU Enterprise
+#
 # GNU Enterprise is free software; you can redistribute it
 # and/or modify it under the terms of the GNU General Public
 # License as published by the Free Software Foundation; either
@@ -16,18 +19,10 @@
 # write to the Free Software Foundation, Inc., 59 Temple Place
 # - Suite 330, Boston, MA 02111-1307, USA.
 #
-# Copyright 2000-2004 Free Software Foundation
-#
-# FILE:
-# Connection.py
-#
-# DESCRIPTION:
-#
-# NOTES:
-#
-# $Id :$
+# $Id$
 
 import copy
+import string
 
 __all__ = ['Connection']
 
@@ -128,15 +123,48 @@
         if table.has_key ('primarykey'):
           del table ['primarykey']
 
-        # we create an index on a table update only if it contains new fields
+        # keep modified or new indices only
         if table.has_key ('indices'):
           keep = []
-          for index in table ['indices']:
-            for field in index ['fields']:
-              if not field.lower () in existingFields:
+
+          # if driver supports index-introspection we'll use it
+          if hasattr (res [0], 'indices'):
+            for index in table ['indices']:
+              oldIndex = None
+              for (name, value) in res [0].indices.items ():
+                if name.lower () == index ['name'].lower ():
+                  oldIndex = value
+
+              if oldIndex is None:
                 keep.append (index)
-                break
+              else:
+                old = [f.lower () for f in oldIndex ['fields']]
+                new = [f.lower () for f in index ['fields']]
 
+                if oldIndex ['unique'] != index ['unique'] or old != new:
+
+                  # make sure the backend has a possibility to remove the old
+                  # index before changing it
+                  if not table.has_key ('old_indices'):
+                    table ['old_indices'] = []
+                  table ['old_indices'].append (index ['name'])
+
+                  keep.append (index)
+          else:
+            # if no index-introspection available we only keep an index, if it
+            # has new fields
+            for index in table ['indices']:
+              for field in index ['fields']:
+                if not field.lower () in existingFields:
+                  # make sure the backend has a possibility to remove the old
+                  # index before changing it
+                  if not table.has_key ('old_indices'):
+                    table ['old_indices'] = []
+                  table ['old_indices'].append (index ['name'])
+
+                  keep.append (index)
+                  break
+
           table ['indices'] = keep
 
         # we create a constraint on a table update only if it contains new


Property changes on: 
trunk/gnue-common/src/datasources/drivers/Base/Connection.py
___________________________________________________________________
Name: svn:keywords
   - +Id
   + Id

Modified: 
trunk/gnue-common/src/datasources/drivers/Base/Schema/Creation/Creation.py
===================================================================
--- trunk/gnue-common/src/datasources/drivers/Base/Schema/Creation/Creation.py  
2004-10-20 10:50:23 UTC (rev 6519)
+++ trunk/gnue-common/src/datasources/drivers/Base/Schema/Creation/Creation.py  
2004-10-20 13:35:13 UTC (rev 6520)
@@ -1,6 +1,9 @@
+# GNU Enterprise Common - Base DB Driver - Schema Creation
 #
-# This file is part of GNU Enterprise.
+# Copyright 2001-2004 Free Software Foundation
 #
+# This file is part of GNU Enterprise
+#
 # GNU Enterprise is free software; you can redistribute it
 # and/or modify it under the terms of the GNU General Public
 # License as published by the Free Software Foundation; either
@@ -16,8 +19,6 @@
 # write to the Free Software Foundation, Inc., 59 Temple Place
 # - Suite 330, Boston, MA 02111-1307, USA.
 #
-# Copyright 2001-2004 Free Software Foundation
-#
 # $Id$
 
 import gnue
@@ -202,6 +203,25 @@
 
 
   # ---------------------------------------------------------------------------
+  # Drop an index
+  # ---------------------------------------------------------------------------
+
+  def dropIndex (self, tableName, indexName, codeOnly = False):
+    """
+    This function drops an index from the given table using the index
+    definition.
+
+    @param tableName: name of the table to drop an index from
+    @param indexName: name of the index to be dropped 
+    @param codeOnly: if TRUE no operation takes place, but only the code will
+        be returned.
+    @return: a tuple of sequences (prologue, body, epliogue) containing the
+        code to perform the action.
+    """
+    return ([], [], [])
+
+
+  # ---------------------------------------------------------------------------
   # Create a constraint
   # ---------------------------------------------------------------------------
 

Modified: 
trunk/gnue-common/src/datasources/drivers/DBSIG2/Schema/Creation/Creation.py
===================================================================
--- 
trunk/gnue-common/src/datasources/drivers/DBSIG2/Schema/Creation/Creation.py    
    2004-10-20 10:50:23 UTC (rev 6519)
+++ 
trunk/gnue-common/src/datasources/drivers/DBSIG2/Schema/Creation/Creation.py    
    2004-10-20 13:35:13 UTC (rev 6520)
@@ -1,6 +1,9 @@
+# GNU Enterprise Common - DBSIG2 DB Driver - Schema Creation
 #
-# This file is part of GNU Enterprise.
+# Copyright 2001-2004 Free Software Foundation
 #
+# This file is part of GNU Enterprise
+#
 # GNU Enterprise is free software; you can redistribute it
 # and/or modify it under the terms of the GNU General Public
 # License as published by the Free Software Foundation; either
@@ -16,8 +19,6 @@
 # write to the Free Software Foundation, Inc., 59 Temple Place
 # - Suite 330, Boston, MA 02111-1307, USA.
 #
-# Copyright 2001-2004 Free Software Foundation
-#
 # $Id$
 
 import string
@@ -120,7 +121,11 @@
 
     table = tableDefinition ['name']
 
-    if tableDefinition.has_key ('fields'):
+    if tableDefinition.has_key ('old_indices'):
+      for ixName in tableDefinition ['old_indices']:
+        self.mergeTuple (res, self.dropIndex (table, ixName, True))
+
+    if tableDefinition.has_key ('fields') and len (tableDefinition ['fields']):
       if self.ALTER_MULTIPLE:
         item = tableDefinition ['fields']
         fCode = self.createFields (table, item, True)
@@ -246,6 +251,33 @@
 
 
   # ---------------------------------------------------------------------------
+  # Drop an old index
+  # ---------------------------------------------------------------------------
+
+  def dropIndex (self, tableName, indexName, codeOnly = False):
+    """
+    This function drops an index from the given table
+
+    @param tableName: name of the table to drop an index from
+    @param indexName: name of the index to be dropped
+    @param codeOnly: if TRUE no operation takes place, but only the code will
+        be returned.
+    @return: a tuple of sequences (prologue, body, epliogue) containing the
+        code to perform the action.
+    """
+
+    res = Base.Creation.dropIndex (self, tableName, indexName, codeOnly)
+
+    indexName = self._shortenName (indexName)
+    res [0].append (u"DROP INDEX %s%s" % (indexName, self.END_COMMAND))
+
+    if not codeOnly:
+      self._executeCodeTuple (res)
+
+    return res
+
+
+  # ---------------------------------------------------------------------------
   # Create a constraint
   # ---------------------------------------------------------------------------
 

Modified: 
trunk/gnue-common/src/datasources/drivers/interbase/Schema/Creation/Creation.py
===================================================================
--- 
trunk/gnue-common/src/datasources/drivers/interbase/Schema/Creation/Creation.py 
    2004-10-20 10:50:23 UTC (rev 6519)
+++ 
trunk/gnue-common/src/datasources/drivers/interbase/Schema/Creation/Creation.py 
    2004-10-20 13:35:13 UTC (rev 6520)
@@ -1,6 +1,9 @@
+# GNU Enterprise Common - Interbase/Firebird DB Driver - Schema Creation
 #
-# This file is part of GNU Enterprise.
+# Copyright 2001-2004 Free Software Foundation
 #
+# This file is part of GNU Enterprise
+#
 # GNU Enterprise is free software; you can redistribute it
 # and/or modify it under the terms of the GNU General Public
 # License as published by the Free Software Foundation; either
@@ -16,8 +19,6 @@
 # write to the Free Software Foundation, Inc., 59 Temple Place
 # - Suite 330, Boston, MA 02111-1307, USA.
 #
-# Copyright 2001-2004 Free Software Foundation
-#
 # $Id$
 
 import os
@@ -73,12 +74,17 @@
     code = u"%s -user sysdba -password %s -add %s -pw %s" % \
         (gsecbin, syspw, username, password)
 
-    if os.system (code):
-      raise gException, ("Cannot create user")
+    try:
+      # if creating the user fails we try to create the db anyway. Maybe this
+      # is done from a remote system where no gsec is available, but the given
+      # credentials are valid on the given server.
+      os.system (code)
+    except:
+      pass
 
     self.connection._driver.create_database (\
-       u"create database '%s' user '%s' password '%s' default character set 
UNICODE_FSS"
-        % (dburl, username, password))
+       u"create database '%s' user '%s' password '%s' " \
+        "default character set UNICODE_FSS" % (dburl, username, password))
 
     self.connection.manager.loginToConnection (self.connection)
 

Modified: 
trunk/gnue-common/src/datasources/drivers/interbase/Schema/Discovery/Introspection.py
===================================================================
--- 
trunk/gnue-common/src/datasources/drivers/interbase/Schema/Discovery/Introspection.py
       2004-10-20 10:50:23 UTC (rev 6519)
+++ 
trunk/gnue-common/src/datasources/drivers/interbase/Schema/Discovery/Introspection.py
       2004-10-20 13:35:13 UTC (rev 6520)
@@ -1,10 +1,13 @@
+# GNU Enterprise Common - Interbase/Firebird DB Driver - Schema Introspection
 #
-# This file is part of GNU Enterprise.
+# Copyright 2001-2004 Free Software Foundation
 #
+# This file is part of GNU Enterprise
+#
 # GNU Enterprise is free software; you can redistribute it
 # and/or modify it under the terms of the GNU General Public
 # License as published by the Free Software Foundation; either
-# version 2, or(at your option) any later version.
+# version 2, or (at your option) any later version.
 #
 # GNU Enterprise is distributed in the hope that it will be
 # useful, but WITHOUT ANY WARRANTY; without even the implied
@@ -16,8 +19,6 @@
 # write to the Free Software Foundation, Inc., 59 Temple Place
 # - Suite 330, Boston, MA 02111-1307, USA.
 #
-# Copyright 2000-2004 Free Software Foundation
-#
 # $Id$
 
 __all__ = ['Introspection']
@@ -81,8 +82,14 @@
         attrs = {'id'        : relname,
                  'name'      : relname,
                  'type'      : rs [1] is None and 'table' or 'view',
-                 'primarykey': self.__getPrimaryKey (relname)}
+                 'primarykey': None,
+                 'indices'   : self.__getIndices (relname)}
 
+        if attrs ['indices'] is not None:
+          for index in attrs ['indices'].values ():
+            if index ['primary']:
+              attrs ['primarykey'] = index ['fields']
+
         result.append ( \
           GIntrospection.Schema (attrs, getChildSchema = self._getChildSchema))
 
@@ -159,36 +166,71 @@
 
 
   # ---------------------------------------------------------------------------
-  # Get all parts of a primary key
+  # Get all indices of a given relation
   # ---------------------------------------------------------------------------
 
-  def __getPrimaryKey (self, relname):
+  def __getIndices (self, relname):
     """
-    This function fetches all fields of a primary key for the given relation.
+    This function creates a dictionary with all indices of a given relation
+    where the keys are the indexnames and the values are dictionaries
+    describing the indices. Such a dictionary has the keys 'unique', 'primary'
+    and 'fields', where 'unique' specifies whether the index is unique or not
+    and 'primary' specifies wether the index is the primary key or not.
+    'fields' holds a sequence with all field names building the index.
 
-    @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.
+    @param relname: name of the relation to fetch indices for
+    @return: dictionary with indices or None if no indices were found
     """
 
-    cmd = u"SELECT rdb$field_name " \
-           "FROM rdb$relation_constraints rc, rdb$index_segments ri " \
-           "WHERE ri.rdb$index_name = rc.rdb$index_name " \
-              "AND rc.rdb$constraint_type = 'PRIMARY KEY' " \
-              "AND rc.rdb$relation_name = '%s'" \
-           "ORDER BY ri.rdb$field_position" % self.__identifier (relname)
+    result = {}
 
+    cmd = u"SELECT i.rdb$index_name, i.rdb$unique_flag, s.rdb$field_name " \
+           "FROM rdb$indices i, rdb$index_segments s " \
+           "WHERE i.rdb$index_name = s.rdb$index_name " \
+           "  AND i.rdb$relation_name = '%s' " \
+           "  AND i.rdb$foreign_key IS NULL " \
+           "ORDER BY i.rdb$index_name, s.rdb$field_position" \
+          % self.__identifier (relname)
+
     cursor = self._connection.makecursor (cmd)
     
     try:
-      result = [rs [0].strip () for rs in cursor.fetchall ()]
+      for rs in cursor.fetchall ():
+        indexName = rs [0].strip ()
 
+        if not result.has_key (indexName):
+          result [indexName] = {'unique' : rs [1] or False,
+                                'primary': False,
+                                'fields' : []}
+        result [indexName] ['fields'].append (rs [2].strip ())
+
     finally:
       cursor.close ()
 
-    return len (result) and result or None
+    # if there's a primary key, update the proper index-entry and replace the
+    # indexname by it's constraint name
+    cmd = u"SELECT rdb$index_name, rdb$constraint_name " \
+           "FROM rdb$relation_constraints " \
+           "WHERE rdb$constraint_type = 'PRIMARY KEY' " \
+              "AND rdb$relation_name = '%s'" % self.__identifier (relname)
 
+    cursor = self._connection.makecursor (cmd)
+    
+    try:
+      rs = cursor.fetchone ()
+      if rs:
+        (ixName, coName) = [rs [0].strip (), rs [1].strip ()]
+        if result.has_key (ixName):
+          result [ixName]['primary'] = True
+          result [coName] = result [ixName]
+          del result [ixName]
 
+    finally:
+      cursor.close ()
+
+    return len (result.keys ()) and result or None
+
+
   # ---------------------------------------------------------------------------
   # Prepare an identifier for matching against rdb$-values
   # ---------------------------------------------------------------------------

Modified: 
trunk/gnue-common/src/datasources/drivers/mysql/Schema/Creation/Creation.py
===================================================================
--- trunk/gnue-common/src/datasources/drivers/mysql/Schema/Creation/Creation.py 
2004-10-20 10:50:23 UTC (rev 6519)
+++ trunk/gnue-common/src/datasources/drivers/mysql/Schema/Creation/Creation.py 
2004-10-20 13:35:13 UTC (rev 6520)
@@ -1,6 +1,9 @@
+# GNU Enterprise Common - MySQL DB Driver - Schema Creation
 #
-# This file is part of GNU Enterprise.
+# Copyright 2001-2004 Free Software Foundation
 #
+# This file is part of GNU Enterprise
+#
 # GNU Enterprise is free software; you can redistribute it
 # and/or modify it under the terms of the GNU General Public
 # License as published by the Free Software Foundation; either
@@ -16,10 +19,10 @@
 # write to the Free Software Foundation, Inc., 59 Temple Place
 # - Suite 330, Boston, MA 02111-1307, USA.
 #
-# Copyright 2001-2004 Free Software Foundation
-#
 # $Id$
 
+import os
+
 from gnue.common.datasources.drivers.DBSIG2.Schema.Creation import \
     Creation as Base
 
@@ -33,6 +36,56 @@
   MAX_NAME_LENGTH = 64
 
   # ---------------------------------------------------------------------------
+  # Create a new database
+  # ---------------------------------------------------------------------------
+
+  def createDatabase (self):
+    """
+    This function creates a new database as specified by the given connection.
+    In order to be successfull the current account must have enough privileges
+    to create new databases.
+    """
+
+    dbname   = self.connection.parameters.get ('dbname')
+    username = self.connection.parameters.get ('username', 'gnue')
+    password = self.connection.parameters.get ('password')
+    host     = self.connection.parameters.get ('host')
+    port     = self.connection.parameters.get ('port')
+
+    createdb = u"mysqladmin %(site)s%(port)s create %(db)s" \
+        % {'db'  : dbname,
+           'site': host and "--host=%s " % host or '',
+           'port': port and "--port=%s " % port or ''}
+
+    os.system (createdb)
+
+    sql = u"GRANT ALL PRIVILEGES ON %(db)s.* TO '%(user)s'@'%%' %(pass)s" \
+        % {'db'  : dbname,
+           'user': username,
+           'pass': password and "IDENTIFIED BY '%s'" % password or ""}
+
+    grant = 'mysql %(host)s%(port)s -e "%(sql)s" -s %(db)s' \
+        % {'sql' : sql,
+           'host': host and "--host=%s " % host or '',
+           'port': port and "--port=%s " % port or '',
+           'db'  : dbname}
+    os.system (grant)
+
+    sql = u"GRANT ALL PRIVILEGES ON %(db)s.* TO '%(user)s'@'localhost' " \
+           "%(pass)s" \
+        % {'db': dbname,
+           'user': username,
+           'pass': password and "IDENTIFIED BY '%s'" % password or ""}
+
+    grant = 'mysql %(host)s%(port)s -e "%(sql)s" -s %(db)s' \
+        % {'sql' : sql,
+           'host': host and "--host=%s " % host or '',
+           'port': port and "--port=%s " % port or '',
+           'db'  : dbname}
+    os.system (grant)
+
+
+  # ---------------------------------------------------------------------------
   # Handle special defaults
   # ---------------------------------------------------------------------------
 
@@ -65,6 +118,33 @@
 
 
   # ---------------------------------------------------------------------------
+  # Drop an old index
+  # ---------------------------------------------------------------------------
+
+  def dropIndex (self, tableName, indexName, codeOnly = False):
+    """
+    This function drops an index from the given table
+
+    @param tableName: name of the table to drop an index from
+    @param indexName: name of the index to be dropped
+    @param codeOnly: if TRUE no operation takes place, but only the code will
+        be returned.
+    @return: a tuple of sequences (prologue, body, epliogue) containing the
+        code to perform the action.
+    """
+
+    res = ([], [], [])
+
+    indexName = self._shortenName (indexName)
+    res [0].append (u"DROP INDEX %s ON %s%s" \
+                    % (indexName, tableName, self.END_COMMAND))
+
+    if not codeOnly:
+      self._executeCodeTuple (res)
+
+    return res
+
+  # ---------------------------------------------------------------------------
   # A key is an unsigned integer
   # ---------------------------------------------------------------------------
 

Modified: 
trunk/gnue-common/src/datasources/drivers/mysql/Schema/Discovery/Introspection.py
===================================================================
--- 
trunk/gnue-common/src/datasources/drivers/mysql/Schema/Discovery/Introspection.py
   2004-10-20 10:50:23 UTC (rev 6519)
+++ 
trunk/gnue-common/src/datasources/drivers/mysql/Schema/Discovery/Introspection.py
   2004-10-20 13:35:13 UTC (rev 6520)
@@ -1,10 +1,13 @@
+# GNU Enterprise Common - MySQL DB driver - Schema Introspection
 #
-# This file is part of GNU Enterprise.
+# Copyright 2001-2004 Free Software Foundation
 #
+# This file is part of GNU Enterprise
+#
 # GNU Enterprise is free software; you can redistribute it
 # and/or modify it under the terms of the GNU General Public
 # License as published by the Free Software Foundation; either
-# version 2, or(at your option) any later version.
+# version 2, or (at your option) any later version.
 #
 # GNU Enterprise is distributed in the hope that it will be
 # useful, but WITHOUT ANY WARRANTY; without even the implied
@@ -16,169 +19,190 @@
 # write to the Free Software Foundation, Inc., 59 Temple Place
 # - Suite 330, Boston, MA 02111-1307, USA.
 #
-# 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.apps import GDebug, GConfig
 from gnue.common.datasources import GIntrospection
 
-class Introspection(GIntrospection.Introspection):
+# =============================================================================
+# This class implements schema introspection for MySQL backends
+# =============================================================================
 
+class Introspection (GIntrospection.Introspection):
+
   # list of the types of Schema objects this driver provides
-  types =[ ('table',_('Tables'),1) ]
+  types = [('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):
+  _TYPES = {'number': ['int','integer','bigint','mediumint',
+                       'smallint','tinyint','float','real', 
'double','decimal'],
+            'date'  : ['date','time','timestamp','datetime']}
+
+  # ---------------------------------------------------------------------------
+  # Find a schema element by name and/or type
+  # ---------------------------------------------------------------------------
+
+  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.
+
+    @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.
+    """
+
+    result = []
+
     if name is None:
-      return self.getSchemaList(type)
+      cmd = u"SHOW TABLES"
+
     else:
-      rs = self.getSchemaByName(name, type)
-      if rs:
-        return [rs]
-      else:
+      cmd = u"SHOW COLUMNS FROM %s" % name
+
+    try:
+      cursor = self._connection.makecursor (cmd)
+
+    except:
+      if name is not  None:
         return None
 
+      else:
+        raise
 
-  # TODO: Merge into find()
-  # Return a list of Schema objects
+    try:
+      for rs in cursor.fetchall ():
+        attrs = {'id'        : name or rs [0],
+                 'name'      : name or rs [0],
+                 'type'      : 'table',
+                 'primarykey': None,
+                 'indices'   : self.__getIndices (name or rs [0])}
 
-  #
-  # Schema (metadata) functions
-  #
+        if attrs ['indices'] is not None:
+          for index in attrs ['indices'].values ():
+            if index ['primary']:
+              attrs ['primarykey'] = index ['fields']
+              break
 
-  # Return a list of Schema objects
-  def getSchemaList(self, type=None):
+        result.append ( \
+          GIntrospection.Schema (attrs, getChildSchema = self._getChildSchema))
 
-    # TODO: This excludes any system tables and views. Should it?
-    statement = "SHOW TABLES"
+        if name is not None:
+          break
 
-    cursor = self._connection.native.cursor()
-    cursor.execute(statement)
+    finally:
+      cursor.close ()
 
-    list = []
-    for rs in cursor.fetchall():
-      list.append(GIntrospection.Schema(attrs={'id':rs[0], 'name':rs[0],
-                         'type':'table',
-                         'primarykey': self.__getPrimaryKey(rs[0])},
-                         getChildSchema=self.__getFieldSchema))
 
-    cursor.close()
-    return list
+    return len (result) and result or None
 
 
-  # Find a schema object with specified name
-  def getSchemaByName(self, name, type=None):
-    statement = "DESCRIBE %s" % (name)
+  # ---------------------------------------------------------------------------
+  # Get all fields of a table
+  # ---------------------------------------------------------------------------
 
-    cursor = self._connection.native.cursor()
+  def _getChildSchema (self, parent):
+    """
+    This function returns a list of all child elements for a given table.
+
+    @param parent: schema object instance whose child elements should be
+        fetched
+    @return: sequence of schema instances
+    """
+
+    result = []
+
+    cmd = u"SHOW COLUMNS FROM %s" % parent.id
+    
+    cursor = self._connection.makecursor (cmd)
+
     try:
-      try:
-        cursor.execute(statement)
+      for rs in cursor.fetchall ():
+        nativetype = string.split (string.replace (rs [1], ')', ''), '(')
 
-        rs = cursor.fetchone()
-        if rs:
-          schema = GIntrospection.Schema(attrs={'id':name, 'name':name,
-                           'type':'table',
-                           'primarykey': self.__getPrimaryKey(name,cursor)},
-                           getChildSchema=self.__getFieldSchema)
-        else:
-          schema = None
+        attrs = {'id'        : "%s.%s" % (parent.id, rs [0]),
+                 'name'      : rs [0],
+                 'type'      : 'field',
+                 'nativetype': nativetype [0],
+                 'required'  : rs [2] != 'YES',
+                 'datatype'  : 'text'}
 
-      except:
-        schema = None
+        for (group, natives) in self._TYPES.items ():
+          if nativetype [0] in natives:
+            attrs ['datatype'] = group
+            break
 
-    finally:
-      cursor.close()
-    return schema
+        if len (nativetype) == 2:
+          parts = []
+          for item in nativetype [1].split (','):
+            parts.extend (item.split ())
 
+          if parts [0].strip ().isdigit ():
+            attrs ['length'] = int (parts [0].strip ())
 
-  def __getPrimaryKey(self, id, cursor=None):
-    statement = "DESCRIBE %s" % id
-    if not cursor:
-      cursor = self._connection.native.cursor()
-      close_cursor = 1
-    else:
-      close_cursor = 0
-    cursor.execute(statement)
+          if len (parts) > 1 and parts [1].strip ().isdigit ():
+            attrs ['precision'] = int (parts [1].strip ())
 
-    lst = []
-    for rs in cursor.fetchall():
-      if rs[3] == 'PRI':
-        lst.append(rs[0])
+        if rs [4] not in ('NULL', '0000-00-00 00:00:00', '', None):
+          attrs ['defaulttype'] = 'constant'
+          attrs ['defaultval']  = rs [4]
 
-    if close_cursor:
-      cursor.close()
+        if rs [5] == 'auto_increment':
+          attrs ['defaulttype'] = 'serial'
 
-    return tuple(lst)
+        result.append (GIntrospection.Schema (attrs))
 
-  # Get fields for a table
-  def __getFieldSchema(self, parent):
+    finally:
+      cursor.close ()
 
-    statement = "DESCRIBE %s" % parent.id
+    return result
 
-    cursor = self._connection.native.cursor()
-    cursor.execute(statement)
 
-    list = []
-    for rs in cursor.fetchall():
+  # ---------------------------------------------------------------------------
+  # Get a dictionary of all indices available for a given table
+  # ---------------------------------------------------------------------------
 
-      nativetype = string.split(string.replace(rs[1],')',''),'(')
+  def __getIndices (self, table):
+    """
+    This function creates a dictionary with all indices of a given relation
+    where the keys are the indexnames and the values are dictionaries
+    describing the indices. Such a dictionary has the keys 'unique', 'primary'
+    and 'fields', where 'unique' specifies whether the index is unique or not
+    and 'primary' specifies wether the index is the primary key or not.
+    'fields' holds a sequence with all field names building the index.
 
+    @param table: name of the table to fetch indices for
+    @return: dictionary with indices or None if no indices were found
+    """
 
-      attrs={'id': "%s.%s" % (parent.id, rs[0]), 'name': rs[0],
-             'type':'field', 'nativetype': nativetype[0],
-             'required': rs[2] != 'YES'}
+    result = {}
 
-      if nativetype[0] in ('int','integer','bigint','mediumint',
-                           'smallint','tinyint','float','real',
-                           'double','decimal'):
-        attrs['datatype']='number'
-      elif nativetype[0] in ('date','time','timestamp','datetime'):
-        attrs['datatype']='date'
-      else:
-        attrs['datatype']='text'
+    cmd = u"SHOW INDEX FROM %s" % table
 
-      try:
-        if len(nativetype) == 2:
-          try:
-            ln, prec = nativetype[1].split(',')
-          except:
-            ln = nativetype[1]
-            prec = None
-          attrs['length'] = int(ln.split()[0])
-          if prec != None:
-            attrs['precision'] = int(prec)
-      except ValueError:
-        GDebug.printMesg(1,'WARNING: mysql native type error: %s' % nativetype)
+    cursor = self._connection.makecursor (cmd)
 
-      if rs[4] not in ('NULL', '0000-00-00 00:00:00','', None):
-        attrs['defaulttype'] = 'constant'
-        attrs['defaultval'] = rs[4]
+    try:
+      for rs in cursor.fetchall ():
+        ixName = rs [2]
+        if not result.has_key (ixName):
+          result [ixName] = {'unique': not rs [1],
+                             'primary': ixName == 'PRIMARY',
+                             'fields': []}
+        result [ixName] ['fields'].append ((rs [3], rs [4]))
 
-      if rs[5] == 'auto_increment':
-        attrs['defaulttype'] = 'serial'
 
+      # Sort the field lists according to their sequence number and remove the
+      # sequences number from the list
+      for index in result.values ():
+        fields = index ['fields']
+        fields.sort ()
+        index ['fields'] = [field [1] for field in fields]
 
-      list.append(GIntrospection.Schema(attrs=attrs))
+    finally:
+      cursor.close ()
 
-    cursor.close()
-    return list
-
+    return len (result.keys ()) and result or None


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

Modified: 
trunk/gnue-common/src/datasources/drivers/postgresql/Schema/Discovery/Introspection.py
===================================================================
--- 
trunk/gnue-common/src/datasources/drivers/postgresql/Schema/Discovery/Introspection.py
      2004-10-20 10:50:23 UTC (rev 6519)
+++ 
trunk/gnue-common/src/datasources/drivers/postgresql/Schema/Discovery/Introspection.py
      2004-10-20 13:35:13 UTC (rev 6520)
@@ -1,10 +1,13 @@
+# GNU Enterprise Common - PostgreSQL DB Driver - Schema Introspection
 #
-# This file is part of GNU Enterprise.
+# Copyright 2001-2004 Free Software Foundation
 #
+# This file is part of GNU Enterprise
+#
 # GNU Enterprise is free software; you can redistribute it
 # and/or modify it under the terms of the GNU General Public
 # License as published by the Free Software Foundation; either
-# version 2, or(at your option) any later version.
+# version 2, or (at your option) any later version.
 #
 # GNU Enterprise is distributed in the hope that it will be
 # useful, but WITHOUT ANY WARRANTY; without even the implied
@@ -16,8 +19,6 @@
 # write to the Free Software Foundation, Inc., 59 Temple Place
 # - Suite 330, Boston, MA 02111-1307, USA.
 #
-# Copyright 2000-2004 Free Software Foundation
-#
 # $Id$
 
 __all__ = ['Introspection']
@@ -30,7 +31,7 @@
 # This class implements schema introspection for PostgreSQL backends
 # =============================================================================
 
-class Introspection(GIntrospection.Introspection):
+class Introspection (GIntrospection.Introspection):
 
 
   # list of the types of Schema objects this driver provides
@@ -74,11 +75,18 @@
 
     try:
       for rs in cursor.fetchall ():
-        attrs = {'id'  : rs [0],
-                 'name': rs [1],
-                 'type': rs [2] == 'v' and 'view' or 'table',
-                 'primarykey': self.__getPrimaryKey (rs [0])}
+        attrs = {'id'        : rs [0],
+                 'name'      : rs [1],
+                 'type'      : rs [2] == 'v' and 'view' or 'table',
+                 'indices'   : self.__getIndices (rs [0]),
+                 'primarykey': None}
 
+        if attrs ['indices'] is not None:
+          for index in attrs ['indices'].values ():
+            if index ['primary']:
+              attrs ['primarykey'] = index ['fields']
+              break
+
         result.append ( \
           GIntrospection.Schema (attrs, getChildSchema = self._getChildSchema))
 
@@ -169,37 +177,49 @@
 
 
   # ---------------------------------------------------------------------------
-  # Get all parts of a primary key
+  # Get a dictionary of all indices defined for a relation
   # ---------------------------------------------------------------------------
 
-  def __getPrimaryKey(self, relid):
+  def __getIndices (self, relid):
     """
-    This function fetches all fields of a primary key for the given relation.
+    This function creates a dictionary with all indices of a given relation
+    where the keys are the indexnames and the values are dictionaries
+    describing the indices. Such a dictionary has the keys 'unique', 'primary'
+    and 'fields', where 'unique' specifies whether the index is unique or not
+    and 'primary' specifies wether the index is the primary key or not.
+    'fields' holds a sequence with all field names building the index.
 
-    @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.
+    @param relid: relation id of the table to fetch indices for
+    @return: dictionary with indices or None if no indices were found
     """
 
-    result = []
-    cmd    = u"SELECT indkey FROM pg_index " \
-              "WHERE indrelid = %s AND indisprimary = TRUE" % relid
+    result = {}
+    cmd    = u"SELECT c.relname, i.indisunique, i.indisprimary, i.indkey " \
+              "FROM pg_index i, pg_class c " \
+              "WHERE i.indrelid = %s AND c.oid = i.indexrelid" % relid
 
     cursor = self._connection.makecursor (cmd)
 
     try:
-      pkrs = cursor.fetchone ()
+      for rs in cursor.fetchall ():
+        order = rs [3].split ()
+        result [rs [0]] = {'unique' : rs [1],
+                           'primary': rs [2],
+                           'fields' : []}
 
-      if pkrs:
-        cmd = u"SELECT attname FROM pg_attribute " \
+        cmd = u"SELECT attname, attnum FROM pg_attribute " \
                "WHERE attrelid = %s AND attnum in (%s)" \
-              % (relid, string.join (pkrs [0].split (), ","))
+              % (relid, string.join (order, ","))
 
         fcursor = self._connection.makecursor (cmd)
 
         try:
-          result = [rs [0] for rs in fcursor.fetchall ()]
+          parts = {}
+          for frs in fcursor.fetchall ():
+            parts ["%s" % frs [1]] = frs [0]
 
+          result [rs [0]] ['fields'] = [parts [ix] for ix in order]
+
         finally:
           fcursor.close ()
 
@@ -207,5 +227,5 @@
     finally:
       cursor.close ()
 
-    return len (result) and result or None
+    return len (result.keys ()) and result or None