/****************************************************************************

**

** Copyright (C) 2009 Nokia Corporation and/or its subsidiary(-ies).

** All rights reserved.

** Contact: Nokia Corporation (qt-info@nokia.com)

**

** This file is part of the test suite of the Qt Toolkit.

**

** $QT_BEGIN_LICENSE:LGPL$

** No Commercial Usage

** This file contains pre-release code and may not be distributed.

** You may use this file in accordance with the terms and conditions

** contained in the Technology Preview License Agreement accompanying

** this package.

**

** GNU Lesser General Public License Usage

** Alternatively, this file may be used under the terms of the GNU Lesser

** General Public License version 2.1 as published by the Free Software

** Foundation and appearing in the file LICENSE.LGPL included in the

** packaging of this file.  Please review the following information to

** ensure the GNU Lesser General Public License version 2.1 requirements

** will be met: http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html.

**

** In addition, as a special exception, Nokia gives you certain additional

** rights.  These rights are described in the Nokia Qt LGPL Exception

** version 1.1, included in the file LGPL_EXCEPTION.txt in this package.

**

** If you have questions regarding the use of this file, please contact

** Nokia at qt-info@nokia.com.

**

**

**

**

**

**

**

**

** $QT_END_LICENSE$

**

****************************************************************************/

#include <QtTest/QtTest>

#include <qsqldatabase.h>

#include <qsqlquery.h>

#include <qsqldriver.h>

#ifdef QT3_SUPPORT

#include <q3sqlcursor.h>

#include <q3sqlrecordinfo.h>

#include <q3cstring.h>

#endif

#include <qsqlrecord.h>

#include <qsqlfield.h>

#include <qsqlindex.h>

#include <qregexp.h>

#include <qvariant.h>

#include <qdatetime.h>

#include <qdebug.h>

#define NODATABASE_SKIP "No database drivers are available in this Qt configuration"

#include "tst_databases.h"

//TESTED_FILES=

QT_FORWARD_DECLARE_CLASS(QSqlDatabase)

struct FieldDef;

class tst_QSqlDatabase : public QObject

{

    Q_OBJECT

public:

    tst_QSqlDatabase();

    virtual ~tst_QSqlDatabase();

public slots:

    void initTestCase();

    void cleanupTestCase();

    void init();

    void cleanup();

private slots:

    void record_data() { generic_data(); }

    //void record();

    void open_data() { generic_data(); }

    void open();

    void tables_data() { generic_data(); }

    void tables();

    void transaction_data() { generic_data(); }

    void transaction();

    void eventNotification_data() { generic_data(); }

    void eventNotification();

    void addDatabase();

    //database specific tests

    void recordMySQL_data() { generic_data("QMYSQL"); }

    void recordMySQL();

    void recordPSQL_data() { generic_data("QPSQL"); }

    void recordPSQL();

    void recordOCI_data() { generic_data("QOCI"); }

    void recordOCI();

    void recordTDS_data() { generic_data("QTDS"); }

    void recordTDS();

    void recordDB2_data() { generic_data("QDB2"); }

    void recordDB2();

    void recordSQLite_data() { generic_data("QSQLITE"); }

    void recordSQLite();

    void recordAccess_data() { generic_data("QODBC"); }

    void recordAccess();

    void recordSQLServer_data() { generic_data("QODBC"); }

    void recordSQLServer();

    void recordIBase_data() {generic_data("QIBASE"); }

    void recordIBase();

    void eventNotificationIBase_data() { generic_data("QIBASE"); }

    void eventNotificationIBase();

    void eventNotificationPSQL_data() { generic_data("QPSQL"); }

    void eventNotificationPSQL();

    //database specific 64 bit integer test

    void bigIntField_data() { generic_data(); }

    void bigIntField();

    // general tests

    void getConnectionName_data() { generic_data(); }

    void getConnectionName(); // For task 129992

    //problem specific tests

