Case insensitive zoeken

[Boudewijn]

Hoi

Ik ben bezig met een Pylons (Python framework) applicatie die aan een psql db hangt, waarbij mensen ook op een string kunnen zoeken.
Deze string wordt vervolgens doodsimpel in een select query omgezet (select * from Y where name = $string) , echter is dit case sensitive...

Nu kan ik de input niet aanpassen (Anders zou ik die velden in de db allemaal lowercase maken...) en is er geen conventie voor input.

Mijn oplossing is om een 2e veld in de DB op te nemen, met daarin een lowercase versie van de naam van het tupel (op die naam wordt gezocht) en bij een search actie de search-string ook lowercase te maken. Moet werken, maar is imo vanuit Software Engineering-oogpunt een lelijke oplossing doordat ik data redundant opsla.

Weet iemand hier een nettere oplossing voor? De losse SQL code kan\wil ik niet zelf gaan schrijven; alles moet door de ORM gebeuren (slqalchemy). Het moet dan ook (als het kan...) nog eens DB onafhankelijk werken .

[urk_forever]

Kan je geen LIKE gebruiken?

Is dit iets?

Of een lower() functie om het veld lowercase the maken.

[ Voor 100% gewijzigd door urk_forever op 16-04-2010 10:28 ]

[Boudewijn]

Inderdaad, maar een like is in principe ook case sensitive. Met regexen in de like kan het trouwens wel, maar ik weet niet of ik dan mijn doel een beetje voorbij schiet.


Bedant voor die lower functie, maar ik dnek dat ik gewoon voor ILIKE ga, die is case insensitive by default. Geen officiele SQL syntax, maar dat is dan maar jammer.

[ Voor 33% gewijzigd door Boudewijn op 16-04-2010 10:43 ]

[cariolive23]

De LOWER() optie is de snelste, dan kun je namelijk ook een index gebruiken:

SQL:

CREATE INDEX idx_naam ON tabelnaam(LOWER(kolomnaam));

[Remus]

Kan je geen case insensitive collation gebruiken of op het veld zelf, of in je query?

[_Erikje_]

Gewoon bruteforce alle mogelijke combinaties met hoofdletters en kleine letters queriën.

Sledgehammer method™

[Boudewijn]

cariolive23 schreef op vrijdag 16 april 2010 @ 11:59:
De LOWER() optie is de snelste, dan kun je namelijk ook een index gebruiken:

SQL:

CREATE INDEX idx_naam ON tabelnaam(LOWER(kolomnaam));

Dat klopt alleen wil sqlalchemy daar niet aan, die gebruikt zelf een SQL-generatie. Eens kijken of ik dat uit de weg kan krijgen.

Remus schreef op zaterdag 17 april 2010 @ 17:33:
Kan je geen case insensitive collation gebruiken of op het veld zelf, of in je query?

Moet ik uitzoeken.

_Erikje_ schreef op zaterdag 17 april 2010 @ 17:57:
Gewoon bruteforce alle mogelijke combinaties met hoofdletters en kleine letters queriën.

Sledgehammer method™

Was jij nou ook SE alumnus?

[CubicQ]

Kijk wel even of bij al je databases die je ondersteunt de index gebruikt wordt. DB2 gebruikt in ieder geval geen index wanneer je een like op een lower() kolom uitvoert. (Dat kan je oplossen door een generated column toe te voegen waar je vervolgens een index op legt)

[Boudewijn]

In principe ondersteun ik alleen psql omdat ik ook geo informatie opsla dmv postgis. Compatibility is een mooi streven, maar op zich hier niet nodig (ondanks mijn eerdere bewering ).

[Wolfboy]

Boudewijn schreef op zaterdag 17 april 2010 @ 19:03:
Dat klopt alleen wil sqlalchemy daar niet aan, die gebruikt zelf een SQL-generatie. Eens kijken of ik dat uit de weg kan krijgen.

Onzin, met SQLAlchemy kan je prima func.upper(some_table.c.some_column).like(func.upper('some text')) doen

Python:

filter = func.upper(some_table.c.some_column).like(func.upper('some text'))

[ Voor 12% gewijzigd door Wolfboy op 17-04-2010 19:43 ]

[cariolive23]

@Wolfboy: Wordt dan de SQL-functie UPPER() gebruikt? Zo niet, dan is de deze functie onbruikbaar, je moet dit namelijk in SQL doen, wil je de index kunnen gebruiken. In dit geval een index met UPPER en uiteraard niet met LOWER zoals eerder is voorgesteld. Je moet hier de database het werk laten doen, daar is deze heel erg goed in.

[Wolfboy]

