PR03 — Pràctica: Automatització amb Docker

Informació general

Mòdul	M0377 - Administració de SGBD
Resultat d'aprenentatge	RA4 - Automatització de tasques administratives
Durada estimada	5 hores
Motor principal	PostgreSQL 17 (via Docker)
Motor secundari	MySQL 8.4 (via Docker, per a la part comparativa)

Objectius

• Configurar un entorn PostgreSQL amb Docker per a pràctiques d'automatització.
• Crear i executar procediments emmagatzemats amb paràmetres i gestió d'errors.
• Implementar triggers de càlcul automàtic i d'auditoria sobre taules reals.
• Instal·lar i configurar pg_cron per a tasques programades.
• Comparar la sintaxi de triggers entre PostgreSQL i MySQL.
• Verificar el funcionament de totes les automatitzacions amb proves documentades.

Prerequisits

• Docker Desktop instal·lat i en execució.
• Haver completat les pràctiques anteriors del mòdul (instal·lació i control d'accés).
• Coneixements bàsics de SQL (SELECT, INSERT, UPDATE, DELETE, JOIN).
• Client SQL: psql, DBeaver, pgAdmin o qualsevol client compatible amb PostgreSQL.

Nom d'alumne als recursos Docker

Al llarg d'aquesta pràctica substituïu [nom-alumne] pel vostre nom d'usuari en minúscules i sense espais (ex: joanfont). Això permet identificar els vostres contenidors en un entorn compartit i evitar conflictes de noms.

---

Escenari: base de dades d'una botiga en línia

Treballareu amb una base de dades d'una botiga en línia amb l'esquema següent:

erDiagram
    PRODUCTES {
        int id PK
        varchar nom
        decimal preu
        int estoc
        int estoc_minim
        int categoria_id FK
        timestamp creat_en
        timestamp actualitzat_en
    }
    CATEGORIES {
        int id PK
        varchar nom
        decimal iva_pct
    }
    CLIENTS {
        int id PK
        varchar nom
        varchar email
        varchar tipus
        timestamp creat_en
    }
    COMANDES {
        int id PK
        int client_id FK
        timestamp data_comanda
        varchar estat
        decimal total
        timestamp actualitzat_en
    }
    LINIES_COMANDA {
        int id PK
        int comanda_id FK
        int producte_id FK
        int quantitat
        decimal preu_unitari
        decimal subtotal
    }
    AUDIT_PRODUCTES {
        int id PK
        varchar operacio
        int producte_id
        decimal preu_anterior
        decimal preu_nou
        varchar usuari
        timestamp data_hora
    }
    COMANDES_ARXIU {
        int id PK
        int comanda_original_id
        int client_id FK
        timestamp data_comanda
        varchar estat
        decimal total
        timestamp arxivada_en
    }

    PRODUCTES }o--|| CATEGORIES : "pertany a"
    COMANDES }o--|| CLIENTS : "fa"
    LINIES_COMANDA }o--|| COMANDES : "pertany a"
    LINIES_COMANDA }o--|| PRODUCTES : "conté"
    AUDIT_PRODUCTES }o--|| PRODUCTES : "registra canvis de"

---

Pas 1: Configuració de l'entorn Docker

1.1 Iniciar el contenidor PostgreSQL

docker run -d \
  --name sgbd-auto-[nom-alumne] \
  -e POSTGRES_USER=admin \
  -e POSTGRES_PASSWORD=Admin1234! \
  -e POSTGRES_DB=botiga \
  -p 5440:5432 \
  -v sgbd-auto-[nom-alumne]-data:/var/lib/postgresql/data \
  postgres:17

Port 5440

Usem el port 5440 (en comptes del 5432 estàndard) per evitar conflictes amb possibles instàncies de PostgreSQL locals o d'altres pràctiques.

1.2 Verificar que el contenidor funciona

# Verificar que el contenidor està en execució
docker ps | grep sgbd-auto-[nom-alumne]

# Connectar-se amb psql directament al contenidor
docker exec -it sgbd-auto-[nom-alumne] psql -U admin -d botiga

