Autor: Juan Pablo Fuentes

-- Dentro de sql lo que tenemos es dos maneras
-- de incorporar programación: funciones y procedimientos
-- ¿Para qué existen? Porque hay veces que queremos hacer cosas
-- Y el SQL normal es insuficiente
-- Para tareas de mantenimiento, de control, de lógica de negocio

-- Los procedimientos almacenados se utilizan normalmente para realizar
-- operaciones que van más allá de devolver un resultado
-- Tienen una sintaxis un poco más compleja que las funciones
-- https://proyectoa.com/anadir-y-usar-procedimiento-almacenado-stored-procedure-en-mysql-server/
-- https://www.dolthub.com/blog/2024-01-17-writing-mysql-procedures/

-- Para empezar tenemos parámetros de entrada (IN) y parámetros de salida (OUT)
-- que se definen en el inicio
-- Ejemplo: (IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
-- Cuando llamo al procedimiento almacenado (call) pasaré 2 parámetros y recibiré uno

call film_in_stock(1,1,@total);
select @total;

-- Luego dentro del procedimiento almacenado tengo las mismas opciones
-- que en las funciones. Podemos programar de verdad
-- Tenemos variables, estructuras de control (if) bucles (while)...

CREATE PROCEDURE fibonacci(n int, out answer int)
BEGIN 
    DECLARE i int default 2;
    DECLARE p, q int default 1;
    SET answer = 1;
    WHILE i < n DO
        SET answer = p + q;
        SET p = q;
        SET q = answer;
        SET i = i + 1;
    END WHILE;
END;

call fibonacci(10,@numero);
select @numero;

-- Ejemplo con utilidad
-- Imaginemos que queremos de dar de alta un cliente
-- Antes del cliente tenemos que tener una dirección
-- Esa dirección tiene que tener una ciudad
-- Esa ciudad tiene que tener un país
-- Spain, logroño, carmen medrano 11, pepito perez
-- Para hacer esto tengo que hacer una serie de comprobaciones
-- Y si no de inserciones
-- Esto con un procedimiento almacenado va muy bien

CREATE DEFINER=`root`@`localhost` PROCEDURE `alta_cliente`(
in p_country varchar(50),
in p_city varchar(50),
in p_address varchar(50),
in p_first_name varchar(50),
in p_last_name varchar(50),
out p_customer_id int
)
BEGIN
declare v_country_id int;
declare v_city_id int;
declare v_address_id int;
declare v_customer_id int;

-- Busco el pais que se llame como el parámetro
select country_id into v_country_id from country
where country=p_country;
-- O existe o no existe. Si no existe, lo inserto
IF v_country_id is null then
 insert into country(country) values (p_country);
 select last_insert_id() into v_country_id;
END IF;
-- Al llegar aquí en v_country_id tengo el id del país que me han
-- pasado como parámetro. Bien porque existía y he recuperado ese valor
-- Bien porque no existía, lo he insertado, y he recuperado el id
-- del registro insertado

-- Con la ciudad hacemos algo parecido

select city_id into v_city_id from city
where city=p_city and country_id=v_country_id;
 
IF v_city_id is null then
 insert into city(city,country_id)
 values (p_city,v_country_id);
 select last_insert_id() into v_city_id;
END IF;

-- Igual que antes, al llegar aquí en v_city-id tengo el id de la ciudad

select address_id into v_address_id from address
where address=p_address and city_id=v_city_id;
 
IF v_address_id is null then
 insert into address(address,city_id)
 values (p_address,v_city_id);
 select last_insert_id() into v_address_id;
END IF;

select customer_id into v_customer_id from customer
where first_name=p_first_name and last_name=p_last_name and address_id=v_address_id;
 
IF v_customer_id is null then
 insert into customer(first_name,last_name,address_id,store_id)
 values (p_first_name,p_last_name, v_address_id,1);
 select last_insert_id() into v_customer_id;
END IF;
 
select v_customer_id into p_customer_id;
END

-- ejemplo de uso

call alta_cliente('Spain','Barcelona','Agla 6','Pepito','Perez',@customer_id);
select @customer_id;

call alta_cliente('Spain','Barcelona','Sant Antoni 3','Ana','Pi',@customer_id);
select @customer_id;

call alta_cliente('Trubulandia','Trubulú','Sant Antoni 3','Ana','Pi',@customer_id);
select @customer_id;

-- Dentro de sql lo que tenemos es dos maneras
-- de incorporar programación: funciones y procedimientos
-- ¿Para qué existen? Porque hay veces que queremos hacer cosas
-- Y el SQL normal es insuficiente
-- Para tareas de mantenimiento, de control, de lógica de negocio

-- Por ejemplo yo puedo tener funciones que me sean de utilidad

CREATE DEFINER=`root`@`localhost` FUNCTION `inventory_in_stock`(p_inventory_id INT) RETURNS tinyint(1)
    READS SQL DATA
BEGIN
    DECLARE v_rentals INT;
    DECLARE v_out     INT;

    #AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE
    #FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED

    SELECT COUNT(*) INTO v_rentals
    FROM rental
    WHERE inventory_id = p_inventory_id;

    IF v_rentals = 0 THEN
      RETURN TRUE;
    END IF;

    SELECT COUNT(rental_id) INTO v_out
    FROM inventory LEFT JOIN rental USING(inventory_id)
    WHERE inventory.inventory_id = p_inventory_id
    AND rental.return_date IS NULL;

    IF v_out > 0 THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
END

-- Como utilizaría lo anterior

select title,inventory_in_stock(inventory_id) stock
from film join inventory on film.film_id=inventory.film_id;

-- Otro ejemplo:

CREATE DEFINER=`root`@`localhost` FUNCTION `max_rental`() RETURNS int(11)
BEGIN
  DECLARE maximo INT;
  
select max(total) into maximo from (
select  count(rental_id) total
    from rental
    group by year(rental_date) , month(rental_date) 
    order by total desc
    ) temp;
    
RETURN maximo;
END

-- como lo usaría
select year(rental_date) anyo, month(rental_date) mes, count(rental_id) total
from rental
group by anyo,mes
having total=max_rental();

-- Triggers
-- Los triggers son acciones que se ejecutan cuando modificamos datos
-- en una tabla. Se suelen usar para realizar tareas de mantenimiento
-- de integridad que no permite el sql
-- Ya estamos trabajando con programación T-SQL
-- https://dev.mysql.com/doc/refman/8.4/en/triggers.html

-- Los triggers los tenemos en la pestaña 'triggers'
-- son before o update y para insert, update y delete

-- Ejemplos
-- Este hace que cuando borremos un actor, si su id está entre 1 y 20 no nos deja
-- En caso contrario lo inserta en un log
CREATE TRIGGER `sakila`.`actor_BEFORE_DELETE` BEFORE DELETE ON `actor` FOR EACH ROW
BEGIN

if OLD.actor_id between 1 and 20 then
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No puedes borrar';
else
 insert into actor_log(first_name, last_name) values (OLD.first_name, OLD.last_name);
end if;
END

CREATE DEFINER=`root`@`localhost` TRIGGER `sakila`.`payment_BEFORE_INSERT` BEFORE INSERT ON `payment` FOR EACH ROW
BEGIN
if NEW.amount>=10 then
 set NEW.amount=9.99;
end if;
END

CREATE DEFINER = CURRENT_USER TRIGGER `sakila`.`payment_BEFORE_UPDATE` BEFORE UPDATE ON `payment` FOR EACH ROW
BEGIN
if NEW.amount > OLD.amount then
 set NEW.amount=OLD.amount;
end if;
END

update customer
set password= sha2(concat(customer_id,email,'daleatucuerpoalegriamacarena'),512);

SELECT * FROM customer
where password=sha2(concat(44,'MARIE.TURNER@sakilacustomer.org','daleatucuerpoalegriamacarena'),512);

update customer
set mail_crypt=aes_encrypt(email,'daleatucuerpoalegriamacarena');

SELECT * FROM customer
where cast(aes_decrypt(mail_crypt,'daleatucuerpoalegriamacarena') as char)
 like 'c%';

-- Funciones de información

-- Funciones de información general del entorno
select current_user(), database(), last_insert_id(),version();


-- funciones que nos sirven para conocer como tengo la base de datos

-- Estos son los datos de las tablas
select * from information_schema.tables;

-- Estos son los datos de las columnas
select * from information_schema.columns;

-- Aquí tenemos todda la información que tenemos en el esquema:
-- https://dev.mysql.com/doc/refman/8.4/en/information-schema-table-reference.html

-- También podemos utilizar SHOW
-- https://dev.mysql.com/doc/refman/8.4/en/show.html

-- Ver todas las tablas
show tables;

-- Ver todas columnas de film
show columns from film;

-- Mostrar los índices de film
show index from film;

-- funciones de compresión
-- COMPRESS y UNCOMPRESS

-- Si yo tengo información que pueda comprimirse
select length(repeat('ab',500)) foo;

select compress(repeat('ab',500)) foo;
-- Paso de una longitud de 1000 caracteres a 22
select length(compress(repeat('ab',500))) foo;

select length(description) foo, length(compress(description)) foo2
from film;

update film set comprimido=compress(description);

SELECT cast(uncompress(comprimido) as char) FROM thecorner_sakila.film;

-- Encriptar y desencriptar
-- Porque hay veces que necesitamos almacenar información
-- que no queremos que se vea pero queremos
-- obtenerla de nuevo
-- P. ej. tarjeta de crédito

-- Para usamos el par aes_encrypt y aes_decrypt
-- Nos sirven para encriptar una información con una contraseña
-- Y con la misma contraseña podemos desencriptar
-- Nos devuelve un objeto binario, si lo tenemos que almacenar
-- tenemos que usar BLOB o varbinary
select aes_encrypt('cadena a encriptar','cadena que es la clave') foo;
select aes_decrypt(aes_encrypt('cadena a encriptar','cadena que es la clave'),'cadena que es la clave') foo;

-- SIempre tenemos que tener una clave, y la usamos tanto para encriptar
-- como para desencriptar. Si no la conocemos no podemos desencriptar

update actor set apellido=aes_encrypt(last_name,'contraseña');

-- Usando la contraseña puedo decodificar el campo encriptado
-- Lo convierto a cadena para poder leerlo
select cast(aes_decrypt(apellido,'contraseña') as char) ap from actor;

-- Si uso contraseñas cuanto más largas y complicadas mejor
-- Muchas veces se usan cosas como esta:

update actor set apellido=aes_encrypt(last_name,sha2('contraseña',512));
select cast(aes_decrypt(apellido,sha2('contraseña',512)) as char) ap from actor;

SELECT last_name, cast(apellido as char) apell, cast(aes_decrypt(apellido,sha2('contraseña',512)) as char) ap FROM thecorner_sakila.actor;

-- Puedo generar contraseñas que tengan en cuenta datos del registro
update actor set apellido=aes_encrypt(last_name,sha2(concat(actor_id,'contraseña'),512));

-- Las funciones de encriptación
-- Hay ciertos datos que se tienen que guardar encriptados
-- filtraciones de bases de datos hay siempre
-- Si yo guardo el dato encriptado como se si es igual a algo
-- Si yo quiero saber si la contraseña que me ha introducido un usuario
-- Es igual a la que tengo almacenada lo único que tengo que hace
-- Es encriptar la contraseña que me han introducido y comprobar que es igual
-- a la de la base de datos
-- Porque las funciones de encriptación con la misma entrada
-- Devuelven la misma salida
-- Nunca a partir de los datos encriptados se puede obtener el valor original
-- El problema es que se pueden generar diccionarios con combinaciones de letras
-- Y no hace falta poder obtener el valor original
-- hay muchas páginas que hacen md5 reverse (https://md5.gromweb.com/ , https://md5decrypt.net/en/)
-- En realidad son funciones de HASH: obtener un número único más pequeño a partir de
-- un conjunto de datos, como en el DNI
-- 12345678 Z (Z es el HASH de 12345678)

-- Vamos al turrón ¿Cómo codificar?
-- La más simple_ md5 devuelve una cadena de 32 caracteres de longitud

select md5('cualquier cosa');
select md5('a');
select md5('cualquier cosa en un lugar de la mancha de cuyo nombre no quiero acordarme') foo;

-- No se recomienda usar md5 porque tiene unos años y está muy investigada
-- Sobre todo hay muchos diccionarios con casi todas las combinaciones
-- SI yo al md5 le concateno una cadena aleatoria gano en complejidad

select  md5('cadena') res;
select  md5(concat('cadena','una cadena aletoria con cosas raras &(4&%$U0atgoNBl3vl8gGauSUhZUVxJH59lrdB')) res;

-- Ahora se usa el SHA que hay SHA1  SHA2
-- Esta función es más segura que MD5

select sha('cadena');
select sha2('cadena',224);

-- El SHA2 viene en varios sabores: 224, 256, 384, 512
-- Número de bits de longitud de la clave generada, cuanto más grande
-- más seguro y también más coste de cálculo

select sha2('cadena',256);
select sha2('cadena',384);
select sha2('cadena',512);

-- Mi recomendación particular es que concatenemos una cadena incluso
-- si usamos SHA2 '%&34m.ñAA'

update actor set password=sha2(concat(first_name,'%&34m.ñAA'),512);

-- Como se yo si por ejemplo me entra un usuario y me dice
-- Soy el usuario 1 y mi password es 'PENELOPE'
-- El resultado de sha2(concat('PENELOPE','%&34m.ñAA'),512)
-- debería ser igual a lo que tengo en la BD
select * from actor
where actor_id=1 and
password=sha2(concat('PENELOPE','%&34m.ñAA'),512);

-- Esto no me devuelve nada porque la contraseña no coincide
select * from actor
where actor_id=1 and
password=sha2(concat('penelope','%&34m.ñAA'),512);

-- Lo mismo para cualquier actor

select * from actor
where actor_id=152 and
password=sha2(concat('BEN','%&34m.ñAA'),512);

-- Hay mil sitios que nos permiten decodificar así que cuidado
-- https://10015.io/tools/sha512-encrypt-decrypt

-- Mostrar los nombres de los clientes que han alquilado 
-- una película los fines de semana

select distinct first_name, last_name
from customer c join rental r on c.customer_id=r.customer_id -- Uso alias para evitar repetir el nombre de la tabla
where weekday(rental_date)>=5; -- weekday(rental_date) in (5,6) weekday(rental_date) between 5 and 6

-- Mostrar el total de pagos realizados en 2006

select sum(amount) total
from payment
where year(payment_date)=2006;

-- Mostrar las películas alquiladas en agosto de 2005

select distinct f.* from film f
join inventory i on f.film_id=i.film_id
join rental r on i.inventory_id=r.inventory_id
where year(rental_date)=2005 and month(rental_date)=8;

-- ¿En qué mes de qué año se han alquilado más películas?

select year(rental_date) anyo, month(rental_date) mes, count(rental_id) total
from rental
group by anyo,mes
order by total desc
limit 1;

-- si soy purista y quiero comprobar repetidos

select year(rental_date) anyo, month(rental_date) mes, count(rental_id) total
from rental
group by anyo,mes
having total=(
	select  count(rental_id) total
	from rental
	group by year(rental_date) , month(rental_date) 
	order by total desc
	limit 1
);

-- Hacer una consulta que nos muestre el total de pagos 
-- realizados entre semana y en fin de semana

select if(weekday(payment_date)<5,'Diario','Fin de semana') tipo, sum(amount) total
from payment
group by tipo;

select case 
	when weekday(payment_date)<5 then 'Diario'
	else 'Fin de semana'
end tipo, sum(amount) total
from payment
group by tipo;

-- En esta solución Jaume utiliza una subconsulta para crear una variable virtual
-- weekday_payment (los pagos de fin de semana)

SELECT SUM(`amount`) AS total_payments, weekday_payment, SUM(`amount`) - weekday_payment AS weekend_payment
FROM `payment`
JOIN (
-- Aquí se usa la subconsulta para crear una variable
    SELECT SUM(`amount`) AS weekday_payment
    FROM `payment`
    WHERE WEEKDAY(`payment_date`) < 5
) AS weekday_payment_tmp;