qsqlrelationaltablemodel.cpp

Go to the documentation of this file.

// Copyright (C) 2016 The Qt Company Ltd.
// SPDX-License-Identifier: LicenseRef-Qt-Commercial OR LGPL-3.0-only OR GPL-2.0-only OR GPL-3.0-only
 
#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
 
using namespace Qt::StringLiterals;
 
class QSqlRelationalTableModelSql: public QSqlTableModelSql
{
public:
    inline const static QString relTablePrefix(int i) { return QString::number(i).prepend("relTblAl_"_L1); }
};
 
using SqlrTm = QSqlRelationalTableModelSql;
 
class QRelatedTableModel;
 
struct QRelation
{
    public:
        QRelation(): model(nullptr), m_parent(nullptr), 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 = nullptr, const QSqlDatabase &db = QSqlDatabase());
    bool select() override;
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 != nullptr);
    m_parent = parent;
    rel = relation;
}
 
void QRelation::populateModel()
{
    if (!isValid())
        return;
    Q_ASSERT(m_parent != nullptr);
 
    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 ==  nullptr)
        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 = nullptr;
    clearDictionary();
}
 
bool QRelation::isValid()
{
    return (rel.isValid() && m_parent != nullptr);
}
 
 
 
QRelatedTableModel::QRelatedTableModel(QRelation *rel, QObject *parent, const 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 fullyQualifiedFieldName(const QString &tableName, const QString &fieldName) const;
 
    int nameToIndex(const QString &name) const override;
    mutable QList<QRelation> relations;
    QSqlRecord baseRec; // the record without relations
    void clearChanges();
    void clearCache() override;
    void revertCachedRow(int row) override;
 
    void translateFieldNames(QSqlRecord &values) const;
    QSqlRelationalTableModel::JoinMode joinMode;
};
 
void QSqlRelationalTableModelPrivate::clearChanges()
{
    for (int i = 0; i < relations.size(); ++i) {
        QRelation &rel = relations[i];
        rel.clear();
    }
}
 
void QSqlRelationalTableModelPrivate::revertCachedRow(int row)
{
    QSqlTableModelPrivate::revertCachedRow(row);
}
 
int QSqlRelationalTableModelPrivate::nameToIndex(const QString &name) const
{
    const QString fieldname = strippedFieldName(name);
    int idx = baseRec.indexOf(fieldname);
    if (idx == -1) {
        // If the name is an alias we can find it here.
        idx = QSqlTableModelPrivate::nameToIndex(name);
    }
    return idx;
}
 
void QSqlRelationalTableModelPrivate::clearCache()
{
    for (int i = 0; i < relations.size(); ++i)
        relations[i].clearDictionary();
 
    QSqlTableModelPrivate::clearCache();
}
 
