SQL/Tests: make sure created procedures are cleaned up on exit

Similar to TableScope - create a helper class to make sure the
procedures are cleaned up on exit so they don't affect the result during
the next test run.

Change-Id: Ic5b02ca63e03f330392797ed22313767557fc548
Reviewed-by: Volker Hilsheimer <volker.hilsheimer@qt.io>

3 files changed, 71 insertions, 78 deletions

diff --git a/tests/auto/sql/kernel/qsqldatabase/tst_databases.h b/tests/auto/sql/kernel/qsqldatabase/tst_databases.h
index 4c02a196c8..0debe5d549 100644
--- a/tests/auto/sql/kernel/qsqldatabase/tst_databases.h
+++ b/tests/auto/sql/kernel/qsqldatabase/tst_databases.h
@@ -495,5 +495,33 @@ private:
     QString m_tableName;
 };
 
+class ProcScope
+{
+public:
+    ProcScope(const QSqlDatabase &db, const char *procName, const char *file)
+        : m_db(db),
+          m_procName(qTableName(procName, file, db))
+    {
+        cleanup();
+    }
+    ~ProcScope()
+    {
+        cleanup();
+    }
+    QString name() const
+    {
+        return m_procName;
+    }
+protected:
+    void cleanup()
+    {
+        QSqlQuery q(m_db);
+        q.exec("DROP PROCEDURE IF EXISTS " + m_procName);
+    }
+private:
+    QSqlDatabase m_db;
+    const QString m_procName;
+};
+
 #endif
 
diff --git a/tests/auto/sql/kernel/qsqldatabase/tst_qsqldatabase.cpp b/tests/auto/sql/kernel/qsqldatabase/tst_qsqldatabase.cpp
index 3eeb91b680..2898725352 100644
--- a/tests/auto/sql/kernel/qsqldatabase/tst_qsqldatabase.cpp
+++ b/tests/auto/sql/kernel/qsqldatabase/tst_qsqldatabase.cpp
@@ -1613,13 +1613,11 @@ void tst_QSqlDatabase::ibase_procWithoutReturnValues()
     QFETCH(QString, dbName);
     QSqlDatabase db = QSqlDatabase::database(dbName);
     CHECK_DATABASE(db);
+    ProcScope ps(db, "qtest_proc1", __FILE__);
 
     QSqlQuery q(db);
-    const QString procName(qTableName("qtest_proc1", __FILE__, db));
-    q.exec(QString("drop procedure %1").arg(procName));
-    QVERIFY_SQL(q, exec("CREATE PROCEDURE " + procName + " (str VARCHAR(10))\nAS BEGIN\nstr='test';\nEND;"));
-    QVERIFY_SQL(q, exec(QString("execute procedure %1('qtest')").arg(procName)));
-    q.exec(QString("drop procedure %1").arg(procName));
+    QVERIFY_SQL(q, exec("CREATE PROCEDURE " + ps.name() + " (str VARCHAR(10))\nAS BEGIN\nstr='test';\nEND;"));
+    QVERIFY_SQL(q, exec(QString("execute procedure %1('qtest')").arg(ps.name())));
 }
 
 void tst_QSqlDatabase::ibase_procWithReturnValues()
@@ -1627,12 +1625,10 @@ void tst_QSqlDatabase::ibase_procWithReturnValues()
     QFETCH(QString, dbName);
     QSqlDatabase db = QSqlDatabase::database(dbName);
     CHECK_DATABASE(db);
-
-    const QString procName(qTableName("qtest_proc2", __FILE__, db));
+    ProcScope ps(db, "qtest_proc2", __FILE__);
 
     QSqlQuery q(db);
