void
gnc_sql_slots_load_for_list( GncSqlBackend* be, GList* list )
{
QofCollection* coll;
GncSqlStatement* stmt;
GString* sql;
GncSqlResult* result;
gboolean single_item;
g_return_if_fail( be != NULL );
// Ignore empty list
if ( list == NULL ) return;
coll = qof_instance_get_collection( QOF_INSTANCE(list->data) );
// Create the query for all slots for all items on the list
sql = g_string_sized_new( 40 + (GUID_ENCODING_LENGTH + 3) * g_list_length( list ) );
g_string_append_printf( sql, "SELECT * FROM %s WHERE %s ", TABLE_NAME, obj_guid_col_table[0].col_name );
if ( g_list_length( list ) != 1 )
{
(void)g_string_append( sql, "IN (" );
single_item = FALSE;
}
else
{
(void)g_string_append( sql, "= " );
single_item = TRUE;
}
(void)gnc_sql_append_guid_list_to_sql( sql, list, G_MAXUINT );
if ( !single_item )
{
(void)g_string_append( sql, ")" );
}
// Execute the query and load the slots
stmt = gnc_sql_create_statement_from_sql( be, sql->str );
if ( stmt == NULL )
{
PERR( "stmt == NULL, SQL = '%s'\n", sql->str );
(void)g_string_free( sql, TRUE );
return;
}
(void)g_string_free( sql, TRUE );
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_list_item( be, row, coll );
row = gnc_sql_result_get_next_row( result );
}
gnc_sql_result_dispose( result );
}
}
/* ----------------------------------------------------------------- */
static void
load_tx_guid( const GncSqlBackend* be, GncSqlRow* row,
/*@ null @*/ QofSetterFunc setter, gpointer pObject,
const GncSqlColumnTableEntry* table_row )
{
const GValue* val;
GncGUID guid;
Transaction* tx;
const gchar* guid_str;
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 );
g_assert( val != NULL );
guid_str = g_value_get_string(val);
if ( guid_str != NULL )
{
(void)string_to_guid( guid_str, &guid );
tx = xaccTransLookup( &guid, be->book );
// If the transaction is not found, try loading it
if ( tx == NULL )
{
gchar* buf;
GncSqlStatement* stmt;
buf = g_strdup_printf( "SELECT * FROM %s WHERE guid='%s'",
TRANSACTION_TABLE, guid_str );
stmt = gnc_sql_create_statement_from_sql( (GncSqlBackend*)be, buf );
g_free( buf );
query_transactions( (GncSqlBackend*)be, stmt );
tx = xaccTransLookup( &guid, be->book );
}
if ( tx != NULL )
{
if ( table_row->gobj_param_name != NULL )
{
qof_instance_increase_editlevel (pObject);
g_object_set( pObject, table_row->gobj_param_name, tx, NULL );
qof_instance_decrease_editlevel (pObject);
}
else
{
g_return_if_fail( setter != NULL );
(*setter)( pObject, (const gpointer)tx );
}
}
}
}
gboolean
gnc_sql_slots_delete( GncSqlBackend* be, const GncGUID* guid )
{
gchar* buf;
GncSqlResult* result;
gchar guid_buf[GUID_ENCODING_LENGTH + 1];
GncSqlStatement* stmt;
slot_info_t slot_info = { NULL, NULL, TRUE, NULL, 0, NULL, FRAME, NULL, g_string_new('\0') };
g_return_val_if_fail( be != NULL, FALSE );
g_return_val_if_fail( guid != NULL, FALSE );
(void)guid_to_string_buff( guid, guid_buf );
buf = g_strdup_printf( "SELECT * FROM %s WHERE obj_guid='%s' and slot_type in ('%d', '%d') and not guid_val is null",
TABLE_NAME, guid_buf, KVP_TYPE_FRAME, KVP_TYPE_GLIST );
stmt = gnc_sql_create_statement_from_sql( be, buf );
g_free( buf );
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 );
while ( row != NULL )
{
GncSqlColumnTableEntry table_row = col_table[guid_val_col];
GncGUID child_guid;
const GValue* val =
gnc_sql_row_get_value_at_col_name( row, table_row.col_name);
if ( val == NULL )
continue;
(void)string_to_guid( g_value_get_string( val ), &child_guid );
gnc_sql_slots_delete( be, &child_guid );
row = gnc_sql_result_get_next_row( result );
}
gnc_sql_result_dispose( result );
}
}
slot_info.be = be;
slot_info.guid = guid;
slot_info.is_ok = TRUE;
slot_info.is_ok = gnc_sql_do_db_operation( be, OP_DB_DELETE, TABLE_NAME,
TABLE_NAME, &slot_info, obj_guid_col_table );
return slot_info.is_ok;
}
/**
* 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.
*
* @param be SQL backend
*/
void gnc_sql_transaction_load_all_tx( GncSqlBackend* be )
{
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 );
}
}
/**
* 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 );
}
}
/**
* Loads all transactions for an account.
*
* @param be SQL backend
* @param account Account
*/
void gnc_sql_transaction_load_tx_for_account( GncSqlBackend* be, Account* account )
{
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 );
}
}
static void
slots_load_info ( slot_info_t *pInfo )
{
gchar* buf;
GncSqlResult* result;
gchar guid_buf[GUID_ENCODING_LENGTH + 1];
GncSqlStatement* stmt;
g_return_if_fail( pInfo != NULL );
g_return_if_fail( pInfo->be != NULL );
g_return_if_fail( pInfo->guid != NULL );
g_return_if_fail( pInfo->pKvpFrame != NULL );
(void)guid_to_string_buff( pInfo->guid, guid_buf );
buf = g_strdup_printf( "SELECT * FROM %s WHERE obj_guid='%s'",
TABLE_NAME, guid_buf );
stmt = gnc_sql_create_statement_from_sql( pInfo->be, buf );
g_free( buf );
if ( stmt != NULL )
{
result = gnc_sql_execute_select_statement( pInfo->be, stmt );
gnc_sql_statement_dispose( stmt );
if ( result != NULL )
{
GncSqlRow* row = gnc_sql_result_get_first_row( result );
while ( row != NULL )
{
load_slot( pInfo, row );
row = gnc_sql_result_get_next_row( result );
}
gnc_sql_result_dispose( result );
}
}
}
/*@ null @*/ GSList*
gnc_sql_get_account_balances_slist( GncSqlBackend* be )
{
#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
}
G_GNUC_UNUSED static /*@ null @*/ gpointer
compile_split_query( GncSqlBackend* be, QofQuery* query )
{
split_query_info_t* query_info = NULL;
gchar* query_sql;
g_return_val_if_fail( be != NULL, NULL );
g_return_val_if_fail( query != NULL, NULL );
query_info = g_malloc( (gsize)sizeof(split_query_info_t) );
g_assert( query_info != NULL );
query_info->has_been_run = FALSE;
if ( qof_query_has_terms( query ) )
{
GList* orterms = qof_query_get_terms( query );
GList* orTerm;
GString* sql = g_string_new( "" );
gboolean need_OR = FALSE;
for ( orTerm = orterms; orTerm != NULL; orTerm = orTerm->next )
{
GList* andterms = (GList*)orTerm->data;
GList* andTerm;
gboolean need_AND = FALSE;
#if TX_GUID_CHECK
gboolean has_tx_guid_check = FALSE;
#endif
if ( need_OR )
{
g_string_append( sql, " OR " );
}
g_string_append( sql, "(" );
for ( andTerm = andterms; andTerm != NULL; andTerm = andTerm->next )
{
QofQueryTerm* term;
GSList* paramPath;
gboolean unknownPath = FALSE;
term = (QofQueryTerm*)andTerm->data;
paramPath = qof_query_term_get_param_path( term );
if ( strcmp( paramPath->data, QOF_PARAM_BOOK ) == 0 ) continue;
#if SIMPLE_QUERY_COMPILATION
if ( strcmp( paramPath->data, SPLIT_ACCOUNT ) != 0
|| strcmp( paramPath->next->data, QOF_PARAM_GUID ) != 0 ) continue;
#endif
if ( need_AND ) g_string_append( sql, " AND " );
if ( strcmp( paramPath->data, SPLIT_ACCOUNT ) == 0
&& strcmp( paramPath->next->data, QOF_PARAM_GUID ) == 0 )
{
convert_query_term_to_sql( be, "s.account_guid", term, sql );
#if SIMPLE_QUERY_COMPILATION
goto done_compiling_query;
#endif
}
else if ( strcmp( paramPath->data, SPLIT_RECONCILE ) == 0 )
{
convert_query_term_to_sql( be, "s.reconcile_state", term, sql );
}
else if ( strcmp( paramPath->data, SPLIT_TRANS ) == 0 )
{
#if TX_GUID_CHECK
if ( !has_tx_guid_check )
{
g_string_append( sql, "(splits.tx_guid = transactions.guid) AND " );
has_tx_guid_check = TRUE;
}
#endif
if ( strcmp( paramPath->next->data, TRANS_DATE_POSTED ) == 0 )
{
convert_query_term_to_sql( be, "t.post_date", term, sql );
}
else if ( strcmp( paramPath->next->data, TRANS_DESCRIPTION ) == 0 )
{
convert_query_term_to_sql( be, "t.description", term, sql );
}
else
{
unknownPath = TRUE;
}
}
else if ( strcmp( paramPath->data, SPLIT_VALUE ) == 0 )
{
convert_query_term_to_sql( be, "s.value_num/s.value_denom", term, sql );
}
else
{
unknownPath = TRUE;
}
if ( unknownPath )
{
GString* name = g_string_new( (gchar*)paramPath->data );
while ( paramPath->next != NULL )
{
g_string_append( name, "." );
g_string_append( name, paramPath->next->data );
paramPath = paramPath->next;
}
PERR( "Unknown SPLIT query field: %s\n", name->str );
g_string_free( name, TRUE );
}
need_AND = TRUE;
}
/* If the last char in the string is a '(', then for some reason, there were
no terms added to the SQL. If so, remove it and ignore the OR term. */
if ( sql->str[sql->len-1] == '(' )
{
g_string_truncate( sql, sql->len - 1 );
need_OR = FALSE;
}
else
{
g_string_append( sql, ")" );
need_OR = TRUE;
}
}
#if SIMPLE_QUERY_COMPILATION
done_compiling_query:
#endif
if ( sql->len != 0 )
{
#if SIMPLE_QUERY_COMPILATION
g_string_append( sql, ")" );
#endif
query_sql = g_strdup_printf(
"SELECT DISTINCT t.* FROM %s AS t, %s AS s WHERE s.tx_guid=t.guid AND %s",
TRANSACTION_TABLE, SPLIT_TABLE, sql->str );
}
else
{
query_sql = g_strdup_printf( "SELECT * FROM %s", TRANSACTION_TABLE );
}
query_info->stmt = gnc_sql_create_statement_from_sql( be, query_sql );
g_string_free( sql, TRUE );
g_free( query_sql );
}
else
{
query_sql = g_strdup_printf( "SELECT * FROM %s", TRANSACTION_TABLE );
query_info->stmt = gnc_sql_create_statement_from_sql( be, query_sql );
g_free( query_sql );
}
return query_info;
}
