[Django Code] #5246: MS SQL Server backend using pyodbc.

[Django Code]

#5246: MS SQL Server backend using pyodbc.
----------------------------------------------------------+-----------------
Reporter: Filip Wasilewski <filip.wa...@gmail.com> | Owner: adrian
Status: new | Component: Database wrapper
Version: SVN | Keywords: mssql pyodbc database backend
Stage: Unreviewed | Has_patch: 1
----------------------------------------------------------+-----------------
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.)
[[BR]]
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...

--
Ticket URL: <http://code.djangoproject.com/ticket/5246>
Django Code <http://code.djangoproject.com/>
The web framework for perfectionists with deadlines

[Django Code]

#5246: MS SQL Server backend using pyodbc.

-------------------------------------------------------------+--------------

Reporter: Filip Wasilewski <filip.wa...@gmail.com> | Owner: adrian
Status: new | Component: Database wrapper

Version: SVN | Resolution:

Keywords: mssql pyodbc database backend | Stage: Unreviewed

Has_patch: 1 | Needs_docs: 0
Needs_tests: 0 | Needs_better_patch: 0
-------------------------------------------------------------+--------------
Changes (by Filip Wasilewski <filip.wa...@gmail.com>):

* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0

Comment:

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

--
Ticket URL: <http://code.djangoproject.com/ticket/5246#comment:1>

[Django Code]

#5246: MS SQL Server backend using pyodbc.

-------------------------------------------------------------+--------------

Reporter: Filip Wasilewski <filip.wa...@gmail.com> | Owner: adrian
Status: new | Component: Database wrapper

Version: SVN | Resolution:

Keywords: mssql pyodbc database backend | Stage: Unreviewed

Has_patch: 1 | Needs_docs: 0
Needs_tests: 0 | Needs_better_patch: 0
-------------------------------------------------------------+--------------

Comment (by 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.

--
Ticket URL: <http://code.djangoproject.com/ticket/5246#comment:2>

[Django Code]

#5246: MS SQL Server backend using pyodbc.

-------------------------------------------------------------+--------------

Reporter: Filip Wasilewski <filip.wa...@gmail.com> | Owner: adrian
Status: new | Component: Database wrapper

Version: SVN | Resolution:

Keywords: mssql pyodbc database backend | Stage: Unreviewed

Has_patch: 1 | Needs_docs: 0
Needs_tests: 0 | Needs_better_patch: 0
-------------------------------------------------------------+--------------

Comment (by Filip Wasilewski <filip.wa...@gmail.com>):

== 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 `TODO`s spread across the source code files, so your
help and comments with these would be very appreciated (pk11?:-)).

--
Ticket URL: <http://code.djangoproject.com/ticket/5246#comment:3>

[Django Code]

#5246: MS SQL Server backend using pyodbc.

-------------------------------------------------------------+--------------

Reporter: Filip Wasilewski <filip.wa...@gmail.com> | Owner: adrian
Status: new | Component: Database wrapper

Version: SVN | Resolution:

Keywords: mssql pyodbc database backend | Stage: Unreviewed

Has_patch: 1 | Needs_docs: 0
Needs_tests: 0 | Needs_better_patch: 0
-------------------------------------------------------------+--------------

