1 /********************************************************************
2 * gnc-slots-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-slots-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 extern "C"
30 {
31 #include <config.h>
32
33 #include <glib.h>
34
35 #include <qof.h>
36 #include <gnc-engine.h>
37
38 #ifdef S_SPLINT_S
39 #include "splint-defs.h"
40 #endif
41 }
42
43 #include <string>
44 #include <sstream>
45
46 #include "gnc-sql-connection.hpp"
47 #include "gnc-sql-backend.hpp"
48 #include "gnc-sql-object-backend.hpp"
49 #include "gnc-sql-column-table-entry.hpp"
50 #include "gnc-slots-sql.h"
51
52 #include <kvp-frame.hpp>
53
54 static QofLogModule log_module = G_LOG_DOMAIN;
55
56 #define TABLE_NAME "slots"
57 #define TABLE_VERSION 4
58
59 typedef enum
60 {
61 NONE,
62 FRAME,
63 LIST
64 } context_t;
65
66 struct slot_info_t
67 {
68 GncSqlBackend* be;
69 const GncGUID* guid;
70 gboolean is_ok;
71 KvpFrame* pKvpFrame;
72 KvpValue::Type value_type;
73 GList* pList;
74 context_t context;
75 KvpValue* pKvpValue;
76 std::string path;
77 std::string parent_path;
78 };
79
80
81 static gpointer get_obj_guid (gpointer pObject);
82 static void set_obj_guid (void);
83 static gpointer get_path (gpointer pObject);
84 static void set_path (gpointer pObject, gpointer pValue);
85 static KvpValue::Type get_slot_type (gpointer pObject);
86 static void set_slot_type (gpointer pObject, gpointer pValue);
87 static gint64 get_int64_val (gpointer pObject);
88 static void set_int64_val (gpointer pObject, gint64 pValue);
89 static gpointer get_string_val (gpointer pObject);
90 static void set_string_val (gpointer pObject, gpointer pValue);
91 static gpointer get_double_val (gpointer pObject);
92 static void set_double_val (gpointer pObject, gpointer pValue);
93 static time64 get_time_val (gpointer pObject);
94 static void set_time_val (gpointer pObject, time64 t);
95 static gpointer get_guid_val (gpointer pObject);
96 static void set_guid_val (gpointer pObject, gpointer pValue);
97 static gnc_numeric get_numeric_val (gpointer pObject);
98 static void set_numeric_val (gpointer pObject, gnc_numeric value);
99 static GDate* get_gdate_val (gpointer pObject);
100 static void set_gdate_val (gpointer pObject, GDate* value);
101 static slot_info_t* slot_info_copy (slot_info_t* pInfo, GncGUID* guid);
102 static void slots_load_info (slot_info_t* pInfo);
103
104 #define SLOT_MAX_PATHNAME_LEN 4096
105 #define SLOT_MAX_STRINGVAL_LEN 4096
106 enum
107 {
108 id_col = 0,
109 obj_guid_col,
110 name_col,
111 slot_type_col,
112 int64_val_col,
113 string_val_col,
114 double_val_col,
115 time_val_col,
116 guid_val_col,
117 numeric_val_col,
118 gdate_val_col
119 };
120
121 static const EntryVec col_table
122 {
123 /* col_name, col_type, size, flags, g0bj_param_name, qof_param_name, getter, setter */
124 gnc_sql_make_table_entry<CT_INT>(
125 "id", 0, COL_PKEY | COL_NNUL | COL_AUTOINC),
126 gnc_sql_make_table_entry<CT_GUID>("obj_guid", 0, COL_NNUL,
127 (QofAccessFunc)get_obj_guid,
128 (QofSetterFunc)set_obj_guid),
129 gnc_sql_make_table_entry<CT_STRING>("name", SLOT_MAX_PATHNAME_LEN, COL_NNUL,
130 (QofAccessFunc)get_path, set_path),
131 gnc_sql_make_table_entry<CT_INT>("slot_type", 0, COL_NNUL,
132 (QofAccessFunc)get_slot_type,
133 set_slot_type),
134 gnc_sql_make_table_entry<CT_INT64>("int64_val", 0, 0,
135 (QofAccessFunc)get_int64_val,
136 (QofSetterFunc)set_int64_val),
137 gnc_sql_make_table_entry<CT_STRING>("string_val", SLOT_MAX_PATHNAME_LEN, 0,
138 (QofAccessFunc)get_string_val,
139 set_string_val),
140 gnc_sql_make_table_entry<CT_DOUBLE>("double_val", 0, 0,
141 (QofAccessFunc)get_double_val,
142 set_double_val),
143 gnc_sql_make_table_entry<CT_TIME>("timespec_val", 0, 0,
144 (QofAccessFunc)get_time_val,
145 (QofSetterFunc)set_time_val),
146 gnc_sql_make_table_entry<CT_GUID>("guid_val", 0, 0,
147 (QofAccessFunc)get_guid_val,
148 set_guid_val),
149 gnc_sql_make_table_entry<CT_NUMERIC>("numeric_val", 0, 0,
150 (QofAccessFunc)get_numeric_val,
151 (QofSetterFunc)set_numeric_val),
152 gnc_sql_make_table_entry<CT_GDATE>("gdate_val", 0, 0,
153 (QofAccessFunc)get_gdate_val,
154 (QofSetterFunc)set_gdate_val),
155 };
156
157 static void
_retrieve_guid_(gpointer pObject,gpointer pValue)158 _retrieve_guid_ (gpointer pObject, gpointer pValue)
159 {
160 GncGUID* pGuid = (GncGUID*)pObject;
161 GncGUID* guid = (GncGUID*)pValue;
162
163 g_return_if_fail (pObject != NULL);
164 g_return_if_fail (pValue != NULL);
165
166 memcpy (pGuid, guid, sizeof (GncGUID));
167 }
168
169 /* Special column table because we need to be able to access the table by
170 a column other than the primary key */
171 static const EntryVec obj_guid_col_table
172 {
173 gnc_sql_make_table_entry<CT_GUID>("obj_guid", 0, 0,
174 (QofAccessFunc)get_obj_guid,
175 _retrieve_guid_),
176 };
177
178 static const EntryVec gdate_col_table
179 {
180 gnc_sql_make_table_entry<CT_GDATE>("gdate_val", 0, 0),
181 };
182
GncSqlSlotsBackend()183 GncSqlSlotsBackend::GncSqlSlotsBackend() :
184 GncSqlObjectBackend(TABLE_VERSION, GNC_ID_ACCOUNT,
185 TABLE_NAME, col_table) {}
186
187 /* ================================================================= */
188
189 inline static std::string::size_type
get_final_delim(std::string & path)190 get_final_delim(std::string& path)
191 {
192 auto idx = path.rfind('/');
193 while (idx == path.length())
194 {
195 path.erase(idx);
196 idx = path.rfind('/');
197 }
198 return idx;
199 }
200
201 static std::string
get_key(slot_info_t * pInfo)202 get_key (slot_info_t* pInfo)
203 {
204 if (!pInfo) return "";
205
206 auto path = pInfo->path;
207 path.erase (0, pInfo->parent_path.size());
208 return path;
209 }
210
211 static void
set_slot_from_value(slot_info_t * pInfo,KvpValue * pValue)212 set_slot_from_value (slot_info_t* pInfo, KvpValue* pValue)
213 {
214 g_return_if_fail (pInfo != NULL);
215 g_return_if_fail (pValue != NULL);
216
217 switch (pInfo->context)
218 {
219 case FRAME:
220 {
221 auto key = get_key (pInfo);
222 pInfo->pKvpFrame->set ({key}, pValue);
223 break;
224 }
225 case LIST:
226 {
227 pInfo->pList = g_list_append (pInfo->pList, pValue);
228 break;
229 }
230 case NONE:
231 default:
232 {
233 auto key = get_key (pInfo);
234 auto path = pInfo->parent_path;
235 auto frame = pInfo->pKvpFrame;
236 if (!path.empty())
237 {
238 frame->set_path ({path, key}, pValue);
239 }
240 else
241 frame->set ({key}, pValue);
242 break;
243 }
244 }
245 }
246
247 static gpointer
get_obj_guid(gpointer pObject)248 get_obj_guid (gpointer pObject)
249 {
250 slot_info_t* pInfo = (slot_info_t*)pObject;
251
252 g_return_val_if_fail (pObject != NULL, NULL);
253
254 return (gpointer)pInfo->guid;
255 }
256
257 static void
set_obj_guid(void)258 set_obj_guid (void)
259 {
260 // Nowhere to put the GncGUID
261 }
262
263 static gpointer
get_path(gpointer pObject)264 get_path (gpointer pObject)
265 {
266 slot_info_t* pInfo = (slot_info_t*)pObject;
267
268 g_return_val_if_fail (pObject != NULL, NULL);
269
270 return (gpointer)pInfo->path.c_str();
271 }
272
273 static void
set_path(gpointer pObject,gpointer pValue)274 set_path (gpointer pObject, gpointer pValue)
275 {
276 slot_info_t* pInfo = (slot_info_t*)pObject;
277 pInfo->path = static_cast<char*>(pValue);
278 if (pInfo->path.find (pInfo->parent_path) != 0)
279 pInfo->parent_path.clear();
280 }
281
282 static KvpValue::Type
get_slot_type(gpointer pObject)283 get_slot_type (gpointer pObject)
284 {
285 slot_info_t* pInfo = (slot_info_t*)pObject;
286
287 g_return_val_if_fail (pObject != NULL, KvpValue::Type::INVALID);
288
289 // return (gpointer)kvp_value_get_type( pInfo->pKvpValue );
290 return pInfo->value_type;
291 }
292
293 static void
set_slot_type(gpointer pObject,gpointer pValue)294 set_slot_type (gpointer pObject, gpointer pValue)
295 {
296 slot_info_t* pInfo = (slot_info_t*)pObject;
297
298 g_return_if_fail (pObject != NULL);
299 g_return_if_fail (pValue != NULL);
300
301 pInfo->value_type = static_cast<KvpValue::Type> (GPOINTER_TO_INT (pValue));
302 }
303
304 static gint64
get_int64_val(gpointer pObject)305 get_int64_val (gpointer pObject)
306 {
307 slot_info_t* pInfo = (slot_info_t*)pObject;
308
309 g_return_val_if_fail (pObject != NULL, 0);
310
311 if (pInfo->pKvpValue->get_type () == KvpValue::Type::INT64)
312 {
313 return pInfo->pKvpValue->get<int64_t> ();
314 }
315 else
316 {
317 return 0;
318 }
319 }
320
321 static void
set_int64_val(gpointer pObject,gint64 value)322 set_int64_val (gpointer pObject, gint64 value)
323 {
324 slot_info_t* pInfo = (slot_info_t*)pObject;
325 KvpValue* pValue = NULL;
326
327 g_return_if_fail (pObject != NULL);
328
329 if (pInfo->value_type != KvpValue::Type::INT64) return;
330 pValue = new KvpValue {value};
331 set_slot_from_value (pInfo, pValue);
332 }
333
334 static gpointer
get_string_val(gpointer pObject)335 get_string_val (gpointer pObject)
336 {
337 slot_info_t* pInfo = (slot_info_t*)pObject;
338
339 g_return_val_if_fail (pObject != NULL, NULL);
340
341 if (pInfo->pKvpValue->get_type () == KvpValue::Type::STRING)
342 {
343 return (gpointer)pInfo->pKvpValue->get<const char*> ();
344 }
345 else
346 {
347 return NULL;
348 }
349 }
350
351 static void
set_string_val(gpointer pObject,gpointer pValue)352 set_string_val (gpointer pObject, gpointer pValue)
353 {
354 slot_info_t* pInfo = (slot_info_t*)pObject;
355 g_return_if_fail (pObject != NULL);
356
357 if (pInfo->value_type != KvpValue::Type::STRING || pValue == NULL)
358 return;
359 auto value = new KvpValue {g_strdup(static_cast<const char*> (pValue))};
360 set_slot_from_value (pInfo, value);
361 }
362
363 static gpointer
get_double_val(gpointer pObject)364 get_double_val (gpointer pObject)
365 {
366 static double d_val; /* static so that we can return it. */
367 g_return_val_if_fail (pObject != NULL, NULL);
368 auto pInfo = static_cast<slot_info_t*>(pObject);
369 if (pInfo->pKvpValue->get_type () == KvpValue::Type::DOUBLE)
370 {
371 d_val = pInfo->pKvpValue->get<double> ();
372 return (gpointer)&d_val;
373 }
374 else
375 {
376 return NULL;
377 }
378 }
379
380 static void
set_double_val(gpointer pObject,gpointer pValue)381 set_double_val (gpointer pObject, gpointer pValue)
382 {
383 slot_info_t* pInfo = (slot_info_t*)pObject;
384 KvpValue* value = NULL;
385
386 g_return_if_fail (pObject != NULL);
387
388 if (pInfo->value_type != KvpValue::Type::DOUBLE || pValue == NULL) return;
389 value = new KvpValue {* (static_cast<double*> (pValue))};
390 set_slot_from_value (pInfo, value);
391 }
392
393 static time64
get_time_val(gpointer pObject)394 get_time_val (gpointer pObject)
395 {
396 slot_info_t* pInfo = (slot_info_t*)pObject;
397
398 g_return_val_if_fail (pObject != NULL, 0);
399
400 //if( kvp_value_get_type( pInfo->pKvpValue ) == KvpValue::Type::TIME64 ) {
401 auto t = pInfo->pKvpValue->get<Time64> ();
402 return t.t;
403 }
404
405 static void
set_time_val(gpointer pObject,time64 time)406 set_time_val (gpointer pObject, time64 time)
407 {
408 slot_info_t* pInfo = (slot_info_t*)pObject;
409 KvpValue* value = NULL;
410 Time64 t{time};
411 g_return_if_fail (pObject != NULL);
412
413 if (pInfo->value_type != KvpValue::Type::TIME64) return;
414 value = new KvpValue {t};
415 set_slot_from_value (pInfo, value);
416 }
417
418 static gpointer
get_guid_val(gpointer pObject)419 get_guid_val (gpointer pObject)
420 {
421 slot_info_t* pInfo = (slot_info_t*)pObject;
422
423 g_return_val_if_fail (pObject != NULL, NULL);
424
425 if (pInfo->pKvpValue->get_type () == KvpValue::Type::GUID)
426 {
427 return (gpointer)pInfo->pKvpValue->get<GncGUID*> ();
428 }
429 else
430 {
431 return NULL;
432 }
433 }
434
435 static void
set_guid_val(gpointer pObject,gpointer pValue)436 set_guid_val (gpointer pObject, gpointer pValue)
437 {
438 slot_info_t* pInfo = (slot_info_t*)pObject;
439
440 g_return_if_fail (pObject != NULL);
441 if (pValue == NULL) return;
442
443 switch (pInfo->value_type)
444 {
445 case KvpValue::Type::GUID:
446 {
447 auto new_guid = guid_copy (static_cast<GncGUID*> (pValue));
448 set_slot_from_value (pInfo, new KvpValue {new_guid});
449 break;
450 }
451 case KvpValue::Type::GLIST:
452 {
453 slot_info_t* newInfo = slot_info_copy (pInfo, (GncGUID*)pValue);
454 KvpValue* pValue = NULL;
455 auto key = get_key (pInfo);
456
457 newInfo->context = LIST;
458
459 slots_load_info (newInfo);
460 pValue = new KvpValue {newInfo->pList};
461 pInfo->pKvpFrame->set ({key.c_str()}, pValue);
462 delete newInfo;
463 break;
464 }
465 case KvpValue::Type::FRAME:
466 {
467 slot_info_t* newInfo = slot_info_copy (pInfo, (GncGUID*)pValue) ;
468 auto newFrame = new KvpFrame;
469 newInfo->pKvpFrame = newFrame;
470
471 switch (pInfo->context)
472 {
473 case LIST:
474 {
475 auto value = new KvpValue {newFrame};
476 newInfo->path = get_key (pInfo);
477 pInfo->pList = g_list_append (pInfo->pList, value);
478 break;
479 }
480 case FRAME:
481 default:
482 {
483 auto key = get_key (pInfo);
484 pInfo->pKvpFrame->set ({key.c_str()}, new KvpValue {newFrame});
485 break;
486 }
487 }
488
489 newInfo->context = FRAME;
490 slots_load_info (newInfo);
491 delete newInfo;
492 break;
493 }
494 default:
495 break;
496 }
497 }
498
499 static gnc_numeric
get_numeric_val(gpointer pObject)500 get_numeric_val (gpointer pObject)
501 {
502 slot_info_t* pInfo = (slot_info_t*)pObject;
503
504 g_return_val_if_fail (pObject != NULL, gnc_numeric_zero ());
505
506 if (pInfo->pKvpValue->get_type () == KvpValue::Type::NUMERIC)
507 {
508 return pInfo->pKvpValue->get<gnc_numeric> ();
509 }
510 else
511 {
512 return gnc_numeric_zero ();
513 }
514 }
515
516 static void
set_numeric_val(gpointer pObject,gnc_numeric value)517 set_numeric_val (gpointer pObject, gnc_numeric value)
518 {
519 slot_info_t* pInfo = (slot_info_t*)pObject;
520 KvpValue* pValue = NULL;
521
522 g_return_if_fail (pObject != NULL);
523
524 if (pInfo->value_type != KvpValue::Type::NUMERIC) return;
525 set_slot_from_value (pInfo, new KvpValue {value});
526 }
527
528 static GDate*
get_gdate_val(gpointer pObject)529 get_gdate_val (gpointer pObject)
530 {
531 slot_info_t* pInfo = (slot_info_t*)pObject;
532 static GDate date;
533
534 g_return_val_if_fail (pObject != NULL, NULL);
535
536 if (pInfo->pKvpValue->get_type () == KvpValue::Type::GDATE)
537 {
538 date = pInfo->pKvpValue->get<GDate> ();
539 return &date;
540 }
541 else
542 {
543 return NULL;
544 }
545 }
546
547 static void
set_gdate_val(gpointer pObject,GDate * value)548 set_gdate_val (gpointer pObject, GDate* value)
549 {
550 slot_info_t* pInfo = (slot_info_t*)pObject;
551 KvpValue* pValue = NULL;
552
553 g_return_if_fail (pObject != NULL);
554
555 if (pInfo->value_type != KvpValue::Type::GDATE) return;
556 set_slot_from_value (pInfo, new KvpValue {*value});
557 }
558
559 static slot_info_t*
slot_info_copy(slot_info_t * pInfo,GncGUID * guid)560 slot_info_copy (slot_info_t* pInfo, GncGUID* guid)
561 {
562 g_return_val_if_fail (pInfo != NULL, NULL);
563 auto newSlot = new slot_info_t;
564
565 newSlot->be = pInfo->be;
566 newSlot->guid = guid == NULL ? pInfo->guid : guid;
567 newSlot->is_ok = pInfo->is_ok;
568 newSlot->pKvpFrame = pInfo->pKvpFrame;
569 newSlot->value_type = pInfo->value_type;
570 newSlot->pList = pInfo->pList;
571 newSlot->context = pInfo->context;
572 newSlot->pKvpValue = pInfo->pKvpValue;
573 if (!pInfo->path.empty())
574 newSlot->parent_path = pInfo->path + "/";
575 else
576 newSlot->parent_path = pInfo->parent_path;
577 return newSlot;
578 }
579
580 static void
save_slot(const char * key,KvpValue * value,slot_info_t & slot_info)581 save_slot (const char* key, KvpValue* value, slot_info_t & slot_info)
582 {
583 g_return_if_fail (value != NULL);
584
585 // Ignore if we've already run into a failure
586 if (!slot_info.is_ok)
587 {
588 return;
589 }
590 slot_info.pKvpValue = value;
591 slot_info.path = slot_info.parent_path + key;
592 slot_info.value_type = value->get_type ();
593
594 switch (slot_info.value_type)
595 {
596 case KvpValue::Type::FRAME:
597 {
598 auto pKvpFrame = value->get<KvpFrame*> ();
599 auto guid = guid_new ();
600 slot_info_t* pNewInfo = slot_info_copy (&slot_info, guid);
601 KvpValue* oldValue = slot_info.pKvpValue;
602 slot_info.pKvpValue = new KvpValue {guid};
603 slot_info.is_ok = slot_info.be->do_db_operation(OP_DB_INSERT,
604 TABLE_NAME,
605 TABLE_NAME,
606 &slot_info,
607 col_table);
608 g_return_if_fail (slot_info.is_ok);
609 pKvpFrame->for_each_slot_temp (save_slot, *pNewInfo);
610 delete slot_info.pKvpValue;
611 slot_info.pKvpValue = oldValue;
612 delete pNewInfo;
613 }
614 break;
615 case KvpValue::Type::GLIST:
616 {
617 GncGUID* guid = guid_new ();
618 slot_info_t* pNewInfo = slot_info_copy (&slot_info, guid);
619 KvpValue* oldValue = slot_info.pKvpValue;
620 slot_info.pKvpValue = new KvpValue {guid}; // Transfer ownership!
621 slot_info.is_ok = slot_info.be->do_db_operation(OP_DB_INSERT,
622 TABLE_NAME,
623 TABLE_NAME,
624 &slot_info,
625 col_table);
626 g_return_if_fail (slot_info.is_ok);
627 for (auto cursor = value->get<GList*> (); cursor; cursor = cursor->next)
628 {
629 auto val = static_cast<KvpValue*> (cursor->data);
630 save_slot ("", val, *pNewInfo);
631 }
632 delete slot_info.pKvpValue;
633 slot_info.pKvpValue = oldValue;
634 delete pNewInfo;
635 }
636 break;
637 default:
638 {
639 slot_info.is_ok = slot_info.be->do_db_operation (OP_DB_INSERT,
640 TABLE_NAME,
641 TABLE_NAME,
642 &slot_info,
643 col_table);
644 }
645 break;
646 }
647 }
648
649 gboolean
gnc_sql_slots_save(GncSqlBackend * sql_be,const GncGUID * guid,gboolean is_infant,QofInstance * inst)650 gnc_sql_slots_save (GncSqlBackend* sql_be, const GncGUID* guid, gboolean is_infant,
651 QofInstance* inst)
652 {
653 slot_info_t slot_info = { NULL, NULL, TRUE, NULL, KvpValue::Type::INVALID,
654 NULL, FRAME, NULL, "" };
655 KvpFrame* pFrame = qof_instance_get_slots (inst);
656
657 g_return_val_if_fail (sql_be != NULL, FALSE);
658 g_return_val_if_fail (guid != NULL, FALSE);
659 g_return_val_if_fail (pFrame != NULL, FALSE);
660
661 // If this is not saving into a new db, clear out the old saved slots first
662 if (!sql_be->pristine() && !is_infant)
663 {
664 (void)gnc_sql_slots_delete (sql_be, guid);
665 }
666
667 slot_info.be = sql_be;
668 slot_info.guid = guid;
669 pFrame->for_each_slot_temp (save_slot, slot_info);
670
671 return slot_info.is_ok;
672 }
673
674 gboolean
gnc_sql_slots_delete(GncSqlBackend * sql_be,const GncGUID * guid)675 gnc_sql_slots_delete (GncSqlBackend* sql_be, const GncGUID* guid)
676 {
677 gchar* buf;
678 gchar guid_buf[GUID_ENCODING_LENGTH + 1];
679 slot_info_t slot_info = { NULL, NULL, TRUE, NULL, KvpValue::Type::INVALID,
680 NULL, FRAME, NULL, "" };
681
682 g_return_val_if_fail (sql_be != NULL, FALSE);
683 g_return_val_if_fail (guid != NULL, FALSE);
684
685 (void)guid_to_string_buff (guid, guid_buf);
686
687 buf = g_strdup_printf ("SELECT * FROM %s WHERE obj_guid='%s' and slot_type in ('%d', '%d') and not guid_val is null",
688 TABLE_NAME, guid_buf, KvpValue::Type::FRAME, KvpValue::Type::GLIST);
689 auto stmt = sql_be->create_statement_from_sql(buf);
690 g_free (buf);
691 if (stmt != nullptr)
692 {
693 auto result = sql_be->execute_select_statement(stmt);
694 for (auto row : *result)
695 {
696 try
697 {
698 const GncSqlColumnTableEntryPtr table_row =
699 col_table[guid_val_col];
700 GncGUID child_guid;
701 auto val = row.get_string_at_col (table_row->name());
702 if (string_to_guid (val.c_str(), &child_guid))
703 gnc_sql_slots_delete (sql_be, &child_guid);
704 }
705 catch (std::invalid_argument&)
706 {
707 continue;
708 }
709 }
710 }
711
712 slot_info.be = sql_be;
713 slot_info.guid = guid;
714 slot_info.is_ok = TRUE;
715 slot_info.is_ok = sql_be->do_db_operation(OP_DB_DELETE, TABLE_NAME,
716 TABLE_NAME, &slot_info,
717 obj_guid_col_table);
718
719 return slot_info.is_ok;
720 }
721
722 static void
load_slot(slot_info_t * pInfo,GncSqlRow & row)723 load_slot (slot_info_t* pInfo, GncSqlRow& row)
724 {
725 slot_info_t* slot_info;
726
727 g_return_if_fail (pInfo != NULL);
728 g_return_if_fail (pInfo->be != NULL);
729 g_return_if_fail (pInfo->pKvpFrame != NULL);
730
731 slot_info = slot_info_copy (pInfo, NULL);
732
733 gnc_sql_load_object (pInfo->be, row, TABLE_NAME, slot_info, col_table);
734
735 if (slot_info->pList != pInfo->pList)
736 {
737 if (pInfo->pList != NULL)
738 {
739 PWARN ("Load slot returned a different list than the original");
740 }
741 else
742 {
743 pInfo->pList = slot_info->pList;
744 }
745 }
746 delete slot_info;
747 }
748
749 void
gnc_sql_slots_load(GncSqlBackend * sql_be,QofInstance * inst)750 gnc_sql_slots_load (GncSqlBackend* sql_be, QofInstance* inst)
751 {
752 slot_info_t info = { NULL, NULL, TRUE, NULL, KvpValue::Type::INVALID,
753 NULL, FRAME, NULL, "" };
754 g_return_if_fail (sql_be != NULL);
755 g_return_if_fail (inst != NULL);
756
757 info.be = sql_be;
758 info.guid = qof_instance_get_guid (inst);
759 info.pKvpFrame = qof_instance_get_slots (inst);
760 info.context = NONE;
761
762 slots_load_info (&info);
763 }
764
765 static void
slots_load_info(slot_info_t * pInfo)766 slots_load_info (slot_info_t* pInfo)
767 {
768 g_return_if_fail (pInfo != NULL);
769 g_return_if_fail (pInfo->be != NULL);
770 g_return_if_fail (pInfo->guid != NULL);
771 g_return_if_fail (pInfo->pKvpFrame != NULL);
772
773 gnc::GUID guid(*pInfo->guid);
774 std::string sql("SELECT * FROM " TABLE_NAME " WHERE obj_guid='");
775 sql += guid.to_string() + "'";
776 auto stmt = pInfo->be->create_statement_from_sql(sql);
777 if (stmt != nullptr)
778 {
779 auto result = pInfo->be->execute_select_statement (stmt);
780 for (auto row : *result)
781 load_slot (pInfo, row);
782 delete result;
783 }
784 }
785
786 static const GncGUID*
load_obj_guid(const GncSqlBackend * sql_be,GncSqlRow & row)787 load_obj_guid (const GncSqlBackend* sql_be, GncSqlRow& row)
788 {
789 static GncGUID guid;
790
791 g_return_val_if_fail (sql_be != NULL, NULL);
792
793 gnc_sql_load_object (sql_be, row, NULL, &guid, obj_guid_col_table);
794
795 return &guid;
796 }
797
798 static void
load_slot_for_list_item(GncSqlBackend * sql_be,GncSqlRow & row,QofCollection * coll)799 load_slot_for_list_item (GncSqlBackend* sql_be, GncSqlRow& row,
800 QofCollection* coll)
801 {
802 slot_info_t slot_info = { NULL, NULL, TRUE, NULL, KvpValue::Type::INVALID,
803 NULL, FRAME, NULL, "" };
804 const GncGUID* guid;
805 QofInstance* inst;
806
807 g_return_if_fail (sql_be != NULL);
808 g_return_if_fail (coll != NULL);
809
810 guid = load_obj_guid (sql_be, row);
811 g_assert (guid != NULL);
812 inst = qof_collection_lookup_entity (coll, guid);
813
814 slot_info.be = sql_be;
815 slot_info.pKvpFrame = qof_instance_get_slots (inst);
816 slot_info.context = NONE;
817
818 gnc_sql_load_object (sql_be, row, TABLE_NAME, &slot_info, col_table);
819
820
821 }
822
823 static void
load_slot_for_book_object(GncSqlBackend * sql_be,GncSqlRow & row,BookLookupFn lookup_fn)824 load_slot_for_book_object (GncSqlBackend* sql_be, GncSqlRow& row,
825 BookLookupFn lookup_fn)
826 {
827 slot_info_t slot_info = { NULL, NULL, TRUE, NULL, KvpValue::Type::INVALID,
828 NULL, FRAME, NULL, "" };
829 const GncGUID* guid;
830 QofInstance* inst;
831
832 g_return_if_fail (sql_be != NULL);
833 g_return_if_fail (lookup_fn != NULL);
834
835 guid = load_obj_guid (sql_be, row);
836 g_return_if_fail (guid != NULL);
837 inst = lookup_fn (guid, sql_be->book());
838 if (inst == NULL) return; /* Silently bail if the guid isn't loaded yet. */
839
840 slot_info.be = sql_be;
841 slot_info.pKvpFrame = qof_instance_get_slots (inst);
842 slot_info.path.clear();
843
844 gnc_sql_load_object (sql_be, row, TABLE_NAME, &slot_info, col_table);
845 }
846
847 /**
848 * gnc_sql_slots_load_for_sql_subquery - Loads slots for all objects whose guid is
849 * supplied by a subquery. The subquery should be of the form "SELECT DISTINCT guid FROM ...".
850 * This is faster than loading for one object at a time because fewer SQL queries * are used.
851 *
852 * @param sql_be SQL backend
853 * @param subquery Subquery SQL string
854 * @param lookup_fn Lookup function
855 */
gnc_sql_slots_load_for_sql_subquery(GncSqlBackend * sql_be,const std::string subquery,BookLookupFn lookup_fn)856 void gnc_sql_slots_load_for_sql_subquery (GncSqlBackend* sql_be,
857 const std::string subquery,
858 BookLookupFn lookup_fn)
859 {
860 g_return_if_fail (sql_be != NULL);
861
862 // Ignore empty subquery
863 if (subquery.empty()) return;
864
865 std::string pkey(obj_guid_col_table[0]->name());
866 std::string sql("SELECT * FROM " TABLE_NAME " WHERE ");
867 sql += pkey + " IN (" + subquery + ")";
868
869 // Execute the query and load the slots
870 auto stmt = sql_be->create_statement_from_sql(sql);
871 if (stmt == nullptr)
872 {
873 PERR ("stmt == NULL, SQL = '%s'\n", sql.c_str());
874 return;
875 }
876 auto result = sql_be->execute_select_statement(stmt);
877 for (auto row : *result)
878 load_slot_for_book_object (sql_be, row, lookup_fn);
879 delete result;
880 }
881
882 /* ================================================================= */
883 void
create_tables(GncSqlBackend * sql_be)884 GncSqlSlotsBackend::create_tables (GncSqlBackend* sql_be)
885 {
886 gint version;
887 gboolean ok;
888
889 g_return_if_fail (sql_be != NULL);
890
891 version = sql_be->get_table_version( TABLE_NAME);
892 if (version == 0)
893 {
894 (void)sql_be->create_table(TABLE_NAME, TABLE_VERSION, col_table);
895
896 ok = sql_be->create_index ("slots_guid_index", TABLE_NAME,
897 obj_guid_col_table);
898 if (!ok)
899 {
900 PERR ("Unable to create index\n");
901 }
902 }
903 else if (version < m_version)
904 {
905 /* Upgrade:
906 1->2: 64-bit int values to proper definition, add index
907 2->3: Add gdate field
908 3->4: Use DATETIME instead of TIMESTAMP in MySQL
909 */
910 if (version == 1)
911 {
912 sql_be->upgrade_table(TABLE_NAME, col_table);
913 ok = sql_be->create_index ("slots_guid_index", TABLE_NAME,
914 obj_guid_col_table);
915 if (!ok)
916 {
917 PERR ("Unable to create index\n");
918 }
919 }
920 else if (version == 2)
921 {
922 ok = sql_be->add_columns_to_table(TABLE_NAME, gdate_col_table);
923 if (!ok)
924 {
925 PERR ("Unable to add gdate column\n");
926 }
927 }
928 else
929 {
930 sql_be->upgrade_table(TABLE_NAME, col_table);
931 }
932 sql_be->set_table_version (TABLE_NAME, TABLE_VERSION);
933 PINFO ("Slots table upgraded from version %d to version %d\n", version,
934 TABLE_VERSION);
935 }
936 }
937
938 /* ========================== END OF FILE ===================== */
939