qsqlrelationaltablemodel.cpp

Go to the documentation of this file.

/****************************************************************************
**
** Copyright (C) 2014 Digia Plc and/or its subsidiary(-ies).
** Contact: http://www.qt-project.org/legal
**
** This file is part of the QtSql module of the Qt Toolkit.
**
** $QT_BEGIN_LICENSE:LGPL$
** Commercial License Usage
** Licensees holding valid commercial Qt licenses may use this file in
** accordance with the commercial license agreement provided with the
** Software or, alternatively, in accordance with the terms contained in
** a written agreement between you and Digia.  For licensing terms and
** conditions see http://qt.digia.com/licensing.  For further information
** use the contact form at http://qt.digia.com/contact-us.
**
** 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, Digia gives you certain additional
** rights.  These rights are described in the Digia Qt LGPL Exception
** version 1.1, included in the file LGPL_EXCEPTION.txt in this package.
**
** GNU General Public License Usage
** Alternatively, this file may be used under the terms of the GNU
** General Public License version 3.0 as published by the Free Software
** Foundation and appearing in the file LICENSE.GPL included in the
** packaging of this file.  Please review the following information to
** ensure the GNU General Public License version 3.0 requirements will be
** met: http://www.gnu.org/copyleft/gpl.html.
**
**
** $QT_END_LICENSE$
**
****************************************************************************/

#include "qsqlrelationaltablemodel.h"

#include "qhash.h"
#include "qstringlist.h"
#include "qsqldatabase.h"
#include "qsqldriver.h"
#include "qsqlerror.h"
#include "qsqlfield.h"
#include "qsqlindex.h"
#include "qsqlquery.h"
#include "qsqlrecord.h"

#include "qsqltablemodel_p.h"

#include "qdebug.h"

QT_BEGIN_NAMESPACE

class QRelatedTableModel;

struct QRelation
{
    public:
        QRelation(): model(0),m_parent(0),m_dictInitialized(false){}
        void init(QSqlRelationalTableModel *parent, const QSqlRelation &relation);

        void populateModel();

        bool isDictionaryInitialized();
        void populateDictionary();
        void clearDictionary();

        void clear();
        bool isValid();

        QSqlRelation rel;
        QRelatedTableModel *model;
        QHash<QString, QVariant> dictionary;//maps keys to display values

    private:
        QSqlRelationalTableModel *m_parent;
        bool m_dictInitialized;
};

class QRelatedTableModel : public QSqlTableModel
{
public:
    QRelatedTableModel(QRelation *rel, QObject *parent = 0, QSqlDatabase db = QSqlDatabase());
    bool select();
private:
    bool firstSelect;
    QRelation *relation;
};
/*
    A QRelation must be initialized before it is considered valid.
    Note: population of the model and dictionary are kept separate
          from initialization, and are populated on an as needed basis.
*/
void QRelation::init(QSqlRelationalTableModel *parent, const QSqlRelation &relation)
{
    Q_ASSERT(parent != NULL);
    m_parent = parent;
    rel = relation;
}

void QRelation::populateModel()
{
    if (!isValid())
        return;
    Q_ASSERT(m_parent != NULL);

    if (!model) {
        model = new QRelatedTableModel(this, m_parent, m_parent->database());
        model->setTable(rel.tableName());
        model->select();
    }
}

bool QRelation::isDictionaryInitialized()
{
    return m_dictInitialized;
}

void QRelation::populateDictionary()
{
    if (!isValid())
        return;

    if (model ==  NULL)
        populateModel();

    QSqlRecord record;
    QString indexColumn;
    QString displayColumn;
    for (int i=0; i < model->rowCount(); ++i) {
        record = model->record(i);

        indexColumn = rel.indexColumn();
        if (m_parent->database().driver()->isIdentifierEscaped(indexColumn, QSqlDriver::FieldName))
            indexColumn = m_parent->database().driver()->stripDelimiters(indexColumn, QSqlDriver::FieldName);

        displayColumn = rel.displayColumn();
        if (m_parent->database().driver()->isIdentifierEscaped(displayColumn, QSqlDriver::FieldName))
            displayColumn = m_parent->database().driver()->stripDelimiters(displayColumn, QSqlDriver::FieldName);

        dictionary[record.field(indexColumn).value().toString()] =
            record.field(displayColumn).value();
    }
    m_dictInitialized = true;
}

void QRelation::clearDictionary()
{
    dictionary.clear();
    m_dictInitialized = false;
}

void QRelation::clear()
{
    delete model;
    model = 0;
    clearDictionary();
}

bool QRelation::isValid()
{
    return (rel.isValid() && m_parent != NULL);
}



QRelatedTableModel::QRelatedTableModel(QRelation *rel, QObject *parent, QSqlDatabase db) : 
    QSqlTableModel(parent, db), firstSelect(true), relation(rel)
{
}

