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

**

** 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_CLASS=

//TESTED_FILES=

class tst_QSqlRelationalTableModel : public QObject

{

    Q_OBJECT

public:

    void recreateTestTables(QSqlDatabase);

    tst_Databases dbs;

public slots:

    void initTestCase_data();

    void initTestCase();

    void cleanupTestCase();

    void init();

    void cleanup();

private slots:

    void data();

    void setData();

    void multipleRelation();

    void insertRecord();

    void setRecord();

    void insertWithStrategies();

    void removeColumn();

    void filter();

    void sort();

    void revert();

    void clearDisplayValuesCache();

    void insertRecordDuplicateFieldNames();

    void invalidData();

    void relationModel();

    void casing();

    void escapedRelations();

    void escapedTableName();

    void whiteSpaceInIdentifiers();

private:

    void dropTestTables( QSqlDatabase db );

};

void tst_QSqlRelationalTableModel::initTestCase_data()

{

    dbs.open();

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

        qWarning("NO DATABASES");

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

    }

}

void tst_QSqlRelationalTableModel::recreateTestTables(QSqlDatabase db)

{

    dropTestTables(db);

    QSqlQuery q(db);

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

            " (id int not null primary key, name varchar(20), title_key int, another_title_key int)"));

    QVERIFY_SQL( q, exec("insert into " + qTableName("reltest1") + " values(1, 'harry', 1, 2)"));

    QVERIFY_SQL( q, exec("insert into " + qTableName("reltest1") + " values(2, 'trond', 2, 1)"));

    QVERIFY_SQL( q, exec("insert into " + qTableName("reltest1") + " values(3, 'vohi', 1, 2)"));

    QVERIFY_SQL( q, exec("insert into " + qTableName("reltest1") + " values(4, 'boris', 2, 2)"));

    QVERIFY_SQL( q, exec("create table " + qTableName("reltest2") + " (tid int not null primary key, title varchar(20))"));

    QVERIFY_SQL( q, exec("insert into " + qTableName("reltest2") + " values(1, 'herr')"));

    QVERIFY_SQL( q, exec("insert into " + qTableName("reltest2") + " values(2, 'mister')"));

    QVERIFY_SQL( q, exec("create table " + qTableName("reltest3") + " (id int not null primary key, name varchar(20), city_key int)"));

    QVERIFY_SQL( q, exec("insert into " + qTableName("reltest3") + " values(1, 'Gustav', 1)"));

    QVERIFY_SQL( q, exec("insert into " + qTableName("reltest3") + " values(2, 'Heidi', 2)"));

    QVERIFY_SQL( q, exec("create table " + qTableName("reltest4") + " (id int not null primary key, name varchar(20))"));

    QVERIFY_SQL( q, exec("insert into " + qTableName("reltest4") + " values(1, 'Oslo')"));

    QVERIFY_SQL( q, exec("insert into " + qTableName("reltest4") + " values(2, 'Trondheim')"));

    QVERIFY_SQL( q, exec("create table " + qTableName("reltest5") + " (title varchar(20) not null primary key, abbrev varchar(20))"));

    QVERIFY_SQL( q, exec("insert into " + qTableName("reltest5") + " values('herr', 'Hr')"));

    QVERIFY_SQL( q, exec("insert into " + qTableName("reltest5") + " values('mister', 'Mr')"));

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

        QString reltest6 = db.driver()->escapeIdentifier(qTableName("rel test6"), QSqlDriver::TableName);

        QVERIFY_SQL( q, exec("create table " + reltest6 + " (id int not null primary key, " + db.driver()->escapeIdentifier("city key", QSqlDriver::FieldName) +

                    " int, " + db.driver()->escapeIdentifier("extra field", QSqlDriver::FieldName) + " int)"));

        QVERIFY_SQL( q, exec("insert into " + reltest6 + " values(1, 1,9)"));

        QVERIFY_SQL( q, exec("insert into " + reltest6 + " values(2, 2,8)"));

        QString reltest7 = db.driver()->escapeIdentifier(qTableName("rel test7"), QSqlDriver::TableName);

        QVERIFY_SQL( q, exec("create table " + reltest7 + " (" + db.driver()->escapeIdentifier("city id", QSqlDriver::TableName) + " int not null primary key, " + db.driver()->escapeIdentifier("city name", QSqlDriver::FieldName) + " varchar(20))"));

        QVERIFY_SQL( q, exec("insert into " + reltest7 + " values(1, 'New York')"));

        QVERIFY_SQL( q, exec("insert into " + reltest7 + " values(2, 'Washington')"));

    }

}

