Commits

Michael Manfre committed 62d598a

Fixed #15 - Added RawStoredProcedureManager

This manager adds raw_callproc(), which behaves the same as raw(), except that
it will execute the stored procedure and look at the first result set to create
instances of the model. The stored procedure should return the model primary
key and any other required fields. The stored procedure return value is
ignored.

  • Participants
  • Parent commits a5b68fd

Comments (0)

Files changed (6)

File docs/changelog.txt

 
 - Ensure master connection connects to the correct database name when TEST_NAME is not defined.
 - Connection.close() will now reset adoConn to make sure it's gone before the CoUninitialize.
+- Added :ref:`rawstoredproceduremanager`, which provides ``raw_callproc`` that works the same as ``raw``, except
+  expects the name of a stored procedure that returns a result set that matches the model.
 
 v1.1
 ----

File docs/usage.txt

             result_set = cursor.fetchall()
         finally:
             cursor.close()
+
+.. _rawstoredproceduremanager:
+
+RawStoredProcedureManager
+-------------------------
+
+The ``RawStoredProcedureManager`` provides the ``raw_callproc`` method that will take the name
+of a stored procedure and use the result set that it returns to create instances of the model.
+
+Example:
+
+    .. code-block:: python
+    
+        from sqlserver_ado.models import RawStoredProcedureManager
+        
+        class MyModel(models.Model):
+            ...
+            
+            objects = RawStoredProcedureManager()
+
+        sproc_params = [1, 2, 3]
+        MyModel.objects.raw_callproc('uspGetMyModels', sproc_params)

File sqlserver_ado/dbapi.py

 class DataError(DatabaseError): pass
 class NotSupportedError(DatabaseError): pass
 
+class FetchFailedError(Error): 
+    """
+    Error is used by RawStoredProcedureQuerySet to determine when a fetch
+    failed due to a connection being closed or there is no record set 
+    returned.
+    """
+    pass
+
 class _DbType(object):
     def __init__(self,valuesTuple):
         self.values = valuesTuple
         self.rowcount = -1
         self.rs = recordset
         desc = list()
-
+        
         for f in self.rs.Fields:
             display_size = None
             if not(self.rs.EOF or self.rs.BOF):
             null_ok = bool(f.Attributes & adFldMayBeNull)
 
             desc.append( (f.Name, f.Type, display_size, f.DefinedSize, f.Precision, f.NumericScale, null_ok) )
+            
         self.description = desc
 
     def close(self):
         rows -- Number of rows to fetch, or None (default) to fetch all rows.
         """
         if self.connection is None or self.rs is None:
-            self._raiseCursorError(Error, None)
+            self._raiseCursorError(FetchFailedError, 'Attempting to fetch from a closed connection or empty record set')
             return
 
         if self.rs.State == adStateClosed or self.rs.BOF or self.rs.EOF:

File sqlserver_ado/models/__init__.py

+from sqlserver_ado.models.manager import RawStoredProcedureManager
+from sqlserver_ado.models.query import RawStoredProcedureQuerySet

File sqlserver_ado/models/manager.py

+from django.db.models import Manager
+from sqlserver_ado.models.query import RawStoredProcedureQuerySet
+
+class RawStoredProcedureManager(Manager):
+    """
+    Adds raw_callproc, which behaves the same as Manager.raw, but relies upon
+    stored procedure that returns a single result set.
+    """
+    def raw_callproc(self, proc_name, params=None, *args, **kwargs):
+        """
+        Execute a stored procedure that returns a single resultset that can be 
+        used to load the current Model. The return value from the stored
+        procedure will be ignored.
+        
+        proc_name is expected to be properly quoted.
+        """
+        return RawStoredProcedureQuerySet(raw_query=proc_name, model=self.model, params=params, using=self._db, 
+            *args, **kwargs)

File sqlserver_ado/models/query.py

+from django.db import connections, router
+from django.db.models import sql
+from django.db.models.query import RawQuerySet
+from django.db.models.query_utils import deferred_class_factory, InvalidQuery
+
+from sqlserver_ado.dbapi import FetchFailedError
+
+__all__ = [
+    'RawStoredProcedureQuery',
+    'RawStoredProcedureQuerySet',
+]
+
+class RawStoredProcedureQuery(sql.RawQuery):
+    """
+    A single raw SQL stored procedure query
+    """
+    def clone(self, using):
+        return RawStoredProcedureQuery(self.sql, using, params=self.params)
+        
+    def __repr__(self):
+        return "<RawStoredProcedureQuery: %r %r>" % (self.sql, self.params)
+
+    def _execute_query(self):
+        """
+        Execute the stored procedure using callproc, instead of execute.
+        """
+        self.cursor = connections[self.using].cursor()
+        self.cursor.callproc(self.sql, self.params)
+
+
+class RawStoredProcedureQuerySet(RawQuerySet):
+    """
+    Provides an iterator which converts the results of raw SQL queries into
+    annotated model instances.
+    
+    raw_query should only be the name of the stored procedure.
+    """
+    def __init__(self, raw_query, model=None, query=None, params=None, translations=None, using=None):
+        self.raw_query = raw_query
+        self.model = model
+        self._db = using
+        self.query = query or RawStoredProcedureQuery(sql=raw_query, using=self.db, params=params)
+        self.params = params or ()
+        self.translations = translations or {}
+
+    def __iter__(self):
+        try:
+            for x in super(RawStoredProcedureQuerySet, self).__iter__():
+                yield x
+        except FetchFailedError:
+            # Stored procedure didn't return a record set
+            pass
+
+    def __repr__(self):
+        return "<RawStoredProcedureQuerySet: %r %r>" % (self.raw_query, self.params)
+
+    @property
+    def columns(self):
+        """
+        A list of model field names in the order they'll appear in the
+        query results.
+        """
+        if not hasattr(self, '_columns'):
+            try:
+                self._columns = self.query.get_columns()
+            except TypeError:
+                # "'NoneType' object is not iterable" thrown when stored procedure
+                # doesn't return a result set.            
+                # no result means no column names, so grab them from the model
+                self._columns = [self.model._meta.pk.db_column] #[x.db_column for x in self.model._meta.fields]
+
+            # Adjust any column names which don't match field names
+            for (query_name, model_name) in self.translations.items():
+                try:
+                    index = self._columns.index(query_name)
+                    self._columns[index] = model_name
+                except ValueError:
+                    # Ignore translations for non-existant column names
+                    pass
+
+        return self._columns