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

**

** 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 <QtSql/QtSql>

#include "../qsqldatabase/tst_databases.h"

//TESTED_FILES=

class tst_QSqlQuery : public QObject

{

    Q_OBJECT

public:

    tst_QSqlQuery();

    virtual ~tst_QSqlQuery();

public slots:

    void initTestCase();

    void cleanupTestCase();

    void init();

    void cleanup();

private slots:

    void value_data() { generic_data(); }

    void value();

    void isValid_data() { generic_data(); }

    void isValid();

    void isActive_data() { generic_data(); }

    void isActive();

    void isSelect_data() { generic_data(); }

    void isSelect();

    void numRowsAffected_data() { generic_data(); }

    void numRowsAffected();

    void size_data() { generic_data(); }

    void size();

    void isNull_data() { generic_data(); }

    void isNull();

    void query_exec_data() { generic_data(); }

    void query_exec();

    void execErrorRecovery_data() { generic_data(); }

    void execErrorRecovery();

    void first_data() { generic_data(); }

    void first();

    void next_data() { generic_data(); }

    void next();

    void prev_data() { generic_data(); }

    void prev();

    void last_data() { generic_data(); }

    void last();

    void seek_data() { generic_data(); }

    void seek();

    void transaction_data() { generic_data(); }

    void transaction();

    void record_data() { generic_data(); }

    void record();

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

    void record_sqlite();

    void finish_data() { generic_data(); }

    void finish();

    void sqlite_finish_data() { generic_data(); }

    void sqlite_finish();

    void nextResult_data() { generic_data(); }

    void nextResult();

    // forwardOnly mode need special treatment

    void forwardOnly_data() { generic_data(); }

    void forwardOnly();

    // bug specific tests

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

    void bitField();

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

    void nullBlob();

    void blob_data() { generic_data(); }

    void blob();

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

    void rawField();

    void precision_data() { generic_data(); }

    void precision();

    void nullResult_data() { generic_data(); }

    void nullResult();

    void joins_data() { generic_data(); }

    void joins();

    void outValues_data() { generic_data(); }

    void outValues();

    void char1Select_data() { generic_data(); }

    void char1Select();

    void char1SelectUnicode_data() { generic_data(); }

    void char1SelectUnicode();

    void synonyms_data() { generic_data(); }

    void synonyms();

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

    void oraOutValues();

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

    void mysqlOutValues();

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

    void oraClob();

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

    void oraLong();

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

    void outValuesDB2();

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

    void storedProceduresIBase();

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

    void oraRowId();

    void prepare_bind_exec_data() { generic_data(); }

    void prepare_bind_exec();

    void prepared_select_data() { generic_data(); }

    void prepared_select();

    void sqlServerLongStrings_data() { generic_data(); }

    void sqlServerLongStrings();

    void invalidQuery_data() { generic_data(); }

    void invalidQuery();

    void batchExec_data() { generic_data(); }

    void batchExec();

    void oraArrayBind_data() { generic_data(); }

    void oraArrayBind();

    void lastInsertId_data() { generic_data(); }

    void lastInsertId();

    void lastQuery_data() { generic_data(); }

    void lastQuery();

    void bindWithDoubleColonCastOperator_data() { generic_data(); }

    void bindWithDoubleColonCastOperator();

    void queryOnInvalidDatabase_data() { generic_data(); }

    void queryOnInvalidDatabase();

    void createQueryOnClosedDatabase_data() { generic_data(); }

    void createQueryOnClosedDatabase();

    void seekForwardOnlyQuery_data() { generic_data(); }

    void seekForwardOnlyQuery();

    void reExecutePreparedForwardOnlyQuery_data() { generic_data(); }

    void reExecutePreparedForwardOnlyQuery();

    void blobsPreparedQuery_data() { generic_data(); }

    void blobsPreparedQuery();

    void emptyTableNavigate_data() { generic_data(); }

    void emptyTableNavigate();

#ifdef NOT_READY_YET

    void task_229811();

    void task_229811_data() { generic_data(); }

    void task_234422_data() {  generic_data(); }

    void task_234422();

#endif

    void task_217003_data() { generic_data(); }

    void task_217003();

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

    void task_250026();

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

    void task_205701();

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

    void task_233829();

    void sqlServerReturn0_data() { generic_data(); }

