Bugs item #2421761, was opened at 2008-12-12 16:27
Message generated for change (Comment added) made by nat__
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=512945&aid=2421761&group_id=65979
Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: None
Group: None
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Nathan Catlow (nat__)
Assigned to: Markus Hoenicka (mhoenicka)
Summary: sqlite3 - primary index type confusion
Initial Comment:
libdbi thinks primary index is an INTEGER and returns a long. Documentation
states "Do not forget to use dbi_result_get_longlong() or
dbi_result_get_ulonglong to retrieve values from columns declared as INTEGER
PRIMARY KEY."
I think sqlite has changed/doesn't work, looking briefly at dbd_sqlite3.c it
seems to rely on PRAGMA table_info() and look for "INTEGER PRIMARY KEY". sqlite
seems to only return INTEGER.
$ sqlite3 testdb
SQLite version 3.6.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE test(id INTEGER PRIMARY KEY, int INTEGER, bigint BIGINT);
sqlite> PRAGMA table_info(test);
0|id|INTEGER|0||1
1|int|INTEGER|0||0
2|bigint|BIGINT|0||0
So dbd_sqlite3.c (_get_row_data) ends up setting data->d_long (line
dbd_sqlite3.c:1549).
But the call to dbi_get_ulonglong untimately returns from
dbi_result_get_longlong_idx (from libdbi) which returns data->d_longlong
(dbi_result.c:1004). This is a union (dbi_data_u).
On little endian this fudges though, but on big endian this is incorrect.
Getting a primary key of decimal 1 from a database;
Little Endian:
<--d_longlong--------->
<--d_long->
01 00 00 00 00 00 00 00 = decimal 1
Big Endian:
<--d_longlong--------->
<--d_long->
00 00 00 01 00 00 00 00 = decimal 4294967296
I'm not sure of the fix if you can't get sqlite to tell you what the primary
key field is properly.
maybe an api added to libdbi, dbi_result_get_primarykey() which ensures a long
long. (this would only be needed for databases like sqlite) but would be still
compatible for code moving to mysql for example.
regards,
nat
----------------------------------------------------------------------
>Comment By: Nathan Catlow (nat__)
Date: 2008-12-16 04:28
Message:
Hi Markus,
The bug only appears on Big Endian architecture, I am running linux
(SlugOS) on an NSLU2 box which is ARM BE.
To demonstrate the problem; patch some printf's into dbi_result.c and
dbd_sqlite3.c (version 0.8.3).
dell630m src # diff -up dbi_result.c.old dbi_result.c
--- dbi_result.c.old 2008-12-16 04:02:36.000000000 +0000
+++ dbi_result.c 2008-12-16 04:04:54.000000000 +0000
@@ -999,6 +999,7 @@ long long dbi_result_get_longlong_idx(db
case DBI_INTEGER_SIZE3:
case DBI_INTEGER_SIZE4:
case DBI_INTEGER_SIZE8:
+ printf("dbi_result_get_longlong_idx returning d_longlong\n");
return
RESULT->rows[RESULT->currowidx]->field_values[fieldidx].d_longlong;
default:
_error_handler(RESULT->conn, DBI_ERROR_BADTYPE);
dell630m sqlite3 # diff -up dbd_sqlite3.c.old dbd_sqlite3.c
--- dbd_sqlite3.c.old 2008-12-16 03:38:15.000000000 +0000
+++ dbd_sqlite3.c 2008-12-16 04:01:31.000000000 +0000
@@ -1020,8 +1020,10 @@ void _get_row_data(dbi_result_t *result,
data->d_short = (short) atol(raw); break;
case DBI_INTEGER_SIZE3:
case DBI_INTEGER_SIZE4:
+ printf("returning a long via data->d_long\n");
data->d_long = (int) atol(raw); break;
case DBI_INTEGER_SIZE8:
+ printf("returning a long long via data->d_longlong\n");
data->d_longlong = (long long) atoll(raw); break; /* hah, wonder
if that'll work */
default:
break;
This is the test code....
dell630m # cat ./test.c
#include <stdio.h>
#include <dbi/dbi.h>
int main(int argc, char *argv[]) {
unsigned long long id;
dbi_conn conn;
dbi_result result;
dbi_initialize("/usr/lib/dbd");
conn = dbi_conn_new("sqlite3");
dbi_conn_set_option(conn, "dbname", "testdb");
dbi_conn_set_option(conn, "sqlite3_dbdir", "./");
dbi_conn_connect(conn);
dbi_conn_query(conn, "CREATE TABLE t(i INTEGER PRIMARY KEY, v TEXT)");
dbi_conn_query(conn, "INSERT INTO t(v) VALUES ('test')");
result = dbi_conn_query(conn, "SELECT i FROM t WHERE v='test'");
if (dbi_result_first_row(result)) {
id = dbi_result_get_ulonglong(result, "i");
}
printf("Answer =%lld\n",id);
if (result) dbi_result_free(result);
return 0;
}
dell630m # gcc -o test test.c -ldbi
dell630m # ./test
returning a long via data->d_long
dbi_result_get_longlong_idx returning d_longlong
Answer =1
The problem is d_long and d_longlong belong to the same union.
dbd_sqlite3.c thinks the size is 32bit integer because sqlite has told it
so and stores the result in d_long. But dbi_result returns the field
d_longlong. The bug is invisible in little endian;
dbd_sqlite.c stores 01 00 00 00 in d_long (only 32 bits), dbi_result.c
returns 01 00 00 00 00 00 00 00 (64 bits), this is still decimal 1 in
little endian, lucky!!
In Big Endian;
dbd_sqlite.c stores 00 00 00 01 in d_long (only 32 bits), dbi_result.c
returns 00 00 00 01 00 00 00 00 (64 bits), this is not decimal 1 in Big
endian, oops!!
I'm sorry I can't get to the ARM box at the moment, but I hope this makes
it clearer.
regards,
nat
----------------------------------------------------------------------
Comment By: Markus Hoenicka (mhoenicka)
Date: 2008-12-15 23:29
Message:
I've attempted to reproduce this problem after following the discussion on
the SQLite list today. However, some debug lines added to test_dbi.c tell
me that both mysql and sqlite3 use 8-byte integers in the auto-incrementing
"id" column of the test table. Before implementing the obvious fix, I'd
like to be able to reproduce the problem, otherwise I can't be sure I *did*
fix it. Which OSes and which versions do you use? I've got FreeBSD 6.4,
SQLite 3.6.4 built from a port, and the cvs versions of libdbi and
libdbi-drivers.
----------------------------------------------------------------------
Comment By: Markus Hoenicka (mhoenicka)
Date: 2008-12-14 16:49
Message:
It's probably about time to add a few comments:
First of all, thanks for following up on this on the SQLite bug tracker.
The mismatch between INTEGER, INTEGER PRIMARY KEY, and BIGINT is documented
in the sqlite3 driver docs, but this doesn't make it less cumbersome. I
wish this was fixed upstream.
You've noticed that the dbi_result_get_FOO() functions do not always
return what they should. They do return values only if the column type as
reported by the database engine match the return type of the function. This
behaviour is intended. In some cases the dbi_result_get_as_longlong()
(available only in the cvs version at this time) function may serve as a
workaround in that it attempts to convert all sorts of input into a long
long integer. However, if the database engine reports an incorrect type
this won't get you far either.
The sqlite3_column_decltype() function is not of much help currently as
the driver does not use prepared statements. In order to use the latter,
we'd have to rewrite the driver and create the results table ourselves by
stepping through the results. This is doable but I currently lack the time
to tackle this.
regards,
Markus
----------------------------------------------------------------------
Comment By: Nathan Catlow (nat__)
Date: 2008-12-14 16:23
Message:
Ok, I've raised a ticket with sqlite. sqlite doesn't support an
autoincrement BIGINT primary key. Which is ridiculous as it really is ROWID
which is 64bit. So i am back to square one :(
http://www.sqlite.org/cvstrac/tktview?tn=3540
----------------------------------------------------------------------
Comment By: Nathan Catlow (nat__)
Date: 2008-12-14 10:47
Message:
Argh, error. By defining an INTEGER PRIMARY KEY, I have in fact made it an
INTEGER. Not really sure what sqlite does here, probably ignores the type
and continues to use a 64bit number. So after two days chasing down a bug
that in theory doesn't exist, annoying!
Perhaps to use libdbi in the way it was intended (to seamlessly move from
one db engine to another) the documentation should reflect that primary
keys in sqlite should be explicitly defined as bigint; CREATE TABLE test(id
BIGINT PRIMARY KEY) to allow get_ulonglong to work properly.
I learned;
dbi_result_get_ulonglong doesn't ensure you get a long long, but will only
fail on big endian processors.
Most of find_result_field_types can be replaced by calling;
http://www.sqlite.org/c3ref/column_decltype.html which returns the column
types from an sql statement.
If sqlite is compiled with -DSQLITE_ENABLE_COLUMN_METADATA, you can use
the API to return the types (but this is possibly not what libdbi needs)
but interesting;
http://www.sqlite.org/c3ref/column_database_name.html
Please update your documentation so nobody else experiences this pain.
Thanks for libdbi it's working well.
regards,
nat
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=512945&aid=2421761&group_id=65979
------------------------------------------------------------------------------
SF.Net email is Sponsored by MIX09, March 18-20, 2009 in Las Vegas, Nevada.
The future of the web can't happen without you. Join us at MIX09 to help
pave the way to the Next Web now. Learn more and register at
http://ad.doubleclick.net/clk;208669438;13503038;i?http://2009.visitmix.com/
_______________________________________________
Libdbi-drivers-devel mailing list
Libdbi-drivers-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/libdbi-drivers-devel
