From 0843c6ca7fe604d8c44b9a81b8fcb072ab424172 Mon Sep 17 00:00:00 2001 From: Andy Shaw Date: Fri, 19 May 2017 15:31:46 +0200 Subject: QSqlField: Add a means to see what the tablename is for a given field When you are using a query that pulls from a number of different tables then it can be ambiguous as to which table a particular field belongs to. So this will make it possible to determine the table that a given field belongs to if it is set. Task-number: QTBUG-7170 Change-Id: I49b7890c0523d81272a153df3860df800ff853d5 Reviewed-by: Jesus Fernandez Reviewed-by: Edward Welbourne --- src/3rdparty/sqlite.pri | 2 +- src/plugins/sqldrivers/db2/qsql_db2.cpp | 14 ++++++++- src/plugins/sqldrivers/ibase/qsql_ibase.cpp | 9 ++++-- src/plugins/sqldrivers/mysql/qsql_mysql.cpp | 3 +- src/plugins/sqldrivers/odbc/qsql_odbc.cpp | 7 +++++ src/plugins/sqldrivers/psql/qsql_psql.cpp | 11 +++++-- src/plugins/sqldrivers/sqlite/qsql_sqlite.cpp | 8 +++-- src/plugins/sqldrivers/sqlite2/qsql_sqlite2.cpp | 2 +- src/plugins/sqldrivers/tds/qsql_tds.cpp | 4 +-- src/sql/kernel/qsqlfield.cpp | 42 ++++++++++++++++++++----- src/sql/kernel/qsqlfield.h | 5 ++- src/sql/kernel/qsqlrecord.cpp | 17 ++++++++-- 12 files changed, 99 insertions(+), 25 deletions(-) (limited to 'src') diff --git a/src/3rdparty/sqlite.pri b/src/3rdparty/sqlite.pri index 79179daaf4..91083f84c4 100644 --- a/src/3rdparty/sqlite.pri +++ b/src/3rdparty/sqlite.pri @@ -1,5 +1,5 @@ CONFIG(release, debug|release):DEFINES *= NDEBUG -DEFINES += SQLITE_OMIT_LOAD_EXTENSION SQLITE_OMIT_COMPLETE SQLITE_ENABLE_FTS3 SQLITE_ENABLE_FTS3_PARENTHESIS SQLITE_ENABLE_FTS5 SQLITE_ENABLE_RTREE +DEFINES += SQLITE_ENABLE_COLUMN_METADATA SQLITE_OMIT_LOAD_EXTENSION SQLITE_OMIT_COMPLETE SQLITE_ENABLE_FTS3 SQLITE_ENABLE_FTS3_PARENTHESIS SQLITE_ENABLE_FTS5 SQLITE_ENABLE_RTREE !contains(CONFIG, largefile):DEFINES += SQLITE_DISABLE_LFS qtConfig(posix_fallocate): DEFINES += HAVE_POSIX_FALLOCATE=1 winrt: DEFINES += SQLITE_OS_WINRT diff --git a/src/plugins/sqldrivers/db2/qsql_db2.cpp b/src/plugins/sqldrivers/db2/qsql_db2.cpp index 27d0e7001a..1a9631f1eb 100644 --- a/src/plugins/sqldrivers/db2/qsql_db2.cpp +++ b/src/plugins/sqldrivers/db2/qsql_db2.cpp @@ -66,6 +66,10 @@ QT_BEGIN_NAMESPACE static const int COLNAMESIZE = 255; +// Based on what is mentioned in the documentation here: +// https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_limits.html +// The limit is 128 bytes for table names +static const SQLSMALLINT TABLENAMESIZE = 128; static const SQLSMALLINT qParamType[4] = { SQL_PARAM_INPUT, SQL_PARAM_INPUT, SQL_PARAM_OUTPUT, SQL_PARAM_INPUT_OUTPUT }; class QDB2DriverPrivate : public QSqlDriverPrivate @@ -297,6 +301,12 @@ static QSqlField qMakeFieldInfo(const QDB2ResultPrivate* d, int i) f.setLength(colSize == 0 ? -1 : int(colSize)); f.setPrecision(colScale == 0 ? -1 : int(colScale)); f.setSqlType(int(colType)); + SQLTCHAR tableName[TABLENAMESIZE]; + SQLSMALLINT tableNameLen; + r = SQLColAttribute(d->hStmt, i + 1, SQL_DESC_BASE_TABLE_NAME, tableName, + TABLENAMESIZE, &tableNameLen, 0); + if (r == SQL_SUCCESS) + f.setTableName(qFromTChar(tableName)); return f; } @@ -1394,7 +1404,9 @@ QSqlRecord QDB2Driver::record(const QString& tableName) const SQL_FETCH_NEXT, 0); while (r == SQL_SUCCESS) { - fil.append(qMakeFieldInfo(hStmt)); + QSqlField fld = qMakeFieldInfo(hStmt); + fld.setTableName(tableName); + fil.append(fld); r = SQLFetchScroll(hStmt, SQL_FETCH_NEXT, 0); diff --git a/src/plugins/sqldrivers/ibase/qsql_ibase.cpp b/src/plugins/sqldrivers/ibase/qsql_ibase.cpp index 6fd91b6b76..23a86e2266 100644 --- a/src/plugins/sqldrivers/ibase/qsql_ibase.cpp +++ b/src/plugins/sqldrivers/ibase/qsql_ibase.cpp @@ -1382,7 +1382,8 @@ QSqlRecord QIBaseResult::record() const for (int i = 0; i < d->sqlda->sqld; ++i) { v = d->sqlda->sqlvar[i]; QSqlField f(QString::fromLatin1(v.aliasname, v.aliasname_length).simplified(), - qIBaseTypeName2(v.sqltype, v.sqlscale < 0)); + qIBaseTypeName2(v.sqltype, v.sqlscale < 0), + QString::fromLatin1(v.relname, v.relname_length)); f.setLength(v.sqllen); f.setPrecision(qAbs(v.sqlscale)); f.setRequiredStatus((v.sqltype & 1) == 0 ? QSqlField::Required : QSqlField::Optional); @@ -1685,7 +1686,7 @@ QSqlRecord QIBaseDriver::record(const QString& tablename) const while (q.next()) { int type = q.value(1).toInt(); bool hasScale = q.value(3).toInt() < 0; - QSqlField f(q.value(0).toString().simplified(), qIBaseTypeName(type, hasScale)); + QSqlField f(q.value(0).toString().simplified(), qIBaseTypeName(type, hasScale), tablename); if(hasScale) { f.setLength(q.value(4).toInt()); f.setPrecision(qAbs(q.value(3).toInt())); @@ -1726,7 +1727,9 @@ QSqlIndex QIBaseDriver::primaryIndex(const QString &table) const "ORDER BY b.RDB$FIELD_POSITION")); while (q.next()) { - QSqlField field(q.value(1).toString().simplified(), qIBaseTypeName(q.value(2).toInt(), q.value(3).toInt() < 0)); + QSqlField field(q.value(1).toString().simplified(), + qIBaseTypeName(q.value(2).toInt(), q.value(3).toInt() < 0), + tablename); index.append(field); //TODO: asc? desc? index.setName(q.value(0).toString()); } diff --git a/src/plugins/sqldrivers/mysql/qsql_mysql.cpp b/src/plugins/sqldrivers/mysql/qsql_mysql.cpp index ee439fa33e..3dc0e73af5 100644 --- a/src/plugins/sqldrivers/mysql/qsql_mysql.cpp +++ b/src/plugins/sqldrivers/mysql/qsql_mysql.cpp @@ -331,7 +331,8 @@ static QVariant::Type qDecodeMYSQLType(int mysqltype, uint flags) static QSqlField qToField(MYSQL_FIELD *field, QTextCodec *tc) { QSqlField f(toUnicode(tc, field->name), - qDecodeMYSQLType(int(field->type), field->flags)); + qDecodeMYSQLType(int(field->type), field->flags), + toUnicode(tc, field->table)); f.setRequired(IS_NOT_NULL(field->flags)); f.setLength(field->length); f.setPrecision(field->decimals); diff --git a/src/plugins/sqldrivers/odbc/qsql_odbc.cpp b/src/plugins/sqldrivers/odbc/qsql_odbc.cpp index 59ef42d609..c32a29c5e7 100644 --- a/src/plugins/sqldrivers/odbc/qsql_odbc.cpp +++ b/src/plugins/sqldrivers/odbc/qsql_odbc.cpp @@ -64,6 +64,7 @@ QT_BEGIN_NAMESPACE #define ODBC_CHECK_DRIVER static const int COLNAMESIZE = 256; +static const SQLSMALLINT TABLENAMESIZE = 128; //Map Qt parameter types to ODBC types static const SQLSMALLINT qParamType[4] = { SQL_PARAM_INPUT, SQL_PARAM_INPUT, SQL_PARAM_OUTPUT, SQL_PARAM_INPUT_OUTPUT }; @@ -730,6 +731,12 @@ static QSqlField qMakeFieldInfo(const SQLHANDLE hStmt, int i, QString *errorMess f.setRequired(false); // else we don't know f.setAutoValue(isAutoValue(hStmt, i)); + QVarLengthArray tableName(TABLENAMESIZE); + SQLSMALLINT tableNameLen; + r = SQLColAttribute(hStmt, i + 1, SQL_DESC_BASE_TABLE_NAME, tableName.data(), + TABLENAMESIZE, &tableNameLen, 0); + if (r == SQL_SUCCESS) + f.setTableName(fromSQLTCHAR(tableName, tableNameLen)); return f; } diff --git a/src/plugins/sqldrivers/psql/qsql_psql.cpp b/src/plugins/sqldrivers/psql/qsql_psql.cpp index b60fa1058f..a3aa0f6593 100644 --- a/src/plugins/sqldrivers/psql/qsql_psql.cpp +++ b/src/plugins/sqldrivers/psql/qsql_psql.cpp @@ -552,6 +552,11 @@ QSqlRecord QPSQLResult::record() const f.setName(QString::fromUtf8(PQfname(d->result, i))); else f.setName(QString::fromLocal8Bit(PQfname(d->result, i))); + QSqlQuery qry(driver()->createResult()); + if (qry.exec(QStringLiteral("SELECT relname FROM pg_class WHERE pg_class.oid = %1") + .arg(PQftable(d->result, i))) && qry.next()) { + f.setTableName(qry.value(0).toString()); + } int ptype = PQftype(d->result, i); f.setType(qDecodePSQLType(ptype)); int len = PQfsize(d->result, i); @@ -1132,7 +1137,7 @@ QSqlIndex QPSQLDriver::primaryIndex(const QString& tablename) const i.exec(stmt.arg(tbl)); while (i.isActive() && i.next()) { - QSqlField f(i.value(0).toString(), qDecodePSQLType(i.value(1).toInt())); + QSqlField f(i.value(0).toString(), qDecodePSQLType(i.value(1).toInt()), tablename); idx.append(f); idx.setName(i.value(2).toString()); } @@ -1237,7 +1242,7 @@ QSqlRecord QPSQLDriver::record(const QString& tablename) const QString defVal = query.value(5).toString(); if (!defVal.isEmpty() && defVal.at(0) == QLatin1Char('\'')) defVal = defVal.mid(1, defVal.length() - 2); - QSqlField f(query.value(0).toString(), qDecodePSQLType(query.value(1).toInt())); + QSqlField f(query.value(0).toString(), qDecodePSQLType(query.value(1).toInt()), tablename); f.setRequired(query.value(2).toBool()); f.setLength(len); f.setPrecision(precision); @@ -1264,7 +1269,7 @@ QSqlRecord QPSQLDriver::record(const QString& tablename) const len = precision - 4; precision = -1; } - QSqlField f(query.value(0).toString(), qDecodePSQLType(query.value(1).toInt())); + QSqlField f(query.value(0).toString(), qDecodePSQLType(query.value(1).toInt()), tablename); f.setRequired(query.value(2).toBool()); f.setLength(len); f.setPrecision(precision); diff --git a/src/plugins/sqldrivers/sqlite/qsql_sqlite.cpp b/src/plugins/sqldrivers/sqlite/qsql_sqlite.cpp index e7f9be9e39..b0db1c276e 100644 --- a/src/plugins/sqldrivers/sqlite/qsql_sqlite.cpp +++ b/src/plugins/sqldrivers/sqlite/qsql_sqlite.cpp @@ -212,7 +212,9 @@ void QSQLiteResultPrivate::initColumns(bool emptyResultset) QString colName = QString(reinterpret_cast( sqlite3_column_name16(stmt, i)) ).remove(QLatin1Char('"')); - + const QString tableName = QString(reinterpret_cast( + sqlite3_column_table_name16(stmt, i)) + ).remove(QLatin1Char('"')); // must use typeName for resolving the type to match QSqliteDriver::record QString typeName = QString(reinterpret_cast( sqlite3_column_decltype16(stmt, i))); @@ -245,7 +247,7 @@ void QSQLiteResultPrivate::initColumns(bool emptyResultset) } } - QSqlField fld(colName, fieldType); + QSqlField fld(colName, fieldType, tableName); fld.setSqlType(stp); rInf.append(fld); } @@ -872,7 +874,7 @@ static QSqlIndex qGetTableInfo(QSqlQuery &q, const QString &tableName, bool only if (onlyPIndex && !isPk) continue; QString typeName = q.value(2).toString().toLower(); - QSqlField fld(q.value(1).toString(), qGetColumnType(typeName)); + QSqlField fld(q.value(1).toString(), qGetColumnType(typeName), tableName); if (isPk && (typeName == QLatin1String("integer"))) // INTEGER PRIMARY KEY fields are auto-generated in sqlite // INT PRIMARY KEY is not the same as INTEGER PRIMARY KEY! diff --git a/src/plugins/sqldrivers/sqlite2/qsql_sqlite2.cpp b/src/plugins/sqldrivers/sqlite2/qsql_sqlite2.cpp index 67c24e4168..93f47e3f13 100644 --- a/src/plugins/sqldrivers/sqlite2/qsql_sqlite2.cpp +++ b/src/plugins/sqldrivers/sqlite2/qsql_sqlite2.cpp @@ -576,7 +576,7 @@ QSqlIndex QSQLite2Driver::primaryIndex(const QString &tblname) const QVariant::Type type = QVariant::Invalid; if (rec.contains(name)) type = rec.field(name).type(); - index.append(QSqlField(name, type)); + index.append(QSqlField(name, type, tblname)); } return index; } diff --git a/src/plugins/sqldrivers/tds/qsql_tds.cpp b/src/plugins/sqldrivers/tds/qsql_tds.cpp index 6ebd09a572..670198af81 100644 --- a/src/plugins/sqldrivers/tds/qsql_tds.cpp +++ b/src/plugins/sqldrivers/tds/qsql_tds.cpp @@ -767,7 +767,7 @@ QSqlRecord QTDSDriver::record(const QString& tablename) const "where id = (select id from sysobjects where name = '%1')")); t.exec(stmt.arg(table)); while (t.next()) { - QSqlField f(t.value(0).toString().simplified(), qDecodeTDSType(t.value(1).toInt())); + QSqlField f(t.value(0).toString().simplified(), qDecodeTDSType(t.value(1).toInt()), tablename); f.setLength(t.value(2).toInt()); f.setPrecision(t.value(3).toInt()); f.setSqlType(t.value(1).toInt()); @@ -853,7 +853,7 @@ QSqlIndex QTDSDriver::primaryIndex(const QString& tablename) const QRegExp regx(QLatin1String("\\s*(\\S+)(?:\\s+(DESC|desc))?\\s*")); for(QStringList::Iterator it = fNames.begin(); it != fNames.end(); ++it) { regx.indexIn(*it); - QSqlField f(regx.cap(1), rec.field(regx.cap(1)).type()); + QSqlField f(regx.cap(1), rec.field(regx.cap(1)).type(), tablename); if (regx.cap(2).toLower() == QLatin1String("desc")) { idx.append(f, true); } else { diff --git a/src/sql/kernel/qsqlfield.cpp b/src/sql/kernel/qsqlfield.cpp index bb810b11df..59b992e803 100644 --- a/src/sql/kernel/qsqlfield.cpp +++ b/src/sql/kernel/qsqlfield.cpp @@ -47,9 +47,9 @@ class QSqlFieldPrivate { public: QSqlFieldPrivate(const QString &name, - QVariant::Type type) : + QVariant::Type type, const QString &tableName) : ref(1), nm(name), ro(false), type(type), req(QSqlField::Unknown), - len(-1), prec(-1), tp(-1), gen(true), autoval(false) + len(-1), prec(-1), tp(-1), gen(true), autoval(false), table(tableName) { } @@ -64,7 +64,8 @@ public: def(other.def), tp(other.tp), gen(other.gen), - autoval(other.autoval) + autoval(other.autoval), + table(other.table) {} bool operator==(const QSqlFieldPrivate& other) const @@ -77,7 +78,8 @@ public: && prec == other.prec && def == other.def && gen == other.gen - && autoval == other.autoval); + && autoval == other.autoval + && table == other.table); } QAtomicInt ref; @@ -91,6 +93,7 @@ public: int tp; uint gen: 1; uint autoval: 1; + QString table; }; @@ -153,14 +156,15 @@ public: /*! Constructs an empty field called \a fieldName of variant type \a - type. + type in \a table. \sa setRequiredStatus(), setLength(), setPrecision(), setDefaultValue(), setGenerated(), setReadOnly() */ -QSqlField::QSqlField(const QString& fieldName, QVariant::Type type) +QSqlField::QSqlField(const QString &fieldName, QVariant::Type type, + const QString &table) { - d = new QSqlFieldPrivate(fieldName, type); + d = new QSqlFieldPrivate(fieldName, type, table); val = QVariant(type); } @@ -518,6 +522,7 @@ QDebug operator<<(QDebug dbg, const QSqlField &f) QDebugStateSaver saver(dbg); dbg.nospace(); dbg << "QSqlField(" << f.name() << ", " << QMetaType::typeName(f.type()); + dbg << ", tableName: " << (f.tableName().isEmpty() ? QStringLiteral("(not specified)") : f.tableName()); if (f.length() >= 0) dbg << ", length: " << f.length(); if (f.precision() >= 0) @@ -565,4 +570,27 @@ void QSqlField::setAutoValue(bool autoVal) d->autoval = autoVal; } +/*! + Sets the tableName of the field to \a table. + + \sa tableName() +*/ + +void QSqlField::setTableName(const QString &table) +{ + detach(); + d->table = table; +} + +/*! + Returns the tableName of the field. + + \sa setTableName() +*/ + +QString QSqlField::tableName() const +{ + return d->table; +} + QT_END_NAMESPACE diff --git a/src/sql/kernel/qsqlfield.h b/src/sql/kernel/qsqlfield.h index 0d8c51f801..30474735f4 100644 --- a/src/sql/kernel/qsqlfield.h +++ b/src/sql/kernel/qsqlfield.h @@ -55,7 +55,8 @@ public: enum RequiredStatus { Unknown = -1, Optional = 0, Required = 1 }; explicit QSqlField(const QString& fieldName = QString(), - QVariant::Type type = QVariant::Invalid); + QVariant::Type type = QVariant::Invalid, + const QString &tableName = QString()); QSqlField(const QSqlField& other); QSqlField& operator=(const QSqlField& other); @@ -68,6 +69,8 @@ public: { return val; } void setName(const QString& name); QString name() const; + void setTableName(const QString &tableName); + QString tableName() const; bool isNull() const; void setReadOnly(bool readOnly); bool isReadOnly() const; diff --git a/src/sql/kernel/qsqlrecord.cpp b/src/sql/kernel/qsqlrecord.cpp index d5adff67a4..1c9ad5ec63 100644 --- a/src/sql/kernel/qsqlrecord.cpp +++ b/src/sql/kernel/qsqlrecord.cpp @@ -232,10 +232,23 @@ QString QSqlRecord::fieldName(int index) const int QSqlRecord::indexOf(const QString& name) const { - QString nm = name.toUpper(); + QString tableName; + QString fieldName = name; + const int idx = name.indexOf(QLatin1Char('.')); + if (idx != -1) { + tableName = name.left(idx); + fieldName = name.mid(idx + 1); + } for (int i = 0; i < count(); ++i) { - if (d->fields.at(i).name().toUpper() == nm) // TODO: case-insensitive comparison + // Check the passed in name first in case it is an alias using a dot. + // Then check if both the table and field match when there is a table name specified. + const auto currentField = d->fields.at(i); + const auto currentFieldName = currentField.name(); + if (currentFieldName.compare(name, Qt::CaseInsensitive) == 0 + || (idx != -1 && currentFieldName.compare(fieldName, Qt::CaseInsensitive) == 0 + && currentField.tableName().compare(tableName, Qt::CaseInsensitive) == 0)) { return i; + } } return -1; } -- cgit v1.2.3