Comment (by 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?

--
Ticket URL: <http://code.djangoproject.com/ticket/5246#comment:4>

[Django Code]

#5246: MS SQL Server backend using pyodbc.

-------------------------------------------------------------+--------------

Reporter: Filip Wasilewski <filip.wa...@gmail.com> | Owner: adrian
Status: new | Component: Database wrapper

Version: SVN | Resolution:

Keywords: mssql pyodbc database backend | Stage: Unreviewed

Has_patch: 1 | Needs_docs: 0
Needs_tests: 0 | Needs_better_patch: 0
-------------------------------------------------------------+--------------

Comment (by Filip Wasilewski <filip.wa...@gmail.com>):

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 `TextField`s in SQL Server 2000 is `ntext`. On
the other hand Python Unicode strings are passed to SQL Server via pyodbc
as `nvarchar`s. 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:
{{{
#!sql
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:
{{{
#!sql
SELECT 1 WHERE cast('a' as nvarchar(max)) = N'a'
}}}

and we can use that for `TextField`s. 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`.[[BR]]
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.[[BR]][[BR]]
1. Change the django admin model for `LogEntry`.[[BR]]
I'm not sure about that, but maybe we don't need `object_id`s longer
than 255 characters and could change the model a bit from:
{{{
#!python
class LogEntry(models.Model):
...
object_id = models.TextField(_('object id'), blank=True,
null=True)
...
}}}
to something like:
{{{
#!python
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.

--
Ticket URL: <http://code.djangoproject.com/ticket/5246#comment:5>

[Django Code]

#5246: MS SQL Server backend using pyodbc.

-------------------------------------------------------------+--------------

Reporter: Filip Wasilewski <filip.wa...@gmail.com> | Owner: adrian
Status: new | Component: Database wrapper

Version: SVN | Resolution:

Keywords: mssql pyodbc database backend | Stage: Unreviewed

Has_patch: 1 | Needs_docs: 0
Needs_tests: 0 | Needs_better_patch: 0
-------------------------------------------------------------+--------------

Comment (by 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.

--
Ticket URL: <http://code.djangoproject.com/ticket/5246#comment:6>

[Django Code]

#5246: MS SQL Server backend using pyodbc.

-------------------------------------------------------------+--------------

Reporter: Filip Wasilewski <filip.wa...@gmail.com> | Owner: adrian
Status: new | Component: Database wrapper

Version: SVN | Resolution:

Keywords: mssql pyodbc database backend | Stage: Unreviewed

Has_patch: 1 | Needs_docs: 0
Needs_tests: 0 | Needs_better_patch: 0
-------------------------------------------------------------+--------------
Comment (by 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.

--
Ticket URL: <http://code.djangoproject.com/ticket/5246#comment:7>

[Django Code]

#5246: MS SQL Server backend using pyodbc.

-------------------------------------------------------------+--------------
Reporter: Filip Wasilewski <filip.wa...@gmail.com> | Owner: nobody

Status: new | Component: Database wrapper

Version: SVN | Resolution:
Keywords: mssql pyodbc database backend feature | Stage: Design decision needed

Has_patch: 1 | Needs_docs: 0
Needs_tests: 0 | Needs_better_patch: 0
-------------------------------------------------------------+--------------

Changes (by PhiR):

* stage: Unreviewed => Design decision needed

Comment:



--
Ticket URL: <http://code.djangoproject.com/ticket/5246#comment:9>

[Django Code]

#5246: MS SQL Server backend using pyodbc.

-------------------------------------------------------------+--------------
Reporter: Filip Wasilewski <filip.wa...@gmail.com> | Owner: nobody

Status: new | Component: Database wrapper

Version: SVN | Resolution:
Keywords: mssql pyodbc database backend feature | Stage: Design decision needed
Has_patch: 1 | Needs_docs: 0
Needs_tests: 0 | Needs_better_patch: 0
-------------------------------------------------------------+--------------

Comment (by Carl Karsten <ca...@personnelware.com>):

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.

--
Ticket URL: <http://code.djangoproject.com/ticket/5246#comment:10>

[Django Code]

#5246: MS SQL Server backend using pyodbc.

-------------------------------------------------------------+--------------
Reporter: Filip Wasilewski <filip.wa...@gmail.com> | Owner: nobody

Status: new | Component: Database wrapper

Version: SVN | Resolution:
Keywords: mssql pyodbc database backend feature | Stage: Design decision needed
Has_patch: 1 | Needs_docs: 0
Needs_tests: 0 | Needs_better_patch: 0
-------------------------------------------------------------+--------------

Comment (by 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

--
Ticket URL: <http://code.djangoproject.com/ticket/5246#comment:11>

[Django Code]

#5246: MS SQL Server backend using pyodbc.

-------------------------------------------------------------+--------------
Reporter: Filip Wasilewski <filip.wa...@gmail.com> | Owner: nobody
Status: closed | Component: Database wrapper
Version: SVN | Resolution: invalid

Keywords: mssql pyodbc database backend feature | Stage: Design decision needed
Has_patch: 1 | Needs_docs: 0
Needs_tests: 0 | Needs_better_patch: 0
-------------------------------------------------------------+--------------

Comment (by bob84123 <ama...@gmail.com>):

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

--
Ticket URL: <http://code.djangoproject.com/ticket/5246#comment:14>

[Django Code]

#5246: MS SQL Server backend using pyodbc.

-------------------------------------------------------------+--------------
Reporter: Filip Wasilewski <filip.wa...@gmail.com> | Owner: nobody
Status: closed | Component: Database wrapper
Version: SVN | Resolution: invalid
Keywords: mssql pyodbc database backend feature | Stage: Design decision needed
Has_patch: 1 | Needs_docs: 0
Needs_tests: 0 | Needs_better_patch: 0
-------------------------------------------------------------+--------------
Comment (by bob84123 <ama...@gmail.com>):

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

--
Ticket URL: <http://code.djangoproject.com/ticket/5246#comment:15>

[Django]

#5246: MS SQL Server backend using pyodbc.

--------------------------------------------------------------------+-------
Reporter: Filip Wasilewski <filip.wa...@gmail.com> | Owner: nobody
Status: closed | Milestone:
Component: Database layer (models, ORM) | Version: SVN

Resolution: invalid | Keywords: mssql pyodbc database backend feature
Stage: Design decision needed | Has_patch: 1
Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |

--------------------------------------------------------------------+-------
Comment (by sunrise):

{{{
#!html
This works ok for me.. but It probably needs feedback. <a
href="http://www.tagnice.com"><font color="#000000">Nike Air Yeezy
Shoes</font></a>
}}}

--
Ticket URL: <http://code.djangoproject.com/ticket/5246#comment:16>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#5246: MS SQL Server backend using pyodbc.

--------------------------------------------------------------------+-------
Reporter: Filip Wasilewski <filip.wa...@gmail.com> | Owner: nobody
Status: closed | Milestone:
Component: Database layer (models, ORM) | Version: SVN
Resolution: invalid | Keywords: mssql pyodbc database backend feature
Stage: Design decision needed | Has_patch: 1
Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
--------------------------------------------------------------------+-------

Comment (by nikecitycn):

[http://www.sneakersupplier.com Nike air force ones]
[http://www.sneakersupplier.com Air Jordan shoes]
[http://www.sneakersupplier.com Nike Dunks]
[http://www.sneakersupplier.com Nike Shox]
[http://www.sneakersupplier.com Nike Shoes]