commit 584721e59c938a6e4bc79abbc40738f05afa8f4e Author: Christian Ehringfeld Date: Mon Aug 3 00:55:59 2015 +0200 query stuff diff --git a/src/query.h b/src/query.h index bf9bee3..f245f8a 100644 --- a/src/query.h +++ b/src/query.h @@ -63,7 +63,8 @@ private: enum class JokerPosition { FRONT, // e.g. "%foo" BEHIND, // e.g. "foo%" - BOTH // e.g. "%foo%" + BOTH, // e.g. "%foo%" + NONE }; diff --git a/src/querybuilder.cpp b/src/querybuilder.cpp index 7e0f30c..c4de56d 100644 --- a/src/querybuilder.cpp +++ b/src/querybuilder.cpp @@ -762,6 +762,29 @@ const { return QString(superMeta->className()).toLower(); } +QString QueryBuilder::addWildcard(QVariant var, JokerPosition jp, + QChar jokerChar) const { + QString val = var.toString(); + if (!val.isEmpty()) { + switch (jp) { + case JokerPosition::BEHIND: + val += jokerChar; + break; + case JokerPosition::BOTH: + val = jokerChar + val + jokerChar; + break; + case JokerPosition::FRONT: + val = jokerChar + val; + break; + case JokerPosition::NONE: + break; + default: + break; + } + } + return val; +} + QString QueryBuilder::joinSuperClasses(const QSharedPointer &entity) const { auto classes = EntityHelper::superClasses(entity.data(), true); @@ -784,7 +807,7 @@ const { QString QueryBuilder::countFunction(const QString &distinctColumn) const { return QString(this->countKeyword() + "(" + (distinctColumn.isEmpty() ? "*" : (this->distinct() + - this->schema->quoteColumnName(distinctColumn))) + ")"); + this->schema->quoteColumnName(distinctColumn))) + ")"); } QString QueryBuilder::distinct() const { @@ -849,6 +872,18 @@ QString QueryBuilder::between(QString colName, QString valName1, this->placeHolder(valName2) + ")"; } +QString QueryBuilder::likeKeyword() const { + return "LIKE"; +} + +QString QueryBuilder::limitKeyword() const { + return "LIMIT"; +} + +QString QueryBuilder::offsetKeyword() const { + return "OFFSET"; +} + QString QueryBuilder::appendNot(bool notOp) { return (notOp ? (this->notKeyword() + " ") : ""); } @@ -860,10 +895,10 @@ QString QueryBuilder::entityClassname() const { QString QueryBuilder::limit(const qint64 &limit, const qint64 &offset) const { QString s = ""; if (limit > 0) { - s.append(" LIMIT ").append(QString::number(limit)); + s.append(" " + this->limitKeyword() + " ").append(QString::number(limit)); } if (offset > 0) { - s.append(" OFFSET ").append(QString::number(offset)); + s.append(" " + this->offsetKeyword() + " ").append(QString::number(offset)); } return s; } @@ -1046,7 +1081,8 @@ QString QueryBuilder::where(const QHash &m, if (m.size() == 0 || (ignoreID && m.contains(primaryKey) && m.size() == 1)) { return ""; } - return (withKeyword ? " WHERE " : "") + this->attributes(m, conjunction, ignoreID, primaryKey); + return (withKeyword ? " WHERE " : "") + this->attributes(m, conjunction, + ignoreID, primaryKey); } QString QueryBuilder::attributes(const QHash &m, @@ -1128,18 +1164,69 @@ void QueryBuilder::andOperator(Query &query, void QueryBuilder::arbitraryOperator(Query &query, QString op, QString column, QVariant value) { - query.appendCondition(column + " " + op + this->placeHolder(column)); + query.appendCondition(this->schema->quoteColumnName(column) + " " + op + " " + + this->placeHolder(column)); query.appendParam(column, value); } +void QueryBuilder::isNull(Query &query, QString column) { + query.appendCondition(this->schema->quoteColumnName(column) + " IS NULL"); +} + +void QueryBuilder::isNotNull(Query &query, QString column) { + query.appendCondition(this->schema->quoteColumnName(column) + " IS " + + this->notKeyword() + " NULL"); +} + void QueryBuilder::plainOr(Query &query) { query.appendCondition(this->orKeyword()); } +void QueryBuilder::plainNor(Query &query) { + query.appendCondition(this->notKeyword() + " " + this->orKeyword()); +} + void QueryBuilder::plainAnd(Query &query) { query.appendCondition(this->andKeyword()); } +void QueryBuilder::plainNand(Query &query) { + query.appendCondition(this->notKeyword() + " " + this->andKeyword()); +} + +void QueryBuilder::like(Query &query, QString column, QVariant value, + JokerPosition jp, QChar wildcard) { + this->arbitraryOperator(query, this->likeKeyword(), column, + this->addWildcard(value, jp, wildcard)); +} + +void QueryBuilder::like(Query &query, QHash conditions, + QString conjunction, + JokerPosition jp, QChar wildcard) { + QString condition = "("; + if (!conditions.isEmpty()) { + bool first = true; + for (auto i = conditions.constBegin(); i != conditions.constEnd(); ++i) { + if (first) { + first = false; + } else { + condition += conjunction; + } + condition += this->schema->quoteColumnName(i.key()) + " " + this->likeKeyword() + + " " + + this->placeHolder(i.key()); + QString newVal = this->addWildcard(i.value(), jp, wildcard); + query.appendParam(i.key(), newVal.isEmpty() ? i.value() : newVal); + } + condition += ")"; + query.appendCondition(condition); + } +} + +QSqlQuery QueryBuilder::generateQuery(const Query &query) const { + +} + void QueryBuilder::where(Query &query, QString column, QVariant value) { QString placeholder = column + "_where"; @@ -1149,14 +1236,22 @@ void QueryBuilder::where(Query &query, QString column, QVariant value) { } void QueryBuilder::where(Query &query, QHash conditions, - QString concat) { - QString condition = this->where(conditions,concat,false,"id",false); + QString conjunction) { + QString condition = this->where(conditions, conjunction, false, "id", false); for (auto i = conditions.constBegin(); i != conditions.constEnd(); ++i) { - query.appendParam(i.key(),i.value()); + query.appendParam(i.key(), i.value()); } query.appendCondition(condition); } +void QueryBuilder::where(Query &query, QString condition, + QHash values) { + query.appendCondition(condition); + for (auto i = values.constBegin(); i != values.constEnd(); ++i) { + query.appendParam(i.key(), i.value()); + } +} + void QueryBuilder::between(Query &query, QString column, QVariant firstValue, QVariant secondValue) { QString firstPh = column + "_bet1"; @@ -1201,7 +1296,8 @@ void QueryBuilder::orOperator(Query &query, } else { condition += " " + this->orKeyword() + " "; } - condition += this->schema->quoteColumnName(i.key()) + (like ? " LIKE " : "=") + + condition += this->schema->quoteColumnName(i.key()) + (like ? " " + + this->likeKeyword() + " " : "=") + this->placeHolder(i.key()); query.appendParam(i.key(), i.value()); } @@ -1213,8 +1309,9 @@ void QueryBuilder::orOperator(Query &query, QString QueryBuilder::where(const QSharedPointer &entity, QString conjunction, bool ignoreID) const { - return this->where(EntityHelper::getEntityAttributes(EntityHelper::getMetaProperties( - entity.data()), - entity), + return this->where(EntityHelper::getEntityAttributes( + EntityHelper::getMetaProperties( + entity.data()), + entity), conjunction, ignoreID, entity->getPrimaryKey()); } diff --git a/src/querybuilder.h b/src/querybuilder.h index 0c2e86e..2694b28 100644 --- a/src/querybuilder.h +++ b/src/querybuilder.h @@ -104,8 +104,15 @@ class QueryBuilder { void bindValue(const QString &key, const QVariant &value, QSqlQuery &q) const; virtual QString placeHolder(const QString &key) const; void where(Query &query, QString column, QVariant value); + /** + * @brief where + * @param query + * @param conditions + * @param conjunction its AND or OR + */ void where(Query &query, QHash conditions, - QString concat = "AND"); + QString conjunction = "AND"); + void where(Query &query, QString condition,QHash values= QHash()); //void where(Query &query,QHash> conditions, QString concat="AND"); void between(Query &query, QString column, QVariant firstValue, QVariant secondValue); @@ -117,29 +124,36 @@ class QueryBuilder { void orOperator(Query &query, QHash conditions, bool like = false); void andOperator(Query &query, QHash conditions); + /** + * @brief arbitraryOperator + * @param query + * @param op can be = | != | <> | > | < | >= | <= | !< | !> @see http://www.tutorialspoint.com/sql/sql-operators.htm comparison operators + * @param column + * @param value + */ void arbitraryOperator(Query &query, QString op, QString column, QVariant value); + void isNull(Query &query, QString column); + void isNotNull(Query &query, QString column); void plainOr(Query &query); //adds a simple OR to condition + void plainNor(Query &query); void plainAnd(Query &query); //add a simple AND to condition + void plainNand(Query &query); /** * Generates 'foo' LIKE "%bar%" * @brief like * @param column * @param value */ - void like(QString column, QString value, JokerPosition = JokerPosition::BOTH); + void like(Query &q, QString column, QVariant value, JokerPosition jp = JokerPosition::BOTH, QChar wildcard ='%'); /** * @brief like * @param condition * @param concat */ - void like(QHash conditions, QString concat = "AND", - JokerPosition = JokerPosition::BOTH); - - - - + void like(Query &query, QHash conditions, QString conjunction = "AND", + JokerPosition jp= JokerPosition::BOTH, QChar wildcard ='%'); protected: class ClassAttributes { @@ -161,6 +175,7 @@ class QueryBuilder { QString pk; QHash attributes; }; + QSqlQuery generateQuery(const Query &query) const; QSqlQuery find(const qint64 &id, const QString &tableName) const; QSqlQuery find(const qint64 &id, const QSharedPointer &entity, @@ -250,6 +265,7 @@ class QueryBuilder { QString leftJoin(const QString &foreignTable, const QString &tableName, const QString &foreignKey = "id", const QString &primaryKey = "id") const; QString superClassColumnName(const QMetaObject *&superMeta) const; + QString addWildcard(QVariant var, JokerPosition jp, QChar jokerChar = '%') const; QString joinSuperClasses(const QSharedPointer &entity) const; virtual QString selectBase(const QStringList &tables, @@ -267,6 +283,9 @@ class QueryBuilder { bool notOp = false); virtual QString between(QString colName, QString valName1, QString valName2, bool notOp = false); + virtual QString likeKeyword() const; + virtual QString limitKeyword() const; + virtual QString offsetKeyword() const; QString appendNot(bool notOp); virtual void appendCondition(Query &q, QString ph1, QString ph2, QVariant val1, QVariant val2, QString condition);