-    q.exec(QString("drop procedure %1").arg(procName));
-    QVERIFY_SQL(q, exec("CREATE PROCEDURE " + procName + " ("
+    QVERIFY_SQL(q, exec("CREATE PROCEDURE " + ps.name() + " ("
                         "\nABC INTEGER)"
                         "\nRETURNS ("
                         "\nRESULT INTEGER)"
@@ -1643,13 +1639,13 @@ void tst_QSqlDatabase::ibase_procWithReturnValues()
                         "\nend"));
 
     // Interbase procedures can be executed in two ways: EXECUTE PROCEDURE or SELECT
-    QVERIFY_SQL(q, exec(QString("execute procedure %1(123)").arg(procName)));
+    QVERIFY_SQL(q, exec(QString("execute procedure %1(123)").arg(ps.name())));
     QVERIFY_SQL(q, next());
     QCOMPARE(q.value(0).toInt(), 1230);
-    QVERIFY_SQL(q, exec(QString("select result from %1(456)").arg(procName)));
+    QVERIFY_SQL(q, exec(QString("select result from %1(456)").arg(ps.name())));
     QVERIFY_SQL(q, next());
     QCOMPARE(q.value(0).toInt(), 4560);
-    QVERIFY_SQL(q, prepare(QLatin1String("execute procedure ")+procName+QLatin1String("(?)")));
+    QVERIFY_SQL(q, prepare(QLatin1String("execute procedure ") + ps.name() + QLatin1String("(?)")));
     q.bindValue(0, 123);
     QVERIFY_SQL(q, exec());
     QVERIFY_SQL(q, next());
@@ -1658,8 +1654,6 @@ void tst_QSqlDatabase::ibase_procWithReturnValues()
     QVERIFY_SQL(q, exec());
     QVERIFY_SQL(q, next());
     QCOMPARE(q.value(0).toInt(), 4560);
-
-    q.exec(QString("drop procedure %1").arg(procName));
 }
 
 void tst_QSqlDatabase::formatValueTrimStrings()
@@ -2020,16 +2014,15 @@ void tst_QSqlDatabase::eventNotificationIBase()
     QSqlDatabase db = QSqlDatabase::database(dbName);
     CHECK_DATABASE(db);
 
-    const QString procedureName(qTableName("posteventProc", __FILE__, db));
+    ProcScope ps(db, "posteventProc", __FILE__);
     QSqlDriver *driver=db.driver();
-    QVERIFY_SQL(*driver, subscribeToNotification(procedureName));
+    QVERIFY_SQL(*driver, subscribeToNotification(ps.name()));
     QTest::qWait(300);  // Interbase needs some time to call the driver callback.
 
     db.transaction();   // InterBase events are posted from within transactions.
     QSqlQuery q(db);
-    q.exec(QString("DROP PROCEDURE %1").arg(procedureName));
-    q.exec(QString("CREATE PROCEDURE %1\nAS BEGIN\nPOST_EVENT '%1';\nEND;").arg(procedureName));
-    q.exec(QString("EXECUTE PROCEDURE %1").arg(procedureName));
+    q.exec(QString("CREATE PROCEDURE %1\nAS BEGIN\nPOST_EVENT '%1';\nEND;").arg(ps.name()));
+    q.exec(QString("EXECUTE PROCEDURE %1").arg(ps.name()));
     QSignalSpy spy(driver, &QSqlDriver::notification);
     db.commit();        // No notifications are posted until the transaction is committed.
     // Interbase needs some time to post the notification and call the driver callback.
@@ -2037,9 +2030,8 @@ void tst_QSqlDatabase::eventNotificationIBase()
     // event handler in the driver to be executed and emit the notification signal.
     QTRY_COMPARE(spy.size(), 1);
     QList<QVariant> arguments = spy.takeFirst();
-    QCOMPARE(arguments.at(0).toString(), procedureName);
-    QVERIFY_SQL(*driver, unsubscribeFromNotification(procedureName));
-    q.exec(QString("DROP PROCEDURE %1").arg(procedureName));
+    QCOMPARE(arguments.at(0).toString(), ps.name());
+    QVERIFY_SQL(*driver, unsubscribeFromNotification(ps.name()));
 }
 
 void tst_QSqlDatabase::eventNotificationPSQL()
diff --git a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
index 205b3617dd..7ab17802b9 100644
--- a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
+++ b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
@@ -342,16 +342,6 @@ void tst_QSqlQuery::dropTestTables(QSqlDatabase db)
                << qTableName("qtest_null", __FILE__, db)
                << qTableName("tst_record", __FILE__, db);
 
