allow column.startswith( anothercolumn)

[Former user]

table.c.name.startswith( table.c.alias) currently fails:

File "/usr/lib/python2.4/site-packages/sqlalchemy/sql.py", line 568, in endswith return self._compare('LIKE', "%" + other) TypeError: cannot concatenate 'str' and 'Column' objects

here a possible patch.

[Former user]

(original author: svil) fixed version

[Former user]

(original author: svil) the type=sqltypes.String) in literal is needed to force the expression to be of str type so it gets compiled into || and not +. At least this was the way i found; u may know better.

[Former user]

(original author: svil) yet another... + testcase Could not find a way to get the str-type down through str + literal(), so forced it on resulting BinaryExpr. sorry for the mess..

[Former user]

force str-type on endswith: str+literal()

[Former user]

(original author: svil) testcase for all possibilities so far

[Former user]

(original author: svil) well... testing is good

[Former user]

(original author: svil) something near - sql.py:563: raise exceptions.InvalidRequestException("in() function accepts ...

probably should be exceptions.InvalidRequestError

[Former user]

(original author: svil) and while on it: why in_() does not accept literal()s/bindparams? see last-patch.

All this fiddling with literals is mostly because 'a'+table1.c.col2 fails to converge..

def __radd__(self, other):

    return self._bind_param(other)._operate('+', self)

seems to fix that; not sure about type though. now endswith() would not need any special treatment than startswith().

[Former user]

(original author: svil) test for all

[Former user]

(original author: svil) nah, the endswith type is still wrong. undo + test

[Mike Bayer]

do two things for me:

• please dont attach tbz files, attach files individually. i know messier, but much easier for me to deal with since i am relying on web access here.

• when testing SQL construction, we test the actual SQL strings produced, typically off the base ansisql dialect. no ORM or actual database access. Create some simple (simple means, dont rely on hard-to-read iterators and string concatenations to automate) tests to be added to source:/sqlalchemy/trunk/test/sql/select.py.

[Former user]

(original author: sdobrev) hmm.

There are several issues here

• (const OPERATOR clauseElem) does not work - u dont have reverse operators defined (radd, rsub etc). so this is one group of testcases

• column.methods() do not work for literals - endswith, startswith, like, in_ - and for clauseExpressions made of literals+consts ; group 2

• column.in_(*args) throws (wrong) error on any clauseElems; while clauses containing only literals and consts should be allowed. Or so i think - i may be wrong. group 3

• string-related operations/methods must use concatenation || and not plain + - and this seems to be sql-dialect related (and very tricky to get done). group 4

• somehere in between fails the type-compatibility - i am just noting it as i dont know how SQL treats things like (1+"aa"), or (x.y LIKE 123). This is another dimension to group 1 and may make the total number explode

• u rely on simple, non-exhaustive tests to prove that something works. This might be ok for the case of 'no time to write them', but in the long run it gets expensive. (it's like random shooting) Just see how many places - polymorphics, inheritance etc - where u wondered why it hasn't come before... And consider the following as example: in these simple cases u've missed the very basic case of const+ClauseElem, and this is 50% of all the possible operator cases! And - it doesnot work. Although in most cases there is a workaround because of associativity of the ops... probably a reason noone has complained. simple has diff. meanings for diff. people: for me something talking about authors, books and relations thereof is awfuly complex - as i have to first translate it into non-english and eventualy understand the application-field-meanings - while something talking about A,B,C is pretty simple (to me). i know, diff. people = diff.tastes and diff.perceptions and diff.ways.

i dont know SQL and i don't want to know it; hence i'm doing a blind-black-box approach in testing SA - i test SA as "language" and not the actual SQL it makes - as long as the result is ok. In some cases this may look as brute-force... i have to prove that it works - or that it does not fail - which are not equivalent, but seem so.

so - try this one (colexpr.py) and say if u want it converted into unittest in its generic form, or u prefer a set of generated simple testcases; also u may as well choose 2-3 off those and abandon the whole issue (shrug).

i've no idea how u would like to test the concat-thing (group4). check finalexpr.type==String?

ciao svil

[Mike Bayer]

duplicates: #474, #475, #476

please keep each distinct issue in its own ticket.

I dont understand what you mean by "use string concatenation and not "+". bring that to an email, not here, unless there is a specific issue.

also, the essay writing and ranting is not appropriate for tickets, please bring all that to email (and also cut down the essay writing...its not helping anything).

also the lack of exhaustive tests is due the fact that SA is developed iteratively. the test cases are not even conceived until some user out in the wild tries something that quite honestly i never even thought of. 90% of SA's codebase are things that i would never have thought of if i didnt put it out there first. its quite easy for someone to come in 18 months after the fact and complain, but not very useful. also until this point i never even understood what "radd()" and such were for so that will be very helpful.

i think your tests tend to encompass too wide a range of functionality and are often not exactly "unit" tests...also not putting them in unittest format creates more work since they have to go there anyway....but most of all they are completely unreadable and a failure condition is useless since it takes twice as long to understand the test case, nestled into a deep iteration of undocumented function calls and loops with undescriptive variable names, as it does to understand the broken function.

for example, here is a readable version of the "operator associativity" test:

from sqlalchemy import *
import operator

t = table('sometable', column('col1'))

for (opfunc, sqlop) in (
    (operator.add, '+'),
    (operator.mul, '*'),
    (operator.sub, '-'),
    (operator.div, '/'),
):

    for (left, right, expected) in (
        ('s', 's', "s %s s" % sqlop),
        (t.c.col1, 's', "col1 %s s" % sqlop),
        ('s', literal('s'), "s %s 's'" % sqlop),
        # etc.
    ):
        assert str(opfunc(left, right)) == expected

note there are no external functions with weird hard-to-understand names, (like "typname( t)"), no undocumented and weird string concatenations (like print ok and 'ok ' or 'err', 'column.in_( ' +', '.join( str(a) for a in args) +' )'), no non-descriptive words (like "litera", unless that is a real word in your native language)...the entirety of whats being tested is immediately obvious. the eye does not need to scroll up and down locating remote function calls that arent really needed anyway. no mental energy need be expended trying to figure out what the "expected" value is. the test also goes from the expression construction right down to the SQL generation so covers a broader chain of events than the attached test. try to make them look like this. readability and exhaustive testing are not mutually exclusive.