Provocarea de azi (bine, de ieri a început): cum țin evenimente (de calendar) în DB? (e mai mult rant, dar dacă are cineva idei promit că nu mă supăr )
Evenimentele pot fi:
la oră fixă (e.g. 1000)
interval de ore (e.g. 1000-1200)
zi întreagă
interval de zile
recurente (în fiecare zi/săptămână/lună/an sau la fiecare X zile/săptămâni/luni/ani), cu sau fără dată finală (i.e. „se repetă până când zicem noi că nu se mai repetă”)
Cerințe:
să pot interoga toate evenimentele (de la început până la sfârșitul lumii)
să pot filtra evenimentele valide (i.e. cele care nu au avut încă loc SAU care nu s-au terminat încă)
să pot filtra evenimentele în anumite intervale (e.g. în săptămâna/luna curentă)
să le pot sorta după data în care au loc
Am mai avut de implementat sisteme asemănătoare dar fără recurență și nu au fost probleme. Însă recurența îmi dă bătăi de cap la sortare/filtrare.
Soluția naivă/brutală este de a stoca toate recurențele în DB și aia e (i.e. fiecare recurență este un eveniment nou). Mi se pare amatoricesc (e.g. pentru actualizări, evenimente care sunt zilnice și se întind pe ani…)
Soluții mai elegante sunt în articolele de mai jos, dar apare problema: cum sortez un eveniment recurent care a pornit în trecut? De exemplul:
am un eveniment zilnic (E1), la ora 9 recurent pornit luna trecută.
am un eveniment unic (E2), mâine la ora 6
Când afișez evenimentele de mâine, vreau să fie afișate în oridinea naturală (E1, E2).
Problema cea mai dureroasă este alta: nu știu dacă nu știu eu să fac asta sau dacă chiar este atât de dificil
Cu ocazia asta am aflat că această problemă nu este atât de simplă pe cât pare, existând nenumărate articole care tratează diverse abordări:
Dacă folosești paginare pentru rezultate o mai scoți cumva la capăt.
Eu de exemplu pentru evenimente recurente generam efectiv evenimentele la pachet cu celelalte pentru cele care se încadrau în fereastra de căutare. Fereastră care nu e niciodată mai mare de 1 an să zicem.
Pentru că la calendar nu poți lucra cu paginație pe bază de items, ci doar cu bază de time interval, puteam face în 2 etape, prima oară scoteam evenimentele normale, apoi generam records/models on the fly (nu în baza de date, doar runtime în cod) pentru cele recurente și abia apoi sortam la final (nu puteam sorta folosind baza de date neaparat).
Pentru mine a funcționat și merge și azi. Evident, cu cât e mai mare intervalul de timp devine mai nefezabil.
In primul rand, trebuie topic separat pentru asta.
Eu am abordat la un moment dat asa: am salvat datele initiale, adica modul in care sunt definite evenimentele de catre utilizator, apoi am calculat recurenta lor pe intervalul de timp dorit. Ca sa simplific, calculam evenimentele pe luna urmatoare in fiecare 1 ale lunii. Cu ocazia asta am aflat ca PHP are functii foarte puternice de calcul a datelor.
That’s a problem for a future me. (la proiectul curent nu va fi cazul, deoarece se lucrează doar pe un singur timezone)
Asta e varianta brutală de care ziceam mai sus și pe care aș vrea să nu o folosesc decât dacă nu am de ales, pentru că în cazul meu va aduce alte bătăi de cap (e.g. actualizarea datelor).
Cum ar arăta un query pentru evenimentele curente ordonate de la cel mai recent la cel mai departe în viitor? Asta mă roade cel mai tare
De ex. am următoarele evenimente:
Recurent, zilnic, la ora 0900 începând cu 1 iunie
Recurent, săptămânal, la ora 1100, începând cu 3 iunie
Care se întinde pe mai multe zile, în perioada 6-11 iunie
Unic, pe 10 iunie, la ora 1000
Vreau să fac … ceva care îmi afișează toate evenimentele din ziua de 10 iunie, în ordinea asta: 3, 1, 4, 2.
Ignorând pentru moment timezones și alte edgecase-uri (e.g. sărbători, zile libere)
Referitor la DB: (cred că) am înțeles articolul ăsta și mi se pare că are sens așa. Dar nu îmi dau seama cum aș putea face un query să îndeplinească cerințele de mai sus
calculezi toate evenimentele definite si retii doar pe cele din perioada care te intereseaza. Ai luat in calcul si definitii de genul “ultima vineri din luna”, “vinerea o data la 3 luni”, etc ? Calendarul e un lucru al naibi de complicat, asta ca sa stii de ce iti meriti cei 10000 eur pentru proiectul asta.
Eu as crea un tabel de Events in care fiecare event sa aiba o data fixa sau sa fie pe perioada si ii pui un duration in secunde.
Asa mereu poti calcula perioada in care e activ eventul fara sa ai un start/end date ci pui start date si duration.
Creezi field-uri dinamice in baza de date, precum type = ALL_DAY daca duration = 86400, type = WEEKLY daca duration = 60480… sau type = CUSTOM
Daca e recurent ai grija la event-urile din trecut, posibil nu vrei sa le modifici deci nu e neaparat ok sa pui recurent = true/false. Eu as rezolva recurenta cu un expire_date, adica o data la care nu mai e recurent, expire_date = start_date inseamna ca e event pe o data fixa, expire_date = null/gol inseamna ca e recurent, expire_date = data fixa inseamna ca e recurent pana la data specifica.
Eventual fiecare event il pui ca si un cronjob si ai rezolvat problema, poti sa le rulezi si la 2 saptamani, doar in anumite zile…
start=9/23/2019 12:00:00 PM; end=9/23/2019 1:00:00 PM, recurence="Weekly on Monday, until June 29, 2020"
în evenimente discrete… e f ușor de lucrat cu ele în felul ăsta…
Efectiv adaugi evenimente pt fiecare zi de luni de la 12pm la 1pm, începînd din Sept 9 pînă pe 29 iunie anul următor
Sau ai un cron job care încercă să “execute” evenimentele la fiecare zi/oră dacă recurența se potrivește cu ceasul sistemului… Nu știu cum le-ai afișa intr-un calendar dar nu e imposibil
o tabela cu evenimentele unice si id-ul tipului de recurenta si statusul instantelor (generate/negenerate sau poate si dezactivate ca sa acoperi si acel scenariu), start_date, end_date
o tabela cu toate instantele de evenimente legate prin id-ul de eveniment de cealalta
o tabela cu tipurile de recurenta si un rank hardcodat pentru fiecare
La crearea fiecarui eveniment il adaugi in tabela cu status “negenerate” pt instante. Periodic sau dupa crearea fiecarui eveniment rulezi un script care creeaza instantele pentru evenimentele noi in tabela 2. si schimba statusul in ‘generate’ in tabela 1. Pentru query-ul ala le grupezi pe zile si le ordonezi pe cele din aceeasi zi dupa rank-ul hardcodat de tine.
LE: am citit in mesajul origca ti se pare “amatoriceasca” abordarea asta, mie nu, pentru ca te salveaza de debugging ulterior cand se incaleca vreo regula, si nu trebuie de fiecare data sa intelegi toaata logica pentru a putea modifica ceva. Uneori solutia cea mai simpla e cea mai buna.
Pt a putea afla dintr-un singur query in mod eficient dpdv al performantei e nevoie intr-adevar ca recurenta sa fie materializata. Asadar fiecare instanta e evenimenutului recurent sa fie un record intr-un tabel. Pt a trata corect cazul “recurent pana la Sf Asteapta” as adauga in master table (nr 1 din lista lui @clickio) si un camp care sa imi spuna pana la ce data am materializat acel record, ca sa pot merge mai departe fara logica suplimentara.
Trebuie desigur tratat cazul in care se cere data 2025-01-25 dar recurentele au fost materializate doar pana la final de 2024. Un trigger (nu neaparat db trigger, nu sunt fană a logicii in db) poate face preinsertia elementelor lipsa inainte de returnarea rezultatelor.
Daca luam cazul unui eveniment cu recurenta la 2 saptamani (cazul meu, community of practice intern) care de ex pica intr-un bank holiday, am nevoie de granularitatea de a face cancel doar la una din instante
PS: n-ar fi rau dupa ce i se aproba lui @iamntz solutia adunata de pe forum sa primim un badge ceva. Ultima data cand am facut un contract similar (problema mult mai complexa decat e descris mai sus) am livrat in 2 luni si am facut bani frumosi de tot la a 3a mana.
P.S. Nu, nu stochezi in db toate evenimentele de la inceputul lumii until the end of time…. Stochezi o regula intr-un format cunoscut. Apoi regula aia o citesti si o interpretezi.
se iau toate evenimentele din sql (din trecut și viitor)
se filtrează și sortează în funcție de dată din PHP
se afișează
Treaba asta durează aproape un minut să se execute. Dacă pe frontend este ok, că toate stau în cache, în backend rupe, că se modifică relativ frecvent.
În naivitatea mea am estimat că e gata în maximum 5h.
Ok, țin treaba aia în DB, dar cum interoghez aceste date (în mysql, dacă are vreo importanță)?
Soluția pe care cred că o voi adopta este o struțocămilă din ce s-a discutat aici și din ce am citit online:
un tabel foarte dumb: event_id | event_start | event_end (am nevoie și de end pentru a putea filtra evenimentele care sunt în desfășurare)
savez aici toate combinațiile posibile pentru un eveniment, tratând evenimentele recurente ca entități distincte
când afișez datele fac un join între tabelul de la punctul 1 și evenimentele reale
tabel 1: ce alege userul din interfata (da si mie un biweekly miercurea la 2pm durata 45min pana la pastele cailor / pana la craciun); aici ai o coloana extra in care zici “si by the way am creat inregistrari in tabelul 2 pana la final de 2022, ‘sa fie’”)
tabel 2: fiecare instanta a orice e declarat in tabel 1; pt luna august 2022 materializarea recordului de mai sus va crea 3 intrari (3 aug, 17 aug, 31 aug); aici pt rapiditatea interogarii recomand coloana de start si end sa poti face comparatii contra lor cu ce iti ofera nativ dbul
@clickio zicea sa faci si tabel 3 ca sa ai recurentele (1=biweekly, 2=weekly; 3=onetime, etc)
ca sa nu creasca tabel 2 “cat china” recomand tabel 4: arhiva; aici muti tot ce s-a intamplat deja — la noapte cureti ziua de azi, ca sa nu fie lumea cu read-heavy sa doamne feri le mearga incet interogarea
orice interogare ai nevoie o faci pe tabel 2 direct; cu indecsi pe datele de start si end; e un tabel read-heavy pe care il vrei mic si eficient
caz exceptional vine userul si cere o data despre care tu stii ca ai recurente dar nu le-ai generat inca in tabel 2 (ex halloween); te pui si le adaugi —> 1 query pe tabel 1 “am de toate?” + 1 insert multiplu pe tabel 2 + 1 read pt ce aveai de fapt de facut
corect, insa cacheurile nu se fac in db (bottom of the stack), ci pe edge la apllication layer, si se aleg tehnologii care fac singure eviction, nu il scrii tu manual
Cazul 1: citesc din db evenimentul cu id 77. Vand ca e recurent, o data la doua saptamani, incepand de la event_date pana la sfarsitul timpului. Super. Asta se vede cititnd tabel 1 si rrule din tabel 2. Rrule o interpretezi in cod(ai librarii in toate limbajele), si arati eventul in interfata.
Cazul 2: userul se duce in interfata pe o recurenta a eventului 77 si vrea sa il stearga. Userul e intrebat imediat daca doreste stergerea intregii recurente sau doar a evenimentului din acea zi.
Cazul 2.1 userul alege sterg toata recurenta. Stergi event_id din tb cu tot cu recurrence_id aferent. Ez.m
Cazul 2.2 userul alege sa stearga doar evenimentul din aceazi punctuala. Aici e mai complicat dar nu imposibil. Evenimentul se va imparti in 2. Prima parte ramane asa cum era, cu recurenta modificata de la start date pana in ziua stergerii. Al doua parte va insemna crearea unui alt event in db, din data disponibila urmatoare dupa stergere, cu recurenta si rrule aferente(o data la doua saptamani, until the end of time).
)
Tu le pastrezi UTC, dar utilizatorul din brazilia le seteaza/consuma pe timezone-ul sau.
