Quoting "D. Richard Hipp" <d...@hwaci.com>:
>
> 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.
I don't require the use of the AUTOINCREMENT keyword and it doesn't
appear in my CREATE TABLE statement.
When I transfer the table to mysql I appreciate the table will not
increment there, what I do want is mysql to reserve the correct size
and accept sqlite's autoincrement values for a primary key. Which
would be greatly simplified if i can just read in the dump from sqlite.
>
> 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.
So you are saying there is an internal difference between INTEGER and
BIGINT in sqlite? I thought they were just synonyms for INTEGER. It
seems from my patch it is very simple to make autoincrement work for
BIGINT as to sqlite it's just another way of saying INTEGER. I could
be wrong here, I haven't examined the code close enough.
I see no evidence to support the assertion that BIGINT PRIMARY KEY !=
INTEGER PRIMARY KEY. I can JOIN tables on BIGINT and INTEGER because
sqlite regards them as the same INTEGER type, sqlite just doesn't
care. This is great, until it comes to autoincrementing then it's
unusually fussy.
It's like you can use BIGINT and INTEGER interchangeably everywhere
except when it comes to incrementing. Nobody seems to have a valid
reasoning behind this except "It's always been that way". Which is
cool if that's the only reason, if there is a technical reason why my
patch won't work please tell me. I want to know if it will 8 byte me
in the ass later :)
My path of least resistance is:
Go with the patch (easy)
Do my own autoincrement with re-usable ids (more coding)
regards,
Nathan
>>
>>
>> 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
>
----------------------------------------------------------------
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
