Help! MySQL

Marius '95

Membru Senior
Sugar daddy
Joined
Nov 13, 2005
Location
Brăila
Am ajuns la un punct unde mi se incarliga mintea. Please help, (again)!

- tabel probe: SID cheie primara
- tabel rezultate: SID, DateV data validarii (poate fi null), MID metoda de lucru (DateV completat apare o singura data pentru fiecare combinatie SID+MID)
- tabel metode: MID cheie primara, TID analiza de care apartine metoda
- tabel analize: TID cheie primara
- tabel comenzi: QID cheie primara
- tabel analize comandate: (QID,TID) cheie primara unde QID e comanda si TID e analiza, SID proba din care se va lucra analiza

Pentru un QID dat, imi trebuie o comanda prin care sa obtin o lista cu analizele comandate (TID) si, la fiecare analiza, data validarii (DateV) pentru oricare rezultat validat sau NULL daca nu-i nici unul, al oricarei metode de lucru a acelei analize.
SELECT
analizecomandate.TID,
rezultate.DateV
FROM analizecomandate
[...] si aici urmeaza un JOIN circular pe care nu stiu sa-l rezolv
WHERE QID=ceva

Calea cea mai scurta, suntand tabela cu probe si aia cu analize ar fi asa:
SQL join circular.png
 
Last edited:

Marius '95

Membru Senior
Sugar daddy
Joined
Nov 13, 2005
Location
Brăila
Ah! Ce simplu era. :tongue: Eu ma gandeam ca se supara daca vede m.mid=r.mid, ele fiind luate din join-uri diferite.
Multumesc!
 
Last edited:

Marius '95

Membru Senior
Sugar daddy
Joined
Nov 13, 2005
Location
Brăila
Inca o problema:
Am un formular web care afiseaza niste randuri din baza de date. Multe randuri. Utilizatorul poate modifica datele => submit form. Programul care proceseaza datele primite trebuie sa salveze modificarile in baza de date, dar fara sa suprascrie vechile inregistrari, ci adaugand randuri noi cu timestamp diferit. Cum fac?

- Varianta 1: compar fiecare rand primit cu cel existent in baza de date. Modificat => insert.
Avantaje: detecteaza si modificarile concurente facute de altcineva.
Dezavantaj: Ma astept sa fie foarte-foarte lent.

- Varianta 2: adaug in formular campuri ascunse cu valorile vechi. La submit, compar valori noi cu vechi. Modificate => insert.
Avantaj: nu vad nici unul.
Dezavantaj: mai rapid ca 1, dar tot lent. E si foarte complicat de scris, ca-s input-uri de diverse tipuri, in numar variabil la fiecare rand.

- Varianta 3: adaug in formular cate un checkbox ascuns si JS trigger onchange() pe fiecare <input>. Checkbox bifat => (optional verific ca la 1) insert. Nebifat => skip.
Avantaj: simplu de facut si rapid la executie.
Dezavantaj: JS = unreliable.

- Sunt curios sa stiu daca exista si o varianta cu insert direct toate randurile si sa se ocupe baza de date de salvat sau nu modificarile. Baza de date este momentan Firebird, dar e posibil sa ma rasgandesc. Sunt interesat de comparatii intre alternativele posibile.

Multumesc.
 

miahi

Wizzard
Sugar daddy
Joined
Aug 1, 2004
Location
Unreal Estate, Ankh-Morpork, Discworld
Orice ORM face asta în modul următor (combinație a tuturor variantelor tale):
- când se citește din DB se creează un obiect pentru fiecare rând (că de-aia e ORM)
- obiectul are flag de modified
- când se modifică obiectul din aplicație se setează flag-ul
- când trebuie persistat (salvat în DB), se iau toate obiectele care au flag-ul de modified, se verifică în DB că nu au fost modificate acolo (extern) și apoi se face update/insert după cum e nevoie

