MS SQL Server backend using pyodbc.
|Reported by:||Filip Wasilewski <filip.wasilewski@…>||Owned by:||nobody|
|Component:||Database layer (models, ORM)||Version:||master|
|Severity:||Keywords:||mssql pyodbc database backend feature|
|Cc:||filip.wasilewski@…||Triage Stage:||Design decision needed|
|Has patch:||yes||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
Note: The code is partially based on the #5062 ticket with several fixes, modifications
and improvements applied.
- 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.
- 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.
Change History (20)
comment:1 Changed 7 years ago by Filip Wasilewski <filip.wasilewski@…>
- Needs documentation unset
- Needs tests unset
- Patch needs improvement unset
comment:9 Changed 6 years ago by PhiR
- Triage Stage changed from Unreviewed to Design decision needed