[SQL-CVS] r552 - home/phd/SQLObject/inheritance/sqlobject

[<sub...@co...>]

Author: phd
Date: 2005-01-24 12:10:39 +0000 (Mon, 24 Jan 2005)
New Revision: 552

Modified:
   home/phd/SQLObject/inheritance/sqlobject/classregistry.py
   home/phd/SQLObject/inheritance/sqlobject/col.py
   home/phd/SQLObject/inheritance/sqlobject/dbconnection.py
   home/phd/SQLObject/inheritance/sqlobject/main.py
Log:
Optimize inheritance - prefetch children using cursor.fetchmany()
instead of selecting them one by one.


Modified: home/phd/SQLObject/inheritance/sqlobject/classregistry.py
===================================================================
--- home/phd/SQLObject/inheritance/sqlobject/classregistry.py	2005-01-15 17:00:08 UTC (rev 551)
+++ home/phd/SQLObject/inheritance/sqlobject/classregistry.py	2005-01-24 12:10:39 UTC (rev 552)
@@ -108,3 +108,6 @@
 
 MasterRegistry = _MasterRegistry()
 registry = MasterRegistry.registry
+
+def findClass(name, class_registry=None):
+    return registry(class_registry).getClass(name)

Modified: home/phd/SQLObject/inheritance/sqlobject/col.py
===================================================================
--- home/phd/SQLObject/inheritance/sqlobject/col.py	2005-01-15 17:00:08 UTC (rev 551)
+++ home/phd/SQLObject/inheritance/sqlobject/col.py	2005-01-24 12:10:39 UTC (rev 552)
@@ -28,6 +28,7 @@
 # arguments in this module, so we rename it:
 import constraints as consts
 from include import validators
+from classregistry import findClass
 
 NoDefault = sqlbuilder.NoDefault
 True, False = 1==1, 0==1
@@ -617,7 +618,6 @@
         SOKeyCol.__init__(self, **kw)
 
     def postgresCreateSQL(self):
-        from main import findClass
         sql = SOKeyCol.postgresCreateSQL(self)
         other = findClass(self.foreignKey)
         tName = other._table
@@ -643,7 +643,6 @@
         return sql
 
     def sybaseCreateSQL(self):
-        from sqlobject.main import findClass
         sql = SOKeyCol.sybaseCreateSQL(self)
         other = findClass(self.foreignKey)
         tName = other._table
@@ -655,7 +654,6 @@
         return sql
 
     def maxdbCreateSQL(self):
-        from main import findClass
         other = findClass(self.foreignKey)
         fidName = self.dbName
         #I assume that foreign key name is identical to the id of the reference table

Modified: home/phd/SQLObject/inheritance/sqlobject/dbconnection.py
===================================================================
--- home/phd/SQLObject/inheritance/sqlobject/dbconnection.py	2005-01-15 17:00:08 UTC (rev 551)
+++ home/phd/SQLObject/inheritance/sqlobject/dbconnection.py	2005-01-24 12:10:39 UTC (rev 552)
@@ -15,6 +15,7 @@
 from converters import sqlrepr
 import urllib
 import weakref
+from classregistry import findClass
 
 warnings.filterwarnings("ignore", "DB-API extension cursor.lastrowid used")
 
@@ -486,33 +487,89 @@
                     pass
 
 class Iteration(object):
+    #phd: default array size for cursor.fetchmany()
+    defaultArraySize = 10000
 
     def __init__(self, dbconn, rawconn, select, keepConnection=False):
         self.dbconn = dbconn
         self.rawconn = rawconn
         self.select = select
         self.keepConnection = keepConnection
-        self.cursor = rawconn.cursor()
+        self.cursor = cursor = rawconn.cursor()
         self.query = self.dbconn.queryForSelect(select)
         if dbconn.debug:
             dbconn.printDebug(rawconn, self.query, 'Select')
-        self.dbconn._executeRetry(self.rawconn, self.cursor, self.query)
+        self.dbconn._executeRetry(self.rawconn, cursor, self.query)
+        cursor.arraysize = self.defaultArraySize
+        self._results = []
+        #phd: find the index of the childName column
+        childNameIdx = None
+        columns = select.sourceClass._SO_columns
+        for i in range(len(columns)): #phd: enumerate() is unavailable python 2.2
+            if columns[i].name == "childName":
+                childNameIdx = i
+                break
+        self._childNameIdx = childNameIdx
 
     def __iter__(self):
         return self
 
     def next(self):
