gnc-backend-sql.h

Go to the documentation of this file.

/********************************************************************
 * gnc-backend-sql.h: load and save data to SQL                     *
 *                                                                  *
 * This program is free software; you can redistribute it and/or    *
 * modify it under the terms of the GNU General Public License as   *
 * published by the Free Software Foundation; either version 2 of   *
 * the License, or (at your option) any later version.              *
 *                                                                  *
 * This program is distributed in the hope that it will be useful,  *
 * but WITHOUT ANY WARRANTY; without even the implied warranty of   *
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the    *
 * GNU General Public License for more details.                     *
 *                                                                  *
 * You should have received a copy of the GNU General Public License*
 * along with this program; if not, contact:                        *
 *                                                                  *
 * Free Software Foundation           Voice:  +1-617-542-5942       *
 * 51 Franklin Street, Fifth Floor    Fax:    +1-617-542-2652       *
 * Boston, MA  02110-1301,  USA       gnu@gnu.org                   *
\********************************************************************/

#ifndef GNC_BACKEND_SQL_H
#define GNC_BACKEND_SQL_H

#include "qof.h"
#include "qofbackend-p.h"
#include <gmodule.h>

typedef struct GncSqlConnection GncSqlConnection;

struct GncSqlBackend
{
    QofBackend be;                              
    GncSqlConnection* conn;             
    /*@ dependent @*/
    QofBook *book;      
    gboolean loading;                           
    gboolean in_query;                  
    gboolean is_pristine_db;            
    gint obj_total;                             
    gint operations_done;                       
    GHashTable* versions;                       
    const gchar* timespec_format;       
};
typedef struct GncSqlBackend GncSqlBackend;

void gnc_sql_init( GncSqlBackend* be );

void gnc_sql_load( GncSqlBackend* be, /*@ dependent @*/ QofBook *book, QofBackendLoadType loadType );

void gnc_sql_sync_all( GncSqlBackend* be, /*@ dependent @*/ QofBook *book );

void gnc_sql_begin_edit( GncSqlBackend* be, QofInstance *inst );

void gnc_sql_rollback_edit( GncSqlBackend* qbe, QofInstance *inst );

void gnc_sql_commit_edit( GncSqlBackend* qbe, QofInstance *inst );

typedef struct GncSqlColumnTableEntry GncSqlColumnTableEntry;
typedef struct GncSqlStatement GncSqlStatement;
typedef struct GncSqlResult GncSqlResult;
typedef struct GncSqlRow GncSqlRow;

struct GncSqlStatement
{
    void (*dispose)( /*@ only @*/ GncSqlStatement* );
    /*@ dependent @*/
    gchar* (*toSql)( GncSqlStatement* );
    void (*addWhereCond)( GncSqlStatement*, QofIdTypeConst, gpointer, const GncSqlColumnTableEntry*, GValue* );
};
#define gnc_sql_statement_dispose(STMT) \
                (STMT)->dispose(STMT)
#define gnc_sql_statement_to_sql(STMT) \
                (STMT)->toSql(STMT)
#define gnc_sql_statement_add_where_cond(STMT,TYPENAME,OBJ,COLDESC,VALUE) \
                (STMT)->addWhereCond(STMT, TYPENAME, OBJ, COLDESC, VALUE)

struct GncSqlConnection
{
    void (*dispose)( /*@ only @*/ GncSqlConnection* );
    GncSqlResult* (*executeSelectStatement)( GncSqlConnection*, GncSqlStatement* ); 
    gint (*executeNonSelectStatement)( GncSqlConnection*, GncSqlStatement* ); 
    GncSqlStatement* (*createStatementFromSql)( /*@ observer @*/ GncSqlConnection*, const gchar* );
    gboolean (*doesTableExist)( GncSqlConnection*, const gchar* );  
    gboolean (*beginTransaction)( GncSqlConnection* ); 
    gboolean (*rollbackTransaction)( GncSqlConnection* ); 
    gboolean (*commitTransaction)( GncSqlConnection* ); 
    gboolean (*createTable)( GncSqlConnection*, const gchar*, GList* ); 
    gboolean (*createIndex)( GncSqlConnection*, const gchar*, const gchar*, const GncSqlColumnTableEntry* ); 
    gboolean (*addColumnsToTable)( GncSqlConnection*, const gchar* table, GList* ); 
    gchar* (*quoteString)( const GncSqlConnection*, gchar* );
};
#define gnc_sql_connection_dispose(CONN) (CONN)->dispose(CONN)
#define gnc_sql_connection_execute_select_statement(CONN,STMT) \
                (CONN)->executeSelectStatement(CONN,STMT)
