Arrays in SQLite
(1) By nalgeon on 2022-01-03 14:10:49 [source]
I've just published an array extension, which (unsurprisingly) adds array features to SQLite.
Supports integers, real numbers and strings (with limited max size). Uses 1-based indexing. Stores itself as a blob value.
sqlite> .load dist/array
sqlite> create table data(arr blob);
sqlite> insert into data(arr) values (array(11, 12, 13));
sqlite> select array_length(arr) from data;
3
sqlite> select array_at(arr, 2) from data;
12
sqlite> select value from data, unnest(data.arr);
11
12
13
Maybe give it a try, if you ever wished for arrays in SQLite ツ
(2) By Stephan (stephancb) on 2022-01-04 17:47:37 in reply to 1 [link] [source]
Thanks, it looks interesting. I could perhaps use it instead of my own unpublished, less complete array extension. Perhaps you can clarify:
is endianness observed? Meaning, that a binary copy of the database file with blobs storing the arrays will also work on a computer with a different endianness than the one where the database was generated?
what is the storage overhead, which could be significant for small arrays, e.g. representing 2d points (latitude, longitude).
extend functionality to 32 bit float arrays? 32 bit resolution is often fully sufficient for observations by physical sensors, saving up to almost 50 % storage space. In some image processing even 16 bit floats are used.
(3) By anonymous on 2022-01-05 13:12:58 in reply to 1 [link] [source]
Are sources available? I find that's the best documentation sometimes. Appreciate all the work, those extensions are very handy!
(4) By Harald Hanche-Olsen (hanche) on 2022-01-05 14:20:47 in reply to 3 [link] [source]
(5) By anonymous on 2022-01-05 16:27:47 in reply to 4 [link] [source]
I have a bad habit only looking at main. Yes, they are in the incubator branch. Great stuff.
(6) By nalgeon on 2022-01-06 12:54:16 in reply to 2 [link] [source]
is endianness observed?
No, I completely forgot about that. Currently, arrays are not portable in regards to endianness. Will look into it.
what is the storage overhead
extend functionality to 32-bit float arrays
Currently, arrays store only default SQLite numeric types: 8-byte integers and 8-byte real numbers. It's not that hard to support 1/2/4-byte integers and 4-byte floats, so I may implement them if there is a demand.
(7) By nalgeon on 2022-01-06 13:02:40 in reply to 2 [link] [source]
Regarding endianness. AFAIK, all modern desktop and mobile processors are little-endian, so that should not be much of a problem.
(8) By Alexandr Burdiyan (burdiyan) on 2022-08-24 14:08:33 in reply to 1 [link] [source]
I accidentally found this extension and I wonder whether integers are stored as varints inside the array, or they occupy full 8 bytes even for small values?
(9) By Simon Slavin (slavin) on 2022-08-24 19:51:27 in reply to 8 [link] [source]
So ? Make two tiny SQLite datbases, one with a big number and the other with a small number, and find out.
(10) By nalgeon on 2022-08-25 08:49:30 in reply to 8 [link] [source]
Integers always occupy 8 bytes.
(11) By anonymous on 2022-08-25 13:22:58 in reply to 7 [link] [source]
Just that SQLite isn't only used on Intel/AMD processors. ARM processors can be little or big endian. Virtually all other processors are big endian.
(12) By Donal Fellows (dkfellows) on 2022-08-25 15:01:14 in reply to 11 [link] [source]
ARMs are almost always little-endian; it's the overwhelmingly common configuration choice. RISC-V is another little-endian modern processor architecture.
Big-endian is rapidly becoming much less relevant than it used to be.
(13) By Richard Hipp (drh) on 2022-08-25 15:23:08 in reply to 12 [link] [source]
TCP/IP is big-endian. So we find ourselves in a situation where all of the CPUs are little-endian and the network is big-endian. This is tragic, but it is what we have to live with, apparently.
When I designed the SQLite file format, big-endian processors were still common (every Mac). In hindsight, it would have been better if I had designed SQLite to be little-endian. But I had no way, back then, of knowing that x64 would come to dominate, moving everything to little-endian.
I'm still nursing a circa 2004 iBook PPC that I use for big-endian testing of SQLite. Big-endian testing is becoming increasingly difficult as big-endian CPUs are harder and harder to find.
(14) By jose isaias cabrera (jicman) on 2022-08-25 15:51:24 in reply to 13 [link] [source]
We appreciate the effort you guys place into allowing us to be blessed by such a fine free product. Thank you.
(15) By Stephan (stephancb) on 2022-08-26 11:49:55 in reply to 13 [link] [source]
Is there info (a bit) in SQLite indicating the detected machine endianness (compile- or run-time)? This could save a few CPU cycles for extensions like "Arrays", if they should observe machine endianness. They could just use SQLite's detection.
(16) By Richard Hipp (drh) on 2022-08-26 13:00:08 in reply to 15 [link] [source]
It attempts to detect endianness at compile-time and sets the SQLITE_BYTEORDER macro accordingly. If unable to detect the byteorder at compile time, it checks for the byteorder at run-time.
The on-disk file format is always big-endian. The question is whether or not integers need to be byte-reversed while reading into memory.
(17) By anonymous on 2022-08-26 13:18:34 in reply to 16 [link] [source]
Not included in the public interface, alas.