Line data Source code
1 : /********************************************************************
2 : * gnc-budget-sql.c: load and save data to SQL *
3 : * *
4 : * This program is free software; you can redistribute it and/or *
5 : * modify it under the terms of the GNU General Public License as *
6 : * published by the Free Software Foundation; either version 2 of *
7 : * the License, or (at your option) any later version. *
8 : * *
9 : * This program is distributed in the hope that it will be useful, *
10 : * but WITHOUT ANY WARRANTY; without even the implied warranty of *
11 : * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *
12 : * GNU General Public License for more details. *
13 : * *
14 : * You should have received a copy of the GNU General Public License*
15 : * along with this program; if not, contact: *
16 : * *
17 : * Free Software Foundation Voice: +1-617-542-5942 *
18 : * 51 Franklin Street, Fifth Floor Fax: +1-617-542-2652 *
19 : * Boston, MA 02110-1301, USA gnu@gnu.org *
20 : \********************************************************************/
21 : /** @file gnc-budget-sql.c
22 : * @brief load and save data to SQL
23 : * @author Copyright (c) 2006-2008 Phil Longstaff <plongstaff@rogers.com>
24 : *
25 : * This file implements the top-level QofBackend API for saving/
26 : * restoring data to/from an SQL db
27 : */
28 : #include <guid.hpp>
29 : #include <config.h>
30 :
31 : #include <glib.h>
32 :
33 : #include "qof.h"
34 : #include "Recurrence.h"
35 : #include "gnc-budget.h"
36 :
37 : #if defined( S_SPLINT_S )
38 : #include "splint-defs.h"
39 : #endif
40 :
41 : #include "gnc-sql-connection.hpp"
42 : #include "gnc-sql-backend.hpp"
43 : #include "gnc-sql-object-backend.hpp"
44 : #include "gnc-sql-column-table-entry.hpp"
45 : #include "gnc-budget-sql.h"
46 : #include "gnc-slots-sql.h"
47 : #include "gnc-recurrence-sql.h"
48 :
49 : #define BUDGET_TABLE "budgets"
50 : #define TABLE_VERSION 1
51 : #define AMOUNTS_TABLE "budget_amounts"
52 : #define AMOUNTS_TABLE_VERSION 1
53 :
54 : [[maybe_unused]] static QofLogModule log_module = G_LOG_DOMAIN;
55 :
56 : #define BUDGET_MAX_NAME_LEN 2048
57 : #define BUDGET_MAX_DESCRIPTION_LEN 2048
58 :
59 : static const EntryVec col_table
60 : {
61 : gnc_sql_make_table_entry<CT_GUID>(
62 : "guid", 0, COL_NNUL | COL_PKEY, "guid"),
63 : gnc_sql_make_table_entry<CT_STRING>(
64 : "name", BUDGET_MAX_NAME_LEN, COL_NNUL, "name"),
65 : gnc_sql_make_table_entry<CT_STRING>(
66 : "description", BUDGET_MAX_DESCRIPTION_LEN, 0, "description"),
67 : gnc_sql_make_table_entry<CT_INT>(
68 : "num_periods", 0, COL_NNUL, "num_periods"),
69 : };
70 :
71 : static QofInstance* get_budget (gpointer pObj);
72 : static void set_budget (gpointer pObj, gpointer val);
73 : static QofInstance* get_account (gpointer pObj);
74 : static void set_account (gpointer pObj, gpointer val);
75 : static gint get_period_num (gpointer pObj);
76 : static void set_period_num (gpointer pObj, gpointer val);
77 : static gnc_numeric get_amount (gpointer pObj);
78 : static void set_amount (gpointer pObj, gnc_numeric value);
79 :
80 15 : GncSqlBudgetBackend::GncSqlBudgetBackend() :
81 30 : GncSqlObjectBackend(TABLE_VERSION, GNC_ID_BUDGET,
82 75 : BUDGET_TABLE, col_table) {}
83 :
84 : typedef struct
85 : {
86 : GncBudget* budget;
87 : Account* account;
88 : guint period_num;
89 : } budget_amount_info_t;
90 :
91 : static const EntryVec budget_amounts_col_table
92 : {
93 : gnc_sql_make_table_entry<CT_INT>(
94 : "id", 0, COL_NNUL | COL_PKEY | COL_AUTOINC),
95 : gnc_sql_make_table_entry<CT_BUDGETREF>("budget_guid", 0, COL_NNUL,
96 : (QofAccessFunc)get_budget,
97 : (QofSetterFunc)set_budget),
98 : gnc_sql_make_table_entry<CT_ACCOUNTREF>("account_guid", 0, COL_NNUL,
99 : (QofAccessFunc)get_account,
100 : (QofSetterFunc)set_account),
101 : gnc_sql_make_table_entry<CT_INT>("period_num", 0, COL_NNUL,
102 : (QofAccessFunc)get_period_num,
103 : (QofSetterFunc)set_period_num),
104 : gnc_sql_make_table_entry<CT_NUMERIC>("amount", 0, COL_NNUL,
105 : (QofAccessFunc)get_amount,
106 : (QofSetterFunc)set_amount),
107 : };
108 :
109 : /* ================================================================= */
110 : static QofInstance*
111 0 : get_budget (gpointer pObj)
112 : {
113 0 : budget_amount_info_t* info = (budget_amount_info_t*)pObj;
114 :
115 0 : g_return_val_if_fail (pObj != NULL, NULL);
116 :
117 0 : return QOF_INSTANCE (info->budget);
118 : }
119 :
120 : static void
121 0 : set_budget (gpointer pObj, gpointer val)
122 : {
123 0 : }
124 :
125 : static QofInstance*
126 0 : get_account (gpointer pObj)
127 : {
128 0 : budget_amount_info_t* info = (budget_amount_info_t*)pObj;
129 :
130 0 : g_return_val_if_fail (pObj != NULL, NULL);
131 :
132 0 : return QOF_INSTANCE (info->account);
133 : }
134 :
135 : static void
136 0 : set_account (gpointer pObj, gpointer val)
137 : {
138 0 : budget_amount_info_t* info = (budget_amount_info_t*)pObj;
139 :
140 0 : g_return_if_fail (pObj != NULL);
141 0 : g_return_if_fail (val != NULL);
142 0 : g_return_if_fail (GNC_IS_ACCOUNT (val));
143 :
144 0 : info->account = GNC_ACCOUNT (val);
145 : }
146 :
147 : static gint
148 0 : get_period_num (gpointer pObj)
149 : {
150 0 : budget_amount_info_t* info = (budget_amount_info_t*)pObj;
151 :
152 0 : g_return_val_if_fail (pObj != NULL, 0);
153 :
154 0 : return info->period_num;
155 : }
156 :
157 : static void
158 0 : set_period_num (gpointer pObj, gpointer val)
159 : {
160 0 : budget_amount_info_t* info = (budget_amount_info_t*)pObj;
161 :
162 0 : g_return_if_fail (pObj != NULL);
163 :
164 0 : info->period_num = GPOINTER_TO_UINT (val);
165 : }
166 :
167 : static gnc_numeric
168 0 : get_amount (gpointer pObj)
169 : {
170 0 : budget_amount_info_t* info = (budget_amount_info_t*)pObj;
171 :
172 0 : g_return_val_if_fail (pObj != NULL, gnc_numeric_zero ());
173 :
174 0 : return gnc_budget_get_account_period_value (info->budget, info->account,
175 0 : info->period_num);
176 : }
177 :
178 : static void
179 0 : set_amount (gpointer pObj, gnc_numeric value)
180 : {
181 0 : budget_amount_info_t* info = (budget_amount_info_t*)pObj;
182 :
183 0 : g_return_if_fail (pObj != NULL);
184 :
185 0 : gnc_budget_set_account_period_value (info->budget, info->account,
186 : info->period_num, value);
187 : }
188 :
189 : /*----------------------------------------------------------------*/
190 : /**
191 : * Loads the budget amounts for a budget.
192 : *
193 : * @param sql_be SQL backend
194 : * @param budget Budget
195 : */
196 : static void
197 1 : load_budget_amounts (GncSqlBackend* sql_be, GncBudget* budget)
198 : {
199 : gchar guid_buf[GUID_ENCODING_LENGTH + 1];
200 :
201 1 : g_return_if_fail (sql_be != NULL);
202 1 : g_return_if_fail (budget != NULL);
203 :
204 1 : (void)guid_to_string_buff (qof_instance_get_guid (QOF_INSTANCE (budget)),
205 : guid_buf);
206 1 : auto sql = g_strdup_printf ("SELECT * FROM %s WHERE budget_guid='%s'",
207 : AMOUNTS_TABLE, guid_buf);
208 2 : auto stmt = sql_be->create_statement_from_sql(sql);
209 1 : g_free (sql);
210 1 : if (stmt != nullptr)
211 : {
212 1 : auto result = sql_be->execute_select_statement(stmt);
213 1 : budget_amount_info_t info = { budget, NULL, 0 };
214 :
215 1 : for (auto row : *result)
216 1 : gnc_sql_load_object (sql_be, row, NULL, &info, budget_amounts_col_table);
217 : }
218 1 : }
219 :
220 : /**
221 : * Deletes the budget amounts for a budget.
222 : *
223 : * @param sql_be SQL backend
224 : * @param budget Budget
225 : */
226 : static gboolean
227 1 : delete_budget_amounts (GncSqlBackend* sql_be, GncBudget* budget)
228 : {
229 : gchar guid_buf[GUID_ENCODING_LENGTH + 1];
230 :
231 1 : g_return_val_if_fail (sql_be != NULL, FALSE);
232 1 : g_return_val_if_fail (budget != NULL, FALSE);
233 :
234 1 : (void)guid_to_string_buff (qof_instance_get_guid (QOF_INSTANCE (budget)),
235 : guid_buf);
236 1 : std::stringstream sql;
237 : sql << "DELETE FROM " << AMOUNTS_TABLE << " WHERE budget_guid='"<<
238 1 : guid_buf << "'";
239 1 : auto stmt = sql_be->create_statement_from_sql(sql.str());
240 1 : sql_be->execute_nonselect_statement(stmt);
241 :
242 1 : return true;
243 1 : }
244 :
245 : /**
246 : * Saves the budget amounts for a budget.
247 : *
248 : * @param sql_be SQL backend
249 : * @param budget Budget
250 : */
251 : static gboolean
252 1 : save_budget_amounts (GncSqlBackend* sql_be, GncBudget* budget)
253 : {
254 : GList* descendants;
255 : GList* node;
256 : budget_amount_info_t info;
257 : guint num_periods;
258 1 : gboolean is_ok = TRUE;
259 :
260 1 : g_return_val_if_fail (sql_be != NULL, FALSE);
261 1 : g_return_val_if_fail (budget != NULL, FALSE);
262 :
263 : // Delete the amounts, then save
264 1 : delete_budget_amounts (sql_be, budget);
265 :
266 1 : info.budget = budget;
267 1 : num_periods = gnc_budget_get_num_periods (budget);
268 1 : descendants = gnc_account_get_descendants (gnc_book_get_root_account (
269 : sql_be->book()));
270 27 : for (node = descendants; node != NULL && is_ok; node = g_list_next (node))
271 : {
272 : guint i;
273 :
274 13 : info.account = GNC_ACCOUNT (node->data);
275 169 : for (i = 0; i < num_periods && is_ok; i++)
276 : {
277 156 : if (gnc_budget_is_account_period_value_set (budget, info.account, i))
278 : {
279 0 : info.period_num = i;
280 0 : is_ok = sql_be->do_db_operation(OP_DB_INSERT, AMOUNTS_TABLE,
281 : "", &info,
282 : budget_amounts_col_table);
283 : }
284 : }
285 : }
286 1 : g_list_free (descendants);
287 :
288 1 : return is_ok;
289 : }
290 : /*----------------------------------------------------------------*/
291 : static GncBudget*
292 1 : load_single_budget (GncSqlBackend* sql_be, GncSqlRow& row)
293 : {
294 : const GncGUID* guid;
295 1 : GncBudget* pBudget = NULL;
296 : Recurrence* r;
297 :
298 1 : g_return_val_if_fail (sql_be != NULL, NULL);
299 :
300 1 : guid = gnc_sql_load_guid (sql_be, row);
301 1 : if (guid != NULL)
302 : {
303 1 : pBudget = gnc_budget_lookup (guid, sql_be->book());
304 : }
305 1 : if (pBudget == NULL)
306 : {
307 1 : pBudget = gnc_budget_new (sql_be->book());
308 : }
309 :
310 1 : gnc_budget_begin_edit (pBudget);
311 1 : gnc_sql_load_object (sql_be, row, GNC_ID_BUDGET, pBudget, col_table);
312 1 : load_budget_amounts (sql_be, pBudget);
313 1 : r = gnc_sql_recurrence_load (sql_be, gnc_budget_get_guid (pBudget));
314 1 : if (r != NULL)
315 : {
316 1 : gnc_budget_set_recurrence (pBudget, r);
317 1 : g_free (r);
318 : }
319 1 : gnc_budget_commit_edit (pBudget);
320 :
321 1 : return pBudget;
322 : }
323 :
324 : void
325 7 : GncSqlBudgetBackend::load_all (GncSqlBackend* sql_be)
326 : {
327 7 : g_return_if_fail (sql_be != NULL);
328 :
329 7 : std::string sql("SELECT * FROM " BUDGET_TABLE);
330 7 : auto stmt = sql_be->create_statement_from_sql(sql);
331 7 : auto result = sql_be->execute_select_statement(stmt);
332 8 : for (auto row : *result)
333 8 : load_single_budget (sql_be, row);
334 :
335 7 : std::string pkey(col_table[0]->name());
336 7 : sql = "SELECT DISTINCT ";
337 7 : sql += pkey + " FROM " BUDGET_TABLE;
338 7 : gnc_sql_slots_load_for_sql_subquery (sql_be, sql,
339 : (BookLookupFn)gnc_budget_lookup);
340 7 : }
341 :
342 : /* ================================================================= */
343 : void
344 13 : GncSqlBudgetBackend::create_tables (GncSqlBackend* sql_be)
345 : {
346 : gint version;
347 :
348 13 : g_return_if_fail (sql_be != NULL);
349 :
350 26 : version = sql_be->get_table_version( BUDGET_TABLE);
351 13 : if (version == 0)
352 : {
353 18 : (void)sql_be->create_table(BUDGET_TABLE, TABLE_VERSION, col_table);
354 : }
355 :
356 26 : version = sql_be->get_table_version( AMOUNTS_TABLE);
357 13 : if (version == 0)
358 : {
359 18 : (void)sql_be->create_table(AMOUNTS_TABLE, AMOUNTS_TABLE_VERSION,
360 : budget_amounts_col_table);
361 : }
362 : }
363 :
364 : /* ================================================================= */
365 : bool
366 1 : GncSqlBudgetBackend::commit (GncSqlBackend* sql_be, QofInstance* inst)
367 : {
368 1 : GncBudget* pBudget = GNC_BUDGET (inst);
369 : const GncGUID* guid;
370 : E_DB_OPERATION op;
371 : gboolean is_infant;
372 : gboolean is_ok;
373 :
374 1 : g_return_val_if_fail (sql_be != NULL, FALSE);
375 1 : g_return_val_if_fail (inst != NULL, FALSE);
376 1 : g_return_val_if_fail (GNC_IS_BUDGET (inst), FALSE);
377 :
378 1 : is_infant = qof_instance_get_infant (inst);
379 1 : if (qof_instance_get_destroying (inst))
380 : {
381 0 : op = OP_DB_DELETE;
382 : }
383 1 : else if (sql_be->pristine() || is_infant)
384 : {
385 1 : op = OP_DB_INSERT;
386 : }
387 : else
388 : {
389 0 : op = OP_DB_UPDATE;
390 : }
391 1 : is_ok = sql_be->do_db_operation(op, BUDGET_TABLE, GNC_ID_BUDGET, pBudget,
392 : col_table);
393 :
394 : // Now, commit any slots and recurrence
395 1 : if (is_ok)
396 : {
397 1 : guid = qof_instance_get_guid (inst);
398 1 : if (!qof_instance_get_destroying (inst))
399 : {
400 1 : is_ok = save_budget_amounts (sql_be, pBudget);
401 1 : if (is_ok)
402 : {
403 1 : is_ok = gnc_sql_recurrence_save (sql_be, guid,
404 : gnc_budget_get_recurrence (pBudget));
405 : }
406 1 : if (is_ok)
407 : {
408 1 : is_ok = gnc_sql_slots_save (sql_be, guid, is_infant, inst);
409 : }
410 : }
411 : else
412 : {
413 0 : is_ok = delete_budget_amounts (sql_be, pBudget);
414 0 : if (is_ok)
415 : {
416 0 : is_ok = gnc_sql_recurrence_delete (sql_be, guid);
417 : }
418 0 : if (is_ok)
419 : {
420 0 : (void)gnc_sql_slots_delete (sql_be, guid);
421 : }
422 : }
423 : }
424 :
425 1 : return is_ok;
426 : }
427 :
428 : static void
429 1 : do_save (QofInstance* inst, gpointer data)
430 : {
431 1 : write_objects_t* s = (write_objects_t*)data;
432 :
433 1 : if (s->is_ok)
434 : {
435 1 : s->is_ok = s->obe->commit (s->be, inst);
436 : }
437 1 : }
438 :
439 : bool
440 6 : GncSqlBudgetBackend::write (GncSqlBackend* sql_be)
441 : {
442 6 : write_objects_t data;
443 :
444 6 : g_return_val_if_fail (sql_be != NULL, FALSE);
445 :
446 6 : data.be = sql_be;
447 6 : data.is_ok = TRUE;
448 6 : data.obe = this;
449 6 : qof_collection_foreach (qof_book_get_collection (sql_be->book(), GNC_ID_BUDGET),
450 : (QofInstanceForeachCB)do_save, &data);
451 :
452 6 : return data.is_ok;
453 : }
454 :
455 : /* ================================================================= */
456 : template<> void
457 0 : GncSqlColumnTableEntryImpl<CT_BUDGETREF>::load (const GncSqlBackend* sql_be,
458 : GncSqlRow& row,
459 : QofIdTypeConst obj_name,
460 : gpointer pObject) const noexcept
461 : {
462 0 : load_from_guid_ref(row, obj_name, pObject,
463 0 : [sql_be](GncGUID* g){
464 0 : return gnc_budget_lookup (g, sql_be->book());
465 : });
466 0 : }
467 :
468 : template<> void
469 6 : GncSqlColumnTableEntryImpl<CT_BUDGETREF>::add_to_table(ColVec& vec) const noexcept
470 : {
471 6 : add_objectref_guid_to_table(vec);
472 6 : }
473 :
474 : template<> void
475 0 : GncSqlColumnTableEntryImpl<CT_BUDGETREF>::add_to_query(QofIdTypeConst obj_name,
476 : const gpointer pObject,
477 : PairVec& vec) const noexcept
478 : {
479 0 : add_objectref_guid_to_query(obj_name, pObject, vec);
480 0 : }
481 :
482 : /* ========================== END OF FILE ===================== */
|
