MS SQL Server backend using pyodbc.

[Filip Wasilewski <filip.wasilewski@…>]

Note: The code is partially based on the #5062 ticket with several fixes, modifications
and improvements applied.

Main features:

• Uses pyodbc (​http://pyodbc.sourceforge.net) as the database driver. This seems to be the most mature SQL Server driver for Python and is still maintained, which is a big plus compared to other SQL Server drivers like adodbapi and pymssql. Pyodbc is also used by SQLAlchemy for SQL Server connections.
• Native Unicode support. Every string that goes in is stored as Unicode, and every string that goes out of the database is returned as Unicode. No conversion to/from intermediate encodings takes place, so things like max_length in CharFiled works just like expected.
• Limit/offset supported in SQL Server 2005 and SQL Server 2000. Yay!
• Both Windows Authentication (Integrated Security) and SQL Server Authentication supported.
• Passes most of the model test cases. The tests that don't work out of the box are:
  • empty: Corner-case. As far as I know SQL Server 2005 does not support that. There seems to be little use of it at all.
  • lookup: Regular expressions are not supported out of the box. Only simple wildcard matching with %, _ and [] character classes.
  • serializers: Forward references cause foreign key constraint violation.
• Tested with SQL Server 2005 Express SP2 and SQL Server 2000 SP4, Python 2.5 and Windows XP.

Open issues:

• SQL Server collation support. I have added a collation parameter to the Field constructor to see how this will work.
• Is case sensitive/insensitive comparison using explicit collations a sensible thing?
• Introspection not tested yet.
• Support for ODBC specific configuration -- see module doc in base.py for DATABASE_SETTINGS options.
• SQL Server 2005 specific: nvarchar(max) instead of ntext is used for storing TextField data. This allows for exact matching (__exact, =) on this field type. (Unicode text parameters are passed to the SQL Server as nvarchars, but the comparison of nvarchar with ntext is not supported.)
  Note: nvarchar(max) type is not supported on previous SQL Server versions and some additional magic may be required. For now the exact matching on TextFields will not work in older versions.

Almost there...

[Filip Wasilewski <filip.wasilewski@…>]

MS SQL Server backend patch

[Filip Wasilewski <filip.wasilewski@…>]

Exclude regular expressions tests

[Filip Wasilewski <filip.wasilewski@…>]

Please note that you may also need to apply the #5242 patch for syncdb command.

[pk11]

this is pretty cool! i dropped my pymssql version for this. I would say let's join our forces to make this the official django mssql backend.

so filip, let me know if you need any help to move things forward.

[Filip Wasilewski <filip.wasilewski@…>]

MS SQL Server backend patch, version 2.

[Filip Wasilewski <filip.wasilewski@…>]

Exclude regular expressions and empty model tests

[Filip Wasilewski <filip.wasilewski@…>]

Changes in the second patch version

• Added introspection. Note that introspection will resolve all datetime columns to DateTimeField, even though some of them might be used to store TimeField and DateField (there are no separate data types for time and date in MSSQL).

• Allow for MSSQL wildcards (%_[]) in __regex and __iregex.

• Added quoting of identifiers and parameters in several places.

• Client module for manage.py dbshell.

• Some general cleanup & comments.

There are still some TODOs spread across the source code files, so your help and comments with these would be very appreciated (pk11?:-)).

[anonymous]

Thanks for the great work. I just tested both patch1 and patch2. It works fine for doing the tutorial, except when I hit the History button in admin, it will give me the following error:

('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. (306)')

Do you have any idea?

[Filip Wasilewski <filip.wasilewski@…>]

Thanks for pointing this out. This error is a known issue when working with MSSQL 2000, although I didn't spot that it occurs in the admin interface.

The data type used to store TextFields in SQL Server 2000 is ntext. On the other hand Python Unicode strings are passed to SQL Server via pyodbc as nvarchars. The problem is when someone tries to compare this two different data types for equality with the "=" operator. The following will raise an error in MSSQL 2000 and 2005:

SELECT 1 WHERE cast('a' as ntext) = N'a'

In SQL Server 2005 there is a new [n]varchar(max) data type which does not have such limitations:

SELECT 1 WHERE cast('a' as nvarchar(max)) = N'a'

and we can use that for TextFields. Also, according to the MSDN, the [n]text type is going to be removed in future versions of MSSQL.

There are several possible ways to solve this for MSSQL 2000:

