1 /***********************************************************************\ 2 * gnc-sql-backend.hpp: Qof Backend for SQL Databases * 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 #ifndef __GNC_SQL_BACKEND_HPP__ 25 #define __GNC_SQL_BACKEND_HPP__ 26 27 extern "C" 28 { 29 #include <qof.h> 30 #include <Account.h> 31 } 32 #include <memory> 33 #include <exception> 34 #include <sstream> 35 #include <vector> 36 #include <qof-backend.hpp> 37 38 class GncSqlColumnTableEntry; 39 using GncSqlColumnTableEntryPtr = std::shared_ptr<GncSqlColumnTableEntry>; 40 using EntryVec = std::vector<GncSqlColumnTableEntryPtr>; 41 class GncSqlObjectBackend; 42 using GncSqlObjectBackendPtr = std::shared_ptr<GncSqlObjectBackend>; 43 using OBEEntry = std::tuple<std::string, GncSqlObjectBackendPtr>; 44 using OBEVec = std::vector<OBEEntry>; 45 class GncSqlConnection; 46 class GncSqlStatement; 47 using GncSqlStatementPtr = std::unique_ptr<GncSqlStatement>; 48 class GncSqlResult; 49 using GncSqlResultPtr = GncSqlResult*; 50 using VersionPair = std::pair<const std::string, unsigned int>; 51 using VersionVec = std::vector<VersionPair>; 52 using uint_t = unsigned int; 53 54 typedef enum 55 { 56 OP_DB_INSERT, 57 OP_DB_UPDATE, 58 OP_DB_DELETE 59 } E_DB_OPERATION; 60 61 /** 62 * 63 * Main SQL backend structure. 64 */ 65 class GncSqlBackend : public QofBackend 66 { 67 public: 68 GncSqlBackend(GncSqlConnection *conn, QofBook* book); 69 virtual ~GncSqlBackend(); 70 /** 71 * Load the contents of an SQL database into a book. 72 * 73 * @param book Book to be loaded 74 */ 75 void load(QofBook*, QofBackendLoadType) override; 76 /** 77 * Save the contents of a book to an SQL database. 78 * 79 * @param book Book to be saved 80 */ 81 void sync(QofBook*) override; 82 /** 83 * An object is about to be edited. 84 * 85 * @param inst Object being edited 86 */ 87 void begin(QofInstance*) override; 88 /** 89 * Object editing is complete and the object should be saved. 90 * 91 * @param inst Object being edited 92 */ 93 void commit(QofInstance*) override; 94 /** 95 * Object editing has been cancelled. 96 * 97 * @param inst Object being edited 98 */ 99 void rollback(QofInstance*) override; 100 /** Connect the backend to a GncSqlConnection. 101 * Sets up version info. Calling with nullptr clears the connection and 102 * destroys the version info. 103 */ 104 void connect(GncSqlConnection *conn) noexcept; 105 /** 106 * Initializes DB table version information. 107 */ 108 void init_version_info() noexcept; 109 bool reset_version_info() noexcept; 110 /** 111 * Finalizes DB table version information. 112 */ 113 void finalize_version_info() noexcept; 114 /* FIXME: These are just pass-throughs of m_conn functions. */ 115 GncSqlStatementPtr create_statement_from_sql(const std::string& str) const noexcept; 116 /** Executes an SQL SELECT statement and returns the result rows. If an 117 * error occurs, an entry is added to the log, an error status is returned 118 * to qof and nullptr is returned. 119 * 120 * @param statement Statement 121 * @return Results, or nullptr if an error has occurred 122 */ 123 GncSqlResultPtr execute_select_statement(const GncSqlStatementPtr& stmt) const noexcept; 124 int execute_nonselect_statement(const GncSqlStatementPtr& stmt) const noexcept; 125 std::string quote_string(const std::string&) const noexcept; 126 /** 127 * Creates a table in the database 128 * 129 * @param table_name Table name 130 * @param col_table DB table description 131 * @return TRUE if successful, FALSE if unsuccessful 132 */ 133 bool create_table(const std::string& table_name, const EntryVec& col_table) const noexcept; 134 /** 135 * Creates a table in the database and sets its version 136 * 137 * @param table_name Table name 138 * @param table_version Table version 139 * @param col_table DB table description 140 * @return TRUE if successful, FALSE if unsuccessful 141 */ 142 bool create_table(const std::string& table_name, int table_version, 143 const EntryVec& col_table) noexcept; 144 /** 145 * Create/update all tables in the database 146 */ 147 void create_tables() noexcept; 148 149 /** 150 * Creates an index in the database 151 * 152 * @param index_name Index name 153 * @param table_name Table name 154 * @param col_table Columns that the index should index 155 * @return TRUE if successful, FALSE if unsuccessful 156 */ 157 bool create_index(const std::string& index_name, 158 const std::string& table_name, 159 const EntryVec& col_table) const noexcept; 160 /** 161 * Adds one or more columns to an existing table. 162 * 163 * @param table_name SQL table name 164 * @param new_col_table Column table for new columns 165 * @return TRUE if successful, FALSE if unsuccessful 166 */ 167 bool add_columns_to_table(const std::string& table_name, 168 const EntryVec& col_table) const noexcept; 169 /** 170 * Upgrades a table to a new structure. 171 * 172 * The upgrade is done by creating a new table with the new structure, 173 * SELECTing the old data into the new table, deleting the old table, then 174 * renaming the new table. Therefore, this will only work if the new table 175 * structure is similar enough to the old table that the SELECT will work. 176 * 177 * @param table_name SQL table name 178 * @param col_table Column table 179 */ 180 void upgrade_table (const std::string& table_name, 181 const EntryVec& col_table) noexcept; 182 /** 183 * Returns the version number for a DB table. 184 * 185 * @param table_name Table name 186 * @return Version number, or 0 if the table does not exist 187 */ 188 uint_t get_table_version(const std::string& table_name) const noexcept; 189 bool set_table_version (const std::string& table_name, uint_t version) noexcept; 190 /** 191 * Register a commodity to be committed after loading is complete. 192 * 193 * Necessary to save corrections made while loading. 194 * @param comm The commodity item to be committed. 195 */ 196 void commodity_for_postload_processing(gnc_commodity*); 197 /** 198 * Get the GncSqlObjectBackend for the indicated type. 199 * 200 * Required because we need to pass a pointer to this to a callback via a C 201 * function. 202 * @param type: The QofInstance type constant to select the object backend. 203 */ 204 GncSqlObjectBackendPtr get_object_backend(const std::string& type) const noexcept; 205 /** 206 * Checks whether an object is in the database or not. 207 * 208 * @param table_name DB table name 209 * @param obj_name QOF object type name 210 * @param pObject Object to be checked 211 * @param table DB table description 212 * @return TRUE if the object is in the database, FALSE otherwise 213 */ 214 bool object_in_db (const char* table_name, QofIdTypeConst obj_name, 215 const gpointer pObject, const EntryVec& table ) const noexcept; 216 /** 217 * Performs an operation on the database. 218 * 219 * @param op Operation type 220 * @param table_name SQL table name 221 * @param obj_name QOF object type name 222 * @param pObject Gnucash object 223 * @param table DB table description 224 * @return TRUE if successful, FALSE if not 225 */ 226 bool do_db_operation (E_DB_OPERATION op, const char* table_name, 227 QofIdTypeConst obj_name, gpointer pObject, 228 const EntryVec& table) const noexcept; 229 /** 230 * Ensure that a commodity referenced in another object is in fact saved 231 * in the database. 232 * 233 * @param comm The commodity in question 234 * @return true if the commodity needed to be saved. 235 */ 236 bool save_commodity(gnc_commodity* comm) noexcept; book() const237 QofBook* book() const noexcept { return m_book; } set_loading(bool loading)238 void set_loading(bool loading) noexcept { m_loading = loading; } pristine() const239 bool pristine() const noexcept { return m_is_pristine_db; } 240 void update_progress(double pct) const noexcept; 241 void finish_progress() const noexcept; 242 243 protected: 244 GncSqlConnection* m_conn = nullptr; /**< SQL connection */ 245 QofBook* m_book = nullptr; /**< The primary, main open book */ 246 bool m_loading; /**< We are performing an initial load */ 247 bool m_in_query; /**< We are processing a query */ 248 bool m_is_pristine_db; /**< Are we saving to a new pristine db? */ 249 const char* m_time_format = nullptr; /**< Server-specific date-time string format */ 250 VersionVec m_versions; /**< Version number for each table */ 251 private: 252 bool write_account_tree(Account*); 253 bool write_accounts(); 254 bool write_transactions(); 255 bool write_template_transactions(); 256 bool write_schedXactions(); 257 GncSqlStatementPtr build_insert_statement (const char* table_name, 258 QofIdTypeConst obj_name, 259 gpointer pObject, 260 const EntryVec& table) const noexcept; 261 GncSqlStatementPtr build_update_statement (const gchar* table_name, 262 QofIdTypeConst obj_name, 263 gpointer pObject, 264 const EntryVec& table) const noexcept; 265 GncSqlStatementPtr build_delete_statement (const char* table_name, 266 QofIdTypeConst obj_name, 267 gpointer pObject, 268 const EntryVec& table) const noexcept; 269 270 class ObjectBackendRegistry 271 { 272 public: 273 ObjectBackendRegistry(); 274 ObjectBackendRegistry(const ObjectBackendRegistry&) = delete; 275 ObjectBackendRegistry(const ObjectBackendRegistry&&) = delete; 276 ObjectBackendRegistry operator=(const ObjectBackendRegistry&) = delete; 277 ObjectBackendRegistry operator=(const ObjectBackendRegistry&&) = delete; 278 ~ObjectBackendRegistry() = default; 279 void register_backend(OBEEntry&& entry) noexcept; 280 void register_backend(GncSqlObjectBackendPtr obe) noexcept; 281 GncSqlObjectBackendPtr get_object_backend(const std::string& type) const; 282 void load_remaining(GncSqlBackend*); begin()283 OBEVec::iterator begin() { return m_registry.begin(); } end()284 OBEVec::iterator end() { return m_registry.end(); } size()285 OBEVec::size_type size() { return m_registry.size(); } 286 private: 287 OBEVec m_registry; 288 }; 289 ObjectBackendRegistry m_backend_registry; 290 std::vector<gnc_commodity*> m_postload_commodities; 291 }; 292 293 #endif //__GNC_SQL_BACKEND_HPP__ 294