    void alterTable_data() { generic_data(); }

    void alterTable();

    void recordNonSelect_data() { generic_data(); }

    void recordNonSelect();

    void caseSensivity_data() { generic_data(); }

    void caseSensivity();

    void noEscapedFieldNamesInRecord_data() { generic_data(); }

    void noEscapedFieldNamesInRecord();

    void whitespaceInIdentifiers_data() { generic_data(); }

    void whitespaceInIdentifiers();

    void formatValueTrimStrings_data() { generic_data(); }

    void formatValueTrimStrings();

    void precisionPolicy_data() { generic_data(); }

    void precisionPolicy();

    void db2_valueCacheUpdate_data() { generic_data("QDB2"); }

    void db2_valueCacheUpdate();

    void psql_schemas_data() { generic_data("QPSQL"); }

    void psql_schemas();

    void psql_escapedIdentifiers_data() { generic_data("QPSQL"); }

    void psql_escapedIdentifiers();

    void psql_escapeBytea_data() { generic_data("QPSQL"); }

    void psql_escapeBytea();

    void bug_249059_data() { generic_data("QPSQL"); }

    void bug_249059();

    void mysqlOdbc_unsignedIntegers_data() { generic_data(); }

    void mysqlOdbc_unsignedIntegers();

    void mysql_multiselect_data() { generic_data("QMYSQL"); }

    void mysql_multiselect();  // For task 144331

    void mysql_savepointtest_data() { generic_data("QMYSQL"); }

    void mysql_savepointtest();

    void accessOdbc_strings_data() { generic_data(); }

    void accessOdbc_strings();

    void ibase_numericFields_data() { generic_data("QIBASE"); }

    void ibase_numericFields(); // For task 125053

    void ibase_fetchBlobs_data() { generic_data("QIBASE"); }

    void ibase_fetchBlobs(); // For task 143471

    void ibase_useCustomCharset_data() { generic_data("QIBASE"); }

    void ibase_useCustomCharset(); // For task 134608

    void ibase_procWithoutReturnValues_data() { generic_data("QIBASE"); } // For task 165423

    void ibase_procWithoutReturnValues();

    void ibase_procWithReturnValues_data() { generic_data("QIBASE"); } // For task 177530

    void ibase_procWithReturnValues();

    void odbc_reopenDatabase_data() { generic_data("QODBC"); }

    void odbc_reopenDatabase();

    void odbc_uniqueidentifier_data() { generic_data("QODBC"); }

    void odbc_uniqueidentifier(); // For task 141822

    void odbc_uintfield_data() { generic_data("QODBC"); }

    void odbc_uintfield();

    void odbc_bindBoolean_data() { generic_data("QODBC"); }

    void odbc_bindBoolean();

    void odbc_testqGetString_data() { generic_data("QODBC"); }

    void odbc_testqGetString();

    void oci_serverDetach_data() { generic_data("QOCI"); }

    void oci_serverDetach(); // For task 154518

    void oci_xmltypeSupport_data() { generic_data("QOCI"); }

    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();

    void sqlStatementUseIsNull_189093_data() { generic_data(); }

    void sqlStatementUseIsNull_189093();

private:

    void createTestTables(QSqlDatabase db);

    void dropTestTables(QSqlDatabase db);

    void populateTestTables(QSqlDatabase db);

    void generic_data(const QString &engine=QString());

#ifdef QT3_SUPPORT

    void testRecordInfo(const FieldDef fieldDefs[], const Q3SqlRecordInfo& inf);

#endif

    void testRecord(const FieldDef fieldDefs[], const QSqlRecord& inf, QSqlDatabase db);

    void commonFieldTest(const FieldDef fieldDefs[], QSqlDatabase, const int);

    void checkValues(const FieldDef fieldDefs[], QSqlDatabase db);

    void checkNullValues(const FieldDef fieldDefs[], QSqlDatabase db);

    tst_Databases dbs;

};

