On Wed, 7 May 2008, Joanne Pham wrote:
I was wondering what is the different in size between int(8) and INTEGER
datatype.
Thanks,
In the sense you are asking, there is no difference, but more importantly,
the question reflects a misunderstanding of how SQLite is designed. Most
database applcations use datatypes as part of the column definitions;
SQLite does not, it uses 'affinities' instead. In other database apps you
assign a datatype to a column; the database engine will then reject (or
convert) data which does not conform to the declared datatype.
In SQLite, any column can store any kind of data. When you create a table,
you are not creating 'datatypes, you are defining column 'affintities'.
Affinities govern the algorithms SQLite uses when processing inserts &
updates. This is clearly discussed in:
http://sqlite.org/datatype3.html
To illustrate it for yourself, try this:
CREATE TABLE ztemp (col1 INTEGER, col2 TEXT);
INSERT INTO ztemp VALUES(1234, "abcd");
INSERT INTO ztemp VALUES("abcd", 1234);
SELECT * FROM ztemp;
output from above:
-----------------
1234,abcd
abcd,1234
Notice the second insert. I inserted the text value 'abc' into 'col1',
which I had defined with an integer affinity. You can see from the output
that the first column contains both data elements, integer and text,
because SQLite columns do not have datatypes.
Note that in above I could also define the table as:
CREATE TABLE ztemp (col1, col2);
and the result with be the same. The database creates the same table with
or without affinities.
The key sentence in the SQLite docs, in section "2. Column Affinity" is:
"In SQLite version 3, the type of a value is associated with the value
itself, not with the column or variable in which the value is stored.
(This is sometimes called manifest typing.) All other SQL databases
engines that we are aware of use the more restrictive system of static
typing where the type is associated with the container, not the value."
You need to re-read that section closely and you will see that the answer
to the question you are asking ("What is the differnce between int(8) and
INTEGER?") is that there is no difference; the question is not applicable
in the context of SQLite because SQLite does not use datatypes.
Chris
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Christopher F. Martin
School of Medicine
Center for Digestive Diseases & Nutrition
CB# 7555, 4104 Bioinformatics Bldg.
University of North Carolina at Chapel Hill
Chapel Hill, North Carolina 27599-7555
Phone: 919.966.9340 Fax: 919.966.7592
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
On Wed, 7 May 2008, Joanne Pham wrote:
Hi ,
"Can you direct us where you find out that "SQLITE3 has bigint and int"
as datatypes?"
Not on any website but one of another project in my company using sqlite
and
they created one of the using bigint, boolean, int(1)
as the datatype and I have tried these datatype to create the table and
the
table is created sucessfully without any problem. So
I was wondering what is the different in size between int(8) and INTEGER
datatype.
Thanks,
JP
----- Original Message ----
From: P Kishor <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Wednesday, May 7, 2008 10:08:54 AM
Subject: Re: [sqlite] SQLITE3 datatype
On 5/7/08, Joanne Pham <[EMAIL PROTECTED]> wrote:
Hi All,
I have read online document regarding SQLITE3 data type and below is
list
of these datatypes:
[ Mark Set ]
� � � � * TEXT
� � � � * NUMERIC
� � � � * INTEGER
� � � � * REAL
� � � � * NONE
But just now I found out SQLITE3 has bigint and int as another
datatype.
� Can you direct me where I can find out the complete list of SQLITE
datatypes and the size of each datatype.
Can you direct us where you find out that "SQLITE3 has bigint and int"
as datatypes?
� Thanks,
� JP
� _______________________________________________
� sqlite-users mailing list
� sqlite-users@sqlite.org
� http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
� � �
____________________________________________________________________________________
� Be a better friend, newshound, and
� know-it-all with Yahoo! Mobile.� Try it now.�
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
� _______________________________________________
� sqlite-users mailing list
� sqlite-users@sqlite.org
� http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now.
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
_______________________________________________
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
