Correct handling of date and time values in mssql provider

qgis · Jan 29, 2016 · 49e4567 · 49e4567
1 parent 78d9617
commit 49e4567
Show file tree

Hide file tree

Showing 7 changed files with 81 additions and 10 deletions.
diff --git a/src/providers/mssql/qgsmssqlfeatureiterator.cpp b/src/providers/mssql/qgsmssqlfeatureiterator.cpp
@@ -284,7 +284,10 @@ bool QgsMssqlFeatureIterator::fetchFeature( QgsFeature& feature )
     for ( int i = 0; i < mAttributesToFetch.count(); i++ )
     {
       QVariant v = mQuery->value( i );
-      feature.setAttribute( mAttributesToFetch[i], mQuery->value( i ) );
+      const QgsField &fld = mSource->mFields.at( i );
+      if ( v.type() != fld.type() )
+        v = QgsVectorDataProvider::convertValue( fld.type(), v.toString() );
+      feature.setAttribute( mAttributesToFetch.at( i ), v );
     }
 
     feature.setFeatureId( mQuery->record().value( mSource->mFidColName ).toLongLong() );

diff --git a/src/providers/mssql/qgsmssqlprovider.cpp b/src/providers/mssql/qgsmssqlprovider.cpp
@@ -160,6 +160,11 @@ QgsMssqlProvider::QgsMssqlProvider( const QString& uri )
   << QgsVectorDataProvider::NativeType( tr( "Decimal number (real)" ), "real", QVariant::Double )
   << QgsVectorDataProvider::NativeType( tr( "Decimal number (double)" ), "float", QVariant::Double )
 
+  // date/time types
+  << QgsVectorDataProvider::NativeType( tr( "Date" ), "date", QVariant::Date, -1, -1, -1, -1 )
+  << QgsVectorDataProvider::NativeType( tr( "Time" ), "time", QVariant::Time, -1, -1, -1, -1 )
+  << QgsVectorDataProvider::NativeType( tr( "Date & Time" ), "datetime", QVariant::DateTime, -1, -1, -1, -1 )
+
   // string types
   << QgsVectorDataProvider::NativeType( tr( "Text, fixed length (char)" ), "char", QVariant::String, 1, 255 )
   << QgsVectorDataProvider::NativeType( tr( "Text, limited variable length (varchar)" ), "varchar", QVariant::String, 1, 255 )
@@ -321,11 +326,14 @@ QVariant::Type QgsMssqlProvider::DecodeSqlType( const QString& sqlTypeName )
   {
     type = QVariant::Date;
   }
-  else if ( sqlTypeName.startsWith( "time", Qt::CaseInsensitive ) ||
-            sqlTypeName.startsWith( "timestamp", Qt::CaseInsensitive ) )
+  else if ( sqlTypeName.startsWith( "timestamp", Qt::CaseInsensitive ) )
   {
     type = QVariant::String;
   }
+  else if ( sqlTypeName.startsWith( "time", Qt::CaseInsensitive ) )
+  {
+    type = QVariant::Time;
+  }
   else
   {
     QgsDebugMsg( QString( "Unknown field type: %1" ).arg( sqlTypeName ) );
@@ -408,6 +416,15 @@ void QgsMssqlProvider::loadFields()
               query.value( 7 ).toInt(),
               query.value( 8 ).toInt() ) );
         }