// number of records to be inserted per testfunction

static const int ITERATION_COUNT = 2;

static int pkey = 1;

//helper class for database specific tests

struct FieldDef {

    FieldDef(QString tn = QString(),

          QVariant::Type t = QVariant::Invalid,

          QVariant v = QVariant(),

          bool nl = true):

        typeName(tn), type(t), val(v), nullable(nl) {}

    QString fieldName() const

    {

        QString rt = typeName;

        rt.replace(QRegExp("\\s"), QString("_"));

#ifdef QT3_SUPPORT

        int i = rt.find("(");

#else

        int i = rt.indexOf("(");

#endif

        if (i == -1)

            i = rt.length();

        if (i > 20)

            i = 20;

        return "t_" + rt.left(i);

    }

    QString typeName;

    QVariant::Type type;

    QVariant val;

    bool nullable;

};

// creates a table out of the FieldDefs and returns the number of fields

// excluding the primary key field

static int createFieldTable(const FieldDef fieldDefs[], QSqlDatabase db)

{

    tst_Databases::safeDropTable(db, qTableName("qtestfields"));

    QSqlQuery q(db);

    // construct a create table statement consisting of all fieldtypes

    QString qs = "create table " + qTableName("qtestfields");

    QString autoName = tst_Databases::autoFieldName(db);

    if (tst_Databases::isMSAccess(db))

        qs.append(" (id int not null");

    else if (tst_Databases::isPostgreSQL(db))

        qs.append(" (id serial not null");

    else

        qs.append(QString("(id integer not null %1 primary key").arg(autoName));

    int i = 0;

    for (i = 0; !fieldDefs[ i ].typeName.isNull(); ++i) {

        qs += QString(",\n %1 %2").arg(fieldDefs[ i ].fieldName()).arg(fieldDefs[ i ].typeName);

        if ((db.driverName().startsWith("QTDS") || tst_Databases::isSqlServer(db)) && fieldDefs[ i ].nullable) {

            qs += " null";

        }

    }

    if (tst_Databases::isMSAccess(db))

        qs.append(",\n primary key (id)");

    qs += ')';

    if (!q.exec(qs)) {

        qDebug() << "Creation of Table failed:" << tst_Databases::printError(q.lastError(), db);

        qDebug() << "Query: " << qs;

        return -1;

    }

    return i;

}

tst_QSqlDatabase::tst_QSqlDatabase()

{

}

tst_QSqlDatabase::~tst_QSqlDatabase()

{

}

void tst_QSqlDatabase::createTestTables(QSqlDatabase db)

{

    if (!db.isValid())

    return;

    QSqlQuery q(db);

    if (db.driverName().startsWith("QMYSQL"))

        // ### stupid workaround until we find a way to hardcode this

        // in the MySQL server startup script

        q.exec("set table_type=innodb");

    else if (tst_Databases::isSqlServer(db)) {

        QVERIFY_SQL(q, exec("SET ANSI_DEFAULTS ON"));

        QVERIFY_SQL(q, exec("SET IMPLICIT_TRANSACTIONS OFF"));

    } else if(tst_Databases::isPostgreSQL(db))

        QVERIFY_SQL( q, exec("set client_min_messages='warning'"));

    // please never ever change this table; otherwise fix all tests ;)

    if (tst_Databases::isMSAccess(db)) {

        QVERIFY_SQL(q, exec("create table " + qTableName("qtest") +

                   " (id int not null, t_varchar varchar(40) not null, t_char char(40), "

                   "t_numeric number, primary key (id, t_varchar))"));

    } else {

        QVERIFY_SQL(q, exec("create table " + qTableName("qtest") +

               " (id integer not null, t_varchar varchar(40) not null, "

               "t_char char(40), t_numeric numeric(6, 3), primary key (id, t_varchar))"));

    }

    if (testWhiteSpaceNames(db.driverName())) {

        QString qry = "create table "

            + db.driver()->escapeIdentifier(qTableName("qtest") + " test", QSqlDriver::TableName)

            + '('

            + db.driver()->escapeIdentifier(QLatin1String("test test"), QSqlDriver::FieldName)

            + " int not null primary key)";

        QVERIFY_SQL(q, exec(qry));

    }

}

