r17780 - gnucash/trunk/src - Fix Bug 559772 – SQL backend not non-ascii-safe
Phil Longstaff
plongstaff at cvs.gnucash.org
Fri Dec 19 22:06:18 EST 2008
Author: plongstaff
Date: 2008-12-19 22:06:17 -0500 (Fri, 19 Dec 2008)
New Revision: 17780
Trac: http://svn.gnucash.org/trac/changeset/17780
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/business/business-core/sql/gnc-address-sql.c
gnucash/trunk/src/business/business-core/sql/gnc-owner-sql.c
Log:
Fix Bug 559772 – SQL backend not non-ascii-safe
Sqlite3 uses utf8 encoding for all char fields, so it is non-ascii-safe.
For postgresql, the default encoding can be set on a per-db basis.
Since the database is not created by gnucash (the tables are, but not
the database), it is for the user to set utf8 encoding when the database
is created. For mysql, a default encoding can be set on a per-db,
per-table or per-field basis. Since there are char fields which do not
need to be utf8 (e.g. guids), encoding is set on a per-field basis.
Modified: gnucash/trunk/src/backend/dbi/gnc-backend-dbi.c
===================================================================
--- gnucash/trunk/src/backend/dbi/gnc-backend-dbi.c 2008-12-15 23:57:39 UTC (rev 17779)
+++ gnucash/trunk/src/backend/dbi/gnc-backend-dbi.c 2008-12-20 03:06:17 UTC (rev 17780)
@@ -54,12 +54,44 @@
static QofLogModule log_module = G_LOG_DOMAIN;
-static GncSqlConnection* create_dbi_connection( gint provider, dbi_conn conn );
+typedef gchar* (*CREATE_TABLE_DDL_FN)( GncSqlConnection* conn,
+ const gchar* table_name,
+ const GList* col_info_list );
+typedef struct {
+ CREATE_TABLE_DDL_FN create_table_ddl;
+} provider_functions_t;
-#define GNC_DBI_PROVIDER_SQLITE 0
-#define GNC_DBI_PROVIDER_MYSQL 1
-#define GNC_DBI_PROVIDER_PGSQL 2
+static gchar* conn_create_table_ddl_sqlite3( GncSqlConnection* conn,
+ const gchar* table_name,
+ const GList* col_info_list );
+static provider_functions_t provider_sqlite3 =
+{
+ conn_create_table_ddl_sqlite3
+};
+
+static gchar* conn_create_table_ddl_mysql( GncSqlConnection* conn,
+ const gchar* table_name,
+ const GList* col_info_list );
+static provider_functions_t provider_mysql =
+{
+ conn_create_table_ddl_mysql
+};
+
+static gchar* conn_create_table_ddl_pgsql( GncSqlConnection* conn,
+ const gchar* table_name,
+ const GList* col_info_list );
+static provider_functions_t provider_pgsql =
+{
+ conn_create_table_ddl_pgsql
+};
+
+static GncSqlConnection* create_dbi_connection( provider_functions_t* provider, dbi_conn conn );
+
+#define GNC_DBI_PROVIDER_SQLITE (&provider_sqlite3)
+#define GNC_DBI_PROVIDER_MYSQL (&provider_mysql)
+#define GNC_DBI_PROVIDER_PGSQL (&provider_pgsql)
+
struct GncDbiBackend_struct
{
GncSqlBackend sql_be;
@@ -825,7 +857,7 @@
GncSqlConnection base;
dbi_conn conn;
- gint provider;
+ provider_functions_t* provider;
} GncDbiSqlConnection;
static void
@@ -992,46 +1024,66 @@
return "";
}
} else {
- PERR( "Unknown provider: %d\n", dbi_conn->provider );
+ PERR( "Unknown provider type\n" );
return "";
}
}
-static void
-add_table_column( GString* ddl, const GncSqlColumnInfo* info )
+static gchar*
+conn_create_table_ddl_sqlite3( GncSqlConnection* conn,
+ const gchar* table_name,
+ const GList* col_info_list )
{
- gchar* buf;
- GError* error = NULL;
- gboolean ok;
+ GString* ddl;
+ const GList* list_node;
+ guint col_num;
+ gchar* ddl_result;
- g_return_if_fail( ddl != NULL );
- g_return_if_fail( info != NULL );
+ g_return_val_if_fail( conn != NULL, NULL );
+ g_return_val_if_fail( table_name != NULL, NULL );
+ g_return_val_if_fail( col_info_list != NULL, NULL );
+
+ ddl = g_string_new( "" );
+ g_string_printf( ddl, "CREATE TABLE %s (", table_name );
+ for( list_node = col_info_list, col_num = 0; list_node != NULL;
+ list_node = list_node->next, col_num++ ) {
+ GncSqlColumnInfo* info = (GncSqlColumnInfo*)(list_node->data);
- g_string_append_printf( ddl, "%s %s", info->name, info->type_name );
- if( info->size != 0 ) {
- g_string_append_printf( ddl, "(%d)", info->size );
+ if( col_num != 0 ) {
+ g_string_append( ddl, ", " );
+ }
+ g_string_append_printf( ddl, "%s %s", info->name,
+ gnc_sql_connection_get_column_type_name( conn, info->type, info->size ) );
+ if( info->size != 0 ) {
+ g_string_append_printf( ddl, "(%d)", info->size );
+ }
+ if( info->is_primary_key ) {
+ g_string_append( ddl, " PRIMARY KEY" );
+ }
+ if( !info->null_allowed ) {
+ g_string_append( ddl, " NOT NULL" );
+ }
}
- if( info->is_primary_key ) {
- g_string_append( ddl, " PRIMARY KEY" );
- }
- if( !info->null_allowed ) {
- g_string_append( ddl, " NOT NULL" );
- }
+ g_string_append( ddl, ")" );
+
+ ddl_result = ddl->str;
+ g_string_free( ddl, FALSE );
+
+ return ddl_result;
}
-static gboolean
-conn_create_table( GncSqlConnection* conn, const gchar* table_name,
+static gchar*
+conn_create_table_ddl_mysql( GncSqlConnection* conn, const gchar* table_name,
const GList* col_info_list )
{
- GncDbiSqlConnection* dbi_conn = (GncDbiSqlConnection*)conn;
GString* ddl;
const GList* list_node;
guint col_num;
- dbi_result result;
+ gchar* ddl_result;
- g_return_val_if_fail( conn != NULL, FALSE );
- g_return_val_if_fail( table_name != NULL, FALSE );
- g_return_val_if_fail( col_info_list != NULL, FALSE );
+ g_return_val_if_fail( conn != NULL, NULL );
+ g_return_val_if_fail( table_name != NULL, NULL );
+ g_return_val_if_fail( col_info_list != NULL, NULL );
ddl = g_string_new( "" );
g_string_printf( ddl, "CREATE TABLE %s (", table_name );
@@ -1042,15 +1094,100 @@
if( col_num != 0 ) {
g_string_append( ddl, ", " );
}
- add_table_column( ddl, info );
+ g_string_append_printf( ddl, "%s %s", info->name,
+ gnc_sql_connection_get_column_type_name( conn, info->type, info->size ) );
+ if( info->size != 0 ) {
+ g_string_append_printf( ddl, "(%d)", info->size );
+ }
+ if( info->is_unicode ) {
+ g_string_append( ddl, " CHARACTER SET utf8" );
+ }
+ if( info->is_primary_key ) {
+ g_string_append( ddl, " PRIMARY KEY" );
+ }
+ if( !info->null_allowed ) {
+ g_string_append( ddl, " NOT NULL" );
+ }
}
g_string_append( ddl, ")" );
- DEBUG( "SQL: %s\n", ddl->str );
- result = dbi_conn_query( dbi_conn->conn, ddl->str );
- dbi_result_free( result );
- g_string_free( ddl, TRUE );
+ ddl_result = ddl->str;
+ g_string_free( ddl, FALSE );
+ return ddl_result;
+}
+
+static gchar*
+conn_create_table_ddl_pgsql( GncSqlConnection* conn, const gchar* table_name,
+ const GList* col_info_list )
+{
+ GString* ddl;
+ const GList* list_node;
+ guint col_num;
+ gchar* ddl_result;
+ gboolean is_unicode = FALSE;
+
+ g_return_val_if_fail( conn != NULL, NULL );
+ g_return_val_if_fail( table_name != NULL, NULL );
+ g_return_val_if_fail( col_info_list != NULL, NULL );
+
+ ddl = g_string_new( "" );
+ g_string_printf( ddl, "CREATE TABLE %s (", table_name );
+ for( list_node = col_info_list, col_num = 0; list_node != NULL;
+ list_node = list_node->next, col_num++ ) {
+ GncSqlColumnInfo* info = (GncSqlColumnInfo*)(list_node->data);
+
+ if( col_num != 0 ) {
+ g_string_append( ddl, ", " );
+ }
+ g_string_append_printf( ddl, "%s %s", info->name,
+ gnc_sql_connection_get_column_type_name( conn, info->type, info->size ) );
+ if( info->size != 0 ) {
+ g_string_append_printf( ddl, "(%d)", info->size );
+ }
+ if( info->is_primary_key ) {
+ g_string_append( ddl, " PRIMARY KEY" );
+ }
+ if( !info->null_allowed ) {
+ g_string_append( ddl, " NOT NULL" );
+ }
+ is_unicode = is_unicode || info->is_unicode;
+ }
+ g_string_append( ddl, ")" );
+ if( is_unicode ) {
+ }
+
+ ddl_result = ddl->str;
+ g_string_free( ddl, FALSE );
+
+ return ddl_result;
+}
+
+static gboolean
+conn_create_table( GncSqlConnection* conn, const gchar* table_name,
+ const GList* col_info_list )
+{
+ GncDbiSqlConnection* dbi_conn = (GncDbiSqlConnection*)conn;
+ gchar* ddl;
+ const GList* list_node;
+ guint col_num;
+ dbi_result result;
+
+ g_return_val_if_fail( conn != NULL, FALSE );
+ g_return_val_if_fail( table_name != NULL, FALSE );
+ g_return_val_if_fail( col_info_list != NULL, FALSE );
+
+
+ ddl = dbi_conn->provider->create_table_ddl( conn, table_name,
+ col_info_list );
+ if( ddl != NULL ) {
+ DEBUG( "SQL: %s\n", ddl );
+ result = dbi_conn_query( dbi_conn->conn, ddl );
+ dbi_result_free( result );
+ } else {
+ return FALSE;
+ }
+
return TRUE;
}
@@ -1156,7 +1293,7 @@
}
static GncSqlConnection*
-create_dbi_connection( gint provider, dbi_conn conn )
+create_dbi_connection( provider_functions_t* provider, dbi_conn conn )
{
GncDbiSqlConnection* dbi_conn;
Modified: gnucash/trunk/src/backend/sql/gnc-backend-sql.c
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-backend-sql.c 2008-12-15 23:57:39 UTC (rev 17779)
+++ gnucash/trunk/src/backend/sql/gnc-backend-sql.c 2008-12-20 03:06:17 UTC (rev 17780)
@@ -941,17 +941,18 @@
}
static GncSqlColumnInfo*
-create_column_info( const GncSqlColumnTableEntry* table_row, const gchar* type,
- gint size )
+create_column_info( const GncSqlColumnTableEntry* table_row, GType type,
+ gint size, gboolean is_unicode )
{
GncSqlColumnInfo* info;
info = g_new0( GncSqlColumnInfo, 1 );
info->name = table_row->col_name;
- info->type_name = type;
+ info->type = type;
info->size = size;
info->is_primary_key = (table_row->flags & COL_PKEY) ? TRUE : FALSE;
info->null_allowed = (table_row->flags & COL_NNUL) ? FALSE : TRUE;
+ info->is_unicode = is_unicode;
return info;
}
@@ -993,10 +994,7 @@
g_return_if_fail( table_row != NULL );
g_return_if_fail( pList != NULL );
- info = create_column_info( table_row,
- gnc_sql_connection_get_column_type_name( be->conn,
- G_TYPE_STRING, table_row->size ),
- table_row->size );
+ info = create_column_info( table_row, G_TYPE_STRING, table_row->size, TRUE );
*pList = g_list_append( *pList, info );
}
@@ -1078,10 +1076,7 @@
g_return_if_fail( table_row != NULL );
g_return_if_fail( pList != NULL );
- info = create_column_info( table_row,
- gnc_sql_connection_get_column_type_name( be->conn,
- G_TYPE_INT, table_row->size ),
- 0 );
+ info = create_column_info( table_row, G_TYPE_INT, 0, FALSE );
*pList = g_list_append( *pList, info );
}
@@ -1161,10 +1156,7 @@
g_return_if_fail( table_row != NULL );
g_return_if_fail( pList != NULL );
- info = create_column_info( table_row,
- gnc_sql_connection_get_column_type_name( be->conn,
- G_TYPE_INT, table_row->size ),
- 0 );
+ info = create_column_info( table_row, G_TYPE_INT, 0, FALSE );
*pList = g_list_append( *pList, info );
}
@@ -1238,10 +1230,7 @@
g_return_if_fail( table_row != NULL );
g_return_if_fail( pList != NULL );
- info = create_column_info( table_row,
- gnc_sql_connection_get_column_type_name( be->conn,
- G_TYPE_INT64, table_row->size ),
- 0 );
+ info = create_column_info( table_row, G_TYPE_INT64, 0, FALSE );
*pList = g_list_append( *pList, info );
}
@@ -1316,10 +1305,7 @@
g_return_if_fail( table_row != NULL );
g_return_if_fail( pList != NULL );
- info = create_column_info( table_row,
- gnc_sql_connection_get_column_type_name( be->conn,
- G_TYPE_DOUBLE, table_row->size ),
- 0 );
+ info = create_column_info( table_row, G_TYPE_DOUBLE, 0, FALSE );
*pList = g_list_append( *pList, info );
}
@@ -1398,7 +1384,7 @@
g_return_if_fail( table_row != NULL );
g_return_if_fail( pList != NULL );
- info = create_column_info( table_row, "CHAR", GUID_ENCODING_LENGTH );
+ info = create_column_info( table_row, G_TYPE_STRING, GUID_ENCODING_LENGTH, FALSE );
*pList = g_list_append( *pList, info );
}
@@ -1539,7 +1525,7 @@
g_return_if_fail( table_row != NULL );
g_return_if_fail( pList != NULL );
- info = create_column_info( table_row, "CHAR", TIMESPEC_COL_SIZE );
+ info = create_column_info( table_row, G_TYPE_STRING, TIMESPEC_COL_SIZE, FALSE );
*pList = g_list_append( *pList, info );
}
@@ -1642,7 +1628,7 @@
g_return_if_fail( table_row != NULL );
g_return_if_fail( pList != NULL );
- info = create_column_info( table_row, "CHAR", DATE_COL_SIZE );
+ info = create_column_info( table_row, G_TYPE_STRING, DATE_COL_SIZE, FALSE );
*pList = g_list_append( *pList, info );
}
@@ -1747,10 +1733,10 @@
buf = g_strdup_printf( "%s_%s", table_row->col_name, subtable_row->col_name );
info = g_new0( GncSqlColumnInfo, 1 );
info->name = buf;
- info->type_name = gnc_sql_connection_get_column_type_name( be->conn,
- G_TYPE_INT64, table_row->size );
+ info->type = G_TYPE_INT64;
info->is_primary_key = (table_row->flags & COL_PKEY) ? TRUE : FALSE;
info->null_allowed = (table_row->flags & COL_NNUL) ? FALSE : TRUE;
+ info->is_unicode = FALSE;
*pList = g_list_append( *pList, info );
}
}
Modified: gnucash/trunk/src/backend/sql/gnc-backend-sql.h
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-backend-sql.h 2008-12-15 23:57:39 UTC (rev 17779)
+++ gnucash/trunk/src/backend/sql/gnc-backend-sql.h 2008-12-20 03:06:17 UTC (rev 17780)
@@ -280,11 +280,12 @@
* a column in a table.
*/
typedef struct {
- const gchar* name; /**< Column name */
- const gchar* type_name; /**< Column SQL type name */
- gint size; /**< Column size (string types) */
- gboolean is_primary_key;
- gboolean null_allowed;
+ 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_primary_key; /**< Column is the primary key */
+ gboolean null_allowed; /**< Column allows NULL values */
} GncSqlColumnInfo;
// Type for conversion of db row to object.
Modified: gnucash/trunk/src/business/business-core/sql/gnc-address-sql.c
===================================================================
--- gnucash/trunk/src/business/business-core/sql/gnc-address-sql.c 2008-12-15 23:57:39 UTC (rev 17779)
+++ gnucash/trunk/src/business/business-core/sql/gnc-address-sql.c 2008-12-20 03:06:17 UTC (rev 17780)
@@ -121,11 +121,11 @@
buf = g_strdup_printf( "%s_%s", table_row->col_name, subtable_row->col_name );
info = g_new0( GncSqlColumnInfo, 1 );
info->name = buf;
- info->type_name = gnc_sql_connection_get_column_type_name( be->conn,
- G_TYPE_STRING, subtable_row->size );
+ info->type = G_TYPE_STRING;
+ info->size = subtable_row->size;
info->is_primary_key = (table_row->flags & COL_PKEY) ? TRUE : FALSE;
- info->size = subtable_row->size;
info->null_allowed = (table_row->flags & COL_NNUL) ? FALSE : TRUE;
+ info->is_unicode = TRUE;
*pList = g_list_append( *pList, info );
}
}
Modified: gnucash/trunk/src/business/business-core/sql/gnc-owner-sql.c
===================================================================
--- gnucash/trunk/src/business/business-core/sql/gnc-owner-sql.c 2008-12-15 23:57:39 UTC (rev 17779)
+++ gnucash/trunk/src/business/business-core/sql/gnc-owner-sql.c 2008-12-20 03:06:17 UTC (rev 17780)
@@ -166,21 +166,21 @@
buf = g_strdup_printf( "%s_type", table_row->col_name );
info = g_new0( GncSqlColumnInfo, 1 );
info->name = buf;
- info->type_name = gnc_sql_connection_get_column_type_name( be->conn,
- G_TYPE_INT, table_row->size );
+ info->type = G_TYPE_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;
+ info->is_unicode = FALSE;
*pList = g_list_append( *pList, info );
buf = g_strdup_printf( "%s_guid", table_row->col_name );
info = g_new0( GncSqlColumnInfo, 1 );
info->name = buf;
- info->type_name = gnc_sql_connection_get_column_type_name( be->conn,
- G_TYPE_STRING, GUID_ENCODING_LENGTH );
+ info->type = G_TYPE_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;
+ info->is_unicode = FALSE;
*pList = g_list_append( *pList, info );
}
More information about the gnucash-changes
mailing list