Fix the behaviour of sql classes regarding quoted identifiers

If no quotes around identifiers are provided by the programmer,
identifiers are treated identically to how the underlying engine
would behave.  i.e. some engines uppercase the identifiers
others lowercase them.  If the programmer wants case sensitivty
and/or use whitespaces they will need to quote their identifiers.
The previous (incorrect) behaviour always quoted the identifiers.

Reviewed-by: Bill King

2 files changed, 65 insertions, 28 deletions

diff --git a/src/sql/models/qsqlrelationaltablemodel.cpp b/src/sql/models/qsqlrelationaltablemodel.cpp
index 935466b..12eae84 100644
--- a/src/sql/models/qsqlrelationaltablemodel.cpp
+++ b/src/sql/models/qsqlrelationaltablemodel.cpp
@@ -182,10 +182,21 @@ void QRelation::populateDictionary()
         populateModel();
 
     QSqlRecord record;
+    QString indexColumn;
+    QString displayColumn;
     for (int i=0; i < model->rowCount(); ++i) {
         record = model->record(i);
-        dictionary[record.field(rel.indexColumn()).value().toString()] =
-            record.field(rel.displayColumn()).value();
+
+        indexColumn = rel.indexColumn();
+        if (m_parent->database().driver()->isIdentifierEscaped(indexColumn, QSqlDriver::FieldName))
+            indexColumn = m_parent->database().driver()->stripDelimiters(indexColumn, QSqlDriver::FieldName);
+
+        displayColumn = rel.displayColumn();
+        if (m_parent->database().driver()->isIdentifierEscaped(displayColumn, QSqlDriver::FieldName))
+            displayColumn = m_parent->database().driver()->stripDelimiters(displayColumn, QSqlDriver::FieldName);
+
+        dictionary[record.field(indexColumn).value().toString()] =
+            record.field(displayColumn).value();
     }
     m_dictInitialized = true;
 }
@@ -215,7 +226,7 @@ public:
     QSqlRelationalTableModelPrivate()
         : QSqlTableModelPrivate()
     {}
-    QString escapedRelationField(const QString &tableName, const QString &fieldName) const;
+    QString relationField(const QString &tableName, const QString &fieldName) const;
 
     int nameToIndex(const QString &name) const;
     mutable QVector<QRelation> relations;
@@ -255,7 +266,10 @@ void QSqlRelationalTableModelPrivate::revertCachedRow(int row)
 
 int QSqlRelationalTableModelPrivate::nameToIndex(const QString &name) const
 {
-    return baseRec.indexOf(name);
+    QString fieldname = name;
+    if (db.driver()->isIdentifierEscaped(fieldname, QSqlDriver::FieldName))
+        fieldname = db.driver()->stripDelimiters(fieldname, QSqlDriver::FieldName);
+    return baseRec.indexOf(fieldname);
 }
 
 void QSqlRelationalTableModelPrivate::clearEditBuffer()
@@ -481,14 +495,14 @@ QSqlRelation QSqlRelationalTableModel::relation(int column) const
     return d->relations.value(column).rel;
 }
 
