The person I replied to cross posted, not I, and I didn't realise this
before I replied to his cross post and the I couldn't change it then, so
maybe take that up with him.
I'm not confusing anything. You, and the other posters, are confusing the
representation of values and the concrete value type with the logical types
declared for column domains. I understand this might be a subtle
distinction to some.
But I don't intend to argue this point any further since I'm merely looking
for advice about how the database engine is implemented, not about how it's
used or how I'm using it.
I'm not looking for confirmation of ideas, on the contrary, people seem to
want to push their own ideas about a database should be used and how I'm
not using it correctly, when that is irrlevent to the issue I'm discussing.
Maybe more focus on the technical facts and less on divining what I think
at night in bed and try framing your arguments based on those technical
facts rather than ad-hominem attacks.
On Wed, Nov 26, 2014 at 4:53 AM, RSmith <rsm...@rsweb.co.za> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
