r18134 - gnucash/trunk/src/backend/sql - Simplify SQL when loading slots for all objects of a given type.
Phil Longstaff
plongstaff at code.gnucash.org
Mon Jun 15 18:19:36 EDT 2009
Author: plongstaff
Date: 2009-06-15 18:19:35 -0400 (Mon, 15 Jun 2009)
New Revision: 18134
Trac: http://svn.gnucash.org/trac/changeset/18134
Modified:
gnucash/trunk/src/backend/sql/gnc-account-sql.c
gnucash/trunk/src/backend/sql/gnc-commodity-sql.c
gnucash/trunk/src/backend/sql/gnc-lots-sql.c
gnucash/trunk/src/backend/sql/gnc-price-sql.c
gnucash/trunk/src/backend/sql/gnc-slots-sql.c
gnucash/trunk/src/backend/sql/gnc-slots-sql.h
Log:
Simplify SQL when loading slots for all objects of a given type.
Modified: gnucash/trunk/src/backend/sql/gnc-account-sql.c
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-account-sql.c 2009-06-14 23:08:31 UTC (rev 18133)
+++ gnucash/trunk/src/backend/sql/gnc-account-sql.c 2009-06-15 22:19:35 UTC (rev 18134)
@@ -189,7 +189,6 @@
QofBook* pBook;
gnc_commodity_table* pTable;
GList* l_accounts_needing_parents = NULL;
- GList* list = NULL;
GSList* bal_slist;
GSList* bal;
@@ -210,20 +209,17 @@
if( result != NULL ) {
GncSqlRow* row = gnc_sql_result_get_first_row( result );
Account* acc;
+ gchar* sql;
while( row != NULL ) {
acc = load_single_account( be, row, &l_accounts_needing_parents );
- if( acc != NULL ) {
- list = g_list_append( list, acc );
- }
row = gnc_sql_result_get_next_row( result );
}
gnc_sql_result_dispose( result );
- if( list != NULL ) {
- gnc_sql_slots_load_for_list( be, list );
- g_list_free( list );
- }
+ sql = g_strdup_printf( "SELECT DISTINCT guid FROM %s", TABLE_NAME );
+ gnc_sql_slots_load_for_sql_subquery( be, sql, (BookLookupFn)xaccAccountLookup );
+ g_free( sql );
/* While there are items on the list of accounts needing parents,
try to see if the parent has now been loaded. Theory says that if
Modified: gnucash/trunk/src/backend/sql/gnc-commodity-sql.c
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-commodity-sql.c 2009-06-14 23:08:31 UTC (rev 18133)
+++ gnucash/trunk/src/backend/sql/gnc-commodity-sql.c 2009-06-15 22:19:35 UTC (rev 18134)
@@ -135,8 +135,8 @@
gnc_sql_statement_dispose( stmt );
if( result != NULL ) {
gnc_commodity* pCommodity;
- GList* list = NULL;
GncSqlRow* row = gnc_sql_result_get_first_row( result );
+ gchar* sql;
while( row != NULL ) {
pCommodity = load_single_commodity( be, row );
@@ -146,17 +146,16 @@
guid = *qof_instance_get_guid( QOF_INSTANCE(pCommodity) );
pCommodity = gnc_commodity_table_insert( pTable, pCommodity );
- list = g_list_append( list, pCommodity );
qof_instance_set_guid( QOF_INSTANCE(pCommodity), &guid );
}
row = gnc_sql_result_get_next_row( result );
}
gnc_sql_result_dispose( result );
- if( list != NULL ) {
- gnc_sql_slots_load_for_list( be, list );
- g_list_free( list );
- }
+ sql = g_strdup_printf( "SELECT DISTINCT guid FROM %s", COMMODITIES_TABLE );
+ gnc_sql_slots_load_for_sql_subquery( be, sql,
+ (BookLookupFn)gnc_commodity_find_commodity_by_guid );
+ g_free( sql );
}
}
/* ================================================================= */
Modified: gnucash/trunk/src/backend/sql/gnc-lots-sql.c
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-lots-sql.c 2009-06-14 23:08:31 UTC (rev 18133)
+++ gnucash/trunk/src/backend/sql/gnc-lots-sql.c 2009-06-15 22:19:35 UTC (rev 18134)
@@ -140,23 +140,19 @@
result = gnc_sql_execute_select_statement( be, stmt );
gnc_sql_statement_dispose( stmt );
if( result != NULL ) {
- GList* list = NULL;
GncSqlRow* row = gnc_sql_result_get_first_row( result );
GNCLot* lot;
+ gchar* sql;
while( row != NULL ) {
lot = load_single_lot( be, row );
- if( lot != NULL ) {
- list = g_list_append( list, lot );
- }
row = gnc_sql_result_get_next_row( result );
}
gnc_sql_result_dispose( result );
- if( list != NULL ) {
- gnc_sql_slots_load_for_list( be, list );
- g_list_free( list );
- }
+ sql = g_strdup_printf( "SELECT DISTINCT guid FROM %s", TABLE_NAME );
+ gnc_sql_slots_load_for_sql_subquery( be, sql, (BookLookupFn)gnc_lot_lookup );
+ g_free( sql );
}
}
}
Modified: gnucash/trunk/src/backend/sql/gnc-price-sql.c
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-price-sql.c 2009-06-14 23:08:31 UTC (rev 18133)
+++ gnucash/trunk/src/backend/sql/gnc-price-sql.c 2009-06-15 22:19:35 UTC (rev 18134)
@@ -102,15 +102,14 @@
gnc_sql_statement_dispose( stmt );
if( result != NULL ) {
GNCPrice* pPrice;
- GList* list = NULL;
GncSqlRow* row = gnc_sql_result_get_first_row( result );
+ gchar* sql;
gnc_pricedb_set_bulk_update( pPriceDB, TRUE );
while( row != NULL ) {
pPrice = load_single_price( be, row );
if( pPrice != NULL ) {
- list = g_list_append( list, pPrice );
(void)gnc_pricedb_add_price( pPriceDB, pPrice );
}
row = gnc_sql_result_get_next_row( result );
@@ -118,10 +117,9 @@
gnc_sql_result_dispose( result );
gnc_pricedb_set_bulk_update( pPriceDB, FALSE );
- if( list != NULL ) {
- gnc_sql_slots_load_for_list( be, list );
- g_list_free( list );
- }
+ sql = g_strdup_printf( "SELECT DISTINCT guid FROM %s", TABLE_NAME );
+ gnc_sql_slots_load_for_sql_subquery( be, sql, (BookLookupFn)gnc_price_lookup );
+ g_free( sql );
}
}
}
Modified: gnucash/trunk/src/backend/sql/gnc-slots-sql.c
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-slots-sql.c 2009-06-14 23:08:31 UTC (rev 18133)
+++ gnucash/trunk/src/backend/sql/gnc-slots-sql.c 2009-06-15 22:19:35 UTC (rev 18134)
@@ -557,6 +557,79 @@
}
}
+static void
+load_slot_for_book_object( GncSqlBackend* be, GncSqlRow* row, BookLookupFn lookup_fn )
+{
+ slot_info_t slot_info;
+ const GUID* guid;
+ QofInstance* inst;
+
+ g_return_if_fail( be != NULL );
+ g_return_if_fail( row != NULL );
+ g_return_if_fail( lookup_fn != NULL );
+
+ guid = load_obj_guid( be, row );
+ g_return_if_fail( guid != NULL );
+ inst = lookup_fn( guid, be->primary_book );
+ g_return_if_fail( inst != NULL );
+
+ slot_info.be = be;
+ slot_info.pKvpFrame = qof_instance_get_slots( inst );
+ slot_info.path = NULL;
+
+ gnc_sql_load_object( be, row, TABLE_NAME, &slot_info, col_table );
+
+ if( slot_info.path != NULL ) {
+ (void)g_string_free( slot_info.path, TRUE );
+ }
+}
+
+/**
+ * gnc_sql_slots_load_for_sql_subquery - Loads slots for all objects whose guid is
+ * supplied by a subquery. The subquery should be of the form "SELECT DISTINCT guid FROM ...".
+ * This is faster than loading for one object at a time because fewer SQL queries * are used.
+ *
+ * @param be SQL backend
+ * @param subquery Subquery SQL string
+ * @param lookup_fn Lookup function
+ */
+void gnc_sql_slots_load_for_sql_subquery( GncSqlBackend* be, const gchar* subquery,
+ BookLookupFn lookup_fn )
+{
+ gchar* sql;
+ GncSqlStatement* stmt;
+ GncSqlResult* result;
+
+ g_return_if_fail( be != NULL );
+
+ // Ignore empty subquery
+ if( subquery == NULL ) return;
+
+ sql = g_strdup_printf( "SELECT * FROM %s WHERE %s IN (%s)",
+ TABLE_NAME, obj_guid_col_table[0].col_name,
+ subquery );
+
+ // Execute the query and load the slots
+ stmt = gnc_sql_create_statement_from_sql( be, sql );
+ if( stmt == NULL ) {
+ PERR( "stmt == NULL, SQL = '%s'\n", sql );
+ g_free( sql );
+ return;
+ }
+ g_free( sql );
+ result = gnc_sql_execute_select_statement( be, stmt );
+ gnc_sql_statement_dispose( stmt );
+ if( result != NULL ) {
+ GncSqlRow* row = gnc_sql_result_get_first_row( result );
+
+ while( row != NULL ) {
+ load_slot_for_book_object( be, row, lookup_fn );
+ row = gnc_sql_result_get_next_row( result );
+ }
+ gnc_sql_result_dispose( result );
+ }
+}
+
/* ================================================================= */
static void
create_slots_tables( GncSqlBackend* be )
Modified: gnucash/trunk/src/backend/sql/gnc-slots-sql.h
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-slots-sql.h 2009-06-14 23:08:31 UTC (rev 18133)
+++ gnucash/trunk/src/backend/sql/gnc-slots-sql.h 2009-06-15 22:19:35 UTC (rev 18134)
@@ -45,32 +45,46 @@
gboolean is_infant, KvpFrame* pFrame );
/**
-* gnc_sql_slots_delete - Deletes slots for an object from the db.
-*
-* @param be SQL backend
-* @param guid Object guid
+ * gnc_sql_slots_delete - Deletes slots for an object from the db.
+ *
+ * @param be SQL backend
+ * @param guid Object guid
* @return TRUE if successful, FALSE if error
-*/
+ */
gboolean gnc_sql_slots_delete( GncSqlBackend* be, const GUID* guid );
/**
-* gnc_sql_slots_load - Loads slots for an object from the db.
-*
-* @param be SQL backend
-* @param guid Object guid
-*/
+ * gnc_sql_slots_load - Loads slots for an object from the db.
+ *
+ * @param be SQL backend
+ * @param guid Object guid
+ */
void gnc_sql_slots_load( GncSqlBackend* be, QofInstance* inst );
/**
-* gnc_sql_slots_load_for_list - Loads slots for a list of objects from the db.
-* Loading slots for a list of objects can be faster than loading for one object
-* at a time because fewer SQL queries are used.
-*
-* @param be SQL backend
-* @param list List of objects
-*/
+ * gnc_sql_slots_load_for_list - Loads slots for a list of objects from the db.
+ * Loading slots for a list of objects can be faster than loading for one object
+ * at a time because fewer SQL queries are used.
+ *
+ * @param be SQL backend
+ * @param list List of objects
+ */
void gnc_sql_slots_load_for_list( GncSqlBackend* be, GList* list );
+/**
+ * gnc_sql_slots_load_for_sql_subquery - Loads slots for all objects whose guid is
+ * supplied by a subquery. The subquery should be of the form "SELECT DISTINCT guid FROM ...".
+ * This is faster than loading for one object at a time because fewer SQL queries * are used.
+ *
+ * @param be SQL backend
+ * @param subquery Subquery SQL string
+ * @param lookup_fn Lookup function to get the right object from the book
+ */
+typedef QofInstance* (*BookLookupFn)( const GUID* guid, const QofBook* book );
+
+void gnc_sql_slots_load_for_sql_subquery( GncSqlBackend* be, const gchar* subquery,
+ BookLookupFn lookup_fn );
+
void gnc_sql_init_slots_handler( void );
#endif /* GNC_SLOTS_SQL_H_ */
More information about the gnucash-changes
mailing list