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 ツ

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.

Regarding endianness. AFAIK, all modern desktop and mobile processors are little-endian, so that should not be much of a problem.

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.

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.

We appreciate the effort you guys place into allowing us to be blessed by such a fine free product. Thank you.

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.

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.

Not included in the public interface, alas.

Are sources available? I find that's the best documentation sometimes. Appreciate all the work, those extensions are very handy!

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.

I have a bad habit only looking at main. Yes, they are in the incubator branch. Great stuff.

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?

So ? Make two tiny SQLite datbases, one with a big number and the other with a small number, and find out.

Integers always occupy 8 bytes.