New backend for Sql Server using pymssql

Integration older patch + enabling of this new backend

Fixed pymssql. Install from site and then apply this

1) in django/db/models/fields

              # mssql can not handle more than two 99-s
              return ['%s-01-01 00:00:00' % value, '%s-12-31 23:59:59.99' % value] 

2) in base.py cursor class:

    def execute(self, query, params=None):
        query = self._convert_limit(query)
        return self.cursor.execute(query, params)

    def executemany(self, query, params=None):
        query = self._convert_limit(query)
        return self.cursor.executemany(query, params)

    def _convert_limit(self,query):
        if query.count("LIMIT ") != 0 and query.upper().count("SELECT ") != 0:
            t = query.split();
            query = "SELECT TOP "+ t[len(t)-1]+ query[6:query.rfind("LIMIT ")]
        return query

....

def get_limit_offset_sql(limit=None, offset=None):
    # we support limit only at the moment
    if limit != None :
        if offset != None:
            sql = "LIMIT %s" % str(int(offset)+int(limit))
        else:
            sql = "LIMIT %s" % limit
        return sql
    else:
        return ""

3) pymssql.py mod to handle unicode strings:

 def _quote(x):
     if type(x) == types.StringType or type(x) == types.UnicodeType:

New diff with limit/offset emulation

New diff with experimental support for LIMIT/OFFSET for Sql Server. Switch to native Sql 2005 implementation if is connected to a Sql 2005 instance.

Hi,

in NewMSSQL2.diff you also need to create an empty __init__.py in db.backends.mssql, otherwise you get the following error:

Traceback (most recent call last):
  File "./manage.py", line 11, in ?
    execute_manager(settings)
  File "/var/lib/python-support/python2.4/django/core/management.py", line 1725, in execute_manager
    execute_from_command_line(action_mapping, argv)
  File "/var/lib/python-support/python2.4/django/core/management.py", line 1614, in execute_from_command_line
    action_mapping[action]()
  File "/var/lib/python-support/python2.4/django/core/management.py", line 1317, in dbshell
    from django.db import runshell
  File "/var/lib/python-support/python2.4/django/db/__init__.py", line 11, in ?
    backend = __import__('django.db.backends.%s.base' % settings.DATABASE_ENGINE, {}, {}, [''])
ImportError: No module named mssql.base

Are you sure used pymssql-0.8.0 as baseline for your pymssql patch? It doesn't apply cleanly here:

patch -p0 -b --dry-run < ../../pymssql.diff
(Stripping trailing CRs from patch.)
patching file pymssql.py
Hunk #1 FAILED at 133.
Hunk #2 FAILED at 227.
Hunk #3 FAILED at 253.
Hunk #4 FAILED at 269.
Hunk #5 FAILED at 284.
5 out of 5 hunks FAILED -- saving rejects to file pymssql.py.rej

Complete pymmsql file

I upload the complete pymmssql file I'm using.

Also, I add the init.py file in the diff but for some reason not appear here...

Just add an empty comment in init.py and it will show up in the diff ;)

I'll let you know how it works out

File "D:\Python25\lib\site-packages\django\db\backends\mssql\base.py", line 755, in iterator

cursor.execute(full_query, params)

File "D:\Python25\lib\site-packages\django\db\backends\util.py", line 20, in execute

return self.cursor.execute(sql, params)

File "D:\Python25\lib\site-packages\pymssql.py", line 126, in execute

self.executemany(operation, (params,))

File "D:\Python25\lib\site-packages\pymssql.py", line 157, in executemany

raise e

InterfaceError?: do not know how to handle type <type 'unicode'>

Patch for r5986

pymssql.py with conversion to latin-1 as unicode error workaround

I have done some work to make this patch work with the new db wrapper.

I hacked pymssql.py since the previous patch for unicode doesn't work. It works if unicode data are actual ascii characters, but fails on any accent (like é in Grégoire). The problems belongs to _mssql module but I needed to make it work with any french characters so I converted the data to latin-1 in the database until someone writes something prettier ...

pymssql.py with conversion to latin-1 as unicode error workaround and fixes indentation error

fixes in db/models/base.py for quote_name() and working implementation of sql_flush

I uploaded a new version.

I had forgotten some refactoring for quote_name() in db/models/base.py.

I also added an working implementation for sql flush which disables the constraints before deleting data and reactivates them after.

I have added a similar patch for MS SQL Server backend using pyodbc. See #5246 for details as some changes can be backported here if needed.

So, what is best? Go ahead with this or put the efforts on #5246?

Is not good have 2 backends doing the same things

Ok, I think is best close it and put the effor on #5246. Looks like have more atention and based in my test the performance is good.

Like #5246 (and #1261), this is far too big a patch/change for our existing infrastructure to handle.

Luckily, there's a better way: turn this backend into an external project (hosted on Google Code, perhaps). Once it's bulletproof, clearly stable, and has active maintainers you can propose addition into Django proper (do so on django-dev). See http://www.pointy-stick.com/blog/2007/11/11/django-tip-external-database-backends/ and http://www.djangoproject.com/documentation/settings/#database-engine for information about how to turn this backend into an external project.

(Or, as you said, help out with #5246, which has become http://code.google.com/p/django-pyodbc/).