r6545 - in trunk/gnue-common/src/datasources/drivers/sqlite/Schema: Creation Discovery

[johannes]

Author: johannes
Date: 2004-10-22 03:55:12 -0500 (Fri, 22 Oct 2004)
New Revision: 6545

Modified:
   trunk/gnue-common/src/datasources/drivers/sqlite/Schema/Creation/Creation.py
   
trunk/gnue-common/src/datasources/drivers/sqlite/Schema/Discovery/Introspection.py
Log:
Added index-introspection and creation to SQLite driver


Modified: 
trunk/gnue-common/src/datasources/drivers/sqlite/Schema/Creation/Creation.py
===================================================================
--- 
trunk/gnue-common/src/datasources/drivers/sqlite/Schema/Creation/Creation.py    
    2004-10-22 08:30:43 UTC (rev 6544)
+++ 
trunk/gnue-common/src/datasources/drivers/sqlite/Schema/Creation/Creation.py    
    2004-10-22 08:55:12 UTC (rev 6545)
@@ -1,6 +1,9 @@
+# GNU Enterprise Common - SQLite 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,20 +19,24 @@
 # write to the Free Software Foundation, Inc., 59 Temple Place
 # - Suite 330, Boston, MA 02111-1307, USA.
 #
-# Copyright 2001-2004 Free Software Foundation
-#
-# $Id: $
+# $Id$
 
 import os
 import re
 import string
+
+from gnue.common.apps import errors
 from gnue.common.datasources.drivers.DBSIG2.Schema.Creation import \
     Creation as Base
 
-class MissingTableError (gException):
+# =============================================================================
+# Excpetions
+# =============================================================================
+
+class MissingTableError (errors.AdminError):
   def __init__ (self, table):
     msg = u_("Cannot find table '%s' anymore") % table
-    gException.__init__ (self, msg)
+    errors.AdminError.__init__ (self, msg)
 
 # =============================================================================
 # Class implementing schema creation for SQLite
@@ -116,42 +123,68 @@
     res = ([], [], [])
     body = res [1]
 
-    tSchema = self.introspector.find (name = tableDefinition ['name'])
-    if tSchema is None:
-      raise MissingTableError, tableDefinition ['name']
+    table     = tableDefinition ['name']
+    hasFields = tableDefinition.has_key ('fields') and \
+        len (tableDefinition ['fields'])
 
-    parts = self._CMD.match (tSchema [0].sql)
-    if not parts:
-      raise gException, u_("Cannot split SQL command: '%s'") % tSchema [0].sql
+    # Remove old indices
+    if not hasFields and tableDefinition.has_key ('old_indices'):
+      for ixName in tableDefinition ['old_indices']:
+        self.mergeTuple (res, self.dropIndex (table, ixName, True))
 
-    fields = [f.name for f in tSchema [0].fields ()]
-    nfields = ["NULL" for f in tableDefinition ['fields']]
-    nfields.extend (fields)
+    if hasFields:
+      tSchema = self.introspector.find (name = tableDefinition ['name'])
+      if tSchema is None:
+        raise MissingTableError, tableDefinition ['name']
 
-    body.append (u"CREATE TEMPORARY TABLE t1_backup (%s)" \
-                 % string.join (fields, ","))
-    body.append (u"INSERT INTO t1_backup SELECT %(fields)s FROM %(table)s" \
-                 % {'fields': string.join (fields, ","),
-                    'table' : tableDefinition ['name']})
-    body.append (u"DROP TABLE %s" % tableDefinition ['name'])
+      parts = self._CMD.match (tSchema [0].sql)
+      if not parts:
+        raise gException, u_("Cannot split SQL command: '%s'") % tSchema 
[0].sql
 
-    fCode = self.createFields (tableDefinition ['name'],
-                               tableDefinition ['fields'])
-    self.mergeTuple (res, (fCode [0], [], fCode [2]))
+      fields = [f.name for f in tSchema [0].fields ()]
+      nfields = ["NULL" for f in tableDefinition ['fields']]
+      nfields.extend (fields)
 
-    oldSQL = parts.groups ()
-    newBody = [string.join (fCode [1], ", ")]
-    if len (oldSQL [1]):
-      newBody.append (oldSQL [1])
+      body.append (u"CREATE TEMPORARY TABLE t1_backup (%s)" \
+                   % string.join (fields, ","))
+      body.append (u"INSERT INTO t1_backup SELECT %(fields)s FROM %(table)s" \
+                   % {'fields': string.join (fields, ","),
+                      'table' : tableDefinition ['name']})
+      body.append (u"DROP TABLE %s" % tableDefinition ['name'])
 
-    cmd = u"%s (%s)%s" % (oldSQL [0], string.join (newBody, ", "), oldSQL [2])
+      fCode = self.createFields (tableDefinition ['name'],
+                                 tableDefinition ['fields'])
+      self.mergeTuple (res, (fCode [0], [], fCode [2]))
 
-    body.append (cmd)
-    body.append (u"INSERT INTO %(table)s SELECT %(fields)s FROM t1_backup" \
-                 % {'table' : tableDefinition ['name'],
-                    'fields': string.join (nfields, ",")})
-    body.append (u"DROP TABLE t1_backup")
+      oldSQL = parts.groups ()
+      newBody = [string.join (fCode [1], ", ")]
+      if len (oldSQL [1]):
+        newBody.append (oldSQL [1])
 
