gnc-transaction-sql.c File Reference

load and save data to SQL More...

#include "config.h"
#include <glib/gi18n.h>
#include "qof.h"
#include "qofquery-p.h"
#include "qofquerycore-p.h"
#include "Account.h"
#include "Transaction.h"
#include "gnc-lot.h"
#include "engine-helpers.h"
#include "gnc-backend-sql.h"
#include "gnc-transaction-sql.h"
#include "gnc-commodity.h"
#include "gnc-commodity-sql.h"
#include "gnc-slots-sql.h"
#include "gnc-engine.h"
#include "escape.h"

Go to the source code of this file.

Data Structures
struct	split_info_t
struct	full_acct_balances_t
struct	split_query_info_t
struct	single_acct_balance_t

Macros
#define	SIMPLE_QUERY_COMPILATION   1
#define	LOAD_TRANSACTIONS_AS_NEEDED   0
#define	TRANSACTION_TABLE   "transactions"
#define	TX_TABLE_VERSION   3
#define	SPLIT_TABLE   "splits"
#define	SPLIT_TABLE_VERSION   4
#define	TX_MAX_NUM_LEN   2048
#define	TX_MAX_DESCRIPTION_LEN   2048
#define	SPLIT_MAX_MEMO_LEN   2048
#define	SPLIT_MAX_ACTION_LEN   2048
#define	TX_GUID_CHECK   0

Functions
gboolean	gnc_sql_save_transaction (GncSqlBackend *be, QofInstance *inst)
void	gnc_sql_transaction_load_tx_for_account (GncSqlBackend *be, Account *account)
void	gnc_sql_transaction_load_all_tx (GncSqlBackend *be)
GSList *	gnc_sql_get_account_balances_slist (GncSqlBackend *be)
void	gnc_sql_init_transaction_handler (void)

Detailed Description

load and save data to SQL

Author

Copyright (c) 2006-2008 Phil Longstaff plong.nosp@m.staf.nosp@m.f@rog.nosp@m.ers..nosp@m.com

This file implements the top-level QofBackend API for saving/ restoring data to/from an SQL db

Definition in file gnc-transaction-sql.c.

Function Documentation

GSList* gnc_sql_get_account_balances_slist

(

GncSqlBackend *

be

)

Returns a list of acct_balances_t structures, one for each account which has splits.

Parameters

be	SQL backend

Returns

GSList of acct_balances_t structures

Definition at line 1339 of file gnc-transaction-sql.c.

{
#if LOAD_TRANSACTIONS_AS_NEEDED
    GncSqlResult* result;
    GncSqlStatement* stmt;
    gchar* buf;
    GSList* bal_slist = NULL;

    g_return_val_if_fail( be != NULL, NULL );

    buf = g_strdup_printf( "SELECT account_guid, reconcile_state, sum(quantity_num) as quantity_num, quantity_denom FROM %s GROUP BY account_guid, reconcile_state, quantity_denom ORDER BY account_guid, reconcile_state",
                           SPLIT_TABLE );
    stmt = gnc_sql_create_statement_from_sql( be, buf );
    g_assert( stmt != NULL );
    g_free( buf );
    result = gnc_sql_execute_select_statement( be, stmt );
    gnc_sql_statement_dispose( stmt );
    if ( result != NULL )
    {
        GncSqlRow* row;
        acct_balances_t* bal = NULL;

        row = gnc_sql_result_get_first_row( result );
        while ( row != NULL )
        {
            single_acct_balance_t* single_bal;

            // Get the next reconcile state balance and merge with other balances
            single_bal = load_single_acct_balances( be, row );
            if ( single_bal != NULL )
            {
                if ( bal != NULL && bal->acct != single_bal->acct )
                {
                    bal->cleared_balance = gnc_numeric_add( bal->cleared_balance, bal->reconciled_balance,
                                                            GNC_DENOM_AUTO, GNC_HOW_DENOM_LCD );
                    bal->balance = gnc_numeric_add( bal->balance, bal->cleared_balance,
                                                    GNC_DENOM_AUTO, GNC_HOW_DENOM_LCD );
                    bal_slist = g_slist_append( bal_slist, bal );
                    bal = NULL;
                }
                if ( bal == NULL )
                {
                    bal = g_malloc( (gsize)sizeof(acct_balances_t) );
                    g_assert( bal != NULL );

                    bal->acct = single_bal->acct;
                    bal->balance = gnc_numeric_zero();
                    bal->cleared_balance = gnc_numeric_zero();
                    bal->reconciled_balance = gnc_numeric_zero();
                }
                if ( single_bal->reconcile_state == 'n' )
                {
                    bal->balance = gnc_numeric_add( bal->balance, single_bal->balance,
                                                    GNC_DENOM_AUTO, GNC_HOW_DENOM_LCD );
                }
                else if ( single_bal->reconcile_state == 'c' )
                {
                    bal->cleared_balance = gnc_numeric_add( bal->cleared_balance, single_bal->balance,
                                                            GNC_DENOM_AUTO, GNC_HOW_DENOM_LCD );
                }
                else if ( single_bal->reconcile_state == 'y' )
                {
                    bal->reconciled_balance = gnc_numeric_add( bal->reconciled_balance, single_bal->balance,
                                              GNC_DENOM_AUTO, GNC_HOW_DENOM_LCD );
                }
                g_free( single_bal );
            }
            row = gnc_sql_result_get_next_row( result );
        }

        // Add the final balance
        if ( bal != NULL )
        {
            bal->cleared_balance = gnc_numeric_add( bal->cleared_balance, bal->reconciled_balance,
                                                    GNC_DENOM_AUTO, GNC_HOW_DENOM_LCD );
            bal->balance = gnc_numeric_add( bal->balance, bal->cleared_balance,
                                            GNC_DENOM_AUTO, GNC_HOW_DENOM_LCD );
            bal_slist = g_slist_append( bal_slist, bal );
        }
        gnc_sql_result_dispose( result );
    }

    return bal_slist;
#else
    return NULL;
#endif
}

