Les consultes de la lliçó anterior funcionen. Aquesta lliçó va de l'altra cosa: de si d'aquí a un any, quan ja no recordis per què vas escriure aquell LEFT JOIN, algú —tu inclòs— les podrà tocar sense por. Perquè el SQL té una peculiaritat que el fa especialment traïdor: una consulta mal escrita no falla, retorna números. Un programa mal escrit peta i te n'assabentes; una consulta amb un JOIN de més retorna una xifra plausible que acaba en una diapositiva de direcció.

Aquí no hi ha sintaxi nova. Hi ha criteri: com s'anomenen les coses, com es formaten, què es decideix en dissenyar l'esquema, quines garanties es posen a la base i quines a l'aplicació, i quin procés envolta tot això. Gairebé tot són convencions, i en una convenció l'important no és quina tries, sinó que l'equip sencer triï la mateixa. La lliçó acaba amb el catàleg d'antipatrons i amb una llista de comprovació per revisar una consulta abans de donar-la per bona.

1. Nomenclatura
2. Format i llegibilitat
3. Disseny de l'esquema
4. Fiabilitat: on viuen les garanties
5. Procés: versions, revisió, proves i còpies
6. Antipatrons
7. Checklist de revisió d'una consulta
8. Errors habituals i consells
9. Exercicis
10. Conclusió

La discussió més vella de l'ofici: client o clients? Hi ha arguments honestos per a totes dues:

Cap de les dues no és millor. El que sí que és objectivament dolent és barrejar-les: un esquema amb clients, comanda i linia_comandes obliga a mirar el diccionari abans de cada consulta. BotigaVerda fa servir plural a totes les taules, sense excepció, i aquest és tot el mèrit que té la decisió.

• snake_case en minúscules, sempre. PostgreSQL passa a minúscules qualsevol identificador que no vagi entre cometes dobles: si crees "DataComanda", l'hauràs d'escriure entre cometes per sempre, i al primer oblit sortirà column "datacomanda" does not exist. Posar cometes dobles en un CREATE TABLE és una condemna.
• Només ASCII als identificadors. Per això les columnes són descripcio i puntuacio, i la taula linies_comanda, sense accents ni punt volat (01-06). Les dades porten accents i ç; els noms d'objecte, no.
• Res de paraules reservades. user, order, group, table, select, check, end... Una columna anomenada order obliga a entrecometar-la a cada consulta. Si el negoci diu "comanda", la taula es diu comandes; si diu "usuari", usuaris.
• Noms que diguin alguna cosa. dades, taula1, temp2, info, camp3, x no signifiquen res d'aquí a sis mesos. I sense abreviatures pròpies: data_comanda, no dat_com.
• Sense prefix de tipus. str_nom, tbl_clients, int_stock són notació hongaresa: soroll que a més menteix tan bon punt algú canvia el tipus.

La convenció de BotigaVerda, que és la més estesa i la que aquest curs ha fet servir en onze mòduls:

Dues notes. La primera: la FK reflexiva es nomena pel paper. empleats.empleat_id no diu res; cap_id ho diu tot. La segona: un booleà en negatiu és un parany —WHERE NOT no_actiu és il·legible i no_actiu = FALSE és pitjor—; anomena sempre la condició certa.

Anomena les restriccions a mà. Si no ho fas, PostgreSQL genera productes_preu_check o comandes_client_id_fkey, que són llegibles però no controlats per tu: canvien si canvia el nom de la columna, i apareixen tal qual al missatge d'error que veurà l'usuari. Amb CONSTRAINT chk_productes_preu_positiu CHECK (preu >= 0), l'error diu quina regla s'ha violat i l'aplicació el pot mapar a un missatge decent.

Una consulta s'escriu una vegada i es llegeix vint. Compara:

Cinc categories: Complements no hi apareix perquè el seu únic producte està descatalogat i no es va vendre mai, i un INNER JOIN no s'inventa files. Les dues consultes retornen el mateix; només una es pot modificar sense rellegir-la sencera. Les regles que aplica la segona:

