Adds OCI support for synonyms to tables created by another user.

Adds support for ::tables and ::record to understand synonyms to tables
created by another user eg: as appuser, see appuser.synonym created
against creator.table1

Task-number: 17327

2 files changed, 62 insertions, 5 deletions

diff --git a/src/sql/drivers/oci/qsql_oci.cpp b/src/sql/drivers/oci/qsql_oci.cpp
index 7dd2ea9..6437841 100644
--- a/src/sql/drivers/oci/qsql_oci.cpp
+++ b/src/sql/drivers/oci/qsql_oci.cpp
@@ -2220,6 +2220,22 @@ QStringList QOCIDriver::tables(QSql::TableType type) const
             else
                 tl.append(t.value(1).toString());
         }
+
+        // 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'"));
+        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());
+        }
     }
     if (type & QSql::Views) {
         t.exec(QLatin1String("select owner, view_name from all_views "
@@ -2269,8 +2285,8 @@ QSqlRecord QOCIDriver::record(const QString& tablename) const
     // eg. a sub-query on the sys.synonyms table
     QString stmt(QLatin1String("select column_name, data_type, data_length, "
                   "data_precision, data_scale, nullable, data_default%1"
-                  "from all_tab_columns "
-                  "where table_name=%2"));
+                  "from all_tab_columns a "
+                  "where a.table_name=%2"));
     if (d->serverVersion >= 9)
         stmt = stmt.arg(QLatin1String(", char_length "));
     else
@@ -2294,12 +2310,15 @@ QSqlRecord QOCIDriver::record(const QString& tablename) const
     else
         owner = owner.toUpper();
 
-    tmpStmt += QLatin1String(" and owner='") + owner + QLatin1Char('\'');
+    tmpStmt += QLatin1String(" and a.owner='") + owner + QLatin1Char('\'');
     t.setForwardOnly(true);
     t.exec(tmpStmt);
     if (!t.next()) { // try and see if the tablename is a synonym
-        stmt= stmt.arg(QLatin1String("(select tname from sys.synonyms where sname='")
-                        + table + QLatin1String("' and creator=owner)"));
+        stmt = stmt + QLatin1String(" join all_synonyms b "
+                              "on a.owner=b.table_owner and a.table_name=b.table_name "
+                              "where b.owner='") + owner +
+                      QLatin1String("' and b.synonym_name='") + table +
+                      QLatin1Char('\'');
         t.setForwardOnly(true);
         t.exec(stmt);
         if (t.next())
diff --git a/tests/auto/qsqldatabase/tst_qsqldatabase.cpp b/tests/auto/qsqldatabase/tst_qsqldatabase.cpp
index a6d2c26..f85ebc5 100644
--- a/tests/auto/qsqldatabase/tst_qsqldatabase.cpp
+++ b/tests/auto/qsqldatabase/tst_qsqldatabase.cpp
@@ -189,6 +189,8 @@ private slots:
     void oci_xmltypeSupport();
     void oci_fieldLength_data() { generic_data("QOCI"); }
     void oci_fieldLength();
+    void oci_synonymstest_data() { generic_data("QOCI"); }
+    void oci_synonymstest();
 
     void sqlite_bindAndFetchUInt_data() { generic_data("QSQLITE"); }
     void sqlite_bindAndFetchUInt();
@@ -365,6 +367,12 @@ void tst_QSqlDatabase::dropTestTables(QSqlDatabase db)
         tableNames <<  db.driver()->escapeIdentifier(qTableName("qtest") + " test", QSqlDriver::TableName);
 
     tst_Databases::safeDropTables(db, tableNames);
+
+    if (db.driverName().startsWith("QOCI")) {
+        q.exec("drop user "+qTableName("CREATOR")+" cascade");
+        q.exec("drop user "+qTableName("APPUSER")+" cascade");
+
+    }
 }
 
 void tst_QSqlDatabase::populateTestTables(QSqlDatabase db)
@@ -2162,6 +2170,36 @@ void tst_QSqlDatabase::oci_fieldLength()
     QCOMPARE(q.record().field(1).length(), 40);
 }
 
+void tst_QSqlDatabase::oci_synonymstest()
+{
+    QFETCH(QString, dbName);
+    QSqlDatabase db = QSqlDatabase::database(dbName);
+    CHECK_DATABASE(db);
+
+    QSqlQuery q(db);
+    QString creator(qTableName("CREATOR")), appuser(qTableName("APPUSER")), table1(qTableName("TABLE1"));
+//     QVERIFY_SQL(q, exec("drop public synonym "+table1));
+    QVERIFY_SQL(q, exec(QLatin1String("create user "+creator+" identified by "+creator+" default tablespace users temporary tablespace temp")));
+    QVERIFY_SQL(q, exec(QLatin1String("grant CONNECT to "+creator)));
+    QVERIFY_SQL(q, exec(QLatin1String("grant RESOURCE to "+creator)));
+    QSqlDatabase db2=db.cloneDatabase(db, QLatin1String("oci_synonymstest"));
+    db2.close();
+    QVERIFY_SQL(db2, open(creator,creator));
+    QSqlQuery q2(db2);
+    QVERIFY_SQL(q2, exec("create table "+table1+"(id int primary key)"));
+    QVERIFY_SQL(q, exec(QLatin1String("create user "+appuser+" identified by "+appuser+" default tablespace users temporary tablespace temp")));
+    QVERIFY_SQL(q, exec(QLatin1String("grant CREATE ANY SYNONYM to "+appuser)));
+    QVERIFY_SQL(q, exec(QLatin1String("grant CONNECT to "+appuser)));
+    QVERIFY_SQL(q2, exec(QLatin1String("grant select, insert, update, delete on "+table1+" to "+appuser)));
+    QSqlDatabase db3=db.cloneDatabase(db, QLatin1String("oci_synonymstest2"));
+    db3.close();
+    QVERIFY_SQL(db3, open(appuser,appuser));
+    QSqlQuery q3(db3);
+    QVERIFY_SQL(q3, exec("create synonym "+appuser+'.'+qTableName("synonyms")+" for "+creator+'.'+table1));
+    QVERIFY_SQL(db3, tables().filter(qTableName("synonyms"), Qt::CaseInsensitive).count() >= 1);
+}
+
+
 // This test isn't really necessary as SQL_GUID / uniqueidentifier is
 // already tested in recordSQLServer().
 void tst_QSqlDatabase::odbc_uniqueidentifier()