> -----Original Message-----
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf
> Of James K. Lowden
> Sent: Tuesday, August 27, 2013 8:11 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] BETWEEN and explicit collation assignment
>
> On Mon, 26 Aug 2013 19:03:39 +0000
> Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote:
>
> > However, sometimes, in comparison we want to ignore some of the
> > attributes, or compare derived ones. Many busses can carry 25 people,
> > and may be considered equal if we simply need to transport people.
> > Busses certainly differ by other attributes.
>
> Busses might indeed differ in many ways, but if you make NAME the
> primary key for BUSSES, the rule is not "compare BUSSES, ignoring
> columns other than NAME". The rule is "compare BUSSES.NAME".
>
> > it is the comparison ( "=", BETWEEN, IN , etc) statements that must
> > be modified
>
> This not a syntax issue. Equality is deeply embedded in the system, in
> many places where there's no SQL in play (e.g. keys).
>
> It's a system of types and operators. We can already convert between
> types and compare them. If you can show some kind of comparison
> that *cannot* be done via type conversion using the operators exactly
> as they are, you might have a point.
>
Roman --
Another issue is the fundamental but often confused difference between
Object Oriented Programming and Relational DataBases (<G> AKA the Object
Relational Impedance mismatch <G>)
One should not model a complex object like a Bus in a Relational DB as
an amorphous Blob, it should instead be modeled as as a collection of
orthogonal attributes in a set of atomic columns, each having a specific
type in one-or-more tables.
Equality, or more generally, comparison of the column primitives is
absolutely as James said, 'deeply embedded in the system itself'.
Be the CPU ...
To find the Bus that fits your needs, you would need to compare sets
of primitive Attributes of interest to specific needs, invoking AND-OR
clauses to combine those specific primitive comparisons.
For example: to find the Name of all the short busses:
-- one model of a bus might be:
CREATE TABLE Bus
(
Id Int INTEGER PRIMARY KEY AUTOINCREMENT
, Name varchar( 32 ) COLLATE NOCASE
, Color varchar( 16 ) COLLATE NOCASE
, Length numeric( 5,3 )
, Capacity int
)
;
-- find the Name of each short bus:
SELECT Name
FROM Bus
WHERE Color = 'yellow'
AND (( Length < 20 )
OR ( Capacity >= 8 AND Capacity < 16 ))
;
-- kjh
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
