Opened 8 years ago

Closed 7 years ago

#5062 closed (invalid)

New backend for Sql Server using pymssql

Reported by: mamcx Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords: ms sql server feature
Cc: Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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?

Attachments (8)

NewMSSQL.diff (38.0 KB) - added by mamcx 8 years ago.
Integration older patch + enabling of this new backend
pymssql.diff (3.6 KB) - added by mamcx 8 years ago.
Fixed pymssql. Install from site and then apply this
NewMSSQL.2.diff (63.9 KB) - added by mamcx 8 years ago.
New diff with limit/offset emulation
pymssql.py (9.4 KB) - added by mamcx <http://www.elmalabarista.com> 8 years ago.
Complete pymmsql file
mssql.r5986.diff (32.5 KB) - added by gregoire@… 8 years ago.
Patch for r5986
pymssql.2.py (9.9 KB) - added by gregoire@… 8 years ago.
pymssql.py with conversion to latin-1 as unicode error workaround
pymssql.3.py (9.9 KB) - added by gregoire@… 8 years ago.
pymssql.py with conversion to latin-1 as unicode error workaround and fixes indentation error
mssql.r5986-2.diff (32.9 KB) - added by gregoire@… 8 years ago.
fixes in db/models/base.py for quote_name() and working implementation of sql_flush

Download all attachments as: .zip

Change History (22)

Changed 8 years ago by mamcx

Integration older patch + enabling of this new backend

Changed 8 years ago by mamcx

Fixed pymssql. Install from site and then apply this

comment:1 Changed 8 years ago by anonymous

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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:

Changed 8 years ago by mamcx

New diff with limit/offset emulation

comment:2 Changed 8 years ago by 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.

comment:3 Changed 8 years ago by 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

comment:4 Changed 8 years ago by 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

Changed 8 years ago by mamcx <http://www.elmalabarista.com>

Complete pymmsql file

comment:5 Changed 8 years ago by 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...

comment:6 Changed 8 years ago by 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

comment:7 Changed 8 years ago by 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'>

Changed 8 years ago by gregoire@…

Patch for r5986

Changed 8 years ago by gregoire@…

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

comment:8 Changed 8 years ago by 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 ...

Changed 8 years ago by gregoire@…

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

Changed 8 years ago by gregoire@…

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

comment:9 Changed 8 years ago by 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.

comment:10 Changed 8 years ago by 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.

comment:11 Changed 8 years ago by 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

comment:12 Changed 7 years ago by 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.

comment:13 Changed 7 years ago by PhiR

  • Keywords feature added

comment:14 Changed 7 years ago by jacob

  • Resolution set to invalid
  • Status changed from new to closed

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

Note: See TracTickets for help on using tickets.
Back to Top