On Sun, Dec 14, 2008 at 08:25:02PM +0000, Nathan Catlow scratched on the wall:
> 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(). Why impossible? The type in INTEGER, just as returned. > As the PRIMARY KEY is an alias to ROWID (64bit), The PRIMARY KEY is *ONLY* an alias for ROWID if it is defined as "INTEGER PRIMARY KEY". You can define any arbitrary PRIMARY KEY, including a multi-column key. > the data should be returned as a 64bit integer. And it is. All INTEGER valued types in SQLite are capable of holding a 64 bit value. > Unfortunately the PRAGMA table_info() > command returns a type of INTEGER (32 bit in at least mysql). Yes. It returns INTEGER because that's what it is-- at least if you're doing a ROWID alias with AUTOINCREMENT. What MySQL might or might not define INTEGER to be is irrelevant. The SQL standard doesn't have specs for how many bits different types of numbers take. If you, or any libraries or software you are using makes such assumptions, you're going to have a lot of headaches. "INTEGER" in Oracle, for example, defines a 38 digit (base-10) integer. That's a bit more than 120 bits. On the flip side, I *wouldn't* assume a ROWID is a 64 bit integer. That happens to be true in SQLite, but there is nothing that says that has to be true. Again, it isn't true in Oracle. > It is > also impossible to help by defining primary key as BIGINT PRIMARY KEY, > as the field does not then autoincrement in sqlite. Right. The docs are quite specific. You can define a BIGINT PRIMARY KEY, but since it is not "INTEGER PRIMARY KEY" it won't become a ROWID alias. AUTOINCREMENT only works on ROWID columns. > 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. The fault is in the libraries for making rash and incorrect assumptions about types and sizes, not in anything SQLite is doing. Anything that assumes "INTEGER" in SQL is a 32 bit number (or that any column marked ROWID must be a 64 bit integer) is making poor and incorrect assumptions. > 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. Yes. You'll find similar problems with nearly any other numeric type in SQL. This is not C or C++ (and even C does not define a specific size for "int"...). Welcome to the wonderful world of cross-platform SQL. > This is a real showstopper for me, I want to use sqlite, but have an > compatible way of supporting other db engines. If compatibility is the issue, you might have a look at the SQL standards and what assumptions you can actually make about types, sizes, and value domains. It seems you, or the software you're using, has a lot of misconceptions about the type systems used in different flavors of SQL. In short, you can't assume much of anything. You definitely can't assume something as generic as "INTEGER" has a specific domain, like a 32-bit number, or that "ROWID" is a 64-bit integer. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