cariolive23 schreef op zaterdag 17 april 2010 @ 20:46:
@Wolfboy: Wordt dan de SQL-functie UPPER() gebruikt? Zo niet, dan is de deze functie onbruikbaar, je moet dit namelijk in SQL doen, wil je de index kunnen gebruiken. In dit geval een index met UPPER en uiteraard niet met LOWER zoals eerder is voorgesteld. Je moet hier de database het werk laten doen, daar is deze heel erg goed in.

Uiteraard, anders had ik het niet als oplossing voorgesteld

Binnen SQLAlchemy werkt "func" als een wrapper voor elke functie in de database, je kan dus ook je eigen database functies laten uitvoeren op die manier. Het wordt allemaal netjes vertaald.

[pedorus]

Dan nog is lower() meestal sneller aangezien tekst veelal vooral uit kleine letters bestaat, en er dus minder conversiehandelingen hoeven te worden verricht. Overigens is idealiter geen tolower/toupper nodig bij vergelijkingen, maar ik weet niet hoe dat hier gaat uitpakken.

[Wolfboy]

Daar heb je gelijk in, ik ga alleen standaard voor UPPER aangezien Django ORM bij case-insensitive vergelijkingen voor UPPER kiest Dus de index werkt dan voor zowel SQLAlchemy als Django

Het voordeel van UPPER is alleen wel dat het beter herkenbaar is

[ Voor 15% gewijzigd door Wolfboy op 18-04-2010 01:06 ]

[Boudewijn]

Hmm het probleem lijkt iets dieper te liggen dan gedacht, en ik snap eigenlijk niet 1-2-3 hoe deze functie werkt:
De code is niet van mijzelf, maar ik ben er momenteel wel verantwoordelijk voor.

Python:

  @authorize(HasAuthKitRole(["web"]))
  def search(self, type='', attribute=''):
    if type == '':
      type = pylons.request.params.get('type',"")
    value = pylons.request.params.get('value',"")

    # the type of the object to search.
    c.type = type
    c.value = value.lower()

    if type not in self.searchable_objects:
      abort(404)
    # Can't search for other users, unless having admin role.
    if type == "User" and not h.authrole("admin"):
      abort(403)
    c.object_list = getattr(self, "_get_list_%s" % type)(value, attribute)
    return render("/Search/%s.mako" % type

Op zich redelijk logisch, maar ik snap niet gode wat er in die get_list functie gebeurt.
Sowieso vind ik het niet echt leesbare code, er zijn 3 types waar je op kunt zoeken... waarom dan dit soort code ipv een minder compact doch leesbaarder if-statement.

Maar goed, het is dus zo dat als ik het type "Site" heb, de code hierboven _get_list_Sites aanroept:

Python:

  @authorize(HasAuthKitRole(["web"]))
  def _get_list_Site(self, value,attr):
    cols = ["SiteLocationCode", "SiteNameSN", "AddressStreetAndNumber", "AddressZipcode", "AddressCity", "Comment"]
    if attr != 'any' and attr in cols:
      cols = [attr]
    return self._get_list(models.Site, cols, value)

Okay die 'cols' zijn inderdaad de kolomnamen in de DB, en de any waarde wordt gebruikt om aan te geven dat dat veld niet gespecificeerd is.

Echter snap ik niet waaorm hier die cols array overschreven wordt.
En als kers op de slagroom:

Python:

  def _get_list(self, modelClass, cols, value):
    query = models.Session.query(modelClass)
    all_contains = [self._contains(modelClass, col, value) for col in cols]
    return query.filter(or_(*all_contains)).all()

Echter snap ik niet helemaal hoe ik met name in deze laatste functie die upper-casing moet in bouwen.
Kan iemand me enigzins uitleggen hoe deze functie werkt?
Ik neem aan dat die all_contains alle kolommen voor het tupel met waarde value bevat.


De functie _contains:

Python:

  def _contains(self, modelClass, column, value):
    return func.lower(getattr(modelClass, column)).like("%%%s%%" % value.lower())

Volgems mij zit namelijk in die _contains functie het hele lowercasing (en dus capital insensitivity verhaal) al, en wordt er feitelijk op *value* gezocht, maar werkt dit niet.

Kan iemand me een bump geven?

[pedorus]

Was dit framework er niet voor om dingen 'makkelijker' te maken?

Als ik even zoek op pylons/ilike vindt ik dit waaruit blijkt dat er gewoon een ilike is. Aangezien je toch geen indexen kan gebruiken bij ilike '%string%', waarom gebruik je dat niet gewoon?

[Boudewijn]

Hmm en dat was inderdaad de oplossing. Lang zitten prutsen op een fix van (uiteindelijk) 1 karakter.