# O connectar-se des del host (si teniu psql instal·lat)
psql -h localhost -p 5440 -U admin -d botiga

1.3 Iniciar el contenidor MySQL (per a la comparativa del Pas 8)

docker run -d \
  --name sgbd-auto-mysql-[nom-alumne] \
  -e MYSQL_ROOT_PASSWORD=Root1234! \
  -e MYSQL_DATABASE=botiga \
  -e MYSQL_USER=admin \
  -e MYSQL_PASSWORD=Admin1234! \
  -p 3310:3306 \
  mysql:8.4

---

Pas 2: Creació de l'esquema i les taules

Connecteu-vos a PostgreSQL i executeu el script següent:

-- Habilitar extensions necessàries
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Taula de categories
CREATE TABLE IF NOT EXISTS categories (
    id      SERIAL PRIMARY KEY,
    nom     VARCHAR(100) NOT NULL,
    iva_pct DECIMAL(5,2) NOT NULL DEFAULT 21.00
);

-- Taula de productes
CREATE TABLE IF NOT EXISTS productes (
    id             SERIAL PRIMARY KEY,
    nom            VARCHAR(200) NOT NULL,
    preu           DECIMAL(10,2) NOT NULL CHECK (preu >= 0),
    estoc          INT NOT NULL DEFAULT 0 CHECK (estoc >= 0),
    estoc_minim    INT NOT NULL DEFAULT 5,
    categoria_id   INT NOT NULL REFERENCES categories(id),
    creat_en       TIMESTAMP NOT NULL DEFAULT NOW(),
    actualitzat_en TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Taula de clients
CREATE TABLE IF NOT EXISTS clients (
    id        SERIAL PRIMARY KEY,
    nom       VARCHAR(200) NOT NULL,
    email     VARCHAR(200) NOT NULL UNIQUE,
    tipus     VARCHAR(20) NOT NULL DEFAULT 'standard'
              CHECK (tipus IN ('standard', 'premium', 'vip')),
    creat_en  TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Taula de comandes
CREATE TABLE IF NOT EXISTS comandes (
    id             SERIAL PRIMARY KEY,
    client_id      INT NOT NULL REFERENCES clients(id),
    data_comanda   TIMESTAMP NOT NULL DEFAULT NOW(),
    estat          VARCHAR(20) NOT NULL DEFAULT 'pendent'
                   CHECK (estat IN ('pendent', 'processant', 'enviada', 'completada', 'cancel·lada')),
    total          DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    actualitzat_en TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Taula de línies de comanda
CREATE TABLE IF NOT EXISTS linies_comanda (
    id            SERIAL PRIMARY KEY,
    comanda_id    INT NOT NULL REFERENCES comandes(id) ON DELETE CASCADE,
    producte_id   INT NOT NULL REFERENCES productes(id),
    quantitat     INT NOT NULL CHECK (quantitat > 0),
    preu_unitari  DECIMAL(10,2) NOT NULL CHECK (preu_unitari >= 0),
    subtotal      DECIMAL(10,2) GENERATED ALWAYS AS (quantitat * preu_unitari) STORED
);

-- Taula d'auditoria de productes
CREATE TABLE IF NOT EXISTS audit_productes (
    id            SERIAL PRIMARY KEY,
    operacio      VARCHAR(10) NOT NULL,
    producte_id   INT,
    nom_anterior  VARCHAR(200),
    nom_nou       VARCHAR(200),
    preu_anterior DECIMAL(10,2),
    preu_nou      DECIMAL(10,2),
    estoc_anterior INT,
    estoc_nou     INT,
    usuari        VARCHAR(100) NOT NULL DEFAULT current_user,
    data_hora     TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Taula d'arxiu de comandes
CREATE TABLE IF NOT EXISTS comandes_arxiu (
    id                  SERIAL PRIMARY KEY,
    comanda_original_id INT NOT NULL,
    client_id           INT NOT NULL,
    data_comanda        TIMESTAMP NOT NULL,
    estat               VARCHAR(20) NOT NULL,
    total               DECIMAL(10,2) NOT NULL,
    arxivada_en         TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Taula de registre de jobs
CREATE TABLE IF NOT EXISTS log_jobs (
    id              SERIAL PRIMARY KEY,
    job_nom         VARCHAR(100) NOT NULL,
    data_execucio   TIMESTAMP NOT NULL DEFAULT NOW(),
    files_afectades INT,
    durada_ms       INT,
    estat           VARCHAR(10) NOT NULL DEFAULT 'OK',
    missatge        TEXT
);

2.2 Inserir dades de prova

-- Categories
INSERT INTO categories (nom, iva_pct) VALUES
    ('Electrònica',   21.00),
    ('Llibres',       4.00),
    ('Roba',          21.00),
    ('Alimentació',   10.00),
    ('Eines',         21.00);

-- Productes
INSERT INTO productes (nom, preu, estoc, estoc_minim, categoria_id) VALUES
    ('Portàtil 15"',         899.99,  10, 3, 1),
    ('Teclat mecànic',        79.99,  25, 5, 1),
    ('Ratolí ergonòmic',      45.00,  30, 5, 1),
    ('Python per a tots',     29.95,   8, 2, 2),
    ('Clean Code',            35.00,   4, 2, 2),  -- estoc proper al mínim!
    ('Samarreta cotó L',      19.99,  50, 10, 3),
    ('Jaqueta impermeabl M',  89.00,  15, 5, 3),
    ('Café molido 500g',       8.50, 100, 20, 4),
    ('Destornillador set',    22.00,   7, 3, 5),
    ('Taladre sense fils',   120.00,   5, 2, 5);

-- Clients
INSERT INTO clients (nom, email, tipus) VALUES
    ('Anna Puig',       'anna@exemple.cat',   'vip'),
    ('Bernat Sala',     'bernat@exemple.cat', 'premium'),
    ('Carla Vidal',     'carla@exemple.cat',  'standard'),
    ('David Mestre',    'david@exemple.cat',  'premium'),
    ('Elena Ferrer',    'elena@exemple.cat',  'standard');

-- Comandes (algunes amb data antiga per poder provar l'arxivat)
INSERT INTO comandes (client_id, data_comanda, estat) VALUES
    (1, NOW() - INTERVAL '2 years', 'completada'),  -- serà arxivada
    (2, NOW() - INTERVAL '18 months', 'completada'), -- serà arxivada
    (3, NOW() - INTERVAL '6 months', 'completada'),  -- no arxivada
    (1, NOW() - INTERVAL '3 months', 'completada'),
    (4, NOW(), 'pendent');

-- Línies de comanda (per les comandes recents)
INSERT INTO linies_comanda (comanda_id, producte_id, quantitat, preu_unitari)
VALUES
    (3, 1, 1, 899.99),
    (3, 2, 2,  79.99),
    (4, 4, 3,  29.95),
    (4, 5, 1,  35.00),
    (5, 6, 2,  19.99);

---

Pas 3: Procediment process_order

Creeu el procediment process_order(p_comanda_id INT) que:

1. Verifica que la comanda existeix i que el seu estat és 'pendent'.
2. Per a cada línia de comanda, verifica que hi ha suficient estoc del producte.
3. Si tot és correcte, actualitza l'estoc de cada producte.
4. Canvia l'estat de la comanda a 'processant'.
5. Retorna un missatge indicant si ha tingut èxit o quin error s'ha produït.

CREATE OR REPLACE PROCEDURE process_order(
    p_comanda_id INT,
    OUT p_resultat TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_estat        comandes.estat%TYPE;
    v_linia        RECORD;
    v_estoc_actual INT;
    v_total_linies INT := 0;
BEGIN
    -- Verificar que la comanda existeix
    SELECT estat INTO v_estat
    FROM comandes
    WHERE id = p_comanda_id
    FOR UPDATE;  -- Bloquejar la fila per evitar condicions de carrera

    IF NOT FOUND THEN
        p_resultat := 'ERROR: la comanda ' || p_comanda_id || ' no existeix.';
        RETURN;
    END IF;

    -- Verificar que la comanda està en estat pendent
    IF v_estat <> 'pendent' THEN
        p_resultat := 'ERROR: la comanda ' || p_comanda_id
                   || ' no es pot processar (estat actual: ' || v_estat || ').';
        RETURN;
    END IF;

    -- Verificar estoc per a cada línia
    FOR v_linia IN
        SELECT lc.id, lc.producte_id, lc.quantitat, p.nom AS producte_nom
        FROM linies_comanda lc
        JOIN productes p ON p.id = lc.producte_id
        WHERE lc.comanda_id = p_comanda_id
        FOR UPDATE OF p  -- Bloquejar els productes per evitar races
    LOOP
        SELECT estoc INTO v_estoc_actual
        FROM productes
        WHERE id = v_linia.producte_id;

        IF v_estoc_actual < v_linia.quantitat THEN
            p_resultat := 'ERROR: estoc insuficient per al producte "'
                       || v_linia.producte_nom || '" (disponible: '
                       || v_estoc_actual || ', sol·licitat: '
                       || v_linia.quantitat || ').';
            RETURN;
        END IF;
    END LOOP;

    -- Tot correcte: actualitzar estoc i estat de la comanda
    FOR v_linia IN
        SELECT producte_id, quantitat
        FROM linies_comanda
        WHERE comanda_id = p_comanda_id
    LOOP
        UPDATE productes
        SET estoc = estoc - v_linia.quantitat,
            actualitzat_en = NOW()
        WHERE id = v_linia.producte_id;

        v_total_linies := v_total_linies + 1;
    END LOOP;

    -- Canviar l'estat de la comanda
    UPDATE comandes
    SET estat = 'processant',
        actualitzat_en = NOW()
    WHERE id = p_comanda_id;

    p_resultat := 'OK: comanda ' || p_comanda_id || ' processada ('
               || v_total_linies || ' línies, estoc actualitzat).';

EXCEPTION
    WHEN OTHERS THEN
        p_resultat := 'ERROR inesperat: ' || SQLERRM;
        RAISE;
END;
$$;

-- PROVA del procediment:
DO $$
DECLARE v_res TEXT;
BEGIN
    CALL process_order(5, v_res);
    RAISE NOTICE 'Resultat: %', v_res;
END;
$$;

-- Comprovar que l'estoc s'ha actualitzat
SELECT id, nom, estoc FROM productes WHERE id = 6;

-- Comprovar l'estat de la comanda
SELECT id, estat, actualitzat_en FROM comandes WHERE id = 5;

Reflexió - Pas 3

• Proveu d'executar CALL process_order(5, NULL) una segona vegada. Quin resultat obteniu i per què?
• Proveu d'executar CALL process_order(999, NULL) amb un ID inexistent. El procediment gestiona l'error correctament?
• Modifiqueu l'estoc d'un producte a 0 i torneu a crear una comanda pendent que l'inclogui. El procediment la rebutja?

---

Pas 4: Trigger per actualitzar el total de la comanda

Creeu un trigger que, cada vegada que s'insereixi, modifiqui o elimini una línia de comanda, recalculi automàticament el camp total de la comanda pare.

-- Funció del trigger
CREATE OR REPLACE FUNCTION trg_fn_recalcula_total()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_comanda_id INT;
    v_nou_total  DECIMAL(10,2);
BEGIN
    -- Determinar quina comanda s'ha vist afectada
    v_comanda_id := COALESCE(NEW.comanda_id, OLD.comanda_id);

    -- Recalcular el total
    SELECT COALESCE(SUM(subtotal), 0.00)
    INTO v_nou_total
    FROM linies_comanda
    WHERE comanda_id = v_comanda_id;

    -- Actualitzar la comanda
    UPDATE comandes
    SET total = v_nou_total,
        actualitzat_en = NOW()
    WHERE id = v_comanda_id;

    RETURN NEW;
END;
$$;

-- Associar el trigger a la taula linies_comanda
CREATE OR REPLACE TRIGGER trg_recalcula_total_comanda
AFTER INSERT OR UPDATE OR DELETE ON linies_comanda
FOR EACH ROW
EXECUTE FUNCTION trg_fn_recalcula_total();

-- PROVA del trigger:
-- Inserir una nova línia a la comanda 4
INSERT INTO linies_comanda (comanda_id, producte_id, quantitat, preu_unitari)
VALUES (4, 3, 1, 45.00);

-- Verificar que el total s'ha actualitzat
SELECT id, total, actualitzat_en FROM comandes WHERE id = 4;

-- Eliminar la línia i verificar que el total disminueix
DELETE FROM linies_comanda
WHERE comanda_id = 4 AND producte_id = 3;

SELECT id, total, actualitzat_en FROM comandes WHERE id = 4;

Reflexió - Pas 4

• Sense el trigger, com hauries actualitzat el total de la comanda des de l'aplicació?
• Quines avantatges té fer-ho amb un trigger versus fer-ho a l'aplicació?
• Quin és el risc si el trigger tarda molt a executar-se en operacions massives d'inserció de línies?

---

Pas 5: Trigger d'auditoria de productes

Creeu un trigger que registri a audit_productes qualsevol canvi (INSERT, UPDATE, DELETE) sobre la taula productes.

-- Funció del trigger d'auditoria
CREATE OR REPLACE FUNCTION trg_fn_audit_productes()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_productes (
        operacio,
        producte_id,
        nom_anterior,
        nom_nou,
        preu_anterior,
        preu_nou,
        estoc_anterior,
        estoc_nou,
        usuari,
        data_hora
    ) VALUES (
        TG_OP,                              -- 'INSERT', 'UPDATE' o 'DELETE'
        COALESCE(NEW.id, OLD.id),
        OLD.nom,
        NEW.nom,
        OLD.preu,
        NEW.preu,
        OLD.estoc,
        NEW.estoc,
        current_user,
        NOW()
    );
    RETURN NEW;
END;
$$;

-- Associar el trigger
CREATE OR REPLACE TRIGGER trg_audit_productes
AFTER INSERT OR UPDATE OR DELETE ON productes
FOR EACH ROW
EXECUTE FUNCTION trg_fn_audit_productes();

-- PROVES del trigger d'auditoria:

-- Prova 1: Modificar el preu d'un producte
UPDATE productes SET preu = 849.99 WHERE id = 1;

-- Prova 2: Inserir un producte nou
INSERT INTO productes (nom, preu, estoc, estoc_minim, categoria_id)
VALUES ('Monitor 27" 4K', 399.00, 8, 2, 1);

-- Prova 3: Actualitzar l'estoc
UPDATE productes SET estoc = estoc - 2 WHERE id = 2;

-- Prova 4: Eliminar el producte de prova
DELETE FROM productes WHERE nom = 'Monitor 27" 4K';

-- Consultar la taula d'auditoria
SELECT
    id,
    operacio,
    producte_id,
    preu_anterior,
    preu_nou,
    estoc_anterior,
    estoc_nou,
    usuari,
    data_hora
FROM audit_productes
ORDER BY data_hora DESC;

Reflexió - Pas 5

• Per quin motiu hem definit el trigger com AFTER i no BEFORE?
• Observeu el registre de l'operació DELETE: quins camps queden a NULL? Per qué?
• Podria l'aplicació falsificar el camp usuari de la taula d'auditoria? Com podríeu garantir que el valor és sempre l'usuari real de la BD?

---

Pas 6: Instal·lació de pg_cron i job d'arxivat

6.1 Instal·lar pg_cron al contenidor

# Accedir al contenidor com a root
docker exec -it --user root sgbd-auto-[nom-alumne] bash

# Instal·lar pg_cron (el paquet ja inclou la llibreria)
apt-get update && apt-get install -y postgresql-16-cron

# Modificar postgresql.conf per carregar pg_cron
echo "shared_preload_libraries = 'pg_cron'" >> /var/lib/postgresql/data/postgresql.conf
echo "cron.database_name = 'botiga'" >> /var/lib/postgresql/data/postgresql.conf

exit

# Reiniciar el contenidor per aplicar els canvis
docker restart sgbd-auto-[nom-alumne]

# Reconnectar i crear l'extensió
docker exec -it sgbd-auto-[nom-alumne] psql -U admin -d botiga

-- Crear l'extensió (un cop reiniciat PostgreSQL)
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Verificar que pg_cron funciona
SELECT * FROM cron.job;

6.2 Procediment d'arxivat

CREATE OR REPLACE PROCEDURE arxivar_comandes_completades(
    p_mesos_antiguitat INT DEFAULT 12
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_data_limit     TIMESTAMP;
    v_arxivades      INT := 0;
    v_temps_inici    TIMESTAMP := NOW();
    v_temps_fi       TIMESTAMP;
    v_durada_ms      INT;
BEGIN
    v_data_limit := NOW() - (p_mesos_antiguitat || ' months')::INTERVAL;

    -- Copiar les comandes antigues a la taula d'arxiu
    INSERT INTO comandes_arxiu (
        comanda_original_id, client_id, data_comanda,
        estat, total, arxivada_en
    )
    SELECT id, client_id, data_comanda, estat, total, NOW()
    FROM comandes
    WHERE estat = 'completada'
      AND data_comanda < v_data_limit;

    GET DIAGNOSTICS v_arxivades = ROW_COUNT;

    -- Eliminar les comandes arxivades de la taula principal
    -- (les linies_comanda s'eliminen en cascada per ON DELETE CASCADE)
    DELETE FROM comandes
    WHERE estat = 'completada'
      AND data_comanda < v_data_limit;

    v_temps_fi := NOW();
    v_durada_ms := EXTRACT(MILLISECONDS FROM (v_temps_fi - v_temps_inici))::INT;

    -- Registrar l'execució
    INSERT INTO log_jobs (job_nom, data_execucio, files_afectades, durada_ms, estat, missatge)
    VALUES (
        'arxivar_comandes_completades',
        v_temps_inici,
        v_arxivades,
        v_durada_ms,
        'OK',
        'Arxivades ' || v_arxivades || ' comandes anteriors a ' || v_data_limit::DATE
    );

    RAISE NOTICE 'Arxivat completat: % comandes en % ms', v_arxivades, v_durada_ms;

EXCEPTION
    WHEN OTHERS THEN
        INSERT INTO log_jobs (job_nom, data_execucio, estat, missatge)
        VALUES ('arxivar_comandes_completades', NOW(), 'ERROR', SQLERRM);
        RAISE;
END;
$$;

6.3 Programar el job amb pg_cron

-- Programar el job per executar-se cada dia a les 2:00
SELECT cron.schedule(
    'arxivat-comandes-diari',
    '0 2 * * *',
    'CALL arxivar_comandes_completades(12)'
);

-- Verificar que el job s'ha creat
SELECT jobid, jobname, schedule, command, active
FROM cron.job;

-- PROVA: executar el procediment manualment
CALL arxivar_comandes_completades(12);

-- Verificar el resultat
SELECT * FROM comandes_arxiu;
SELECT * FROM log_jobs ORDER BY data_execucio DESC;

-- Verificar que les comandes antigues han desaparegut de la taula principal
SELECT id, data_comanda, estat, total
FROM comandes
ORDER BY data_comanda;

Reflexió - Pas 6

• Quantes comandes han estat arxivades? Coincideix amb el que esperaveu mirant les dades del Pas 2?
• Si la comanda 3 (de fa 6 mesos) NO ha estat arxivada, per qué? Canvieu el paràmetre a 6 mesos i torneu a provar: CALL arxivar_comandes_completades(6).
• El job de pg_cron s'executa dins de la BD botiga. Que passaria si programàveu el job connectat a la BD postgres?

---

Pas 7: Prova integral de totes les automatitzacions

Executeu la seqüència de proves següent i documenteu el resultat de cada pas.

-- === SEQÜÈNCIA DE PROVES INTEGRALS ===

-- 1. Crear una comanda pendent nova
INSERT INTO comandes (client_id, estat) VALUES (3, 'pendent') RETURNING id;
-- Anoteu el nou ID (suposem que és 6)

-- 2. Afegir línies a la comanda (el trigger del Pas 4 ha d'actualitzar el total)
INSERT INTO linies_comanda (comanda_id, producte_id, quantitat, preu_unitari)
VALUES (6, 8, 5, 8.50);  -- Café: 5 x 8.50 = 42.50

INSERT INTO linies_comanda (comanda_id, producte_id, quantitat, preu_unitari)
VALUES (6, 9, 1, 22.00);  -- Destornilladors: 1 x 22.00 = 22.00

-- 3. Verificar que el total s'ha calculat automàticament (ha de ser 64.50)
SELECT id, total FROM comandes WHERE id = 6;

-- 4. Processar la comanda (el procediment del Pas 3 actualitza l'estoc)
DO $$
DECLARE v_res TEXT;
BEGIN
    CALL process_order(6, v_res);
    RAISE NOTICE '%', v_res;
END;
$$;

-- 5. Verificar estoc actualitzat
SELECT id, nom, estoc FROM productes WHERE id IN (8, 9);

-- 6. Modificar el preu d'un producte (el trigger del Pas 5 ha d'auditar el canvi)
UPDATE productes SET preu = 9.00 WHERE id = 8;

-- 7. Consultar la taula d'auditoria
SELECT operacio, producte_id, preu_anterior, preu_nou, usuari, data_hora
FROM audit_productes
ORDER BY data_hora DESC
LIMIT 5;

-- 8. Provar un cas d'error: intentar processar una comanda amb estoc insuficient
-- Primer, buidar l'estoc del producte 5
UPDATE productes SET estoc = 0 WHERE id = 5;

-- Crear comanda que el necessiti
INSERT INTO comandes (client_id, estat) VALUES (2, 'pendent') RETURNING id;
INSERT INTO linies_comanda (comanda_id, producte_id, quantitat, preu_unitari)
VALUES (7, 5, 2, 35.00);  -- Vol 2 unitats de Clean Code (estoc: 0)

DO $$
DECLARE v_res TEXT;
BEGIN
    CALL process_order(7, v_res);
    RAISE NOTICE 'Esperava error: %', v_res;
END;
$$;

-- Verificar que la comanda segueix en estat 'pendent' (no s'ha processat)
SELECT id, estat FROM comandes WHERE id = 7;

---

Pas 8: Trigger equivalent en MySQL

Mostreu la versió equivalent del trigger d'auditoria de productes (Pas 5) en MySQL.

8.1 Connectar-se a MySQL

docker exec -it sgbd-auto-mysql-[nom-alumne] mysql -u admin -pAdmin1234! botiga

8.2 Crear les taules necessàries

-- MySQL
CREATE TABLE IF NOT EXISTS productes (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    nom            VARCHAR(200) NOT NULL,
    preu           DECIMAL(10,2) NOT NULL,
    estoc          INT NOT NULL DEFAULT 0,
    categoria_id   INT NOT NULL,
    creat_en       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualitzat_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                   ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS audit_productes (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    operacio      VARCHAR(10) NOT NULL,
    producte_id   INT,
    nom_anterior  VARCHAR(200),
    nom_nou       VARCHAR(200),
    preu_anterior DECIMAL(10,2),
    preu_nou      DECIMAL(10,2),
    estoc_anterior INT,
    estoc_nou     INT,
    usuari        VARCHAR(100) NOT NULL DEFAULT (USER()),
    data_hora     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

8.3 Triggers d'auditoria en MySQL

MySQL / MariaDBPostgreSQL

DELIMITER //

-- Trigger per INSERT
CREATE TRIGGER trg_audit_productes_insert
AFTER INSERT ON productes
FOR EACH ROW
BEGIN
    INSERT INTO audit_productes (
        operacio, producte_id,
        nom_nou, preu_nou, estoc_nou,
        usuari, data_hora
    ) VALUES (
        'INSERT', NEW.id,
        NEW.nom, NEW.preu, NEW.estoc,
        USER(), NOW()
    );
END //

-- Trigger per UPDATE
CREATE TRIGGER trg_audit_productes_update
AFTER UPDATE ON productes
FOR EACH ROW
BEGIN
    INSERT INTO audit_productes (
        operacio, producte_id,
        nom_anterior, nom_nou,
        preu_anterior, preu_nou,
        estoc_anterior, estoc_nou,
        usuari, data_hora
    ) VALUES (
        'UPDATE', OLD.id,
        OLD.nom, NEW.nom,
        OLD.preu, NEW.preu,
        OLD.estoc, NEW.estoc,
        USER(), NOW()
    );
END //

-- Trigger per DELETE
CREATE TRIGGER trg_audit_productes_delete
AFTER DELETE ON productes
FOR EACH ROW
BEGIN
    INSERT INTO audit_productes (
        operacio, producte_id,
        nom_anterior, preu_anterior, estoc_anterior,
        usuari, data_hora
    ) VALUES (
        'DELETE', OLD.id,
        OLD.nom, OLD.preu, OLD.estoc,
        USER(), NOW()
    );
END //

DELIMITER ;

-- Inserir dades de prova i verificar els triggers
INSERT INTO productes (nom, preu, estoc, categoria_id) VALUES ('Test MySQL', 10.00, 20, 1);
UPDATE productes SET preu = 12.00 WHERE nom = 'Test MySQL';
DELETE FROM productes WHERE nom = 'Test MySQL';

-- Consultar la taula d'auditoria
SELECT * FROM audit_productes ORDER BY data_hora DESC;

-- Referència: l'equivalent en PostgreSQL (Pas 5) per comparar

-- Un sol trigger cobre INSERT, UPDATE i DELETE gràcies a TG_OP
-- El trigger és associat a una funció separada (CREATE FUNCTION + CREATE TRIGGER)

-- Avantatge de PostgreSQL: un sol objecte cobreix les tres operacions DML
-- Avantatge de MySQL: sintaxi més senzilla per a casos simples,
--                    sense necessitat de crear la funció separadament

-- Diferències clau:
-- PostgreSQL: NEW/OLD sense dos punts, TG_OP per detectar l'operació
-- MySQL: NEW.col / OLD.col, trigger separat per operació, no hi ha TG_OP
-- MySQL: un trigger per event (INSERT, UPDATE o DELETE) per taula

Reflexió final - Comparativa

Responeu les preguntes de reflexió de cada pas i, a més:

1. Diferència de triggers: En PostgreSQL heu creat una sola funció i un sol trigger. En MySQL heu necessitat tres triggers separats. Quines implicacions té per al manteniment si cal canviar la lògica d'auditoria?

2. pg_cron vs EVENT SCHEDULER: Quina és la principal diferència arquitectònica entre pg_cron (PostgreSQL) i l'EVENT SCHEDULER de MySQL? (Pista: on resideix la configuració, on s'executa el codi?)

3. Transaccions i triggers: Si el trigger d'auditoria falla (per exemple, per un error d'inserció a audit_productes), l'operació original (l'UPDATE sobre productes) es fa enrere? Proveu-ho bloquejant l'accés a audit_productes i fent un UPDATE.

4. Escalabilitat: Si la botiga processa 10.000 comandes per dia, quants registres genera el trigger d'auditoria en una setmana (considerant que cada comanda té de mitjana 3 modificacions)? Quin seria el pla de manteniment de la taula audit_productes?

5. Seguretat: El procediment process_order requereix permisos sobre les taules comandes, linies_comanda i productes. Si en comptes de donar permisos directes sobre les taules, doneu únicament EXECUTE sobre el procediment, com afecta a la seguretat del sistema?

---

Lliurament

Prepareu un arxiu ZIP amb:

practica_automatitzacio_[nom-alumne]/
├── 01_esquema_i_dades.sql         # Script del Pas 2
├── 02_procediment_process_order.sql
├── 03_trigger_total_comanda.sql
├── 04_trigger_audit_productes.sql
├── 05_pgcron_arxivat.sql
├── 06_proves_integrals.sql
├── 07_mysql_triggers.sql
└── 08_respostes_reflexio.md       # Respostes a totes les preguntes de reflexió

Format del document de respostes

Per a cada pregunta de reflexió, escriviu: - La pregunta (copiant-la) - La vostra resposta (com a mínim 3-4 frases per pregunta) - Si és aplicable, la comanda SQL que heu usat per verificar la resposta i el resultat obtingut.