QSqlRelationalTableModel::QSqlRelationalTableModel(QObject *parent, const 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.size() &&
            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.
        if (d->strategy != OnFieldChange) {
            const QSqlTableModelPrivate::ModifiedRow row = d->cache.value(index.row());
            if (row.op() != QSqlTableModelPrivate::None && row.rec().isGenerated(index.column())) {
                if (d->strategy == OnManualSubmit || row.op() != QSqlTableModelPrivate::Delete) {
                    QVariant v = row.rec().value(index.column());
                    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.size()
            && 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::fullyQualifiedFieldName(const QString &tableName,
                                                                 const QString &fieldName) const
{
    QString ret;
    ret.reserve(tableName.size() + fieldName.size() + 1);
    ret.append(tableName).append(u'.').append(fieldName);
 
    return ret;
}
 
QString QSqlRelationalTableModel::selectStatement() const
{
    Q_D(const QSqlRelationalTableModel);
 
    if (tableName().isEmpty())
        return QString();
    if (d->relations.isEmpty())
        return QSqlTableModel::selectStatement();
 
    // Count how many times each field name occurs in the record
    QHash<QString, int> fieldNames;
    QStringList fieldList;
    for (int i = 0; i < d->baseRec.count(); ++i) {
        QSqlRelation relation = d->relations.value(i).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);
 
            const 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 = d->baseRec.fieldName(i);
        }
        fieldNames[name] = fieldNames.value(name, 0) + 1;
        fieldList.append(name);
    }
 
    QString fList;
    QString conditions;
    QString from = SqlrTm::from(tableName());
    for (int i = 0; i < d->baseRec.count(); ++i) {
        QSqlRelation relation = d->relations.value(i).rel;
        const QString tableField = d->fullyQualifiedFieldName(tableName(), d->db.driver()->escapeIdentifier(d->baseRec.fieldName(i), QSqlDriver::FieldName));
        if (relation.isValid()) {
            const QString relTableAlias = SqlrTm::relTablePrefix(i);
            QString displayTableField = d->fullyQualifiedFieldName(relTableAlias, relation.displayColumn());
 
            // Duplicate field names 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);
                QString alias = QString::fromLatin1("%1_%2_%3")
                                      .arg(relTableName, displayColumn, QString::number(fieldNames.value(fieldList[i])));
                alias.truncate(d->db.driver()->maximumIdentifierLength(QSqlDriver::FieldName));
                alias = d->db.driver()->escapeIdentifier(alias, QSqlDriver::FieldName);
                displayTableField = SqlrTm::as(displayTableField, alias);
                --fieldNames[fieldList[i]];
            }
 
            fList = SqlrTm::comma(fList, displayTableField);
 
            // Join related table
            const QString tblexpr = SqlrTm::concat(relation.tableName(), relTableAlias);
            const QString relTableField = d->fullyQualifiedFieldName(relTableAlias, relation.indexColumn());
            const QString cond = SqlrTm::eq(tableField, relTableField);
            if (d->joinMode == QSqlRelationalTableModel::InnerJoin) {
                // FIXME: InnerJoin code is known to be broken.
                // Use LeftJoin mode if you want correct behavior.
                from = SqlrTm::comma(from, tblexpr);
                conditions = SqlrTm::et(conditions, cond);
            } else {
                from = SqlrTm::concat(from, SqlrTm::leftJoin(tblexpr));
                from = SqlrTm::concat(from, SqlrTm::on(cond));
            }
        } else {
            fList = SqlrTm::comma(fList, tableField);
        }
    }
 
    if (fList.isEmpty())
        return QString();
 
    const QString stmt = SqlrTm::concat(SqlrTm::select(fList), from);
    const QString where = SqlrTm::where(SqlrTm::et(SqlrTm::paren(conditions), SqlrTm::paren(filter())));
    return SqlrTm::concat(SqlrTm::concat(stmt, where), orderByClause());
}
 
QSqlTableModel *QSqlRelationalTableModel::relationModel(int column) const
{
    Q_D(const QSqlRelationalTableModel);
    if (column < 0 || column >= d->relations.size())
        return nullptr;
 
    QRelation &relation = const_cast<QSqlRelationalTableModelPrivate *>(d)->relations[column];
    if (!relation.isValid())
        return nullptr;
 
    if (!relation.model)
        relation.populateModel();
    return relation.model;
}
 
void QSqlRelationalTableModel::revertRow(int row)
{
    QSqlTableModel::revertRow(row);
}
 
void QSqlRelationalTableModel::clear()
{
    Q_D(QSqlRelationalTableModel);
    beginResetModel();
    d->clearChanges();
    d->relations.clear();
    QSqlTableModel::clear();
    endResetModel();
}
 
 
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(QSqlRecord &values) const
{
    for (int i = 0; i < values.count(); ++i) {
        if (relations.value(i).isValid()) {
            QVariant v = values.value(i);
            bool gen = values.isGenerated(i);
            values.replace(i, baseRec.field(i));
            values.setValue(i, v);
            values.setGenerated(i, gen);
        }
    }
}
 
bool QSqlRelationalTableModel::updateRowInTable(int row, const QSqlRecord &values)
{
    Q_D(QSqlRelationalTableModel);
 
    QSqlRecord rec = values;
    d->translateFieldNames(rec);
 
    return QSqlTableModel::updateRowInTable(row, rec);
}
 
bool QSqlRelationalTableModel::insertRowIntoTable(const QSqlRecord &values)
{
    Q_D(QSqlRelationalTableModel);
 
    QSqlRecord rec = values;
    d->translateFieldNames(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 f = d->fullyQualifiedFieldName(SqlrTm::relTablePrefix(d->sortColumn), rel.displayColumn());
    f = d->sortOrder == Qt::AscendingOrder ? SqlrTm::asc(f) : SqlrTm::desc(f);
    return SqlrTm::orderBy(f);
}
 
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.size() > column)
            d->relations.remove(column);
    }
    return QSqlTableModel::removeColumns(column, count, parent);
}
 
QT_END_NAMESPACE
 
#include "moc_qsqlrelationaltablemodel.cpp"