On Dec 15, 2008, at 10:27 AM, 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?
First off, AUTOINCREMENT means something different to SQLite than it
does to MySQL. Do not be confused by the similarity in names.
Secondly, AUTOINCREMENT in SQLite only works on an INTEGER PRIMARY
KEY, not on any other kind of primary key or on any non-primary-key
field. BIGINT PRIMARY KEY is not an INTEGER PRIMARY KEY and so
AUTOINCREMENT won't work on it.
>
>
> 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
D. Richard Hipp
d...@hwaci.com
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
