oracle provider: improve date/time support

qgis · Feb 11, 2014 · 3b438e2 · 3b438e2
1 parent fa1dd25
commit 3b438e2
Show file tree

Hide file tree

Showing 5 changed files with 57 additions and 25 deletions.
diff --git a/src/providers/oracle/qgsoracleconn.cpp b/src/providers/oracle/qgsoracleconn.cpp
@@ -25,6 +25,7 @@
 
 #include <QSettings>
 #include <QSqlError>
+#include <QDateTime>
 
 QMap<QString, QgsOracleConn *> QgsOracleConn::sConnections;
 int QgsOracleConn::snConnections = 0;
@@ -257,25 +258,53 @@ QString QgsOracleConn::quotedIdentifier( QString ident )
   return ident;
 }
 
-QString QgsOracleConn::quotedValue( QVariant value )
+QString QgsOracleConn::quotedValue( const QVariant &value, QVariant::Type type )
 {
   if ( value.isNull() )
     return "NULL";
 
-  switch ( value.type() )
+  if ( type == QVariant::Invalid )
+    type = value.type();
+
+  if ( value.canConvert( type ) )
   {
-    case QVariant::Int:
-    case QVariant::LongLong:
-    case QVariant::Double:
-      return value.toString();
+    switch ( type )
+    {
+      case QVariant::Int:
+      case QVariant::LongLong:
+      case QVariant::Double:
+        return value.toString();
+
+      case QVariant::DateTime:
+      {
+        QDateTime datetime( value.toDateTime() );
+        if ( datetime.isValid() )
+          return QString( "TO_DATE('%1','YYYY-MM-DD HH24:MI:SS')" ).arg( datetime.toString( "yyyy-MM-dd hh:mm:ss" ) );
+	break;
+      }
+
+      case QVariant::Date:
+      {
+        QDate date( value.toDate() );
+        if ( date.isValid() )
+          return QString( "TO_DATE('%1','YYYY-MM-DD')" ).arg( date.toString( "yyyy-MM-dd" ) );
+        break;
+      }
 
-    default:
-    case QVariant::String:
-      QString v = value.toString();
-      v.replace( "'", "''" );
-      v.replace( "\\\"", "\\\\\"" );
-      return v.prepend( "'" ).append( "'" );
+      case QVariant::Time:
+      {
+        QDateTime datetime( value.toDateTime() );
+        if ( datetime.isValid() )
+          return QString( "TO_DATE('%1','HH24:MI:SS')" ).arg( datetime.toString( "hh:mm:ss" ) );
+        break;
+      }
+    }
   }
+
+  QString v = value.toString();
+  v.replace( "'", "''" );
+  v.replace( "\\\"", "\\\\\"" );
+  return v.prepend( "'" ).append( "'" );
 }
 
 QString QgsOracleConn::fieldExpression( const QgsField &fld )

diff --git a/src/providers/oracle/qgsoracleconn.h b/src/providers/oracle/qgsoracleconn.h
@@ -109,7 +109,7 @@ class QgsOracleConn : public QThread
 
     /** Quote a value for placement in a SQL string.
      */
