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