diff --git a/src/plugins/reports/KexiDBReportDataSource.h b/src/plugins/reports/KexiDBReportDataSource.h --- a/src/plugins/reports/KexiDBReportDataSource.h +++ b/src/plugins/reports/KexiDBReportDataSource.h @@ -1,7 +1,7 @@ /* * Kexi Report Plugin * Copyright (C) 2007-2017 by Adam Pigg -* Copyright (C) 2017 Jarosław Staniek +* Copyright (C) 2017-2018 Jarosław Staniek * * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public @@ -66,6 +66,21 @@ virtual qint64 at() const; virtual qint64 recordCount() const; + /** + * Runs aggregate function @a function on the data source + * + * @param function name such as max, min, avg + * @param field name of field for which the aggregation should be executed + * @param conditions optional conditions that limit the record set + * @return value of the function, 0.0 on failure + * + * @warning SQL injection warning: validity of @a function name is not checked, this should not + * be part of a public API. + * @todo Move SQL aggregate functions to KDb. Current code depends on support for subqueries. + */ + double runAggregateFunction(const QString &function, const QString &field, + const QMap &conditions); + //Utility Functions virtual QStringList dataSourceNames() const; virtual KReportDataSource* create(const QString& source) const Q_REQUIRED_RESULT; diff --git a/src/plugins/reports/KexiDBReportDataSource.cpp b/src/plugins/reports/KexiDBReportDataSource.cpp --- a/src/plugins/reports/KexiDBReportDataSource.cpp +++ b/src/plugins/reports/KexiDBReportDataSource.cpp @@ -1,7 +1,7 @@ /* * Kexi Report Plugin * Copyright (C) 2007-2017 by Adam Pigg -* Copyright (C) 2017 Jarosław Staniek +* Copyright (C) 2017-2018 Jarosław Staniek * * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public @@ -48,6 +48,7 @@ KexiReportPartTempData *tempData; KDbQuerySchema *originalSchema; KDbQuerySchema *copySchema; + KDbEscapedString schemaSql; }; KexiDBReportDataSource::KexiDBReportDataSource(const QString &objectName, const QString &pluginId, @@ -185,8 +186,8 @@ d->copySchema = new KDbQuerySchema(*d->originalSchema, d->tempData->connection()); qDebug() << KDbConnectionAndQuerySchema(d->tempData->connection(), *d->copySchema); - if (builder.generateSelectStatement(&sql, d->copySchema)) { - qDebug() << "Copy:" << sql; + if (builder.generateSelectStatement(&d->schemaSql, d->copySchema)) { + qDebug() << "Copy:" << d->schemaSql; } else { qDebug() << "Copy: ERROR"; return false; @@ -302,6 +303,55 @@ return 1; } +double KexiDBReportDataSource::runAggregateFunction(const QString &function, const QString &field, + const QMap &conditions) +{ + double numberResult = 0.0; + if (d->schemaSql.isEmpty()) { + qWarning() << "No query for running aggregate function" << function << field; + return numberResult; + } + KDbEscapedString whereSql; + KDbConnection *conn = d->tempData->connection(); + if (!conditions.isEmpty()) { + for (QMap::ConstIterator it = conditions.constBegin(); + it != conditions.constEnd(); ++it) + { + if (!whereSql.isEmpty()) { + whereSql.append(" AND "); + } + KDbQueryColumnInfo *cinfo = d->copySchema->columnInfo(conn, it.key()); + if (!cinfo) { + qWarning() << "Could not find column" << it.key() << "for condition" << it.key() + << "=" << it.value(); + return numberResult; + } + whereSql.append( + KDbEscapedString(d->tempData->connection()->escapeIdentifier(cinfo->aliasOrName())) + + " = " + + d->tempData->connection()->driver()->valueToSql(cinfo->field(), it.value())); + } + whereSql.prepend(" WHERE "); + } + + const KDbEscapedString sql = KDbEscapedString("SELECT " + function + "(" + field + ") FROM (" + + d->schemaSql + ")" + whereSql); + QString stringResult; + const tristate res = d->tempData->connection()->querySingleString(sql, &stringResult); + if (res != true) { + qWarning() << "Failed to execute query for running aggregate function" << function << field; + return numberResult; + } + bool ok; + numberResult = stringResult.toDouble(&ok); + if (!ok) { + qWarning() << "Result of query for running aggregate function" << function << field + << "is not a number (" << stringResult << ")"; + return numberResult; + } + return numberResult; +} + QStringList KexiDBReportDataSource::dataSourceNames() const { //Get the list of queries in the database diff --git a/src/plugins/reports/kexireportview.h b/src/plugins/reports/kexireportview.h --- a/src/plugins/reports/kexireportview.h +++ b/src/plugins/reports/kexireportview.h @@ -1,7 +1,7 @@ /* * Kexi Report Plugin * Copyright (C) 2007-2008 by Adam Pigg (adam@piggz.co.uk) - * Copyright (C) 2014-2017 Jarosław Staniek + * Copyright (C) 2014-2018 Jarosław Staniek * * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public @@ -20,15 +20,14 @@ #ifndef KEXIREPORTVIEW_H #define KEXIREPORTVIEW_H -#include - #include #include #include #include #include "kexireportpart.h" +class KexiDBReportDataSource; class KReportPreRenderer; class ORODocument; class KReportView; @@ -69,7 +68,7 @@ #endif KexiReportPartTempData* tempData() const; - KReportDataSource* createDataSource(const QDomElement &e); + KexiDBReportDataSource* createDataSource(const QDomElement &e); //! @todo KEXI3 KexiScriptAdaptor *m_kexi; KRScriptFunctions *m_functions; KReportRendererFactory m_factory; diff --git a/src/plugins/reports/kexireportview.cpp b/src/plugins/reports/kexireportview.cpp --- a/src/plugins/reports/kexireportview.cpp +++ b/src/plugins/reports/kexireportview.cpp @@ -1,7 +1,7 @@ /* * Kexi Report Plugin * Copyright (C) 2007-2008 by Adam Pigg (adam@piggz.co.uk) - * Copyright (C) 2014-2017 Jarosław Staniek + * Copyright (C) 2014-2018 Jarosław Staniek * * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public @@ -358,7 +358,7 @@ //qDebug() << tempData()->reportDefinition.tagName(); m_preRenderer = new KReportPreRenderer(tempData()->reportDefinition); if (m_preRenderer->isValid()) { - KReportDataSource *reportData = 0; + KexiDBReportDataSource *reportData = nullptr; if (!tempData()->connectionDefinition.isNull()) { reportData = createDataSource(tempData()->connectionDefinition); } @@ -373,8 +373,8 @@ // } // m_preRenderer->registerScriptObject(m_kexi, "Kexi"); //If using a kexidb source, add a functions scripting object - if (tempData()->connectionDefinition.attribute("type") == "internal") { - m_functions = new KRScriptFunctions(reportData, KexiMainWindowIface::global()->project()->dbConnection()); + if (reportData && tempData()->connectionDefinition.attribute("type") == "internal") { + m_functions = new KRScriptFunctions(reportData); m_preRenderer->registerScriptObject(m_functions, "field"); connect(m_preRenderer, SIGNAL(groupChanged(QMap)), @@ -399,7 +399,7 @@ return true; } -KReportDataSource* KexiReportView::createDataSource(const QDomElement &e) +KexiDBReportDataSource* KexiReportView::createDataSource(const QDomElement &e) { if (e.attribute("type") == "internal" && !e.attribute("source").isEmpty()) { return new KexiDBReportDataSource(e.attribute("source"), e.attribute("class"), tempData()); diff --git a/src/plugins/reports/krscriptfunctions.h b/src/plugins/reports/krscriptfunctions.h --- a/src/plugins/reports/krscriptfunctions.h +++ b/src/plugins/reports/krscriptfunctions.h @@ -1,6 +1,7 @@ /* * Kexi Report Plugin * Copyright (C) 2007-2008 by Adam Pigg (adam@piggz.co.uk) + * Copyright (C) 2012-2018 Jarosław Staniek * * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public @@ -19,11 +20,13 @@ #ifndef KRSCRIPTFUNCTIONS_H #define KRSCRIPTFUNCTIONS_H -#include #include #include +#include + +class KexiDBReportDataSource; class KDbConnection; class KDbCursor; @@ -33,20 +36,19 @@ { Q_OBJECT public: - KRScriptFunctions(const KReportDataSource *, KDbConnection*); + KRScriptFunctions(KexiDBReportDataSource *dataSource); ~KRScriptFunctions(); private: - KDbConnection *m_connection; - const KReportDataSource *m_cursor; + KexiDBReportDataSource * const m_dataSource; QString m_source; + + //! @todo Move SQL aggregate functions to KDb qreal math(const QString &, const QString &); QMap m_groupData; - KDbEscapedString where(); - public Q_SLOTS: virtual void setGroupData(const QMap &groupData); diff --git a/src/plugins/reports/krscriptfunctions.cpp b/src/plugins/reports/krscriptfunctions.cpp --- a/src/plugins/reports/krscriptfunctions.cpp +++ b/src/plugins/reports/krscriptfunctions.cpp @@ -1,7 +1,7 @@ /* * Kexi Report Plugin * Copyright (C) 2007-2008 by Adam Pigg - * Copyright (C) 2012 Jarosław Staniek + * Copyright (C) 2012-2018 Jarosław Staniek * * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public @@ -18,29 +18,18 @@ */ #include "krscriptfunctions.h" +#include "KexiDBReportDataSource.h" #include #include #include #include -KRScriptFunctions::KRScriptFunctions(const KReportDataSource* datasource, KDbConnection* conn) +KRScriptFunctions::KRScriptFunctions(KexiDBReportDataSource *datasource) + : m_dataSource(datasource) { - m_cursor = datasource; - m_connection = conn; - - if (datasource) { - if (m_connection->containsTable(datasource->sourceName()) == true) { - m_source = datasource->sourceName(); - } else if (m_connection->querySchema(datasource->sourceName())) { - KDbNativeStatementBuilder builder(conn, KDb::DriverEscaping); - KDbEscapedString source; - if (builder.generateSelectStatement(&source, m_connection->querySchema(datasource->sourceName()))) { - m_source = source.toByteArray(); - } - } - } + Q_ASSERT(m_dataSource); } KRScriptFunctions::~KRScriptFunctions() @@ -54,23 +43,7 @@ qreal KRScriptFunctions::math(const QString &function, const QString &field) { - QString ret = QLatin1String("0.0"); - - if (!m_connection) { - return 0.0; - } - - KDbEscapedString sql = KDbEscapedString("SELECT " + function + "(" + field + ") FROM (" + m_source + ")"); - - if (!m_groupData.isEmpty()) { - sql += " WHERE(" + where() + ')'; - } - - qDebug() << sql; - - m_connection->querySingleString(sql,&ret); - - return ret.toDouble(); + return m_dataSource->runAggregateFunction(function, field, m_groupData); } qreal KRScriptFunctions::sum(const QString &field) @@ -100,32 +73,10 @@ QVariant KRScriptFunctions::value(const QString &field) { - QVariant val; - if (!m_cursor) { - qDebug() << "No cursor to get value of field " << field; - return val; - } - - QStringList fields = m_cursor->fieldNames(); - - val = m_cursor->value(fields.indexOf(field)); + const QVariant val = m_dataSource->value(field); if (val.type() == QVariant::String) { // UTF-8 values are expected so convert this return val.toString().toUtf8(); } - return val; } - -KDbEscapedString KRScriptFunctions::where() -{ - QByteArray w; - QMap::const_iterator i = m_groupData.constBegin(); - while (i != m_groupData.constEnd()) { - w += '(' + i.key().toUtf8() + QByteArrayLiteral(" = '") + i.value().toString().toUtf8() + QByteArrayLiteral("') AND "); - ++i; - } - w.chop(4); - //kreportDebug() << w; - return KDbEscapedString(w); -}