GnuCash  4.11-517-g41de4cefce
gnc-sql-backend.cpp
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 extern "C"
24 {
25 #include <config.h>
26 #include <gnc-prefs.h>
27 #include <gnc-engine.h>
28 #include <gnc-commodity.h>
29 #include <SX-book.h>
30 #include <Recurrence.h>
31 #include <gncBillTerm.h>
32 #include <gncTaxTable.h>
33 #include <gncInvoice.h>
34 #include <gnc-pricedb.h>
35 }
36 
37 #include <algorithm>
38 #include <cassert>
39 
40 #include "gnc-sql-connection.hpp"
41 #include "gnc-sql-backend.hpp"
42 #include "gnc-sql-object-backend.hpp"
43 #include "gnc-sql-column-table-entry.hpp"
44 #include "gnc-sql-result.hpp"
45 
46 #include "gnc-account-sql.h"
47 #include "gnc-book-sql.h"
48 #include "gnc-budget-sql.h"
49 #include "gnc-commodity-sql.h"
50 #include "gnc-lots-sql.h"
51 #include "gnc-price-sql.h"
52 #include "gnc-recurrence-sql.h"
53 #include "gnc-schedxaction-sql.h"
54 #include "gnc-slots-sql.h"
55 #include "gnc-transaction-sql.h"
56 
57 #include "gnc-bill-term-sql.h"
58 #include "gnc-customer-sql.h"
59 #include "gnc-employee-sql.h"
60 #include "gnc-entry-sql.h"
61 #include "gnc-invoice-sql.h"
62 #include "gnc-job-sql.h"
63 #include "gnc-order-sql.h"
64 #include "gnc-tax-table-sql.h"
65 #include "gnc-vendor-sql.h"
66 
67 static QofLogModule log_module = G_LOG_DOMAIN;
68 #define VERSION_TABLE_NAME "versions"
69 #define MAX_TABLE_NAME_LEN 50
70 #define TABLE_COL_NAME "table_name"
71 #define VERSION_COL_NAME "table_version"
72 
73 using StrVec = std::vector<std::string>;
74 
75 static std::string empty_string{};
76 static EntryVec version_table
77 {
78  gnc_sql_make_table_entry<CT_STRING>(
79  TABLE_COL_NAME, MAX_TABLE_NAME_LEN, COL_PKEY | COL_NNUL),
80  gnc_sql_make_table_entry<CT_INT>(VERSION_COL_NAME, 0, COL_NNUL)
81 };
82 
83 GncSqlBackend::GncSqlBackend(GncSqlConnection *conn, QofBook* book) :
84  QofBackend {}, m_conn{conn}, m_book{book}, m_loading{false},
85  m_in_query{false}, m_is_pristine_db{false}
86 {
87  if (conn != nullptr)
88  connect (conn);
89 }
90 
91 GncSqlBackend::~GncSqlBackend()
92 {
93  connect(nullptr);
94 }
95 
96 void
98 {
99  if (m_conn != nullptr && m_conn != conn)
100  delete m_conn;
101  finalize_version_info();
102  m_conn = conn;
103 }
104 
105 GncSqlStatementPtr
106 GncSqlBackend::create_statement_from_sql(const std::string& str) const noexcept
107 {
108  auto stmt = m_conn ? m_conn->create_statement_from_sql(str) : nullptr;
109  if (stmt == nullptr)
110  {
111  PERR ("SQL error: %s\n", str.c_str());
113  }
114  return stmt;
115 }
116 
118 GncSqlBackend::execute_select_statement(const GncSqlStatementPtr& stmt) const noexcept
119 {
120  auto result = m_conn ? m_conn->execute_select_statement(stmt) : nullptr;
121  if (result == nullptr)
122  {
123  PERR ("SQL error: %s\n", stmt->to_sql());
125  }
126  return result;
127 }
128 
129 int
130 GncSqlBackend::execute_nonselect_statement(const GncSqlStatementPtr& stmt) const noexcept
131 {
132  int result = m_conn ? m_conn->execute_nonselect_statement(stmt) : -1;
133  if (result == -1)
134  {
135  PERR ("SQL error: %s\n", stmt->to_sql());
137  }
138  return result;
139 }
140 
141 std::string
142 GncSqlBackend::quote_string(const std::string& str) const noexcept
143 {
144  g_return_val_if_fail (m_conn != nullptr, empty_string);
145  if (!m_conn)
146  return empty_string;
147  return m_conn->quote_string(str);
148 }
149 
150 bool
151 GncSqlBackend::create_table(const std::string& table_name,
152  const EntryVec& col_table) const noexcept
153 {
154  g_return_val_if_fail (m_conn != nullptr, false);
155 
156  ColVec info_vec;
157 
158  for (auto const& table_row : col_table)
159  {
160  table_row->add_to_table (info_vec);
161  }
162  return m_conn->create_table (table_name, info_vec);
163 
164 }
165 
166 bool
167 GncSqlBackend::create_table(const std::string& table_name, int table_version,
168  const EntryVec& col_table) noexcept
169 {
170  if (create_table (table_name, col_table))
171  return set_table_version (table_name, table_version);
172  return false;
173 }
174 
175 bool
176 GncSqlBackend::create_index(const std::string& index_name,
177  const std::string& table_name,
178  const EntryVec& col_table) const noexcept
179 {
180  g_return_val_if_fail (m_conn != nullptr, false);
181  return m_conn->create_index(index_name, table_name, col_table);
182 }
183 
184 bool
185 GncSqlBackend::add_columns_to_table(const std::string& table_name,
186  const EntryVec& col_table) const noexcept
187 {
188  g_return_val_if_fail (m_conn != nullptr, false);
189 
190  ColVec info_vec;
191 
192  for (auto const& table_row : col_table)
193  {
194  table_row->add_to_table (info_vec);
195  }
196  return m_conn->add_columns_to_table(table_name, info_vec);
197 }
198 
199 void
200 GncSqlBackend::update_progress(double pct) const noexcept
201 {
202  if (m_percentage != nullptr)
203  (m_percentage) (nullptr, pct);
204 }
205 
206 void
207 GncSqlBackend::finish_progress() const noexcept
208 {
209  if (m_percentage != nullptr)
210  (m_percentage) (nullptr, -1.0);
211 }
212 
213 void
215 {
216  for(auto entry : m_backend_registry)
217  {
218  update_progress(101.0);
219  std::get<1>(entry)->create_tables(this);
220  }
221 }
222 
223 /* Main object load order */
224 static const StrVec fixed_load_order
225 { GNC_ID_BOOK, GNC_ID_COMMODITY, GNC_ID_ACCOUNT, GNC_ID_LOT, GNC_ID_TRANS };
226 
227 /* Order in which business objects need to be loaded */
228 static const StrVec business_fixed_load_order =
229 { GNC_ID_BILLTERM, GNC_ID_TAXTABLE, GNC_ID_INVOICE };
230 
231 void
232 GncSqlBackend::ObjectBackendRegistry::load_remaining(GncSqlBackend* sql_be)
233 {
234 
235  auto num_types = m_registry.size();
236  auto num_done = fixed_load_order.size() + business_fixed_load_order.size();
237 
238  for (auto entry : m_registry)
239  {
240  std::string type;
241  GncSqlObjectBackendPtr obe = nullptr;
242  std::tie(type, obe) = entry;
243 
244  /* Don't need to load anything if it has already been loaded with
245  * the fixed order.
246  */
247  if (std::find(fixed_load_order.begin(), fixed_load_order.end(),
248  type) != fixed_load_order.end()) continue;
249  if (std::find(business_fixed_load_order.begin(),
250  business_fixed_load_order.end(),
251  type) != business_fixed_load_order.end()) continue;
252 
253  num_done++;
254  sql_be->update_progress(num_done * 100 / num_types);
255  obe->load_all (sql_be);
256  }
257 }
258 
259 typedef struct
260 {
261  QofIdType searchObj;
262  gpointer pCompiledQuery;
264 
265 /* callback structure */
266 typedef struct
267 {
268  gboolean is_known;
269  gboolean is_ok;
270  GncSqlBackend* sql_be;
271  QofInstance* inst;
272  QofQuery* pQuery;
273  gpointer pCompiledQuery;
274  gnc_sql_query_info* pQueryInfo;
275 } sql_backend;
276 
277 
278 void
279 GncSqlBackend::load (QofBook* book, QofBackendLoadType loadType)
280 {
281  Account* root;
282 
283  g_return_if_fail (book != NULL);
284 
285  ENTER ("sql_be=%p, book=%p", this, book);
286 
287  m_loading = TRUE;
288 
289  if (loadType == LOAD_TYPE_INITIAL_LOAD)
290  {
291  assert (m_book == nullptr);
292  m_book = book;
293 
294  auto num_types = m_backend_registry.size();
295  auto num_done = 0;
296 
297  /* Load any initial stuff. Some of this needs to happen in a certain order */
298  for (auto type : fixed_load_order)
299  {
300  num_done++;
301  auto obe = m_backend_registry.get_object_backend(type);
302  if (obe)
303  {
304  update_progress(num_done * 100 / num_types);
305  obe->load_all(this);
306  }
307  }
308  for (auto type : business_fixed_load_order)
309  {
310  num_done++;
311  auto obe = m_backend_registry.get_object_backend(type);
312  if (obe)
313  {
314  update_progress(num_done * 100 / num_types);
315  obe->load_all(this);
316  }
317  }
318 
319  root = gnc_book_get_root_account( book );
321  nullptr);
322 
323  m_backend_registry.load_remaining(this);
324 
326  nullptr);
327  }
328  else if (loadType == LOAD_TYPE_LOAD_ALL)
329  {
330  // Load all transactions
331  auto obe = m_backend_registry.get_object_backend (GNC_ID_TRANS);
332  obe->load_all (this);
333  }
334 
335  m_loading = FALSE;
336  std::for_each(m_postload_commodities.begin(), m_postload_commodities.end(),
337  [](gnc_commodity* comm) {
338  gnc_commodity_begin_edit(comm);
339  gnc_commodity_commit_edit(comm);
340  });
341  m_postload_commodities.clear();
342 
343  /* Mark the sessoion as clean -- though it should never be marked
344  * dirty with this backend
345  */
347  finish_progress();
348 
349  LEAVE ("");
350 }
351 
352 /* ================================================================= */
353 
354 bool
355 GncSqlBackend::write_account_tree(Account* root)
356 {
357  GList* descendants;
358  GList* node;
359  bool is_ok = true;
360 
361  g_return_val_if_fail (root != nullptr, false);
362 
363  auto obe = m_backend_registry.get_object_backend(GNC_ID_ACCOUNT);
364  is_ok = obe->commit (this, QOF_INSTANCE (root));
365  if (is_ok)
366  {
367  descendants = gnc_account_get_descendants (root);
368  for (node = descendants; node != NULL && is_ok; node = g_list_next (node))
369  {
370  is_ok = obe->commit(this, QOF_INSTANCE (GNC_ACCOUNT (node->data)));
371  if (!is_ok) break;
372  }
373  g_list_free (descendants);
374  }
375  update_progress(101.0);
376 
377  return is_ok;
378 }
379 
380 bool
381 GncSqlBackend::write_accounts()
382 {
383  update_progress(101.0);
384  auto is_ok = write_account_tree (gnc_book_get_root_account (m_book));
385  if (is_ok)
386  {
387  update_progress(101.0);
388  is_ok = write_account_tree (gnc_book_get_template_root(m_book));
389  }
390 
391  return is_ok;
392 }
393 
394 static gboolean // Can't be bool because of signature for xaccAccountTreeForEach
395 write_tx (Transaction* tx, gpointer data)
396 {
397  auto s = static_cast<write_objects_t*>(data);
398 
399  g_return_val_if_fail (tx != NULL, 0);
400  g_return_val_if_fail (data != NULL, 0);
401 
402  s->commit (QOF_INSTANCE (tx));
403  auto splitbe = s->be->get_object_backend(GNC_ID_SPLIT);
404  for (auto split_node = xaccTransGetSplitList (tx);
405  split_node != nullptr && s->is_ok;
406  split_node = g_list_next (split_node))
407  {
408  s->is_ok = splitbe->commit(s->be, QOF_INSTANCE(split_node->data));
409  }
410  s->be->update_progress (101.0);
411  return (s->is_ok ? 0 : 1);
412 }
413 
414 bool
415 GncSqlBackend::write_transactions()
416 {
417  auto obe = m_backend_registry.get_object_backend(GNC_ID_TRANS);
418  write_objects_t data{this, TRUE, obe.get()};
419 
421  gnc_book_get_root_account (m_book), write_tx, &data);
422  update_progress(101.0);
423  return data.is_ok;
424 }
425 
426 bool
427 GncSqlBackend::write_template_transactions()
428 {
429  auto obe = m_backend_registry.get_object_backend(GNC_ID_TRANS);
430  write_objects_t data{this, true, obe.get()};
432  if (gnc_account_n_descendants (ra) > 0)
433  {
434  (void)xaccAccountTreeForEachTransaction (ra, write_tx, &data);
435  update_progress(101.0);
436  }
437 
438  return data.is_ok;
439 }
440 
441 bool
442 GncSqlBackend::write_schedXactions()
443 {
444  GList* schedXactions;
445  SchedXaction* tmpSX;
446  bool is_ok = true;
447 
448  schedXactions = gnc_book_get_schedxactions (m_book)->sx_list;
449  auto obe = m_backend_registry.get_object_backend(GNC_ID_SCHEDXACTION);
450 
451  for (; schedXactions != NULL && is_ok; schedXactions = schedXactions->next)
452  {
453  tmpSX = static_cast<decltype (tmpSX)> (schedXactions->data);
454  is_ok = obe->commit (this, QOF_INSTANCE (tmpSX));
455  }
456  update_progress(101.0);
457 
458  return is_ok;
459 }
460 
461 #pragma GCC diagnostic warning "-Wformat-nonliteral"
462 
463 void
464 GncSqlBackend::sync(QofBook* book)
465 {
466  g_return_if_fail (book != NULL);
467  g_return_if_fail (m_conn != nullptr);
468 
470  ENTER ("book=%p, sql_be->book=%p", book, m_book);
471  update_progress(101.0);
472 
473  /* Create new tables */
474  m_is_pristine_db = true;
475  create_tables();
476 
477  /* Save all contents */
478  m_book = book;
479  auto is_ok = m_conn->begin_transaction();
480 
481  // FIXME: should write the set of commodities that are used
482  // write_commodities(sql_be, book);
483  if (is_ok)
484  {
485  auto obe = m_backend_registry.get_object_backend(GNC_ID_BOOK);
486  is_ok = obe->commit (this, QOF_INSTANCE (book));
487  }
488  if (is_ok)
489  {
490  is_ok = write_accounts();
491  }
492  if (is_ok)
493  {
494  is_ok = write_transactions();
495  }
496  if (is_ok)
497  {
498  is_ok = write_template_transactions();
499  }
500  if (is_ok)
501  {
502  is_ok = write_schedXactions();
503  }
504  if (is_ok)
505  {
506  for (auto entry : m_backend_registry)
507  std::get<1>(entry)->write (this);
508  }
509  if (is_ok)
510  {
511  is_ok = m_conn->commit_transaction();
512  }
513  if (is_ok)
514  {
515  m_is_pristine_db = false;
516 
517  /* Mark the session as clean -- though it shouldn't ever get
518  * marked dirty with this backend
519  */
521  }
522  else
523  {
526  }
527  finish_progress();
528  LEAVE ("book=%p", book);
529 }
530 
531 /* ================================================================= */
532 /* Routines to deal with the creation of multiple books. */
533 
534 void
536 {
537  //g_return_if_fail (inst != NULL);
538 
539  //ENTER (" ");
540  //LEAVE ("");
541 }
542 
543 void
545 {
546  //g_return_if_fail (inst != NULL);
547 
548  //ENTER (" ");
549  //LEAVE ("");
550 }
551 
552 void
554 {
555  m_postload_commodities.push_back(commodity);
556 }
557 
558 GncSqlObjectBackendPtr
559 GncSqlBackend::get_object_backend(const std::string& type) const noexcept
560 {
561  return m_backend_registry.get_object_backend(type);
562 }
563 
564 
565 /* Commit_edit handler - find the correct backend handler for this object
566  * type and call its commit handler
567  */
568 void
570 {
571  sql_backend be_data;
572  gboolean is_dirty;
573  gboolean is_destroying;
574  gboolean is_infant;
575 
576  g_return_if_fail (inst != NULL);
577  g_return_if_fail (m_conn != nullptr);
578 
580  {
582  (void)m_conn->rollback_transaction ();
583  return;
584  }
585  /* During initial load where objects are being created, don't commit
586  anything, but do mark the object as clean. */
587  if (m_loading)
588  {
589  qof_instance_mark_clean (inst);
590  return;
591  }
592 
593  // The engine has a PriceDB object but it isn't in the database
594  if (strcmp (inst->e_type, "PriceDB") == 0)
595  {
596  qof_instance_mark_clean (inst);
598  return;
599  }
600 
601  ENTER (" ");
602 
603  is_dirty = qof_instance_get_dirty_flag (inst);
604  is_destroying = qof_instance_get_destroying (inst);
605  is_infant = qof_instance_get_infant (inst);
606 
607  DEBUG ("%s dirty = %d, do_free = %d, infant = %d\n",
608  (inst->e_type ? inst->e_type : "(null)"),
609  is_dirty, is_destroying, is_infant);
610 
611  if (!is_dirty && !is_destroying)
612  {
613  LEAVE ("!dirty OR !destroying");
614  return;
615  }
616 
617  if (!m_conn->begin_transaction ())
618  {
619  PERR ("begin_transaction failed\n");
620  LEAVE ("Rolled back - database transaction begin error");
621  return;
622  }
623 
624  bool is_ok = true;
625 
626  auto obe = m_backend_registry.get_object_backend(std::string{inst->e_type});
627  if (obe != nullptr)
628  is_ok = obe->commit(this, inst);
629  else
630  {
631  PERR ("Unknown object type '%s'\n", inst->e_type);
632  (void)m_conn->rollback_transaction ();
633 
634  // Don't let unknown items still mark the book as being dirty
636  qof_instance_mark_clean (inst);
637  LEAVE ("Rolled back - unknown object type");
638  return;
639  }
640  if (!is_ok)
641  {
642  // Error - roll it back
643  (void)m_conn->rollback_transaction();
644 
645  // This *should* leave things marked dirty
646  LEAVE ("Rolled back - database error");
647  return;
648  }
649 
650  (void)m_conn->commit_transaction ();
651 
653  qof_instance_mark_clean (inst);
654 
655  LEAVE ("");
656 }
657 
658 
665 void
667 {
668  g_return_if_fail (m_conn != nullptr);
669  if (m_conn->does_table_exist (VERSION_TABLE_NAME))
670  {
671  std::string sql {"SELECT * FROM "};
672  sql += VERSION_TABLE_NAME;
673  auto stmt = m_conn->create_statement_from_sql(sql);
674  auto result = m_conn->execute_select_statement (stmt);
675  for (const auto& row : *result)
676  {
677  auto name = row.get_string_at_col (TABLE_COL_NAME);
678  unsigned int version = row.get_int_at_col (VERSION_COL_NAME);
679  m_versions.push_back(std::make_pair(name, version));
680  }
681  }
682  else
683  {
684  create_table (VERSION_TABLE_NAME, version_table);
685  set_table_version("Gnucash", gnc_prefs_get_long_version ());
686  set_table_version("Gnucash-Resave", GNUCASH_RESAVE_VERSION);
687  }
688 }
689 
697 bool
699 {
700  bool ok = create_table (VERSION_TABLE_NAME, version_table);
701  m_versions.clear();
702  set_table_version ("Gnucash", gnc_prefs_get_long_version ());
703  set_table_version ("Gnucash-Resave", GNUCASH_RESAVE_VERSION);
704  return ok;
705 }
706 
712 void
714 {
715  m_versions.clear();
716 }
717 
718 unsigned int
719 GncSqlBackend::get_table_version(const std::string& table_name) const noexcept
720 {
721  /* If the db is pristine because it's being saved, the table does not exist. */
722  if (m_is_pristine_db)
723  return 0;
724 
725  auto version = std::find_if(m_versions.begin(), m_versions.end(),
726  [table_name](const VersionPair& version) {
727  return version.first == table_name; });
728  if (version != m_versions.end())
729  return version->second;
730  return 0;
731 }
732 
742 bool
743 GncSqlBackend::set_table_version (const std::string& table_name,
744  uint_t version) noexcept
745 {
746  g_return_val_if_fail (version > 0, false);
747 
748  unsigned int cur_version{0};
749  std::stringstream sql;
750  auto ver_entry = std::find_if(m_versions.begin(), m_versions.end(),
751  [table_name](const VersionPair& ver) {
752  return ver.first == table_name; });
753  if (ver_entry != m_versions.end())
754  cur_version = ver_entry->second;
755  if (cur_version != version)
756  {
757  if (cur_version == 0)
758  {
759  sql << "INSERT INTO " << VERSION_TABLE_NAME << " VALUES('" <<
760  table_name << "'," << version <<")";
761  m_versions.push_back(std::make_pair(table_name, version));
762  }
763  else
764  {
765  sql << "UPDATE " << VERSION_TABLE_NAME << " SET " <<
766  VERSION_COL_NAME << "=" << version << " WHERE " <<
767  TABLE_COL_NAME << "='" << table_name << "'";
768  ver_entry->second = version;
769  }
770  auto stmt = create_statement_from_sql(sql.str());
771  auto status = execute_nonselect_statement (stmt);
772  if (status == -1)
773  {
774  PERR ("SQL error: %s\n", sql.str().c_str());
776  return false;
777  }
778  }
779 
780  return true;
781 }
782 
783 void
784 GncSqlBackend::upgrade_table (const std::string& table_name,
785  const EntryVec& col_table) noexcept
786 {
787  DEBUG ("Upgrading %s table\n", table_name.c_str());
788 
789  auto temp_table_name = table_name + "_new";
790  create_table (temp_table_name, col_table);
791  std::stringstream sql;
792  sql << "INSERT INTO " << temp_table_name << " SELECT * FROM " << table_name;
793  auto stmt = create_statement_from_sql(sql.str());
794  execute_nonselect_statement(stmt);
795 
796  sql.str("");
797  sql << "DROP TABLE " << table_name;
798  stmt = create_statement_from_sql(sql.str());
799  execute_nonselect_statement(stmt);
800 
801  sql.str("");
802  sql << "ALTER TABLE " << temp_table_name << " RENAME TO " << table_name;
803  stmt = create_statement_from_sql(sql.str());
804  execute_nonselect_statement(stmt);
805 }
806 
807 static inline PairVec
808 get_object_values (QofIdTypeConst obj_name,
809  gpointer pObject, const EntryVec& table)
810 {
811  PairVec vec;
812 
813  for (auto const& table_row : table)
814  {
815  if (!(table_row->is_autoincr()))
816  {
817  table_row->add_to_query (obj_name, pObject, vec);
818  }
819  }
820  return vec;
821 }
822 
823 bool
824 GncSqlBackend::object_in_db (const char* table_name, QofIdTypeConst obj_name,
825  const gpointer pObject, const EntryVec& table) const noexcept
826 {
827  guint count;
828  g_return_val_if_fail (table_name != nullptr, false);
829  g_return_val_if_fail (obj_name != nullptr, false);
830  g_return_val_if_fail (pObject != nullptr, false);
831 
832  /* SELECT * FROM */
833  auto sql = std::string{"SELECT "} + table[0]->name() + " FROM " + table_name;
834  auto stmt = create_statement_from_sql(sql.c_str());
835  assert (stmt != nullptr);
836 
837  /* WHERE */
838  PairVec values{get_object_values(obj_name, pObject, table)};
839  /* We want only the first item in the table, which should be the PK. */
840  values.resize(1);
841  stmt->add_where_cond(obj_name, values);
842  auto result = execute_select_statement (stmt);
843  return (result != nullptr && result->size() > 0);
844 }
845 
846 bool
847 GncSqlBackend::do_db_operation (E_DB_OPERATION op, const char* table_name,
848  QofIdTypeConst obj_name, gpointer pObject,
849  const EntryVec& table) const noexcept
850 {
851  GncSqlStatementPtr stmt;
852 
853  g_return_val_if_fail (table_name != nullptr, false);
854  g_return_val_if_fail (obj_name != nullptr, false);
855  g_return_val_if_fail (pObject != nullptr, false);
856 
857  switch(op)
858  {
859  case OP_DB_INSERT:
860  stmt = build_insert_statement (table_name, obj_name, pObject, table);
861  break;
862  case OP_DB_UPDATE:
863  stmt = build_update_statement (table_name, obj_name, pObject, table);
864  break;
865  case OP_DB_DELETE:
866  stmt = build_delete_statement (table_name, obj_name, pObject, table);
867  break;
868  }
869  if (stmt == nullptr)
870  return false;
871  return (execute_nonselect_statement(stmt) != -1);
872 }
873 
874 bool
875 GncSqlBackend::save_commodity(gnc_commodity* comm) noexcept
876 {
877  if (comm == nullptr) return false;
878  QofInstance* inst = QOF_INSTANCE(comm);
879  auto obe = m_backend_registry.get_object_backend(std::string(inst->e_type));
880  if (obe && !obe->instance_in_db(this, inst))
881  return obe->commit(this, inst);
882  return true;
883 }
884 
885 GncSqlStatementPtr
886 GncSqlBackend::build_insert_statement (const char* table_name,
887  QofIdTypeConst obj_name,
888  gpointer pObject,
889  const EntryVec& table) const noexcept
890 {
891  GncSqlStatementPtr stmt;
892  PairVec col_values;
893  std::ostringstream sql;
894 
895  g_return_val_if_fail (table_name != nullptr, nullptr);
896  g_return_val_if_fail (obj_name != nullptr, nullptr);
897  g_return_val_if_fail (pObject != nullptr, nullptr);
898  PairVec values{get_object_values(obj_name, pObject, table)};
899 
900  sql << "INSERT INTO " << table_name <<"(";
901  for (auto const& col_value : values)
902  {
903  if (col_value != *values.begin())
904  sql << ",";
905  sql << col_value.first;
906  }
907 
908  sql << ") VALUES(";
909  for (auto col_value : values)
910  {
911  if (col_value != *values.begin())
912  sql << ",";
913  sql << col_value.second;
914  }
915  sql << ")";
916 
917  stmt = create_statement_from_sql(sql.str());
918  return stmt;
919 }
920 
921 GncSqlStatementPtr
922 GncSqlBackend::build_update_statement(const gchar* table_name,
923  QofIdTypeConst obj_name, gpointer pObject,
924  const EntryVec& table) const noexcept
925 {
926  GncSqlStatementPtr stmt;
927  std::ostringstream sql;
928 
929  g_return_val_if_fail (table_name != nullptr, nullptr);
930  g_return_val_if_fail (obj_name != nullptr, nullptr);
931  g_return_val_if_fail (pObject != nullptr, nullptr);
932 
933 
934  PairVec values{get_object_values (obj_name, pObject, table)};
935 
936  // Create the SQL statement
937  sql << "UPDATE " << table_name << " SET ";
938 
939  for (auto const& col_value : values)
940  {
941  if (col_value != *values.begin())
942  sql << ",";
943  sql << col_value.first << "=" <<
944  col_value.second;
945  }
946 
947  stmt = create_statement_from_sql(sql.str());
948  /* We want our where condition to be just the first column and
949  * value, i.e. the guid of the object.
950  */
951  values.erase(values.begin() + 1, values.end());
952  stmt->add_where_cond(obj_name, values);
953  return stmt;
954 }
955 
956 GncSqlStatementPtr
957 GncSqlBackend::build_delete_statement(const gchar* table_name,
958  QofIdTypeConst obj_name,
959  gpointer pObject,
960  const EntryVec& table) const noexcept
961 {
962  std::ostringstream sql;
963 
964  g_return_val_if_fail (table_name != nullptr, nullptr);
965  g_return_val_if_fail (obj_name != nullptr, nullptr);
966  g_return_val_if_fail (pObject != nullptr, nullptr);
967 
968  sql << "DELETE FROM " << table_name;
969  auto stmt = create_statement_from_sql (sql.str());
970 
971  /* WHERE */
972  PairVec values;
973  table[0]->add_to_query (obj_name, pObject, values);
974  PairVec col_values{values[0]};
975  stmt->add_where_cond (obj_name, col_values);
976 
977  return stmt;
978 }
979 
980 GncSqlBackend::ObjectBackendRegistry::ObjectBackendRegistry()
981 {
982  register_backend(std::make_shared<GncSqlBookBackend>());
983  register_backend(std::make_shared<GncSqlCommodityBackend>());
984  register_backend(std::make_shared<GncSqlAccountBackend>());
985  register_backend(std::make_shared<GncSqlBudgetBackend>());
986  register_backend(std::make_shared<GncSqlPriceBackend>());
987  register_backend(std::make_shared<GncSqlTransBackend>());
988  register_backend(std::make_shared<GncSqlSplitBackend>());
989  register_backend(std::make_shared<GncSqlSlotsBackend>());
990  register_backend(std::make_shared<GncSqlRecurrenceBackend>());
991  register_backend(std::make_shared<GncSqlSchedXactionBackend>());
992  register_backend(std::make_shared<GncSqlLotsBackend>());
993  register_backend(std::make_shared<GncSqlBillTermBackend>());
994  register_backend(std::make_shared<GncSqlCustomerBackend>());
995  register_backend(std::make_shared<GncSqlEmployeeBackend>());
996  register_backend(std::make_shared<GncSqlEntryBackend>());
997  register_backend(std::make_shared<GncSqlInvoiceBackend>());
998  register_backend(std::make_shared<GncSqlJobBackend>());
999  register_backend(std::make_shared<GncSqlOrderBackend>());
1000  register_backend(std::make_shared<GncSqlTaxTableBackend>());
1001  register_backend(std::make_shared<GncSqlVendorBackend>());
1002 }
1003 
1004 void
1005 GncSqlBackend::ObjectBackendRegistry::register_backend(OBEEntry&& entry) noexcept
1006 {
1007  m_registry.emplace_back(entry);
1008 }
1009 
1010 void
1011 GncSqlBackend::ObjectBackendRegistry::register_backend(GncSqlObjectBackendPtr obe) noexcept
1012 {
1013  m_registry.emplace_back(make_tuple(std::string{obe->type()}, obe));
1014 }
1015 
1016 GncSqlObjectBackendPtr
1017 GncSqlBackend::ObjectBackendRegistry::get_object_backend(const std::string& type) const
1018 {
1019  auto entry = std::find_if(m_registry.begin(), m_registry.end(),
1020  [type](const OBEEntry& entry){
1021  return type == std::get<0>(entry);
1022  });
1023  if (entry == m_registry.end())
1024  return nullptr;
1025 
1026  return std::get<1>(*entry);
1027 }
bool do_db_operation(E_DB_OPERATION op, const char *table_name, QofIdTypeConst obj_name, gpointer pObject, const EntryVec &table) const noexcept
Performs an operation on the database.
bool add_columns_to_table(const std::string &table_name, const EntryVec &col_table) const noexcept
Adds one or more columns to an existing table.
bool create_table(const std::string &table_name, const EntryVec &col_table) const noexcept
Creates a table in the database.
int xaccAccountTreeForEachTransaction(Account *acc, TransactionCallback proc, void *data)
Traverse all of the transactions in the given account group.
load and save vendor data to SQL
bool set_table_version(const std::string &table_name, uint_t version) noexcept
Registers the version for a table.
GncSqlResultPtr execute_select_statement(const GncSqlStatementPtr &stmt) const noexcept
Executes an SQL SELECT statement and returns the result rows.
gint gnc_account_n_descendants(const Account *account)
Return the number of descendants of the specified account.
Definition: Account.cpp:2981
void gnc_account_foreach_descendant(const Account *acc, AccountCb thunk, gpointer user_data)
This method will traverse all children of this accounts and their descendants, calling &#39;func&#39; on each...
Definition: Account.cpp:3245
a simple price database for gnucash
#define G_LOG_DOMAIN
Functions providing the SX List as a plugin page.
load and save data to SQL
const gchar * QofIdTypeConst
QofIdTypeConst declaration.
Definition: qofid.h:87
load and save accounts data to SQL
VersionVec m_versions
Version number for each table.
void rollback(QofInstance *) override
Object editing has been cancelled.
void qof_backend_set_error(QofBackend *qof_be, QofBackendError err)
Set the error on the specified QofBackend.
#define DEBUG(format, args...)
Print a debugging message.
Definition: qoflog.h:264
gboolean qof_instance_get_destroying(gconstpointer ptr)
Retrieve the flag that indicates whether or not this object is about to be destroyed.
void commit(QofInstance *) override
Object editing is complete and the object should be saved.
void create_tables() noexcept
Create/update all tables in the database.
load and save customer data to SQL
Account * gnc_book_get_template_root(const QofBook *book)
Returns the template group from the book.
Definition: SX-book.c:65
bool m_loading
We are performing an initial load.
void commodity_for_postload_processing(gnc_commodity *)
Register a commodity to be committed after loading is complete.
GncSqlConnection * m_conn
SQL connection.
load and save accounts data to SQL
load and save data to SQL
void load(QofBook *, QofBackendLoadType) override
Load the contents of an SQL database into a book.
#define PERR(format, args...)
Log a serious error.
Definition: qoflog.h:244
#define ENTER(format, args...)
Print a function entry debugging message.
Definition: qoflog.h:272
void sync(QofBook *) override
Save the contents of a book to an SQL database.
bool object_in_db(const char *table_name, QofIdTypeConst obj_name, const gpointer pObject, const EntryVec &table) const noexcept
Checks whether an object is in the database or not.
error in response from server
Definition: qofbackend.h:71
const gchar * QofIdType
QofIdType declaration.
Definition: qofid.h:85
load and save accounts data to SQL
load and save order data to SQL
bool save_commodity(gnc_commodity *comm) noexcept
Ensure that a commodity referenced in another object is in fact saved in the database.
void qof_book_mark_session_saved(QofBook *book)
The qof_book_mark_saved() routine marks the book as having been saved (to a file, to a database)...
Definition: qofbook.cpp:393
load and save job data to SQL
load and save accounts data to SQL
void upgrade_table(const std::string &table_name, const EntryVec &col_table) noexcept
Upgrades a table to a new structure.
load and save data to SQL
gboolean qof_instance_get_dirty_flag(gconstpointer ptr)
Retrieve the flag that indicates whether or not this object has been modified.
QofBook * m_book
The primary, main open book.
Anchor Scheduled Transaction info in a book.
GncSqlObjectBackendPtr get_object_backend(const std::string &type) const noexcept
Get the GncSqlObjectBackend for the indicated type.
load and save employee data to SQL
load and save data to SQL
Tax Table programming interface.
void init_version_info() noexcept
Initializes DB table version information.
All type declarations for the whole Gnucash engine.
virtual bool commit_transaction() noexcept=0
Returns TRUE if successful, FALSE if error.
load and save data to SQL
Generic api to store and retrieve preferences.
GList * gnc_account_get_descendants(const Account *account)
This routine returns a flat list of all of the accounts that are descendants of the specified account...
Definition: Account.cpp:3036
Business Invoice Interface.
QofIdType e_type
Entity type.
Definition: qofinstance.h:71
gboolean qof_book_is_readonly(const QofBook *book)
Return whether the book is read only.
Definition: qofbook.cpp:506
void begin(QofInstance *) override
An object is about to be edited.
Encapsulate the connection to the database.
bool create_index(const std::string &index_name, const std::string &table_name, const EntryVec &col_table) const noexcept
Creates an index in the database.
Data-passing struct for callbacks to qof_object_foreach() used in GncSqlObjectBackend::write().
void xaccAccountBeginEdit(Account *acc)
The xaccAccountBeginEdit() subroutine is the first phase of a two-phase-commit wrapper for account up...
Definition: Account.cpp:1449
void connect(GncSqlConnection *conn) noexcept
Connect the backend to a GncSqlConnection.
cannot write to file/directory
Definition: qofbackend.h:68
load and save entry data to SQL
bool m_is_pristine_db
Are we saving to a new pristine db?
#define LEAVE(format, args...)
Print a function exit debugging message.
Definition: qoflog.h:282
Pure virtual class to iterate over a query result set.
virtual bool begin_transaction() noexcept=0
Returns TRUE if successful, false if error.
load and save data to SQL
virtual bool rollback_transaction() noexcept=0
Returns TRUE if successful, FALSE if error.
load and save invoice data to SQL
virtual bool does_table_exist(const std::string &) const noexcept=0
Returns true if successful.
A Query.
Definition: qofquery.cpp:77
bool reset_version_info() noexcept
Resets the version table information by removing all version table info.
void xaccAccountCommitEdit(Account *acc)
ThexaccAccountCommitEdit() subroutine is the second phase of a two-phase-commit wrapper for account u...
Definition: Account.cpp:1490
uint_t get_table_version(const std::string &table_name) const noexcept
Returns the version number for a DB table.
SplitList * xaccTransGetSplitList(const Transaction *trans)
The xaccTransGetSplitList() method returns a GList of the splits in a transaction.
Billing Term interface.
Commodity handling public routines.
void set_error(QofBackendError err)
Set the error value only if there isn&#39;t already an error already.
Definition: qof-backend.cpp:59
Main SQL backend structure.
void finalize_version_info() noexcept
Finalizes DB table version information.
load and save tax table data to SQL