Hi Darko,
Firstly, kindly keep this to the sqlite-users forum and not on the dev forum (the devs read this too, the difference being simply
that this one exists to help you, the other one is to discuss development stuff, not to help anyone).
Secondly, you are confusing two things. You are arguing about what /CAN/ be done while James tried to explain what /Should/ be done.
Nobody can argue with the possibility, but from experience we know that you are going down a road that will bite you later.
That said, the best way to achieve what you wish to do is by adding columns that explain the domain of a type on a per-row basis.
The fact that inside of SQLite an Integer can be stored in different ways is simply a code/data/space optimisation for SQLite, it is
transparent to the user and transparent to the SQL - it is in no way intended as a data-feature or extension of the SQL Language. If
you try to build on top of that (or maybe not on top of it, but in the same way) more arbitrary sub-types, yes of course it is
possible, but it may leave you in a World of hurt in terms of using the DB as a relational database system for other normal activities.
In contrast, if this is intended more as an application file-format than RDBMS, sure, make it better but, as others pointed out,
still try to do so in a way that does not require maintaining a software fork. This is good advice and in no way forbidding or
prescribing, just letting you know what will make life easier for you in the long run.
From the tone of your last post (if I am reading correctly) I understand that you have your mind set on finding a way that you have
thought about a lot lying in your bed late at night, you have rolled it around in your head and you just "feel" this should be
doable and will be so elegant a solution. We all go through that. You probably came here looking for confirmation of your ideas
rather than advice, but many of the people here have already done what you are trying now, this is why they know and this is why
this list is useful.
Y'know, use it, don't use it, etc. :)
On 2014/11/26 13:22, Darko Volaric wrote:
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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
