TIL: regex în mysql

TIL: regex în mysql

Printr-o alinere extrem de nasoală a planetelor, împreună cu zeița bazelor de date supărată, am nimerit într-o situație foarte mișto. Pe scurt: versiune veche a DB. Mai pe lung:

Datorită structurii, cea mai bună soluție pentru a stoca niște documente în DB mi s-a părut a fi ceva ne-relațional. Mă uit în manualul MySQL: suportă JSON de ceva timp. Perfect! Pe local am instalat ultima dată MariaDB acum vreo 1-2 ani, deci e OK. Pe server nu mai verific, că sigur nu stau ăștia cu versiunile atât de vechi.

Care-i treaba cu serverul? Clientul pentru care dezvolt șmecheria asta folosește Pantheon, WP Engine & co, deci nu am nici un control asupra ce este instalat acolo

Mă apuc, fac queries cu JSON, dezvolt tot ce trebuie, fac deploy. Pe server avem Maria DB 10.0. Ultimul release în 2015. EVIDENT că are fix zero support pentru JSON!

Deci sunt într-o dilemă: ori conving clientul să renunțe la provider ori migrez datele pe ceva relațional. Deci am relaționat datele, că de fapt asta era singura opțiune. Și am făcut asta în doi pași (mai simpli decât mă așteptam):

  1. Am făcut o tabelă documents ce conține minimum de coloane: id, title, abstract, content.
  2. Am făcut o tabelă document_meta ce conține patru coloane: id, document_id, meta_key, meta_value.

Eh, iar șmecheria abia acum începe: dacă pentru majoritatea datelor structura asta este OK, aveam un element mai … deosebit: geography. Care este un array multidimensional de forma:

[
  {"state": "Colorado", "province": "Aspen"},
  ....
]

Provocarea era să număr toate documentele specifice unui anumit stat (sau provincie) și să le ordonez după acest număr. După căutări și multă lectură, am reușit treaba asta:

SELECT REGEXP_REPLACE( 
    REGEXP_SUBSTR(meta_value, '"state":"[^"]+"'), 
    '"state":"([^\"]+)"' , 
    '\\1' ) as state, COUNT(*) as count
FROM documents doc
    INNER JOIN document_meta meta ON meta.document_id = doc.id
WHERE
    meta.meta_key = 'geography'
GROUP BY state
ORDER BY count DESC

E prima dată când lucrez cu regex în SQL iar senzatia este… ciudată. N-aș putea spune că mi-a plăcut în mod deosebit sau că a fost din cale-afară de intuitiv.

Mi s-a readus aminte că niciodată nu trebuie să fac presupuneri despre mediul în care rulez ceva. Mai ales dacă nu am nici un fel de control asupra acelui mediu.

All in all, a fost o zi bună, am învățat chestii. Fu bine.

2 Comentarii

Ionuț Staicu a scris

@Silviu: Vorbit la hosting, nu se poate (în ciuda faptului că se plătesc niște sute de dolari lunar).

Oricum, tot răul spre bine: acum pot face niște queries ce nu puteam (adică nu știam) să le fac cu JSON (e.g. numărarea și sortarea de mai sus dar în două dimensiuni, e.g. după numărul statului ȘI al regiunii) sau, dacă îmi ieșea ceva, era foarte lent. (bine, nu este nevoie, dar mno :D

Adaugă un comentariurăspuns pentru

Poți adăuga bucăți de cod folosind [code]codul tău aici[/code], [js][/js], [php][/php] etc.

Link-urile în context sunt binevenite. Comentariile fără nume/email valid sunt șterse.
PS: Comentariul NU este editabil.

Acest sit folosește Akismet pentru a reduce spamul. Află cum sunt procesate datele comentariilor tale.

Site-ul blog.iamntz.com utilizează cookie-uri. Continuarea navigării presupune acceptarea lor. Mai multe informații.

windows apple dropbox facebook twitter