Please understand that the problem boils down to this: Why can't I CREATE TABLE t(i BIGINT PRIMARY KEY) and have it autoincrement?
What internal mechanisms am I breaking by defining it like this? This would make the "reverse trip" easier surely? Well it would for me anyway (It allows me to easily import into mysql without writing an "edit program", mysql understands TEXT too). My patch works for me, so i can go with that, I'm just unsure about the reasonings behind the autoincrement restriction. Which is just a syntax parsing issue in the end. Or am I demanding too much? regards, nat Quoting "John Stanton" <[email protected]>: > You are still missing something. Apply some deeper thought to the > concepts behind Sqlite and the elegance will become clear. At run time > the Sqlite programmer has access to the declared type and the actual > storage type of the data. An API layer between the Sqlite API and the > application can resolve any data format issues. > > Note that Sqlite maps to commonly used scripting systems seamlessly. > Transferring data from somethiong like Mysql/DB2/Oracle/PostreSQL etc is > very simple. Making the reverse trip may not be so simple and in > general would require some form of edit program to transform TEXT > columns into the typed columns of say Mysql. If such a transfer is > important to you an application software layer can enforce the decclared > types of Sqlite. Otherwise use Mysql or preferably PostreSQL. > > Finally, Sqlite is simple and easy to use, hence the "lite". Let it > make life easy for you and don't fight it. You will be rewarded handsomely. > > Nathan Catlow wrote: >> Quoting "John Stanton" <[email protected]>: >> >> >>> 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. >>> >> >> Yes, I understand this, sqlite's lovelyness. >> >> >>> 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. >>> >> >> You've hit the nail on the head, I am trying to do exactly that! >> Providing an assist for an application level by explicitly declaring >> PRIMARY KEY BIGINT. This can then be transferred safely to another >> database (mysql in this example) even by doing the following; >> >> $ sqlite3 sqlitedb .d > out.sql >> shell> mysql mysqldb < out.sql >> >> All the autoincrement values can now be safely carried across because >> I could provide the assistance with an explicit BIGINT (This is >> already possible but *without* the autoincrement feature) >> >> I fail to understand the limiting of autoincrement to just INTEGER >> where there is no difference internally to sqlite between INTEGER and >> BIGINT etc. It is just an unnecessary restriction. >> >> Consider this 2 line patch which works against sqlite-3.6.2, it could >> be extended to all the other (external) integer types with no adverse >> effect. Am I missing something here? >> >> --- build.c.old 2008-12-14 20:53:19.000000000 +0000 >> +++ build.c 2008-12-14 16:29:03.000000000 +0000 >> @@ -1165,7 +1165,7 @@ >> if( iCol>=0 && iCol<pTab->nCol ){ >> zType = pTab->aCol[iCol].zType; >> } >> - if( zType && sqlite3StrICmp(zType, "INTEGER")==0 >> + if( zType && (sqlite3StrICmp(zType, "INTEGER")==0 || >> sqlite3StrICmp(zType, "BIGINT")==0) >> && sortOrder==SQLITE_SO_ASC ){ >> pTab->iPKey = iCol; >> pTab->keyConf = onError; >> @@ -1174,7 +1174,7 @@ >> }else if( autoInc ){ >> #ifndef SQLITE_OMIT_AUTOINCREMENT >> sqlite3ErrorMsg(pParse, "AUTOINCREMENT is only allowed on an " >> - "INTEGER PRIMARY KEY"); >> + "INTEGER or BIGINT PRIMARY KEY"); >> #endif >> }else{ >> sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0, 0, >> sortOrder, 0); >> >> Thank you for all your patience. >> >> regards, >> >> Nathan >> >> >>> 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 >>>> [email protected] >>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>>> >>>> >>> _______________________________________________ >>> sqlite-users mailing list >>> [email protected] >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >> >> >> >> ---------------------------------------------------------------- >> This message was sent using IMP, the Internet Messaging Program. >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
