Commits

Michael Manfre committed 5f2a820

Fixed #31, #25 - Added date related custom fields with improved introspection

Added additional custom fields to allow explicitly controlling the date & time
database data type. New option 'use_legacy_date_fields' allows changing the
default data types to the newer (SQL Server 2008+) date and time data types.

Comments (0)

Files changed (12)

docs/changelog.txt

   and mapping column aliases properly.
 - Improved the "return ID from insert" logic so it can properly extract the
   column data type from user defined fields with custom data type strings.
+- Fixed case for identifiers in introspection. Thanks Mikhail Denisenko.
+- Added option :setting:`use_legacy_date_fields` (defaults to True) to allow changing the DatabaseCreation.data_types to
+  not use the Microsoft preferred date data types that were added with SQL Server 2008. :issue:`31`
+- Improved accuracy of field type guessing with inspectdb. See :ref:`introspecting-custom-fields`
 
 v1.3.1
 ------

docs/datatypes.txt

 Dates and Times
 ---------------
 
-Earliest Year
-~~~~~~~~~~~~~
+When using `Django-mssql` with SQL Server 2005, all of the date related fields
+only support the `datetime` data type. Support for these legacy data types can
+be enabled using the :setting:`use_legacy_date_fields` option, or using the 
+fields ``LegacyDateField``,  ``LegacyDateTimeField``, and ``LegacyTimeField`` in
+``sqlserver_ado.fields``.
 
-SQL Server provides two datetime types: `datetime and smalldatetime`_.
+To allow migrating specific apps or only some of your models to the new date 
+times, the model fields ``DateField``, ``DateTimeField``, and ``TimeField`` in
+``sqlserver_ado.fields`` use the new data types regardless of the 
+:setting:`use_legacy_date_fields` option.
 
-.. _`datetime and smalldatetime`: http://msdn.microsoft.com/en-us/library/ms187819.aspx
+.. code:: python
 
-This backend always uses datetime, which supports dates back to 
-January 1, 1753.
+	from django.db import models
+	from sqlserver_ado.fields import DateField, DateTimeField, TimeField
 
