Trifulcas

-- 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;

-- Funciones matemáticas
-- Mysql como no podía ser menos incorpora funciones para realizar calculos matemáticos
-- Tenemos los sospechosos habituales: logaritmos, trigonométricas...

-- Valor absoluto: Se usa porque si tenga restas (de fechas, de valores...)

SELECT ABS(-5) AS valor_absoluto;

-- Redondear

SELECT ROUND(3.14159) AS redondeo_entero, ROUND(3.14159, 2) AS redondeo_dos_decimales,
sqrt(2) raiz, round(sqrt(2),3) raiz_redondeada;

-- Truncate nos quita los decimales SIN redondear

SELECT TRUNCATE(1.223,1) a, TRUNCATE(1.263,1) b;

-- round vs truncate

select round(1.2), truncate(1.2,0),round(1.5), truncate(1.5,0),round(1.8), truncate(1.8,0);

-- Trigonométricas y logaritmos...

SELECT pi(),SIN(PI()/2) AS seno_90_grados, COS(0) AS coseno_0_radianes, TAN(PI()/4) AS tangente_45_grados;
SELECT LOG(10) AS logaritmo_10;

-- Raices y exponente

SELECT POW(2,4), sqrt(2), POW(65536,1/4);

-- Números aleatorios
-- el primero es un número aleatorio entre 0 y 1 (no incluído)
-- El segundo es la fórmula para obtener números entre 1 y 6
-- El tercero es el mismo pero si le pongo un valor se utiliza como semilla
-- Y siempre nos devolverá lo mismo
select rand(), FLOOR(1 + (RAND() * 6)), FLOOR(1 + (RAND(15) * 6));

-- A veces se usa random para esto:

select * from actor
order by rand()