void tst_QSqlRelationalTableModel::initTestCase()

{

    foreach (const QString &dbname, dbs.dbNames) {

        QSqlDatabase db=QSqlDatabase::database(dbname);

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

            db.exec("SET DIALECT 3");

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

        }

        recreateTestTables(db);

    }

}

void tst_QSqlRelationalTableModel::cleanupTestCase()

{

    foreach (const QString &dbName, dbs.dbNames) {

        QSqlDatabase db = QSqlDatabase::database(dbName);

        CHECK_DATABASE( db );

        dropTestTables( QSqlDatabase::database(dbName) );

    }

    dbs.close();

}

void tst_QSqlRelationalTableModel::dropTestTables( QSqlDatabase db )

{

    QStringList tableNames;

    tableNames << qTableName( "reltest1" )

            << qTableName( "reltest2" )

            << qTableName( "reltest3" )

            << qTableName( "reltest4" )

            << qTableName( "reltest5" )

            << qTableName( "rel test6" )

            << qTableName( "rel test7" )

            << qTableName("CASETEST1" )

            << qTableName("casetest1" );

    tst_Databases::safeDropTables( db, tableNames );

}

void tst_QSqlRelationalTableModel::init()

{

}

void tst_QSqlRelationalTableModel::cleanup()

{

}

void tst_QSqlRelationalTableModel::data()

{

    QFETCH_GLOBAL(QString, dbName);

    QSqlDatabase db = QSqlDatabase::database(dbName);

    CHECK_DATABASE(db);

    QSqlRelationalTableModel model(0, db);

    model.setTable(qTableName("reltest1"));

    model.setRelation(2, QSqlRelation(qTableName("reltest2"), "tid", "title"));

    QVERIFY_SQL(model, select());

    QCOMPARE(model.columnCount(), 4);

    QCOMPARE(model.data(model.index(0, 0)).toInt(), 1);

    QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry"));

    QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));

    //try a non-existant index

    QVERIFY2(model.data(model.index(0,4)).isValid() == false,"Invalid index returned valid QVariant");

    //check data retrieval when relational key is a non-integer type

    //in this case a string

    QSqlRelationalTableModel model2(0,db);

    model2.setTable(qTableName("reltest2"));

    model2.setRelation(1, QSqlRelation(qTableName("reltest5"),"title","abbrev"));

    QVERIFY_SQL(model2, select());

    QCOMPARE(model2.data(model2.index(0, 1)).toString(), QString("Hr"));

    QCOMPARE(model2.data(model2.index(1, 1)).toString(), QString("Mr"));

}

void tst_QSqlRelationalTableModel::setData()

