[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY
From:       "Nathan Catlow" <nat () uncon ! org>
Date:       2008-12-15 11:24:30
Message-ID: 20081215112432.5D3FB11C23 () sqlite ! org
[Download RAW message or body]


Quoting "Jay A. Kreibich" <jay@kreibi.ch>:

> 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
>

Hi,

I am perfectly aware of the size of INTEGERS on differing platforms  
and of sqlite's typeless nature.

Can you tell me why you can even specify BIGINT to sqlite then? Or  
SMALLINT or any other datatype? What is the difference between INTEGER  
and any other type? none of course!

sqlite> CREATE TABLE t(i SMALLINT, t TEXT);
sqlite> INSERT INTO t(i,t) VALUES(9999999999999999, 'test');
sqlite> SELECT * FROM t;
9999999999999999|test

But there is a difference, autoincrement ONLY works with "INTEGER",  
why? sqlite quite rightly allows the code above due to all types being  
treated the same, but all of a sudden starts getting all fussy when I  
want to autoincrement a BIGINT. If ROWID == INTEGER then it must match  
the others too ROWID == INTEGER == BIGINT == SMALLINT sqlite just  
shouldn't care.

The point about "cross platform SQL" and using a library like libdbi,  
is that it tries to ensure that a particular length of data can fit in  
all makes of SQL.

My code drives the databases not the other way around, so if *I*  
decide an integer is only 32bits, then I don't give a damn if  
sqlite/oracle or whatever wastes space be putting it in a 64bit space.  
The ultimate goal is running the same code on all DB engines. The goal  
is not to take an arbitrary database and expect libdbi to read it  
efficiently or even correctly.

The only thing I have no control over is when using the autoincrement  
feature, as this is driven by sqlite, and will always attempt to use  
the full 64bit space. I need to know this to ensure the correct memory  
is allocated.

I completely accept your point about assumption, but there has to be a  
compromise on allocating 64bits everywhere, which is inefficient on  
small systems. I want to raise this point with the libdbi developers.  
Their code is broken, I know that, but an acceptable compromise is  
nearly there. I just need to determine that a field is a  
autoincrementing PRIMARY KEY or be able to explicitly state BIGINT  
PRIMARY KEY.

I have one question, lets forget about the argument about types, it is  
a red herring.

Is there a way through sqlite API or PRAGMA to determine a field is an  
autoincrementing INTEGER PRIMARY KEY? The only way i've found is to  
parse the create table statement from sqlite_master which is cludgy.

PRAGMA table_info(t);
PRAGMA index_list(t);

Both those give me no love.

I suppose if i wrote my own autoincrement all this would go away as I  
could ensure its length.

regards,

Nathan



----------------------------------------------------------------
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
[prev in list] [next in list] [prev in thread] [next in thread]