New backend for Sql Server using pymssql

[mamcx]

Related to ​this thread I code a new backend for Sql Server using Sql Server.

This is a alpha version... and I put it here so can get early feedback. Is not production ready.

Hacked on

• Sql Server 2000 SP1
  
• WinXP PRO 64 bits
  
• Python 2.4
  
• Django rev 5783
  

What's on:

• Merge of several code & tickets around, including the original ado_mssql code as base.
  
• Based on ​pymssql 0.8.0 so in theroy must work across plataforms. This have some bugs and issues so please install it from pymssql site and then apply the patch here. Include detection of boolean types, support of autocommit, splits on GO, and not mask python errors as sql server fault.
  
• Fix problems with dateformat on nonenglish sql instalations.
  
• Tested using included django\tests>runtests.py, with the tutorial, satchmo rev 590 with not fatal errors.
  
• Detection of Sql Server version, so later can apply optimizations based on this information.
  
• Implemented client.py (so python manage.py dbshell work).
  

What's left

• No LIMIT/OFFSET emulation. I'm learning about the Oracle backend for apply a similar aproach. I think however is a doable task with the refactoring of query class.
  
• No get_deferrable_sql (aka CASCADE delete/update on relations). Sql Server is very sensitive about circular references.
  
• Testing against Sql 2005 and on linux.
  
• Login using integrated security (on windows)
  
• Check what happend with multiples collations
  

What's uggly

• Barney and their friends
  
• Seriously, check db\models line 242+: How detect properly autoinc fields?
  

[mamcx]

Integration older patch + enabling of this new backend

[mamcx]

Fixed pymssql. Install from site and then apply this

[anonymous]

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:

[mamcx]

New diff with limit/offset emulation

[mamcx (http://www.elmalabarista.com)]

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.

[anonymous]

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

[anonymous]

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

[mamcx <http://www.elmalabarista.com>]

Complete pymmsql file

[mamcx <http://www.elmalabarista.com>]

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...

[anonymous]

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

[anonymous]

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'>

[gregoire@…]

Patch for r5986

[gregoire@…]

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

[anonymous]

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 ...

[gregoire@…]

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

[gregoire@…]

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

[gregoire@…]

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.

[Filip Wasilewski <filip.wasilewski@…>]

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.

[mamcx (http://www.elmalabarista.com)]

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

Is not good have 2 backends doing the same things

[http://www.elmalabarista.com]

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.

[Jacob]

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/).