Opened 17 years ago

Closed 15 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: dev
Severity: normal Keywords:
Cc: moof@… Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

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 17 years ago.
mssql_updated.diff (4.3 KB) - added by Dan Hristodorescu 17 years ago.
updated
mssql_update1.diff (4.4 KB) - added by Dan Hristodorescu 17 years ago.
forgot brackets and added microseconds check
mssql_update2.diff (12.0 KB) - added by sdelatorre+django@… 17 years ago.
[patch]The patch mentioned in the last message.
mssql_update3.diff (12.4 KB) - added by sdelatorre+django@… 17 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@… 16 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@… 16 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 16 years ago.
mssql_update6.diff (13.7 KB) - added by sdelatorre+django@… 16 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@… 16 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@… 16 years ago.
i am sorry. in mssql_update6a.diff. I forgot importing django.conf.settings
mssql_update7.diff (14.6 KB) - added by wycharon@… 16 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@… 16 years ago.
Fixed typo in ado_mssql\base.py
mssql_update8a.diff (14.5 KB) - added by wycharon@… 16 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@… 16 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@… 16 years ago.
test output at --verbose=2 from running patch 9

Download all attachments as: .zip

Change History (31)

Changed 17 years ago by Dan Hristodorescu

Attachment: mssql.diff added

Changed 17 years ago by Dan Hristodorescu

Attachment: mssql_updated.diff added

updated

comment:1 Changed 17 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 17 years ago by Dan Hristodorescu

Attachment: mssql_update1.diff added

forgot brackets and added microseconds check

comment:2 Changed 17 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 17 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 17 years ago by sdelatorre+django@…

Attachment: mssql_update2.diff added

[patch]The patch mentioned in the last message.

Changed 17 years ago by sdelatorre+django@…

Attachment: mssql_update3.diff added

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 16 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 16 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 16 years ago by sdelatorre+django@…

Attachment: mssql_update4.diff added

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 16 years ago by sdelatorre+django@…

Attachment: mssql_update5.diff added

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

Attachment: mssql.patch added

comment:6 Changed 16 years ago by anonymous

Changed 16 years ago by sdelatorre+django@…

Attachment: mssql_update6.diff added

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

comment:7 Changed 16 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 16 years ago by wycharon@…

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

Changed 16 years ago by wycharon@…

Attachment: mssql_update6a.diff added

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 16 years ago by wycharon@…

Attachment: mssql_update6b.diff added

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

Changed 16 years ago by wycharon@…

Attachment: mssql_update7.diff added

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 16 years ago by tclancy@…

Attachment: mssql_update8.diff added

Fixed typo in ado_mssql\base.py

Changed 16 years ago by wycharon@…

Attachment: mssql_update8a.diff added

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 16 years ago by Simon G. <dev@…>

Triage Stage: UnreviewedAccepted

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 16 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 16 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 16 years ago by moof@…

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

Changed 16 years ago by moof@…

Attachment: patch_9_test_output.txt added

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

comment:12 Changed 16 years ago by anonymous

When will all this be ready and usable?

comment:13 Changed 16 years ago by James Bennett

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 16 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 15 years ago by Jacob

Resolution: invalid
Status: newclosed

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.

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