• Una clàusula per línia, amb les paraules clau alineades a l'esquerra. Els ulls troben el WHERE sense buscar-lo.
• JOIN explícit, mai la coma. FROM a, b WHERE a.id = b.a_id és sintaxi del 1989: barreja la unió amb el filtre i, si oblides la condició, produeix un producte cartesià silenciós (03-06). Amb JOIN ... ON, la unió i el filtre estan separats.
• Àlies significatius. lc, p, cat s'entenen; a, b, c obliguen a pujar a mirar. Els del curs estan fixats des del mòdul 3 i no canvien mai.
• AS explícit als àlies de columna. És opcional a PostgreSQL, i ometre'l fa que una coma oblidada converteixi preu, cost en preu AS cost, un error que cap eina no detecta.
• Paraules clau en majúscules, identificadors en minúscules. No és cosmètica: separa d'un cop d'ull el llenguatge de les teves dades.
• Condició de JOIN en ordre constant: taula_nova.columna = taula_ja_coneguda.columna. Llegit en cadena, explica el recorregut.

I per al que sí que és de l'esquema, PostgreSQL té el lloc correcte: COMMENT ON, que queda dins de la base de dades i el veu qualsevol amb \d+.

Discutir la indentació en una revisió de codi és temps llençat. Delega-ho:

La recomanació: fixa un estil en un fitxer de configuració dins del repositori (.sqlfluff, .pg_format) i executa'l al ganxo de pre-commit o a la integració contínua. Així l'estil deixa de ser una opinió i passa a ser una comprovació automàtica.

La regla, reprenent 01-05 i 05-06: comença normalitzat. La normalització elimina la redundància, i la redundància és el que permet que dues còpies de la mateixa dada acabin dient coses diferents. Desnormalitza només quan tinguis una mesura que ho justifiqui i un mecanisme que mantingui la còpia sincronitzada.

BotigaVerda té els dos casos, i convé distingir-los:

La pregunta que resol el dubte: el valor pot canviar pel seu compte després de desar-se? Si no —el preu al qual es va vendre—, no és redundància, és història, i s'ha de desar. Si sí —el total de la comanda, la valoració mitjana—, és un agregat i té cost de manteniment.

Un tipus és la restricció més barata que hi ha, perquè no costa res comprovar-la.

Reprenent 04-03: un NULL és una font de complexitat que es propaga per tota l'aplicació —agregats que l'ignoren, comparacions que no són certes ni falses, NOT IN que retorna zero files—. Per això la postura per omissió ha de ser NOT NULL, i cada columna nul·lable ha de ser una decisió que sàpigues defensar.

A BotigaVerda hi ha tres columnes nul·les i totes tres tenen un significat explícit: comandes.empleat_id = comanda web sense comercial; clients.referit_per_id = va arribar pel seu compte; empleats.cap_id = direcció general. Cap no significa "encara no ho sabem" ni "el formulari venia buit", i aquí hi ha la diferència: si un NULL pot significar dues coses diferents, el disseny està malament.

El criteri: id subrogat com a PK, i la clau natural protegida amb UNIQUE. És exactament el que fa clients (PK id, UNIQUE(email)), i així tens les dues coses: estabilitat i unicitat de negoci.

I l'antipatró que cal reconèixer per fugir-ne: l'EAV (Entity-Attribute-Value), una taula atributs(entitat_id, nom_atribut, valor) que promet flexibilitat infinita. El preu: tot és text, no hi ha tipus ni restriccions, consultar tres atributs són tres autounions, i ningú no sap quins atributs existeixen. Si els camps varien de debò, la resposta moderna és JSONB (10-06); si no varien, són columnes.

comandes.estat podria ser un VARCHAR sense més, i al cap de sis mesos tindries lliurat, Lliurat, LLIURAT i lliruat. Hi ha dues maneres d'impedir-ho:

BotigaVerda fa servir CHECK per a estat i metode_pagament perquè són cinc i quatre valors que formen part del model de negoci. Un catàleg de països o de categories, en canvi, és una taula.

La regla que separa una base sana d'una de malalta: la integritat viu a la base de dades, no només a l'aplicació.

És temptador pensar "ja valido jo al formulari". No n'hi ha prou, per quatre motius que es compleixen sempre: arribarà una segona aplicació (el panell d'administració, un guió de migració, una integració); algú executarà un UPDATE a mà a psql una nit; hi haurà un bug a la validació de l'aplicació; i hi haurà concurrència, i una comprovació "llegeixo i després escric" feta a l'aplicació té una condició de cursa que només una restricció UNIQUE tanca de debò (09-04).