    void sqlServerReturn0();

private:

    // returns all database connections

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

    void dropTestTables( QSqlDatabase db );

    void createTestTables( QSqlDatabase db );

    void populateTestTables( QSqlDatabase db );

    tst_Databases dbs;

};

tst_QSqlQuery::tst_QSqlQuery()

{

}

tst_QSqlQuery::~tst_QSqlQuery()

{

}

void tst_QSqlQuery::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_QSqlQuery::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_QSqlQuery::init()

{

}

void tst_QSqlQuery::cleanup()

{

    QFETCH( QString, dbName );

    QSqlDatabase db = QSqlDatabase::database( dbName );

    CHECK_DATABASE( db );

    if ( QTest::currentTestFunction() == QLatin1String( "numRowsAffected" )

            || QTest::currentTestFunction() == QLatin1String( "transactions" )

            || QTest::currentTestFunction() == QLatin1String( "size" )

            || QTest::currentTestFunction() == QLatin1String( "isActive" )

            || QTest::currentTestFunction() == QLatin1String( "lastInsertId" ) ) {

        populateTestTables( db );

    }

    if ( QTest::currentTestFailed() && ( db.driverName().startsWith( "QOCI" )

                                         || db.driverName().startsWith( "QODBC" ) ) ) {

        //since Oracle ODBC totally craps out on error, we init again

        db.close();

        db.open();

    }

}

void tst_QSqlQuery::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_QSqlQuery::dropTestTables( QSqlDatabase db )

{

    QStringList tablenames;

    // drop all the table in case a testcase failed

    tablenames <<  qTableName( "qtest" )

               << qTableName( "qtest_null" )

               << qTableName( "qtest_blob" )

               << qTableName( "qtest_bittest" )

               << qTableName( "qtest_nullblob" )

               << qTableName( "qtest_rawtest" )

               << qTableName( "qtest_precision" )

               << qTableName( "qtest_prepare" )

               << qTableName( "qtestj1" )

               << qTableName( "qtestj2" )

               << qTableName( "char1Select" )

               << qTableName( "char1SelectUnicode" )

               << qTableName( "qxmltest" )

               << qTableName( "qtest_exerr" )

               << qTableName( "qtest_empty" )

               << qTableName( "clobby" )

               << qTableName( "bindtest" )

               << qTableName( "more_results" )

               << qTableName( "blobstest" )

               << qTableName( "oraRowId" )

               << qTableName( "qtest_batch" );

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

        tablenames << qTableName("task_233829");

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

        tablenames << qTableName( "record_sqlite" );

    if ( tst_Databases::isSqlServer( db ) || db.driverName().startsWith( "QOCI" ) )

        tablenames << qTableName( "qtest_longstr" );

    tablenames <<  qTableName( "qtest_lockedtable" );

    tablenames <<  qTableName( "Planet" );

    tablenames << qTableName( "task_250026" );

    tablenames << qTableName( "task_234422" );

    if (tst_Databases::isSqlServer( db )) {

        QSqlQuery q( db );

        q.exec("DROP PROCEDURE " + qTableName("test141895_proc"));

    }

    tablenames << qTableName("test141895");

    tst_Databases::safeDropTables( db, tablenames );

}

void tst_QSqlQuery::createTestTables( QSqlDatabase db )

{

    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::isPostgreSQL(db))

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

    if(tst_Databases::isPostgreSQL(db))

        QVERIFY_SQL( q, exec( "create table " + qTableName( "qtest" ) + " (id serial NOT NULL, t_varchar varchar(20), t_char char(20), primary key(id)) WITH OIDS" ) );

    else

        QVERIFY_SQL( q, exec( "create table " + qTableName( "qtest" ) + " (id int "+tst_Databases::autoFieldName(db) +" NOT NULL, t_varchar varchar(20), t_char char(20), primary key(id))" ) );

    if ( tst_Databases::isSqlServer( db ) || db.driverName().startsWith( "QTDS" ) )

        QVERIFY_SQL( q, exec( "create table " + qTableName( "qtest_null" ) + " (id int null, t_varchar varchar(20) null)" ) );

    else

        QVERIFY_SQL( q, exec( "create table " + qTableName( "qtest_null" ) + " (id int, t_varchar varchar(20))" ) );

}

