From 1b4ff1bab4ccc4bdc403b84920314f76fbca02c5 Mon Sep 17 00:00:00 2001 From: Bill King Date: Thu, 3 Dec 2009 09:49:49 +1000 Subject: Fixes: OCI QSqlDatabase.tables() does not work with system tables. Task-number: QTBUG-5298 Reviewed-by: Justin McPherson --- src/sql/drivers/oci/qsql_oci.cpp | 83 +++++++++++++++++----------- tests/auto/qsqldatabase/tst_qsqldatabase.cpp | 16 ++++++ 2 files changed, 68 insertions(+), 31 deletions(-) diff --git a/src/sql/drivers/oci/qsql_oci.cpp b/src/sql/drivers/oci/qsql_oci.cpp index 17f2c92..f130087 100644 --- a/src/sql/drivers/oci/qsql_oci.cpp +++ b/src/sql/drivers/oci/qsql_oci.cpp @@ -2200,26 +2200,34 @@ bool QOCIDriver::rollbackTransaction() QStringList QOCIDriver::tables(QSql::TableType type) const { QStringList tl; + QStringList sysUsers = QStringList() << QLatin1String("MDSYS") + << QLatin1String("LBACSYS") + << QLatin1String("SYS") + << QLatin1String("SYSTEM") + << QLatin1String("WKSYS") + << QLatin1String("CTXSYS") + << QLatin1String("WMSYS"); + + QString user = d->user; + if ( isIdentifierEscaped(user, QSqlDriver::TableName)) + user = stripDelimiters(user, QSqlDriver::TableName); + else + user = user.toUpper(); + + if(sysUsers.contains(user)) + sysUsers.removeAll(user);; + if (!isOpen()) return tl; QSqlQuery t(createResult()); t.setForwardOnly(true); if (type & QSql::Tables) { - t.exec(QLatin1String("select owner, table_name from all_tables " - "where owner != 'MDSYS' " - "and owner != 'LBACSYS' " - "and owner != 'SYS' " - "and owner != 'SYSTEM' " - "and owner != 'WKSYS'" - "and owner != 'CTXSYS'" - "and owner != 'WMSYS'")); - - QString user = d->user; - if ( isIdentifierEscaped(user, QSqlDriver::TableName)) - user = stripDelimiters(user, QSqlDriver::TableName); - else - user = user.toUpper(); + QString query = QLatin1String("select owner, table_name from all_tables where "); + QStringList whereList; + foreach(const QString &sysUserName, sysUsers) + whereList << QLatin1String("owner != '") + sysUserName + QLatin1String("' "); + t.exec(query + whereList.join(QLatin1String(" and "))); while (t.next()) { if (t.value(0).toString().toUpper() != user.toUpper()) @@ -2229,30 +2237,21 @@ QStringList QOCIDriver::tables(QSql::TableType type) const } // list all table synonyms as well - t.exec(QLatin1String("select owner, synonym_name from all_synonyms " - "where owner != 'MDSYS' " - "and owner != 'LBACSYS' " - "and owner != 'SYS' " - "and owner != 'SYSTEM' " - "and owner != 'WKSYS'" - "and owner != 'CTXSYS'" - "and owner != 'WMSYS'")); + query = QLatin1String("select owner, synonym_name from all_synonyms where "); + t.exec(query + whereList.join(QLatin1String(" and "))); while (t.next()) { if (t.value(0).toString() != d->user) - tl.append(t.value(0).toString() + QLatin1String(".") + t.value(1).toString()); + tl.append(t.value(0).toString() + QLatin1Char('.') + t.value(1).toString()); else tl.append(t.value(1).toString()); } } if (type & QSql::Views) { - t.exec(QLatin1String("select owner, view_name from all_views " - "where owner != 'MDSYS' " - "and owner != 'LBACSYS' " - "and owner != 'SYS' " - "and owner != 'SYSTEM' " - "and owner != 'WKSYS'" - "and owner != 'CTXSYS'" - "and owner != 'WMSYS'")); + QString query = QLatin1String("select owner, view_name from all_views where "); + QStringList whereList; + foreach(const QString &sysUserName, sysUsers) + whereList << QLatin1String("owner != '") + sysUserName + QLatin1String("' "); + t.exec(query + whereList.join(QLatin1String(" and "))); while (t.next()) { if (t.value(0).toString().toUpper() != d->user.toUpper()) tl.append(t.value(0).toString() + QLatin1Char('.') + t.value(1).toString()); @@ -2265,6 +2264,28 @@ QStringList QOCIDriver::tables(QSql::TableType type) const while (t.next()) { tl.append(t.value(0).toString()); } + QString query = QLatin1String("select owner, table_name from all_tables where "); + QStringList whereList; + foreach(const QString &sysUserName, sysUsers) + whereList << QLatin1String("owner = '") + sysUserName + QLatin1String("' "); + t.exec(query + whereList.join(QLatin1String(" or "))); + + while (t.next()) { + if (t.value(0).toString().toUpper() != user.toUpper()) + tl.append(t.value(0).toString() + QLatin1Char('.') + t.value(1).toString()); + else + tl.append(t.value(1).toString()); + } + + // list all table synonyms as well + query = QLatin1String("select owner, synonym_name from all_synonyms where "); + t.exec(query + whereList.join(QLatin1String(" or "))); + while (t.next()) { + if (t.value(0).toString() != d->user) + tl.append(t.value(0).toString() + QLatin1String(".") + t.value(1).toString()); + else + tl.append(t.value(1).toString()); + } } return tl; } diff --git a/tests/auto/qsqldatabase/tst_qsqldatabase.cpp b/tests/auto/qsqldatabase/tst_qsqldatabase.cpp index fe7c3ea..f840ca6 100644 --- a/tests/auto/qsqldatabase/tst_qsqldatabase.cpp +++ b/tests/auto/qsqldatabase/tst_qsqldatabase.cpp @@ -86,6 +86,8 @@ private slots: void open(); void tables_data() { generic_data(); } void tables(); + void oci_tables_data() { generic_data("QOCI"); } + void oci_tables(); void transaction_data() { generic_data(); } void transaction(); void eventNotification_data() { generic_data(); } @@ -380,6 +382,7 @@ void tst_QSqlDatabase::dropTestTables(QSqlDatabase db) if (db.driverName().startsWith("QOCI")) { q.exec("drop user "+qTableName("CREATOR")+" cascade"); q.exec("drop user "+qTableName("APPUSER")+" cascade"); + q.exec("DROP TABLE system."+qTableName("mypassword")); } } @@ -2481,5 +2484,18 @@ void tst_QSqlDatabase::mysql_savepointtest() QVERIFY_SQL(q, exec("savepoint foo")); } +void tst_QSqlDatabase::oci_tables() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + QSqlQuery q(db); + QString systemTableName("system."+qTableName("mypassword")); + QVERIFY_SQL(q, exec("CREATE TABLE "+systemTableName+"(name VARCHAR(20))")); + QVERIFY(!db.tables().contains(systemTableName.toUpper())); + qDebug() << db.tables(QSql::SystemTables); + QVERIFY(db.tables(QSql::SystemTables).contains(systemTableName.toUpper())); +} + QTEST_MAIN(tst_QSqlDatabase) #include "tst_qsqldatabase.moc" -- cgit v0.12