On Sunday, 12 January, 2020 15:31, Simon Slavin <slav...@bigfraud.org> wrote:
>You're generally right. SQLite always uses affinities (more or less
>'weak typing') rather than strong typing. I don't know of any other SQL
>implementations which allow this without explicit declaration, and most
>don't allow it at all.
SQLite is strongly typed however "entities" (table rows) are composed of a
fixed number of dynamically typed values. A collection of entities (a table)
has an affinity (preference for a particular type if possible) for each
"column" in the collection.
This is not "weak typing" nor is it "duck typing". It is "dynamic typing".
>You can enforce strong typing in SQLite using a constraint. But most
>people enforce type in their own code, before the value gets to SQLite.
Well, no. You can restrict the size of the dynamic though a check constraint,
but you cannot make the members of entities strongly typed. For example:
create table x
(
c text check (typeof(c) is 'text')
);
does not strongly type the column "c" of entity collection "x". What it does
is ensure that only text values are stored in that particular dynamically typed
column of the entity. You will note that the declaration is inconsistent --
the two available correct forms would be:
create table x
(
c text not null check (typeof(c) is 'text')
);
and
create table x
(
c text check (typeof(c) in ('null', 'text'))
);
while one may be tempted to claim that "consistency is the hobgoblin of little
minds" its converse, inconsistency, is often a source of errors.
Some affinities are more complicated. For example:
create table x
(
c numeric check (typeof(c) in ('null', 'real', 'integer'))
);
both 'real' and 'integer' are required for numeric affinity enforcement, and
the 'null' because the column may contain nulls.
Doing this does not really do much since you still have to check the type on
retrieval of the value anyway in order to know what to do with it.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
