I am trying to use libdbi + sqlite for my project. It is impossible
for libdbi to determine the PRIMARY KEY type (64bit) using PRAGMA
table_info(). This has completely crippled my project that relies on
autoincrement.
As the PRIMARY KEY is an alias to ROWID (64bit), the data should be
returned as a 64bit integer. Unfortunately the PRAGMA table_info()
command returns a type of INTEGER (32 bit in at least mysql). It is
also impossible to help by defining primary key as BIGINT PRIMARY KEY,
as the field does not then autoincrement in sqlite.
sqlite> CREATE TABLE test(id BIGINT PRIMARY KEY AUTOINCREMENT); SQL
error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
As the PRIMARY KEY is really ROWID (64bit), shouldn't I be able to
define it as BIGINT and still have it autoincrement? This would then
mean it will be returned via the PRAGMA statement as BIGINT and DB
independant libraries can return the correct datatype and also be SQL
compatible with other DB engines.
Another example would be to move data from sqlite to mysql, because
the datatype INTEGER PRIMARY KEY is only 32bits on mysql, if I were to
dump the database from sqlite the CREATE TABLE test(id INTEGER PRIMARY
KEY) would only be able to hold a 32bit integer in mysql where the
data in sqlite is actually 64bits (ROWID) resulting in an overflow.
The correct definition is actually CREATE TABLE test(id BIGINT PRIMARY
KEY AUTOINCREMENT), this would be a simple patch to sqlite allow that
statement and everything would be groovy.
So to summarise:
CREATE TABLE test(id BIGINT PRIMARY KEY) - Should internally work
exactly the same as INTEGER PRIMARY KEY where rowid == id and can be
reused.
CREATE TABLE test(id BIGINT PRIMARY KEY AUTOINCREMENT) - Should
internally work exactly the same as INTEGER PRIMARY KEY but rowid ==
id and cannot be re-used.
And the output from PRAGMA table_info(test) would be completely correct;
PRAGMA table_info(test);
0|id|BIGINT|0||1
By amending sqlite3.c (sqlite-3.6.6.2) line 59780 to read:
if( zType && (sqlite3StrICmp(zType, "INTEGER")==0 ||
sqlite3StrICmp(zType, "BIGINT")==0)
&& sortOrder==SQLITE_SO_ASC ){
I was able to get sqlite to accept the AUTOINCREMENT command on
BIGINT, but I don't know how to make it work for re-usable ROWIDS.
This is a real showstopper for me, I want to use sqlite, but have an
compatible way of supporting other db engines.
I appreciate your comments.
regards
nat
----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