#define gnc_sql_connection_execute_nonselect_statement(CONN,STMT) \
                (CONN)->executeNonSelectStatement(CONN,STMT)
#define gnc_sql_connection_create_statement_from_sql(CONN,SQL) \
                (CONN)->createStatementFromSql(CONN,SQL)
#define gnc_sql_connection_does_table_exist(CONN,NAME) \
                (CONN)->doesTableExist(CONN,NAME)
#define gnc_sql_connection_begin_transaction(CONN) \
                (CONN)->beginTransaction(CONN)
#define gnc_sql_connection_rollback_transaction(CONN) \
                (CONN)->rollbackTransaction(CONN)
#define gnc_sql_connection_commit_transaction(CONN) \
                (CONN)->commitTransaction(CONN)
#define gnc_sql_connection_create_table(CONN,NAME,COLLIST) \
                (CONN)->createTable(CONN,NAME,COLLIST)
#define gnc_sql_connection_create_index(CONN,INDEXNAME,TABLENAME,COLTABLE) \
                (CONN)->createIndex(CONN,INDEXNAME,TABLENAME,COLTABLE)
#define gnc_sql_connection_add_columns_to_table(CONN,TABLENAME,COLLIST) \
                (CONN)->addColumnsToTable(CONN,TABLENAME,COLLIST)
#define gnc_sql_connection_quote_string(CONN,STR) \
                (CONN)->quoteString(CONN,STR)

struct GncSqlRow
{
    const GValue* (*getValueAtColName)( GncSqlRow*, const gchar* );
    void (*dispose)( /*@ only @*/ GncSqlRow* );
};
#define gnc_sql_row_get_value_at_col_name(ROW,N) \
                (ROW)->getValueAtColName(ROW,N)
#define gnc_sql_row_dispose(ROW) \
                (ROW)->dispose(ROW)

struct GncSqlResult
{
    guint (*getNumRows)( GncSqlResult* );
    GncSqlRow* (*getFirstRow)( GncSqlResult* );
    GncSqlRow* (*getNextRow)( GncSqlResult* );
    void (*dispose)( /*@ only @*/ GncSqlResult* );
};
#define gnc_sql_result_get_num_rows(RESULT) \
                (RESULT)->getNumRows(RESULT)
#define gnc_sql_result_get_first_row(RESULT) \
                (RESULT)->getFirstRow(RESULT)
#define gnc_sql_result_get_next_row(RESULT) \
                (RESULT)->getNextRow(RESULT)
#define gnc_sql_result_dispose(RESULT) \
                (RESULT)->dispose(RESULT)

typedef struct
{
    int         version;                
    const gchar *       type_name;      
    /*@ null @*/
    gboolean    (*commit)( GncSqlBackend* be, QofInstance* inst );
    /*@ null @*/
    void                (*initial_load)( GncSqlBackend* be );
    /*@ null @*/
    void                (*create_tables)( GncSqlBackend* be );
    /*@ null @*/
    gpointer    (*compile_query)( GncSqlBackend* be, QofQuery* pQuery );
    /*@ null @*/
    void                (*run_query)( GncSqlBackend* be, gpointer pQuery );
    /*@ null @*/
    void                (*free_query)( GncSqlBackend* be, gpointer pQuery );
    /*@ null @*/
    gboolean    (*write)( GncSqlBackend* be );
} GncSqlObjectBackend;
#define GNC_SQL_BACKEND             "gnc:sql:1"
#define GNC_SQL_BACKEND_VERSION 1