1. Use a specific __exact operator for TextField.
   The exact matching is translated into "= %s", where "%s" is parameter placeholder (see operators in DatabaseWrapper). For the TextField it would need to be translated into "LIKE %s" statement or use explicit casting of parameter to ntext type - "= cast(%s as ntext)". This approach would require patching the get_where_clause function in django\db\models\query.py, which I would prefer to avoid.
   
   
2. Change the django admin model for LogEntry.
   I'm not sure about that, but maybe we don't need object_ids longer than 255 characters and could change the model a bit from:

   class LogEntry(models.Model):
       ...
       object_id = models.TextField(_('object id'), blank=True, null=True)
       ...
   

   to something like:

   class LogEntry(models.Model):
       ...
       object_id = models.CharField(_('object id'), blank=True, null=True, max_length=255)
       ...
   

Personally, I would incline to the second option as there is already quite a lot of backend-specific conditional statements in the django.db, but would like to hear how this may influence the admin interface functionality and how would work with other backends first.

Note: Regarding to the versions of the patch listed on this site, the highest number is the most recent patch version (currently mssql_pyodbc.2.patch) and previous versions should not be used. I don't have rights to replace or remove the older attachments and they are simply listed here as a ticket history.

[Filip Wasilewski <filip.wasilewski@…>]

MSSQL 2000: Fix for "History button" in the Admin interface -- second option.

[anonymous]

Thanks again. I switched to SQL Server 2005, which solved the problem. But then I just noticed that 'python manage.py syncdb' worked only on the first time. As I called it again, it tried to recreate the table 'auth_message' and errored, as the following:

C:\Programming\Python\django\mysite>python manage.py syncdb
Creating table auth_message
Traceback (most recent call last):

File "manage.py", line 11, in <module>

execute_manager(settings)

File "C:\Python25\lib\site-packages\django\core\management\init.py", line 180, in execute_manager

utility.execute(argv)

File "C:\Python25\lib\site-packages\django\core\management\init.py", line 124, in execute

command.execute(*args[1:], options.dict)

File "C:\Python25\lib\site-packages\django\core\management\base.py", line 33, in execute

output = self.handle(*args, options)

File "C:\Python25\lib\site-packages\django\core\management\base.py", line 117, in handle

return self.handle_noargs(options)

File "C:\Python25\lib\site-packages\django\core\management\commands\syncdb.py", line 66, in handle_noargs

cursor.execute(statement)

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

return self.cursor.execute(sql, params)

File "C:\Python25\lib\site-packages\django\db\backends\mssql\base.py", line 170, in execute

return self.cursor.execute(sql, params)

pyodbc.ProgrammingError: ('42S01', "[42S01] [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'auth_message' in the database. (2714)")

I saw also the same behaviour with SQL Server 2000. Looks like it doesn't recognized that the tables already exists. I tried to debug, but haven't got any further. Any hints? Thanks.

[anonymous]

Yes, I have submitted a patch #5242 (see the third comment from the top) some time ago that now awaits to be checked in. Applying this should fix the syncdb.

[Carl Karsten <carl@…>]

Does it make any sense to split this into abstract ODBC classes and then a set of subclasses for MsSQL?

I am considering making 2 or 3 other odbc modules (c-tree, VFP and Access). I am guessing this mssql_pyodbc odbc module is a good place to start, just wondering if there will be any code that should stay synchronized.

Also, it would be nice if there was a 'generic' odbc module that would give some functionality. For instance, I am not planning on implementing any write access, which I am sure will make it easier.

[DavidH]

I'm new to django and SVN and am interested in trying out mssql_pyodbc, as I would need to use django with a SQL server backend. When I tried to apply these patches to my working copy using patch.exe from GnuWin32, I got the error, "patch: Only garbage was found in the patch input." I suspect this is because my version is out of sync as that in the patch. I checked out the trunk at revision 6474. Is there a branch I should checkout instead, or do I just need a specific version of the trunk?

Any help here would be appreciated.

Thanks,
David Holt

[Jacob]

Quoting what I said about #1261 (since this is a similar situation):

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.

[pk11]

​http://code.google.com/p/django-pyodbc/

let me know if you want commit access.

[bob84123 <amagee@…>]

I'd really like to get this working (I'm prepared to help with development). It seems there are some files missing from the subversion repository so I can't actually run it. I'm writing here as I can't seem to find any way of contacting the developers from the google code page (or anywhere else).

[bob84123 <amagee@…>]

Oh.. when I said 'subversion repository' I mean the one at ​http://django-pyodbc.googlecode.com/svn/trunk/.