1 /***************************************************************************
2 mymoneystoragesql.cpp
3 ---------------------
4 begin : 11 November 2005
5 copyright : (C) 2005 by Tony Bloomfield
6 email : tonybloom@users.sourceforge.net
7 : Fernando Vilas <fvilas@iname.com>
8 : Christian Dávid <christian-david@web.de>
9 (C) 2017 by Łukasz Wojniłowicz <lukasz.wojnilowicz@gmail.com>
10 ***************************************************************************/
11
12 /***************************************************************************
13 * *
14 * This program is free software; you can redistribute it and/or modify *
15 * it under the terms of the GNU General Public License as published by *
16 * the Free Software Foundation; either version 2 of the License, or *
17 * (at your option) any later version. *
18 * *
19 ***************************************************************************/
20
21 #include "mymoneystoragesql_p.h"
22
23 // ----------------------------------------------------------------------------
24 // System Includes
25
26 // ----------------------------------------------------------------------------
27 // QT Includes
28
29 #include <QInputDialog>
30
31 // ----------------------------------------------------------------------------
32 // KDE Includes
33
34 #include "KMessageBox"
35
36 // ----------------------------------------------------------------------------
37 // Project Includes
38
39 //************************ Constructor/Destructor *****************************
MyMoneyStorageSql(MyMoneyStorageMgr * storage,const QUrl & url)40 MyMoneyStorageSql::MyMoneyStorageSql(MyMoneyStorageMgr *storage, const QUrl &url) :
41 QSqlDatabase(QUrlQuery(url).queryItemValue("driver")),
42 d_ptr(new MyMoneyStorageSqlPrivate(this))
43 {
44 Q_D(MyMoneyStorageSql);
45 d->m_storage = storage;
46 }
47
~MyMoneyStorageSql()48 MyMoneyStorageSql::~MyMoneyStorageSql()
49 {
50 try {
51 close(true);
52 } catch (const MyMoneyException &e) {
53 qDebug() << "Caught Exception in MMStorageSql dtor: " << e.what();
54 }
55 Q_D(MyMoneyStorageSql);
56 delete d;
57 }
58
currentVersion() const59 uint MyMoneyStorageSql::currentVersion() const
60 {
61 Q_D(const MyMoneyStorageSql);
62 return (d->m_db.currentVersion());
63 }
64
open(const QUrl & url,int openMode,bool clear)65 int MyMoneyStorageSql::open(const QUrl &url, int openMode, bool clear)
66 {
67 Q_D(MyMoneyStorageSql);
68 try {
69 int rc = 0;
70 d->m_driver = MyMoneyDbDriver::create(QUrlQuery(url).queryItemValue("driver"));
71 //get the input options
72 QStringList options = QUrlQuery(url).queryItemValue("options").split(',');
73 d->m_loadAll = true; // force loading whole database into memory since unification of storages
74 // options.contains("loadAll")/*|| m_mode == 0*/;
75 d->m_override = options.contains("override");
76
77 // create the database connection
78 // regarding the construction of the database name see the discussion on
79 // https://phabricator.kde.org/D12681. In case of a local file based DB
80 // driver we cut off the leading slash only in those cases, where we
81 // a) have a file based DB on Windows systems and
82 // b) have a server based DB.
83 // so that we do not modify the absolute path on *nix based systems
84 // in case of a DB based driver
85 QString dbName = url.path();
86 if(d->m_driver->requiresExternalFile()) {
87 #ifdef Q_OS_WIN
88 dbName = url.path().remove(0, 1); // remove separator slash for files on Windows
89 #endif
90 } else {
91 dbName = url.path().remove(0, 1); // remove separator slash for server based databases
92 }
93 setDatabaseName(dbName);
94 setHostName(url.host());
95 setUserName(url.userName());
96 setPassword(url.password());
97 if (QUrlQuery(url).queryItemValue("driver").contains("QMYSQL")) {
98 setConnectOptions("MYSQL_OPT_RECONNECT=1");
99 }
100
101 QSqlQuery query(*this);
102 switch (openMode) {
103 case QIODevice::ReadOnly: // OpenDatabase menu entry (or open last file)
104 case QIODevice::ReadWrite: // Save menu entry with database open
105 // this may be a sqlite file opened from the recently used list
106 // but which no longer exists. In that case, open will work but create an empty file.
107 // This is not what the user's after; he may accuse KMM of deleting all his data!
108 if (d->m_driver->requiresExternalFile()) {
109 if (!d->fileExists(dbName)) {
110 rc = 1;
111 break;
112 }
113 }
114 if (!QSqlDatabase::open()) {
115 d->buildError(QSqlQuery(*this), Q_FUNC_INFO, "opening database");
116 rc = 1;
117 } else {
118 if (driverName().compare(QLatin1String("QSQLCIPHER")) == 0) {
119 auto passphrase = password();
120 while (true) {
121 if (!passphrase.isEmpty()) {
122 query.exec(QString::fromLatin1("PRAGMA cipher_version"));
123 if(!query.next())
124 throw MYMONEYEXCEPTION_CSTRING("Based on empty cipher_version, libsqlcipher is not in use.");
125 query.exec(QString::fromLatin1("PRAGMA key = '%1'").arg(passphrase)); // SQLCipher feature to decrypt a database
126 }
127 query.exec(QStringLiteral("SELECT count(*) FROM sqlite_master")); // SQLCipher recommended way to check if password is correct
128 if (query.next()) {
129 query.finish();
130 rc = d->createTables(); // check all tables are present, create if not
131 break;
132 }
133 auto ok = false;
134 passphrase = QInputDialog::getText(nullptr, i18n("Password"),
135 i18n("You're trying to open an encrypted database.\n"
136 "Please provide a password in order to open it."),
137 QLineEdit::Password, QString(), &ok);
138 if (!ok) {
139 QSqlDatabase::close();
140 throw MYMONEYEXCEPTION_CSTRING("Bad password.");
141 }
142 }
143 } else {
144 rc = d->createTables(); // check all tables are present, create if not
145 }
146 }
147 break;
148 case QIODevice::WriteOnly: // SaveAs Database - if exists, must be empty, if not will create
149 {
150 // Try to open the database.
151 // If that fails, try to create the database, then try to open it again.
152 d->m_newDatabase = true;
153
154 // QSqlDatabase::open() always returns true on MS Windows
155 // even if SQLite database doesn't exist
156 auto isSQLiteAutocreated = false;
157 if (driverName().compare(QLatin1String("QSQLITE")) == 0 ||
158 driverName().compare(QLatin1String("QSQLCIPHER")) == 0) {
159 if (!QFile::exists(dbName))
160 isSQLiteAutocreated = true;
161 }
162 const auto isSuccessfullyOpened = QSqlDatabase::open();
163 if (!isSuccessfullyOpened || (isSQLiteAutocreated && isSuccessfullyOpened)) {
164 if (!d->createDatabase(url)) {
165 rc = 1;
166 } else {
167 if (!QSqlDatabase::open()) {
168 d->buildError(QSqlQuery(*this), Q_FUNC_INFO, "opening new database");
169 rc = 1;
170 } else {
171 query.exec(QString::fromLatin1("PRAGMA key = '%1'").arg(password()));
172 rc = d->createTables();
173 }
174 }
175 } else {
176 if (driverName().compare(QLatin1String("QSQLCIPHER")) == 0 &&
177 !password().isEmpty()) {
178 KMessageBox::information(nullptr, i18n("Overwriting an existing database with an encrypted database is not yet supported.\n"
179 "Please save your database under a new name."));
180 QSqlDatabase::close();
181 rc = 3;
182 return rc;
183 }
184 rc = d->createTables();
185 if (rc == 0) {
186 if (clear) {
187 d->clean();
188 } else {
189 rc = d->isEmpty();
190 }
191 }
192 }
193 break;
194 }
195 default:
196 qWarning("%s", qPrintable(QString("%1 - unknown open mode %2").arg(Q_FUNC_INFO).arg(openMode)));
197 }
198 if (rc != 0) return (rc);
199 // bypass logon check if we are creating a database
200 if (d->m_newDatabase) return(0);
201 // check if the database is locked, if not lock it
202 d->readFileInfo();
203 if (!d->m_logonUser.isEmpty() && (!d->m_override)) {
204 d->m_error = i18n("Database apparently in use\nOpened by %1 on %2 at %3.\nOpen anyway?",
205 d->m_logonUser,
206 d->m_logonAt.date().toString(Qt::ISODate),
207 d->m_logonAt.time().toString("hh.mm.ss"));
208 qDebug("%s", qPrintable(d->m_error));
209 close(false);
210 rc = -1; // retryable error
211 } else {
212 d->m_logonUser = url.userName() + '@' + url.host();
213 d->m_logonAt = QDateTime::currentDateTime();
214 d->writeFileInfo();
215 }
216 return(rc);
217 } catch (const QString& s) {
218 qDebug("%s", qPrintable(s));
219 return (1);
220 }
221 }
222
close(bool logoff)223 void MyMoneyStorageSql::close(bool logoff)
224 {
225 Q_D(MyMoneyStorageSql);
226 if (QSqlDatabase::isOpen()) {
227 if (logoff) {
228 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
229 d->m_logonUser.clear();
230 d->writeFileInfo();
231 }
232 QSqlDatabase::close();
233 QSqlDatabase::removeDatabase(connectionName());
234 }
235 }
236
getRecCount(const QString & table) const237 ulong MyMoneyStorageSql::getRecCount(const QString& table) const
238 {
239 Q_D(const MyMoneyStorageSql);
240 QSqlQuery q(*const_cast <MyMoneyStorageSql*>(this));
241 q.prepare(QString("SELECT COUNT(*) FROM %1;").arg(table));
242 if ((!q.exec()) || (!q.next())) { // krazy:exclude=crashy
243 d->buildError(q, Q_FUNC_INFO, "error retrieving record count");
244 qFatal("Error retrieving record count"); // definitely shouldn't happen
245 }
246 return ((ulong) q.value(0).toULongLong());
247 }
248
249 //////////////////////////////////////////////////////////////////
250
readFile()251 bool MyMoneyStorageSql::readFile()
252 {
253 Q_D(MyMoneyStorageSql);
254 d->m_displayStatus = true;
255 try {
256 d->readFileInfo();
257 d->readInstitutions();
258 if (d->m_loadAll) {
259 readPayees();
260 } else {
261 QList<QString> user;
262 user.append(QString("USER"));
263 readPayees(user);
264 }
265 readTags();
266 d->readCurrencies();
267 d->readSecurities();
268 d->readAccounts();
269 if (d->m_loadAll) {
270 d->readTransactions();
271 } else {
272 if (d->m_preferred.filterSet().testFlag(MyMoneyTransactionFilter::accountFilterActive))
273 readTransactions(d->m_preferred);
274 }
275 d->readSchedules();
276 d->readPrices();
277 d->readReports();
278 d->readBudgets();
279 d->readOnlineJobs();
280 //FIXME - ?? if (m_mode == 0)
281 //m_storage->rebuildAccountBalances();
282 // this seems to be nonsense, but it clears the dirty flag
283 // as a side-effect.
284 d->m_storage->setLastModificationDate(d->m_storage->lastModificationDate());
285 // FIXME?? if (m_mode == 0) m_storage = NULL;
286 // make sure the progress bar is not shown any longer
287 d->signalProgress(-1, -1);
288 d->m_displayStatus = false;
289 //MyMoneySqlQuery::traceOn();
290 return true;
291 } catch (const QString &) {
292 return false;
293 }
294 // this seems to be nonsense, but it clears the dirty flag
295 // as a side-effect.
296 }
297
298 // The following is called from 'SaveAsDatabase'
writeFile()299 bool MyMoneyStorageSql::writeFile()
300 {
301 Q_D(MyMoneyStorageSql);
302 // initialize record counts and hi ids
303 d->m_institutions = d->m_accounts = d->m_payees = d->m_tags = d->m_transactions = d->m_splits
304 = d->m_securities = d->m_prices = d->m_currencies = d->m_schedules = d->m_reports = d->m_kvps = d->m_budgets = 0;
305 d->m_hiIdInstitutions = d->m_hiIdPayees = d->m_hiIdTags = d->m_hiIdAccounts = d->m_hiIdTransactions =
306 d->m_hiIdSchedules = d->m_hiIdSecurities = d->m_hiIdReports = d->m_hiIdBudgets = 0;
307 d->m_onlineJobs = d->m_payeeIdentifier = 0;
308 d->m_displayStatus = true;
309 try {
310 const auto driverName = this->driverName();
311 if (driverName.compare(QLatin1String("QSQLITE")) == 0 ||
312 driverName.compare(QLatin1String("QSQLCIPHER")) == 0) {
313 QSqlQuery query(*this);
314 query.exec("PRAGMA foreign_keys = ON"); // this is needed for "ON UPDATE" and "ON DELETE" to work
315 }
316
317 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
318 d->writeInstitutions();
319 d->writePayees();
320 d->writeTags();
321 d->writeAccounts();
322 d->writeTransactions();
323 d->writeSchedules();
324 d->writeSecurities();
325 d->writePrices();
326 d->writeCurrencies();
327 d->writeReports();
328 d->writeBudgets();
329 d->writeOnlineJobs();
330 d->writeFileInfo();
331 // this seems to be nonsense, but it clears the dirty flag
332 // as a side-effect.
333 //m_storage->setLastModificationDate(m_storage->lastModificationDate());
334 // FIXME?? if (m_mode == 0) m_storage = NULL;
335
336 // make sure the progress bar is not shown any longer
337 d->signalProgress(-1, -1);
338 d->m_displayStatus = false;
339 // this seems to be nonsense, but it clears the dirty flag
340 // as a side-effect.
341 d->m_storage->setLastModificationDate(d->m_storage->lastModificationDate());
342 return true;
343 } catch (const QString &) {
344 return false;
345 }
346 }
347
lastError() const348 QString MyMoneyStorageSql::lastError() const
349 {
350 Q_D(const MyMoneyStorageSql);
351 return d->m_error;
352 }
353
354 // --------------- SQL Transaction (commit unit) handling -----------------------------------
startCommitUnit(const QString & callingFunction)355 void MyMoneyStorageSql::startCommitUnit(const QString& callingFunction)
356 {
357 Q_D(MyMoneyStorageSql);
358 if (d->m_commitUnitStack.isEmpty()) {
359 if (!transaction()) throw MYMONEYEXCEPTION(d->buildError(QSqlQuery(), callingFunction, "starting commit unit"));
360 }
361 d->m_commitUnitStack.push(callingFunction);
362 }
363
endCommitUnit(const QString & callingFunction)364 bool MyMoneyStorageSql::endCommitUnit(const QString& callingFunction)
365 {
366 Q_D(MyMoneyStorageSql);
367 // for now, we don't know if there were any changes made to the data so
368 // we expect the data to have changed. This assumption causes some unnecessary
369 // repaints of the UI here and there, but for now it's ok. If we can determine
370 // that the commit() really changes the data, we can return that information
371 // as value of this method.
372 bool rc = true;
373 if (d->m_commitUnitStack.isEmpty()) {
374 throw MYMONEYEXCEPTION_CSTRING("Empty commit unit stack while trying to commit");
375 }
376
377 if (callingFunction != d->m_commitUnitStack.top())
378 qDebug("%s", qPrintable(QString("%1 - %2 s/be %3").arg(Q_FUNC_INFO).arg(callingFunction).arg(d->m_commitUnitStack.top())));
379 d->m_commitUnitStack.pop();
380 if (d->m_commitUnitStack.isEmpty()) {
381 //qDebug() << "Committing with " << QSqlQuery::refCount() << " queries";
382 if (!commit()) throw MYMONEYEXCEPTION(d->buildError(QSqlQuery(), callingFunction, "ending commit unit"));
383 }
384 return rc;
385 }
386
cancelCommitUnit(const QString & callingFunction)387 void MyMoneyStorageSql::cancelCommitUnit(const QString& callingFunction)
388 {
389 Q_D(MyMoneyStorageSql);
390 if (d->m_commitUnitStack.isEmpty()) return;
391 if (callingFunction != d->m_commitUnitStack.top())
392 qDebug("%s", qPrintable(QString("%1 - %2 s/be %3").arg(Q_FUNC_INFO).arg(callingFunction).arg(d->m_commitUnitStack.top())));
393 d->m_commitUnitStack.clear();
394 if (!rollback()) throw MYMONEYEXCEPTION(d->buildError(QSqlQuery(), callingFunction, "cancelling commit unit") + ' ' + callingFunction);
395 }
396
397 /////////////////////////////////////////////////////////////////////
fillStorage()398 void MyMoneyStorageSql::fillStorage()
399 {
400 Q_D(MyMoneyStorageSql);
401 // if (!m_transactionListRead) // make sure we have loaded everything
402 d->readTransactions();
403 // if (!m_payeeListRead)
404 readPayees();
405 }
406
407 //------------------------------ Write SQL routines ----------------------------------------
408 // **** Institutions ****
409
410
addInstitution(const MyMoneyInstitution & inst)411 void MyMoneyStorageSql::addInstitution(const MyMoneyInstitution& inst)
412 {
413 Q_D(MyMoneyStorageSql);
414 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
415 QSqlQuery q(*this);
416 q.prepare(d->m_db.m_tables["kmmInstitutions"].insertString());
417 QList<MyMoneyInstitution> iList;
418 iList << inst;
419 d->writeInstitutionList(iList , q);
420 ++d->m_institutions;
421 d->writeFileInfo();
422 }
423
modifyInstitution(const MyMoneyInstitution & inst)424 void MyMoneyStorageSql::modifyInstitution(const MyMoneyInstitution& inst)
425 {
426 Q_D(MyMoneyStorageSql);
427 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
428 QSqlQuery q(*this);
429 q.prepare(d->m_db.m_tables["kmmInstitutions"].updateString());
430 QVariantList kvpList;
431 kvpList << inst.id();
432 d->deleteKeyValuePairs("OFXSETTINGS", kvpList);
433 QList<MyMoneyInstitution> iList;
434 iList << inst;
435 d->writeInstitutionList(iList , q);
436 d->writeFileInfo();
437 }
438
removeInstitution(const MyMoneyInstitution & inst)439 void MyMoneyStorageSql::removeInstitution(const MyMoneyInstitution& inst)
440 {
441 Q_D(MyMoneyStorageSql);
442 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
443 QVariantList kvpList;
444 kvpList << inst.id();
445 d->deleteKeyValuePairs("OFXSETTINGS", kvpList);
446 QSqlQuery query(*this);
447 query.prepare(d->m_db.m_tables["kmmInstitutions"].deleteString());
448 query.bindValue(":id", inst.id());
449 if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting Institution")); // krazy:exclude=crashy
450 --d->m_institutions;
451 d->writeFileInfo();
452 }
453
addPayee(const MyMoneyPayee & payee)454 void MyMoneyStorageSql::addPayee(const MyMoneyPayee& payee)
455 {
456 Q_D(MyMoneyStorageSql);
457 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
458 QSqlQuery query(*this);
459 query.prepare(d->m_db.m_tables["kmmPayees"].insertString());
460 d->writePayee(payee, query);
461 ++d->m_payees;
462
463 QVariantList identIds;
464 QList<payeeIdentifier> idents = payee.payeeIdentifiers();
465 // Store ids which have to be stored in the map table
466 identIds.reserve(idents.count());
467 foreach (payeeIdentifier ident, idents) {
468 try {
469 // note: this changes ident
470 addPayeeIdentifier(ident);
471 identIds.append(ident.idString());
472 } catch (const payeeIdentifier::empty &) {
473 }
474 }
475
476 if (!identIds.isEmpty()) {
477 // Create lists for batch processing
478 QVariantList order;
479 QVariantList payeeIdList;
480 order.reserve(identIds.size());
481 payeeIdList.reserve(identIds.size());
482
483 for (int i = 0; i < identIds.size(); ++i) {
484 order << i;
485 payeeIdList << payee.id();
486 }
487 query.prepare("INSERT INTO kmmPayeesPayeeIdentifier (payeeId, identifierId, userOrder) VALUES(?, ?, ?)");
488 query.bindValue(0, payeeIdList);
489 query.bindValue(1, identIds);
490 query.bindValue(2, order);
491 if (!query.execBatch())
492 throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("writing payee's identifiers")); // krazy:exclude=crashy
493 }
494
495 d->writeFileInfo();
496 }
497
modifyPayee(MyMoneyPayee payee)498 void MyMoneyStorageSql::modifyPayee(MyMoneyPayee payee)
499 {
500 Q_D(MyMoneyStorageSql);
501 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
502 QSqlQuery query(*this);
503 query.prepare(d->m_db.m_tables["kmmPayees"].updateString());
504 d->writePayee(payee, query);
505
506 // Get a list of old identifiers first
507 query.prepare("SELECT identifierId FROM kmmPayeesPayeeIdentifier WHERE payeeId = ?");
508 query.bindValue(0, payee.id());
509 if (!query.exec())
510 throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("modifying payee's identifiers (getting old values failed)")); // krazy:exclude=crashy
511
512 QStringList oldIdentIds;
513 oldIdentIds.reserve(query.numRowsAffected());
514 while (query.next())
515 oldIdentIds << query.value(0).toString();
516
517 // Add new and modify old payeeIdentifiers
518 foreach (payeeIdentifier ident, payee.payeeIdentifiers()) {
519 if (ident.idString().isEmpty()) {
520 payeeIdentifier oldIdent(ident);
521 addPayeeIdentifier(ident);
522 // addPayeeIdentifier could fail (throws an exception then) only remove old
523 // identifier if new one is stored correctly
524 payee.removePayeeIdentifier(oldIdent);
525 payee.addPayeeIdentifier(ident);
526 } else {
527 modifyPayeeIdentifier(ident);
528 payee.modifyPayeeIdentifier(ident);
529 oldIdentIds.removeAll(ident.idString());
530 }
531 }
532
533 // Remove identifiers which are not used anymore
534 foreach (QString idToRemove, oldIdentIds) {
535 payeeIdentifier ident(fetchPayeeIdentifier(idToRemove));
536 removePayeeIdentifier(ident);
537 }
538
539 // Update relation table
540 query.prepare("DELETE FROM kmmPayeesPayeeIdentifier WHERE payeeId = ?");
541 query.bindValue(0, payee.id());
542 if (!query.exec())
543 throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("modifying payee's identifiers (delete from mapping table)")); // krazy:exclude=crashy
544
545 // Get list again because modifiyPayeeIdentifier which is used above may change the id
546 QList<payeeIdentifier> idents(payee.payeeIdentifiers());
547
548 QVariantList order;
549 QVariantList payeeIdList;
550 QVariantList identIdList;
551 order.reserve(idents.size());
552 payeeIdList.reserve(idents.size());
553 identIdList.reserve(idents.size());
554
555 {
556 QList<payeeIdentifier>::const_iterator end = idents.constEnd();
557 int i = 0;
558 for (QList<payeeIdentifier>::const_iterator iter = idents.constBegin(); iter != end; ++iter, ++i) {
559 order << i;
560 payeeIdList << payee.id();
561 identIdList << iter->idString();
562 }
563 }
564
565 query.prepare("INSERT INTO kmmPayeesPayeeIdentifier (payeeId, userOrder, identifierId) VALUES(?, ?, ?)");
566 query.bindValue(0, payeeIdList);
567 query.bindValue(1, order);
568 query.bindValue(2, identIdList);
569 if (!query.execBatch())
570 throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("writing payee's identifiers during modify")); // krazy:exclude=crashy
571
572 d->writeFileInfo();
573 }
574
modifyUserInfo(const MyMoneyPayee & payee)575 void MyMoneyStorageSql::modifyUserInfo(const MyMoneyPayee& payee)
576 {
577 Q_D(MyMoneyStorageSql);
578 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
579 QSqlQuery q(*this);
580 q.prepare(d->m_db.m_tables["kmmPayees"].updateString());
581 d->writePayee(payee, q, true);
582 d->writeFileInfo();
583 }
584
removePayee(const MyMoneyPayee & payee)585 void MyMoneyStorageSql::removePayee(const MyMoneyPayee& payee)
586 {
587 Q_D(MyMoneyStorageSql);
588 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
589 QSqlQuery query(*this);
590
591 // Get identifiers first so we know which to delete
592 query.prepare("SELECT identifierId FROM kmmPayeesPayeeIdentifier WHERE payeeId = ?");
593 query.bindValue(0, payee.id());
594 if (!query.exec())
595 throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("removing payee's identifiers (getting old values failed)")); // krazy:exclude=crashy
596
597 QStringList identIds;
598 while (query.next())
599 identIds << query.value(0).toString();
600
601 QMap<QString, payeeIdentifier> idents = fetchPayeeIdentifiers(identIds);
602 foreach (payeeIdentifier ident, idents) {
603 removePayeeIdentifier(ident);
604 }
605
606 // Delete entries from mapping table
607 query.prepare("DELETE FROM kmmPayeesPayeeIdentifier WHERE payeeId = ?");
608 query.bindValue(0, payee.id());
609 if (!query.exec())
610 throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("removing payee's identifiers (delete from mapping table)")); // krazy:exclude=crashy
611
612 // Delete payee
613 query.prepare(d->m_db.m_tables["kmmPayees"].deleteString());
614 query.bindValue(":id", payee.id());
615 if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting Payee")); // krazy:exclude=crashy
616 --d->m_payees;
617
618 d->writeFileInfo();
619 }
620
621 // **** Tags ****
addTag(const MyMoneyTag & tag)622 void MyMoneyStorageSql::addTag(const MyMoneyTag& tag)
623 {
624 Q_D(MyMoneyStorageSql);
625 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
626 QSqlQuery q(*this);
627 q.prepare(d->m_db.m_tables["kmmTags"].insertString());
628 d->writeTag(tag, q);
629 ++d->m_tags;
630 d->writeFileInfo();
631 }
632
modifyTag(const MyMoneyTag & tag)633 void MyMoneyStorageSql::modifyTag(const MyMoneyTag& tag)
634 {
635 Q_D(MyMoneyStorageSql);
636 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
637 QSqlQuery q(*this);
638 q.prepare(d->m_db.m_tables["kmmTags"].updateString());
639 d->writeTag(tag, q);
640 d->writeFileInfo();
641 }
642
removeTag(const MyMoneyTag & tag)643 void MyMoneyStorageSql::removeTag(const MyMoneyTag& tag)
644 {
645 Q_D(MyMoneyStorageSql);
646 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
647 QSqlQuery query(*this);
648 query.prepare(d->m_db.m_tables["kmmTags"].deleteString());
649 query.bindValue(":id", tag.id());
650 if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting Tag")); // krazy:exclude=crashy
651 --d->m_tags;
652 d->writeFileInfo();
653 }
654
655 // **** Accounts ****
addAccount(const MyMoneyAccount & acc)656 void MyMoneyStorageSql::addAccount(const MyMoneyAccount& acc)
657 {
658 Q_D(MyMoneyStorageSql);
659 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
660 QSqlQuery q(*this);
661 q.prepare(d->m_db.m_tables["kmmAccounts"].insertString());
662 QList<MyMoneyAccount> aList;
663 aList << acc;
664 d->writeAccountList(aList, q);
665 ++d->m_accounts;
666 d->writeFileInfo();
667 }
668
modifyAccount(const MyMoneyAccount & acc)669 void MyMoneyStorageSql::modifyAccount(const MyMoneyAccount& acc)
670 {
671 QList<MyMoneyAccount> aList;
672 aList << acc;
673 modifyAccountList(aList);
674 }
675
modifyAccountList(const QList<MyMoneyAccount> & acc)676 void MyMoneyStorageSql::modifyAccountList(const QList<MyMoneyAccount>& acc)
677 {
678 Q_D(MyMoneyStorageSql);
679 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
680 QSqlQuery q(*this);
681 q.prepare(d->m_db.m_tables["kmmAccounts"].updateString());
682 QVariantList kvpList;
683 foreach (const MyMoneyAccount& a, acc) {
684 kvpList << a.id();
685 }
686 d->deleteKeyValuePairs("ACCOUNT", kvpList);
687 d->deleteKeyValuePairs("ONLINEBANKING", kvpList);
688 d->writeAccountList(acc, q);
689 d->writeFileInfo();
690 }
691
removeAccount(const MyMoneyAccount & acc)692 void MyMoneyStorageSql::removeAccount(const MyMoneyAccount& acc)
693 {
694 Q_D(MyMoneyStorageSql);
695 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
696 QVariantList kvpList;
697 kvpList << acc.id();
698 d->deleteKeyValuePairs("ACCOUNT", kvpList);
699 d->deleteKeyValuePairs("ONLINEBANKING", kvpList);
700 QSqlQuery query(*this);
701 query.prepare(d->m_db.m_tables["kmmAccounts"].deleteString());
702 query.bindValue(":id", acc.id());
703 if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting Account")); // krazy:exclude=crashy
704 --d->m_accounts;
705 d->writeFileInfo();
706 }
707
708
709 // **** Transactions and Splits ****
addTransaction(const MyMoneyTransaction & tx)710 void MyMoneyStorageSql::addTransaction(const MyMoneyTransaction& tx)
711 {
712 Q_D(MyMoneyStorageSql);
713 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
714 // add the transaction and splits
715 QSqlQuery q(*this);
716 q.prepare(d->m_db.m_tables["kmmTransactions"].insertString());
717 d->writeTransaction(tx.id(), tx, q, "N");
718 ++d->m_transactions;
719 QList<MyMoneyAccount> aList;
720 // for each split account, update lastMod date, balance, txCount
721 foreach (const MyMoneySplit& it_s, tx.splits()) {
722 MyMoneyAccount acc = d->m_storage->account(it_s.accountId());
723 ++d->m_transactionCountMap[acc.id()];
724 aList << acc;
725 }
726 modifyAccountList(aList);
727 // in the fileinfo record, update lastMod, txCount, next TxId
728 d->writeFileInfo();
729 }
730
modifyTransaction(const MyMoneyTransaction & tx)731 void MyMoneyStorageSql::modifyTransaction(const MyMoneyTransaction& tx)
732 {
733 Q_D(MyMoneyStorageSql);
734 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
735 // remove the splits of the old tx from the count table
736 QSqlQuery query(*this);
737 query.prepare("SELECT accountId FROM kmmSplits WHERE transactionId = :txId;");
738 query.bindValue(":txId", tx.id());
739 if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("retrieving old splits"));
740 while (query.next()) {
741 QString id = query.value(0).toString();
742 --d->m_transactionCountMap[id];
743 }
744 // add the transaction and splits
745 query.prepare(d->m_db.m_tables["kmmTransactions"].updateString());
746 d->writeTransaction(tx.id(), tx, query, "N");
747 QList<MyMoneyAccount> aList;
748 // for each split account, update lastMod date, balance, txCount
749 foreach (const MyMoneySplit& it_s, tx.splits()) {
750 MyMoneyAccount acc = d->m_storage->account(it_s.accountId());
751 ++d->m_transactionCountMap[acc.id()];
752 aList << acc;
753 }
754 modifyAccountList(aList);
755 //writeSplits(tx.id(), "N", tx.splits());
756 // in the fileinfo record, update lastMod
757 d->writeFileInfo();
758 }
759
removeTransaction(const MyMoneyTransaction & tx)760 void MyMoneyStorageSql::removeTransaction(const MyMoneyTransaction& tx)
761 {
762 Q_D(MyMoneyStorageSql);
763 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
764 d->deleteTransaction(tx.id());
765 --d->m_transactions;
766
767 QList<MyMoneyAccount> aList;
768 // for each split account, update lastMod date, balance, txCount
769 foreach (const MyMoneySplit& it_s, tx.splits()) {
770 MyMoneyAccount acc = d->m_storage->account(it_s.accountId());
771 --d->m_transactionCountMap[acc.id()];
772 aList << acc;
773 }
774 modifyAccountList(aList);
775 // in the fileinfo record, update lastModDate, txCount
776 d->writeFileInfo();
777 }
778
779 // **** Schedules ****
780
addSchedule(const MyMoneySchedule & sched)781 void MyMoneyStorageSql::addSchedule(const MyMoneySchedule& sched)
782 {
783 Q_D(MyMoneyStorageSql);
784 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
785 QSqlQuery q(*this);
786 q.prepare(d->m_db.m_tables["kmmSchedules"].insertString());
787 d->writeSchedule(sched, q, true);
788 ++d->m_schedules;
789 d->writeFileInfo();
790 }
791
modifySchedule(const MyMoneySchedule & sched)792 void MyMoneyStorageSql::modifySchedule(const MyMoneySchedule& sched)
793 {
794 Q_D(MyMoneyStorageSql);
795 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
796 QSqlQuery q(*this);
797 q.prepare(d->m_db.m_tables["kmmSchedules"].updateString());
798 d->writeSchedule(sched, q, false);
799 d->writeFileInfo();
800 }
801
removeSchedule(const MyMoneySchedule & sched)802 void MyMoneyStorageSql::removeSchedule(const MyMoneySchedule& sched)
803 {
804 Q_D(MyMoneyStorageSql);
805 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
806 d->deleteSchedule(sched.id());
807 --d->m_schedules;
808 d->writeFileInfo();
809 }
810
811 // **** Securities ****
addSecurity(const MyMoneySecurity & sec)812 void MyMoneyStorageSql::addSecurity(const MyMoneySecurity& sec)
813 {
814 Q_D(MyMoneyStorageSql);
815 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
816 QSqlQuery q(*this);
817 q.prepare(d->m_db.m_tables["kmmSecurities"].insertString());
818 d->writeSecurity(sec, q);
819 ++d->m_securities;
820 d->writeFileInfo();
821 }
822
modifySecurity(const MyMoneySecurity & sec)823 void MyMoneyStorageSql::modifySecurity(const MyMoneySecurity& sec)
824 {
825 Q_D(MyMoneyStorageSql);
826 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
827 QVariantList kvpList;
828 kvpList << sec.id();
829 d->deleteKeyValuePairs("SECURITY", kvpList);
830 QSqlQuery q(*this);
831 q.prepare(d->m_db.m_tables["kmmSecurities"].updateString());
832 d->writeSecurity(sec, q);
833 d->writeFileInfo();
834 }
835
removeSecurity(const MyMoneySecurity & sec)836 void MyMoneyStorageSql::removeSecurity(const MyMoneySecurity& sec)
837 {
838 Q_D(MyMoneyStorageSql);
839 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
840 QVariantList kvpList;
841 kvpList << sec.id();
842 d->deleteKeyValuePairs("SECURITY", kvpList);
843 QSqlQuery query(*this);
844 query.prepare(d->m_db.m_tables["kmmSecurities"].deleteString());
845 query.bindValue(":id", kvpList);
846 if (!query.execBatch()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting Security"));
847 --d->m_securities;
848 d->writeFileInfo();
849 }
850
851 // **** Prices ****
addPrice(const MyMoneyPrice & p)852 void MyMoneyStorageSql::addPrice(const MyMoneyPrice& p)
853 {
854 Q_D(MyMoneyStorageSql);
855 if (d->m_readingPrices) return;
856 // the app always calls addPrice, whether or not there is already one there
857 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
858 bool newRecord = false;
859 QSqlQuery query(*this);
860 QString s = d->m_db.m_tables["kmmPrices"].selectAllString(false);
861 s += " WHERE fromId = :fromId AND toId = :toId AND priceDate = :priceDate;";
862 query.prepare(s);
863 query.bindValue(":fromId", p.from());
864 query.bindValue(":toId", p.to());
865 query.bindValue(":priceDate", p.date().toString(Qt::ISODate));
866 if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("finding Price")); // krazy:exclude=crashy
867 if (query.next()) {
868 query.prepare(d->m_db.m_tables["kmmPrices"].updateString());
869 } else {
870 query.prepare(d->m_db.m_tables["kmmPrices"].insertString());
871 ++d->m_prices;
872 newRecord = true;
873 }
874 query.bindValue(":fromId", p.from());
875 query.bindValue(":toId", p.to());
876 query.bindValue(":priceDate", p.date().toString(Qt::ISODate));
877 query.bindValue(":price", p.rate(QString()).toString());
878 const MyMoneySecurity sec = d->m_storage->security(p.to());
879 query.bindValue(":priceFormatted",
880 p.rate(QString()).formatMoney("", sec.pricePrecision()));
881 query.bindValue(":priceSource", p.source());
882 if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("writing Price")); // krazy:exclude=crashy
883
884 if (newRecord) d->writeFileInfo();
885 }
886
removePrice(const MyMoneyPrice & p)887 void MyMoneyStorageSql::removePrice(const MyMoneyPrice& p)
888 {
889 Q_D(MyMoneyStorageSql);
890 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
891 QSqlQuery query(*this);
892 query.prepare(d->m_db.m_tables["kmmPrices"].deleteString());
893 query.bindValue(":fromId", p.from());
894 query.bindValue(":toId", p.to());
895 query.bindValue(":priceDate", p.date().toString(Qt::ISODate));
896 if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting Price")); // krazy:exclude=crashy
897 --d->m_prices;
898 d->writeFileInfo();
899 }
900
901 // **** Currencies ****
addCurrency(const MyMoneySecurity & sec)902 void MyMoneyStorageSql::addCurrency(const MyMoneySecurity& sec)
903 {
904 Q_D(MyMoneyStorageSql);
905 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
906 QSqlQuery q(*this);
907 q.prepare(d->m_db.m_tables["kmmCurrencies"].insertString());
908 d->writeCurrency(sec, q);
909 ++d->m_currencies;
910 d->writeFileInfo();
911 }
912
modifyCurrency(const MyMoneySecurity & sec)913 void MyMoneyStorageSql::modifyCurrency(const MyMoneySecurity& sec)
914 {
915 Q_D(MyMoneyStorageSql);
916 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
917 QSqlQuery q(*this);
918 q.prepare(d->m_db.m_tables["kmmCurrencies"].updateString());
919 d->writeCurrency(sec, q);
920 d->writeFileInfo();
921 }
922
removeCurrency(const MyMoneySecurity & sec)923 void MyMoneyStorageSql::removeCurrency(const MyMoneySecurity& sec)
924 {
925 Q_D(MyMoneyStorageSql);
926 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
927 QSqlQuery query(*this);
928 query.prepare(d->m_db.m_tables["kmmCurrencies"].deleteString());
929 query.bindValue(":ISOcode", sec.id());
930 if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting Currency")); // krazy:exclude=crashy
931 --d->m_currencies;
932 d->writeFileInfo();
933 }
934
addReport(const MyMoneyReport & rep)935 void MyMoneyStorageSql::addReport(const MyMoneyReport& rep)
936 {
937 Q_D(MyMoneyStorageSql);
938 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
939 QSqlQuery q(*this);
940 q.prepare(d->m_db.m_tables["kmmReportConfig"].insertString());
941 d->writeReport(rep, q);
942 ++d->m_reports;
943 d->writeFileInfo();
944 }
945
modifyReport(const MyMoneyReport & rep)946 void MyMoneyStorageSql::modifyReport(const MyMoneyReport& rep)
947 {
948 Q_D(MyMoneyStorageSql);
949 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
950 QSqlQuery q(*this);
951 q.prepare(d->m_db.m_tables["kmmReportConfig"].updateString());
952 d->writeReport(rep, q);
953 d->writeFileInfo();
954 }
955
removeReport(const MyMoneyReport & rep)956 void MyMoneyStorageSql::removeReport(const MyMoneyReport& rep)
957 {
958 Q_D(MyMoneyStorageSql);
959 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
960 QSqlQuery query(*this);
961 query.prepare("DELETE FROM kmmReportConfig WHERE id = :id");
962 query.bindValue(":id", rep.id());
963 if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting Report")); // krazy:exclude=crashy
964 --d->m_reports;
965 d->writeFileInfo();
966 }
967
addBudget(const MyMoneyBudget & bud)968 void MyMoneyStorageSql::addBudget(const MyMoneyBudget& bud)
969 {
970 Q_D(MyMoneyStorageSql);
971 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
972 QSqlQuery q(*this);
973 q.prepare(d->m_db.m_tables["kmmBudgetConfig"].insertString());
974 d->writeBudget(bud, q);
975 ++d->m_budgets;
976 d->writeFileInfo();
977 }
978
modifyBudget(const MyMoneyBudget & bud)979 void MyMoneyStorageSql::modifyBudget(const MyMoneyBudget& bud)
980 {
981 Q_D(MyMoneyStorageSql);
982 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
983 QSqlQuery q(*this);
984 q.prepare(d->m_db.m_tables["kmmBudgetConfig"].updateString());
985 d->writeBudget(bud, q);
986 d->writeFileInfo();
987 }
988
removeBudget(const MyMoneyBudget & bud)989 void MyMoneyStorageSql::removeBudget(const MyMoneyBudget& bud)
990 {
991 Q_D(MyMoneyStorageSql);
992 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
993 QSqlQuery query(*this);
994 query.prepare(d->m_db.m_tables["kmmBudgetConfig"].deleteString());
995 query.bindValue(":id", bud.id());
996 if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting Budget")); // krazy:exclude=crashy
997 --d->m_budgets;
998 d->writeFileInfo();
999 }
1000
addOnlineJob(const onlineJob & job)1001 void MyMoneyStorageSql::addOnlineJob(const onlineJob& job)
1002 {
1003 Q_D(MyMoneyStorageSql);
1004 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
1005 QSqlQuery query(*this);
1006 query.prepare("INSERT INTO kmmOnlineJobs (id, type, jobSend, bankAnswerDate, state, locked) VALUES(:id, :type, :jobSend, :bankAnswerDate, :state, :locked);");
1007 d->writeOnlineJob(job, query);
1008 if (!query.exec())
1009 throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("writing onlineJob")); // krazy:exclude=crashy
1010 ++d->m_onlineJobs;
1011
1012 try {
1013 // Save online task
1014 d->actOnOnlineJobInSQL(MyMoneyStorageSqlPrivate::SQLAction::Save, *job.constTask(), job.id());
1015 } catch (onlineJob::emptyTask&) {
1016 }
1017 }
1018
modifyOnlineJob(const onlineJob & job)1019 void MyMoneyStorageSql::modifyOnlineJob(const onlineJob& job)
1020 {
1021 Q_D(MyMoneyStorageSql);
1022 Q_ASSERT(!job.id().isEmpty());
1023
1024 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
1025 QSqlQuery query(*this);
1026 query.prepare(QLatin1String(
1027 "UPDATE kmmOnlineJobs SET "
1028 " type = :type, "
1029 " jobSend = :jobSend, "
1030 " bankAnswerDate = :bankAnswerDate, "
1031 " state = :state, "
1032 " locked = :locked "
1033 " WHERE id = :id"
1034 ));
1035
1036 d->writeOnlineJob(job, query);
1037 if (!query.exec())
1038 throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("writing onlineJob")); // krazy:exclude=crashy
1039
1040 try {
1041 // Modify online task
1042 d->actOnOnlineJobInSQL(MyMoneyStorageSqlPrivate::SQLAction::Modify, *job.constTask(), job.id());
1043 } catch (onlineJob::emptyTask&) {
1044 // If there is no task attached this is fine as well
1045 }
1046 }
1047
removeOnlineJob(const onlineJob & job)1048 void MyMoneyStorageSql::removeOnlineJob(const onlineJob& job)
1049 {
1050 Q_D(MyMoneyStorageSql);
1051 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
1052
1053 // Remove onlineTask first, because it could have a constraint
1054 // which could block the removal of the onlineJob
1055
1056 try {
1057 // Remove task
1058 d->actOnOnlineJobInSQL(MyMoneyStorageSqlPrivate::SQLAction::Remove, *job.constTask(), job.id());
1059 } catch (onlineJob::emptyTask&) {
1060 }
1061
1062 QSqlQuery query(*this);
1063 query.prepare(d->m_db.m_tables["kmmOnlineJobs"].deleteString());
1064 query.bindValue(":id", job.id());
1065 if (!query.exec())
1066 throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting onlineJob")); // krazy:exclude=crashy
1067 --d->m_onlineJobs;
1068 }
1069
addPayeeIdentifier(payeeIdentifier & ident)1070 void MyMoneyStorageSql::addPayeeIdentifier(payeeIdentifier& ident)
1071 {
1072 Q_D(MyMoneyStorageSql);
1073 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
1074
1075 ident = payeeIdentifier(incrementPayeeIdentfierId(), ident);
1076
1077 QSqlQuery q(*this);
1078 q.prepare("INSERT INTO kmmPayeeIdentifier (id, type) VALUES(:id, :type)");
1079 d->writePayeeIdentifier(ident, q);
1080 ++d->m_payeeIdentifier;
1081
1082 try {
1083 d->actOnPayeeIdentifierObjectInSQL(MyMoneyStorageSqlPrivate::SQLAction::Save, ident);
1084 } catch (const payeeIdentifier::empty &) {
1085 }
1086 }
1087
modifyPayeeIdentifier(const payeeIdentifier & ident)1088 void MyMoneyStorageSql::modifyPayeeIdentifier(const payeeIdentifier& ident)
1089 {
1090 Q_D(MyMoneyStorageSql);
1091 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
1092
1093 QSqlQuery query(*this);
1094 query.prepare("SELECT type FROM kmmPayeeIdentifier WHERE id = ?");
1095 query.bindValue(0, ident.idString());
1096 if (!query.exec() || !query.next())
1097 throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("modifying payeeIdentifier")); // krazy:exclude=crashy
1098
1099 bool typeChanged = (query.value(0).toString() != ident.iid());
1100
1101 if (typeChanged) {
1102 // Delete old identifier if type changed
1103 const payeeIdentifier oldIdent(fetchPayeeIdentifier(ident.idString()));
1104 try {
1105 d->actOnPayeeIdentifierObjectInSQL(MyMoneyStorageSqlPrivate::SQLAction::Modify, oldIdent);
1106 } catch (const payeeIdentifier::empty &) {
1107 // Note: this should not happen because the ui does not offer a way to change
1108 // the type of an payeeIdentifier if it was not correctly loaded.
1109 throw MYMONEYEXCEPTION((QString::fromLatin1("Could not modify payeeIdentifier '")
1110 + ident.idString()
1111 + QLatin1String("' because type changed and could not remove identifier of old type. Maybe a plugin is missing?"))
1112 ); // krazy:exclude=crashy
1113 }
1114 }
1115
1116 query.prepare("UPDATE kmmPayeeIdentifier SET type = :type WHERE id = :id");
1117 d->writePayeeIdentifier(ident, query);
1118
1119 try {
1120 if (typeChanged)
1121 d->actOnPayeeIdentifierObjectInSQL(MyMoneyStorageSqlPrivate::SQLAction::Save, ident);
1122 else
1123 d->actOnPayeeIdentifierObjectInSQL(MyMoneyStorageSqlPrivate::SQLAction::Modify, ident);
1124 } catch (const payeeIdentifier::empty &) {
1125 }
1126 }
1127
removePayeeIdentifier(const payeeIdentifier & ident)1128 void MyMoneyStorageSql::removePayeeIdentifier(const payeeIdentifier& ident)
1129 {
1130 Q_D(MyMoneyStorageSql);
1131 MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
1132
1133 // Remove first, the table could have a contraint which prevents removal
1134 // of row in kmmPayeeIdentifier
1135 try {
1136 d->actOnPayeeIdentifierObjectInSQL(MyMoneyStorageSqlPrivate::SQLAction::Remove, ident);
1137 } catch (const payeeIdentifier::empty &) {
1138 }
1139
1140 QSqlQuery query(*this);
1141 query.prepare(d->m_db.m_tables["kmmPayeeIdentifier"].deleteString());
1142 query.bindValue(":id", ident.idString());
1143 if (!query.exec())
1144 throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("deleting payeeIdentifier")); // krazy:exclude=crashy
1145 --d->m_payeeIdentifier;
1146 }
1147
1148 // **** Key/value pairs ****
1149
1150 //******************************** read SQL routines **************************************
1151
1152 /*void MyMoneyStorageSql::setVersion (const QString& version)
1153 {
1154 m_dbVersion = version.section('.', 0, 0).toUInt();
1155 m_minorVersion = version.section('.', 1, 1).toUInt();
1156 // Okay, I made a cockup by forgetting to include a fixversion in the database
1157 // design, so we'll use the minor version as fix level (similar to VERSION
1158 // and FIXVERSION in XML file format). A second mistake was setting minor version to 1
1159 // in the first place, so we need to subtract one on reading and add one on writing (sigh)!!
1160 m_storage->setFileFixVersion( m_minorVersion - 1);
1161 }*/
1162
1163
fetchInstitutions(const QStringList & idList,bool forUpdate) const1164 QMap<QString, MyMoneyInstitution> MyMoneyStorageSql::fetchInstitutions(const QStringList& idList, bool forUpdate) const
1165 {
1166 Q_D(const MyMoneyStorageSql);
1167 int institutionsNb = (idList.isEmpty() ? d->m_institutions : idList.size());
1168 d->signalProgress(0, institutionsNb, QObject::tr("Loading institutions..."));
1169 int progress = 0;
1170 QMap<QString, MyMoneyInstitution> iList;
1171 ulong lastId = 0;
1172 const MyMoneyDbTable& t = d->m_db.m_tables["kmmInstitutions"];
1173 QSqlQuery sq(*const_cast <MyMoneyStorageSql*>(this));
1174 sq.prepare("SELECT id FROM kmmAccounts WHERE institutionId = :id");
1175 QSqlQuery query(*const_cast <MyMoneyStorageSql*>(this));
1176 QString queryString(t.selectAllString(false));
1177
1178 // Use bind variables, instead of just inserting the values in the queryString,
1179 // so that values containing a ':' will work.
1180 if (! idList.empty()) {
1181 queryString += " WHERE";
1182 for (int i = 0; i < idList.count(); ++i)
1183 queryString += QString(" id = :id%1 OR").arg(i);
1184 queryString = queryString.left(queryString.length() - 2);
1185 }
1186 if (forUpdate)
1187 queryString += d->m_driver->forUpdateString();
1188
1189 queryString += ';';
1190
1191 query.prepare(queryString);
1192
1193 if (! idList.empty()) {
1194 QStringList::ConstIterator bindVal = idList.constBegin();
1195 for (int i = 0; bindVal != idList.constEnd(); ++i, ++bindVal) {
1196 query.bindValue(QString(":id%1").arg(i), *bindVal);
1197 }
1198 }
1199
1200 if (!query.exec()) throw MYMONEYEXCEPTION(d->buildError(query, Q_FUNC_INFO, QString::fromLatin1("reading Institution"))); // krazy:exclude=crashy
1201 int idCol = t.fieldNumber("id");
1202 int nameCol = t.fieldNumber("name");
1203 int managerCol = t.fieldNumber("manager");
1204 int routingCodeCol = t.fieldNumber("routingCode");
1205 int addressStreetCol = t.fieldNumber("addressStreet");
1206 int addressCityCol = t.fieldNumber("addressCity");
1207 int addressZipcodeCol = t.fieldNumber("addressZipcode");
1208 int telephoneCol = t.fieldNumber("telephone");
1209
1210 QStringList kvpInstitutionList(idList);
1211 while (query.next()) {
1212 MyMoneyInstitution inst;
1213 QString iid = GETSTRING(idCol);
1214 inst.setName(GETSTRING(nameCol));
1215 inst.setManager(GETSTRING(managerCol));
1216 inst.setSortcode(GETSTRING(routingCodeCol));
1217 inst.setStreet(GETSTRING(addressStreetCol));
1218 inst.setCity(GETSTRING(addressCityCol));
1219 inst.setPostcode(GETSTRING(addressZipcodeCol));
1220 inst.setTelephone(GETSTRING(telephoneCol));
1221 if (idList.isEmpty()) {
1222 kvpInstitutionList.append(iid);
1223 }
1224 // get list of subaccounts
1225 sq.bindValue(":id", iid);
1226 if (!sq.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading Institution AccountList")); // krazy:exclude=crashy
1227 QStringList aList;
1228 while (sq.next()) aList.append(sq.value(0).toString());
1229 foreach (const QString& it, aList)
1230 inst.addAccountId(it);
1231
1232 iList[iid] = MyMoneyInstitution(iid, inst);
1233 ulong id = MyMoneyUtils::extractId(iid);
1234 if (id > lastId)
1235 lastId = id;
1236
1237 d->signalProgress(++progress, 0);
1238 }
1239
1240 auto kvpResult = d->readKeyValuePairs("INSTITUTION", kvpInstitutionList);
1241 // old versions used to store the KVP with the false key (OFXSETTINGS)
1242 // but we load it nevertheless in case the new one is empty. During the
1243 // next write to the database, the key value pairs will be written with the
1244 // right id (INSTITUTION).
1245 auto kvpResultOld = d->readKeyValuePairs("OFXSETTINGS", kvpInstitutionList);
1246 if (kvpResult.isEmpty()) {
1247 kvpResult = kvpResultOld;
1248 }
1249 const auto kvp_end = kvpResult.constEnd();
1250 for (auto it_kvp = kvpResult.constBegin(); it_kvp != kvp_end; ++it_kvp) {
1251 iList[it_kvp.key()].setPairs(it_kvp.value().pairs());
1252 }
1253 return iList;
1254 }
1255
fetchInstitutions() const1256 QMap<QString, MyMoneyInstitution> MyMoneyStorageSql::fetchInstitutions() const
1257 {
1258 return fetchInstitutions(QStringList(), false);
1259 }
1260
readPayees(const QString & id)1261 void MyMoneyStorageSql::readPayees(const QString& id)
1262 {
1263 QList<QString> list;
1264 list.append(id);
1265 readPayees(list);
1266 }
1267
readPayees(const QList<QString> & pid)1268 void MyMoneyStorageSql::readPayees(const QList<QString>& pid)
1269 {
1270 Q_D(MyMoneyStorageSql);
1271 try {
1272 d->m_storage->loadPayees(fetchPayees(pid));
1273 } catch (const MyMoneyException &) {
1274 }
1275 // if (pid.isEmpty()) m_payeeListRead = true;
1276 }
1277
readPayees()1278 void MyMoneyStorageSql::readPayees()
1279 {
1280 readPayees(QList<QString>());
1281 }
1282
fetchPayees(const QStringList & idList,bool) const1283 QMap<QString, MyMoneyPayee> MyMoneyStorageSql::fetchPayees(const QStringList& idList, bool /*forUpdate*/) const
1284 {
1285 Q_D(const MyMoneyStorageSql);
1286 MyMoneyDbTransaction trans(const_cast <MyMoneyStorageSql&>(*this), Q_FUNC_INFO);
1287 if (d->m_displayStatus) {
1288 int payeesNb = (idList.isEmpty() ? d->m_payees : idList.size());
1289 d->signalProgress(0, payeesNb, QObject::tr("Loading payees..."));
1290 }
1291
1292 int progress = 0;
1293 QMap<QString, MyMoneyPayee> pList;
1294
1295 QSqlQuery query(*const_cast <MyMoneyStorageSql*>(this));
1296 QString queryString = QLatin1String("SELECT kmmPayees.id AS id, kmmPayees.name AS name, kmmPayees.reference AS reference, "
1297 " kmmPayees.email AS email, kmmPayees.addressStreet AS addressStreet, kmmPayees.addressCity AS addressCity, kmmPayees.addressZipcode AS addressZipcode, "
1298 " kmmPayees.addressState AS addressState, kmmPayees.telephone AS telephone, kmmPayees.notes AS notes, "
1299 " kmmPayees.defaultAccountId AS defaultAccountId, kmmPayees.matchData AS matchData, kmmPayees.matchIgnoreCase AS matchIgnoreCase, "
1300 " kmmPayees.matchKeys AS matchKeys, "
1301 " kmmPayeesPayeeIdentifier.identifierId AS identId "
1302 " FROM ( SELECT * FROM kmmPayees ");
1303
1304 if (!idList.isEmpty()) {
1305 // Create WHERE clause if needed
1306 queryString += QLatin1String(" WHERE id IN (");
1307 queryString += QString("?, ").repeated(idList.length());
1308 queryString.chop(2); // remove ", " from end
1309 queryString += QLatin1Char(')');
1310 }
1311
1312 queryString += QLatin1String(
1313 " ) kmmPayees "
1314 " LEFT OUTER JOIN kmmPayeesPayeeIdentifier ON kmmPayees.Id = kmmPayeesPayeeIdentifier.payeeId "
1315 // The order is used below
1316 " ORDER BY kmmPayees.id, kmmPayeesPayeeIdentifier.userOrder;");
1317
1318 query.prepare(queryString);
1319
1320 if (!idList.isEmpty()) {
1321 // Bind values
1322 QStringList::const_iterator end = idList.constEnd();
1323 for (QStringList::const_iterator iter = idList.constBegin(); iter != end; ++iter) {
1324 query.addBindValue(*iter);
1325 }
1326 }
1327
1328 if (!query.exec())
1329 throw MYMONEYEXCEPTION(d->buildError(query, Q_FUNC_INFO, QString::fromLatin1("reading Payee"))); // krazy:exclude=crashy
1330 const QSqlRecord record = query.record();
1331 const int idCol = record.indexOf("id");
1332 const int nameCol = record.indexOf("name");
1333 const int referenceCol = record.indexOf("reference");
1334 const int emailCol = record.indexOf("email");
1335 const int addressStreetCol = record.indexOf("addressStreet");
1336 const int addressCityCol = record.indexOf("addressCity");
1337 const int addressZipcodeCol = record.indexOf("addressZipcode");
1338 const int addressStateCol = record.indexOf("addressState");
1339 const int telephoneCol = record.indexOf("telephone");
1340 const int notesCol = record.indexOf("notes");
1341 const int defaultAccountIdCol = record.indexOf("defaultAccountId");
1342 const int matchDataCol = record.indexOf("matchData");
1343 const int matchIgnoreCaseCol = record.indexOf("matchIgnoreCase");
1344 const int matchKeysCol = record.indexOf("matchKeys");
1345 const int identIdCol = record.indexOf("identId");
1346
1347 if (query.next()) {
1348 while (query.isValid()) {
1349 QString pid;
1350 MyMoneyPayee payee;
1351 uint type;
1352 bool ignoreCase;
1353 QString matchKeys;
1354 pid = GETSTRING(idCol);
1355 payee.setName(GETSTRING(nameCol));
1356 payee.setReference(GETSTRING(referenceCol));
1357 payee.setEmail(GETSTRING(emailCol));
1358 payee.setAddress(GETSTRING(addressStreetCol));
1359 payee.setCity(GETSTRING(addressCityCol));
1360 payee.setPostcode(GETSTRING(addressZipcodeCol));
1361 payee.setState(GETSTRING(addressStateCol));
1362 payee.setTelephone(GETSTRING(telephoneCol));
1363 payee.setNotes(GETSTRING(notesCol));
1364 payee.setDefaultAccountId(GETSTRING(defaultAccountIdCol));
1365 type = GETINT(matchDataCol);
1366 ignoreCase = (GETSTRING(matchIgnoreCaseCol) == "Y");
1367 matchKeys = GETSTRING(matchKeysCol);
1368
1369 payee.setMatchData(static_cast<eMyMoney::Payee::MatchType>(type), ignoreCase, matchKeys);
1370
1371 // Get payeeIdentifier ids
1372 QStringList identifierIds;
1373 do {
1374 identifierIds.append(GETSTRING(identIdCol));
1375 } while (query.next() && GETSTRING(idCol) == pid); // as long as the payeeId is unchanged
1376
1377 // Fetch and save payeeIdentifier
1378 if (!identifierIds.isEmpty()) {
1379 QList< ::payeeIdentifier > identifier = fetchPayeeIdentifiers(identifierIds).values();
1380 payee.resetPayeeIdentifiers(identifier);
1381 }
1382
1383 if (pid == "USER")
1384 d->m_storage->setUser(payee);
1385 else
1386 pList[pid] = MyMoneyPayee(pid, payee);
1387
1388 if (d->m_displayStatus)
1389 d->signalProgress(++progress, 0);
1390 }
1391 }
1392 return pList;
1393 }
1394
fetchPayees() const1395 QMap<QString, MyMoneyPayee> MyMoneyStorageSql::fetchPayees() const
1396 {
1397 return fetchPayees(QStringList(), false);
1398 }
1399
readTags(const QString & id)1400 void MyMoneyStorageSql::readTags(const QString& id)
1401 {
1402 QList<QString> list;
1403 list.append(id);
1404 readTags(list);
1405 }
1406
readTags(const QList<QString> & pid)1407 void MyMoneyStorageSql::readTags(const QList<QString>& pid)
1408 {
1409 Q_D(MyMoneyStorageSql);
1410 try {
1411 d->m_storage->loadTags(fetchTags(pid));
1412 } catch (const MyMoneyException &) {
1413 }
1414 }
1415
readTags()1416 void MyMoneyStorageSql::readTags()
1417 {
1418 readTags(QList<QString>());
1419 }
1420
fetchOnlineJobs(const QStringList & idList,bool forUpdate) const1421 QMap<QString, onlineJob> MyMoneyStorageSql::fetchOnlineJobs(const QStringList& idList, bool forUpdate) const
1422 {
1423 Q_D(const MyMoneyStorageSql);
1424 Q_UNUSED(forUpdate);
1425 MyMoneyDbTransaction trans(const_cast <MyMoneyStorageSql&>(*this), Q_FUNC_INFO);
1426 if (d->m_displayStatus)
1427 d->signalProgress(0, idList.isEmpty() ? d->m_onlineJobs : idList.size(), QObject::tr("Loading online banking data..."));
1428
1429 // Create query
1430 QSqlQuery query(*const_cast <MyMoneyStorageSql*>(this));
1431 if (idList.isEmpty()) {
1432 query.prepare("SELECT id, type, jobSend, bankAnswerDate, state, locked FROM kmmOnlineJobs;");
1433 } else {
1434 QString queryIdSet = QString("?, ").repeated(idList.length());
1435 queryIdSet.chop(2);
1436 query.prepare(QLatin1String("SELECT id, type, jobSend, bankAnswerDate, state, locked FROM kmmOnlineJobs WHERE id IN (") + queryIdSet + QLatin1String(");"));
1437
1438 QStringList::const_iterator end = idList.constEnd();
1439 for (QStringList::const_iterator iter = idList.constBegin(); iter != end; ++iter) {
1440 query.addBindValue(*iter);
1441 }
1442 }
1443 if (!query.exec())
1444 throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading onlineJobs")); // krazy:exclude=crashy
1445
1446 // Create onlineJobs
1447 int progress = 0;
1448 QMap<QString, onlineJob> jobList;
1449
1450 while (query.next()) {
1451 const QString& id = query.value(0).toString();
1452 onlineTask *const task = d->createOnlineTaskObject(query.value(1).toString(), id, *this);
1453 onlineJob job = onlineJob(task, id);
1454 job.setJobSend(query.value(2).toDateTime());
1455 eMyMoney::OnlineJob::sendingState state;
1456 const QString stateString = query.value(4).toString();
1457 if (stateString == "acceptedByBank")
1458 state = eMyMoney::OnlineJob::sendingState::acceptedByBank;
1459 else if (stateString == "rejectedByBank")
1460 state = eMyMoney::OnlineJob::sendingState::rejectedByBank;
1461 else if (stateString == "abortedByUser")
1462 state = eMyMoney::OnlineJob::sendingState::abortedByUser;
1463 else if (stateString == "sendingError")
1464 state = eMyMoney::OnlineJob::sendingState::sendingError;
1465 else // includes: stateString == "noBankAnswer"
1466 state = eMyMoney::OnlineJob::sendingState::noBankAnswer;
1467
1468 job.setBankAnswer(state, query.value(3).toDateTime());
1469 job.setLock(query.value(5).toString() == QLatin1String("Y") ? true : false);
1470 jobList.insert(job.id(), job);
1471 if (d->m_displayStatus)
1472 d->signalProgress(++progress, 0);
1473 }
1474 return jobList;
1475 }
1476
fetchOnlineJobs() const1477 QMap<QString, onlineJob> MyMoneyStorageSql::fetchOnlineJobs() const
1478 {
1479 return fetchOnlineJobs(QStringList(), false);
1480 }
1481
fetchPayeeIdentifier(const QString & id) const1482 payeeIdentifier MyMoneyStorageSql::fetchPayeeIdentifier(const QString& id) const
1483 {
1484 QMap<QString, payeeIdentifier> list = fetchPayeeIdentifiers(QStringList(id));
1485 QMap<QString, payeeIdentifier>::const_iterator iter = list.constFind(id);
1486 if (iter == list.constEnd())
1487 throw MYMONEYEXCEPTION(QString::fromLatin1("payeeIdentifier with id '%1' not found").arg(id)); // krazy:exclude=crashy
1488 return *iter;
1489 }
1490
fetchPayeeIdentifiers(const QStringList & idList) const1491 QMap< QString, payeeIdentifier > MyMoneyStorageSql::fetchPayeeIdentifiers(const QStringList& idList) const
1492 {
1493 Q_D(const MyMoneyStorageSql);
1494 MyMoneyDbTransaction trans(const_cast <MyMoneyStorageSql&>(*this), Q_FUNC_INFO);
1495 // Create query
1496 QSqlQuery query(*const_cast <MyMoneyStorageSql*>(this));
1497 if (idList.isEmpty()) {
1498 query.prepare("SELECT id, type FROM kmmPayeeIdentifier;");
1499 } else {
1500 QString queryIdSet = QString("?, ").repeated(idList.length());
1501 queryIdSet.chop(2); // remove ", " from end
1502 query.prepare(QLatin1String("SELECT id, type FROM kmmPayeeIdentifier WHERE id IN (") + queryIdSet + QLatin1String(");"));
1503
1504 QStringList::const_iterator end = idList.constEnd();
1505 for (QStringList::const_iterator iter = idList.constBegin(); iter != end; ++iter) {
1506 query.addBindValue(*iter);
1507 }
1508 }
1509 if (!query.exec())
1510 throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading payee identifiers")); // krazy:exclude=crashy
1511
1512 QMap<QString, payeeIdentifier> identList;
1513
1514 while (query.next()) {
1515 const auto id = query.value(0).toString();
1516 identList.insert(id, d->createPayeeIdentifierObject(*this, query.value(1).toString(), id));
1517 }
1518
1519 return identList;
1520 }
1521
fetchPayeeIdentifiers() const1522 QMap< QString, payeeIdentifier > MyMoneyStorageSql::fetchPayeeIdentifiers() const
1523 {
1524 return fetchPayeeIdentifiers(QStringList());
1525 }
1526
fetchTags(const QStringList & idList,bool) const1527 QMap<QString, MyMoneyTag> MyMoneyStorageSql::fetchTags(const QStringList& idList, bool /*forUpdate*/) const
1528 {
1529 Q_D(const MyMoneyStorageSql);
1530 MyMoneyDbTransaction trans(const_cast <MyMoneyStorageSql&>(*this), Q_FUNC_INFO);
1531 if (d->m_displayStatus) {
1532 int tagsNb = (idList.isEmpty() ? d->m_tags : idList.size());
1533 d->signalProgress(0, tagsNb, QObject::tr("Loading tags..."));
1534 } else {
1535 // if (m_tagListRead) return;
1536 }
1537 int progress = 0;
1538 QMap<QString, MyMoneyTag> taList;
1539 //ulong lastId;
1540 const MyMoneyDbTable& t = d->m_db.m_tables["kmmTags"];
1541 QSqlQuery query(*const_cast <MyMoneyStorageSql*>(this));
1542 if (idList.isEmpty()) {
1543 query.prepare(t.selectAllString());
1544 } else {
1545 QString whereClause = " where (";
1546 QString itemConnector = "";
1547 foreach (const QString& it, idList) {
1548 whereClause.append(QString("%1id = '%2'").arg(itemConnector).arg(it));
1549 itemConnector = " or ";
1550 }
1551 whereClause += ')';
1552 query.prepare(t.selectAllString(false) + whereClause);
1553 }
1554 if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading Tag")); // krazy:exclude=crashy
1555 int idCol = t.fieldNumber("id");
1556 int nameCol = t.fieldNumber("name");
1557 int notesCol = t.fieldNumber("notes");
1558 int tagColorCol = t.fieldNumber("tagColor");
1559 int closedCol = t.fieldNumber("closed");
1560
1561 while (query.next()) {
1562 QString pid;
1563 MyMoneyTag tag;
1564 pid = GETSTRING(idCol);
1565 tag.setName(GETSTRING(nameCol));
1566 tag.setNotes(GETSTRING(notesCol));
1567 tag.setClosed((GETSTRING(closedCol) == "Y"));
1568 tag.setTagColor(QColor(GETSTRING(tagColorCol)));
1569 taList[pid] = MyMoneyTag(pid, tag);
1570 if (d->m_displayStatus) d->signalProgress(++progress, 0);
1571 }
1572 return taList;
1573 }
1574
fetchTags() const1575 QMap<QString, MyMoneyTag> MyMoneyStorageSql::fetchTags() const
1576 {
1577 return fetchTags(QStringList(), false);
1578 }
1579
fetchAccounts(const QStringList & idList,bool forUpdate) const1580 QMap<QString, MyMoneyAccount> MyMoneyStorageSql::fetchAccounts(const QStringList& idList, bool forUpdate) const
1581 {
1582 Q_D(const MyMoneyStorageSql);
1583 int accountsNb = (idList.isEmpty() ? d->m_accounts : idList.size());
1584 d->signalProgress(0, accountsNb, QObject::tr("Loading accounts..."));
1585 int progress = 0;
1586 QMap<QString, MyMoneyAccount> accList;
1587 QStringList kvpAccountList(idList);
1588
1589 const MyMoneyDbTable& t = d->m_db.m_tables["kmmAccounts"];
1590 QSqlQuery query(*const_cast <MyMoneyStorageSql*>(this));
1591 QSqlQuery sq(*const_cast <MyMoneyStorageSql*>(this));
1592
1593 QString childQueryString = "SELECT id, parentId FROM kmmAccounts WHERE ";
1594 QString queryString(t.selectAllString(false));
1595
1596 // Use bind variables, instead of just inserting the values in the queryString,
1597 // so that values containing a ':' will work.
1598 if (! idList.empty()) {
1599 queryString += " WHERE id IN (";
1600 childQueryString += " parentId IN (";
1601 QString inString;
1602 for (int i = 0; i < idList.count(); ++i) {
1603 inString += QString(":id%1, ").arg(i);
1604 }
1605 inString = inString.left(inString.length() - 2) + ')';
1606
1607 queryString += inString;
1608 childQueryString += inString;
1609 } else {
1610 childQueryString += " NOT parentId IS NULL";
1611 }
1612
1613 queryString += " ORDER BY id";
1614 childQueryString += " ORDER BY parentid, id";
1615
1616 if (forUpdate) {
1617 queryString += d->m_driver->forUpdateString();
1618 childQueryString += d->m_driver->forUpdateString();
1619 }
1620
1621 query.prepare(queryString);
1622 sq.prepare(childQueryString);
1623
1624 if (! idList.empty()) {
1625 QStringList::ConstIterator bindVal = idList.constBegin();
1626 for (int i = 0; bindVal != idList.constEnd(); ++i, ++bindVal) {
1627 query.bindValue(QString(":id%1").arg(i), *bindVal);
1628 sq.bindValue(QString(":id%1").arg(i), *bindVal);
1629 }
1630 }
1631
1632 if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading Account")); // krazy:exclude=crashy
1633 if (!sq.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading subAccountList")); // krazy:exclude=crashy
1634
1635 // Reserve enough space for all values. Approximate it with the size of the
1636 // idList in case the db doesn't support reporting the size of the
1637 // resultset to the caller.
1638 //FIXME: this is for if/when there is a QHash conversion
1639 //accList.reserve(q.size() > 0 ? q.size() : idList.size());
1640
1641 static const int idCol = t.fieldNumber("id");
1642 static const int institutionIdCol = t.fieldNumber("institutionId");
1643 static const int parentIdCol = t.fieldNumber("parentId");
1644 static const int lastReconciledCol = t.fieldNumber("lastReconciled");
1645 static const int lastModifiedCol = t.fieldNumber("lastModified");
1646 static const int openingDateCol = t.fieldNumber("openingDate");
1647 static const int accountNumberCol = t.fieldNumber("accountNumber");
1648 static const int accountTypeCol = t.fieldNumber("accountType");
1649 static const int accountNameCol = t.fieldNumber("accountName");
1650 static const int descriptionCol = t.fieldNumber("description");
1651 static const int currencyIdCol = t.fieldNumber("currencyId");
1652 static const int balanceCol = t.fieldNumber("balance");
1653 static const int transactionCountCol = t.fieldNumber("transactionCount");
1654
1655 while (query.next()) {
1656 QString aid;
1657 MyMoneyAccount acc;
1658
1659 aid = GETSTRING(idCol);
1660 acc.setInstitutionId(GETSTRING(institutionIdCol));
1661 acc.setParentAccountId(GETSTRING(parentIdCol));
1662 acc.setLastReconciliationDate(GETDATE_D(lastReconciledCol));
1663 acc.setLastModified(GETDATE_D(lastModifiedCol));
1664 acc.setOpeningDate(GETDATE_D(openingDateCol));
1665 acc.setNumber(GETSTRING(accountNumberCol));
1666 acc.setAccountType(static_cast<Account::Type>(GETINT(accountTypeCol)));
1667 acc.setName(GETSTRING(accountNameCol));
1668 acc.setDescription(GETSTRING(descriptionCol));
1669 acc.setCurrencyId(GETSTRING(currencyIdCol));
1670 acc.setBalance(MyMoneyMoney(GETSTRING(balanceCol)));
1671 const_cast <MyMoneyStorageSql*>(this)->d_func()->m_transactionCountMap[aid] = (ulong) GETULL(transactionCountCol);
1672
1673 // Process any key value pair
1674 if (idList.empty())
1675 kvpAccountList.append(aid);
1676
1677 accList.insert(aid, MyMoneyAccount(aid, acc));
1678 if (acc.value("PreferredAccount") == "Yes") {
1679 const_cast <MyMoneyStorageSql*>(this)->d_func()->m_preferred.addAccount(aid);
1680 }
1681 d->signalProgress(++progress, 0);
1682 }
1683
1684 QMap<QString, MyMoneyAccount>::Iterator it_acc;
1685 QMap<QString, MyMoneyAccount>::Iterator accListEnd = accList.end();
1686 while (sq.next()) {
1687 it_acc = accList.find(sq.value(1).toString());
1688 if (it_acc != accListEnd && it_acc.value().id() == sq.value(1).toString()) {
1689 while (sq.isValid() && it_acc != accListEnd
1690 && it_acc.value().id() == sq.value(1).toString()) {
1691 it_acc.value().addAccountId(sq.value(0).toString());
1692 if (!sq.next())
1693 break;
1694 }
1695 sq.previous();
1696 }
1697 }
1698
1699 //TODO: There should be a better way than this. What's below is O(n log n) or more,
1700 // where it may be able to be done in O(n), if things are just right.
1701 // The operator[] call in the loop is the most expensive call in this function, according
1702 // to several profile runs.
1703 QHash <QString, MyMoneyKeyValueContainer> kvpResult = d->readKeyValuePairs("ACCOUNT", kvpAccountList);
1704 QHash <QString, MyMoneyKeyValueContainer>::const_iterator kvp_end = kvpResult.constEnd();
1705 for (QHash <QString, MyMoneyKeyValueContainer>::const_iterator it_kvp = kvpResult.constBegin();
1706 it_kvp != kvp_end; ++it_kvp) {
1707 accList[it_kvp.key()].setPairs(it_kvp.value().pairs());
1708 }
1709
1710 kvpResult = d->readKeyValuePairs("ONLINEBANKING", kvpAccountList);
1711 kvp_end = kvpResult.constEnd();
1712 for (QHash <QString, MyMoneyKeyValueContainer>::const_iterator it_kvp = kvpResult.constBegin();
1713 it_kvp != kvp_end; ++it_kvp) {
1714 accList[it_kvp.key()].setOnlineBankingSettings(it_kvp.value());
1715 }
1716
1717 return accList;
1718 }
1719
fetchAccounts() const1720 QMap<QString, MyMoneyAccount> MyMoneyStorageSql::fetchAccounts() const
1721 {
1722 return fetchAccounts(QStringList(), false);
1723 }
1724
fetchBalance(const QStringList & idList,const QDate & date) const1725 QMap<QString, MyMoneyMoney> MyMoneyStorageSql::fetchBalance(const QStringList& idList, const QDate& date) const
1726 {
1727 Q_D(const MyMoneyStorageSql);
1728 QMap<QString, MyMoneyMoney> returnValue;
1729 QSqlQuery query(*const_cast <MyMoneyStorageSql*>(this));
1730 QString queryString = "SELECT action, shares, accountId, postDate "
1731 "FROM kmmSplits WHERE txType = 'N'";
1732
1733 if (idList.count() > 0) {
1734 queryString += "AND accountId in (";
1735
1736 for (int i = 0; i < idList.count(); ++i) {
1737 queryString += QString(":id%1, ").arg(i);
1738 }
1739 queryString = queryString.left(queryString.length() - 2) + ')';
1740 }
1741
1742 // SQLite stores dates as YYYY-MM-DDTHH:mm:ss with 0s for the time part. This makes
1743 // the <= operator misbehave when the date matches. To avoid this, add a day to the
1744 // requested date and use the < operator.
1745 if (date.isValid() && !date.isNull())
1746 queryString += QString(" AND postDate < '%1'").arg(date.addDays(1).toString(Qt::ISODate));
1747
1748 queryString += " ORDER BY accountId, postDate;";
1749 //DBG(queryString);
1750 query.prepare(queryString);
1751
1752 int i = 0;
1753 foreach (const QString& bindVal, idList) {
1754 query.bindValue(QString(":id%1").arg(i), bindVal);
1755 ++i;
1756 }
1757
1758 if (!query.exec()) // krazy:exclude=crashy
1759 throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("fetching balance"));
1760 QString id;
1761 QString oldId;
1762 MyMoneyMoney temp;
1763 while (query.next()) {
1764 id = query.value(2).toString();
1765 // If the old ID does not match the new ID, then the account being summed has changed.
1766 // Write the balance into the returnValue map and update the oldId to the current one.
1767 if (id != oldId) {
1768 if (!oldId.isEmpty()) {
1769 returnValue.insert(oldId, temp);
1770 temp = 0;
1771 }
1772 oldId = id;
1773 }
1774 if (MyMoneySplit::actionName(eMyMoney::Split::Action::SplitShares) == query.value(0).toString())
1775 temp *= MyMoneyMoney(query.value(1).toString());
1776 else
1777 temp += MyMoneyMoney(query.value(1).toString());
1778 }
1779 // Do not forget the last id in the list.
1780 returnValue.insert(id, temp);
1781
1782 // Return the map.
1783 return returnValue;
1784 }
1785
readTransactions(const MyMoneyTransactionFilter & filter)1786 void MyMoneyStorageSql::readTransactions(const MyMoneyTransactionFilter& filter)
1787 {
1788 Q_D(MyMoneyStorageSql);
1789 try {
1790 d->m_storage->loadTransactions(fetchTransactions(filter));
1791 } catch (const MyMoneyException &) {
1792 throw;
1793 }
1794 }
1795
fetchTransactions(const QString & tidList,const QString & dateClause,bool) const1796 QMap<QString, MyMoneyTransaction> MyMoneyStorageSql::fetchTransactions(const QString& tidList, const QString& dateClause, bool /*forUpdate*/) const
1797 {
1798 Q_D(const MyMoneyStorageSql);
1799 // if (m_transactionListRead) return; // all list already in memory
1800 if (d->m_displayStatus) {
1801 int transactionsNb = (tidList.isEmpty() ? d->m_transactions : tidList.size());
1802 d->signalProgress(0, transactionsNb, QObject::tr("Loading transactions..."));
1803 }
1804 int progress = 0;
1805 // m_payeeList.clear();
1806 QString whereClause = " WHERE txType = 'N' ";
1807 if (! tidList.isEmpty()) {
1808 whereClause += " AND id IN " + tidList;
1809 }
1810 if (!dateClause.isEmpty()) whereClause += " AND " + dateClause;
1811 const MyMoneyDbTable& t = d->m_db.m_tables["kmmTransactions"];
1812 QSqlQuery query(*const_cast <MyMoneyStorageSql*>(this));
1813 query.prepare(t.selectAllString(false) + whereClause + " ORDER BY id;");
1814 if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading Transaction")); // krazy:exclude=crashy
1815 const MyMoneyDbTable& ts = d->m_db.m_tables["kmmSplits"];
1816 whereClause = " WHERE txType = 'N' ";
1817 if (! tidList.isEmpty()) {
1818 whereClause += " AND transactionId IN " + tidList;
1819 }
1820 if (!dateClause.isEmpty()) whereClause += " AND " + dateClause;
1821 QSqlQuery qs(*const_cast <MyMoneyStorageSql*>(this));
1822 QString splitQuery = ts.selectAllString(false) + whereClause
1823 + " ORDER BY transactionId, splitId;";
1824 qs.prepare(splitQuery);
1825 if (!qs.exec()) throw MYMONEYEXCEPTION(d->buildError(qs, Q_FUNC_INFO, "reading Splits")); // krazy:exclude=crashy
1826 QString splitTxId = "ZZZ";
1827 MyMoneySplit s;
1828 if (qs.next()) {
1829 splitTxId = qs.value(0).toString();
1830 s = d->readSplit(qs);
1831 } else {
1832 splitTxId = "ZZZ";
1833 }
1834 QMap <QString, MyMoneyTransaction> txMap;
1835 QStringList txList;
1836 int idCol = t.fieldNumber("id");
1837 int postDateCol = t.fieldNumber("postDate");
1838 int memoCol = t.fieldNumber("memo");
1839 int entryDateCol = t.fieldNumber("entryDate");
1840 int currencyIdCol = t.fieldNumber("currencyId");
1841 int bankIdCol = t.fieldNumber("bankId");
1842
1843 while (query.next()) {
1844 MyMoneyTransaction tx;
1845 QString txId = GETSTRING(idCol);
1846 tx.setPostDate(GETDATE_D(postDateCol));
1847 tx.setMemo(GETSTRING(memoCol));
1848 tx.setEntryDate(GETDATE_D(entryDateCol));
1849 tx.setCommodity(GETSTRING(currencyIdCol));
1850 tx.setBankID(GETSTRING(bankIdCol));
1851
1852 // skip all splits while the transaction id of the split is less than
1853 // the transaction id of the current transaction. Don't forget to check
1854 // for the ZZZ flag for the end of the list.
1855 while (txId < splitTxId && splitTxId != "ZZZ") {
1856 if (qs.next()) {
1857 splitTxId = qs.value(0).toString();
1858 s = d->readSplit(qs);
1859 } else {
1860 splitTxId = "ZZZ";
1861 }
1862 }
1863
1864 // while the split transaction id matches the current transaction id,
1865 // add the split to the current transaction. Set the ZZZ flag if
1866 // all splits for this transaction have been read.
1867 while (txId == splitTxId) {
1868 tx.addSplit(s);
1869 if (qs.next()) {
1870 splitTxId = qs.value(0).toString();
1871 s = d->readSplit(qs);
1872 } else {
1873 splitTxId = "ZZZ";
1874 }
1875 }
1876
1877 // Process any key value pair
1878 if (! txId.isEmpty()) {
1879 txList.append(txId);
1880 tx = MyMoneyTransaction(txId, tx);
1881 txMap.insert(tx.uniqueSortKey(), tx);
1882 }
1883 }
1884
1885 // get the kvps
1886 QHash <QString, MyMoneyKeyValueContainer> kvpMap = d->readKeyValuePairs("TRANSACTION", txList);
1887 QMap<QString, MyMoneyTransaction>::Iterator txMapEnd = txMap.end();
1888 for (QMap<QString, MyMoneyTransaction>::Iterator i = txMap.begin();
1889 i != txMapEnd; ++i) {
1890 i.value().setPairs(kvpMap[i.value().id()].pairs());
1891
1892 if (d->m_displayStatus) d->signalProgress(++progress, 0);
1893 }
1894
1895 if ((tidList.isEmpty()) && (dateClause.isEmpty())) {
1896 //qDebug("setting full list read");
1897 }
1898 return txMap;
1899 }
1900
fetchTransactions(const QString & tidList) const1901 QMap<QString, MyMoneyTransaction> MyMoneyStorageSql::fetchTransactions(const QString& tidList) const
1902 {
1903 return fetchTransactions(tidList, QString(), false);
1904 }
1905
fetchTransactions() const1906 QMap<QString, MyMoneyTransaction> MyMoneyStorageSql::fetchTransactions() const
1907 {
1908 return fetchTransactions(QString(), QString(), false);
1909 }
1910
fetchTransactions(const MyMoneyTransactionFilter & filter) const1911 QMap<QString, MyMoneyTransaction> MyMoneyStorageSql::fetchTransactions(const MyMoneyTransactionFilter& filter) const
1912 {
1913 Q_D(const MyMoneyStorageSql);
1914 // analyze the filter
1915 // if (m_transactionListRead) return; // all list already in memory
1916 // if the filter is restricted to certain accounts/categories
1917 // check if we already have them all in memory
1918 QStringList accounts;
1919 QString inQuery;
1920 filter.accounts(accounts);
1921 filter.categories(accounts);
1922 // QStringList::iterator it;
1923 // bool allAccountsLoaded = true;
1924 // for (it = accounts.begin(); it != accounts.end(); ++it) {
1925 // if (m_accountsLoaded.find(*it) == m_accountsLoaded.end()) {
1926 // allAccountsLoaded = false;
1927 // break;
1928 // }
1929 // }
1930 // if (allAccountsLoaded) return;
1931 /* Some filter combinations do not lend themselves to implementation
1932 * in SQL, or are likely to require such extensive reading of the database
1933 * as to make it easier to just read everything into memory. */
1934 bool canImplementFilter = true;
1935 MyMoneyMoney m1, m2;
1936 if (filter.amountFilter(m1, m2)) {
1937 d->alert("Amount Filter Set");
1938 canImplementFilter = false;
1939 }
1940 QString n1, n2;
1941 if (filter.numberFilter(n1, n2)) {
1942 d->alert("Number filter set");
1943 canImplementFilter = false;
1944 }
1945 int t1;
1946 if (filter.firstType(t1)) {
1947 d->alert("Type filter set");
1948 canImplementFilter = false;
1949 }
1950 // int s1;
1951 // if (filter.firstState(s1)) {
1952 // alert("State filter set");
1953 // canImplementFilter = false;
1954 // }
1955 QRegExp t2;
1956 if (filter.textFilter(t2)) {
1957 d->alert("text filter set");
1958 canImplementFilter = false;
1959 }
1960 if (filter.filterSet().testFlag(MyMoneyTransactionFilter::validityFilterActive)) {
1961 d->alert("Validity filter set");
1962 canImplementFilter = false;
1963 }
1964 if (!canImplementFilter) {
1965 QMap<QString, MyMoneyTransaction> transactionList = fetchTransactions();
1966
1967 std::remove_if(transactionList.begin(), transactionList.end(), FilterFail(filter));
1968 return transactionList;
1969 }
1970
1971 bool splitFilterActive = false; // the split filter is active if we are selecting on fields in the split table
1972 // get start and end dates
1973 QDate start = filter.fromDate();
1974 QDate end = filter.toDate();
1975 // not entirely sure if the following is correct, but at best, saves a lot of reads, at worst
1976 // it only causes us to read a few more transactions that strictly necessary (I think...)
1977 if (start == MyMoneyStorageSqlPrivate::m_startDate) start = QDate();
1978 bool txFilterActive = ((start != QDate()) || (end != QDate())); // and this for fields in the transaction table
1979
1980 QString whereClause = "";
1981 QString subClauseconnector = " WHERE txType = 'N' AND ";
1982 // payees
1983 QStringList payees;
1984 if (filter.payees(payees)) {
1985 QString itemConnector = "payeeId IN (";
1986 QString payeesClause = "";
1987 foreach (const QString& it, payees) {
1988 payeesClause.append(QString("%1'%2'")
1989 .arg(itemConnector).arg(it));
1990 itemConnector = ", ";
1991 }
1992 if (!payeesClause.isEmpty()) {
1993 whereClause += subClauseconnector + payeesClause + ')';
1994 subClauseconnector = " AND ";
1995 }
1996 splitFilterActive = true;
1997 }
1998
1999 //tags
2000 QStringList tags;
2001 if (filter.tags(tags)) {
2002 QString itemConnector = "splitId IN ( SELECT splitId FROM kmmTagSplits WHERE kmmTagSplits.transactionId = kmmSplits.transactionId AND tagId IN (";
2003 QString tagsClause = "";
2004 foreach (const QString& it, tags) {
2005 tagsClause.append(QString("%1'%2'")
2006 .arg(itemConnector).arg(it));
2007 itemConnector = ", ";
2008 }
2009 if (!tagsClause.isEmpty()) {
2010 whereClause += subClauseconnector + tagsClause + ')';
2011 subClauseconnector = " AND ";
2012 }
2013 splitFilterActive = true;
2014 }
2015
2016 // accounts and categories
2017 if (!accounts.isEmpty()) {
2018 splitFilterActive = true;
2019 QString itemConnector = "accountId IN (";
2020 QString accountsClause = "";
2021 foreach (const QString& it, accounts) {
2022 accountsClause.append(QString("%1 '%2'")
2023 .arg(itemConnector).arg(it));
2024 itemConnector = ", ";
2025 }
2026 if (!accountsClause.isEmpty()) {
2027 whereClause += subClauseconnector + accountsClause + ')';
2028 subClauseconnector = " AND (";
2029 }
2030 }
2031
2032 // split states
2033 QList <int> splitStates;
2034 if (filter.states(splitStates)) {
2035 splitFilterActive = true;
2036 QString itemConnector = " reconcileFlag IN (";
2037 QString statesClause = "";
2038 foreach (int it, splitStates) {
2039 statesClause.append(QString(" %1 '%2'").arg(itemConnector)
2040 .arg(d->splitState(TransactionFilter::State(it))));
2041 itemConnector = ',';
2042 }
2043 if (!statesClause.isEmpty()) {
2044 whereClause += subClauseconnector + statesClause + ')';
2045 subClauseconnector = " AND (";
2046 }
2047 }
2048 // I've given up trying to work out the logic. we keep getting the wrong number of close brackets
2049 int obc = whereClause.count('(');
2050 int cbc = whereClause.count(')');
2051 if (cbc > obc) {
2052 qDebug() << "invalid where clause " << whereClause;
2053 qFatal("aborting");
2054 }
2055 while (cbc < obc) {
2056 whereClause.append(')');
2057 cbc++;
2058 }
2059 // if the split filter is active, but the where clause and the date filter is empty
2060 // it means we already have all the transactions for the specified filter
2061 // in memory, so just exit
2062 if ((splitFilterActive) && (whereClause.isEmpty()) && (!txFilterActive)) {
2063 qDebug("all transactions already in storage");
2064 return fetchTransactions();
2065 }
2066
2067 // if we have neither a split filter, nor a tx (date) filter
2068 // it's effectively a read all
2069 if ((!splitFilterActive) && (!txFilterActive)) {
2070 //qDebug("reading all transactions");
2071 return fetchTransactions();
2072 }
2073 // build a date clause for the transaction table
2074 QString dateClause;
2075 QString connector = "";
2076 if (end != QDate()) {
2077 dateClause = QString("(postDate < '%1')").arg(end.addDays(1).toString(Qt::ISODate));
2078 connector = " AND ";
2079 }
2080 if (start != QDate()) {
2081 dateClause += QString("%1 (postDate >= '%2')").arg(connector).arg(start.toString(Qt::ISODate));
2082 }
2083 // now get a list of transaction ids
2084 // if we have only a date filter, we need to build the list from the tx table
2085 // otherwise we need to build from the split table
2086 if (splitFilterActive) {
2087 inQuery = QString("(SELECT DISTINCT transactionId FROM kmmSplits %1)").arg(whereClause);
2088 } else {
2089 inQuery = QString("(SELECT DISTINCT id FROM kmmTransactions WHERE %1)").arg(dateClause);
2090 txFilterActive = false; // kill off the date filter now
2091 }
2092
2093 return fetchTransactions(inQuery, dateClause);
2094 //FIXME: if we have an accounts-only filter, recalc balances on loaded accounts
2095 }
2096
transactionCount(const QString & aid) const2097 ulong MyMoneyStorageSql::transactionCount(const QString& aid) const
2098 {
2099 Q_D(const MyMoneyStorageSql);
2100 if (aid.isEmpty())
2101 return d->m_transactions;
2102 else
2103 return d->m_transactionCountMap[aid];
2104 }
2105
transactionCountMap() const2106 QHash<QString, ulong> MyMoneyStorageSql::transactionCountMap() const
2107 {
2108 Q_D(const MyMoneyStorageSql);
2109 return d->m_transactionCountMap;
2110 }
2111
isReferencedByTransaction(const QString & id) const2112 bool MyMoneyStorageSql::isReferencedByTransaction(const QString& id) const
2113 {
2114 Q_D(const MyMoneyStorageSql);
2115 //FIXME-ALEX should I add sub query for kmmTagSplits here?
2116 QSqlQuery q(*const_cast <MyMoneyStorageSql*>(this));
2117 q.prepare("SELECT COUNT(*) FROM kmmTransactions "
2118 "INNER JOIN kmmSplits ON kmmTransactions.id = kmmSplits.transactionId "
2119 "WHERE kmmTransactions.currencyId = :ID OR kmmSplits.payeeId = :ID "
2120 "OR kmmSplits.accountId = :ID OR kmmSplits.costCenterId = :ID");
2121 q.bindValue(":ID", id);
2122 if ((!q.exec()) || (!q.next())) { // krazy:exclude=crashy
2123 d->buildError(q, Q_FUNC_INFO, "error retrieving reference count");
2124 qFatal("Error retrieving reference count"); // definitely shouldn't happen
2125 }
2126 return (0 != q.value(0).toULongLong());
2127 }
2128
fetchSchedules(const QStringList & idList,bool forUpdate) const2129 QMap<QString, MyMoneySchedule> MyMoneyStorageSql::fetchSchedules(const QStringList& idList, bool forUpdate) const
2130 {
2131 Q_D(const MyMoneyStorageSql);
2132 int schedulesNb = (idList.isEmpty() ? d->m_schedules : idList.size());
2133 d->signalProgress(0, schedulesNb, QObject::tr("Loading schedules..."));
2134 int progress = 0;
2135 const MyMoneyDbTable& t = d->m_db.m_tables["kmmSchedules"];
2136 QSqlQuery query(*const_cast <MyMoneyStorageSql*>(this));
2137 QMap<QString, MyMoneySchedule> sList;
2138 //ulong lastId = 0;
2139 const MyMoneyDbTable& ts = d->m_db.m_tables["kmmSplits"];
2140 QSqlQuery qs(*const_cast <MyMoneyStorageSql*>(this));
2141 qs.prepare(ts.selectAllString(false) + " WHERE transactionId = :id ORDER BY splitId;");
2142 QSqlQuery sq(*const_cast <MyMoneyStorageSql*>(this));
2143 sq.prepare("SELECT payDate FROM kmmSchedulePaymentHistory WHERE schedId = :id");
2144
2145 QString queryString(t.selectAllString(false));
2146
2147 // Use bind variables, instead of just inserting the values in the queryString,
2148 // so that values containing a ':' will work.
2149 if (! idList.empty()) {
2150 queryString += " WHERE";
2151 for (int i = 0; i < idList.count(); ++i)
2152 queryString += QString(" id = :id%1 OR").arg(i);
2153 queryString = queryString.left(queryString.length() - 2);
2154 }
2155 queryString += " ORDER BY id";
2156
2157 if (forUpdate)
2158 queryString += d->m_driver->forUpdateString();
2159
2160 query.prepare(queryString);
2161
2162 if (! idList.empty()) {
2163 QStringList::ConstIterator bindVal = idList.constBegin();
2164 for (int i = 0; bindVal != idList.constEnd(); ++i, ++bindVal) {
2165 query.bindValue(QString(":id%1").arg(i), *bindVal);
2166 }
2167 }
2168
2169 if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading Schedules")); // krazy:exclude=crashy
2170 int idCol = t.fieldNumber("id");
2171 int nameCol = t.fieldNumber("name");
2172 int typeCol = t.fieldNumber("type");
2173 int occurrenceCol = t.fieldNumber("occurence"); // krazy:exclude=spelling
2174 int occurrenceMultiplierCol = t.fieldNumber("occurenceMultiplier"); // krazy:exclude=spelling
2175 int paymentTypeCol = t.fieldNumber("paymentType");
2176 int startDateCol = t.fieldNumber("startDate");
2177 int endDateCol = t.fieldNumber("endDate");
2178 int fixedCol = t.fieldNumber("fixed");
2179 int lastDayInMonthCol = t.fieldNumber("lastDayInMonth");
2180 int autoEnterCol = t.fieldNumber("autoEnter");
2181 int lastPaymentCol = t.fieldNumber("lastPayment");
2182 int weekendOptionCol = t.fieldNumber("weekendOption");
2183 int nextPaymentDueCol = t.fieldNumber("nextPaymentDue");
2184
2185 while (query.next()) {
2186 MyMoneySchedule s;
2187 QString boolChar;
2188
2189 QString sId = GETSTRING(idCol);
2190 s.setName(GETSTRING(nameCol));
2191 s.setType(static_cast<Schedule::Type>(GETINT(typeCol)));
2192 s.setOccurrencePeriod(static_cast<Schedule::Occurrence>(GETINT(occurrenceCol)));
2193 s.setOccurrenceMultiplier(GETINT(occurrenceMultiplierCol));
2194 s.setPaymentType(static_cast<Schedule::PaymentType>(GETINT(paymentTypeCol)));
2195 s.setStartDate(GETDATE_D(startDateCol));
2196 s.setEndDate(GETDATE_D(endDateCol));
2197 boolChar = GETSTRING(fixedCol); s.setFixed(boolChar == "Y");
2198 boolChar = GETSTRING(lastDayInMonthCol); s.setLastDayInMonth(boolChar == "Y");
2199 boolChar = GETSTRING(autoEnterCol); s.setAutoEnter(boolChar == "Y");
2200 s.setLastPayment(GETDATE_D(lastPaymentCol));
2201 s.setWeekendOption(static_cast<Schedule::WeekendOption>(GETINT(weekendOptionCol)));
2202 QDate nextPaymentDue = GETDATE_D(nextPaymentDueCol);
2203
2204 // convert simple occurrence to compound occurrence
2205 int mult = s.occurrenceMultiplier();
2206 Schedule::Occurrence occ = s.occurrence();
2207 MyMoneySchedule::simpleToCompoundOccurrence(mult, occ);
2208 s.setOccurrencePeriod(occ);
2209 s.setOccurrenceMultiplier(mult);
2210 // now assign the id to the schedule
2211 MyMoneySchedule _s(sId, s);
2212 s = _s;
2213 // read the associated transaction
2214 // m_payeeList.clear();
2215
2216 const MyMoneyDbTable& transactionTable = d->m_db.m_tables["kmmTransactions"];
2217 QSqlQuery q(*const_cast <MyMoneyStorageSql*>(this));
2218 q.prepare(transactionTable.selectAllString(false) + " WHERE id = :id;");
2219 q.bindValue(":id", s.id());
2220 if (!q.exec()) throw MYMONEYEXCEPTION(d->buildError(q, Q_FUNC_INFO, QString("reading Scheduled Transaction"))); // krazy:exclude=crashy
2221 QSqlRecord rec = q.record();
2222 if (!q.next()) throw MYMONEYEXCEPTION(d->buildError(q, Q_FUNC_INFO, QString("retrieving scheduled transaction")));
2223 MyMoneyTransaction tx(s.id(), MyMoneyTransaction());
2224 // we cannot use the GET.... macros here as they are bound to the query variable
2225 tx.setPostDate(d->getDate(q.value(transactionTable.fieldNumber("postDate")).toString()));
2226 tx.setMemo(q.value(transactionTable.fieldNumber("memo")).toString());
2227 tx.setEntryDate(d->getDate(q.value(transactionTable.fieldNumber("entryDate")).toString()));
2228 tx.setCommodity(q.value(transactionTable.fieldNumber("currencyId")).toString());
2229 tx.setBankID(q.value(transactionTable.fieldNumber("bankId")).toString());
2230
2231 qs.bindValue(":id", s.id());
2232 if (!qs.exec()) throw MYMONEYEXCEPTION(d->buildError(qs, Q_FUNC_INFO, "reading Scheduled Splits")); // krazy:exclude=crashy
2233 while (qs.next()) {
2234 MyMoneySplit sp(d->readSplit(qs));
2235 tx.addSplit(sp);
2236 }
2237 // if (!m_payeeList.isEmpty())
2238 // readPayees(m_payeeList);
2239 // Process any key value pair
2240 tx.setPairs(d->readKeyValuePairs("TRANSACTION", s.id()).pairs());
2241
2242 // If the transaction doesn't have a post date, setTransaction will reject it.
2243 // The old way of handling things was to store the next post date in the schedule object
2244 // and set the transaction post date to QDate().
2245 // For compatibility, if this is the case, copy the next post date from the schedule object
2246 // to the transaction object post date.
2247 if (!tx.postDate().isValid()) {
2248 tx.setPostDate(nextPaymentDue);
2249 }
2250
2251 s.setTransaction(tx);
2252
2253 // read in the recorded payments
2254 sq.bindValue(":id", s.id());
2255 if (!sq.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading schedule payment history")); // krazy:exclude=crashy
2256 while (sq.next()) s.recordPayment(sq.value(0).toDate());
2257
2258 sList[s.id()] = s;
2259
2260 //FIXME: enable when schedules have KVPs.
2261 // s.setPairs(readKeyValuePairs("SCHEDULE", s.id()).pairs());
2262
2263 //ulong id = MyMoneyUtils::extractId(s.id().data());
2264 //if(id > lastId)
2265 // lastId = id;
2266
2267 d->signalProgress(++progress, 0);
2268 }
2269 return sList;
2270 }
2271
fetchSchedules() const2272 QMap<QString, MyMoneySchedule> MyMoneyStorageSql::fetchSchedules() const
2273 {
2274 return fetchSchedules(QStringList(), false);
2275 }
2276
fetchSecurities(const QStringList &,bool) const2277 QMap<QString, MyMoneySecurity> MyMoneyStorageSql::fetchSecurities(const QStringList& /*idList*/, bool /*forUpdate*/) const
2278 {
2279 Q_D(const MyMoneyStorageSql);
2280 d->signalProgress(0, d->m_securities, QObject::tr("Loading securities..."));
2281 int progress = 0;
2282 QMap<QString, MyMoneySecurity> sList;
2283 ulong lastId = 0;
2284 const MyMoneyDbTable& t = d->m_db.m_tables["kmmSecurities"];
2285 QSqlQuery query(*const_cast <MyMoneyStorageSql*>(this));
2286 query.prepare(t.selectAllString(false) + " ORDER BY id;");
2287 if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading Securities")); // krazy:exclude=crashy
2288 int idCol = t.fieldNumber("id");
2289 int nameCol = t.fieldNumber("name");
2290 int symbolCol = t.fieldNumber("symbol");
2291 int typeCol = t.fieldNumber("type");
2292 int roundingMethodCol = t.fieldNumber("roundingMethod");
2293 int smallestAccountFractionCol = t.fieldNumber("smallestAccountFraction");
2294 int pricePrecisionCol = t.fieldNumber("pricePrecision");
2295 int tradingCurrencyCol = t.fieldNumber("tradingCurrency");
2296 int tradingMarketCol = t.fieldNumber("tradingMarket");
2297
2298 while (query.next()) {
2299 MyMoneySecurity e;
2300 QString eid;
2301 eid = GETSTRING(idCol);
2302 e.setName(GETSTRING(nameCol));
2303 e.setTradingSymbol(GETSTRING(symbolCol));
2304 e.setSecurityType(static_cast<Security::Type>(GETINT(typeCol)));
2305 e.setRoundingMethod(static_cast<AlkValue::RoundingMethod>(GETINT(roundingMethodCol)));
2306 int saf = GETINT(smallestAccountFractionCol);
2307 int pp = GETINT(pricePrecisionCol);
2308 e.setTradingCurrency(GETSTRING(tradingCurrencyCol));
2309 e.setTradingMarket(GETSTRING(tradingMarketCol));
2310
2311 if (e.tradingCurrency().isEmpty())
2312 e.setTradingCurrency(d->m_storage->pairs()["kmm-baseCurrency"]);
2313 if (saf == 0)
2314 saf = 100;
2315 if (pp == 0 || pp > 10)
2316 pp = 4;
2317 e.setSmallestAccountFraction(saf);
2318 e.setPricePrecision(pp);
2319
2320 // Process any key value pairs
2321 e.setPairs(d->readKeyValuePairs("SECURITY", eid).pairs());
2322 //tell the storage objects we have a new security object.
2323
2324 // FIXME: Adapt to new interface make sure, to take care of the currencies as well
2325 // see MyMoneyStorageXML::readSecurites()
2326 MyMoneySecurity security(eid, e);
2327 sList[security.id()] = security;
2328
2329 ulong id = MyMoneyUtils::extractId(security.id());
2330 if (id > lastId)
2331 lastId = id;
2332
2333 d->signalProgress(++progress, 0);
2334 }
2335 return sList;
2336 }
2337
fetchSecurities() const2338 QMap<QString, MyMoneySecurity> MyMoneyStorageSql::fetchSecurities() const
2339 {
2340 return fetchSecurities(QStringList(), false);
2341 }
2342
fetchSinglePrice(const QString & fromId,const QString & toId,const QDate & date_,bool exactDate,bool) const2343 MyMoneyPrice MyMoneyStorageSql::fetchSinglePrice(const QString& fromId, const QString& toId, const QDate& date_, bool exactDate, bool /*forUpdate*/) const
2344 {
2345 Q_D(const MyMoneyStorageSql);
2346 const MyMoneyDbTable& t = d->m_db.m_tables["kmmPrices"];
2347
2348 static const int priceDateCol = t.fieldNumber("priceDate");
2349 static const int priceCol = t.fieldNumber("price");
2350 static const int priceSourceCol = t.fieldNumber("priceSource");
2351
2352 QSqlQuery query(*const_cast <MyMoneyStorageSql*>(this));
2353
2354 // Use bind variables, instead of just inserting the values in the queryString,
2355 // so that values containing a ':' will work.
2356 // See balance query for why the date logic seems odd.
2357 QString queryString = t.selectAllString(false) +
2358 " WHERE fromId = :fromId AND toId = :toId AND priceDate < :priceDate ";
2359
2360 if (exactDate)
2361 queryString += "AND priceDate > :exactDate ";
2362
2363 queryString += "ORDER BY priceDate DESC;";
2364
2365 query.prepare(queryString);
2366
2367 QDate date(date_);
2368
2369 if (!date.isValid())
2370 date = QDate::currentDate();
2371
2372 query.bindValue(":fromId", fromId);
2373 query.bindValue(":toId", toId);
2374 query.bindValue(":priceDate", date.addDays(1).toString(Qt::ISODate));
2375
2376 if (exactDate)
2377 query.bindValue(":exactDate", date.toString(Qt::ISODate));
2378
2379 if (! query.exec()) return MyMoneyPrice(); // krazy:exclude=crashy
2380
2381 if (query.next()) {
2382
2383 return MyMoneyPrice(fromId,
2384 toId,
2385 GETDATE_D(priceDateCol),
2386 MyMoneyMoney(GETSTRING(priceCol)),
2387 GETSTRING(priceSourceCol));
2388 }
2389
2390 return MyMoneyPrice();
2391 }
2392
fetchPrices(const QStringList & fromIdList,const QStringList & toIdList,bool forUpdate) const2393 MyMoneyPriceList MyMoneyStorageSql::fetchPrices(const QStringList& fromIdList, const QStringList& toIdList, bool forUpdate) const
2394 {
2395 Q_D(const MyMoneyStorageSql);
2396 int pricesNb = (fromIdList.isEmpty() ? d->m_prices : fromIdList.size());
2397 d->signalProgress(0, pricesNb, QObject::tr("Loading prices..."));
2398 int progress = 0;
2399 const_cast <MyMoneyStorageSql*>(this)->d_func()->m_readingPrices = true;
2400 MyMoneyPriceList pList;
2401 const MyMoneyDbTable& t = d->m_db.m_tables["kmmPrices"];
2402 QSqlQuery query(*const_cast <MyMoneyStorageSql*>(this));
2403 QString queryString = t.selectAllString(false);
2404
2405 // Use bind variables, instead of just inserting the values in the queryString,
2406 // so that values containing a ':' will work.
2407 if (! fromIdList.empty()) {
2408 queryString += " WHERE (";
2409 for (int i = 0; i < fromIdList.count(); ++i) {
2410 queryString += QString(" fromId = :fromId%1 OR").arg(i);
2411 }
2412 queryString = queryString.left(queryString.length() - 2) + ')';
2413 }
2414 if (! toIdList.empty()) {
2415 queryString += " AND (";
2416 for (int i = 0; i < toIdList.count(); ++i) {
2417 queryString += QString(" toId = :toId%1 OR").arg(i);
2418 }
2419 queryString = queryString.left(queryString.length() - 2) + ')';
2420 }
2421
2422 if (forUpdate)
2423 queryString += d->m_driver->forUpdateString();
2424
2425 queryString += ';';
2426
2427 query.prepare(queryString);
2428
2429 if (! fromIdList.empty()) {
2430 QStringList::ConstIterator bindVal = fromIdList.constBegin();
2431 for (int i = 0; bindVal != fromIdList.constEnd(); ++i, ++bindVal) {
2432 query.bindValue(QString(":fromId%1").arg(i), *bindVal);
2433 }
2434 }
2435 if (! toIdList.empty()) {
2436 QStringList::ConstIterator bindVal = toIdList.constBegin();
2437 for (int i = 0; bindVal != toIdList.constEnd(); ++i, ++bindVal) {
2438 query.bindValue(QString(":toId%1").arg(i), *bindVal);
2439 }
2440 }
2441
2442 if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading Prices")); // krazy:exclude=crashy
2443 static const int fromIdCol = t.fieldNumber("fromId");
2444 static const int toIdCol = t.fieldNumber("toId");
2445 static const int priceDateCol = t.fieldNumber("priceDate");
2446 static const int priceCol = t.fieldNumber("price");
2447 static const int priceSourceCol = t.fieldNumber("priceSource");
2448
2449 while (query.next()) {
2450 QString from = GETSTRING(fromIdCol);
2451 QString to = GETSTRING(toIdCol);
2452 QDate date = GETDATE_D(priceDateCol);
2453
2454 pList [MyMoneySecurityPair(from, to)].insert(date, MyMoneyPrice(from, to, date, MyMoneyMoney(GETSTRING(priceCol)), GETSTRING(priceSourceCol)));
2455 d->signalProgress(++progress, 0);
2456 }
2457 const_cast <MyMoneyStorageSql*>(this)->d_func()->m_readingPrices = false;
2458
2459 return pList;
2460 }
2461
fetchPrices() const2462 MyMoneyPriceList MyMoneyStorageSql::fetchPrices() const
2463 {
2464 return fetchPrices(QStringList(), QStringList(), false);
2465 }
2466
fetchCurrencies(const QStringList & idList,bool forUpdate) const2467 QMap<QString, MyMoneySecurity> MyMoneyStorageSql::fetchCurrencies(const QStringList& idList, bool forUpdate) const
2468 {
2469 Q_D(const MyMoneyStorageSql);
2470 int currenciesNb = (idList.isEmpty() ? d->m_currencies : idList.size());
2471 d->signalProgress(0, currenciesNb, QObject::tr("Loading currencies..."));
2472 int progress = 0;
2473 QMap<QString, MyMoneySecurity> cList;
2474 const MyMoneyDbTable& t = d->m_db.m_tables["kmmCurrencies"];
2475 QSqlQuery query(*const_cast <MyMoneyStorageSql*>(this));
2476
2477 QString queryString(t.selectAllString(false));
2478
2479 // Use bind variables, instead of just inserting the values in the queryString,
2480 // so that values containing a ':' will work.
2481 if (! idList.empty()) {
2482 queryString += " WHERE";
2483 for (int i = 0; i < idList.count(); ++i)
2484 queryString += QString(" isocode = :id%1 OR").arg(i);
2485 queryString = queryString.left(queryString.length() - 2);
2486 }
2487
2488 queryString += " ORDER BY ISOcode";
2489
2490 if (forUpdate)
2491 queryString += d->m_driver->forUpdateString();
2492
2493 queryString += ';';
2494
2495 query.prepare(queryString);
2496
2497 if (! idList.empty()) {
2498 QStringList::ConstIterator bindVal = idList.constBegin();
2499 for (int i = 0; bindVal != idList.end(); ++i, ++bindVal) {
2500 query.bindValue(QString(":id%1").arg(i), *bindVal);
2501 }
2502 }
2503
2504 if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading Currencies")); // krazy:exclude=crashy
2505 int ISOcodeCol = t.fieldNumber("ISOcode");
2506 int nameCol = t.fieldNumber("name");
2507 int typeCol = t.fieldNumber("type");
2508 int symbol1Col = t.fieldNumber("symbol1");
2509 int symbol2Col = t.fieldNumber("symbol2");
2510 int symbol3Col = t.fieldNumber("symbol3");
2511 int smallestCashFractionCol = t.fieldNumber("smallestCashFraction");
2512 int smallestAccountFractionCol = t.fieldNumber("smallestAccountFraction");
2513 int pricePrecisionCol = t.fieldNumber("pricePrecision");
2514
2515 while (query.next()) {
2516 QString id;
2517 MyMoneySecurity c;
2518 QChar symbol[3];
2519 id = GETSTRING(ISOcodeCol);
2520 c.setName(GETSTRING(nameCol));
2521 c.setSecurityType(static_cast<Security::Type>(GETINT(typeCol)));
2522 symbol[0] = QChar(GETINT(symbol1Col));
2523 symbol[1] = QChar(GETINT(symbol2Col));
2524 symbol[2] = QChar(GETINT(symbol3Col));
2525 c.setSmallestCashFraction(GETINT(smallestCashFractionCol));
2526 c.setSmallestAccountFraction(GETINT(smallestAccountFractionCol));
2527 c.setPricePrecision(GETINT(pricePrecisionCol));
2528 c.setTradingSymbol(QString(symbol, 3).trimmed());
2529
2530 cList[id] = MyMoneySecurity(id, c);
2531
2532 d->signalProgress(++progress, 0);
2533 }
2534 return cList;
2535 }
2536
fetchCurrencies() const2537 QMap<QString, MyMoneySecurity> MyMoneyStorageSql::fetchCurrencies() const
2538 {
2539 return fetchCurrencies(QStringList(), false);
2540 }
2541
fetchReports(const QStringList &,bool) const2542 QMap<QString, MyMoneyReport> MyMoneyStorageSql::fetchReports(const QStringList& /*idList*/, bool /*forUpdate*/) const
2543 {
2544 Q_D(const MyMoneyStorageSql);
2545 d->signalProgress(0, d->m_reports, QObject::tr("Loading reports..."));
2546 int progress = 0;
2547 const MyMoneyDbTable& t = d->m_db.m_tables["kmmReportConfig"];
2548 QSqlQuery query(*const_cast <MyMoneyStorageSql*>(this));
2549 query.prepare(t.selectAllString(true));
2550 if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading reports")); // krazy:exclude=crashy
2551 int xmlCol = t.fieldNumber("XML");
2552 QMap<QString, MyMoneyReport> rList;
2553 while (query.next()) {
2554 QDomDocument dom;
2555 dom.setContent(GETSTRING(xmlCol), false);
2556
2557 QDomNode child = dom.firstChild();
2558 child = child.firstChild();
2559 auto report = MyMoneyXmlContentHandler2::readReport(child.toElement());
2560 rList[report.id()] = report;
2561
2562 d->signalProgress(++progress, 0);
2563 }
2564 return rList;
2565 }
2566
fetchReports() const2567 QMap<QString, MyMoneyReport> MyMoneyStorageSql::fetchReports() const
2568 {
2569 return fetchReports(QStringList(), false);
2570 }
2571
fetchBudgets(const QStringList & idList,bool forUpdate) const2572 QMap<QString, MyMoneyBudget> MyMoneyStorageSql::fetchBudgets(const QStringList& idList, bool forUpdate) const
2573 {
2574 Q_D(const MyMoneyStorageSql);
2575 int budgetsNb = (idList.isEmpty() ? d->m_budgets : idList.size());
2576 d->signalProgress(0, budgetsNb, QObject::tr("Loading budgets..."));
2577 int progress = 0;
2578 const MyMoneyDbTable& t = d->m_db.m_tables["kmmBudgetConfig"];
2579 QSqlQuery query(*const_cast <MyMoneyStorageSql*>(this));
2580 QString queryString(t.selectAllString(false));
2581 if (! idList.empty()) {
2582 queryString += " WHERE id = '" + idList.join("' OR id = '") + '\'';
2583 }
2584 if (forUpdate)
2585 queryString += d->m_driver->forUpdateString();
2586
2587 queryString += ';';
2588
2589 query.prepare(queryString);
2590 if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading budgets")); // krazy:exclude=crashy
2591 QMap<QString, MyMoneyBudget> budgets;
2592 int xmlCol = t.fieldNumber("XML");
2593 while (query.next()) {
2594 QDomDocument dom;
2595 dom.setContent(GETSTRING(xmlCol), false);
2596
2597 QDomNode child = dom.firstChild();
2598 child = child.firstChild();
2599 auto budget = MyMoneyXmlContentHandler2::readBudget(child.toElement());
2600 budgets.insert(budget.id(), budget);
2601 d->signalProgress(++progress, 0);
2602 }
2603 return budgets;
2604 }
2605
fetchBudgets() const2606 QMap<QString, MyMoneyBudget> MyMoneyStorageSql::fetchBudgets() const
2607 {
2608 return fetchBudgets(QStringList(), false);
2609 }
2610
getNextBudgetId() const2611 ulong MyMoneyStorageSql::getNextBudgetId() const
2612 {
2613 Q_D(const MyMoneyStorageSql);
2614 return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdBudgets>(QLatin1String("kmmBudgetConfig"), QLatin1String("id"), 1);
2615 }
2616
getNextAccountId() const2617 ulong MyMoneyStorageSql::getNextAccountId() const
2618 {
2619 Q_D(const MyMoneyStorageSql);
2620 return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdAccounts>(QLatin1String("kmmAccounts"), QLatin1String("id"), 1);
2621 }
2622
getNextInstitutionId() const2623 ulong MyMoneyStorageSql::getNextInstitutionId() const
2624 {
2625 Q_D(const MyMoneyStorageSql);
2626 return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdInstitutions>(QLatin1String("kmmInstitutions"), QLatin1String("id"), 1);
2627 }
2628
getNextPayeeId() const2629 ulong MyMoneyStorageSql::getNextPayeeId() const
2630 {
2631 Q_D(const MyMoneyStorageSql);
2632 return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdPayees>(QLatin1String("kmmPayees"), QLatin1String("id"), 1);
2633 }
2634
getNextTagId() const2635 ulong MyMoneyStorageSql::getNextTagId() const
2636 {
2637 Q_D(const MyMoneyStorageSql);
2638 return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdTags>(QLatin1String("kmmTags"), QLatin1String("id"), 1);
2639 }
2640
getNextReportId() const2641 ulong MyMoneyStorageSql::getNextReportId() const
2642 {
2643 Q_D(const MyMoneyStorageSql);
2644 return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdReports>(QLatin1String("kmmReportConfig"), QLatin1String("id"), 1);
2645 }
2646
getNextScheduleId() const2647 ulong MyMoneyStorageSql::getNextScheduleId() const
2648 {
2649 Q_D(const MyMoneyStorageSql);
2650 return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdSchedules>(QLatin1String("kmmSchedules"), QLatin1String("id"), 3);
2651 }
2652
getNextSecurityId() const2653 ulong MyMoneyStorageSql::getNextSecurityId() const
2654 {
2655 Q_D(const MyMoneyStorageSql);
2656 return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdSecurities>(QLatin1String("kmmSecurities"), QLatin1String("id"), 1);
2657 }
2658
getNextTransactionId() const2659 ulong MyMoneyStorageSql::getNextTransactionId() const
2660 {
2661 Q_D(const MyMoneyStorageSql);
2662 return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdTransactions>(QLatin1String("kmmTransactions"), QLatin1String("id"), 1);
2663 }
2664
getNextOnlineJobId() const2665 ulong MyMoneyStorageSql::getNextOnlineJobId() const
2666 {
2667 Q_D(const MyMoneyStorageSql);
2668 return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdOnlineJobs>(QLatin1String("kmmOnlineJobs"), QLatin1String("id"), 1);
2669 }
2670
getNextPayeeIdentifierId() const2671 ulong MyMoneyStorageSql::getNextPayeeIdentifierId() const
2672 {
2673 Q_D(const MyMoneyStorageSql);
2674 return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdPayeeIdentifier>(QLatin1String("kmmPayeeIdentifier"), QLatin1String("id"), 5);
2675 }
2676
getNextCostCenterId() const2677 ulong MyMoneyStorageSql::getNextCostCenterId() const
2678 {
2679 Q_D(const MyMoneyStorageSql);
2680 return d->getNextId<&MyMoneyStorageSqlPrivate::m_hiIdCostCenter>(QLatin1String("kmmCostCenterIdentifier"), QLatin1String("id"), 5);
2681 }
2682
incrementBudgetId()2683 ulong MyMoneyStorageSql::incrementBudgetId()
2684 {
2685 Q_D(MyMoneyStorageSql);
2686 d->m_hiIdBudgets = getNextBudgetId() + 1;
2687 return (d->m_hiIdBudgets - 1);
2688 }
2689
2690 /**
2691 * @warning This method uses getNextAccountId() internally. The database is not informed which can cause issues
2692 * when the database is accessed concurrently. Then maybe a single id is used twice but the RDBMS will detect the
2693 * issue and KMyMoney crashes. This issue can only occur when two instances of KMyMoney access the same database.
2694 * But in this unlikly case MyMoneyStorageSql will have a lot more issues, I think.
2695 */
incrementAccountId()2696 ulong MyMoneyStorageSql::incrementAccountId()
2697 {
2698 Q_D(MyMoneyStorageSql);
2699 d->m_hiIdAccounts = getNextAccountId() + 1;
2700 return (d->m_hiIdAccounts - 1);
2701 }
2702
incrementInstitutionId()2703 ulong MyMoneyStorageSql::incrementInstitutionId()
2704 {
2705 Q_D(MyMoneyStorageSql);
2706 d->m_hiIdInstitutions = getNextInstitutionId() + 1;
2707 return (d->m_hiIdInstitutions - 1);
2708 }
2709
incrementPayeeId()2710 ulong MyMoneyStorageSql::incrementPayeeId()
2711 {
2712 Q_D(MyMoneyStorageSql);
2713 d->m_hiIdPayees = getNextPayeeId() + 1;
2714 return (d->m_hiIdPayees - 1);
2715 }
2716
incrementTagId()2717 ulong MyMoneyStorageSql::incrementTagId()
2718 {
2719 Q_D(MyMoneyStorageSql);
2720 d->m_hiIdTags = getNextTagId() + 1;
2721 return (d->m_hiIdTags - 1);
2722 }
2723
incrementReportId()2724 ulong MyMoneyStorageSql::incrementReportId()
2725 {
2726 Q_D(MyMoneyStorageSql);
2727 d->m_hiIdReports = getNextReportId() + 1;
2728 return (d->m_hiIdReports - 1);
2729 }
2730
incrementScheduleId()2731 ulong MyMoneyStorageSql::incrementScheduleId()
2732 {
2733 Q_D(MyMoneyStorageSql);
2734 d->m_hiIdSchedules = getNextScheduleId() + 1;
2735 return (d->m_hiIdSchedules - 1);
2736 }
2737
incrementSecurityId()2738 ulong MyMoneyStorageSql::incrementSecurityId()
2739 {
2740 Q_D(MyMoneyStorageSql);
2741 d->m_hiIdSecurities = getNextSecurityId() + 1;
2742 return (d->m_hiIdSecurities - 1);
2743 }
2744
incrementTransactionId()2745 ulong MyMoneyStorageSql::incrementTransactionId()
2746 {
2747 Q_D(MyMoneyStorageSql);
2748 d->m_hiIdTransactions = getNextTransactionId() + 1;
2749 return (d->m_hiIdTransactions - 1);
2750 }
2751
incrementOnlineJobId()2752 ulong MyMoneyStorageSql::incrementOnlineJobId()
2753 {
2754 Q_D(MyMoneyStorageSql);
2755 d->m_hiIdOnlineJobs = getNextOnlineJobId() + 1;
2756 return (d->m_hiIdOnlineJobs - 1);
2757 }
2758
incrementPayeeIdentfierId()2759 ulong MyMoneyStorageSql::incrementPayeeIdentfierId()
2760 {
2761 Q_D(MyMoneyStorageSql);
2762 d->m_hiIdPayeeIdentifier = getNextPayeeIdentifierId() + 1;
2763 return (d->m_hiIdPayeeIdentifier - 1);
2764 }
2765
incrementCostCenterId()2766 ulong MyMoneyStorageSql::incrementCostCenterId()
2767 {
2768 Q_D(MyMoneyStorageSql);
2769 d->m_hiIdCostCenter = getNextCostCenterId() + 1;
2770 return (d->m_hiIdCostCenter - 1);
2771 }
2772
loadAccountId(ulong id)2773 void MyMoneyStorageSql::loadAccountId(ulong id)
2774 {
2775 Q_D(MyMoneyStorageSql);
2776 d->m_hiIdAccounts = id;
2777 d->writeFileInfo();
2778 }
2779
loadTransactionId(ulong id)2780 void MyMoneyStorageSql::loadTransactionId(ulong id)
2781 {
2782 Q_D(MyMoneyStorageSql);
2783 d->m_hiIdTransactions = id;
2784 d->writeFileInfo();
2785 }
2786
loadPayeeId(ulong id)2787 void MyMoneyStorageSql::loadPayeeId(ulong id)
2788 {
2789 Q_D(MyMoneyStorageSql);
2790 d->m_hiIdPayees = id;
2791 d->writeFileInfo();
2792 }
2793
loadTagId(ulong id)2794 void MyMoneyStorageSql::loadTagId(ulong id)
2795 {
2796 Q_D(MyMoneyStorageSql);
2797 d->m_hiIdTags = id;
2798 d->writeFileInfo();
2799 }
2800
loadInstitutionId(ulong id)2801 void MyMoneyStorageSql::loadInstitutionId(ulong id)
2802 {
2803 Q_D(MyMoneyStorageSql);
2804 d->m_hiIdInstitutions = id;
2805 d->writeFileInfo();
2806 }
2807
loadScheduleId(ulong id)2808 void MyMoneyStorageSql::loadScheduleId(ulong id)
2809 {
2810 Q_D(MyMoneyStorageSql);
2811 d->m_hiIdSchedules = id;
2812 d->writeFileInfo();
2813 }
2814
loadSecurityId(ulong id)2815 void MyMoneyStorageSql::loadSecurityId(ulong id)
2816 {
2817 Q_D(MyMoneyStorageSql);
2818 d->m_hiIdSecurities = id;
2819 d->writeFileInfo();
2820 }
2821
loadReportId(ulong id)2822 void MyMoneyStorageSql::loadReportId(ulong id)
2823 {
2824 Q_D(MyMoneyStorageSql);
2825 d->m_hiIdReports = id;
2826 d->writeFileInfo();
2827 }
2828
loadBudgetId(ulong id)2829 void MyMoneyStorageSql::loadBudgetId(ulong id)
2830 {
2831 Q_D(MyMoneyStorageSql);
2832 d->m_hiIdBudgets = id;
2833 d->writeFileInfo();
2834 }
2835
loadOnlineJobId(ulong id)2836 void MyMoneyStorageSql::loadOnlineJobId(ulong id)
2837 {
2838 Q_D(MyMoneyStorageSql);
2839 d->m_hiIdOnlineJobs = id;
2840 d->writeFileInfo();
2841 }
2842
loadPayeeIdentifierId(ulong id)2843 void MyMoneyStorageSql::loadPayeeIdentifierId(ulong id)
2844 {
2845 Q_D(MyMoneyStorageSql);
2846 d->m_hiIdPayeeIdentifier = id;
2847 d->writeFileInfo();
2848 }
2849
2850 //****************************************************
2851
setProgressCallback(void (* callback)(int,int,const QString &))2852 void MyMoneyStorageSql::setProgressCallback(void(*callback)(int, int, const QString&))
2853 {
2854 Q_D(MyMoneyStorageSql);
2855 d->m_progressCallback = callback;
2856 }
2857
readFile(QIODevice * s,MyMoneyStorageMgr * storage)2858 void MyMoneyStorageSql::readFile(QIODevice* s, MyMoneyStorageMgr* storage)
2859 {
2860 Q_UNUSED(s); Q_UNUSED(storage)
2861 }
2862
writeFile(QIODevice * s,MyMoneyStorageMgr * storage)2863 void MyMoneyStorageSql::writeFile(QIODevice* s, MyMoneyStorageMgr* storage)
2864 {
2865 Q_UNUSED(s); Q_UNUSED(storage)
2866 }
2867
2868 // **************************** Error display routine *******************************
2869
2870
2871 QDate MyMoneyStorageSqlPrivate::m_startDate = QDate(1900, 1, 1);
2872
setStartDate(const QDate & startDate)2873 void MyMoneyStorageSql::setStartDate(const QDate& startDate)
2874 {
2875 MyMoneyStorageSqlPrivate::m_startDate = startDate;
2876 }
2877
fetchCostCenters(const QStringList & idList,bool forUpdate) const2878 QMap< QString, MyMoneyCostCenter > MyMoneyStorageSql::fetchCostCenters(const QStringList& idList, bool forUpdate) const
2879 {
2880 Q_D(const MyMoneyStorageSql);
2881 Q_UNUSED(forUpdate);
2882
2883 MyMoneyDbTransaction trans(const_cast <MyMoneyStorageSql&>(*this), Q_FUNC_INFO);
2884 if (d->m_displayStatus) {
2885 int costCenterNb = (idList.isEmpty() ? 100 : idList.size());
2886 d->signalProgress(0, costCenterNb, QObject::tr("Loading cost center..."));
2887 }
2888 int progress = 0;
2889 QMap<QString, MyMoneyCostCenter> costCenterList;
2890 //ulong lastId;
2891 const MyMoneyDbTable& t = d->m_db.m_tables["kmmCostCenter"];
2892 QSqlQuery query(*const_cast <MyMoneyStorageSql*>(this));
2893 if (idList.isEmpty()) {
2894 query.prepare(t.selectAllString());
2895 } else {
2896 QString whereClause = " where (";
2897 QString itemConnector = "";
2898 foreach (const QString& it, idList) {
2899 whereClause.append(QString("%1id = '%2'").arg(itemConnector).arg(it));
2900 itemConnector = " or ";
2901 }
2902 whereClause += ')';
2903 query.prepare(t.selectAllString(false) + whereClause);
2904 }
2905 if (!query.exec()) throw MYMONEYEXCEPTIONSQL_D(QString::fromLatin1("reading CostCenter")); // krazy:exclude=crashy
2906 const int idCol = t.fieldNumber("id");
2907 const int nameCol = t.fieldNumber("name");
2908
2909 while (query.next()) {
2910 MyMoneyCostCenter costCenter;
2911 QString pid = GETSTRING(idCol);
2912 costCenter.setName(GETSTRING(nameCol));
2913 costCenterList[pid] = MyMoneyCostCenter(pid, costCenter);
2914 if (d->m_displayStatus) d->signalProgress(++progress, 0);
2915 }
2916 return costCenterList;
2917 }
2918
fetchCostCenters() const2919 QMap< QString, MyMoneyCostCenter > MyMoneyStorageSql::fetchCostCenters() const
2920 {
2921 return fetchCostCenters(QStringList(), false);
2922 }
2923