diff options
| -rw-r--r-- | src/sql/drivers/oci/qsql_oci.cpp | 29 | ||||
| -rw-r--r-- | tests/auto/qsqldatabase/tst_qsqldatabase.cpp | 38 |
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() |