-    if (dbType == QSqlDriver::MSSqlServer) {
-        QSqlQuery q(db);
-        q.exec("DROP PROCEDURE " + qTableName("test141895_proc", __FILE__, db));
-    }
-
-    if (dbType == QSqlDriver::MySqlServer) {
-        QSqlQuery q(db);
-        q.exec("DROP PROCEDURE IF EXISTS " + qTableName("bug6852_proc", __FILE__, db));
-    }
-
     tst_Databases::safeDropTables(db, tablenames);
 
     if (dbType == QSqlDriver::Oracle) {
@@ -529,7 +519,7 @@ void tst_QSqlQuery::mysql_outValues()
     QSqlDatabase db = QSqlDatabase::database(dbName);
     CHECK_DATABASE(db);
     const QString hello(qTableName("hello", __FILE__, db));
-    const QString qtestproc(qTableName("qtestproc", __FILE__, db));
+    ProcScope ps(db, "qtestproc", __FILE__);
 
     QSqlQuery q(db);
 
@@ -551,25 +541,22 @@ void tst_QSqlQuery::mysql_outValues()
     QCOMPARE(q.value(0).toString(), u"Hello harald");
 
     QVERIFY_SQL(q, exec("drop function " + hello));
-    q.exec("drop procedure " + qtestproc);
 
     QVERIFY_SQL(q, exec(QLatin1String("create procedure %1 () BEGIN "
-                                      "select * from %2 order by id; END").arg(qtestproc, qtest)));
-    QVERIFY_SQL(q, exec(QLatin1String("call %1()").arg(qtestproc)));
+                                      "select * from %2 order by id; END").arg(ps.name(), qtest)));
+    QVERIFY_SQL(q, exec(QLatin1String("call %1()").arg(ps.name())));
     QVERIFY_SQL(q, next());
     QCOMPARE(q.value(1).toString(), u"VarChar1");
 
-    QVERIFY_SQL(q, exec("drop procedure " + qtestproc));
+    QVERIFY_SQL(q, exec("drop procedure " + ps.name()));
     QVERIFY_SQL(q, exec(QLatin1String("create procedure %1 (OUT param1 INT) "
-                                      "BEGIN set param1 = 42; END").arg(qtestproc)));
+                                      "BEGIN set param1 = 42; END").arg(ps.name())));
 
-    QVERIFY_SQL(q, exec(QLatin1String("call %1 (@out)").arg(qtestproc)));
+    QVERIFY_SQL(q, exec(QLatin1String("call %1 (@out)").arg(ps.name())));
     QVERIFY_SQL(q, exec("select @out"));
     QCOMPARE(q.record().fieldName(0), u"@out");
     QVERIFY_SQL(q, next());
     QCOMPARE(q.value(0).toInt(), 42);
-
-    QVERIFY_SQL(q, exec("drop procedure " + qtestproc));
 }
 
 void tst_QSqlQuery::bindBool()
@@ -829,19 +816,15 @@ void tst_QSqlQuery::storedProceduresIBase()
     QFETCH(QString, dbName);
     QSqlDatabase db = QSqlDatabase::database(dbName);
     CHECK_DATABASE(db);
+    ProcScope ps(db, "TESTPROC", __FILE__);
 
     QSqlQuery q(db);
-    const auto procName = qTableName("TESTPROC", __FILE__, db);
-    q.exec("drop procedure " + procName);
-
     QVERIFY_SQL(q, exec(QLatin1String("create procedure %1 RETURNS (x integer, y varchar(20)) "
                                       "AS BEGIN "
                                       "  x = 42; "
                                       "  y = 'Hello Anders'; "
-                                      "END").arg(procName)));
-    const auto tidier = qScopeGuard([&]() { q.exec("drop procedure " + procName); });
-
-    QVERIFY_SQL(q, prepare("execute procedure " + procName));
+                                      "END").arg(ps.name())));
+    QVERIFY_SQL(q, prepare("execute procedure " + ps.name()));
     QVERIFY_SQL(q, exec());
 
     // Check for a valid result set:
@@ -868,12 +851,11 @@ void tst_QSqlQuery::outValuesDB2()
     if (!db.driver()->hasFeature(QSqlDriver::PreparedQueries))
         QSKIP("Test requires prepared query support");
 
-    QSqlQuery q(db);
+    ProcScope ps(db, "tst_outValues", __FILE__);
 
+    QSqlQuery q(db);
     q.setForwardOnly(true);
 
-    const QString procName = qTableName("tst_outValues", __FILE__, db);
-    q.exec("drop procedure " + procName); // non-fatal
     QVERIFY_SQL(q, exec(QLatin1String("CREATE PROCEDURE %1 "
                                       "(OUT x int, OUT x2 double, OUT x3 char(20))\n"
                                       "LANGUAGE SQL\n"
@@ -881,9 +863,9 @@ void tst_QSqlQuery::outValuesDB2()
                                       " SET x = 42;\n"
                                       " SET x2 = 4.2;\n"
                                       " SET x3 = 'Homer';\n"
-                                      "END P1").arg(procName)));
+                                      "END P1").arg(ps.name())));
 
