r18250 - gnucash/trunk/src/backend/sql - Add budget amounts table. This table contains 1 record per budget per account per period which
Phil Longstaff
plongstaff at code.gnucash.org
Sat Aug 15 15:02:27 EDT 2009
Author: plongstaff
Date: 2009-08-15 15:02:27 -0400 (Sat, 15 Aug 2009)
New Revision: 18250
Trac: http://svn.gnucash.org/trac/changeset/18250
Modified:
gnucash/trunk/src/backend/sql/gnc-backend-sql.h
gnucash/trunk/src/backend/sql/gnc-budget-sql.c
Log:
Add budget amounts table. This table contains 1 record per budget per account per period which
has an amount set.
Modified: gnucash/trunk/src/backend/sql/gnc-backend-sql.h
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-backend-sql.h 2009-08-15 08:56:19 UTC (rev 18249)
+++ gnucash/trunk/src/backend/sql/gnc-backend-sql.h 2009-08-15 19:02:27 UTC (rev 18250)
@@ -310,9 +310,10 @@
#define CT_DOUBLE "ct_double"
#define CT_BOOLEAN "ct_boolean"
#define CT_ACCOUNTREF "ct_accountref"
+#define CT_BUDGETREF "ct_budgetref"
#define CT_COMMODITYREF "ct_commodityref"
+#define CT_LOTREF "ct_lotref"
#define CT_TXREF "ct_txref"
-#define CT_LOTREF "ct_lotref"
/**
* @struct GncSqlColumnTableEntry
Modified: gnucash/trunk/src/backend/sql/gnc-budget-sql.c
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-budget-sql.c 2009-08-15 08:56:19 UTC (rev 18249)
+++ gnucash/trunk/src/backend/sql/gnc-budget-sql.c 2009-08-15 19:02:27 UTC (rev 18250)
@@ -48,6 +48,8 @@
#define BUDGET_TABLE "budgets"
#define TABLE_VERSION 1
+#define AMOUNTS_TABLE "budget_amounts"
+#define AMOUNTS_TABLE_VERSION 1
/*@ unused @*/ static QofLogModule log_module = G_LOG_DOMAIN;
@@ -65,7 +67,213 @@
/*@ +full_init_block @*/
};
+static /*@ dependent @*//*@ null @*/ QofInstance* get_budget( gpointer pObj );
+static /*@ dependent @*//*@ null @*/ QofInstance* get_account( gpointer pObj );
+static void set_account( gpointer pObj, gpointer val );
+static gint get_period_num( gpointer pObj );
+static void set_period_num( gpointer pObj, gpointer val );
+static gnc_numeric get_amount( gpointer pObj );
+static void set_amount( gpointer pObj, gnc_numeric value );
+
+typedef struct {
+ GncBudget* budget;
+ Account* account;
+ guint period_num;
+} budget_amount_info_t;
+
+static const GncSqlColumnTableEntry budget_amounts_col_table[] =
+{
+ /*@ -full_init_block @*/
+ { "id", CT_INT, 0, COL_NNUL|COL_PKEY|COL_AUTOINC },
+ { "budget_guid", CT_BUDGETREF, 0, COL_NNUL, NULL, NULL,
+ (QofAccessFunc)get_budget },
+ { "account_guid", CT_ACCOUNTREF, 0, COL_NNUL, NULL, NULL,
+ (QofAccessFunc)get_account, (QofSetterFunc)set_account },
+ { "period_num", CT_INT, 0, COL_NNUL, NULL, NULL,
+ (QofAccessFunc)get_period_num, (QofSetterFunc)set_period_num },
+ { "amount", CT_NUMERIC, 0, COL_NNUL, NULL, NULL,
+ (QofAccessFunc)get_amount, (QofSetterFunc)set_amount },
+ { NULL }
+ /*@ +full_init_block @*/
+};
+
/* ================================================================= */
+static /*@ dependent @*//*@ null@ */ QofInstance*
+get_budget( gpointer pObj )
+{
+ budget_amount_info_t* info = (budget_amount_info_t*)pObj;
+
+ g_return_val_if_fail( pObj != NULL, NULL );
+
+ return QOF_INSTANCE(info->budget);
+}
+
+static /*@ dependent @*//*@ null @*/ QofInstance*
+get_account( gpointer pObj )
+{
+ budget_amount_info_t* info = (budget_amount_info_t*)pObj;
+
+ g_return_val_if_fail( pObj != NULL, NULL );
+
+ return QOF_INSTANCE(info->account);
+}
+
+static void
+set_account( gpointer pObj, gpointer val )
+{
+ budget_amount_info_t* info = (budget_amount_info_t*)pObj;
+
+ g_return_if_fail( pObj != NULL );
+ g_return_if_fail( val != NULL );
+ g_return_if_fail( GNC_IS_ACCOUNT(val) );
+
+ info->account = GNC_ACCOUNT(val);
+}
+
+static gint
+get_period_num( gpointer pObj )
+{
+ budget_amount_info_t* info = (budget_amount_info_t*)pObj;
+
+ g_return_val_if_fail( pObj != NULL, 0 );
+
+ return info->period_num;
+}
+
+static void
+set_period_num( gpointer pObj, gpointer val )
+{
+ budget_amount_info_t* info = (budget_amount_info_t*)pObj;
+
+ g_return_if_fail( pObj != NULL );
+
+ info->period_num = (guint)val;
+}
+
+static gnc_numeric
+get_amount( gpointer pObj )
+{
+ budget_amount_info_t* info = (budget_amount_info_t*)pObj;
+
+ g_return_val_if_fail( pObj != NULL, gnc_numeric_zero() );
+
+ return gnc_budget_get_account_period_value( info->budget, info->account, info->period_num );
+}
+
+static void
+set_amount( gpointer pObj, gnc_numeric value )
+{
+ budget_amount_info_t* info = (budget_amount_info_t*)pObj;
+
+ g_return_if_fail( pObj != NULL );
+
+ return gnc_budget_set_account_period_value( info->budget, info->account, info->period_num, value );
+}
+
+/*----------------------------------------------------------------*/
+/**
+ * Loads the budget amounts for a budget.
+ *
+ * @param be SQL backend
+ * @param budget Budget
+ */
+static void
+load_budget_amounts( GncSqlBackend* be, GncBudget* budget )
+{
+ gchar guid_buf[GUID_ENCODING_LENGTH+1];
+ gchar* sql;
+ GncSqlStatement* stmt;
+ GncSqlResult* result;
+
+ g_return_if_fail( be != NULL );
+ g_return_if_fail( budget != NULL );
+
+ (void)guid_to_string_buff( qof_instance_get_guid( QOF_INSTANCE(budget) ), guid_buf );
+ sql = g_strdup_printf( "SELECT * FROM %s WHERE budget_guid='%s'", AMOUNTS_TABLE, guid_buf );
+ stmt = gnc_sql_create_statement_from_sql( be, sql );
+ g_free( sql );
+ if( stmt != NULL ) {
+ 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 );
+ budget_amount_info_t info;
+
+ info.budget = budget;
+
+ while( row != NULL ) {
+ gnc_sql_load_object( be, row, NULL, &info, budget_amounts_col_table );
+ row = gnc_sql_result_get_next_row( result );
+ }
+ gnc_sql_result_dispose( result );
+ }
+ }
+}
+
+/**
+ * Deletes the budget amounts for a budget.
+ *
+ * @param be SQL backend
+ * @param budget Budget
+ */
+static gboolean
+delete_budget_amounts( GncSqlBackend* be, GncBudget* budget )
+{
+ gchar guid_buf[GUID_ENCODING_LENGTH+1];
+ gchar* sql;
+
+ g_return_val_if_fail( be != NULL, FALSE );
+ g_return_val_if_fail( budget != NULL, FALSE );
+
+ (void)guid_to_string_buff( qof_instance_get_guid( QOF_INSTANCE(budget) ), guid_buf );
+ sql = g_strdup_printf( "DELETE FROM %s WHERE budget_guid='%s'", AMOUNTS_TABLE, guid_buf );
+ (void)gnc_sql_execute_nonselect_sql( be, sql );
+ g_free( sql );
+
+ return TRUE;
+}
+
+/**
+ * Saves the budget amounts for a budget.
+ *
+ * @param be SQL backend
+ * @param budget Budget
+ */
+static gboolean
+save_budget_amounts( GncSqlBackend* be, GncBudget* budget )
+{
+ GList* descendants;
+ /*@ dependent @*/ GList* node;
+ budget_amount_info_t info;
+ guint num_periods;
+ gboolean is_ok = TRUE;;
+
+ g_return_val_if_fail( be != NULL, FALSE );
+ g_return_val_if_fail( budget != NULL, FALSE );
+
+ // Delete the amounts, then save
+ delete_budget_amounts( be, budget );
+
+ info.budget = budget;
+ num_periods = gnc_budget_get_num_periods( budget );
+ descendants = gnc_account_get_descendants( gnc_book_get_root_account( be->primary_book ) );
+ for( node = descendants; node != NULL && is_ok; node = g_list_next(node) ) {
+ guint i;
+
+ info.account = GNC_ACCOUNT(node->data);
+ for( i = 0; i < num_periods && is_ok; i++ ) {
+ if( gnc_budget_is_account_period_value_set( budget, info.account, i ) ) {
+ info.period_num = i;
+ is_ok = gnc_sql_do_db_operation( be, OP_DB_INSERT, AMOUNTS_TABLE, "", &info,
+ budget_amounts_col_table );
+ }
+ }
+ }
+ g_list_free( descendants );
+
+ return is_ok;
+}
+/*----------------------------------------------------------------*/
static /*@ dependent @*//*@ null @*/ GncBudget*
load_single_budget( GncSqlBackend* be, GncSqlRow* row )
{
@@ -86,6 +294,7 @@
gnc_budget_begin_edit( pBudget );
gnc_sql_load_object( be, row, GNC_ID_BUDGET, pBudget, col_table );
+ load_budget_amounts( be, pBudget );
r = gnc_sql_recurrence_load( be, gnc_budget_get_guid( pBudget ) );
if( r != NULL ) {
gnc_budget_set_recurrence( pBudget, r );
@@ -142,6 +351,11 @@
if( version == 0 ) {
(void)gnc_sql_create_table( be, BUDGET_TABLE, TABLE_VERSION, col_table );
}
+
+ version = gnc_sql_get_table_version( be, AMOUNTS_TABLE );
+ if( version == 0 ) {
+ (void)gnc_sql_create_table( be, AMOUNTS_TABLE, AMOUNTS_TABLE_VERSION, budget_amounts_col_table );
+ }
}
/* ================================================================= */
@@ -172,13 +386,19 @@
if( is_ok ) {
guid = qof_instance_get_guid( inst );
if( !qof_instance_get_destroying(inst) ) {
- is_ok = gnc_sql_recurrence_save( be, guid, gnc_budget_get_recurrence( pBudget ) );
+ is_ok = save_budget_amounts( be, pBudget );
if( is_ok ) {
+ is_ok = gnc_sql_recurrence_save( be, guid, gnc_budget_get_recurrence( pBudget ) );
+ }
+ if( is_ok ) {
is_ok = gnc_sql_slots_save( be, guid, is_infant, qof_instance_get_slots( inst ) );
}
} else {
- is_ok = gnc_sql_recurrence_delete( be, guid );
+ is_ok = delete_budget_amounts( be, pBudget );
if( is_ok ) {
+ is_ok = gnc_sql_recurrence_delete( be, guid );
+ }
+ if( is_ok ) {
(void)gnc_sql_slots_delete( be, guid );
}
}
@@ -213,6 +433,47 @@
}
/* ================================================================= */
+static void
+load_budget_guid( const GncSqlBackend* be, GncSqlRow* row,
+ /*@ null @*/ QofSetterFunc setter, gpointer pObject,
+ const GncSqlColumnTableEntry* table_row )
+{
+ const GValue* val;
+ GUID guid;
+ const GUID* pGuid;
+ GncBudget* budget = NULL;
+
+ g_return_if_fail( be != NULL );
+ g_return_if_fail( row != NULL );
+ g_return_if_fail( pObject != NULL );
+ g_return_if_fail( table_row != NULL );
+
+ val = gnc_sql_row_get_value_at_col_name( row, table_row->col_name );
+ if( val == NULL ) {
+ pGuid = NULL;
+ } else {
+ (void)string_to_guid( g_value_get_string( val ), &guid );
+ pGuid = &guid;
+ }
+ if( pGuid != NULL ) {
+ budget = gnc_budget_lookup( pGuid, be->primary_book );
+ }
+ if( budget != NULL ) {
+ if( table_row->gobj_param_name != NULL ) {
+ g_object_set( pObject, table_row->gobj_param_name, budget, NULL );
+ } else {
+ g_return_if_fail( setter != NULL );
+ (*setter)( pObject, (const gpointer)budget );
+ }
+ }
+}
+
+static GncSqlColumnTypeHandler budget_guid_handler
+ = { load_budget_guid,
+ gnc_sql_add_objectref_guid_col_info_to_list,
+ gnc_sql_add_colname_to_list,
+ gnc_sql_add_gvalue_objectref_guid_to_slist };
+/* ================================================================= */
void
gnc_sql_init_budget_handler( void )
{
@@ -230,5 +491,7 @@
};
(void)qof_object_register_backend( GNC_ID_BUDGET, GNC_SQL_BACKEND, &be_data );
+
+ gnc_sql_register_col_type_handler( CT_BUDGETREF, &budget_guid_handler );
}
/* ========================== END OF FILE ===================== */
More information about the gnucash-changes
mailing list