{

    QFETCH_GLOBAL(QString, dbName);

    QSqlDatabase db = QSqlDatabase::database(dbName);

    CHECK_DATABASE(db);

    // set the values using OnRowChange Strategy

    {

        QSqlRelationalTableModel model(0, db);

        model.setTable(qTableName("reltest1"));

        model.setSort(0, Qt::AscendingOrder);

        model.setRelation(2, QSqlRelation(qTableName("reltest2"), "tid", "title"));

        QVERIFY_SQL(model, select());

        QVERIFY(model.setData(model.index(0, 1), QString("harry2")));

        QVERIFY(model.setData(model.index(0, 2), 2));

        QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry2"));

        QCOMPARE(model.data(model.index(0, 2)).toString(), QString("mister"));

        model.submit();

        QVERIFY(model.setData(model.index(3,1), QString("boris2")));

        QVERIFY(model.setData(model.index(3, 2), 1));

        QCOMPARE(model.data(model.index(3,1)).toString(), QString("boris2"));

        QCOMPARE(model.data(model.index(3, 2)).toString(), QString("herr"));

        model.submit();

    }

    { //verify values

        QSqlRelationalTableModel model(0, db);

        model.setTable(qTableName("reltest1"));

        model.setSort(0, Qt::AscendingOrder);

        QVERIFY_SQL(model, select());

        QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry2"));

        QCOMPARE(model.data(model.index(0, 2)).toInt(), 2);

        QCOMPARE(model.data(model.index(3, 1)).toString(), QString("boris2"));

        QCOMPARE(model.data(model.index(3, 2)).toInt(), 1);

        model.setRelation(2, QSqlRelation(qTableName("reltest2"), "tid", "title"));

        QVERIFY_SQL(model, select());

        QCOMPARE(model.data(model.index(0, 2)).toString(), QString("mister"));

        QCOMPARE(model.data(model.index(3,2)).toString(), QString("herr"));

    }

    //set the values using OnFieldChange strategy

    {

        QSqlRelationalTableModel model(0, db);

        model.setTable(qTableName("reltest1"));

        model.setEditStrategy(QSqlTableModel::OnFieldChange);

        model.setSort(0, Qt::AscendingOrder);

        model.setRelation(2, QSqlRelation(qTableName("reltest2"), "tid", "title"));

        QVERIFY_SQL(model, select());

        QVERIFY(model.setData(model.index(1,1), QString("trond2")));

        QVERIFY(model.setData(model.index(2,2), 2));

        QCOMPARE(model.data(model.index(1,1)).toString(), QString("trond2"));

        QCOMPARE(model.data(model.index(2,2)).toString(), QString("mister"));

    }

    { //verify values

        QSqlRelationalTableModel model(0, db);

        model.setTable(qTableName("reltest1"));

        model.setSort(0, Qt::AscendingOrder);

        QVERIFY_SQL(model, select());

        QCOMPARE(model.data(model.index(1, 1)).toString(), QString("trond2"));

        QCOMPARE(model.data(model.index(2, 2)).toInt(), 2);

        model.setRelation(2, QSqlRelation(qTableName("reltest2"), "tid", "title"));

        QVERIFY_SQL(model, select());

        QCOMPARE(model.data(model.index(2, 2)).toString(), QString("mister"));

    }

    //set values using OnManualSubmit strategy

    {

        QSqlRelationalTableModel model(0, db);

        model.setTable(qTableName("reltest1"));

        model.setRelation(2, QSqlRelation(qTableName("reltest2"), "tid", "title"));

        //sybase doesn't allow tables with the same alias used twice as col names

        //so don't set up an identical relation when using the tds driver

        if (!db.driverName().startsWith("QTDS"))

            model.setRelation(3, QSqlRelation(qTableName("reltest2"), "tid", "title"));

        model.setEditStrategy(QSqlTableModel::OnManualSubmit);

        model.setSort(0, Qt::AscendingOrder);

        QVERIFY_SQL(model, select());

        QVERIFY(model.setData(model.index(2, 1), QString("vohi2")));

        QVERIFY(model.setData(model.index(3, 2), 1));

        QVERIFY(model.setData(model.index(0, 3), 1));

        QCOMPARE(model.data(model.index(2, 1)).toString(), QString("vohi2"));

        QCOMPARE(model.data(model.index(3, 2)).toString(), QString("herr"));

        if (!db.driverName().startsWith("QTDS"))

            QCOMPARE(model.data(model.index(0, 3)).toString(), QString("herr"));

        else

            QCOMPARE(model.data(model.index(0, 3)).toInt(), 1);

        QVERIFY_SQL(model, submitAll());

    }

    { //verify values

        QSqlRelationalTableModel model(0, db);

        model.setTable(qTableName("reltest1"));

        model.setSort(0, Qt::AscendingOrder);

        QVERIFY_SQL(model, select());

        QCOMPARE(model.data(model.index(2, 1)).toString(), QString("vohi2"));

        QCOMPARE(model.data(model.index(3, 2)).toInt(), 1);

        QCOMPARE(model.data(model.index(0, 3)).toInt(), 1);

        model.setRelation(2, QSqlRelation(qTableName("reltest2"), "tid", "title"));

        if (!db.driverName().startsWith("QTDS"))

            model.setRelation(3, QSqlRelation(qTableName("reltest2"), "tid", "title"));

        QVERIFY_SQL(model, select());

        QCOMPARE(model.data(model.index(3, 2)).toString(), QString("herr"));

        if (!db.driverName().startsWith("QTDS"))

            QCOMPARE(model.data(model.index(0, 3)).toString(), QString("herr"));

        else

            QCOMPARE(model.data(model.index(0, 3)).toInt(), 1);

    }

    //check setting of data when the relational key is a non-integer type

    //in this case a string.

    {

        QSqlRelationalTableModel model(0, db);

        model.setTable(qTableName("reltest2"));

        model.setRelation(1, QSqlRelation(qTableName("reltest5"), "title", "abbrev"));

        model.setEditStrategy(QSqlTableModel::OnManualSubmit);

        QVERIFY_SQL(model, select());

        QCOMPARE(model.data(model.index(0,1)).toString(), QString("Hr"));

        QVERIFY(model.setData(model.index(0,1), QString("mister")));

        QCOMPARE(model.data(model.index(0,1)).toString(), QString("Mr"));

        QVERIFY_SQL(model, submitAll());

        QCOMPARE(model.data(model.index(0,1)).toString(), QString("Mr"));

    }

}