L'aplicació també valida: per donar missatges útils i no fer viatges inútils al servidor. Però valida a més, no en lloc de.

Dos detalls més. DEFAULT assenyats: stock DEFAULT 0, actiu DEFAULT TRUE, descompte DEFAULT 0 eviten que un INSERT incomplet fiqui nuls on no deu. I TIMESTAMPTZ amb UTC per a tot instant: TIMESTAMP sense zona desa un número sense significat, i en una botiga que ven a Espanya, Portugal i França això es paga el dia del canvi d'hora. Desa en UTC, converteix en mostrar. Les dates de calendari pures —data_comanda, data_registre— sí que són DATE, perquè el 4 de març és el 4 de març a tot arreu.

L'esquema és codi. Tot el de la lliçó 05-06 es resumeix en una frase: si l'esquema de producció no es pot reconstruir des del repositori, no tens control de versions.

• Migracions numerades, versionades i immutables. Cada canvi és un fitxer (V007__add_index_comandes_data.sql), va a Git amb el codi que el necessita, i no s'edita una vegada aplicat: es corregeix amb una migració nova. Eines: Flyway, Liquibase, Alembic, o les migracions de l'ORM (11-05).
• Cada migració amb la seva tornada enrere, o almenys amb un pla escrit de què fer si falla. I els canvis trencadors, amb el patró expand/contract de 05-06: afegir, desplegar, migrar dades, i només llavors treure.
• Revisió de codi també per al SQL. Una consulta d'informe mereix la mateixa revisió que una funció: és igual de fàcil equivocar-se i molt més difícil adonar-se'n. Un JOIN que duplica files produeix números, no excepcions.
• Un entorn de proves amb dades realistes. Realistes en volum i forma —un pla sobre 20 files no diu res del que passarà amb 20 milions (08-05)— però ficticis o anonimitzats.

⚠️ No copiïs dades personals de producció a desenvolupament. És la pràctica més estesa i una de les més perilloses: multiplica les còpies de dades reals en portàtils, entorns sense xifrar i bolcats que ningú no esborra. Genera dades sintètiques, o pseudonimitza abans de copiar (substituir noms i correus, desplaçar dates, arrodonir imports) — amb l'advertiment que la pseudonimització mal feta és reversible. Abans de moure dades personals entre entorns, consulta-ho amb el responsable de protecció de dades o amb l'assessoria jurídica de la teva organització. És el mateix avís de 05-04, i aquí també s'aplica.

• Còpies de seguretat provades. Una còpia que no s'ha restaurat mai no és una còpia: és un fitxer del qual suposes coses. Programa una restauració de prova periòdica en un entorn a part i mesura quant triga, perquè aquest número és el teu temps real de recuperació. Comprova també que la còpia inclou el que et penses (rols, extensions, seqüències) i que la retenció cobreix el temps que trigues a detectar un problema: si l'esborrat es descobreix als deu dies i en guardes set, no hi ha còpia.
• Monitoratge de consultes lentes. pg_stat_statements ordenat per temps total —no per temps mitjà, que amaga l'N+1 (08-04)—, log_min_duration_statement per registrar el que passi d'un llindar, i una revisió periòdica del rànquing. Sense això no t'assabentes que alguna cosa va malament: t'ho explica un usuari enfadat.

Abans de donar una consulta per bona, en aquest ordre:

Correcció

1. Els JOIN multipliquen files? Comprova el recompte abans i després de cadascun: si comandes passa de 20 a 47, estàs sumant línies, no comandes.
2. Hi ha LEFT JOIN on el negoci admet absència? Les 10 comandes web sense comercial desapareixen amb un INNER JOIN.
3. Què passa amb els NULL? Al WHERE, al NOT IN, a les agregacions, a les concatenacions.
4. El resultat quadra amb una xifra coneguda? Si el total d'un desglossament no dona 727,95 €, el desglossament està malament.
5. L'ORDER BY és determinista? Sense desempat, dues execucions poden retornar ordres diferents.

