{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Vistas, disparadores y lo que SQLite no tiene\n", "\n", "Consultas con nombre, código que corre solo, y los procedimientos almacenados que en uno de los cuatro motores no existen.\n", "\n", "Cuaderno de soluciones del capítulo 21 de **SQL desde cero**, de Miss Yera.\n", "\n", "Corre de arriba abajo. Si lo abres en Google Colab no necesitas instalar nada.\n", "\n", "Capítulo completo: https://missyera.com/guias/sql-desde-cero/vistas-procedimientos-triggers/\n", "\n", "Este es el cuaderno de **soluciones**. Trae el código de cada ejercicio, la\n", "explicación de la trampa y la respuesta del quiz. Si vienes del cuaderno de\n", "práctica sin haberlo intentado, vuelve 🙂" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Antes de empezar\n", "\n", "Se baja la base y se deja lista una función `q()` que corre las consultas." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import sqlite3\n", "import urllib.request\n", "\n", "import pandas as pd\n", "\n", "urllib.request.urlretrieve(\"https://missyera.com/static/datasets/tienda.db\", \"tienda.db\")\n", "con = sqlite3.connect(\"tienda.db\")\n", "\n", "def q(sql):\n", " \"\"\"Corre las sentencias del bloque y devuelve la ultima como tabla.\n", "\n", " Parte por sentencias igual que la consola de SQLite, porque un bloque puede\n", " traer varias y un CREATE TRIGGER lleva punto y coma dentro de su cuerpo.\n", " \"\"\"\n", " resultado = None\n", " trozo = \"\"\n", " for linea in sql.splitlines(keepends=True):\n", " trozo += linea\n", " if sqlite3.complete_statement(trozo):\n", " if trozo.strip():\n", " cur = con.execute(trozo.strip())\n", " resultado = (pd.DataFrame(cur.fetchall(),\n", " columns=[d[0] for d in cur.description])\n", " if cur.description else None)\n", " trozo = \"\"\n", " if trozo.strip():\n", " cur = con.execute(trozo.strip())\n", " resultado = (pd.DataFrame(cur.fetchall(),\n", " columns=[d[0] for d in cur.description])\n", " if cur.description else None)\n", " con.commit()\n", " return resultado\n", "\n", "q(\"SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Hasta aquí tus consultas viven en tu archivo, en tu laptop. Este capítulo va\n", "de lo contrario: cosas que se guardan *dentro* de la base y que están ahí\n", "para todo el mundo, corra la consulta quien la corra 🏠\n", "\n", "Son tres, y en SQLite solo hay dos, que es la sorpresa del capítulo.\n", "\n", "Y una pregunta que vale más de lo que parece: **¿en tu equipo todos calculan \"cliente activo\" igual?** Si cada quien lo escribe a su manera, salen tres números para la misma pregunta, y de eso va la primera mitad de este capítulo 🏷️" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Una vista es una consulta con nombre" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "En el capítulo 7 escribimos el resumen por ciudad. Es de esas consultas que\n", "pides una vez y después la quiere todo el mundo, y cada persona la reescribe un\n", "poquito distinta y salen tres números distintos para la misma pregunta 🫠" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "CREATE VIEW ventas_por_ciudad AS\n", "SELECT c.ciudad,\n", " COUNT(DISTINCT c.id) AS clientes,\n", " COUNT(p.id) AS pedidos,\n", " ROUND(SUM(p.monto), 2) AS soles\n", "FROM clientes c\n", "LEFT JOIN pedidos p ON p.id_cliente = c.id\n", "GROUP BY c.ciudad;\n", "SELECT * FROM ventas_por_ciudad ORDER BY soles DESC;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "A partir de ahora `ventas_por_ciudad` se usa como si fuera una\n", "tabla, y nadie más tiene que acordarse del `LEFT JOIN` ni del\n", "`COUNT(DISTINCT)`." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT ciudad, soles FROM ventas_por_ciudad WHERE soles > 100000 ORDER BY soles DESC;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Una vista no guarda datos.** Es un apodo: cada vez que la\n", "consultas, la base pega tu consulta encima de la de dentro y ejecuta todo junto.\n", "Por eso siempre está al día, y por eso no es más rápida que escribir la consulta\n", "a mano.\n", "\n", "Para qué la uso yo, que es la parte que casi nunca te cuentan:\n", "\n", "- 📐 **Para que \"cliente activo\" signifique lo mismo** en los\n", "cinco reportes. La definición vive en un sitio.\n", "\n", "- 🧼 **Para esconder la suciedad.** Una vista que ya venga con el\n", "`UPPER(TRIM(...))` del capítulo 5 y nadie tenga que acordarse.\n", "\n", "- 🔐 **Para dar acceso a una parte.** Le das permiso a la vista y\n", "no a la tabla, y esa persona ve las columnas que le tocan y ninguna más." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "CREATE VIEW clientes_limpios AS\n", "SELECT id, UPPER(TRIM(nombre)) AS nombre, ciudad, segmento, fecha_alta\n", "FROM clientes;\n", "SELECT id, nombre FROM clientes_limpios WHERE id IN (1, 12, 26) ORDER BY id;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Los dieciocho nombres sucios del capítulo 5, arreglados de una vez y para\n", "todos, sin tocar la tabla original. Esa es mi vista favorita de este libro 🧽\n", "\n", "Lo que no se puede hacer con una vista es escribir a través de ella." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "try:\n", " q(\"\"\"\n", " INSERT INTO clientes_limpios (id, nombre, ciudad, segmento, fecha_alta)\n", " VALUES (200, 'NUEVA', 'Lima', 'Bodega', '2026-06-24');\n", " \"\"\")\n", "except Exception as e:\n", " print(f'{type(e).__name__}: {e}')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Y la queja que tiene que salir es esta:\n", "\n", "```\n", "OperationalError: cannot modify clientes_limpios because it is a view\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "¿Se puede escribir a través de una vista?\n", "\n", "| PostgreSQL | `sí si la vista es simple; si no, con un trigger INSTEAD OF` |\n", "|---|---|\n", "| MySQL | `sí si la vista es simple, o sea sin GROUP BY, DISTINCT ni funciones raras` |\n", "| SQL Server | `sí si es simple, o con un trigger INSTEAD OF` |\n", "| SQLite | `nunca directamente: siempre hace falta un trigger INSTEAD OF` |\n", "\n", "Que una vista \"simple\" sea escribible y una complicada no, y que dónde está la raya lo decida cada motor, es de las cosas que hacen que yo trate las vistas como solo lectura y listo." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT name FROM sqlite_master WHERE type = 'view' ORDER BY name;\n", "\"\"\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "DROP VIEW clientes_limpios;\n", "SELECT COUNT(*) AS vistas FROM sqlite_master WHERE type = 'view';\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## La vista que recorta filas: quién puede ver qué" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Hasta acá la vista sirvió para no repetir una consulta. Tiene otro uso que es\n", "de seguridad y que vale más: **dar acceso a una parte de la tabla en vez de\n", "a la tabla**.\n", "\n", "El caso típico. La jefa de la zona norte tiene que ver sus pedidos y no los de\n", "las otras zonas. La respuesta perezosa es darle acceso a `pedidos` y\n", "pedirle que filtre. La respuesta correcta es que no pueda ver lo demás:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "CREATE VIEW pedidos_lima AS\n", "SELECT p.*\n", "FROM pedidos p\n", "JOIN clientes c ON c.id = p.id_cliente\n", "WHERE c.ciudad = 'Lima';\n", "\n", "SELECT (SELECT COUNT(*) FROM pedidos) AS todos,\n", " (SELECT COUNT(*) FROM pedidos_lima) AS solo_lima;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Novecientos contra ciento cuatro. A quien use la vista le existen ciento\n", "cuatro pedidos, y los otros no es que estén prohibidos: es que no aparecen 🔒\n", "\n", "Y ahí está la diferencia con pedir que filtre, que no es de comodidad: un\n", "filtro se olvida, y una consulta con un `WHERE` mal escrito devuelve\n", "de más sin avisar. Lo que no se puede ver no se puede olvidar de filtrar.\n", "\n", "### Cómo se hace esto en serio, fuera de SQLite\n", "\n", "La vista de arriba tiene la ciudad escrita a mano, así que harían falta seis\n", "vistas para seis zonas. Las bases grandes lo resuelven con una regla sola que se\n", "aplica sobre la tabla y se evalúa según quién pregunta. Se llama\n", "**seguridad a nivel de fila**, o *Row Level Security*, y en\n", "PostgreSQL se ve así:" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "-- Esto es de PostgreSQL y no corre en SQLite, que no lo trae.\n", "ALTER TABLE pedidos ENABLE ROW LEVEL SECURITY;\n", "\n", "CREATE POLICY zona_propia ON pedidos\n", " USING (id_cliente IN (SELECT id FROM clientes\n", " WHERE ciudad = current_setting('app.zona')));\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Una regla, todas las zonas, y aplicada en la base y no en cada consulta. La\n", "gracia es esa: **si la regla vive en la base, da igual por dónde entren**,\n", "por la aplicación, por un reporte o por alguien con acceso directo. Si vive en el\n", "código de la aplicación, cada camino nuevo hay que acordarse de protegerlo.\n", "\n", "Es la misma lección del capítulo 13 vista desde el otro\n", "lado. Allá el problema era una consulta que hacía de más; acá es una consulta que\n", "devuelve de más. Las dos se arreglan igual, quitando la posibilidad en vez de\n", "confiando en que nadie se equivoque 🔒" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## La vista que sí guarda datos" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Como una vista se recalcula entera cada vez, si por dentro tiene un\n", "`GROUP BY` sobre diez millones de filas, cada persona que abra el\n", "dashboard paga ese `GROUP BY`. Para eso existen las vistas\n", "materializadas, que sí guardan el resultado." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "try:\n", " q(\"\"\"\n", " CREATE MATERIALIZED VIEW ventas_cache AS SELECT 1;\n", " \"\"\")\n", "except Exception as e:\n", " print(f'{type(e).__name__}: {e}')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Y la queja que tiene que salir es esta:\n", "\n", "```\n", "OperationalError: near \"MATERIALIZED\": syntax error\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Vistas materializadas\n", "\n", "| PostgreSQL | `CREATE MATERIALIZED VIEW y REFRESH MATERIALIZED VIEW cuando quieras` |\n", "|---|---|\n", "| MySQL | `no las tiene: se hace con una tabla normal y un proceso que la rellena` |\n", "| SQL Server | `vistas indexadas: CREATE VIEW ... WITH SCHEMABINDING y un índice único encima` |\n", "| SQLite | `no las tiene` |\n", "\n", "La de PostgreSQL se refresca cuando tú digas, así que el dato puede estar viejo y hay que decidir cada cuánto. La de SQL Server se mantiene sola en cada escritura, que es más cómodo y más caro. En MySQL y SQLite lo haces a mano con INSERT INTO ... SELECT del capítulo 10." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Los disparadores" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Un disparador es código que la base ejecuta **sola** cuando pasa\n", "algo. Nadie lo llama: se dispara.\n", "\n", "El caso que de verdad vale la pena es la auditoría: quiero saber quién cambió\n", "un precio y cuándo, sin depender de que el programa que lo cambió se acuerde de\n", "anotarlo." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "CREATE TABLE precios (\n", " id_producto INTEGER PRIMARY KEY,\n", " precio REAL NOT NULL\n", ");\n", "CREATE TABLE precios_historial (\n", " id INTEGER PRIMARY KEY,\n", " id_producto INTEGER NOT NULL,\n", " precio_viejo REAL,\n", " precio_nuevo REAL,\n", " cuando TEXT NOT NULL\n", ");\n", "CREATE TRIGGER tr_precios_cambio\n", "AFTER UPDATE OF precio ON precios\n", "FOR EACH ROW\n", "BEGIN\n", " INSERT INTO precios_historial (id_producto, precio_viejo, precio_nuevo, cuando)\n", " VALUES (OLD.id_producto, OLD.precio, NEW.precio, '2026-06-24');\n", "END;\n", "INSERT INTO precios (id_producto, precio) SELECT id, precio FROM productos WHERE id <= 3;\n", "SELECT * FROM precios ORDER BY id_producto;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Tres productos cargados y ni una fila en el historial, porque el disparador\n", "solo mira los `UPDATE`. Ahora sube los precios." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "UPDATE precios SET precio = precio * 1.10 WHERE id_producto <= 2;\n", "SELECT id_producto, ROUND(precio_viejo, 2) AS antes, ROUND(precio_nuevo, 2) AS despues, cuando\n", "FROM precios_historial ORDER BY id;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Yo no escribí ningún `INSERT` en el historial y ahí están las dos\n", "filas 🪄\n", "\n", "Las piezas del disparador, que son las mismas en los cuatro motores aunque se\n", "escriban distinto:\n", "\n", "- ⏱️ **`AFTER` o `BEFORE`**: si corre\n", "después o antes del cambio.\n", "\n", "- 🎬 **El evento**: `INSERT`, `UPDATE` o\n", "`DELETE`. Y se puede afinar a una columna, como aquí con\n", "`UPDATE OF precio`.\n", "\n", "- 🔁 **`FOR EACH ROW`**: se ejecuta una vez por fila\n", "cambiada, no una vez por sentencia.\n", "\n", "- 👯 **`OLD` y `NEW`**: la fila como estaba\n", "y como queda. En un `INSERT` solo hay `NEW`; en un\n", "`DELETE`, solo `OLD`.\n", "\n", "Y la otra cosa que hacen bien los disparadores: impedir algo." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "try:\n", " q(\"\"\"\n", " CREATE TRIGGER tr_precio_no_negativo\n", " BEFORE INSERT ON precios\n", " FOR EACH ROW\n", " WHEN NEW.precio < 0\n", " BEGIN\n", " SELECT RAISE(ABORT, 'el precio no puede ser negativo');\n", " END;\n", " INSERT INTO precios (id_producto, precio) VALUES (99, -5);\n", " \"\"\")\n", "except Exception as e:\n", " print(f'{type(e).__name__}: {e}')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Y la queja que tiene que salir es esta:\n", "\n", "```\n", "IntegrityError: el precio no puede ser negativo\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "El mensaje es el mío, escrito por mí. Ese `WHEN` es la condición\n", "para que el disparador se moleste siquiera, y con precio positivo ni se\n", "entera." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "INSERT INTO precios (id_producto, precio) VALUES (99, 5);\n", "SELECT COUNT(*) AS filas FROM precios;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Aunque, siendo honesta, para *este* caso concreto un\n", "`CHECK (precio >= 0)` del capítulo 10 es más simple, más rápido y\n", "se lee mejor. **Antes de escribir un disparador, pregúntate si un\n", "`CHECK`, un `DEFAULT` o una clave foránea hacen el\n", "trabajo**, porque casi siempre sí 💛" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Escribir un disparador\n", "\n", "| PostgreSQL | `el cuerpo va en una FUNCTION aparte y el TRIGGER la llama: son dos objetos, siempre` |\n", "|---|---|\n", "| MySQL | `el cuerpo va dentro, y hace falta cambiar el DELIMITER para poder escribirlo` |\n", "| SQL Server | `trabaja con las tablas INSERTED y DELETED, no con OLD y NEW fila a fila` |\n", "| SQLite | `el cuerpo va dentro, entre BEGIN y END, con OLD y NEW` |\n", "\n", "Lo de SQL Server cambia la cabeza y no solo la sintaxis: sus disparadores se ejecutan una vez por SENTENCIA y te dan dos tablas con todas las filas afectadas, así que el código que escribas ahí es un UPDATE de conjunto y no un \"para cada fila\"." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Abortar desde un disparador con tu propio mensaje\n", "\n", "| PostgreSQL | `RAISE EXCEPTION 'el precio no puede ser negativo'` |\n", "|---|---|\n", "| MySQL | `SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'el precio no puede ser negativo'` |\n", "| SQL Server | `THROW 50000, 'el precio no puede ser negativo', 1` |\n", "| SQLite | `SELECT RAISE(ABORT, 'el precio no puede ser negativo')` |\n", "\n", "Cuatro formas distintas de decir la misma frase. La de MySQL es la que nadie se aprende: ese 45000 es el código de \"error definido por el usuario\" y hay que ponerlo tal cual." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT name, tbl_name FROM sqlite_master WHERE type = 'trigger' ORDER BY name;\n", "\"\"\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "DROP TRIGGER tr_precio_no_negativo;\n", "SELECT COUNT(*) AS disparadores FROM sqlite_master WHERE type = 'trigger';\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Y el aviso que va con los disparadores, que es serio: **un disparador\n", "es código que corre sin que nadie lo llame y que no se ve en ninguna\n", "consulta**. Cuando alguien pase tres días buscando por qué una tabla\n", "cambia sola, va a ser esto. Ponles nombres que se entiendan, apúntalos en algún\n", "sitio y úsalos poco." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Y los procedimientos, que en SQLite no existen" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "try:\n", " q(\"\"\"\n", " CREATE PROCEDURE resumen_canal()\n", " BEGIN\n", " SELECT canal, COUNT(*) FROM pedidos GROUP BY canal;\n", " END;\n", " \"\"\")\n", "except Exception as e:\n", " print(f'{type(e).__name__}: {e}')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Y la queja que tiene que salir es esta:\n", "\n", "```\n", "OperationalError: near \"PROCEDURE\": syntax error\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "try:\n", " q(\"\"\"\n", " CREATE FUNCTION doble(x INT) RETURNS INT RETURN x * 2;\n", " \"\"\")\n", "except Exception as e:\n", " print(f'{type(e).__name__}: {e}')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Y la queja que tiene que salir es esta:\n", "\n", "```\n", "OperationalError: near \"FUNCTION\": syntax error\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Ninguna de las dos. **SQLite no tiene procedimientos almacenados ni\n", "funciones definidas por el usuario en SQL**, y no es un olvido: es la\n", "decisión de diseño de caber en un archivo y no traer un lenguaje de\n", "programación dentro.\n", "\n", "En SQLite, esa lógica va en tu programa: en Python, en tu app, donde sea. Y\n", "la verdad es que hoy mucha gente lo hace así incluso teniendo motores que sí los\n", "soportan, porque el código en un archivo `.py` se versiona en git, se\n", "revisa en un pull request y se prueba; el código dentro de la base, no 🤷‍♀️" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Procedimientos almacenados\n", "\n", "| PostgreSQL | `CREATE PROCEDURE y CREATE FUNCTION, en PL/pgSQL y hasta en Python` |\n", "|---|---|\n", "| MySQL | `CREATE PROCEDURE, cambiando antes el DELIMITER` |\n", "| SQL Server | `CREATE PROCEDURE en T-SQL, y es donde más se usan de los cuatro` |\n", "| SQLite | `NO EXISTEN` |\n", "\n", "Esta es la diferencia más grande de todo el libro: no es que se escriba distinto, es que en uno de los cuatro no está. Si vienes de SQL Server, donde media la lógica de negocio suele vivir en procedimientos, el cambio de cabeza es enorme." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Ejercicios" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Siete sobre tu copia. Intenta antes de abrir 💛" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 1. Tu vista de clientes activos\n", "\n", "Una vista con los clientes que han comprado alguna vez, su\n", "número de pedidos y sus soles." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "CREATE VIEW clientes_activos AS\n", "SELECT c.id, TRIM(c.nombre) AS nombre, c.ciudad, c.segmento,\n", " COUNT(p.id) AS pedidos,\n", " ROUND(SUM(p.monto), 2) AS soles\n", "FROM clientes c\n", "JOIN pedidos p ON p.id_cliente = c.id\n", "GROUP BY c.id, c.nombre, c.ciudad, c.segmento;\n", "SELECT COUNT(*) AS activos FROM clientes_activos;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "activos\n", "-------\n", "119\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "119, que son los 120 menos Comercial Rojas 120 del capítulo 7. Y ahora\n", "\"cliente activo\" quiere decir lo mismo para todo el que consulte esta base, que\n", "es exactamente para lo que sirve una vista 🌟" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2. Consultar la vista como si fuera tabla\n", "\n", "Los cinco activos que más gastaron, en Chiclayo." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT nombre, pedidos, soles\n", "FROM clientes_activos\n", "WHERE ciudad = 'Chiclayo'\n", "ORDER BY soles DESC\n", "LIMIT 5;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "nombre pedidos soles\n", "---------------------- ------- -------\n", "Market Central 087 12 8148.56\n", "MARKET CENTRAL 078 14 7977.9\n", "Autoservicio Norte 008 14 7494.38\n", "Bodega San Martin 048 14 7328.96\n", "Market Central 064 11 6941.42\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Mira lo corta que quedó la consulta. Todo el `JOIN` y el\n", "`GROUP BY` están escondidos en la vista, y quien pregunta solo tiene\n", "que saber filtrar y ordenar." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 3. Una vista sobre otra vista\n", "\n", "Los activos que están por encima de S/8.000, apoyándote en\n", "la vista anterior." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "CREATE VIEW clientes_top AS\n", "SELECT * FROM clientes_activos WHERE soles > 8000;\n", "SELECT nombre, ciudad, soles FROM clientes_top ORDER BY soles DESC;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "nombre ciudad soles\n", "-------------------------- -------- --------\n", "Distribuidora Paz 058 Lima 10131.72\n", "Almacenes Vega 067 Piura 9516.06\n", "Market Central 087 Chiclayo 8148.56\n", "Restaurante Miraflores 093 Trujillo 8119.95\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Sí, se pueden encadenar. Y también es fácil pasarse: cinco vistas una encima\n", "de otra son imposibles de depurar cuando algo sale mal, porque el\n", "`EXPLAIN` te muestra la consulta expandida entera y no se parece a\n", "nada de lo que escribiste 😵‍💫" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 4. Un disparador que lleva la cuenta\n", "\n", "Una tabla de pedidos nuevos que mantenga sola un contador\n", "por canal." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "CREATE TABLE pedidos_nuevos (id INTEGER PRIMARY KEY, canal TEXT NOT NULL, monto REAL NOT NULL);\n", "CREATE TABLE contador_canal (canal TEXT PRIMARY KEY, pedidos INTEGER NOT NULL);\n", "CREATE TRIGGER tr_contar_pedido\n", "AFTER INSERT ON pedidos_nuevos\n", "FOR EACH ROW\n", "BEGIN\n", " INSERT INTO contador_canal (canal, pedidos) VALUES (NEW.canal, 1)\n", " ON CONFLICT (canal) DO UPDATE SET pedidos = contador_canal.pedidos + 1;\n", "END;\n", "INSERT INTO pedidos_nuevos (canal, monto) VALUES ('Web', 100), ('Web', 200), ('Tienda', 50);\n", "SELECT * FROM contador_canal ORDER BY canal;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "canal pedidos\n", "------ -------\n", "Tienda 1\n", "Web 2\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Un disparador con el upsert del capítulo 11 adentro. Web en 2 y Tienda en 1,\n", "y yo solo escribí tres `INSERT` en la otra tabla.\n", "\n", "Así se llevan los contadores de verdad en producción, y también así nacen los\n", "misterios: dentro de un año, quien vea `contador_canal` no va a saber\n", "de dónde salen esos números 🕵️‍♀️" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 5. Un disparador que borra en cascada\n", "\n", "Al borrar un pedido nuevo, que su contador baje solo." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "CREATE TRIGGER tr_descontar_pedido\n", "AFTER DELETE ON pedidos_nuevos\n", "FOR EACH ROW\n", "BEGIN\n", " UPDATE contador_canal SET pedidos = pedidos - 1 WHERE canal = OLD.canal;\n", "END;\n", "DELETE FROM pedidos_nuevos WHERE canal = 'Web' AND monto = 100;\n", "SELECT * FROM contador_canal ORDER BY canal;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "canal pedidos\n", "------ -------\n", "Tienda 1\n", "Web 1\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Aquí `NEW` no existe porque no hay fila nueva: en un\n", "`DELETE` solo tienes `OLD`. Y en un `INSERT` es\n", "al revés." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 6. El disparador que protege\n", "\n", "Impide que nadie deje un monto en cero en pedidos\n", "nuevos." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "CREATE TRIGGER tr_monto_positivo\n", "BEFORE INSERT ON pedidos_nuevos\n", "FOR EACH ROW\n", "WHEN NEW.monto <= 0\n", "BEGIN\n", " SELECT RAISE(ABORT, 'un pedido no puede valer cero');\n", "END;\n", "INSERT INTO pedidos_nuevos (canal, monto) VALUES ('Web', 0);\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "IntegrityError: un pedido no puede valer cero\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Y fíjate en algo importante: como es `BEFORE`, la fila no llegó a\n", "entrar, así que el contador del ejercicio 4 tampoco se movió. El orden de los\n", "disparadores importa." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 7. Escríbelo para los cuatro\n", "\n", "Sin ejecutar: la misma vista y el mismo disparador de\n", "auditoría, en los cuatro motores." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "-- La VISTA se escribe igual en los cuatro. Esta es de las buenas.\n", "CREATE VIEW ventas_por_ciudad AS\n", "SELECT c.ciudad, COUNT(p.id) AS pedidos, SUM(p.monto) AS soles\n", "FROM clientes c LEFT JOIN pedidos p ON p.id_cliente = c.id\n", "GROUP BY c.ciudad;\n", "\n", "-- El DISPARADOR, no. PostgreSQL pide una función aparte:\n", "CREATE FUNCTION auditar_precio() RETURNS TRIGGER AS $$\n", "BEGIN\n", " INSERT INTO precios_historial (id_producto, precio_viejo, precio_nuevo, cuando)\n", " VALUES (OLD.id_producto, OLD.precio, NEW.precio, CURRENT_DATE);\n", " RETURN NEW;\n", "END;\n", "$$ LANGUAGE plpgsql;\n", "\n", "CREATE TRIGGER tr_precios_cambio AFTER UPDATE OF precio ON precios\n", "FOR EACH ROW EXECUTE FUNCTION auditar_precio();\n", "\n", "-- MySQL: el cuerpo va dentro, pero hay que cambiar el DELIMITER\n", "DELIMITER //\n", "CREATE TRIGGER tr_precios_cambio AFTER UPDATE ON precios\n", "FOR EACH ROW\n", "BEGIN\n", " INSERT INTO precios_historial (id_producto, precio_viejo, precio_nuevo, cuando)\n", " VALUES (OLD.id_producto, OLD.precio, NEW.precio, CURRENT_DATE);\n", "END//\n", "DELIMITER ;\n", "\n", "-- SQL Server: una vez por sentencia, con las tablas INSERTED y DELETED\n", "CREATE TRIGGER tr_precios_cambio ON precios AFTER UPDATE AS\n", "BEGIN\n", " INSERT INTO precios_historial (id_producto, precio_viejo, precio_nuevo, cuando)\n", " SELECT d.id_producto, d.precio, i.precio, CAST(GETDATE() AS DATE)\n", " FROM deleted d JOIN inserted i ON i.id_producto = d.id_producto;\n", "END;\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Lo de MySQL merece una nota: ese `DELIMITER //` existe porque el\n", "cliente de MySQL corta las sentencias en el punto y coma, y el cuerpo del\n", "disparador tiene punto y comas dentro. Así que hay que decirle \"por un rato, el\n", "final de sentencia es `//`\". Es puro cliente, no es SQL 🫠\n", "\n", "Y mira el de SQL Server: no hay `OLD` ni `NEW`, hay un\n", "`JOIN` entre `deleted` e `inserted`. Es otra\n", "forma de pensarlo, y es la que hay que aprender si trabajas ahí." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## El registro que dice que no pasó nada" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Antes de cerrar, una trampa de disparadores que da especialmente mal cuerpo, porque el que la sufre es justo quien confía en la auditoría 👻" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### La trampa\n", "\n", "Pones un disparador para llevar el registro de todo lo que cambia en la tabla, que es justo para lo que sirven. Cargas el archivo del mes con INSERT OR REPLACE y vas a mirar el registro.\n", "\n", "```\n", "CREATE TRIGGER t_upd AFTER UPDATE ON clientes\n", "BEGIN INSERT INTO log VALUES ('update'); END;\n", "\n", "CREATE TRIGGER t_del AFTER DELETE ON clientes\n", "BEGIN INSERT INTO log VALUES ('delete'); END;\n", "\n", "INSERT OR REPLACE INTO clientes VALUES (1, 'Otro nombre', ...);\n", "\n", "SELECT * FROM log;\n", "-- (vacío)\n", "```\n", "\n", "**Qué está mal**\n", "\n", "El nombre del cliente cambió y el registro está vacío 👻\n", "\n", "`REPLACE` por dentro borra la fila vieja y mete una nueva, así que uno esperaría que disparara el de DELETE, o el de UPDATE, o los dos. No dispara ninguno: SQLite solo lanza los disparadores de borrado en un REPLACE si tienes encendido `PRAGMA recursive_triggers`, que también está apagado por defecto.\n", "\n", "Y esto es peor que un disparador que no existe, porque un registro vacío se lee como \"no cambió nada\". Tienes una auditoría que dice que el dato está intacto mientras el dato cambió.\n", "\n", "Con datos que alguien va a auditar, prefiero no usar REPLACE nunca y escribir el UPDATE o el `ON CONFLICT DO UPDATE` a mano. Es una línea más y hace lo que parece que hace, que en una tabla que alguien va a revisar vale mucho más que la línea que te ahorras." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Comprueba que lo tienes\n", "\n", "Creas una vista que resume las ventas por ciudad. Al día siguiente entran ventas nuevas. ¿Qué ves al consultar la vista?\n", "\n", "a) Los datos nuevos, porque la vista guarda la consulta y no el resultado\n", "\n", "b) Los datos de ayer, hasta que la actualices\n", "\n", "c) Un error, porque los datos cambiaron\n", "\n", "d) Depende de si le pusiste un índice\n", "\n", "---\n", "\n", "**La correcta es la a.**\n", "\n", "*b)* Eso es una vista materializada, que es otra cosa y no todos los motores la tienen.\n", "\n", "*c)* Una vista no se rompe porque la tabla crezca: para eso está.\n", "\n", "*d)* Los índices aceleran, no deciden qué datos ves.\n", "\n", "Una vista es una consulta con nombre. Se ejecuta cada vez que la miras." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Lo que te llevas" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "- 🏷️ Una vista es una consulta con nombre. No guarda datos, siempre está al\n", "día y no acelera nada.\n", "\n", "- 📐 Sirve para que una definición de negocio signifique lo mismo para todos,\n", "para esconder la limpieza y para dar acceso a una parte.\n", "\n", "- 💾 Las materializadas sí guardan el resultado: están en PostgreSQL y (a su\n", "manera) en SQL Server. En MySQL y SQLite se hacen a mano con una tabla.\n", "\n", "- 🪄 Un disparador corre solo cuando pasa algo, con `OLD` y\n", "`NEW` a mano. Sirve para auditar y para impedir.\n", "\n", "- 🛑 Antes de escribir uno, mira si un `CHECK`, un\n", "`DEFAULT` o una clave foránea hacen el trabajo. Casi siempre sí.\n", "\n", "- 👻 Un disparador es código invisible desde la consulta. Nómbralos bien y\n", "úsalos poco.\n", "\n", "- 🚫 SQLite no tiene procedimientos almacenados. Esa lógica va en tu programa,\n", "que además se versiona y se prueba.\n", "\n", "Y si de todo el capítulo te llevas una sola frase, que sea esta:" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Una definición que vive en la base significa lo mismo para todo el mundo.\n", "Una que vive en tu archivo, no." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Y poner de acuerdo a un equipo en qué significa cada número es la mitad\n", "del trabajo de verdad. Es justo lo que hacemos en el\n", "[Full Day IA](https://missyera.com/cursos/full-day-ia/), sobre los datos de cada\n", "quien 💜\n", "\n", "En el capítulo 18 juntamos todo: un análisis completo de la tienda de punta a\n", "punta, con las preguntas que de verdad te van a hacer y las consultas que las\n", "contestan.\n", "\n", "Que tengas lindo día! 🌸" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---\n", "\n", "Ese era el capítulo 21 de **SQL desde cero**. El texto completo, con las salidas de cada bloque, está en https://missyera.com/guias/sql-desde-cero/vistas-procedimientos-triggers/\n", "\n", "Que tengas lindo día! 🌸" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "name": "python", "version": "3.11" } }, "nbformat": 4, "nbformat_minor": 5 }