-    QVERIFY_SQL(q, prepare(QLatin1String("call %1(?, ?, ?)").arg(procName)));
+    QVERIFY_SQL(q, prepare(QLatin1String("call %1(?, ?, ?)").arg(ps.name())));
 
     q.addBindValue(0, QSql::Out);
     q.addBindValue(0.0, QSql::Out);
@@ -901,11 +883,11 @@ void tst_QSqlQuery::outValues()
     QFETCH(QString, dbName);
     QSqlDatabase db = QSqlDatabase::database(dbName);
     CHECK_DATABASE(db);
-    const QString tst_outValues(qTableName("tst_outValues", __FILE__, db));
 
     if (!db.driver()->hasFeature(QSqlDriver::PreparedQueries))
         QSKIP("Test requires prepared query support");
 
+    ProcScope ps(db, "tst_outValues", __FILE__);
     QSqlQuery q(db);
 
     q.setForwardOnly(true);
@@ -919,7 +901,6 @@ void tst_QSqlQuery::outValues()
         caller = QLatin1String("call %1(?)");
         break;
     case QSqlDriver::DB2:
-        q.exec("drop procedure " + tst_outValues); // non-fatal
         creator = QLatin1String("CREATE PROCEDURE %1 (OUT x int)\n"
                                 "LANGUAGE SQL\n"
                                 "P1: BEGIN\n"
@@ -928,7 +909,6 @@ void tst_QSqlQuery::outValues()
         caller = QLatin1String("call %1(?)");
         break;
     case QSqlDriver::MSSqlServer:
-        q.exec("drop procedure " + tst_outValues);  // non-fatal
         creator = QLatin1String("create procedure %1 (@x int out) as\n"
                                 "begin\n"
                                 "    set @x = 42\n"
@@ -939,8 +919,8 @@ void tst_QSqlQuery::outValues()
         QSKIP("Don't know how to create a stored procedure for this database server, "
               "please fix this test");
     }
-    QVERIFY_SQL(q, exec(creator.arg(tst_outValues)));
-    QVERIFY(q.prepare(caller.arg(tst_outValues)));
+    QVERIFY_SQL(q, exec(creator.arg(ps.name())));
+    QVERIFY(q.prepare(caller.arg(ps.name())));
 
     q.addBindValue(0, QSql::Out);
 
@@ -3564,11 +3544,10 @@ void tst_QSqlQuery::sqlServerReturn0()
     if (tst_Databases::getDatabaseType(db) != QSqlDriver::MSSqlServer)
         QSKIP("Test is specific to SQL Server");
 
+    ProcScope ps(db, "test141895_proc", __FILE__);
     TableScope ts(db, "test141895", __FILE__);
     const auto &tableName = ts.tableName();
-    const QString procName(qTableName("test141895_proc", __FILE__, db));
     QSqlQuery q(db);
-    q.exec("DROP PROCEDURE " + procName);
     QVERIFY_SQL(q, exec(QLatin1String("CREATE TABLE %1 (id integer)").arg(tableName)));
     QVERIFY_SQL(q, exec(QLatin1String("INSERT INTO %1 (id) VALUES (1)").arg(tableName)));
     QVERIFY_SQL(q, exec(QLatin1String("INSERT INTO %1 (id) VALUES (2)").arg(tableName)));
@@ -3576,10 +3555,10 @@ void tst_QSqlQuery::sqlServerReturn0()
     QVERIFY_SQL(q, exec(QLatin1String("INSERT INTO %1 (id) VALUES (3)").arg(tableName)));
     QVERIFY_SQL(q, exec(QLatin1String("INSERT INTO %1 (id) VALUES (1)").arg(tableName)));
     QVERIFY_SQL(q, exec(QLatin1String("CREATE PROCEDURE %1 AS "
-                                      "SELECT * FROM %1 WHERE ID = 2 "
-                                      "RETURN 0").arg(tableName)));
+                                      "SELECT * FROM %2 WHERE ID = 2 "
+                                      "RETURN 0").arg(ps.name(), ts.tableName())));
 
-    QVERIFY_SQL(q, exec(QLatin1String("{CALL %1}").arg(procName)));
+    QVERIFY_SQL(q, exec(QLatin1String("{CALL %1}").arg(ps.name())));
     QVERIFY_SQL(q, next());
 }
 
