On 2016/03/02 6:47 PM, Eric Grange wrote: > Hi, > > I am wondering if very small BLOBs are stored inline with the record fields > (in the same page) or in another page? > > So far I had not worried about it, but now I have a usage case where I need > to store 128 and 256 bit integers (numbers, not IDs or hashes), SQLite does > not support such large integers natively, so I am planning to store them as > little-endian binary blobs, so that the BINARY collation can work for > sorting, and for the rest I would do the processing with custom functions > or plain old code. > > However I will have quite a lot of those numbers, and would like to know if > they would be stored inline with the rest of the records (would be > preferable for db size), or with an indirection in another page of the db > (so larger db), and if stored in another page, if there is an alternative, > such as passing binary as "utf8 text"and forcing a COLLATE BINARY on the > column? > > (yes, I could test that or use the source, but I would also like to have > some "designer feedback", as what current SQLite version does may not be > what designers intend for future SQLite versions)
Personally, unless your space is constrained, I would simply save the numbers as strings, perhaps Hex or BCD with leading chars and convert as needed. This would sort correctly without tricks and not do much worse for space. (Base64 would be even better space-wise but won't sort correct). Storing Blobs has a few conversions added if you try to use the SQL to store it, but it's quite efficient when using the API. I am confused as to why you would prefer little-endian word order? The binary sorting would not work at all I think, unless I am missing some trick in SQLite's sort methods. Lots of considerations to take into account, speed, DB size etc. If I were you, I'd definitely do some testing first of different methods with data simulated for your intended use case. I don't think there's a one-size-fits-all-solution. Cheers, Ryan