void tst_QSqlDatabase::dropTestTables(QSqlDatabase db)

{

    if (!db.isValid())

        return;

    if(tst_Databases::isPostgreSQL(db)) {

        QSqlQuery q(db);

        QVERIFY_SQL( q, exec("set client_min_messages='warning'"));

    }

    // drop the view first, otherwise we'll get dependency problems

    tst_Databases::safeDropViews(db, QStringList() << qTableName("qtest_view") << qTableName("qtest_view2"));

    QStringList tableNames;

    tableNames << qTableName("qtest")

            << qTableName("qtestfields")

            << qTableName("qtestalter")

            << qTableName("qtest_temp")

            << qTableName("qtest_bigint")

            << qTableName("qtest_xmltype")

            << qTableName("latin1table")

            << qTableName("qtest_sqlguid")

            << qTableName("batable")

            << qTableName("qtest_prec")

            << qTableName("uint")

            << qTableName("strings")

            << qTableName("numericfields")

            << qTableName("qtest_ibaseblobs")

            << qTableName("qtestBindBool")

            << qTableName("testqGetString")

            << qTableName("qtest_sqlguid")

            << qTableName("uint_table")

            << qTableName("uint_test")

            << qTableName("bug_249059");

    QSqlQuery q(0, db);

    if (db.driverName().startsWith("QPSQL")) {

        q.exec("drop schema " + qTableName("qtestschema") + " cascade");

        q.exec("drop schema " + qTableName("qtestScHeMa") + " cascade");

    }

    if (testWhiteSpaceNames(db.driverName()))

        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)

{

    if (!db.isValid())

        return;

    QSqlQuery q(db);

    q.exec("delete from " + qTableName("qtest")); //non-fatal

    QVERIFY_SQL(q, exec("insert into " + qTableName("qtest") + " (id, t_varchar, t_char, t_numeric) values (0, 'VarChar0', 'Char0', 1.1)"));

    QVERIFY_SQL(q, exec("insert into " + qTableName("qtest") + " (id, t_varchar, t_char, t_numeric) values (1, 'VarChar1', 'Char1', 2.2)"));

    QVERIFY_SQL(q, exec("insert into " + qTableName("qtest") + " (id, t_varchar, t_char, t_numeric) values (2, 'VarChar2', 'Char2', 3.3)"));

    QVERIFY_SQL(q, exec("insert into " + qTableName("qtest") + " (id, t_varchar, t_char, t_numeric) values (3, 'VarChar3', 'Char3', 4.4)"));

    QVERIFY_SQL(q, exec("insert into " + qTableName("qtest") + " (id, t_varchar, t_char, t_numeric) values (4, 'VarChar4', NULL, NULL)"));

}

void tst_QSqlDatabase::initTestCase()

{

    dbs.open();

    for (QStringList::ConstIterator it = dbs.dbNames.begin(); it != dbs.dbNames.end(); ++it) {

        QSqlDatabase db = QSqlDatabase::database((*it));

        CHECK_DATABASE(db);

        dropTestTables(db); //in case of leftovers

        createTestTables(db);

        populateTestTables(db);

    }

}

void tst_QSqlDatabase::cleanupTestCase()

{

    for (QStringList::ConstIterator it = dbs.dbNames.begin(); it != dbs.dbNames.end(); ++it) {

        QSqlDatabase db = QSqlDatabase::database((*it));

        CHECK_DATABASE(db);

        dropTestTables(db);

    }

    dbs.close();

}

void tst_QSqlDatabase::init()

{

}

void tst_QSqlDatabase::cleanup()