Rendiment

6. Les condicions són sargables (columna nua)? Existeixen els índexs que necessita (08-01)?
7. Retorna només les columnes i les files que es faran servir? Té LIMIT si va a una pantalla?
8. L'has executada amb EXPLAIN ANALYZE sobre un volum realista (08-05)?

Mantenibilitat

9. Es llegeix? Àlies significatius, una clàusula per línia, AS explícit?
10. Els comentaris expliquen per què, no què?
11. És al repositori, amb la pregunta de negoci que respon escrita al costat?

Seguretat

12. Tots els valors de l'usuari van com a paràmetres (11-03)?
13. Retorna dades personals que qui l'executa no hauria de veure?

• Entrecometar identificadors al CREATE TABLE. "DataComanda" t'obliga a escriure-ho amb cometes per sempre. snake_case en minúscules i s'ha acabat.
• Barrejar singular i plural, o dues convencions de FK. El cost no és estètic: és haver de consultar el diccionari a cada consulta.
• Deixar que PostgreSQL anomeni les restriccions. El nom generat acaba al missatge d'error que veu l'usuari i canvia si canvia la columna. Anomena-les: chk_, fk_, uq_, idx_.
• Desar diners en FLOAT o dates en VARCHAR. És la decisió que més car es paga i la més difícil de revertir amb dades a dins.
• Posar tota la validació a l'aplicació. Arribarà una segona aplicació, un guió nocturn i una condició de cursa. La integritat va a la base.
• Fer servir TIMESTAMP sense zona per a instants. Desa en UTC amb TIMESTAMPTZ i converteix en mostrar.
• Editar una migració ja aplicada. Els entorns queden desincronitzats en silenci. Es corregeix amb una migració nova.
• Consell: la primera consulta de qualsevol informe és SELECT COUNT(*). Si el recompte canvia en afegir un JOIN, atura't i esbrina per què abans de continuar.
• Consell: automatitza l'estil amb sqlfluff o pg_format a la integració contínua. Les revisions de codi haurien de discutir la lògica, no la indentació.
• Consell: escriu la convenció en un fitxer del repositori. Mitja pàgina n'hi ha prou, i converteix "així ho fem" en una cosa que un company nou pot llegir.

Aquest esquema és real en el sentit que s'assembla molt al que et trobaràs. Enumera tots els problemes de nomenclatura, tipus i disseny, i reescriu-lo.

El teu equip discuteix on posar la regla "una comanda cancel·lada no pot rebre línies noves". Hi ha tres propostes: (a) validar-ho al formulari web; (b) un CHECK a linies_comanda; (c) un disparador BEFORE INSERT. (1) Quina funciona i quina no, i per què? (2) Quina triaries i què faries a més? (3) Canvia la resposta si el sistema té també un panell d'administració i un procés nocturn d'importació?

Reps aquesta consulta en una revisió de codi, amb la nota "dona el nombre de comandes i la facturació per comercial". Aplica-hi el checklist de l'apartat 7 i digues què està malament.

Solució 1

Els problemes, un a un: identificadors entre cometes amb majúscules ("Comandes_Client", "ID", "Estat"), que obliguen a escriure cometes per sempre; "user" és paraula reservada; barreja d'idiomes (order_date al costat d'Estat); ID VARCHAR(50) com a PK quan hauria de ser un enter subrogat; order_date VARCHAR en lloc de DATE; total FLOAT per a diners; VARCHAR(255) per costum; no_actiu en negatiu i com a INTEGER en comptes de BOOLEAN; dades TEXT que no diu què conté; cap FK cap a clients; cap NOT NULL, cap CHECK i cap restricció amb nom.

I una pregunta que cal fer-se abans d'escriure res: total és un agregat precalculat. Si es pot sumar de les línies, potser no hauria d'existir; i si existeix per rendiment, cal el mecanisme que el mantingui (10-05) i la decisió escrita de per què.

Solució 2

1. La (b) no funciona: un CHECK només pot mirar la fila que s'està inserint, i l'estat de la comanda és en una altra taula. PostgreSQL no permet subconsultes en un CHECK precisament perquè no podria garantir que continuï sent cert quan canviï l'altra taula. La (a) funciona però no protegeix: cobreix un camí d'entrada dels diversos que hi ha. La (c) sí que funciona: un disparador BEFORE INSERT sobre linies_comanda pot consultar comandes.estat i llançar RAISE EXCEPTION (10-05).

