Darko,
I have rather a long answer for you. I'm not confused about the
difference between logical and physical types, but I am confused about
what you meant and what you're hoping to accomplish.
On Wed, 26 Nov 2014 03:22:03 -0800
Darko Volaric <li...@darko.org> wrote:
> 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.
Because they're different types.
> You can have those two differently typed although identical bit
> values in the same SQLite column.
Because SQLite doesn't enforce type-checking.
> The data identifying the representation of those datums is integral
> to that value and doesn't belong in a different column as you say.
"The data identifying the representation" is type information.
> You're assuming that because columns have a domain or type, then that
> domain must have a fixed representation in the database
> implementation.
I think it's a reasonable design choice for the machines we have. In
any event, the DBMS's choice of representation is its own.
Looking at your other replies, I'm not sure I understand what you're
hoping to accomplish. I answered your assertion that you'd like to
have multi-type columns:
> > 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.
Where you lose me is the idea that two rows in one column might have
identical bit patterns but mean different things. Computers and
databases generally represent values as bits. Surely two identical
values of one type are equal. You have to store the distinction
somewhere, right?
Elsewhere you suggested you want to *extend* the type system, to define
more complex types (for which identical bit patterns are equal). You
could define them in terms of contraints,
> 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}.
This as you know can already be done using CHECK constraints.
> There is no logical or formal reason why this can't be extended
> further to allow arbitrary subtypes according to the user's wishes.
I think I would argue that's true, provided:
1. the constraints can be expressed to the evaluation system
2. further-constrained types don't necessary represent a "subtype"
After all, a "1-bit integer" might as well be a Boolean or for that
matter might represent red and green. You can't add red and green in a
1-bit regime, nor divide, nor increment. It's a "subtype" only in some
physical sense, the very sense you're saying is irrelevant.
I also think such constraints can be expressed now using DRI. Is there
something you'd like to enforce that you cannot?
Aggregate types are a different matter. For example, one might want to
have a PDF type. PDFs can contain certain metadata, such as author or
date. Comparison could be defined in terms of those metadata;
constraints could require them. If you had a way to signify the type
and comparitor to SQLite, you could extend its type system and
distinguish PDFs from ordinary blobs.
We can go one step further: a column may hold tables. Nothing in RM
prevents that either; it's just not supported by SQL.
I don't think you're going to find much appetite for a user-extensible
type system in SQLite. It's harder than it seems on both sides,
implementation and use. And without very deep support -- in the
constraint system, in the SQL syntax -- it would probably do more harm
than good.
I'm not sure where that leaves us. It's one thing to distinguish
between logical and physical types. It's another to extend the type
system. It's a third to argue that because a logical type could be
represented in more than one way physically, that it should be, in one
system, in one column. And it's yet a fourth to say that a column
should be able to represent more than one type, be it physical or
logical. I'm not sure which of those you mean.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
