Well we are already using the quirk that kills performance. My point is that irrespective as to the settings on an individual DB, we need two methods for the framework: (1) case-insensitive and (2) case-sensitive.
The first uses ILIKE (pg) and CONTAINING (firebird) and whatever other DBs offer, or a fall-back to LOWER or UPPER. The second uses a straight-forward SQL LIKE. Case-sensitive. To argue for overall DB settings is missing the point. A developer should be able to choose which search he requires for each query. To preserve backward compatibility, I suggest retaining LIKE for case-insensitive, and introducing a new method for case-sensitive. The alternative is to break our code and/or start using horrible switches. On Friday, 5 September 2014 14:47:02 UTC+1, Niphlod wrote: > > except that except postgresql any database can deal with case-sensitivity > or not at the database level, and doesn't have an ILIKE, so needs to go > through LOWER(), effectively doing a full scan "by default" ditching every > possible index. > > Using by default a quirk that kills performances just because python > doesn't map to SQL (or better, just because web2py doesn't map to > postgresql) seems an overkill and a total turndown for anybody looking at > DAL. > > The fact that users using grid expect the grid to treat everything as > case-insensitive is a VERY POOR argument over the fact that the lower-level > handles LIKE as it should. Changing the upper level impacts performances of > who uses the grid. Changing it at DAL level impacts everyone using DAL > (web2py or elsewhere). > > On Friday, September 5, 2014 1:15:22 PM UTC+2, villas wrote: >> >> > Should we put to a vote the default behavior of db(...).like(...)? >> >> The change to LIKE was to mirror the standard SQL and make it >> case-sensitive. Unfortunately, most web2py users expect LIKE to be >> insensitive. >> >> There really should have been two commands from the beginning, LIKE and >> ILIKE (if we wished to copy Postgres keyword) >> >> My favorite solution is this: leave LIKE as it is (insensitive) and >> introduce SLIKE (sensitive). It would be a quirk of web2py, but once >> known easily remembered. We could leave our code unchanged, hurray!! >> >> Another more painful idea is to continue making LIKE case-sensitive, but >> then to introduce ILIKE so that everyone has the choice for each SQL >> statement. The problem is that we still have to change our code. >> >> The idea of using switches is much more horrible and really just a >> sticking plaster over the problem. IMO we should really have two explicit >> methods. >> >> >> -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