+      cmd = u"%s (%s)%s" % (oldSQL [0], string.join (newBody, ", "), oldSQL 
[2])
+
+      body.append (cmd)
+      body.append (u"INSERT INTO %(table)s SELECT %(fields)s FROM t1_backup" \
+                   % {'table' : tableDefinition ['name'],
+                      'fields': string.join (nfields, ",")})
+      body.append (u"DROP TABLE t1_backup")
+
+      # add all 'unchanged' indices
+      if tSchema [0].indices is not None:
+        for (indexName, index) in tSchema [0].indices.items ():
+          addIndices = tableDefinition.get ('indices')
+          if addIndices is None or indexName in addIndices:
+            continue
+
+          index ['name'] = indexName
+          self.mergeTuple (res, self.createIndex (table, index, True))
+
+
+    # Create all requested indices
+    if tableDefinition.has_key ('indices'):
+      for ixDef in tableDefinition ['indices']:
+        self.mergeTuple (res, self.createIndex (table, ixDef, True))
+
     if not codeOnly:
       self._executeCodeTuple (res)
 


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

Modified: 
trunk/gnue-common/src/datasources/drivers/sqlite/Schema/Discovery/Introspection.py
===================================================================
--- 
trunk/gnue-common/src/datasources/drivers/sqlite/Schema/Discovery/Introspection.py
  2004-10-22 08:30:43 UTC (rev 6544)
+++ 
trunk/gnue-common/src/datasources/drivers/sqlite/Schema/Discovery/Introspection.py
  2004-10-22 08:55:12 UTC (rev 6545)
@@ -1,10 +1,13 @@
+# GNU Enterprise Common - SQLite 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,13 +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
-#
-# FILE:
-# Introspection.py
-#
-# DESCRIPTION:
-#
 # $Id$
 
 __all__ = ['Introspection']
@@ -32,6 +28,10 @@
 
 from gnue.common.datasources import GIntrospection
 
+# =============================================================================
+# This class implements schema introspection for SQLite backends
+# =============================================================================
+
 class Introspection (GIntrospection.Introspection):
 
   # list of the types of Schema objects this driver provides
@@ -45,6 +45,9 @@
   _NOTNULL   = re.compile ('(.*)(NOT NULL)(.*)', re.IGNORECASE)
   _CONSTRAINTS = re.compile ('.*?((UNIQUE|CHECK|PRIMARY KEY)\s*\(.*?\)).*', \
                              re.IGNORECASE)
+  _PRIMARYKEY  = re.compile ('.*?PRIMARY KEY\s*\((.*?)\).*', re.IGNORECASE)
+  _INDEX = re.compile ('CREATE\s*(\w+){0,1}\s*INDEX\s*(\w+)\s*ON\s*\w+\s*' \
+                       '\((.*?)\).*', re.IGNORECASE)
 
   # ---------------------------------------------------------------------------
   # Get a schema object matching name and type
@@ -63,7 +66,7 @@
     else:
       condition = "WHERE name = '%s'" % name
 
-    cmd = "SELECT type, name, sql FROM sqlite_master %s" % condition
+    cmd = u"SELECT type, name, sql FROM sqlite_master %s" % condition
     cursor = self._connection.makecursor (cmd)
 
     try:
@@ -75,6 +78,10 @@
                  'name': rs [1],
                  'type': rs [0],
                  'sql' : rs [2]}
+        
+        attrs ['primarykey'] = self.__getPrimaryKey (rs [2])
+        attrs ['indices']    = self.__getIndices (rs [1])
+
         result.append ( \
           GIntrospection.Schema (attrs, getChildSchema = self._getChildSchema))
 
@@ -156,3 +163,62 @@
       cursor.close ()
 
     return result
+
+
+  # ---------------------------------------------------------------------------
+  # Try to extract a primary key definition from SQL code
+  # ---------------------------------------------------------------------------
+
+  def __getPrimaryKey (self, sql):
+    """
+    This function extracts a primary key from the given SQL code.
+
+    @param sql: SQL code to extract primary key from
+    @return: sequence of fields building the primary key or None
+    """
+
+    result = None
+    pk     = self._PRIMARYKEY.match (sql)
+
+    if pk is not None:
+      result = [f.strip () for f in pk.groups () [0].split (',')]
+    
+    return result
+
+
+  # ---------------------------------------------------------------------------
+  # Try to extract all index definitions for a given table
+  # ---------------------------------------------------------------------------
+
+  def __getIndices (self, table):
+    """
+    This function fetches all indices for a given table and returns them as a
+    dictionary or None if no indices are available. NOTE: the primary key is
+    *NOT* listed as index.
+
+    @param table: name of the table to fetch indices from
+    @return: dictionary with index information or None
+    """
+
+    result = {}
+
+    cmd = u"SELECT name, sql FROM sqlite_master " \
+           "WHERE type = 'index' AND tbl_name = '%s' AND sql IS NOT NULL" \
+          % table
+
+    cursor = self._connection.makecursor (cmd)
+
+    try:
+      for rs in cursor.fetchall ():
+        ixm = self._INDEX.match (rs [1])
+        if ixm is not None:
+          (unique, name, fields) = ixm.groups ()
+          result [name] = { \
+              'unique' : unique is not None and unique.lower () == 'unique',
+              'primary': False,
+              'fields' : [f.strip () for f in fields.split (',')]}
+
+    finally:
+      cursor.close ()
+
+    return len (result.keys ()) and result or None