Opened 17 years ago

Closed 16 years ago

#5062 closed (invalid)

New backend for Sql Server using pymssql

Reported by: mamcx Owned by: nobody
Component: Database layer (models, ORM) Version: dev
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: no UI/UX: no

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 17 years ago.
Integration older patch + enabling of this new backend
pymssql.diff (3.6 KB ) - added by mamcx 17 years ago.
Fixed pymssql. Install from site and then apply this
NewMSSQL.2.diff (63.9 KB ) - added by mamcx 17 years ago.
New diff with limit/offset emulation
pymssql.py (9.4 KB ) - added by mamcx <http://www.elmalabarista.com> 17 years ago.
Complete pymmsql file
mssql.r5986.diff (32.5 KB ) - added by gregoire@… 17 years ago.
Patch for r5986
pymssql.2.py (9.9 KB ) - added by gregoire@… 17 years ago.
pymssql.py with conversion to latin-1 as unicode error workaround
pymssql.3.py (9.9 KB ) - added by gregoire@… 17 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@… 17 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)

by mamcx, 17 years ago

Attachment: NewMSSQL.diff added

Integration older patch + enabling of this new backend

by mamcx, 17 years ago

Attachment: pymssql.diff added

Fixed pymssql. Install from site and then apply this

comment:1 by anonymous, 17 years ago

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:

by mamcx , 17 years ago

Attachment: NewMSSQL.2.diff added

New diff with limit/offset emulation

comment:2 by mamcx (http://www.elmalabarista.com), 17 years ago

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 by anonymous, 17 years ago

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 by anonymous, 17 years ago

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

by mamcx <http://www.elmalabarista.com>, 17 years ago

Attachment: pymssql.py added

Complete pymmsql file

comment:5 by mamcx <http://www.elmalabarista.com>, 17 years ago

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 by anonymous, 17 years ago

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 by anonymous, 17 years ago

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

by gregoire@…, 17 years ago

Attachment: mssql.r5986.diff added

Patch for r5986

by gregoire@…, 17 years ago

Attachment: pymssql.2.py added

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

comment:8 by anonymous, 17 years ago

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

by gregoire@…, 17 years ago

Attachment: pymssql.3.py added

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

by gregoire@…, 17 years ago

Attachment: mssql.r5986-2.diff added

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

comment:9 by gregoire@…, 17 years ago

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 by Filip Wasilewski <filip.wasilewski@…>, 17 years ago

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 by mamcx (http://www.elmalabarista.com), 17 years ago

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 by http://www.elmalabarista.com, 17 years ago

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 by Philippe Raoult, 17 years ago

Keywords: feature added

comment:14 by Jacob, 16 years ago

Resolution: invalid
Status: newclosed

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