31 #include <glib/gi18n.h> 34 #include "qofquery-p.h" 35 #include "qofquerycore-p.h" 41 #include "engine-helpers.h" 46 #include "splint-defs.h" 54 #include <gnc-datetime.hpp> 55 #include "gnc-sql-connection.hpp" 56 #include "gnc-sql-backend.hpp" 57 #include "gnc-sql-object-backend.hpp" 58 #include "gnc-sql-column-table-entry.hpp" 63 #define SIMPLE_QUERY_COMPILATION 1 67 #define TRANSACTION_TABLE "transactions" 68 #define TX_TABLE_VERSION 4 69 #define SPLIT_TABLE "splits" 70 #define SPLIT_TABLE_VERSION 5 81 #define TX_MAX_NUM_LEN 2048 82 #define TX_MAX_DESCRIPTION_LEN 2048 84 static const EntryVec tx_col_table
86 gnc_sql_make_table_entry<CT_GUID>(
"guid", 0, COL_NNUL | COL_PKEY,
"guid"),
87 gnc_sql_make_table_entry<CT_COMMODITYREF>(
"currency_guid", 0, COL_NNUL,
89 gnc_sql_make_table_entry<CT_STRING>(
"num", TX_MAX_NUM_LEN, COL_NNUL,
"num"),
90 gnc_sql_make_table_entry<CT_TIME>(
"post_date", 0, 0,
"post-date"),
91 gnc_sql_make_table_entry<CT_TIME>(
"enter_date", 0, 0,
"enter-date"),
92 gnc_sql_make_table_entry<CT_STRING>(
"description", TX_MAX_DESCRIPTION_LEN,
96 static gpointer get_split_reconcile_state (gpointer pObject);
97 static void set_split_reconcile_state (gpointer pObject, gpointer pValue);
98 static void set_split_lot (gpointer pObject, gpointer pLot);
100 #define SPLIT_MAX_MEMO_LEN 2048 101 #define SPLIT_MAX_ACTION_LEN 2048 103 static const EntryVec split_col_table
105 gnc_sql_make_table_entry<CT_GUID>(
"guid", 0, COL_NNUL | COL_PKEY,
"guid"),
106 gnc_sql_make_table_entry<CT_TXREF>(
"tx_guid", 0, COL_NNUL,
"transaction"),
107 gnc_sql_make_table_entry<CT_ACCOUNTREF>(
"account_guid", 0, COL_NNUL,
109 gnc_sql_make_table_entry<CT_STRING>(
"memo", SPLIT_MAX_MEMO_LEN, COL_NNUL,
111 gnc_sql_make_table_entry<CT_STRING>(
"action", SPLIT_MAX_ACTION_LEN,
113 gnc_sql_make_table_entry<CT_STRING>(
"reconcile_state", 1, COL_NNUL,
115 set_split_reconcile_state),
116 gnc_sql_make_table_entry<CT_TIME>(
"reconcile_date", 0, 0,
118 gnc_sql_make_table_entry<CT_NUMERIC>(
"value", 0, COL_NNUL,
"value"),
119 gnc_sql_make_table_entry<CT_NUMERIC>(
"quantity", 0, COL_NNUL,
"amount"),
120 gnc_sql_make_table_entry<CT_LOTREF>(
"lot_guid", 0, 0,
125 static const EntryVec post_date_col_table
127 gnc_sql_make_table_entry<CT_TIME>(
"post_date", 0, 0,
"post-date"),
130 static const EntryVec account_guid_col_table
132 gnc_sql_make_table_entry<CT_ACCOUNTREF>(
"account_guid", 0, COL_NNUL,
136 static const EntryVec tx_guid_col_table
138 gnc_sql_make_table_entry<CT_GUID>(
"tx_guid", 0, 0,
"guid"),
141 GncSqlTransBackend::GncSqlTransBackend() :
143 TRANSACTION_TABLE, tx_col_table) {}
145 GncSqlSplitBackend::GncSqlSplitBackend() :
147 SPLIT_TABLE, split_col_table) {}
159 get_split_reconcile_state (gpointer pObject)
163 g_return_val_if_fail (pObject != NULL, NULL);
164 g_return_val_if_fail (GNC_IS_SPLIT (pObject), NULL);
172 set_split_reconcile_state (gpointer pObject, gpointer pValue)
174 const gchar* s = (
const gchar*)pValue;
176 g_return_if_fail (pObject != NULL);
177 g_return_if_fail (GNC_IS_SPLIT (pObject));
178 g_return_if_fail (pValue != NULL);
184 set_split_lot (gpointer pObject, gpointer pLot)
189 g_return_if_fail (pObject != NULL);
190 g_return_if_fail (GNC_IS_SPLIT (pObject));
192 if (pLot == NULL)
return;
194 g_return_if_fail (GNC_IS_LOT (pLot));
196 split = GNC_SPLIT (pObject);
197 lot = GNC_LOT (pLot);
206 Split* pSplit = NULL;
208 g_return_val_if_fail (sql_be != NULL, NULL);
210 guid = gnc_sql_load_guid (sql_be, row);
211 if (guid == NULL)
return NULL;
214 PWARN (
"Bad GUID, creating new");
227 gnc_sql_load_object (sql_be, row, GNC_ID_SPLIT, pSplit, split_col_table);
234 PERR (
"A malformed split with id %s was found in the dataset.", guidstr);
242 PERR(
"Split %s created with no account!", guidstr);
247 load_splits_for_transactions (
GncSqlBackend* sql_be, std::string selector)
249 g_return_if_fail (sql_be != NULL);
251 const std::string spkey(split_col_table[0]->name());
252 const std::string sskey(tx_guid_col_table[0]->name());
253 const std::string tpkey(tx_col_table[0]->name());
255 std::string sql(
"SELECT ");
256 if (selector.empty())
258 sql += SPLIT_TABLE
".* FROM " SPLIT_TABLE
" INNER JOIN " 259 TRANSACTION_TABLE
" ON " SPLIT_TABLE
"." + sskey +
" = " 260 TRANSACTION_TABLE
"." + tpkey;
261 selector =
"(SELECT DISTINCT " + tpkey +
" FROM " TRANSACTION_TABLE
")";
264 sql +=
" * FROM " SPLIT_TABLE
" WHERE " + sskey +
" IN " + selector;
267 auto stmt = sql_be->create_statement_from_sql(sql);
270 for (
auto row : *result)
271 load_single_split (sql_be, row);
272 sql =
"SELECT DISTINCT ";
273 sql += spkey +
" FROM " SPLIT_TABLE
" WHERE " + sskey +
" IN " + selector;
285 g_return_val_if_fail (sql_be != NULL, NULL);
287 guid = gnc_sql_load_guid (sql_be, row);
288 if (guid == NULL)
return NULL;
297 gnc_sql_load_object (sql_be, row, GNC_ID_TRANS, pTx, tx_col_table);
303 PERR (
"A malformed transaction with id %s was found in the dataset.", guidstr);
320 gnc_numeric start_bal;
322 gnc_numeric start_cleared_bal;
323 gnc_numeric end_cleared_bal;
324 gnc_numeric start_reconciled_bal;
325 gnc_numeric end_reconciled_bal;
336 query_transactions (
GncSqlBackend* sql_be, std::string selector)
338 g_return_if_fail (sql_be != NULL);
340 const std::string tpkey(tx_col_table[0]->name());
341 std::string sql(
"SELECT * FROM " TRANSACTION_TABLE);
343 if (!selector.empty() && selector[0] ==
'(')
344 sql +=
" WHERE " + tpkey +
" IN " + selector;
345 else if (!selector.empty())
346 sql +=
" WHERE " + selector;
347 auto stmt = sql_be->create_statement_from_sql(sql);
349 if (result->begin() == result->end())
351 PINFO(
"Query %s returned no results", sql.c_str());
358 InstanceVec instances;
359 instances.reserve(result->size());
360 for (
auto row : *result)
362 tx = load_single_tx (sql_be, row);
366 instances.push_back(QOF_INSTANCE(tx));
371 if (!instances.empty())
373 const std::string tpkey(tx_col_table[0]->name());
374 if (!selector.empty() && (selector[0] !=
'('))
376 auto tselector = std::string (
"(SELECT DISTINCT ");
377 tselector += tpkey +
" FROM " TRANSACTION_TABLE
" WHERE " + selector +
")";
378 selector = tselector;
381 load_splits_for_transactions (sql_be, selector);
383 if (selector.empty())
385 selector =
"SELECT DISTINCT ";
386 selector += tpkey +
" FROM " TRANSACTION_TABLE;
393 for (
auto instance : instances)
411 g_return_if_fail (sql_be != NULL);
416 (void)sql_be->
create_table(TRANSACTION_TABLE, TX_TABLE_VERSION,
418 ok = sql_be->
create_index (
"tx_post_date_index", TRANSACTION_TABLE,
419 post_date_col_table);
422 PERR (
"Unable to create index\n");
425 else if (version < m_version)
434 PINFO (
"Transactions table upgraded from version %d to version %d\n",
441 g_return_if_fail (sql_be !=
nullptr);
449 m_table_name.c_str(), tx_guid_col_table))
450 PERR (
"Unable to create index\n");
452 m_table_name.c_str(),
453 account_guid_col_table))
454 PERR (
"Unable to create index\n");
456 else if (version < SPLIT_TABLE_VERSION)
464 sql_be->
upgrade_table(m_table_name.c_str(), split_col_table);
466 m_table_name.c_str(),
468 PERR (
"Unable to create index\n");
470 m_table_name.c_str(),
471 account_guid_col_table))
472 PERR (
"Unable to create index\n");
474 PINFO (
"Splits table upgraded from version %d to version %d\n", version,
486 delete_split_slots_cb (gpointer data, gpointer user_data)
489 Split* pSplit = GNC_SPLIT (data);
491 g_return_if_fail (data != NULL);
492 g_return_if_fail (GNC_IS_SPLIT (data));
493 g_return_if_fail (user_data != NULL);
495 if (split_info->is_ok)
514 g_return_val_if_fail (sql_be != NULL, FALSE);
515 g_return_val_if_fail (pTx != NULL, FALSE);
518 SPLIT_TABLE, pTx, tx_guid_col_table))
522 split_info.be = sql_be;
523 split_info.is_ok = TRUE;
528 return split_info.is_ok;
546 g_return_val_if_fail (inst != NULL, FALSE);
547 g_return_val_if_fail (sql_be != NULL, FALSE);
549 is_infant = qof_instance_get_infant (inst);
554 else if (sql_be->pristine() || is_infant)
566 qof_instance_set_guid (inst, guid);
570 inst, split_col_table);
585 gboolean is_ok = TRUE;
586 const char* err = NULL;
588 g_return_val_if_fail (sql_be != NULL, FALSE);
589 g_return_val_if_fail (inst != NULL, FALSE);
591 auto pTx = GNC_TRANS(inst);
592 auto is_infant = qof_instance_get_infant (inst);
597 else if (sql_be->pristine() || is_infant)
606 if (op != OP_DB_DELETE)
613 err =
"Commodity save failed: Probably an invalid or missing currency";
624 err =
"Transaction header save failed. Check trace log for SQL errors";
637 err =
"Slots save failed. Check trace log for SQL errors";
645 err =
"Slots delete failed. Check trace log for SQL errors";
649 is_ok = delete_splits (sql_be, pTx);
652 err =
"Split delete failed. Check trace log for SQL errors";
673 PERR (
"Transaction %s dated %s in account %s not saved due to %s.\n",
692 g_return_if_fail (sql_be != NULL);
693 g_return_if_fail (account != NULL);
697 const std::string tpkey(tx_col_table[0]->name());
698 const std::string spkey(split_col_table[0]->name());
699 const std::string stkey(split_col_table[1]->name());
700 const std::string sakey(split_col_table[2]->name());
701 std::string sql(
"(SELECT DISTINCT ");
702 sql += stkey +
" FROM " SPLIT_TABLE
" WHERE " + sakey +
" = '";
703 sql +=
gnc::GUID(*guid).to_string() +
"')";
704 query_transactions (sql_be, sql);
716 g_return_if_fail (sql_be != NULL);
718 auto root = gnc_book_get_root_account (sql_be->book());
721 query_transactions (sql_be,
"");
728 GncSqlStatementPtr stmt;
729 gboolean has_been_run;
737 char reconcile_state;
742 set_acct_bal_account_from_guid (gpointer pObject, gpointer pValue)
747 g_return_if_fail (pObject != NULL);
748 g_return_if_fail (pValue != NULL);
754 set_acct_bal_reconcile_state (gpointer pObject, gpointer pValue)
757 const gchar* s = (
const gchar*)pValue;
759 g_return_if_fail (pObject != NULL);
760 g_return_if_fail (pValue != NULL);
762 bal->reconcile_state = s[0];
766 set_acct_bal_balance (gpointer pObject, gnc_numeric value)
770 g_return_if_fail (pObject != NULL);
772 bal->balance = value;
775 static const EntryVec acct_balances_col_table
777 gnc_sql_make_table_entry<CT_GUID>(
"account_guid", 0, 0,
nullptr,
779 gnc_sql_make_table_entry<CT_STRING>(
"reconcile_state", 1, 0,
nullptr,
781 gnc_sql_make_table_entry<CT_NUMERIC>(
"quantity", 0, 0,
nullptr,
790 gpointer pObject)
const noexcept
792 g_return_if_fail (sql_be != NULL);
793 g_return_if_fail (pObject != NULL);
795 auto val = row.get_string_at_col (m_col_name);
800 Transaction *tx =
nullptr;
805 std::string tpkey(tx_col_table[0]->name());
808 std::string sql = tpkey +
" = '" + *val +
"'";
814 set_parameter (pObject, tx, get_setter(obj_name), m_gobj_param_name);
821 add_objectref_guid_to_table(vec);
826 const gpointer pObject,
827 PairVec& vec)
const noexcept
829 add_objectref_guid_to_query(obj_name, pObject, vec);
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 create_table(const std::string &table_name, const EntryVec &col_table) const noexcept
Creates a table in the database.
Transaction * xaccMallocTransaction(QofBook *book)
The xaccMallocTransaction() will malloc memory and initialize it.
bool set_table_version(const std::string &table_name, uint_t version) noexcept
Registers the version for a table.
const EntryVec & m_col_table
The front-end QofIdType.
GncSqlResultPtr execute_select_statement(const GncSqlStatementPtr &stmt) const noexcept
Executes an SQL SELECT statement and returns the result rows.
const GncGUID * qof_instance_get_guid(gconstpointer inst)
Return the GncGUID of this instance.
Split * xaccTransGetSplit(const Transaction *trans, int i)
Return a pointer to the indexed split in this transaction's split list.
time64 xaccTransGetDate(const Transaction *trans)
Retrieve the posted date of the transaction.
void create_tables(GncSqlBackend *) override
Creates the transaction and split tables.
void gnc_sql_slots_load_for_sql_subquery(GncSqlBackend *sql_be, const std::string subquery, BookLookupFn lookup_fn)
gnc_sql_slots_load_for_sql_subquery - Loads slots for all objects whose guid is supplied by a subquer...
#define G_LOG_DOMAIN
Functions providing the SX List as a plugin page.
#define PINFO(format, args...)
Print an informational note.
load and save data to SQL
const gchar * QofIdTypeConst
QofIdTypeConst declaration.
load and save accounts data to SQL
GncGUID guid_new_return(void)
Generate a new id.
void qof_backend_set_error(QofBackend *qof_be, QofBackendError err)
Set the error on the specified QofBackend.
gboolean qof_instance_get_destroying(gconstpointer ptr)
Retrieve the flag that indicates whether or not this object is about to be destroyed.
gboolean string_to_guid(const gchar *string, GncGUID *guid)
Given a string, replace the given guid with the parsed one unless the given value is null...
char xaccSplitGetReconcile(const Split *split)
Returns the value of the reconcile flag.
gboolean gnc_sql_slots_save(GncSqlBackend *sql_be, const GncGUID *guid, gboolean is_infant, QofInstance *inst)
gnc_sql_slots_save - Saves slots for an object to the db.
void add_to_query(QofIdTypeConst obj_name, void *pObject, PairVec &vec) const noexcept override
Add a pair of the table column heading and object's value's string representation to a PairVec; used ...
void gnc_lot_add_split(GNCLot *lot, Split *split)
Adds a split to this lot.
void load_all(GncSqlBackend *) override
Loads all transactions.
void xaccSplitSetReconcile(Split *split, char recn)
Set the reconcile flag.
gchar * guid_to_string_buff(const GncGUID *guid, gchar *str)
The guid_to_string_buff() routine puts a null-terminated string encoding of the id into the memory po...
void xaccTransScrubPostedDate(Transaction *trans)
Changes Transaction date_posted timestamps from 00:00 local to 11:00 UTC.
#define PERR(format, args...)
Log a serious error.
void(* QofSetterFunc)(gpointer, gpointer)
The QofSetterFunc defines an function pointer for parameter setters.
void create_tables(GncSqlBackend *) override
Conditionally create or update a database table from m_col_table.
#define PWARN(format, args...)
Log a warning.
void load(const GncSqlBackend *sql_be, GncSqlRow &row, QofIdTypeConst obj_name, void *pObject) const noexcept override
Load a value into an object from the database row.
Transaction * xaccTransLookup(const GncGUID *guid, QofBook *book)
The xaccTransLookup() subroutine will return the transaction associated with the given id...
convert single-entry accounts to clean double-entry
char * qof_print_date(time64 secs)
Convenience; calls through to qof_print_date_dmy_buff().
Split * xaccSplitLookup(const GncGUID *guid, QofBook *book)
The xaccSplitLookup() subroutine will return the split associated with the given id, or NULL if there is no such split.
Account handling public routines.
bool save_commodity(gnc_commodity *comm) noexcept
Ensure that a commodity referenced in another object is in fact saved in the database.
Row of SQL Query results.
void upgrade_table(const std::string &table_name, const EntryVec &col_table) noexcept
Upgrades a table to a new structure.
gboolean guid_equal(const GncGUID *guid_1, const GncGUID *guid_2)
Given two GUIDs, return TRUE if they are non-NULL and equal.
#define GUID_ENCODING_LENGTH
Number of characters needed to encode a guid as a string not including the null terminator.
bool commit(GncSqlBackend *sql_be, QofInstance *inst) override
Commits a split to the database.
load and save data to SQL
const char * xaccTransGetDescription(const Transaction *trans)
Gets the transaction Description.
gpointer(* QofAccessFunc)(gpointer object, const QofParam *param)
The QofAccessFunc defines an arbitrary function pointer for access functions.
Encapsulates per-class table schema with functions to load, create a table, commit a changed front-en...
bool commit(GncSqlBackend *sql_be, QofInstance *inst) override
UPDATE/INSERT a single instance of m_type_name into the database.
void xaccTransCommitEdit(Transaction *trans)
The xaccTransCommitEdit() method indicates that the changes to the transaction and its splits are com...
void xaccTransBeginEdit(Transaction *trans)
The xaccTransBeginEdit() method must be called before any changes are made to a transaction or any of...
All type declarations for the whole Gnucash engine.
Split * xaccMallocSplit(QofBook *book)
Constructor.
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...
Account * xaccSplitGetAccount(const Split *split)
Returns the account of this split, which was set through xaccAccountInsertSplit().
const GncGUID * guid_null(void)
Returns a GncGUID which is guaranteed to never reference any entity.
gnc_commodity * xaccTransGetCurrency(const Transaction *trans)
Returns the valuation commodity of this transaction.
gboolean gnc_sql_slots_delete(GncSqlBackend *sql_be, const GncGUID *guid)
gnc_sql_slots_delete - Deletes slots for an object from the db.
void add_to_table(ColVec &vec) const noexcept override
Add a GncSqlColumnInfo structure for the column type to a ColVec.
const char * xaccAccountGetName(const Account *acc)
Get the account's name.
API for Transactions and Splits (journal entries)
The type used to store guids in C.
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.
Structure to hold start/end balances for each account.
GNCLot * xaccSplitGetLot(const Split *split)
Returns the pointer to the debited/credited Lot where this split belongs to, or NULL if it doesn't be...
Main SQL backend structure.
void gnc_sql_transaction_load_tx_for_account(GncSqlBackend *sql_be, Account *account)
Loads all transactions which have splits for a specific account.
Account * xaccAccountLookup(const GncGUID *guid, QofBook *book)
The xaccAccountLookup() subroutine will return the account associated with the given id...