-    static QString quotedValue( QVariant value );
+    static QString quotedValue( const QVariant &value, QVariant::Type type = QVariant::Invalid );
 
     //! Get the list of supported layers
     bool supportedLayers( QVector<QgsOracleLayerProperty> &layers,

diff --git a/src/providers/oracle/qgsoraclefeatureiterator.cpp b/src/providers/oracle/qgsoraclefeatureiterator.cpp
@@ -185,7 +185,9 @@ bool QgsOracleFeatureIterator::fetchFeature( QgsFeature& feature )
           {
             const QgsField &fld = P->field( idx );
 
-            QVariant v = P->convertValue( fld.type(), mQry.value( col ).toString() );
+            QVariant v = mQry.value( col );
+            if ( v.type() != fld.type() )
+              v = P->convertValue( fld.type(), v.toString() );
             primaryKeyVals << v;
 
             if ( mAttributeList.contains( idx ) )
@@ -219,7 +221,9 @@ bool QgsOracleFeatureIterator::fetchFeature( QgsFeature& feature )
 
       const QgsField &fld = P->field( idx );
 
-      QVariant v = P->convertValue( fld.type(), mQry.value( col ).toString() );
+      QVariant v = mQry.value( col );
+      if ( v.type() != fld.type() )
+        v = P->convertValue( fld.type(), v.toString() );
       feature.setAttribute( idx, v );
 
       col++;

diff --git a/src/providers/oracle/qgsoracleprovider.cpp b/src/providers/oracle/qgsoracleprovider.cpp
@@ -441,7 +441,7 @@ QString QgsOracleProvider::whereClause( QgsFeatureId featureId ) const
             int idx = mPrimaryKeyAttrs[i];
             const QgsField &fld = field( idx );
 
-            whereClause += delim + QString( "%1=%2" ).arg( mConnection->fieldExpression( fld ) ).arg( quotedValue( pkVals[i].toString() ) );
+            whereClause += delim + QString( "%1=%2" ).arg( mConnection->fieldExpression( fld ) ).arg( quotedValue( pkVals[i], fld.type() ) );
             delim = " AND ";
           }
         }
@@ -1222,12 +1222,11 @@ bool QgsOracleProvider::addFeatures( QgsFeatureList &flist )
       if ( fieldId.contains( idx ) )
         continue;
 
-      QString fieldname = mAttributeFields[idx].name();
-      QString fieldTypeName = mAttributeFields[idx].typeName();
+      const QgsField &fld = mAttributeFields[idx];
 
-      QgsDebugMsg( "Checking field against: " + fieldname );
+      QgsDebugMsg( "Checking field against: " + fld.name() );
 
-      if ( fieldname.isEmpty() || fieldname == mGeometryColumn )
+      if ( fld.name().isEmpty() || fld.name() == mGeometryColumn )
         continue;
 
       int i;
@@ -1243,7 +1242,7 @@ bool QgsOracleProvider::addFeatures( QgsFeatureList &flist )
           break;
       }
 
-      insert += delim + quotedIdentifier( fieldname );
+      insert += delim + quotedIdentifier( fld.name() );
 
       QString defVal = defaultValue( idx ).toString();
 
@@ -1260,13 +1259,13 @@ bool QgsOracleProvider::addFeatures( QgsFeatureList &flist )
             values += delim + defVal;
           }
         }
-        else if ( fieldTypeName == "MDSYS.SDO_GEOMETRY" )
+        else if ( fld.typeName() == "MDSYS.SDO_GEOMETRY" )
         {
           values += delim + "?";
         }
         else
         {
-          values += delim + quotedValue( v.toString() );
+          values += delim + quotedValue( v, mAttributeFields[idx].type() );
         }
       }
       else
@@ -1620,7 +1619,7 @@ bool QgsOracleProvider::changeAttributeValues( const QgsChangedAttributesMap & a
           }
           else
           {
-            sql += quotedValue( siter->toString() );
+            sql += quotedValue( *siter, fld.type() );
           }
         }
         catch ( OracleFieldNotFound )

diff --git a/src/providers/oracle/qgsoracleprovider.h b/src/providers/oracle/qgsoracleprovider.h
@@ -380,7 +380,7 @@ class QgsOracleProvider : public QgsVectorDataProvider
     void disconnectDb();
 
     static QString quotedIdentifier( QString ident ) { return QgsOracleConn::quotedIdentifier( ident ); }
-    static QString quotedValue( QVariant value ) { return QgsOracleConn::quotedValue( value ); }
+    static QString quotedValue( const QVariant &value, QVariant::Type type = QVariant::Invalid ) { return QgsOracleConn::quotedValue( value, type ); }
 
     QgsFeatureId lookupFid( const QVariant &v ); //! lookup existing mapping or add a new one