-QString QSqlRelationalTableModelPrivate::escapedRelationField(const QString &tableName,
+QString QSqlRelationalTableModelPrivate::relationField(const QString &tableName,
                         const QString &fieldName) const
 {
-    QString esc;
-    esc.reserve(tableName.size() + fieldName.size() + 1);
-    esc.append(tableName).append(QLatin1Char('.')).append(fieldName);
+    QString ret;
+    ret.reserve(tableName.size() + fieldName.size() + 1);
+    ret.append(tableName).append(QLatin1Char('.')).append(fieldName);
 
-    return db.driver()->escapeIdentifier(esc, QSqlDriver::FieldName);
+    return ret;
 }
 
 /*!
@@ -514,15 +528,29 @@ QString QSqlRelationalTableModel::selectStatement() const
 
     // Count how many times each field name occurs in the record
     QHash<QString, int> fieldNames;
+    QStringList fieldList;
     for (int i = 0; i < rec.count(); ++i) {
         QSqlRelation relation = d->relations.value(i, nullRelation).rel;
         QString name;
         if (relation.isValid())
+        {
             // Count the display column name, not the original foreign key
             name = relation.displayColumn();
+            if (d->db.driver()->isIdentifierEscaped(name, QSqlDriver::FieldName))
+                name = d->db.driver()->stripDelimiters(name, QSqlDriver::FieldName);
+
+            QSqlRecord rec = database().record(relation.tableName());
+            for (int i = 0; i < rec.count(); ++i) {
+                if (name.compare(rec.fieldName(i), Qt::CaseInsensitive) == 0) {
+                    name = rec.fieldName(i);
+                    break;
+                }
+            }
+        }
         else
             name = rec.fieldName(i);
         fieldNames.insert(name, fieldNames.value(name, 0) + 1);
+        fieldList.append(name);
     }
 
     for (int i = 0; i < rec.count(); ++i) {
@@ -531,27 +559,30 @@ QString QSqlRelationalTableModel::selectStatement() const
             QString relTableAlias = QString::fromLatin1("relTblAl_%1").arg(i);
             if (!fList.isEmpty())
                 fList.append(QLatin1String(", "));
-            fList.append(d->escapedRelationField(relTableAlias, relation.displayColumn()));
+            fList.append(d->relationField(relTableAlias,relation.displayColumn()));
 
             // If there are duplicate field names they must be aliased
-            if (fieldNames.value(relation.displayColumn()) > 1) {
-                fList.append(QString::fromLatin1(" AS %1_%2").arg(relation.tableName()).arg(relation.displayColumn()));
+            if (fieldNames.value(fieldList[i]) > 1) {
+                QString relTableName = relation.tableName();
+                if (d->db.driver()->isIdentifierEscaped(relTableName, QSqlDriver::TableName))
+                    relTableName = d->db.driver()->stripDelimiters(relTableName, QSqlDriver::TableName);
+                QString displayColumn = relation.displayColumn();
+                if (d->db.driver()->isIdentifierEscaped(displayColumn, QSqlDriver::FieldName))
+                    displayColumn = d->db.driver()->stripDelimiters(displayColumn, QSqlDriver::FieldName);
+                fList.append(QString::fromLatin1(" AS %1_%2").arg(relTableName).arg(displayColumn));
             }
 
             // this needs fixing!! the below if is borken.
-            if (!tables.contains(relation.tableName()))
-                tables.append(d->db.driver()->escapeIdentifier(relation.tableName(),QSqlDriver::TableName)
-                        .append(QLatin1String(" "))
-                        .append(d->db.driver()->escapeIdentifier(relTableAlias, QSqlDriver::TableName)));
+            tables.append(relation.tableName().append(QLatin1String(" ")).append(relTableAlias));
             if(!where.isEmpty())
                 where.append(QLatin1String(" AND "));
-            where.append(d->escapedRelationField(tableName(), rec.fieldName(i)));
+            where.append(d->relationField(tableName(), d->db.driver()->escapeIdentifier(rec.fieldName(i), QSqlDriver::FieldName)));
             where.append(QLatin1String(" = "));
-            where.append(d->escapedRelationField(relTableAlias, relation.indexColumn()));
+            where.append(d->relationField(relTableAlias, relation.indexColumn()));
         } else {
             if (!fList.isEmpty())
                 fList.append(QLatin1String(", "));
-            fList.append(d->escapedRelationField(tableName(), rec.fieldName(i)));
+            fList.append(d->relationField(tableName(), d->db.driver()->escapeIdentifier(rec.fieldName(i), QSqlDriver::FieldName)));
         }
     }
     if (!tables.isEmpty())
@@ -560,7 +591,7 @@ QString QSqlRelationalTableModel::selectStatement() const
         return query;
     if(!tList.isEmpty())
         tList.prepend(QLatin1String(", "));
-    tList.prepend(d->db.driver()->escapeIdentifier(tableName(),QSqlDriver::TableName));
+    tList.prepend(tableName());
     query.append(QLatin1String("SELECT "));
     query.append(fList).append(QLatin1String(" FROM ")).append(tList);
     qAppendWhereClause(query, where, filter());
@@ -690,7 +721,7 @@ QString QSqlRelationalTableModel::orderByClause() const
         return QSqlTableModel::orderByClause();
 
     QString s = QLatin1String("ORDER BY ");
-    s.append(d->escapedRelationField(QLatin1String("relTblAl_") + QString::number(d->sortColumn),
+    s.append(d->relationField(QLatin1String("relTblAl_") + QString::number(d->sortColumn),
                     rel.displayColumn()));
     s += d->sortOrder == Qt::AscendingOrder ? QLatin1String(" ASC") : QLatin1String(" DESC");
     return s;
diff --git a/src/sql/models/qsqltablemodel.cpp b/src/sql/models/qsqltablemodel.cpp
index 2fb9b0f..c2442c5 100644
--- a/src/sql/models/qsqltablemodel.cpp
+++ b/src/sql/models/qsqltablemodel.cpp
@@ -98,7 +98,10 @@ bool QSqlTableModelPrivate::setRecord(int row, const QSqlRecord &record)
 
 int QSqlTableModelPrivate::nameToIndex(const QString &name) const
 {
-    return rec.indexOf(name);
+    QString fieldname = name;
+    if (db.driver()->isIdentifierEscaped(fieldname, QSqlDriver::FieldName))
+        fieldname = db.driver()->stripDelimiters(fieldname, QSqlDriver::FieldName);
+    return rec.indexOf(fieldname);
 }
 
 void QSqlTableModelPrivate::initRecordAndPrimaryIndex()
@@ -367,10 +370,7 @@ void QSqlTableModel::setTable(const QString &tableName)
 {
     Q_D(QSqlTableModel);
     clear();
-    if(d->db.tables().contains(tableName.toUpper()))
-        d->tableName = tableName.toUpper();
-    else
-        d->tableName = tableName;
+    d->tableName = tableName;
     d->initRecordAndPrimaryIndex();
     d->initColOffsets(d->rec.count());
 
@@ -976,7 +976,9 @@ QString QSqlTableModel::orderByClause() const
     if (!f.isValid())
         return s;
         
-    QString table = d->db.driver()->escapeIdentifier(d->tableName, QSqlDriver::TableName);
+    QString table = d->tableName;
+    //we can safely escape the field because it would have been obtained from the database
+    //and have the correct case
     QString field = d->db.driver()->escapeIdentifier(f.name(), QSqlDriver::FieldName);
     s.append(QLatin1String("ORDER BY ")).append(table).append(QLatin1Char('.')).append(field);
     s += d->sortOrder == Qt::AscendingOrder ? QLatin1String(" ASC") : QLatin1String(" DESC");
@@ -1317,8 +1319,12 @@ bool QSqlTableModel::setRecord(int row, const QSqlRecord &record)
             mrow.rec = d->rec;
             mrow.primaryValues = d->primaryValues(indexInQuery(createIndex(row, 0)).row());
         }
+        QString fieldName;
         for (int i = 0; i < record.count(); ++i) {
-            int idx = mrow.rec.indexOf(record.fieldName(i));
+            fieldName = record.fieldName(i);
+            if (d->db.driver()->isIdentifierEscaped(fieldName, QSqlDriver::FieldName))
+                fieldName = d->db.driver()->stripDelimiters(fieldName, QSqlDriver::FieldName);
+            int idx = mrow.rec.indexOf(fieldName);
             if (idx == -1)
                 isOk = false;
             else