Efficient array data type

(1) By Andrea Aime (andrea.aime) on 2020-04-30 13:29:07 [link] [source]

Hi, I'm trying to figure out if sqlite, or one of its extensions, supports efficient storage of arrays of various data types. For the problem at hand, of numbers and dates, but in general, of any SQLite supported data type.

Looking around it seems that:

The core SQLite does not support arrays
The CArray extension, despite the name, does something else
The JSON1 extension supports, among other things, arrays, but stores them as strings, thus not in a space efficient way for numbers and dates.

Am I missing anything here?

Best regards Andrea

(2) By Kees Nuyt (knu) on 2020-04-30 14:18:30 in reply to 1 [source]

Hi, I'm trying to figure out if sqlite, or one of its extensions, supports efficient storage of arrays of various data types. For the problem at hand, of numbers and dates, but in general, of any SQLite supported data type.

Arrays are not supported as datatype for a column value in SQLite.

In relational databases, arrays are genrally stored as rows with as many key columns as the array has dimensions, and one value column.

Your question suggests you think that is not efficient, but doesn't have to be true. Just try it.

Alternatives I can think of:

You could serrialize an array into a blob, but you will have to build that by yourself
JSON arrays, yes they are probably much less space efficient than storing them the SQL way.

CREATE TABLE My3Darray (
  x INTEGER NOT NULL -- dimension 1
, y INTEGER NOT NULL -- dimension 2
, z INTEGER NOT NULL -- dimension 3
, v REAL -- the proper type for julianday() dat3e/time stamps
, PRIMARY KEY (x,y,z)
);

-- 
Regards,
Kees Nuyt

(3) By ddevienne on 2020-04-30 15:19:40 in reply to 2 [link] [source]

Your question suggests you think that is not efficient, but doesn't have to be true

That's just a fact Kees, IMHO.

Storing doubles of a 3D cube as you suggest would blow up the disk used
tremendously, and arrays tend to be direct addressable by computing
an offset, based on the dimensions and fixed-size element, which would be
incredibly faster than a BTree lookup, I can tell you that.

Of course, storing large blobs in SQLite is not particularly efficient
either, blobs are one of the weak points of SQLite IMHO... Chasing all
these overflow pages will add overhead to address into a far-offset.

I really really wish SQLite had a new kind of pages, or even page-ranges
to be much more efficient with large blobs...

(4) By ddevienne on 2020-04-30 15:28:02 in reply to 1 [link] [source]

Not really.

CArray is about using in SQL arrays which live in C-memory.

But it would be relatively easy to write something based on CArray
which instead of accessing C-memory directly, read from an SQLite
blob instead, and did the same kind of decoding (i.e. C-casts).

But doing that, you lose portability of your data, because if you
store C-arrays as persistent blobs, they are in whatever endianness
your machine is in. x86 and x64 are little-endian though, while ARM
is either I think (at least at the chip-level I believe). And of course
blobs are opaque, so unless you encoding the type (and endianness) in
the blob as well, or another column next to it, it's error prone. But
it might be OK too, depends on the context.

JSON1 also does what you want, but using text instead of blobs indeed.

You basically want a mix of CArray and JSON1 :)

There's nothing like that out of the box, ready to use, but again, writing
a custom table-valued function that decode blobs is not too difficult.