Arrays in SQLite
(1) By nalgeon on 2022-01-03 14:10:49 [link] [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 [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.
(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.
(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.