Cum tu nu folosești un ORM, va trebui să faci ceva manual. Aș zice varianta 3, dar JS e unreliable dacă nu știi ce faci, deci nu. Varianta 2 e prea complicată dacă nu folosești obiecte stocate în sesiune (și mi se pare că aplicația ta e stateless), deci nu. Varianta 1 rămâne - cu mențiunea că în mod normal NU vrei să detectezi modificările concurente în acest mod (pentru că le suprascrii fără warning, dar am mai vorbit despre asta). E lent pentru 5000 de rânduri, dar dacă faci chestia asta pe 5000 de rânduri ai probleme mult mai mari :smile:.

BTW, cu CNP-urile ca primary key: presupun că sunteți înregistrați ca procesator de date personale, dar GDPR is coming, v-ați desemnat Data Protection Officer (obligatoriu pentru procesatorii de date medicale)? Va fi foarte "plăcut" pentru el să afle cum sunt folosite datele personale în aplicație, având în vedere care sunt pedepsele pentru nerespectarea protecției - până la maximum dintre 20M€ sau 4% din încasările globale ale firmei. Ai făcut procedurile de ștergere de date personale din aplicație (oricine va putea face cerere pentru asta)? Ai făcut un modul de audit al accesului la datele personale (obligatoriu să loghezi cine/ce/în ce scop a accesat/modificat)? Ai scris script-urile de extragere a tuturor datelor personale pentru o persoană și prezentarea lor în format electronic (oricine va putea face cerere pentru datele lui)? Ați făcut procedura de identificare și raportare în 72 de ore a data breach-urilor (= orice expunere sau modificare neautorizată a datelor personale)? Cum datele medicale sunt probabil "special data", mă aștept să văd multe chestii interesante în presă în 6 luni :smile:.
 

Marius '95

Membru Senior
Sugar daddy
Joined
Nov 13, 2005
Location
Brăila
Cred ca o sa aplic totusi #3, cu mici modificari sa detecteze modificarile concurente - checkbox-ul va contine timestampul randului citit si va fi comparat cu timestamp-ul inainte de insert.
Datele NU se suprascriu. E cerinta ISO privitoare la audit. Deci va fi INSERT, nu update.
Aia cu GDPR va fi show. Ma bucur ca nu ma ocup eu de aspectele legale. Probabil ai dreptate cu CNP-ul. O sa ma gandesc cum sa modific.
 

AdrianB1

Membru Senior
Sugar daddy
Joined
Aug 3, 2004
Location
offline
Varianta 1 nu e deloc lenta daca o faci cum trebuie: arunci totul intr-o tabela si folosesti o procedura stocata care sa faca comparatiile si sa scrie ce e modificat. E mai rapid decat sa o faci din cod extern.
La varianta 2 poti sa simplifici destul de mult codul si nu bagi campuri ascunse, s-a inventat HTML data attributes. Asta inseamna ca la fiecare camp din formular ai valoarea noua in input si valoarea veche in data attribute, comparatia e banala, afli pe loc daca e ceva modificat sau nu si o poti face usor programatic (fara sa scrii cod pentru fiecare camp).

O combinatie intre 2 si 3 cu data attributes e cea mai buna, cred. La Submit verifici diferentele si le trimiti.
 

Marius '95

Membru Senior
Sugar daddy
Joined
Nov 13, 2005
Location
Brăila
La varianta 1 propusa:
Ma gandeam la un trigger BEFORE INSERT care sa dea o eroare custom (ignorata) daca nu-i nimic modificat.

La varianta 2/3 propusa:
Inteleg ca acea comparatie trebuie facuta de JS, ca acele data attributes nu par sa fie trimise cu POST. Daca da, nu inteleg cu ce ajuta fata de #3 chior. Mai explica un pic.