void tst_QSqlRelationalTableModel::multipleRelation()

{

    QFETCH_GLOBAL(QString, dbName);

    QSqlDatabase db = QSqlDatabase::database(dbName);

    CHECK_DATABASE(db);

    recreateTestTables(db);

    QSqlRelationalTableModel model(0, db);

    model.setTable(qTableName("reltest1"));

    model.setRelation(2, QSqlRelation(qTableName("reltest2"), "tid", "title"));

    model.setRelation(3, QSqlRelation(qTableName("reltest4"), "id", "name"));

    model.setSort(0, Qt::AscendingOrder);

    QVERIFY_SQL(model, select());

    QCOMPARE(model.data(model.index(2, 0)).toInt(), 3);

    QCOMPARE(model.data(model.index(0, 0)).toInt(), 1);

    QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry"));

    QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));

    QCOMPARE(model.data(model.index(0, 3)).toString(), QString("Trondheim"));

}

void tst_QSqlRelationalTableModel::insertRecord()

{

    QFETCH_GLOBAL(QString, dbName);

    QSqlDatabase db = QSqlDatabase::database(dbName);

    CHECK_DATABASE(db);

    QSqlRelationalTableModel model(0, db);

    model.setTable(qTableName("reltest1"));

    model.setRelation(2, QSqlRelation(qTableName("reltest2"), "tid", "title"));

    model.setSort(0, Qt::AscendingOrder);

    QVERIFY_SQL(model, select());

    QSqlRecord rec;

    QSqlField f1("id", QVariant::Int);

    QSqlField f2("name", QVariant::String);

    QSqlField f3("title_key", QVariant::Int);

    QSqlField f4("another_title_key", QVariant::Int);

    f1.setValue(5);

    f2.setValue("test");

    f3.setValue(1);

    f4.setValue(2);

    f1.setGenerated(true);

    f2.setGenerated(true);

    f3.setGenerated(true);

    f4.setGenerated(true);

    rec.append(f1);

    rec.append(f2);

    rec.append(f3);

    rec.append(f4);

    QVERIFY_SQL(model, insertRecord(-1, rec));

    QCOMPARE(model.data(model.index(4, 0)).toInt(), 5);

    QCOMPARE(model.data(model.index(4, 1)).toString(), QString("test"));

    QCOMPARE(model.data(model.index(4, 2)).toString(), QString("herr"));

}

