Pràctica ASI-C6-ASGBD: PostgreSQL II: Creació de bases de dades i usuaris

PostgreSQL. Pràctica 2

Consola psql: [1]

psql és la consola de PostgreSQL, que permet connectar-nos des d'un client. Permet escriure les consultes de forma interactiva y veure els resultats. Admet l'entrada des de fitxer. A més, disposa de meta-comandes i utilitats de shell que possibiliten escriure scripts i automatitzar les tasques d'administració i de producció.

Per executar el següent script pots utilitzar tant la base de dades local de la teva màquina, o connectar-se al servidor 192.168.0.10. En aquest cas, has de saber que en aquest servidor tens una base de dades amb el mateix nom que el teu usuari:

$ ssh asi2axx@192.168.0.10
asi2axx@ubuntu-bbdd:~$ psql -h localhost -p 5432 -U asi2axx -d asi2axx
Contraseña para usuario asi2axx: 
Bienvenido a psql 8.3.8, la terminal interactiva de PostgreSQL.

Teclee: \copyright para ver los términos de distribución
        \h para ayuda sobre órdenes SQL
        \? para ayuda sobre órdenes psql
        \g o punto y coma («;») para ejecutar la consulta
        \q para salir

conexión SSL (cifrado: DHE-RSA-AES256-SHA, bits: 256)

asi2axx=> -> aquest prompt significa que ja estem connectats a la base de dades asi2axx

script biblioteca.sql:

--exemple script PostgreSQL 
\o c:/resultats.log
\echo eliminació de les taules ****************************
\qecho "eliminació de les taules"
drop table prestec;
drop table alumne;
drop table llibre;

\echo esborro la seqüència
\qecho "esborro la seqüència"
DROP SEQUENCE seq_prestec;

\echo creació de les taules *******************************
\qecho "creació de les taules"

create table alumne(
id_alumne integer primary key,
nom varchar(20) not null,
cognom varchar(20) not null,
guixeta char(2) unique,
data_naix date
);

create table llibre(
id_llibre integer primary key,
titol varchar(20),
en_prestec char(1) check(en_prestec in('S','N')),
tema varchar(50) check(tema in('literatura','ciència','arts'))
);

create table prestec(
id_prestec integer primary key,
id_llibre integer references llibre,
id_alumne integer references alumne,
dia_prestec date,
dia_retorn date
);

\echo creo la seqüència
\qecho "creo la seqüència"

CREATE SEQUENCE seq_prestec INCREMENT BY 1 START WITH 1 MINVALUE 1;

\echo inserció dades ******************************************
\qecho "inserció dades"

insert into alumne values(1,'Pere','Gelabert','A3','12/12/1980');
\echo provoca error perquè la guixeta ha de ser única
insert into alumne values(2,'Maria','Batlló','A3','12/12/1980'); 
insert into alumne values(3,'Rita','Ribera','A2','12/09/1980');
insert into alumne values(4,'Josep','Claror','B2',NULL);  

insert into llibre values(1,'Mates III','N','ciència');
\echo provoca error DE CLAU PRIMÀRIA
insert into llibre values(1,'Món Romà','A','arts');
insert into llibre values(2,'Món Grec','N','arts');
insert into llibre values(3,'El Quixot','N','literatura');
insert into llibre values(4,'Novela','N','literatura');

\echo anem a fer un préstec
\qecho "simulem un prestec"

\echo simulo un prestec
insert into prestec values(NEXTVAL('seq_prestec'),4,3,'12/11/2007',NULL);
update llibre set en_prestec='S' where id_llibre=4;

\echo simulo un retorn
\qecho "simulem un retorn"

update prestec set dia_retorn='08/12/2007' where id_llibre=4;
update llibre set en_prestec='N' where id_llibre=4;

\echo faig un altre prestec
insert into prestec values(NEXTVAL('seq_prestec'),1,3,'12/11/2007',NULL);
update llibre set en_prestec='S' where id_llibre=1;

\echo quins alumnes tenen llibres en préstec? i el dia teòric de retorn?
\qecho "quins alumnes tenen llibres en préstec? i el dia teòric de retorn?"

select llibre.id_llibre,titol,dia_prestec, dia_prestec+20 as "dia teor retorn",nom,cognom from llibre, prestec, alumne where llibre.id_llibre=prestec.id_llibre and prestec.id_alumne=alumne.id_alumne and en_prestec='S';

Feina

• Estudiar la comanda psql i totes les seves possibilitats: enllaç que se us dóna, man psql, \?, \h
• Executar totes les comandes anteriors en una sessió interactiva de psql
• crear un script (fitxer), i executar les anteriors comandes de forma desatesa. Fixar-se en la generació del log

  \i FILE                execute commands from file

• fer modificacions sobre les comandes, ampliant el script.