# Creació de Bases de dades Objecte relacionals amb postgreSQL. Connectors.

## Tipus bàsics de dades a postgresql

* Lògics:
* Es declaren com **boolean**.
* Ocupen 1 by.
* Valors possibles: true/false
* Numèrics:
* Enters:
* smallint (2 by) (de -32768 a +32767).
* integer (4 by) (de -2147483648 a [phone]).
* bigint (8 by) (de -9223372036854775808 a +9223372036854775807).
* Coma flotant:
* real (4 by) (precisió de 6 dígits decimals).
* double precision (8 by) (precisió de 15 dígits decimals).
* Precisió arbitrària:
* numeric:
* Poden emmagatzemar nombres amb precisió variable (a diferència dels real o doble).
* Així, un camp de tipus numeric ha de declarar la seva màxima precisió i la seva màxima escala com **numeric(precisio, escala)**
* La precisió representa la longitud del nombre.
* L'escala representa el nombre màxim de dígits que hi pot haver darrera de la coma. Pot prendre valors negatius!
* Exemples:
* **numeric(3,1)** pot emmagatzemar nombres en el rang [-99.9,+99.9].
* **numeric(5,-3)** significa que els 3 darrers dígits (els de la dreta) s'arrodoneixen als milers, per exemple el 12345 passa a ser 12000, el 8789 al 9000 o el 999 passa a ser 0 (no arriba a 1000).
* Text:
* char(n):
* longitud fixa, omple els espais buits a espais en blanc (blank-padded)
* varchar(n):
* longitud variable, el nombre indicat com a paràmetre indica la longitud màxima, però no omple els buits amb res.
* text:
* longitud variable sense límit.
* Serial:
* És un tipus específic pels camps autoincrementals. Idoni per les típiques claus primàries seqüencials.
* Equival a un nombre enter, per tant podrem tenir smallserial (2 by), serial (4 by) i bigserial (8 by).
* Dates i hores (vegeu [datatype-datetime](https://www.postgresql.org/docs/current/datatype-datetime.html)):
* date (4 by).
* time (sense zona horària 8 by, amb zona horària 12 by).
* timestamp (amb i sense zona horària 8 by).

## Bases de dades Objecte Relacionals

Es tracta d'un tipus de bases de dades que neixen com extensió de les bases de dades relacionals on s'incorporen característiques d'orientació a objectes.

A diferència de les bases de dades relacionals, veurem tot seguit com aquestes bases de dades incorporen tipus estructurats de dades i com és possible implementar el mecanisme d'herència entre taules (és a dir l'especialització entre relacions).

## Tipus estructurats (o compostos)

* És un tipus de dada definit per l'usuari.
* Sols es pot utilitzar a la base de dades on s'ha definit.
* Per crear un tipus compost fem:

CREATE TYPE adresa AS (
    carrer text,
    nombre smallint,
    ciutat text,
    cp text,
    provincia text); 

A partir d'aquí podem fer-lo servir quan creem una taula com si fos un tipus més, fixeu-vos:

CREATE TABLE persones01 (
    numpers serial PRIMARY KEY,
    nompers text,
    cognomspers text,
    datanaix timestamp,
    domicili adresa);

Així quan fem l'INSERT el fem com segueix:

INSERT INTO persones01 (nompers,cognomspers,datanaix,domicili)
VALUES ('Pepet','Martínez López','1975-09-09',ROW('Carrer Balmes',10,'Barcelona','08001',
'Barcelona'));

Si ara vull consultar el nom de la persona i el carrer i nombre on viu faria:

SELECT nompers, (domicili).carrer,(domicili).nombre FROM persones01;

Obtenint aquest resultat:

 nompers |    carrer     | nombre
---------+---------------+--------
 Pepet   | Carrer Balmes |     10
(1 row)

## Col·leccions

* Postgres permet emmagatzemar arrays de dades com una columna més.
* Agafem la taula anterior i anem a afegir un array de telèfons:

ALTER TABLE persones01 ADD telefons text[];

Per fer-ho cal afegir els caràcters [] després del tipus de dada. Aplica tant per tipus bàsics com per tipus estructurats (com és el de l'adreça que acabem de crear).

En aquest cas, si volem inserir un nou registre a persones01 procedim així:

INSERT INTO persones01 (nompers,cognomspers,datanaix,
domicili,telefons)
VALUES ('Laura','Martínez López','1975-09-09',ROW('Carrer Balmes',10,'Barcelona',
'08001','Barcelona'),'{"636660000",
"756909090"}');

Algunes consultes que es poden fer amb aquests camps:

1) Tots els telèfons:

SELECT telefons from persones01;

       telefons
-----------------------

 {636660000,756909090}
(2 rows)

2) El primer telèfon:

SELECT telefons[1] from persones01;

 telefons
-----------

 636660000
(2 rows)

3) Del primer al segon telèfon de la persona (útil quan hi ha molts telèfons i volem un intèrval en concret):

SELECT telefons[1:2] from persones01;
       telefons
-----------------------

 {636660000,756909090}
(2 rows)

4) Mostrar-ho en format cadena de caràcters:

samp1=# SELECT array_to_string(telefons,',') AS telefons from persones01;

      telefons
---------------------

 636660000,756909090
(2 rows)

## Tipus enumerats