@@ -3677,24 +3656,21 @@ void tst_QSqlQuery::QTBUG_18435()
     if (dbType != QSqlDriver::MSSqlServer || !db.driverName().startsWith("QODBC"))
         QSKIP("Test is specific to SQL Server");
 
+    ProcScope ps(db, "qtbug_18435_proc", __FILE__);
     QSqlQuery q(db);
-    QString procName(qTableName("qtbug_18435_proc", __FILE__, db));
 
-    q.exec("DROP PROCEDURE " + procName);
     const QString stmt = QLatin1String("CREATE PROCEDURE %1 @key nvarchar(50) OUTPUT AS\n"
                                        "BEGIN\n"
                                        "  SET NOCOUNT ON\n"
                                        "  SET @key = 'TEST'\n"
-                                       "END\n").arg(procName);
+                                       "END\n").arg(ps.name());
 
     QVERIFY_SQL(q, exec(stmt));
-    QVERIFY_SQL(q, prepare(QLatin1String("{CALL %1(?)}").arg(procName)));
+    QVERIFY_SQL(q, prepare(QLatin1String("{CALL %1(?)}").arg(ps.name())));
     const QString testStr = "0123";
     q.bindValue(0, testStr, QSql::Out);
     QVERIFY_SQL(q, exec());
     QCOMPARE(q.boundValue(0).toString(), QLatin1String("TEST"));
-
-    QVERIFY_SQL(q, exec("DROP PROCEDURE " + procName));
 }
 
 void tst_QSqlQuery::QTBUG_5251()
@@ -3765,9 +3741,8 @@ void tst_QSqlQuery::QTBUG_6618()
     if (tst_Databases::getDatabaseType(db) != QSqlDriver::MSSqlServer)
         QSKIP("Test is specific to SQL Server");
 
+    ProcScope ps(db, "tst_raiseError", __FILE__);
     QSqlQuery q(db);
-    const QString procedureName = qTableName("tst_raiseError", __FILE__, db);
-    q.exec("drop procedure " + procedureName);  // non-fatal
     QString errorString;
     for (int i = 0; i < 110; ++i)
         errorString += "reallylong";
@@ -3775,8 +3750,8 @@ void tst_QSqlQuery::QTBUG_6618()
     QVERIFY_SQL(q, exec(QLatin1String("create procedure %1 as\n"
                                       "begin\n"
                                       "    raiserror('%2', 16, 1)\n"
-                                      "end\n").arg(procedureName, errorString)));
-    q.exec(QLatin1String("{call %1}").arg(procedureName));
+                                      "end\n").arg(ps.name(), errorString)));
+    q.exec(QLatin1String("{call %1}").arg(ps.name()));
     QVERIFY(q.lastError().text().contains(errorString));
 }
 
@@ -3787,11 +3762,9 @@ void tst_QSqlQuery::QTBUG_6852()
     CHECK_DATABASE(db);
     TableScope ts(db, "bug6852", __FILE__);
     const auto &tableName = ts.tableName();
+    ProcScope ps(db, "bug6852_proc", __FILE__);
 
     QSqlQuery q(db);
-    const QString procName(qTableName("bug6852_proc", __FILE__, db));
-
-    QVERIFY_SQL(q, exec("DROP PROCEDURE IF EXISTS " + procName));
     QVERIFY_SQL(q, exec(QLatin1String("CREATE TABLE %1(\n"
                                       "MainKey INT NOT NULL,\n"
                                       "OtherTextCol VARCHAR(45) NOT NULL,\n"
@@ -3806,9 +3779,9 @@ void tst_QSqlQuery::QTBUG_6852()
                                       "  SET @st = 'SELECT MainKey, OtherTextCol from %2';\n"
                                       "  PREPARE stmt from @st;\n"
                                       "  EXECUTE stmt;\n"
-                                      "END;").arg(procName, tableName)));
+                                      "END;").arg(ps.name(), tableName)));
 
-    QVERIFY_SQL(q, exec(QLatin1String("CALL %1()").arg(procName)));
+    QVERIFY_SQL(q, exec(QLatin1String("CALL %1()").arg(ps.name())));
     QVERIFY_SQL(q, next());
     QCOMPARE(q.value(0).toInt(), 0);
     QCOMPARE(q.value(1).toString(), QLatin1String("Disabled"));