+        else if ( sqlType == QVariant::Date || sqlType == QVariant::DateTime || sqlType == QVariant::Time )
+        {
+          mAttributeFields.append(
+            QgsField(
+              query.value( 3 ).toString(), sqlType,
+              sqlTypeName,
+              -1,
+              -1 ) );
+        }
         else
         {
           mAttributeFields.append(
@@ -1455,8 +1472,20 @@ bool QgsMssqlProvider::convertField( QgsField &field )
       break;
 
     case QVariant::DateTime:
+      fieldType = "datetime";
+      fieldPrec = -1;
+      break;
+
     case QVariant::Date:
+      fieldType = "date";
+      fieldPrec = -1;
+      break;
+
     case QVariant::Time:
+      fieldType = "time";
+      fieldPrec = -1;
+      break;
+
     case QVariant::String:
       fieldType = "nvarchar(max)";
       fieldPrec = -1;

diff --git a/tests/src/python/test_provider_mssql.py b/tests/src/python/test_provider_mssql.py
@@ -18,7 +18,7 @@
 from qgis.core import NULL
 
 from qgis.core import QgsVectorLayer, QgsFeatureRequest, QgsFeature, QgsProviderRegistry
-from PyQt4.QtCore import QSettings
+from PyQt4.QtCore import QSettings, QDate, QTime, QDateTime, QVariant
 from utilities import (unitTestDataPath,
                        getQgisTestApp,
                        unittest,
@@ -53,5 +53,27 @@ def enableCompiler(self):
     def disableCompiler(self):
         QSettings().setValue(u'/qgis/compileExpressions', False)
 
+    # HERE GO THE PROVIDER SPECIFIC TESTS
+    def testDateTimeTypes(self):
+        vl = QgsVectorLayer('%s table="qgis_test"."date_times" sql=' % (self.dbconn), "testdatetimes", "mssql")
+        assert(vl.isValid())
+
+        fields = vl.dataProvider().fields()
+        self.assertEqual(fields.at(fields.indexFromName('date_field')).type(), QVariant.Date)
+        self.assertEqual(fields.at(fields.indexFromName('time_field')).type(), QVariant.Time)
+        self.assertEqual(fields.at(fields.indexFromName('datetime_field')).type(), QVariant.DateTime)
+
+        f = vl.getFeatures(QgsFeatureRequest()).next()
+
+        date_idx = vl.fieldNameIndex('date_field')
+        assert isinstance(f.attributes()[date_idx], QDate)
+        self.assertEqual(f.attributes()[date_idx], QDate(2004, 3, 4))
+        time_idx = vl.fieldNameIndex('time_field')
+        assert isinstance(f.attributes()[time_idx], QTime)
+        self.assertEqual(f.attributes()[time_idx], QTime(13, 41, 52))
+        datetime_idx = vl.fieldNameIndex('datetime_field')
+        assert isinstance(f.attributes()[datetime_idx], QDateTime)
+        self.assertEqual(f.attributes()[datetime_idx], QDateTime(QDate(2004, 3, 4), QTime(13, 41, 52)))
+
 if __name__ == '__main__':
     unittest.main()
diff --git a/tests/src/python/test_provider_postgres.py b/tests/src/python/test_provider_postgres.py
@@ -72,10 +72,13 @@ def testDateTimeTypes(self):
 
         date_idx = vl.fieldNameIndex('date_field')
         assert isinstance(f.attributes()[date_idx], QDate)
+        self.assertEqual(f.attributes()[date_idx], QDate(2004, 3, 4))
         time_idx = vl.fieldNameIndex('time_field')
         assert isinstance(f.attributes()[time_idx], QTime)
+        self.assertEqual(f.attributes()[time_idx], QTime(13, 41, 52))
         datetime_idx = vl.fieldNameIndex('datetime_field')
         assert isinstance(f.attributes()[datetime_idx], QDateTime)
+        self.assertEqual(f.attributes()[datetime_idx], QDateTime(QDate(2004, 3, 4), QTime(13, 41, 52)))
 
     def testQueryLayers(self):
         def test_query(dbconn, query, key):

diff --git a/tests/src/python/test_provider_tabfile.py b/tests/src/python/test_provider_tabfile.py
@@ -47,10 +47,13 @@ def testDateTimeFormats(self):
 
         date_idx = vl.fieldNameIndex('date')
         assert isinstance(f.attributes()[date_idx], QDate)
+        self.assertEqual(f.attributes()[date_idx], QDate(2004, 5, 3))
         time_idx = vl.fieldNameIndex('time')
         assert isinstance(f.attributes()[time_idx], QTime)
+        self.assertEqual(f.attributes()[time_idx], QTime(13, 41, 00))
         datetime_idx = vl.fieldNameIndex('date_time')
         assert isinstance(f.attributes()[datetime_idx], QDateTime)
+        self.assertEqual(f.attributes()[datetime_idx], QDateTime(QDate(2004, 5, 3), QTime(13, 41, 00)))
 
 if __name__ == '__main__':
     unittest.main()
diff --git a/tests/src/python/test_qgsvectorfilewriter.py b/tests/src/python/test_qgsvectorfilewriter.py
@@ -72,7 +72,7 @@ def testDateTimeWriteShapefile(self):
 
         ft = QgsFeature()
         ft.setGeometry(QgsGeometry.fromPoint(QgsPoint(10, 10)))
-        ft.setAttributes([1, QDate(2014, 0o3, 0o5), QTime(13, 45, 22), QDateTime(QDate(2014, 0o3, 0o5), QTime(13, 45, 22))])
+        ft.setAttributes([1, QDate(2014, 3, 5), QTime(13, 45, 22), QDateTime(QDate(2014, 3, 5), QTime(13, 45, 22))])
         res, features = provider.addFeatures([ft])
         assert res
         assert len(features) > 0
@@ -103,15 +103,15 @@ def testDateTimeWriteShapefile(self):
 
         date_idx = created_layer.fieldNameIndex('date_f')
         assert isinstance(f.attributes()[date_idx], QDate)
-        self.assertEqual(f.attributes()[date_idx], QDate(2014, 0o3, 0o5))
+        self.assertEqual(f.attributes()[date_idx], QDate(2014, 3, 5))
         time_idx = created_layer.fieldNameIndex('time_f')
         #shapefiles do not support time types
         assert isinstance(f.attributes()[time_idx], basestring)
         self.assertEqual(f.attributes()[time_idx], '13:45:22')
         #shapefiles do not support datetime types
         datetime_idx = created_layer.fieldNameIndex('dt_f')
         assert isinstance(f.attributes()[datetime_idx], QDate)
-        self.assertEqual(f.attributes()[datetime_idx], QDate(2014, 0o3, 0o5))
+        self.assertEqual(f.attributes()[datetime_idx], QDate(2014, 3, 5))
 
     def testDateTimeWriteTabfile(self):
         """Check writing date and time fields to an MapInfo tabfile."""
@@ -128,7 +128,7 @@ def testDateTimeWriteTabfile(self):
 
         ft = QgsFeature()
         ft.setGeometry(QgsGeometry.fromPoint(QgsPoint(10, 10)))
-        ft.setAttributes([1, QDate(2014, 0o3, 0o5), QTime(13, 45, 22), QDateTime(QDate(2014, 0o3, 0o5), QTime(13, 45, 22))])
+        ft.setAttributes([1, QDate(2014, 3, 5), QTime(13, 45, 22), QDateTime(QDate(2014, 3, 5), QTime(13, 45, 22))])
         res, features = provider.addFeatures([ft])
         assert res
         assert len(features) > 0
@@ -157,13 +157,13 @@ def testDateTimeWriteTabfile(self):
 
         date_idx = created_layer.fieldNameIndex('date_f')
         assert isinstance(f.attributes()[date_idx], QDate)
-        self.assertEqual(f.attributes()[date_idx], QDate(2014, 0o3, 0o5))
+        self.assertEqual(f.attributes()[date_idx], QDate(2014, 3, 5))
         time_idx = created_layer.fieldNameIndex('time_f')
         assert isinstance(f.attributes()[time_idx], QTime)
         self.assertEqual(f.attributes()[time_idx], QTime(13, 45, 22))
         datetime_idx = created_layer.fieldNameIndex('dt_f')
         assert isinstance(f.attributes()[datetime_idx], QDateTime)
-        self.assertEqual(f.attributes()[datetime_idx], QDateTime(QDate(2014, 0o3, 0o5), QTime(13, 45, 22)))
+        self.assertEqual(f.attributes()[datetime_idx], QDateTime(QDate(2014, 3, 5), QTime(13, 45, 22)))
 
 if __name__ == '__main__':
     unittest.main()
diff --git a/tests/testdata/provider/testdata_mssql.sql b/tests/testdata/provider/testdata_mssql.sql
@@ -8,10 +8,21 @@ CREATE TABLE qgis_test.[someData] (
     geom geometry
 )
 
+CREATE TABLE qgis_test.[date_times] (
+       id integer PRIMARY KEY,
+       date_field date,
+       time_field time,
+       datetime_field datetime
+);
+
+
 INSERT INTO qgis_test.[someData] (pk, cnt, name, name2, geom) VALUES
 (5, -200, NULL, 'NuLl', geometry::STGeomFromText( 'Point(-71.123 78.23)', 4326 )),
 (3,  300, 'Pear', 'PEaR', NULL),
 (1,  100, 'Orange', 'oranGe', geometry::STGeomFromText( 'Point(-70.332 66.33)', 4326 )),
 (2,  200, 'Apple', 'Apple', geometry::STGeomFromText( 'Point(-68.2 70.8)', 4326 )),
 (4,  400, 'Honey', 'Honey', geometry::STGeomFromText( 'Point(-65.32 78.3)', 4326 ))
 ;
+
+INSERT INTO qgis_test.[date_times] (id, date_field, time_field, datetime_field ) VALUES
+ (1, '2004-03-04', '13:41:52', '2004-03-04 13:41:52' );