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