typedef enum
{
    BCT_STRING,
    BCT_INT,
    BCT_INT64,
    BCT_DATE,
    BCT_DOUBLE,
    BCT_DATETIME
} GncSqlBasicColumnType;

typedef struct
{
    /*@ only @*/ gchar* name;                           
    GncSqlBasicColumnType type;                         
    gint size;                                                          
    gboolean is_unicode;                                        
    gboolean is_autoinc;                                        
    gboolean is_primary_key;                            
    gboolean null_allowed;                                      
} GncSqlColumnInfo;

// Type for conversion of db row to object.
#define CT_STRING "ct_string"
#define CT_GUID "ct_guid"
#define CT_INT "ct_int"
#define CT_INT64 "ct_int64"
#define CT_TIMESPEC "ct_timespec"
#define CT_GDATE "ct_gdate"
#define CT_NUMERIC "ct_numeric"
#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"

struct GncSqlColumnTableEntry
{
    /*@ dependent @*/ const gchar* col_name;    
    const gchar* col_type;      
    gint size;                          
#define COL_PKEY        0x01    
#define COL_NNUL        0x02    
#define COL_UNIQUE      0x04    
#define COL_AUTOINC     0x08    
    gint flags;                         
    /*@ null @*/
    const gchar* gobj_param_name; 
    /*@ null @*/
    const gchar* qof_param_name;  
    /*@ null @*/
    QofAccessFunc getter;       
    /*@ null @*/
    QofSetterFunc setter;       
};

typedef enum
{
    OP_DB_INSERT,
    OP_DB_UPDATE,
    OP_DB_DELETE
} E_DB_OPERATION;

typedef void (*GNC_SQL_LOAD_FN)( const GncSqlBackend* be,
                                 GncSqlRow* row,
                                 /*@ null @*/ QofSetterFunc setter, gpointer pObject,
                                 const GncSqlColumnTableEntry* table );
typedef void (*GNC_SQL_ADD_COL_INFO_TO_LIST_FN)( const GncSqlBackend* be,
        const GncSqlColumnTableEntry* table_row,
        GList** pList );
typedef void (*GNC_SQL_ADD_COLNAME_TO_LIST_FN)( const GncSqlColumnTableEntry* table_row, GList** pList );
typedef void (*GNC_SQL_ADD_GVALUE_TO_SLIST_FN)( const GncSqlBackend* be,
        QofIdTypeConst obj_name, const gpointer pObject,
        const GncSqlColumnTableEntry* table_row, GSList** pList );

typedef struct
{
    GNC_SQL_LOAD_FN                 load_fn;

    GNC_SQL_ADD_COL_INFO_TO_LIST_FN add_col_info_to_list_fn;

    GNC_SQL_ADD_COLNAME_TO_LIST_FN  add_colname_to_list_fn;

    GNC_SQL_ADD_GVALUE_TO_SLIST_FN      add_gvalue_to_slist_fn;
} GncSqlColumnTypeHandler;

/*@ null @*/
QofAccessFunc gnc_sql_get_getter( QofIdTypeConst obj_name, const GncSqlColumnTableEntry* table_row );

void gnc_sql_add_colname_to_list( const GncSqlColumnTableEntry* table_row, GList** pList );

gboolean gnc_sql_do_db_operation( GncSqlBackend* be,
                                  E_DB_OPERATION op,
                                  const gchar* table_name,
                                  QofIdTypeConst obj_name,
                                  gpointer pObject,
                                  const GncSqlColumnTableEntry* table );

/*@ null @*/
GncSqlResult* gnc_sql_execute_select_statement( GncSqlBackend* be, GncSqlStatement* statement );

/*@ null @*/
GncSqlResult* gnc_sql_execute_select_sql( GncSqlBackend* be, const gchar* sql );

gint gnc_sql_execute_nonselect_sql( GncSqlBackend* be, const gchar* sql );

/*@ null @*/
GncSqlStatement* gnc_sql_create_statement_from_sql( GncSqlBackend* be, const gchar* sql );

