On 2 Jul 2012, at 10:29am, Niall O'Reilly <niall.orei...@ucd.ie> wrote:
> On 29 Jun 2012, at 23:58, Richard Hipp wrote: > >> But you know: How often do people use BLOBs as keys? What other SQL >> engines other than SQLite even allow BLOBs as keys? Are we trying to >> optimize something that is never actually used? > > For an IPAM application I have on my back burner, BLOB seems > a natural way to express IPv[46] addresses, ranges, and prefixes. > A bulkier alternative would be hexadecimal encoding as text. Strikes me as premature optimisation. Storing them as strings of decimal or hex (with, of course, leading zeros) would allow you to sort them meaningfully, take substrings meaningfully, and to understand the contents of your file when displayed using debugging tools. If you do that, and the results turn out to be too slow for your user(s), /then/ revisit ideas of making things faster or more compact. Worth remembering that BLOBs don't have a well-ordering function. You can compare two BLOBs and tell whether they're the same (usually, but lossless encoding defeats this), but if they're not the same you can't put one 'before' the other. This is because BLOBs are essentially black boxes. You have no idea what the data represents. If you know what it represented, you'd probably be storing it as text or a number. Think of storing images as BLOBs. How do you compare two images ? Is one before another because it is smaller ? Or because it contains darker pixels (lower brightness) ? Or because the EXIF information says it was taken on an earlier date ? Or because it's an earlier frame in the animation you're making ? So if a function like building an index requires an ordering function, you can't use it on a BLOB. Now, as it happens, BLOBs are stored as octets, and if the functionality is presented there's no harm in sorting them as if they're octet-streams. But it doesn't really mean anything. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
