From bb7bddc47dd0748b45d22180d9e3c8e5209010b3 Mon Sep 17 00:00:00 2001 From: abcd Date: Wed, 15 Apr 2009 10:16:48 +1000 Subject: 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 --- src/sql/models/qsqlrelationaltablemodel.cpp | 73 ++++++++++++++++++++--------- src/sql/models/qsqltablemodel.cpp | 20 +++++--- 2 files changed, 65 insertions(+), 28 deletions(-) (limited to 'src/sql/models') diff --git a/src/sql/models/qsqlrelationaltablemodel.cpp b/src/sql/models/qsqlrelationaltablemodel.cpp index 935466bd4e..12eae84f24 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 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 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 2fb9b0fc42..c2442c57cf 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 -- cgit v1.2.3