Niste detalii suplimentare:
Formularul este cel de introducere manuala a rezultatelor la analize. In prezent (posibil sa modific), formularul contine toate analizele posibile, nu doar cele solicitate de pacient, si toate metodele in uz curent la fiecare analiza. Desi sunt toate acolo, cu exceptia analizelor solicitate si a metodei default, restul vor fi randuri ascunse. Asta inseamna formular scurt pe ecran, dar lung la POST. Estimez 200-500 de randuri.
Daca o sa fie lent, pot usor sa renunt la toate analizele care nu-s cerute, cu riscul injuraturilor de la personal, ca in prezent se baga direct rezultate, nu inregistreaza nimeni cereri.
 

AdrianB1

Membru Senior
Sugar daddy
Joined
Aug 3, 2004
Location
offline
Nu, la varianta 1 nu faci asa ceva. Trimiti tot continutul formularului intr-o tabela, apelezi o sp care verifica cate o linie sau coloana (depinde cum le ai organizate) si scrie diferentele.

Da, comparatia e in JS. Chiar si la cateva sute de randuri, o sa ii ia maxim 1-2 sec si o sa trimita doar diferentele inapoi la server. Mai simplu de lucrat asa.

Data attributes iti permit practic ca la fiecare coloana de pe fiecare rand (sa zicem, fiecare input field) sa ai valoarea din celula si inca un numar de alte valori, de pilda valoarea veche, asa ca e foarte usor sa parcurgi tot formul si sa stii exact care sunt diferentele. Oricum daca ai datele organizate la nivel de linie trebuie sa scrii toata linia ca sa ai istoricul complet si usor de reprodus. Ideea e ca nu mai pui cate un flag de modificat la fiecare modificare sau iesire din camp, ci o faci la sfarsit la submit, foarte repede.
 

Marius '95

Membru Senior
Sugar daddy
Joined
Nov 13, 2005
Location
Brăila
Adica la submit sa parcurg formularul si sa fac ce anume? Sa dezactivez input-urile nemodificate?

Gata, merge. N-am mai facut stored procedure, ca POST-ul e mic dupa filtrarea cu JS si nu merita pentru .5s in minus.
Multumesc.
 

AdrianB1

Membru Senior
Sugar daddy
Joined
Aug 3, 2004
Location
offline
La parcurgere compari valoarea din input cu cea veche (stocata in data attribute) si daca sunt diferite le scrii in baza, altfel treci mai departe.
 

Marius '95

Membru Senior
Sugar daddy
Joined
Nov 13, 2005
Location
Brăila
Intr-o stored procedure, daca un INSERT da eroare, ce se intampla? Documentatia zice asa:
An EXCEPTION statement throws the user-defined exception with the specified name. An alternative message text of up to 1,021 bytes can optionally override the exception's default message text.
The exception can be handled in the statement, by just leaving it with no specific WHEN ... DO handler and allowing the trigger or stored procedure to terminate and roll back all operations. The calling application application gets the alternative message text, if any was specified; otherwise, it receives the message originally defined for that exception.
Deci sa inteleg ca in caz de eroare, operatiile sunt anulate (transaction rollback, desi eu n-am deschis o tranzactie in procedura) si executia se opreste la comanda care a dat eroare?
 

miahi

Wizzard
Sugar daddy
Joined
Aug 1, 2004
Location
Unreal Estate, Ankh-Morpork, Discworld
În mod normal procedurile sunt atomice, dacă nu specifici altfel. https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-transacs-statements.html

Savepoints and PSQL
Transaction control statements are not allowed in PSQL, as that would break the atomicity of the statement that calls the procedure. However, Firebird does support the raising and handling of exceptions in PSQL, so that actions performed in stored procedures and triggers can be selectively undone without the entire procedure failing.

Internally, automatic savepoints are used to:
  • undo all actions in the BEGIN...END block where an exception occurs
  • undo all actions performed by the procedure or trigger or, in for a selectable procedure, all actions performed since the last SUSPEND, when execution terminates prematurely because of an uncaught error or exception
Each PSQL exception handling block is also bounded by automatic system savepoints.
 

AdrianB1

