[Dbix-class] SQLA refactor: proposal

[Darren Duncan]

At 2:23 PM -0800 11/20/07, Darren Duncan wrote:
>At 8:14 PM +0000 11/20/07, Matt S Trout wrote:
>>There are a few SQL-y operators such as LIKE or BETWEEN that don't seem to
>>be in there, but I don't see why they can't be in sqla.Core.Str.Between
>>etc.
>
>As for BETWEEN, there is currently no Muldis D operator specifically 
>for that, though one could be added easily enough; meanwhile, the 
>likes of "x >= 4 and x <= 7" works, as does "x IN (4,5,6,7)", 
>assuming integers; the latter form is semijoin().

As an update, for our convenience, I just added a pair of generic 
range-checking operators to the Muldis D spec in Core.pod; you can 
see it in today's commit at 
http://utsl.gen.nz/gitweb/?p=Language-MuldisD .

Here is the text of the additions:

     =item C<sys.Core.Ordered.is_inside_range of Bool (Some.Ordered $topic,
Some.Ordered $min, Some.Ordered $max, Bool $min_is_inside, Bool
$max_is_inside)>

     This function results in C<Bool:true> iff its C<$topic> argument is within
the range whose bounds are defined by its C<$min> and C<$max> arguments. If
C<$min_is_inside> or C<$max_is_inside> are C<Bool:true>, then C<$topic> is
considered to be within the range if it is equal to C<$min> or C<$max>,
respectively.  This function's arguments must be of compatible declared
types as per C<sys.Core.Ordered.compare>.  This function will fail if
C<$min> is an increase over C<$max>.

     =item C<sys.Core.Ordered.is_outside_range of Bool (Some.Ordered $topic,
Some.Ordered $min, Some.Ordered $max, Bool $min_is_inside, Bool
$max_is_inside)>

     This function is exactly the same as C<sys.Core.Ordered.is_inside_range>
except that it results in the opposite boolean value when given the same
arguments.

These 2 operators are analagous to SQL's BETWEEN and NOT BETWEEN, 
respectively, but that you can also specify whether the range is 
inclusive or exclusive.

>As for LIKE, the common case where you just want to see if something 
>is a substring of something else is covered by 
>sys.Core.Text.contains(); this has the flexability to specify 
>at-start/at-end/may-be-middle.  But the less common cases covered by 
>a generic LIKE aren't in Muldis D yet.

To explain this further, Muldis D currently supports these uses of 
SQL's LIKE in contains():

   foo LIKE '%bar%'
   foo LIKE '%bar'
   foo LIKE 'bar%'
   foo LIKE 'bar'

Though the last is logically the same as an equality test.

More complicated things, such as "foo LIKE '%bar%baz' are not 
directly supported yet, and I think that an implementation of such 
should be generalized to something like basic pattern matching / 
regular expression support (which some SQL DBMSs support), which 
isn't provided yet.

Meanwhile, I think the simpler contains() should satisfy most uses of LIKE.

I will also clarify/repeat that all character string comparisons in 
Muldis D are case-sensitive, like Perl's and many/most DBMSs (except 
MySQL) are; as a corollary, a relational join or union or what have 
you will only consider two texts to be a match when they are of the 
same case (after all, they aren't "the same" otherwise).  No 
case-insensitive comparison operators are provided, though there are 
lowercase/uppercase operators, so you can use the results of those in 
the comparison, or to make indexes with.

-- Darren Duncan