Line data Source code
1 : /************************************************************************
2 : * gnc-dbiproviderimpl.hpp: Encapsulate differences among Dbi backends. *
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 : #ifndef __GNC_DBISQLPROVIDERIMPL_HPP__
24 : #define __GNC_DBISQLPROVIDERIMPL_HPP__
25 : #include <guid.hpp>
26 : #include <config.h>
27 :
28 : #include <string>
29 : #include <algorithm>
30 : #include <vector>
31 :
32 : #include "gnc-backend-dbi.hpp"
33 : #include "gnc-dbiprovider.hpp"
34 : #include "gnc-backend-dbi.h"
35 : #include <gnc-sql-column-table-entry.hpp>
36 :
37 : using StrVec = std::vector<std::string>;
38 :
39 : template <DbType T>
40 : class GncDbiProviderImpl : public GncDbiProvider
41 : {
42 : public:
43 : StrVec get_table_list(dbi_conn conn, const std::string& table);
44 : void append_col_def(std::string& ddl, const GncSqlColumnInfo& info);
45 : StrVec get_index_list (dbi_conn conn);
46 : void drop_index(dbi_conn conn, const std::string& index);
47 : };
48 :
49 : template <DbType T> GncDbiProviderPtr
50 0 : make_dbi_provider()
51 : {
52 0 : return GncDbiProviderPtr(new GncDbiProviderImpl<T>);
53 : }
54 :
55 : template<> void
56 0 : GncDbiProviderImpl<DbType::DBI_SQLITE>::append_col_def(std::string& ddl,
57 : const GncSqlColumnInfo& info)
58 : {
59 0 : const char* type_name = nullptr;
60 :
61 0 : if (info.m_type == BCT_INT)
62 : {
63 0 : type_name = "integer";
64 : }
65 0 : else if (info.m_type == BCT_INT64)
66 : {
67 0 : type_name = "bigint";
68 : }
69 0 : else if (info.m_type == BCT_DOUBLE)
70 : {
71 0 : type_name = "float8";
72 : }
73 0 : else if (info.m_type == BCT_STRING || info.m_type == BCT_DATE
74 0 : || info.m_type == BCT_DATETIME)
75 : {
76 0 : type_name = "text";
77 : }
78 : else
79 : {
80 0 : PERR ("Unknown column type: %d\n", info.m_type);
81 0 : type_name = "";
82 : }
83 0 : ddl += (info.m_name + " " + type_name);
84 0 : if (info.m_size != 0)
85 : {
86 0 : ddl += "(" + std::to_string(info.m_size) + ")";
87 : }
88 0 : if (info.m_primary_key)
89 : {
90 0 : ddl += " PRIMARY KEY";
91 : }
92 0 : if (info.m_autoinc)
93 : {
94 0 : ddl += " AUTOINCREMENT";
95 : }
96 0 : if (info.m_not_null)
97 : {
98 0 : ddl += " NOT NULL";
99 : }
100 0 : }
101 :
102 :
103 : template<> void
104 0 : GncDbiProviderImpl<DbType::DBI_MYSQL>::append_col_def (std::string& ddl,
105 : const GncSqlColumnInfo& info)
106 : {
107 0 : const char* type_name = nullptr;
108 :
109 0 : if (info.m_type == BCT_INT)
110 : {
111 0 : type_name = "integer";
112 : }
113 0 : else if (info.m_type == BCT_INT64)
114 : {
115 0 : type_name = "bigint";
116 : }
117 0 : else if (info.m_type == BCT_DOUBLE)
118 : {
119 0 : type_name = "double";
120 : }
121 0 : else if (info.m_type == BCT_STRING)
122 : {
123 0 : type_name = "varchar";
124 : }
125 0 : else if (info.m_type == BCT_DATE)
126 : {
127 0 : type_name = "date";
128 : }
129 0 : else if (info.m_type == BCT_DATETIME)
130 : {
131 0 : type_name = "DATETIME NULL DEFAULT '1970-01-01 00:00:00'";
132 : }
133 : else
134 : {
135 0 : PERR ("Unknown column type: %d\n", info.m_type);
136 0 : type_name = "";
137 : }
138 0 : ddl += info.m_name + " " + type_name;
139 0 : if (info.m_size != 0 && info.m_type == BCT_STRING)
140 : {
141 0 : ddl += "(" + std::to_string(info.m_size) + ")";
142 : }
143 0 : if (info.m_unicode)
144 : {
145 0 : ddl += " CHARACTER SET utf8";
146 : }
147 0 : if (info.m_primary_key)
148 : {
149 0 : ddl += " PRIMARY KEY";
150 : }
151 0 : if (info.m_autoinc)
152 : {
153 0 : ddl += " AUTO_INCREMENT";
154 : }
155 0 : if (info.m_not_null)
156 : {
157 0 : ddl += " NOT NULL";
158 : }
159 0 : }
160 :
161 :
162 : template<> void
163 0 : GncDbiProviderImpl<DbType::DBI_PGSQL>::append_col_def (std::string& ddl,
164 : const GncSqlColumnInfo& info)
165 : {
166 0 : const char* type_name = nullptr;
167 :
168 0 : if (info.m_type == BCT_INT)
169 : {
170 0 : if (info.m_autoinc)
171 : {
172 0 : type_name = "serial";
173 : }
174 : else
175 : {
176 0 : type_name = "integer";
177 : }
178 : }
179 0 : else if (info.m_type == BCT_INT64)
180 : {
181 0 : type_name = "int8";
182 : }
183 0 : else if (info.m_type == BCT_DOUBLE)
184 :
185 : {
186 0 : type_name = "double precision";
187 : }
188 0 : else if (info.m_type == BCT_STRING)
189 : {
190 0 : type_name = "varchar";
191 : }
192 0 : else if (info.m_type == BCT_DATE)
193 : {
194 0 : type_name = "date";
195 : }
196 0 : else if (info.m_type == BCT_DATETIME)
197 : {
198 0 : type_name = "timestamp without time zone";
199 : }
200 : else
201 : {
202 0 : PERR ("Unknown column type: %d\n", info.m_type);
203 0 : type_name = "";
204 : }
205 0 : ddl += info.m_name + " " + type_name;
206 0 : if (info.m_size != 0 && info.m_type == BCT_STRING)
207 : {
208 0 : ddl += "(" + std::to_string(info.m_size) + ")";
209 : }
210 0 : if (info.m_primary_key)
211 : {
212 0 : ddl += " PRIMARY KEY";
213 : }
214 0 : if (info.m_not_null)
215 : {
216 0 : ddl += " NOT NULL";
217 : }
218 0 : }
219 :
220 : static StrVec
221 0 : conn_get_table_list (dbi_conn conn, const std::string& dbname,
222 : const std::string& table)
223 : {
224 0 : StrVec retval;
225 0 : const char* tableptr = (table.empty() ? nullptr : table.c_str());
226 0 : auto tables = dbi_conn_get_table_list (conn, dbname.c_str(), tableptr);
227 0 : while (dbi_result_next_row (tables) != 0)
228 : {
229 0 : std::string table_name {dbi_result_get_string_idx (tables, 1)};
230 0 : retval.push_back(table_name);
231 0 : }
232 0 : dbi_result_free (tables);
233 0 : return retval;
234 0 : }
235 :
236 : template<> StrVec
237 0 : GncDbiProviderImpl<DbType::DBI_SQLITE>::get_table_list (dbi_conn conn,
238 : const std::string& table)
239 : {
240 : /* Return the list, but remove the tables that sqlite3 adds for
241 : * its own use. */
242 0 : std::string dbname (dbi_conn_get_option (conn, "dbname"));
243 0 : auto list = conn_get_table_list (conn, dbname, table);
244 0 : auto end = std::remove(list.begin(), list.end(), "sqlite_sequence");
245 0 : list.erase(end, list.end());
246 0 : return list;
247 0 : }
248 :
249 : template<> StrVec
250 0 : GncDbiProviderImpl<DbType::DBI_MYSQL>::get_table_list (dbi_conn conn,
251 : const std::string& table)
252 : {
253 0 : std::string dbname (dbi_conn_get_option (conn, "dbname"));
254 0 : dbname.insert((std::string::size_type)0, 1, '`');
255 0 : dbname += '`';
256 0 : return conn_get_table_list (conn, dbname, table);
257 0 : }
258 :
259 : template<> StrVec
260 0 : GncDbiProviderImpl<DbType::DBI_PGSQL>::get_table_list (dbi_conn conn,
261 : const std::string& table)
262 : {
263 0 : const char* query_no_regex = "SELECT relname FROM pg_class WHERE relname"
264 : "!~ '^(pg|sql)_' AND relkind = 'r' ORDER BY relname";
265 0 : std::string query_with_regex = "SELECT relname FROM pg_class WHERE relname LIKE '";
266 0 : query_with_regex += table + "' AND relkind = 'r' ORDER BY relname";
267 : dbi_result result;
268 0 : if (table.empty())
269 0 : result = dbi_conn_query (conn, query_no_regex);
270 : else
271 0 : result = dbi_conn_query (conn, query_with_regex.c_str());
272 :
273 0 : StrVec list;
274 : const char* errmsg;
275 0 : if (dbi_conn_error (conn, &errmsg) != DBI_ERROR_NONE)
276 : {
277 0 : PWARN ("Table List Retrieval Error: %s\n", errmsg);
278 0 : return list;
279 : }
280 :
281 0 : while (dbi_result_next_row (result) != 0)
282 : {
283 0 : std::string index_name {dbi_result_get_string_idx (result, 1)};
284 0 : list.push_back(index_name);
285 0 : }
286 0 : dbi_result_free (result);
287 0 : return list;
288 0 : }
289 :
290 : template<> StrVec
291 0 : GncDbiProviderImpl<DbType::DBI_SQLITE>::get_index_list (dbi_conn conn)
292 : {
293 0 : StrVec retval;
294 : const char* errmsg;
295 0 : dbi_result result = dbi_conn_query (conn,
296 : "SELECT name FROM sqlite_master WHERE type = 'index' AND name NOT LIKE 'sqlite_autoindex%'");
297 0 : if (dbi_conn_error (conn, &errmsg) != DBI_ERROR_NONE)
298 : {
299 0 : PWARN ("Index Table Retrieval Error: %s\n", errmsg);
300 0 : return retval;
301 : }
302 0 : while (dbi_result_next_row (result) != 0)
303 : {
304 0 : std::string index_name {dbi_result_get_string_idx (result, 1)};
305 0 : retval.push_back(index_name);
306 0 : }
307 0 : dbi_result_free (result);
308 0 : return retval;
309 0 : }
310 :
311 : template<> StrVec
312 0 : GncDbiProviderImpl<DbType::DBI_MYSQL>::get_index_list (dbi_conn conn)
313 : {
314 0 : StrVec retval;
315 : const char* errmsg;
316 0 : auto tables = get_table_list(conn, "");
317 0 : for (auto table_name : tables)
318 : {
319 0 : auto result = dbi_conn_queryf (conn,
320 : "SHOW INDEXES IN %s WHERE Key_name != 'PRIMARY'",
321 : table_name.c_str());
322 0 : if (dbi_conn_error (conn, &errmsg) != DBI_ERROR_NONE)
323 : {
324 0 : PWARN ("Index Table Retrieval Error: %s on table %s\n",
325 : errmsg, table_name.c_str());
326 0 : continue;
327 : }
328 :
329 0 : while (dbi_result_next_row (result) != 0)
330 : {
331 0 : std::string index_name {dbi_result_get_string_idx (result, 3)};
332 0 : retval.push_back(index_name + " " + table_name);
333 0 : }
334 0 : dbi_result_free (result);
335 0 : }
336 :
337 0 : return retval;
338 0 : }
339 :
340 : template<> StrVec
341 0 : GncDbiProviderImpl<DbType::DBI_PGSQL>::get_index_list (dbi_conn conn)
342 : {
343 0 : StrVec retval;
344 : const char* errmsg;
345 0 : PINFO ("Retrieving postgres index list\n");
346 0 : auto result = dbi_conn_query (conn,
347 : "SELECT relname FROM pg_class AS a INNER JOIN pg_index AS b ON (b.indexrelid = a.oid) INNER JOIN pg_namespace AS c ON (a.relnamespace = c.oid) WHERE reltype = '0' AND indisprimary = 'f' AND nspname = 'public'");
348 0 : if (dbi_conn_error (conn, &errmsg) != DBI_ERROR_NONE)
349 : {
350 0 : PWARN("Index Table Retrieval Error: %s\n", errmsg);
351 0 : return retval;
352 : }
353 0 : while (dbi_result_next_row (result) != 0)
354 : {
355 0 : std::string index_name {dbi_result_get_string_idx (result, 1)};
356 0 : retval.push_back(index_name);
357 0 : }
358 0 : dbi_result_free (result);
359 0 : return retval;
360 0 : }
361 :
362 : template <DbType P> void
363 0 : GncDbiProviderImpl<P>::drop_index(dbi_conn conn, const std::string& index)
364 : {
365 0 : dbi_result result = dbi_conn_queryf (conn, "DROP INDEX %s", index.c_str());
366 0 : if (result)
367 0 : dbi_result_free (result);
368 0 : }
369 :
370 : template<> void
371 0 : GncDbiProviderImpl<DbType::DBI_MYSQL>::drop_index (dbi_conn conn, const std::string& index)
372 : {
373 :
374 0 : auto sep = index.find(' ', 0);
375 0 : if (index.find(' ', sep + 1) != std::string::npos)
376 : {
377 0 : PWARN("Drop index error: invalid MySQL index format (<index> <table>): %s",
378 : index.c_str());
379 0 : return;
380 : }
381 :
382 0 : auto result = dbi_conn_queryf (conn, "DROP INDEX %s ON %s",
383 0 : index.substr(0, sep).c_str(),
384 0 : index.substr(sep + 1).c_str());
385 0 : if (result)
386 0 : dbi_result_free (result);
387 : }
388 : #endif //__GNC_DBISQLPROVIDERIMPL_HPP__
|
