Line data Source code
1 : /********************************************************************
2 : * gnc-dbisqlconnection.cpp: Encapsulate libdbi dbi_conn *
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 :
24 : #include <guid.hpp>
25 : #include <config.h>
26 : #include <platform.h>
27 : #include <gnc-locale-utils.h>
28 :
29 : #include <string>
30 : #include <regex>
31 : #include <sstream>
32 :
33 : #include "gnc-dbisqlconnection.hpp"
34 :
35 : static QofLogModule log_module = G_LOG_DOMAIN;
36 : // gnc-dbiproviderimpl.hpp has templates that need log_module defined.
37 : #include "gnc-dbiproviderimpl.hpp"
38 :
39 : static const unsigned int DBI_MAX_CONN_ATTEMPTS = 5;
40 : const std::string lock_table = "gnclock";
41 :
42 : /* --------------------------------------------------------- */
43 : class GncDbiSqlStatement : public GncSqlStatement
44 : {
45 : public:
46 0 : GncDbiSqlStatement(const std::string& sql) :
47 0 : m_sql {sql} {}
48 0 : ~GncDbiSqlStatement() {}
49 : const char* to_sql() const override;
50 : void add_where_cond(QofIdTypeConst, const PairVec&) override;
51 :
52 : private:
53 : std::string m_sql;
54 : };
55 :
56 :
57 : const char*
58 0 : GncDbiSqlStatement::to_sql() const
59 : {
60 0 : return m_sql.c_str();
61 : }
62 :
63 : void
64 0 : GncDbiSqlStatement::add_where_cond(QofIdTypeConst type_name,
65 : const PairVec& col_values)
66 : {
67 0 : m_sql += " WHERE ";
68 0 : for (auto colpair : col_values)
69 : {
70 0 : if (colpair != *col_values.begin())
71 0 : m_sql += " AND ";
72 0 : if (colpair.second == "NULL")
73 0 : m_sql += colpair.first + " IS " + colpair.second;
74 : else
75 0 : m_sql += colpair.first + " = " + colpair.second;
76 0 : }
77 0 : }
78 :
79 0 : GncDbiSqlConnection::GncDbiSqlConnection (DbType type, QofBackend* qbe,
80 0 : dbi_conn conn, SessionOpenMode mode) :
81 0 : m_qbe{qbe}, m_conn{conn},
82 0 : m_provider{type == DbType::DBI_SQLITE ?
83 : make_dbi_provider<DbType::DBI_SQLITE>() :
84 0 : type == DbType::DBI_MYSQL ?
85 : make_dbi_provider<DbType::DBI_MYSQL>() :
86 : make_dbi_provider<DbType::DBI_PGSQL>()},
87 0 : m_conn_ok{true}, m_last_error{ERR_BACKEND_NO_ERR}, m_error_repeat{0},
88 0 : m_retry{false}, m_sql_savepoint{0}, m_readonly{false}
89 : {
90 0 : if (mode == SESSION_READ_ONLY)
91 0 : m_readonly = true;
92 0 : else if (!lock_database(mode == SESSION_BREAK_LOCK))
93 0 : throw std::runtime_error("Failed to lock database!");
94 0 : if (!check_and_rollback_failed_save())
95 : {
96 0 : unlock_database();
97 0 : throw std::runtime_error("A failed safe-save was detected and rolling it back failed.");
98 : }
99 0 : }
100 :
101 : bool
102 0 : GncDbiSqlConnection::lock_database (bool break_lock)
103 : {
104 : const char *errstr;
105 : /* Protect everything with a single transaction to prevent races */
106 0 : if (!begin_transaction())
107 0 : return false;
108 0 : auto tables = m_provider->get_table_list(m_conn, lock_table);
109 0 : if (tables.empty())
110 : {
111 0 : auto result = dbi_conn_queryf (m_conn,
112 : "CREATE TABLE %s ( Hostname varchar(%d), PID int )",
113 : lock_table.c_str(),
114 : GNC_HOST_NAME_MAX);
115 0 : if (result)
116 : {
117 0 : dbi_result_free (result);
118 0 : result = nullptr;
119 : }
120 0 : if (dbi_conn_error (m_conn, &errstr))
121 : {
122 0 : PERR ("Error %s creating lock table", errstr);
123 0 : qof_backend_set_error (m_qbe, ERR_BACKEND_SERVER_ERR);
124 0 : return false;
125 : }
126 : }
127 :
128 : /* Check for an existing entry; delete it if break_lock is true, otherwise fail */
129 : char hostname[ GNC_HOST_NAME_MAX + 1 ];
130 0 : auto result = dbi_conn_queryf (m_conn, "SELECT * FROM %s",
131 : lock_table.c_str());
132 0 : if (result && dbi_result_get_numrows (result))
133 : {
134 0 : dbi_result_free (result);
135 0 : result = nullptr;
136 0 : if (!break_lock)
137 : {
138 0 : qof_backend_set_error (m_qbe, ERR_BACKEND_LOCKED);
139 : /* FIXME: After enhancing the qof_backend_error mechanism, report in the dialog what is the hostname of the machine holding the lock. */
140 0 : rollback_transaction();
141 0 : return false;
142 : }
143 0 : result = dbi_conn_queryf (m_conn, "DELETE FROM %s", lock_table.c_str());
144 0 : if (!result)
145 : {
146 0 : qof_backend_set_error (m_qbe, ERR_BACKEND_SERVER_ERR);
147 0 : m_qbe->set_message("Failed to delete lock record");
148 0 : rollback_transaction();
149 0 : return false;
150 : }
151 0 : dbi_result_free (result);
152 0 : result = nullptr;
153 : }
154 : /* Add an entry and commit the transaction */
155 0 : memset (hostname, 0, sizeof (hostname));
156 0 : gethostname (hostname, GNC_HOST_NAME_MAX);
157 0 : result = dbi_conn_queryf (m_conn,
158 : "INSERT INTO %s VALUES ('%s', '%d')",
159 0 : lock_table.c_str(), hostname, (int)GETPID ());
160 0 : if (!result)
161 : {
162 0 : qof_backend_set_error (m_qbe, ERR_BACKEND_SERVER_ERR);
163 0 : m_qbe->set_message("Failed to create lock record");
164 0 : rollback_transaction();
165 0 : return false;
166 : }
167 0 : dbi_result_free (result);
168 0 : return commit_transaction();
169 0 : }
170 :
171 : void
172 0 : GncDbiSqlConnection::unlock_database ()
173 : {
174 0 : if (m_conn == nullptr) return;
175 0 : if (m_readonly) return;
176 0 : auto dbi_error{dbi_conn_error (m_conn, nullptr)};
177 0 : g_return_if_fail (dbi_error == DBI_ERROR_NONE || dbi_error == DBI_ERROR_BADIDX);
178 :
179 0 : auto tables = m_provider->get_table_list (m_conn, lock_table);
180 0 : if (tables.empty())
181 : {
182 0 : PWARN ("No lock table in database, so not unlocking it.");
183 0 : return;
184 : }
185 0 : if (begin_transaction())
186 : {
187 : /* Delete the entry if it's our hostname and PID */
188 : char hostname[ GNC_HOST_NAME_MAX + 1 ];
189 :
190 0 : memset (hostname, 0, sizeof (hostname));
191 0 : gethostname (hostname, GNC_HOST_NAME_MAX);
192 0 : auto result = dbi_conn_queryf (m_conn,
193 : "SELECT * FROM %s WHERE Hostname = '%s' "
194 : "AND PID = '%d'", lock_table.c_str(),
195 : hostname,
196 0 : (int)GETPID ());
197 0 : if (result && dbi_result_get_numrows (result))
198 : {
199 0 : if (result)
200 : {
201 0 : dbi_result_free (result);
202 0 : result = nullptr;
203 : }
204 0 : result = dbi_conn_queryf (m_conn, "DELETE FROM %s",
205 : lock_table.c_str());
206 0 : if (!result)
207 : {
208 0 : PERR ("Failed to delete the lock entry");
209 0 : m_qbe->set_error (ERR_BACKEND_SERVER_ERR);
210 0 : rollback_transaction();
211 0 : return;
212 : }
213 : else
214 : {
215 0 : dbi_result_free (result);
216 0 : result = nullptr;
217 : }
218 0 : commit_transaction();
219 0 : return;
220 : }
221 0 : rollback_transaction();
222 0 : PWARN ("There was no lock entry in the Lock table");
223 0 : return;
224 : }
225 0 : PWARN ("Unable to get a lock on LOCK, so failed to clear the lock entry.");
226 0 : m_qbe->set_error (ERR_BACKEND_SERVER_ERR);
227 0 : }
228 :
229 : bool
230 0 : GncDbiSqlConnection::check_and_rollback_failed_save()
231 : {
232 0 : auto backup_tables = m_provider->get_table_list(m_conn, "%back");
233 0 : if (backup_tables.empty())
234 0 : return true;
235 0 : auto merge_tables = m_provider->get_table_list(m_conn, "%_merge");
236 0 : if (!merge_tables.empty())
237 : {
238 0 : PERR("Merge tables exist in the database indicating a previous"
239 : "attempt to recover from a failed safe-save. Automatic"
240 : "recovery is beyond GnuCash's ability, you must recover"
241 : "by hand or restore from a good backup.");
242 0 : return false;
243 : }
244 0 : return table_operation(recover);
245 0 : }
246 :
247 0 : GncDbiSqlConnection::~GncDbiSqlConnection()
248 : {
249 0 : if (m_conn)
250 : {
251 0 : unlock_database();
252 0 : dbi_conn_close(m_conn);
253 0 : m_conn = nullptr;
254 : }
255 0 : }
256 :
257 : GncSqlResultPtr
258 0 : GncDbiSqlConnection::execute_select_statement (const GncSqlStatementPtr& stmt)
259 : noexcept
260 : {
261 : dbi_result result;
262 :
263 0 : DEBUG ("SQL: %s\n", stmt->to_sql());
264 0 : auto locale = gnc_push_locale (LC_NUMERIC, "C");
265 : do
266 : {
267 0 : init_error ();
268 0 : result = dbi_conn_query (m_conn, stmt->to_sql());
269 : }
270 0 : while (m_retry);
271 0 : if (result == nullptr)
272 : {
273 0 : PERR ("Error executing SQL %s\n", stmt->to_sql());
274 0 : if(m_last_error)
275 0 : m_qbe->set_error(m_last_error);
276 : else
277 0 : m_qbe->set_error(ERR_BACKEND_SERVER_ERR);
278 : }
279 0 : gnc_pop_locale (LC_NUMERIC, locale);
280 0 : return GncSqlResultPtr(new GncDbiSqlResult (this, result));
281 0 : }
282 :
283 : int
284 0 : GncDbiSqlConnection::execute_nonselect_statement (const GncSqlStatementPtr& stmt)
285 : noexcept
286 : {
287 : dbi_result result;
288 :
289 0 : DEBUG ("SQL: %s\n", stmt->to_sql());
290 : do
291 : {
292 0 : init_error ();
293 0 : result = dbi_conn_query (m_conn, stmt->to_sql());
294 : }
295 0 : while (m_retry);
296 0 : if (result == nullptr && m_last_error)
297 : {
298 0 : PERR ("Error executing SQL %s\n", stmt->to_sql());
299 0 : if(m_last_error)
300 0 : m_qbe->set_error(m_last_error);
301 : else
302 0 : m_qbe->set_error(ERR_BACKEND_SERVER_ERR);
303 0 : return -1;
304 : }
305 0 : if (!result)
306 0 : return 0;
307 0 : auto num_rows = (gint)dbi_result_get_numrows_affected (result);
308 0 : auto status = dbi_result_free (result);
309 0 : if (status < 0)
310 : {
311 0 : PERR ("Error in dbi_result_free() result\n");
312 0 : if(m_last_error)
313 0 : m_qbe->set_error(m_last_error);
314 : else
315 0 : m_qbe->set_error(ERR_BACKEND_SERVER_ERR);
316 : }
317 0 : return num_rows;
318 : }
319 :
320 : GncSqlStatementPtr
321 0 : GncDbiSqlConnection::create_statement_from_sql (const std::string& sql)
322 : const noexcept
323 : {
324 0 : return std::unique_ptr<GncSqlStatement>{new GncDbiSqlStatement (sql)};
325 : }
326 :
327 : bool
328 0 : GncDbiSqlConnection::does_table_exist (const std::string& table_name)
329 : const noexcept
330 : {
331 0 : return ! m_provider->get_table_list(m_conn, table_name).empty();
332 : }
333 :
334 : bool
335 0 : GncDbiSqlConnection::begin_transaction () noexcept
336 : {
337 : dbi_result result;
338 :
339 0 : DEBUG ("BEGIN\n");
340 :
341 0 : if (!verify ())
342 : {
343 0 : PERR ("gnc_dbi_verify_conn() failed\n");
344 0 : qof_backend_set_error (m_qbe, ERR_BACKEND_SERVER_ERR);
345 0 : return false;
346 : }
347 :
348 : do
349 : {
350 0 : init_error ();
351 0 : if (m_sql_savepoint == 0)
352 0 : result = dbi_conn_queryf (m_conn, "BEGIN");
353 : else
354 : {
355 0 : std::ostringstream savepoint;
356 0 : savepoint << "savepoint_" << m_sql_savepoint;
357 0 : result = dbi_conn_queryf(m_conn, "SAVEPOINT %s",
358 0 : savepoint.str().c_str());
359 0 : }
360 : }
361 0 : while (m_retry);
362 :
363 0 : if (!result)
364 : {
365 0 : PERR ("BEGIN transaction failed()\n");
366 0 : qof_backend_set_error (m_qbe, ERR_BACKEND_SERVER_ERR);
367 0 : return false;
368 : }
369 0 : if (dbi_result_free (result) < 0)
370 : {
371 0 : PERR ("Error in dbi_result_free() result\n");
372 0 : qof_backend_set_error (m_qbe, ERR_BACKEND_SERVER_ERR);
373 0 : return false;
374 : }
375 0 : ++m_sql_savepoint;
376 0 : return true;
377 : }
378 :
379 : bool
380 0 : GncDbiSqlConnection::rollback_transaction () noexcept
381 : {
382 0 : DEBUG ("ROLLBACK\n");
383 0 : if (m_sql_savepoint == 0) return false;
384 : dbi_result result;
385 0 : if (m_sql_savepoint == 1)
386 0 : result = dbi_conn_query (m_conn, "ROLLBACK");
387 : else
388 : {
389 0 : std::ostringstream savepoint;
390 0 : savepoint << "savepoint_" << m_sql_savepoint - 1;
391 0 : result = dbi_conn_queryf(m_conn, "ROLLBACK TO SAVEPOINT %s",
392 0 : savepoint.str().c_str());
393 0 : }
394 0 : if (!result)
395 : {
396 0 : PERR ("Error in conn_rollback_transaction()\n");
397 0 : qof_backend_set_error (m_qbe, ERR_BACKEND_SERVER_ERR);
398 0 : return false;
399 : }
400 :
401 0 : if (dbi_result_free (result) < 0)
402 : {
403 0 : PERR ("Error in dbi_result_free() result\n");
404 0 : qof_backend_set_error (m_qbe, ERR_BACKEND_SERVER_ERR);
405 0 : return false;
406 : }
407 :
408 0 : --m_sql_savepoint;
409 0 : return true;
410 : }
411 :
412 : bool
413 0 : GncDbiSqlConnection::commit_transaction () noexcept
414 : {
415 0 : DEBUG ("COMMIT\n");
416 0 : if (m_sql_savepoint == 0) return false;
417 : dbi_result result;
418 0 : if (m_sql_savepoint == 1)
419 0 : result = dbi_conn_queryf (m_conn, "COMMIT");
420 : else
421 : {
422 0 : std::ostringstream savepoint;
423 0 : savepoint << "savepoint_" << m_sql_savepoint - 1;
424 0 : result = dbi_conn_queryf(m_conn, "RELEASE SAVEPOINT %s",
425 0 : savepoint.str().c_str());
426 0 : }
427 :
428 0 : if (!result)
429 : {
430 0 : PERR ("Error in conn_commit_transaction()\n");
431 0 : qof_backend_set_error (m_qbe, ERR_BACKEND_SERVER_ERR);
432 0 : return false;
433 : }
434 :
435 0 : if (dbi_result_free (result) < 0)
436 : {
437 0 : PERR ("Error in dbi_result_free() result\n");
438 0 : qof_backend_set_error (m_qbe, ERR_BACKEND_SERVER_ERR);
439 0 : return false;
440 : }
441 0 : --m_sql_savepoint;
442 0 : return true;
443 : }
444 :
445 :
446 : bool
447 0 : GncDbiSqlConnection::create_table (const std::string& table_name,
448 : const ColVec& info_vec) const noexcept
449 : {
450 0 : std::string ddl;
451 0 : unsigned int col_num = 0;
452 :
453 0 : ddl += "CREATE TABLE " + table_name + "(";
454 0 : for (auto const& info : info_vec)
455 : {
456 0 : if (col_num++ != 0)
457 : {
458 0 : ddl += ", ";
459 : }
460 0 : m_provider->append_col_def (ddl, info);
461 : }
462 0 : ddl += ")";
463 :
464 0 : if (ddl.empty())
465 0 : return false;
466 :
467 0 : DEBUG ("SQL: %s\n", ddl.c_str());
468 0 : auto result = dbi_conn_query (m_conn, ddl.c_str());
469 0 : auto status = dbi_result_free (result);
470 0 : if (status < 0)
471 : {
472 0 : PERR ("Error in dbi_result_free() result\n");
473 0 : qof_backend_set_error (m_qbe, ERR_BACKEND_SERVER_ERR);
474 : }
475 :
476 0 : return true;
477 0 : }
478 :
479 : static std::string
480 0 : create_index_ddl (const GncSqlConnection* conn, const std::string& index_name,
481 : const std::string& table_name, const EntryVec& col_table)
482 : {
483 0 : std::string ddl;
484 0 : ddl += "CREATE INDEX " + index_name + " ON " + table_name + "(";
485 0 : for (const auto& table_row : col_table)
486 : {
487 0 : if (table_row != *col_table.begin())
488 : {
489 0 : ddl =+ ", ";
490 : }
491 0 : ddl += table_row->name();
492 : }
493 0 : ddl += ")";
494 0 : return ddl;
495 0 : }
496 :
497 : bool
498 0 : GncDbiSqlConnection::create_index(const std::string& index_name,
499 : const std::string& table_name,
500 : const EntryVec& col_table) const noexcept
501 : {
502 0 : auto ddl = create_index_ddl (this, index_name, table_name, col_table);
503 0 : if (ddl.empty())
504 0 : return false;
505 0 : DEBUG ("SQL: %s\n", ddl.c_str());
506 0 : auto result = dbi_conn_query (m_conn, ddl.c_str());
507 0 : auto status = dbi_result_free (result);
508 0 : if (status < 0)
509 : {
510 0 : PERR ("Error in dbi_result_free() result\n");
511 0 : qof_backend_set_error (m_qbe, ERR_BACKEND_SERVER_ERR);
512 : }
513 :
514 0 : return true;
515 0 : }
516 :
517 : bool
518 0 : GncDbiSqlConnection::add_columns_to_table(const std::string& table_name,
519 : const ColVec& info_vec)
520 : const noexcept
521 : {
522 0 : auto ddl = add_columns_ddl(table_name, info_vec);
523 0 : if (ddl.empty())
524 0 : return false;
525 :
526 0 : DEBUG ("SQL: %s\n", ddl.c_str());
527 0 : auto result = dbi_conn_query (m_conn, ddl.c_str());
528 0 : auto status = dbi_result_free (result);
529 0 : if (status < 0)
530 : {
531 0 : PERR( "Error in dbi_result_free() result\n" );
532 0 : qof_backend_set_error(m_qbe, ERR_BACKEND_SERVER_ERR );
533 : }
534 :
535 0 : return true;
536 0 : }
537 :
538 : std::string
539 0 : GncDbiSqlConnection::quote_string (const std::string& unquoted_str)
540 : const noexcept
541 : {
542 : char* quoted_str;
543 :
544 0 : dbi_conn_quote_string_copy (m_conn, unquoted_str.c_str(),
545 : "ed_str);
546 0 : if (quoted_str == nullptr)
547 0 : return std::string{""};
548 0 : std::string retval{quoted_str};
549 0 : free(quoted_str);
550 0 : return retval;
551 0 : }
552 :
553 :
554 : /** Check if the dbi connection is valid. If not attempt to re-establish it
555 : * Returns TRUE if there is a valid connection in the end or FALSE otherwise
556 : */
557 : bool
558 0 : GncDbiSqlConnection::verify () noexcept
559 : {
560 0 : if (m_conn_ok)
561 0 : return true;
562 :
563 : /* We attempt to connect only once here. The error function will
564 : * automatically re-attempt up until DBI_MAX_CONN_ATTEMPTS time to connect
565 : * if this call fails. After all these attempts, conn_ok will indicate if
566 : * there is a valid connection or not.
567 : */
568 0 : init_error ();
569 0 : m_conn_ok = true;
570 0 : (void)dbi_conn_connect (m_conn);
571 :
572 0 : return m_conn_ok;
573 : }
574 :
575 : bool
576 0 : GncDbiSqlConnection::retry_connection(const char* msg)
577 : noexcept
578 : {
579 0 : while (m_retry && m_error_repeat <= DBI_MAX_CONN_ATTEMPTS)
580 : {
581 0 : m_conn_ok = false;
582 0 : if (dbi_conn_connect(m_conn) == 0)
583 : {
584 0 : init_error();
585 0 : m_conn_ok = true;
586 0 : return true;
587 : }
588 : #ifdef G_OS_WIN32
589 : const guint backoff_msecs = 1;
590 : Sleep (backoff_msecs * 2 << ++m_error_repeat);
591 : #else
592 0 : const guint backoff_usecs = 1000;
593 0 : usleep (backoff_usecs * 2 << ++m_error_repeat);
594 : #endif
595 0 : PINFO ("DBI error: %s - Reconnecting...\n", msg);
596 :
597 : }
598 0 : PERR ("DBI error: %s - Giving up after %d consecutive attempts.\n", msg,
599 : DBI_MAX_CONN_ATTEMPTS);
600 0 : m_conn_ok = false;
601 0 : return false;
602 : }
603 :
604 : bool
605 0 : GncDbiSqlConnection::rename_table(const std::string& old_name,
606 : const std::string& new_name)
607 : {
608 0 : std::string sql = "ALTER TABLE " + old_name + " RENAME TO " + new_name;
609 0 : auto stmt = create_statement_from_sql(sql);
610 0 : return execute_nonselect_statement(stmt) >= 0;
611 0 : }
612 :
613 : bool
614 0 : GncDbiSqlConnection::drop_table(const std::string& table)
615 : {
616 0 : std::string sql = "DROP TABLE " + table;
617 0 : auto stmt = create_statement_from_sql(sql);
618 0 : return execute_nonselect_statement(stmt) >= 0;
619 0 : }
620 :
621 : bool
622 0 : GncDbiSqlConnection::merge_tables(const std::string& table,
623 : const std::string& other)
624 : {
625 0 : auto merge_table = table + "_merge";
626 0 : std::string sql = "CREATE TABLE " + merge_table + " AS SELECT * FROM " +
627 0 : table + " UNION SELECT * FROM " + other;
628 0 : auto stmt = create_statement_from_sql(sql);
629 0 : if (execute_nonselect_statement(stmt) < 0)
630 0 : return false;
631 0 : sql = std::string("DELETE FROM ") + table;
632 0 : stmt = create_statement_from_sql(sql);
633 0 : if (execute_nonselect_statement(stmt) < 0)
634 0 : return false;
635 0 : sql = std::string("INSERT INTO ") + table +
636 0 : " SELECT * FROM " + merge_table;
637 0 : stmt = create_statement_from_sql(sql);
638 0 : if (execute_nonselect_statement(stmt) < 0)
639 0 : return false;
640 0 : if (!drop_table(merge_table))
641 0 : return false;
642 0 : return drop_table(other);
643 0 : }
644 :
645 : /**
646 : * Perform a specified SQL operation on every table in a
647 : * database. Possible operations are:
648 : * * drop: to DROP all tables from the database
649 : * * empty: to DELETE all records from each table in the database.
650 : * * backup: Rename every table from "name" to "name_back"
651 : * * drop_backup: DROP the backup tables.
652 : * * rollback: DROP the new table "name" and rename "name_back" to
653 : * "name", restoring the database to its previous state.
654 : *
655 : * The intent of the last two is to be able to move an existing table
656 : * aside, query its contents with a transformation (in 2.4.x this is
657 : * already done as the contents are loaded completely when a Qof
658 : * session is started), save them to a new table according to a new
659 : * database format, and finally drop the backup table; if there's an
660 : * error during the process, rollback allows returning the table to
661 : * its original state.
662 : *
663 : * @param sql_conn: The sql connection (via dbi) to which the
664 : * transactions will be sent
665 : * @param table_namess: StrVec of tables to operate on.
666 : * @param op: The operation to perform.
667 : * @return Success (TRUE) or failure.
668 : */
669 :
670 : bool
671 0 : GncDbiSqlConnection::table_operation(TableOpType op) noexcept
672 : {
673 0 : auto backup_tables = m_provider->get_table_list(m_conn, "%_back");
674 0 : auto all_tables = m_provider->get_table_list(m_conn, "");
675 : /* No operations on the lock table */
676 0 : auto new_end = std::remove(all_tables.begin(), all_tables.end(), lock_table);
677 0 : all_tables.erase(new_end, all_tables.end());
678 0 : StrVec data_tables;
679 0 : data_tables.reserve(all_tables.size() - backup_tables.size());
680 0 : std::set_difference(all_tables.begin(), all_tables.end(),
681 : backup_tables.begin(), backup_tables.end(),
682 : std::back_inserter(data_tables));
683 0 : switch(op)
684 : {
685 0 : case backup:
686 0 : if (!backup_tables.empty())
687 : {
688 0 : PERR("Unable to backup database, an existing backup is present.");
689 0 : qof_backend_set_error(m_qbe, ERR_BACKEND_DATA_CORRUPT);
690 0 : return false;
691 : }
692 0 : for (auto table : data_tables)
693 0 : if (!rename_table(table, table +"_back"))
694 0 : return false; /* Error, trigger rollback. */
695 0 : break;
696 0 : case drop_backup:
697 0 : for (auto table : backup_tables)
698 : {
699 0 : auto data_table = table.substr(0, table.find("_back"));
700 0 : if (std::find(data_tables.begin(), data_tables.end(),
701 0 : data_table) != data_tables.end())
702 0 : drop_table(table); /* Other table exists, OK. */
703 : else /* No data table, restore the backup */
704 0 : rename_table(table, data_table);
705 0 : }
706 0 : break;
707 0 : case rollback:
708 0 : for (auto table : backup_tables)
709 : {
710 0 : auto data_table = table.substr(0, table.find("_back"));
711 0 : if (std::find(data_tables.begin(), data_tables.end(),
712 0 : data_table) != data_tables.end())
713 0 : drop_table(data_table); /* Other table exists, OK. */
714 0 : rename_table(table, data_table);
715 0 : }
716 0 : break;
717 0 : case recover:
718 0 : for (auto table : backup_tables)
719 : {
720 0 : auto data_table = table.substr(0, table.find("_back"));
721 0 : if (std::find(data_tables.begin(), data_tables.end(),
722 0 : data_table) != data_tables.end())
723 : {
724 0 : if (!merge_tables(data_table, table))
725 0 : return false;
726 : }
727 : else
728 : {
729 0 : if (!rename_table(table, data_table))
730 0 : return false;
731 : }
732 0 : }
733 0 : break;
734 : }
735 0 : return true;
736 0 : }
737 :
738 : bool
739 0 : GncDbiSqlConnection::drop_indexes() noexcept
740 : {
741 0 : auto index_list = m_provider->get_index_list (m_conn);
742 0 : for (auto index : index_list)
743 : {
744 : const char* errmsg;
745 0 : m_provider->drop_index (m_conn, index);
746 0 : if (DBI_ERROR_NONE != dbi_conn_error (m_conn, &errmsg))
747 : {
748 0 : PERR("Failed to drop indexes %s", errmsg);
749 0 : return false;
750 : }
751 0 : }
752 0 : return true;
753 0 : }
754 :
755 : std::string
756 0 : GncDbiSqlConnection::add_columns_ddl(const std::string& table_name,
757 : const ColVec& info_vec) const noexcept
758 : {
759 0 : std::string ddl;
760 :
761 0 : ddl += "ALTER TABLE " + table_name;
762 0 : for (auto const& info : info_vec)
763 : {
764 0 : if (info != *info_vec.begin())
765 : {
766 0 : ddl += ", ";
767 : }
768 0 : ddl += "ADD COLUMN ";
769 0 : m_provider->append_col_def (ddl, info);
770 : }
771 0 : return ddl;
772 : }
|
