add concatenation SQL construct

[Mike Bayer]

right now functions like startswith() and endswith() rely upon an in-python string concatenation of "%" with the given value. this prevents the usage of bind parameters and other non-literal expressions. a better approach would be to move the string concatenation into the database layer, so that instead of producing:

where somecol LIKE "%foo"

we instead produce:

where somecol LIKE '%' + 'foo'

but not every database supports "+" as a concatenation operator. so a ConcatenationClause would be needed which each dialect can produce as it likes, such as oracle which would produce CONCAT(x, y) for example.

[Former user]

(original author: svil) mmh, i think u misunderstood this one: it's more for this use case:

table.select( table.c.somecol.endswith( bindparam(":foo") ))

or

table.select( table.c.somecol.endswith( literal(whatever) ))

IMO once the reverse-operators do work, this may just pass - IF the String type somehow reaches down to the sql-dialect and uses || where needed.

[Mike Bayer]

oh right, gotya.

[Former user]

(original author: svil) i think even without special concat clause it would work - u do have sqlCompioler.binary_operator_string(), and it is used IF the expression type is String.

but: The type of the expression sometimes gets lost - or is not "inherited" properly, i.e. a String column + something becomes _BinaryExpr of type String, but literal('a')+'b' does not. One have to explicitly specify literal('a', type=String), and even then, sometimes it does not work (see initial patch of endswith()), as the type of the expression is taken only from one of the operands (left, i.e. the one which .add is being called).

First do #474, and then see how this behaves. startswith() and like() should be pretty ok; endswith may take longer. Playing with types may be somewhat slippery downslide - i mean, once u start caring about them, it's hard to stop.

[Former user]

my code; depends on #474 being done to work in all cases

[Mike Bayer]

changeset:2519 implements this so far, but its not done. "+" does not work as a string concatenation operator on most databases. the Concatenate construct is still needed, which will compile to CONCAT or similar depending on dialect. additionally, CompareMixin should have a concatenate() method which produces this construct, and is used by startswith() and endswith(). the + operator can perhaps check the type of item being compared and if its string based, produces the Contenate construct as well.

[Mike Bayer]

unit tests should be added to query.py to test the concatenation operation too so we can verify it works on actual databases.

[Mike Bayer]

ive made a new ticket for "generic functions" which would address a "concat()" function in a general way. lets move it over to there.

[Mike Bayer]

sorry, that last move was on crack. concatenation is more basic than a SQL function, expressed by "||" on some databases. the "+" operator in CompareMixin should detect a string-based type on the left side (right side for radd()), produce ConcatenationClause, the dialects should compile appropriately, either into ||, +, concat(), whatever. also the sql construction docs have been wrong on this since the beginning of time.

[Mike Bayer]

this is fixed in changeset:2974 in a much better way than I previously proposed. All operators are now abstract tokens, drawing upon Python's operator module as well as a set of custom operators. + becomes a concat operator in a string context, MySQL can then add in concat() when its encountered (all others use ||).

[Mike Bayer]

Removing milestone: 0.4.0 (automated comment)