gboolean gnc_sql_save_transaction	(	GncSqlBackend *	be,
		QofInstance *	inst
	)

Saves a transaction to the db.

Parameters

be	SQL backend
inst	Transaction instance

Returns

TRUE if successful, FALSE if unsuccessful

Definition at line 783 of file gnc-transaction-sql.c.

{
    g_return_val_if_fail( be != NULL, FALSE );
    g_return_val_if_fail( inst != NULL, FALSE );
    g_return_val_if_fail( GNC_IS_TRANS(inst), FALSE );

    return save_transaction( be, GNC_TRANS(inst), /* do_save_splits */TRUE );
}

void gnc_sql_transaction_load_all_tx

(

GncSqlBackend *

be

)

Loads all transactions. This might be used during a save-as operation to ensure that all data is in memory and ready to be saved.

Parameters

be	SQL backend

Definition at line 839 of file gnc-transaction-sql.c.

{
    gchar* query_sql;
    GncSqlStatement* stmt;

    g_return_if_fail( be != NULL );

    query_sql = g_strdup_printf( "SELECT * FROM %s", TRANSACTION_TABLE );
    stmt = gnc_sql_create_statement_from_sql( be, query_sql );
    g_free( query_sql );
    if ( stmt != NULL )
    {
        query_transactions( be, stmt );
        gnc_sql_statement_dispose( stmt );
    }
}

void gnc_sql_transaction_load_tx_for_account	(	GncSqlBackend *	be,
		Account *	account
	)

Loads all transactions for an account.

Parameters

be	SQL backend
account	Account

Definition at line 809 of file gnc-transaction-sql.c.

{
    const GncGUID* guid;
    gchar guid_buf[GUID_ENCODING_LENGTH+1];
    gchar* query_sql;
    GncSqlStatement* stmt;

    g_return_if_fail( be != NULL );
    g_return_if_fail( account != NULL );

    guid = qof_instance_get_guid( QOF_INSTANCE(account) );
    (void)guid_to_string_buff( guid, guid_buf );
    query_sql = g_strdup_printf(
                    "SELECT DISTINCT t.* FROM %s AS t, %s AS s WHERE s.tx_guid=t.guid AND s.account_guid ='%s'",
                    TRANSACTION_TABLE, SPLIT_TABLE, guid_buf );
    stmt = gnc_sql_create_statement_from_sql( be, query_sql );
    g_free( query_sql );
    if ( stmt != NULL )
    {
        query_transactions( be, stmt );
        gnc_sql_statement_dispose( stmt );
    }
}