commit dbf92b46962b690efc729933026c728feb5f6492 Author: Christian Ehringfeld Date: Wed Oct 14 22:58:25 2015 +0200 some comfort methods for query builder diff --git a/src/entitymanager.h b/src/entitymanager.h index 8d940e7..e387d4a 100644 --- a/src/entitymanager.h +++ b/src/entitymanager.h @@ -218,7 +218,7 @@ class EntityManager : public QObject { QHash(), quint64 limit = 0, quint64 offset = 0, bool joinBaseClasses = false, const bool resolveRelations = true) { QSharedPointer e = QSharedPointer - (EntityInstanceFactory::createInstance()); + (EntityInstanceFactory::createInstance()); if (e) { Query query = Query(QStringList(e->getTablename())); if (joinBaseClasses) { diff --git a/src/query.cpp b/src/query.cpp index 1cf9727..fb75285 100644 --- a/src/query.cpp +++ b/src/query.cpp @@ -15,6 +15,7 @@ */ #include "query.h" +#include "entity.h" using namespace CuteEntityManager; Query::Query() { } @@ -51,7 +52,6 @@ Query::Query(QString from, Expression where, Join join, this->groupBy.append(groupBy); this->distinct = distinct; this->having = having; - } void Query::appendWhere(const QString &condition) { diff --git a/src/query.h b/src/query.h index 1adc513..5371957 100644 --- a/src/query.h +++ b/src/query.h @@ -25,6 +25,7 @@ namespace CuteEntityManager { class Condition; class OrderBy; +class Entity; enum class Direction; class Query { public: @@ -112,10 +113,16 @@ class Query { quint64 offset = 0; }; +/** + * @brief The JokerPosition enum + * FRONT -> e.g. "%foo" + * BEHIND -> e.g. "foo%" + * BOTH -> e.g. "%foo%" + */ enum class JokerPosition { - FRONT, // e.g. "%foo" - BEHIND, // e.g. "foo%" - BOTH, // e.g. "%foo%" + FRONT, + BEHIND, + BOTH, NONE }; diff --git a/src/querybuilder.cpp b/src/querybuilder.cpp index 391ed7f..5ded4e8 100644 --- a/src/querybuilder.cpp +++ b/src/querybuilder.cpp @@ -547,7 +547,7 @@ QSqlQuery QueryBuilder::find(const qint64 &id, QSqlQuery q = this->database->getQuery(this->selectBase(QStringList( entity->getTablename())) + this->joinSuperClasses( entity) + " WHERE " + this->schema->quoteColumnName(entity->getTablename() + "." - + pk) + "= " + this->placeHolder(pk) + this->limit(1, offset)); + + pk) + this->equalOperator() + " " + this->placeHolder(pk) + this->limit(1, offset)); this->bindValue(pk, id, q); return q; } @@ -719,7 +719,7 @@ QSqlQuery QueryBuilder::manyToMany(const QString &tableName, sql += " " + this->whereKeyword() + " "; sql += this->schema->quoteColumnName( attribute); - sql += " = " + this->placeHolder(pk) + ";"; + sql += " " + this->equalOperator()+" " + this->placeHolder(pk) + ";"; q.prepare(sql); this->bindValue(pk, id, q); return q; @@ -731,7 +731,7 @@ QSqlQuery QueryBuilder::manyToManyDelete(const QString &tableName, QString pkCol = "id"; QString sql = "DELETE FROM " + this->schema->quoteTableName( tableName) + " WHERE " + this->schema->quoteColumnName( - attribute) + "=" + this->placeHolder(pkCol); + attribute) + this->equalOperator() + this->placeHolder(pkCol); q.prepare(sql); this->bindValue(pkCol, id, q); return q; @@ -813,6 +813,26 @@ QString QueryBuilder::countKeyword() const { return "COUNT"; } +QString QueryBuilder::notEqualOperator() const { + return "!="; +} + +QString QueryBuilder::equalOperator() const { + return "="; +} + +QString QueryBuilder::fromKeyword() const { + return "FROM"; +} + +QString QueryBuilder::isNullKeywords() const { + return "IS NULL"; +} + +QString QueryBuilder::isNotNullKeywords() const { + return "IS NOT NULL"; +} + Expression QueryBuilder::inFunction(QString column, QList values, bool notOp) const { QString condition = ""; @@ -1084,6 +1104,14 @@ QString QueryBuilder::where(const QHash &m, ignoreID, primaryKey); } +QString QueryBuilder::where(const QString &key, const QVariant &var, bool withKeyword, bool select, bool notEqual) const { + QString r = (withKeyword ? " WHERE " : ""); + r += this->schema->quoteColumnName(key) + (var.isNull() && select + ? (" " +(notEqual ? this->isNotNullKeywords(): this->isNullKeywords())) + : (notEqual ? this->notEqualOperator() : this->equalOperator() ) + this->placeHolder(key)); + return r; +} + QString QueryBuilder::attributes(const QHash &m, bool select, const QString &conjunction, bool ignoreID, const QString &primaryKey) const { @@ -1093,9 +1121,7 @@ QString QueryBuilder::attributes(const QHash &m, bool select, if (!rc.isEmpty()) { rc += " " + conjunction + " "; } - rc += this->schema->quoteColumnName(i.key()) + (i.value().isNull() - && select ? " is null" - : "=" + this->placeHolder(i.key())); + rc += this->where(i.key(), i.value(), false, select); } } return rc; @@ -1170,12 +1196,11 @@ Expression QueryBuilder::arbitraryOperator(QString op, QString column, } Expression QueryBuilder::isNull(QString column) const { - return Expression(this->schema->quoteColumnName(column) + " IS NULL"); + return Expression(this->schema->quoteColumnName(column) + this->isNullKeywords()); } Expression QueryBuilder::isNotNull(QString column) const { - return Expression(this->schema->quoteColumnName(column) + " IS " + - this->notKeyword() + " NULL"); + return Expression(this->schema->quoteColumnName(column) + this->isNotNullKeywords()); } Expression QueryBuilder::plainOr() const { @@ -1295,6 +1320,18 @@ Expression QueryBuilder::where(QString condition, return exp; } +Expression QueryBuilder::equal(QString &key, QVariant &value) { + Expression exp = Expression(this->where(key, value, false, true, false)); + exp.appendParam(key,value); + return exp; +} + +Expression QueryBuilder::notEqual(QString &key, QVariant &value) { + Expression exp = Expression(this->where(key, value, false, true, true)); + exp.appendParam(key,value); + return exp; +} + Expression QueryBuilder::between(QString column, QVariant firstValue, QVariant secondValue) const { QString firstPh = column + "_bet1"; @@ -1348,7 +1385,7 @@ Expression QueryBuilder::orOperator( condition += " " + this->orKeyword() + " "; } condition += this->schema->quoteColumnName(i.key()) + (like ? " " + - this->likeKeyword() + " " : "=") + + this->likeKeyword() + " " : this->equalOperator()) + this->placeHolder(i.key()); exp.appendParam(i.key(), i.value()); } diff --git a/src/querybuilder.h b/src/querybuilder.h index eb8e50a..ce33060 100644 --- a/src/querybuilder.h +++ b/src/querybuilder.h @@ -118,6 +118,8 @@ class QueryBuilder { QString conjunction = QStringLiteral("AND")) const; Expression where(QString condition, QHash values = QHash()) const; + Expression equal(QString &key, QVariant &value); + Expression notEqual(QString &key, QVariant &value); //void where(Query &query,QHash> conditions, QString concat="AND"); Expression between(QString column, QVariant firstValue, QVariant secondValue) const; @@ -263,6 +265,7 @@ class QueryBuilder { const QString &conjunction, bool ignoreID = false, const QString &primaryKey = "id", bool withKeyword = true) const; + QString where(const QString &key, const QVariant &var, bool withKeyword, bool select=true, bool notEqual=false) const; QString attributes(const QHash &m, bool select = true, const QString &conjunction = ",", bool ignoreID = false, const QString &primaryKey = "id") const; @@ -296,6 +299,11 @@ class QueryBuilder { virtual QString inKeyword() const; virtual QString whereKeyword() const; virtual QString countKeyword() const; + virtual QString notEqualOperator() const; + virtual QString equalOperator() const; + virtual QString fromKeyword() const; + virtual QString isNullKeywords() const; + virtual QString isNotNullKeywords() const; virtual Expression inFunction(QString column, QList values, bool notOp = false) const; virtual QString between(QString colName, QString valName1, QString valName2,