-        result = self.cursor.fetchone()
-        if result is None:
+        lazyColumns = self.select.ops.get('lazyColumns', 0)
+        if not self._results:
+            self._results = list(self.cursor.fetchmany())
+            if not lazyColumns: self.fetchChildren()
+        if not self._results:
             self._cleanup()
             raise StopIteration
-        if self.select.ops.get('lazyColumns', 0):
+        result = self._results[0]
+        del self._results[0]
+        if lazyColumns:
             obj = self.select.sourceClass.get(result[0], connection=self.dbconn)
             return obj
         else:
-            obj = self.select.sourceClass.get(result[0], selectResults=result[1:], connection=self.dbconn)
+            id = result[0]
+            if id in self._childrenResults:
+                childResults = self._childrenResults[id]
+                del self._childrenResults[id]
+            else:
+                childResults = None
+            obj = self.select.sourceClass.get(id, selectResults=result[1:],
+                childResults=childResults, connection=self.dbconn)
             return obj
 
+    def fetchChildren(self):
+        """Prefetch childrens' data
+
+        Fetch childrens' data for every subclass in one big .select()
+        to avoid .get() fetching it one by one.
+        """
+        self._childrenResults = {}
+        if self._childNameIdx is None:
+            return
+        childIdsNames = {}
+        childNameIdx = self._childNameIdx
+        for result in self._results:
+            childName = result[childNameIdx+1]
+            if childName:
+                ids = childIdsNames.get(childName)
+                if ids is None:
+                    ids = childIdsNames[childName] = []
+                ids.append(result[0])
+        dbconn = self.dbconn
+        rawconn = self.rawconn
+        cursor = rawconn.cursor()
+        registry = self.select.sourceClass._registry
+        for childName, ids in childIdsNames.items():
+            klass = findClass(childName, registry)
+            select = klass.select(sqlbuilder.IN(sqlbuilder.SQLConstant("id"), ids))
+            query = dbconn.queryForSelect(select)
+            if dbconn.debug:
+                dbconn.printDebug(rawconn, query, 'Select children of the class %s' % childName)
+            self.dbconn._executeRetry(rawconn, cursor, query)
+            for result in cursor.fetchall():
+                self._childrenResults[result[0]] = result[1:]
+
     def _cleanup(self):
         if getattr(self, 'query', None) is None:
             # already cleaned up

Modified: home/phd/SQLObject/inheritance/sqlobject/main.py
===================================================================
--- home/phd/SQLObject/inheritance/sqlobject/main.py	2005-01-15 17:00:08 UTC (rev 551)
+++ home/phd/SQLObject/inheritance/sqlobject/main.py	2005-01-24 12:10:39 UTC (rev 552)
@@ -34,6 +34,7 @@
 import joins
 import index
 import classregistry
+findClass = classregistry.findClass # for those who imported findClass from sqlobject.main
 
 import sys
 if sys.version_info[:3] < (2, 2, 0):
@@ -315,9 +316,7 @@
                     delFunc(obj, var)
                     break
 
-def findClass(name, registry=None):
-    #assert classRegistry.get(registry, {}).has_key(name), "No class by the name %s found (I have %s)" % (repr(name), ', '.join(map(str, classRegistry.keys())))
-    return classregistry.registry(registry).getClass(name)
+#phd: findClass has been moved to classregistry to avoid circular import
 
 def findDependencies(name, registry=None):
     depends = []
@@ -406,7 +405,7 @@
     # aren't using integer IDs
     _idType = int
 
-    def get(cls, id, connection=None, selectResults=None, childUpdate=False):
+    def get(cls, id, connection=None, selectResults=None, childResults=None, childUpdate=False):
 
         assert id is not None, 'None is not a possible id for %s' % cls.__name
 
@@ -434,7 +433,7 @@
         if hasattr(val, 'childName'):
              childName = val.childName
              if childName is not None:
-                 return val._childClasses[childName].get(id)
+                 return val._childClasses[childName].get(id, selectResults=childResults)
         #DSM: Now, we know we are alone or the last child in a family...
         #DSM: It's time to find our parents
         inst = val