És un tipus de dada composta/estructurada per llistes fixes de constants:

CREATE TYPE professions AS ENUM ('profe','tècnic','cap');

Imaginem una taula anomenada empresa on el seu personal pot pertànyer a diferents rols definits segons el tipus enumerat anterior. A l'hora de crear-la faríem:

CREATE TABLE empresa (
idEmpresa serial PRIMARY KEY,
nomEmpresa text UNIQUE,
rols professions);

Quan inserim un registre sols podrem informar el camp rols amb un dels valors definits segons el tipus enumerat:

INSERT INTO empresa (nomEmpresa,rols) VALUES ('My valued DAM2B','profe');

Quan fem la consulta obtenim:

select * from empresa where idempresa=3;

 idempresa |   nomempresa    | rols
-----------+-----------------+-------
         3 | My valued DAM2B | profe
(1 row)

Si provem a inserir amb un valor no contingut al tipus enumerat obtindrem un error:

INSERT INTO empresa (nomEmpresa,rols) VALUES ('My valued DAM2A','cuiner');
ERROR:  invalid input value for enum professions: "cuiner"
LINE 1: ...presa (nomEmpresa,rols) VALUES ('My valued DAM2A','cuiner');
                                                             ^

## Claus foranes

Per referenciar una taula amb una altra emprem la instrucció REFERENCES.

Posem per cas que vull relacionar la taula empresa amb la taula departament. Ho podríem fer així:

CREATE TABLE departament (
idDepartament serial PRIMARY KEY,
nomDepartament text UNIQUE,
numEmpleats smallint);

ALTER TABLE empresa ADD idDepartament smallint
REFERENCES departament(idDepartament);

## Herència

L'herència entre taules permet que una taula pugui incorporar els camps d'una altra taula, de forma semblant a com succeeix entre classes.

Per fer-ho emprem la instrucció INHERITS a la taula que hereta els camps d'una altra. En aquest exemple creem una taula d'empleats que hereta els camps de la taula persones que hem creat abans:

CREATE TABLE empleat (
numEmpleat serial PRIMARY KEY
) INHERITS (persones01);

D'aquesta forma la nova taula empleat té el camp numEmpleat més els camps de la taula persones01. Vegem-ho:

INSERT INTO empleat (nompers,cognomspers,datanaix,domicili,telefons)
VALUES ('Carlos','Soriano','1975-10-09',ROW('Carrer Aribau',10,'Barcelona', '08001','Barcelona'),'{"636660000","756909090"}');

Això provoca que una inserció tant a empleats com a persones01:

select * from persones01;

 numpers | nompers |  cognomspers   |      datanaix       |                     domicili                      |       telefons
---------+---------+----------------+---------------------+---------------------------------------------------+-----------------------
       1 | Pepet   | Martínez López | 1975-09-09 00:00:00 | ("Carrer Balmes",10,Barcelona,08001,"Barcelona));+|
         |         |                |                     | ")                                                |
       2 | Laura   | Martínez López | 1975-09-09 00:00:00 | ("Carrer Balmes",10,Barcelona,08001,Barcelona)    | {636660000,756909090}
       3 | Carlos  | Soriano        | 1975-10-09 00:00:00 | ("Carrer Aribau",10,Barcelona,08001,Barcelona)    | {636660000,756909090}
(3 rows)

select * from empleat;

 numpers | nompers | cognomspers |      datanaix       |                    domicili                    |       telefons        | numempleat
---------+---------+-------------+---------------------+------------------------------------------------+-----------------------+------------
       3 | Carlos  | Soriano     | 1975-10-09 00:00:00 | ("Carrer Aribau",10,Barcelona,08001,Barcelona) | {636660000,756909090} |          1
(1 row)

## Connector java i codi d'exemple

Al repositori següent disposeu d'un codi java d'exemple per interaccionar amb una base de dades objecte relacional on apareixen les taules abans utilitzades:

[BDOR_sample](https://github.com/atalens1/BDOR_sample)

Es tracta d'un projecte maven on hem utilitzat la darrera dependència del postgresql, la qual podreu treure d'[aquest enllaç](https://mvnrepository.com/artifact/org.postgresql/postgresql).

Per connectar-nos a la base de dades utilitzem un connector JDBC com els ja vistos anteriorment, però adaptat a postgresql:

jdbc:postgresql://localhost:54320/

On el port 54320 és el port local on es mapeja el port 5432 que utilitza postgresql al contenidor docker.

Com també podreu apreciar, a l'hora d'inserir un nou registre escrivim la instrucció INSERT fil per randa a com ho fem en instruccions DML:

String query = "INSERT INTO persones01 (nompers,cognomspers,datanaix,domicilipers,telefons)" +
            " VALUES (?,?,?,ROW(?,?,?,?,?),?)";

Això és degut a les limitacions que té el driver de postgresql, ja que l'API de JDBC permet perfectament crear tipus estructurats i utilitzar el mètode setObject sobre preparedStatement. Però el driver corresponent a postgresql no en permet fer ús.

Per acabar, fixeu-vos que per poder passar l'array de telèfons fem servir el mètode setArray:

Array telArray = conn.createArrayOf("text", pers.getTelefons());
    
prep.setArray(9, telArray);

La resta de mètodes i funcions són molt semblants a quan interaccionem amb una base de dades relacional fent servir JDBC.