void tst_QSqlRelationalTableModel::setRecord()

{

    QFETCH_GLOBAL(QString, dbName);

    QSqlDatabase db = QSqlDatabase::database(dbName);

    CHECK_DATABASE(db);

    recreateTestTables(db);

    QSqlRelationalTableModel model(0, db);

    model.setTable(qTableName("reltest1"));

    model.setRelation(2, QSqlRelation(qTableName("reltest2"), "tid", "title"));

    model.setSort(0, Qt::AscendingOrder);

    QVERIFY_SQL(model, select());

    QSqlRecord rec;

    QSqlField f1("id", QVariant::Int);

    QSqlField f2("name", QVariant::String);

    QSqlField f3("title_key", QVariant::Int);

    QSqlField f4("another_title_key", QVariant::Int);

    f1.setValue(5);

    f2.setValue("tester");

    f3.setValue(1);

    f4.setValue(2);

    f1.setGenerated(true);

    f2.setGenerated(true);

    f3.setGenerated(true);

    f4.setGenerated(true);

    rec.append(f1);

    rec.append(f2);

    rec.append(f3);

    rec.append(f4);

    QCOMPARE(model.data(model.index(1, 0)).toInt(), 2);

    QCOMPARE(model.data(model.index(1, 1)).toString(), QString("trond"));

    QCOMPARE(model.data(model.index(1, 2)).toString(), QString("mister"));

    QVERIFY_SQL(model, setRecord(1, rec));

    QCOMPARE(model.data(model.index(1, 0)).toInt(), 5);

    QCOMPARE(model.data(model.index(1, 1)).toString(), QString("tester"));

    QCOMPARE(model.data(model.index(1, 2)).toString(), QString("herr"));

    model.setSort(0, Qt::AscendingOrder);

    QVERIFY_SQL(model, submit());

    QCOMPARE(model.data(model.index(3, 0)).toInt(), 5);

    QCOMPARE(model.data(model.index(3, 1)).toString(), QString("tester"));

    QCOMPARE(model.data(model.index(3, 2)).toString(), QString("herr"));

}

void tst_QSqlRelationalTableModel::insertWithStrategies()

{

    QFETCH_GLOBAL(QString, dbName);

    QSqlDatabase db = QSqlDatabase::database(dbName);

    CHECK_DATABASE(db);

    QSqlRelationalTableModel model(0, db);

    model.setTable(qTableName("reltest1"));

    model.setRelation(2, QSqlRelation(qTableName("reltest2"), "tid", "title"));

    model.setSort(0, Qt::AscendingOrder);

    if (!db.driverName().startsWith("QTDS"))

        model.setRelation(3, QSqlRelation(qTableName("reltest2"), "tid", "title"));

    QVERIFY_SQL(model, select());

    QCOMPARE(model.data(model.index(0,0)).toInt(), 1);

    QCOMPARE(model.data(model.index(0,1)).toString(), QString("harry"));

    QCOMPARE(model.data(model.index(0,2)).toString(), QString("herr"));

    if (!db.driverName().startsWith("QTDS"))

        QCOMPARE(model.data(model.index(0,3)).toString(), QString("mister"));

    else

        QCOMPARE(model.data(model.index(0,3)).toInt(), 2);

    model.insertRows(0, 1);

    model.setData(model.index(0, 0), 1011);

    model.setData(model.index(0, 1), "test");

    model.setData(model.index(0, 2), 2);

    model.setData(model.index(0, 3), 1);

    QCOMPARE(model.data(model.index(0,0)).toInt(), 1011);