Joan: /* Desenvolupament */

2009-11-03T14:52:13Z

Desenvolupament

Pàgina nova

==Objectius==
Unitat Didàctica: UD 7. Programació PL/pgSQL

Assentament dels conceptes i sintaxi de PL/pgSQL.

==Desenvolupament==
Farem un projecte sobre la base de dades d'un restaurant.

En principi treballaràs en local, tot i que també hi hauria la possibilitat de treballar contra el servidor PostgreSQL de l'institut (192.168.0.10).

El primer que has de fer és crear la base de dades ''restaurant'', i crear un usuari per a aquesta base de dades que no sigui l'usuari administratiu:
<pre>
$ psql -h localhost -p 5432 -U postgres postgres
postgres# CREATE DATABASE restaurant ENCODING 'UTF-8';
postgres=# CREATE USER profe WITH ENCRYPTED PASSWORD 'profe';
postgres=# grant all privileges on database restaurant to profe;
postgres=# \q

$ psql -h localhost -p 5432 -U profe restaurant
restaurant#
</pre>
Encara ens falta una cosa, crear el llenguatge plpgsql per a aquesta base de dades. Això ho ha de fer el propietari de la base de dades ''restaurant'', que és l'usuari ''postgres'':
<pre>
$ psql -h localhost -p 5432 -U postgres restaurant
restaurant=# CREATE language plpgsql;
CREATE LANGUAGE
restaurant=# \q
</pre>

Se't proporciona el script per crear l'estructura de la base de dades:
<pre>
-- projecte restaurant
-- IES Jaume Balmes

-- les taules amb què treballaràs són:
-- TAULA(id_taula,taula,zona)
-- CAMBRER(id_cambrer,nom)
-- CARTA(id_plat,plat,tipus,preu)
-- SERVEI(id_servei,id_cambrer,id_taula,dia_hora,preu)
-- DETALL_SERVEI(id_servei,id_plat,quantitat)

DROP SEQUENCE seq_servei;

DROP TABLE DETALL_SERVEI;
DROP TABLE SERVEI;
DROP TABLE CARTA;
DROP TABLE CAMBRER;
DROP TABLE TAULA;

CREATE TABLE TAULA(
id_taula INTEGER PRIMARY KEY,
taula CHAR(3),
zona CHAR(1) CHECK (zona IN ('A','B','C','D'))
);

CREATE TABLE CAMBRER(
id_cambrer INTEGER PRIMARY KEY,
nom VARCHAR(10)
);

CREATE TABLE CARTA(
id_plat INTEGER PRIMARY KEY,
plat VARCHAR(20),
tipus CHAR(1) CHECK (tipus IN ('E','P','S','D','B')),
preu NUMERIC(4,2)
);

CREATE TABLE SERVEI(
id_servei INTEGER PRIMARY KEY,
id_cambrer INTEGER REFERENCES CAMBRER,
id_taula INTEGER REFERENCES TAULA,
dia_hora TIMESTAMP,
preu NUMERIC(4,2)
);

CREATE SEQUENCE seq_servei START 1;

CREATE TABLE DETALL_SERVEI(
id_servei INTEGER REFERENCES SERVEI,
id_plat INTEGER REFERENCES CARTA,
quantitat INTEGER,
PRIMARY KEY(id_servei,id_plat)
);

INSERT INTO TAULA(id_taula,taula,zona) VALUES(1,'T1','A');
INSERT INTO TAULA(id_taula,taula,zona) VALUES(2,'T2','A');
INSERT INTO TAULA(id_taula,taula,zona) VALUES(3,'T3','A');
INSERT INTO TAULA(id_taula,taula,zona) VALUES(4,'T4','B');
INSERT INTO TAULA(id_taula,taula,zona) VALUES(5,'T5','B');
INSERT INTO TAULA(id_taula,taula,zona) VALUES(6,'T6','B');
INSERT INTO TAULA(id_taula,taula,zona) VALUES(7,'T7','C');
INSERT INTO TAULA(id_taula,taula,zona) VALUES(8,'T8','C');
INSERT INTO TAULA(id_taula,taula,zona) VALUES(9,'T9','D');
INSERT INTO TAULA(id_taula,taula,zona) VALUES(10,'T10','D');

INSERT INTO CAMBRER(id_cambrer,nom) VALUES(1,'Pere');
INSERT INTO CAMBRER(id_cambrer,nom) VALUES(2,'Maria');
INSERT INTO CAMBRER(id_cambrer,nom) VALUES(3,'Tomàs');

INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(1,'Amanida catalana','E',4.25);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(2,'Amanida Russa','E',5.5);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(3,'Meló amb pernil','E',6);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(4,'Paella','P',7);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(5,'Crema de carbassó','P',3.5);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(6,'Sopa de peix','P',6.5);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(7,'Sopa aranesa','P',8);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(8,'Torrades escalivada','P',5);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(9,'Verduretes de l''hort','P',5.5);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(10,'Pollastre farcit','S',8.5);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(11,'Peus de porc','S',7);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(12,'Bistec tàrtar','S',8);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(13,'Vedella amb bolets','S',8.5);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(14,'Lluç a la Biscaïna','S',10);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(15,'Mousse de xocolata','D',3);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(16,'Plat de músic','D',2);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(17,'Mel i metó','D',2.5);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(18,'Fruita del temps','D',1);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(19,'Vi Negre','B',5);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(20,'Vi Blanc','B',6);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(21,'Aigua mineral','B',2);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(22,'Cervesa','B',2.5);
</pre>

Per executar aquest script, crees el fitxer restaurant.sql i executes:
<pre>
restaurant# \i restaurant.sql
o bé
restaurant# \i restaurant.sql \g arxiu_XX.log
</pre>
També es pot executar des del prompt del sistema:
<pre>
$ psql -h localhost -U postgres restaurant < script.sql > fitxer.log
</pre>
veiem els ERROR i els NOTICE en la pantalla, i en el fitxer.log hi ha les sentències correctes

<pre>
$ psql -h localhost -U postgres restaurant < script.sql > fitxer.log 2>&1
</pre>
no veiem res a la pantalla: tota la sortida s'envia a fitxer.log, tant sentències correctes com incorrectes

Un cop tens creada l'estructura i has estudiat els lligams que hi ha entre les taules, ja pots crear els següents procediments que se't demana:

Crear els següents procediments:

*''inserir_servei(id_cambrer,id_taula)'': ha d'inserir un registre en la taula servei, amb la data del sistema. El preu es deixa NULL (es calcularà a posteriori). El valor id_servei s'agafa a partir de la seqüència corresponent. És el procediment que s'executa quan el cambrer comença a prendre nota a una taula.
*''inserir_detall_servei(id_servei,id_plat,quantitat)'': insereix un registre en la taula DETALL_SERVEI. És el procediment que s'executa quan el cambrer pren nota de què menjaran els comensals.
*''calcular_servei(id_servei)'': s'executa quan hem de generar el ticket, per saber quan ha de pagar la taula pel servei. S'ha de fer un UPDATE de SERVEI.preu
*''mostra_servei(id_servei)'': mostrar tots els plats que s'han de servir en un servei. Mostra la taula i el cambrer.
*''resum_cambrer(id_cambrer,dia)'': mostrar un informe de tots els serveis que ha fet un cambrer en un dia. Informar de què ha facturat a cada servei, i el total del dia.
*''informe_mensual(mes)'': per al mes indicat, fer un informe del que han facturat tots els cambrers, ordenats de major a menor.

Per a cadascun dels procediments, hauràs de fer un joc de proves. Com sempre, els jocs de proves han de ser significatius i han de contemplar les diferents situacions que es poden donar. Integra tot aquest codi dins del script restaurant.sql, i sigues generós amb els comentaris.

Aquests procediments seran la base per crear una aplicació PHP que treballarà contra Postgres, Oracle i MySQL,

==Entrega==
* entregar els fitxers DAI2AXX_restaurant_NA13.sql i DAI2AXX_NA13.log. Si has treballat per parelles: DAI2AXXXX_script_NA12.sql i DAI2AXXXX_NA12.log, indicant a dins clarament el nom i el número de classe. Exercici individual o per parelles.
* entregar al Moodle: http://192.168.0.15/moodle

==Recursos==
* Teoria: [[Teoria_PL/pgSQL]]
* També pots trobar la teoria en el Moodle en format pdf.
* El professor penjarà la solució dels exercicis en el Moodle, a posteriori.
* Solució: [[Imatge:Solucio procediments postgres.sql.zip]]

==Durarda==
4 hores

{{Autor}}, octubre 2009
[[Categoria: IES Jaume Balmes]]
[[Categoria: Pràctiques DAI-C8-EC]]

DAI-C8-EC: Programació PL/pgSQL - Historial de revisió

Joan: /* Desenvolupament */