On 07/23/2013 02:52 PM, Max Vlasov wrote:
I've created a kind of triple storage base with Sqlite db as the container.
Basically it's several tables implementing Object-Propery-Value metaphor.
There's only one field for data so thinking about generality I assumed that
the type for the data field should be TEXT of nothing since most of other
types (let's forget about blob for a moment) can be stored (and easily
visualized) with this field. But there are also indexes involved and here
comes the problem. If I insert natural numbers in some sub-sequence I will
get non-naturally sorted ones (1, 10, 2, 20). But we know that Sqlite can
accept any data in any field, so I can change the type to INTEGER and enjoy
numbered order when there are numbers were added (1, 2, 10, 20). On the
other side, when we look at real numbers, the problem would still exist. So
paradoxically probably the best type for universal field container is REAL
(or NUMERIC) since it will accept data of any type, but has advantage of
best sorting if reals or integers are involved.
Is this correct or I am missing something?
The only difference between "INTEGER" and "REAL" is that real
values are converted to integers if this is possible without
loss of data. In other respects they are the same. Both try
to convert text values to numbers on insert. For example:
sqlite> CREATE TABLE t2(a INTEGER);
sqlite> INSERT INTO t2 VALUES('one');
sqlite> INSERT INTO t2 VALUES('1.5');
sqlite> INSERT INTO t2 VALUES('2.0');
sqlite> SELECT typeof(a), a FROM t2;
text|one
real|1.5
integer|2
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
