From 29a67e29b969a081097aaa99e36e4ca2c5e0392d Mon Sep 17 00:00:00 2001 From: Mark Brand Date: Wed, 14 Mar 2012 10:22:50 +0100 Subject: QSql*Model: add SQL syntax helper Change-Id: Ia53e30d7a2f2bb5b4f76c32fcf0fe526d1e4ab51 Reviewed-by: Honglei Zhang --- src/sql/models/qsqlquerymodel_p.h | 39 +++++++++++++++ src/sql/models/qsqlrelationaltablemodel.cpp | 76 ++++++++++------------------- src/sql/models/qsqltablemodel.cpp | 63 ++++++++++-------------- src/sql/models/qsqltablemodel_p.h | 5 ++ 4 files changed, 97 insertions(+), 86 deletions(-) (limited to 'src/sql') diff --git a/src/sql/models/qsqlquerymodel_p.h b/src/sql/models/qsqlquerymodel_p.h index b5eb60c57d..6e0349f032 100644 --- a/src/sql/models/qsqlquerymodel_p.h +++ b/src/sql/models/qsqlquerymodel_p.h @@ -82,6 +82,45 @@ public: QVarLengthArray colOffsets; // used to calculate indexInQuery of columns }; +// helpers for building SQL expressions +class QSqlQueryModelSql +{ +public: + // SQL keywords + inline const static QLatin1String as() { return QLatin1String("AS"); } + inline const static QLatin1String asc() { return QLatin1String("ASC"); } + inline const static QLatin1String comma() { return QLatin1String(","); } + inline const static QLatin1String desc() { return QLatin1String("DESC"); } + inline const static QLatin1String eq() { return QLatin1String("="); } + // "and" is a C++ keyword + inline const static QLatin1String et() { return QLatin1String("AND"); } + inline const static QLatin1String from() { return QLatin1String("FROM"); } + inline const static QLatin1String leftJoin() { return QLatin1String("LEFT JOIN"); } + inline const static QLatin1String on() { return QLatin1String("ON"); } + inline const static QLatin1String orderBy() { return QLatin1String("ORDER BY"); } + inline const static QLatin1String parenClose() { return QLatin1String(")"); } + inline const static QLatin1String parenOpen() { return QLatin1String("("); } + inline const static QLatin1String select() { return QLatin1String("SELECT"); } + inline const static QLatin1String sp() { return QLatin1String(" "); } + inline const static QLatin1String where() { return QLatin1String("WHERE"); } + + // Build expressions based on key words + inline const static QString as(const QString &a, const QString &b) { return b.isEmpty() ? a : concat(concat(a, as()), b); } + inline const static QString asc(const QString &s) { return concat(s, asc()); } + inline const static QString comma(const QString &a, const QString &b) { return a.isEmpty() ? b : b.isEmpty() ? a : QString(a).append(comma()).append(b); } + inline const static QString concat(const QString &a, const QString &b) { return a.isEmpty() ? b : b.isEmpty() ? a : QString(a).append(sp()).append(b); } + inline const static QString desc(const QString &s) { return concat(s, desc()); } + inline const static QString eq(const QString &a, const QString &b) { return QString(a).append(eq()).append(b); } + inline const static QString et(const QString &a, const QString &b) { return a.isEmpty() ? b : b.isEmpty() ? a : concat(concat(a, et()), b); } + inline const static QString from(const QString &s) { return concat(from(), s); } + inline const static QString leftJoin(const QString &s) { return concat(leftJoin(), s); } + inline const static QString on(const QString &s) { return concat(on(), s); } + inline const static QString orderBy(const QString &s) { return s.isEmpty() ? s : concat(orderBy(), s); } + inline const static QString paren(const QString &s) { return s.isEmpty() ? s : parenOpen() + s + parenClose(); } + inline const static QString select(const QString &s) { return concat(select(), s); } + inline const static QString where(const QString &s) { return s.isEmpty() ? s : concat(where(), s); } +}; + QT_END_NAMESPACE #endif // QSQLQUERYMODEL_P_H diff --git a/src/sql/models/qsqlrelationaltablemodel.cpp b/src/sql/models/qsqlrelationaltablemodel.cpp index b5d98d56b7..0387e5691f 100644 --- a/src/sql/models/qsqlrelationaltablemodel.cpp +++ b/src/sql/models/qsqlrelationaltablemodel.cpp @@ -57,6 +57,14 @@ QT_BEGIN_NAMESPACE +class QSqlRelationalTableModelSql: public QSqlTableModelSql +{ +public: + inline const static QString relTablePrefix(int i) { return QString::number(i).prepend(QLatin1String("relTblAl_")); } +}; + +typedef QSqlRelationalTableModelSql Sql; + /*! \class QSqlRelation \brief The QSqlRelation class stores information about an SQL foreign key. @@ -272,18 +280,6 @@ public: 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) { @@ -571,12 +567,12 @@ QString QSqlRelationalTableModel::selectStatement() const QString fList; QString conditions; - QString from = tableName(); + QString from = Sql::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 = QString::fromLatin1("relTblAl_%1").arg(i); + const QString relTableAlias = Sql::relTablePrefix(i); QString displayTableField = d->fullyQualifiedFieldName(relTableAlias, relation.displayColumn()); // Duplicate field names must be aliased @@ -587,55 +583,37 @@ QString QSqlRelationalTableModel::selectStatement() const QString displayColumn = relation.displayColumn(); if (d->db.driver()->isIdentifierEscaped(displayColumn, QSqlDriver::FieldName)) displayColumn = d->db.driver()->stripDelimiters(displayColumn, QSqlDriver::FieldName); - displayTableField.append(QString::fromLatin1(" AS %1_%2_%3").arg(relTableName).arg(displayColumn).arg(fieldNames.value(fieldList[i]))); + const QString alias = QString::fromLatin1("%1_%2_%3").arg(relTableName).arg(displayColumn).arg(fieldNames.value(fieldList[i])); + displayTableField = Sql::as(displayTableField, alias); --fieldNames[fieldList[i]]; } - if (!fList.isEmpty()) - fList.append(QLatin1String(", ")); - fList.append(displayTableField); + fList = Sql::comma(fList, displayTableField); // Join related table - const QString tblexpr = relation.tableName().append(QLatin1Char(' ')).append(relTableAlias); + const QString tblexpr = Sql::concat(relation.tableName(), relTableAlias); const QString relTableField = d->fullyQualifiedFieldName(relTableAlias, relation.indexColumn()); - const QString cond = tableField + QLatin1String(" = ") + relTableField; + const QString cond = Sql::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.append(QLatin1String(", ")).append(tblexpr); - if (!conditions.isEmpty()) - conditions.append(QLatin1String(" AND ")); - conditions.append(cond); + from = Sql::comma(from, tblexpr); + conditions = Sql::et(conditions, cond); } else { - from.append(QLatin1String(" LEFT JOIN ")).append(tblexpr); - from.append(QLatin1String(" ON ")).append(cond); + from = Sql::concat(from, Sql::leftJoin(tblexpr)); + from = Sql::concat(from, Sql::on(cond)); } } else { - if (!fList.isEmpty()) - fList.append(QLatin1String(", ")); - fList.append(tableField); + fList = Sql::comma(fList, tableField); } } if (fList.isEmpty()) return QString(); - QString query = query.append(QLatin1String("SELECT ")); - query.append(fList).append(QLatin1String(" FROM ")).append(from); - - if (d->joinMode == QSqlRelationalTableModel::InnerJoin) { - qAppendWhereClause(query, conditions, 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; + const QString stmt = Sql::concat(Sql::select(fList), from); + const QString where = Sql::where(Sql::et(Sql::paren(conditions), Sql::paren(filter()))); + return Sql::concat(Sql::concat(stmt, where), orderByClause()); } /*! @@ -777,11 +755,9 @@ QString QSqlRelationalTableModel::orderByClause() const if (!rel.isValid()) return QSqlTableModel::orderByClause(); - QString s = QLatin1String("ORDER BY "); - s.append(d->fullyQualifiedFieldName(QLatin1String("relTblAl_") + QString::number(d->sortColumn), - rel.displayColumn())); - s += d->sortOrder == Qt::AscendingOrder ? QLatin1String(" ASC") : QLatin1String(" DESC"); - return s; + QString f = d->fullyQualifiedFieldName(Sql::relTablePrefix(d->sortColumn), rel.displayColumn()); + f = d->sortOrder == Qt::AscendingOrder ? Sql::asc(f) : Sql::desc(f); + return Sql::orderBy(f); } /*! diff --git a/src/sql/models/qsqltablemodel.cpp b/src/sql/models/qsqltablemodel.cpp index 0e5eba1a73..20d2be36e1 100644 --- a/src/sql/models/qsqltablemodel.cpp +++ b/src/sql/models/qsqltablemodel.cpp @@ -55,6 +55,8 @@ QT_BEGIN_NAMESPACE +typedef QSqlTableModelSql Sql; + /*! \internal Populates our record with values. */ @@ -411,8 +413,9 @@ bool QSqlTableModel::selectRow(int row) d->tableName, d->primaryValues(row), false); - if (d->filter.startsWith(QLatin1String("WHERE "), Qt::CaseInsensitive)) - d->filter.remove(0, 6); + static const QString wh = Sql::where() + Sql::sp(); + if (d->filter.startsWith(wh, Qt::CaseInsensitive)) + d->filter.remove(0, wh.length()); const QString stmt = selectStatement(); d->sortColumn = table_sort_col; d->filter = table_filter; @@ -589,8 +592,8 @@ bool QSqlTableModel::updateRowInTable(int row, const QSqlRecord &values) const QSqlRecord whereValues = d->primaryValues(row); const bool prepStatement = d->db.driver()->hasFeature(QSqlDriver::PreparedQueries); - QString stmt = d->db.driver()->sqlStatement(QSqlDriver::UpdateStatement, d->tableName, - rec, prepStatement); + const QString stmt = d->db.driver()->sqlStatement(QSqlDriver::UpdateStatement, d->tableName, + rec, prepStatement); const QString where = d->db.driver()->sqlStatement(QSqlDriver::WhereStatement, d->tableName, whereValues, prepStatement); @@ -599,9 +602,8 @@ bool QSqlTableModel::updateRowInTable(int row, const QSqlRecord &values) QSqlError::StatementError); return false; } - stmt.append(QLatin1Char(' ')).append(where); - return d->exec(stmt, prepStatement, rec, whereValues); + return d->exec(Sql::concat(stmt, where), prepStatement, rec, whereValues); } @@ -656,10 +658,10 @@ bool QSqlTableModel::deleteRowFromTable(int row) const QSqlRecord whereValues = d->primaryValues(row); const bool prepStatement = d->db.driver()->hasFeature(QSqlDriver::PreparedQueries); - QString stmt = d->db.driver()->sqlStatement(QSqlDriver::DeleteStatement, - d->tableName, - QSqlRecord(), - prepStatement); + const QString stmt = d->db.driver()->sqlStatement(QSqlDriver::DeleteStatement, + d->tableName, + QSqlRecord(), + prepStatement); const QString where = d->db.driver()->sqlStatement(QSqlDriver::WhereStatement, d->tableName, whereValues, @@ -670,9 +672,8 @@ bool QSqlTableModel::deleteRowFromTable(int row) QSqlError::StatementError); return false; } - stmt.append(QLatin1Char(' ')).append(where); - return d->exec(stmt, prepStatement, QSqlRecord() /* no new values */, whereValues); + return d->exec(Sql::concat(stmt, where), prepStatement, QSqlRecord() /* no new values */, whereValues); } /*! @@ -923,19 +924,16 @@ void QSqlTableModel::setSort(int column, Qt::SortOrder order) QString QSqlTableModel::orderByClause() const { Q_D(const QSqlTableModel); - QString s; QSqlField f = d->rec.field(d->sortColumn); if (!f.isValid()) - return s; - - QString table = d->tableName; + return QString(); + //we can safely escape the field because it would have been obtained from the database //and have the correct case QString field = d->db.driver()->escapeIdentifier(f.name(), QSqlDriver::FieldName); - s.append(QLatin1String("ORDER BY ")).append(table).append(QLatin1Char('.')).append(field); - s += d->sortOrder == Qt::AscendingOrder ? QLatin1String(" ASC") : QLatin1String(" DESC"); - - return s; + field.prepend(QLatin1Char('.')).prepend(d->tableName); + field = d->sortOrder == Qt::AscendingOrder ? Sql::asc(field) : Sql::desc(field); + return Sql::orderBy(field); } /*! @@ -958,34 +956,27 @@ int QSqlTableModel::fieldIndex(const QString &fieldName) const QString QSqlTableModel::selectStatement() const { Q_D(const QSqlTableModel); - QString query; if (d->tableName.isEmpty()) { d->error = QSqlError(QLatin1String("No table name given"), QString(), QSqlError::StatementError); - return query; + return QString(); } if (d->rec.isEmpty()) { d->error = QSqlError(QLatin1String("Unable to find table ") + d->tableName, QString(), QSqlError::StatementError); - return query; + return QString(); } - query = d->db.driver()->sqlStatement(QSqlDriver::SelectStatement, - d->tableName, - d->rec, - false); - if (query.isEmpty()) { + const QString stmt = d->db.driver()->sqlStatement(QSqlDriver::SelectStatement, + d->tableName, + d->rec, + false); + if (stmt.isEmpty()) { d->error = QSqlError(QLatin1String("Unable to select fields from table ") + d->tableName, QString(), QSqlError::StatementError); - return query; + return stmt; } - if (!d->filter.isEmpty()) - query.append(QLatin1String(" WHERE ")).append(d->filter); - QString orderBy(orderByClause()); - if (!orderBy.isEmpty()) - query.append(QLatin1Char(' ')).append(orderBy); - - return query; + return Sql::concat(Sql::concat(stmt, Sql::where(d->filter)), orderByClause()); } /*! diff --git a/src/sql/models/qsqltablemodel_p.h b/src/sql/models/qsqltablemodel_p.h index 20ca63af61..2bbb4e1cf8 100644 --- a/src/sql/models/qsqltablemodel_p.h +++ b/src/sql/models/qsqltablemodel_p.h @@ -191,6 +191,11 @@ public: CacheMap cache; }; +class QSqlTableModelSql: public QSqlQueryModelSql +{ +public: +}; + QT_END_NAMESPACE #endif // QSQLTABLEMODEL_P_H -- cgit v1.2.3