Re: [review] [sqlite]

From: Dominique Devienne (ddevienne_at_[hidden])
Date: 2024-11-18 10:10:24

On Mon, Nov 18, 2024 at 10:34 AM Klemens Morgenstern via Boost
<boost_at_[hidden]> wrote:
> On Mon, Nov 18, 2024 at 1:03 AM Barend Gehrels via Boost <boost_at_[hidden]> wrote:
> > One of its columns is typed geometry, which is not listed in this sqlite
> > documentation (that’s OK), but apparently it can be handled as a blob by
> > the library. Awesome.
>
> This is probably a subtype, which would make a great example for custom
> conversions. Are you using spatialite?

SQLite has 5 types, period [1]. And that includes NULL as a type.
[1]: https://www.sqlite.org/datatype3.html#storage_classes_and_datatypes

Unless you use STRICT tables [2], you can name your types anything.
So having a types named GEOMETRY means nothing to SQLite,
and its affinity rules kick in [3] to see how the value is handled,
often as a blob.
[2]: https://www.sqlite.org/stricttables.html
[3] https://www.sqlite.org/datatype3.html#type_affinity

The nothing of subtype [4] exist in SQLite, but is very *transient*.
It is never stored in DBs (in memory or otherwise), and only used to
convey transient typing information from the result of one function,
to the argument of another function. Subtypes are not even preserved
by views using functions adding subtype information on their results.
[4]: https://www.sqlite.org/c3ref/result_subtype.html

SQLite typing is weird and full of gotchas.
Sorry for being pedantic, but better to avoid misconception about subtyping,
in SQLite at least. Perhaps Klemens meant it in a different context. --DD