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).
You have found that you should not use TEXT affinity for this column. > 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, In SQLite, tables and indexes will always accept values of any type. > but has advantage of best sorting if reals or integers are involved. But REAL will sort the strings '1', '10', '2' wrong. Columns with TEXT/NUMERIC/INTEGER/REAL affinity might change some values (if those values can be converted). So the only correct affinity for your value column is NONE. Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
