Cutelyst  2.13.0
sql.cpp
1 /*
2  * Copyright (C) 2015-2018 Daniel Nicoletti <dantti12@gmail.com>
3  *
4  * This library is free software; you can redistribute it and/or
5  * modify it under the terms of the GNU Lesser General Public
6  * License as published by the Free Software Foundation; either
7  * version 2.1 of the License, or (at your option) any later version.
8  *
9  * This library is distributed in the hope that it will be useful,
10  * but WITHOUT ANY WARRANTY; without even the implied warranty of
11  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
12  * Lesser General Public License for more details.
13  *
14  * You should have received a copy of the GNU Lesser General Public
15  * License along with this library; if not, write to the Free Software
16  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
17  */
18 #include "sql.h"
19 
20 #include <QLoggingCategory>
21 #include <QThread>
22 
23 #include <QJsonObject>
24 #include <QJsonArray>
25 #include <QJsonValue>
26 
27 #include <QtSql/QSqlQuery>
28 #include <QtSql/QSqlError>
29 #include <QtSql/QSqlRecord>
30 
31 Q_LOGGING_CATEGORY(C_SQL, "cutelyst.utils.sql", QtWarningMsg)
32 
33 using namespace Cutelyst;
34 
35 QVariantHash Sql::queryToHashObject(QSqlQuery &query)
36 {
37  QVariantHash ret;
38  if (query.next()) {
39  const QSqlRecord record = query.record();
40  const int columns = record.count();
41  for (int i = 0; i < columns; ++i) {
42  ret.insert(record.fieldName(i), query.value(i));
43  }
44  }
45  return ret;
46 }
47 
48 QVariantList Sql::queryToHashList(QSqlQuery &query)
49 {
50  QVariantList ret;
51  const QSqlRecord record = query.record();
52  const int columns = record.count();
53  QStringList cols;
54  for (int i = 0; i < columns; ++i) {
55  cols.append(record.fieldName(i));
56  }
57 
58  while (query.next()) {
59  QVariantHash line;
60  for (int i = 0; i < columns; ++i) {
61  line.insert(cols.at(i), query.value(i));
62  }
63  ret.append(line);
64  }
65  return ret;
66 }
67 
68 QVariantMap Sql::queryToMapObject(QSqlQuery &query)
69 {
70  QVariantMap ret;
71  if (query.next()) {
72  const QSqlRecord record = query.record();
73  const int columns = record.count();
74  for (int i = 0; i < columns; ++i) {
75  ret.insert(record.fieldName(i), query.value(i));
76  }
77  }
78  return ret;
79 }
80 
81 QJsonObject Sql::queryToJsonObject(QSqlQuery &query)
82 {
83  QJsonObject ret;
84  if (query.next()) {
85  const QSqlRecord record = query.record();
86  const int columns = record.count();
87  for (int i = 0; i < columns; ++i) {
88  ret.insert(record.fieldName(i), QJsonValue::fromVariant(query.value(i)));
89  }
90  }
91  return ret;
92 }
93 
94 QVariantList Sql::queryToMapList(QSqlQuery &query)
95 {
96  QVariantList ret;
97  const QSqlRecord record = query.record();
98  const int columns = record.count();
99  QStringList cols;
100  for (int i = 0; i < columns; ++i) {
101  cols.append(record.fieldName(i));
102  }
103 
104  while (query.next()) {
105  QVariantMap line;
106  for (int i = 0; i < columns; ++i) {
107  line.insert(cols.at(i), query.value(i));
108  }
109  ret.append(line);
110  }
111  return ret;
112 }
113 
114 QJsonArray Sql::queryToJsonObjectArray(QSqlQuery &query)
115 {
116  QJsonArray ret;
117  const QSqlRecord record = query.record();
118  const int columns = record.count();
119  QStringList cols;
120  for (int i = 0; i < columns; ++i) {
121  cols.append(record.fieldName(i));
122  }
123 
124  while (query.next()) {
125  QJsonObject line;
126  for (int i = 0; i < columns; ++i) {
127  line.insert(cols.at(i), QJsonValue::fromVariant(query.value(i)));
128  }
129  ret.append(line);
130  }
131  return ret;
132 }
133 
134 QVariantList Sql::queryToList(QSqlQuery &query)
135 {
136  QVariantList ret;
137 
138  const int columns = query.record().count();
139  while (query.next()) {
140  QVariantList line;
141  for (int i = 0; i < columns; ++i) {
142  line.append(query.value(i));
143  }
144  ret.append(QVariant::fromValue(line));
145  }
146 
147  return ret;
148 }
149 
150 QJsonArray Sql::queryToJsonArray(QSqlQuery &query)
151 {
152  QJsonArray ret;
153 
154  const int columns = query.record().count();
155  while (query.next()) {
156  QJsonArray array;
157  for (int i = 0; i < columns; ++i) {
158  array.append(QJsonValue::fromVariant(query.value(i)));
159  }
160  ret.append(array);
161  }
162 
163  return ret;
164 }
165 
166 QVariantHash Sql::queryToIndexedHash(QSqlQuery &query, const QString &key)
167 {
168  QVariantHash ret;
169 
170  const QSqlRecord record = query.record();
171  int index = record.indexOf(key);
172  if (index == -1) {
173  qCCritical(C_SQL) << "Field Name " << key <<
174  " not found in result set";
175  return ret;
176  }
177 
178  const int columns = record.count();
179  QStringList cols;
180 
181  for (int i = 0; i < columns; ++i) {
182  cols.append(record.fieldName(i));
183  }
184 
185  while (query.next()) {
186  QVariantHash line;
187  for (int i = 0; i < columns; ++i) {
188  if (i != index) {
189  line.insertMulti(cols.at(i), query.value(i));
190  }
191  }
192 
193  ret.insertMulti(query.value(index).toString(), line);
194  }
195 
196  return ret;
197 }
198 
199 QJsonObject Sql::queryToIndexedJsonObject(QSqlQuery &query, const QString &key)
200 {
201  QJsonObject ret;
202 
203  const QSqlRecord record = query.record();
204  int index = record.indexOf(key);
205  if (index == -1) {
206  qCCritical(C_SQL) << "Field Name " << key <<
207  " not found in result set";
208  return ret;
209  }
210 
211  const int columns = record.count();
212  QStringList cols;
213 
214  for (int i = 0; i < columns; ++i) {
215  cols.append(record.fieldName(i));
216  }
217 
218  while (query.next()) {
219  QJsonObject obj;
220  for (int i = 0; i < columns; ++i) {
221  if (i != index) {
222  obj.insert(cols.at(i), QJsonValue::fromVariant(query.value(i)));
223  }
224  }
225 
226  ret.insert(query.value(index).toString(), obj);
227  }
228 
229  return ret;
230 }
231 
232 void Sql::bindParamsToQuery(QSqlQuery &query, const Cutelyst::ParamsMultiMap &params, bool htmlEscaped)
233 {
234  auto it = params.constBegin();
235  if (htmlEscaped) {
236  while (it != params.constEnd()) {
237  if (it.value().isNull()) {
238  query.bindValue(QLatin1Char(':') + it.key(), QVariant());
239  } else {
240  query.bindValue(QLatin1Char(':') + it.key(), it.value().toHtmlEscaped());
241  }
242  ++it;
243  }
244  } else {
245  while (it != params.constEnd()) {
246  if (it.value().isNull()) {
247  query.bindValue(QLatin1Char(':') + it.key(), QVariant());
248  } else {
249  query.bindValue(QLatin1Char(':') + it.key(), it.value());
250  }
251  ++it;
252  }
253  }
254 }
255 
256 QSqlQuery Sql::preparedQuery(const QString &query, QSqlDatabase db, bool forwardOnly)
257 {
258  QSqlQuery sqlQuery(db);
259  sqlQuery.setForwardOnly(forwardOnly);
260  if (!sqlQuery.prepare(query)) {
261  qCCritical(C_SQL) << "Failed to prepare query:" << query << sqlQuery.lastError().databaseText();
262  }
263  return sqlQuery;
264 }
265 
266 QSqlQuery Sql::preparedQueryThread(const QString &query, const QString &dbName, bool forwardOnly)
267 {
268  QSqlQuery sqlQuery(QSqlDatabase::database(databaseNameThread(dbName)));
269  sqlQuery.setForwardOnly(forwardOnly);
270  if (!sqlQuery.prepare(query)) {
271  qCCritical(C_SQL) << "Failed to prepare query:" << query << sqlQuery.lastError().databaseText();
272  }
273  return sqlQuery;
274 }
275 
276 QString Sql::databaseNameThread(const QString &dbName)
277 {
278  return dbName + QLatin1Char('-') + QThread::currentThread()->objectName();
279 }
280 
281 QSqlDatabase Sql::databaseThread(const QString &dbName)
282 {
283  return QSqlDatabase::database(databaseNameThread(dbName));
284 }
285 
286 Sql::Transaction::Transaction(const QString &databaseName) : m_db(databaseThread(databaseName))
287 {
288  m_transactionRunning = m_db.transaction();
289 }
290 
291 Sql::Transaction::Transaction(const QSqlDatabase &database) : m_db(database)
292 {
293  m_transactionRunning = m_db.transaction();
294 }
295 
296 Sql::Transaction::~Transaction()
297 {
298  if (m_transactionRunning) {
299  m_db.rollback();
300  }
301 }
302 
303 bool Sql::Transaction::transaction() const
304 {
305  return m_transactionRunning;
306 }
307 
308 bool Sql::Transaction::commit()
309 {
310  // In case we fail to commit we will still call rollback, not sure it's really needed
311  m_transactionRunning = !m_db.commit();
312  return !m_transactionRunning;
313 }
314 
315 bool Sql::Transaction::rollback()
316 {
317  m_transactionRunning = false;
318  return m_db.rollback();
319 }
Cutelyst::ParamsMultiMap
QMap< QString, QString > ParamsMultiMap
Definition: paramsmultimap.h:36
Cutelyst
The Cutelyst namespace holds all public Cutelyst API.
Definition: Mainpage.dox:7
Cutelyst::Sql::Transaction::Transaction
Transaction(const QString &databaseName=QString())
Definition: sql.cpp:286