r19959 - gnucash/trunk/src/backend/dbi - Add enumeration of indexes in sql backends.
John Ralls
jralls at code.gnucash.org
Fri Dec 17 18:50:26 EST 2010
Author: jralls
Date: 2010-12-17 18:50:25 -0500 (Fri, 17 Dec 2010)
New Revision: 19959
Trac: http://svn.gnucash.org/trac/changeset/19959
Added:
gnucash/trunk/src/backend/dbi/gnc-backend-dbi-priv.h
Modified:
gnucash/trunk/src/backend/dbi/gnc-backend-dbi.c
gnucash/trunk/src/backend/dbi/test/test-dbi-basic.c
gnucash/trunk/src/backend/dbi/test/test-dbi-stuff.c
Log:
Add enumeration of indexes in sql backends.
This is a first step to rewriting databases in a safe way. Indexes are
named entities in sql databases and so need to be renamed along with the
tables before creating and populating a new set.
This change also introduces a unit test of the new enumeration, and that
test has to be able to see data structures which are private to
gnc-backend-dbi.c, so those data structures are moved to a new file,
gnc-backend-dbi-priv.h, which both gnc-backend-dbi.c and
tests/test-stuff.c can include.
Added: gnucash/trunk/src/backend/dbi/gnc-backend-dbi-priv.h
===================================================================
--- gnucash/trunk/src/backend/dbi/gnc-backend-dbi-priv.h (rev 0)
+++ gnucash/trunk/src/backend/dbi/gnc-backend-dbi-priv.h 2010-12-17 23:50:25 UTC (rev 19959)
@@ -0,0 +1,82 @@
+/********************************************************************
+ * gnc-backend-dbi.c: load and save data to SQL via libdbi *
+ * *
+ * This program is free software; you can redistribute it and/or *
+ * modify it under the terms of the GNU General Public License as *
+ * published by the Free Software Foundation; either version 2 of *
+ * the License, or (at your option) any later version. *
+ * *
+ * This program is distributed in the hope that it will be useful, *
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of *
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *
+ * GNU General Public License for more details. *
+ * *
+ * You should have received a copy of the GNU General Public License*
+ * along with this program; if not, contact: *
+ * *
+ * Free Software Foundation Voice: +1-617-542-5942 *
+ * 51 Franklin Street, Fifth Floor Fax: +1-617-542-2652 *
+ * Boston, MA 02110-1301, USA gnu at gnu.org *
+\********************************************************************/
+/* Private structures and variables for gnc-backend-dbi.c and its unit tests */
+#ifndef GNC_BACKEND_DBI_PRIV_H
+#define GNC_BACKEND_DBI_PRIV_H
+#include <dbi/dbi.h>
+#include "gnc-backend-sql.h"
+
+typedef gchar* (*CREATE_TABLE_DDL_FN)( GncSqlConnection* conn,
+ const gchar* table_name,
+ const GList* col_info_list );
+typedef GSList* (*GET_TABLE_LIST_FN)( dbi_conn conn, const gchar* dbname );
+typedef void (*APPEND_COLUMN_DEF_FN)( GString* ddl, GncSqlColumnInfo* info );
+typedef GSList* (*GET_INDEX_LIST_FN)( dbi_conn conn );
+typedef struct
+{
+ CREATE_TABLE_DDL_FN create_table_ddl;
+ GET_TABLE_LIST_FN get_table_list;
+ APPEND_COLUMN_DEF_FN append_col_def;
+ GET_INDEX_LIST_FN get_index_list;
+} provider_functions_t;
+
+
+struct GncDbiBackend_struct
+{
+ GncSqlBackend sql_be;
+
+ dbi_conn conn;
+
+ /*@ dependent @*/
+ QofBook *primary_book; /* The primary, main open book */
+ gboolean loading; /* We are performing an initial load */
+ gboolean in_query;
+ gboolean supports_transactions;
+ gboolean is_pristine_db; // Are we saving to a new pristine db?
+ gboolean exists; // Does the database exist?
+
+ gint obj_total; // Total # of objects (for percentage calculation)
+ gint operations_done; // Number of operations (save/load) done
+// GHashTable* versions; // Version number for each table
+};
+
+typedef struct GncDbiBackend_struct GncDbiBackend;
+
+typedef struct
+{
+ GncSqlConnection base;
+
+ /*@ observer @*/
+ QofBackend* qbe;
+ /*@ observer @*/
+ dbi_conn conn;
+ /*@ observer @*/
+ provider_functions_t* provider;
+ gboolean conn_ok; // Used by the error handler routines to flag if the connection is ok to use
+ gint last_error; // Code of the last error that occurred. This is set in the error callback function
+ gint error_repeat; // Used in case of transient errors. After such error, another attempt at the
+ // original call is allowed. error_repeat tracks the number of attempts and can
+ // be used to prevent infinite loops.
+ gboolean retry; // Signals the calling function that it should retry (the error handler detected
+ // transient error and managed to resolve it, but it can't run the original query)
+} GncDbiSqlConnection;
+
+#endif //GNC_BACKEND_DBI_PRIV_H
Modified: gnucash/trunk/src/backend/dbi/gnc-backend-dbi.c
===================================================================
--- gnucash/trunk/src/backend/dbi/gnc-backend-dbi.c 2010-12-17 21:31:22 UTC (rev 19958)
+++ gnucash/trunk/src/backend/dbi/gnc-backend-dbi.c 2010-12-17 23:50:25 UTC (rev 19959)
@@ -36,9 +36,8 @@
#include "gmtime_r.h"
#endif
-#include <dbi/dbi.h>
-#include "gnc-backend-sql.h"
+#include "gnc-backend-dbi-priv.h"
#include "qof.h"
#include "qofquery-p.h"
@@ -80,28 +79,18 @@
#define SQLITE3_URI_PREFIX (SQLITE3_URI_TYPE "://")
#define PGSQL_DEFAULT_PORT 5432
-typedef gchar* (*CREATE_TABLE_DDL_FN)( GncSqlConnection* conn,
- const gchar* table_name,
- const GList* col_info_list );
-typedef GSList* (*GET_TABLE_LIST_FN)( dbi_conn conn, const gchar* dbname );
-typedef void (*APPEND_COLUMN_DEF_FN)( GString* ddl, GncSqlColumnInfo* info );
-typedef struct
-{
- CREATE_TABLE_DDL_FN create_table_ddl;
- GET_TABLE_LIST_FN get_table_list;
- APPEND_COLUMN_DEF_FN append_col_def;
-} provider_functions_t;
-
static /*@ null @*/ gchar* conn_create_table_ddl_sqlite3( GncSqlConnection* conn,
const gchar* table_name,
const GList* col_info_list );
static GSList* conn_get_table_list( dbi_conn conn, const gchar* dbname );
static void append_sqlite3_col_def( GString* ddl, GncSqlColumnInfo* info );
+static GSList *conn_get_index_list_sqlite3( dbi_conn conn );
static provider_functions_t provider_sqlite3 =
{
conn_create_table_ddl_sqlite3,
conn_get_table_list,
- append_sqlite3_col_def
+ append_sqlite3_col_def,
+ conn_get_index_list_sqlite3
};
#define SQLITE3_TIMESPEC_STR_FORMAT "%04d%02d%02d%02d%02d%02d"
@@ -109,11 +98,13 @@
const gchar* table_name,
const GList* col_info_list );
static void append_mysql_col_def( GString* ddl, GncSqlColumnInfo* info );
+static GSList *conn_get_index_list_mysql( dbi_conn conn );
static provider_functions_t provider_mysql =
{
conn_create_table_ddl_mysql,
conn_get_table_list,
- append_mysql_col_def
+ append_mysql_col_def,
+ conn_get_index_list_mysql
};
#define MYSQL_TIMESPEC_STR_FORMAT "%04d%02d%02d%02d%02d%02d"
@@ -122,18 +113,21 @@
const GList* col_info_list );
static GSList* conn_get_table_list_pgsql( dbi_conn conn, const gchar* dbname );
static void append_pgsql_col_def( GString* ddl, GncSqlColumnInfo* info );
-static gboolean gnc_dbi_lock_database( QofBackend *qbe, gboolean ignore_lock );
-static void gnc_dbi_unlock( QofBackend *qbe );
-static gboolean save_may_clobber_data( QofBackend* qbe );
+static GSList *conn_get_index_list_pgsql( dbi_conn conn );
static provider_functions_t provider_pgsql =
{
conn_create_table_ddl_pgsql,
conn_get_table_list_pgsql,
- append_pgsql_col_def
+ append_pgsql_col_def,
+ conn_get_index_list_pgsql
};
#define PGSQL_TIMESPEC_STR_FORMAT "%04d%02d%02d %02d%02d%02d"
+static gboolean gnc_dbi_lock_database( QofBackend *qbe, gboolean ignore_lock );
+static void gnc_dbi_unlock( QofBackend *qbe );
+static gboolean save_may_clobber_data( QofBackend* qbe );
+
static /*@ null @*/ gchar* create_index_ddl( GncSqlConnection* conn,
const gchar* index_name,
const gchar* table_name,
@@ -147,45 +141,7 @@
#define GNC_DBI_PROVIDER_MYSQL (&provider_mysql)
#define GNC_DBI_PROVIDER_PGSQL (&provider_pgsql)
-struct GncDbiBackend_struct
-{
- GncSqlBackend sql_be;
- dbi_conn conn;
-
- /*@ dependent @*/
- QofBook *primary_book; /* The primary, main open book */
- gboolean loading; /* We are performing an initial load */
- gboolean in_query;
- gboolean supports_transactions;
- gboolean is_pristine_db; // Are we saving to a new pristine db?
- gboolean exists; // Does the database exist?
-
- gint obj_total; // Total # of objects (for percentage calculation)
- gint operations_done; // Number of operations (save/load) done
-// GHashTable* versions; // Version number for each table
-};
-typedef struct GncDbiBackend_struct GncDbiBackend;
-
-typedef struct
-{
- GncSqlConnection base;
-
- /*@ observer @*/
- QofBackend* qbe;
- /*@ observer @*/
- dbi_conn conn;
- /*@ observer @*/
- provider_functions_t* provider;
- gboolean conn_ok; // Used by the error handler routines to flag if the connection is ok to use
- gint last_error; // Code of the last error that occurred. This is set in the error callback function
- gint error_repeat; // Used in case of transient errors. After such error, another attempt at the
- // original call is allowed. error_repeat tracks the number of attempts and can
- // be used to prevent infinite loops.
- gboolean retry; // Signals the calling function that it should retry (the error handler detected
- // transient error and managed to resolve it, but it can't run the original query)
-} GncDbiSqlConnection;
-
#define DBI_MAX_CONN_ATTEMPTS 5
/* ================================================================= */
@@ -369,6 +325,28 @@
LEAVE (" ");
}
+static GSList*
+conn_get_index_list_sqlite3( dbi_conn conn )
+{
+ GSList *list = NULL;
+ const gchar *errmsg;
+ dbi_result result = dbi_conn_query( conn, "SELECT name FROM sqlite_master WHERE type = 'index' AND name NOT LIKE 'sqlite_autoindex%'" );
+ if ( dbi_conn_error( conn, &errmsg ) != DBI_ERROR_NONE )
+ {
+ g_print( "Index Table Retrieval Error: %s\n", errmsg );
+ return NULL;
+ }
+ while ( dbi_result_next_row( result ) != 0 )
+ {
+ const gchar* index_name;
+
+ index_name = dbi_result_get_string_idx( result, 1 );
+ list = g_slist_prepend( list, strdup( index_name ) );
+ }
+ dbi_result_free( result );
+ return list;
+}
+
static void
mysql_error_fn( dbi_conn conn, void* user_data )
{
@@ -862,6 +840,44 @@
LEAVE (" ");
}
+static GSList*
+conn_get_index_list_mysql( dbi_conn conn )
+{
+ GSList *index_list = NULL;
+ dbi_result table_list;
+ const char *errmsg;
+ const gchar *dbname = dbi_conn_get_option( conn, "dbname" );
+ g_return_val_if_fail( conn != NULL, NULL );
+ table_list = dbi_conn_get_table_list( conn, dbname, NULL );
+ if ( dbi_conn_error( conn, &errmsg ) != DBI_ERROR_NONE )
+ {
+ g_print( "Table Retrieval Error: %s\n", errmsg );
+ return NULL;
+ }
+ while ( dbi_result_next_row( table_list ) != 0 )
+ {
+ dbi_result result;
+ const gchar *table_name = dbi_result_get_string_idx( table_list, 1 );
+ result = dbi_conn_queryf( conn,
+ "SHOW INDEXES IN %s WHERE Key_name != 'PRIMARY'",
+ table_name );
+ if ( dbi_conn_error( conn, &errmsg ) != DBI_ERROR_NONE )
+ {
+ g_print( "Index Table Retrieval Error: %s\n", errmsg );
+ continue;
+ }
+
+ while ( dbi_result_next_row( result ) != 0 )
+ {
+ const gchar* index_name = dbi_result_get_string_idx( result, 3 );
+ index_list = g_slist_prepend( index_list, strdup( index_name ) );
+ }
+ dbi_result_free( result );
+ }
+
+ return index_list;
+}
+
static void
pgsql_error_fn( dbi_conn conn, void* user_data )
{
@@ -1067,6 +1083,31 @@
LEAVE (" ");
}
+static GSList*
+conn_get_index_list_pgsql( dbi_conn conn )
+{
+ GSList *list = NULL;
+ const gchar *errmsg;
+ dbi_result result;
+ g_print( "Retrieving postgres index list\n");
+ result = dbi_conn_query( conn, "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'" );
+ if ( dbi_conn_error( conn, &errmsg ) != DBI_ERROR_NONE )
+ {
+ g_print( "Index Table Retrieval Error: %s\n", errmsg );
+ return NULL;
+ }
+ while ( dbi_result_next_row( result ) != 0 )
+ {
+ const gchar* index_name;
+
+ index_name = dbi_result_get_string_idx( result, 1 );
+ list = g_slist_prepend( list, strdup( index_name ) );
+ }
+ dbi_result_free( result );
+ return list;
+}
+
+
/* ================================================================= */
static void
Modified: gnucash/trunk/src/backend/dbi/test/test-dbi-basic.c
===================================================================
--- gnucash/trunk/src/backend/dbi/test/test-dbi-basic.c 2010-12-17 21:31:22 UTC (rev 19958)
+++ gnucash/trunk/src/backend/dbi/test/test-dbi-basic.c 2010-12-17 23:50:25 UTC (rev 19959)
@@ -112,13 +112,15 @@
filename = tempnam( "/tmp", "test-sqlite3-" );
printf( "Using filename: %s\n", filename );
test_dbi_store_and_reload( "sqlite3", session_1, filename );
-#if 0
+#ifdef TEST_MYSQL_URL
printf( "TEST_MYSQL_URL='%s'\n", TEST_MYSQL_URL );
if ( strlen( TEST_MYSQL_URL ) > 0 )
{
session_1 = create_session();
test_dbi_store_and_reload( "mysql", session_1, TEST_MYSQL_URL );
}
+#endif
+#ifdef TEST_PGSQL_URL
printf( "TEST_PGSQL_URL='%s'\n", TEST_PGSQL_URL );
if ( strlen( TEST_PGSQL_URL ) > 0 )
{
Modified: gnucash/trunk/src/backend/dbi/test/test-dbi-stuff.c
===================================================================
--- gnucash/trunk/src/backend/dbi/test/test-dbi-stuff.c 2010-12-17 21:31:22 UTC (rev 19958)
+++ gnucash/trunk/src/backend/dbi/test/test-dbi-stuff.c 2010-12-17 23:50:25 UTC (rev 19959)
@@ -34,6 +34,7 @@
#include "Split.h"
#include "Transaction.h"
#include "gnc-commodity.h"
+#include "../gnc-backend-dbi-priv.h"
static QofLogModule log_module = "test-dbi";
@@ -132,15 +133,33 @@
}
static void
+test_conn_get_index_list( QofBackend *qbe )
+{
+ GncDbiBackend *be = (GncDbiBackend*)qbe;
+ GSList *index_list = ((GncDbiSqlConnection*)(be->sql_be.conn))->provider->get_index_list( be->conn );
+ g_print ( "Returned from index list\n");
+ if ( index_list == NULL )
+ {
+ do_test( FALSE, "Index List Test -- No List" );
+ return;
+ }
+ do_test( g_slist_length( index_list ) == 4, "Index List Test" );
+ g_slist_free( index_list );
+}
+
+static void
compare_books( QofBook* book_1, QofBook* book_2 )
{
+ QofBackend *be = qof_book_get_backend( book_2 );
compare_account_trees( book_1, book_2 );
compare_pricedbs( book_1, book_2 );
compare_txs( book_1, book_2 );
compare_sxs( book_1, book_2 );
compare_lots( book_1, book_2 );
+ test_conn_get_index_list( be );
}
+
void
test_dbi_store_and_reload( const gchar* driver, QofSession* session_1, const gchar* url )
{
@@ -152,14 +171,37 @@
// Save the session data
session_2 = qof_session_new();
qof_session_begin( session_2, url, FALSE, TRUE, TRUE );
+ if (session_2 && qof_session_get_error(session_2) != ERR_BACKEND_NO_ERR)
+ {
+ g_warning("Session Error: %d, %s", qof_session_get_error(session_2), qof_session_get_error_message(session_2));
+ do_test( FALSE, "First DB Session Creation Failed");
+ return;
+ }
qof_session_swap_data( session_1, session_2 );
qof_session_save( session_2, NULL );
+ if (session_2 && qof_session_get_error(session_2) != ERR_BACKEND_NO_ERR)
+ {
+ g_warning("Session Error: %s", qof_session_get_error_message(session_2));
+ do_test( FALSE, "First DB Session Save Failed");
+ return;
+ }
// Reload the session data
session_3 = qof_session_new();
qof_session_begin( session_3, url, TRUE, FALSE, FALSE );
+ if (session_3 && qof_session_get_error(session_3) != ERR_BACKEND_NO_ERR)
+ {
+ g_warning("Session Error: %s", qof_session_get_error_message(session_3));
+ do_test( FALSE, "Second DB Session Creation Failed");
+ return;
+ }
qof_session_load( session_3, NULL );
-
+ if (session_3 && qof_session_get_error(session_3) != ERR_BACKEND_NO_ERR)
+ {
+ g_warning("Session Error: %s", qof_session_get_error_message(session_3));
+ do_test( FALSE, "Second DBI Session Load Failed");
+ return;
+ }
// Compare with the original data
compare_books( qof_session_get_book( session_2 ), qof_session_get_book( session_3 ) );
qof_session_end( session_1 );
More information about the gnucash-changes
mailing list