[prev in list] [next in list] [prev in thread] [next in thread]
List: kde-commits
Subject: kdesupport/akonadi/server
From: Milian Wolff <mail () milianw ! de>
Date: 2010-05-17 16:17:17
Message-ID: 20100517161717.ECB40AC8B8 () svn ! kde ! org
[Download RAW message or body]
SVN commit 1127784 by mwolff:
make it possible to build select queries using GROUP BY or HAVING with the \
QueryBuilder, add tests
M +35 -9 src/storage/querybuilder.cpp
M +44 -8 src/storage/querybuilder.h
M +31 -0 tests/unittest/querybuildertest.cpp
--- trunk/kdesupport/akonadi/server/src/storage/querybuilder.cpp #1127783:1127784
@@ -119,14 +119,16 @@
addJoin( joinType, table, condition );
}
-void QueryBuilder::addValueCondition(const QString & column, Query::CompareOperator \
op, const QVariant & value) +void QueryBuilder::addValueCondition(const QString & \
column, Query::CompareOperator op, const QVariant & value, ConditionType type) {
- mRootCondition.addValueCondition( column, op, value );
+ Q_ASSERT( type == WhereCondition || ( type == HavingCondition && mType == Select ) \
); + mRootCondition[type].addValueCondition( column, op, value );
}
-void QueryBuilder::addColumnCondition(const QString & column, Query::CompareOperator \
op, const QString & column2) +void QueryBuilder::addColumnCondition(const QString & \
column, Query::CompareOperator op, const QString & column2, ConditionType type) {
- mRootCondition.addColumnCondition( column, op, column2 );
+ Q_ASSERT( type == WhereCondition || ( type == HavingCondition && mType == Select ) \
); + mRootCondition[type].addColumnCondition( column, op, column2 );
}
QSqlQuery& QueryBuilder::query()
@@ -140,7 +142,7 @@
// we add the ON conditions of Inner Joins in a Update query here
// but don't want to change the mRootCondition on each exec().
- Query::Condition whereCondition = mRootCondition;
+ Query::Condition whereCondition = mRootCondition[WhereCondition];
switch ( mType ) {
case Select:
@@ -238,6 +240,16 @@
statement += buildWhereCondition( whereCondition );
}
+ if ( !mGroupColumns.isEmpty() ) {
+ statement += QLatin1String(" GROUP BY ");
+ statement += mGroupColumns.join( QLatin1String( ", " ) );
+ }
+
+ if ( !mRootCondition[HavingCondition].isEmpty() ) {
+ statement += QLatin1String(" HAVING ");
+ statement += buildWhereCondition( mRootCondition[HavingCondition] );
+ }
+
if ( !mSortColumns.isEmpty() ) {
Q_ASSERT_X( mType == Select, "QueryBuilder::exec()", "Order statements are only \
valid for SELECT queries" ); QStringList orderStmts;
@@ -323,14 +335,16 @@
}
}
-void QueryBuilder::setSubQueryMode(Query::LogicOperator op)
+void QueryBuilder::setSubQueryMode(Query::LogicOperator op, ConditionType type)
{
- mRootCondition.setSubQueryMode( op );
+ Q_ASSERT( type == WhereCondition || ( type == HavingCondition && mType == Select ) \
); + mRootCondition[type].setSubQueryMode( op );
}
-void QueryBuilder::addCondition(const Query::Condition & condition)
+void QueryBuilder::addCondition(const Query::Condition & condition, ConditionType \
type) {
- mRootCondition.addCondition( condition );
+ Q_ASSERT( type == WhereCondition || ( type == HavingCondition && mType == Select ) \
); + mRootCondition[type].addCondition( condition );
}
void QueryBuilder::addSortColumn(const QString & column, Query::SortOrder order )
@@ -338,6 +352,18 @@
mSortColumns << qMakePair( column, order );
}
+void QueryBuilder::addGroupColumn(const QString& column)
+{
+ Q_ASSERT( mType == Select );
+ mGroupColumns << column;
+}
+
+void QueryBuilder::addGroupColumns(const QStringList& columns)
+{
+ Q_ASSERT( mType == Select );
+ mGroupColumns += columns;
+}
+
void QueryBuilder::setColumnValue(const QString & column, const QVariant & value)
{
mColumnValues << qMakePair( column, value );
--- trunk/kdesupport/akonadi/server/src/storage/querybuilder.h #1127783:1127784
@@ -68,6 +68,17 @@
};
/**
+ * Defines the place at which a condition should be evaluated.
+ */
+ enum ConditionType {
+ /// add condition to WHERE part of the query
+ WhereCondition,
+ /// add condition to HAVING part of the query
+ /// NOTE: only supported for SELECT queries
+ HavingCondition
+ };
+
+ /**
Creates a new query builder.
@param table The main table to operate on.
@@ -115,31 +126,41 @@
void addColumn( const QString &col );
/**
- Add a WHERE condition which compares a column with a given value.
+ Add a WHERE or HAVING condition which compares a column with a given value.
@param column The column that should be compared.
@param op The operator used for comparison
@param value The value @p column is compared to.
+ @param type Defines whether this condition should be part of the WHERE or the \
HAVING + part of the query. Defaults to WHERE.
*/
- void addValueCondition( const QString &column, Query::CompareOperator op, const \
QVariant &value ); + void addValueCondition( const QString &column, \
Query::CompareOperator op, const QVariant &value, ConditionType type = WhereCondition \
);
/**
- Add a WHERE condition which compares a column with another column.
+ Add a WHERE or HAVING condition which compares a column with another column.
@param column The column that should be compared.
@param op The operator used for comparison.
@param column2 The column @p column is compared to.
+ @param type Defines whether this condition should be part of the WHERE or the \
HAVING + part of the query. Defaults to WHERE.
*/
- void addColumnCondition( const QString &column, Query::CompareOperator op, const \
QString &column2 ); + void addColumnCondition( const QString &column, \
Query::CompareOperator op, const QString &column2, ConditionType type = \
WhereCondition );
/**
Add a WHERE condition. Use this to build hierarchical conditions.
+ @param condition The condition that the resultset should satisfy.
+ @param type Defines whether this condition should be part of the WHERE or the \
HAVING + part of the query. Defaults to WHERE.
*/
- void addCondition( const Query::Condition &condition );
+ void addCondition( const Query::Condition &condition, ConditionType type = \
WhereCondition );
/**
- Define how WHERE condition are combined.
+ Define how WHERE or HAVING conditions are combined.
@todo Give this method a better name.
+ @param op The logical operator that should be used to combine the conditions.
+ @param type Defines whether the operator should be used for WHERE or for \
HAVING + conditions. Defaults to WHERE conditions.
*/
- void setSubQueryMode( Query::LogicOperator op );
+ void setSubQueryMode( Query::LogicOperator op, ConditionType type = \
WhereCondition );
/**
Add sort column.
@@ -149,6 +170,20 @@
void addSortColumn( const QString &column, Query::SortOrder order = \
Query::Ascending );
/**
+ Add a GROUP BY column.
+ NOTE: Only supported in SELECT queries.
+ @param column Name of the column to use for grouping.
+ */
+ void addGroupColumn( const QString &column );
+
+ /**
+ Add list of columns to GROUP BY.
+ NOTE: Only supported in SELECT queries.
+ @param columns Names of columns to use for grouping.
+ */
+ void addGroupColumns( const QStringList &columns );
+
+ /**
Sets a column to the given value (only valid for INSERT and UPDATE queries).
@param column Column to change.
@param value The value @p column should be set to.
@@ -189,12 +224,13 @@
private:
QString mTable;
DatabaseType mDatabaseType;
- Query::Condition mRootCondition;
+ QHash<ConditionType, Query::Condition> mRootCondition;
QSqlQuery mQuery;
QueryType mType;
QStringList mColumns;
QList<QVariant> mBindValues;
QList<QPair<QString, Query::SortOrder> > mSortColumns;
+ QStringList mGroupColumns;
QList<QPair<QString, QVariant> > mColumnValues;
// we must make sure that the tables are joined in the correct order
--- trunk/kdesupport/akonadi/server/tests/unittest/querybuildertest.cpp \
#1127783:1127784 @@ -137,6 +137,37 @@
mBuilders << qb;
QTest::newRow( "insert multi column PSQL" ) << mBuilders.count() << QString( \
"INSERT INTO table (col1, col2) VALUES (:0, :1) RETURNING id" ) << bindVals;
+ // test GROUP BY foo
+ bindVals.clear();
+ qb = QueryBuilder( "table", QueryBuilder::Select );
+ qb.addColumn( "foo" );
+ qb.addGroupColumn( "id1" );
+ mBuilders << qb;
+ QTest::newRow( "select group by single column" ) << mBuilders.count() << QString( \
"SELECT foo FROM table GROUP BY id1" ) << bindVals; + // test GROUP BY foo, bar
+ qb.addGroupColumn( "id2" );
+ mBuilders << qb;
+ QTest::newRow( "select group by two columns" ) << mBuilders.count() << QString( \
"SELECT foo FROM table GROUP BY id1, id2" ) << bindVals; + // test: HAVING \
.addValueCondition() + qb.addValueCondition( "bar", Equals, 1, \
QueryBuilder::HavingCondition ); + mBuilders << qb;
+ bindVals << 1;
+ QTest::newRow( "select with having valueCond" ) << mBuilders.count() << QString( \
"SELECT foo FROM table GROUP BY id1, id2 HAVING ( bar = :0 )" ) << bindVals; + // \
test: HAVING .addColumnCondition() + qb.addColumnCondition( "asdf", Equals, "yxcv", \
QueryBuilder::HavingCondition ); + mBuilders << qb;
+ QTest::newRow( "select with having columnCond" ) << mBuilders.count() << QString( \
"SELECT foo FROM table GROUP BY id1, id2 HAVING ( bar = :0 AND asdf = yxcv )" ) << \
bindVals; + // test: HAVING .addCondition()
+ qb.addCondition( subCon, QueryBuilder::HavingCondition );
+ mBuilders << qb;
+ QTest::newRow( "select with having condition" ) << mBuilders.count() << QString( \
"SELECT foo FROM table GROUP BY id1, id2 HAVING ( bar = :0 AND asdf = yxcv AND ( col1 \
> col2 AND col1 <> NULL ) )" ) << bindVals; + // test: HAVING and WHERE
+ qb.addValueCondition( "bla", Equals, 2, QueryBuilder::WhereCondition );
+ mBuilders << qb;
+ bindVals.clear();
+ bindVals << 2 << 1;
+ QTest::newRow( "select with having and where" ) << mBuilders.count() << QString( \
"SELECT foo FROM table WHERE ( bla = :0 ) GROUP BY id1, id2 HAVING ( bar = :1 AND \
asdf = yxcv AND ( col1 > col2 AND col1 <> NULL ) )" ) << bindVals; +
{
/// SELECT with JOINS
QueryBuilder qbTpl = QueryBuilder( "table1", QueryBuilder::Select );
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic