boost rebuild

[packages/akonadi.git] / 0021-Implement-support-for-CASE.WHEN.THEN-SQL-statements-.patch

From 9698d589e4c2b489f406fe1a823d4bb42c322f71 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dan=20Vr=C3=A1til?= <dvratil@redhat.com>
Date: Fri, 5 Dec 2014 18:21:18 +0100
Subject: [PATCH 21/30] Implement support for CASE...WHEN...THEN SQL statements
 SELECT columns

CASE...WHEN...THEN is a useful construct especially for aggregation
queries.
---
 server/src/storage/query.cpp               | 38 ++++++++++++++++++++++++++++++
 server/src/storage/query.h                 | 19 +++++++++++++++
 server/src/storage/querybuilder.cpp        | 30 +++++++++++++++++++++++
 server/src/storage/querybuilder.h          | 14 +++++++++++
 server/tests/unittest/querybuildertest.cpp | 38 +++++++++++++++++++++++++++++-
 5 files changed, 138 insertions(+), 1 deletion(-)

diff --git a/server/src/storage/query.cpp b/server/src/storage/query.cpp
index 6fb6c6e..c938ade 100644
--- a/server/src/storage/query.cpp
+++ b/server/src/storage/query.cpp
@@ -68,3 +68,41 @@ void Query::Condition::addCondition( const Condition &condition )
 {
   mSubConditions << condition;
 }
+
+
+Case::Case(const Condition &when, const QString &then, const QString &elseBranch)
+{
+    addCondition(when, then);
+    setElse(elseBranch);
+}
+
+Case::Case(const QString &column, CompareOperator op, const QVariant &value, const QString &when, const QString &elseBranch)
+{
+    addValueCondition(column, op, value, when);
+    setElse(elseBranch);
+}
+
+void Case::addCondition(const Condition &when, const QString &then)
+{
+    mWhenThen.append(qMakePair(when, then));
+}
+
+void Case::addValueCondition(const QString &column, CompareOperator op, const QVariant &value, const QString &then)
+{
+    Condition when;
+    when.addValueCondition(column, op, value);
+    addCondition(when, then);
+}
+
+void Case::addColumnCondition(const QString &column, CompareOperator op, const QString &column2, const QString &then)
+{
+    Condition when;
+    when.addColumnCondition(column, op, column2);
+    addCondition(when, then);
+}
+
+void Case::setElse(const QString &elseBranch)
+{
+    mElse = elseBranch;
+}
+
diff --git a/server/src/storage/query.h b/server/src/storage/query.h
index f4f1ac0..c8f35a7 100644
--- a/server/src/storage/query.h
+++ b/server/src/storage/query.h
@@ -130,6 +130,25 @@ class Condition
 
 }; // class Condition
 
+
+class Case
+{
+  friend class Akonadi::Server::QueryBuilder;
+  public:
+    Case(const Condition &when, const QString &then, const QString &elseBranch = QString());
+    Case(const QString &column, Query::CompareOperator op, const QVariant &value, const QString &when, const QString &elseBranch = QString());
+
+    void addCondition(const Condition &when, const QString &then);
+    void addValueCondition(const QString &column, Query::CompareOperator op, const QVariant &value, const QString &then);
+    void addColumnCondition(const QString &column, Query::CompareOperator op, const QString &column2, const QString &then);
+
+    void setElse(const QString &elseBranch);
+
+  private:
+    QVector<QPair<Condition, QString> > mWhenThen;
+    QString mElse;
+};
+
 } // namespace Query
 } // namespace Server
 } // namespace Akonadi
diff --git a/server/src/storage/querybuilder.cpp b/server/src/storage/querybuilder.cpp
index 3017867..74ed2da 100644
--- a/server/src/storage/querybuilder.cpp
+++ b/server/src/storage/querybuilder.cpp
@@ -457,11 +457,27 @@ void QueryBuilder::addColumn( const QString &col )
   mColumns << col;
 }
 
+void QueryBuilder::addColumn( const Query::Case &caseStmt )
+{
+  QString query;
+  buildCaseStatement(&query, caseStmt);
+  mColumns.append(query);
+}
+
 void QueryBuilder::addAggregation( const QString &col, const QString &aggregate )
 {
   mColumns.append( aggregate + QLatin1Char( '(' ) + col + QLatin1Char( ')' ) );
 }
 
+void QueryBuilder::addAggregation(const Query::Case &caseStmt, const QString &aggregate)
+{
+  QString query(aggregate + QLatin1Char('('));
+  buildCaseStatement(&query, caseStmt);
+  query += QLatin1Char(')');
+
+  mColumns.append(query);
+}
+
 void QueryBuilder::bindValue( QString *query, const QVariant &value )
 {
   mBindValues << value;
@@ -510,6 +526,20 @@ void QueryBuilder::buildWhereCondition( QString *query, const Query::Condition &
   }
 }
 
