[SQL] Zoek-index custom filter (à la Filemaker rekenveld)

[benoni]

Hier draait een database in MySQL, waarin de lokale fileservers een centrale zoek-index bijhouden voor alle archief- en werkbestanden. De gebruikers kunnen bestanden opzoeken en openen met behulp van een Dashboard widget. De basisgegevens staan in een enkele datatabel (id, disk id, node id, bestandsnaam, pad, datums enz.). De applicatie heeft momenteel een miljoen bestanden geïndexeerd.

Nu hebben de meeste bestanden artikelcodes in de naam of in de metadata. Alleen hebben die codes niet altijd dezelfde schrijfwijze. Artikelnummers zijn de ene keer geformatteerd met punten of streepjes, de andere keer is het een volledig numerieke code met vulnullen. Volledige standaardisatie is niet makkelijk te realiseren omdat materiaal wordt aangeleverd door allerlei verschillende leveranciers.

Voor het zoeken gebruik ik standaard fulltext boolean mode, alleen als een gebruiker wildcards gebruikt dan wordt een WHERE ... LIKE ... statement. Dat werkt op zich al redelijk, maar we zouden graag wat meer intelligentie in de database in willen bouwen m.b.t. de artikelnummers, zodat je niet telkens dezelfde code met en zonder puntjes, streepjes of wat al dies meer zij hoeft te proberen.

Vroeger werkte ik wat meer met Filemaker Pro en in die omgeving lag de oplossing voor de hand: je gebruikte een berekeningsveld als basis voor de zoek-index, je hing daar een formule in die de teksten en codes uit de andere velden precies zo kon filteren en combineren als de gebruiker het wilde. Ideaal voor mensen die alles vanuit een enkel invoerveld willen kunnen vinden, met een functie erachter die 'begrijpt' wat ze zoeken. Inderdaad, het zijn Mac gebruikers

Voor deze applicatie wil ik het graag bij een open architectuur houden, makkelijk uit te breiden en te combineren met andere software (dus zoiets als het nu is, op basis van een standaard SQL backend). Maar in MySQL kon ik niet zoiets als een rekenveld vinden. Uiteraard kan ik de filterfunctionaliteit verplaatsen naar de interface, nog beter kan ik het in een stored procedure verpakken, maar dat haalt de flexibiliteit uit het ontwerp (je kunt dan bijvoorbeeld niet zomaar een zoek-en-vervang doen in een kolom). Ik zoek eigenlijk een soort stored procedure die je aan een index kunt hangen, zodat het altijd vanzelf wordt bijgewerkt.

Kan dit wel? Hoe pakken jullie zoiets aan?

[PolarBear]

Kan je niet voor het zoeken de artikelnummers ontdoen van alle niet numerieke tekens en in een apart veld zetten?

[benoni]

Dat kan uiteraard, maar het is minder flexibel. De functionaliteit is dan verplaatst naar een tussenlaag, het zit niet in de database zelf. Stel dat ik op het veld waar het artikelnummer vandaan komt een zoek/vervang actie uitvoer (hoeft niet per se de bestandsnaam te zijn, er zijn ook metadata (koppel)velden waar het in kan staan) dan wordt het ingedikte artikelnummer niet automatisch bijgewerkt. Ik zou dan of echt alle bewerkingen via een abstractie layer moeten laten lopen of een soort van update functie moeten verzinnen die de wijzigingen bijhoudt (misschien aan de hand van de binlog of zo )...

[Remus]

benoni schreef op donderdag 04 juni 2009 @ 19:34:
Dat kan uiteraard, maar het is minder flexibel. De functionaliteit is dan verplaatst naar een tussenlaag, het zit niet in de database zelf. Stel dat ik op het veld waar het artikelnummer vandaan komt een zoek/vervang actie uitvoer (hoeft niet per se de bestandsnaam te zijn, er zijn ook metadata (koppel)velden waar het in kan staan) dan wordt het ingedikte artikelnummer niet automatisch bijgewerkt. Ik zou dan of echt alle bewerkingen via een abstractie layer moeten laten lopen of een soort van update functie moeten verzinnen die de wijzigingen bijhoudt (misschien aan de hand van de binlog of zo )...

Heeft MySQL geen triggers? Dan kan je namelijk op het bijwerken van het veld met het artikelnummer een actie zetten om de metadata bij te werken.

[benoni]

Hé, inderdaad, dat ziet er goed uit... daarmee zou je een automatisch een stored procedure moeten kunnen laten aanroepen die zoeksleutels op een rijtje in een veld wegschrijft. Precies zoals ik het bij Filemaker deed. Of beter nog: een aparte koppeltabel met zoektermen... Bedankt!

[JaQ]

Remus schreef op vrijdag 05 juni 2009 @ 08:47:
Heeft MySQL geen triggers? Dan kan je namelijk op het bijwerken van het veld met het artikelnummer een actie zetten om de metadata bij te werken.

Dit is wat omslachtig met een trigger, ik opteer voor een "virtual column". http://forge.mysql.com/wiki/MySQL_virtual_columns_ref_manual