bool QRelatedTableModel::select()
{
    if (firstSelect) {
        firstSelect = false;
        return QSqlTableModel::select();
    }
    relation->clearDictionary();
    bool res = QSqlTableModel::select();
    if (res)
        relation->populateDictionary();
    return res;
}


class QSqlRelationalTableModelPrivate: public QSqlTableModelPrivate
{
    Q_DECLARE_PUBLIC(QSqlRelationalTableModel)
public:
    QSqlRelationalTableModelPrivate()
        : QSqlTableModelPrivate(),
        joinMode( QSqlRelationalTableModel::InnerJoin )
    {}
    QString relationField(const QString &tableName, const QString &fieldName) const;

    int nameToIndex(const QString &name) const;
    mutable QVector<QRelation> relations;
    QSqlRecord baseRec; // the record without relations
    void clearChanges();
    void clearEditBuffer();
    void clearCache();
    void revertCachedRow(int row);

    void translateFieldNames(int row, QSqlRecord &values) const;
    QSqlRelationalTableModel::JoinMode joinMode;
};

static void qAppendWhereClause(QString &query, const QString &clause1, const QString &clause2)
{
    if (clause1.isEmpty() && clause2.isEmpty())
        return;
    if (clause1.isEmpty() || clause2.isEmpty())
        query.append(QLatin1String(" WHERE (")).append(clause1).append(clause2);
    else
        query.append(QLatin1String(" WHERE (")).append(clause1).append(
                        QLatin1String(") AND (")).append(clause2);
    query.append(QLatin1String(") "));
}

void QSqlRelationalTableModelPrivate::clearChanges()
{
    for (int i = 0; i < relations.count(); ++i) {
        QRelation &rel = relations[i];
        rel.clear();
    }
}

void QSqlRelationalTableModelPrivate::revertCachedRow(int row)
{
    QSqlTableModelPrivate::revertCachedRow(row);
}

int QSqlRelationalTableModelPrivate::nameToIndex(const QString &name) const
{
    QString fieldname = name;
    if (db.driver()->isIdentifierEscaped(fieldname, QSqlDriver::FieldName))
        fieldname = db.driver()->stripDelimiters(fieldname, QSqlDriver::FieldName);
    return baseRec.indexOf(fieldname);
}

void QSqlRelationalTableModelPrivate::clearEditBuffer()
{
    editBuffer = baseRec;
    clearGenerated(editBuffer);
}

void QSqlRelationalTableModelPrivate::clearCache()
{
    for (int i = 0; i < relations.count(); ++i)
        relations[i].clearDictionary();

    QSqlTableModelPrivate::clearCache();
}

QSqlRelationalTableModel::QSqlRelationalTableModel(QObject *parent, QSqlDatabase db)
    : QSqlTableModel(*new QSqlRelationalTableModelPrivate, parent, db)
{
}

QSqlRelationalTableModel::~QSqlRelationalTableModel()
{
}

QVariant QSqlRelationalTableModel::data(const QModelIndex &index, int role) const
{
    Q_D(const QSqlRelationalTableModel);

    if (role == Qt::DisplayRole && index.column() >= 0 && index.column() < d->relations.count() &&
            d->relations.value(index.column()).isValid()) {
        QRelation &relation = d->relations[index.column()];
        if (!relation.isDictionaryInitialized())
            relation.populateDictionary();

        //only perform a dictionary lookup for the display value
        //when the value at index has been changed or added.
        //At an unmodified index, the underlying model will
        //already have the correct display value.
        QVariant v;
        switch (d->strategy) {
            case OnFieldChange:
                break;
            case OnRowChange:
                if ((index.row() == d->editIndex || index.row() == d->insertIndex)
                    && d->editBuffer.isGenerated(index.column()))
                    v = d->editBuffer.value(index.column());
                break;
            case OnManualSubmit:
                const QSqlTableModelPrivate::ModifiedRow row = d->cache.value(index.row());
                if (row.op != QSqlTableModelPrivate::None && row.rec.isGenerated(index.column()))
                    v = row.rec.value(index.column());
                break;
        }
        if (v.isValid())
            return relation.dictionary[v.toString()];
    }
    return QSqlTableModel::data(index, role);
}

bool QSqlRelationalTableModel::setData(const QModelIndex &index, const QVariant &value,
                                       int role)
{
    Q_D(QSqlRelationalTableModel);
    if ( role == Qt::EditRole && index.column() > 0 && index.column() < d->relations.count()
            && d->relations.value(index.column()).isValid()) {
        QRelation &relation = d->relations[index.column()];
        if (!relation.isDictionaryInitialized())
            relation.populateDictionary();
        if (!relation.dictionary.contains(value.toString()))
            return false;
    }
    return QSqlTableModel::setData(index, value, role);
}