+void QueryBuilder::buildCaseStatement(QString *query, const Query::Case &caseStmt)
+{
+    *query += QLatin1String("CASE ");
+    for (const auto whenThen : caseStmt.mWhenThen) {
+        *query += QLatin1String("WHEN ");
+        buildWhereCondition(query, whenThen.first);    // When
+        *query += QLatin1String(" THEN ") + whenThen.second; // then
+    }
+    if (!caseStmt.mElse.isEmpty()) {
+        *query += QLatin1String(" ELSE ") + caseStmt.mElse;
+    }
+    *query += QLatin1String(" END");
+}
+
 void QueryBuilder::setSubQueryMode( Query::LogicOperator op, ConditionType type )
 {
   Q_ASSERT( type == WhereCondition || ( type == HavingCondition && mType == Select ) );
diff --git a/server/src/storage/querybuilder.h b/server/src/storage/querybuilder.h
index df7c362..0304108 100644
--- a/server/src/storage/querybuilder.h
+++ b/server/src/storage/querybuilder.h
@@ -123,6 +123,12 @@ class QueryBuilder
     void addColumn( const QString &col );
 
     /**
+     * Adds the given case statement to a select query.
+     * @param caseStmt The case statement to add.
+     */
+    void addColumn( const Query::Case &caseStmt );
+
+    /**
      * Adds an aggregation statement.
      * @param col The column to aggregate on
      * @param aggregate The aggregation function.
@@ -130,6 +136,13 @@ class QueryBuilder
     void addAggregation( const QString &col, const QString &aggregate );
 
     /**
+     * Adds and aggregation statement with CASE
+     * @param caseStmt The case statement to aggregate on
+     * @param aggregate The aggregation function.
+     */
+    void addAggregation( const Query::Case &caseStmt, const QString &aggregate );
+
+    /**
       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
@@ -239,6 +252,7 @@ class QueryBuilder
     void buildQuery( QString *query );
     void bindValue( QString *query, const QVariant &value );
     void buildWhereCondition( QString *query, const Query::Condition &cond );
+    void buildCaseStatement( QString *query, const Query::Case &caseStmt );
 
     /**
      * SQLite does not support JOINs with UPDATE, so we have to convert it into
diff --git a/server/tests/unittest/querybuildertest.cpp b/server/tests/unittest/querybuildertest.cpp
index 92df2a2..848829d 100644
--- a/server/tests/unittest/querybuildertest.cpp
+++ b/server/tests/unittest/querybuildertest.cpp
@@ -217,6 +217,42 @@ void QueryBuilderTest::testQueryBuilder_data()
   }
 
   {
+    /// SELECT with CASE
+    QueryBuilder qbTpl = QueryBuilder("table1", QueryBuilder::Select );
+    qbTpl.setDatabaseType( DbType::MySQL );
+
+    QueryBuilder qb = qbTpl;
+    qb.addColumn( "col" );
+    qb.addColumn( Query::Case( "col1", Query::Greater, 42, "1", "0" ) );
+    bindVals.clear();
+    bindVals << 42;
+    mBuilders << qb;
+    QTest::newRow( "select case simple") << mBuilders.count()
+        << QString( "SELECT col, CASE WHEN ( col1 > :0 ) THEN 1 ELSE 0 END FROM table1" ) << bindVals;
+
+
+    qb = qbTpl;
+    qb.addAggregation( "table1.col1", "sum" );
+    qb.addAggregation( "table1.col2", "count" );
+    Query::Condition cond( Query::Or );
+    cond.addValueCondition( "table3.col2", Query::Equals, "value1" );
+    cond.addValueCondition( "table3.col2", Query::Equals, "value2" );\
+    Query::Case caseStmt( cond, "1", "0" );
+    qb.addAggregation( caseStmt, "sum" );
+    qb.addJoin( QueryBuilder::LeftJoin, "table2", "table1.col3", "table2.col1" );
+    qb.addJoin( QueryBuilder::LeftJoin, "table3", "table2.col2", "table3.col1" );
+    bindVals.clear();
+    bindVals << QString("value1") << QString("value2");
+    mBuilders <<qb;
+    QTest::newRow( "select case, aggregation and joins" ) << mBuilders.count()
+        << QString( "SELECT sum(table1.col1), count(table1.col2), sum(CASE WHEN ( table3.col2 = :0 OR table3.col2 = :1 ) THEN 1 ELSE 0 END) "
+                    "FROM table1 "
+                    "LEFT JOIN table2 ON ( table1.col3 = table2.col1 ) "
+                    "LEFT JOIN table3 ON ( table2.col2 = table3.col1 )")
+        << bindVals;
+  }
+
+  {
     /// UPDATE with INNER JOIN
     QueryBuilder qbTpl = QueryBuilder( "table1", QueryBuilder::Update );
     qbTpl.setColumnValue( "col", 42 );
@@ -310,4 +346,4 @@ void QueryBuilderTest::benchQueryBuilder()
   }
 
   QVERIFY(executed);
-}
\ No newline at end of file
+}
-- 
2.1.0