void tst_QSqlQuery::populateTestTables( QSqlDatabase db )

{

    QSqlQuery q( db );

    q.exec( "delete from " + qTableName( "qtest" ) );

    QVERIFY_SQL( q, exec( "insert into " + qTableName( "qtest" ) + " values (1, 'VarChar1', 'Char1')" ) );

    QVERIFY_SQL( q, exec( "insert into " + qTableName( "qtest" ) + " values (2, 'VarChar2', 'Char2')" ) );

    QVERIFY_SQL( q, exec( "insert into " + qTableName( "qtest" ) + " values (3, 'VarChar3', 'Char3')" ) );

    QVERIFY_SQL( q, exec( "insert into " + qTableName( "qtest" ) + " values (4, 'VarChar4', 'Char4')" ) );

    QVERIFY_SQL( q, exec( "insert into " + qTableName( "qtest" ) + " values (5, 'VarChar5', 'Char5')" ) );

    q.exec( "delete from " + qTableName( "qtest_null" ) );

    QVERIFY_SQL( q, exec( "insert into " + qTableName( "qtest_null" ) + " values (0, NULL)" ) );

    QVERIFY_SQL( q, exec( "insert into " + qTableName( "qtest_null" ) + " values (1, 'n')" ) );

    QVERIFY_SQL( q, exec( "insert into " + qTableName( "qtest_null" ) + " values (2, 'i')" ) );

    QVERIFY_SQL( q, exec( "insert into " + qTableName( "qtest_null" ) + " values (3, NULL)" ) );

}

// There were problems with char fields of size 1

void tst_QSqlQuery::char1Select()

{

    QFETCH( QString, dbName );

    QSqlDatabase db = QSqlDatabase::database( dbName );

    CHECK_DATABASE( db );

    {

        QSqlQuery q( db );

        QVERIFY_SQL( q, exec( "create table " + qTableName( "char1Select" ) + " (id char(1))" ) );

        QVERIFY_SQL( q, exec( "insert into " + qTableName( "char1Select" ) + " values ('a')" ) );

        QVERIFY_SQL( q, exec( "select * from " + qTableName( "char1Select" ) ) );

        QVERIFY( q.next() );

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

            QCOMPARE( q.value( 0 ).toString().left( 1 ), QString( "a" ) );

        else

            QCOMPARE( q.value( 0 ).toString(), QString( "a" ) );

        QVERIFY( !q.next() );

    }

}

void tst_QSqlQuery::char1SelectUnicode()

{

    QFETCH( QString, dbName );

    QSqlDatabase db = QSqlDatabase::database( dbName );

    CHECK_DATABASE( db );

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

        QSKIP("Needs someone with more Unicode knowledge than I have to fix", SkipSingle);

    if ( db.driver()->hasFeature( QSqlDriver::Unicode ) ) {

        QString uniStr( QChar( 'का' ) );

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

        QString createQuery;

        if ( tst_Databases::isSqlServer( db ) )

            createQuery = "create table " + qTableName( "char1SelectUnicode" ) + "(id nchar(1))";

        else if ( db.driverName().startsWith( "QDB2" )

                  || db.driverName().startsWith( "QOCI" )

                  || db.driverName().startsWith( "QPSQL" ) )

            createQuery = "create table " + qTableName( "char1SelectUnicode" ) + " (id char(3))";

        else if ( db.driverName().startsWith( "QIBASE" ) )

            createQuery = "create table " + qTableName( "char1SelectUnicode" ) +

                          " (id char(1) character set unicode_fss)";

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

            createQuery = "create table " + qTableName( "char1SelectUnicode" ) + " (id char(1)) "

                          "default character set 'utf8'";

        else

            createQuery = "create table " + qTableName( "char1SelectUnicode" ) + " (id char(1))";

        QVERIFY_SQL( q, exec( createQuery ) );

        QVERIFY_SQL( q, prepare( "insert into " + qTableName( "char1SelectUnicode" ) + " values(?)" ) );

        q.bindValue( 0, uniStr );

        QVERIFY_SQL( q, exec() );

        QVERIFY_SQL( q, exec( "select * from " + qTableName( "char1SelectUnicode" ) ) );

        QVERIFY( q.next() );

        if ( !q.value( 0 ).toString().isEmpty() )

            QCOMPARE( q.value( 0 ).toString()[ 0 ].unicode(), uniStr[0].unicode() );

        QCOMPARE( q.value( 0 ).toString().trimmed(), uniStr );

        QVERIFY( !q.next() );

    }

    else

        QSKIP( "Database not unicode capable", SkipSingle );

}

void tst_QSqlQuery::oraRowId()

{

    QFETCH( QString, dbName );

    QSqlDatabase db = QSqlDatabase::database( dbName );

    CHECK_DATABASE( db );

    QSqlQuery q( db );

    QVERIFY_SQL( q, exec( "select rowid from " + qTableName( "qtest" ) ) );

    QVERIFY( q.next() );

    QCOMPARE( q.value( 0 ).type(), QVariant::String );

    QVERIFY( !q.value( 0 ).toString().isEmpty() );

    QVERIFY_SQL( q, exec( "create table " + qTableName( "oraRowId" ) + " (id char(1))" ) );

    QVERIFY_SQL( q, exec( "insert into " + qTableName( "oraRowId" ) + " values('a')" ) );

    QVariant v1 = q.lastInsertId();

    QVERIFY( v1.isValid() );

    QVERIFY_SQL( q, exec( "insert into " + qTableName( "oraRowId" ) + " values('b')" ) );

    QVariant v2 = q.lastInsertId();

    QVERIFY( v2.isValid() );

    QVERIFY_SQL( q, prepare( "select * from " + qTableName( "oraRowId" ) + " where rowid = ?" ) );

    q.addBindValue( v1 );

    QVERIFY_SQL( q, exec() );

    QVERIFY( q.next() );

    QCOMPARE( q.value( 0 ).toString(), QString( "a" ) );

    q.addBindValue( v2 );

    QVERIFY_SQL( q, exec() );

    QVERIFY( q.next() );

    QCOMPARE( q.value( 0 ).toString(), QString( "b" ) );

}

void tst_QSqlQuery::mysqlOutValues()

{

    QFETCH( QString, dbName );

    QSqlDatabase db = QSqlDatabase::database( dbName );

    CHECK_DATABASE( db );

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

    q.exec( "drop function " + qTableName( "hello" ) );

    QVERIFY_SQL( q, exec( "create function " + qTableName( "hello" ) + " (s char(20)) returns varchar(50) return concat('Hello ', s)" ) );

    QVERIFY_SQL( q, exec( "select " + qTableName( "hello" ) + "('world')" ) );

    QVERIFY_SQL( q, next() );

    QCOMPARE( q.value( 0 ).toString(), QString( "Hello world" ) );

    QVERIFY_SQL( q, prepare( "select " + qTableName( "hello" ) + "('harald')" ) );

    QVERIFY_SQL( q, exec() );

    QVERIFY_SQL( q, next() );

    QCOMPARE( q.value( 0 ).toString(), QString( "Hello harald" ) );

    QVERIFY_SQL( q, exec( "drop function " + qTableName( "hello" ) ) );

    q.exec( "drop procedure " + qTableName( "qtestproc" ) );

    QVERIFY_SQL( q, exec( "create procedure " + qTableName( "qtestproc" ) + " () "

                            "BEGIN select * from " + qTableName( "qtest" ) + " order by id; END" ) );

    QVERIFY_SQL( q, exec( "call " + qTableName( "qtestproc" ) + "()" ) );

    QVERIFY_SQL( q, next() );

    QCOMPARE( q.value( 1 ).toString(), QString( "VarChar1" ) );

    QVERIFY_SQL( q, exec( "drop procedure " + qTableName( "qtestproc" ) ) );

    QVERIFY_SQL( q, exec( "create procedure " + qTableName( "qtestproc" ) + " (OUT param1 INT) "

                            "BEGIN set param1 = 42; END" ) );

    QVERIFY_SQL( q, exec( "call " + qTableName( "qtestproc" ) + " (@out)" ) );

    QVERIFY_SQL( q, exec( "select @out" ) );

    QCOMPARE( q.record().fieldName( 0 ), QString( "@out" ) );

    QVERIFY_SQL( q, next() );

    QCOMPARE( q.value( 0 ).toInt(), 42 );

    QVERIFY_SQL( q, exec( "drop procedure " + qTableName( "qtestproc" ) ) );

}