2. El disparador, i a més la validació al formulari, per donar un missatge decent sense anar al servidor. La base garanteix; la interfície explica. I cal pensar el cas simètric —què passa si la comanda es cancel·la després de tenir línies?—, que aquell disparador no cobreix.

3. No canvia la resposta, la reforça. Amb tres vies d'entrada, la validació al formulari en protegeix una de tres, i precisament el procés nocturn és el que inserirà milers de files sense que ningú les miri. Aquest és l'argument sencer de l'apartat 4.

Solució 3

Quatre problemes de correcció i uns quants de forma. (1) El COUNT(*) està malament: en unir amb linies_comanda, cada comanda hi apareix tantes vegades com línies té, així que no compta comandes sinó línies. Ha de ser COUNT(DISTINCT c.id). (2) Falta el descompte: l'import és quantitat * preu_unitari * (1 - descompte), i sense ell la facturació surt inflada. (3) INNER JOIN amb empleats deixa fora les 10 comandes web: si l'informe vol "per comercial", cal decidir explícitament si aquelles comandes s'exclouen o hi apareixen com a "Web" amb un LEFT JOIN i COALESCE. (4) GROUP BY e.nom agrupa per nom de pila: dos comercials que es diguin igual es fondrien en una fila. Cal agrupar per e.id. En la forma: sintaxi de comes en lloc de JOIN, sense AS ni àlies de columna, sense majúscules i sense ORDER BY.

Tres comercials, 10 comandes i 378,83 € — el canal telefònic. Els altres 349,12 € són les 10 comandes web sense comercial, i que no hi apareguin és ara una decisió, no un descuit.

Aquesta lliçó no afegia sintaxi: afegia criteri.

• Nomenclatura: snake_case en minúscules i només ASCII; singular o plural tant és, barrejar-los no; id per a la PK i <taula>_id per a la FK, amb el nom del paper a les reflexives; booleans en afirmatiu; res de paraules reservades ni de dades/taula1; i restriccions i índexs anomenats a mà amb chk_, fk_, uq_, idx_.
• Format: una clàusula per línia, JOIN ... ON explícit en lloc de la coma, àlies significatius, AS explícit, paraules clau en majúscules, comentaris que expliquen el perquè, COMMENT ON per al que és de l'esquema, i un formatador automàtic configurat al repositori.
• Disseny: normalitzar per defecte i desnormalitzar amb una mesura i un mecanisme al darrere; el tipus més restrictiu que serveixi; NOT NULL per defecte i cada NULL amb un significat únic; id subrogat més UNIQUE sobre la clau natural; res d'EAV; i CHECK per a llistes tancades, taula de catàleg per a les que creixen.
• Fiabilitat: la integritat viu a la base de dades, perquè arribarà una segona aplicació, un UPDATE a mà, un bug i una condició de cursa. DEFAULT assenyats i TIMESTAMPTZ en UTC.
• Procés: migracions versionades i immutables, revisió de codi també per al SQL, entorn de proves amb dades fictícies o anonimitzades —mai còpies de producció amb dades personals—, còpies de seguretat provades (una còpia sense restaurar no és una còpia) i monitoratge de consultes lentes per temps total.
• I el checklist de tretze punts, que comença per la pregunta que més errors evita: aquest JOIN multiplica files?

Dos dels antipatrons de la taula s'han quedat amb una promesa: la construcció de SQL per concatenació i el "qui pot veure què" que ha anat apareixent des del mòdul 5. A la lliçó següent, Seguretat: injecció SQL, permisos i rols, es tanquen tots dos: què és exactament una injecció SQL i per què passa; la defensa que funciona de debò —les consultes parametritzades— escrita en quatre llenguatges; el que no és una defensa; el cas especial dels identificadors dinàmics; i després el model de permisos de PostgreSQL complet, amb GRANT, REVOKE, rols, ALTER DEFAULT PRIVILEGES, seguretat a nivell de fila i un disseny de rols concret per a BotigaVerda.