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