{

}

void tst_QSqlDatabase::generic_data(const QString& engine)

{

    if ( dbs.fillTestTable(engine) == 0 ) {

        if(engine.isEmpty())

           QSKIP( "No database drivers are available in this Qt configuration", SkipAll );

        else

           QSKIP( (QString("No database drivers of type %1 are available in this Qt configuration").arg(engine)).toLocal8Bit(), SkipAll );

    }

}

void tst_QSqlDatabase::addDatabase()

{

    QTest::ignoreMessage(QtWarningMsg, "QSqlDatabase: BLAH_FOO_NONEXISTENT_DRIVER driver not loaded");

    QTest::ignoreMessage(QtWarningMsg, qPrintable("QSqlDatabase: available drivers: " + QSqlDatabase::drivers().join(QLatin1String(" "))));

    {

        QSqlDatabase db = QSqlDatabase::addDatabase("BLAH_FOO_NONEXISTENT_DRIVER",

                                                    "INVALID_CONNECTION");

        QVERIFY(!db.isValid());

    }

    QVERIFY(QSqlDatabase::contains("INVALID_CONNECTION"));

    QSqlDatabase::removeDatabase("INVALID_CONNECTION");

    QVERIFY(!QSqlDatabase::contains("INVALID_CONNECTION"));

}

void tst_QSqlDatabase::open()

{

    QFETCH(QString, dbName);

    QSqlDatabase db = QSqlDatabase::database(dbName);

    CHECK_DATABASE(db);

    int i;

    for (i = 0; i < 10; ++i) {

        db.close();

        QVERIFY(!db.isOpen());

        QVERIFY_SQL(db, open());

        QVERIFY(db.isOpen());

        QVERIFY(!db.isOpenError());

    }

    if (db.driverName().startsWith("QSQLITE") && db.databaseName() == ":memory:") {

        // tables in in-memory databases don't survive an open/close

        createTestTables(db);

        populateTestTables(db);

    }

}

void tst_QSqlDatabase::recordNonSelect()

{

#ifdef QT3_SUPPORT

    QFETCH(QString, dbName);

    QSqlDatabase db = QSqlDatabase::database(dbName);

    CHECK_DATABASE(db);

    QSqlQuery q(db);

    // nothing should happen on an empty query

    QSqlRecord rec = db.record(q);

    QVERIFY(rec.isEmpty());

    Q3SqlRecordInfo rInf = db.recordInfo(q);

    QVERIFY(rInf.isEmpty());

    QVERIFY_SQL(q, exec("create table " + qTableName("qtest_temp") + " (id int)"));

    // query without result set should return empty record

    rec = db.record(q);

    QVERIFY(rec.isEmpty());

    rInf = db.recordInfo(q);

    QVERIFY(rInf.isEmpty());

#endif

}

void tst_QSqlDatabase::tables()

{

    QFETCH(QString, dbName);

    QSqlDatabase db = QSqlDatabase::database(dbName);

    CHECK_DATABASE(db);

    bool views = true;

    bool tempTables = false;

    QSqlQuery q(db);

    if ( db.driverName().startsWith( "QMYSQL" ) && tst_Databases::getMySqlVersion( db ).section( QChar('.'), 0, 0 ).toInt()<5 )

        QSKIP( "Test requires MySQL >= 5.0", SkipSingle );

    if (!q.exec("CREATE VIEW " + qTableName("qtest_view") + " as select * from " + qTableName("qtest"))) {

        qDebug(QString("DBMS '%1' cannot handle VIEWs: %2").arg(

                tst_Databases::dbToString(db)).arg(QString(tst_Databases::printError(q.lastError()))).toLatin1());

        views = false;

    }

    if (db.driverName().startsWith("QSQLITE3")) {

        QVERIFY_SQL(q, exec("CREATE TEMPORARY TABLE " + qTableName("temp_tab") + " (id int)"));

        tempTables = true;

    }