-Django previously only supported dates back to 1900, but recent revisions 
-7946 + 7950 changed this behavior. If you need to store historical data in 
-datetime fields, then SQL Server 2005 will have a problem. 
-(I'd welcome bugs/repro cases/patches on this issue.)
+	class MyModel(models.Model):
+		# when use_legecy_date_fields is False, models.*Field will behave like these
+		a_real_date = DateField() # date data type
+		a_datetime2 = DateTimeField() # datetime2 data type
+		a_real_time = TimeField() # time data type
+
+		# when use_legecy_date_fields is True, models.*Field will behave like these
+		a_date = LegacyDateField() # datetime data type
+		a_datetime = LegacyDateTime() # datetime data type
+		a_time = LegacyTimeField() # datetime data type
+
+datetime limitations
+~~~~~~~~~~~~~~~~~~~~
+
+With SQL Server 2005, only the `datetime`_ data type is usable with Django. This
+data type does not store enough precision to provide the full range of Python
+datetime dates and will round to increments of .000, .003, or .007 seconds. The
+earliest supported datetime date value is January 1, 1753.
+
+.. _`datetime`: http://msdn.microsoft.com/en-us/library/ms187819.aspx
 
 SQL Server 2008 introduces a datetime2_ type, with support for fractional 
-seconds and the full range of Python datetime dates. To use this time, edit 
-your local creation.py/introspection.py files to use this datatype instead.
+seconds and the full range of Python datetime dates. To use this time, either
+set the :settings:`use_legacy_date_fields` option to ``False`` or use the 
+``sqlserver_ado.fields.DateTimeField`` with your models.
 
 .. _datetime2: http://msdn.microsoft.com/en-us/library/ms180878(SQL.100).aspx
 
-Bare Times
-~~~~~~~~~~
-
-SQL Server 2005 doesn't have a bare "time" datatype, only a datetime. SQL 
-Server 2008 introduces a time type, but this isn't used by this backend.
 
 bigint
 ------
     ``BigForeignKey`` for things to work as expected.
 
 
+money
+-----
+
+The ``money`` and ``smallmoney`` data types will be introspected as 
+``DecimalField`` with the appropriate values for ``max_digits`` and 
+``decimal_places``. This does not mean that they are expected to work without
+issue.
+
 Unsupported Types
 -----------------
 
 smaller than what Django expects from similar types:
 
 - smalldatetime
-- smallmoney
 - tinyint
 - real

docs/known-issues.txt

 the database column so it does not include a '%' (percent) character, or change ``DEBUG = False`` 
 in your settings when you run ``manage.py inspectdb``.
 
+.. _introspecting-custom-fields:
+
+Introspecting custom fields
+---------------------------
+
+Some datatypes will be mapped to a custom model field provided by 
+`Django-mssql`. If any of these fields are used, it will be necessary to add 
+``import sqlserver_ado.fields`` to the top of the models.py file. If using a
+version of Django prior to 1.7, it will be necessary to also remove the 
+"models." prefix from any of these custom fields. :djangoticket:`21090`
+
 .. _azure-clustered-indices:
 
 Azure requires clustered indices

docs/settings.txt

     SQL server maintains the datatype of the values used in ``AVG``. The
     average of an ``int`` column will be an ``int``. With this option set
     to ``True``, ``AVG([1,2])`` == 1, not 1.5.
+
+.. setting:: use_legacy_date_fields
+
+use_legacy_date_fields
+~~~~~~~~~~~~~~~~~~~~~~
+
+Default: ``True``
+
+This setting alters which data types are used for the ``DateField``, 
+``DateTimeField``, and ``TimeField`` fields. When ``True``, the fields will all 
+use the ``datetime`` data type. When ``False``, they will use ``date``, 
+``datetime``, and ``time`` data types.
+
+
+
+.. versionadded:: 1.4

sqlserver_ado/ado_consts.py

 adVarWChar                    = 0xCA
 adVariant                     = 0xC
 adWChar                       = 0x82
-# Additional constants used by introspection but not ADO itself
-AUTO_FIELD_MARKER = -1000
 
 adTypeNames = {
     adBSTR: 'adBSTR',

sqlserver_ado/base.py

         except ValueError:   
             self.command_timeout = 30
         
+        options = self.settings_dict.get('OPTIONS', {})
         try:
-            options = self.settings_dict.get('OPTIONS', {})
             self.cast_avg_to_float = not bool(options.get('disable_avg_cast', False))
         except ValueError:
             self.cast_avg_to_float = False
+
+        USE_LEGACY_DATE_FIELDS_DEFAULT = True
+        try:
+            use_legacy_date_fields = bool(options.get('use_legacy_date_fields', USE_LEGACY_DATE_FIELDS_DEFAULT))
+        except ValueError:
+            use_legacy_date_fields = USE_LEGACY_DATE_FIELDS_DEFAULT
+
+        if use_legacy_date_fields:
+            self.creation._enable_legacy_date_fields()
         
         self.ops.is_sql2000 = self.is_sql2000
         self.ops.is_sql2005 = self.is_sql2005

sqlserver_ado/creation.py

         'BooleanField':                 'bit',
         'CharField':                    'nvarchar(%(max_length)s)',
         'CommaSeparatedIntegerField':   'nvarchar(%(max_length)s)',
-        'DateField':                    'datetime',
-        'DateTimeField':                'datetime',
+        'DateField':                    'date',
+        'DateTimeField':                'datetime2',
         'DecimalField':                 'decimal(%(max_digits)s, %(decimal_places)s)',
         'FileField':                    'nvarchar(%(max_length)s)',
         'FilePathField':                'nvarchar(%(max_length)s)',
         'FloatField':                   'double precision',
+        'GenericIPAddressField':        'nvarchar(39)',
         'IntegerField':                 'int',
         'IPAddressField':               'nvarchar(15)',
-        'GenericIPAddressField':        'nvarchar(39)',
+        'LegacyDateTimeField':          'datetime',
+        'NewDateField':                 'date',
+        'NewDateTimeField':             'datetime2',
+        'NewTimeField':                 'time',
         'NullBooleanField':             'bit',
         'OneToOneField':                'int',
         'PositiveIntegerField':         'int CHECK ([%(column)s] >= 0)',
         'SlugField':                    'nvarchar(%(max_length)s)',
         'SmallIntegerField':            'smallint',
         'TextField':                    'nvarchar(max)',
-        'TimeField':                    'datetime',
+        'TimeField':                    'time',
     }
 
+    def _enable_legacy_date_fields(self):
+        """
+        Revert date related data_types to SQL 2005 compatible values.
+        """
+        self.data_types.update({
+            'DateField': 'datetime',
+            'DateTimeField': 'datetime',
+            'TimeField': 'datetime',
+        })
+
     def _create_master_connection(self):
         """
         Create a transactionless connection to 'master' database.

sqlserver_ado/fields.py

 """This module provides SQL Server specific fields for Django models."""
-from django.db.models import AutoField, ForeignKey, BigIntegerField
+from django.db import models
 from django.forms import ValidationError
 from django.utils.translation import ugettext_lazy as _
 
     'BigAutoField',
     'BigForeignKey',
     'BigIntegerField',
+    'DateField',
+    'DateTimeField',
+    'LegacyTimeField',
+    'LegacyDateField',
+    'LegacyDateTimeField',
+    'TimeField',
 )
 
-class BigAutoField(AutoField):
+class BigAutoField(models.AutoField):
     """A bigint IDENTITY field"""
     def get_internal_type(self):
         return "BigAutoField"
             return None
         return long(value)
 
-class BigForeignKey(ForeignKey):
+class BigForeignKey(models.ForeignKey):
     """A ForeignKey field that points to a BigAutoField or BigIntegerField"""
     def db_type(self, connection=None):
         try:
-            return BigIntegerField().db_type(connection=connection)
+            return models.BigIntegerField().db_type(connection=connection)
         except AttributeError:
-            return BigIntegerField().db_type()
+            return models.BigIntegerField().db_type()
+
+BigIntegerField = models.BigIntegerField
+
+class DateField(models.DateField):
+    """
+    A DateField backed by a 'date' database field.
+    """
+    def get_internal_type(self):
+        return 'NewDateField'
+
+class DateTimeField(models.DateTimeField):
+    """
+    A DateTimeField backed by a 'datetime2' database field.
+    """
+    def get_internal_type(self):
+        return 'NewDateTimeField'
+
+class TimeField(models.TimeField):
+    """
+    A TimeField backed by a 'time' database field.
+    """
+    def get_internal_type(self):
+        return 'NewTimeField'
+
+class LegacyDateField(models.DateField):
+    """
+    A DateField that is backed by a 'datetime' database field.
+    """
+    def get_internal_type(self):
+        return 'LegacyDateTimeField'
+
+class LegacyDateTimeField(models.DateTimeField):
+    """
+    A DateTimeField that is backed by a 'datetime' database field.
+    """
+    def get_internal_type(self):
+        return 'LegacyDateTimeField'
+
+class LegacyTimeField(models.TimeField):
+    """
+    A TimeField that is backed by a 'datetime' database field.
+    """
+    def get_internal_type(self):
+        return 'LegacyDateTimeField'

sqlserver_ado/introspection.py

 from django.db.backends import BaseDatabaseIntrospection
 import ado_consts
 
+AUTO_FIELD_MARKER = -1000
+BIG_AUTO_FIELD_MARKER = -1001
+MONEY_FIELD_MARKER = -1002
+
 class DatabaseIntrospection(BaseDatabaseIntrospection):
+
     def get_table_list(self, cursor):
         "Return a list of table and view names in the current database."
         cursor.execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' UNION SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS")
         cursor.execute(sql)
         return cursor.fetchone()[0]
 
+    def _get_table_field_type_map(self, cursor, table_name):
+        """
+        Return a dict mapping field name to data type. DB-API cursor description 
+        interprets the date columns as chars.
+        """
+        cursor.execute('SELECT [COLUMN_NAME], [DATA_TYPE] FROM INFORMATION_SCHEMA.COLUMNS WHERE [TABLE_NAME] LIKE \'%s\'' % table_name)
+        results = dict(cursor.fetchall())
+        return results
+
+    def _datatype_to_ado_type(self, datatype):
+        """
+        Map datatype name to ado type.
+        """
+        return {
+            'bigint': ado_consts.adBigInt,
+            'binary': ado_consts.adBinary,
+            'bit': ado_consts.adBoolean,
+            'char': ado_consts.adChar,
+            'date': ado_consts.adDBDate,
+            'datetime': ado_consts.adDBTimeStamp,
+            'datetime2': ado_consts.adDBTimeStamp,
+            'datetimeoffset': ado_consts.adDBTimeStamp,
+            'decimal': ado_consts.adDecimal,
+            'float': ado_consts.adDouble,
+            'image': ado_consts.adVarBinary,
+            'int': ado_consts.adInteger,
+            'money': MONEY_FIELD_MARKER,
+            'numeric': ado_consts.adNumeric,
+            'nchar': ado_consts.adWChar,
+            'ntext': ado_consts.adLongVarWChar,
+            'nvarchar': ado_consts.adVarWChar,
+            'smalldatetime': ado_consts.adDBTimeStamp,
+            'smallint': ado_consts.adSmallInt,
+            'smallmoney': MONEY_FIELD_MARKER,
+            'text': ado_consts.adLongVarChar,
+            'time': ado_consts.adDBTime,
+            'tinyint': ado_consts.adTinyInt,
+            'varbinary': ado_consts.adVarBinary,
+            'varchar': ado_consts.adVarChar,
+        }.get(datatype.lower(), None)
+
     def get_table_description(self, cursor, table_name, identity_check=True):
         """Return a description of the table, with DB-API cursor.description interface.
 
         When a field is found with an IDENTITY property, it is given a custom field number
         of SQL_AUTOFIELD, which maps to the 'AutoField' value in the DATA_TYPES_REVERSE dict.
         """
+        table_field_type_map = self._get_table_field_type_map(cursor, table_name)
+
         cursor.execute("SELECT * FROM [%s] where 1=0" % (table_name))
         columns = cursor.description
 
         items = list()
         for column in columns:
             column = list(column) # Convert tuple to list
+            # fix data type
+            column[1] = self._datatype_to_ado_type(table_field_type_map.get(column[0]))
+
             if identity_check and self._is_auto_field(cursor, table_name, column[0]):
-                column[1] = ado_consts.AUTO_FIELD_MARKER
+                if column[1] == ado_consts.adBigInt:
+                    column[1] = BIG_AUTO_FIELD_MARKER
+                else:
+                    column[1] = AUTO_FIELD_MARKER
+
+            if column[1] == MONEY_FIELD_MARKER:
+                # force decimal_places=4 to match data type. Cursor description thinks this column is a string
+                column[5] = 4
             items.append(column)
         return items
 
 
 
     data_types_reverse = {
-        ado_consts.AUTO_FIELD_MARKER: 'AutoField',
+        AUTO_FIELD_MARKER: 'AutoField',
+        BIG_AUTO_FIELD_MARKER: 'sqlserver_ado.fields.BigAutoField',
+        MONEY_FIELD_MARKER: 'DecimalField',
         ado_consts.adBoolean: 'BooleanField',
         ado_consts.adChar: 'CharField',
         ado_consts.adWChar: 'CharField',
         ado_consts.adDecimal: 'DecimalField',
         ado_consts.adNumeric: 'DecimalField',
+        ado_consts.adDate: 'DateField',
+        ado_consts.adDBDate: 'DateField',
+        ado_consts.adDBTime: 'TimeField',
         ado_consts.adDBTimeStamp: 'DateTimeField',
         ado_consts.adDouble: 'FloatField',
         ado_consts.adSingle: 'FloatField',
         ado_consts.adInteger: 'IntegerField',
-        ado_consts.adBigInt: 'IntegerField',
-        #ado_consts.adBigInt: 'BigIntegerField',
-        ado_consts.adSmallInt: 'IntegerField',
-        ado_consts.adTinyInt: 'IntegerField',
+        ado_consts.adBigInt: 'BigIntegerField',
+        ado_consts.adSmallInt: 'SmallIntegerField',
+        ado_consts.adTinyInt: 'SmallIntegerField',
         ado_consts.adVarChar: 'CharField',
         ado_consts.adVarWChar: 'CharField',
         ado_consts.adLongVarWChar: 'TextField',
         ado_consts.adLongVarChar: 'TextField',
+        ado_consts.adBinary: 'BinaryField',
+        ado_consts.adVarBinary: 'BinaryField',
     }

tests/dbsettings.py

         'OPTIONS' : {
             'provider': 'SQLNCLI10',
             'extra_params': 'DataTypeCompatibility=80;MARS Connection=True;',
+            'use_legacy_date_fields': False,
         },
     }
 }

tests/test_main/regressiontests/models.py

 from django.db import models, IntegrityError
 from django.test import TestCase
 from django.core.paginator import Paginator
-from sqlserver_ado.fields import BigAutoField, BigIntegerField, BigForeignKey
+from sqlserver_ado.fields import *
 
 class Bug19Table(models.Model):
     """ A simple model for testing string comparisons.
 
 class StringTable(models.Model):
     name = models.CharField(max_length=50)
+
+
+class DateTable(models.Model):
+    legacy_datetime = LegacyDateTimeField()
+    legacy_date = LegacyDateField()
+    legacy_time = LegacyTimeField()
+    new_date = DateField()
+    new_time = TimeField()
+    new_datetime = DateTimeField()

tests/test_main/regressiontests/tests.py

 import datetime
 import decimal
+import time
 from django.core.exceptions import ImproperlyConfigured
 from django.db import models, connection
 from django.test import TestCase
 from django.utils.safestring import mark_safe
 
-from regressiontests.models import Bug69Table1, Bug69Table2, Bug70Table, Bug93Table, IntegerIdTable, StringTable
+from regressiontests.models import Bug69Table1, Bug69Table2, Bug70Table, Bug93Table, IntegerIdTable, StringTable, DateTable
 
 class Bug38Table(models.Model):
     d = models.DecimalField(max_digits=5, decimal_places=2)
         obj = StringTable(name=mark_safe(u'string'))
         obj.save()
         self.assertEqual(unicode(obj.name), unicode(StringTable.objects.get(pk=obj.id).name))
+
+class DateTestCase(TestCase):
+    def test_fields(self):
+        now = datetime.datetime.now()
+
+        d = DateTable.objects.create(
+            legacy_datetime=now,
+            legacy_time=now.time(),
+            legacy_date=now.date(),
+            new_datetime=now,
+            new_time=now.time(),
+            new_date=now.date(),
+        )
+
+        self.assertEqual(d.legacy_date, d.new_date)
+        self.assertEqual(d.legacy_time, d.new_time)
+        self.assertEqual(d.legacy_datetime, d.new_datetime)
+
+        self.assertTrue(isinstance(d.legacy_datetime, datetime.datetime))
+        self.assertTrue(isinstance(d.legacy_date, datetime.date))
+        self.assertTrue(isinstance(d.legacy_time, datetime.time))
+        self.assertTrue(isinstance(d.new_datetime, datetime.datetime))
+        self.assertTrue(isinstance(d.new_date, datetime.date))
+        self.assertTrue(isinstance(d.new_time, datetime.time))
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.