Where did you get your pre-occupation with BIGINT? Sqlite handles
INTEGERS and makes them up to 64 bits as necessary. An INTEGER primary
key will autoincrement,.
Sql;ite lets you introduce a type BIGINT as a declared type, but makes
its own decision as to underlying type.
Nathan Catlow wrote:
> 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" <jo...@viacognis.com>:
>
>
>> 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" <jo...@viacognis.com>:
>>>
>>>
>>>
>>>> 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
>>>>> 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
>>>>
>>>>
>>>>
>>>
>>> ----------------------------------------------------------------
>>> 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
>>
>>
>
>
>
> ----------------------------------------------------------------
> 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
