That's not true. A 64 bit floating point number and an 64 bit integer can
be represented by the exact same 64 bit pattern, and no-one would suggest
they're the same value. You can have those two differently typed although
identical bit values in the same SQLite column. The data identifying the
representation of those datums is integral to that value and doesn't belong
in a different column as you say. Other SQL based systems also allow
differently represented ("typed") values to appear in the same column.
The domain of a column can logically incorporate these different kinds of
values by introducing the concept of subtype. For instance in SQLite there
are something like 6 different representations of integer of between 1 and
64 bits. Each one of those is a different "type" in the sense of having a
different representation due to the number of bits they use and being
limited to a different set of numbers. A 1 bit integer is a subtype of a 64
bit integer since the set of numbers allowed by the 1 bit integer is {0, 1}
are contained with the set of numbers allowed by 64 bit integers, ie
{0..2^64-1}. If the column has a domain of integer then all these values
are valid since they're all integers. There is no logical or formal reason
why this can't be extended further to allow arbitrary subtypes according to
the user's wishes.
You can have the same 64 bits of data represent 4 different values in
SQLite: integer, double, string and blob (I'm assuming SQLite can store a
blob in 8 bytes). They are not treated as equal by SQLite because they have
different types. There is no reason why we should be limited to those 4
types in SQLite. Many SQL based system allow users to define their own
types. What I'm proposing is just implementing the same thing in SQLite.
There is nothing in the relational model that disallows this. You're
assuming that because columns have a domain or type, then that domain must
have a fixed representation in the database implementation. The relational
model says nothing about how the data is represented or what kind of data
can be stored.
On Tue, Nov 25, 2014 at 5:20 PM, James K. Lowden <jklow...@schemamania.org>
wrote:
> On Tue, 25 Nov 2014 04:41:51 -0800
> Darko Volaric <li...@darko.org> wrote:
>
> > I have a need to implement per-value custom typing in SQLite. In my
> > case I may have the integer value 1234 appear in two rows in the same
> > column, but they are different in that they have completely different
> > meanings because they have different types although they have the
> > same representation in the database.
>
> A column is a type. Two values of the same type *must* represent the
> same thing. That's how SQL works.
>
> When you say 1234 may represent two different things in the same
> column, what you really mean is that 1234 doesn't stand for the whole
> thing, that the column isn't the whole type (as you conceive it).
> That's fine; you need another column to discriminate between them, to
> capture that "whole type". Each column-component of that type is itself
> a type, just as a street name is part of a postal address.
>
> As a practical example of what that's true, consider this list:
>
> anything
> --------
> cat
> green
> jogging
>
> We can sort that "anything" column as *strings*, but what if each one is
> 1234 in the database? Even if they are different values, how do you
> compare green to jogging? Which one comes first? How should a join
> work?
>
> If that doesn't convince you, please understand I'm not expressing an
> opinion. I'm pointing out a basic tenet of the relational model. I
> can recommend good references on the subject.
>
> If you represent your things, whatever they are, in the model according
> to its rules, you will find you don't need to extend the type system.
> Nothing good awaits you if you attempt to extend it without first
> understanding it.
>
> HTH.
>
> --jkl
> _______________________________________________
> sqlite-dev mailing list
> sqlite-...@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
