Stored function in reference manager

Om het werk aan mijn blogs en mijn boekje te vereenvoudigen heb ik ooit eens een programmaatje geschreven waarin ik de citaten uit de boeken die ik lees bij kan houden. Ik typ de citaten over, zodat ik daar makkelijk doorheen kan zoeken en gezichtspunten met elkaar kan vergelijken en makkelijk terug kan vinden. Een vrij eenvoudig ding, maar wel iets dat zijn meerwaarde al een aantal keren heeft bewezen.

citatenmanagerOmdat ik op verschillende manieren met deze teksten werk, maak ik soms gebruik van APA5, soms van MD5 en soms van mijn eigen systeem met siglia. Een probleem van deze werkwijze is dat ik de referenties die ik uit mijn programma haal steeds met de hand moet aanpassen aan het formaat waarin ik ze wil hebben. Om dit te vereenvoudigen wilde ik het programma met een pulldown uitbreiden waarin ik kan aangeven in welk formaat ik de referentie wil hebben.

Een probleem dat daarbij om de hoek kwam kijken, was dat ik de tekst samen met het paginanummer eenvoudig als een blob in de database heb opgeslagen. Meestal eindigen de citaten met het paginanummer tussen haakjes, maar soms ook met de referentie in kwestie in APA5. Netter zou natuurlijk zijn om het paginanummer in een separate kolom op te slaan, zodat ik die in elk gewenst formaat kan ophalen.

Allereerst begon ik met de database een beetje op te schonen. Omdat het programma toch alleen maar door mezelf wordt gebruikt, heb ik me nooit echt bekommerd over de netheid van de data, maar nu ik er toch mee bezig ging, wat het een kleine moeite dit aan te passen. Dus lege citaten uit de database, harde returns aan het eind van het citaat eruit en er voor zorgen dat alle citaten inderdaad eindigen met een haakje sluiten. Als dat allemaal gebeurd is, de tabel aanvullen met een kolom voor het paginanummer:

> delete from quotes where quote like '';
> update quotes set quote=trim(trailing '\n' from quote);
> update quotes set quote = replace(quote, ').' ,')') where quote like '%).';
> alter table quotes add column page integer unsigned default 0;

Nu leek het me zaak om uit te zoeken wat de algemene structuur was van de paginanummers aan het eind van de referenties. Hiervoor maak ik uiteraard gebruik van reguliere expressies, die in mysql echter iets anders werken dan ik gewend ben.

mysql> select '(123)' regexp '.\d\d\d.';
+---------------------------+
| '(123)' regexp '.\d\d\d.' |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0,00 sec)

maar

mysql> select '(123)' regexp '.[0-9]+.';
+---------------------------+
| '(123)' regexp '.[0-9]+.' |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0,00 sec)

Op zich was dit uiteraard wel afdoende om de paginanummers te identificeren, maar naar bleek bevat mysql geen mogelijkheid om op basis van reguliere expressies en backreferencing waardes te vervangen. Dus moest het maar op een pragmatischer manier.

Eerst de edge-cases maar eens identificeren, de quotes die niet eindigen met het paginanummer tussen haakjes:

select count(id) from quotes where quote not regexp '.*\([0-9]+\)';
select id, right(quote,5) from quotes where quote not regexp '.*\([0-9]+\)';

Dit bleken er op de life database slechts enige tientallen te zijn. Op een totaal van een kleine tweeduizend citaten, kunnen we dit aantal ofwel negeren ofwel later met de hand aanpassen, mocht dat nodig blijken te zijn. Moeilijker is het vinden van citaten waarin niet alleen het paginanummer in staat, maar ook al het boek waar het uitkomt, zoals

" contemporary nihilism. (Dreyfus and Keyll 2011, 214)"

Het vreemde was echter dat deze vorm ook in de selection hierboven terugkomt.

mysql> select ' contemporary nihilism. (Dreyfus and Kelly 2011, 214)' regexp '.*\([0-9]+\)' as f;
+---+
| f |
+---+
| 1 |
+---+
1 row in set (0,01 sec)

Wat natuurlijk raar is, want (Dreyfus and Kelly 2011, 214) voldoet niet aan het patroon .*\([0-9]+\). Na enig zoeken bleek je in mysql de haakjes twee keer te moeten escapen om ze in een regex op te kunnen nemen:

mysql> select 'D1' regexp '\([[:digit:]]+\)' as f; // true
mysql> select 'D1' regexp '\\([[:digit:]]+\\)' as f; //false
mysql> select '(D1)' regexp '\\(D[[:digit:]]+\\)' as f; //true

Dus:

select '(Dreyfus and Kelly 2011, 214)' regexp '.*\\([0-9]+\\)' as f; //false 

en

select '(Dreyfus and Kelly 2011, 214)' regexp '.*\\([A-Za-z0-9 ]+, [0-9]+\\)' as f; //true

en

select '(214)' regexp '.*\\([A-Za-z0-9 ]+, [0-9]+\\)' as f; //false

Dus het identificeren van die citaten lukt wel, maar wat dan? Zoals gezegd is er in mysql geen preg_replace, maar we kunnen wel uitgaan van die spatie rechts en dan links gaan tot de eerste spatie: dat is het paginanummer. Dat kunnen we dan in de nieuwe kolom zetten en vervolgens het gedoe van begin tot eind spatie weghalen. Dus we moeten een stored function maken die het getal teruggeeft op basis van dat haakje sluiten en de spatie.

