{ "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 práctica 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", "Los ejercicios están al final y traen una celda vacía debajo de cada uno. Las\n", "respuestas viven en el cuaderno de soluciones, y merece la pena pelearse un\n", "rato antes de abrirlo 💛" ] }, { "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": [ "## Antes de empezar\n", "\n", "Esta celda baja el ayudante que corrige tus ejercicios. Después, en cada\n", "ejercicio que se pueda corregir solo, vas a ver `%%revisa` arriba de la celda:\n", "escribe tu respuesta debajo, ejecuta, y te digo si te salió 💛" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import urllib.request\n", "\n", "# El ayudante de los cuadernos. Trae la corrección de los ejercicios y, en los\n", "# capítulos de consola, la celda mágica que ejecuta los comandos. Se baja en\n", "# vez de venir pegado aquí para que siempre sea el último.\n", "urllib.request.urlretrieve(\n", " \"https://missyera.com/static/cuadernos/revisa.py\", \"revisa.py\")\n", "import revisa\n", "revisa.carga({\n", " 1: \"YWN0aXZvcwotLS0tLS0tCjExOQ==\",\n", " 2: \"bm9tYnJlICAgICAgICAgICAgICAgICAgcGVkaWRvcyAgc29sZXMKLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSAgLS0tLS0tLSAgLS0tLS0tLQpNYXJrZXQgQ2VudHJhbCAwODcgICAgICAxMiAgICAgICA4MTQ4LjU2Ck1BUktFVCBDRU5UUkFMIDA3OCAgICAgIDE0ICAgICAgIDc5NzcuOQpBdXRvc2VydmljaW8gTm9ydGUgMDA4ICAxNCAgICAgICA3NDk0LjM4CkJvZGVnYSBTYW4gTWFydGluIDA0OCAgIDE0ICAgICAgIDczMjguOTYKTWFya2V0IENlbnRyYWwgMDY0ICAgICAgMTEgICAgICAgNjk0MS40Mg==\",\n", " 3: \"bm9tYnJlICAgICAgICAgICAgICAgICAgICAgIGNpdWRhZCAgICBzb2xlcwotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSAgLS0tLS0tLS0gIC0tLS0tLS0tCkRpc3RyaWJ1aWRvcmEgUGF6IDA1OCAgICAgICBMaW1hICAgICAgMTAxMzEuNzIKQWxtYWNlbmVzIFZlZ2EgMDY3ICAgICAgICAgIFBpdXJhICAgICA5NTE2LjA2Ck1hcmtldCBDZW50cmFsIDA4NyAgICAgICAgICBDaGljbGF5byAgODE0OC41NgpSZXN0YXVyYW50ZSBNaXJhZmxvcmVzIDA5MyAgVHJ1amlsbG8gIDgxMTkuOTU=\",\n", " 4: \"Y2FuYWwgICBwZWRpZG9zCi0tLS0tLSAgLS0tLS0tLQpUaWVuZGEgIDEKV2ViICAgICAy\",\n", " 5: \"Y2FuYWwgICBwZWRpZG9zCi0tLS0tLSAgLS0tLS0tLQpUaWVuZGEgIDEKV2ViICAgICAx\",\n", " 6: \"SW50ZWdyaXR5RXJyb3I6IHVuIHBlZGlkbyBubyBwdWVkZSB2YWxlciBjZXJv\",\n", "}, lenguaje=\"sql\")" ] }, { "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": [ "%%revisa 1\n", "-- tu turno" ] }, { "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": [ "%%revisa 2\n", "-- tu turno" ] }, { "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": [ "%%revisa 3\n", "-- tu turno" ] }, { "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": [ "%%revisa 4\n", "-- tu turno" ] }, { "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": [ "%%revisa 5\n", "-- tu turno" ] }, { "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": [ "%%revisa 6\n", "-- tu turno" ] }, { "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": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# tu turno" ] }, { "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?** La respuesta está en el cuaderno de soluciones. Míralo tú primero." ] }, { "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" ] }, { "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 }