gnc-slots-sql.c File Reference

load and save data to SQL More...

#include "config.h"
#include <glib.h>
#include "qof.h"
#include "gnc-engine.h"
#include "gnc-backend-sql.h"
#include "gnc-slots-sql.h"

Go to the source code of this file.

Data Structures
struct	slot_info_t

Macros
#define	TABLE_NAME   "slots"
#define	TABLE_VERSION   3
#define	SLOT_MAX_PATHNAME_LEN   4096
#define	SLOT_MAX_STRINGVAL_LEN   4096

Enumerations
enum	context_t { NONE, FRAME, LIST }
enum	{   id_col = 0, obj_guid_col, name_col, slot_type_col,   int64_val_col, string_val_col, double_val_col, timespec_val_col,   guid_val_col, numeric_val_col, gdate_val_col }

Functions
gboolean	gnc_sql_slots_save (GncSqlBackend *be, const GncGUID *guid, gboolean is_infant, KvpFrame *pFrame)
gboolean	gnc_sql_slots_delete (GncSqlBackend *be, const GncGUID *guid)
void	gnc_sql_slots_load (GncSqlBackend *be, QofInstance *inst)
void	gnc_sql_slots_load_for_list (GncSqlBackend *be, GList *list)
void	gnc_sql_slots_load_for_sql_subquery (GncSqlBackend *be, const gchar *subquery, BookLookupFn lookup_fn)
void	gnc_sql_init_slots_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-slots-sql.c.

Function Documentation

gboolean gnc_sql_slots_delete	(	GncSqlBackend *	be,
		const GncGUID *	guid
	)

gnc_sql_slots_delete - Deletes slots for an object from the db.

Parameters

be	SQL backend
guid	Object guid

Returns

TRUE if successful, FALSE if error

Definition at line 733 of file gnc-slots-sql.c.

{
    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(NULL) };

    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;
}

void gnc_sql_slots_load	(	GncSqlBackend *	be,
		QofInstance *	inst
	)

Loads slots for an object from the db.

Parameters

be	SQL backend

Definition at line 819 of file gnc-slots-sql.c.

{
    slot_info_t info = { NULL, NULL, TRUE, NULL, 0, NULL, FRAME, NULL, g_string_new(NULL) };
    g_return_if_fail( be != NULL );
    g_return_if_fail( inst != NULL );

    info.be = be;
    info.guid = qof_instance_get_guid( inst );
    info.pKvpFrame = qof_instance_get_slots( inst );
    info.context = NONE;

    slots_load_info( &info );
}

void gnc_sql_slots_load_for_list	(	GncSqlBackend *	be,
		GList *	list
	)

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.

Parameters

be	SQL backend
list	List of objects

Definition at line 911 of file gnc-slots-sql.c.

{
    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 );
    }
}

void gnc_sql_slots_load_for_sql_subquery	(	GncSqlBackend *	be,
		const gchar *	subquery,
		BookLookupFn	lookup_fn
	)

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.

Parameters

be	SQL backend
subquery	Subquery SQL string
lookup_fn	Lookup function

Definition at line 1006 of file gnc-slots-sql.c.

{
    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 );
    }
}

gboolean gnc_sql_slots_save	(	GncSqlBackend *	be,
		const GncGUID *	guid,
		gboolean	is_infant,
		KvpFrame *	pFrame
	)

gnc_sql_slots_save - Saves slots for an object to the db.

Parameters

be	SQL backend
guid	Object guid
is_infant	Is this an infant object?
pFrame	Top-level KVP frame

Returns

TRUE if successful, FALSE if error

Definition at line 710 of file gnc-slots-sql.c.

{
    slot_info_t slot_info = { NULL, NULL, TRUE, NULL, 0, NULL, FRAME, NULL, g_string_new(NULL) };

    g_return_val_if_fail( be != NULL, FALSE );
    g_return_val_if_fail( guid != NULL, FALSE );
    g_return_val_if_fail( pFrame != NULL, FALSE );

    // If this is not saving into a new db, clear out the old saved slots first
    if ( !be->is_pristine_db && !is_infant )
    {
        (void)gnc_sql_slots_delete( be, guid );
    }

    slot_info.be = be;
    slot_info.guid = guid;
    kvp_frame_for_each_slot( pFrame, save_slot, &slot_info );
    (void)g_string_free( slot_info.path, TRUE );

    return slot_info.is_ok;
}