Entrega 2

¿Cómo crear y poblar rápidamente una base de datos sin ORM?

Problema en cuestión

A lo largo de la esta entrega ustedes deben crear un esquema normalizado y poblarlo con datos previamente entregados, el problema con esto es que en las bases de datos por lo general existen relaciones entre las diversas tablas que existen y esto puede implicar que, al cometer un pequeño error en la creación de una tabla, tengamos que borrar y volver a crear tablas manualmente, lo que puede tomar una gran cantidad de tiempo. Otra cosa que puede pasar es que nos hayamos olvidado de un atributo y tengamos que volver a ingresar los datos en el comando de creación (lo que puede generar a veces más de un dolor de cabeza). La pregunta entonces es... cómo podemos solucionar esto sin utilizar programas de terceros?

Solución

Para poder solucionar esto podemos crear archivos .sql en los que declararemos el esquema que queremos utilizar, para poder centralizar los cambios en dicho archivo y poder corregir errores en la base de datos de una forma mucho más simple, además, luego de cambiar el archivo lo único que tendrás que hacer es utilizar el comando:

psql -U usuario base_de_datos < nombre_archivo.sql

Así de simple

Construcción del archivo

En primer lugar, debemos definir cuál va a ser el esquema que queremos crear. Para ello tomaremos el caso de diversas tiendas y el personal que trabajan en ellas, considerando que un trabajador puede estar trabajando en más de una a la vez. El esquema entonces sería el siguiente:

• personal: id int PRIMARY KEY, nombre varchar, rut varchar, fecha_nacimiento date
• tienda: id int PRIMARY KEY, nombre varchar, rubro varchar
• personal_tienda: personal_id int, tienda_id int, FOREING_KEY (personal_id) REFERENCES personal(id), FOREING_KEY (tienda_id) REFERENCES tienda(id)

Ahora, podemos notar que este esquema es fácilmente traspasable a tablas en SQL, por lo que partiremos nuestro archivo con:

-- Creamos las tablas con sus respectivos atributos
CREATE TABLE personal(
  id int PRIMARY KEY,
  nombre varchar(150),
  rut varchar(10),
  fecha_nacimiento date
);
CREATE TABLE tienda(
  id int PRIMARY KEY,
  nombre varchar(150),
  rubro varchar(100)
);
CREATE TABLE personal_tienda(
  personal_id int,
  tienda_id int,
  FOREIGN KEY(personal_id) REFERENCES personal(id),
  FOREIGN KEY(tienda_id) REFERENCES tienda(id)
);

Ahora, el archivo anterior se ve bastante bien, no? Pero... ¿Qué pasaría si quisiéramos correr el archivo y alguna de las tablas ya se encuentra creada? La respuesta es simple... nos saldría el siguiente error:

ERROR:  relation "personal" already exists
ERROR:  relation "tienda" already exists
ERROR:  relation "personal_tienda" already exists

Pero... ¿Cómo podemos solucionar eso? La respuesta es bastante simple, lo que debemos hacer es verificar si las tablas creadas existen, de lo contrario las eliminamos. Para ello entonces debemos modificar el archivo para que quede así:

-- Eliminamos las tablas si es que estas existían antes
DROP TABLE IF EXISTS personal_tienda;
DROP TABLE IF EXISTS personal;
DROP TABLE IF EXISTS tienda;

-- Creamos las tablas con sus respectivos atributos
CREATE TABLE personal(
  id int PRIMARY KEY,
  nombre varchar(150),
  rut varchar(12),
  fecha_nacimiento date
);
CREATE TABLE tienda(
  id int PRIMARY KEY,
  nombre varchar(150),
  rubro varchar(100)
);
CREATE TABLE personal_tienda(
  personal_id int,
  tienda_id int,
  FOREIGN KEY(personal_id) REFERENCES personal(id),
  FOREIGN KEY(tienda_id) REFERENCES tienda(id)
);

Con este código entonces nos aseguramos de que las tablas estén siempre en el último estado posible.

Ahora, sería bastante útil poblar las tablas, no? Para ello podemos nuevamente utilizar nuestro archivo y añadir algo como esto:

INSERT INTO TABLE personal VALUES(1, 'John Doe', '11.101.101-1', 1964-11-30);
...

El problema de eso es que es poco óptimo, sobretodo al momento de querer añadir muchos datos, para ello entonces importaremos datos de unos archivos .csv que coincidentemente nos dieron al interior de una carpeta denominada data cuando nos solicitaron realizar esta base de datos. Para poder entonces importar dicha información en nuestro .sql añadiremos las siguientes líneas:

\COPY personal from './data/personal.csv' DELIMITER ',' CSV HEADER;
\COPY tienda from './data/tienda.csv' DELIMITER ',' CSV HEADER;
\COPY personal_tienda from './data/personal_tienda.csv' DELIMITER ',' CSV HEADER;

Lo que nos deja finalmente con el siguiente archivo .sql

-- Eliminamos las tablas si es que estas existían antes
DROP TABLE IF EXISTS personal_tienda;
DROP TABLE IF EXISTS personal;
DROP TABLE IF EXISTS tienda;

-- Creamos las tablas con sus respectivos atributos
CREATE TABLE personal(
  id int PRIMARY KEY,
  nombre varchar(150),
  rut varchar(12),
  fecha_nacimiento date
);
CREATE TABLE tienda(
  id int PRIMARY KEY,
  nombre varchar(150),
  rubro varchar(100)
);
CREATE TABLE personal_tienda(
  personal_id int,
  tienda_id int,
  FOREIGN KEY(personal_id) REFERENCES personal(id),
  FOREIGN KEY(tienda_id) REFERENCES tienda(id)
);

-- Migramos los datos de los archivos
\COPY personal from './data/personal.csv' DELIMITER ',' CSV HEADER;
\COPY tienda from './data/tienda.csv' DELIMITER ',' CSV HEADER;
\COPY personal_tienda from './data/personal_tienda.csv' DELIMITER ',' CSV HEADER; 

Logrando entonces con este poder migrar los datos.

Casos especiales

Eliminación de Datos

Ahora, hay que tener mucho cuidado con los datos pues normalmente vamos a trabajar con llaves foráneas las cuales ínter relacionan las tablas entre si, por lo que el archivo anterior puede tener uno que otros problemas al momento de eliminar las tablas, noten que el orden de eliminación es a partir de aquellas tablas que están relacionadas a más de una y luego se eliminan las tablas "base". Ahora, para poder evitar dicho problema podemos añadir al comando DROP TABLE un parámetro que nos va a permitir no solo eliminar los datos, sino que también todas las referencias a ellos, el cual es CASCADE, dejándonos finalmente con el siguiente archivo:

-- Eliminamos las tablas si es que estas existían antes
DROP TABLE IF EXISTS personal_tienda CASCADE;
DROP TABLE IF EXISTS personal CASCADE;
DROP TABLE IF EXISTS tienda CASCADE;

-- Creamos las tablas con sus respectivos atributos
CREATE TABLE personal(
  id int PRIMARY KEY,
  nombre varchar(150),
  rut varchar(12),
  fecha_nacimiento date
);
CREATE TABLE tienda(
  id int PRIMARY KEY,
  nombre varchar(150),
  rubro varchar(100)
);
CREATE TABLE personal_tienda(
  personal_id int,
  tienda_id int,
  FOREIGN KEY(personal_id) REFERENCES personal(id),
  FOREIGN KEY(tienda_id) REFERENCES tienda(id)
);

-- Migramos los datos de los archivos
\COPY personal from './data/personal.csv' DELIMITER ',' CSV HEADER;
\COPY tienda from './data/tienda.csv' DELIMITER ',' CSV HEADER;
\COPY personal_tienda from './data/personal_tienda.csv' DELIMITER ',' CSV HEADER; 

Añadir relaciones después de crear una tabla

Ahora, imagina que existe una relación circular o una tabla a la que le quieras añadir una relación con otra que creas después, para ello vas a tener que añadir una constraint al final del archivo que nos permita establecer dicha relación, esta sería de la siguiente forma:

ALTER TABLE tabla_1 ADD CONSTRAINT nombre_constrain FOREIGN KEY (atributo_llave_foranea) REFERENCES tabla_2(atributo_referenciado) ON DELETE CASCADE;

Esto es ideal añadirlo luego de migrar los datos para no generar problemas. Para más detalle recomiendo ver la sección de template

Template

-- Eliminamos las tablas si es que estas existían antes
DROP TABLE IF EXISTS table_name CASCADE;

-- Creamos las tablas con sus respectivos atributos
CREATE TABLE table_name(
  primary_key_attribute primary_key_attribute_type PRIMARY KEY,
  attribute_1 attribute_1_type,
  ...
  other_table_related_attribute other_table_related_attribute_type,
  FOREIGN KEY(other_table_related_attribute) REFERENCES other_table(other_table_attribute)
);

-- Migramos los datos de los archivos 
\COPY table_name from 'path_to_file.csv' DELIMITER ',' CSV HEADER;

-- En el caso de que haya alguna relación "recíproca" entre dos tablas
ALTER TABLE table_name 
ADD CONSTRAINT constrain_name 
FOREIGN KEY (table_name_foreign_key) REFERENCES other_table(other_table_referenced_attribute) 
ON DELETE CASCADE;