SQLite Forum

Arrays in SQLite
Login

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

More in the docs and samples.

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]

I haven't looked carefully, but it appears to be in the incubator branch of the main sqlean repository. At least, there is a file called src/array.c there.

(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.