1 /*
2 * Kexi Report Plugin
3 * Copyright (C) 2007-2017 by Adam Pigg <adam@piggz.co.uk>
4 * Copyright (C) 2017-2018 Jarosław Staniek <staniek@kde.org>
5 *
6 * This library is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU Lesser General Public
8 * License as published by the Free Software Foundation; either
9 * version 2.1 of the License, or (at your option) any later version.
10 *
11 * This library is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
14 * Lesser General Public License for more details.
15 *
16 * You should have received a copy of the GNU Lesser General Public
17 * License along with this library.  If not, see <http://www.gnu.org/licenses/>.
18 */
19 
20 #include "KexiDBReportDataSource.h"
21 #include "kexireportpart.h"
22 #include <kexiutils/utils.h>
23 #include <kexiqueryparameters.h>
24 
25 #include <KDbConnection>
26 #include <KDbOrderByColumn>
27 #include <KDbQuerySchema>
28 #include <KDbNativeStatementBuilder>
29 
30 #include <QDomDocument>
31 #include <QDebug>
32 
33 class Q_DECL_HIDDEN KexiDBReportDataSource::Private
34 {
35 public:
Private(KexiReportPartTempData * data)36     explicit Private(KexiReportPartTempData *data)
37       : cursor(0), tempData(data), originalSchema(0), copySchema(0)
38     {
39     }
~Private()40     ~Private()
41     {
42         delete copySchema;
43         delete originalSchema;
44     }
45 
46 
47     QString objectName;
48 
49     KDbCursor *cursor;
50     KexiReportPartTempData *tempData;
51     KDbQuerySchema *originalSchema;
52     KDbQuerySchema *copySchema;
53     KDbEscapedString schemaSql;
54     QList<QVariant> currentParams;
55 };
56 
KexiDBReportDataSource(const QString & objectName,const QString & pluginId,KexiReportPartTempData * data)57 KexiDBReportDataSource::KexiDBReportDataSource(const QString &objectName, const QString &pluginId,
58                                                KexiReportPartTempData *data)
59     : d(new Private(data))
60 {
61     d->objectName = objectName;
62     getSchema(pluginId);
63 }
64 
setSorting(const QList<SortedField> & sorting)65 void KexiDBReportDataSource::setSorting(const QList<SortedField>& sorting)
66 {
67     if (d->copySchema) {
68         if (sorting.isEmpty())
69             return;
70         KDbOrderByColumnList order;
71         for (int i = 0; i < sorting.count(); i++) {
72             if (!order.appendField(d->tempData->connection(), d->copySchema, sorting[i].field(),
73                                    KDbOrderByColumn::fromQt(sorting[i].order())))
74             {
75                 qWarning() << "Cannot set sort field" << i << sorting[i].field();
76                 return;
77             }
78         }
79         d->copySchema->setOrderByColumnList(order);
80     } else {
81         qWarning() << "Unable to sort null schema";
82     }
83 }
84 
addCondition(const QString & field,const QVariant & value,const QString & relation)85 void KexiDBReportDataSource::addCondition(const QString &field, const QVariant &value, const QString& relation)
86 {
87     if (d->copySchema) {
88         KDbField *fld = d->copySchema->findTableField(field);
89         if (fld) {
90             if (relation.length() == 1) {
91                 QString errorMessage;
92                 QString errorDescription;
93                 if (!d->copySchema->addToWhereExpression(fld, value, KDbToken(relation.toLatin1()[0]),
94                                                          &errorMessage, &errorDescription))
95                 {
96                     qWarning() << "Invalid expression cannot be added to WHERE:" << fld
97                                << relation << value;
98                     qWarning() << "addToWhereExpression() failed, message=" << errorMessage
99                                << "description=" << errorDescription;
100                 }
101             } else {
102                 qWarning() << "Invalid relation passed in:" << relation;
103             }
104         }
105     } else {
106         qDebug() << "Unable to add expresstion to null schema";
107     }
108 }
109 
~KexiDBReportDataSource()110 KexiDBReportDataSource::~KexiDBReportDataSource()
111 {
112     close();
113     delete d;
114 }
115 
open()116 bool KexiDBReportDataSource::open()
117 {
118     if ( d->tempData->connection() && d->cursor == 0 )
119     {
120         if ( d->objectName.isEmpty() )
121         {
122             return false;
123         }
124         else if ( d->copySchema)
125         {
126             //qDebug() << "Opening cursor.."
127             //         << KDbConnectionAndQuerySchema(d->tempData->connection(), *d->copySchema);
128             bool ok;
129             KexiUtils::WaitCursorRemover remover;
130             d->currentParams = KexiQueryParameters::getParameters(0, d->tempData->connection(), d->originalSchema, &ok);
131             if (!ok) {
132                 return false;
133             }
134 
135             d->cursor = d->tempData->connection()->executeQuery(d->copySchema, d->currentParams, KDbCursor::Option::Buffered);
136         }
137 
138 
139         if ( d->cursor )
140         {
141             qDebug() << "Moving to first record..";
142             return d->cursor->moveFirst();
143         }
144         else
145             return false;
146     }
147     return false;
148 }
149 
close()150 bool KexiDBReportDataSource::close()
151 {
152     if (d->cursor) {
153         const bool ok = d->cursor->close();
154         d->tempData->connection()->deleteCursor(d->cursor);
155         d->cursor = nullptr;
156         return ok;
157     }
158     return true;
159 }
160 
getSchema(const QString & pluginId)161 bool KexiDBReportDataSource::getSchema(const QString& pluginId)
162 {
163     if (d->tempData->connection()) {
164         KDbTableSchemaChangeListener::unregisterForChanges(d->tempData->connection(), d->tempData);
165         delete d->originalSchema;
166         d->originalSchema = 0;
167         delete d->copySchema;
168         d->copySchema = 0;
169 
170         KDbTableSchema *table = nullptr;
171         KDbQuerySchema *query = nullptr;
172         if ((pluginId.isEmpty() || pluginId == "org.kexi-project.table")
173                 && (table = d->tempData->connection()->tableSchema(d->objectName)))
174         {
175             qDebug() << d->objectName <<  "is a table..";
176             d->originalSchema = new KDbQuerySchema(table);
177         }
178         else if ((pluginId.isEmpty() || pluginId == "org.kexi-project.query")
179                  && (query = d->tempData->connection()->querySchema(d->objectName)))
180         {
181             qDebug() << d->objectName <<  "is a query..";
182             qDebug() << KDbConnectionAndQuerySchema(d->tempData->connection(), *query);
183             d->originalSchema = new KDbQuerySchema(*query, d->tempData->connection());
184         }
185 
186         if (d->originalSchema) {
187             const KDbNativeStatementBuilder builder(d->tempData->connection(), KDb::DriverEscaping);
188             KDbEscapedString sql;
189             if (builder.generateSelectStatement(&sql, d->originalSchema, d->currentParams)) {
190                 qDebug() << "Original:" << sql;
191             } else {
192                 qDebug() << "Original: ERROR";
193                 return false;
194             }
195             qDebug() << KDbConnectionAndQuerySchema(d->tempData->connection(), *d->originalSchema);
196 
197             d->copySchema = new KDbQuerySchema(*d->originalSchema, d->tempData->connection());
198             qDebug() << KDbConnectionAndQuerySchema(d->tempData->connection(), *d->copySchema);
199             if (builder.generateSelectStatement(&d->schemaSql, d->copySchema, d->currentParams)) {
200                 qDebug() << "Copy:" << d->schemaSql;
201             } else {
202                 qDebug() << "Copy: ERROR";
203                 return false;
204             }
205             if (table) {
206                 KDbTableSchemaChangeListener::registerForChanges(d->tempData->connection(), d->tempData, table);
207             } else if (query) {
208                 KDbTableSchemaChangeListener::registerForChanges(d->tempData->connection(), d->tempData, query);
209             }
210         }
211         return true;
212     }
213     return false;
214 }
215 
sourceName() const216 QString KexiDBReportDataSource::sourceName() const
217 {
218     return d->objectName;
219 }
220 
fieldNumber(const QString & fld) const221 int KexiDBReportDataSource::fieldNumber ( const QString &fld ) const
222 {
223     if (!d->cursor || !d->cursor->query()) {
224         return -1;
225     }
226     const KDbQueryColumnInfo::Vector fieldsExpanded(d->cursor->query()->fieldsExpanded(
227         d->tempData->connection(), KDbQuerySchema::FieldsExpandedMode::Unique));
228     for (int i = 0; i < fieldsExpanded.size(); ++i) {
229         if (0 == QString::compare(fld, fieldsExpanded[i]->aliasOrName(), Qt::CaseInsensitive)) {
230             return i;
231         }
232     }
233     return -1;
234 }
235 
fieldNames() const236 QStringList KexiDBReportDataSource::fieldNames() const
237 {
238     if (!d->originalSchema) {
239         return QStringList();
240     }
241     QStringList names;
242     const KDbQueryColumnInfo::Vector fieldsExpanded(d->originalSchema->fieldsExpanded(
243         d->tempData->connection(), KDbQuerySchema::FieldsExpandedMode::Unique));
244     for (int i = 0; i < fieldsExpanded.size(); i++) {
245         //! @todo in some Kexi mode captionOrAliasOrName() would be used here (more user-friendly)
246         names.append(fieldsExpanded[i]->aliasOrName());
247     }
248     return names;
249 }
250 
value(int i) const251 QVariant KexiDBReportDataSource::value (int i) const
252 {
253     if ( d->cursor )
254         return d->cursor->value ( i );
255 
256     return QVariant();
257 }
258 
value(const QString & fld) const259 QVariant KexiDBReportDataSource::value ( const QString &fld ) const
260 {
261     int i = fieldNumber ( fld );
262 
263     if (d->cursor && i >= 0)
264         return d->cursor->value ( i );
265 
266     return QVariant();
267 }
268 
moveNext()269 bool KexiDBReportDataSource::moveNext()
270 {
271     if ( d->cursor )
272         return d->cursor->moveNext();
273 
274     return false;
275 }
276 
movePrevious()277 bool KexiDBReportDataSource::movePrevious()
278 {
279     if ( d->cursor ) return d->cursor->movePrev();
280 
281     return false;
282 }
283 
moveFirst()284 bool KexiDBReportDataSource::moveFirst()
285 {
286     if ( d->cursor ) return d->cursor->moveFirst();
287 
288     return false;
289 }
290 
moveLast()291 bool KexiDBReportDataSource::moveLast()
292 {
293     if ( d->cursor )
294         return d->cursor->moveLast();
295 
296     return false;
297 }
298 
at() const299 qint64 KexiDBReportDataSource::at() const
300 {
301     if ( d->cursor )
302         return d->cursor->at();
303 
304     return 0;
305 }
306 
recordCount() const307 qint64 KexiDBReportDataSource::recordCount() const
308 {
309     if (d->copySchema) {
310         return d->tempData->connection()->recordCount(d->copySchema);
311     }
312 
313     return 1;
314 }
315 
runAggregateFunction(const QString & function,const QString & field,const QMap<QString,QVariant> & conditions)316 double KexiDBReportDataSource::runAggregateFunction(const QString &function, const QString &field,
317                                                     const QMap<QString, QVariant> &conditions)
318 {
319     double numberResult = 0.0;
320     if (d->schemaSql.isEmpty()) {
321         qWarning() << "No query for running aggregate function" << function << field;
322         return numberResult;
323     }
324     KDbEscapedString whereSql;
325     KDbConnection *conn = d->tempData->connection();
326     if (!conditions.isEmpty()) {
327         for (QMap<QString, QVariant>::ConstIterator it = conditions.constBegin();
328              it != conditions.constEnd(); ++it)
329         {
330             if (!whereSql.isEmpty()) {
331                 whereSql.append(" AND ");
332             }
333             KDbQueryColumnInfo *cinfo = d->copySchema->columnInfo(conn, it.key());
334             if (!cinfo) {
335                 qWarning() << "Could not find column" << it.key() << "for condition" << it.key()
336                            << "=" << it.value();
337                 return numberResult;
338             }
339             whereSql.append(
340                 KDbEscapedString(d->tempData->connection()->escapeIdentifier(cinfo->aliasOrName()))
341                 + " = "
342                 + d->tempData->connection()->driver()->valueToSql(cinfo->field(), it.value()));
343         }
344         whereSql.prepend(" WHERE ");
345     }
346 
347     const KDbEscapedString sql = KDbEscapedString("SELECT " + function + "(" + field + ") FROM ("
348                                                   + d->schemaSql + ")" + whereSql);
349     QString stringResult;
350     const tristate res = d->tempData->connection()->querySingleString(sql, &stringResult);
351     if (res != true) {
352         qWarning() << "Failed to execute query for running aggregate function" << function << field;
353         return numberResult;
354     }
355     bool ok;
356     numberResult = stringResult.toDouble(&ok);
357     if (!ok) {
358         qWarning() << "Result of query for running aggregate function" << function << field
359                    << "is not a number (" << stringResult << ")";
360         return numberResult;
361     }
362     return numberResult;
363 }
364 
dataSourceNames() const365 QStringList KexiDBReportDataSource::dataSourceNames() const
366 {
367     //Get the list of queries in the database
368     QStringList qs;
369     if (d->tempData->connection() && d->tempData->connection()->isConnected()) {
370         QList<int> tids = d->tempData->connection()->tableIds();
371         qs << "";
372         for (int i = 0; i < tids.size(); ++i) {
373             KDbTableSchema* tsc = d->tempData->connection()->tableSchema(tids[i]);
374             if (tsc)
375                 qs << tsc->name();
376         }
377 
378         QList<int> qids = d->tempData->connection()->queryIds();
379         qs << "";
380         for (int i = 0; i < qids.size(); ++i) {
381             KDbQuerySchema* qsc = d->tempData->connection()->querySchema(qids[i]);
382             if (qsc)
383                 qs << qsc->name();
384         }
385     }
386 
387     return qs;
388 }
389 
create(const QString & source) const390 KReportDataSource* KexiDBReportDataSource::create(const QString& source) const
391 {
392     return new KexiDBReportDataSource(source, QString(), d->tempData);
393 }
394