You have not grasped the fundamental concept of typing used by Sqlite. It implements manifest typeing in the manner of scripting systems like Javascript etc. It has a clever feature which permits you to declare a type as anything you like and parses that name to decide on the underlying type as basically either text or numeric. It decides at run time how to store the data.
The declared type in Sqlite is in essence an assist for the programmer and is useful at application level to determine how to handle a column. For example a floating point number declared as DATE would be processed differently from one declared as ISOTOPE_COUNT. Ypu can rely on Sqlite storing large integer value as 64 bits and a small one using less space. The actual storage form for a particular column may vary from row to row according to decisions made by Sqlite at run time after it analyzes the data value. JS 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
