r18248 - gnucash/trunk/src - Save timespec values into 'timestamp' in mysql and pgsql.
Phil Longstaff
plongstaff at code.gnucash.org
Thu Aug 13 21:58:36 EDT 2009
Author: plongstaff
Date: 2009-08-13 21:58:36 -0400 (Thu, 13 Aug 2009)
New Revision: 18248
Trac: http://svn.gnucash.org/trac/changeset/18248
Modified:
gnucash/trunk/src/backend/dbi/gnc-backend-dbi.c
gnucash/trunk/src/backend/sql/gnc-backend-sql.c
gnucash/trunk/src/backend/sql/gnc-backend-sql.h
gnucash/trunk/src/backend/sql/gnc-transaction-sql.c
gnucash/trunk/src/business/business-core/sql/gnc-address-sql.c
gnucash/trunk/src/business/business-core/sql/gnc-owner-sql.c
Log:
Save timespec values into 'timestamp' in mysql and pgsql.
NOTE: with this change, the format of the tables has changed.
Modified: gnucash/trunk/src/backend/dbi/gnc-backend-dbi.c
===================================================================
--- gnucash/trunk/src/backend/dbi/gnc-backend-dbi.c 2009-08-14 00:29:52 UTC (rev 18247)
+++ gnucash/trunk/src/backend/dbi/gnc-backend-dbi.c 2009-08-14 01:58:36 UTC (rev 18248)
@@ -82,6 +82,7 @@
conn_create_table_ddl_sqlite3,
conn_get_table_list
};
+#define SQLITE3_TIMESPEC_STR_FORMAT "%04d%02d%02d%02d%02d%02d"
static /*@ null @*/ gchar* conn_create_table_ddl_mysql( GncSqlConnection* conn,
const gchar* table_name,
@@ -91,6 +92,7 @@
conn_create_table_ddl_mysql,
conn_get_table_list
};
+#define MYSQL_TIMESPEC_STR_FORMAT "%04d%02d%02d%02d%02d%02d"
static /*@ null @*/ gchar* conn_create_table_ddl_pgsql( GncSqlConnection* conn,
const gchar* table_name,
@@ -101,6 +103,7 @@
conn_create_table_ddl_pgsql,
conn_get_table_list_pgsql
};
+#define PGSQL_TIMESPEC_STR_FORMAT "%04d%02d%02d %02d%02d%02d"
static /*@ null @*/ gchar* create_index_ddl( GncSqlConnection* conn,
const gchar* index_name,
@@ -247,6 +250,7 @@
gnc_sql_connection_dispose( be->sql_be.conn );
}
be->sql_be.conn = create_dbi_connection( GNC_DBI_PROVIDER_SQLITE, qbe, be->conn );
+ be->sql_be.timespec_format = SQLITE3_TIMESPEC_STR_FORMAT;
LEAVE (" ");
}
@@ -430,6 +434,7 @@
}
be->sql_be.conn = create_dbi_connection( GNC_DBI_PROVIDER_MYSQL, qbe, be->conn );
}
+ be->sql_be.timespec_format = MYSQL_TIMESPEC_STR_FORMAT;
exit:
if( dsn != NULL ) {
g_free( dsn );
@@ -566,6 +571,7 @@
}
be->sql_be.conn = create_dbi_connection( GNC_DBI_PROVIDER_PGSQL, qbe, be->conn );
}
+ be->sql_be.timespec_format = PGSQL_TIMESPEC_STR_FORMAT;
exit:
if( dsn != NULL ) {
g_free( dsn );
@@ -1387,18 +1393,17 @@
if( col_num != 0 ) {
(void)g_string_append( ddl, ", " );
}
- if( info->type == G_TYPE_INT ) {
+ if( info->type == BCT_INT ) {
type_name = "integer";
- } else if( info->type == G_TYPE_INT64 ) {
+ } else if( info->type == BCT_INT64 ) {
type_name = "bigint";
- } else if( info->type == G_TYPE_DOUBLE ) {
+ } else if( info->type == BCT_DOUBLE ) {
type_name = "real";
- } else if( info->type == G_TYPE_STRING ) {
+ } else if( info->type == BCT_STRING || info->type == BCT_DATE
+ || info->type == BCT_DATETIME ) {
type_name = "text";
- } else if( info->type == G_TYPE_DATE ) {
- type_name = "text";
} else {
- PERR( "Unknown GType: %s\n", g_type_name( info->type ) );
+ PERR( "Unknown column type: %d\n", info->type );
type_name = "";
}
g_string_append_printf( ddl, "%s %s", info->name, type_name );
@@ -1442,19 +1447,22 @@
if( col_num != 0 ) {
(void)g_string_append( ddl, ", " );
}
- if( info->type == G_TYPE_INT ) {
+ if( info->type == BCT_INT ) {
type_name = "integer";
- } else if( info->type == G_TYPE_INT64 ) {
+ } else if( info->type == BCT_INT64 ) {
type_name = "bigint";
- } else if( info->type == G_TYPE_DOUBLE ) {
+ } else if( info->type == BCT_DOUBLE ) {
type_name = "double";
- } else if( info->type == G_TYPE_STRING ) {
+ } else if( info->type == BCT_STRING ) {
type_name = "varchar";
- } else if( info->type == G_TYPE_DATE ) {
+ } else if( info->type == BCT_DATE ) {
info->size = 0;
type_name = "date";
+ } else if( info->type == BCT_DATETIME ) {
+ info->size = 0;
+ type_name = "timestamp";
} else {
- PERR( "Unknown GType: %s\n", g_type_name( info->type ) );
+ PERR( "Unknown column type: %d\n", info->type );
type_name = "";
}
g_string_append_printf( ddl, "%s %s", info->name, type_name );
@@ -1502,23 +1510,26 @@
if( col_num != 0 ) {
(void)g_string_append( ddl, ", " );
}
- if( info->type == G_TYPE_INT ) {
+ if( info->type == BCT_INT ) {
if( info->is_autoinc ) {
type_name = "serial";
} else {
type_name = "integer";
}
- } else if( info->type == G_TYPE_INT64 ) {
+ } else if( info->type == BCT_INT64 ) {
type_name = "int8";
- } else if( info->type == G_TYPE_DOUBLE ) {
+ } else if( info->type == BCT_DOUBLE ) {
type_name = "double precision";
- } else if( info->type == G_TYPE_STRING ) {
+ } else if( info->type == BCT_STRING ) {
type_name = "varchar";
- } else if( info->type == G_TYPE_DATE ) {
+ } else if( info->type == BCT_DATE ) {
info->size = 0;
type_name = "date";
+ } else if( info->type == BCT_DATETIME ) {
+ info->size = 0;
+ type_name = "timestamp without time zone";
} else {
- PERR( "Unknown GType: %s\n", g_type_name( info->type ) );
+ PERR( "Unknown column type: %d\n", info->type );
type_name = "";
}
g_string_append_printf( ddl, "%s %s", info->name, type_name );
Modified: gnucash/trunk/src/backend/sql/gnc-backend-sql.c
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-backend-sql.c 2009-08-14 00:29:52 UTC (rev 18247)
+++ gnucash/trunk/src/backend/sql/gnc-backend-sql.c 2009-08-14 01:58:36 UTC (rev 18248)
@@ -997,7 +997,7 @@
}
static GncSqlColumnInfo*
-create_column_info( const GncSqlColumnTableEntry* table_row, GType type,
+create_column_info( const GncSqlColumnTableEntry* table_row, GncSqlBasicColumnType type,
gint size, gboolean is_unicode )
{
GncSqlColumnInfo* info;
@@ -1050,7 +1050,7 @@
g_return_if_fail( table_row != NULL );
g_return_if_fail( pList != NULL );
- info = create_column_info( table_row, G_TYPE_STRING, table_row->size, TRUE );
+ info = create_column_info( table_row, BCT_STRING, table_row->size, TRUE );
*pList = g_list_append( *pList, info );
}
@@ -1136,7 +1136,7 @@
g_return_if_fail( table_row != NULL );
g_return_if_fail( pList != NULL );
- info = create_column_info( table_row, G_TYPE_INT, 0, FALSE );
+ info = create_column_info( table_row, BCT_INT, 0, FALSE );
*pList = g_list_append( *pList, info );
}
@@ -1220,7 +1220,7 @@
g_return_if_fail( table_row != NULL );
g_return_if_fail( pList != NULL );
- info = create_column_info( table_row, G_TYPE_INT, 0, FALSE );
+ info = create_column_info( table_row, BCT_INT, 0, FALSE );
*pList = g_list_append( *pList, info );
}
@@ -1297,7 +1297,7 @@
g_return_if_fail( table_row != NULL );
g_return_if_fail( pList != NULL );
- info = create_column_info( table_row, G_TYPE_INT64, 0, FALSE );
+ info = create_column_info( table_row, BCT_INT64, 0, FALSE );
*pList = g_list_append( *pList, info );
}
@@ -1376,7 +1376,7 @@
g_return_if_fail( table_row != NULL );
g_return_if_fail( pList != NULL );
- info = create_column_info( table_row, G_TYPE_DOUBLE, 0, FALSE );
+ info = create_column_info( table_row, BCT_DOUBLE, 0, FALSE );
*pList = g_list_append( *pList, info );
}
@@ -1461,7 +1461,7 @@
g_return_if_fail( table_row != NULL );
g_return_if_fail( pList != NULL );
- info = create_column_info( table_row, G_TYPE_STRING, GUID_ENCODING_LENGTH, FALSE );
+ info = create_column_info( table_row, BCT_STRING, GUID_ENCODING_LENGTH, FALSE );
*pList = g_list_append( *pList, info );
}
@@ -1559,7 +1559,7 @@
#define TIMESPEC_COL_SIZE (4+2+2+2+2+2)
gchar*
-gnc_sql_convert_timespec_to_string( Timespec ts )
+gnc_sql_convert_timespec_to_string( const GncSqlBackend* be, Timespec ts )
{
time_t time;
struct tm* tm;
@@ -1572,7 +1572,7 @@
if( tm->tm_year < 60 ) year = tm->tm_year + 2000;
else year = tm->tm_year + 1900;
- datebuf = g_strdup_printf( TIMESPEC_STR_FORMAT,
+ datebuf = g_strdup_printf( be->timespec_format,
year, tm->tm_mon+1, tm->tm_mday, tm->tm_hour, tm->tm_min, tm->tm_sec );
return datebuf;
}
@@ -1627,7 +1627,7 @@
g_return_if_fail( table_row != NULL );
g_return_if_fail( pList != NULL );
- info = create_column_info( table_row, G_TYPE_STRING, TIMESPEC_COL_SIZE, FALSE );
+ info = create_column_info( table_row, BCT_DATETIME, TIMESPEC_COL_SIZE, FALSE );
*pList = g_list_append( *pList, info );
}
@@ -1651,7 +1651,7 @@
g_return_if_fail( ts_getter != NULL );
ts = (*ts_getter)( pObject );
- datebuf = gnc_sql_convert_timespec_to_string( ts );
+ datebuf = gnc_sql_convert_timespec_to_string( be, ts );
value = g_new0( GValue, 1 );
g_assert( value != NULL );
(void)g_value_init( value, G_TYPE_STRING );
@@ -1725,7 +1725,7 @@
g_return_if_fail( table_row != NULL );
g_return_if_fail( pList != NULL );
- info = create_column_info( table_row, G_TYPE_DATE, DATE_COL_SIZE, FALSE );
+ info = create_column_info( table_row, BCT_DATE, DATE_COL_SIZE, FALSE );
*pList = g_list_append( *pList, info );
}
@@ -1836,7 +1836,7 @@
info = g_new0( GncSqlColumnInfo, 1 );
g_assert( info != NULL );
info->name = buf;
- info->type = G_TYPE_INT64;
+ info->type = BCT_INT64;
info->is_primary_key = ((table_row->flags & COL_PKEY) != 0) ? TRUE : FALSE;
info->null_allowed = ((table_row->flags & COL_NNUL) != 0) ? FALSE : TRUE;
info->is_unicode = FALSE;
Modified: gnucash/trunk/src/backend/sql/gnc-backend-sql.h
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-backend-sql.h 2009-08-14 00:29:52 UTC (rev 18247)
+++ gnucash/trunk/src/backend/sql/gnc-backend-sql.h 2009-08-14 01:58:36 UTC (rev 18248)
@@ -65,15 +65,16 @@
*/
struct GncSqlBackend
{
- QofBackend be; /**< QOF backend */
- GncSqlConnection* conn; /**< SQL connection */
+ QofBackend be; /**< QOF backend */
+ GncSqlConnection* conn; /**< SQL connection */
/*@ dependent @*/ QofBook *primary_book; /**< The primary, main open book */
- gboolean loading; /**< We are performing an initial load */
- gboolean in_query; /**< We are processing a query */
- gboolean is_pristine_db; /**< Are we saving to a new pristine db? */
- gint obj_total; /**< Total # of objects (for percentage calculation) */
- gint operations_done; /**< Number of operations (save/load) done */
- GHashTable* versions; /**< Version number for each table */
+ gboolean loading; /**< We are performing an initial load */
+ gboolean in_query; /**< We are processing a query */
+ gboolean is_pristine_db; /**< Are we saving to a new pristine db? */
+ gint obj_total; /**< Total # of objects (for percentage calculation) */
+ gint operations_done; /**< Number of operations (save/load) done */
+ GHashTable* versions; /**< Version number for each table */
+ const gchar* timespec_format; /**< Format string for SQL for timespec values */
};
typedef struct GncSqlBackend GncSqlBackend;
@@ -271,6 +272,18 @@
#define GNC_SQL_BACKEND_VERSION 1
/**
+ * Basic column type
+ */
+typedef enum {
+ BCT_STRING,
+ BCT_INT,
+ BCT_INT64,
+ BCT_DATE,
+ BCT_DOUBLE,
+ BCT_DATETIME
+} GncSqlBasicColumnType;
+
+/**
* @struct GncSqlColumnInfo
*
* The GncSqlColumnInfo structure contains information required to create
@@ -278,12 +291,12 @@
*/
typedef struct {
/*@ only @*/ const gchar* name; /**< Column name */
- GType type; /**< Column basic type */
- gint size; /**< Column size (string types) */
- gboolean is_unicode; /**< Column is unicode (string types) */
- gboolean is_autoinc; /**< Column is autoinc (int type) */
- gboolean is_primary_key; /**< Column is the primary key */
- gboolean null_allowed; /**< Column allows NULL values */
+ GncSqlBasicColumnType type; /**< Column basic type */
+ gint size; /**< Column size (string types) */
+ gboolean is_unicode; /**< Column is unicode (string types) */
+ gboolean is_autoinc; /**< Column is autoinc (int type) */
+ gboolean is_primary_key; /**< Column is the primary key */
+ gboolean null_allowed; /**< Column allows NULL values */
} GncSqlColumnInfo;
// Type for conversion of db row to object.
@@ -679,10 +692,11 @@
/**
* Converts a Timespec value to a string value for the database.
*
+ * @param be SQL backend
* @param ts Timespec to be converted
* @return String representation of the Timespec
*/
-gchar* gnc_sql_convert_timespec_to_string( Timespec ts );
+gchar* gnc_sql_convert_timespec_to_string( const GncSqlBackend* be, Timespec ts );
/**
* Upgrades a table to a new structure. The upgrade is done by creating a new table with
Modified: gnucash/trunk/src/backend/sql/gnc-transaction-sql.c
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-transaction-sql.c 2009-08-14 00:29:52 UTC (rev 18247)
+++ gnucash/trunk/src/backend/sql/gnc-transaction-sql.c 2009-08-14 01:58:36 UTC (rev 18248)
@@ -795,7 +795,7 @@
}
static void
-convert_query_term_to_sql( const gchar* fieldName, QofQueryTerm* pTerm, GString* sql )
+convert_query_term_to_sql( const GncSqlBackend* be, const gchar* fieldName, QofQueryTerm* pTerm, GString* sql )
{
GSList* pParamPath;
QofQueryPredData* pPredData;
@@ -912,7 +912,7 @@
query_date_t date_data = (query_date_t)pPredData;
gchar* datebuf;
- datebuf = gnc_sql_convert_timespec_to_string( date_data->date );
+ datebuf = gnc_sql_convert_timespec_to_string( be, date_data->date );
g_string_append_printf( sql, "'%s'", datebuf );
} else if( strcmp( pPredData->type_name, QOF_TYPE_INT32 ) == 0 ) {
@@ -998,13 +998,13 @@
if( strcmp( paramPath->data, SPLIT_ACCOUNT ) == 0
&& strcmp( paramPath->next->data, QOF_PARAM_GUID ) == 0 ) {
- convert_query_term_to_sql( "s.account_guid", term, sql );
+ 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( "s.reconcile_state", term, sql );
+ convert_query_term_to_sql( be, "s.reconcile_state", term, sql );
} else if( strcmp( paramPath->data, SPLIT_TRANS ) == 0 ) {
#if 0
@@ -1014,15 +1014,15 @@
}
#endif
if( strcmp( paramPath->next->data, TRANS_DATE_POSTED ) == 0 ) {
- convert_query_term_to_sql( "t.post_date", term, sql );
+ 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( "t.description", term, sql );
+ 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( "s.value_num/s.value_denom", term, sql );
+ convert_query_term_to_sql( be, "s.value_num/s.value_denom", term, sql );
} else {
unknownPath = TRUE;
Modified: gnucash/trunk/src/business/business-core/sql/gnc-address-sql.c
===================================================================
--- gnucash/trunk/src/business/business-core/sql/gnc-address-sql.c 2009-08-14 00:29:52 UTC (rev 18247)
+++ gnucash/trunk/src/business/business-core/sql/gnc-address-sql.c 2009-08-14 01:58:36 UTC (rev 18248)
@@ -121,7 +121,7 @@
buf = g_strdup_printf( "%s_%s", table_row->col_name, subtable_row->col_name );
info = g_new0( GncSqlColumnInfo, 1 );
info->name = buf;
- info->type = G_TYPE_STRING;
+ info->type = BCT_STRING;
info->size = subtable_row->size;
info->is_primary_key = (table_row->flags & COL_PKEY) ? TRUE : FALSE;
info->null_allowed = (table_row->flags & COL_NNUL) ? FALSE : TRUE;
Modified: gnucash/trunk/src/business/business-core/sql/gnc-owner-sql.c
===================================================================
--- gnucash/trunk/src/business/business-core/sql/gnc-owner-sql.c 2009-08-14 00:29:52 UTC (rev 18247)
+++ gnucash/trunk/src/business/business-core/sql/gnc-owner-sql.c 2009-08-14 01:58:36 UTC (rev 18248)
@@ -166,7 +166,7 @@
buf = g_strdup_printf( "%s_type", table_row->col_name );
info = g_new0( GncSqlColumnInfo, 1 );
info->name = buf;
- info->type = G_TYPE_INT;
+ info->type = BCT_INT;
info->is_primary_key = (table_row->flags & COL_PKEY) ? TRUE : FALSE;
info->null_allowed = (table_row->flags & COL_NNUL) ? FALSE : TRUE;
info->size = table_row->size;
@@ -176,7 +176,7 @@
buf = g_strdup_printf( "%s_guid", table_row->col_name );
info = g_new0( GncSqlColumnInfo, 1 );
info->name = buf;
- info->type = G_TYPE_STRING;
+ info->type = BCT_STRING;
info->size = GUID_ENCODING_LENGTH;
info->is_primary_key = (table_row->flags & COL_PKEY) ? TRUE : FALSE;
info->null_allowed = (table_row->flags & COL_NNUL) ? FALSE : TRUE;
More information about the gnucash-changes
mailing list