We kunnen er van uitgaan dat het laatste karakter het haakje sluiten is (dat hebben we immers hiervoor al bewerkstelligd). Er zijn ook geen citaten die op een paginanummer staan met vier cijfers of meer, maar er zijn wel citaten met één cijfer. Dus de eerste spatie of het eerste haakje openen na het einde van de string is het einde van het getal dat we zoeken. Met substring kunnen we dat relatief eenvoudig terugvinden. Dit levert allemaal de volgende use-cases op:

foobar, 123) → 123
foobar, 12) → 12
.(foobar, 1) → 1
bla (as, 1) → 1

In de stored function die we gaan moeten is het dus zaak om eerst te kijken of de referentie überhaupt eindigt met een haakje-sluiten. Als dat zo is, dan lopen we van achteren naar voren over die string tot de eerste spatie of het eerste haakje openen: wat daartussen zit is het paginanummer. Ik moest wel weer even kijken hoe dat ook al weer zat met de if-syntax in mysql (gelukkig was ik diezelfde dag bezig Y te helpen met haar programmeeropdracht die in Pascal moest – wat een vergelijkbare syntax heeft), maar uiteindelijk werd de hele functie als volgt:

delimiter $$
create function get_page_number(p_reference char(10)) returns integer
begin
  declare x int;
  declare rv varchar(6);
  declare tmpstr char(1);

  set x = length(p_reference)-1;
  set rv = '';

  if substring(p_reference, length(p_reference),1) != ')' then return -1;
  end if;

  while x>0 do
    set tmpstr = substring(p_reference, x, 1);
    if tmpstr=' ' or tmpstr='(' then
      set x=-1;
    else
      set rv = concat(tmpstr, rv);
      set x = x-1;
    end if;
  end while;
  return cast(rv as unsigned);
end$$
delimiter ;

Nu is het zaak van alle referenties deze cijfers op te halen en die in de nieuwe kolom page te zetten. Initieel ziet het er goed uit:

mysql> select right(quote,6) as org, get_page_number(right(quote,6)) as page from quotes limit 10;
+--------+------+
| org    | page |
+--------+------+
| . (15) |   15 |
| . (17) |   17 |
| . (19) |   19 |
| . (19) |   19 |
| . (21) |   21 |
| . (46) |   46 |
| . (47) |   47 |
| . (47) |   47 |
| . (48) |   48 |
| . (49) |   49 |
+--------+------+
10 rows in set (0,00 sec)

Maar wanneer ik dat over de gehele dataset doe, krijg ik een foutmelding waar ik niet aan had gedacht:

mysql> select right(quote,6) as org, get_page_number(right(quote,6)) as page from quotes;
ERROR 1292 (22007): Truncated incorrect INTEGER value: '51-53'
mysql>

Er zitten natuurlijk ook referenties in die over de pagina heengaan, dus die dit formaat hebben. Dat betekent dat de nieuwe kolom page niet een integer maar een string moet worden, en dat de lengte van het return-type maximaal zeven kan worden (\d\d\d-\d\d\d is het uiterste wat voor kan komen).

alter table quotes drop column page;
alter table quotes add column page varchar(16);

Dit vereiste ook een paar wijzigingen in de stored function hierboven: hij moet geen integer maar een varchar(8) teruggeven, hij moet iets langere strings als input krijgen en de cast onderaan is niet nodig. Uiteindelijk heb ik de input en de output van de functie even lang gemaakt, want er bleken ook referenties in de database te zitten die het algoritme niet op tijd deden stoppen. Hiermee kon ik de hele dataset aanpassen:

mysql> update quotes set page = get_page_number(right(quote,16));
Query OK, 1183 rows affected (0,06 sec)
Rows matched: 1183  Changed: 1183  Warnings: 0

Om de referentie uit het citaat zelf te verwijderen, maakte ik een tweede functie die heel veel lijkt op de eerste – duplicatie, ik weet het, maar dit is als het goed is toch maar een one-off:

create function remove_reference(p_quote blob) returns blob
begin
  declare x int;
  declare tmpstr char(1);

  if substring(p_quote, length(p_quote),1) != ')' then return p_quote; 
  end if;

  set x = length(p_quote);

  testwhile: while x>0 do 
    set tmpstr = substring(p_quote, x, 1);
    if tmpstr='(' then leave testwhile;
    else set x = x-1;
    end if;
  end while;
  
  return substring(p_quote,1,x-1);
end

Het enige wat nog even lastig was, is dat je een kolom in mysql niet kunt updaten met waarden uit diezelfde kolom. Dat is natuurlijk eenvoudig op te lossen met een tijdelijke kolom:

> alter table quotes add column quote_new blob;
> update quotes set quote_new=remove_reference(quote);
> alter table quotes drop column quote;
> alter table quotes change column quote_new quote blob;

Nu moet ik aan de voorkant wat dingen veranderen om die goed weer te kunnen geven, maar dat is een volgend project.

Leave a comment

Your email address will not be published. Required fields are marked *