r17927 - gnucash/trunk/src - Fix 64 bit integer handling. In an sqlite3 db, column types are basically ignored.
Phil Longstaff
plongstaff at cvs.gnucash.org
Mon Feb 16 11:22:19 EST 2009
Author: plongstaff
Date: 2009-02-16 11:22:18 -0500 (Mon, 16 Feb 2009)
New Revision: 17927
Trac: http://svn.gnucash.org/trac/changeset/17927
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-lots-sql.c
gnucash/trunk/src/backend/sql/gnc-price-sql.c
gnucash/trunk/src/backend/sql/gnc-slots-sql.c
gnucash/trunk/src/backend/sql/gnc-transaction-sql.c
gnucash/trunk/src/business/business-core/sql/gnc-bill-term-sql.c
gnucash/trunk/src/business/business-core/sql/gnc-customer-sql.c
gnucash/trunk/src/business/business-core/sql/gnc-employee-sql.c
gnucash/trunk/src/business/business-core/sql/gnc-entry-sql.c
gnucash/trunk/src/business/business-core/sql/gnc-invoice-sql.c
gnucash/trunk/src/business/business-core/sql/gnc-tax-table-sql.c
Log:
Fix 64 bit integer handling. In an sqlite3 db, column types are basically ignored.
However, libdbi uses the column type name to determine how big it thinks the integer
value is. Therefore, change all 64 bit integer values (including num/denom in numeric
values) to 'bigint'. Tables will be automatically upgraded when an sqlite3 file is
opened.
Modified: gnucash/trunk/src/backend/dbi/gnc-backend-dbi.c
===================================================================
--- gnucash/trunk/src/backend/dbi/gnc-backend-dbi.c 2009-02-16 01:08:57 UTC (rev 17926)
+++ gnucash/trunk/src/backend/dbi/gnc-backend-dbi.c 2009-02-16 16:22:18 UTC (rev 17927)
@@ -676,24 +676,13 @@
GncDbiSqlRow* dbi_row = (GncDbiSqlRow*)row;
gushort type;
GValue* value;
- long long v64;
- gint64 raw_int64_value;
- gint raw_int_value;
type = dbi_result_get_field_type( dbi_row->result, col_name );
value = g_new0( GValue, 1 );
switch( type ) {
case DBI_TYPE_INTEGER:
g_value_init( value, G_TYPE_INT64 );
-
- // FIXME: Bug in LibDBI: 64 bit int values returned incorrectly
- v64 = dbi_result_get_longlong( dbi_row->result, col_name );
- raw_int64_value = dbi_result_get_longlong( dbi_row->result, col_name );
- raw_int_value = dbi_result_get_int( dbi_row->result, col_name );
- if( raw_int_value < 0 && raw_int64_value > 0 ) {
- raw_int64_value = raw_int_value;
- }
- g_value_set_int64( value, raw_int64_value );
+ g_value_set_int64( value, dbi_result_get_longlong( dbi_row->result, col_name ) );
break;
case DBI_TYPE_DECIMAL:
g_value_init( value, G_TYPE_DOUBLE );
@@ -998,9 +987,11 @@
if( dbi_conn->provider == GNC_DBI_PROVIDER_SQLITE ) {
switch( type ) {
case G_TYPE_INT:
- case G_TYPE_INT64:
return "integer";
break;
+ case G_TYPE_INT64:
+ return "bigint";
+ break;
case G_TYPE_DOUBLE:
return "real";
break;
@@ -1014,9 +1005,11 @@
} else if( dbi_conn->provider == GNC_DBI_PROVIDER_MYSQL ) {
switch( type ) {
case G_TYPE_INT:
- case G_TYPE_INT64:
return "integer";
break;
+ case G_TYPE_INT64:
+ return "bigint";
+ break;
case G_TYPE_DOUBLE:
return "double";
break;
@@ -1030,9 +1023,11 @@
} else if( dbi_conn->provider == GNC_DBI_PROVIDER_PGSQL ) {
switch( type ) {
case G_TYPE_INT:
- case G_TYPE_INT64:
return "integer";
break;
+ case G_TYPE_INT64:
+ return "int8";
+ break;
case G_TYPE_DOUBLE:
return "double precision";
break;
Modified: gnucash/trunk/src/backend/sql/gnc-backend-sql.c
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-backend-sql.c 2009-02-16 01:08:57 UTC (rev 17926)
+++ gnucash/trunk/src/backend/sql/gnc-backend-sql.c 2009-02-16 16:22:18 UTC (rev 17927)
@@ -2405,6 +2405,12 @@
{
gboolean ok;
+ g_return_val_if_fail( be != NULL, FALSE );
+ g_return_val_if_fail( table_name != NULL, FALSE );
+ g_return_val_if_fail( col_table != NULL, FALSE );
+
+ DEBUG( "Creating %s table\n", table_name );
+
ok = do_create_table( be, table_name, col_table );
if( ok ) {
ok = gnc_sql_set_table_version( be, table_name, table_version );
@@ -2416,6 +2422,10 @@
gnc_sql_create_temp_table( const GncSqlBackend* be, const gchar* table_name,
const GncSqlColumnTableEntry* col_table )
{
+ g_return_val_if_fail( be != NULL, FALSE );
+ g_return_val_if_fail( table_name != NULL, FALSE );
+ g_return_val_if_fail( col_table != NULL, FALSE );
+
return do_create_table( be, table_name, col_table );
}
@@ -2454,6 +2464,36 @@
return GPOINTER_TO_INT(g_hash_table_lookup( be->versions, table_name ));
}
+/* Create a temporary table, copy the data from the old table, delete the
+ old table, then rename the new one. */
+void
+gnc_sql_upgrade_table( GncSqlBackend* be, const gchar* table_name,
+ const GncSqlColumnTableEntry* col_table )
+{
+ gchar* sql;
+ gchar* temp_table_name;
+ GncSqlStatement* stmt;
+
+ g_return_if_fail( be != NULL );
+ g_return_if_fail( table_name != NULL );
+ g_return_if_fail( col_table != NULL );
+
+ DEBUG( "Upgrading %s table\n", table_name );
+
+ temp_table_name = g_strdup_printf( "%s_new", table_name );
+ gnc_sql_create_temp_table( be, temp_table_name, col_table );
+ sql = g_strdup_printf( "INSERT INTO %s SELECT * FROM %s",
+ temp_table_name, table_name );
+ (void)gnc_sql_execute_nonselect_sql( be, sql );
+
+ sql = g_strdup_printf( "DROP TABLE %s", table_name );
+ (void)gnc_sql_execute_nonselect_sql( be, sql );
+
+ sql = g_strdup_printf( "ALTER TABLE %s RENAME TO %s", temp_table_name, table_name );
+ (void)gnc_sql_execute_nonselect_sql( be, sql );
+ g_free( temp_table_name );
+}
+
/* ================================================================= */
#define VERSION_TABLE_NAME "versions"
#define MAX_TABLE_NAME_LEN 50
Modified: gnucash/trunk/src/backend/sql/gnc-backend-sql.h
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-backend-sql.h 2009-02-16 01:08:57 UTC (rev 17926)
+++ gnucash/trunk/src/backend/sql/gnc-backend-sql.h 2009-02-16 16:22:18 UTC (rev 17927)
@@ -678,6 +678,19 @@
*/
gint64 gnc_sql_get_integer_value( const GValue* value );
+/**
+ * Upgrades a table to a new structure. The upgrade is done by creating a new table with
+ * the new structure, SELECTing the old data into the new table, deleting the old table,
+ * then renaming the new table. Therefore, this will only work if the new table structure
+ * is similar enough to the old table that the SELECT will work.
+ *
+ * @param be SQL backend
+ * @param table_name SQL table name
+ * @param col_table Column table
+ */
+void gnc_sql_upgrade_table( GncSqlBackend* be, const gchar* table_name,
+ const GncSqlColumnTableEntry* col_table );
+
void _retrieve_guid_( gpointer pObject, gpointer pValue );
gpointer gnc_sql_compile_query( QofBackend* pBEnd, QofQuery* pQuery );
Modified: gnucash/trunk/src/backend/sql/gnc-lots-sql.c
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-lots-sql.c 2009-02-16 01:08:57 UTC (rev 17926)
+++ gnucash/trunk/src/backend/sql/gnc-lots-sql.c 2009-02-16 16:22:18 UTC (rev 17927)
@@ -167,22 +167,8 @@
Create a temporary table, copy the data from the old table, delete the
old table, then rename the new one. */
- gchar* sql;
-#define TEMP_TABLE_NAME "lots_new"
- GncSqlStatement* stmt;
- gnc_sql_create_temp_table( be, TEMP_TABLE_NAME, col_table );
- sql = g_strdup_printf( "INSERT INTO %s SELECT * FROM %s",
- TEMP_TABLE_NAME, TABLE_NAME );
- (void)gnc_sql_execute_nonselect_sql( be, sql );
-
- sql = g_strdup_printf( "DROP TABLE %s", TABLE_NAME );
- (void)gnc_sql_execute_nonselect_sql( be, sql );
-
- sql = g_strdup_printf( "ALTER TABLE %s RENAME TO %s",
- TEMP_TABLE_NAME, TABLE_NAME );
- (void)gnc_sql_execute_nonselect_sql( be, sql );
-
+ gnc_sql_upgrade_table( be, TABLE_NAME, col_table );
gnc_sql_set_table_version( be, TABLE_NAME, TABLE_VERSION );
}
}
Modified: gnucash/trunk/src/backend/sql/gnc-price-sql.c
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-price-sql.c 2009-02-16 01:08:57 UTC (rev 17926)
+++ gnucash/trunk/src/backend/sql/gnc-price-sql.c 2009-02-16 16:22:18 UTC (rev 17927)
@@ -127,6 +127,10 @@
version = gnc_sql_get_table_version( be, TABLE_NAME );
if( version == 0 ) {
gnc_sql_create_table( be, TABLE_NAME, TABLE_VERSION, col_table );
+ } else if( version == 1 ) {
+ /* Upgrade 64 bit int handling */
+ gnc_sql_upgrade_table( be, TABLE_NAME, col_table );
+ gnc_sql_set_table_version( be, TABLE_NAME, TABLE_VERSION );
}
}
Modified: gnucash/trunk/src/backend/sql/gnc-slots-sql.c
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-slots-sql.c 2009-02-16 01:08:57 UTC (rev 17926)
+++ gnucash/trunk/src/backend/sql/gnc-slots-sql.c 2009-02-16 16:22:18 UTC (rev 17927)
@@ -40,7 +40,7 @@
static QofLogModule log_module = G_LOG_DOMAIN;
#define TABLE_NAME "slots"
-#define TABLE_VERSION 1
+#define TABLE_VERSION 2
typedef struct {
GncSqlBackend* be;
@@ -560,6 +560,10 @@
PERR( "Unable to create index: %s\n", error->message );
}
#endif
+ } else if( version == 1 ) {
+ /* Upgrade 64-bit int values to proper definition */
+ gnc_sql_upgrade_table( be, TABLE_NAME, col_table );
+ gnc_sql_set_table_version( be, TABLE_NAME, TABLE_VERSION );
}
}
Modified: gnucash/trunk/src/backend/sql/gnc-transaction-sql.c
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-transaction-sql.c 2009-02-16 01:08:57 UTC (rev 17926)
+++ gnucash/trunk/src/backend/sql/gnc-transaction-sql.c 2009-02-16 16:22:18 UTC (rev 17927)
@@ -506,11 +506,19 @@
version = gnc_sql_get_table_version( be, TRANSACTION_TABLE );
if( version == 0 ) {
gnc_sql_create_table( be, TRANSACTION_TABLE, TX_TABLE_VERSION, tx_col_table );
+ } else if( version == 1 ) {
+ /* Upgrade 64 bit int handling */
+ gnc_sql_upgrade_table( be, TRANSACTION_TABLE, tx_col_table );
+ gnc_sql_set_table_version( be, TRANSACTION_TABLE, TX_TABLE_VERSION );
}
version = gnc_sql_get_table_version( be, SPLIT_TABLE );
if( version == 0 ) {
gnc_sql_create_table( be, SPLIT_TABLE, SPLIT_TABLE_VERSION, split_col_table );
+ } else if( version == 1 ) {
+ /* Upgrade 64 bit int handling */
+ gnc_sql_upgrade_table( be, SPLIT_TABLE, split_col_table );
+ gnc_sql_set_table_version( be, SPLIT_TABLE, SPLIT_TABLE_VERSION );
}
}
/* ================================================================= */
Modified: gnucash/trunk/src/business/business-core/sql/gnc-bill-term-sql.c
===================================================================
--- gnucash/trunk/src/business/business-core/sql/gnc-bill-term-sql.c 2009-02-16 01:08:57 UTC (rev 17926)
+++ gnucash/trunk/src/business/business-core/sql/gnc-bill-term-sql.c 2009-02-16 16:22:18 UTC (rev 17927)
@@ -182,7 +182,11 @@
version = gnc_sql_get_table_version( be, TABLE_NAME );
if( version == 0 ) {
gnc_sql_create_table( be, TABLE_NAME, TABLE_VERSION, col_table );
- }
+ } else if( version == 1 ) {
+ /* Upgrade 64 bit int handling */
+ gnc_sql_upgrade_table( be, TABLE_NAME, col_table );
+ gnc_sql_set_table_version( be, TABLE_NAME, TABLE_VERSION );
+ }
}
/* ================================================================= */
Modified: gnucash/trunk/src/business/business-core/sql/gnc-customer-sql.c
===================================================================
--- gnucash/trunk/src/business/business-core/sql/gnc-customer-sql.c 2009-02-16 01:08:57 UTC (rev 17926)
+++ gnucash/trunk/src/business/business-core/sql/gnc-customer-sql.c 2009-02-16 16:22:18 UTC (rev 17927)
@@ -143,6 +143,10 @@
version = gnc_sql_get_table_version( be, TABLE_NAME );
if( version == 0 ) {
gnc_sql_create_table( be, TABLE_NAME, TABLE_VERSION, col_table );
+ } else if( version == 1 ) {
+ /* Upgrade 64 bit int handling */
+ gnc_sql_upgrade_table( be, TABLE_NAME, col_table );
+ gnc_sql_set_table_version( be, TABLE_NAME, TABLE_VERSION );
}
}
Modified: gnucash/trunk/src/business/business-core/sql/gnc-employee-sql.c
===================================================================
--- gnucash/trunk/src/business/business-core/sql/gnc-employee-sql.c 2009-02-16 01:08:57 UTC (rev 17926)
+++ gnucash/trunk/src/business/business-core/sql/gnc-employee-sql.c 2009-02-16 16:22:18 UTC (rev 17927)
@@ -140,6 +140,10 @@
version = gnc_sql_get_table_version( be, TABLE_NAME );
if( version == 0 ) {
gnc_sql_create_table( be, TABLE_NAME, TABLE_VERSION, col_table );
+ } else if( version == 1 ) {
+ /* Upgrade 64 bit int handling */
+ gnc_sql_upgrade_table( be, TABLE_NAME, col_table );
+ gnc_sql_set_table_version( be, TABLE_NAME, TABLE_VERSION );
}
}
Modified: gnucash/trunk/src/business/business-core/sql/gnc-entry-sql.c
===================================================================
--- gnucash/trunk/src/business/business-core/sql/gnc-entry-sql.c 2009-02-16 01:08:57 UTC (rev 17926)
+++ gnucash/trunk/src/business/business-core/sql/gnc-entry-sql.c 2009-02-16 16:22:18 UTC (rev 17927)
@@ -163,6 +163,10 @@
version = gnc_sql_get_table_version( be, TABLE_NAME );
if( version == 0 ) {
gnc_sql_create_table( be, TABLE_NAME, TABLE_VERSION, col_table );
+ } else if( version == 1 ) {
+ /* Upgrade 64 bit int handling */
+ gnc_sql_upgrade_table( be, TABLE_NAME, col_table );
+ gnc_sql_set_table_version( be, TABLE_NAME, TABLE_VERSION );
}
}
Modified: gnucash/trunk/src/business/business-core/sql/gnc-invoice-sql.c
===================================================================
--- gnucash/trunk/src/business/business-core/sql/gnc-invoice-sql.c 2009-02-16 01:08:57 UTC (rev 17926)
+++ gnucash/trunk/src/business/business-core/sql/gnc-invoice-sql.c 2009-02-16 16:22:18 UTC (rev 17927)
@@ -147,6 +147,10 @@
version = gnc_sql_get_table_version( be, TABLE_NAME );
if( version == 0 ) {
gnc_sql_create_table( be, TABLE_NAME, TABLE_VERSION, col_table );
+ } else if( version == 1 ) {
+ /* Upgrade 64 bit int handling */
+ gnc_sql_upgrade_table( be, TABLE_NAME, col_table );
+ gnc_sql_set_table_version( be, TABLE_NAME, TABLE_VERSION );
}
}
Modified: gnucash/trunk/src/business/business-core/sql/gnc-tax-table-sql.c
===================================================================
--- gnucash/trunk/src/business/business-core/sql/gnc-tax-table-sql.c 2009-02-16 01:08:57 UTC (rev 17926)
+++ gnucash/trunk/src/business/business-core/sql/gnc-tax-table-sql.c 2009-02-16 16:22:18 UTC (rev 17927)
@@ -259,11 +259,19 @@
version = gnc_sql_get_table_version( be, TT_TABLE_NAME );
if( version == 0 ) {
gnc_sql_create_table( be, TT_TABLE_NAME, TT_TABLE_VERSION, tt_col_table );
+ } else if( version == 1 ) {
+ /* Upgrade 64 bit int handling */
+ gnc_sql_upgrade_table( be, TT_TABLE_NAME, tt_col_table );
+ gnc_sql_set_table_version( be, TT_TABLE_NAME, TT_TABLE_VERSION );
}
version = gnc_sql_get_table_version( be, TTENTRIES_TABLE_NAME );
if( version == 0 ) {
gnc_sql_create_table( be, TTENTRIES_TABLE_NAME, TTENTRIES_TABLE_VERSION, ttentries_col_table );
+ } else if( version == 1 ) {
+ /* Upgrade 64 bit int handling */
+ gnc_sql_upgrade_table( be, TTENTRIES_TABLE_NAME, ttentries_col_table );
+ gnc_sql_set_table_version( be, TTENTRIES_TABLE_NAME, TTENTRIES_TABLE_VERSION );
}
}
More information about the gnucash-changes
mailing list