Code

Opened 8 years ago

Closed 6 years ago

#2358 closed defect (invalid)

[patch] MS-SQL server fixes

Reported by: Dan Hristodorescu danh@… Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: normal Keywords:
Cc: moof@… Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: UI/UX:

Description

I attached a patch to try to make django work with MS-SQL server.
The patch is for these issues:

  • The backend didn't create the proper wrapper cursor to replace parameter placeholders to ?
  • MS-SQL doesn't support microseconds, so I added it like it was already in for MySQL. Since there are already two databases that need this I would suggest to refactor the code and add a flag in the backend drivers for this.
  • In django/db/models/base.py line 175: cursor.execute("SELECT 1 FROM %s WHERE %s=%%s LIMIT 1"


In SQL server should be SELECT TOP 1 instead of LIMIT although I don't understand why one would put a limit on an exact match on the primary key. It could only return one row anyway. I removed it but someone should check if it's ok.

Attachments (16)

mssql.diff (3.1 KB) - added by Dan Hristodorescu 8 years ago.
mssql_updated.diff (4.3 KB) - added by Dan Hristodorescu 8 years ago.
updated
mssql_update1.diff (4.4 KB) - added by Dan Hristodorescu 8 years ago.
forgot brackets and added microseconds check
mssql_update2.diff (12.0 KB) - added by sdelatorre+django@… 8 years ago.
[patch]The patch mentioned in the last message.
mssql_update3.diff (12.4 KB) - added by sdelatorre+django@… 8 years ago.
Added a patch for queries that use a datetime.datetime object. Because MSSQL can't parse the microseconds from a datetime object, the microseconds need to be set to 0 before being used in a query. Also re-ran the diff against the SVN trunk for all the changes made in this ticket.
mssql_update4.diff (13.4 KB) - added by sdelatorre+django@… 8 years ago.
I made a slight modification to the django.db.backends.util file to work with custom mssql queries. In general, the CursorDebugWrapper attempts to record the full SQL statement that was executed by performing a string substitution on the SQL statement with its query parameters. This doesn't work with mssql queries because they use a '?' for string substitution instead of the typical Python '%s'. The new changes introduced in this patch change all ‘?’ in mssql queries to ‘%s’ before the substitution is executed. The entire patch have also been updated against the latest SVN trunk.
mssql_update5.diff (14.0 KB) - added by sdelatorre+django@… 8 years ago.
Added another fix to the util.py file. If '%' wildcards are used in a custom query, the CursorDebugWrapper.execute function fails. This was patched by wrapping the string substituion in a try/except block to prevent the exception from halting execution.
mssql.patch (125.7 KB) - added by anonymous 8 years ago.
mssql_update6.diff (13.7 KB) - added by sdelatorre+django@… 7 years ago.
Added another CoInitialize()/CoUninitialize() wrapper around the executeHelper function to fix related errors. Updated the diff against revision 4459.
mssql_update6a.diff (14.1 KB) - added by wycharon@… 7 years ago.
modified backends/ado_mssq/base : 1. changes 'import adodbapi as Database' to 'import adodbapi.adodbapi as Database'. 2. in variantToPython,add explicitly DEFAULT_CHARSET translate for unicode
mssql_update6b.diff (14.2 KB) - added by wycharon@… 7 years ago.
i am sorry. in mssql_update6a.diff. I forgot importing django.conf.settings
mssql_update7.diff (14.6 KB) - added by wycharon@… 7 years ago.
modified function get_last_insert_id(). use IDENT_CURRENT(..) instead of @@IDENTITY. if someone deploy a sync insert trigger on the table, there is another insert sql after orignal insert,so @@IDENTITY is error prone
mssql_update8.diff (575 bytes) - added by tclancy@… 7 years ago.
Fixed typo in ado_mssql\base.py
mssql_update8a.diff (14.5 KB) - added by wycharon@… 7 years ago.
full pacth for typo. i am sorry. it's my mistake for the typo. i will re upload the mssql_update7.diff
mssql_update9_NOT_WORKING.patch (18.4 KB) - added by moof@… 7 years ago.
Not working patch that resolves some issues towards getting the test suite running.
patch_9_test_output.txt (10.2 KB) - added by moof@… 7 years ago.
test output at --verbose=2 from running patch 9

Download all attachments as: .zip

Change History (31)

Changed 8 years ago by Dan Hristodorescu

Changed 8 years ago by Dan Hristodorescu

updated

comment:1 Changed 8 years ago by Dan Hristodorescu

Now it's working with MS-SQL but I had to remove the microseconds in session handling in django/contrib/sessions/middleware.py

I don't think it's a big deal in session expiration just being millisecond accurate.
The datetime handling should be improved to handle these database differences.

Changed 8 years ago by Dan Hristodorescu

forgot brackets and added microseconds check

comment:2 Changed 8 years ago by sdelatorre+django@…

This patch worked out great for me. I completed the rest of the intropection.py functionality in patch #2563.

comment:3 Changed 8 years ago by sdelatorre+django@…

After going live with this patch, I ran into a frustrating error. I found that I would randomly get connection failures from the adodbapi lib. After further investigation, the error tunred out to be "com_error: 'CoInitialize has not been called.'". To remedy the situation, the pythoncom.CoInitialize() and pythoncom.CoUninitialize() functions need to be called before/after the database connection is established. I've attached a patch for this, and merged my changes from Ticket #2563 into this one since they target the same functionality.

Changed 8 years ago by sdelatorre+django@…

[patch]The patch mentioned in the last message.

Changed 8 years ago by sdelatorre+django@…

Added a patch for queries that use a datetime.datetime object. Because MSSQL can't parse the microseconds from a datetime object, the microseconds need to be set to 0 before being used in a query. Also re-ran the diff against the SVN trunk for all the changes made in this ticket.

comment:4 Changed 8 years ago by anonymous

Great job! But it does lack of pagination support, doesn't it? (get_limit_offset_sql() returns "".) I don't think there is any 'easy' sql statements that can manipulate the task well on SQL Server. Maybe using a server-side cursor would be a quick & dirty solution?

comment:5 Changed 8 years ago by sdelatorre+django@…

But it does lack of pagination support, doesn't it? (get_limit_offset_sql() returns "".) I don't think there is any 'easy' sql statements that can manipulate the task well on SQL Server. Maybe using a server-side cursor would be a quick & dirty solution?

Yes, it still lacks pagination support. A server-side cursor does seem to be the best option right now, though I don't have any time to test it out at the moment.

Changed 8 years ago by sdelatorre+django@…

I made a slight modification to the django.db.backends.util file to work with custom mssql queries. In general, the CursorDebugWrapper attempts to record the full SQL statement that was executed by performing a string substitution on the SQL statement with its query parameters. This doesn't work with mssql queries because they use a '?' for string substitution instead of the typical Python '%s'. The new changes introduced in this patch change all ‘?’ in mssql queries to ‘%s’ before the substitution is executed. The entire patch have also been updated against the latest SVN trunk.

Changed 8 years ago by sdelatorre+django@…

Added another fix to the util.py file. If '%' wildcards are used in a custom query, the CursorDebugWrapper.execute function fails. This was patched by wrapping the string substituion in a try/except block to prevent the exception from halting execution.

Changed 8 years ago by anonymous

comment:6 Changed 7 years ago by anonymous

Changed 7 years ago by sdelatorre+django@…

Added another CoInitialize()/CoUninitialize() wrapper around the executeHelper function to fix related errors. Updated the diff against revision 4459.

comment:7 Changed 7 years ago by wycharon@…

there are two problems in mssql_update6:

  1. in django.db.backends.ado_mssql,we should import adodbapi.adodbapi other than adodbapi,

line10:

import adodbapi as Database

should be:

import adodbapi.adodbapi as Database

otherwise,any change to attrubites of Database has no effect

  1. function variantToPython should handle charset translate. in charset other than ascii,we should translate unicode to DEFAULT_CHARSET explicitly. As follows:

...
from django.conf import settings
...
if type(res) == unicode:

return isinstance(res,unicode) and res.encode(settings.DEFAULT_CHARSET) or res

comment:8 Changed 7 years ago by wycharon@…

i am sorry. the last two lines should be: if type(res) == unicode:return es.encode(settings.DEFAULT_CHARSET)

Changed 7 years ago by wycharon@…

modified backends/ado_mssq/base : 1. changes 'import adodbapi as Database' to 'import adodbapi.adodbapi as Database'. 2. in variantToPython,add explicitly DEFAULT_CHARSET translate for unicode

Changed 7 years ago by wycharon@…

i am sorry. in mssql_update6a.diff. I forgot importing django.conf.settings

Changed 7 years ago by wycharon@…

modified function get_last_insert_id(). use IDENT_CURRENT(..) instead of @@IDENTITY. if someone deploy a sync insert trigger on the table, there is another insert sql after orignal insert,so @@IDENTITY is error prone

Changed 7 years ago by tclancy@…

Fixed typo in ado_mssql\base.py

Changed 7 years ago by wycharon@…

full pacth for typo. i am sorry. it's my mistake for the typo. i will re upload the mssql_update7.diff

comment:9 Changed 7 years ago by Simon G. <dev@…>

  • Triage Stage changed from Unreviewed to Accepted

Reading the comments here, it looks like this is ready for checkin, but can someone with MS-SQL see if this passes all tests?

comment:10 Changed 7 years ago by moof@…

  • Patch needs improvement set

Not Quite There Yet. Still giving a bunch of errors here, which I'm slowly starting to fix. Inserting values for AutoFields needs massaging, and a couple of other things.

I have a big beef with the flush commands, right now, as MSSQL doesn't cascade by default, so you have to explicitly ask for it on table creation. I'm also running into a weird bug involving missing references on database creation.

I'll post up a patch when I manage to fix that.

Though this might have something to do with the thread bullying that's been done in the patch I downloaded, as it looks like a classic race condition to me.

comment:11 Changed 7 years ago by moof@…

  • Cc moof@… added

OK, so far I haven't managed to run the test suite, but I offer my improvements on the patch that get it a few steps further into running it. Specifically, I've got AutoField insertion working OK, and got the flush command sort of working. I've also updated it to run with the latest svn HEAD - #4757

This Patch does not fully work

I have come into a problem whereby the standard test suite tries to create a database and fails. I attach test_output.txt run at --verbose=3 so people can see what I mean, I can't create test databases. Specifically, for some reason the application is attempting to create comments_karmascore before comments_comment, which sounds like a race condition to me, possibly at the driver level or database level, as the output shows that the tables creation command is issued. Inspecting the database after the run shows that none of the comment tables are created.

I don't know enough about Django internals to know quite how threading is handled in this matter, or transactions. I'll try asking on the mailing list, but I attach this patch in case anyone else wishes to give it a try.

For the record, I'm using SQL Server 2005 Express Edition.

Changed 7 years ago by moof@…

Not working patch that resolves some issues towards getting the test suite running.

Changed 7 years ago by moof@…

test output at --verbose=2 from running patch 9

comment:12 Changed 7 years ago by anonymous

When will all this be ready and usable?

comment:13 Changed 7 years ago by ubernostrum

Please do not ask "when will this be finished" or "when will this merge" questions in the ticket tracker; such discussion is more appropriate for the django-developers list, and having it there keeps noise down in the ticket system.

comment:14 Changed 7 years ago by cwt@…

I found that datetime and smalldatetime are not converted correctly. I try to change both in creation.py I got this error messages while create django admin user (syncdb is OK)...

com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider

for SQL Server', 'Syntax error converting character string to smalldatetime data

type.', None, 0, -2147217913), None)

I use django from SVN and adodbpai 2.0.1 from http://sourceforge.net/projects/adodbapi
It seem like there are some bugs in adodbapi. But I'm not so sure.

Hope this can help.

comment:15 Changed 6 years ago by jacob

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

This is too "big" a feature for a ticket. The best thing to do with this is to create an external project with the mssql backend (see http://www.djangoproject.com/documentation/settings/#database-engine). Ask on django-dev if you need help doing this.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.