Membru Senior
Sugar daddy
Joined
Aug 3, 2004
Location
offline
Nu stiu cum e in lumea lui Creanga, dar eu cand ma gandesc la casa parinteasca imi aduc aminte intotdeauna ca MS SQL trateaza orice comanda ca o tranzactie daca nu e inclusa manual intr-o tranzactie mai mare. Comanda se executa in temp si se scrie in log, daca reuseste e permanenta, daca esueaza se face drop si ramane la starea precedenta. In MySQL ar trebui sa fie la fel, dar nu bag mana in foc.
 

miahi

Wizzard
Sugar daddy
Joined
Aug 1, 2004
Location
Unreal Estate, Ankh-Morpork, Discworld
Tranzacțiile automate depind mult de implementare. Întotdeauna ai o tranzacție, diferențele sunt când se face commit (posibil transparent) la ea. Procedurile și funcțiile sunt de obicei atomice și participă în tranzacția call-ului (ca să poți face orchestrare cu mai multe). În Oracle nu se face auto commit la DML, dar orice DDL are implicit un commit înainte (pentru că se manipulează tabele sistem într-un DDL, în tranzacție proprie). Cel mai fain e că sqlplus face automat commit dacă dai comanda exit :smile:.

În MySQL de obicei e default autocommit pe DML, ceea ce mi se pare o idee foarte proastă.

Marius folosește Firebird, probabil ar trebui redenumit thread-ul :smile:.
 

Marius '95

Membru Senior
Sugar daddy
Joined
Nov 13, 2005
Location
Brăila
Da, deci in caz de eroare (am un EXCEPTION pe la mijloc), se face rollback la tot. Urmeaza sa si testez la un moment dat. Momentan am alta problema cu HTML-ul.
 

Marius '95

Membru Senior
Sugar daddy
Joined
Nov 13, 2005
Location
Brăila
Vreau un select cu left join, dar coloana din join, in loc sa aiba null sau valoarea, vreau sa contina 0 sau 1 (prezent/absent). Cum fac?
Multumesc!
 

AdrianB1

Membru Senior
Sugar daddy
Joined
Aug 3, 2004
Location
offline
Nu stiu daca am inteles bine cerinta, dar cred ca vrei ceva gen
SELECT
CASE
WHEN
Coloana IS NULL
THEN 0 ELSE 1
END
AS ColoanaDinJoin FROM tabela ... etc?
 

Marius '95

Membru Senior
Sugar daddy
Joined
Nov 13, 2005
Location
Brăila
Da, cred ca asta e. Multumesc.
In urma testelor, ma confrunt si cu o problema de precizie float. Rezultatele numerice la analize pot fi dintre cele mai variate: 0.001 - 10 000 000, chiar si negative in unele cazuri. Mi s-a parut firesc sa stochez valorile ca float. Se pare ca PHP are o problema cu float. Introduc 1.5 in formular si in baza de date ajunge 1.499999... Am citit explicatia. Intrebarea e cum fac sa stochez "1.5". Am incercat INSERT [...] VALUES ( ROUND(?,5) ), dar se crash-uie la null, iar null e important (semnifica rezultat sters).
Cum fac? Trigger care face round cand valoarea nu e null?
 
Last edited:

miahi

Wizzard
Sugar daddy
Joined
Aug 1, 2004
Location
Unreal Estate, Ankh-Morpork, Discworld
Float e problema în acest caz, și nu round e metoda de rezolvare. Ce îți trebuie e o bibliotecă de lucru cu numere mari; din câte înțeleg http://us.php.net/manual/en/ref.bc.php ar fi una din ele, dar după cum arată e dubios de implementat. Se pare că și alții au considerat că e dubios de implementat și au făcut wrappers https://github.com/direvus/php-decimal

Null nu ar trebui să semnifice că un rezultat e șters, ar trebui să ai alt câmp lângă care să specifice asta (util la istoric și ca să nu ai două utilizări ale aceluiași câmp).
 
Top Bottom