Line data Source code
1 : /********************************************************************
2 : * gnc-sql-backend.cpp: Implementation of GncSqlBackend *
3 : * *
4 : * Copyright 2016 John Ralls <jralls@ceridwen.us> *
5 : * *
6 : * This program is free software; you can redistribute it and/or *
7 : * modify it under the terms of the GNU General Public License as *
8 : * published by the Free Software Foundation; either version 2 of *
9 : * the License, or (at your option) any later version. *
10 : * *
11 : * This program is distributed in the hope that it will be useful, *
12 : * but WITHOUT ANY WARRANTY; without even the implied warranty of *
13 : * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *
14 : * GNU General Public License for more details. *
15 : * *
16 : * You should have received a copy of the GNU General Public License*
17 : * along with this program; if not, contact: *
18 : * *
19 : * Free Software Foundation Voice: +1-617-542-5942 *
20 : * 51 Franklin Street, Fifth Floor Fax: +1-617-542-2652 *
21 : * Boston, MA 02110-1301, USA gnu@gnu.org *
22 : \********************************************************************/
23 : #include <config.h>
24 : #include <gnc-prefs.h>
25 : #include <gnc-engine.h>
26 : #include <gnc-commodity.h>
27 : #include <SX-book.h>
28 : #include <Recurrence.h>
29 : #include <gncBillTerm.h>
30 : #include <gncTaxTable.h>
31 : #include <gncInvoice.h>
32 : #include <gnc-pricedb.h>
33 : #include <TransLog.h>
34 :
35 : #include <algorithm>
36 : #include <cassert>
37 :
38 : #include "gnc-sql-connection.hpp"
39 : #include "gnc-sql-backend.hpp"
40 : #include "gnc-sql-object-backend.hpp"
41 : #include "gnc-sql-column-table-entry.hpp"
42 : #include "gnc-sql-result.hpp"
43 :
44 : #include "gnc-account-sql.h"
45 : #include "gnc-book-sql.h"
46 : #include "gnc-budget-sql.h"
47 : #include "gnc-commodity-sql.h"
48 : #include "gnc-lots-sql.h"
49 : #include "gnc-price-sql.h"
50 : #include "gnc-recurrence-sql.h"
51 : #include "gnc-schedxaction-sql.h"
52 : #include "gnc-slots-sql.h"
53 : #include "gnc-transaction-sql.h"
54 :
55 : #include "gnc-bill-term-sql.h"
56 : #include "gnc-customer-sql.h"
57 : #include "gnc-employee-sql.h"
58 : #include "gnc-entry-sql.h"
59 : #include "gnc-invoice-sql.h"
60 : #include "gnc-job-sql.h"
61 : #include "gnc-order-sql.h"
62 : #include "gnc-tax-table-sql.h"
63 : #include "gnc-vendor-sql.h"
64 :
65 : static QofLogModule log_module = G_LOG_DOMAIN;
66 : #define VERSION_TABLE_NAME "versions"
67 : #define MAX_TABLE_NAME_LEN 50
68 : #define TABLE_COL_NAME "table_name"
69 : #define VERSION_COL_NAME "table_version"
70 :
71 : using StrVec = std::vector<std::string>;
72 :
73 : static std::string empty_string{};
74 : static EntryVec version_table
75 : {
76 : gnc_sql_make_table_entry<CT_STRING>(
77 : TABLE_COL_NAME, MAX_TABLE_NAME_LEN, COL_PKEY | COL_NNUL),
78 : gnc_sql_make_table_entry<CT_INT>(VERSION_COL_NAME, 0, COL_NNUL)
79 : };
80 :
81 15 : GncSqlBackend::GncSqlBackend(GncSqlConnection *conn, QofBook* book) :
82 15 : QofBackend {}, m_conn{conn}, m_book{book}, m_loading{false},
83 15 : m_in_query{false}, m_is_pristine_db{false}
84 : {
85 15 : if (conn != nullptr)
86 1 : connect (conn);
87 15 : }
88 :
89 15 : GncSqlBackend::~GncSqlBackend()
90 : {
91 15 : connect(nullptr);
92 15 : }
93 :
94 : void
95 72 : GncSqlBackend::connect(GncSqlConnection *conn) noexcept
96 : {
97 72 : if (m_conn != nullptr && m_conn != conn)
98 15 : delete m_conn;
99 72 : finalize_version_info();
100 72 : m_conn = conn;
101 72 : }
102 :
103 : GncSqlStatementPtr
104 672 : GncSqlBackend::create_statement_from_sql(const std::string& str) const noexcept
105 : {
106 672 : auto stmt = m_conn ? m_conn->create_statement_from_sql(str) : nullptr;
107 672 : if (stmt == nullptr)
108 : {
109 0 : PERR ("SQL error: %s\n", str.c_str());
110 0 : qof_backend_set_error ((QofBackend*)this, ERR_BACKEND_SERVER_ERR);
111 : }
112 672 : return stmt;
113 : }
114 :
115 : GncSqlResultPtr
116 314 : GncSqlBackend::execute_select_statement(const GncSqlStatementPtr& stmt) const noexcept
117 : {
118 314 : auto result = m_conn ? m_conn->execute_select_statement(stmt) : nullptr;
119 314 : if (result == nullptr)
120 : {
121 0 : PERR ("SQL error: %s\n", stmt->to_sql());
122 0 : qof_backend_set_error ((QofBackend*)this, ERR_BACKEND_SERVER_ERR);
123 : }
124 314 : return result;
125 : }
126 :
127 : int
128 358 : GncSqlBackend::execute_nonselect_statement(const GncSqlStatementPtr& stmt) const noexcept
129 : {
130 358 : int result = m_conn ? m_conn->execute_nonselect_statement(stmt) : -1;
131 358 : if (result == -1)
132 : {
133 0 : PERR ("SQL error: %s\n", stmt->to_sql());
134 0 : qof_backend_set_error ((QofBackend*)this, ERR_BACKEND_SERVER_ERR);
135 : }
136 358 : return result;
137 : }
138 :
139 : std::string
140 0 : GncSqlBackend::quote_string(const std::string& str) const noexcept
141 : {
142 0 : g_return_val_if_fail (m_conn != nullptr, empty_string);
143 0 : if (!m_conn)
144 0 : return empty_string;
145 0 : return m_conn->quote_string(str);
146 : }
147 :
148 : bool
149 138 : GncSqlBackend::create_table(const std::string& table_name,
150 : const EntryVec& col_table) const noexcept
151 : {
152 138 : g_return_val_if_fail (m_conn != nullptr, false);
153 :
154 138 : ColVec info_vec;
155 :
156 1494 : for (auto const& table_row : col_table)
157 : {
158 1218 : table_row->add_to_table (info_vec);
159 : }
160 138 : return m_conn->create_table (table_name, info_vec);
161 :
162 138 : }
163 :
164 : bool
165 90 : GncSqlBackend::create_table(const std::string& table_name, int table_version,
166 : const EntryVec& col_table) noexcept
167 : {
168 90 : if (create_table (table_name, col_table))
169 90 : return set_table_version (table_name, table_version);
170 0 : return false;
171 : }
172 :
173 : bool
174 24 : GncSqlBackend::create_index(const std::string& index_name,
175 : const std::string& table_name,
176 : const EntryVec& col_table) const noexcept
177 : {
178 24 : g_return_val_if_fail (m_conn != nullptr, false);
179 24 : return m_conn->create_index(index_name, table_name, col_table);
180 : }
181 :
182 : bool
183 0 : GncSqlBackend::add_columns_to_table(const std::string& table_name,
184 : const EntryVec& col_table) const noexcept
185 : {
186 0 : g_return_val_if_fail (m_conn != nullptr, false);
187 :
188 0 : ColVec info_vec;
189 :
190 0 : for (auto const& table_row : col_table)
191 : {
192 0 : table_row->add_to_table (info_vec);
193 : }
194 0 : return m_conn->add_columns_to_table(table_name, info_vec);
195 0 : }
196 :
197 : void
198 440 : GncSqlBackend::update_progress(double pct) const noexcept
199 : {
200 440 : if (m_percentage != nullptr)
201 0 : (m_percentage) (nullptr, pct);
202 440 : }
203 :
204 : void
205 14 : GncSqlBackend::finish_progress() const noexcept
206 : {
207 14 : if (m_percentage != nullptr)
208 0 : (m_percentage) (nullptr, -1.0);
209 14 : }
210 :
211 : void
212 13 : GncSqlBackend::create_tables() noexcept
213 : {
214 546 : for(auto entry : m_backend_registry)
215 : {
216 260 : update_progress(101.0);
217 260 : std::get<1>(entry)->create_tables(this);
218 260 : }
219 13 : }
220 :
221 : /* Main object load order */
222 : static const StrVec fixed_load_order
223 : { GNC_ID_BOOK, GNC_ID_COMMODITY, GNC_ID_ACCOUNT, GNC_ID_LOT, GNC_ID_TRANS };
224 :
225 : /* Order in which business objects need to be loaded */
226 : static const StrVec business_fixed_load_order =
227 : { GNC_ID_BILLTERM, GNC_ID_TAXTABLE, GNC_ID_INVOICE };
228 :
229 : void
230 7 : GncSqlBackend::ObjectBackendRegistry::load_remaining(GncSqlBackend* sql_be)
231 : {
232 :
233 7 : auto num_types = m_registry.size();
234 7 : auto num_done = fixed_load_order.size() + business_fixed_load_order.size();
235 :
236 154 : for (const auto& entry : m_registry)
237 : {
238 140 : std::string type;
239 140 : GncSqlObjectBackendPtr obe = nullptr;
240 140 : std::tie(type, obe) = entry;
241 :
242 : /* Don't need to load anything if it has already been loaded with
243 : * the fixed order.
244 : */
245 140 : if (std::find(fixed_load_order.begin(), fixed_load_order.end(),
246 280 : type) != fixed_load_order.end()) continue;
247 91 : if (std::find(business_fixed_load_order.begin(),
248 : business_fixed_load_order.end(),
249 182 : type) != business_fixed_load_order.end()) continue;
250 :
251 70 : num_done++;
252 70 : sql_be->update_progress(num_done * 100 / num_types);
253 70 : obe->load_all (sql_be);
254 210 : }
255 7 : }
256 :
257 : typedef struct
258 : {
259 : QofIdType searchObj;
260 : gpointer pCompiledQuery;
261 : } gnc_sql_query_info;
262 :
263 : /* callback structure */
264 : typedef struct
265 : {
266 : gboolean is_known;
267 : gboolean is_ok;
268 : GncSqlBackend* sql_be;
269 : QofInstance* inst;
270 : QofQuery* pQuery;
271 : gpointer pCompiledQuery;
272 : gnc_sql_query_info* pQueryInfo;
273 : } sql_backend;
274 :
275 : static void
276 13 : scrub_txn_callback (QofInstance* inst, [[maybe_unused]] void* data)
277 : {
278 13 : auto trans = GNC_TRANSACTION(inst);
279 13 : xaccTransBeginEdit(trans);
280 13 : xaccTransCommitEdit(trans);
281 13 : }
282 :
283 : void
284 8 : GncSqlBackend::load (QofBook* book, QofBackendLoadType loadType)
285 : {
286 : Account* root;
287 :
288 8 : g_return_if_fail (book != NULL);
289 :
290 8 : ENTER ("sql_be=%p, book=%p", this, book);
291 :
292 8 : m_loading = TRUE;
293 :
294 8 : if (loadType == LOAD_TYPE_INITIAL_LOAD)
295 : {
296 7 : assert (m_book == nullptr);
297 7 : m_book = book;
298 :
299 7 : auto num_types = m_backend_registry.size();
300 7 : auto num_done = 0;
301 :
302 : /* Load any initial stuff. Some of this needs to happen in a certain order */
303 49 : for (const auto& type : fixed_load_order)
304 : {
305 35 : num_done++;
306 35 : auto obe = m_backend_registry.get_object_backend(type);
307 35 : if (obe)
308 : {
309 35 : update_progress(num_done * 100 / num_types);
310 35 : obe->load_all(this);
311 : }
312 35 : }
313 35 : for (const auto& type : business_fixed_load_order)
314 : {
315 21 : num_done++;
316 21 : auto obe = m_backend_registry.get_object_backend(type);
317 21 : if (obe)
318 : {
319 21 : update_progress(num_done * 100 / num_types);
320 21 : obe->load_all(this);
321 : }
322 21 : }
323 :
324 7 : root = gnc_book_get_root_account( book );
325 7 : gnc_account_foreach_descendant(root, (AccountCb)xaccAccountBeginEdit,
326 : nullptr);
327 :
328 7 : m_backend_registry.load_remaining(this);
329 :
330 7 : gnc_account_foreach_descendant(root, (AccountCb)xaccAccountCommitEdit,
331 : nullptr);
332 : }
333 1 : else if (loadType == LOAD_TYPE_LOAD_ALL)
334 : {
335 : // Load all transactions
336 2 : auto obe = m_backend_registry.get_object_backend (GNC_ID_TRANS);
337 1 : obe->load_all (this);
338 1 : }
339 :
340 8 : m_loading = FALSE;
341 8 : std::for_each(m_postload_commodities.begin(), m_postload_commodities.end(),
342 0 : [](gnc_commodity* comm) {
343 0 : gnc_commodity_begin_edit(comm);
344 0 : gnc_commodity_commit_edit(comm);
345 0 : });
346 8 : m_postload_commodities.clear();
347 : /* We deferred the transaction scrub while loading because having
348 : * m_loading true prevents changes from being written back to the
349 : * database. Do that now.
350 : */
351 8 : xaccLogDisable();
352 8 : auto transactions = qof_book_get_collection (book, GNC_ID_TRANS);
353 8 : qof_collection_foreach(transactions, scrub_txn_callback, nullptr);
354 8 : xaccLogEnable();
355 :
356 : /* Mark the session as clean -- though it should never be marked
357 : * dirty with this backend
358 : */
359 8 : qof_book_mark_session_saved (book);
360 8 : finish_progress();
361 :
362 8 : LEAVE ("");
363 : }
364 :
365 : /* ================================================================= */
366 :
367 : bool
368 12 : GncSqlBackend::write_account_tree(Account* root)
369 : {
370 : GList* descendants;
371 : GList* node;
372 12 : bool is_ok = true;
373 :
374 12 : g_return_val_if_fail (root != nullptr, false);
375 :
376 24 : auto obe = m_backend_registry.get_object_backend(GNC_ID_ACCOUNT);
377 12 : is_ok = obe->commit (this, QOF_INSTANCE (root));
378 12 : if (is_ok)
379 : {
380 12 : descendants = gnc_account_get_descendants (root);
381 64 : for (node = descendants; node != NULL && is_ok; node = g_list_next (node))
382 : {
383 26 : is_ok = obe->commit(this, QOF_INSTANCE (GNC_ACCOUNT (node->data)));
384 26 : if (!is_ok) break;
385 : }
386 12 : g_list_free (descendants);
387 : }
388 12 : update_progress(101.0);
389 :
390 12 : return is_ok;
391 12 : }
392 :
393 : bool
394 6 : GncSqlBackend::write_accounts()
395 : {
396 6 : update_progress(101.0);
397 6 : auto is_ok = write_account_tree (gnc_book_get_root_account (m_book));
398 6 : if (is_ok)
399 : {
400 6 : update_progress(101.0);
401 6 : is_ok = write_account_tree (gnc_book_get_template_root(m_book));
402 : }
403 :
404 6 : return is_ok;
405 : }
406 :
407 : static gboolean // Can't be bool because of signature for xaccAccountTreeForEach
408 11 : write_tx (Transaction* tx, gpointer data)
409 : {
410 11 : auto s = static_cast<write_objects_t*>(data);
411 :
412 11 : g_return_val_if_fail (tx != NULL, 0);
413 11 : g_return_val_if_fail (data != NULL, 0);
414 :
415 11 : s->commit (QOF_INSTANCE (tx));
416 22 : auto splitbe = s->be->get_object_backend(GNC_ID_SPLIT);
417 32 : for (auto split_node = xaccTransGetSplitList (tx);
418 32 : split_node != nullptr && s->is_ok;
419 21 : split_node = g_list_next (split_node))
420 : {
421 21 : s->is_ok = splitbe->commit(s->be, QOF_INSTANCE(split_node->data));
422 : }
423 11 : s->be->update_progress (101.0);
424 11 : return (s->is_ok ? 0 : 1);
425 11 : }
426 :
427 : bool
428 6 : GncSqlBackend::write_transactions()
429 : {
430 12 : auto obe = m_backend_registry.get_object_backend(GNC_ID_TRANS);
431 6 : write_objects_t data{this, TRUE, obe.get()};
432 :
433 6 : (void)xaccAccountTreeForEachTransaction (
434 : gnc_book_get_root_account (m_book), write_tx, &data);
435 6 : update_progress(101.0);
436 6 : return data.is_ok;
437 6 : }
438 :
439 : bool
440 6 : GncSqlBackend::write_template_transactions()
441 : {
442 12 : auto obe = m_backend_registry.get_object_backend(GNC_ID_TRANS);
443 6 : write_objects_t data{this, true, obe.get()};
444 6 : auto ra = gnc_book_get_template_root (m_book);
445 6 : if (gnc_account_n_descendants (ra) > 0)
446 : {
447 1 : (void)xaccAccountTreeForEachTransaction (ra, write_tx, &data);
448 1 : update_progress(101.0);
449 : }
450 :
451 6 : return data.is_ok;
452 6 : }
453 :
454 : bool
455 6 : GncSqlBackend::write_schedXactions()
456 : {
457 : GList* schedXactions;
458 : SchedXaction* tmpSX;
459 6 : bool is_ok = true;
460 :
461 6 : schedXactions = gnc_book_get_schedxactions (m_book)->sx_list;
462 12 : auto obe = m_backend_registry.get_object_backend(GNC_ID_SCHEDXACTION);
463 :
464 9 : for (; schedXactions != NULL && is_ok; schedXactions = schedXactions->next)
465 : {
466 3 : tmpSX = static_cast<decltype (tmpSX)> (schedXactions->data);
467 3 : is_ok = obe->commit (this, QOF_INSTANCE (tmpSX));
468 : }
469 6 : update_progress(101.0);
470 :
471 6 : return is_ok;
472 6 : }
473 :
474 : void
475 6 : GncSqlBackend::sync(QofBook* book)
476 : {
477 6 : g_return_if_fail (book != NULL);
478 6 : g_return_if_fail (m_conn != nullptr);
479 :
480 6 : reset_version_info();
481 6 : ENTER ("book=%p, sql_be->book=%p", book, m_book);
482 6 : update_progress(101.0);
483 :
484 : /* Create new tables */
485 6 : m_is_pristine_db = true;
486 6 : create_tables();
487 :
488 : /* Save all contents */
489 6 : m_book = book;
490 6 : auto is_ok = m_conn->begin_transaction();
491 :
492 : // FIXME: should write the set of commodities that are used
493 : // write_commodities(sql_be, book);
494 6 : if (is_ok)
495 : {
496 12 : auto obe = m_backend_registry.get_object_backend(GNC_ID_BOOK);
497 6 : is_ok = obe->commit (this, QOF_INSTANCE (book));
498 6 : }
499 6 : if (is_ok)
500 : {
501 6 : is_ok = write_accounts();
502 : }
503 6 : if (is_ok)
504 : {
505 6 : is_ok = write_transactions();
506 : }
507 6 : if (is_ok)
508 : {
509 6 : is_ok = write_template_transactions();
510 : }
511 6 : if (is_ok)
512 : {
513 6 : is_ok = write_schedXactions();
514 : }
515 6 : if (is_ok)
516 : {
517 252 : for (auto entry : m_backend_registry)
518 120 : std::get<1>(entry)->write (this);
519 : }
520 6 : if (is_ok)
521 : {
522 6 : is_ok = m_conn->commit_transaction();
523 : }
524 6 : if (is_ok)
525 : {
526 6 : m_is_pristine_db = false;
527 :
528 : /* Mark the session as clean -- though it shouldn't ever get
529 : * marked dirty with this backend
530 : */
531 6 : qof_book_mark_session_saved(book);
532 : }
533 : else
534 : {
535 0 : set_error (ERR_BACKEND_SERVER_ERR);
536 0 : m_conn->rollback_transaction ();
537 : }
538 6 : finish_progress();
539 6 : LEAVE ("book=%p", book);
540 : }
541 :
542 : /* ================================================================= */
543 : /* Routines to deal with the creation of multiple books. */
544 :
545 : void
546 449 : GncSqlBackend::begin(QofInstance* inst)
547 : {
548 : //g_return_if_fail (inst != NULL);
549 :
550 : //ENTER (" ");
551 : //LEAVE ("");
552 449 : }
553 :
554 : void
555 0 : GncSqlBackend::rollback(QofInstance* inst)
556 : {
557 : //g_return_if_fail (inst != NULL);
558 :
559 : //ENTER (" ");
560 : //LEAVE ("");
561 0 : }
562 :
563 : void
564 0 : GncSqlBackend::commodity_for_postload_processing(gnc_commodity* commodity)
565 : {
566 0 : m_postload_commodities.push_back(commodity);
567 0 : }
568 :
569 : GncSqlObjectBackendPtr
570 11 : GncSqlBackend::get_object_backend(const std::string& type) const noexcept
571 : {
572 11 : return m_backend_registry.get_object_backend(type);
573 : }
574 :
575 :
576 : /* Commit_edit handler - find the correct backend handler for this object
577 : * type and call its commit handler
578 : */
579 : void
580 476 : GncSqlBackend::commit (QofInstance* inst)
581 : {
582 : gboolean is_dirty;
583 : gboolean is_destroying;
584 : gboolean is_infant;
585 :
586 941 : g_return_if_fail (inst != NULL);
587 476 : g_return_if_fail (m_conn != nullptr);
588 :
589 476 : if (qof_book_is_readonly(m_book))
590 : {
591 0 : set_error (ERR_BACKEND_READONLY);
592 0 : (void)m_conn->rollback_transaction ();
593 0 : return;
594 : }
595 : /* During initial load where objects are being created, don't commit
596 : anything, but do mark the object as clean. */
597 476 : if (m_loading)
598 : {
599 445 : qof_instance_mark_clean (inst);
600 445 : return;
601 : }
602 :
603 : // The engine has a PriceDB object but it isn't in the database
604 31 : if (strcmp (inst->e_type, "PriceDB") == 0)
605 : {
606 0 : qof_instance_mark_clean (inst);
607 0 : qof_book_mark_session_saved (m_book);
608 0 : return;
609 : }
610 :
611 31 : ENTER (" ");
612 :
613 31 : is_dirty = qof_instance_get_dirty_flag (inst);
614 31 : is_destroying = qof_instance_get_destroying (inst);
615 31 : is_infant = qof_instance_get_infant (inst);
616 :
617 31 : DEBUG ("%s dirty = %d, do_free = %d, infant = %d\n",
618 : (inst->e_type ? inst->e_type : "(null)"),
619 : is_dirty, is_destroying, is_infant);
620 :
621 31 : if (!is_dirty && !is_destroying)
622 : {
623 19 : LEAVE ("!dirty OR !destroying");
624 19 : return;
625 : }
626 :
627 12 : if (!m_conn->begin_transaction ())
628 : {
629 0 : PERR ("begin_transaction failed\n");
630 0 : LEAVE ("Rolled back - database transaction begin error");
631 0 : return;
632 : }
633 :
634 12 : bool is_ok = true;
635 :
636 24 : auto obe = m_backend_registry.get_object_backend(std::string{inst->e_type});
637 12 : if (obe != nullptr)
638 11 : is_ok = obe->commit(this, inst);
639 : else
640 : {
641 1 : PERR ("Unknown object type '%s'\n", inst->e_type);
642 1 : (void)m_conn->rollback_transaction ();
643 :
644 : // Don't let unknown items still mark the book as being dirty
645 1 : qof_book_mark_session_saved(m_book);
646 1 : qof_instance_mark_clean (inst);
647 1 : LEAVE ("Rolled back - unknown object type");
648 1 : return;
649 : }
650 11 : if (!is_ok)
651 : {
652 : // Error - roll it back
653 0 : (void)m_conn->rollback_transaction();
654 :
655 : // This *should* leave things marked dirty
656 0 : LEAVE ("Rolled back - database error");
657 0 : return;
658 : }
659 :
660 11 : (void)m_conn->commit_transaction ();
661 :
662 11 : qof_book_mark_session_saved(m_book);
663 11 : qof_instance_mark_clean (inst);
664 :
665 11 : LEAVE ("");
666 12 : }
667 :
668 :
669 : /**
670 : * Sees if the version table exists, and if it does, loads the info into
671 : * the version hash table. Otherwise, it creates an empty version table.
672 : *
673 : * @param be Backend struct
674 : */
675 : void
676 7 : GncSqlBackend::init_version_info() noexcept
677 : {
678 7 : g_return_if_fail (m_conn != nullptr);
679 21 : if (m_conn->does_table_exist (VERSION_TABLE_NAME))
680 : {
681 7 : std::string sql {"SELECT * FROM "};
682 7 : sql += VERSION_TABLE_NAME;
683 7 : auto stmt = m_conn->create_statement_from_sql(sql);
684 7 : auto result = m_conn->execute_select_statement (stmt);
685 175 : for (const auto& row : *result)
686 : {
687 168 : auto name = row.get_string_at_col (TABLE_COL_NAME);
688 168 : auto version = row.get_int_at_col (VERSION_COL_NAME);
689 168 : if (name && version)
690 168 : m_versions.push_back(std::make_pair(*name, static_cast<unsigned int>(*version)));
691 175 : }
692 7 : }
693 : else
694 : {
695 0 : create_table (VERSION_TABLE_NAME, version_table);
696 0 : set_table_version("Gnucash", gnc_prefs_get_long_version ());
697 0 : set_table_version("Gnucash-Resave", GNUCASH_RESAVE_VERSION);
698 : }
699 : }
700 :
701 : /**
702 : * Resets the version table information by removing all version table info.
703 : * It also recreates the version table in the db.
704 : *
705 : * @param be Backend struct
706 : * @return TRUE if successful, FALSE if error
707 : */
708 : bool
709 6 : GncSqlBackend::reset_version_info() noexcept
710 : {
711 6 : bool ok = create_table (VERSION_TABLE_NAME, version_table);
712 6 : m_versions.clear();
713 18 : set_table_version ("Gnucash", gnc_prefs_get_long_version ());
714 6 : set_table_version ("Gnucash-Resave", GNUCASH_RESAVE_VERSION);
715 6 : return ok;
716 : }
717 :
718 : /**
719 : * Finalizes the version table info by destroying the hash table.
720 : *
721 : * @param be Backend struct
722 : */
723 : void
724 100 : GncSqlBackend::finalize_version_info() noexcept
725 : {
726 100 : m_versions.clear();
727 100 : }
728 :
729 : unsigned int
730 301 : GncSqlBackend::get_table_version(const std::string& table_name) const noexcept
731 : {
732 : /* If the db is pristine because it's being saved, the table does not exist. */
733 301 : if (m_is_pristine_db)
734 132 : return 0;
735 :
736 169 : auto version = std::find_if(m_versions.begin(), m_versions.end(),
737 338 : [table_name](const VersionPair& version) {
738 2101 : return version.first == table_name; });
739 338 : if (version != m_versions.end())
740 169 : return version->second;
741 0 : return 0;
742 : }
743 :
744 : /**
745 : * Registers the version for a table. Registering involves updating the
746 : * db version table and also the hash table.
747 : *
748 : * @param be Backend struct
749 : * @param table_name Table name
750 : * @param version Version number
751 : * @return TRUE if successful, FALSE if unsuccessful
752 : */
753 : bool
754 148 : GncSqlBackend::set_table_version (const std::string& table_name,
755 : uint_t version) noexcept
756 : {
757 148 : g_return_val_if_fail (version > 0, false);
758 :
759 148 : unsigned int cur_version{0};
760 148 : std::stringstream sql;
761 148 : auto ver_entry = std::find_if(m_versions.begin(), m_versions.end(),
762 296 : [table_name](const VersionPair& ver) {
763 1662 : return ver.first == table_name; });
764 296 : if (ver_entry != m_versions.end())
765 4 : cur_version = ver_entry->second;
766 148 : if (cur_version != version)
767 : {
768 148 : if (cur_version == 0)
769 : {
770 : sql << "INSERT INTO " << VERSION_TABLE_NAME << " VALUES('" <<
771 144 : table_name << "'," << version <<")";
772 144 : m_versions.push_back(std::make_pair(table_name, version));
773 : }
774 : else
775 : {
776 : sql << "UPDATE " << VERSION_TABLE_NAME << " SET " <<
777 4 : VERSION_COL_NAME << "=" << version << " WHERE " <<
778 4 : TABLE_COL_NAME << "='" << table_name << "'";
779 8 : ver_entry->second = version;
780 : }
781 148 : auto stmt = create_statement_from_sql(sql.str());
782 148 : auto status = execute_nonselect_statement (stmt);
783 148 : if (status == -1)
784 : {
785 0 : PERR ("SQL error: %s\n", sql.str().c_str());
786 0 : qof_backend_set_error ((QofBackend*)this, ERR_BACKEND_SERVER_ERR);
787 0 : return false;
788 : }
789 148 : }
790 :
791 148 : return true;
792 148 : }
793 :
794 : void
795 0 : GncSqlBackend::upgrade_table (const std::string& table_name,
796 : const EntryVec& col_table) noexcept
797 : {
798 0 : DEBUG ("Upgrading %s table\n", table_name.c_str());
799 :
800 0 : auto temp_table_name = table_name + "_new";
801 0 : create_table (temp_table_name, col_table);
802 0 : std::stringstream sql;
803 0 : sql << "INSERT INTO " << temp_table_name << " SELECT * FROM " << table_name;
804 0 : auto stmt = create_statement_from_sql(sql.str());
805 0 : execute_nonselect_statement(stmt);
806 :
807 0 : sql.str("");
808 0 : sql << "DROP TABLE " << table_name;
809 0 : stmt = create_statement_from_sql(sql.str());
810 0 : execute_nonselect_statement(stmt);
811 :
812 0 : sql.str("");
813 0 : sql << "ALTER TABLE " << temp_table_name << " RENAME TO " << table_name;
814 0 : stmt = create_statement_from_sql(sql.str());
815 0 : execute_nonselect_statement(stmt);
816 0 : }
817 :
818 : static inline PairVec
819 239 : get_object_values (QofIdTypeConst obj_name,
820 : gpointer pObject, const EntryVec& table)
821 : {
822 239 : PairVec vec;
823 :
824 2777 : for (auto const& table_row : table)
825 : {
826 2299 : if (!(table_row->is_autoincr()))
827 : {
828 2217 : table_row->add_to_query (obj_name, pObject, vec);
829 : }
830 : }
831 239 : return vec;
832 : }
833 :
834 : bool
835 50 : GncSqlBackend::object_in_db (const char* table_name, QofIdTypeConst obj_name,
836 : const gpointer pObject, const EntryVec& table) const noexcept
837 : {
838 50 : g_return_val_if_fail (table_name != nullptr, false);
839 50 : g_return_val_if_fail (obj_name != nullptr, false);
840 50 : g_return_val_if_fail (pObject != nullptr, false);
841 :
842 : /* SELECT * FROM */
843 150 : auto sql = std::string{"SELECT "} + table[0]->name() + " FROM " + table_name;
844 50 : auto stmt = create_statement_from_sql(sql.c_str());
845 50 : assert (stmt != nullptr);
846 :
847 : /* WHERE */
848 50 : PairVec values{get_object_values(obj_name, pObject, table)};
849 : /* We want only the first item in the table, which should be the PK. */
850 50 : values.resize(1);
851 50 : stmt->add_where_cond(obj_name, values);
852 50 : auto result = execute_select_statement (stmt);
853 50 : return (result != nullptr && result->size() > 0);
854 50 : }
855 :
856 : bool
857 209 : GncSqlBackend::do_db_operation (E_DB_OPERATION op, const char* table_name,
858 : QofIdTypeConst obj_name, gpointer pObject,
859 : const EntryVec& table) const noexcept
860 : {
861 209 : GncSqlStatementPtr stmt;
862 :
863 209 : g_return_val_if_fail (table_name != nullptr, false);
864 209 : g_return_val_if_fail (obj_name != nullptr, false);
865 209 : g_return_val_if_fail (pObject != nullptr, false);
866 :
867 209 : switch(op)
868 : {
869 181 : case OP_DB_INSERT:
870 181 : stmt = build_insert_statement (table_name, obj_name, pObject, table);
871 181 : break;
872 8 : case OP_DB_UPDATE:
873 8 : stmt = build_update_statement (table_name, obj_name, pObject, table);
874 8 : break;
875 20 : case OP_DB_DELETE:
876 20 : stmt = build_delete_statement (table_name, obj_name, pObject, table);
877 20 : break;
878 : }
879 209 : if (stmt == nullptr)
880 0 : return false;
881 209 : return (execute_nonselect_statement(stmt) != -1);
882 209 : }
883 :
884 : bool
885 41 : GncSqlBackend::save_commodity(gnc_commodity* comm) noexcept
886 : {
887 41 : if (comm == nullptr) return false;
888 41 : QofInstance* inst = QOF_INSTANCE(comm);
889 82 : auto obe = m_backend_registry.get_object_backend(std::string(inst->e_type));
890 41 : if (obe && !obe->instance_in_db(this, inst))
891 9 : return obe->commit(this, inst);
892 32 : return true;
893 41 : }
894 :
895 : GncSqlStatementPtr
896 181 : GncSqlBackend::build_insert_statement (const char* table_name,
897 : QofIdTypeConst obj_name,
898 : gpointer pObject,
899 : const EntryVec& table) const noexcept
900 : {
901 181 : GncSqlStatementPtr stmt;
902 181 : PairVec col_values;
903 181 : std::ostringstream sql;
904 :
905 181 : g_return_val_if_fail (table_name != nullptr, nullptr);
906 181 : g_return_val_if_fail (obj_name != nullptr, nullptr);
907 181 : g_return_val_if_fail (pObject != nullptr, nullptr);
908 181 : PairVec values{get_object_values(obj_name, pObject, table)};
909 :
910 181 : sql << "INSERT INTO " << table_name <<"(";
911 1932 : for (auto const& col_value : values)
912 : {
913 3140 : if (col_value != *values.begin())
914 1389 : sql << ",";
915 1570 : sql << col_value.first;
916 : }
917 :
918 181 : sql << ") VALUES(";
919 1932 : for (const auto& col_value : values)
920 : {
921 3140 : if (col_value != *values.begin())
922 1389 : sql << ",";
923 1570 : sql << col_value.second;
924 : }
925 181 : sql << ")";
926 :
927 181 : stmt = create_statement_from_sql(sql.str());
928 181 : return stmt;
929 181 : }
930 :
931 : GncSqlStatementPtr
932 8 : GncSqlBackend::build_update_statement(const gchar* table_name,
933 : QofIdTypeConst obj_name, gpointer pObject,
934 : const EntryVec& table) const noexcept
935 : {
936 8 : GncSqlStatementPtr stmt;
937 8 : std::ostringstream sql;
938 :
939 8 : g_return_val_if_fail (table_name != nullptr, nullptr);
940 8 : g_return_val_if_fail (obj_name != nullptr, nullptr);
941 8 : g_return_val_if_fail (pObject != nullptr, nullptr);
942 :
943 :
944 8 : PairVec values{get_object_values (obj_name, pObject, table)};
945 :
946 : // Create the SQL statement
947 8 : sql << "UPDATE " << table_name << " SET ";
948 :
949 60 : for (auto const& col_value : values)
950 : {
951 88 : if (col_value != *values.begin())
952 36 : sql << ",";
953 44 : sql << col_value.first << "=" <<
954 44 : col_value.second;
955 : }
956 :
957 8 : stmt = create_statement_from_sql(sql.str());
958 : /* We want our where condition to be just the first column and
959 : * value, i.e. the guid of the object.
960 : */
961 32 : values.erase(values.begin() + 1, values.end());
962 8 : stmt->add_where_cond(obj_name, values);
963 8 : return stmt;
964 8 : }
965 :
966 : GncSqlStatementPtr
967 20 : GncSqlBackend::build_delete_statement(const gchar* table_name,
968 : QofIdTypeConst obj_name,
969 : gpointer pObject,
970 : const EntryVec& table) const noexcept
971 : {
972 20 : std::ostringstream sql;
973 :
974 20 : g_return_val_if_fail (table_name != nullptr, nullptr);
975 20 : g_return_val_if_fail (obj_name != nullptr, nullptr);
976 20 : g_return_val_if_fail (pObject != nullptr, nullptr);
977 :
978 20 : sql << "DELETE FROM " << table_name;
979 20 : auto stmt = create_statement_from_sql (sql.str());
980 :
981 : /* WHERE */
982 20 : PairVec values;
983 20 : table[0]->add_to_query (obj_name, pObject, values);
984 60 : PairVec col_values{values[0]};
985 20 : stmt->add_where_cond (obj_name, col_values);
986 :
987 20 : return stmt;
988 40 : }
989 :
990 15 : GncSqlBackend::ObjectBackendRegistry::ObjectBackendRegistry()
991 : {
992 15 : register_backend(std::make_shared<GncSqlBookBackend>());
993 15 : register_backend(std::make_shared<GncSqlCommodityBackend>());
994 15 : register_backend(std::make_shared<GncSqlAccountBackend>());
995 15 : register_backend(std::make_shared<GncSqlBudgetBackend>());
996 15 : register_backend(std::make_shared<GncSqlPriceBackend>());
997 15 : register_backend(std::make_shared<GncSqlTransBackend>());
998 15 : register_backend(std::make_shared<GncSqlSplitBackend>());
999 15 : register_backend(std::make_shared<GncSqlSlotsBackend>());
1000 15 : register_backend(std::make_shared<GncSqlRecurrenceBackend>());
1001 15 : register_backend(std::make_shared<GncSqlSchedXactionBackend>());
1002 15 : register_backend(std::make_shared<GncSqlLotsBackend>());
1003 15 : register_backend(std::make_shared<GncSqlBillTermBackend>());
1004 15 : register_backend(std::make_shared<GncSqlCustomerBackend>());
1005 15 : register_backend(std::make_shared<GncSqlEmployeeBackend>());
1006 15 : register_backend(std::make_shared<GncSqlEntryBackend>());
1007 15 : register_backend(std::make_shared<GncSqlInvoiceBackend>());
1008 15 : register_backend(std::make_shared<GncSqlJobBackend>());
1009 15 : register_backend(std::make_shared<GncSqlOrderBackend>());
1010 15 : register_backend(std::make_shared<GncSqlTaxTableBackend>());
1011 15 : register_backend(std::make_shared<GncSqlVendorBackend>());
1012 15 : }
1013 :
1014 : void
1015 0 : GncSqlBackend::ObjectBackendRegistry::register_backend(OBEEntry&& entry) noexcept
1016 : {
1017 0 : m_registry.emplace_back(entry);
1018 0 : }
1019 :
1020 : void
1021 300 : GncSqlBackend::ObjectBackendRegistry::register_backend(GncSqlObjectBackendPtr obe) noexcept
1022 : {
1023 600 : m_registry.emplace_back(make_tuple(std::string{obe->type()}, obe));
1024 300 : }
1025 :
1026 : GncSqlObjectBackendPtr
1027 157 : GncSqlBackend::ObjectBackendRegistry::get_object_backend(const std::string& type) const
1028 : {
1029 157 : auto entry = std::find_if(m_registry.begin(), m_registry.end(),
1030 314 : [type](const OBEEntry& entry){
1031 881 : return type == std::get<0>(entry);
1032 : });
1033 314 : if (entry == m_registry.end())
1034 1 : return nullptr;
1035 :
1036 156 : return std::get<1>(*entry);
1037 : }
|
