0021-Implement-support-for-CASE.WHEN.THEN-SQL-statements-.patch

   1 From 9698d589e4c2b489f406fe1a823d4bb42c322f71 Mon Sep 17 00:00:00 2001
   2 From: =?UTF-8?q?Dan=20Vr=C3=A1til?= <dvratil@redhat.com>
   3 Date: Fri, 5 Dec 2014 18:21:18 +0100
   4 Subject: [PATCH 21/30] Implement support for CASE...WHEN...THEN SQL statements
   5  SELECT columns
   6
   7 CASE...WHEN...THEN is a useful construct especially for aggregation
   8 queries.
   9 ---
  10  server/src/storage/query.cpp               | 38 ++++++++++++++++++++++++++++++
  11  server/src/storage/query.h                 | 19 +++++++++++++++
  12  server/src/storage/querybuilder.cpp        | 30 +++++++++++++++++++++++
  13  server/src/storage/querybuilder.h          | 14 +++++++++++
  14  server/tests/unittest/querybuildertest.cpp | 38 +++++++++++++++++++++++++++++-
  15  5 files changed, 138 insertions(+), 1 deletion(-)
  16
  17 diff --git a/server/src/storage/query.cpp b/server/src/storage/query.cpp
  18 index 6fb6c6e..c938ade 100644
  19 --- a/server/src/storage/query.cpp
  20 +++ b/server/src/storage/query.cpp
  21 @@ -68,3 +68,41 @@ void Query::Condition::addCondition( const Condition &condition )
  22  {
  23    mSubConditions << condition;
  24  }
  25 +
  26 +
  27 +Case::Case(const Condition &when, const QString &then, const QString &elseBranch)
  28 +{
  29 +    addCondition(when, then);
  30 +    setElse(elseBranch);
  31 +}
  32 +
  33 +Case::Case(const QString &column, CompareOperator op, const QVariant &value, const QString &when, const QString &elseBranch)
  34 +{
  35 +    addValueCondition(column, op, value, when);
  36 +    setElse(elseBranch);
  37 +}
  38 +
  39 +void Case::addCondition(const Condition &when, const QString &then)
  40 +{
  41 +    mWhenThen.append(qMakePair(when, then));
  42 +}
  43 +
  44 +void Case::addValueCondition(const QString &column, CompareOperator op, const QVariant &value, const QString &then)
  45 +{
  46 +    Condition when;
  47 +    when.addValueCondition(column, op, value);
  48 +    addCondition(when, then);
  49 +}
  50 +
  51 +void Case::addColumnCondition(const QString &column, CompareOperator op, const QString &column2, const QString &then)
  52 +{
  53 +    Condition when;
  54 +    when.addColumnCondition(column, op, column2);
  55 +    addCondition(when, then);
  56 +}
  57 +
  58 +void Case::setElse(const QString &elseBranch)
  59 +{
  60 +    mElse = elseBranch;
  61 +}
  62 +
  63 diff --git a/server/src/storage/query.h b/server/src/storage/query.h
  64 index f4f1ac0..c8f35a7 100644
  65 --- a/server/src/storage/query.h
  66 +++ b/server/src/storage/query.h
  67 @@ -130,6 +130,25 @@ class Condition
  68
  69  }; // class Condition
  70
  71 +
  72 +class Case
  73 +{
  74 +  friend class Akonadi::Server::QueryBuilder;
  75 +  public:
  76 +    Case(const Condition &when, const QString &then, const QString &elseBranch = QString());
  77 +    Case(const QString &column, Query::CompareOperator op, const QVariant &value, const QString &when, const QString &elseBranch = QString());
  78 +
  79 +    void addCondition(const Condition &when, const QString &then);
  80 +    void addValueCondition(const QString &column, Query::CompareOperator op, const QVariant &value, const QString &then);
  81 +    void addColumnCondition(const QString &column, Query::CompareOperator op, const QString &column2, const QString &then);
  82 +
  83 +    void setElse(const QString &elseBranch);
  84 +
  85 +  private:
  86 +    QVector<QPair<Condition, QString> > mWhenThen;
  87 +    QString mElse;
  88 +};
  89 +
  90  } // namespace Query
  91  } // namespace Server
  92  } // namespace Akonadi
  93 diff --git a/server/src/storage/querybuilder.cpp b/server/src/storage/querybuilder.cpp
  94 index 3017867..74ed2da 100644
  95 --- a/server/src/storage/querybuilder.cpp
  96 +++ b/server/src/storage/querybuilder.cpp
  97 @@ -457,11 +457,27 @@ void QueryBuilder::addColumn( const QString &col )
  98    mColumns << col;
  99  }
 100
 101 +void QueryBuilder::addColumn( const Query::Case &caseStmt )
 102 +{
 103 +  QString query;
 104 +  buildCaseStatement(&query, caseStmt);
 105 +  mColumns.append(query);
 106 +}
 107 +
 108  void QueryBuilder::addAggregation( const QString &col, const QString &aggregate )
 109  {
 110    mColumns.append( aggregate + QLatin1Char( '(' ) + col + QLatin1Char( ')' ) );
 111  }
 112
 113 +void QueryBuilder::addAggregation(const Query::Case &caseStmt, const QString &aggregate)
 114 +{
 115 +  QString query(aggregate + QLatin1Char('('));
 116 +  buildCaseStatement(&query, caseStmt);
 117 +  query += QLatin1Char(')');
 118 +
 119 +  mColumns.append(query);
 120 +}
 121 +
 122  void QueryBuilder::bindValue( QString *query, const QVariant &value )
 123  {
 124    mBindValues << value;
 125 @@ -510,6 +526,20 @@ void QueryBuilder::buildWhereCondition( QString *query, const Query::Condition &
 126    }
 127  }
 128
 129 +void QueryBuilder::buildCaseStatement(QString *query, const Query::Case &caseStmt)
 130 +{
 131 +    *query += QLatin1String("CASE ");
 132 +    for (const auto whenThen : caseStmt.mWhenThen) {
 133 +        *query += QLatin1String("WHEN ");
 134 +        buildWhereCondition(query, whenThen.first);    // When
 135 +        *query += QLatin1String(" THEN ") + whenThen.second; // then
 136 +    }
 137 +    if (!caseStmt.mElse.isEmpty()) {
 138 +        *query += QLatin1String(" ELSE ") + caseStmt.mElse;
 139 +    }
 140 +    *query += QLatin1String(" END");
 141 +}
 142 +
 143  void QueryBuilder::setSubQueryMode( Query::LogicOperator op, ConditionType type )
 144  {
 145    Q_ASSERT( type == WhereCondition || ( type == HavingCondition && mType == Select ) );
 146 diff --git a/server/src/storage/querybuilder.h b/server/src/storage/querybuilder.h
 147 index df7c362..0304108 100644
 148 --- a/server/src/storage/querybuilder.h
 149 +++ b/server/src/storage/querybuilder.h
 150 @@ -123,6 +123,12 @@ class QueryBuilder
 151      void addColumn( const QString &col );
 152
 153      /**
 154 +     * Adds the given case statement to a select query.
 155 +     * @param caseStmt The case statement to add.
 156 +     */
 157 +    void addColumn( const Query::Case &caseStmt );
 158 +
 159 +    /**
 160       * Adds an aggregation statement.
 161       * @param col The column to aggregate on
 162       * @param aggregate The aggregation function.
 163 @@ -130,6 +136,13 @@ class QueryBuilder
 164      void addAggregation( const QString &col, const QString &aggregate );
 165
 166      /**
 167 +     * Adds and aggregation statement with CASE
 168 +     * @param caseStmt The case statement to aggregate on
 169 +     * @param aggregate The aggregation function.
 170 +     */
 171 +    void addAggregation( const Query::Case &caseStmt, const QString &aggregate );
 172 +
 173 +    /**
 174        Add a WHERE or HAVING condition which compares a column with a given value.
 175        @param column The column that should be compared.
 176        @param op The operator used for comparison
 177 @@ -239,6 +252,7 @@ class QueryBuilder
 178      void buildQuery( QString *query );
 179      void bindValue( QString *query, const QVariant &value );
 180      void buildWhereCondition( QString *query, const Query::Condition &cond );
 181 +    void buildCaseStatement( QString *query, const Query::Case &caseStmt );
 182
 183      /**
 184       * SQLite does not support JOINs with UPDATE, so we have to convert it into
 185 diff --git a/server/tests/unittest/querybuildertest.cpp b/server/tests/unittest/querybuildertest.cpp
 186 index 92df2a2..848829d 100644
 187 --- a/server/tests/unittest/querybuildertest.cpp
 188 +++ b/server/tests/unittest/querybuildertest.cpp
 189 @@ -217,6 +217,42 @@ void QueryBuilderTest::testQueryBuilder_data()
 190    }
 191
 192    {
 193 +    /// SELECT with CASE
 194 +    QueryBuilder qbTpl = QueryBuilder("table1", QueryBuilder::Select );
 195 +    qbTpl.setDatabaseType( DbType::MySQL );
 196 +
 197 +    QueryBuilder qb = qbTpl;
 198 +    qb.addColumn( "col" );
 199 +    qb.addColumn( Query::Case( "col1", Query::Greater, 42, "1", "0" ) );
 200 +    bindVals.clear();
 201 +    bindVals << 42;
 202 +    mBuilders << qb;
 203 +    QTest::newRow( "select case simple") << mBuilders.count()
 204 +        << QString( "SELECT col, CASE WHEN ( col1 > :0 ) THEN 1 ELSE 0 END FROM table1" ) << bindVals;
 205 +
 206 +
 207 +    qb = qbTpl;
 208 +    qb.addAggregation( "table1.col1", "sum" );
 209 +    qb.addAggregation( "table1.col2", "count" );
 210 +    Query::Condition cond( Query::Or );
 211 +    cond.addValueCondition( "table3.col2", Query::Equals, "value1" );
 212 +    cond.addValueCondition( "table3.col2", Query::Equals, "value2" );\
 213 +    Query::Case caseStmt( cond, "1", "0" );
 214 +    qb.addAggregation( caseStmt, "sum" );
 215 +    qb.addJoin( QueryBuilder::LeftJoin, "table2", "table1.col3", "table2.col1" );
 216 +    qb.addJoin( QueryBuilder::LeftJoin, "table3", "table2.col2", "table3.col1" );
 217 +    bindVals.clear();
 218 +    bindVals << QString("value1") << QString("value2");
 219 +    mBuilders <<qb;
 220 +    QTest::newRow( "select case, aggregation and joins" ) << mBuilders.count()
 221 +        << QString( "SELECT sum(table1.col1), count(table1.col2), sum(CASE WHEN ( table3.col2 = :0 OR table3.col2 = :1 ) THEN 1 ELSE 0 END) "
 222 +                    "FROM table1 "
 223 +                    "LEFT JOIN table2 ON ( table1.col3 = table2.col1 ) "
 224 +                    "LEFT JOIN table3 ON ( table2.col2 = table3.col1 )")
 225 +        << bindVals;
 226 +  }
 227 +
 228 +  {
 229      /// UPDATE with INNER JOIN
 230      QueryBuilder qbTpl = QueryBuilder( "table1", QueryBuilder::Update );
 231      qbTpl.setColumnValue( "col", 42 );
 232 @@ -310,4 +346,4 @@ void QueryBuilderTest::benchQueryBuilder()
 233    }
 234
 235    QVERIFY(executed);
 236 -}
 237 \ No newline at end of file
 238 +}
 239 --
 240 2.1.0
 241