void QSqlRelationalTableModel::setRelation(int column, const QSqlRelation &relation)
{
    Q_D(QSqlRelationalTableModel);
    if (column < 0)
        return;
    if (d->relations.size() <= column)
        d->relations.resize(column + 1);
    d->relations[column].init(this, relation);
}

QSqlRelation QSqlRelationalTableModel::relation(int column) const
{
    Q_D(const QSqlRelationalTableModel);
    return d->relations.value(column).rel;
}

QString QSqlRelationalTableModelPrivate::relationField(const QString &tableName,
                        const QString &fieldName) const
{
    QString ret;
    ret.reserve(tableName.size() + fieldName.size() + 1);
    ret.append(tableName).append(QLatin1Char('.')).append(fieldName);

    return ret;
}

QString QSqlRelationalTableModel::selectStatement() const
{
    Q_D(const QSqlRelationalTableModel);
    QString query;

    if (tableName().isEmpty())
        return query;
    if (d->relations.isEmpty())
        return QSqlTableModel::selectStatement();

    QString tList;
    QString fList;
    QString where;

    QSqlRecord rec = d->baseRec;
    QStringList tables;
    const QRelation nullRelation;

    // Count how many times each field name occurs in the record
    QHash<QString, int> fieldNames;
    QStringList fieldList;
    for (int i = 0; i < rec.count(); ++i) {
        QSqlRelation relation = d->relations.value(i, nullRelation).rel;
        QString name;
        if (relation.isValid())
        {
            // Count the display column name, not the original foreign key
            name = relation.displayColumn();
            if (d->db.driver()->isIdentifierEscaped(name, QSqlDriver::FieldName))
                name = d->db.driver()->stripDelimiters(name, QSqlDriver::FieldName);

            QSqlRecord rec = database().record(relation.tableName());
            for (int i = 0; i < rec.count(); ++i) {
                if (name.compare(rec.fieldName(i), Qt::CaseInsensitive) == 0) {
                    name = rec.fieldName(i);
                    break;
                }
            }
        }
        else
            name = rec.fieldName(i);
        fieldNames.insert(name, fieldNames.value(name, 0) + 1);
        fieldList.append(name);
    }

    for (int i = 0; i < rec.count(); ++i) {
        QSqlRelation relation = d->relations.value(i, nullRelation).rel;
        if (relation.isValid()) {
            QString relTableAlias = QString::fromLatin1("relTblAl_%1").arg(i);
            if (!fList.isEmpty())
                fList.append(QLatin1String(", "));
            fList.append(d->relationField(relTableAlias,relation.displayColumn()));

            // If there are duplicate field names they must be aliased
            if (fieldNames.value(fieldList[i]) > 1) {
                QString relTableName = relation.tableName().section(QChar::fromLatin1('.'), -1, -1);
                if (d->db.driver()->isIdentifierEscaped(relTableName, QSqlDriver::TableName))
                    relTableName = d->db.driver()->stripDelimiters(relTableName, QSqlDriver::TableName);
                QString displayColumn = relation.displayColumn();
                if (d->db.driver()->isIdentifierEscaped(displayColumn, QSqlDriver::FieldName))
                    displayColumn = d->db.driver()->stripDelimiters(displayColumn, QSqlDriver::FieldName);
                fList.append(QString::fromLatin1(" AS %1_%2_%3").arg(relTableName).arg(displayColumn).arg(fieldNames.value(fieldList[i])));
                fieldNames.insert(fieldList[i], fieldNames.value(fieldList[i])-1);
            }

            if (d->joinMode == QSqlRelationalTableModel::InnerJoin) {
                // this needs fixing!! the below if is borken.
                // Use LeftJoin mode if you want correct behavior
                tables.append(relation.tableName().append(QLatin1Char(' ')).append(relTableAlias));
                if(!where.isEmpty())
                    where.append(QLatin1String(" AND "));
                where.append(d->relationField(tableName(), d->db.driver()->escapeIdentifier(rec.fieldName(i), QSqlDriver::FieldName)));
                where.append(QLatin1String(" = "));
                where.append(d->relationField(relTableAlias, relation.indexColumn()));
            } else {
                tables.append(QLatin1String(" LEFT JOIN"));
                tables.append(relation.tableName().append(QLatin1Char(' ')).append(relTableAlias));
                tables.append(QLatin1String("ON"));

                QString clause;
                clause.append(d->relationField(tableName(), d->db.driver()->escapeIdentifier(rec.fieldName(i), QSqlDriver::FieldName)));
                clause.append(QLatin1String(" = "));
                clause.append(d->relationField(relTableAlias, relation.indexColumn()));

                tables.append(clause);
            }
        } else {
            if (!fList.isEmpty())
                fList.append(QLatin1String(", "));
            fList.append(d->relationField(tableName(), d->db.driver()->escapeIdentifier(rec.fieldName(i), QSqlDriver::FieldName)));
        }
    }

    if (d->joinMode == QSqlRelationalTableModel::InnerJoin && !tables.isEmpty()) {
        tList.append(tables.join(QLatin1String(", ")));
        if(!tList.isEmpty())
            tList.prepend(QLatin1String(", "));
    } else
        tList.append(tables.join(QLatin1String(" ")));

    if (fList.isEmpty())
        return query;

    tList.prepend(tableName());
    query.append(QLatin1String("SELECT "));
    query.append(fList).append(QLatin1String(" FROM ")).append(tList);

    if (d->joinMode == QSqlRelationalTableModel::InnerJoin) {
        qAppendWhereClause(query, where, filter());
    } else if (!filter().isEmpty()) {
        query.append(QLatin1String(" WHERE ("));
        query.append(filter());
        query.append(QLatin1String(")"));
    }

    QString orderBy = orderByClause();
    if (!orderBy.isEmpty())
        query.append(QLatin1Char(' ')).append(orderBy);

    return query;
}

