28 #include <Recurrence.h> 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" 50 #include "gnc-schedxaction-sql.h" 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" 70 using StrVec = std::vector<std::string>;
72 static std::string empty_string{};
73 static EntryVec version_table
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)
81 QofBackend {}, m_conn{conn}, m_book{book}, m_loading{
false},
82 m_in_query{
false}, m_is_pristine_db{
false}
88 GncSqlBackend::~GncSqlBackend()
96 if (m_conn !=
nullptr && m_conn != conn)
98 finalize_version_info();
103 GncSqlBackend::create_statement_from_sql(
const std::string& str)
const noexcept
105 auto stmt = m_conn ? m_conn->create_statement_from_sql(str) :
nullptr;
108 PERR (
"SQL error: %s\n", str.c_str());
117 auto result = m_conn ? m_conn->execute_select_statement(stmt) :
nullptr;
118 if (result ==
nullptr)
120 PERR (
"SQL error: %s\n", stmt->to_sql());
127 GncSqlBackend::execute_nonselect_statement(
const GncSqlStatementPtr& stmt)
const noexcept
129 int result = m_conn ? m_conn->execute_nonselect_statement(stmt) : -1;
132 PERR (
"SQL error: %s\n", stmt->to_sql());
139 GncSqlBackend::quote_string(
const std::string& str)
const noexcept
141 g_return_val_if_fail (m_conn !=
nullptr, empty_string);
144 return m_conn->quote_string(str);
149 const EntryVec& col_table)
const noexcept
151 g_return_val_if_fail (m_conn !=
nullptr,
false);
155 for (
auto const& table_row : col_table)
157 table_row->add_to_table (info_vec);
159 return m_conn->create_table (table_name, info_vec);
165 const EntryVec& col_table) noexcept
167 if (create_table (table_name, col_table))
168 return set_table_version (table_name, table_version);
174 const std::string& table_name,
175 const EntryVec& col_table)
const noexcept
177 g_return_val_if_fail (m_conn !=
nullptr,
false);
178 return m_conn->create_index(index_name, table_name, col_table);
183 const EntryVec& col_table)
const noexcept
185 g_return_val_if_fail (m_conn !=
nullptr,
false);
189 for (
auto const& table_row : col_table)
191 table_row->add_to_table (info_vec);
193 return m_conn->add_columns_to_table(table_name, info_vec);
197 GncSqlBackend::update_progress(
double pct)
const noexcept
199 if (m_percentage !=
nullptr)
200 (m_percentage) (
nullptr, pct);
204 GncSqlBackend::finish_progress() const noexcept
206 if (m_percentage !=
nullptr)
207 (m_percentage) (
nullptr, -1.0);
213 for(
auto entry : m_backend_registry)
215 update_progress(101.0);
221 static const StrVec fixed_load_order
222 { GNC_ID_BOOK, GNC_ID_COMMODITY, GNC_ID_ACCOUNT, GNC_ID_LOT, GNC_ID_TRANS };
225 static const StrVec business_fixed_load_order =
226 { GNC_ID_BILLTERM, GNC_ID_TAXTABLE, GNC_ID_INVOICE };
229 GncSqlBackend::ObjectBackendRegistry::load_remaining(
GncSqlBackend* sql_be)
232 auto num_types = m_registry.size();
233 auto num_done = fixed_load_order.size() + business_fixed_load_order.size();
235 for (
const auto& entry : m_registry)
238 GncSqlObjectBackendPtr obe =
nullptr;
239 std::tie(type, obe) = entry;
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;
251 sql_be->update_progress(num_done * 100 / num_types);
252 obe->load_all (sql_be);
259 gpointer pCompiledQuery;
270 gpointer pCompiledQuery;
280 g_return_if_fail (book != NULL);
282 ENTER (
"sql_be=%p, book=%p",
this, book);
286 if (loadType == LOAD_TYPE_INITIAL_LOAD)
288 assert (
m_book ==
nullptr);
291 auto num_types = m_backend_registry.size();
295 for (
const auto& type : fixed_load_order)
298 auto obe = m_backend_registry.get_object_backend(type);
301 update_progress(num_done * 100 / num_types);
305 for (
const auto& type : business_fixed_load_order)
308 auto obe = m_backend_registry.get_object_backend(type);
311 update_progress(num_done * 100 / num_types);
316 root = gnc_book_get_root_account( book );
320 m_backend_registry.load_remaining(
this);
325 else if (loadType == LOAD_TYPE_LOAD_ALL)
328 auto obe = m_backend_registry.get_object_backend (GNC_ID_TRANS);
329 obe->load_all (
this);
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);
338 m_postload_commodities.clear();
352 GncSqlBackend::write_account_tree(
Account* root)
358 g_return_val_if_fail (root !=
nullptr,
false);
360 auto obe = m_backend_registry.get_object_backend(GNC_ID_ACCOUNT);
361 is_ok = obe->commit (
this, QOF_INSTANCE (root));
365 for (node = descendants; node != NULL && is_ok; node = g_list_next (node))
367 is_ok = obe->commit(
this, QOF_INSTANCE (GNC_ACCOUNT (node->data)));
370 g_list_free (descendants);
372 update_progress(101.0);
378 GncSqlBackend::write_accounts()
380 update_progress(101.0);
381 auto is_ok = write_account_tree (gnc_book_get_root_account (
m_book));
384 update_progress(101.0);
392 write_tx (Transaction* tx, gpointer data)
396 g_return_val_if_fail (tx != NULL, 0);
397 g_return_val_if_fail (data != NULL, 0);
399 s->commit (QOF_INSTANCE (tx));
400 auto splitbe = s->be->get_object_backend(GNC_ID_SPLIT);
402 split_node !=
nullptr && s->is_ok;
403 split_node = g_list_next (split_node))
405 s->is_ok = splitbe->commit(s->be, QOF_INSTANCE(split_node->data));
407 s->be->update_progress (101.0);
408 return (s->is_ok ? 0 : 1);
412 GncSqlBackend::write_transactions()
414 auto obe = m_backend_registry.get_object_backend(GNC_ID_TRANS);
418 gnc_book_get_root_account (
m_book), write_tx, &data);
419 update_progress(101.0);
424 GncSqlBackend::write_template_transactions()
426 auto obe = m_backend_registry.get_object_backend(GNC_ID_TRANS);
432 update_progress(101.0);
439 GncSqlBackend::write_schedXactions()
441 GList* schedXactions;
445 schedXactions = gnc_book_get_schedxactions (
m_book)->sx_list;
446 auto obe = m_backend_registry.get_object_backend(GNC_ID_SCHEDXACTION);
448 for (; schedXactions != NULL && is_ok; schedXactions = schedXactions->next)
450 tmpSX =
static_cast<decltype (tmpSX)
> (schedXactions->data);
451 is_ok = obe->commit (
this, QOF_INSTANCE (tmpSX));
453 update_progress(101.0);
461 g_return_if_fail (book != NULL);
462 g_return_if_fail (
m_conn !=
nullptr);
466 update_progress(101.0);
480 auto obe = m_backend_registry.get_object_backend(GNC_ID_BOOK);
481 is_ok = obe->commit (
this, QOF_INSTANCE (book));
485 is_ok = write_accounts();
489 is_ok = write_transactions();
493 is_ok = write_template_transactions();
497 is_ok = write_schedXactions();
501 for (
auto entry : m_backend_registry)
502 std::get<1>(entry)->write (
this);
523 LEAVE (
"book=%p", book);
550 m_postload_commodities.push_back(commodity);
553 GncSqlObjectBackendPtr
556 return m_backend_registry.get_object_backend(type);
567 gboolean is_destroying;
570 g_return_if_fail (inst != NULL);
571 g_return_if_fail (
m_conn !=
nullptr);
583 qof_instance_mark_clean (inst);
588 if (strcmp (inst->
e_type,
"PriceDB") == 0)
590 qof_instance_mark_clean (inst);
599 is_infant = qof_instance_get_infant (inst);
601 DEBUG (
"%s dirty = %d, do_free = %d, infant = %d\n",
603 is_dirty, is_destroying, is_infant);
605 if (!is_dirty && !is_destroying)
607 LEAVE (
"!dirty OR !destroying");
613 PERR (
"begin_transaction failed\n");
614 LEAVE (
"Rolled back - database transaction begin error");
620 auto obe = m_backend_registry.get_object_backend(std::string{inst->
e_type});
622 is_ok = obe->commit(
this, inst);
625 PERR (
"Unknown object type '%s'\n", inst->
e_type);
630 qof_instance_mark_clean (inst);
631 LEAVE (
"Rolled back - unknown object type");
640 LEAVE (
"Rolled back - database error");
647 qof_instance_mark_clean (inst);
662 g_return_if_fail (
m_conn !=
nullptr);
665 std::string sql {
"SELECT * FROM "};
666 sql += VERSION_TABLE_NAME;
667 auto stmt =
m_conn->create_statement_from_sql(sql);
668 auto result =
m_conn->execute_select_statement (stmt);
669 for (
const auto& row : *result)
671 auto name = row.get_string_at_col (TABLE_COL_NAME);
672 auto version = row.get_int_at_col (VERSION_COL_NAME);
674 m_versions.push_back(std::make_pair(*name, static_cast<unsigned int>(*version)));
695 bool ok =
create_table (VERSION_TABLE_NAME, version_table);
717 if (m_is_pristine_db)
720 auto version = std::find_if(m_versions.begin(), m_versions.end(),
721 [table_name](
const VersionPair& version) {
722 return version.first == table_name; });
723 if (version != m_versions.end())
724 return version->second;
739 uint_t version) noexcept
741 g_return_val_if_fail (version > 0,
false);
743 unsigned int cur_version{0};
744 std::stringstream sql;
745 auto ver_entry = std::find_if(m_versions.begin(), m_versions.end(),
746 [table_name](
const VersionPair& ver) {
747 return ver.first == table_name; });
748 if (ver_entry != m_versions.end())
749 cur_version = ver_entry->second;
750 if (cur_version != version)
752 if (cur_version == 0)
754 sql <<
"INSERT INTO " << VERSION_TABLE_NAME <<
" VALUES('" <<
755 table_name <<
"'," << version <<
")";
756 m_versions.push_back(std::make_pair(table_name, version));
760 sql <<
"UPDATE " << VERSION_TABLE_NAME <<
" SET " <<
761 VERSION_COL_NAME <<
"=" << version <<
" WHERE " <<
762 TABLE_COL_NAME <<
"='" << table_name <<
"'";
763 ver_entry->second = version;
765 auto stmt = create_statement_from_sql(sql.str());
766 auto status = execute_nonselect_statement (stmt);
769 PERR (
"SQL error: %s\n", sql.str().c_str());
780 const EntryVec& col_table) noexcept
782 DEBUG (
"Upgrading %s table\n", table_name.c_str());
784 auto temp_table_name = table_name +
"_new";
785 create_table (temp_table_name, col_table);
786 std::stringstream sql;
787 sql <<
"INSERT INTO " << temp_table_name <<
" SELECT * FROM " << table_name;
788 auto stmt = create_statement_from_sql(sql.str());
789 execute_nonselect_statement(stmt);
792 sql <<
"DROP TABLE " << table_name;
793 stmt = create_statement_from_sql(sql.str());
794 execute_nonselect_statement(stmt);
797 sql <<
"ALTER TABLE " << temp_table_name <<
" RENAME TO " << table_name;
798 stmt = create_statement_from_sql(sql.str());
799 execute_nonselect_statement(stmt);
802 static inline PairVec
804 gpointer pObject,
const EntryVec&
table)
808 for (
auto const& table_row :
table)
810 if (!(table_row->is_autoincr()))
812 table_row->add_to_query (obj_name, pObject, vec);
820 const gpointer pObject,
const EntryVec&
table)
const noexcept
822 g_return_val_if_fail (table_name !=
nullptr,
false);
823 g_return_val_if_fail (obj_name !=
nullptr,
false);
824 g_return_val_if_fail (pObject !=
nullptr,
false);
827 auto sql = std::string{
"SELECT "} +
table[0]->name() +
" FROM " + table_name;
828 auto stmt = create_statement_from_sql(sql.c_str());
829 assert (stmt !=
nullptr);
832 PairVec values{get_object_values(obj_name, pObject,
table)};
835 stmt->add_where_cond(obj_name, values);
836 auto result = execute_select_statement (stmt);
837 return (result !=
nullptr && result->size() > 0);
843 const EntryVec&
table)
const noexcept
845 GncSqlStatementPtr stmt;
847 g_return_val_if_fail (table_name !=
nullptr,
false);
848 g_return_val_if_fail (obj_name !=
nullptr,
false);
849 g_return_val_if_fail (pObject !=
nullptr,
false);
854 stmt = build_insert_statement (table_name, obj_name, pObject,
table);
857 stmt = build_update_statement (table_name, obj_name, pObject,
table);
860 stmt = build_delete_statement (table_name, obj_name, pObject,
table);
865 return (execute_nonselect_statement(stmt) != -1);
871 if (comm ==
nullptr)
return false;
873 auto obe = m_backend_registry.get_object_backend(std::string(inst->
e_type));
874 if (obe && !obe->instance_in_db(
this, inst))
875 return obe->commit(
this, inst);
880 GncSqlBackend::build_insert_statement (
const char* table_name,
883 const EntryVec&
table)
const noexcept
885 GncSqlStatementPtr stmt;
887 std::ostringstream sql;
889 g_return_val_if_fail (table_name !=
nullptr,
nullptr);
890 g_return_val_if_fail (obj_name !=
nullptr,
nullptr);
891 g_return_val_if_fail (pObject !=
nullptr,
nullptr);
892 PairVec values{get_object_values(obj_name, pObject,
table)};
894 sql <<
"INSERT INTO " << table_name <<
"(";
895 for (
auto const& col_value : values)
897 if (col_value != *values.begin())
899 sql << col_value.first;
903 for (
const auto& col_value : values)
905 if (col_value != *values.begin())
907 sql << col_value.second;
911 stmt = create_statement_from_sql(sql.str());
916 GncSqlBackend::build_update_statement(
const gchar* table_name,
918 const EntryVec&
table)
const noexcept
920 GncSqlStatementPtr stmt;
921 std::ostringstream sql;
923 g_return_val_if_fail (table_name !=
nullptr,
nullptr);
924 g_return_val_if_fail (obj_name !=
nullptr,
nullptr);
925 g_return_val_if_fail (pObject !=
nullptr,
nullptr);
928 PairVec values{get_object_values (obj_name, pObject,
table)};
931 sql <<
"UPDATE " << table_name <<
" SET ";
933 for (
auto const& col_value : values)
935 if (col_value != *values.begin())
937 sql << col_value.first <<
"=" <<
941 stmt = create_statement_from_sql(sql.str());
945 values.erase(values.begin() + 1, values.end());
946 stmt->add_where_cond(obj_name, values);
951 GncSqlBackend::build_delete_statement(
const gchar* table_name,
954 const EntryVec&
table)
const noexcept
956 std::ostringstream sql;
958 g_return_val_if_fail (table_name !=
nullptr,
nullptr);
959 g_return_val_if_fail (obj_name !=
nullptr,
nullptr);
960 g_return_val_if_fail (pObject !=
nullptr,
nullptr);
962 sql <<
"DELETE FROM " << table_name;
963 auto stmt = create_statement_from_sql (sql.str());
967 table[0]->add_to_query (obj_name, pObject, values);
968 PairVec col_values{values[0]};
969 stmt->add_where_cond (obj_name, col_values);
974 GncSqlBackend::ObjectBackendRegistry::ObjectBackendRegistry()
976 register_backend(std::make_shared<GncSqlBookBackend>());
977 register_backend(std::make_shared<GncSqlCommodityBackend>());
978 register_backend(std::make_shared<GncSqlAccountBackend>());
979 register_backend(std::make_shared<GncSqlBudgetBackend>());
980 register_backend(std::make_shared<GncSqlPriceBackend>());
981 register_backend(std::make_shared<GncSqlTransBackend>());
982 register_backend(std::make_shared<GncSqlSplitBackend>());
983 register_backend(std::make_shared<GncSqlSlotsBackend>());
984 register_backend(std::make_shared<GncSqlRecurrenceBackend>());
985 register_backend(std::make_shared<GncSqlSchedXactionBackend>());
986 register_backend(std::make_shared<GncSqlLotsBackend>());
987 register_backend(std::make_shared<GncSqlBillTermBackend>());
988 register_backend(std::make_shared<GncSqlCustomerBackend>());
989 register_backend(std::make_shared<GncSqlEmployeeBackend>());
990 register_backend(std::make_shared<GncSqlEntryBackend>());
991 register_backend(std::make_shared<GncSqlInvoiceBackend>());
992 register_backend(std::make_shared<GncSqlJobBackend>());
993 register_backend(std::make_shared<GncSqlOrderBackend>());
994 register_backend(std::make_shared<GncSqlTaxTableBackend>());
995 register_backend(std::make_shared<GncSqlVendorBackend>());
999 GncSqlBackend::ObjectBackendRegistry::register_backend(OBEEntry&& entry) noexcept
1001 m_registry.emplace_back(entry);
1005 GncSqlBackend::ObjectBackendRegistry::register_backend(GncSqlObjectBackendPtr obe) noexcept
1007 m_registry.emplace_back(make_tuple(std::string{obe->type()}, obe));
1010 GncSqlObjectBackendPtr
1011 GncSqlBackend::ObjectBackendRegistry::get_object_backend(
const std::string& type)
const 1013 auto entry = std::find_if(m_registry.begin(), m_registry.end(),
1014 [type](
const OBEEntry& entry){
1015 return type == std::get<0>(entry);
1017 if (entry == m_registry.end())
1020 return std::get<1>(*entry);
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.
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.
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.
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.
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.
#define ENTER(format, args...)
Print a function entry debugging message.
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
const gchar * QofIdType
QofIdType declaration.
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)...
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...
Business Invoice Interface.
QofIdType e_type
Entity type.
gboolean qof_book_is_readonly(const QofBook *book)
Return whether the book is read only.
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...
void connect(GncSqlConnection *conn) noexcept
Connect the backend to a GncSqlConnection.
cannot write to file/directory
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.
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.
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...
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.
Commodity handling public routines.
void set_error(QofBackendError err)
Set the error value only if there isn't already an error already.
Main SQL backend structure.
void finalize_version_info() noexcept
Finalizes DB table version information.
load and save tax table data to SQL