void gnc_sql_load_object( const GncSqlBackend* be, GncSqlRow* row,
                          /*@ null @*/ QofIdTypeConst obj_name, gpointer pObject,
                          const GncSqlColumnTableEntry* table );

gboolean gnc_sql_object_is_it_in_db( GncSqlBackend* be,
                                     const gchar* table_name,
                                     QofIdTypeConst obj_name, const gpointer pObject,
                                     const GncSqlColumnTableEntry* table );

gint gnc_sql_get_table_version( const GncSqlBackend* be, const gchar* table_name );

gboolean gnc_sql_set_table_version( GncSqlBackend* be,
                                    const gchar* table_name,
                                    gint version );

gboolean gnc_sql_create_table( GncSqlBackend* be,
                               const gchar* table_name,
                               gint table_version,
                               const GncSqlColumnTableEntry* col_table );

gboolean gnc_sql_create_temp_table( const GncSqlBackend* be,
                                    const gchar* table_name,
                                    const GncSqlColumnTableEntry* col_table );

gboolean gnc_sql_create_index( const GncSqlBackend* be, const gchar* index_name,
                               const gchar* table_name, const GncSqlColumnTableEntry* col_table );

/*@ dependent @*//*@ null @*/
const GncGUID* gnc_sql_load_guid( const GncSqlBackend* be, GncSqlRow* row );

/*@ dependent @*//*@ null @*/
const GncGUID* gnc_sql_load_tx_guid( const GncSqlBackend* be, GncSqlRow* row );

/*@ null @*/
GncSqlStatement* gnc_sql_create_select_statement( GncSqlBackend* be,
        const gchar* table_name );

void gnc_sql_register_col_type_handler( const gchar* colType, const GncSqlColumnTypeHandler* handler );

void gnc_sql_add_gvalue_objectref_guid_to_slist( const GncSqlBackend* be,
        QofIdTypeConst obj_name, const gpointer pObject,
        const GncSqlColumnTableEntry* table_row, GSList** pList );

void gnc_sql_add_objectref_guid_col_info_to_list( const GncSqlBackend* be,
        const GncSqlColumnTableEntry* table_row, GList** pList );

guint gnc_sql_append_guid_list_to_sql( GString* str, GList* list, guint maxCount );

void gnc_sql_add_subtable_colnames_to_list( const GncSqlColumnTableEntry* table_row,
        const GncSqlColumnTableEntry* subtable,
        GList** pList );

gchar* gnc_sql_get_sql_value( const GncSqlConnection* conn, const GValue* value );

void gnc_sql_init_version_info( GncSqlBackend* be );

void gnc_sql_finalize_version_info( GncSqlBackend* be );

gboolean gnc_sql_commit_standard_item( GncSqlBackend* be, QofInstance* inst, const gchar* tableName,
                                       QofIdTypeConst obj_name, const GncSqlColumnTableEntry* col_table );

gint64 gnc_sql_get_integer_value( const GValue* value );

gchar* gnc_sql_convert_timespec_to_string( const GncSqlBackend* be, Timespec ts );

void gnc_sql_upgrade_table( GncSqlBackend* be, const gchar* table_name,
                            const GncSqlColumnTableEntry* col_table );

gboolean gnc_sql_add_columns_to_table( GncSqlBackend* be, const gchar* table_name,
                                       const GncSqlColumnTableEntry* new_col_table );

void gnc_sql_set_load_order( const gchar** load_order );

void _retrieve_guid_( gpointer pObject, /*@ null @*/ gpointer pValue );

/*@ null @*/
gpointer gnc_sql_compile_query( QofBackend* pBEnd, QofQuery* pQuery );
void gnc_sql_free_query( QofBackend* pBEnd, gpointer pQuery );
void gnc_sql_run_query( QofBackend* pBEnd, gpointer pQuery );

typedef struct
{
    /*@ dependent @*/ GncSqlBackend* be;
    gboolean is_ok;
} write_objects_t;

#endif /* GNC_BACKEND_SQL_H */