r16534 - gnucash/branches/gda-dev/src/backend/gda - 1) When loading an account, query splits and set
Phil Longstaff
plongstaff at cvs.gnucash.org
Sat Sep 15 21:28:55 EDT 2007
Author: plongstaff
Date: 2007-09-15 21:28:54 -0400 (Sat, 15 Sep 2007)
New Revision: 16534
Trac: http://svn.gnucash.org/trac/changeset/16534
Modified:
gnucash/branches/gda-dev/src/backend/gda/gnc-account-gda.c
gnucash/branches/gda-dev/src/backend/gda/gnc-backend-gda.c
gnucash/branches/gda-dev/src/backend/gda/gnc-backend-util-gda.c
gnucash/branches/gda-dev/src/backend/gda/gnc-backend-util-gda.h
gnucash/branches/gda-dev/src/backend/gda/gnc-transaction-gda.c
gnucash/branches/gda-dev/src/backend/gda/gnc-transaction-gda.h
Log:
1) When loading an account, query splits and set
starting/cleared/reconciled balances. Because of a Gda bug, I can't
just use SUM(quantity_num) so the backend adds all of the quantities.
2) Expand the set of allowable URLs. The format is now:
- gda://@xxxxxxx where xxxxxxx is a connection configured
in the libgda config db
- gda://sqlite:file opens file as an sqlite file
- gda://prov:dsn where prov is a provider name built into
libgda and dsn is a valid connection
string for that provider
3) MySQL was hanging on "select * from transactions where guid in
(select tx_guid from splits where ...) so this is now split into 2
separate queries.
Modified: gnucash/branches/gda-dev/src/backend/gda/gnc-account-gda.c
===================================================================
--- gnucash/branches/gda-dev/src/backend/gda/gnc-account-gda.c 2007-09-15 08:29:14 UTC (rev 16533)
+++ gnucash/branches/gda-dev/src/backend/gda/gnc-account-gda.c 2007-09-16 01:28:54 UTC (rev 16534)
@@ -40,6 +40,7 @@
#include "gnc-account-gda.h"
#include "gnc-commodity-gda.h"
#include "gnc-slots-gda.h"
+#include "gnc-transaction-gda.h"
static QofLogModule log_module = GNC_MOD_BACKEND;
@@ -124,6 +125,20 @@
}
}
+static void
+load_balances( GncGdaBackend* be, Account* pAccount )
+{
+ gnc_numeric start_balance, cleared_balance, reconciled_balance;
+
+ gnc_gda_get_account_balances( be, pAccount, &start_balance, &cleared_balance, &reconciled_balance );
+
+ g_object_set( pAccount,
+ "start-balance", &start_balance,
+ "start-cleared-balance", &cleared_balance,
+ "start-reconciled-balance", &reconciled_balance,
+ NULL);
+}
+
static Account*
load_single_account( GncGdaBackend* be, GdaDataModel* pModel, int row,
Account* pAccount )
@@ -143,6 +158,7 @@
gnc_gda_load_object( pModel, row, GNC_ID_ACCOUNT, pAccount, col_table );
gnc_gda_slots_load( be, xaccAccountGetGUID( pAccount ),
qof_instance_get_slots( QOF_INSTANCE(pAccount) ) );
+ load_balances( be, pAccount );
qof_instance_mark_clean( QOF_INSTANCE(pAccount) );
Modified: gnucash/branches/gda-dev/src/backend/gda/gnc-backend-gda.c
===================================================================
--- gnucash/branches/gda-dev/src/backend/gda/gnc-backend-gda.c 2007-09-15 08:29:14 UTC (rev 16533)
+++ gnucash/branches/gda-dev/src/backend/gda/gnc-backend-gda.c 2007-09-16 01:28:54 UTC (rev 16534)
@@ -97,15 +97,16 @@
static void
gnc_gda_session_begin(QofBackend *be_start, QofSession *session,
const gchar *book_id,
- gboolean ignore_lock, gboolean create_if_nonexistent)
+ gboolean ignore_lock,
+ gboolean create_if_nonexistent)
{
GncGdaBackend *be = (GncGdaBackend*) be_start;
GError* error = NULL;
gda_backend be_data;
gchar* book_info;
gchar* dsn;
- gchar* username;
- gchar* password;
+ gchar* username = "";
+ gchar* password = "";
ENTER (" ");
@@ -115,25 +116,100 @@
book_info = g_strdup( book_id );
dsn = strchr( book_info, ':' );
*dsn = '\0';
- dsn += 3;
- username = strchr( dsn, ':' );
- if( username != NULL ) {
- *username++ = '\0';
- } else {
- username = "";
- }
- password = strchr( username, ':' );
- if( password != NULL ) {
- *password++ = '\0';
- } else {
- password = "";
- }
+ dsn += 3; // Skip '://'
- be->pConnection = gda_client_open_connection( be->pClient,
+ // String will be one of:
+ //
+ // sqlite:<filename>
+ // mysql:<dbname>
+ // pgsql:<dbname>
+ // @<gda_connectionname>
+
+ if( dsn[0] == '@' ) {
+ dsn++;
+
+ be->pConnection = gda_client_open_connection( be->pClient,
dsn,
username, password,
0,
&error );
+ } else {
+ gchar* provider;
+ GList* provider_list;
+ GList* l;
+ gboolean provider_found;
+
+ provider = dsn;
+ dsn = strchr( dsn, ':' );
+ *dsn = '\0';
+ dsn++;
+
+ // Get a list of all of the providers. If the requested provider is on the list, use it.
+ // Note that we need a case insensitive comparison here
+ provider_list = gda_config_get_provider_list();
+
+ provider_found = FALSE;
+ for( l = provider_list; l != NULL; l = l->next ) {
+ GdaProviderInfo* provider_info = (GdaProviderInfo*)l->data;
+
+ if( provider_info != NULL && g_ascii_strcasecmp( provider_info->id, provider ) == 0 ) {
+ provider_found = TRUE;
+ provider = provider_info->id;
+ break;
+ }
+ }
+
+ if( provider_found ) {
+ gchar* cnc;
+
+ // If the provider is SQLite, split the file name into DB_DIR and
+ // DB_NAME
+ if( strcmp( provider, "SQLite" ) == 0 ) {
+ gchar* last_slash = g_strrstr( dsn, "/" );
+ if( last_slash != NULL ) {
+ *last_slash = '\0';
+ last_slash++;
+ cnc = g_strdup_printf( "DB_DIR=%s;DB_NAME=%s",
+ dsn, last_slash );
+ } else {
+ cnc = g_strdup_printf( "DB_DIR=.;DB_NAME=%s", dsn );
+ }
+ } else {
+ cnc = g_strdup( dsn );
+ }
+
+ be->pConnection = gda_client_open_connection_from_string( be->pClient,
+ provider,
+ cnc,
+ username, password,
+ 0,
+ &error );
+
+ if( be->pConnection == NULL ) {
+ GdaServerOperation* op = gda_client_prepare_create_database(
+ be->pClient,
+ dsn,
+ provider );
+ if( op != NULL ) {
+ gboolean isOK;
+ isOK = gda_client_perform_create_database(
+ be->pClient,
+ op,
+ &error );
+ if( isOK ) {
+ be->pConnection = gda_client_open_connection_from_string(
+ be->pClient,
+ provider,
+ cnc,
+ username, password,
+ 0,
+ &error );
+ }
+ }
+ }
+ g_free( cnc );
+ }
+ }
g_free( book_info );
if( be->pConnection == NULL ) {
Modified: gnucash/branches/gda-dev/src/backend/gda/gnc-backend-util-gda.c
===================================================================
--- gnucash/branches/gda-dev/src/backend/gda/gnc-backend-util-gda.c 2007-09-15 08:29:14 UTC (rev 16533)
+++ gnucash/branches/gda-dev/src/backend/gda/gnc-backend-util-gda.c 2007-09-16 01:28:54 UTC (rev 16534)
@@ -847,10 +847,10 @@
static void
retrieve_guid( gpointer pObject, gpointer pValue )
{
- GUID** ppGuid = (GUID**)pObject;
+ GUID* pGuid = (GUID*)pObject;
GUID* guid = (GUID*)pValue;
- *ppGuid = guid;
+ memcpy( pGuid, guid, sizeof( GUID ) );
}
@@ -864,13 +864,30 @@
const GUID*
gnc_gda_load_guid( GdaDataModel* pModel, gint row )
{
- const GUID* guid;
+ static GUID guid;
gnc_gda_load_object( pModel, row, NULL, &guid, guid_table );
- return guid;
+ return &guid;
}
+// Table to retrieve just the guid
+static col_cvt_t tx_guid_table[] =
+{
+ { "tx_guid", CT_GUID, 0, 0, NULL, NULL, NULL, retrieve_guid },
+ { NULL }
+};
+
+const GUID*
+gnc_gda_load_tx_guid( GdaDataModel* pModel, gint row )
+{
+ static GUID guid;
+
+ gnc_gda_load_object( pModel, row, NULL, &guid, tx_guid_table );
+
+ return &guid;
+}
+
void
gnc_gda_load_object( GdaDataModel* pModel, gint row,
QofIdTypeConst obj_name, gpointer pObject,
@@ -935,8 +952,8 @@
return ret;
}
-GdaObject*
-gnc_gda_execute_sql( GncGdaBackend* be, const gchar* sql )
+GdaQuery*
+gnc_gda_create_query_from_sql( GncGdaBackend* be, const gchar* sql )
{
GError* error = NULL;
@@ -945,11 +962,24 @@
query = gda_query_new_from_sql( be->pDict, sql, &error );
if( query == NULL ) {
g_critical( "SQL error: %s\n", error->message );
- return NULL;
}
- return gnc_gda_execute_query( be, query );
+
+ return query;
}
+GdaObject*
+gnc_gda_execute_sql( GncGdaBackend* be, const gchar* sql )
+{
+ GdaQuery* query;
+
+ query = gnc_gda_create_query_from_sql( be, sql );
+ if( query != NULL ) {
+ return gnc_gda_execute_query( be, query );
+ } else {
+ return NULL;
+ }
+}
+
int
gnc_gda_execute_select_get_count( GncGdaBackend* be, const gchar* sql )
{
Modified: gnucash/branches/gda-dev/src/backend/gda/gnc-backend-util-gda.h
===================================================================
--- gnucash/branches/gda-dev/src/backend/gda/gnc-backend-util-gda.h 2007-09-15 08:29:14 UTC (rev 16533)
+++ gnucash/branches/gda-dev/src/backend/gda/gnc-backend-util-gda.h 2007-09-16 01:28:54 UTC (rev 16534)
@@ -130,6 +130,7 @@
const col_cvt_t* table );
GdaObject* gnc_gda_execute_query( GncGdaBackend* pBackend, GdaQuery* pQuery );
GdaObject* gnc_gda_execute_sql( GncGdaBackend* pBackend, const gchar* sql );
+GdaQuery* gnc_gda_create_query_from_sql( GncGdaBackend* pBackend, const gchar* sql );
int gnc_gda_execute_select_get_count( GncGdaBackend* pBackend, const gchar* sql );
int gnc_gda_execute_query_get_count( GncGdaBackend* pBackend, GdaQuery* query );
void gnc_gda_load_object( GdaDataModel* pModel, int row,
@@ -145,6 +146,7 @@
void gnc_gda_create_table_if_needed( GncGdaBackend* be,
const gchar* table_name, col_cvt_t* col_table );
const GUID* gnc_gda_load_guid( GdaDataModel* pModel, int row );
+const GUID* gnc_gda_load_tx_guid( GdaDataModel* pModel, int row );
GdaQuery* gnc_gda_create_select_query( const GncGdaBackend* be, const gchar* table_name );
GdaQueryCondition* gnc_gda_create_condition_from_field( GdaQuery* query,
const gchar* col_name,
Modified: gnucash/branches/gda-dev/src/backend/gda/gnc-transaction-gda.c
===================================================================
--- gnucash/branches/gda-dev/src/backend/gda/gnc-transaction-gda.c 2007-09-15 08:29:14 UTC (rev 16533)
+++ gnucash/branches/gda-dev/src/backend/gda/gnc-transaction-gda.c 2007-09-16 01:28:54 UTC (rev 16534)
@@ -106,21 +106,21 @@
static col_cvt_t split_col_table[] =
{
- { "guid", CT_GUID, 0, COL_NNUL|COL_PKEY, NULL, NULL,
+ { "guid", CT_GUID, 0, COL_NNUL|COL_PKEY, NULL, NULL,
get_guid, set_guid },
{ "tx_guid", CT_GUID, 0, COL_NNUL, NULL, NULL,
get_split_tx_guid, set_split_tx_guid },
- { "memo", CT_STRING, SPLIT_MAX_MEMO_LEN, COL_NNUL, NULL, SPLIT_MEMO },
- { "action", CT_STRING, SPLIT_MAX_ACTION_LEN, COL_NNUL, NULL, SPLIT_ACTION },
- { "reconcile_state", CT_STRING, 1, COL_NNUL, NULL, NULL,
+ { "memo", CT_STRING, SPLIT_MAX_MEMO_LEN, COL_NNUL, NULL, SPLIT_MEMO },
+ { "action", CT_STRING, SPLIT_MAX_ACTION_LEN, COL_NNUL, NULL, SPLIT_ACTION },
+ { "reconcile_state", CT_STRING, 1, COL_NNUL, NULL, NULL,
get_split_reconcile_state, set_split_reconcile_state },
- { "reconcile_date", CT_TIMESPEC, 0, COL_NNUL, NULL, NULL,
+ { "reconcile_date", CT_TIMESPEC, 0, COL_NNUL, NULL, NULL,
get_split_reconcile_date, set_split_reconcile_date },
- { "value", CT_NUMERIC, 0, COL_NNUL, NULL, NULL,
+ { "value", CT_NUMERIC, 0, COL_NNUL, NULL, NULL,
get_split_value, set_split_value },
- { "quantity", CT_NUMERIC, 0, COL_NNUL, NULL, NULL,
+ { "quantity", CT_NUMERIC, 0, COL_NNUL, NULL, NULL,
get_split_quantity, set_split_quantity },
- { "account_guid", CT_GUID, 0, COL_NNUL, NULL, NULL,
+ { "account_guid", CT_GUID, 0, COL_NNUL, NULL, NULL,
get_split_account_guid, set_split_account_guid },
{ NULL }
};
@@ -342,6 +342,130 @@
xaccSplitSetAccount( pSplit, pAccount );
}
+static void retrieve_numeric_value( gpointer pObject, gpointer pValue );
+
+static void
+retrieve_numeric_value( gpointer pObject, gpointer pValue )
+{
+ gnc_numeric* pResult = (gnc_numeric*)pObject;
+ gnc_numeric val = *(gnc_numeric*)pValue;
+
+ *pResult = val;
+}
+
+
+// Table to retrieve just the guid
+static col_cvt_t quantity_table[] =
+{
+ { "quantity", CT_NUMERIC, 0, COL_NNUL, NULL, NULL, NULL, retrieve_numeric_value },
+ { NULL }
+};
+
+static gnc_numeric
+get_gnc_numeric_from_row( GdaDataModel* model, int row )
+{
+ gnc_numeric val;
+
+ gnc_gda_load_object( model, row, NULL, &val, quantity_table );
+
+ return val;
+}
+
+/*
+ * get_account_balance_from_query
+ *
+ * Given a GDA query which should return a number of rows of gnc_numeric num/denom pairs,
+ * return the sum.
+ */
+static gnc_numeric
+get_account_balance_from_query( GncGdaBackend* be, GdaQuery* query )
+{
+ gnc_numeric bal = gnc_numeric_zero();
+ GdaObject* ret;
+
+ /* Execute the query */
+ ret = gnc_gda_execute_query( be, query );
+
+ /* Loop for all rows, convert each to a gnc_numeric and sum them */
+ if( GDA_IS_DATA_MODEL( ret ) ) {
+ GdaDataModel* pModel = GDA_DATA_MODEL(ret);
+ int numRows = gda_data_model_get_n_rows( pModel );
+ int r;
+
+ for( r = 0; r < numRows; r++ ) {
+ gnc_numeric val = get_gnc_numeric_from_row( pModel, r );
+ bal = gnc_numeric_add( bal, val, GNC_DENOM_AUTO, GNC_HOW_DENOM_LCD );
+ }
+ }
+
+ return bal;
+}
+
+void
+gnc_gda_get_account_balances( GncGdaBackend* be, Account* pAccount,
+ gnc_numeric* start_balance,
+ gnc_numeric* cleared_balance,
+ gnc_numeric* reconciled_balance )
+{
+ GdaQuery* query;
+ gchar guid_buf[GUID_ENCODING_LENGTH+1];
+ gchar* sql;
+
+ guid_to_string_buff( qof_instance_get_guid( pAccount ), guid_buf );
+
+ /*
+ * For start balance,
+ * SELECT SUM(QUANTITY_NUM),QUANTITY_DENOM FROM SPLITS
+ * WHERE ACCOUNT_GUID=<guid> GROUP BY QUANTITY_DENOM
+ *
+ * This will return one entry per denom. These can then be made into
+ * gnc_numerics and then added. With luck, there will only be one entry.
+ */
+
+ //sql = g_strdup_printf( "SELECT SUM(QUANTITY_NUM),QUANTITY_DENOM FROM %s WHERE ACCOUNT_GUID='%s' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf );
+ sql = g_strdup_printf( "SELECT QUANTITY_NUM,QUANTITY_DENOM FROM %s WHERE ACCOUNT_GUID='%s' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf );
+
+ /* Create the query */
+ query = gnc_gda_create_query_from_sql( be, sql );
+ *start_balance = get_account_balance_from_query( be, query );
+
+ g_free( sql );
+
+ /*
+ * For cleared balance,
+ * SELECT SUM(QUANTITY_NUM),QUANTITY_DENOM FROM SPLITS
+ * WHERE ACCOUNT_GUID=<guid> AND RECONCILE_STATE='c'
+ * GROUP BY QUANTITY_DENOM
+ *
+ * This just requires a modification to the query
+ */
+
+ //sql = g_strdup_printf( "SELECT SUM(QUANTITY_NUM),QUANTITY_DENOM FROM %s WHERE ACCOUNT_GUID='%s' AND RECONCILE_STATE='c' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf );
+ sql = g_strdup_printf( "SELECT QUANTITY_NUM,QUANTITY_DENOM FROM %s WHERE ACCOUNT_GUID='%s' AND RECONCILE_STATE='c' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf );
+
+ query = gnc_gda_create_query_from_sql( be, sql );
+ *cleared_balance = get_account_balance_from_query( be, query );
+
+ g_free( sql );
+
+ /*
+ * For reconciled balance,
+ * SELECT SUM(QUANTITY_NUM),QUANTITY_DENOM FROM SPLITS
+ * WHERE ACCOUNT_GUID=<guid> AND RECONCILE_STATE='c'
+ * GROUP BY QUANTITY_DENOM
+ *
+ * This just requires a small modification to the cleared balance query
+ */
+
+ //sql = g_strdup_printf( "SELECT SUM(QUANTITY_NUM),QUANTITY_DENOM FROM %s WHERE ACCOUNT_GUID='%s' AND RECONCILE_STATE='y' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf );
+ sql = g_strdup_printf( "SELECT QUANTITY_NUM,QUANTITY_DENOM FROM %s WHERE ACCOUNT_GUID='%s' AND RECONCILE_STATE='y' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf );
+
+ query = gnc_gda_create_query_from_sql( be, sql );
+ *reconciled_balance = get_account_balance_from_query( be, query );
+
+ g_free( sql );
+}
+
static Split*
load_single_split( GncGdaBackend* be, GdaDataModel* pModel, int row, Split* pSplit )
{
@@ -596,15 +720,51 @@
}
static gpointer
-compile_split_query( GncGdaBackend* pBackend, QofQuery* pQuery )
+compile_split_query( GncGdaBackend* be, QofQuery* pQuery )
{
gchar* buf;
const GUID* acct_guid;
gchar guid_buf[GUID_ENCODING_LENGTH+1];
+ gchar* s;
#if 1
+ GdaQuery* query;
+ GdaObject* results;
+
acct_guid = get_guid_from_query( pQuery );
guid_to_string_buff( acct_guid, guid_buf );
+ buf = g_strdup_printf( "SELECT DISTINCT tx_guid FROM %s WHERE account_guid='%s'", SPLIT_TABLE, guid_buf );
+ results = gnc_gda_execute_sql( be, buf );
+ g_free( buf );
+ buf = g_strdup_printf( "SELECT * FROM %s WHERE guid IN (", TRANSACTION_TABLE );
+ if( GDA_IS_DATA_MODEL( results ) ) {
+ GdaDataModel* pModel = GDA_DATA_MODEL(results);
+ int numRows = gda_data_model_get_n_rows( pModel );
+ int r;
+
+ for( r = 0; r < numRows; r++ ) {
+ const GUID* guid;
+
+ guid = gnc_gda_load_tx_guid( pModel, r );
+ guid_to_string_buff( guid, guid_buf );
+ if( r == 0 ) {
+ s = g_strconcat( buf, "'", guid_buf, "'", NULL );
+ } else {
+ s = g_strconcat( buf, ",'", guid_buf, "'", NULL );
+ }
+ g_free( buf );
+ buf = s;
+ }
+ }
+ s = g_strconcat( buf, ")", NULL );
+ g_free( buf );
+ buf = s;
+
+ return buf;
+#else
+#if 1
+ acct_guid = get_guid_from_query( pQuery );
+ guid_to_string_buff( acct_guid, guid_buf );
buf = g_strdup_printf( "SELECT * FROM %s WHERE guid IN (SELECT DISTINCT tx_guid FROM %s WHERE account_guid = '%s')",
TRANSACTION_TABLE, SPLIT_TABLE, guid_buf );
return buf;
@@ -625,7 +785,7 @@
/* Subquery */
/* SELECT */
- subQuery = gda_query_new( pBackend->pDict );
+ subQuery = gda_query_new( be->pDict );
gda_query_set_query_type( subQuery, GDA_QUERY_TYPE_SELECT );
/* FROM splits */
@@ -650,7 +810,7 @@
/* Main query */
/* SELECT * FROM transactions */
- query = gnc_gda_create_select_query( pBackend, TRANSACTION_TABLE );
+ query = gnc_gda_create_select_query( be, TRANSACTION_TABLE );
gda_query_add_sub_query( query, subQuery );
g_object_unref( G_OBJECT(subQuery) );
@@ -676,6 +836,7 @@
return query;
#endif
+#endif
}
static void
@@ -683,15 +844,9 @@
{
GdaQuery* query;
#if 1
- GError* error = NULL;
const gchar* sql = (const gchar*)pQuery;
- query = gda_query_new_from_sql( be->pDict, sql, &error );
- if( query == NULL ) {
- g_critical( "SQL error: %s\n", error->message );
- return;
- }
- error = NULL;
+ query = gnc_gda_create_query_from_sql( be, sql );
#else
query = GDA_QUERY(pQuery);
#endif
Modified: gnucash/branches/gda-dev/src/backend/gda/gnc-transaction-gda.h
===================================================================
--- gnucash/branches/gda-dev/src/backend/gda/gnc-transaction-gda.h 2007-09-15 08:29:14 UTC (rev 16533)
+++ gnucash/branches/gda-dev/src/backend/gda/gnc-transaction-gda.h 2007-09-16 01:28:54 UTC (rev 16534)
@@ -35,5 +35,9 @@
void gnc_gda_init_transaction_handler( void );
void gnc_gda_transaction_commit_splits( GncGdaBackend* be, Transaction* pTx );
void gnc_gda_save_transaction( GncGdaBackend* be, QofInstance* inst );
+void gnc_gda_get_account_balances( GncGdaBackend* be, Account* pAccount,
+ gnc_numeric* start_balance,
+ gnc_numeric* cleared_balance,
+ gnc_numeric* reconciled_balance );
#endif /* GNC_TRANSACTION_GDA_H_ */
More information about the gnucash-changes
mailing list