QSqlTableModel *QSqlRelationalTableModel::relationModel(int column) const
{
    Q_D(const QSqlRelationalTableModel);
    if ( column < 0 || column >= d->relations.count())
        return 0;

    QRelation &relation = const_cast<QSqlRelationalTableModelPrivate *>(d)->relations[column];
    if (!relation.isValid())
        return 0;

    if (!relation.model)
        relation.populateModel();
    return relation.model;
}

void QSqlRelationalTableModel::revertRow(int row)
{
    QSqlTableModel::revertRow(row);
}

void QSqlRelationalTableModel::clear()
{
    Q_D(QSqlRelationalTableModel);
    d->clearChanges();
    d->relations.clear();
    QSqlTableModel::clear();
}


void QSqlRelationalTableModel::setJoinMode(QSqlRelationalTableModel::JoinMode joinMode)
{
    Q_D(QSqlRelationalTableModel);
    d->joinMode = joinMode;
}
bool QSqlRelationalTableModel::select()
{
    return QSqlTableModel::select();
}

void QSqlRelationalTableModel::setTable(const QString &table)
{
    Q_D(QSqlRelationalTableModel);

    // memorize the table before applying the relations
    d->baseRec = d->db.record(table);

    QSqlTableModel::setTable(table);
}

void QSqlRelationalTableModelPrivate::translateFieldNames(int row, QSqlRecord &values) const
{
    Q_Q(const QSqlRelationalTableModel);

    for (int i = 0; i < values.count(); ++i) {
        int realCol = q->indexInQuery(q->createIndex(row, i)).column();
        if (realCol != -1 && relations.value(realCol).isValid()) {
            QVariant v = values.value(i);
            bool gen = values.isGenerated(i);
            values.replace(i, baseRec.field(realCol));
            values.setValue(i, v);
            values.setGenerated(i, gen);
        }
    }
}

bool QSqlRelationalTableModel::updateRowInTable(int row, const QSqlRecord &values)
{
    Q_D(QSqlRelationalTableModel);

    QSqlRecord rec = values;
    d->translateFieldNames(row, rec);

    return QSqlTableModel::updateRowInTable(row, rec);
}

bool QSqlRelationalTableModel::insertRowIntoTable(const QSqlRecord &values)
{
    Q_D(QSqlRelationalTableModel);

    QSqlRecord rec = values;
    d->translateFieldNames(0, rec);

    return QSqlTableModel::insertRowIntoTable(rec);
}

QString QSqlRelationalTableModel::orderByClause() const
{
    Q_D(const QSqlRelationalTableModel);

    const QSqlRelation rel = d->relations.value(d->sortColumn).rel;
    if (!rel.isValid())
        return QSqlTableModel::orderByClause();

    QString s = QLatin1String("ORDER BY ");
    s.append(d->relationField(QLatin1String("relTblAl_") + QString::number(d->sortColumn),
                    rel.displayColumn()));
    s += d->sortOrder == Qt::AscendingOrder ? QLatin1String(" ASC") : QLatin1String(" DESC");
    return s;
}

bool QSqlRelationalTableModel::removeColumns(int column, int count, const QModelIndex &parent)
{
    Q_D(QSqlRelationalTableModel);

    if (parent.isValid() || column < 0 || column + count > d->rec.count())
        return false;

    for (int i = 0; i < count; ++i) {
        d->baseRec.remove(column);
        if (d->relations.count() > column)
            d->relations.remove(column);
    }
    return QSqlTableModel::removeColumns(column, count, parent);
}

QT_END_NAMESPACE