So if character-separated values (CSV-ish) were originally your preferred
import format, would using that format for the blob's work for you?
E.g., Suppose you need to index the first two fields only, and so can use a
blob column for the bulk of the record. If the records were supplied as:
MyFirstField~MySecondField~thousands|of|data|items|...
and you imported these records into your 3-column table, defining tilde (~)
as the separator, you could retain a simple format.
If ever needed, you can easily export the table with the indexed columns
treated like the bulk of the data
from the sqlite3 command line utility:
.mode list
.separator |
SELECT * FROM MyTable;
MyFirstField|MySecondField|thousands|of|data|items|...
On Thu, Oct 17, 2019 at 9:10 AM Mitar <[email protected]> wrote:
> Hi!
>
> On Thu, Oct 17, 2019 at 3:04 PM Eric Grange <[email protected]> wrote:
> > my suggestion would be to store them as JSON in a blob, and use the JSON
> > functions of SQLite to extract the data
>
> JSON has some crazy limitations like by standard it does not support
> full floating point spec, so NaN and infinity cannot be represented
> there. So JSON is really no a great format when you want to preserve
> as much of the input as possible (like, integers, floats, text, and
> binary). SQLite seems to be spot on in this regard.
>
> But yes, if there would be some other standard to SQLite and supported
> format to embed, that approach would be useful. Like composite value
> types.
>
>
> Mitar
>
> --
> http://mitar.tnode.com/
> https://twitter.com/mitar_m
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
